public void SequenceWriter(string name, int start, int step, DbDialect dialect,ScriptMode mode, string test){
var pt = new PersistentClass{Name = name, Schema = "dbo"};
var sq = new Sequence();
sq.Setup(null,pt,null,null);
var wr = new SequenceWriter(sq) { NoComment = true, NoDelimiter = true, Mode = mode, Dialect = dialect }; ;
Assert.AreEqual(test,wr.ToString().Trim());
}
/// <summary>
/// 构造函数。
/// </summary>
/// <param name="logMsg">日志的文本信息</param>
/// <param name="destTable">数据存储的目标表名称</param>
/// <param name="dbDialect">数据库类型</param>
public DbMetaLog(string logMsg, string destTable, DbDialect dbDialect)
: base(logMsg)
{
this._storage = Storage.Db;
this._destTable = destTable;
this._dbDialect = dbDialect;
}
string GetDigest(string code,ScriptMode mode =ScriptMode.Create, DbDialect dialect = DbDialect.SqlServer){
var model = PersistentModel.Compile(code);
if(!model.IsValid)Assert.Fail("invalid model");
var sb = new StringBuilder();
foreach (var w in model.GetWriters(dialect,mode)){
var str = w.GetDigest();
if (!string.IsNullOrWhiteSpace(str)){
sb.AppendLine(str);
}
}
var result = sb.ToString();
Console.WriteLine(result.Replace("\"","\"\""));
return result.Trim();
}
/// <summary>
/// Возвращает реальные скрипты н
/// </summary>
/// <param name="dialect"></param>
/// <param name="position"></param>
/// <param name="mode"></param>
/// <returns></returns>
public IEnumerable<SqlScript> GetRealScripts(DbDialect dialect, ScriptPosition position, ScriptMode mode){
if (0 == SubScripts.Count){
if (DbDialect == DbDialect.Ansi || dialect == DbDialect){
if (Mode == mode){
if (Position == position){
yield return this;
}
}
}
}
else{
foreach (SqlScript subScript in SubScripts){
foreach (SqlScript rs in subScript.GetRealScripts(dialect, position, mode)){
yield return rs;
}
}
}
}
private DbConnection CreateConnection(DbDialect dbDialect, string connstr)
{
string typeName = string.Empty;
if (dbDialect == DbDialect.MySql)
{
typeName = "MySql.Data.MySqlClient.MySqlConnection,MySql.Data";
}
else if (dbDialect == DbDialect.SqlServer)
{
typeName = "System.Data.SqlClient.SqlConnection,System.Data";
}
else if (dbDialect == DbDialect.Oracle)
{
typeName = "Oracle.DataAccess.Client.OracleConnection,Oracle.DataAccess";
}
else if (dbDialect == DbDialect.Access)
{
typeName = "System.Data.OleDb.OleDbConnection,System.Data";
}
return((DbConnection)Activator.CreateInstance(Type.GetType(typeName), connstr));
}
private DbParameter CreateDbParameter(DbDialect dbDialect, string name, object value)
{
string typeName = string.Empty;
if (dbDialect == DbDialect.MySql)
{
typeName = "MySql.Data.MySqlClient.MySqlParameter,MySql.Data";
}
else if (dbDialect == DbDialect.SqlServer)
{
typeName = "System.Data.SqlClient.SqlParameter,System.Data";
}
else if (dbDialect == DbDialect.Oracle)
{
typeName = "Oracle.DataAccess.Client.OracleParameter,Oracle.DataAccess";
}
else if (dbDialect == DbDialect.Access)
{
typeName = "System.Data.OleDb.OleDbParameter,System.Data";
}
return((DbParameter)Activator.CreateInstance(Type.GetType(typeName), name, value));
}
private static void AddColumn(DbDialect dd, StringBuilder columns, bool includeOrigin, bool includeAlias, KeyValuePair <string, string> k)
{
if (includeOrigin)
{
columns.Append(dd.QuoteForColumnName(k.Key));
if (includeAlias && k.Value != null)
{
columns.Append(" AS ");
}
}
if (includeAlias)
{
if (k.Value != null)
{
columns.Append(dd.QuoteForColumnName(k.Value));
}
else if (!includeOrigin)
{
columns.Append(dd.QuoteForColumnName(k.Key));
}
}
columns.Append(",");
}
string GetDigest(string code, ScriptMode mode = ScriptMode.Create, DbDialect dialect = DbDialect.SqlServer)
{
var model = PersistentModel.Compile(code);
if (!model.IsValid)
{
Assert.Fail("invalid model");
}
var sb = new StringBuilder();
foreach (var w in model.GetWriters(dialect, mode))
{
var str = w.GetDigest();
if (!string.IsNullOrWhiteSpace(str))
{
sb.AppendLine(str);
}
}
var result = sb.ToString();
Console.WriteLine(result.Replace("\"", "\"\""));
return(result.Trim());
}
private void WriteUsualDefault(DbDialect dialect, Field field, StringBuilder sb)
{
DefaultValue def = field.DefaultSqlValue;
switch (def.DefaultValueType)
{
case DbDefaultValueType.String:
sb.Append("'" + def.Value.ToSqlString() + "'");
return;
case DbDefaultValueType.Native:
if (def.Value == null || "".Equals(def.Value))
{
sb.Append("''");
}
else if (def.Value is bool)
{
if (dialect == DbDialect.PostGres)
{
sb.Append(def.Value.ToString().ToLower());
}
else
{
sb.Append("0");
}
}
else
{
sb.Append(def.Value);
}
return;
case DbDefaultValueType.Expression:
sb.Append("(" + def.Value + ")");
return;
}
}
/// <summary>
/// Получить последовательность генерации
/// </summary>
/// <returns></returns>
public IEnumerable <SqlCommandWriter> GetWriters(DbDialect dialect, ScriptMode mode)
{
if (mode == ScriptMode.Create && !GenerationOptions.GenerateCreateScript)
{
yield break;
}
if (mode == ScriptMode.Drop && !GenerationOptions.GenerateDropScript)
{
yield break;
}
if (!GenerationOptions.IncludeDialect.HasFlag(dialect))
{
yield break;
}
var factory = new SqlCommandWriterFactory {
Mode = mode, Dialect = dialect, Model = this
};
IEnumerable <object> objset = mode == ScriptMode.Create ? GetCreateOrderedWriters(dialect) : GetDropWriters(dialect);
foreach (SqlCommandWriter w in factory.Get(objset))
{
yield return(w);
}
}
public override string ToSqlText(DataParameterCollection dpc, DbDialect dd, List <string> queryRequiredFields)
{
if (queryRequiredFields != null && dpc.FindQueryRequiedFieldOrId == false)
{
if (KV.Key == "Id" || queryRequiredFields.Contains(KV.Key))
{
dpc.FindQueryRequiedFieldOrId = true;
}
}
string dpStr = GetValueString(dpc, dd, KV);
string dkStr = dd.QuoteForColumnName(KV.Key);
switch (function)
{
case ColumnFunction.ToLower:
dkStr = string.Format("LOWER({0})", dkStr);
break;
case ColumnFunction.ToUpper:
dkStr = string.Format("UPPER({0})", dkStr);
break;
}
return(string.Format("{0} {2} {1}", dkStr, dpStr, Comp));
}
public void LateFKGenerator(DbDialect dialect, ScriptMode mode,string test){
var model = PersistentModel.Compile(CircularModel);
var cref = model["slave"]["master"];
var crefwr = new LateForeignKeyWriter(cref){NoDelimiter = true, NoComment = true, Dialect = dialect,Mode = mode};
var result = crefwr.ToString().Trim();
Console.WriteLine(result);
Assert.AreEqual(test, result);
}
public void SchemaWriter(string schemaname, ScriptMode mode, DbDialect dialect, string test){
var schema = new Schema{Name = schemaname};
var writer = new SchemaWriter(schema){Mode = mode, Dialect = dialect,NoComment=true,NoDelimiter=true};
Assert.AreEqual(test,writer.ToString().Trim());
}
private DbParameter CreateDbParameter(DbDialect dbDialect, string name, object value)
{
string typeName = string.Empty;
if (dbDialect == DbDialect.MySql)
typeName = "MySql.Data.MySqlClient.MySqlParameter,MySql.Data";
else if (dbDialect == DbDialect.SqlServer)
typeName = "System.Data.SqlClient.SqlParameter,System.Data";
else if (dbDialect == DbDialect.Oracle)
typeName = "Oracle.DataAccess.Client.OracleParameter,Oracle.DataAccess";
else if (dbDialect == DbDialect.Access)
typeName = "System.Data.OleDb.OleDbParameter,System.Data";
return (DbParameter)Activator.CreateInstance(Type.GetType(typeName), name, value);
}
/// <summary>
/// Возваращает все скрипты для указанной позиции и языка в генерации
/// </summary>
/// <param name="dialect"></param>
/// <param name="mode"></param>
/// <param name="position"></param>
/// <returns></returns>
public IEnumerable<SqlScript> GetScripts(DbDialect dialect, ScriptMode mode, ScriptPosition position){
if (GenerationOptions.Supports(SqlObjectType.Script)){
return ExtendedScripts.SelectMany(_ => _.GetRealScripts(dialect, position, mode));
}
return new SqlScript[]{};
}
/// <summary>
/// Резолюция полного типа данных для поля
/// </summary>
/// <param name="dialect"></param>
/// <returns></returns>
public SqlDataType GetSqlType(DbDialect dialect = DbDialect.Ansi){
SqlDataType result = DataType.ResolveSqlDataType(dialect);
if (0 == Size) return result;
return new SqlDataType{Name = result.Name, Size = Size, Precession = result.Precession};
}
public abstract string ToSqlText(DataParameterCollection dpc, DbDialect dd, List <string> queryRequiredFields);
public DbEngine(DbDialect dialect, string connectionString)
{
this._dialect = dialect;
this._ConnectionString = connectionString;
}
public SqlString(DbDialect dialect) : this(dialect, null)
{
}
private IEnumerable <object> GetDropWriters(DbDialect dialect)
{
return(GetCreateOrderedWriters(dialect, ScriptMode.Drop).Reverse());
}
public static IServiceCollection AddDbContext(this IServiceCollection services, DbDialect dbDialect, string connectionString)
{
services.AddDbContextPool <BorrowBuddyContext>(options => {
options.UseLazyLoadingProxies();
switch (dbDialect)
{
case DbDialect.MySql: {
options.UseMySql(connectionString);
break;
}
case DbDialect.SqlServer: {
options.UseSqlServer(connectionString);
break;
}
}
});
return(services);
}
/// <summary>
/// 创建常用的Insert和Update操作的SQL语句
/// </summary>
/// <param name="dialect">Sql方言</param>
/// <param name="TableName">表名、对象名</param>
/// <param name="paramOperation">操作描述，Insert一般参数为"I"，Update需紧跟匹配参数形如：U@[ID=8888]。</param>
/// <param name="Columns">列集合</param>
/// <param name="dRow">填充数据列的相应数据行</param>
/// <returns>相关SQL语句</returns>
public static string BuildSqlIU(DbDialect dialect, string TableName, string paramOperation, string[] Columns, DataRow dRow)
{
return(BuildSqlIU(dialect, TableName, paramOperation, Columns, dRow.ItemArray));
}
/// <summary>
/// 获取特定值在Sql语句中的文本表现形式
/// </summary>
/// <param name="dialect">Sql方言</param>
/// <param name="objColVal">相关值集合</param>
/// <returns>相关SQL列值的集合</returns>
public static string[] GetSqlItemValue(DbDialect dialect, object[] objColVal)
{
string[] sqlVal = new string[objColVal.Length];
string colValue = string.Empty;
for (int i = 0; i < sqlVal.Length; i++)
{
if (objColVal[i].GetType() == typeof(System.String))
{
colValue = objColVal[i].ToString();
// 字符
// 自定义函数、查询，则直接赋值。 [N'getdate(),N'(select @@IDENTIDY)]
if (colValue.StartsWith("N'"))
{
sqlVal[i] = colValue.Substring(2);
}
else
{
sqlVal[i] = String.Concat("'", colValue.Replace("'", "''"), "'");
}
}
else if (objColVal[i].GetType() == typeof(System.Guid))
{
sqlVal[i] = String.Concat("'", objColVal[i], "'");
}
else if (objColVal[i].GetType() == typeof(System.Boolean))
{
// 布尔值
if (dialect == DbDialect.MsSqlServer)
{
sqlVal[i] = Convert.ToBoolean(objColVal[i]) ? "1" : "0";
}
else if (dialect == DbDialect.MsAccess)
{
sqlVal[i] = Convert.ToBoolean(objColVal[i]) ? "True" : "False";
}
else
{
sqlVal[i] = objColVal[i].ToString();
}
}
else if (objColVal[i].GetType() == typeof(System.DateTime))
{
// 时间格式
if (dialect == DbDialect.MsSqlServer)
{
sqlVal[i] = String.Concat("'", objColVal[i], "'");
}
else if (dialect == DbDialect.MsAccess)
{
sqlVal[i] = String.Concat("#", objColVal[i], "#");
}
else
{
sqlVal[i] = objColVal[i].ToString();
}
}
else
{
sqlVal[i] = objColVal[i].ToString();
}
}
return(sqlVal);
}
/// <summary>
/// </summary>
/// <param name="dialect"></param>
/// <returns></returns>
public bool IsSupportPartitioning(DbDialect dialect){
if (!GenerationOptions.GeneratePartitions) return false;
return dialect == DbDialect.SqlServer;
}
/// <summary>
/// </summary>
/// <param name="dialect"></param>
/// <param name="mode"></param>
/// <returns></returns>
public string GetDigest(DbDialect dialect, ScriptMode mode){
var sb = new StringBuilder();
foreach (SqlCommandWriter sw in GetWriters(dialect, mode)){
if (null == sw) continue;
string subresult = sw.GetDigest();
if (!string.IsNullOrWhiteSpace(subresult)){
sb.AppendLine(subresult);
}
}
return sb.ToString();
}
public OracleDriver(DbDialect dialectClass, string name, string connectionString, string dbProviderFactoryName, AutoScheme autoScheme)
: base(dialectClass, name, connectionString, dbProviderFactoryName, autoScheme)
{
}
private IEnumerable<object> GetDropWriters(DbDialect dialect){
return GetCreateOrderedWriters(dialect, ScriptMode.Drop).Reverse();
}
internal string GetColumns(DbDialect dd)
{
return(GetColumns(dd, true, true));
}
private IEnumerable<object> GetCreateOrderedWriters(DbDialect dialect, ScriptMode mode = ScriptMode.Create){
foreach (SqlScript script in GetScripts(dialect, mode, ScriptPosition.Before)){
yield return script;
}
foreach (FileGroup fg in DatabaseSqlObjects.OfType<FileGroup>()){
yield return fg;
}
foreach (Schema schema in DatabaseSqlObjects.OfType<Schema>()){
yield return schema;
}
foreach (Sequence sequence in Tables.SelectMany(_ => _.SqlObjects.OfType<Sequence>())){
yield return sequence;
}
foreach (PartitionDefinition part in Tables.SelectMany(_ => _.SqlObjects.OfType<PartitionDefinition>())){
yield return part;
}
foreach (SqlScript script in GetScripts(dialect, mode, ScriptPosition.BeforeTables)){
yield return script;
}
foreach (PersistentClass cls in Tables){
yield return cls;
}
foreach (Field circularRef in Tables.SelectMany(_ => _.Fields.Values.Where(__ => __.GetIsCircular()))){
yield return circularRef;
}
foreach (SqlScript script in GetScripts(dialect, mode, ScriptPosition.AfterTables)){
yield return script;
}
foreach (SqlFunction function in Tables.SelectMany(_ => _.SqlObjects.OfType<SqlFunction>())){
if(function.Dialect==DbDialect.None||function.Dialect==dialect||function.Dialect==DbDialect.Ansi)
yield return function;
}
foreach (SqlView view in Tables.SelectMany(_ => _.SqlObjects.OfType<SqlView>())){
if (view.Dialect == DbDialect.None || view.Dialect == dialect || view.Dialect == DbDialect.Ansi)
yield return view;
}
foreach (SqlTrigger trigger in Tables.SelectMany(_ => _.SqlObjects.OfType<SqlTrigger>())){
if (trigger.Dialect == DbDialect.None || trigger.Dialect == dialect || trigger.Dialect == DbDialect.Ansi)
yield return trigger;
}
foreach (SqlScript script in Tables.SelectMany(_ => _.SqlObjects.OfType<SqlScript>()))
{
if (script.Dialect == DbDialect.None || script.Dialect == dialect || script.Dialect == DbDialect.Ansi)
yield return script;
}
foreach (SqlScript script in GetScripts(dialect, mode, ScriptPosition.After)){
yield return script;
}
}
public LibraryContextProvider(IHttpContextAccessor contextAccessor, IMapper mapper,
LibraryBusinessProvider businessProvider, LibraryContext dbContext, DbDialect dbDialect)
: base(contextAccessor, mapper, businessProvider, dbContext, dbDialect)
{
}
private DbConnection CreateConnection(DbDialect dbDialect, string connstr)
{
string typeName = string.Empty;
if (dbDialect == DbDialect.MySql)
typeName = "MySql.Data.MySqlClient.MySqlConnection,MySql.Data";
else if (dbDialect == DbDialect.SqlServer)
typeName = "System.Data.SqlClient.SqlConnection,System.Data";
else if (dbDialect == DbDialect.Oracle)
typeName = "Oracle.DataAccess.Client.OracleConnection,Oracle.DataAccess";
else if (dbDialect == DbDialect.Access)
typeName = "System.Data.OleDb.OleDbConnection,System.Data";
return (DbConnection)Activator.CreateInstance(Type.GetType(typeName), connstr);
}
/// <summary>
/// Получить последовательность генерации
/// </summary>
/// <returns></returns>
public IEnumerable<SqlCommandWriter> GetWriters(DbDialect dialect, ScriptMode mode){
if (mode == ScriptMode.Create && !GenerationOptions.GenerateCreateScript) yield break;
if (mode == ScriptMode.Drop && !GenerationOptions.GenerateDropScript) yield break;
if (!GenerationOptions.IncludeDialect.HasFlag(dialect)) yield break;
var factory = new SqlCommandWriterFactory{Mode = mode, Dialect = dialect, Model = this};
IEnumerable<object> objset = mode == ScriptMode.Create ? GetCreateOrderedWriters(dialect) : GetDropWriters(dialect);
foreach (SqlCommandWriter w in factory.Get(objset)){
yield return w;
}
}
protected abstract SqlStatement ToSqlStatement(DbDialect dd, List <string> queryRequiredFields);
private IEnumerable <object> GetCreateOrderedWriters(DbDialect dialect, ScriptMode mode = ScriptMode.Create)
{
foreach (SqlScript script in GetScripts(dialect, mode, ScriptPosition.Before))
{
yield return(script);
}
foreach (FileGroup fg in DatabaseSqlObjects.OfType <FileGroup>())
{
yield return(fg);
}
foreach (Schema schema in DatabaseSqlObjects.OfType <Schema>())
{
yield return(schema);
}
foreach (Sequence sequence in Tables.SelectMany(_ => _.SqlObjects.OfType <Sequence>()))
{
yield return(sequence);
}
foreach (PartitionDefinition part in Tables.SelectMany(_ => _.SqlObjects.OfType <PartitionDefinition>()))
{
yield return(part);
}
foreach (SqlScript script in GetScripts(dialect, mode, ScriptPosition.BeforeTables))
{
yield return(script);
}
foreach (PersistentClass cls in Tables)
{
yield return(cls);
}
foreach (Field circularRef in Tables.SelectMany(_ => _.Fields.Values.Where(__ => __.GetIsCircular())))
{
yield return(circularRef);
}
foreach (SqlScript script in GetScripts(dialect, mode, ScriptPosition.AfterTables))
{
yield return(script);
}
foreach (SqlFunction function in Tables.SelectMany(_ => _.SqlObjects.OfType <SqlFunction>()))
{
if (function.Dialect == DbDialect.None || function.Dialect == dialect || function.Dialect == DbDialect.Ansi)
{
yield return(function);
}
}
foreach (SqlView view in Tables.SelectMany(_ => _.SqlObjects.OfType <SqlView>()))
{
if (view.Dialect == DbDialect.None || view.Dialect == dialect || view.Dialect == DbDialect.Ansi)
{
yield return(view);
}
}
foreach (SqlTrigger trigger in Tables.SelectMany(_ => _.SqlObjects.OfType <SqlTrigger>()))
{
if (trigger.Dialect == DbDialect.None || trigger.Dialect == dialect || trigger.Dialect == DbDialect.Ansi)
{
yield return(trigger);
}
}
foreach (SqlScript script in Tables.SelectMany(_ => _.SqlObjects.OfType <SqlScript>()))
{
if (script.Dialect == DbDialect.None || script.Dialect == dialect || script.Dialect == DbDialect.Ansi)
{
yield return(script);
}
}
foreach (SqlScript script in GetScripts(dialect, mode, ScriptPosition.After))
{
yield return(script);
}
}
public DbSession(string connectionString, DbDialect dbType)
{
_connectionString = connectionString;
_dbType = dbType;
}
