2 CREATE TABLE "patchwork_bundlepatch" (
3 "id" SERIAL NOT NULL PRIMARY KEY,
4 "patch_id" INTEGER NOT NULL
5 REFERENCES "patchwork_patch" ("id") DEFERRABLE INITIALLY DEFERRED,
6 "bundle_id" INTEGER NOT NULL
7 REFERENCES "patchwork_bundle" ("id") DEFERRABLE INITIALLY DEFERRED,
8 "order" SERIAL NOT NULL,
9 UNIQUE ("bundle_id", "patch_id")
12 -- we 'INSERT INTO ... SELECT' (rather than renaming and adding the order
13 -- column) here so that we can order by date
14 INSERT INTO patchwork_bundlepatch (id, patch_id, bundle_id)
15 SELECT patchwork_bundle_patches.id, patch_id, bundle_id
16 FROM patchwork_bundle_patches
17 INNER JOIN patchwork_patch
18 ON patchwork_patch.id = patchwork_bundle_patches.patch_id
19 ORDER BY bundle_id, patchwork_patch.date;
23 ALTER TABLE patchwork_bundlepatch
24 ALTER COLUMN "order" TYPE INTEGER;
26 -- normalise ordering: order should start with 1 in each bundle
27 UPDATE patchwork_bundlepatch SET "order" = 1 + "order" -
28 (SELECT min("order") FROM patchwork_bundlepatch AS p2
29 WHERE p2.bundle_id = patchwork_bundlepatch.bundle_id);
31 SELECT * FROM patchwork_bundlepatch;