liquid_feedback_core: 58451b5565ae update/prepare-beta18-beta19.sql

liquid_feedback_core

view update/prepare-beta18-beta19.sql @ 97:58451b5565ae

Introduced (organizational) units

- New table "unit"
- Each "area" refers to one unit
- "invite_code"s are valid for one or many units
- New table "privilege" stores "voting_right" for units
- Delegation scope 'global' is replaced by delegation scope 'unit'
- View "global_delegation" is replaced by "unit_delegation"
- Removed helper view "active_delegation"
- Checking that members have "voting_right" in several views and functions
- New view "unit_member_count" to update "member_count" column of new unit table
- New argument list for function "delegation_chain"(...) containing "unit_id"
- Renamed column "member_active" to "member_valid" in "delegation_chain_row"
- Modified demo.sql and init.sql to support units

author	jbe
date	Tue Dec 07 00:04:44 2010 +0100 (2010-12-07)
parents	77ac81cbe5c0
children

line source

1 BEGIN;

3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

4 SELECT * FROM (VALUES ('incomplete_update_from_beta18_to_beta19', NULL, NULL, NULL))

5 AS "subquery"("string", "major", "minor", "revision");

7 ALTER TABLE "issue" RENAME COLUMN "latest_snapshot_event" TO "tmp";

8 ALTER TABLE "direct_population_snapshot" RENAME COLUMN "event" TO "tmp";

9 ALTER TABLE "delegating_population_snapshot" RENAME COLUMN "event" TO "tmp";

10 ALTER TABLE "direct_interest_snapshot" RENAME COLUMN "event" TO "tmp";

11 ALTER TABLE "delegating_interest_snapshot" RENAME COLUMN "event" TO "tmp";

12 ALTER TABLE "direct_supporter_snapshot" RENAME COLUMN "event" TO "tmp";

14 ALTER TABLE "issue" ADD COLUMN "latest_snapshot_event" TEXT;

15 ALTER TABLE "direct_population_snapshot" ADD COLUMN "event" TEXT;

16 ALTER TABLE "delegating_population_snapshot" ADD COLUMN "event" TEXT;

17 ALTER TABLE "direct_interest_snapshot" ADD COLUMN "event" TEXT;

18 ALTER TABLE "delegating_interest_snapshot" ADD COLUMN "event" TEXT;

19 ALTER TABLE "direct_supporter_snapshot" ADD COLUMN "event" TEXT;

21 ALTER TABLE "issue" ADD COLUMN "admission_time" INTERVAL;

22 ALTER TABLE "issue" ADD COLUMN "discussion_time" INTERVAL;

23 ALTER TABLE "issue" ADD COLUMN "verification_time" INTERVAL;

24 ALTER TABLE "issue" ADD COLUMN "voting_time" INTERVAL;

26 UPDATE "issue" SET "latest_snapshot_event" = "tmp";

27 UPDATE "direct_population_snapshot" SET "event" = "tmp";

28 UPDATE "delegating_population_snapshot" SET "event" = "tmp";

29 UPDATE "direct_interest_snapshot" SET "event" = "tmp";

30 UPDATE "delegating_interest_snapshot" SET "event" = "tmp";

31 UPDATE "direct_supporter_snapshot" SET "event" = "tmp";

33 UPDATE "issue" SET "latest_snapshot_event" = 'full_freeze' WHERE "latest_snapshot_event" = 'start_of_voting';

34 UPDATE "direct_population_snapshot" SET "event" = 'full_freeze' WHERE "event" = 'start_of_voting';

35 UPDATE "delegating_population_snapshot" SET "event" = 'full_freeze' WHERE "event" = 'start_of_voting';

36 UPDATE "direct_interest_snapshot" SET "event" = 'full_freeze' WHERE "event" = 'start_of_voting';

37 UPDATE "delegating_interest_snapshot" SET "event" = 'full_freeze' WHERE "event" = 'start_of_voting';

38 UPDATE "direct_supporter_snapshot" SET "event" = 'full_freeze' WHERE "event" = 'start_of_voting';

40 UPDATE "issue" SET

41 "admission_time" = "policy"."admission_time",

42 "discussion_time" = "policy"."discussion_time",

43 "verification_time" = "policy"."verification_time",

44 "voting_time" = "policy"."voting_time"

45 FROM "policy" WHERE "issue"."policy_id" = "policy"."id";

47 -- remove "tmp" columns indirectly

48 DROP TYPE "snapshot_event" CASCADE;

50 COMMIT;

52 -- Complete the update as follows:

53 -- =========================================

54 -- pg_dump --disable-triggers --data-only DATABASE_NAME > tmp.sql

55 -- dropdb DATABASE_NAME

56 -- createdb DATABASE_NAME

57 -- psql -v ON_ERROR_STOP=1 -f core.sql DATABASE_NAME

58 -- psql -v ON_ERROR_STOP=1 -f tmp.sql DATABASE_NAME

59 -- rm tmp.sql