

CREATE TABLE job (
   id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   job_start TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
   job_end TIMESTAMP WITH TIME ZONE,
   job_name VARCHAR(50) NOT NULL
);
CREATE INDEX ON job(job_start);

CREATE TABLE job_detail (
   jobid INT REFERENCES job(id) ON DELETE CASCADE NOT NULL,
   log_date TIMESTAMP WITH TIME ZONE NOT NULL,
   log_message TEXT NOT NULL
);

-- Quelques données dans les tables
INSERT INTO job (job_end, job_name) VALUES 
   (now() + INTERVAL '1 hour', 'Daily routine');
INSERT INTO job_detail VALUES 
   (currval('job_id_seq'), now(), 'Purge is started');
INSERT INTO job_detail VALUES 
   (currval('job_id_seq'), now() + INTERVAL '1 hour', 'Purge is completed');

   
   
   
-- Quelques données dans les tables
INSERT INTO job (job_end, job_name) VALUES 
   (now() + INTERVAL '2 hour', 'Daily routine');
INSERT INTO job_detail VALUES 
   (currval('job_id_seq'), now() + INTERVAL '2 hour 1s', 'Purge is started');
INSERT INTO job_detail VALUES 
   (currval('job_id_seq'), now() + INTERVAL '2 hour 30min', 'Purge is completed');

   
   
   CREATE TABLE job_part (
   id INT GENERATED BY DEFAULT AS IDENTITY,
   job_start TIMESTAMP WITH TIME ZONE DEFAULT now(),
   job_end TIMESTAMP WITH TIME ZONE,
   job_name VARCHAR(50) NOT NULL
   /* CC,
   PRIMARY KEY (id, job_start)
   */
) PARTITION BY RANGE (job_start);


-- CC
-- Préparer l index sur la future partition pour réduire le temps de transition
CREATE UNIQUE INDEX job_start_id ON job(job_start,id);

-- fin CC

\d+ job
\d+ job_part


START TRANSACTION;

-- Suppression des contraintes de clé primaire pour prévenir l'erreur suivante :
-- ERROR:  multiple primary keys for table "job" are not allowed
ALTER TABLE job_detail DROP CONSTRAINT IF EXISTS job_detail_jobid_fkey;
ALTER TABLE job DROP CONSTRAINT IF EXISTS job_pkey;

-- Ajout de la table job en tant que partition
ALTER TABLE job_part ATTACH PARTITION job DEFAULT;

-- ceci fout la m.. en empechant renommage
-- ALTER TABLE job ADD CONSTRAINT job_default_pkey
-- CHECK (job_start > '2000-01-01'::timestamptz) ;

--CC 
--CREATE INDEX ON job_part(job_start);
-- l ancienne contrainte ayant disparu, on peut créer la PK sur la partition par défaut en réutilisant l'index déjà créé
ALTER TABLE job ADD CONSTRAINT job_default_pkey PRIMARY KEY USING INDEX job_start_id ;
--NOTICE:  ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "job_start_id" to "job_default_pkey"

\d+ job
-- redéclaration au niveau de la partition, qui va réutiliser la PK sur la partition déjà remplie
ALTER TABLE job_part ADD CONSTRAINT job_pkey PRIMARY KEY (job_start, id) ;


-- fin CC

--- CC
 SELECT nextval(pg_get_serial_sequence('job', 'id')) ; 

-- Récupération de la dernière valeur de la séquence de la précédente table
SELECT setval(pg_get_serial_sequence('job_part', 'id'),
      currval(pg_get_serial_sequence('job', 'id')), true);

-- Suppression de l'ancienne séquence et de sa relation avec la partition job
ALTER TABLE job ALTER id DROP IDENTITY, ALTER job_start DROP DEFAULT;

-- Réactivation des contraintes de clés étrangères
-- Puisque la colonne job_start fait à présent partie de la PRIMARY KEY, il est
-- nécessaire d'ajouter cette colonne dans la table job_detail et de l'alimenter
ALTER TABLE job_detail ADD job_start TIMESTAMP WITH TIME ZONE;

/* KO
UPDATE job_detail SET job_start = j.job_start
  FROM job_detail d JOIN job j ON j.id = d.jobid; 
*/
UPDATE job_detail SET job_start = j.job_start
  FROM job_part j WHERE (j.id = jobid) ; 

select * from job ;
  
select * from job_detail ;

  
ALTER TABLE job_detail ADD FOREIGN KEY (jobid, job_start) 
   REFERENCES job(id, job_start) ON DELETE CASCADE;

COMMIT;

\d+
\d+ job_part
\d+ job

START TRANSACTION;

-- Renommage des relations pour maintenir la logique métier
ALTER TABLE job RENAME TO job_default;
ALTER TABLE job_part RENAME TO job;
ALTER SEQUENCE job_part_id_seq RENAME TO job_id_seq;

COMMIT;


\quit
