private static string InitJournalSql(string tableName, string schemaName = null)
{
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException("tableName", "Akka.Persistence.PostgreSql journal table name is required");
}
schemaName = schemaName ?? "public";
var cb = new NpgsqlCommandBuilder();
return(string.Format(SqlJournalFormat, cb.QuoteIdentifier(schemaName), cb.QuoteIdentifier(tableName), cb.UnquoteIdentifier(schemaName), cb.UnquoteIdentifier(tableName)));
}
static void CreateTable(
this NpgsqlConnection conn,
SqlTable table,
PrimaryKeyInfo primaryKey,
Dictionary <string, IColumnInfo> fields,
uint tableRevision,
ILog log)
{
var b = new NpgsqlCommandBuilder();
log.InfoFormat("Creating table '{0}' at revision {1}", table.Name, tableRevision);
var fieldSqls = new List <string>();
foreach (IColumnInfo field in fields.Values)
{
fieldSqls.Add(field.FieldSql());
}
if (null != primaryKey)
{
fieldSqls.Add(primaryKey.FieldSql());
}
string escapedTableName = b.QuoteIdentifier(table.Name);
string cmd = "CREATE TABLE " + escapedTableName + " (";
cmd += string.Join(",", fieldSqls);
cmd += ")";
cmd += "; COMMENT ON TABLE " + escapedTableName + " IS '" + tableRevision.ToString() + "';";
ExecuteStatement(conn, cmd, log);
}
public static void UpdateSet(this NpgsqlConnection connection, string tablename, Dictionary <string, object> vals, Dictionary <string, object> where, NpgsqlTransaction transaction = null)
{
var b = new NpgsqlCommandBuilder();
string q1 = "UPDATE " + tablename + " SET ";
q1 += string.Join(",", UpdateSetFromVals(vals, b));
var wherestr = new StringBuilder();
foreach (KeyValuePair <string, object> w in where)
{
if (wherestr.Length != 0)
{
wherestr.Append(" AND ");
}
wherestr.AppendFormat("{0} = @w_{1}", b.QuoteIdentifier(w.Key), w.Key);
}
using (var command = new NpgsqlCommand(q1 + " WHERE " + wherestr, connection)
{
Transaction = transaction
})
{
AddParameters(command.Parameters, vals);
foreach (KeyValuePair <string, object> w in where)
{
command.Parameters.AddParameter("@w_" + w.Key, w.Value);
}
if (command.ExecuteNonQuery() < 1)
{
throw new PostgreSQLInsertException();
}
}
}
public void CommandBuilderQuoting()
{
var cb = new NpgsqlCommandBuilder();
const string orig = "some\"column";
var quoted = cb.QuoteIdentifier(orig);
Assert.That(quoted, Is.EqualTo("\"some\"\"column\""));
Assert.That(cb.UnquoteIdentifier(quoted), Is.EqualTo(orig));
}
public static void CreateDb(string dbName, bool dropIfExists = false)
{
if (dropIfExists)
{
DropDb(dbName);
}
using (var command = _connection.CreateCommand())
{
command.CommandText = $"CREATE DATABASE {cb.QuoteIdentifier(dbName)};";
command.ExecuteNonQuery();
}
}
public static int GetRowCount(this NpgsqlConnection connection, string schema, string tableName)
{
var commandBuilder = new NpgsqlCommandBuilder();
return((int)connection.ExecuteScalar <long>($"SELECT COUNT(*) FROM {commandBuilder.QuoteIdentifier(schema)}.{commandBuilder.QuoteIdentifier(tableName)}"));
}
private static List <string> UpdateSetFromVals(Dictionary <string, object> vals, NpgsqlCommandBuilder b)
{
var updates = new List <string>();
foreach (KeyValuePair <string, object> kvp in vals)
{
object value = kvp.Value;
var t = value?.GetType();
string key = kvp.Key;
if (t == typeof(Vector3))
{
updates.Add(b.QuoteIdentifier(key + "X") + " = @v_" + key + "X");
updates.Add(b.QuoteIdentifier(key + "Y") + " = @v_" + key + "Y");
updates.Add(b.QuoteIdentifier(key + "Z") + " = @v_" + key + "Z");
}
else if (t == typeof(GridVector) || t == typeof(EnvironmentController.WLVector2))
{
updates.Add(b.QuoteIdentifier(key + "X") + " = @v_" + key + "X");
updates.Add(b.QuoteIdentifier(key + "Y") + " = @v_" + key + "Y");
}
else if (t == typeof(Quaternion))
{
updates.Add(b.QuoteIdentifier(key + "X") + " = @v_" + key + "X");
updates.Add(b.QuoteIdentifier(key + "Y") + " = @v_" + key + "Y");
updates.Add(b.QuoteIdentifier(key + "Z") + " = @v_" + key + "Z");
updates.Add(b.QuoteIdentifier(key + "W") + " = @v_" + key + "W");
}
else if (t == typeof(Color))
{
updates.Add(b.QuoteIdentifier(key + "Red") + " = @v_" + key + "Red");
updates.Add(b.QuoteIdentifier(key + "Green") + " = @v_" + key + "Green");
updates.Add(b.QuoteIdentifier(key + "Blue") + " = @v_" + key + "Blue");
}
else if (t == typeof(EnvironmentController.WLVector4))
{
updates.Add(b.QuoteIdentifier(key + "Red") + " = @v_" + key + "Red");
updates.Add(b.QuoteIdentifier(key + "Green") + " = @v_" + key + "Green");
updates.Add(b.QuoteIdentifier(key + "Blue") + " = @v_" + key + "Blue");
updates.Add(b.QuoteIdentifier(key + "Value") + " = @v_" + key + "Value");
}
else if (t == typeof(ColorAlpha))
{
updates.Add(b.QuoteIdentifier(key + "Red") + " = @v_" + key + "Red");
updates.Add(b.QuoteIdentifier(key + "Green") + " = @v_" + key + "Green");
updates.Add(b.QuoteIdentifier(key + "Blue") + " = @v_" + key + "Blue");
updates.Add(b.QuoteIdentifier(key + "Alpha") + " = @v_" + key + "Alpha");
}
else if (value == null)
{
/* skip */
}
else
{
updates.Add(b.QuoteIdentifier(key) + " = @v_" + key);
}
}
return(updates);
}
public static void InsertInto(this NpgsqlConnection connection, string tablename, Dictionary <string, object> vals, NpgsqlTransaction transaction = null)
{
var q = new List <string>();
foreach (KeyValuePair <string, object> kvp in vals)
{
object value = kvp.Value;
var t = value?.GetType();
string key = kvp.Key;
if (t == typeof(Vector3))
{
q.Add(key + "X");
q.Add(key + "Y");
q.Add(key + "Z");
}
else if (t == typeof(GridVector) || t == typeof(EnvironmentController.WLVector2))
{
q.Add(key + "X");
q.Add(key + "Y");
}
else if (t == typeof(Quaternion))
{
q.Add(key + "X");
q.Add(key + "Y");
q.Add(key + "Z");
q.Add(key + "W");
}
else if (t == typeof(Color))
{
q.Add(key + "Red");
q.Add(key + "Green");
q.Add(key + "Blue");
}
else if (t == typeof(EnvironmentController.WLVector4))
{
q.Add(key + "Red");
q.Add(key + "Green");
q.Add(key + "Blue");
q.Add(key + "Value");
}
else if (t == typeof(ColorAlpha))
{
q.Add(key + "Red");
q.Add(key + "Green");
q.Add(key + "Blue");
q.Add(key + "Alpha");
}
else if (value == null)
{
/* skip */
}
else
{
q.Add(key);
}
}
var cb = new NpgsqlCommandBuilder();
var q1 = new StringBuilder();
var q2 = new StringBuilder();
q1.Append("INSERT INTO ");
q1.Append(cb.QuoteIdentifier(tablename));
q1.Append(" (");
q2.Append(") VALUES (");
bool first = true;
foreach (string p in q)
{
if (!first)
{
q1.Append(",");
q2.Append(",");
}
first = false;
q1.Append(cb.QuoteIdentifier(p));
q2.Append("@v_");
q2.Append(p);
}
q1.Append(q2);
q1.Append(")");
using (var command = new NpgsqlCommand(q1.ToString(), connection)
{
Transaction = transaction
})
{
AddParameters(command.Parameters, vals);
if (command.ExecuteNonQuery() < 1)
{
throw new PostgreSQLInsertException();
}
}
}
public static void ReplaceInto(this NpgsqlConnection connection, string tablename, Dictionary <string, object> vals, string[] keyfields, bool enableOnConflict, NpgsqlTransaction transaction = null)
{
bool useOnConflict = connection.HasOnConflict() && enableOnConflict;
var q = new List <string>();
foreach (KeyValuePair <string, object> kvp in vals)
{
object value = kvp.Value;
var t = value?.GetType();
string key = kvp.Key;
if (t == typeof(Vector3))
{
q.Add(key + "X");
q.Add(key + "Y");
q.Add(key + "Z");
}
else if (t == typeof(GridVector) || t == typeof(EnvironmentController.WLVector2))
{
q.Add(key + "X");
q.Add(key + "Y");
}
else if (t == typeof(Quaternion))
{
q.Add(key + "X");
q.Add(key + "Y");
q.Add(key + "Z");
q.Add(key + "W");
}
else if (t == typeof(Color))
{
q.Add(key + "Red");
q.Add(key + "Green");
q.Add(key + "Blue");
}
else if (t == typeof(EnvironmentController.WLVector4))
{
q.Add(key + "Red");
q.Add(key + "Green");
q.Add(key + "Blue");
q.Add(key + "Value");
}
else if (t == typeof(ColorAlpha))
{
q.Add(key + "Red");
q.Add(key + "Green");
q.Add(key + "Blue");
q.Add(key + "Alpha");
}
else if (value == null)
{
/* skip */
}
else
{
q.Add(key);
}
}
var cb = new NpgsqlCommandBuilder();
var q1 = new StringBuilder();
string quotedTableName = cb.QuoteIdentifier(tablename);
if (useOnConflict)
{
var insertIntoFields = new StringBuilder();
var conflictParams = new StringBuilder();
var updateParams = new StringBuilder();
q1.Append("INSERT INTO ");
q1.Append(quotedTableName);
q1.Append(" (");
insertIntoFields.Append(") VALUES (");
bool first = true;
foreach (string p in q)
{
if (!first)
{
q1.Append(",");
insertIntoFields.Append(",");
}
first = false;
q1.Append(cb.QuoteIdentifier(p));
insertIntoFields.Append("@v_");
insertIntoFields.Append(p);
if (keyfields.Contains(p))
{
if (conflictParams.Length != 0)
{
conflictParams.Append(",");
}
conflictParams.Append(cb.QuoteIdentifier(p));
}
else
{
if (updateParams.Length != 0)
{
updateParams.Append(",");
}
updateParams.Append(cb.QuoteIdentifier(p));
updateParams.Append("=");
updateParams.Append("@v_");
updateParams.Append(p);
}
}
q1.Append(insertIntoFields);
q1.Append(") ON CONFLICT (");
q1.Append(conflictParams);
q1.Append(") ");
if (updateParams.Length != 0)
{
q1.Append("DO UPDATE SET ");
q1.Append(updateParams);
}
else
{
q1.Append("DO NOTHING");
}
}
else
{
var insertIntoParams = new StringBuilder();
var insertIntoFields = new StringBuilder();
var updateParams = new StringBuilder();
var whereParams = new StringBuilder();
foreach (string p in q)
{
string quotedFieldName = cb.QuoteIdentifier(p);
if (insertIntoParams.Length != 0)
{
insertIntoParams.Append(",");
insertIntoFields.Append(",");
}
insertIntoParams.Append("@v_");
insertIntoParams.Append(p);
insertIntoFields.Append(quotedFieldName);
if (keyfields.Contains(p))
{
if (whereParams.Length != 0)
{
whereParams.Append(" AND ");
}
whereParams.Append(quotedFieldName);
whereParams.Append(" = ");
whereParams.Append("@v_");
whereParams.Append(p);
}
else
{
if (updateParams.Length != 0)
{
updateParams.Append(",");
}
updateParams.Append(quotedFieldName);
updateParams.Append("=");
updateParams.Append("@v_");
updateParams.Append(p);
}
}
if (updateParams.Length != 0)
{
q1.Append("UPDATE ");
q1.Append(quotedTableName);
q1.Append(" SET ");
q1.Append(updateParams);
q1.Append(" WHERE ");
q1.Append(whereParams);
q1.Append(";");
}
q1.Append("INSERT INTO ");
q1.Append(quotedTableName);
q1.Append(" (");
q1.Append(insertIntoFields);
q1.Append(") SELECT ");
q1.Append(insertIntoParams);
q1.Append(" WHERE NOT EXISTS (SELECT 1 FROM ");
q1.Append(quotedTableName);
q1.Append(" WHERE ");
q1.Append(whereParams);
q1.Append(")");
}
if (useOnConflict)
{
using (var command = new NpgsqlCommand(q1.ToString(), connection))
{
command.Transaction = transaction;
AddParameters(command.Parameters, vals);
if (command.ExecuteNonQuery() < 1)
{
throw new PostgreSQLInsertException();
}
}
}
else if (transaction != null)
{
using (var command = new NpgsqlCommand(q1.ToString(), connection)
{
Transaction = transaction
})
{
AddParameters(command.Parameters, vals);
if (command.ExecuteNonQuery() < 1)
{
throw new PostgreSQLInsertException();
}
}
}
else
{
connection.InsideTransaction((transactionin) =>
{
using (var command = new NpgsqlCommand(q1.ToString(), connection)
{
Transaction = transactionin
})
{
AddParameters(command.Parameters, vals);
if (command.ExecuteNonQuery() < 1)
{
throw new PostgreSQLInsertException();
}
}
});
}
}
public static string QuoteSchemaAndTable(this string sqlQuery, string schemaName, string tableName)
{
var cb = new NpgsqlCommandBuilder();
return(string.Format(sqlQuery, cb.QuoteIdentifier(schemaName), cb.QuoteIdentifier(tableName)));
}
public static void MigrateTables(this NpgsqlConnection conn, IMigrationElement[] processTable, ILog log)
{
var b = new NpgsqlCommandBuilder();
var tableFields = new Dictionary <string, IColumnInfo>();
PrimaryKeyInfo primaryKey = null;
var tableKeys = new Dictionary <string, NamedKeyInfo>();
SqlTable table = null;
uint processingTableRevision = 0;
uint currentAtRevision = 0;
NpgsqlTransaction insideTransaction = null;
m_MaxAvailableMigrationRevision = 1;
if (processTable.Length == 0)
{
throw new PostgreSQLMigrationException("Invalid PostgreSQL migration");
}
if (null == processTable[0] as SqlTable)
{
throw new PostgreSQLMigrationException("First entry must be table name");
}
bool skipToNext = false;
foreach (IMigrationElement migration in processTable)
{
Type migrationType = migration.GetType();
if (typeof(SqlTable) == migrationType)
{
skipToNext = false;
if (insideTransaction != null)
{
ExecuteStatement(conn, string.Format("COMMENT ON TABLE {0} IS '{1}';", table.Name, processingTableRevision), log);
insideTransaction.Commit();
insideTransaction = null;
}
if (null != table && 0 != processingTableRevision)
{
if (currentAtRevision == 0)
{
conn.CreateTable(
table,
primaryKey,
tableFields,
processingTableRevision,
log);
}
tableFields.Clear();
primaryKey = null;
}
table = (SqlTable)migration;
currentAtRevision = conn.GetTableRevision(table.Name);
processingTableRevision = 1;
if (currentAtRevision != 0 && m_DeleteTablesBefore)
{
log.Info($"Dropping table {table.Name}");
ExecuteStatement(conn, $"DROP TABLE {table.Name}", log);
currentAtRevision = 0;
}
}
else if (skipToNext)
{
/* skip processing */
if (typeof(TableRevision) == migrationType)
{
m_MaxAvailableMigrationRevision = Math.Max(m_MaxAvailableMigrationRevision, ((TableRevision)migration).Revision);
}
}
else if (typeof(TableRevision) == migrationType)
{
if (insideTransaction != null)
{
ExecuteStatement(conn, string.Format("COMMENT ON TABLE {0} IS '{1}';", table.Name, processingTableRevision), log);
insideTransaction.Commit();
insideTransaction = null;
if (currentAtRevision != 0)
{
currentAtRevision = processingTableRevision;
}
}
var rev = (TableRevision)migration;
m_MaxAvailableMigrationRevision = Math.Max(m_MaxAvailableMigrationRevision, rev.Revision);
if (processingTableRevision == m_StopAtMigrationRevision)
{
/* advance to next table for testing */
skipToNext = true;
continue;
}
if (rev.Revision != processingTableRevision + 1)
{
throw new PostgreSQLMigrationException(string.Format("Invalid TableRevision entry. Expected {0}. Got {1}", processingTableRevision + 1, rev.Revision));
}
processingTableRevision = rev.Revision;
if (processingTableRevision - 1 == currentAtRevision && 0 != currentAtRevision)
{
insideTransaction = conn.BeginTransaction(System.Data.IsolationLevel.Serializable);
log.InfoFormat("Migration table '{0}' to revision {1}", table.Name, processingTableRevision);
}
}
else if (processingTableRevision == 0 || table == null)
{
if (table != null)
{
throw new PostgreSQLMigrationException("Unexpected processing element for " + table.Name);
}
else
{
throw new PostgreSQLMigrationException("Unexpected processing element");
}
}
else
{
Type[] interfaces = migration.GetType().GetInterfaces();
if (interfaces.Contains(typeof(IAddColumn)))
{
var columnInfo = (IAddColumn)migration;
if (tableFields.ContainsKey(columnInfo.Name))
{
throw new ArgumentException("Column " + columnInfo.Name + " was added twice.");
}
tableFields.Add(columnInfo.Name, columnInfo);
if (insideTransaction != null)
{
ExecuteStatement(conn, columnInfo.Sql(table.Name), log);
}
}
else if (interfaces.Contains(typeof(IChangeColumn)))
{
var columnInfo = (IChangeColumn)migration;
IColumnInfo oldColumn;
if (columnInfo.OldName?.Length != 0)
{
if (!tableFields.TryGetValue(columnInfo.OldName, out oldColumn))
{
throw new ArgumentException("Change column for " + columnInfo.Name + " has no preceeding AddColumn for " + columnInfo.OldName);
}
}
else if (!tableFields.TryGetValue(columnInfo.Name, out oldColumn))
{
throw new ArgumentException("Change column for " + columnInfo.Name + " has no preceeding AddColumn");
}
if (insideTransaction != null)
{
ExecuteStatement(conn, columnInfo.Sql(table.Name, oldColumn.FieldType), log);
}
if (columnInfo.OldName?.Length != 0)
{
tableFields.Remove(columnInfo.OldName);
if (primaryKey != null)
{
string[] fields = primaryKey.FieldNames;
int n = fields.Length;
for (int i = 0; i < n; ++i)
{
if (fields[i] == columnInfo.OldName)
{
fields[i] = columnInfo.Name;
}
}
}
foreach (NamedKeyInfo keyinfo in tableKeys.Values)
{
string[] fields = keyinfo.FieldNames;
int n = fields.Length;
for (int i = 0; i < n; ++i)
{
if (fields[i] == columnInfo.OldName)
{
fields[i] = columnInfo.Name;
}
}
}
}
tableFields[columnInfo.Name] = columnInfo;
}
else if (migrationType == typeof(DropColumn))
{
var columnInfo = (DropColumn)migration;
if (insideTransaction != null)
{
ExecuteStatement(conn, columnInfo.Sql(table.Name, tableFields[columnInfo.Name].FieldType), log);
}
tableFields.Remove(columnInfo.Name);
}
else if (migrationType == typeof(PrimaryKeyInfo))
{
if (null != primaryKey && insideTransaction != null)
{
ExecuteStatement(conn, "ALTER TABLE " + b.QuoteIdentifier(table.Name) + " DROP CONSTRAINT " + b.QuoteIdentifier(table.Name + "_pkey") + ";", log);
}
primaryKey = new PrimaryKeyInfo((PrimaryKeyInfo)migration);
if (insideTransaction != null)
{
ExecuteStatement(conn, primaryKey.Sql(table.Name), log);
}
}
else if (migrationType == typeof(DropPrimaryKeyinfo))
{
if (null != primaryKey && insideTransaction != null)
{
ExecuteStatement(conn, ((DropPrimaryKeyinfo)migration).Sql(table.Name), log);
}
primaryKey = null;
}
else if (migrationType == typeof(NamedKeyInfo))
{
var namedKey = new NamedKeyInfo((NamedKeyInfo)migration);
if (insideTransaction != null)
{
ExecuteStatement(conn, namedKey.Sql(table.Name), log);
}
}
else if (migrationType == typeof(DropNamedKeyInfo))
{
var namedKey = (DropNamedKeyInfo)migration;
if (insideTransaction != null)
{
ExecuteStatement(conn, namedKey.Sql(table.Name), log);
}
}
else
{
throw new PostgreSQLMigrationException("Invalid type " + migrationType.FullName + " in migration list");
}
}
}
if (insideTransaction != null)
{
ExecuteStatement(conn, string.Format("COMMENT ON TABLE {0} IS '{1}';", b.QuoteIdentifier(table.Name), processingTableRevision), log);
insideTransaction.Commit();
if (currentAtRevision != 0)
{
currentAtRevision = processingTableRevision;
}
}
if (null != table && 0 != processingTableRevision && currentAtRevision == 0)
{
conn.CreateTable(
table,
primaryKey,
tableFields,
processingTableRevision,
log);
}
}