-- Université IBM i du 7 novembre 2023
-- Session S19 – Apprivoisez l'IFS avec les services SQL
-- Philippe Bourgeois - IBM France

-- 1. Modification des propriétés de la connexion JDBC pour indiquer NAMING=*SYS
-- 2. Exécution des commandes CL suivantes :
CL:ADDLIBLE LIB(SYSTOOLS);
CL:CHGCURLIB CURLIB(PB);

SELECT * FROM library_list_info;

-- Liste de tous les services
SELECT COUNT(*) FROM services_info; -- 326 au 07/11/2023
SELECT * FROM services_info ORDER BY service_category, service_name;
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Liste de tous les services IBM i
SELECT COUNT(*) FROM services_info WHERE service_category NOT LIKE 'DATABASE%'; -- 278 au 07/11/2023
SELECT * FROM services_info WHERE service_category NOT LIKE 'DATABASE%' ORDER BY service_category, service_name;

-- Catégorie IFS
SELECT COUNT(*) FROM services_info WHERE service_category = 'IFS'; -- 8 au 30/10/2023 --> Il en manque 5
SELECT * FROM services_info WHERE service_category = 'IFS' ORDER BY service_name;

-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Services IFS
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- 1. OBJECT_STATISTICS - Liste et attributs des objets d'un répertoire de l'IFS

SELECT * FROM TABLE (qsys2.ifs_object_statistics(START_PATH_NAME => '/home/BOURGEOIS'));, 
                                                  
                                                   
-- Les objets sous /home - SANS les sous-répertoires - Informations de base

SELECT Path_Name, Object_Type, Data_Size, Object_Owner
 FROM TABLE (ifs_object_statistics(START_PATH_NAME => '/home', 
                                                     SUBTREE_DIRECTORIES => 'NO')); 
-- /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
  
-- Les objets sous /home - AVEC les sous-répertoires - Informations de base

SELECT Path_Name, Object_Type, Data_Size, Object_Owner
 FROM TABLE (ifs_object_statistics(START_PATH_NAME => '/home', 
                                                     SUBTREE_DIRECTORIES => 'YES')) ORDER BY Path_Name;   
-- /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////                            
                                                     
-- Quels sont les fichiers JSON du répertoire /pb auxquels on a accédé après le 1er octobre 2023 ?

SELECT Path_Name, Object_Type, Create_Timestamp, Access_Timestamp, Last_Used_Timestamp
 FROM TABLE(ifs_object_statistics(START_PATH_NAME => '/pb', 
                                                    SUBTREE_DIRECTORIES => 'YES'))
  WHERE RIGHT(TRIM(Path_Name), 5) = '.json'
    AND Access_Timestamp >= '2023-10-01';
-- /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////  
  
-- Quels sont les 5 plus gros fichiers sous le répertoire /home ?

SELECT Path_Name, CHAR(Data_Size/1000000) AS Data_Size_Mo, Last_Used_Timestamp
 FROM TABLE(ifs_object_statistics(START_PATH_NAME => '/home', 
                                                    SUBTREE_DIRECTORIES => 'YES',
                                                    OBJECT_TYPE_LIST => '*STMF' ))
  ORDER BY Data_Size DESC LIMIT 5;
-- /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- Liste des fichiers dans /tmp qui n'ont pas été utilisés depuis au moins 2 ans     
                               
SELECT Path_Name, Object_Type, Create_Timestamp, Access_Timestamp, Last_Used_Timestamp
 FROM TABLE (ifs_object_statistics(START_PATH_NAME => '/tmp', 
                                                     SUBTREE_DIRECTORIES => 'YES',
                                                     OBJECT_TYPE_LIST  => '*ALLSTMF'))
  WHERE Last_Used_Timestamp < Current Timestamp - 2 years;

-- Suppression des fichiers dans /tmp qui n'ont pas été utilisés depuis au moins 2 ans   

