jbe@26: BEGIN;
jbe@26: 
jbe@26: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@27:   SELECT * FROM (VALUES ('incomplete_update_from_beta18_to_beta19', NULL, NULL, NULL))
jbe@26:   AS "subquery"("string", "major", "minor", "revision");
jbe@26: 
jbe@26: ALTER TABLE "issue" RENAME COLUMN "latest_snapshot_event" TO "tmp";
jbe@26: ALTER TABLE "direct_population_snapshot"     RENAME COLUMN "event" TO "tmp";
jbe@26: ALTER TABLE "delegating_population_snapshot" RENAME COLUMN "event" TO "tmp";
jbe@26: ALTER TABLE "direct_interest_snapshot"       RENAME COLUMN "event" TO "tmp";
jbe@26: ALTER TABLE "delegating_interest_snapshot"   RENAME COLUMN "event" TO "tmp";
jbe@26: ALTER TABLE "direct_supporter_snapshot"      RENAME COLUMN "event" TO "tmp";
jbe@26: 
jbe@26: ALTER TABLE "issue" ADD COLUMN "latest_snapshot_event" TEXT;
jbe@26: ALTER TABLE "direct_population_snapshot"     ADD COLUMN "event" TEXT;
jbe@26: ALTER TABLE "delegating_population_snapshot" ADD COLUMN "event" TEXT;
jbe@26: ALTER TABLE "direct_interest_snapshot"       ADD COLUMN "event" TEXT;
jbe@26: ALTER TABLE "delegating_interest_snapshot"   ADD COLUMN "event" TEXT;
jbe@26: ALTER TABLE "direct_supporter_snapshot"      ADD COLUMN "event" TEXT;
jbe@26: 
jbe@26: ALTER TABLE "issue" ADD COLUMN "admission_time"    INTERVAL;
jbe@26: ALTER TABLE "issue" ADD COLUMN "discussion_time"   INTERVAL;
jbe@26: ALTER TABLE "issue" ADD COLUMN "verification_time" INTERVAL;
jbe@26: ALTER TABLE "issue" ADD COLUMN "voting_time"       INTERVAL;
jbe@26: 
jbe@26: UPDATE "issue" SET "latest_snapshot_event"  = "tmp";
jbe@26: UPDATE "direct_population_snapshot"     SET "event" = "tmp";
jbe@26: UPDATE "delegating_population_snapshot" SET "event" = "tmp";
jbe@26: UPDATE "direct_interest_snapshot"       SET "event" = "tmp";
jbe@26: UPDATE "delegating_interest_snapshot"   SET "event" = "tmp";
jbe@26: UPDATE "direct_supporter_snapshot"      SET "event" = "tmp";
jbe@26: 
jbe@26: UPDATE "issue" SET "latest_snapshot_event" = 'full_freeze' WHERE "latest_snapshot_event" = 'start_of_voting';
jbe@26: UPDATE "direct_population_snapshot"     SET "event" = 'full_freeze' WHERE "event" = 'start_of_voting';
jbe@26: UPDATE "delegating_population_snapshot" SET "event" = 'full_freeze' WHERE "event" = 'start_of_voting';
jbe@26: UPDATE "direct_interest_snapshot"       SET "event" = 'full_freeze' WHERE "event" = 'start_of_voting';
jbe@26: UPDATE "delegating_interest_snapshot"   SET "event" = 'full_freeze' WHERE "event" = 'start_of_voting';
jbe@26: UPDATE "direct_supporter_snapshot"      SET "event" = 'full_freeze' WHERE "event" = 'start_of_voting';
jbe@26: 
jbe@26: UPDATE "issue" SET
jbe@26:   "admission_time"    = "policy"."admission_time",
jbe@26:   "discussion_time"   = "policy"."discussion_time",
jbe@26:   "verification_time" = "policy"."verification_time",
jbe@26:   "voting_time"       = "policy"."voting_time"
jbe@26:   FROM "policy" WHERE "issue"."policy_id" = "policy"."id";
jbe@26: 
jbe@26: -- remove "tmp" columns indirectly
jbe@26: DROP TYPE "snapshot_event" CASCADE;
jbe@26: 
jbe@26: COMMIT;
jbe@26: 
jbe@26: -- Complete the update as follows:
jbe@26: -- =========================================
jbe@26: -- pg_dump --disable-triggers --data-only DATABASE_NAME > tmp.sql
jbe@26: -- dropdb DATABASE_NAME
jbe@26: -- createdb DATABASE_NAME
jbe@26: -- psql -v ON_ERROR_STOP=1 -f core.sql DATABASE_NAME
jbe@26: -- psql -v ON_ERROR_STOP=1 -f tmp.sql DATABASE_NAME
jbe@26: -- rm tmp.sql
jbe@26: