1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211
| create table access ( access_id int NOT NULL AUTO_INCREMENT, access_code char(1), comment varchar (30), primary key (access_id) ); insert into access (access_code, comment) values ('M', 'Management access for project'), ('R', 'Read access for project'), ('W', 'Write access for project'), ('D', 'Delete access for project'), ('S', 'Search access for project');
create table role ( role_id int NOT NULL AUTO_INCREMENT, role_mask int DEFAULT 0 NOT NULL, role_code varchar(20), name varchar (20), primary key (role_id) ); insert into role (role_code, name) values ('MDRWS', 'projectAdmin'), ('RWS', 'developer'), ('RS', 'guest');
create table user ( user_id int NOT NULL AUTO_INCREMENT, username varchar(255), email varchar(255), password varchar(40) NOT NULL, realname varchar (255) NOT NULL, comment varchar (30), deleted tinyint (1) DEFAULT 0 NOT NULL, reset_uuid varchar(40) DEFAULT NULL, salt varchar(40) DEFAULT NULL, sysadmin_flag tinyint (1), creation_time timestamp NOT NULL default CURRENT_TIMESTAMP, update_time timestamp NOT NULL default CURRENT_TIMESTAMP, primary key (user_id), UNIQUE (username), UNIQUE (email) ); insert into user (username, email, password, realname, comment, deleted, sysadmin_flag, creation_time, update_time) values ('admin', 'admin@example.com', '', 'system admin', 'admin user',0, 1, NOW(), NOW()),('anonymous','anonymous@example.com', '', 'anonymous user', 'anonymous user', 1, 0, NOW(), NOW());
create table project ( project_id int NOT NULL AUTO_INCREMENT, owner_id int NOT NULL, name varchar (255) NOT NULL, creation_time timestamp NOT NULL default CURRENT_TIMESTAMP, update_time timestamp NOT NULL default CURRENT_TIMESTAMP, deleted tinyint (1) DEFAULT 0 NOT NULL, primary key (project_id), FOREIGN KEY (owner_id) REFERENCES user(user_id), UNIQUE (name) ); insert into project (owner_id, name, creation_time, update_time) values (1, 'library', NOW(), NOW());
create table project_member ( project_id int NOT NULL, user_id int NOT NULL, role int NOT NULL, creation_time timestamp NOT NULL default CURRENT_TIMESTAMP, update_time timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (project_id, user_id), FOREIGN KEY (role) REFERENCES role(role_id), FOREIGN KEY (project_id) REFERENCES project(project_id), FOREIGN KEY (user_id) REFERENCES user(user_id) ); insert into project_member (project_id, user_id, role, creation_time, update_time) values(1, 1, 1, NOW(), NOW());
create table project_metadata ( id int NOT NULL AUTO_INCREMENT, project_id int NOT NULL, name varchar(255) NOT NULL, value varchar(255), creation_time timestamp default CURRENT_TIMESTAMP, update_time timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, deleted tinyint (1) DEFAULT 0 NOT NULL, PRIMARY KEY (id), CONSTRAINT unique_project_id_and_name UNIQUE (project_id,name), FOREIGN KEY (project_id) REFERENCES project(project_id) ); insert into project_metadata (id, project_id, name, value, creation_time, update_time, deleted) values (1, 1, 'public', 'true', NOW(), NOW(), 0);
create table access_log ( log_id int NOT NULL AUTO_INCREMENT, username varchar (255) NOT NULL, project_id int NOT NULL, repo_name varchar (256), repo_tag varchar (128), GUID varchar(64), operation varchar(20) NOT NULL, op_time timestamp NOT NULL default CURRENT_TIMESTAMP, primary key (log_id), INDEX pid_optime (project_id, op_time) );
create table repository ( repository_id int NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, project_id int NOT NULL, description text, pull_count int DEFAULT 0 NOT NULL, star_count int DEFAULT 0 NOT NULL, creation_time timestamp default CURRENT_TIMESTAMP, update_time timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, primary key (repository_id), UNIQUE (name) );
create table replication_policy ( id int NOT NULL AUTO_INCREMENT, name varchar(256), project_id int NOT NULL, target_id int NOT NULL, enabled tinyint(1) NOT NULL DEFAULT 1, description text, deleted tinyint (1) DEFAULT 0 NOT NULL, cron_str varchar(256), filters varchar(1024), replicate_deletion tinyint (1) DEFAULT 0 NOT NULL, start_time timestamp NULL, creation_time timestamp default CURRENT_TIMESTAMP, update_time timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (id) );
create table replication_target ( id int NOT NULL AUTO_INCREMENT, name varchar(64), url varchar(64), username varchar(255), password varchar(128), target_type tinyint(1) NOT NULL DEFAULT 0, insecure tinyint(1) NOT NULL DEFAULT 0, creation_time timestamp default CURRENT_TIMESTAMP, update_time timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (id) );
create table replication_job ( id int NOT NULL AUTO_INCREMENT, status varchar(64) NOT NULL, policy_id int NOT NULL, repository varchar(256) NOT NULL, operation varchar(64) NOT NULL, tags varchar(16384), creation_time timestamp default CURRENT_TIMESTAMP, update_time timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX policy (policy_id), INDEX poid_uptime (policy_id, update_time) );
create table replication_immediate_trigger ( id int NOT NULL AUTO_INCREMENT, policy_id int NOT NULL, namespace varchar(256) NOT NULL, on_push tinyint(1) NOT NULL DEFAULT 0, on_deletion tinyint(1) NOT NULL DEFAULT 0, creation_time timestamp default CURRENT_TIMESTAMP, update_time timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (id) );
create table img_scan_job ( id int NOT NULL AUTO_INCREMENT, status varchar(64) NOT NULL, repository varchar(256) NOT NULL, tag varchar(128) NOT NULL, digest varchar(128), creation_time timestamp default CURRENT_TIMESTAMP, update_time timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (id) );
create table img_scan_overview ( id int NOT NULL AUTO_INCREMENT, image_digest varchar(128) NOT NULL, scan_job_id int NOT NULL, severity int NOT NULL default 0, components_overview varchar(2048), details_key varchar(128), creation_time timestamp default CURRENT_TIMESTAMP, update_time timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY(id), UNIQUE(image_digest) );
create table clair_vuln_timestamp ( id int NOT NULL AUTO_INCREMENT, namespace varchar(128) NOT NULL, last_update timestamp NOT NULL, PRIMARY KEY(id), UNIQUE(namespace) );
create table properties ( id int NOT NULL AUTO_INCREMENT, k varchar(64) NOT NULL, v varchar(128) NOT NULL, PRIMARY KEY(id), UNIQUE (k) );
CREATE TABLE IF NOT EXISTS `alembic_version` ( `version_num` varchar(32) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into alembic_version values ('1.4.0');
|