41 lines
2.1 KiB
SQL
41 lines
2.1 KiB
SQL
CREATE TABLE pgit_users (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
username VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
|
|
password_hash VARCHAR(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
|
|
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY pgit_users_username_unique (username)
|
|
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE pgit_access_tokens (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
user_id BIGINT UNSIGNED NOT NULL,
|
|
name VARCHAR(80) NOT NULL,
|
|
token_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
last_used_at TIMESTAMP NULL DEFAULT NULL,
|
|
expires_at TIMESTAMP NULL DEFAULT NULL,
|
|
revoked_at TIMESTAMP NULL DEFAULT NULL,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY pgit_access_tokens_hash_unique (token_hash),
|
|
KEY pgit_access_tokens_user_active (user_id, revoked_at),
|
|
CONSTRAINT pgit_access_tokens_user_foreign
|
|
FOREIGN KEY (user_id) REFERENCES pgit_users (id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE pgit_repositories (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
repository_name VARCHAR(68) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
|
|
owner_user_id BIGINT UNSIGNED NOT NULL,
|
|
is_private TINYINT(1) NOT NULL DEFAULT 0,
|
|
is_ready TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY pgit_repositories_name_unique (repository_name),
|
|
KEY pgit_repositories_owner (owner_user_id),
|
|
CONSTRAINT pgit_repositories_owner_foreign
|
|
FOREIGN KEY (owner_user_id) REFERENCES pgit_users (id) ON DELETE RESTRICT
|
|
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |