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