🏡 index : ~doyle/chartered.git

67bb17a27be04249f84abd9ad33000ed0ed548f7 [patch]

2f5e234ed55a8acbab46cd2781cf6220784c5027

2e1195cf2c8c79beac1638fd57523193d35704a6

67bb17a27be04249f84abd9ad33000ed0ed548f7.tar.gz

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(-)

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<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)?;
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);