Build migrations specifically for Postgres
Diff
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(-)
@@ -73,7 +73,11 @@
pub type ConnectionPool = Arc<Pool<ConnectionManager<Connection>>>;
pub type Result<T> = std::result::Result<T, Error>;
embed_migrations!();
#[cfg(feature = "postgres")]
embed_migrations!("../migrations/postgres");
#[cfg(feature = "sqlite")]
embed_migrations!("../migrations/sqlite");
pub fn init(connection_uri: &str) -> Result<ConnectionPool> {
let connection_uri = parse_connection_uri(connection_uri)?;
@@ -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;
@@ -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
);
@@ -1,2 +1,0 @@
DROP INDEX unique_user_sessions_uuid;
ALTER TABLE user_sessions DROP COLUMN uuid;
@@ -1,5 +1,0 @@
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);
@@ -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;
@@ -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
);
@@ -1,0 +1,2 @@
DROP INDEX unique_user_sessions_uuid;
ALTER TABLE user_sessions DROP COLUMN uuid;
@@ -1,0 +1,5 @@
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);
@@ -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;
@@ -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
);
@@ -1,0 +1,2 @@
DROP INDEX unique_user_sessions_uuid;
ALTER TABLE user_sessions DROP COLUMN uuid;
@@ -1,0 +1,5 @@
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);