From 67bb17a27be04249f84abd9ad33000ed0ed548f7 Mon Sep 17 00:00:00 2001 From: Jordan Doyle Date: Fri, 09 Sep 2022 20:54:26 +0100 Subject: [PATCH] Build migrations specifically for Postgres --- chartered-db/src/lib.rs | 6 +++++- migrations/2021-08-31-214501_create_crates_table/down.sql | 9 --------- migrations/2021-08-31-214501_create_crates_table/up.sql | 106 -------------------------------------------------------------------------------- migrations/2021-11-08-005634_update_sessions_add_uuid/down.sql | 2 -- migrations/2021-11-08-005634_update_sessions_add_uuid/up.sql | 5 ----- migrations/postgres/2021-08-31-214501_create_crates_table/down.sql | 9 +++++++++ migrations/postgres/2021-08-31-214501_create_crates_table/up.sql | 101 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ migrations/postgres/2021-11-08-005634_update_sessions_add_uuid/down.sql | 2 ++ migrations/postgres/2021-11-08-005634_update_sessions_add_uuid/up.sql | 5 +++++ migrations/sqlite/2021-08-31-214501_create_crates_table/down.sql | 9 +++++++++ migrations/sqlite/2021-08-31-214501_create_crates_table/up.sql | 106 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ migrations/sqlite/2021-11-08-005634_update_sessions_add_uuid/down.sql | 2 ++ migrations/sqlite/2021-11-08-005634_update_sessions_add_uuid/up.sql | 5 +++++ 13 files changed, 244 insertions(+), 123 deletions(-) diff --git a/chartered-db/src/lib.rs b/chartered-db/src/lib.rs index 13d992a..f80da20 100644 --- a/chartered-db/src/lib.rs +++ a/chartered-db/src/lib.rs @@ -73,7 +73,11 @@ pub type ConnectionPool = Arc>>; pub type Result = std::result::Result; -embed_migrations!(); +#[cfg(feature = "postgres")] +embed_migrations!("../migrations/postgres"); + +#[cfg(feature = "sqlite")] +embed_migrations!("../migrations/sqlite"); pub fn init(connection_uri: &str) -> Result { let connection_uri = parse_connection_uri(connection_uri)?; diff --git a/migrations/2021-08-31-214501_create_crates_table/down.sql b/migrations/2021-08-31-214501_create_crates_table/down.sql deleted file mode 100644 index 8322caf..0000000 100644 --- a/migrations/2021-08-31-214501_create_crates_table/down.sql +++ /dev/null @@ -1,9 +1,0 @@ -DROP TABLE organisations; -DROP TABLE users; -DROP TABLE crates; -DROP TABLE crate_versions; -DROP TABLE user_organisation_permissions; -DROP TABLE user_crate_permissions; -DROP TABLE user_ssh_keys; -DROP TABLE user_sessions; -DROP TABLE server_private_keys; diff --git a/migrations/2021-08-31-214501_create_crates_table/up.sql b/migrations/2021-08-31-214501_create_crates_table/up.sql deleted file mode 100644 index 7ec5834..0000000 100644 --- a/migrations/2021-08-31-214501_create_crates_table/up.sql +++ /dev/null @@ -1,106 +1,0 @@ -CREATE TABLE users ( - id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - uuid BINARY(128) NOT NULL UNIQUE, - username VARCHAR(255) NOT NULL UNIQUE, - password CHAR(60), - name VARCHAR(255), - nick VARCHAR(255), - email VARCHAR(255), - external_profile_url VARCHAR(2048), - picture_url VARCHAR(2048) -); - -INSERT INTO users (id, uuid, username) VALUES (1, X'936DA01F9ABD4D9D80C702AF85C822A8', "admin"); -INSERT INTO users (id, uuid, username) VALUES (2, X'936DA01F9ABD4D9D80C702AF85C822A9', "billy"); - -CREATE TABLE organisations ( - id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - uuid BINARY(128) NOT NULL UNIQUE, - name VARCHAR(255) NOT NULL UNIQUE, - description TEXT NOT NULL DEFAULT "", - public BOOLEAN NOT NULL DEFAULT FALSE -); - -INSERT INTO organisations (id, uuid, name, description) VALUES (1, X'936DA01F9ABD4D9D80C702AF85C822A8', "core", "My first organisation"); - -CREATE TABLE crates ( - id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - name VARCHAR(255) NOT NULL, - organisation_id INTEGER NOT NULL, - readme TEXT, - description VARCHAR(255), - repository VARCHAR(255), - homepage VARCHAR(255), - documentation VARCHAR(255), - downloads INTEGER NOT NULL DEFAULT 0, - created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, - UNIQUE (name, organisation_id), - FOREIGN KEY (organisation_id) REFERENCES organisations (id) -); - -CREATE TABLE crate_versions ( - id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - crate_id INTEGER NOT NULL, - version VARCHAR(255) NOT NULL, - filesystem_object VARCHAR(255) NOT NULL, - size INTEGER NOT NULL, - yanked BOOLEAN NOT NULL DEFAULT FALSE, - checksum VARCHAR(255) NOT NULL, - dependencies BLOB NOT NULL, - features BLOB NOT NULL, - links VARCHAR(255), - user_id INTEGER NOT NULL, - created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, - UNIQUE (crate_id, version), - FOREIGN KEY (user_id) REFERENCES users (id) - FOREIGN KEY (crate_id) REFERENCES crates (id) -); - -CREATE TABLE user_organisation_permissions ( - id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - user_id INTEGER NOT NULL, - organisation_id INTEGER NOT NULL, - permissions INTEGER NOT NULL, - UNIQUE (user_id, organisation_id), - FOREIGN KEY (user_id) REFERENCES users (id) - FOREIGN KEY (organisation_id) REFERENCES organisations (id) -); - -CREATE TABLE user_crate_permissions ( - id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - user_id INTEGER NOT NULL, - crate_id INTEGER NOT NULL, - permissions INTEGER NOT NULL, - UNIQUE (user_id, crate_id), - FOREIGN KEY (user_id) REFERENCES users (id) - FOREIGN KEY (crate_id) REFERENCES crates (id) -); - -CREATE TABLE user_ssh_keys ( - id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - uuid BINARY(128) NOT NULL UNIQUE, - name VARCHAR(255) NOT NULL, - user_id INTEGER NOT NULL, - ssh_key BLOB NOT NULL UNIQUE, - created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - last_used_at DATETIME, - FOREIGN KEY (user_id) REFERENCES users (id) -); - -CREATE TABLE user_sessions ( - id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - user_id INTEGER NOT NULL, - session_key VARCHAR(255) NOT NULL UNIQUE, - user_ssh_key_id INTEGER, - expires_at DATETIME, - user_agent VARCHAR(255), - ip VARCHAR(255), - FOREIGN KEY (user_id) REFERENCES users (id) - FOREIGN KEY (user_ssh_key_id) REFERENCES user_ssh_keys (id) -); - -CREATE TABLE server_private_keys ( - id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - ssh_key_type VARCHAR(255) NOT NULL UNIQUE, - ssh_private_key BLOB NOT NULL UNIQUE -); diff --git a/migrations/2021-11-08-005634_update_sessions_add_uuid/down.sql b/migrations/2021-11-08-005634_update_sessions_add_uuid/down.sql deleted file mode 100644 index 99c116b..0000000 100644 --- a/migrations/2021-11-08-005634_update_sessions_add_uuid/down.sql +++ /dev/null @@ -1,2 +1,0 @@ -DROP INDEX unique_user_sessions_uuid; -ALTER TABLE user_sessions DROP COLUMN uuid; diff --git a/migrations/2021-11-08-005634_update_sessions_add_uuid/up.sql b/migrations/2021-11-08-005634_update_sessions_add_uuid/up.sql deleted file mode 100644 index dc7d397..0000000 100644 --- a/migrations/2021-11-08-005634_update_sessions_add_uuid/up.sql +++ /dev/null @@ -1,5 +1,0 @@ --- drop all sessions before creating NOT NULL column -DELETE FROM user_sessions; - -ALTER TABLE user_sessions ADD COLUMN uuid BINARY(128) NOT NULL; -CREATE UNIQUE INDEX unique_user_sessions_uuid ON user_sessions(uuid); diff --git a/migrations/postgres/2021-08-31-214501_create_crates_table/down.sql b/migrations/postgres/2021-08-31-214501_create_crates_table/down.sql new file mode 100644 index 0000000..8322caf 100644 --- /dev/null +++ a/migrations/postgres/2021-08-31-214501_create_crates_table/down.sql @@ -1,0 +1,9 @@ +DROP TABLE organisations; +DROP TABLE users; +DROP TABLE crates; +DROP TABLE crate_versions; +DROP TABLE user_organisation_permissions; +DROP TABLE user_crate_permissions; +DROP TABLE user_ssh_keys; +DROP TABLE user_sessions; +DROP TABLE server_private_keys; diff --git a/migrations/postgres/2021-08-31-214501_create_crates_table/up.sql b/migrations/postgres/2021-08-31-214501_create_crates_table/up.sql new file mode 100644 index 0000000..43430c2 100644 --- /dev/null +++ a/migrations/postgres/2021-08-31-214501_create_crates_table/up.sql @@ -1,0 +1,101 @@ +CREATE TABLE users ( + id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, + uuid UUID NOT NULL UNIQUE, + username VARCHAR(255) NOT NULL UNIQUE, + password CHAR(60), + name VARCHAR(255), + nick VARCHAR(255), + email VARCHAR(255), + external_profile_url VARCHAR(2048), + picture_url VARCHAR(2048) +); + +CREATE TABLE organisations ( + id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, + uuid UUID NOT NULL UNIQUE, + name VARCHAR(255) NOT NULL UNIQUE, + description TEXT NOT NULL DEFAULT '', + public BOOLEAN NOT NULL DEFAULT FALSE +); + +CREATE TABLE crates ( + id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, + name VARCHAR(255) NOT NULL, + organisation_id INTEGER NOT NULL, + readme TEXT, + description VARCHAR(255), + repository VARCHAR(255), + homepage VARCHAR(255), + documentation VARCHAR(255), + downloads INTEGER NOT NULL DEFAULT 0, + created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + UNIQUE (name, organisation_id), + FOREIGN KEY (organisation_id) REFERENCES organisations (id) +); + +CREATE TABLE crate_versions ( + id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, + crate_id INTEGER NOT NULL, + version VARCHAR(255) NOT NULL, + filesystem_object VARCHAR(255) NOT NULL, + size INTEGER NOT NULL, + yanked BOOLEAN NOT NULL DEFAULT FALSE, + checksum VARCHAR(255) NOT NULL, + dependencies BYTEA NOT NULL, + features BYTEA NOT NULL, + links VARCHAR(255), + user_id INTEGER NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + UNIQUE (crate_id, version), + FOREIGN KEY (user_id) REFERENCES users (id), + FOREIGN KEY (crate_id) REFERENCES crates (id) +); + +CREATE TABLE user_organisation_permissions ( + id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, + user_id INTEGER NOT NULL, + organisation_id INTEGER NOT NULL, + permissions INTEGER NOT NULL, + UNIQUE (user_id, organisation_id), + FOREIGN KEY (user_id) REFERENCES users (id), + FOREIGN KEY (organisation_id) REFERENCES organisations (id) +); + +CREATE TABLE user_crate_permissions ( + id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, + user_id INTEGER NOT NULL, + crate_id INTEGER NOT NULL, + permissions INTEGER NOT NULL, + UNIQUE (user_id, crate_id), + FOREIGN KEY (user_id) REFERENCES users (id), + FOREIGN KEY (crate_id) REFERENCES crates (id) +); + +CREATE TABLE user_ssh_keys ( + id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, + uuid UUID NOT NULL UNIQUE, + name VARCHAR(255) NOT NULL, + user_id INTEGER NOT NULL, + ssh_key BYTEA NOT NULL UNIQUE, + created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + last_used_at TIMESTAMP, + FOREIGN KEY (user_id) REFERENCES users (id) +); + +CREATE TABLE user_sessions ( + id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, + user_id INTEGER NOT NULL, + session_key VARCHAR(255) NOT NULL UNIQUE, + user_ssh_key_id INTEGER, + expires_at TIMESTAMP, + user_agent VARCHAR(255), + ip VARCHAR(255), + FOREIGN KEY (user_id) REFERENCES users (id), + FOREIGN KEY (user_ssh_key_id) REFERENCES user_ssh_keys (id) +); + +CREATE TABLE server_private_keys ( + id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, + ssh_key_type VARCHAR(255) NOT NULL UNIQUE, + ssh_private_key BYTEA NOT NULL UNIQUE +); diff --git a/migrations/postgres/2021-11-08-005634_update_sessions_add_uuid/down.sql b/migrations/postgres/2021-11-08-005634_update_sessions_add_uuid/down.sql new file mode 100644 index 0000000..99c116b 100644 --- /dev/null +++ a/migrations/postgres/2021-11-08-005634_update_sessions_add_uuid/down.sql @@ -1,0 +1,2 @@ +DROP INDEX unique_user_sessions_uuid; +ALTER TABLE user_sessions DROP COLUMN uuid; diff --git a/migrations/postgres/2021-11-08-005634_update_sessions_add_uuid/up.sql b/migrations/postgres/2021-11-08-005634_update_sessions_add_uuid/up.sql new file mode 100644 index 0000000..7a7721a 100644 --- /dev/null +++ a/migrations/postgres/2021-11-08-005634_update_sessions_add_uuid/up.sql @@ -1,0 +1,5 @@ +-- drop all sessions before creating NOT NULL column +DELETE FROM user_sessions; + +ALTER TABLE user_sessions ADD COLUMN uuid UUID NOT NULL; +CREATE UNIQUE INDEX unique_user_sessions_uuid ON user_sessions(uuid); diff --git a/migrations/sqlite/2021-08-31-214501_create_crates_table/down.sql b/migrations/sqlite/2021-08-31-214501_create_crates_table/down.sql new file mode 100644 index 0000000..8322caf 100644 --- /dev/null +++ a/migrations/sqlite/2021-08-31-214501_create_crates_table/down.sql @@ -1,0 +1,9 @@ +DROP TABLE organisations; +DROP TABLE users; +DROP TABLE crates; +DROP TABLE crate_versions; +DROP TABLE user_organisation_permissions; +DROP TABLE user_crate_permissions; +DROP TABLE user_ssh_keys; +DROP TABLE user_sessions; +DROP TABLE server_private_keys; diff --git a/migrations/sqlite/2021-08-31-214501_create_crates_table/up.sql b/migrations/sqlite/2021-08-31-214501_create_crates_table/up.sql new file mode 100644 index 0000000..7ec5834 100644 --- /dev/null +++ a/migrations/sqlite/2021-08-31-214501_create_crates_table/up.sql @@ -1,0 +1,106 @@ +CREATE TABLE users ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + uuid BINARY(128) NOT NULL UNIQUE, + username VARCHAR(255) NOT NULL UNIQUE, + password CHAR(60), + name VARCHAR(255), + nick VARCHAR(255), + email VARCHAR(255), + external_profile_url VARCHAR(2048), + picture_url VARCHAR(2048) +); + +INSERT INTO users (id, uuid, username) VALUES (1, X'936DA01F9ABD4D9D80C702AF85C822A8', "admin"); +INSERT INTO users (id, uuid, username) VALUES (2, X'936DA01F9ABD4D9D80C702AF85C822A9', "billy"); + +CREATE TABLE organisations ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + uuid BINARY(128) NOT NULL UNIQUE, + name VARCHAR(255) NOT NULL UNIQUE, + description TEXT NOT NULL DEFAULT "", + public BOOLEAN NOT NULL DEFAULT FALSE +); + +INSERT INTO organisations (id, uuid, name, description) VALUES (1, X'936DA01F9ABD4D9D80C702AF85C822A8', "core", "My first organisation"); + +CREATE TABLE crates ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + name VARCHAR(255) NOT NULL, + organisation_id INTEGER NOT NULL, + readme TEXT, + description VARCHAR(255), + repository VARCHAR(255), + homepage VARCHAR(255), + documentation VARCHAR(255), + downloads INTEGER NOT NULL DEFAULT 0, + created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + UNIQUE (name, organisation_id), + FOREIGN KEY (organisation_id) REFERENCES organisations (id) +); + +CREATE TABLE crate_versions ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + crate_id INTEGER NOT NULL, + version VARCHAR(255) NOT NULL, + filesystem_object VARCHAR(255) NOT NULL, + size INTEGER NOT NULL, + yanked BOOLEAN NOT NULL DEFAULT FALSE, + checksum VARCHAR(255) NOT NULL, + dependencies BLOB NOT NULL, + features BLOB NOT NULL, + links VARCHAR(255), + user_id INTEGER NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + UNIQUE (crate_id, version), + FOREIGN KEY (user_id) REFERENCES users (id) + FOREIGN KEY (crate_id) REFERENCES crates (id) +); + +CREATE TABLE user_organisation_permissions ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + user_id INTEGER NOT NULL, + organisation_id INTEGER NOT NULL, + permissions INTEGER NOT NULL, + UNIQUE (user_id, organisation_id), + FOREIGN KEY (user_id) REFERENCES users (id) + FOREIGN KEY (organisation_id) REFERENCES organisations (id) +); + +CREATE TABLE user_crate_permissions ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + user_id INTEGER NOT NULL, + crate_id INTEGER NOT NULL, + permissions INTEGER NOT NULL, + UNIQUE (user_id, crate_id), + FOREIGN KEY (user_id) REFERENCES users (id) + FOREIGN KEY (crate_id) REFERENCES crates (id) +); + +CREATE TABLE user_ssh_keys ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + uuid BINARY(128) NOT NULL UNIQUE, + name VARCHAR(255) NOT NULL, + user_id INTEGER NOT NULL, + ssh_key BLOB NOT NULL UNIQUE, + created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, + last_used_at DATETIME, + FOREIGN KEY (user_id) REFERENCES users (id) +); + +CREATE TABLE user_sessions ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + user_id INTEGER NOT NULL, + session_key VARCHAR(255) NOT NULL UNIQUE, + user_ssh_key_id INTEGER, + expires_at DATETIME, + user_agent VARCHAR(255), + ip VARCHAR(255), + FOREIGN KEY (user_id) REFERENCES users (id) + FOREIGN KEY (user_ssh_key_id) REFERENCES user_ssh_keys (id) +); + +CREATE TABLE server_private_keys ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + ssh_key_type VARCHAR(255) NOT NULL UNIQUE, + ssh_private_key BLOB NOT NULL UNIQUE +); diff --git a/migrations/sqlite/2021-11-08-005634_update_sessions_add_uuid/down.sql b/migrations/sqlite/2021-11-08-005634_update_sessions_add_uuid/down.sql new file mode 100644 index 0000000..99c116b 100644 --- /dev/null +++ a/migrations/sqlite/2021-11-08-005634_update_sessions_add_uuid/down.sql @@ -1,0 +1,2 @@ +DROP INDEX unique_user_sessions_uuid; +ALTER TABLE user_sessions DROP COLUMN uuid; diff --git a/migrations/sqlite/2021-11-08-005634_update_sessions_add_uuid/up.sql b/migrations/sqlite/2021-11-08-005634_update_sessions_add_uuid/up.sql new file mode 100644 index 0000000..dc7d397 100644 --- /dev/null +++ a/migrations/sqlite/2021-11-08-005634_update_sessions_add_uuid/up.sql @@ -1,0 +1,5 @@ +-- drop all sessions before creating NOT NULL column +DELETE FROM user_sessions; + +ALTER TABLE user_sessions ADD COLUMN uuid BINARY(128) NOT NULL; +CREATE UNIQUE INDEX unique_user_sessions_uuid ON user_sessions(uuid); -- rgit 0.1.3