QandAAnalysisEngineのSQL
をテンプレートにして作成
[
トップ
] [
新規
|
一覧
|
単語検索
|
最終更新
|
ヘルプ
]
開始行:
「[[.NET 開発基盤部会 Wiki>http://dotnetdevelopmentinfras...
-[[戻る>QandAAnalysisEngineの開発で検討したこと。#y76d016...
--[[業務プロセス>QandAAnalysisEngineの業務プロセス]]
--[[画面>QandAAnalysisEngineの画面]]
--[[テーブル>QandAAnalysisEngineのテーブル]]
--SQL
*目次 [#s6d6a31b]
#contents
*概要 [#lf5d23fa]
SQLについて。
*PostgreSQL [#c560bbd4]
**型 [#l5e9f96b]
-データ型 - 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
***型チェック [#mf6186f8]
日付でBETWEEN処理したら、"the operator does not exist:"が...
NpgsqlParameterに、NpgsqlTypes.NpgsqlDbTypeを明示した。
-PostgreSQL 8.3 から型チェックが厳密になっている件 - taka...
http://d.hatena.ne.jp/takami_hiroki/20101007/p1
-npgsql, arrays and C# the operator does not exist: text[...
http://stackoverflow.com/questions/21146606/npgsql-arrays...
-Date and Time~
http://www.npgsql.org/doc/datetime.html
-PostgreSQL and C# Datatypes - Stack Overflow~
http://stackoverflow.com/questions/845458/postgresql-and-...
***JSON/JSONB [#t1e8467b]
-8.14. JSONデータ型~
https://www.postgresql.jp/document/9.5/html/datatype-json...
-PGCon2014(2014-12-05)JSONB データ型を使ってみよう~
https://www.postgresql.jp/events/jpugpgcon2014files/B5_PG...
-PostgreSQL 9.5のJSONBの部分更新 — | サイオスOSS | サイオ...
https://oss.sios.com/yorozu-blog/postgresql95-jsonb
-PostgreSQL 9.3 の JSON サポートについて（長いよッ） - Qi...
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 [#raa323ea]
**Master [#xafaf47b]
***TABLE [#la73dd38]
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 [#yd8d7c23]
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 [#xf573ade]
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 [#kf26db27]
***TABLE [#g1c849f2]
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 N...
AnswerID VARCHAR(10) NOT NULL,
AdditionalSearchCriteria VARCHAR(40),
CONSTRAINT PK_Answers PRIMARY KEY (ID)
);
***INDEX [#a14458ab]
CREATE INDEX Split_key_InitializeScreenInfo ON Initializ...
CLUSTER InitializeScreenInfo USING Split_key_InitializeS...
CREATE INDEX Split_key_Answers ON Answers (EnterpriseID);
CLUSTER Answers USING Split_key_Answers;
***SEQUENCE [#w1732312]
CREATE SEQUENCE TS_InitializeScreenInfo;
CREATE SEQUENCE TS_AnswersID;
*DML [#wc684f9f]
**Master [#v56f43ad]
***INSERT [#pf0ba1a6]
--既定（0-1000）
---CMN(共通:0, 0-500)
INSERT INTO Enterprises (ID, Name, Description, Phone) ...
INSERT INTO Stores (ID, EnterpriseID, Name, Descrip...
INSERT INTO Devices (ID, EnterpriseID, StoreID, Name...
INSERT INTO Screens (ID, EnterpriseID, Name, Url, De...
---KYD（試作＋デモ用:1, 501-1000）
INSERT INTO Enterprises (ID, Name, Description, Phone) ...
INSERT INTO Stores (ID, EnterpriseID, Name, Descrip...
INSERT INTO Devices (ID, EnterpriseID, StoreID, Name...
INSERT INTO Screens (ID, EnterpriseID, Name, Url, De...
--ユーザ定義(1001-, )
--INSERT INTO Enterprises (ID, Name, Description, Phone)...
--INSERT INTO Stores (ID, EnterpriseID, Name, Descr...
--INSERT INTO Devices (ID, EnterpriseID, StoreID, Na...
--INSERT INTO Screens (ID, EnterpriseID, Name, Url, ...
***SELECT [#uef2c3a5]
SELECT * FROM Enterprises;
SELECT * FROM Stores;
SELECT * FROM Devices;
**Transaction [#y003efbb]
***INSERT [#q6f49504]
--作成順
--共通
INSERT INTO
InitializeScreenInfo(ID, EnterpriseID, ScreenID, Expir...
VALUES(0, 0, 0, NULL, 'Standard screen', '', '{}');
--デモ
INSERT INTO
InitializeScreenInfo(ID, EnterpriseID, ScreenID, Expir...
VALUES(1, 1, 501, NULL, 'Utah screen', '', '{}');
***SELECT [#x0d1feff]
SELECT * FROM InitializeScreenInfo;
SELECT * FROM Answers;
終了行:
「[[.NET 開発基盤部会 Wiki>http://dotnetdevelopmentinfras...
-[[戻る>QandAAnalysisEngineの開発で検討したこと。#y76d016...
--[[業務プロセス>QandAAnalysisEngineの業務プロセス]]
--[[画面>QandAAnalysisEngineの画面]]
--[[テーブル>QandAAnalysisEngineのテーブル]]
--SQL
*目次 [#s6d6a31b]
#contents
*概要 [#lf5d23fa]
SQLについて。
*PostgreSQL [#c560bbd4]
**型 [#l5e9f96b]
-データ型 - 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
***型チェック [#mf6186f8]
日付でBETWEEN処理したら、"the operator does not exist:"が...
NpgsqlParameterに、NpgsqlTypes.NpgsqlDbTypeを明示した。
-PostgreSQL 8.3 から型チェックが厳密になっている件 - taka...
http://d.hatena.ne.jp/takami_hiroki/20101007/p1
-npgsql, arrays and C# the operator does not exist: text[...
http://stackoverflow.com/questions/21146606/npgsql-arrays...
-Date and Time~
http://www.npgsql.org/doc/datetime.html
-PostgreSQL and C# Datatypes - Stack Overflow~
http://stackoverflow.com/questions/845458/postgresql-and-...
***JSON/JSONB [#t1e8467b]
-8.14. JSONデータ型~
https://www.postgresql.jp/document/9.5/html/datatype-json...
-PGCon2014(2014-12-05)JSONB データ型を使ってみよう~
https://www.postgresql.jp/events/jpugpgcon2014files/B5_PG...
-PostgreSQL 9.5のJSONBの部分更新 — | サイオスOSS | サイオ...
https://oss.sios.com/yorozu-blog/postgresql95-jsonb
-PostgreSQL 9.3 の JSON サポートについて（長いよッ） - Qi...
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 [#raa323ea]
**Master [#xafaf47b]
***TABLE [#la73dd38]
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 [#yd8d7c23]
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 [#xf573ade]
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 [#kf26db27]
***TABLE [#g1c849f2]
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 N...
AnswerID VARCHAR(10) NOT NULL,
AdditionalSearchCriteria VARCHAR(40),
CONSTRAINT PK_Answers PRIMARY KEY (ID)
);
***INDEX [#a14458ab]
CREATE INDEX Split_key_InitializeScreenInfo ON Initializ...
CLUSTER InitializeScreenInfo USING Split_key_InitializeS...
CREATE INDEX Split_key_Answers ON Answers (EnterpriseID);
CLUSTER Answers USING Split_key_Answers;
***SEQUENCE [#w1732312]
CREATE SEQUENCE TS_InitializeScreenInfo;
CREATE SEQUENCE TS_AnswersID;
*DML [#wc684f9f]
**Master [#v56f43ad]
***INSERT [#pf0ba1a6]
--既定（0-1000）
---CMN(共通:0, 0-500)
INSERT INTO Enterprises (ID, Name, Description, Phone) ...
INSERT INTO Stores (ID, EnterpriseID, Name, Descrip...
INSERT INTO Devices (ID, EnterpriseID, StoreID, Name...
INSERT INTO Screens (ID, EnterpriseID, Name, Url, De...
---KYD（試作＋デモ用:1, 501-1000）
INSERT INTO Enterprises (ID, Name, Description, Phone) ...
INSERT INTO Stores (ID, EnterpriseID, Name, Descrip...
INSERT INTO Devices (ID, EnterpriseID, StoreID, Name...
INSERT INTO Screens (ID, EnterpriseID, Name, Url, De...
--ユーザ定義(1001-, )
--INSERT INTO Enterprises (ID, Name, Description, Phone)...
--INSERT INTO Stores (ID, EnterpriseID, Name, Descr...
--INSERT INTO Devices (ID, EnterpriseID, StoreID, Na...
--INSERT INTO Screens (ID, EnterpriseID, Name, Url, ...
***SELECT [#uef2c3a5]
SELECT * FROM Enterprises;
SELECT * FROM Stores;
SELECT * FROM Devices;
**Transaction [#y003efbb]
***INSERT [#q6f49504]
--作成順
--共通
INSERT INTO
InitializeScreenInfo(ID, EnterpriseID, ScreenID, Expir...
VALUES(0, 0, 0, NULL, 'Standard screen', '', '{}');
--デモ
INSERT INTO
InitializeScreenInfo(ID, EnterpriseID, ScreenID, Expir...
VALUES(1, 1, 501, NULL, 'Utah screen', '', '{}');
***SELECT [#x0d1feff]
SELECT * FROM InitializeScreenInfo;
SELECT * FROM Answers;
ページ名:
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
