public override long Insert(string tableName, IEnumerable <ColumnValue> setValues)
{
// get the policy, strategy and convention which applies to this table
// TODO: add ability to override these for specific tables, currently just uses default for all tables
var autoIdentityPolicy = DefaultAutoIdentityPolicy;
var primaryKeyConvention = DefaultPrimaryKeyConvention;
// Determine what needs to be done
ColumnValue primarykeyColumn;
bool needToEnableExplicitID;
bool needToCalculateID;
bool hasPrimaryKeyInInsert = DACTool.TryFindPrimaryKeyColumnValue(primaryKeyConvention, tableName, setValues, out primarykeyColumn);
switch (autoIdentityPolicy)
{
case AutoIdentityPolicy.UseDBMSAutoIncrement:
needToEnableExplicitID = hasPrimaryKeyInInsert;
needToCalculateID = false;
break;
case AutoIdentityPolicy.CalculateAutoIncrement:
case AutoIdentityPolicy.CalculateAutoDecrement:
needToCalculateID = !hasPrimaryKeyInInsert;
needToEnableExplicitID = true;
break;
case AutoIdentityPolicy.None:
default:
return(base.Insert(tableName, setValues)); // Pass through to underlying DAC
}
// Start building the SQL
var sqlBuilder =
CreateSQLBuilder()
.DeclareVariable("NewID", typeof(long));
if (needToCalculateID)
{
if (!hasPrimaryKeyInInsert)
{
// Add the PK to the insert clause
primarykeyColumn = new ColumnValue(
DACTool.GeneratePrimaryKeyColumnName(primaryKeyConvention, tableName),
SQLBuilderCommand.Variable("NewID")
);
setValues = setValues.Concat(primarykeyColumn);
}
// Calculate the PK value
if (!autoIdentityPolicy.IsIn(AutoIdentityPolicy.CalculateAutoIncrement, AutoIdentityPolicy.CalculateAutoDecrement))
{
throw new SoftwareException("Internal Error. Unable to calculate ID for policy '{0}'.", autoIdentityPolicy);
}
sqlBuilder
.AssignVariable(
"NewID",
SQLBuilderCommand.Expression(
typeof(long),
autoIdentityPolicy == AutoIdentityPolicy.CalculateAutoIncrement ?
"(SELECT MAX(T.ID) FROM (SELECT [{0}] as ID FROM [{1}] UNION SELECT 0 as ID ) T) + 1" :
"(SELECT MIN(T.ID) FROM (SELECT [{0}] as ID FROM [{1}] UNION SELECT 0 as ID ) T) - 1",
primarykeyColumn.ColumnName,
tableName)
);
}
else if (hasPrimaryKeyInInsert)
{
// ID explicitly given
sqlBuilder.AssignVariable("NewID", primarykeyColumn.Value);
}
// Set the PK value to the variable
if (hasPrimaryKeyInInsert)
{
primarykeyColumn.Value = SQLBuilderCommand.Variable("NewID");
}
// Enable explicit ID if necessary
if (needToEnableExplicitID)
{
sqlBuilder.DisableAutoIncrementID(tableName);
}
// Insert the record already
sqlBuilder.Insert(tableName, setValues);
// Disable explicit ID if we enabled it
if (needToEnableExplicitID)
{
sqlBuilder.EnableAutoIncrementID(tableName);
}
// If DBMS calculated the ID, then assign our variable to the last identity
if (!needToCalculateID && !hasPrimaryKeyInInsert)
{
sqlBuilder.AssignVariable("NewID", SQLBuilderCommand.LastIdentity(tableName));
}
// Select the identity variable
sqlBuilder
.Emit("SELECT ")
.VariableName("NewID")
.EndOfStatement()
.End();
// run the query returning the ID
var generatedID = base.ExecuteScalar(((Object)sqlBuilder).ToString());
return((long)generatedID);
}
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);
}