internal static void CreateDatabase(InstallSetup setup) { try { using (var conn = new NpgsqlConnection(setup.MasterConnectionString)) { conn.Open(); var cmdCreateDb = new NpgsqlCommand(); var fileInfo = string.Empty; if (!string.IsNullOrEmpty(setup.DiskPath)) { fileInfo = " WITH LOCATION '" + Path.Combine(setup.DiskPath, setup.NewDatabaseName) + "')"; } cmdCreateDb.CommandText = $"CREATE DATABASE \"{setup.NewDatabaseName}\"" + fileInfo; cmdCreateDb.CommandType = System.Data.CommandType.Text; cmdCreateDb.Connection = conn; DatabaseServer.ExecuteCommand(cmdCreateDb); } using (var conn = new NpgsqlConnection(setup.ConnectionString)) { conn.Open(); //Add UUID generator var command = new NpgsqlCommand(); command.CommandText = "CREATE EXTENSION \"uuid-ossp\";"; command.CommandType = System.Data.CommandType.Text; command.Connection = conn; DatabaseServer.ExecuteCommand(command); } } catch { throw; } finally { System.Threading.Thread.Sleep(1000); } }
internal static void ExecuteSQL(NpgsqlConnection connection, NpgsqlTransaction transaction, string sql, InstallSetup setup, List <KeyValuePair <Guid, string> > failedScripts, List <Guid> successOrderScripts) { if (sql.StartsWith("--##METHODCALL")) { CallMethod(sql, connection, transaction, setup); return; } //Test for empty statements var originalSQL = sql.Trim(); sql = originalSQL; if (string.IsNullOrEmpty(sql)) { return; } //Test for noop statements (all comments/empty strings) var lines = sql.BreakLines().TrimAll(); lines.RemoveAll(x => x.StartsWith("--")); lines.RemoveAll(x => x == ""); if ([email protected]()) { return; } lines = sql.BreakLines().TrimAll(); //Reset #region Get Script Key var isBody = false; var key = Guid.NewGuid(); var l = lines.FirstOrDefault(x => x.StartsWith("--MODELID: ")); if (l != null) { lines.Remove(l); l = l.Replace("--MODELID:", string.Empty).Trim(); sql = string.Join("\n", lines.ToArray()); //Remove the model key from the SQL before run //if (!Guid.TryParse(l, out key)) key = Guid.NewGuid(); } else { l = lines.FirstOrDefault(x => x.StartsWith("--MODELID,BODY: ")); if (l != null) { lines.Remove(l); l = l.Replace("--MODELID,BODY:", string.Empty).Trim(); sql = string.Join("\n", lines.ToArray()); //Remove the model key from the SQL before run if (!Guid.TryParse(l, out key)) { key = Guid.NewGuid(); } else { isBody = true; } } } #endregion if (string.IsNullOrEmpty(sql)) { return; } #region Try to remove objects before creation var dropObjectName = string.Empty; var dropSQL = GetSQLDropScript(sql); //Add a bit of convenience for dropping DB objects before creation if (!string.IsNullOrEmpty(dropSQL)) { try { if (!setup.CheckOnly) { var dropCommand = new NpgsqlCommand(dropSQL, connection); dropCommand.Transaction = transaction; dropCommand.CommandTimeout = 0; DatabaseServer.ExecuteCommand(dropCommand); } } catch (Exception ex) { //Ignore. The scripts should not need this. It has been added for convenience } } #endregion var command = new NpgsqlCommand(sql, connection); command.Transaction = transaction; command.CommandTimeout = 0; try { if (!setup.CheckOnly) { var debugText = "[" + DateTime.Now.ToString() + "]\r\n"; const int MAX_SQL = 500; var sqlLength = Math.Min(sql.Length, MAX_SQL); debugText += sql.Substring(0, sqlLength); if (sqlLength == MAX_SQL) { debugText += "..."; } debugText += "\r\n\r\n"; Log.Verbose(debugText); _timer.Restart(); DatabaseServer.ExecuteCommand(command); _timer.Stop(); Log.Debug <long, string>("Time:{Elapsed:000} Sql:{sql}", _timer.ElapsedMilliseconds, sql); if (successOrderScripts != null && isBody) { successOrderScripts.Add(key); } } } catch (NpgsqlException sqlexp) { if (failedScripts != null) { //Ignore this error, we will re-process it failedScripts.Add(new KeyValuePair <Guid, string>(key, originalSQL)); return; } else { throw new InvalidSQLException(sqlexp.Message, sqlexp) { SQL = sql, FileName = setup.DebugScriptName }; } } catch (Exception ex) { throw; } finally { if (command != null) { command.Dispose(); } } }
public static void Save(string connectionString, string modelKey, IEnumerable <nHydrateDbObject> list, NpgsqlTransaction transaction) { NpgsqlConnection conn = null; if (transaction == null) { conn = new NpgsqlConnection(connectionString); conn.Open(); } else { conn = transaction.Connection; } try { //Create the table if need be using (var command3 = new NpgsqlCommand("CREATE TABLE IF NOT EXISTS \"__nhydrateobjects\"" + "(\"rowid\" bigint GENERATED BY DEFAULT AS IDENTITY," + "\"id\" UUID NULL," + "\"name\" varchar (450) NOT NULL," + "\"type\" varchar (10) NOT NULL," + "\"schema\" varchar (450) NULL," + "\"CreatedDate\" timestamp NOT NULL," + "\"ModifiedDate\" timestamp NOT NULL," + "\"Hash\" varchar (32) NULL," + "\"Status\" varchar (500) NULL," + "\"ModelKey\" UUID NOT NULL)", conn)) { command3.Transaction = transaction; DatabaseServer.ExecuteCommand(command3); } var sql = new StringBuilder(); sql.AppendLine($"delete from \"__nhydrateobjects\" where \"id\" IS NULL and \"ModelKey\" = '{UpgradeInstaller.MODELKEY}'"); using (var command3 = new NpgsqlCommand(sql.ToString(), conn)) { command3.Transaction = transaction; DatabaseServer.ExecuteCommand(command3); } //Save items to the table foreach (var item in list.Where(x => x.Changed)) { using (var command = new NpgsqlCommand("insert into __nhydrateobjects (\"id\", \"name\", \"type\", \"schema\", \"CreatedDate\", \"ModifiedDate\", \"Hash\", \"ModelKey\", \"Status\") values (@id, @name, @type, @schema, @CreatedDate, @ModifiedDate, @Hash, @ModelKey, @Status)", conn)) { command.Transaction = transaction; command.Parameters.Add(new NpgsqlParameter { DbType = DbType.Guid, Value = (item.id == Guid.Empty ? System.DBNull.Value : (object)item.id), ParameterName = "@id", IsNullable = true }); command.Parameters.Add(new NpgsqlParameter { DbType = DbType.Int64, Value = item.rowid, ParameterName = "@rowid", IsNullable = false }); command.Parameters.Add(new NpgsqlParameter { DbType = DbType.String, Value = item.name, ParameterName = "@name", IsNullable = false }); command.Parameters.Add(new NpgsqlParameter { DbType = DbType.String, Value = item.type, ParameterName = "@type", IsNullable = false }); command.Parameters.Add(new NpgsqlParameter { DbType = DbType.String, Value = (item.schema == null ? System.DBNull.Value : (object)item.schema), ParameterName = "@schema", IsNullable = true }); command.Parameters.Add(new NpgsqlParameter { DbType = DbType.DateTime, Value = item.CreatedDate, ParameterName = "@CreatedDate", IsNullable = false }); command.Parameters.Add(new NpgsqlParameter { DbType = DbType.DateTime, Value = DateTime.Now, ParameterName = "@ModifiedDate", IsNullable = false }); command.Parameters.Add(new NpgsqlParameter { DbType = DbType.String, Value = item.Hash, ParameterName = "@Hash", IsNullable = false }); command.Parameters.Add(new NpgsqlParameter { DbType = DbType.Guid, Value = item.ModelKey, ParameterName = "@ModelKey", IsNullable = false }); command.Parameters.Add(new NpgsqlParameter { DbType = DbType.String, Value = item.Status, ParameterName = "@Status", IsNullable = true }); DatabaseServer.ExecuteCommand(command); } } } catch (Exception ex) { throw; } finally { if (transaction == null && conn != null) { conn.Close(); conn.Dispose(); } } }