/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer">The materializer.</param>
/// <returns>ExecutionToken<TCommand>.</returns>
public override CommandExecutionToken <SqlCommand, SqlParameter> Prepare(Materializer <SqlCommand, SqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
if (KeyColumns.Count > 0)
{
sqlBuilder.OverrideKeys(KeyColumns);
}
var availableColumns = sqlBuilder.GetParameterizedColumns().ToList();
var sql = new StringBuilder();
sqlBuilder.UseTableVariable(Table, out var header, out var intoClause, out var footer);
sql.Append(header);
var identityInsert = m_Options.HasFlag(UpsertOptions.IdentityInsert);
if (identityInsert)
{
sql.AppendLine($"SET IDENTITY_INSERT {Table.Name.ToQuotedString()} ON;");
}
sql.Append($"MERGE INTO {Table.Name.ToQuotedString()} WITH ( UPDLOCK, SERIALIZABLE ) target USING "); sql.Append("(VALUES (" + string.Join(", ", availableColumns.Select(c => c.SqlVariableName)) + ")) AS source (" + string.Join(", ", availableColumns.Select(c => c.QuotedSqlName)) + ")");
sql.Append(" ON ");
sql.Append(string.Join(" AND ", sqlBuilder.GetKeyColumns().ToList().Select(c => $"target.{c.QuotedSqlName} = source.{c.QuotedSqlName}")));
sql.Append(" WHEN MATCHED THEN UPDATE SET ");
sql.Append(string.Join(", ", sqlBuilder.GetUpdateColumns().Select(x => $"{x.QuotedSqlName} = source.{x.QuotedSqlName}")));
var insertColumns = sqlBuilder.GetInsertColumns(m_Options.HasFlag(UpsertOptions.IdentityInsert));
sql.Append(" WHEN NOT MATCHED THEN INSERT (");
sql.Append(string.Join(", ", insertColumns.Select(x => x.QuotedSqlName)));
sql.Append(") VALUES (");
sql.Append(string.Join(", ", insertColumns.Select(x => "source." + x.QuotedSqlName)));
sql.Append(" )");
sqlBuilder.BuildSelectClause(sql, " OUTPUT ", "Inserted.", intoClause);
sql.Append(";");
sql.Append(footer);
if (identityInsert)
{
sql.AppendLine($"SET IDENTITY_INSERT {Table.Name.ToQuotedString()} OFF;");
}
return(new SqlServerCommandExecutionToken(DataSource, "Insert or update " + Table.Name, sql.ToString(), sqlBuilder.GetParameters()));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="PostgreSqlCommandExecutionToken" /></returns>
public override CommandExecutionToken <NpgsqlCommand, NpgsqlParameter> Prepare(Materializer <NpgsqlCommand, NpgsqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var identityInsert = m_Options.HasFlag(UpsertOptions.IdentityInsert);
if (identityInsert)
{
throw new NotImplementedException("See issue 256. https://github.com/TortugaResearch/Tortuga.Chain/issues/256");
}
//var primaryKeyNames = Table.PrimaryKeyColumns.Select(x => x.QuotedSqlName);
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
if (KeyColumns.Count > 0)
{
sqlBuilder.OverrideKeys(KeyColumns);
}
var sql = new StringBuilder();
List <NpgsqlParameter> keyParameters;
var isPrimaryKeyIdentity = sqlBuilder.PrimaryKeyIsIdentity(out keyParameters);
if (isPrimaryKeyIdentity && KeyColumns.Count == 0)
{
var areKeysNull = keyParameters.Any(c => c.Value == DBNull.Value || c.Value == null) ? true : false;
if (areKeysNull)
{
sqlBuilder.BuildInsertStatement(sql, Table.Name.ToString(), null);
}
else
{
sqlBuilder.BuildUpdateByKeyStatement(sql, Table.Name.ToString(), null);
}
sqlBuilder.BuildSelectClause(sql, " RETURNING ", null, ";");
}
else
{
string conflictNames = string.Join(", ", sqlBuilder.GetKeyColumns().Select(x => x.QuotedSqlName));
sqlBuilder.BuildInsertClause(sql, $"INSERT INTO {Table.Name.ToString()} (", null, ")");
sqlBuilder.BuildValuesClause(sql, " VALUES (", ")");
sqlBuilder.BuildSetClause(sql, $" ON CONFLICT ({conflictNames}) DO UPDATE SET ", null, null);
sqlBuilder.BuildSelectClause(sql, " RETURNING ", null, ";");
}
//Looks like ON CONFLICT is useful here http://www.postgresql.org/docs/current/static/sql-insert.html
//Use RETURNING in place of SQL Servers OUTPUT clause http://www.postgresql.org/docs/current/static/sql-insert.html
return(new PostgreSqlCommandExecutionToken(DataSource, "Insert or update " + Table.Name, sql.ToString(), sqlBuilder.GetParameters()));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="MySqlCommandExecutionToken" /></returns>
public override CommandExecutionToken <MySqlCommand, MySqlParameter> Prepare(Materializer <MySqlCommand, MySqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
var identityInsert = m_Options.HasFlag(UpsertOptions.IdentityInsert);
var sql = new StringBuilder();
List <MySqlParameter> keyParameters;
var isPrimaryKeyIdentity = sqlBuilder.PrimaryKeyIsIdentity(out keyParameters);
if (isPrimaryKeyIdentity && !identityInsert)
{
var areKeysNull = keyParameters.Any(c => c.Value == DBNull.Value || c.Value == null) ? true : false;
if (areKeysNull)
{
sqlBuilder.BuildInsertStatement(sql, Table.Name.ToString(), null);
}
else
{
sqlBuilder.BuildUpdateByKeyStatement(sql, Table.Name.ToString(), null);
}
sql.Append(";");
}
else
{
sqlBuilder.BuildInsertClause(sql, $"INSERT INTO {Table.Name.ToString()} (", null, ")", identityInsert);
sqlBuilder.BuildValuesClause(sql, " VALUES (", ")", identityInsert);
sqlBuilder.BuildSetClause(sql, $" ON DUPLICATE KEY UPDATE ", null, null);
sql.Append(";");
}
if (sqlBuilder.HasReadFields)
{
var keys = sqlBuilder.GetKeyColumns().ToList();
if (keys.Count != 1)
{
throw new NotSupportedException("Cannot return data from a MySQL Upsert unless there is a single primary key.");
}
var key = keys[0];
sqlBuilder.BuildSelectClause(sql, "SELECT ", null, $" FROM {Table.Name.ToQuotedString()} WHERE {key.QuotedSqlName} = CASE WHEN {key.SqlVariableName} IS NULL OR {key.SqlVariableName} = 0 THEN LAST_INSERT_ID() ELSE {key.SqlVariableName} END;");
}
return(new MySqlCommandExecutionToken(DataSource, "Insert or update " + Table.Name, sql.ToString(), sqlBuilder.GetParameters()));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="SQLiteCommandExecutionToken" /></returns>
public override CommandExecutionToken <SQLiteCommand, SQLiteParameter> Prepare(Materializer <SQLiteCommand, SQLiteParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var identityInsert = m_Options.HasFlag(UpsertOptions.IdentityInsert);
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
if (KeyColumns.Count > 0)
{
sqlBuilder.OverrideKeys(KeyColumns);
}
var sql = new StringBuilder();
sqlBuilder.BuildUpdateByKeyStatement(sql, Table.Name.ToQuotedString(), ";");
sql.AppendLine();
sqlBuilder.BuildInsertClause(sql, $"INSERT OR IGNORE INTO {Table.Name.ToQuotedString()} (", null, ")", identityInsert);
sqlBuilder.BuildValuesClause(sql, " VALUES (", ");", identityInsert);
sql.AppendLine();
if (sqlBuilder.HasReadFields)
{
var keys = sqlBuilder.GetKeyColumns().ToList();
if (keys.Count != 1)
{
throw new NotSupportedException("Cannot return data from a SQLite Upsert unless there is a single primary key.");
}
var key = keys[0];
sqlBuilder.BuildSelectClause(sql, "SELECT ", null, $" FROM {Table.Name.ToQuotedString()} WHERE {key.QuotedSqlName} = CASE WHEN {key.SqlVariableName} IS NULL OR {key.SqlVariableName} = 0 THEN last_insert_rowid() ELSE {key.SqlVariableName} END;");
}
return(new SQLiteCommandExecutionToken(DataSource, "Insert or update " + Table.Name, sql.ToString(), sqlBuilder.GetParameters(), lockType: LockType.Write));
}
