summaryrefslogtreecommitdiffstats
path: root/lib/sql/migration/005-bundle-patch-ordering.sql
blob: 238e37bcac11cca635bb86c4fd290a73c6a3adad (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
BEGIN;
CREATE TABLE "patchwork_bundlepatch" (                                          
    "id" SERIAL NOT NULL PRIMARY KEY,
    "patch_id" INTEGER NOT NULL
        REFERENCES "patchwork_patch" ("id") DEFERRABLE INITIALLY DEFERRED,
    "bundle_id" INTEGER NOT NULL
        REFERENCES "patchwork_bundle" ("id") DEFERRABLE INITIALLY DEFERRED,
    "order" SERIAL NOT NULL,
    UNIQUE ("bundle_id", "patch_id")
);

-- we 'INSERT INTO ... SELECT' (rather than renaming and adding the order
-- column) here so that we can order by date
INSERT INTO patchwork_bundlepatch (id, patch_id, bundle_id)
    SELECT patchwork_bundle_patches.id, patch_id, bundle_id
        FROM patchwork_bundle_patches
        INNER JOIN patchwork_patch
            ON patchwork_patch.id = patchwork_bundle_patches.patch_id
        ORDER BY bundle_id, patchwork_patch.date;
COMMIT;

BEGIN;
ALTER TABLE patchwork_bundlepatch
    ALTER COLUMN "order" TYPE INTEGER;

-- normalise ordering: order should start with 1 in each bundle
UPDATE patchwork_bundlepatch SET "order" = 1 + "order" -
	(SELECT min("order") FROM patchwork_bundlepatch AS p2
		WHERE p2.bundle_id = patchwork_bundlepatch.bundle_id);

SELECT * FROM patchwork_bundlepatch;
COMMIT;