QandAAnalysisEngineのSQL

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

• 戻る
  • 業務プロセス
  • 画面
  • テーブル
  • SQL

目次 †

概要 †

SQLについて。

PostgreSQL †

型 †

• データ型 - PostgreSQL 9.1.5文書
  https://www.postgresql.jp/document/9.1/html/datatype.html

• [ThinkIT] 第1回：データベース移行 (2/2)
  https://thinkit.co.jp/cert/marugoto/2/1/19/2.htm
  • PostgreSQLのデータ型
    https://thinkit.co.jp/cert/marugoto/2/1/19/2table.htm

型チェック †

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

• PostgreSQL 8.3 から型チェックが厳密になっている件 - takami_hirokiの日記
  http://d.hatena.ne.jp/takami_hiroki/20101007/p1
• npgsql, arrays and C# the operator does not exist: text[] = text - Stack Overflow
  http://stackoverflow.com/questions/21146606/npgsql-arrays-and-c-sharp-the-operator-does-not-exist-text-text

• Date and Time
  http://www.npgsql.org/doc/datetime.html
• PostgreSQL and C# Datatypes - Stack Overflow
  http://stackoverflow.com/questions/845458/postgresql-and-c-sharp-datatypes

JSON/JSONB †

• 8.14. JSONデータ型
  https://www.postgresql.jp/document/9.5/html/datatype-json.html

• PGCon2014(2014-12-05)JSONB データ型を使ってみよう
  https://www.postgresql.jp/events/jpugpgcon2014files/B5_PGCon2014-JSONB-datatype
• PostgreSQL 9.5のJSONBの部分更新 — | サイオスOSS | サイオステクノロジー
  https://oss.sios.com/yorozu-blog/postgresql95-jsonb

• PostgreSQL 9.3 の JSON サポートについて（長いよッ） - Qiita
  http://qiita.com/kumazo@github/items/483f47360f8b61a9fbb9
• PostgreSQL 9.4 の JSON サポートについて - Qiita
  http://qiita.com/kumazo@github/items/9010f9dad134b9d9d16d
• PostgreSQL JSON の応用メモ - Qiita
  http://qiita.com/kumazo@github/items/19361acffddb6be10ac2

• Querying JSON in Postgres - Schinckel.net
  http://schinckel.net/2014/05/25/querying-json-in-postgres/

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;