public override ISQLBuilder Select(string tableName, IEnumerable <object> columns = null, bool distinct = false, int?limit = null, int?offset = null, IEnumerable <ColumnValue> columnMatches = null, string whereClause = null, string orderByClause = null, bool endStatement = false)
{
if (offset.HasValue)
{
if (!limit.HasValue)
{
throw new NotSupportedException("When selecting by an offset the limit parameter must also be supplied for this SQL Builder");
}
const string pagedQuery =
@"WITH __UNPAGED_RESULT AS (
{0}
), __PAGED_RESULT AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY {3}) AS __ROWNUM FROM __UNPAGED_RESULT
)
SELECT * FROM __PAGED_RESULT WHERE __ROWNUM > {2} AND __ROWNUM <= ({2} + {1})";
Emit(
pagedQuery,
SQLBuilderCommand.Generic(b => b.Select(tableName, columns: columns, distinct: distinct, limit: null, offset: null, columnMatches: columnMatches, whereClause: whereClause, orderByClause: null, endStatement: false)),
limit.Value,
offset.Value,
string.IsNullOrEmpty(orderByClause) ? "@@IDENTITY" : orderByClause
);
if (endStatement)
{
base.EndOfStatement();
}
return(this);
}
return(base.Select(tableName, columns: columns, distinct: distinct, limit: limit, offset: null, columnMatches: columnMatches, whereClause: whereClause, orderByClause: orderByClause, endStatement: endStatement));
}
public override ISQLBuilder SelectValues(IEnumerable <object> values, string whereClause, params object[] whereClauseFormatArgs)
{
if (!values.Any())
{
throw new ArgumentException("values is empty", "values");
}
Emit("SELECT ");
foreach (var value in values.WithDescriptions())
{
if (value.Index > 0)
{
Emit(", ");
}
if (value.Item == null)
{
Emit("NULL");
}
else if (value.Item is ColumnValue)
{
var columnValue = value.Item as ColumnValue;
Emit("{0} AS ", columnValue.Value, SQLBuilderCommand.ColumnName(columnValue.ColumnName));
}
else
{
Literal(value.Item);
}
}
Emit(" FROM RDB$DATABASE");
if (!string.IsNullOrEmpty(whereClause))
{
Emit(" WHERE ").Emit(whereClause, whereClauseFormatArgs);
}
return(this);
}
public static IDictionary <string, long> CountRecords(this IDAC dac, IEnumerable <string> tableNames = null)
{
var result = new Dictionary <string, long>();
if (tableNames == null)
{
tableNames = dac.GetSchemaCached().Tables.Select(table => table.Name);
}
if (!tableNames.Any())
{
return(result);
}
var sqlBuilder = dac.CreateSQLBuilder();
foreach (var table in tableNames.WithDescriptions())
{
if (table.Index > 0)
{
sqlBuilder.NewLine().Emit("UNION ALL").NewLine();
}
sqlBuilder.Emit("SELECT '{0}', COUNT(1) FROM {1}", table.Item, SQLBuilderCommand.TableName(table.Item));
}
dac
.ExecuteQuery(((Object)sqlBuilder).ToString())
.Rows
.Cast <DataRow>()
.ForEach(row => result.Add(row.Get <string>(0), row.Get <long>(1)));
return(result);
}
public override ISQLBuilder AssignVariable(string variableName, object value)
{
if (!_variableDeclarations.ContainsKey(variableName))
{
throw new SoftwareException("A variable with name '{0}' has not been declared", variableName);
}
var variable = _variableDeclarations[variableName];
var variableID = variable.Item1;
var variableStorageClass = variable.Item2;
Update(
this.QuickString("{0}", SQLBuilderCommand.TableName(VariableTableName, TableType.Temporary)),
new[] {
new ColumnValue("IntValue", variableStorageClass == VariableStorageClass.Integer ? value : null),
new ColumnValue("TextValue", variableStorageClass == VariableStorageClass.Text ? value : null),
new ColumnValue("DateValue", variableStorageClass == VariableStorageClass.DateTime ? value : null),
new ColumnValue("UuidValue", variableStorageClass == VariableStorageClass.Uuid ? value : null),
new ColumnValue("BlobValue", variableStorageClass == VariableStorageClass.Blob ? value : null)
},
matchColumns:
new[] {
new ColumnValue("ID", variableID)
}
);
return(this);
}
public static long GetMaxID(this IDAC dac, string tableName, string idColName)
{
return(dac.ExecuteScalar <long>(
dac.QuickString(
"SELECT MAX(T.ID) FROM (SELECT {0} ID FROM {1} WHERE ID IS NOT NULL UNION SELECT 0 ID ) T",
SQLBuilderCommand.ColumnName(idColName),
SQLBuilderCommand.TableName(tableName)
)
));
}
public override ISQLBuilder VariableName(string variableName)
{
if (!_variableDeclarations.ContainsKey(variableName))
{
throw new SoftwareException("A variable with name '{0}' has not been declared", variableName);
}
var variable = _variableDeclarations[variableName];
var variableID = variable.Item1;
var variableStorageClass = variable.Item2;
string column;
switch (variableStorageClass)
{
case VariableStorageClass.Integer:
column = "IntValue";
break;
case VariableStorageClass.DateTime:
column = "DateValue";
break;
case VariableStorageClass.Text:
column = "TextValue";
break;
case VariableStorageClass.Uuid:
column = "UuidValue";
break;
case VariableStorageClass.Blob:
column = "BlobValue";
break;
default:
throw new NotImplementedException("Unknown VariableStorageClass " + variableStorageClass.ToString());
}
return
(Emit(
"(SELECT {0} FROM {1} WHERE {2} = {3})",
SQLBuilderCommand.ColumnName(column),
SQLBuilderCommand.TableName(VariableTableName, TableType.Temporary),
SQLBuilderCommand.ColumnName("ID"),
SQLBuilderCommand.Literal(variableID)
));
}
public virtual ISQLBuilder CreateTable(TableSpecification tableSpecification)
{
bool singularyPrimaryKey = tableSpecification.PrimaryKey.Columns.Length == 1;
bool hasDeclarationsAfterColumns = !singularyPrimaryKey;
Emit("{0} ", FormatCreateTable(tableSpecification.Type))
.TableName(tableSpecification.Name, tableSpecification.Type)
.Emit("(")
.NewLine();
this.TabRight();
foreach (var column in tableSpecification.Columns.WithDescriptions())
{
Emit(
"{0} {1}{2}{3}{4}",
SQLBuilderCommand.ColumnName(column.Item.Name),
!string.IsNullOrEmpty(column.Item.DataType) ? column.Item.DataType : ConvertTypeToSQLType(column.Item.Type),
singularyPrimaryKey && column.Item.Name == tableSpecification.PrimaryKey.Columns[0] ? " PRIMARY KEY" : string.Empty,
!column.Item.Nullable ? " NOT NULL" : string.Empty,
(column.Description.HasFlag(EnumeratedItemDescription.Last) && !hasDeclarationsAfterColumns) ? string.Empty : ", "
)
.NewLine();
}
if (!singularyPrimaryKey)
{
Emit("PRIMARY KEY(");
foreach (var primaryKeyColumn in tableSpecification.PrimaryKey.Columns.WithDescriptions())
{
if (primaryKeyColumn.Description != EnumeratedItemDescription.Last)
{
Emit(", ");
}
this.ColumnName(primaryKeyColumn.Item);
}
}
this.Untab();
Emit(")");
Emit(FormatCreateTableEnd(tableSpecification.Type));
return(EndOfStatement(SQLStatementType.DDL));
}
public override ISQLBuilder DeclareVariable(string variableName, Type type)
{
if (_variableDeclarations.ContainsKey(variableName))
{
throw new SoftwareException("A variable with name '{0}' has already been declared", variableName);
}
if (!VariableTableHasBeenCreated)
{
CreateVariableTempTable(VariableTableName);
}
if (VariableDeclarationCount == 0)
{
Emit("DELETE FROM ").TableName(VariableTableName, TableType.Temporary).EndOfStatement();
}
var variableID = VariableDeclarationCount++;
Insert(this.QuickString("{0}", SQLBuilderCommand.TableName(VariableTableName, TableType.Temporary)), new[] { new ColumnValue("ID", variableID) });
_variableDeclarations.Add(variableName, Tuple.Create(variableID, TypeToStorageClass(type)));
return(this);
}
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);
}
public virtual string FormatTableName(string tableName, TableType tableType)
{
return(this.QuickString("{0}", SQLBuilderCommand.TableName(tableName)));
}