liquid_feedback_core
changeset 118:7d6c5032262f
Reconstructing old events in v1.4.0_rc1 update script
author | jbe |
---|---|
date | Mon Mar 07 13:54:43 2011 +0100 (2011-03-07) |
parents | a5d39efbfe5b |
children | d6a145a5c9d3 |
files | update/core-update.v1.3.1-v1.4.0_rc1.sql |
line diff
1.1 --- a/update/core-update.v1.3.1-v1.4.0_rc1.sql Mon Mar 07 11:42:46 2011 +0100 1.2 +++ b/update/core-update.v1.3.1-v1.4.0_rc1.sql Mon Mar 07 13:54:43 2011 +0100 1.3 @@ -2075,3 +2075,152 @@ 1.4 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ); 1.5 1.6 1.7 +-- Filling of "event" table with old (reconstructed) events: 1.8 + 1.9 +DELETE FROM "event"; 1.10 +SELECT setval('event_id_seq', 1, false); 1.11 + 1.12 +INSERT INTO "event" 1.13 + ( "occurrence", "event", "member_id", "issue_id", "state", 1.14 + "initiative_id", "draft_id", "suggestion_id" ) 1.15 + SELECT * FROM ( 1.16 + SELECT * FROM ( 1.17 + SELECT DISTINCT ON ("initiative"."id") 1.18 + "timeline"."occurrence", 1.19 + CASE WHEN "issue_creation"."issue_id" NOTNULL THEN 1.20 + 'initiative_created_in_new_issue'::"event_type" 1.21 + ELSE 1.22 + 'initiative_created_in_existing_issue'::"event_type" 1.23 + END, 1.24 + "draft"."author_id", 1.25 + "issue"."id", 1.26 + CASE 1.27 + WHEN "timeline"."occurrence" < "issue"."accepted" THEN 1.28 + 'admission'::"issue_state" 1.29 + WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN 1.30 + 'discussion'::"issue_state" 1.31 + ELSE 1.32 + 'verification'::"issue_state" 1.33 + END, 1.34 + "initiative"."id", 1.35 + "draft"."id", 1.36 + NULL::INT8 1.37 + FROM "timeline" 1.38 + JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id" 1.39 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.40 + LEFT JOIN "timeline" AS "issue_creation" 1.41 + ON "initiative"."issue_id" = "issue_creation"."issue_id" 1.42 + AND "issue_creation"."event" = 'issue_created' 1.43 + AND "timeline"."occurrence" = "issue_creation"."occurrence" 1.44 + JOIN "draft" 1.45 + ON "initiative"."id" = "draft"."initiative_id" 1.46 + WHERE "timeline"."event" = 'initiative_created' 1.47 + ORDER BY "initiative"."id", "draft"."id" 1.48 + ) AS "subquery" -- NOTE: subquery needed due to DISTINCT/ORDER 1.49 + UNION ALL 1.50 + SELECT 1.51 + "timeline"."occurrence", 1.52 + 'issue_state_changed'::"event_type", 1.53 + NULL, 1.54 + "issue"."id", 1.55 + CASE 1.56 + WHEN "timeline"."event" IN ( 1.57 + 'issue_canceled', 1.58 + 'issue_finished_without_voting', 1.59 + 'issue_finished_after_voting' 1.60 + ) THEN 1.61 + "issue"."state" 1.62 + WHEN "timeline"."event" = 'issue_accepted' THEN 1.63 + 'discussion'::"issue_state" 1.64 + WHEN "timeline"."event" = 'issue_half_frozen' THEN 1.65 + 'verification'::"issue_state" 1.66 + WHEN "timeline"."event" = 'issue_voting_started' THEN 1.67 + 'voting'::"issue_state" 1.68 + END, 1.69 + NULL, 1.70 + NULL, 1.71 + NULL 1.72 + FROM "timeline" 1.73 + JOIN "issue" ON "timeline"."issue_id" = "issue"."id" 1.74 + WHERE "timeline"."event" IN ( 1.75 + 'issue_canceled', 1.76 + 'issue_accepted', 1.77 + 'issue_half_frozen', 1.78 + 'issue_finished_without_voting', 1.79 + 'issue_voting_started', 1.80 + 'issue_finished_after_voting' ) 1.81 + UNION ALL 1.82 + SELECT 1.83 + "timeline"."occurrence", 1.84 + 'initiative_revoked'::"event_type", 1.85 + "initiative"."revoked_by_member_id", 1.86 + "issue"."id", 1.87 + CASE 1.88 + WHEN "timeline"."occurrence" < "issue"."accepted" THEN 1.89 + 'admission'::"issue_state" 1.90 + WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN 1.91 + 'discussion'::"issue_state" 1.92 + ELSE 1.93 + 'verification'::"issue_state" 1.94 + END, 1.95 + "initiative"."id", 1.96 + "current_draft"."id", 1.97 + NULL 1.98 + FROM "timeline" 1.99 + JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id" 1.100 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.101 + JOIN "current_draft" ON "initiative"."id" = "current_draft"."initiative_id" 1.102 + WHERE "timeline"."event" = 'initiative_revoked' 1.103 + UNION ALL 1.104 + SELECT 1.105 + "timeline"."occurrence", 1.106 + 'new_draft_created'::"event_type", 1.107 + "draft"."author_id", 1.108 + "issue"."id", 1.109 + CASE 1.110 + WHEN "timeline"."occurrence" < "issue"."accepted" THEN 1.111 + 'admission'::"issue_state" 1.112 + WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN 1.113 + 'discussion'::"issue_state" 1.114 + ELSE 1.115 + 'verification'::"issue_state" 1.116 + END, 1.117 + "initiative"."id", 1.118 + "draft"."id", 1.119 + NULL 1.120 + FROM "timeline" 1.121 + JOIN "draft" ON "timeline"."draft_id" = "draft"."id" 1.122 + JOIN "initiative" ON "draft"."initiative_id" = "initiative"."id" 1.123 + JOIN "issue" ON "initiative"."issue_id" = "issue"."id" 1.124 + LEFT JOIN "timeline" AS "initiative_creation" 1.125 + ON "initiative"."id" = "initiative_creation"."initiative_id" 1.126 + AND "initiative_creation"."event" = 'initiative_created' 1.127 + AND "timeline"."occurrence" = "initiative_creation"."occurrence" 1.128 + WHERE "timeline"."event" = 'draft_created' 1.129 + AND "initiative_creation"."initiative_id" ISNULL 1.130 + UNION ALL 1.131 + SELECT 1.132 + "timeline"."occurrence", 1.133 + 'suggestion_created'::"event_type", 1.134 + "suggestion"."author_id", 1.135 + "issue"."id", 1.136 + CASE 1.137 + WHEN "timeline"."occurrence" < "issue"."accepted" THEN 1.138 + 'admission'::"issue_state" 1.139 + WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN 1.140 + 'discussion'::"issue_state" 1.141 + ELSE 1.142 + 'verification'::"issue_state" 1.143 + END, 1.144 + "initiative"."id", 1.145 + NULL, 1.146 + "suggestion"."id" 1.147 + FROM "timeline" 1.148 + JOIN "suggestion" ON "timeline"."suggestion_id" = "suggestion"."id" 1.149 + JOIN "initiative" ON "suggestion"."initiative_id" = "initiative"."id" 1.150 + JOIN "issue" ON "initiative"."issue_id" = "issue"."id" 1.151 + WHERE "timeline"."event" = 'suggestion_created' 1.152 + ) AS "subquery" 1.153 + ORDER BY "occurrence"; 1.154 + 1.155 +