diff --git a/src/main/resources/update/2_9.sql b/src/main/resources/update/2_9.sql deleted file mode 100644 index 3ddc48a..0000000 --- a/src/main/resources/update/2_9.sql +++ /dev/null @@ -1,42 +0,0 @@ -DROP TABLE IF EXISTS ACCESS_TOKEN; - -CREATE TABLE ACCESS_TOKEN ( - ACCESS_TOKEN_ID INT NOT NULL AUTO_INCREMENT, - TOKEN_HASH VARCHAR(40) NOT NULL, - USER_NAME VARCHAR(100) NOT NULL, - NOTE TEXT NOT NULL -); - -ALTER TABLE ACCESS_TOKEN ADD CONSTRAINT IDX_ACCESS_TOKEN_PK PRIMARY KEY (ACCESS_TOKEN_ID); -ALTER TABLE ACCESS_TOKEN ADD CONSTRAINT IDX_ACCESS_TOKEN_FK0 FOREIGN KEY (USER_NAME) REFERENCES ACCOUNT (USER_NAME) - ON DELETE CASCADE ON UPDATE CASCADE; -ALTER TABLE ACCESS_TOKEN ADD CONSTRAINT IDX_ACCESS_TOKEN_TOKEN_HASH UNIQUE(TOKEN_HASH); - - -DROP TABLE IF EXISTS COMMIT_STATUS; -CREATE TABLE COMMIT_STATUS( - COMMIT_STATUS_ID INT AUTO_INCREMENT, - USER_NAME VARCHAR(100) NOT NULL, - REPOSITORY_NAME VARCHAR(100) NOT NULL, - COMMIT_ID VARCHAR(40) NOT NULL, - CONTEXT VARCHAR(255) NOT NULL, -- context is too long (maximum is 255 characters) - STATE VARCHAR(10) NOT NULL, -- pending, success, error, or failure - TARGET_URL VARCHAR(200), - DESCRIPTION TEXT, - CREATOR VARCHAR(100) NOT NULL, - REGISTERED_DATE TIMESTAMP NOT NULL, -- CREATED_AT - UPDATED_DATE TIMESTAMP NOT NULL -- UPDATED_AT -); -ALTER TABLE COMMIT_STATUS ADD CONSTRAINT IDX_COMMIT_STATUS_PK PRIMARY KEY (COMMIT_STATUS_ID); -ALTER TABLE COMMIT_STATUS ADD CONSTRAINT IDX_COMMIT_STATUS_1 - UNIQUE (USER_NAME, REPOSITORY_NAME, COMMIT_ID, CONTEXT); -ALTER TABLE COMMIT_STATUS ADD CONSTRAINT IDX_COMMIT_STATUS_FK1 - FOREIGN KEY (USER_NAME, REPOSITORY_NAME) - REFERENCES REPOSITORY (USER_NAME, REPOSITORY_NAME) - ON DELETE CASCADE ON UPDATE CASCADE; -ALTER TABLE COMMIT_STATUS ADD CONSTRAINT IDX_COMMIT_STATUS_FK2 - FOREIGN KEY (USER_NAME) REFERENCES ACCOUNT (USER_NAME) - ON DELETE CASCADE ON UPDATE CASCADE; -ALTER TABLE COMMIT_STATUS ADD CONSTRAINT IDX_COMMIT_STATUS_FK3 - FOREIGN KEY (CREATOR) REFERENCES ACCOUNT (USER_NAME) - ON DELETE CASCADE ON UPDATE CASCADE; diff --git a/src/main/resources/update/3_1.sql b/src/main/resources/update/3_1.sql new file mode 100644 index 0000000..3ddc48a --- /dev/null +++ b/src/main/resources/update/3_1.sql @@ -0,0 +1,42 @@ +DROP TABLE IF EXISTS ACCESS_TOKEN; + +CREATE TABLE ACCESS_TOKEN ( + ACCESS_TOKEN_ID INT NOT NULL AUTO_INCREMENT, + TOKEN_HASH VARCHAR(40) NOT NULL, + USER_NAME VARCHAR(100) NOT NULL, + NOTE TEXT NOT NULL +); + +ALTER TABLE ACCESS_TOKEN ADD CONSTRAINT IDX_ACCESS_TOKEN_PK PRIMARY KEY (ACCESS_TOKEN_ID); +ALTER TABLE ACCESS_TOKEN ADD CONSTRAINT IDX_ACCESS_TOKEN_FK0 FOREIGN KEY (USER_NAME) REFERENCES ACCOUNT (USER_NAME) + ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE ACCESS_TOKEN ADD CONSTRAINT IDX_ACCESS_TOKEN_TOKEN_HASH UNIQUE(TOKEN_HASH); + + +DROP TABLE IF EXISTS COMMIT_STATUS; +CREATE TABLE COMMIT_STATUS( + COMMIT_STATUS_ID INT AUTO_INCREMENT, + USER_NAME VARCHAR(100) NOT NULL, + REPOSITORY_NAME VARCHAR(100) NOT NULL, + COMMIT_ID VARCHAR(40) NOT NULL, + CONTEXT VARCHAR(255) NOT NULL, -- context is too long (maximum is 255 characters) + STATE VARCHAR(10) NOT NULL, -- pending, success, error, or failure + TARGET_URL VARCHAR(200), + DESCRIPTION TEXT, + CREATOR VARCHAR(100) NOT NULL, + REGISTERED_DATE TIMESTAMP NOT NULL, -- CREATED_AT + UPDATED_DATE TIMESTAMP NOT NULL -- UPDATED_AT +); +ALTER TABLE COMMIT_STATUS ADD CONSTRAINT IDX_COMMIT_STATUS_PK PRIMARY KEY (COMMIT_STATUS_ID); +ALTER TABLE COMMIT_STATUS ADD CONSTRAINT IDX_COMMIT_STATUS_1 + UNIQUE (USER_NAME, REPOSITORY_NAME, COMMIT_ID, CONTEXT); +ALTER TABLE COMMIT_STATUS ADD CONSTRAINT IDX_COMMIT_STATUS_FK1 + FOREIGN KEY (USER_NAME, REPOSITORY_NAME) + REFERENCES REPOSITORY (USER_NAME, REPOSITORY_NAME) + ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE COMMIT_STATUS ADD CONSTRAINT IDX_COMMIT_STATUS_FK2 + FOREIGN KEY (USER_NAME) REFERENCES ACCOUNT (USER_NAME) + ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE COMMIT_STATUS ADD CONSTRAINT IDX_COMMIT_STATUS_FK3 + FOREIGN KEY (CREATOR) REFERENCES ACCOUNT (USER_NAME) + ON DELETE CASCADE ON UPDATE CASCADE;