SELECT QCMDEXC('RMVLNK OBJLNK(''' CONCAT TRIM(Path_Name) CONCAT  ''')')
 FROM TABLE (ifs_object_statistics(START_PATH_NAME => '/tmp', 
                                                     SUBTREE_DIRECTORIES => 'YES',
                                                     OBJECT_TYPE_LIST  => '*ALLSTMF'))
  WHERE Last_Used_Timestamp < Current Timestamp - 2 years;

-- /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

 -- Quels sont les membres dans la bibliothèque PB qui ont été modifiés après le 1er octobre 2023 ?
  
SELECT PATH_NAME, 
           SUBSTR(Path_Name, 
                       LOCATE_IN_STRING(Path_Name, '/', 1, 3) + 1, 
                       (LOCATE_IN_STRING(Path_Name, '/', 1, 4) - LOCATE_IN_STRING(Path_Name, '/', 1, 3))-1) AS Fichier, 
            SUBSTR(Path_Name, LOCATE_IN_STRING(Path_Name, '/', -1) + 1) AS Membre, 
            Text_Description, Data_Change_Timestamp, Object_Owner
  FROM TABLE(ifs_object_statistics(START_PATH_NAME => '/QSYS.LIB/PB.LIB',                                                   
                                                 OBJECT_TYPE_LIST => '*MBR' ))
  WHERE Data_Change_Timestamp >= '2023-09-01'
  ORDER BY Data_Change_Timestamp DESC;  
  
 -- /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
  
--  Nombre de fichiers et taille globale par répertoire de l'IFS

WITH all_ifs_dirs AS
 (SELECT Path_Name AS dir_path
   FROM TABLE(ifs_object_statistics(START_PATH_NAME => '/',
                                                      SUBTREE_DIRECTORIES => 'YES',
                                                      OBJECT_TYPE_LIST => '*ALLDIR *NOQSYS')))
  SELECT dir_path, COUNT(*) AS stmf_count, SUM(data_size) AS total_size FROM
   all_ifs_dirs CROSS JOIN TABLE(ifs_object_statistics(START_PATH_NAME => dir_path,
                                                                                SUBTREE_DIRECTORIES => 'NO',
                                                                                OBJECT_TYPE_LIST => '*STMF'))
   GROUP BY dir_path     
   ORDER BY stmf_count DESC;

-- /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
  
--  Les objets de mon répertoire sous /home dont je ne suis pas propriétaire

SELECT Path_Name, Object_Type, Object_Owner
 FROM TABLE (ifs_object_statistics(START_PATH_NAME => '/home/bourgeois', 
                                                  SUBTREE_DIRECTORIES => 'YES')) 
 WHERE Object_Owner <> 'BOURGEOIS' AND Path_Name <> '/home/bourgeois'; 
 
 
 -- Correction de la problématique
SELECT Path_Name,
           QCMDEXC('CHGOWN OBJ(''' CONCAT Path_Name CONCAT ''') NEWOWN(BOURGEOIS) RVKOLDAUT(*NO)') 
           AS CHGOWN_Result
 FROM TABLE (ifs_object_statistics(START_PATH_NAME => '/home/bourgeois', 
                                                  SUBTREE_DIRECTORIES => 'YES')) 
 WHERE Object_Owner <> 'BOURGEOIS' AND Path_Name <> '/home/bourgeois'; 
 
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 2. IFS_OBJECT_PRIVILEGES

-- Quels sont les droits sur le fichier /pb/json/films.json ?

SELECT * FROM TABLE(ifs_object_privileges('/pb/json/films.json'));      

SELECT Path_Name, Object_Type, Owner, Authorization_Name, Data_Authority FROM TABLE(ifs_object_privileges('/home/bourgeois/xsr.log'));   

-- /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- Objets IFS de /home/bourgeois qui ne sont pas en *PUBLIC *EXCLUDE

SELECT Path_Name, 1 AS CHGAUT_Result, os.Object_Type, Owner, Data_Authority 
  FROM TABLE(ifs_object_statistics(START_PATH_NAME => '/home/bourgeois',
                                                 SUBTREE_DIRECTORIES => 'YES')) os,
            LATERAL (SELECT Object_Type, Owner, Data_Authority 
                             FROM TABLE(ifs_object_privileges(PATH_NAME => os.path_name)) op
                             WHERE op.authorization_name = '*PUBLIC' AND data_authority <> '*EXCLUDE');

-- Objets IFS de /home/bourgeois qui ne sont pas en *PUBLIC *EXCLUDE  
-- Correction de la problématique

SELECT Path_Name, QCMDEXC('CHGAUT OBJ(''' CONCAT TRIM(os.Path_Name) CONCAT ''') USER(*PUBLIC) DTAAUT(*EXCLUDE) OBJAUT(*NONE)') 
            AS CHGAUT_Result
 FROM TABLE(ifs_object_statistics(START_PATH_NAME => '/home/bourgeois',
                                                SUBTREE_DIRECTORIES => 'YES')) os,
           LATERAL (SELECT Object_Type, Owner, Data_Authority 
                            FROM TABLE(ifs_object_privileges(PATH_NAME => os.path_name)) op
                            WHERE op.authorization_name = '*PUBLIC' AND data_authority <> '*EXCLUDE');                                  

-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 3. IFS_OBJECT_REFERENCES_INFO

--  Combien de travaux ont une référence sur le répertoire /home/bourgeois ?

   SELECT * FROM TABLE(ifs_object_references_info(PATH_NAME => '/home/bourgeois'));   
   
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 -- 4. IFS_OBJECT_LOCK_INFO

-- Quels sont les travaux qui ont un verrouillage ou une référence sur le répertoire /home/bourgeois ?

 SELECT * FROM TABLE(ifs_object_lock_info(PATH_NAME => '/home/bourgeois'));

-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 -- 5. IFS_JOB_INFO

-- Quels sont les références IFS de mon travail en cours ? 

 SELECT * FROM TABLE(ifs_job_info(JOB_NAME => '*'));
 
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 6. SERVER_SHARE_INFO

-- Quels sont les partages de fichiers Net Server ?

SELECT * FROM  server_share_info
  WHERE SHARE_TYPE = 'FILE';

-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 7 -- IFS_RENAME

VALUES ifs_rename(FROM_OBJECT => '/pb/json/listefilms.json', 
                            TO_OBJECT     => '/pb/json/listefilms' CONCAT 
                                                                                   VARCHAR_FORMAT(current date, 'YYYYMMDD')
                                                                                   CONCAT '.json',
                           REPLACE  => 'NO');
 
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Lecture et écriture de fichiers

-- Fonctions GET_xxx_FROM_FILE

VALUES get_clob_from_file('/pb/json/films.json') WITH CS;
VALUES get_clob_from_file('/pb/films.properties') WITH CS;
VALUES get_clob_from_file('/pb/csv/films.csv') WITH CS;
VALUES get_blob_from_file('/pb/pdf/SQL_Reference_V7R5.pdf') WITH CS;

-- /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- Recherche des fichiers JSON dans /pb qui contiennent "1984"

SELECT Path_Name, get_clob_from_file(Path_Name) AS "Contenu du fichier IFS"
 FROM TABLE(ifs_object_statistics(START_PATH_NAME => '/pb', 
                                                SUBTREE_DIRECTORIES => 'YES',
                                                OBJECT_TYPE_LIST => '*ALLSTMF'))
 WHERE RIGHT(Path_Name, 5) = '.json'
   AND get_clob_from_file(Path_Name) LIKE '%1984%'
 ORDER BY Path_Name WITH CS;    
  
 -- /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
  
-- Est-ce que les fichiers listefilms.json qui se trouvent dans 2 répertoires différents ont le même contenu ?

VALUES (CASE 
              WHEN get_clob_from_file('/pb/json/listefilms.json') = 
                       get_clob_from_file('/pb/iws/listefilms.json')
              THEN 'Les 2 fichiers ont le même contenu'
              ELSE 'Les 2 fichiers ont un contenu différent'
            END) WITH CS;          

 -- /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- Lecture et décomposition d'un fichier JSON dans l'IFS
            
SELECT * FROM JSON_TABLE(
   get_clob_from_file('/pb/json/films.json'),
   '$.films' COLUMNS(titre VARCHAR(40) PATH '$.titre', 
                             annee CHAR(4) PATH '$.annee',
                             duree CHAR(4) PATH '$.duree')) WITH CS;

-- Lecture et décomposition d'un fichier JSON dans l'IFS en passant par une vue

CREATE OR REPLACE VIEW filmsv1 AS
 (SELECT * FROM JSON_TABLE(
   get_clob_from_file('/pb/json/films.json'),
   '$.films' COLUMNS(titre VARCHAR(40) PATH '$.titre', 
                             annee CHAR(4) PATH '$.annee',
                             duree CHAR(4) PATH '$.duree')));
                             

SELECT * FROM filmsv1 WITH CS;                             

-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- Fonction GET_XML_FILE

VALUES get_xml_file('/pb/xml/films.xml') WITH CS;

VALUES XMLPARSE(DOCUMENT get_xml_file('/pb/xml/films.xml')) WITH CS;

SELECT * FROM XMLTABLE('/films/film' 
  PASSING XMLPARSE(DOCUMENT get_xml_file('/pb/xml/films.xml'))
  COLUMNS titre      VARCHAR(40)  PATH 'titre',
                 annee   CHAR(4)          PATH 'annee', 
                 duree   CHAR(3)          PATH 'duree') WITH CS;
  
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Services IFS_READ_xxx

SELECT * FROM TABLE(ifs_read(PATH_NAME => '/pb/films.properties',
                                                END_OF_LINE => 'ANY'));
                                              
SELECT * FROM TABLE(ifs_read(PATH_NAME => '/pb/json/films.json',
                                                END_OF_LINE => 'NONE'));
                                              
SELECT LINE FROM TABLE(ifs_read_utf8(PATH_NAME => '/pb/csv/films.csv',
                                                             END_OF_LINE => 'ANY'));                               

SELECT * FROM JSON_TABLE(
  (SELECT LINE FROM TABLE(ifs_read(PATH_NAME => '/pb/json/films.json',
                                                        END_OF_LINE => 'NONE'))), 
   '$.films' COLUMNS(titre VARCHAR(40) PATH '$.titre', 
                              annee CHAR(4) PATH '$.annee',
                              duree CHAR(4) PATH '$.duree'));
        
 -- /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
 
-- Recherche des fichiers JSON dans /pb qui contiennent "1984"

SELECT Path_Name, (SELECT LINE 
                                  FROM TABLE(ifs_read(PATH_NAME => Path_Name,
                                                                   END_OF_LINE => 'NONE'))) AS "Contenu du fichier IFS"
 FROM TABLE(ifs_object_statistics(START_PATH_NAME => '/pb', 
                                                   SUBTREE_DIRECTORIES => 'YES',
                                                   OBJECT_TYPE_LIST => '*ALLSTMF'))
 WHERE RIGHT(Path_Name, 5) = '.json'
   AND (SELECT LINE FROM TABLE(ifs_read(PATH_NAME => Path_Name,
                                                                 END_OF_LINE => 'NONE'))) LIKE '%1984%'
 ORDER BY Path_Name;    
 
 -- /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

                
-- SPLIT
SELECT *
  FROM TABLE(systools.split('A,B,C,D,E,F,G,H', ','));
  
WITH cte1 (z1) AS (VALUES('A,B,C,D,E'), ('F,G,H'))
SELECT  ordinal_position, z1, element
  FROM cte1 CROSS JOIN TABLE(systools.split(z1, ','));
ORDER BY z1, ordinal_position;  


-- Lecture et décomposition d'un fichier CSV de l'IFS

WITH cte1 AS (SELECT line_number, line FROM TABLE(ifs_read_utf8(PATH_NAME => '/pb/csv/films.csv'))),
         cte2 AS (SELECT line_number, line, ordinal_position, TRIM(B '"' FROM element) AS element
                       FROM cte1 CROSS JOIN TABLE(systools.split(line, ',')))
 SELECT line_number,
            MIN(CASE WHEN ordinal_position = 1 THEN element END) AS code_film,     
            MIN(CASE WHEN ordinal_position = 2 THEN element END) AS code_realisateur,
            MIN(CASE WHEN ordinal_position = 3 THEN element END) AS titre_film,     
            MIN(CASE WHEN ordinal_position = 4 THEN element END) AS genre_film,
            MIN(CASE WHEN ordinal_position = 5 THEN element END) AS duree_film,     
            MIN(CASE WHEN ordinal_position = 6 THEN element END) AS annee_film
  FROM cte2        
  GROUP BY line_number;                                                               
 
 CREATE OR REPLACE  VARIABLE pb.fichier_ifs VARCHAR(1024);

CREATE OR REPLACE  VIEW pb.filmsv2 AS
WITH cte1 AS (SELECT line_number, line FROM TABLE(ifs_read_utf8(PATH_NAME => pb.fichier_ifs))),
         cte2 AS (SELECT line_number, line, ordinal_position, TRIM(B '"' FROM element) AS element
                       FROM cte1 CROSS JOIN TABLE(systools.split(line, ',')))
 SELECT line_number,
            MIN(CASE WHEN ordinal_position = 1 THEN element END) AS code_film,     
            MIN(CASE WHEN ordinal_position = 2 THEN element END) AS code_realisateur,
            MIN(CASE WHEN ordinal_position = 3 THEN element END) AS titre_film,     
            MIN(CASE WHEN ordinal_position = 4 THEN element END) AS genre_film,
            MIN(CASE WHEN ordinal_position = 5 THEN element END) AS duree_film,     
            MIN(CASE WHEN ordinal_position = 6 THEN element END) AS annee_film
  FROM cte2        
  GROUP BY line_number;       
  
  SET pb.fichier_ifs = '/pb/csv/films.csv';
  
  SELECT * FROM pb.filmsv2;  
 
                  
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- Services IFS_WRITE_xxx

CALL ifs_write_utf8(PATH_NAME  => '/pb/ifsw1.txt',
                            LINE => 'Premier enregistrement',
                            FILE_CCSID => 1208,
                            OVERWRITE => 'REPLACE');

CALL ifs_write_utf8(PATH_NAME  => '/pb/ifsw1.txt',
                            LINE => 'Second enregistrement',
                            OVERWRITE => 'APPEND');

-- Copie d'un fichier IFS d'un répertoire à un autre

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

CALL ifs_write_utf8(PATH_NAME  => '/pb/txt/ifsw1.txt',
                            LINE => get_clob_from_file('/pb/ifsw1.txt'),
                            OVERWRITE => 'REPLACE');

COMMIT;     

CALL ifs_write_utf8(PATH_NAME  => '/pb/txt/ifsw1.txt',
                            LINE => 'Troisième enregistrement',
                            OVERWRITE => 'APPEND');

-- Création d'un fichier JSON dans l'IFS à partir de données d'une table

CALL ifs_write_utf8(PATH_NAME => '/pb/json/films2.json', 
                     OVERWRITE => 'REPLACE',
                     LINE => (SELECT JSON_OBJECT('films':JSON_ARRAYAGG(JSON_OBJECT('titre' VALUE RTRIM(titre), 
                                                                                                                                'annee' VALUE annee, 
                                                                                                                                'duree' VALUE duree)))
                                     FROM video.films));       


-- Création d'un fichier dans l'IFS contenant le nom
-- de tous les répertoires principaux de l'IFS

BEGIN 
  CALL ifs_write_utf8(PATH_NAME =>'/pb/rep_ifs.txt',
                       LINE => '',  
                       OVERWRITE => 'REPLACE',  
                       END_OF_LINE => 'NONE'); 
  
  FOR SELECT path_name AS nom_repertoire 
   FROM TABLE(ifs_object_statistics(START_PATH_NAME => '/',
                                                  SUBTREE_DIRECTORIES => 'NO',
                                                  OBJECT_TYPE_LIST => '*ALLDIR')) DO 
    CALL ifs_write_utf8(PATH_NAME => '/pb/rep_ifs.txt',  
                         LINE => nom_repertoire); 
  END FOR; 
 END; 
 
-- Ajout dans l'IFS du PDF "SQL Reference" qui se trouve sur le site Web Infocenter d'IBM

CALL ifs_write_binary(PATH_NAME => '/pb/db2/SQL_Reference_75.pdf', 
                               OVERWRITE => 'REPLACE',
                               LINE => HTTP_GET_BLOB('https://www.ibm.com/docs/en/ssw_ibm_i_75/pdf/rbafzpdf.pdf'));      

CALL ifs_write_binary(PATH_NAME => '/pb/db2/SQL_Reference_75.pdf', 
                               OVERWRITE => 'REPLACE',
                               LINE => HTTP_GET_BLOB('https://www.ibm.com/docs/en/ssw_ibm_i_75/pdf/rbafzpdf.pdf', '{"proxy":"9.128.135.5,80", "proxySsl":"false"}'));       
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------