From c3522a8d3b0236b3c9358db4160182cc211f8069 Mon Sep 17 00:00:00 2001
From: Santo Cariotti <santo@dcariotti.me>
Date: Wed, 17 Mar 2021 14:14:36 +0100
Subject: fix(schema): add on delete referenceses

---
 schema.sql | 20 ++++++++++----------
 1 file changed, 10 insertions(+), 10 deletions(-)

(limited to 'schema.sql')

diff --git a/schema.sql b/schema.sql
index 9daa7f9..ef066d7 100644
--- a/schema.sql
+++ b/schema.sql
@@ -1,9 +1,9 @@
 CREATE TABLE "repository" (
     id uuid PRIMARY KEY NOT NULL,
     url varchar(255) UNIQUE NOT NULL,
-    created_at timestamp NOT NULL,
-    updated_at timestamp NOT NULL,
-    uploader_ip varchar(15) NOT NULL
+    created_at timestamp NOT NULL DEFAULT NOW(),
+    updated_at timestamp NOT NULL DEFAULT NOW(),
+    uploader_ip varchar(21) NOT NULL
 );
 
 CREATE TABLE "email"(
@@ -13,19 +13,19 @@ CREATE TABLE "email"(
 
 CREATE TABLE "commit" (
     hash varchar(40) PRIMARY KEY NOT NULL,
-    tree varchar(40) REFERENCES commit(hash) NULL,
+    tree varchar(40) REFERENCES commit(hash) ON DELETE CASCADE NULL,
     text text NOT NULL,
-    date timestamp NOT NULL,
-    author_email varchar(120) REFERENCES email(email) NOT NULL,
+    date timestamptz NOT NULL,
+    author_email varchar(120) REFERENCES email(email) ON DELETE NO ACTION NOT NULL,
     author_name varchar(120) NOT NULL,
-    committer_email varchar(120) REFERENCES email(email) NOT NULL,
+    committer_email varchar(120) REFERENCES email(email) ON DELETE NO ACTION NOT NULL,
     committer_name varchar(120) NOT NULL,
-    repository_url varchar(256) REFERENCES repository(url) NOT NULL 
+    repository_url varchar(256) REFERENCES repository(url) ON DELETE CASCADE NOT NULL
 );
 
 CREATE TABLE "branch" (
     id uuid PRIMARY KEY NOT NULL,
     name varchar(120) NOT NULL,
-    repository_id uuid REFERENCES repository(id) NOT NULL,
-    head varchar(40) REFERENCES commit(hash) NULL
+    repository_id uuid REFERENCES repository(id) ON DELETE CASCADE NOT NULL,
+    head varchar(40) REFERENCES commit(hash) ON DELETE SET NULL NULL
 );
-- 
cgit v1.2.3-18-g5258

