Schema Upgrades Across Revisions — Site
Personal tools

Skip to content. | Skip to navigation

 
You are here: Home → Bungeni Table of Contents → Appendix → Schema Upgrades Across Revisions
 

Document Actions

Previous: 5.9. Functional System Requirements  

5.10. Schema Upgrades Across Revisions

Introduction

This page documents a schema upgrade of a running bungeni instance.

In this example -- the bungeni revision r6991 will be upgrade to r7212 this involves 10 different schema changes.

Methodology

We start with the first schema change starting from r6991 and replay those changes on the data set from before r6991.

The bungeni schema is recorded in bungeni.models/schema.py - so we use the google code change history of schema.py to create an upgrade script.

These links provides a chronological list of changes made to the schema.

We are interested in all the changes greater than r6991.

  • r7003 - was simply a change in formatting of the source file - there were no schema changes
  • r7006 - changed the schema of some tables - the change log links to a couple of issues which document the schema changes - so we can generate an upgrade script for it :

    ALTER TABLE agenda_item_changes RENAME COLUMN date TO date_audit ;
    ALTER TABLE agenda_item_changes ALTER COLUMN date_audit TYPE timestamp without time zone ;
    ALTER TABLE agenda_item_changes ADD COLUMN date_active timestamp without time zone not null DEFAULT now() ;
    UPDATE agenda_item_changes SET date_active=date_audit ;

    ALTER TABLE attached_file_changes RENAME COLUMN date TO date_audit ;
    ALTER TABLE attached_file_changes ALTER COLUMN date_audit TYPE timestamp without time zone ;
    ALTER TABLE attached_file_changes ADD COLUMN date_active timestamp without time zone not null DEFAULT now() ;
    UPDATE attached_file_changes SET date_active=date_audit ;

    ALTER TABLE bill_changes RENAME COLUMN date TO date_audit ;
    ALTER TABLE bill_changes ALTER COLUMN date_audit TYPE timestamp without time zone ;
    ALTER TABLE bill_changes ADD COLUMN date_active timestamp without time zone not null DEFAULT now() ;
    UPDATE bill_changes SET date_active=date_audit ;

    ALTER TABLE motion_changes RENAME COLUMN date TO date_audit ;
    ALTER TABLE motion_changes ALTER COLUMN date_audit TYPE timestamp without time zone ;
    ALTER TABLE motion_changes ADD COLUMN date_active timestamp without time zone not null DEFAULT now() ;
    UPDATE motion_changes SET date_active=date_audit ;

    ALTER TABLE question_changes RENAME COLUMN date TO date_audit ;
    ALTER TABLE question_changes ALTER COLUMN date_audit TYPE timestamp without time zone ;
    ALTER TABLE question_changes ADD COLUMN date_active timestamp without time zone not null DEFAULT now() ;
    UPDATE question_changes SET date_active=date_audit ;

    ALTER TABLE tabled_document_changes RENAME COLUMN date TO date_audit ;
    ALTER TABLE tabled_document_changes ALTER COLUMN date_audit TYPE timestamp without time zone ;
    ALTER TABLE tabled_document_changes ADD COLUMN date_active timestamp without time zone not null DEFAULT now() ;
    UPDATE tabled_document_changes SET date_active=date_audit ;

    ALTER TABLE agenda_item_changes alter column date_audit set not null;
    ALTER TABLE attached_file_changes ALTER COLUMN  date_audit set not null ;
    ALTER TABLE bill_changes ALTER COLUMN  date_audit set not null;
    ALTER TABLE motion_changes ALTER COLUMN  date_audit set not null;
    ALTER TABLE question_changes ALTER COLUMN  date_audit set not null;
    ALTER TABLE tabled_document_changes ALTER COLUMN  date_audit set not null;
  • r7031 no significant schema change
  • r7109 - the issue log documents the changes - using that we can generate an upgrade script --

    ALTER TABLE items_schedule ALTER COLUMN item_id SET not null;
    ALTER TABLE items_schedule ALTER COLUMN sitting_id SET not null;
  • r7127 we skip for now - because the next change reverses the schema change made in this revision.
  • r7168 has significant changes - but no script is provided in the log - in this case we look at the schema diff to write our own script :

    ALTER TABLE reports ADD CONSTRAINT report_id_fkey FOREIGN KEY (report_id) REFERENCES parliamentary_items(parliamentary_item_id);
    ALTER TABLE reports DROP COLUMN body_text;
    ALTER TABLE reports DROP COLUMN note;
    ALTER TABLE reports DROP COLUMN language;
    ALTER TABLE sitting_reports DROP CONSTRAINT sitting_reports_pkey;
    ALTER TABLE sitting_reports DROP COLUMN sitting_report_id;
    ALTER TABLE sitting_reports ADD PRIMARY KEY (report_id, sitting_id);
  • r7194 - We again use the schema diff to generate the update script :

    ALTER TABLE reports DROP COLUMN user_id;
    ALTER TABLE reports DROP COLUMN created_date;
  • r7204 - the log documents the required changes :
    ALTER TABLE sessions ALTER COLUMN parliament_id SET not null;
  • r7212 is a more complex change - here, two columns province_id and region_id are being moved from the constituencies table into the parliamentary memberships table - so we need to migrate the data in these columns over into the parliamentary memberships table using the constituency_id as the key.

    First we add the new columns to the parliament_memberships table --

    ALTER TABLE parliament_memberships add column province_id integer;
    ALTER TABLE parliament_memberships add column region_id integer;

    Then we set the value correctly for the new columns using the constituency_id as the reference --

    update parliament_memberships 
    set province_id  = c.province_id , region_id = c.region_id
    from  constituencies c
    where parliament_memberships.constituency_id = c.constituency_id;

    Then we remove the migrated columns from the constituencies table --

    ALTER TABLE constituencies DROP COLUMN province_id;
    ALTER TABLE constituencies DROP COLUMN region_id;

    Finally we make the newly added columns in the parliament_memberships table into foreign keys --

    ALTER TABLE parliament_memberships add foreign key (province_id) references provinces(province_id);
    ALTER TABLE parliament_memberships add foreign key (region_id ) references regions(region_id);

