.NET 開発基盤部会 Wiki」は、「Open棟梁Project」,「OSSコンソーシアム .NET開発基盤部会」によって運営されています。

目次

概要

SQLについて。

PostgreSQL

型チェック

日付でBETWEEN処理したら、"the operator does not exist:"が表示されたので、
NpgsqlParameter?に、NpgsqlTypes?.NpgsqlDbType?を明示した。

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;

添付ファイル: fileDDL_AntSurvey.sql 49件 [詳細]

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2017-02-27 (月) 20:17:19 (847d)