| -- Upgrade: schema_version 4 to 5 |
| -- |
| |
| ALTER TABLE accounts ADD contact_filed_on TIMESTAMP WITH TIME ZONE; |
| UPDATE accounts |
| SET contact_filed_on = (SELECT MAX(accepted_on) |
| FROM account_agreements g |
| WHERE g.account_id = accounts.account_id |
| AND g.status <> 'R') |
| WHERE full_name IS NOT NULL |
| AND full_name <> '' |
| AND preferred_email IS NOT null |
| AND preferred_email <> '' |
| AND contact_address IS NOT NULL |
| AND contact_address <> '' |
| AND EXISTS (SELECT 1 FROM account_agreements g |
| WHERE g.account_id = accounts.account_id |
| AND g.status <> 'R'); |
| UPDATE accounts SET contact_filed_on = registered_on |
| WHERE full_name IS NOT NULL |
| AND full_name <> '' |
| AND preferred_email IS NOT null |
| AND preferred_email <> '' |
| AND contact_address IS NOT NULL |
| AND contact_address <> '' |
| AND contact_filed_on IS NULL; |
| |
| ALTER TABLE system_config ADD contact_store_url VARCHAR(255); |
| ALTER TABLE system_config ADD contact_store_appsec VARCHAR(255); |
| |
| UPDATE schema_version SET version_nbr = 5; |