lab11_sab

--b)
--LS / DIR - Displays all the sub-folders / files of a certain folder
SELECT name
FROM(
  SELECT directoryid, name, parentid FROM DIRECTORIES
  UNION
  SELECT 0, name, directoryid FROM FILES
  )
WHERE parentid=7;

--TREE - Displays all the folders / files in a "tree-like" view  (Connect By)

SELECT level, LPAD (' ', 2 * (LEVEL - 1)) || name AS padname
FROM (select directoryid, name, parentid FROM directories
      UNION
      SELECT 0, name, directoryid from files) res
START WITH directoryid=1
CONNECT BY PRIOR directoryid=parentid
ORDER siblings BY name;

-- MKDIR    - Creates a folder (complying with the initial constraints)
INSERT INTO directories values(2,'genres',1);--already exists=>error
INSERT INTO directories values(10,'documentaries',3);

-- MKFILE   - Creates a file (complying with the initial constraints)
INSERT INTO files values(7,'xyz',1,'b');--already exists=>error
INSERT INTO files values(8,'best_jazz',8,'m');

--     (CAT)- adds data to it
UPDATE files SET content=CONCAT(content,'nop') WHERE directoryid = 8;

-- values to be deleted:
INSERT INTO directories values (11,'A',1);
INSERT INTO directories values (12,'B',7);
INSERT INTO directories values (13,'C',7);

--RMDIR    - Deletes a folder and all the corresponding files / sub-folders
--make files table fk to be on cascade delete:
ALTER TABLE FILES
ADD FOREIGN KEY (DIRECTORYID)
REFERENCES DIRECTORIES(DIRECTORYID)
ON DELETE CASCADE;

DELETE FROM directories
WHERE directoryid = 13;

--DEL      - Deletes a file
DELETE FROM files
WHERE name = 'abc' AND directoryid = 7;


-- c) PL/SQL   - Create PL/SQL stored procedures / functions corresponding to the
--                above mentioned commands + CD (change directory)
-- LS PROCEDURE:
set define off;

  CREATE OR REPLACE PROCEDURE "LS"
(
  dirid IN directories.directoryid%TYPE
) AS
BEGIN
    dbms_output.put_line('Directories and files found in the directory with directoryid = '||dirid||':');
    FOR rec in (select name
    from (select directoryid, name, parentid from directories
          union
          select 0, name, directoryid from files) res
    where parentid=dirid) LOOP
      dbms_output.put_line(rec.name);
    END LOOP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('No such directory!');
  WHEN others THEN
    dbms_output.put_line('Error!');
END LS;

SET serveroutput ON
begin
  ls(999);
end;






--DIR PROCEDURE:
set define off;
CREATE OR REPLACE PROCEDURE "DIR"
(
  P1 IN VARCHAR2
)AS
BEGIN
  dbms_output.put_line('abc ' || P1);
  for i in (SELECT * FROM directories) loop
    dbms_output.put_line('->'||i.name);
  end loop;
end DIR;

SET serveroutput ON
begin
  dir(1);
end;