devDB.script

CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE DEVICE_MANUFACTURER(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,MANUFACTURER_NAME VARCHAR(255) NOT NULL)
CREATE MEMORY TABLE DEVICE_MODEL(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,MANUFACTURER_ID BIGINT NOT NULL,MODEL_NAME VARCHAR(255) NOT NULL,CONSTRAINT FKDCC4E40025F67029 FOREIGN KEY(MANUFACTURER_ID) REFERENCES DEVICE_MANUFACTURER(ID))
CREATE MEMORY TABLE DEVICE_STATUS(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,STATUS VARCHAR(255) NOT NULL)
CREATE MEMORY TABLE INSTALLATION(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,NAME VARCHAR(255) NOT NULL,CONFIGURATION_ID BIGINT,LONG_OFFSET FLOAT,PROJECT_ID BIGINT,LAT_OFFSET FLOAT,LON_OFFSET FLOAT)
CREATE MEMORY TABLE ORGANISATION(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,FAX_NUMBER VARCHAR(255) NOT NULL,NAME VARCHAR(255) NOT NULL,PHONE_NUMBER VARCHAR(255) NOT NULL,POSTAL_ADDRESS VARCHAR(255) NOT NULL)
CREATE MEMORY TABLE ORGANISATION_PROJECT(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,ORGANISATION_ID BIGINT NOT NULL,PROJECT_ID BIGINT NOT NULL,CONSTRAINT FKF3B978B499B5ECD3 FOREIGN KEY(ORGANISATION_ID) REFERENCES ORGANISATION(ID))
CREATE MEMORY TABLE PERSON(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,EMAIL_ADDRESS VARCHAR(255) NOT NULL,FIRST_NAME VARCHAR(255) NOT NULL,LAST_NAME VARCHAR(255) NOT NULL)
CREATE MEMORY TABLE PROJECT(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,DESCRIPTION VARCHAR(255) NOT NULL,NAME VARCHAR(255) NOT NULL)
CREATE MEMORY TABLE PROJECT_ROLE(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,PERSON_ID BIGINT NOT NULL,PROJECT_ID BIGINT NOT NULL,ROLE_TYPE_ID BIGINT NOT NULL,CONSTRAINT FK37FFF5DCBF505A21 FOREIGN KEY(PROJECT_ID) REFERENCES PROJECT(ID),CONSTRAINT FK37FFF5DCE985CDB3 FOREIGN KEY(PERSON_ID) REFERENCES PERSON(ID))
CREATE MEMORY TABLE PROJECT_ROLE_TYPE(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,DISPLAY_NAME VARCHAR(255) NOT NULL)
CREATE MEMORY TABLE RECEIVER(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,CODE_NAME VARCHAR(255) NOT NULL,EMBARGO_DATE TIMESTAMP NOT NULL,MODEL_ID BIGINT NOT NULL,PROJECT_ID BIGINT NOT NULL,SERIAL_NUMBER VARCHAR(255) NOT NULL,STATUS_ID BIGINT NOT NULL,STATION_ID BIGINT,CONSTRAINT FKCFCBE9EFBF505A21 FOREIGN KEY(PROJECT_ID) REFERENCES PROJECT(ID),CONSTRAINT FKCFCBE9EF6B73E7C9 FOREIGN KEY(STATUS_ID) REFERENCES DEVICE_STATUS(ID),CONSTRAINT FKCFCBE9EF1C043BEB FOREIGN KEY(MODEL_ID) REFERENCES DEVICE_MODEL(ID))
CREATE MEMORY TABLE SENSOR(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,CODE_MAP VARCHAR(255) NOT NULL,INTERCEPT INTEGER NOT NULL,PING_CODE INTEGER NOT NULL,SLOPE INTEGER NOT NULL,TAG_ID BIGINT NOT NULL,TRANSMITTER_TYPE_ID BIGINT NOT NULL)
CREATE MEMORY TABLE TAG(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,CODE_NAME VARCHAR(255) NOT NULL,EMBARGO_DATE TIMESTAMP NOT NULL,MODEL_ID BIGINT NOT NULL,PROJECT_ID BIGINT NOT NULL,SERIAL_NUMBER VARCHAR(255) NOT NULL,STATUS_ID BIGINT NOT NULL,CONSTRAINT FK1BF9ABF505A21 FOREIGN KEY(PROJECT_ID) REFERENCES PROJECT(ID),CONSTRAINT FK1BF9A6B73E7C9 FOREIGN KEY(STATUS_ID) REFERENCES DEVICE_STATUS(ID),CONSTRAINT FK1BF9A1C043BEB FOREIGN KEY(MODEL_ID) REFERENCES DEVICE_MODEL(ID))
CREATE MEMORY TABLE TRANSMITTER_TYPE(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,TRANSMITTER_TYPE_NAME VARCHAR(255) NOT NULL)
CREATE MEMORY TABLE INSTALLATION_CONFIGURATION(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,TYPE VARCHAR(255) NOT NULL)
CREATE MEMORY TABLE INSTALLATION_STATION(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,CURTAIN_POSITION INTEGER NOT NULL,INSTALLATION_ID BIGINT NOT NULL,NAME VARCHAR(255) NOT NULL,LOCATION VARBINARY(255),CONSTRAINT FK902C2C2F35E870D3 FOREIGN KEY(INSTALLATION_ID) REFERENCES INSTALLATION(ID))
CREATE MEMORY TABLE DETECTION(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,RECEIVER_ID BIGINT NOT NULL,TAG_ID BIGINT NOT NULL,TIMESTAMP TIMESTAMP NOT NULL,CONSTRAINT FK90E7CA85F0BB6D33 FOREIGN KEY(RECEIVER_ID) REFERENCES RECEIVER(ID),CONSTRAINT FK90E7CA85CEAB1A01 FOREIGN KEY(TAG_ID) REFERENCES TAG(ID))
ALTER TABLE INSTALLATION ADD CONSTRAINT FK796D5E3ABF505A21 FOREIGN KEY(PROJECT_ID) REFERENCES PROJECT(ID)
ALTER TABLE INSTALLATION ADD CONSTRAINT FK796D5E3A18D65D27 FOREIGN KEY(CONFIGURATION_ID) REFERENCES INSTALLATION_CONFIGURATION(ID)
ALTER TABLE ORGANISATION_PROJECT ADD CONSTRAINT FKF3B978B4BF505A21 FOREIGN KEY(PROJECT_ID) REFERENCES PROJECT(ID)
ALTER TABLE PROJECT_ROLE ADD CONSTRAINT FK37FFF5DC51218487 FOREIGN KEY(ROLE_TYPE_ID) REFERENCES PROJECT_ROLE_TYPE(ID)
ALTER TABLE RECEIVER ADD CONSTRAINT FKCFCBE9EFCDAF3227 FOREIGN KEY(STATION_ID) REFERENCES INSTALLATION_STATION(ID)
ALTER TABLE SENSOR ADD CONSTRAINT FKCA0053BA4B0C3BC4 FOREIGN KEY(TRANSMITTER_TYPE_ID) REFERENCES TRANSMITTER_TYPE(ID)
ALTER TABLE SENSOR ADD CONSTRAINT FKCA0053BACEAB1A01 FOREIGN KEY(TAG_ID) REFERENCES TAG(ID)
ALTER TABLE DEVICE_MANUFACTURER ALTER COLUMN ID RESTART WITH 1
ALTER TABLE DEVICE_MODEL ALTER COLUMN ID RESTART WITH 1
ALTER TABLE DEVICE_STATUS ALTER COLUMN ID RESTART WITH 1
ALTER TABLE INSTALLATION ALTER COLUMN ID RESTART WITH 2
ALTER TABLE ORGANISATION ALTER COLUMN ID RESTART WITH 1
ALTER TABLE ORGANISATION_PROJECT ALTER COLUMN ID RESTART WITH 1
ALTER TABLE PERSON ALTER COLUMN ID RESTART WITH 1
ALTER TABLE PROJECT ALTER COLUMN ID RESTART WITH 2
ALTER TABLE PROJECT_ROLE ALTER COLUMN ID RESTART WITH 1
ALTER TABLE PROJECT_ROLE_TYPE ALTER COLUMN ID RESTART WITH 1
ALTER TABLE RECEIVER ALTER COLUMN ID RESTART WITH 1
ALTER TABLE SENSOR ALTER COLUMN ID RESTART WITH 1
ALTER TABLE TAG ALTER COLUMN ID RESTART WITH 1
ALTER TABLE TRANSMITTER_TYPE ALTER COLUMN ID RESTART WITH 1
ALTER TABLE INSTALLATION_CONFIGURATION ALTER COLUMN ID RESTART WITH 3
ALTER TABLE INSTALLATION_STATION ALTER COLUMN ID RESTART WITH 1
ALTER TABLE DETECTION ALTER COLUMN ID RESTART WITH 1
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 10
SET SCHEMA PUBLIC
INSERT INTO INSTALLATION VALUES(1,0,'maria island',1,123.0E0,1,NULL,NULL)
INSERT INTO PROJECT VALUES(1,0,'seal count','seal count')
INSERT INTO INSTALLATION_CONFIGURATION VALUES(1,0,'ARRAY')
INSERT INTO INSTALLATION_CONFIGURATION VALUES(2,0,'CURTAIN')