QandAAnalysisEngineのSQL のバックアップ(No.1)

--------------------
-- TABLE:
--------------------
CREATE TABLE Enterprises(
    ID              integer        NOT NULL,
    Name            VARCHAR(40)    NOT NULL,
    Description     VARCHAR(40),
    Phone           VARCHAR(24),
    CONSTRAINT PK_Enterprises PRIMARY KEY (ID)
);

CREATE TABLE Stores(
    ID               integer        NOT NULL,
    EnterprisesID    integer        NOT NULL,
    Name             VARCHAR(40)    NOT NULL,
    Description      VARCHAR(40),
    Phone            VARCHAR(24),
    CONSTRAINT PK_Stores PRIMARY KEY (ID)
);

CREATE TABLE Devices(
    ID               integer        NOT NULL,
    EnterprisesID    integer        NOT NULL,
    StoresID         integer        NOT NULL,
    Name             VARCHAR(40)    NOT NULL,
    Description      VARCHAR(40),
    CONSTRAINT PK_Devices PRIMARY KEY (ID)
);

--------------------
-- Index:
--------------------
CREATE INDEX Split_key_Stores ON Stores (EnterprisesID);
CREATE INDEX Split_key_Devices ON Devices (EnterprisesID);

CLUSTER Stores USING Split_key_Stores;
CLUSTER Devices USING Split_key_Devices;

--------------------
-- Sequence:
--------------------
CREATE SEQUENCE TS_EnterpriseID START 1001;
CREATE SEQUENCE TS_StoreID START 1001;
CREATE SEQUENCE TS_DeviceID START 1001;

--------------------
-- INSERT
--------------------
INSERT INTO Enterprises (ID, Name, Description, Phone) VALUES(0, '0', '0', '0');
INSERT INTO Stores (ID, EnterprisesID, Name, Description, Phone) VALUES(0, 0, '0', '0', '0');
INSERT INTO Devices (ID, EnterprisesID, StoresID, Name, Description) VALUES(0, 0, 0, '0', '0');

INSERT INTO Enterprises (ID, Name, Description, Phone) VALUES(nextval('TS_EnterpriseID'), 'AAAA', '・・・・・', '1111');
INSERT INTO Stores (ID, EnterprisesID, Name, Description, Phone) VALUES(nextval('TS_StoreID'), currval('TS_EnterpriseID'), 'aaaa', '・・・・・', '1112');
INSERT INTO Devices (ID, EnterprisesID, StoresID, Name, Description) VALUES(nextval('TS_DeviceID'), currval('TS_EnterpriseID'), currval('TS_StoreID'), '001', '・・・・・');

--------------------
-- SELECT
--------------------
SELECT * FROM Enterprises;
SELECT * FROM Stores;
SELECT * FROM Devices;

Transaction

--------------------
-- TABLE:
--------------------
CREATE TABLE InitializeScreenInfo(
    ID                        integer        NOT NULL,
    EnterprisesID             integer        NOT NULL,
    ExpirationDate            date,
    ScreenTitle               VARCHAR(40),
    AdditionalSearchCriteria  VARCHAR(24),
    UnstructuredData          jsonb,
    CONSTRAINT PK_InitializeScreenInfo PRIMARY KEY (ID)
);

CREATE TABLE Answers(
    ID                       integer        NOT NULL,
    EnterprisesID            integer        NOT NULL,
    InitializeScreenInfoID   integer        NOT NULL,
    StoresID                 integer        NOT NULL,
    DevicesID                integer        NOT NULL,
    Datetime                 timestamp	     NOT NULL,
    AnswerID                 integer        NOT NULL,
    AdditionalSearchCriteria VARCHAR(40),
    CONSTRAINT PK_Answers PRIMARY KEY (ID)
);

--------------------
-- Index:
--------------------
CREATE INDEX Split_key_InitializeScreenInfo ON Stores (EnterprisesID);
CREATE INDEX Split_key_Answers ON Devices (EnterprisesID);

CLUSTER Stores USING Split_key_InitializeScreenInfo;
CLUSTER Devices USING Split_key_Answers;

--------------------
-- Sequence:
--------------------
CREATE SEQUENCE TS_InitializeScreenInfo;
CREATE SEQUENCE TS_AnswersID;

--------------------
-- INSERT
--------------------

--------------------
-- SELECT
--------------------
SELECT * FROM InitializeScreenInfo;
SELECT * FROM Answers;