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 +

Impressum / About Us