63 lines
No EOL
1.8 KiB
SQL
63 lines
No EOL
1.8 KiB
SQL
CREATE TABLE IF NOT EXISTS "device" (
|
|
"device_id" UUID NOT NULL DEFAULT uuidv4(),
|
|
"device_eui" VARCHAR(255) NOT NULL UNIQUE,
|
|
"site_id" UUID,
|
|
PRIMARY KEY("device_id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "site" (
|
|
"site_id" UUID NOT NULL DEFAULT uuidv4(),
|
|
"pce" VARCHAR(255) NOT NULL UNIQUE,
|
|
"address_1" VARCHAR(255) NOT NULL,
|
|
"address_2" VARCHAR(255),
|
|
"postal_code" VARCHAR(255) NOT NULL,
|
|
"city" VARCHAR(255) NOT NULL,
|
|
PRIMARY KEY("site_id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "user" (
|
|
"user_id" UUID NOT NULL DEFAULT uuidv4(),
|
|
"name" VARCHAR(255) NOT NULL,
|
|
"first_name" VARCHAR(255) NOT NULL,
|
|
"email" VARCHAR(255) NOT NULL UNIQUE,
|
|
"password_hash" VARCHAR(255),
|
|
"user_type_id" UUID NOT NULL,
|
|
PRIMARY KEY("user_id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "reading" (
|
|
"reading_id" UUID NOT NULL DEFAULT uuidv7(),
|
|
"device_id" UUID NOT NULL,
|
|
"date" TIMESTAMP NOT NULL,
|
|
"pulses" INTEGER NOT NULL,
|
|
PRIMARY KEY("reading_id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "subscription" (
|
|
"subscription_id" UUID NOT NULL DEFAULT uuidv4(),
|
|
"site_id" UUID NOT NULL,
|
|
"user_id" UUID NOT NULL,
|
|
PRIMARY KEY("subscription_id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "user_type" (
|
|
"user_type_id" UUID NOT NULL DEFAULT uuidv4(),
|
|
"label" VARCHAR(255) NOT NULL,
|
|
PRIMARY KEY("user_type_id")
|
|
);
|
|
|
|
ALTER TABLE "device"
|
|
ADD FOREIGN KEY("site_id") REFERENCES "site"("site_id")
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION;
|
|
ALTER TABLE "subscription"
|
|
ADD FOREIGN KEY("site_id") REFERENCES "site"("site_id")
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION;
|
|
ALTER TABLE "subscription"
|
|
ADD FOREIGN KEY("user_id") REFERENCES "user"("user_id")
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION;
|
|
ALTER TABLE "reading"
|
|
ADD FOREIGN KEY("device_id") REFERENCES "device"("device_id")
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION;
|
|
ALTER TABLE "user"
|
|
ADD FOREIGN KEY("user_type_id") REFERENCES "user_type"("user_type_id")
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION; |