Project

General

Profile

1 6167 aaronmk
-- Restore original table names
2
DROP TABLE "plotMetadata"; -- name conflicts with PlotMetadata and has less data
3
ALTER TABLE "PlotMetadata" RENAME TO "plotMetadata";
4
ALTER TABLE "PlotObservations" RENAME TO "plotObservations";
5
6
-- Delete rows that do not satisfy foreign key constraints
7
DELETE FROM stems WHERE "PlotObsID" IN (
8
    SELECT stems."PlotObsID"
9
    FROM stems
10
    LEFT JOIN "plotObservations" ON "plotObservations"."PlotObsID" = stems."PlotObsID"
11
    WHERE "plotObservations"."PlotObsID" IS NULL
12
);
13
14 6162 aaronmk
-- Enable cascading deletes
15
16
CREATE INDEX ON "plotMetadata" (project_id);
17
ALTER TABLE "plotMetadata" ADD FOREIGN KEY (project_id) REFERENCES projects(project_id) ON UPDATE CASCADE ON DELETE CASCADE;
18
19
CREATE INDEX ON "plotObservations" ("PlotID");
20
ALTER TABLE "plotObservations" ADD FOREIGN KEY ("PlotID") REFERENCES "plotMetadata"("PlotID") ON UPDATE CASCADE ON DELETE CASCADE;
21
22
CREATE INDEX ON stems ("PlotObsID");
23
ALTER TABLE stems ADD FOREIGN KEY ("PlotObsID") REFERENCES "plotObservations"("PlotObsID") ON UPDATE CASCADE ON DELETE CASCADE;
24 6163 aaronmk
25
-- Remove private data that should not be publicly visible
26
DELETE FROM "plotMetadata" WHERE "AccessCode" = 1;
27 6166 aaronmk
28
-- Ensure globally unique column names when tables are joined
29
ALTER TABLE "lookup_MethodCode" RENAME "Description" TO "lookup_MethodCode_Description";