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