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