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