protected override IEnumerable <string> GetBatches(ISQLBuilder sqlBuilder)
{
return
(sqlBuilder
.Statements
.Select(statement => statement.SQL));
}
public static ISQLBuilder EmitOrderByExpression(this ISQLBuilder sqlBuilder, IEnumerable <SortOption> sortOptions)
{
foreach (var so in sortOptions.WithDescriptions())
{
if (!so.Description.HasFlag(EnumeratedItemDescription.First))
{
sqlBuilder.Emit(", ");
}
sqlBuilder.TableName(so.Item.Name);
switch (so.Item.Direction)
{
case SortDirection.Ascending:
sqlBuilder.Emit(" ASC");
break;
case SortDirection.Descending:
sqlBuilder.Emit(" DESC");
break;
case SortDirection.None:
default:
break;
}
}
return(sqlBuilder);
}
public override DataTable[] ExecuteBatch(ISQLBuilder sqlBuilder)
{
// Firebird doesn't like mixing DDL with DML, so we break up
// DDL's that contains CREATE into an initial transaction
// Then rest of statements in another transaction
var statements = sqlBuilder.Statements.Where(s => s.Type.IsIn(SQLStatementType.DDL, SQLStatementType.DML)).ToList();
statements.RemoveAll(s => string.IsNullOrEmpty(s.SQL));
var ddls = statements.Where(statement => statement.Type == SQLStatementType.DDL && statement.SQL.TrimStart().StartsWith("CREATE")).ToList();
statements.RemoveAll(ddls.Contains);
var results = new List <DataTable>();
if (ddls.Count > 0)
{
results.AddRange(ExecuteStatementBatch(ddls.Select(s => s.SQL)));
}
if (statements.Count > 0)
{
results.AddRange(ExecuteStatementBatch(statements.Select(s => s.SQL)));
}
return(results.ToArray());
}
// This needs to be done properly!
protected override IEnumerable <string> GetBatches(ISQLBuilder sqlBuilder)
{
yield return("SET XACT_ABORT ON;");
yield return("BEGIN TRANSACTION;");
// TODO: issue batches in smaller chunks
var currentBatch = new FastStringBuilder();
foreach (var statement in sqlBuilder.Statements.Where(x => x.Type != SQLStatementType.TCL))
{
if (statement.Type == SQLStatementType.DDL)
{
if (currentBatch.Length > 0)
{
yield return(currentBatch.ToString());
currentBatch.Clear();
}
yield return(statement.SQL);
}
else
{
currentBatch.Append(statement.SQL);
}
}
if (currentBatch.Length > 0)
{
yield return(currentBatch.ToString());
}
yield return("COMMIT TRANSACTION;");
}
public virtual DataTable[] ExecuteBatch(ISQLBuilder sqlBuilder)
{
try {
var results = new List <DataTable>();
using (var scope = this.BeginScope(true)) {
using (var command = scope.Connection.CreateCommand()) {
command.CommandTimeout = 0;
command.CommandType = CommandType.Text;
//command.Transaction =;
if (scope.Transaction != null)
{
command.Transaction = scope.Transaction.DangerousInternalTransaction;
}
// var fileID = Path.Combine("c:\\temp\\", this.ConnectionString.ToPathSafe() + ".txt");
foreach (var batch in GetBatches(sqlBuilder))
{
// File.AppendAllText(fileID, batch);
command.CommandText = batch;
Log.Debug(Environment.NewLine + batch);
//command.ExecuteNonQuery();
#warning this code needs to be studied for optimization
using (var reader = command.ExecuteReader(CommandBehavior.Default)) {
results.AddRange(reader.ToDataTables());
}
}
}
}
return(results.ToArray());
} catch (Exception error) {
Log.Error(error.ToDiagnosticString());
throw;
}
}
protected override IEnumerable <string> GetBatches(ISQLBuilder sqlBuilder)
{
return
(sqlBuilder
.Statements
.Where(s => s.Type != SQLStatementType.TCL && !string.IsNullOrEmpty(s.SQL))
.Select(statement => statement.SQL));
}
public VeryLargeSQLBuilder(ISQLBuilder internaBuilder, int scriptPageSize)
: base(internaBuilder)
{
SystemLog.Info("VeryLargeScriptBuilder created - scriptPageSize = {0}", scriptPageSize);
_pageSize = scriptPageSize;
_generatedContainers = new List <LargeCollection <SQLStatement> >();
// We clear again, which forces the base implementation to recreate the container
base.Clear();
}
public ExecuteScriptForm(IDAC dac, ISQLBuilder script)
{
InitializeComponent();
_scriptTextBox.Text = script.ToString();
_scriptTextBox.FocusAtEnd();
_databaseConnectionStringLabel.Text = dac.ConnectionString;
DAC = dac;
Script = script;
DialogResult = DialogResult.Cancel;
}
public static ISQLBuilder Update(this ISQLBuilder @this, DataRow dataRow)
{
var primaryKeyCols = dataRow.Table.PrimaryKey;
var allCols = dataRow.Table.Columns.Cast <DataColumn>();
return
(@this.Update(
dataRow.Table.TableName,
allCols.Except(primaryKeyCols).Select(c => new ColumnValue(c.ColumnName, dataRow[c])),
matchColumns: primaryKeyCols.Select(c => new ColumnValue(c.ColumnName, dataRow[c]))));
}
public static ISQLBuilder Insert(this ISQLBuilder @this, DataRow dataRow)
{
var primaryKeyCols = dataRow.Table.PrimaryKey;
var allCols = dataRow.Table.Columns.Cast <DataColumn>();
return
(@this.Insert(
dataRow.Table.TableName,
(dataRow.Table.HasAutoIncrementPrimaryKey() ? allCols.Except(primaryKeyCols) : allCols)
.Select(c => new ColumnValue(c.ColumnName, dataRow[c]))));
}
/// <summary>
/// Routine to create an instance of the PFQueryBuilder class.
/// </summary>
/// <param name="dbPlat">Database platform to use.</param>
/// <param name="connStr">Connection string for the query.</param>
/// <param name="ansiLevel">AnsiSQLLevel for the query builder to use.</param>
/// <returns>PFQueryBuilder object.</returns>
public static ISQLBuilder CreateQueryBuilderObject(DatabasePlatform dbPlat, string connStr, AnsiSQLLevel ansiLevel)
{
ISQLBuilder qbf = null;
qbf = LoadObjectFromAssembly(dbPlat);
qbf.DatabasePlatform = qbf.ConvertDbPlatformToQueryBuilderPlatform(dbPlat);
qbf.ConnectionString = connStr;
qbf.AnsiSQLVersion = ansiLevel;
return(qbf);
}
public static StringBuilder GenerateInsertStatement(ISQLBuilder builder, IEnumerable <GenerateConfig> configs)
{
var result = new StringBuilder();
using (var writer = new StringWriter(result))
{
GenerateInsertStatement(builder, configs, writer);
}
GC.Collect();
return(result);
}
public RepositoryBase(JunDbContext dbContext, ISQLBuilder sqlBuilder, WebHelper webHelper, ILogger <RepositoryBase> logger)
{
this._dbContext = dbContext;
this._sqlBuilder = sqlBuilder;
this._webHelper = webHelper;
this._logger = logger;
this.Conn = _dbContext.Database.GetDbConnection();
if (Conn.State == System.Data.ConnectionState.Closed)
{
Conn.Open();
}
}
//private ILogger<DefaultDatabase> _logger;
//public IDatabaseEngine DatabaseEngine { get { return _databaseEngine; } }
public DefaultDatabase(IDatabaseSettings databaseSettings, IDatabaseEngine databaseEngine, IDatabaseEntityDefFactory modelDefFactory, IDatabaseEntityMapper modelMapper, ISQLBuilder sqlBuilder /*, ILogger<DefaultDatabase> logger*/)
{
if (databaseSettings.Version < 0)
{
throw new ArgumentException("Database Version should greater than 0");
}
_databaseSettings = databaseSettings;
_databaseEngine = databaseEngine;
_entityDefFactory = modelDefFactory;
_modelMapper = modelMapper;
_sqlBuilder = sqlBuilder;
//_logger = logger;
}
/// <summary>
/// 获取SQL构造实现
/// </summary>
/// <returns>SQL构造实现</returns>
public static ISQLBuilder GetInstance()
{
if (builder == null)
{
lock (syncObj)
{
if (builder == null)
{
builder = new SQLBuilderImpl();
}
}
}
return builder;
}
public SQLBuilderWithTableBasedVariables(ISQLBuilder internalBuilder, string variableTableName = null, bool alreadyCreated = false) : base(internalBuilder)
{
VariableTableName = variableTableName ?? "VAR" + Tools.Text.ToBase32(Guid.NewGuid().ToByteArray().ToASCIIString()); // "VAR7BHBHPURK64WYPK3PA2TT6K3U2";
VariableTableHasBeenCreated = alreadyCreated;
DontDropOnEnd = alreadyCreated;
_variableDeclarations = new Dictionary <string, Tuple <int, VariableStorageClass> >();
_columnLookup =
Enum
.GetValues(typeof(VariableStorageClass))
.Cast <VariableStorageClass>()
.OrderBy(x => x)
.WithDescriptions()
.Select(val =>
new {
StorageClass = val.Item,
Order = val.Index,
ColumnName = val.Item.GetAttributes <DescriptionAttribute>().First().Description
}
)
.ToDictionary(x => x.StorageClass, x => Tuple.Create(x.Order, x.ColumnName));
}
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));
}
protected virtual IEnumerable <string> GetBatches(ISQLBuilder sqlBuilder)
{
return(new[] { sqlBuilder.ToString() });
}
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 static string QuickString(this ISQLBuilder sqlBuilder, string sql, params object[] args)
{
var newBuilder = sqlBuilder.CreateBuilder();
return(newBuilder.Emit(sql, args).ToString());
}
public static ISQLBuilder Select(this ISQLBuilder sqlBuilder, string tableName, IEnumerable <string> columns = null, bool distinct = false, int?limit = null, IEnumerable <ColumnValue> columnMatches = null, string whereClause = null, string orderByClause = null)
{
return(sqlBuilder.Select(tableName, columns: columns != null ? columns.Cast <object>() : null, distinct: distinct, limit: limit, columnMatches: columnMatches, whereClause: whereClause, orderByClause: orderByClause));
}
public static ISQLBuilder TriggerName(this ISQLBuilder sqlBuilder, string name)
{
return(sqlBuilder.ObjectName(name));
}
public static ISQLBuilder InsertMany <T>(this ISQLBuilder sqlBuilder, string table, IEnumerable <string> columns, IEnumerable <T> values) where T : IEnumerable <object>
{
return(sqlBuilder.InsertMany(table, columns, values.Select(v => v.Cast <object>())));
}
public override void Execute(ISQLBuilder builder)
{
builder.Literal(LiteralValue);
}
public VeryLargeSQLBuilder(ISQLBuilder internaBuilder) : this(internaBuilder, DefaultScriptPageSize)
{
}
public override void Execute(ISQLBuilder builder)
{
builder.Cast(ValueKind, Value, Type);
}
public BatchSQLBuilder(ISQLBuilder decoratedBuilder, uint batchSize)
: base(decoratedBuilder)
{
_batchSize = Tools.Maths.ClipValue(batchSize, 1, uint.MaxValue - 1);
}
public override void Execute(ISQLBuilder builder)
{
builder.EndOfStatement(StatementType);
}
public static ISQLBuilder TableName(this ISQLBuilder sqlBuilder, string name, TableType tableType = TableType.Persistent)
{
return(sqlBuilder.TableName(name, tableType));
}
public override void Execute(ISQLBuilder builder)
{
builder.VariableName(VariableName);
}
