public static ISQLBuilder InsertSelect(
this ISQLBuilder sqlBuilder,
string insertTable,
IEnumerable <string> insertColumns,
string table,
IEnumerable <object> columns = null,
bool distinct = false,
int?limit = null,
IEnumerable <ColumnValue> columnMatches = null,
string whereClause = null,
string orderByClause = null
)
{
if (columns != null && columns.Count() != insertColumns.Count())
{
throw new ArgumentException("Insert column count doesnt match select", "insertColumns");
}
sqlBuilder.Emit("INSERT INTO ").TableName(insertTable);
if (insertColumns.Any())
{
sqlBuilder.Emit(" (");
insertColumns.WithDescriptions().ForEach(
c => {
if (!c.Description.HasFlag(EnumeratedItemDescription.First))
{
sqlBuilder.Emit(", ");
}
sqlBuilder.ColumnName(c.Item);
}
);
}
sqlBuilder.Emit(")").NewLine();
return(sqlBuilder.Select(table, columns: columns, distinct: distinct, limit: limit, columnMatches: columnMatches, whereClause: whereClause, orderByClause: orderByClause));
}
public static ISQLBuilder DuplicateRow(this ISQLBuilder sqlBuilder, DBTableSchema table, IEnumerable <object> sourcePrimaryKey, IEnumerable <object> destPrimaryKey, out string identityVariable, IEnumerable <ColumnValue> overrideColumns = null)
{
// Query structure:
// INSERT INTO Table([PkCol1], ..., [PkColN], [NonPkCol1], ..., [NonPkColN]) VALUES SELECT {destPrimaryKey1}, ..., {destPrimaryKeyN}, [NonPkCol1], ..., [NonPkColN] FROM Table WHERE [PkCol1] = {sourcePrimaryKey1}, ..., [PkColN] = {sourcePrimaryKeyN}
bool isAutoIncrement;
bool usesGenerator;
bool specifiesPrimaryKey;
#region Validation
if (sourcePrimaryKey == null)
{
throw new SoftwareException("Source primary key not specified");
}
if (destPrimaryKey == null)
{
destPrimaryKey = Enumerable.Empty <object>();
}
if (overrideColumns == null)
{
overrideColumns = Enumerable.Empty <ColumnValue>();
}
if (table.PrimaryKeyColumns.Length == 0)
{
throw new SoftwareException("Table '{0}' does not have a primary key", table.Name);
}
if (!sourcePrimaryKey.Any())
{
throw new SoftwareException("Inconsistent primary key parameter. Table {0} primary key has {1} columns, argument specified {2} values", table.Name, table.PrimaryKeyColumns.Length, sourcePrimaryKey.Count());
}
isAutoIncrement = table.PrimaryKeyColumns.Length == 1 && table.PrimaryKeyColumns[0].IsAutoIncrement;
usesGenerator = false;
specifiesPrimaryKey = destPrimaryKey.Any();
if (!(isAutoIncrement || usesGenerator) && !specifiesPrimaryKey)
{
throw new SoftwareException("Destination primary key not specified");
}
#endregion
identityVariable = null;
var sourcePrimaryKeyArray = sourcePrimaryKey.ToArray();
// Declare variable to store generated identity (if applicable)
if ((isAutoIncrement || usesGenerator) && !specifiesPrimaryKey)
{
identityVariable = string.Format("uniquedup{0}", sqlBuilder.VariableDeclarationCount + 1);
sqlBuilder.DeclareVariable(identityVariable, typeof(long));
}
// get the non-primary key columns
var nonPkColumns = table.Columns.Where(c => !c.IsPrimaryKey);
// disable autoincrement if user specified key
if (isAutoIncrement && specifiesPrimaryKey)
{
sqlBuilder.DisableAutoIncrementID(table.Name);
}
sqlBuilder
.Emit("INSERT INTO ").TableName(table.Name).Emit("(");
// TODO: Changed Union to Concat -- will this introduce errors?
((specifiesPrimaryKey || usesGenerator) ?
table.PrimaryKeyColumns :
Enumerable.Empty <DBColumnSchema>()
).Concat(nonPkColumns)
.WithDescriptions()
.ForEach(
colDescription => {
if (colDescription.Index > 0)
{
sqlBuilder.Emit(", ");
}
sqlBuilder.ColumnName(colDescription.Item.Name);
}
);
sqlBuilder.Emit(") SELECT ");
var overrideColumnsLookup = overrideColumns.ToDictionary(c => c.ColumnName, c => c.Value);
if (specifiesPrimaryKey)
{
// insert explicit primary key values
destPrimaryKey
.WithDescriptions()
.ForEach(
destPrimaryKeyValue => {
if (destPrimaryKeyValue.Index > 0)
{
sqlBuilder.Emit(", ");
}
sqlBuilder.Literal(destPrimaryKeyValue.Item);
}
);
}
else if (usesGenerator)
{
// insert call to generator
throw new NotImplementedException();
}
nonPkColumns
.WithDescriptions()
.ForEach(
colDescription => {
if (specifiesPrimaryKey || usesGenerator || colDescription.Index > 0)
{
sqlBuilder.Emit(", ");
}
if (overrideColumnsLookup.ContainsKey(colDescription.Item.Name))
{
sqlBuilder.Literal(overrideColumnsLookup[colDescription.Item.Name]);
}
else
{
sqlBuilder.ColumnName(colDescription.Item.Name);
}
}
);
sqlBuilder.Emit(" FROM ").TableName(table.Name).Emit(" WHERE ");
table
.PrimaryKeyColumns
.WithDescriptions()
.ForEach(
pkCol => {
if (pkCol.Index > 0)
{
sqlBuilder.Emit(" AND ");
}
sqlBuilder
.ColumnName(pkCol.Item.Name)
.Emit(" = ")
.Literal(sourcePrimaryKeyArray[pkCol.Index]);
}
);
sqlBuilder.EndOfStatement();
if (isAutoIncrement)
{
sqlBuilder.EnableAutoIncrementID(table.Name);
}
if ((isAutoIncrement || usesGenerator) && !specifiesPrimaryKey)
{
sqlBuilder.AssignVariable(identityVariable, SQLBuilderCommand.LastIdentity(table.Name));
}
return(sqlBuilder);
}
public override void Execute(ISQLBuilder builder)
{
builder.ColumnName(ColumnName);
}