Add migrations for creating all tables

Signed-off-by: Matthias Beyer <mail@beyermatthias.de>

22 files changed, 108 insertions, 0 deletions

diff --git a/migrations/2020-10-29-132821_create_envvars/down.sql b/migrations/2020-10-29-132821_create_envvars/down.sql
new file mode 100644
index 0000000..f9d8d4f
--- /dev/null
+++ b/migrations/2020-10-29-132821_create_envvars/down.sql
@@ -0,0 +1,2 @@
+-- This file should undo anything in `up.sql`
+DROP TABLE envvars
diff --git a/migrations/2020-10-29-132821_create_envvars/up.sql b/migrations/2020-10-29-132821_create_envvars/up.sql
new file mode 100644
index 0000000..9ef897b
--- /dev/null
+++ b/migrations/2020-10-29-132821_create_envvars/up.sql
@@ -0,0 +1,8 @@
+-- Your SQL goes here
+CREATE TABLE envvars (
+    id SERIAL PRIMARY KEY NOT NULL,
+    name VARCHAR NOT NULL,
+    value VARCHAR NOT NULL,
+
+    CONSTRAINT UC_name_value UNIQUE (name, value)
+)
diff --git a/migrations/2020-10-29-140235_create_githashes/down.sql b/migrations/2020-10-29-140235_create_githashes/down.sql
new file mode 100644
index 0000000..db92088
--- /dev/null
+++ b/migrations/2020-10-29-140235_create_githashes/down.sql
@@ -0,0 +1,2 @@
+-- This file should undo anything in `up.sql`
+DROP TABLE githashes
diff --git a/migrations/2020-10-29-140235_create_githashes/up.sql b/migrations/2020-10-29-140235_create_githashes/up.sql
new file mode 100644
index 0000000..4894cc6
--- /dev/null
+++ b/migrations/2020-10-29-140235_create_githashes/up.sql
@@ -0,0 +1,5 @@
+-- Your SQL goes here
+CREATE TABLE githashes (
+    id SERIAL PRIMARY KEY NOT NULL,
+    hash VARCHAR(64) NOT NULL UNIQUE
+)
diff --git a/migrations/2020-10-29-140838_create_packages/down.sql b/migrations/2020-10-29-140838_create_packages/down.sql
new file mode 100644
index 0000000..47f1bb4
--- /dev/null
+++ b/migrations/2020-10-29-140838_create_packages/down.sql
@@ -0,0 +1,2 @@
+-- This file should undo anything in `up.sql`
+DROP TABLE packages
diff --git a/migrations/2020-10-29-140838_create_packages/up.sql b/migrations/2020-10-29-140838_create_packages/up.sql
new file mode 100644
index 0000000..f1a6be6
--- /dev/null
+++ b/migrations/2020-10-29-140838_create_packages/up.sql
@@ -0,0 +1,8 @@
+-- Your SQL goes here
+CREATE TABLE packages (
+    id SERIAL PRIMARY KEY NOT NULL,
+    name    VARCHAR NOT NULL,
+    version VARCHAR NOT NULL,
+
+    CONSTRAINT UC_name_version UNIQUE (name, version)
+)
diff --git a/migrations/2020-10-29-140956_create_images/down.sql b/migrations/2020-10-29-140956_create_images/down.sql
new file mode 100644
index 0000000..9ba02d4
--- /dev/null
+++ b/migrations/2020-10-29-140956_create_images/down.sql
@@ -0,0 +1,2 @@
+-- This file should undo anything in `up.sql`
+DROP TABLE images
diff --git a/migrations/2020-10-29-140956_create_images/up.sql b/migrations/2020-10-29-140956_create_images/up.sql
new file mode 100644
index 0000000..c047e10
--- /dev/null
+++ b/migrations/2020-10-29-140956_create_images/up.sql
@@ -0,0 +1,5 @@
+-- Your SQL goes here
+CREATE TABLE images (
+    id SERIAL PRIMARY KEY NOT NULL,
+    name VARCHAR NOT NULL UNIQUE
+)
diff --git a/migrations/2020-10-29-141023_create_submits/down.sql b/migrations/2020-10-29-141023_create_submits/down.sql
new file mode 100644
index 0000000..8d0c7f4
--- /dev/null
+++ b/migrations/2020-10-29-141023_create_submits/down.sql
@@ -0,0 +1,2 @@
+-- This file should undo anything in `up.sql`
+DROP TABLE submits
diff --git a/migrations/2020-10-29-141023_create_submits/up.sql b/migrations/2020-10-29-141023_create_submits/up.sql
new file mode 100644
index 0000000..ca1a8bb
--- /dev/null
+++ b/migrations/2020-10-29-141023_create_submits/up.sql
@@ -0,0 +1,13 @@
+-- Your SQL goes here
+CREATE TABLE submits (
+    id SERIAL PRIMARY KEY NOT NULL,
+    uuid UUID NOT NULL UNIQUE,
+    submit_time TIMESTAMP WITH TIME ZONE NOT NULL,
+
+    requested_image_id   INTEGER REFERENCES images(id) NOT NULL,
+    requested_package_id INTEGER REFERENCES packages(id) NOT NULL,
+    repo_hash_id         INTEGER REFERENCES githashes(id) NOT NULL,
+
+    tree JSONB NOT NULL,
+    buildplan JSONB NOT NULL
+)
diff --git a/migrations/2020-10-29-141904_create_endpoints/down.sql b/migrations/2020-10-29-141904_create_endpoints/down.sql
new file mode 100644
index 0000000..930227d
--- /dev/null
+++ b/migrations/2020-10-29-141904_create_endpoints/down.sql
@@ -0,0 +1,2 @@
+-- This file should undo anything in `up.sql`
+DROP TABLE endpoints
diff --git a/migrations/2020-10-29-141904_create_endpoints/up.sql b/migrations/2020-10-29-141904_create_endpoints/up.sql
new file mode 100644
index 0000000..b14c176
--- /dev/null
+++ b/migrations/2020-10-29-141904_create_endpoints/up.sql
@@ -0,0 +1,5 @@
+-- Your SQL goes here
+CREATE TABLE endpoints (
+    id SERIAL PRIMARY KEY NOT NULL,
+    name VARCHAR NOT NULL UNIQUE
+)
diff --git a/migrations/2020-10-29-142339_create_artifacts/down.sql b/migrations/2020-10-29-142339_create_artifacts/down.sql
new file mode 100644
index 0000000..263debe
--- /dev/null
+++ b/migrations/2020-10-29-142339_create_artifacts/down.sql
@@ -0,0 +1,2 @@
+-- This file should undo anything in `up.sql`
+DROP TABLE artifacts
diff --git a/migrations/2020-10-29-142339_create_artifacts/up.sql b/migrations/2020-10-29-142339_create_artifacts/up.sql
new file mode 100644
index 0000000..b133ee7
--- /dev/null
+++ b/migrations/2020-10-29-142339_create_artifacts/up.sql
@@ -0,0 +1,5 @@
+-- Your SQL goes here
+CREATE TABLE artifacts (
+    id SERIAL PRIMARY KEY NOT NULL,
+    path VARCHAR NOT NULL UNIQUE
+)
diff --git a/migrations/2020-10-29-142443_create_jobs/down.sql b/migrations/2020-10-29-142443_create_jobs/down.sql
new file mode 100644
index 0000000..b54a154
--- /dev/null
+++ b/migrations/2020-10-29-142443_create_jobs/down.sql
@@ -0,0 +1,2 @@
+-- This file should undo anything in `up.sql`
+DROP TABLE jobs
diff --git a/migrations/2020-10-29-142443_create_jobs/up.sql b/migrations/2020-10-29-142443_create_jobs/up.sql
new file mode 100644
index 0000000..0ee6d6b
--- /dev/null
+++ b/migrations/2020-10-29-142443_create_jobs/up.sql
@@ -0,0 +1,14 @@
+-- Your SQL goes here
+CREATE TABLE jobs (
+    id SERIAL PRIMARY KEY NOT NULL,
+
+    submit_id 	INTEGER REFERENCES submits(id) NOT NULL,
+    endpoint_id INTEGER REFERENCES endpoints(id) NOT NULL,
+    package_id 	INTEGER REFERENCES packages(id) NOT NULL,
+    image_id 	INTEGER REFERENCES images(id) NOT NULL,
+    artifact_id INTEGER REFERENCES artifacts(id) NOT NULL,
+
+    container_hash VARCHAR NOT NULL,
+    script_text TEXT NOT NULL,
+    log_text TEXT NOT NULL
+)
diff --git a/migrations/2020-10-29-142619_create_job_input_artifacts/down.sql b/migrations/2020-10-29-142619_create_job_input_artifacts/down.sql
new file mode 100644
index 0000000..5b74a7b
--- /dev/null
+++ b/migrations/2020-10-29-142619_create_job_input_artifacts/down.sql
@@ -0,0 +1,2 @@
+-- This file should undo anything in `up.sql`
+DROP TABLE job_input_artifacts
diff --git a/migrations/2020-10-29-142619_create_job_input_artifacts/up.sql b/migrations/2020-10-29-142619_create_job_input_artifacts/up.sql
new file mode 100644
index 0000000..dc29ba6
--- /dev/null
+++ b/migrations/2020-10-29-142619_create_job_input_artifacts/up.sql
@@ -0,0 +1,8 @@
+-- Your SQL goes here
+CREATE TABLE job_input_artifacts (
+    id SERIAL PRIMARY KEY NOT NULL,
+    job_id      INTEGER REFERENCES jobs(id) NOT NULL,
+    artifact_id INTEGER REFERENCES artifacts(id) NOT NULL,
+
+    CONSTRAINT UC_jobid_artifactid UNIQUE (job_id, artifact_id)
+)
diff --git a/migrations/2020-10-29-142914_create_job_envs/down.sql b/migrations/2020-10-29-142914_create_job_envs/down.sql
new file mode 100644
index 0000000..beaba4d
--- /dev/null
+++ b/migrations/2020-10-29-142914_create_job_envs/down.sql
@@ -0,0 +1,2 @@
+-- This file should undo anything in `up.sql`
+DROP TABLE job_envs
diff --git a/migrations/2020-10-29-142914_create_job_envs/up.sql b/migrations/2020-10-29-142914_create_job_envs/up.sql
new file mode 100644
index 0000000..f5cedae
--- /dev/null
+++ b/migrations/2020-10-29-142914_create_job_envs/up.sql
@@ -0,0 +1,8 @@
+-- Your SQL goes here
+CREATE TABLE job_envs (
+    id SERIAL PRIMARY KEY NOT NULL,
+    job_id INTEGER REFERENCES jobs(id) NOT NULL,
+    env_id INTEGER REFERENCES envvars(id) NOT NULL,
+
+    CONSTRAINT UC_jobid_envid UNIQUE (job_id, env_id)
+)
diff --git a/migrations/2020-10-29-143003_create_submit_envs/down.sql b/migrations/2020-10-29-143003_create_submit_envs/down.sql
new file mode 100644
index 0000000..291a97c
--- /dev/null
+++ b/migrations/2020-10-29-143003_create_submit_envs/down.sql
@@ -0,0 +1 @@
+-- This file should undo anything in `up.sql`
\ No newline at end of file
diff --git a/migrations/2020-10-29-143003_create_submit_envs/up.sql b/migrations/2020-10-29-143003_create_submit_envs/up.sql
new file mode 100644
index 0000000..1be7c2e
--- /dev/null
+++ b/migrations/2020-10-29-143003_create_submit_envs/up.sql
@@ -0,0 +1,8 @@
+-- Your SQL goes here
+CREATE TABLE submit_envs (
+    id SERIAL PRIMARY KEY NOT NULL,
+    submit_id INTEGER REFERENCES submits(id) NOT NULL,
+    env_id    INTEGER REFERENCES envvars(id) NOT NULL,
+
+    CONSTRAINT UC_submitid_envid UNIQUE (submit_id, env_id)
+)