/// <summary>
/// SQL文を生成する
/// </summary>
/// <param name="context">生成先のコンテキスト</param>
public void ToElementCode(ElementCode context)
{
var delayedCode = new DelayedCodeGenerator((wb) => {
var ec = new ElementCode();
ec.BeginParenthesize();
ec.Add(SqlKeyword.Values);
var list = this.List;
for (int i = 0, n = list.Count; i < n; i++)
{
if (i != 0)
{
ec.AddComma();
}
ec.BeginParenthesize();
ec.AddValues <TColumns>(list[i]);
ec.EndParenthesize();
}
ec.EndParenthesize();
ec.Add(this);
ec.BeginParenthesize();
ec.AddColumns(this.ColumnMap, column => ec.Concat(column.Name));
ec.EndParenthesize();
ec.Build(wb);
});
context.Add(delayedCode);
context.RegisterBuildHandler(this, (item, wb) => {
// delayedCode 内でエイリアス名付与するので、既存の処理をオーバーライドし何もしないようにする
});
}
public override void CreateDatabase(ICodeDbConnection connection, string databaseName, string owner)
{
using (var cmd = connection.CreateCommand()) {
var context = new ElementCode();
context.Add(SqlKeyword.CreateDatabase);
context.Concat(Quote(databaseName));
context.Add(SqlKeyword.Owner);
context.Concat(Quote(owner));
context.Go();
context.Build().Execute(cmd);
}
}
public override void CreateRole(ICodeDbConnection connection, string roleName, string password)
{
using (var cmd = connection.CreateCommand()) {
var context = new ElementCode();
context.Add(SqlKeyword.CreateRole);
context.Concat(Quote(roleName));
context.Add(SqlKeyword.Password);
context.Concat(string.Concat("'", password, "'"));
context.Concat("LOGIN");
context.Go();
context.Build().Execute(cmd);
}
}
static void Main(string[] args)
{
var E = TestDb.E;
using (var con = E.CreateConnection("User ID=postgres;Password=wertyu89?;Host=localhost;Port=5432;Database=postgres;")) {
con.Open();
try {
E.CreateRole(con, RoleName, "Passw0rd!");
} catch (CodeDbEnvironmentException ex) {
if (ex.ErrorType != DbEnvironmentErrorType.DuplicateObject)
{
throw;
}
}
try {
E.CreateDatabase(con, DbName, RoleName);
} catch (CodeDbEnvironmentException ex) {
if (ex.ErrorType != DbEnvironmentErrorType.DuplicateDatabase)
{
throw;
}
}
}
using (var con = E.CreateConnection($"User ID={RoleName};Password='******';Host=localhost;Port=5432;Database={DbName};")) {
con.Open();
var cmd = con.CreateCommand();
cmd.CommandTimeout = 0;
{
var s = TestDb.E.NewSql();
s.DropTable(TestDb.User);
s.Build().Execute(cmd);
}
// データベースの状態を取得
var current = E.ReadDatabaseDef(con);
// クラスからデータベース定義を生成する
var target = E.GenerateDatabaseDef(typeof(TestDb), "test_db");
// 差分を生成
var delta = E.GetDatabaseDelta(current, target);
// 差分を適用する
var context = new ElementCode();
E.ApplyDatabaseDelta(context, delta);
context.Build().Execute(cmd);
{
var sql = TestDb.E.NewSql();
sql.InsertIntoWithValue(TestDb.User, t => new[] { t.UserName == "a" });
sql.InsertIntoWithValue(TestDb.User, t => new[] { t.UserName == "b" });
sql.InsertIntoWithValue(TestDb.User, t => new[] { t.UserName == "c" });
sql.BuildAction().Execute(cmd);
}
{
var sql = TestDb.E.NewSql();
var select = sql.From(TestDb.User).Where(t => t.UserName == "a").Select(t => new { t.UserName, t.UserID, t.CreateDateTime });
var f = sql.BuildSelectFunc(select);
using (var reader = f.Execute(cmd)) {
foreach (var record in reader.Records)
{
Console.WriteLine($"{record.UserID} {record.UserName} {record.CreateDateTime}");
}
}
}
{
var code = new ElementCode();
var arg1 = new Argument(0);
var arg2 = new Argument(0);
code.Concat("SELECT ARRAY[1, 2, 3], '{8EA22A18-EB0A-49E5-B61D-F026CA7773FF}'::uuid, now(),");
code.Add(arg1);
code.Go();
code.Concat("SELECT ARRAY[1, 2, 3], '{8EA22A18-EB0A-49E5-B61D-F026CA7773FF}'::uuid, now(),");
code.Add(arg2);
code.Go();
var s = TestDb.E.NewSql();
s.Code(code);
var commandable = s.BuildFunc <int, int, Tuple <int[], Guid, DateTime, int> >(arg1, arg2);
using (var reader = commandable.Execute(cmd, 16, 32)) {
do
{
foreach (var record in reader.Records)
{
Console.WriteLine(record);
}
} while (reader.DataReader.NextResult());
}
}
}
}
public static void Initialize()
{
// ロールとデータベースを作成する
using (var con = E.CreateConnection("User ID=sa;Password='******';Host=localhost;Port=5432;Database=postgres;")) {
con.Open();
try {
E.CreateRole(con, RoleName, "Passw0rd!");
} catch (DbCodeEnvironmentException ex) {
if (ex.ErrorType != DbEnvironmentErrorType.DuplicateObject)
{
throw;
}
}
try {
E.CreateDatabase(con, DbName, RoleName);
} catch (DbCodeEnvironmentException ex) {
if (ex.ErrorType != DbEnvironmentErrorType.DuplicateDatabase)
{
throw;
}
}
}
// データベース内のテーブル等を最新の仕様に合わせて変更する
using (var con = CreateConnection()) {
con.Open();
var cmd = con.CreateCommand();
cmd.CommandTimeout = 0;
// データベースの状態を取得
var current = E.ReadDatabaseDef(con);
// クラスからデータベース定義を生成する
var target = E.GenerateDatabaseDef(typeof(Db), DbName);
// 差分を生成
var delta = E.GetDatabaseDelta(current, target);
// 差分を適用する
var context = new ElementCode();
E.ApplyDatabaseDelta(context, delta);
context.Build().Execute(cmd);
// URL登録ストアドを登録
cmd.CommandText = $@"
DROP FUNCTION IF EXISTS {SpAddUrl}(TEXT);
CREATE OR REPLACE FUNCTION {SpAddUrl}(url_to_add TEXT)
RETURNS INT AS $$
DECLARE
id INT := 0;
BEGIN
SELECT {Db.Url.ColumnName(nameof(Db.Url._.UrlID))} INTO id FROM {Db.Url.Name} WHERE {Db.Url.ColumnName(nameof(Db.Url._.Url))}=url_to_add;
IF id <> 0 THEN
RETURN id;
ELSE
INSERT INTO {Db.Url.Name}({Db.Url.ColumnName(nameof(Db.Url._.Url))}) VALUES (url_to_add);
RETURN lastval();
END IF;
END;
$$ LANGUAGE plpgsql;
";
cmd.ExecuteNonQuery();
// キーワード登録ストアドを登録
cmd.CommandText = $@"
DROP FUNCTION IF EXISTS {SpAddKeyword}(TEXT);
CREATE OR REPLACE FUNCTION {SpAddKeyword}(keyword_to_add TEXT)
RETURNS INT AS $$
DECLARE
id INT := 0;
BEGIN
SELECT {Db.Keyword.ColumnName(nameof(Db.Keyword._.KeywordID))} INTO id FROM {Db.Keyword.Name} WHERE {Db.Keyword.ColumnName(nameof(Db.Keyword._.Keyword))}=keyword_to_add;
IF id <> 0 THEN
RETURN id;
ELSE
INSERT INTO {Db.Keyword.Name}({Db.Keyword.ColumnName(nameof(Db.Keyword._.Keyword))}) VALUES (keyword_to_add);
RETURN lastval();
END IF;
END;
$$ LANGUAGE plpgsql;
";
cmd.ExecuteNonQuery();
// リンク登録ストアドを登録
cmd.CommandText = $@"
DROP FUNCTION IF EXISTS {SpAddLink}(INT, INT, TEXT);
CREATE OR REPLACE FUNCTION {SpAddLink}(src_url_id_to_add INT, dst_url_id_to_add INT, link_text_to_add TEXT)
RETURNS INT AS $$
DECLARE
id INT := 0;
BEGIN
SELECT {Db.Link.ColumnName(nameof(Db.Link._.LinkID))} INTO id FROM {Db.Link.Name} WHERE {Db.Link.ColumnName(nameof(Db.Link._.SrcUrlID))}=src_url_id_to_add AND {Db.Link.ColumnName(nameof(Db.Link._.DstUrlID))}=dst_url_id_to_add;
IF id <> 0 THEN
RETURN id;
ELSE
INSERT INTO {Db.Link.Name}({Db.Link.ColumnName(nameof(Db.Link._.SrcUrlID))}, {Db.Link.ColumnName(nameof(Db.Link._.DstUrlID))}, {Db.Link.ColumnName(nameof(Db.Link._.LinkText))}) VALUES (src_url_id_to_add, dst_url_id_to_add, link_text_to_add);
RETURN lastval();
END IF;
END;
$$ LANGUAGE plpgsql;
";
cmd.ExecuteNonQuery();
}
}
public static void Initialize()
{
// ロールとデータベースを作成する
using (var con = E.CreateConnection("User ID=sa;Password='******';Host=localhost;Port=5432;Database=postgres;")) {
con.Open();
try {
E.CreateRole(con, RoleName, "Passw0rd!");
} catch (DbCodeEnvironmentException ex) {
if (ex.ErrorType != DbEnvironmentErrorType.DuplicateObject)
{
throw;
}
}
try {
E.CreateDatabase(con, DbName, RoleName);
} catch (DbCodeEnvironmentException ex) {
if (ex.ErrorType != DbEnvironmentErrorType.DuplicateDatabase)
{
throw;
}
}
}
// データベース内のテーブル等を最新の仕様に合わせて変更する
using (var con = CreateConnection()) {
con.Open();
var cmd = con.CreateCommand();
cmd.CommandTimeout = 0;
// データベースの状態を取得
var current = E.ReadDatabaseDef(con);
// クラスからデータベース定義を生成する
var target = E.GenerateDatabaseDef(typeof(Db), DbName);
// 差分を生成
var delta = E.GetDatabaseDelta(current, target);
// 差分を適用する
var context = new ElementCode();
E.ApplyDatabaseDelta(context, delta);
context.Build().Execute(cmd);
// ストアド登録コマンドを生成するデリゲート
Func <string, Column, Column, string> createAddTextStoredProcedure = (spname, idcolumn, column) => {
var tableName = (column.Table as ITableDef).Name;
return($@"
DROP FUNCTION IF EXISTS {spname}(TEXT);
CREATE OR REPLACE FUNCTION {spname}(value_to_add TEXT)
RETURNS INT AS $$
DECLARE
id INT := 0;
BEGIN
SELECT {idcolumn.Name} INTO id FROM {tableName} WHERE {column.Name}=value_to_add;
IF id <> 0 THEN
RETURN id;
ELSE
INSERT INTO {tableName}({column.Name}) VALUES (value_to_add);
RETURN lastval();
END IF;
END;
$$ LANGUAGE plpgsql;
");
};
// CVE登録ストアドを登録
cmd.CommandText = createAddTextStoredProcedure(SpAddCve, Db.Cve.GetColumn(() => Db.Cve._.CveID), Db.Cve.GetColumn(() => Db.Cve._.Cve));
cmd.ExecuteNonQuery();
// ベンダー登録ストアドを登録
cmd.CommandText = createAddTextStoredProcedure(SpAddVendor, Db.Vendor.GetColumn(() => Db.Vendor._.VendorID), Db.Vendor.GetColumn(() => Db.Vendor._.Vendor));
cmd.ExecuteNonQuery();
// CPE2.2登録ストアドを登録
cmd.CommandText = createAddTextStoredProcedure(SpAddCpe22, Db.Cpe22.GetColumn(() => Db.Cpe22._.Cpe22ID), Db.Cpe22.GetColumn(() => Db.Cpe22._.Uri));
cmd.ExecuteNonQuery();
// CPE2.3登録ストアドを登録
cmd.CommandText = createAddTextStoredProcedure(SpAddCpe23, Db.Cpe23.GetColumn(() => Db.Cpe23._.Cpe23ID), Db.Cpe23.GetColumn(() => Db.Cpe23._.Uri));
cmd.ExecuteNonQuery();
}
}
