From 0e615cf95a4fe198eed9ce125804767d1f38d80f Mon Sep 17 00:00:00 2001 From: franck cuny Date: Mon, 24 Jan 2011 21:32:25 +0100 Subject: add sql schema for various version --- sql/PostgreSQL/deploy/2/001-auto.sql | 64 ++++++++++++++++++++++++++++++++++++ 1 file changed, 64 insertions(+) create mode 100644 sql/PostgreSQL/deploy/2/001-auto.sql (limited to 'sql/PostgreSQL/deploy/2/001-auto.sql') diff --git a/sql/PostgreSQL/deploy/2/001-auto.sql b/sql/PostgreSQL/deploy/2/001-auto.sql new file mode 100644 index 0000000..da37dac --- /dev/null +++ b/sql/PostgreSQL/deploy/2/001-auto.sql @@ -0,0 +1,64 @@ +-- +-- Created by SQL::Translator::Producer::PostgreSQL +-- Created on Mon Jan 24 21:26:41 2011 +-- +; +-- +-- Table: project +-- +CREATE TABLE "project" ( + "projectid" serial NOT NULL, + "name" text NOT NULL, + "url" text NOT NULL, + "description" text NOT NULL, + "owner" text NOT NULL, + PRIMARY KEY ("projectid"), + CONSTRAINT "project_name" UNIQUE ("name") +); + +; +-- +-- Table: commit_push +-- +CREATE TABLE "commit_push" ( + "sha256" text NOT NULL, + "content" text NOT NULL, + "projectid" integer NOT NULL, + "timestamp" timestamp NOT NULL, + PRIMARY KEY ("sha256") +); +CREATE INDEX "commit_push_idx_projectid" on "commit_push" ("projectid"); + +; +-- +-- Table: task +-- +CREATE TABLE "task" ( + "taskid" serial NOT NULL, + "sha256" text NOT NULL, + "projectid" integer NOT NULL, + "running" bool DEFAULT '0' NOT NULL, + PRIMARY KEY ("taskid"), + CONSTRAINT "task_projectid" UNIQUE ("projectid"), + CONSTRAINT "task_sha256" UNIQUE ("sha256") +); +CREATE INDEX "task_idx_sha256" on "task" ("sha256"); +CREATE INDEX "task_idx_projectid" on "task" ("projectid"); + +; +-- +-- Foreign Key Definitions +-- + +; +ALTER TABLE "commit_push" ADD FOREIGN KEY ("projectid") + REFERENCES "project" ("projectid") DEFERRABLE; + +; +ALTER TABLE "task" ADD FOREIGN KEY ("sha256") + REFERENCES "commit_push" ("sha256") DEFERRABLE; + +; +ALTER TABLE "task" ADD FOREIGN KEY ("projectid") + REFERENCES "project" ("projectid") DEFERRABLE; + -- cgit 1.4.1