User story (meta) #2253

Automatically update the SQL schema on upgrade from to 2.4 (extra column in policyInstances and groups tables)

Added by Jonathan CLARKE over 1 year ago. Updated 3 months ago.

Status:Released Start date:2012-02-09
Priority:2 Due date:
Assignee:Matthieu CERDA % Done:

100%
Category:-
Target version:Rudder - 2.4.0~alpha5

Description

After #2012 and #2143 we will need to add two new columns to SQL tables that historize group and PT names.

This will need to be implemented in the migration script, in particular:

  • Check if column for group type exists, and add if not
  • Check if column for PT name exists and if not:
    • Add the column
    • Run a script to fetch all existing lines in the table and add in the PT name, after searching for it in the /var/rudder/configuration-rules/policy-templates repository

dbMigration.sql (2 kB) Nicolas CHARLES, 2012-02-09 10:33

rudder-upgrade-database-2.3-2.4-PT-names.pl Magnifier (1.4 kB) Jonathan CLARKE, 2012-02-15 10:41

Related issues

follows Rudder - Architecture (meta) #2012: The historization of the group doesn't store if the group... Released 2011-11-08
follows Rudder - Architecture (meta) #2143: Historize human readable name of PT Released 2011-12-23

Associated revisions

Revision 4294b4cf
Added by Nicolas CHARLES over 1 year ago

Refs #2253 : commiting the DB migration script

Revision 08a84424
Added by Jonathan CLARKE over 1 year ago

Separate and rename db migration scripts for 2.3 to 2.4. Refs #2253

Revision 04541ab7
Added by Nicolas CHARLES over 1 year ago

Refs #2253 : change the init script for inital install

Revision 530dbf80
Added by Jonathan CLARKE over 1 year ago

Add packaging logic to install SQL table upgrades and run them on package upgrades. Refs #2253

Revision 0044ce75
Added by Matthieu CERDA over 1 year ago

Fixes #2253: The 2.3-2.4 SQL migration script now initializes the new PolicyTemplateHumanName to the policy template name by default

Revision 347a16e4
Added by Matthieu CERDA over 1 year ago

Refs #2253: Adapt the rudder-upgrade script to the new SQL 2.3-2.4 upgrade scripts

History

#1 Updated by Nicolas CHARLES over 1 year ago

  • File dbMigration.sql added

Here is the DB migration script (it will also be on github)
It creates two columns :
- PolicyTemplateHumanName in the table PolicyInstances
- GroupStatus in the table Groups

Thank you !

#2 Updated by Nicolas CHARLES over 1 year ago

  • File deleted (dbMigration.sql)

#3 Updated by Nicolas CHARLES over 1 year ago

#4 Updated by Nicolas CHARLES over 1 year ago

Nicolas CHARLES wrote:

Here is the DB migration script (it will also be on github)
It creates two columns :
- PolicyTemplateHumanName in the table PolicyInstances
- GroupStatus in the table Groups

Thank you !

And you should make a script that would set the value of the PolicyTemplateHumanName based on the value of the policyPackageName and policyPackageVersion (note that all these names might change)

#5 Updated by Jonathan CLARKE over 1 year ago

Nicolas CHARLES wrote:

Here is the DB migration script (it will also be on github)
It creates two columns :
- PolicyTemplateHumanName in the table PolicyInstances
- GroupStatus in the table Groups

Thank you !

Thanks, but we really need it in a git repo to be able to use it. The packaging always copies files from their original git location, instead of duplicating them.

#6 Updated by Jonathan CLARKE over 1 year ago

Matthieu, I'm handing this over to you. Attached is the embryon of a script that I started - it may or may not be useful to you.

#7 Updated by Matthieu CERDA over 1 year ago

  • Status changed from 2 to In progress

Leeeet's rock ! I'm on it.

#8 Updated by Matthieu CERDA over 1 year ago

  • Status changed from In progress to Pending technical review
  • % Done changed from 0 to 100

Applied in changeset commit:0044ce7557b15f5300900b7dc0522d571fc5a34a.

#9 Updated by Matthieu CERDA over 1 year ago

As discussed between NCH, JCL and MCE, a perl script iterating through the PTs to get their real name to store them in the table would have a too serious impact on the machine (DBI and XPath dependencies especially), so the idea has been forfeited. A plain SQL script initializing the PolicyTemplateHumanName to the PT name already present on the SQL table has been preferred.

#10 Updated by Jonathan CLARKE over 1 year ago

  • Subject changed from Migration script to add columns to SQL tables policyInstances and groups and fill them to Automatically update the SQL schema on upgrade from to 2.4 (igration script to add columns to SQL tables policyInstances and groups and fill them
  • Status changed from Pending technical review to Pending functional review

#11 Updated by Jonathan CLARKE over 1 year ago

  • Subject changed from Automatically update the SQL schema on upgrade from to 2.4 (igration script to add columns to SQL tables policyInstances and groups and fill them to Automatically update the SQL schema on upgrade from to 2.4 (extra column in policyInstances and groups tables)
  • Status changed from Pending functional review to Released

Latest modifications look good.

#12 Updated by Nicolas PERRON 3 months ago

  • Project changed from Rudder to packages
  • Category deleted (11)

Also available in: Atom PDF