/// <summary>
/// 配置Sql生成器
/// </summary>
private static void AddSqlBuilder(IServiceCollection services, SqlOptions config)
{
switch (config.DatabaseType)
{
case DatabaseType.SqlServer:
services.TryAddScoped <ISqlBuilder, SqlServerBuilder>();
return;
case DatabaseType.PgSql:
services.TryAddScoped <ISqlBuilder, PgSqlBuilder>();
return;
case DatabaseType.MySql:
services.TryAddScoped <ISqlBuilder, MySqlBuilder>();
return;
default:
throw new NotImplementedException($"Sql生成器未实现 {config.DatabaseType.Description()} 数据库");
}
}
public override string BuildSql(SqlOptions sqlOptions)
{
var keys = string.Join(", ", Entities.Select(e => e.BuildSql(sqlOptions, FlowOptions.Construct(this))));
string command;
switch (sqlOptions.DatabaseType)
{
case SqlDatabaseType.Postgres:
case SqlDatabaseType.SqLite:
command = "ON CONFLICT";
return($"{command} ({keys})");
case SqlDatabaseType.MySql:
case SqlDatabaseType.MariaDb:
command = "ON DUPLICATE KEY";
return($"{command}");
default: return(null);
}
}
public override string BuildSql(SqlOptions sqlOptions)
{
string command;
switch (sqlOptions.DatabaseType)
{
case SqlDatabaseType.MsSql:
command = sqlOptions.Command("FETCH NEXT");
break;
default:
command = sqlOptions.Command("LIMIT");
break;
}
var limitBy = LimitBy?.BuildSql(sqlOptions, FlowOptions.Construct(this));
return($"{command} {limitBy}");
}
public void SelectCountStar_EqualsExpected()
{
var table = new SqlTable <Class1>();
var sqlOptions = new SqlOptions {
Dialect = SqlDialect.Postgres95
};
var sqlBuilder = SqlBuilder.Select
.CountStar()
.From(table);
var actual = sqlBuilder.BuildSql(sqlOptions);
var expected = @"
SELECT
COUNT(*)
FROM foo.class1
";
Check(expected, actual);
Assert.AreEqual("", sqlBuilder.SplitOn);
}
public virtual string Condition(Condition condition, SqlOptions sql)
{
return(condition.ConditionType switch
{
ConditionType.Between => $"{condition.Column.ToString(sql)} BETWEEN " + ConditionBetween(condition, sql),
ConditionType.NotBetween => $"{condition.Column.ToString(sql)} NOT BETWEEN " + ConditionBetween(condition, sql),
ConditionType.Equal => ConditionToString(condition, "=", sql),
ConditionType.NotEqual => ConditionToString(condition, "<>", sql),
ConditionType.GreaterThan => ConditionToString(condition, ">", sql),
ConditionType.LessThan => ConditionToString(condition, "<", sql),
ConditionType.Like => ConditionToString(condition, "LIKE", sql),
ConditionType.IsNull => $"{condition.Column.ToString(sql)} IS NULL",
ConditionType.IsNotNull => $"{condition.Column.ToString(sql)} IS NOT NULL",
ConditionType.All => $"{condition.Column.ToString(sql)} = ALL ({(condition.Item as Select).ToString(sql)})",
ConditionType.Any => $"{condition.Column.ToString(sql)} = ANY ({(condition.Item as Select).ToString(sql)})",
ConditionType.Exists => $"EXISTS ({(condition.Item as Select).ToString(sql)})",
ConditionType.NotExists => $"NOT EXISTS ({(condition.Item as Select).ToString(sql)})",
ConditionType.In => $"{condition.Column.ToString(sql)} IN ({(condition.Item as Select).ToString(sql)})",
ConditionType.NotIn => $"{condition.Column.ToString(sql)} NOT IN ({(condition.Item as Select).ToString(sql)})",
_ => "",
});
public void Select_JoinViaThirdTable()
{
var table1 = new SqlTable <Class1>();
var table2 = new SqlTable <Class2>();
var table3 = new SqlTable <Class3>();
var sqlBuilder = SqlBuilder.Select.Star().From(table1).InnerJoin(table3).InnerJoin(table2);
var sqlOptions = new SqlOptions {
Dialect = SqlDialect.Postgres95
};
var actual = sqlBuilder.BuildSql(sqlOptions);
var expected = @"
SELECT
*
FROM foo.class1
INNER JOIN foo.class3 ON class3.key1 = class1.key
INNER JOIN foo.class2 ON class2.key = class3.key2
";
Check(expected, actual);
}
public override string BuildSql(SqlOptions sqlOptions)
{
string dir;
switch (Direction)
{
case OrderDirection.Asc:
dir = "ASC";
break;
case OrderDirection.Desc:
dir = "DESC";
break;
default: return(null);
}
var command = sqlOptions.Command(dir);
var orderItem = OrderItemOperand?.BuildSql(sqlOptions, FlowOptions.Construct(this)) ??
throw new ArgumentException(nameof(OrderItemOperand));
return($"{orderItem} {command}");
}
public override string BuildSql(SqlOptions sqlOptions)
{
var properties = string.Join($",{sqlOptions.NewLine()}{sqlOptions.Indent()}",
Entities.Select(e => e.BuildSql(sqlOptions, FlowOptions.Construct(this))));
string command;
switch (sqlOptions.DatabaseType)
{
case SqlDatabaseType.Postgres:
case SqlDatabaseType.SqLite:
command = sqlOptions.Command("DO UPDATE SET");
break;
case SqlDatabaseType.MySql:
case SqlDatabaseType.MariaDb:
command = sqlOptions.Command("UPDATE");
break;
default: return(null);
}
return($"{command}{sqlOptions.NewLine()}{sqlOptions.Indent()}{properties}");
}
public void SelectSum_EqualsExpected()
{
var table = new SqlTable <Class1>();
var sqlOptions = new SqlOptions {
Dialect = SqlDialect.Postgres95
};
var sqlBuilder = SqlBuilder.Select
.Value(SqlBuilder.Select.Value(Operand.From(table[t => t.Value2])).From(table).Sum())
.From(table);
var actual = sqlBuilder.BuildSql(sqlOptions);
var expected = @"
SELECT
SUM(SELECT
class1.value2
FROM foo.class1)
FROM foo.class1
";
Check(expected, actual);
Assert.AreEqual("", sqlBuilder.SplitOn);
}
/// <summary>
/// Sql查询语句的锁选项
/// </summary>
/// <param name="options"></param>
/// <returns></returns>
private static string LockOption(SqlOptions options)
{
var optVal = (int)options;
if ((optVal & (int)SqlOptions.NoLock) > 0)
{
return(KeyWordNoLock);
}
if ((optVal & (int)SqlOptions.RowLock) > 0)
{
return(KeyWordRowLock);
}
if ((optVal & (int)SqlOptions.UpdateLock) > 0)
{
return(KeyWordUpdateLock);
}
if ((optVal & (int)SqlOptions.TableLock) > 0)
{
return(KeyWordTabLock);
}
//
return(string.Empty);
}
/// <summary>
/// 注册Sql查询服务
/// </summary>
private static IServiceCollection AddSqlQuery(IServiceCollection services, Action <SqlOptions> configAction, Type database, Type entityMatedata)
{
if (database != null)
{
services.TryAddScoped(database);
services.TryAddScoped(typeof(IDatabase), t => t.GetService(database));
}
services.TryAddScoped <ISqlQuery, SqlQuery>();
if (entityMatedata != null)
{
services.TryAddScoped(typeof(IEntityMatedata), t => t.GetService(entityMatedata));
}
var config = new SqlOptions();
if (configAction != null)
{
configAction.Invoke(config);
services.Configure(configAction);
}
AddSqlBuilder(services, config);
RegisterTypeHandlers();
return(services);
}
public void OrderByEnumerable_EqualsExpected()
{
var table = new SqlTable <Class1>();
var order = new[]
{
new NamedOrderItem {
PropertyName = "Value1", Direction = OrderDirection.Desc
},
new NamedOrderItem {
PropertyName = "Id", Direction = OrderDirection.Asc
},
null
};
var orderMap = OrderMap.Build(table, order, true);
var sqlOptions = new SqlOptions {
Dialect = SqlDialect.Postgres95
};
var sqlBuilder = SqlBuilder.Select
.Star()
.From(table)
.Order(orderMap);
var actual = sqlBuilder.BuildSql(sqlOptions);
var expected = @"
SELECT
*
FROM foo.class1
ORDER BY
class1.value1 DESC,
class1.id ASC
";
Check(expected, actual);
}
public override string BuildSql(SqlOptions sqlOptions)
{
if (sqlOptions == null)
{
throw new ArgumentException(nameof(sqlOptions));
}
var updatedSqlOptions = ((SqlOptions)sqlOptions.Clone()).WithoutTableNames();
CheckBeforeBuild(updatedSqlOptions);
IEnumerable <SqlBuilderEntity> data = new SqlBuilderEntity[]
{
CustomBlocks[SqlDeletePosition.Start],
DeleteFromBlock,
CustomBlocks[SqlDeletePosition.From],
WhereBlock,
CustomBlocks[SqlDeletePosition.Where],
ReturningsBlock,
CustomBlocks[SqlDeletePosition.Return]
};
var commands = data.Where(b => CheckBlock(b, updatedSqlOptions)).Select(b => b.BuildSql(updatedSqlOptions, FlowOptions.Construct(this)));
return(string.Join(sqlOptions.NewLine(), commands));
}
public override bool Present(SqlOptions sqlOptions) => true;
/// <summary>
/// Returns the <see cref="NewColumn"/> as a string.
/// </summary>
public string ToString(SqlOptions sql)
{
return(sqlTypeItem.ToString(sql));
}
public override string BuildSql(SqlOptions sqlOptions)
{
var propertyName = PropertyName;
return(propertyName);
}
public override bool Present(SqlOptions sqlOptions) => !string.IsNullOrEmpty(PropertyName);
public override bool Present(SqlOptions sqlOptions) => _selectBuilder.Present(sqlOptions);
public DbContractRepository(SqlOptions sqlOptions) : base(sqlOptions)
{
_contracts = new List <Contract>();
}
/// <summary>
/// 初始化一个<see cref="DataConfig"/>类型的实例
/// </summary>
public DataConfig()
{
SqlOptions = new SqlOptions();
}
/// <summary>
/// Return the <see cref="Insert"/> statement as a string.
/// </summary>
public virtual string ToString(SqlOptions sql)
{
return(sql.SqlBase.Insert(this, sql));
}
public override bool Present(SqlOptions sqlOptions) => InsertIntoBlock?.Present(sqlOptions) == true && InsertColumnsBlock.Present(sqlOptions) && InsertValuesBlock.Present(sqlOptions);
public override bool Present(SqlOptions sqlOptions) => DeleteFromBlock?.Present(sqlOptions) == true;
public static string IncrementByQuery(this ISqlBuilder builder, IEntityExplain entity, string alias, SqlOptions options)
{
if (entity == null)
{
throw new ArgumentNullException("metaInfo");
}
if (entity.Increment == null)
{
throw new ArgumentException("没有自动增长标识");
}
return(builder.IncrementByQuery(entity.Increment.IncrementName, alias, options));
}
/// <summary>
/// where条件
/// </summary>
/// <param name="condition"> </param>
/// <param name="output"> </param>
/// <param name="options"> </param>
/// <returns>生成的where条件</returns>
public static string Where(this ISqlBuilder builder, IDictionary <string, object> condition, ParameterCollection output, SqlOptions options)
{
if (condition == null || condition.Count == 0)
{
return(string.Empty);
}
var where = new StringBuilder();
foreach (var item in condition)
{
if (item.Value is ICollection)
{
var values = ((ICollection)item.Value);
var list = new List <string>();
foreach (var value in values)
{
list.Add(output.Append("p", value, true).ParameterName);
}
where.Append(builder.BuildField(item.Key)).Append("IN(").Append(builder.ExpressionsJoin(list)).Append(") AND ");
}
else
{
where.Append(builder.BuildField(item.Key)).Append("=").Append(output.Append("p", item.Value, true).ParameterName).Append(" AND ");
}
}
where.Remove(where.Length - 5, 5);
return(where.ToString());
}
public override string BuildSql(SqlOptions sqlOptions)
{
var columnName = ColumnName;
return(columnName);
}
public ExecuteCommandService(SqlOptions sqlOptions)
{
this.sqlOptions = sqlOptions ?? throw new ArgumentNullException(nameof(sqlOptions));
}
private void AddAttributesTable(string newTypeName)
{
var sqlQuery = SqlOptions.GenerateCreateAttributesTableScript(newTypeName);
ExecuteSqlAndSave(sqlQuery);
}
/// <summary>
/// 初始化Dapper Sql查询对象
/// </summary>
/// <param name="sqlBuilder">Sql生成器</param>
/// <param name="database">数据库</param>
/// <param name="sqlOptions">Sql配置</param>
protected SqlQuery(ISqlBuilder sqlBuilder, IDatabase database, SqlOptions sqlOptions) : base(sqlBuilder, database, sqlOptions)
{
}
public override string BuildSql(SqlOptions sqlOptions) => "*";
