dbd_lab11_sql

--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)

set define off;

  CREATE OR REPLACE PROCEDURE "LS"
(
  DIRID IN directories.directoryid%TYPE
) AS
norows EXCEPTION;
CURSOR results IS
    select name
    from (select directoryid, name, parentid from directories
          union
          select 0, name, directoryid from files) res
    where parentid=dirid;
BEGIN
  if SQL%ROWCOUNT=0 then
    raise norows;
  else
    dbms_output.put_line('Directories and files found in the directory with directoryid = '||dirid||':');
    FOR rec in results LOOP
      dbms_output.put_line(rec.name);
    END LOOP;
  end if;
EXCEPTION
  when norows then
    dbms_output.put_line('Directory with id '||dirid||' is empty.');
END LS;

SET serveroutput ON
begin
  ls(7);
end;