「.NET 開発基盤部会 Wiki」は、「Open棟梁Project」,「OSSコンソーシアム .NET開発基盤部会」によって運営されています。 目次 †概要 †SQLについて。 PostgreSQL †型 †
型チェック †日付でBETWEEN処理したら、"the operator does not exist:"が表示されたので、
JSON/JSONB †
DDL †Master †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, EnterpriseID 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, EnterpriseID integer NOT NULL, StoreID integer NOT NULL, Name VARCHAR(40) NOT NULL, Description VARCHAR(40), CONSTRAINT PK_Devices PRIMARY KEY (ID) ); CREATE TABLE Screens( ID integer NOT NULL, EnterpriseID integer NOT NULL, Name VARCHAR(40) NOT NULL, Url VARCHAR(200) NOT NULL, Description VARCHAR(40), CONSTRAINT PK_Screens PRIMARY KEY (ID) ); INDEX †CREATE INDEX Split_key_Stores ON Stores (EnterpriseID); CLUSTER Stores USING Split_key_Stores; CREATE INDEX Split_key_Devices ON Devices (EnterpriseID); CLUSTER Devices USING Split_key_Devices; CREATE INDEX Split_key_Screens ON Screens (EnterpriseID); CLUSTER Screens USING Split_key_Screens; SEQUENCE †CREATE SEQUENCE TS_EnterpriseID START 1001; CREATE SEQUENCE TS_StoreID START 1001; CREATE SEQUENCE TS_DeviceID START 1001; CREATE SEQUENCE TS_ScreenID START 1001; Transaction †TABLE †CREATE TABLE InitializeScreenInfo( ID integer NOT NULL, EnterpriseID integer NOT NULL, ScreenID 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, EnterpriseID integer NOT NULL, StoreID integer NOT NULL, DeviceID integer NOT NULL, ScreenID integer NOT NULL, InitializeScreenInfoID integer NOT NULL, Datetime timestamp UNIQUE NOT NULL, AnswerID VARCHAR(10) NOT NULL, AdditionalSearchCriteria VARCHAR(40), CONSTRAINT PK_Answers PRIMARY KEY (ID) ); INDEX †CREATE INDEX Split_key_InitializeScreenInfo ON InitializeScreenInfo (EnterpriseID); CLUSTER InitializeScreenInfo USING Split_key_InitializeScreenInfo; CREATE INDEX Split_key_Answers ON Answers (EnterpriseID); CLUSTER Answers USING Split_key_Answers; SEQUENCE †CREATE SEQUENCE TS_InitializeScreenInfo; CREATE SEQUENCE TS_AnswersID; DML †Master †INSERT †--既定(0-1000) ---CMN(共通:0, 0-500) INSERT INTO Enterprises (ID, Name, Description, Phone) VALUES(0, 'CMN', 'CMN', '0'); INSERT INTO Stores (ID, EnterpriseID, Name, Description, Phone) VALUES(0, 0, 'CMN0', 'CMN0', '0'); INSERT INTO Devices (ID, EnterpriseID, StoreID, Name, Description) VALUES(0, 0, 0, 'CMN00', 'CMN00'); INSERT INTO Screens (ID, EnterpriseID, Name, Url, Description) VALUES(0, 0, 'Standard screen', 'http://xxxx', 'Most typical screen.'); ---KYD(試作+デモ用:1, 501-1000) INSERT INTO Enterprises (ID, Name, Description, Phone) VALUES(1, 'KYD', 'KYD', '090-7432-2014'); INSERT INTO Stores (ID, EnterpriseID, Name, Description, Phone) VALUES(1, 0, 'KYD0', 'KYD0', '090-7432-2014'); INSERT INTO Devices (ID, EnterpriseID, StoreID, Name, Description) VALUES(1, 0, 0, 'KYD00', 'KYD00'); INSERT INTO Screens (ID, EnterpriseID, Name, Url, Description) VALUES(501, 1, 'Utah screen', 'http://xxxx', 'Demo in the Utah state.'); --ユーザ定義(1001-, ) --INSERT INTO Enterprises (ID, Name, Description, Phone) VALUES(nextval('TS_EnterpriseID'), 'AAAA', '・・・・・.', '1111'); --INSERT INTO Stores (ID, EnterpriseID, Name, Description, Phone) VALUES(nextval('TS_StoreID'), currval('TS_EnterpriseID'), 'aaaa', '・・・・・.', '1112'); --INSERT INTO Devices (ID, EnterpriseID, StoreID, Name, Description) VALUES(nextval('TS_DeviceID'), currval('TS_EnterpriseID'), currval('TS_StoreID'), '001', '・・・・・.'); --INSERT INTO Screens (ID, EnterpriseID, Name, Url, Description) VALUES(501, currval('TS_EnterpriseID'), 'xxxx', 'http://xxxx', '・・・・・.'); SELECT †SELECT * FROM Enterprises; SELECT * FROM Stores; SELECT * FROM Devices; Transaction †INSERT †--作成順 --共通 INSERT INTO InitializeScreenInfo(ID, EnterpriseID, ScreenID, ExpirationDate, ScreenTitle, AdditionalSearchCriteria, UnstructuredData) VALUES(0, 0, 0, NULL, 'Standard screen', '', '{}'); --デモ INSERT INTO InitializeScreenInfo(ID, EnterpriseID, ScreenID, ExpirationDate, ScreenTitle, AdditionalSearchCriteria, UnstructuredData) VALUES(1, 1, 501, NULL, 'Utah screen', '', '{}'); SELECT †SELECT * FROM InitializeScreenInfo; SELECT * FROM Answers; |