/// <summary>
/// Deletes the index.
/// </summary>
/// <param name="deleteIndexSql">The delete index sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildDeleteIndex(DeleteIndexSql deleteIndexSql)
{
var r = new ParmsSqlResult();
r.SQL = $"drop index {deleteIndexSql.IndexName} on {deleteIndexSql.TableName}";
return(r);
}
/// <summary>
/// Renames the index sql.
/// </summary>
/// <param name="renameIndexSql">The rename index sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult RenameIndexSql(RenameIndexSql renameIndexSql)
{
var r = new ParmsSqlResult();
r.SQL = $"ALTER TABLE {renameIndexSql.TableName} RENAME INDEX {renameIndexSql.OldIndexName} TO {renameIndexSql.NewIndexName}";
return(r);
}
/// <summary>
/// Creates the index.
/// </summary>
/// <param name="createIndexSql">The create index sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildCreateIndex(CreateIndexSql createIndexSql)
{
var r = new ParmsSqlResult();
r.SQL = $"create {createIndexSql.SqlIndex} {createIndexSql.IndexName} on {createIndexSql.TableName}({string.Join(",", createIndexSql.ColumnNames)})";
return(r);
}
/// <summary>
/// Builds the column names.
/// </summary>
/// <param name="columsNamesSql">The colums names sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildColumnNames(ColumsNamesSql columsNamesSql)
{
var r = new ParmsSqlResult();
r.SQL = $"select column_name from information_schema.columns where table_schema='{columsNamesSql.Database}' and table_name='{columsNamesSql.TableName}'";
return(r);
}
/// <summary>
/// Builds the all table name.
/// </summary>
/// <param name="allTableNamesSql">The all table names sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildAllTableName(AllTableNamesSql allTableNamesSql)
{
var result = new ParmsSqlResult();
result.SQL = $"select table_name from information_schema.tables where table_schema='{allTableNamesSql.Database}'";
return(result);
}
/// <summary>
/// Builds the all database.
/// </summary>
/// <param name="allDatabaseSql">The all database sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildAllDatabase(AllDatabaseSql allDatabaseSql)
{
var result = new ParmsSqlResult();
result.SQL = "select schema_name from information_schema.schemata";
return(result);
}
/// <summary>
/// Builders the string sql.
/// </summary>
/// <param name="stringsql">The stringsql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuilderStringSql(StringSql stringsql)
{
var sqlResult = new ParmsSqlResult();
sqlResult.SQL = stringsql.ToString();
return(sqlResult);
}
/// <summary>
/// Builds the delete database.
/// </summary>
/// <param name="deleteDatabaseSql">The delete database sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildDeleteDatabase(DeleteDatabaseSql deleteDatabaseSql)
{
var result = new ParmsSqlResult();
result.SQL = $"drop database {deleteDatabaseSql.Database}";
return(result);
}
/// <summary>
/// Builds the create database.
/// </summary>
/// <param name="createDatabaseSql">The create database sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildCreateDatabase(CreateDatabaseSql createDatabaseSql)
{
var result = new ParmsSqlResult();
result.SQL = $"create database {createDatabaseSql.Database}";
return(result);
}
/// <summary>
/// Updates the table name.
/// </summary>
/// <param name="updateTableNameSql">The update table name sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildUpdateTableName(UpdateTableNameSql updateTableNameSql)
{
var r = new ParmsSqlResult();
r.SQL = $"alter table {updateTableNameSql.OldName} rename {updateTableNameSql.NewName}";
return(r);
}
/// <summary>
/// Builds the count for database.
/// </summary>
/// <param name="countDatabaseSql">The count database sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildCountForDatabase(CountDatabaseSql countDatabaseSql)
{
var result = new ParmsSqlResult();
result.SQL = $"SELECT count(1) FROM information_schema.SCHEMATA where SCHEMA_NAME='{countDatabaseSql.Database}'";
return(result);
}
/// <summary>
/// Builds the colums count.
/// </summary>
/// <param name="countColumsSql">The count colums sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildColumnsCount(CountColumsSql countColumsSql)
{
var r = new ParmsSqlResult();
r.SQL = $"select COUNT(1) from information_schema.columns WHERE table_schema='{countColumsSql.Database}' and table_name = '{countColumsSql.TableName}' and column_name = '{countColumsSql.ColumnsName}'";
return(r);
}
/// <summary>
/// Gets the table count.
/// </summary>
/// <param name="countTableSql">The count table sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildTableCount(CountTableSql countTableSql)
{
var r = new ParmsSqlResult();
r.SQL = $"SELECT count(1) FROM information_schema.TABLES WHERE table_name ='{countTableSql.TableName}'";
return(r);
}
/// <summary>
/// Builds the delete column.
/// </summary>
/// <param name="deleteColumsSql">The delete colums sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildDeleteColumn(DeleteColumsSql deleteColumsSql)
{
var r = new ParmsSqlResult();
r.SQL = $"alter table {deleteColumsSql.TableName} drop column {deleteColumsSql.ColumsName}";
return(r);
}
/// <summary>
/// Builds the update sql.
/// </summary>
/// <param name="updateSql">The update sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildUpdateSql(UpdateSql updateSql)
{
var query = updateSql.WhereQuery;
var result = new ParmsSqlResult();
var whereSqlResult = query != null?BuildQuery(query) : new ParmsSqlResult();
var tableName = TableUtlis.GetTableName(updateSql.Table);
var doc = updateSql.UpdateObj.As <IRecord>();
var sql = $"update {_dbProvider.FormatFieldName(tableName)} set ";
var fieldList = new List <string>();
var parms = new List <IDbDataParameter>(whereSqlResult.Parameters);
foreach (var item in doc.Keys.ToList())
{
var fieldStr = string.Empty;
if (_dbOption.IsUseParamers)
{
var param = _dbProvider.GetDbDataParameter();
param.ParameterName = $"{_dbProvider.GetParamsSymbol()}{item}";
param.Value = doc[item];
fieldStr = $"{_dbProvider.FormatFieldName(item)}={param.ParameterName}";
parms.Add(param);
}
else
{
fieldStr = $"{_dbProvider.FormatFieldName(item)}={_dbProvider.GetObjectType(doc[item])}";
}
fieldList.Add(fieldStr);
}
sql = $"{sql}{fieldList.Join(",")} {whereSqlResult.SQL}";
result.SQL = sql;
result.Parameters.AddRange(parms);
return(result);
}
/// <summary>
/// Deletes the table.
/// </summary>
/// <param name="deleteTableSql">The delete table sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildDeleteTable(DeleteTableSql deleteTableSql)
{
var r = new ParmsSqlResult();
r.SQL = $"drop table {deleteTableSql.TableName}";
return(r);
}
/// <summary>
/// Builds the query.
/// </summary>
/// <param name="query">The query.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildQuery(IQuery query)
{
var result = new ParmsSqlResult();
var sql = BuildSubQuery(query);
var strBuild = new StringBuilder(sql.SQL);
RenameTableType(strBuild, sql.TableTypeDic, sql.TempTable, sql.GroupDic);
RenameTempTableType(strBuild, sql.TempTable);
result.SQL = strBuild.ToString();
result.Parameters.AddRange(sql.Params);
return(result);
}
/// <summary>
/// Builds the delete sql.
/// </summary>
/// <param name="deleteSql">The delete sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildDeleteSql(DeleteSql deleteSql)
{
var query = deleteSql.WhereQuery;
var result = new ParmsSqlResult();
var whereSqlResult = query != null?BuildQuery(query) : new ParmsSqlResult();
var tableName = TableUtlis.GetTableName(deleteSql.Table);
var sql = $"delete from {_dbProvider.FormatFieldName(tableName)} {whereSqlResult.SQL}";
result.SQL = sql;
result.Parameters.AddRange(whereSqlResult.Parameters);
return(result);
}
/// <summary>
/// Builds the rebane columns name.
/// </summary>
/// <param name="renameColumnSql">The rename column sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildRenameColumnsName(RenameColumnSql renameColumnSql)
{
var r = new ParmsSqlResult();
var sqlType = _typeMap.GetSqlType(renameColumnSql.TypeCode.ToString());
if (renameColumnSql.Lentgh < 0)
{
throw new ArgumentException("长度不能为小于0");
}
var lengthStr = GetLengthStr(renameColumnSql.Lentgh, renameColumnSql.TypeCode);
r.SQL = $"alter table {renameColumnSql.TableName} change column {renameColumnSql.OldName} {renameColumnSql.NewName} {sqlType}{lengthStr}";
return(r);
}
/// <summary>
/// Builds the insert sql.
/// </summary>
/// <param name="insertSql">The insert sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildInsertSql(InsertSql insertSql)
{
var result = new ParmsSqlResult();
var doc = EntityUtil.AsTableRecord(insertSql.Table);
var tableName = TableUtlis.GetTableName(insertSql.Table.GetType());
var sql = $"insert into {_dbProvider.FormatFieldName(tableName)}";
var pams = new List <IDbDataParameter>();
var fields = new List <string>();
var valueList = new List <string>();
foreach (var item in doc.Keys.ToList())
{
if (_dbOption.IsUseParamers)
{
var param = _dbProvider.GetDbDataParameter();
param.ParameterName = Guid.NewGuid().ToString();
param.Value = doc[item];
if (param.Value != null)
{
fields.Add($"{_dbProvider.FormatFieldName(item)}");
pams.Add(param);
}
}
else
{
var t_value = _dbProvider.GetObjectType(doc[item]);
if (t_value != null)
{
valueList.Add(t_value);
fields.Add($"{_dbProvider.FormatFieldName(item)}");
}
}
}
if (_dbOption.IsUseParamers)
{
sql = $"{sql}({fields.Join(",")}) values({pams.Join(",", t => t.ParameterName)})";
}
else
{
sql = $"{sql}({fields.Join(",")}) values({valueList.Join(",")})";
}
result.SQL = sql;
result.Parameters.AddRange(pams);
return(result);
}
/// <summary>
/// Builds the.
/// </summary>
/// <param name="sqls"></param>
/// <returns>A ParmsSqlResult.</returns>
public ISqlResult Build(params ISql[] sqls)
{
var result = new ParmsSqlResult();
var sqlList = new List <string>();
var sqlParam = new SqlParam();
foreach (var item in sqls)
{
var r = BuildSqlResult(item);
sqlList.Add(r.SQL);
result.Parameters.AddRange(r.Parameters);
}
result.SQL = string.Join(';', sqlList);
result.Parameters.AddRange(sqlParam.Params);
var t_sql = new StringBuilder(result.SQL);
RenameParameter(t_sql, result.Parameters);
result.SQL = t_sql.ToString().Trim();
return(result);
}
/// <summary>
/// Builds the add colums.
/// </summary>
/// <param name="addColumnsSql">The add columns sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildAddColumns(AddColumnsSql addColumnsSql)
{
var r = new ParmsSqlResult();
var sqlType = _typeMap.GetSqlType(addColumnsSql.TypeCode.ToString());
var sql = $"alter table {addColumnsSql.TableName} add column {addColumnsSql.ColumnName} {sqlType}";
if (addColumnsSql.Lentgh < 0)
{
throw new ArgumentException("长度不能为小于0");
}
var lengthStr = GetLengthStr(addColumnsSql.Lentgh, addColumnsSql.TypeCode);
sql = $"{sql}{lengthStr}";
if (addColumnsSql.IsNotNull)
{
sql = $"{sql} not null";
}
r.SQL = sql;
return(r);
}
/// <summary>
/// Creates the table.
/// </summary>
/// <param name="createTableSql">The create table sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildCreateTable(CreateTableSql createTableSql)
{
var type = createTableSql.TableType;
var r = new ParmsSqlResult();
var typeInfo = type.GetTypeInfo();
var props = typeInfo.GetRuntimeProperties();
var columSqls = new List <string>();
var tableName = TableUtlis.GetTableName(type);
var keys = new List <string>();
foreach (var item in props)
{
var pType = item.PropertyType.Name;
var isNullType = false;
if (item.PropertyType.Name == typeof(Nullable <>).Name)
{
isNullType = true;
pType = item.PropertyType.GenericTypeArguments[0].Name;
}
var aType = _typeMap.GetSqlType(pType);
var columSql = $"{item.Name} {aType}";
var isSequence = item.GetCustomAttribute(typeof(SequenceAttribute), true) != null;
if (isSequence)
{
columSql = $"SequenceId int AUTO_INCREMENT";
columSqls.Add(columSql);
continue;
}
else if (item.Name == "Id")
{
columSql = $"Id nvarchar(36)";
columSqls.Add(columSql);
continue;
}
var igAttribute = item.GetCustomAttribute(typeof(IngoreAttribute), true);
if (igAttribute != null)
{
continue;
}
if (item.GetCustomAttribute(typeof(ColumnAttribute), true) is ColumnAttribute columnAttribute)
{
if (string.IsNullOrWhiteSpace(columnAttribute.Name))
{
columSql = columSql.Replace(item.Name, columnAttribute.Name);
}
if (pType == nameof(TypeCode.Double) || pType == nameof(TypeCode.Single))
{
var length = columnAttribute.Length == -1 ? 15 : columnAttribute.Length;
columSql = $"{columSql}({length},6)";
}
else if (pType == nameof(TypeCode.DateTime) || pType == nameof(TypeCode.Byte))
{
columSql = $"{columSql}";
}
else if (pType == typeof(Guid).Name)
{
columSql = $"{columSql}";
}
else
{
var length = columnAttribute.Length == -1 ? 255 : columnAttribute.Length;
columSql = $"{columSql}({length})";
}
}
else
{
if (pType == nameof(TypeCode.Double) || pType == nameof(TypeCode.Single))
{
columSql = $"{columSql}(15,6)";
}
else if (pType == nameof(TypeCode.DateTime) || pType == nameof(TypeCode.Byte) || pType == nameof(TypeCode.Int32))
{
columSql = $"{columSql}";
}
else if (pType == typeof(Guid).Name)
{
columSql = $"{columSql}(36)";
}
else
{
columSql = $"{columSql}({255})";
}
}
if (item.GetCustomAttribute(typeof(KeyAttribute), true) is KeyAttribute _)
{
keys.Add(item.Name);
}
var isNotNullAttribute = item.GetCustomAttribute(typeof(NotNullAttribute), true);
if (item.PropertyType == typeof(string) && isNotNullAttribute == null)//如果是string类型默认是可null类型
{
isNullType = true;
}
else if (isNotNullAttribute != null && isNullType)
{
throw new NotSupportedException("可空类型不能被NotNull标记");
}
else if (!isNullType)
{
columSql = $"{columSql} not null";
}
columSqls.Add(columSql);
}
if (keys.Count == 0)
{
columSqls.Add($"PRIMARY KEY ( Id )");
}
else if (keys.Count == 1)
{
columSqls.Add($"PRIMARY KEY ( {keys[0]} )");
}
else
{
throw new NotSupportedException("暂时不支持联合主键，请使用联合唯一索引代替");
}
if (string.IsNullOrWhiteSpace(tableName))
{
throw new Exception("当前TabelName的值为null 无法创建表");
}
var sql = $@"create table {tableName}({string.Join(",", columSqls)})";
r.SQL = sql;
return(r);
}
