Upgrade to id - Revision history

Unknown user at 17:25, 1 September 2020

2020-09-01T17:25:29Z

← Older revision		Revision as of 19:25, 1 September 2020
Line 7:		Line 7:
	In the version 1.4 we have performed the step 3 for the tables Moves and MoveTemplates.		In the version 1.4 we have performed the step 3 for the tables Moves and MoveTemplates.

	In the version 1.5 we ~~plan to do~~ the ~~same~~ for the tables MoveLines and MoveTemplatesLines.		In the version 1.5 we have performed the step 4 for the tables Moves and MoveTemplates, and the step 3 for the tables MoveLines and MoveTemplatesLines.


	<source lang="sql">		<source lang="sql">

Unknown user: Text replacement - "{{#customtitle" to "{{DISPLAYTITLE"

2020-02-11T17:11:25Z

Text replacement - "{{#customtitle" to "{{DISPLAYTITLE"

← Older revision		Revision as of 19:11, 11 February 2020
Line 1:		Line 1:
	{{~~#customtitle~~:Rename primary key field to Id}}		{{DISPLAYTITLE: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.		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.

Tintinux at 12:40, 9 May 2017

2017-05-09T12:40:05Z

← Older revision		Revision as of 14:40, 9 May 2017
Line 5:		Line 5:
	This should be possible in 4 steps, that is 4 Gestinux versions.		This should be possible in 4 steps, that is 4 Gestinux versions.

	# In the version 1.4 we have performed the step 3 for the tables Moves and MoveTemplates.		In the version 1.4 we have performed the step 3 for the tables Moves and MoveTemplates.

	# In the version 1.5 we ~~paln~~ to do the same for the tables MoveLines and MoveTemplatesLines.		In the version 1.5 we plan to do the same for the tables MoveLines and MoveTemplatesLines.

Tintinux at 12:39, 9 May 2017

2017-05-09T12:39:27Z

← Older revision		Revision as of 14:39, 9 May 2017
Line 5:		Line 5:
	This should be possible in 4 steps, that is 4 Gestinux versions.		This should be possible in 4 steps, that is 4 Gestinux versions.

	# In the version 1.4 we ~~perform~~ the step 3 for the tables Moves and MoveTemplates.		# In the version 1.4 we have performed the step 3 for the tables Moves and MoveTemplates.

			# In the version 1.5 we paln to do the same for the tables MoveLines and MoveTemplatesLines.


	<source lang="sql">		<source lang="sql">

Tintinux at 14:08, 18 October 2016

2016-10-18T14:08:47Z

← Older revision		Revision as of 16:08, 18 October 2016
Line 5:		Line 5:
	This should be possible in 4 steps, that is 4 Gestinux versions.		This should be possible in 4 steps, that is 4 Gestinux versions.

	# In the ~~trunk~~ version ~~(future~~ 1.4) we perform the step 3 for the tables Moves and MoveTemplates.		# In the version 1.4 we perform the step 3 for the tables Moves and MoveTemplates.
	~~# the version 1.2 is at the step 2 for these 2 tables, it works even after a database reorganization by the next version~~.

	<source lang="sql">		<source lang="sql">

Tintinux at 15:31, 8 March 2016

2016-03-08T15:31:47Z

← Older revision		Revision as of 17:31, 8 March 2016
Line 6:		Line 6:

	# In the trunk version (future 1.4) we perform the step 3 for the tables Moves and MoveTemplates.		# In the trunk version (future 1.4) we perform the step 3 for the tables Moves and MoveTemplates.
	# the version 1.2 is at the step 2 for these 2 tables, it ~~looks working~~ even after a database reorganization by the next version.		# the version 1.2 is at the step 2 for these 2 tables, it works even after a database reorganization by the next version.

	~~It is currently not working with PostGreSQL~~.

	<source lang="sql">		<source lang="sql">

Tintinux at 14:07, 24 April 2015

2015-04-24T14:07:06Z

← Older revision		Revision as of 16:07, 24 April 2015
Line 4:		Line 4:

	This should be possible in 4 steps, that is 4 Gestinux versions.		This should be possible in 4 steps, that is 4 Gestinux versions.
	It is currently ~~untested~~ with PostGreSQL.
			# In the trunk version (future 1.4) we perform the step 3 for the tables Moves and MoveTemplates.
			# the version 1.2 is at the step 2 for these 2 tables, it looks working even after a database reorganization by the next version.

			It is currently not working with PostGreSQL.

	<source lang="sql">		<source lang="sql">
Line 11:		Line 15:
	DROP TABLE IF EXISTS table1 ;		DROP TABLE IF EXISTS table1 ;

	-- ----------------------------------- V1		-- ----------------------------------- STEP 1 (INITIAL) --------------------------------------------------------------------
	--------------------------------------------------------------------
	CREATE TABLE table1 (		CREATE TABLE table1 (
	old_key INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,		old_key INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
Line 32:		Line 35:
	SELECT * FROM table1 JOIN table2 ON table2.old_key=table1.old_key;		SELECT * FROM table1 JOIN table2 ON table2.old_key=table1.old_key;

	-- ----------------------------------- V2		-- ----------------------------------- STEP 2 --------------------------------------------------------------------
	--------------------------------------------------------------------
	ALTER TABLE table1		ALTER TABLE table1
	ADD COLUMN Id INT NULL AFTER donnee;		ADD COLUMN Id INT NULL AFTER donnee;

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

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

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

	-- ~~GESTINUX (trunk, future 1.2) must be modified for~~ :		-- In this step :
	-- ~~Set~~ a field auto_inc only when it is created, never if already existing		-- We change a field auto_inc only when it is created, never if already existing (I.E coming from a next version)
	-- Create a primary key or a foreign key (specified in property) only when not 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;		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~~

	-- ----------------------------------- V3		-- After inserting a record, update the field which is not auto_inc, old_key when coming from STEP 3, or Id if STEP 3 was never used
	--------------------------------------------------------------------
	-- During upgrade, when coming from V4, the datamodule change the primary key (deleting and re-creating all foreign keys)		-- ----------------------------------- STEP 3 --------------------------------------------------------------------
	-- old_key is deleted from the table, and will thus not be created if coming from V4		-- During upgrade, when coming from STEP 4, 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 STEP 4

	ALTER TABLE table2 DROP FOREIGN KEY table2_C1;		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 CHANGE COLUMN old_key old_key INT(10) NULL DEFAULT NULL;
	ALTER TABLE table1 DROP PRIMARY KEY;		ALTER TABLE table1 DROP PRIMARY KEY;
	ALTER TABLE table1 DROP INDEX table1_K1; -- ~~désormais inutile~~		ALTER TABLE table1 DROP INDEX table1_K1; -- now useless
	ALTER TABLE table1 ADD PRIMARY KEY(Id);		ALTER TABLE table1 ADD PRIMARY KEY(Id);
	ALTER TABLE table1 CHANGE COLUMN Id Id INT(10) NOT NULL AUTO_INCREMENT;		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		ALTER TABLE table2 ADD FOREIGN KEY table2_C1 (old_key) REFERENCES table1 (Id) ON DELETE CASCADE; -- must keep the same name
	(Id) ON DELETE CASCADE; -- ~~garder impérativement le même nom~~
			-- When inserting we update the old_key after a new record is inserted, to allow backward compatibility
			-- Every reference to the old key must be removed at this step

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

	-- ----------------------------------- V4		-- ----------------------------------- STEP 4 (FINAL) -------------------------------------------------------------------
	--------------------------------------------------------------------
	~~-- finished !~~
	ALTER TABLE table1 DROP COLUMN old_key;		ALTER TABLE table1 DROP COLUMN old_key;
	SELECT * FROM table1 JOIN table2 ON table2.old_key=table1.Id;		SELECT * FROM table1 JOIN table2 ON table2.old_key=table1.Id;
	</source>		</source>

Tintinux at 16:57, 2 April 2015

2015-04-02T16:57:32Z

Tintinux at 17:23, 2 October 2014

2014-10-02T17:23:52Z

← Older revision		Revision as of 19:23, 2 October 2014
Line 3:		Line 3:
	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.		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~~, and~~ is currently ~~tested~~.		This should be possible in 4 steps, that is 4 Gestinux versions.
			It is currently untested with PostGreSQL.

	<source lang="sql">		<source lang="sql">

Tintinux at 17:23, 2 October 2014

2014-10-02T17:23:08Z

← Older revision		Revision as of 19:23, 2 October 2014
Line 4:		Line 4:

	This should be possible in 4 steps, that is 4 Gestinux versions, and is currently tested.		This should be possible in 4 steps, that is 4 Gestinux versions, and is currently tested.

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

← Older revision		Revision as of 18:57, 2 April 2015
Line 44:		Line 44:

	-- If upgrading from V3, the old primary key must not be re-created,		-- 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		-- auto_inc property must remain on Id, and foreign keys must not be changed

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


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

	-- ----------------------------------- V4		-- ----------------------------------- V3
	--------------------------------------------------------------------		--------------------------------------------------------------------
	-- During upgrade, when coming from V3, the datamodule change the primary		-- During upgrade, when coming from V4, the datamodule change the primary key (deleting and re-creating all foreign keys)
	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
	-- 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 table2 DROP FOREIGN KEY table2_C1;
	ALTER TABLE table1 CHANGE COLUMN old_key old_key INT(10) NULL DEFAULT NULL;		ALTER TABLE table1 CHANGE COLUMN old_key old_key INT(10) NULL DEFAULT NULL;
Line 72:		Line 68:
	(Id) ON DELETE CASCADE; -- garder impérativement le même nom		(Id) ON DELETE CASCADE; -- garder impérativement le même nom

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