Project

General

Profile

1
-- Restore original table names
2
ALTER TABLE "PlotMetadata" RENAME TO "plotMetadata";
3
ALTER TABLE "PlotObservations" RENAME TO "plotObservations";
4

    
5
-- Delete rows that do not satisfy foreign key constraints
6
DELETE FROM stems WHERE "plotobs_id" IN (
7
    SELECT stems."plotobs_id"
8
    FROM stems
9
    LEFT JOIN "plotObservations" ON "plotObservations"."PlotObsID" = stems."plotobs_id"
10
    WHERE "plotObservations"."PlotObsID" IS NULL
11
);
12

    
13
-- Enable cascading deletes
14

    
15
CREATE INDEX ON "plotMetadata" (project_id);
16
ALTER TABLE "plotMetadata" ADD FOREIGN KEY (project_id) REFERENCES projects(project_id) ON UPDATE CASCADE ON DELETE CASCADE;
17

    
18
CREATE INDEX ON "plotObservations" ("PlotID");
19
ALTER TABLE "plotObservations" ADD FOREIGN KEY ("PlotID") REFERENCES "plotMetadata"("PlotID") ON UPDATE CASCADE ON DELETE CASCADE;
20

    
21
CREATE INDEX ON stems ("plotobs_id");
22
ALTER TABLE stems ADD FOREIGN KEY ("plotobs_id") REFERENCES "plotObservations"("PlotObsID") ON UPDATE CASCADE ON DELETE CASCADE;
23

    
24
-- Remove private data that should not be publicly visible
25
DELETE FROM "plotMetadata" WHERE "AccessCode" = 1;
26

    
27
-- Ensure globally unique column names when tables are joined
28
ALTER TABLE "lookup_MethodCode" RENAME "Description" TO "lookup_MethodCode_Description";
(6-6/10)