The composite upgrade script is shown below :

--r7006
ALTER TABLE agenda_item_changes RENAME COLUMN date TO date_audit ;
ALTER TABLE agenda_item_changes ALTER COLUMN date_audit TYPE timestamp without time zone ;
ALTER TABLE agenda_item_changes ADD COLUMN date_active timestamp without time zone not null DEFAULT now() ;
UPDATE agenda_item_changes SET date_active=date_audit ;

ALTER TABLE attached_file_changes RENAME COLUMN date TO date_audit ;
ALTER TABLE attached_file_changes ALTER COLUMN date_audit TYPE timestamp without time zone ;
ALTER TABLE attached_file_changes ADD COLUMN date_active timestamp without time zone not null DEFAULT now() ;
UPDATE attached_file_changes SET date_active=date_audit ;

ALTER TABLE bill_changes RENAME COLUMN date TO date_audit ;
ALTER TABLE bill_changes ALTER COLUMN date_audit TYPE timestamp without time zone ;
ALTER TABLE bill_changes ADD COLUMN date_active timestamp without time zone not null DEFAULT now() ;
UPDATE bill_changes SET date_active=date_audit ;

ALTER TABLE motion_changes RENAME COLUMN date TO date_audit ;
ALTER TABLE motion_changes ALTER COLUMN date_audit TYPE timestamp without time zone ;
ALTER TABLE motion_changes ADD COLUMN date_active timestamp without time zone not null DEFAULT now() ;
UPDATE motion_changes SET date_active=date_audit ;

ALTER TABLE question_changes RENAME COLUMN date TO date_audit ;
ALTER TABLE question_changes ALTER COLUMN date_audit TYPE timestamp without time zone ;
ALTER TABLE question_changes ADD COLUMN date_active timestamp without time zone not null DEFAULT now() ;
UPDATE question_changes SET date_active=date_audit ;

ALTER TABLE tabled_document_changes RENAME COLUMN date TO date_audit ;
ALTER TABLE tabled_document_changes ALTER COLUMN date_audit TYPE timestamp without time zone ;
ALTER TABLE tabled_document_changes ADD COLUMN date_active timestamp without time zone not null DEFAULT now() ;
UPDATE tabled_document_changes SET date_active=date_audit ;



ALTER TABLE agenda_item_changes alter column date_audit set not null;
ALTER TABLE attached_file_changes ALTER COLUMN  date_audit set not null ;
ALTER TABLE bill_changes ALTER CO#summary One-sentence summary of this page.

= Introduction =

Add your content here.

= Details =

Add your content here.  Format your content with:
* Text in *bold* or _italic_
* Headings, paragraphs, and lists
* Automatic links to other wiki pages
LUMN  date_audit set not null;
ALTER TABLE motion_changes ALTER COLUMN  date_audit set not null;
ALTER TABLE question_changes ALTER COLUMN  date_audit set not null;
ALTER TABLE tabled_document_changes ALTER COLUMN  date_audit set not null;


--r7109

ALTER TABLE items_schedule ALTER COLUMN item_id SET not null;
ALTER TABLE items_schedule ALTER COLUMN sitting_id SET not null;

--r7127

--skipped

--r7168

ALTER TABLE reports ADD CONSTRAINT report_id_fkey FOREIGN KEY (report_id) REFERENCES parliamentary_items(parliamentary_item_id);
ALTER TABLE reports DROP COLUMN body_text;
ALTER TABLE reports DROP COLUMN note;
ALTER TABLE reports DROP COLUMN language;
ALTER TABLE sitting_reports DROP CONSTRAINT sitting_reports_pkey;
ALTER TABLE sitting_reports DROP COLUMN sitting_report_id;
ALTER TABLE sitting_reports ADD PRIMARY KEY (report_id, sitting_id);

--r7194

ALTER TABLE reports DROP COLUMN user_id;
ALTER TABLE reports DROP COLUMN created_date;

--r7204

ALTER TABLE sessions ALTER COLUMN parliament_id SET not null;

--r7212

ALTER TABLE parliament_memberships add column province_id integer;
ALTER TABLE parliament_memberships add column region_id integer;

update parliament_memberships  
set province_id  = c.province_id , region_id = c.region_id
from  constituencies c
where parliament_memberships.constituency_id = c.constituency_id;

ALTER TABLE constituencies DROP COLUMN province_id;
ALTER TABLE constituencies DROP COLUMN region_id;

ALTER TABLE parliament_memberships add foreign key (province_id) references provinces(province_id);
ALTER TABLE parliament_memberships add foreign key (region_id ) references regions(region_id);



 
Previous: 5.9. Functional System Requirements