RuD8PMDq

1CREATE ROLE "plantlife_owner" WITH
2	LOGIN
3	NOSUPERUSER
4	NOCREATEDB
5	NOCREATEROLE
6	INHERIT
7	NOREPLICATION
8	CONNECTION LIMIT -1
9	PASSWORD 'foobar';
10
11CREATE DATABASE "plantlife" WITH
12    OWNER = "plantlife_owner"
13    ENCODING = 'UTF8'
14    CONNECTION LIMIT = -1;
15
16CREATE TYPE "Light" AS ENUM
17  ( 'Full sun'
18  , 'Indirect sun'
19  , 'Partial sun'
20  , 'Partial shade'
21  , 'Full shade'
22  , 'No light'
23  );
24
25CREATE TABLE IF NOT EXISTS "LightPreference"
26  ( "id" SERIAL PRIMARY KEY
27  , "brightest" "Light" NOT NULL
28  , "darkest" "Light" NOT NULL
29  );
30
31CREATE TABLE IF NOT EXISTS "Location"
32  ( "id" SERIAL PRIMARY KEY
33  , "name" TEXT NOT NULL
34  );
35
36CREATE TABLE IF NOT EXISTS "Family"
37  ( "id" SERIAL PRIMARY KEY
38  , "name" TEXT NOT NULL
39  , "description" TEXT NULL
40  );
41
42CREATE TABLE IF NOT EXISTS "Species"
43  ( "id" SERIAL PRIMARY KEY
44  , "name" TEXT NOT NULL
45  , "description" TEXT NOT NULL
46  , "familyID" INT NOT NULL REFERENCES "Family" ("id")
47  );
48
49CREATE TABLE IF NOT EXISTS "Plant"
50  ( "id" SERIAL PRIMARY KEY
51  , "name" TEXT NOT NULL
52  , "description" TEXT
53  , "wateringInterval" INTERVAL SECOND
54  , "fertilizationInterval" INTERVAL SECOND
55  , "mistingInterval" INTERVAL SECOND
56  , "preferredLightID" INT REFERENCES "LightPreference" ("id")
57  , "locationID" INT REFERENCES "Location" ("id")
58  , "speciesID" INT REFERENCES "Species" ("id")
59  );
60
61CREATE TYPE "EventType" AS ENUM
62  ( 'Acquired'
63  , 'Created'
64  , 'Deleted'
65  , 'Death'
66  , 'Misting'
67  , 'Repotting'
68  , 'Treatment'
69  , 'Watering'
70  , 'Fertilization'
71  , 'Disease'
72  , 'Inspection'
73  );
74
75CREATE TABLE IF NOT EXISTS "Event"
76  ( "id" BIGSERIAL PRIMARY KEY
77  , "plantID" INT NOT NULL REFERENCES "Plant" ("id")
78  , "eventType" "EventType" NOT NULL
79  , "time" TIMESTAMP(0) WITH TIME ZONE NOT NULL DEFAULT NOW()
80  , UNIQUE ("plantID", "eventType")
81  );
82
83CREATE TABLE IF NOT EXISTS "EventNote"
84  ( "id" BIGSERIAL PRIMARY KEY
85  , "eventID" INT NOT NULL REFERENCES "Event" ("id")
86  , "note" TEXT NOT NULL
87  );
88
89CREATE TABLE IF NOT EXISTS "Image"
90  ( "id" SERIAL PRIMARY KEY
91  , "plantID" INT NOT NULL REFERENCES "Plant" ("id")
92  , "imageBase64" TEXT NOT NULL
93  , "createdAt" TIMESTAMP(0) WITH TIME ZONE NOT NULL DEFAULT NOW()
94  );
95
96CREATE TABLE IF NOT EXISTS "ProfilePicture"
97  ( "id" SERIAL PRIMARY KEY
98  , "plantID" INT NOT NULL UNIQUE REFERENCES "Plant" ("id")
99  , "imageID" INT NOT NULL REFERENCES "Image" ("id")
100  );
101