diff options
author | franck cuny <franck@lumberjaph.net> | 2011-01-24 21:32:25 +0100 |
---|---|---|
committer | franck cuny <franck@lumberjaph.net> | 2011-01-24 21:32:25 +0100 |
commit | 0e615cf95a4fe198eed9ce125804767d1f38d80f (patch) | |
tree | 76b999d3a2f25ec6c76e24a2f05aa42ac01ded60 /sql | |
parent | don't ignore .sql files (diff) | |
download | jitterbug-0e615cf95a4fe198eed9ce125804767d1f38d80f.tar.gz |
add sql schema for various version
Diffstat (limited to '')
-rw-r--r-- | sql/MySQL/deploy/1/001-auto-__VERSION.sql | 18 | ||||
-rw-r--r-- | sql/MySQL/deploy/1/001-auto.sql | 46 | ||||
-rw-r--r-- | sql/MySQL/deploy/2/001-auto-__VERSION.sql | 18 | ||||
-rw-r--r-- | sql/MySQL/deploy/2/001-auto.sql | 47 | ||||
-rw-r--r-- | sql/MySQL/downgrade/2-1/001-auto.sql | 12 | ||||
-rw-r--r-- | sql/MySQL/upgrade/1-2/001-auto.sql | 12 | ||||
-rw-r--r-- | sql/PostgreSQL/deploy/1/001-auto-__VERSION.sql | 17 | ||||
-rw-r--r-- | sql/PostgreSQL/deploy/1/001-auto.sql | 63 | ||||
-rw-r--r-- | sql/PostgreSQL/deploy/2/001-auto-__VERSION.sql | 17 | ||||
-rw-r--r-- | sql/PostgreSQL/deploy/2/001-auto.sql | 64 | ||||
-rw-r--r-- | sql/PostgreSQL/downgrade/2-1/001-auto.sql | 12 | ||||
-rw-r--r-- | sql/PostgreSQL/upgrade/1-2/001-auto.sql | 12 | ||||
-rw-r--r-- | sql/SQLite/deploy/1/001-auto-__VERSION.sql | 18 | ||||
-rw-r--r-- | sql/SQLite/deploy/1/001-auto.sql | 42 | ||||
-rw-r--r-- | sql/SQLite/deploy/2/001-auto-__VERSION.sql | 18 | ||||
-rw-r--r-- | sql/SQLite/deploy/2/001-auto.sql | 43 | ||||
-rw-r--r-- | sql/SQLite/downgrade/2-1/001-auto.sql | 47 | ||||
-rw-r--r-- | sql/SQLite/upgrade/1-2/001-auto.sql | 12 |
18 files changed, 518 insertions, 0 deletions
diff --git a/sql/MySQL/deploy/1/001-auto-__VERSION.sql b/sql/MySQL/deploy/1/001-auto-__VERSION.sql new file mode 100644 index 0000000..597d180 --- /dev/null +++ b/sql/MySQL/deploy/1/001-auto-__VERSION.sql @@ -0,0 +1,18 @@ +-- +-- Created by SQL::Translator::Producer::MySQL +-- Created on Mon Jan 24 21:25:06 2011 +-- +; +SET foreign_key_checks=0; +-- +-- Table: `dbix_class_deploymenthandler_versions` +-- +CREATE TABLE `dbix_class_deploymenthandler_versions` ( + `id` integer NOT NULL auto_increment, + `version` varchar(50) NOT NULL, + `ddl` text, + `upgrade_sql` text, + PRIMARY KEY (`id`), + UNIQUE `dbix_class_deploymenthandler_versions_version` (`version`) +); +SET foreign_key_checks=1 \ No newline at end of file diff --git a/sql/MySQL/deploy/1/001-auto.sql b/sql/MySQL/deploy/1/001-auto.sql new file mode 100644 index 0000000..2a8b829 --- /dev/null +++ b/sql/MySQL/deploy/1/001-auto.sql @@ -0,0 +1,46 @@ +-- +-- Created by SQL::Translator::Producer::MySQL +-- Created on Mon Jan 24 21:25:06 2011 +-- +; +SET foreign_key_checks=0; +-- +-- Table: `project` +-- +CREATE TABLE `project` ( + `projectid` integer NOT NULL auto_increment, + `name` text NOT NULL, + `url` text NOT NULL, + `description` text NOT NULL, + `owner` text NOT NULL, + PRIMARY KEY (`projectid`), + UNIQUE `project_name` (`name`) +) ENGINE=InnoDB; +-- +-- Table: `commit_push` +-- +CREATE TABLE `commit_push` ( + `sha256` text NOT NULL, + `content` text NOT NULL, + `projectid` integer NOT NULL, + `timestamp` datetime NOT NULL, + INDEX `commit_push_idx_projectid` (`projectid`), + PRIMARY KEY (`sha256`), + CONSTRAINT `commit_push_fk_projectid` FOREIGN KEY (`projectid`) REFERENCES `project` (`projectid`) +) ENGINE=InnoDB; +-- +-- Table: `task` +-- +CREATE TABLE `task` ( + `taskid` integer NOT NULL auto_increment, + `sha256` text NOT NULL, + `projectid` integer NOT NULL, + INDEX `task_idx_sha256` (`sha256`), + INDEX `task_idx_projectid` (`projectid`), + PRIMARY KEY (`taskid`), + UNIQUE `task_projectid` (`projectid`), + UNIQUE `task_sha256` (`sha256`), + CONSTRAINT `task_fk_sha256` FOREIGN KEY (`sha256`) REFERENCES `commit_push` (`sha256`), + CONSTRAINT `task_fk_projectid` FOREIGN KEY (`projectid`) REFERENCES `project` (`projectid`) +) ENGINE=InnoDB; +SET foreign_key_checks=1 \ No newline at end of file diff --git a/sql/MySQL/deploy/2/001-auto-__VERSION.sql b/sql/MySQL/deploy/2/001-auto-__VERSION.sql new file mode 100644 index 0000000..e91854a --- /dev/null +++ b/sql/MySQL/deploy/2/001-auto-__VERSION.sql @@ -0,0 +1,18 @@ +-- +-- Created by SQL::Translator::Producer::MySQL +-- Created on Mon Jan 24 21:26:42 2011 +-- +; +SET foreign_key_checks=0; +-- +-- Table: `dbix_class_deploymenthandler_versions` +-- +CREATE TABLE `dbix_class_deploymenthandler_versions` ( + `id` integer NOT NULL auto_increment, + `version` varchar(50) NOT NULL, + `ddl` text, + `upgrade_sql` text, + PRIMARY KEY (`id`), + UNIQUE `dbix_class_deploymenthandler_versions_version` (`version`) +); +SET foreign_key_checks=1 \ No newline at end of file diff --git a/sql/MySQL/deploy/2/001-auto.sql b/sql/MySQL/deploy/2/001-auto.sql new file mode 100644 index 0000000..d208773 --- /dev/null +++ b/sql/MySQL/deploy/2/001-auto.sql @@ -0,0 +1,47 @@ +-- +-- Created by SQL::Translator::Producer::MySQL +-- Created on Mon Jan 24 21:26:41 2011 +-- +; +SET foreign_key_checks=0; +-- +-- Table: `project` +-- +CREATE TABLE `project` ( + `projectid` integer NOT NULL auto_increment, + `name` text NOT NULL, + `url` text NOT NULL, + `description` text NOT NULL, + `owner` text NOT NULL, + PRIMARY KEY (`projectid`), + UNIQUE `project_name` (`name`) +) ENGINE=InnoDB; +-- +-- Table: `commit_push` +-- +CREATE TABLE `commit_push` ( + `sha256` text NOT NULL, + `content` text NOT NULL, + `projectid` integer NOT NULL, + `timestamp` datetime NOT NULL, + INDEX `commit_push_idx_projectid` (`projectid`), + PRIMARY KEY (`sha256`), + CONSTRAINT `commit_push_fk_projectid` FOREIGN KEY (`projectid`) REFERENCES `project` (`projectid`) +) ENGINE=InnoDB; +-- +-- Table: `task` +-- +CREATE TABLE `task` ( + `taskid` integer NOT NULL auto_increment, + `sha256` text NOT NULL, + `projectid` integer NOT NULL, + `running` bool NOT NULL DEFAULT '0', + INDEX `task_idx_sha256` (`sha256`), + INDEX `task_idx_projectid` (`projectid`), + PRIMARY KEY (`taskid`), + UNIQUE `task_projectid` (`projectid`), + UNIQUE `task_sha256` (`sha256`), + CONSTRAINT `task_fk_sha256` FOREIGN KEY (`sha256`) REFERENCES `commit_push` (`sha256`), + CONSTRAINT `task_fk_projectid` FOREIGN KEY (`projectid`) REFERENCES `project` (`projectid`) +) ENGINE=InnoDB; +SET foreign_key_checks=1 \ No newline at end of file diff --git a/sql/MySQL/downgrade/2-1/001-auto.sql b/sql/MySQL/downgrade/2-1/001-auto.sql new file mode 100644 index 0000000..3285916 --- /dev/null +++ b/sql/MySQL/downgrade/2-1/001-auto.sql @@ -0,0 +1,12 @@ +-- Convert schema 'sql/_source/deploy/2/001-auto.yml' to 'sql/_source/deploy/1/001-auto.yml':; + +; +BEGIN; + +; +ALTER TABLE task DROP COLUMN running; + +; + +COMMIT; + diff --git a/sql/MySQL/upgrade/1-2/001-auto.sql b/sql/MySQL/upgrade/1-2/001-auto.sql new file mode 100644 index 0000000..1183d23 --- /dev/null +++ b/sql/MySQL/upgrade/1-2/001-auto.sql @@ -0,0 +1,12 @@ +-- Convert schema 'sql/_source/deploy/1/001-auto.yml' to 'sql/_source/deploy/2/001-auto.yml':; + +; +BEGIN; + +; +ALTER TABLE task ADD COLUMN running bool NOT NULL DEFAULT '0'; + +; + +COMMIT; + diff --git a/sql/PostgreSQL/deploy/1/001-auto-__VERSION.sql b/sql/PostgreSQL/deploy/1/001-auto-__VERSION.sql new file mode 100644 index 0000000..43ecfa6 --- /dev/null +++ b/sql/PostgreSQL/deploy/1/001-auto-__VERSION.sql @@ -0,0 +1,17 @@ +-- +-- Created by SQL::Translator::Producer::PostgreSQL +-- Created on Mon Jan 24 21:25:06 2011 +-- +; +-- +-- Table: dbix_class_deploymenthandler_versions +-- +CREATE TABLE "dbix_class_deploymenthandler_versions" ( + "id" serial NOT NULL, + "version" character varying(50) NOT NULL, + "ddl" text, + "upgrade_sql" text, + PRIMARY KEY ("id"), + CONSTRAINT "dbix_class_deploymenthandler_versions_version" UNIQUE ("version") +); + diff --git a/sql/PostgreSQL/deploy/1/001-auto.sql b/sql/PostgreSQL/deploy/1/001-auto.sql new file mode 100644 index 0000000..3f0a076 --- /dev/null +++ b/sql/PostgreSQL/deploy/1/001-auto.sql @@ -0,0 +1,63 @@ +-- +-- Created by SQL::Translator::Producer::PostgreSQL +-- Created on Mon Jan 24 21:25:06 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, + 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; + diff --git a/sql/PostgreSQL/deploy/2/001-auto-__VERSION.sql b/sql/PostgreSQL/deploy/2/001-auto-__VERSION.sql new file mode 100644 index 0000000..348bca5 --- /dev/null +++ b/sql/PostgreSQL/deploy/2/001-auto-__VERSION.sql @@ -0,0 +1,17 @@ +-- +-- Created by SQL::Translator::Producer::PostgreSQL +-- Created on Mon Jan 24 21:26:42 2011 +-- +; +-- +-- Table: dbix_class_deploymenthandler_versions +-- +CREATE TABLE "dbix_class_deploymenthandler_versions" ( + "id" serial NOT NULL, + "version" character varying(50) NOT NULL, + "ddl" text, + "upgrade_sql" text, + PRIMARY KEY ("id"), + CONSTRAINT "dbix_class_deploymenthandler_versions_version" UNIQUE ("version") +); + 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; + diff --git a/sql/PostgreSQL/downgrade/2-1/001-auto.sql b/sql/PostgreSQL/downgrade/2-1/001-auto.sql new file mode 100644 index 0000000..3285916 --- /dev/null +++ b/sql/PostgreSQL/downgrade/2-1/001-auto.sql @@ -0,0 +1,12 @@ +-- Convert schema 'sql/_source/deploy/2/001-auto.yml' to 'sql/_source/deploy/1/001-auto.yml':; + +; +BEGIN; + +; +ALTER TABLE task DROP COLUMN running; + +; + +COMMIT; + diff --git a/sql/PostgreSQL/upgrade/1-2/001-auto.sql b/sql/PostgreSQL/upgrade/1-2/001-auto.sql new file mode 100644 index 0000000..3edf5df --- /dev/null +++ b/sql/PostgreSQL/upgrade/1-2/001-auto.sql @@ -0,0 +1,12 @@ +-- Convert schema 'sql/_source/deploy/1/001-auto.yml' to 'sql/_source/deploy/2/001-auto.yml':; + +; +BEGIN; + +; +ALTER TABLE task ADD COLUMN running bool DEFAULT '0' NOT NULL; + +; + +COMMIT; + diff --git a/sql/SQLite/deploy/1/001-auto-__VERSION.sql b/sql/SQLite/deploy/1/001-auto-__VERSION.sql new file mode 100644 index 0000000..6d6e3fa --- /dev/null +++ b/sql/SQLite/deploy/1/001-auto-__VERSION.sql @@ -0,0 +1,18 @@ +-- +-- Created by SQL::Translator::Producer::SQLite +-- Created on Mon Jan 24 21:25:06 2011 +-- + +; +BEGIN TRANSACTION; +-- +-- Table: dbix_class_deploymenthandler_versions +-- +CREATE TABLE dbix_class_deploymenthandler_versions ( + id INTEGER PRIMARY KEY NOT NULL, + version varchar(50) NOT NULL, + ddl text, + upgrade_sql text +); +CREATE UNIQUE INDEX dbix_class_deploymenthandler_versions_version ON dbix_class_deploymenthandler_versions (version); +COMMIT \ No newline at end of file diff --git a/sql/SQLite/deploy/1/001-auto.sql b/sql/SQLite/deploy/1/001-auto.sql new file mode 100644 index 0000000..804958b --- /dev/null +++ b/sql/SQLite/deploy/1/001-auto.sql @@ -0,0 +1,42 @@ +-- +-- Created by SQL::Translator::Producer::SQLite +-- Created on Mon Jan 24 21:25:06 2011 +-- + +; +BEGIN TRANSACTION; +-- +-- Table: project +-- +CREATE TABLE project ( + projectid INTEGER PRIMARY KEY NOT NULL, + name text NOT NULL, + url text NOT NULL, + description text NOT NULL, + owner text NOT NULL +); +CREATE UNIQUE INDEX project_name ON project (name); +-- +-- Table: commit_push +-- +CREATE TABLE commit_push ( + sha256 text NOT NULL, + content text NOT NULL, + projectid int NOT NULL, + timestamp datetime NOT NULL, + PRIMARY KEY (sha256) +); +CREATE INDEX commit_push_idx_projectid ON commit_push (projectid); +-- +-- Table: task +-- +CREATE TABLE task ( + taskid INTEGER PRIMARY KEY NOT NULL, + sha256 text NOT NULL, + projectid int NOT NULL +); +CREATE INDEX task_idx_sha256 ON task (sha256); +CREATE INDEX task_idx_projectid ON task (projectid); +CREATE UNIQUE INDEX task_projectid ON task (projectid); +CREATE UNIQUE INDEX task_sha256 ON task (sha256); +COMMIT \ No newline at end of file diff --git a/sql/SQLite/deploy/2/001-auto-__VERSION.sql b/sql/SQLite/deploy/2/001-auto-__VERSION.sql new file mode 100644 index 0000000..8b1bc1f --- /dev/null +++ b/sql/SQLite/deploy/2/001-auto-__VERSION.sql @@ -0,0 +1,18 @@ +-- +-- Created by SQL::Translator::Producer::SQLite +-- Created on Mon Jan 24 21:26:41 2011 +-- + +; +BEGIN TRANSACTION; +-- +-- Table: dbix_class_deploymenthandler_versions +-- +CREATE TABLE dbix_class_deploymenthandler_versions ( + id INTEGER PRIMARY KEY NOT NULL, + version varchar(50) NOT NULL, + ddl text, + upgrade_sql text +); +CREATE UNIQUE INDEX dbix_class_deploymenthandler_versions_version ON dbix_class_deploymenthandler_versions (version); +COMMIT \ No newline at end of file diff --git a/sql/SQLite/deploy/2/001-auto.sql b/sql/SQLite/deploy/2/001-auto.sql new file mode 100644 index 0000000..45ac741 --- /dev/null +++ b/sql/SQLite/deploy/2/001-auto.sql @@ -0,0 +1,43 @@ +-- +-- Created by SQL::Translator::Producer::SQLite +-- Created on Mon Jan 24 21:26:41 2011 +-- + +; +BEGIN TRANSACTION; +-- +-- Table: project +-- +CREATE TABLE project ( + projectid INTEGER PRIMARY KEY NOT NULL, + name text NOT NULL, + url text NOT NULL, + description text NOT NULL, + owner text NOT NULL +); +CREATE UNIQUE INDEX project_name ON project (name); +-- +-- Table: commit_push +-- +CREATE TABLE commit_push ( + sha256 text NOT NULL, + content text NOT NULL, + projectid int NOT NULL, + timestamp datetime NOT NULL, + PRIMARY KEY (sha256) +); +CREATE INDEX commit_push_idx_projectid ON commit_push (projectid); +-- +-- Table: task +-- +CREATE TABLE task ( + taskid INTEGER PRIMARY KEY NOT NULL, + sha256 text NOT NULL, + projectid int NOT NULL, + running bool NOT NULL DEFAULT '0' +); +CREATE INDEX task_idx_sha256 ON task (sha256); +CREATE INDEX task_idx_projectid ON task (projectid); +CREATE UNIQUE INDEX task_projectid ON task (projectid); +CREATE UNIQUE INDEX task_sha256 ON task (sha256); +COMMIT \ No newline at end of file diff --git a/sql/SQLite/downgrade/2-1/001-auto.sql b/sql/SQLite/downgrade/2-1/001-auto.sql new file mode 100644 index 0000000..763b77b --- /dev/null +++ b/sql/SQLite/downgrade/2-1/001-auto.sql @@ -0,0 +1,47 @@ +-- Convert schema 'sql/_source/deploy/2/001-auto.yml' to 'sql/_source/deploy/1/001-auto.yml':; + +; +BEGIN; + +; +CREATE TEMPORARY TABLE task_temp_alter ( + taskid INTEGER PRIMARY KEY NOT NULL, + sha256 text NOT NULL, + projectid int NOT NULL +); + +; +INSERT INTO task_temp_alter SELECT taskid, sha256, projectid FROM task; + +; +DROP TABLE task; + +; +CREATE TABLE task ( + taskid INTEGER PRIMARY KEY NOT NULL, + sha256 text NOT NULL, + projectid int NOT NULL +); + +; +CREATE INDEX task_idx_sha25602 ON task (sha256); + +; +CREATE INDEX task_idx_projectid02 ON task (projectid); + +; +CREATE UNIQUE INDEX task_projectid02 ON task (projectid); + +; +CREATE UNIQUE INDEX task_sha25602 ON task (sha256); + +; +INSERT INTO task SELECT taskid, sha256, projectid FROM task_temp_alter; + +; +DROP TABLE task_temp_alter; + +; + +COMMIT; + diff --git a/sql/SQLite/upgrade/1-2/001-auto.sql b/sql/SQLite/upgrade/1-2/001-auto.sql new file mode 100644 index 0000000..1183d23 --- /dev/null +++ b/sql/SQLite/upgrade/1-2/001-auto.sql @@ -0,0 +1,12 @@ +-- Convert schema 'sql/_source/deploy/1/001-auto.yml' to 'sql/_source/deploy/2/001-auto.yml':; + +; +BEGIN; + +; +ALTER TABLE task ADD COLUMN running bool NOT NULL DEFAULT '0'; + +; + +COMMIT; + |