/// <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(); } }