Upgrade to id

{{#customtitle:Rename primary key field to Id}}

Some tables were created in early versions 0.x with a primary key different of Id. There is an attempt to change this, in order to have an Id field primary key of each table. This is rather complex if we want to ensure upward and downward compatibility, with both DBMS MySql and PostgreSql.

This should be possible in 4 steps, that is 4 Gestinux versions. It is currently untested with PostGreSQL.

<source lang="sql"> -- clean up DROP TABLE IF EXISTS table2 ; DROP TABLE IF EXISTS table1 ;

-- ----------------------------------- V1

---

CREATE TABLE table1 ( old_key INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT, donnee VARCHAR(50) NULL );

CREATE TABLE table2 ( old_key INT(10) NOT NULL, donnee VARCHAR(50) NULL,

  KEY table2_K1 (old_key),
  CONSTRAINT table2_C1 FOREIGN KEY (old_key) REFERENCES table1 (old_key)

ON DELETE CASCADE );

INSERT INTO table1 (donnee) VALUES ('test1'); INSERT INTO table2 (old_key,donnee) VALUES ( 1, 'test1'); -- forbidden : INSERT INTO table2 (old_key,donnee) VALUES ( 2, 'test2');

SELECT * FROM table1 JOIN table2 ON table2.old_key=table1.old_key;

-- ----------------------------------- V2

---

ALTER TABLE table1 ADD COLUMN Id INT NULL AFTER donnee;

CREATE INDEX table1_K1 ON table1 (Id); -- must be not unique for backward compatibility

-- During upgrade : duplicate old_key into Id UPDATE table1 SET Id=old_key WHERE Id IS NULL;

-- If upgrading from V3, the old primary key must not be re-created, auto_inc property must remain on Id, and foreign keys must not be changed

-- GESTINUX (trunk, future 1.2) must be modified for : -- Set a field auto_inc only when it is created, never if already existing -- Create a primary key or a foreign key (specified in property) only when not already existing

SELECT * FROM table1 JOIN table2 ON table2.old_key=table1.Id; -- Do not use Table1.old_key in SQL queries, and only Table1.id -- When inserting, update the field which is not auto_inc, old_key when coming from V3, or Id if V3 was never used

-- ----------------------------------- V4

---

-- During upgrade, when coming from V3, the datamodule change the primary key (deleting and re-creating all foreign keys) -- old_key is deleted from the table, and will thus not be created if coming from V4 ALTER TABLE table2 DROP FOREIGN KEY table2_C1; ALTER TABLE table1 CHANGE COLUMN old_key old_key INT(10) NULL DEFAULT NULL; ALTER TABLE table1 DROP PRIMARY KEY; ALTER TABLE table1 DROP INDEX table1_K1; -- désormais inutile ALTER TABLE table1 ADD PRIMARY KEY(Id); ALTER TABLE table1 CHANGE COLUMN Id Id INT(10) NOT NULL AUTO_INCREMENT; ALTER TABLE table2 ADD FOREIGN KEY table2_C1 (old_key) REFERENCES table1 (Id) ON DELETE CASCADE; -- garder impérativement le même nom

-- When inserting V3 do not update anymore old_key, this field remains NULL until deleted (in V4) INSERT INTO table1 ( donnee) value ('test3'); SELECT * FROM table1 JOIN table2 ON table2.old_key=table1.Id;

-- ----------------------------------- V4

---

-- finished ! ALTER TABLE table1 DROP COLUMN old_key; SELECT * FROM table1 JOIN table2 ON table2.old_key=table1.Id; </source>