public void CreateEasyJobTable(EntityDB.IDatabaseService db)
{
bool exists = true;
try
{
db.ExecSqlString("select * from __wayeasyjob");
}
catch (Exception ex)
{
exists = false;
}
if (!exists)
{
db.ExecSqlString("CREATE TABLE [__wayeasyjob](contentConfig TEXT NOT NULL)");
var dbconfig = new DataBaseConfig();
try
{
dbconfig.LastUpdatedID = Convert.ToInt32(db.ExecSqlString("select lastID from __EasyJob"));
}
catch
{
}
db.ExecSqlString("insert into __wayeasyjob (contentConfig) values (@p0)", dbconfig.ToJsonString());
}
//try
//{
// db.ExecSqlString("CREATE TABLE [] ([lastID] integer NOT NULL)");
// db.ExecSqlString("insert into (lastID) values (0)");
//}
//catch
//{
//}
}
List <string> checkIfIdxChanged(EntityDB.IDatabaseService database, string tablename, List <IndexInfo> indexInfos)
{
tablename = tablename.ToLower();
List <string> need2Dels = new List <string>();
using (var dt = database.SelectTable("select * from sqlite_master WHERE type='index' and tbl_name='" + tablename + "'"))
{
foreach (var drow in dt.Rows)
{
if (drow["sql"] == null)
{
continue;
}
bool isUnique = drow["sql"].ToString().Contains(" UNIQUE ");
string name = drow["name"].ToString().ToLower();
var exitsItem = indexInfos.FirstOrDefault(m => tablename + "_ej_" + m.ColumnNames.OrderBy(p => p).ToArray().ToSplitString("_").ToLower() == name && m.IsUnique == isUnique);
if (exitsItem == null)
{
need2Dels.Add(name);
}
else
{
indexInfos.Remove(exitsItem);
}
}
}
return(need2Dels);
}
void createIndex(EntityDB.IDatabaseService database, string table, IndexInfo indexinfo)
{
table = table.ToLower();
//alter table table_name add unique key `new_uk_name` (`col1`,`col2`);
var columns = indexinfo.ColumnNames.OrderBy(m => m).Select(m => m.ToLower()).ToArray();
string columnsStr = "";
string name = table + "_ej_" + columns.ToSplitString("_");
for (int i = 0; i < columns.Length; i++)
{
columnsStr += "`" + columns[i] + "`";
if (i < columns.Length - 1)
{
columnsStr += ",";
}
}
string type = "";
//if (indexinfo.IsClustered)
// throw new Exception("MySql不支持定义聚集索引");
if (indexinfo.IsUnique || indexinfo.IsClustered)
{
type += "unique ";
}
database.ExecSqlString(string.Format("alter table `{0}` add {3} key `{1}` ({2})", table, name, columnsStr, type));
}
void deletecolumn(EntityDB.IDatabaseService database, string table, string column)
{
table = table.ToLower();
column = column.ToLower();
database.ExecSqlString(string.Format("alter table \"{0}\" drop column \"{1}\"", table, column));
}
List <string> checkIfIdxChanged(EntityDB.IDatabaseService database, string tablename, List <IndexInfo> indexInfos)
{
tablename = tablename.ToLower();
var tableindexes = new Impls.PostgreSQL.PostgreSQLDatabaseService().GetCurrentIndexes(database, tablename.ToLower());
List <string> needToDels = new List <string>();
foreach (var dbindex in tableindexes)
{
string longname = dbindex.ColumnNames.ToArray().ToSplitString(",");
if (indexInfos.Any(m => m.ColumnNames.OrderBy(n => n).ToArray().ToSplitString(",").ToLower() == longname.ToLower()) == false)
{
needToDels.Add(dbindex.Name.ToLower());
}
else
{
var existIndexes = indexInfos.Where(m => m.IsUnique == dbindex.IsUnique && m.IsClustered == dbindex.IsClustered && m.ColumnNames.OrderBy(n => n).ToArray().ToSplitString(",").ToLower() == longname.ToLower()).ToArray();
if (existIndexes.Length == 0)
{
needToDels.Add(dbindex.Name.ToLower());
}
else
{
//现有的索引是一样的，所以不用创建了
foreach (var indexinfo in existIndexes)
{
indexInfos.Remove(indexinfo);
}
}
}
}
return(needToDels);
}
/// <summary>
///
/// </summary>
/// <param name="database"></param>
/// <param name="table"></param>
void dropTableIndex(EntityDB.IDatabaseService database, string table, IndexInfo[] dontDelIndexes)
{
table = table.ToLower();
List <string> toDelIndexes = new List <string>();
using (var sp_helpResult = database.SelectDataSet("sp_help [" + table + "]"))
{
foreach (var dtable in sp_helpResult.Tables)
{
if (dtable.Columns.Any(m => m.ColumnName == "index_keys"))
{
foreach (WayDataRow drow in dtable.Rows)
{
string existColumnString = drow["index_keys"].ToString();
string indexName = drow["index_name"].ToString();
string index_description = drow["index_description"].ToString();
if (index_description.Contains("primary key") == false)
{
if (dontDelIndexes.Count(m => string.Equals(m.Name, indexName, StringComparison.CurrentCultureIgnoreCase)) == 0)
{
toDelIndexes.Add(indexName);
}
}
}
break;
}
}
}
foreach (string indexName in toDelIndexes)
{
database.ExecSqlString("drop index " + indexName + " on [" + table + "]");
}
}
public void CreateEasyJobTable(EntityDB.IDatabaseService db)
{
bool exists = true;
try
{
db.ExecSqlString("select * from __wayeasyjob");
}
catch
{
exists = false;
}
if (!exists)
{
db.ExecSqlString("create table `__wayeasyjob` (contentConfig varchar(1000) not null)");
db.ExecSqlString("insert into __wayeasyjob (contentConfig) values (@p0)", new DataBaseConfig().ToJsonString());
}
//try
//{
// db.ExecSqlString("create table `` (lastID int(11) not null)");
// db.ExecSqlString("insert into `` (lastID) values (0)");
//}
//catch
//{
//}
}
public void CreateEasyJobTable(EntityDB.IDatabaseService db)
{
bool exists = true;
try
{
db.ExecSqlString("select * from __wayeasyjob");
}
catch
{
exists = false;
}
if (!exists)
{
db.ExecSqlString("CREATE TABLE [__wayeasyjob](contentConfig varchar(1000) NOT NULL)");
var dbconfig = new DataBaseConfig();
try
{
dbconfig.LastUpdatedID = Convert.ToInt32(db.ExecSqlString("select lastID from __EasyJob"));
}
catch
{
}
db.ExecSqlString("insert into __wayeasyjob (contentConfig) values (@p0)", dbconfig.ToJsonString());
}
}
public void DeleteTable(EntityDB.IDatabaseService database, string tableName)
{
tableName = tableName.ToLower();
string sql = "if exists(select id from sysobjects where name='" + tableName + "' and xtype='U') drop table [" + tableName + "]";
database.ExecSqlString(sql);
}
public void CreateEasyJobTable(EntityDB.IDatabaseService db)
{
bool exists = Convert.ToInt32(db.ExecSqlString("select count(*) from pg_tables where tablename='__wayeasyjob'")) > 0;
if (!exists)
{
db.ExecSqlString("create table __wayeasyjob (contentConfig VARCHAR(1000) NOT NULL)");
db.ExecSqlString("insert into __wayeasyjob (contentConfig) values (@p0)", new DataBaseConfig().ToJsonString());
}
}
void deleteAllIndex(EntityDB.IDatabaseService database, string tableName)
{
tableName = tableName.ToLower();
using (var dtable = database.SelectTable("select * from sqlite_master where type='index' and tbl_name='" + tableName + "' "))
{
foreach (var drow in dtable.Rows)
{
database.ExecSqlString("DROP INDEX IF EXISTS [" + drow["name"] + "]");
}
}
}
public override void Invoke(EntityDB.IDatabaseService invokingDB)
{
if (_getColumnsFunc == null)
{
throw new Exception("getColumnsFunc is null");
}
ITableDesignService service = DBHelper.CreateTableDesignService(invokingDB.DBContext.DatabaseType);
service.ChangeTable(invokingDB, OldTableName, NewTableName, newColumns, changedColumns, deletedColumns, _getColumnsFunc, IDXConfigs);
}
public void ChangeTable(EntityDB.IDatabaseService database, string oldTableName, string newTableName, EJ.DBColumn[] addColumns, EJ.DBColumn[] changed_columns, EJ.DBColumn[] deletedColumns, Func <List <EJ.DBColumn> > getColumnsFunc, IndexInfo[] _indexInfos)
{
List <EJ.DBColumn> changedColumns = new List <EJ.DBColumn>(changed_columns);
oldTableName = oldTableName.ToLower();
newTableName = newTableName.ToLower();
List <IndexInfo> indexInfos = new List <IndexInfo>(_indexInfos);
//先判断表明是否更改
if (oldTableName != newTableName)
{
//更改表名
database.ExecSqlString($"alter table \"{oldTableName}\" RENAME TO \"{newTableName}\"");
}
var needToDels = checkIfIdxChanged(database, newTableName, indexInfos);
foreach (var column in deletedColumns)
{
ChangeColumnHandler.HandleDelete(database, newTableName, column);
}
foreach (string delIndexName in needToDels)
{
dropTableIndex(database, newTableName, delIndexName);
}
//将取消主键的列放前面处理
if (true)
{
var column = changedColumns.FirstOrDefault(m => m.BackupChangedProperties["IsPKID"] != null && (bool)m.BackupChangedProperties["IsPKID"].OriginalValue == true);
if (column != null && column.IsPKID == false)
{
changedColumns.Remove(column);
changedColumns.Insert(0, column);
}
}
foreach (var column in changedColumns)
{
ChangeColumnHandler.HandleChange(database, newTableName, column);
}
foreach (var column in addColumns)
{
ChangeColumnHandler.HandleNewColumn(database, newTableName, column);
}
foreach (var config in indexInfos)
{
createIndex(database, newTableName, config);
}
}
public static void SetLastUpdateID(object actionid, string databaseGuid, EntityDB.IDatabaseService db)
{
if (string.IsNullOrEmpty(databaseGuid))
{
throw new Exception("Database Guid can not be empty");
}
var dbconfig = db.ExecSqlString("select contentConfig from __wayeasyjob").ToString().ToJsonObject <DataBaseConfig>();
dbconfig.LastUpdatedID = Convert.ToInt32(actionid);
dbconfig.DatabaseGuid = databaseGuid;
var data = new EntityDB.CustomDataItem("__wayeasyjob", null, null);
data.SetValue("contentConfig", dbconfig.ToJsonString());
db.Update(data);
}
List <string> checkIfIdxChanged(EntityDB.IDatabaseService database, string tablename, List <IndexInfo> indexInfos)
{
tablename = tablename.ToLower();
List <string> needToDels = new List <string>();
string dbname = null;
var dbnameMatch = System.Text.RegularExpressions.Regex.Match(database.ConnectionString, @"database=(?<dname>(\w)+)", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
if (dbnameMatch == null)
{
throw new Exception("连接字符串必须采用以下形式server=localhost;User Id=root;password=123456;Database=testdb");
}
dbname = dbnameMatch.Groups["dname"].Value;
var db = EntityDB.DBContext.CreateDatabaseService(database.ConnectionString.Replace(dbnameMatch.Value, "database=INFORMATION_SCHEMA"), EntityDB.DatabaseType.MySql);
{
var tableid = db.ExecSqlString("select TABLE_ID from INNODB_SYS_TABLES where Name='" + dbname + "/" + tablename + "'");
if (tableid != null)
{
using (var INNODB_SYS_INDEXES_table = db.SelectTable("select * from INNODB_SYS_INDEXES where TABLE_ID=" + tableid + " and type<>3"))
{
foreach (var drow in INNODB_SYS_INDEXES_table.Rows)
{
string indexName = drow["NAME"].ToString().ToLower();
bool isUnique = Convert.ToInt32(drow["TYPE"]) == 2;
var findExistItem = indexInfos.FirstOrDefault(m => tablename.ToLower() + "_ej_" + m.ColumnNames.OrderBy(p => p).ToArray().ToSplitString("_").ToLower() == indexName &&
m.IsUnique == isUnique);
if (findExistItem == null)
{
if (indexName.ToLower() != "GEN_CLUST_INDEX".ToLower())//GEN_CLUST_INDEX好像是表示没有主键的意思
{
needToDels.Add(indexName);
}
}
else
{
indexInfos.Remove(findExistItem);
}
}
}
}
}
return(needToDels);
}
void deletecolumn(EntityDB.IDatabaseService database, string table, string column)
{
table = table.ToLower();
column = column.ToLower();
#region delete
using (var sp_helpResult = database.SelectDataSet("sp_help [" + table + "]"))
{
foreach (var dtable in sp_helpResult.Tables)
{
if (dtable.Columns.Any(m => m.ColumnName == "constraint_name"))
{
var query = dtable.Rows.Where(m => m["constraint_keys"].ToSafeString().ToLower() == column.ToLower());
if (query.Count() > 0)
{
database.ExecSqlString("alter table [" + table + "] drop constraint " + query.First()["constraint_name"]);
}
break;
}
}
//删除默认值
foreach (var dtable in sp_helpResult.Tables)
{
if (dtable.Columns.Any(m => m.ColumnName == "constraint_name"))
{
var query = from m in dtable.Rows
where ((string)m["constraint_type"]).ToLower().EndsWith(" " + column.ToLower()) && ((string)m["constraint_type"]).ToLower().StartsWith("default on ")
select m;
if (query.Count() > 0)
{
database.ExecSqlString("alter table [" + table + "] drop constraint " + query.First()["constraint_name"]);
}
break;
}
}
}
#endregion
database.ExecSqlString("ALTER TABLE [" + table + "] DROP COLUMN [" + column + "]");
}
/// <summary>
/// 根据列创建索引
/// </summary>
/// <param name="database"></param>
/// <param name="table"></param>
/// <param name="indexInfo"></param>
void createIndex(EntityDB.IDatabaseService database, string table, IndexInfo indexInfo)
{
table = table.ToLower();
var columns = indexInfo.ColumnNames.OrderBy(m => m).Select(m => m.ToLower()).ToArray();
string columnsStr = "";
string name = table + "_";
for (int i = 0; i < columns.Length; i++)
{
name += columns[i] + "_";
columnsStr += "[" + columns[i] + "]";
if (i < columns.Length - 1)
{
columnsStr += ",";
}
}
try
{
string type = "";
if (indexInfo.IsUnique)
{
type = "unique ";
}
if (indexInfo.IsClustered)
{
type += "CLUSTERED ";
}
else
{
type += "NONCLUSTERED ";
}
database.ExecSqlString(string.Format("CREATE {3} index IDX_{2} on [{0}] ({1})", table, columnsStr, name, type));
}
catch (Exception ex)
{
throw ex;
}
}
void createIndex(EntityDB.IDatabaseService database, string table, IndexInfo indexinfo)
{
table = table.ToLower();
//alter table table_name add unique key new_uk_name (col1,col2);
var columns = indexinfo.ColumnNames.OrderBy(m => m).Select(m => m.ToLower()).ToArray();
string name = table + "_ej_" + columns.ToSplitString("_");
string sql = "CREATE ";
if (indexinfo.IsUnique)
{
sql += "UNIQUE ";
}
if (indexinfo.IsClustered)
{
sql += $"INDEX ON \"{table}\" ({columns.ToSplitString(" ASC NULLS FIRST,")} ASC NULLS FIRST)";
}
else
{
sql += $"INDEX ON \"{table}\" ({columns.ToSplitString(",")})";
}
database.ExecSqlString(sql);
}
/// <summary>
/// 删除table的所有包含字段的索引
/// </summary>
/// <param name="database"></param>
/// <param name="table"></param>
/// <param name="columnNames">包含的字段</param>
void dropTableAllUniqueIndexWithColumns(EntityDB.IDatabaseService database, string table, List <string> columnNames)
{
table = table.ToLower();
List <string> toDelIndexes = new List <string>();
using (var sp_helpResult = database.SelectDataSet("sp_help [" + table + "]"))
{
foreach (var dtable in sp_helpResult.Tables)
{
if (dtable.Columns.Any(m => m.ColumnName == "index_keys"))
{
foreach (var drow in dtable.Rows)
{
string existColumnString = drow["index_keys"].ToString();
string indexName = drow["index_name"].ToString();
if (indexName.StartsWith("IDX_"))
{
foreach (string column in columnNames)
{
if (Regex.IsMatch(existColumnString, @"\b(" + column + @")\b", RegexOptions.IgnoreCase))
{
toDelIndexes.Add(indexName);
break;
}
}
}
}
break;
}
}
}
foreach (string indexName in toDelIndexes)
{
database.ExecSqlString("drop index " + indexName + " on [" + table + "]");
}
}
public void ChangeTable(EntityDB.IDatabaseService database, string oldTableName, string newTableName, EJ.DBColumn[] addColumns, EJ.DBColumn[] changedColumns, EJ.DBColumn[] deletedColumns, EJ.DBColumn[] otherColumns, IndexInfo[] _indexInfos)
{
oldTableName = oldTableName.ToLower();
newTableName = newTableName.ToLower();
List <IndexInfo> indexInfos = new List <IndexInfo>(_indexInfos);
//先判断表明是否更改
if (oldTableName != newTableName)
{
//更改表名
database.ExecSqlString($"alter table \"{oldTableName}\" RENAME TO \"{newTableName}\"");
}
var needToDels = checkIfIdxChanged(database, newTableName, indexInfos);
foreach (var column in deletedColumns)
{
deletecolumn(database, newTableName, column.Name.ToLower());
}
foreach (string delIndexName in needToDels)
{
dropTableIndex(database, newTableName, delIndexName);
}
foreach (var column in changedColumns)
{
string sqltype = getSqlType(column);
if (column.length.IsNullOrEmpty() == false)
{
if (sqltype.Contains("("))
{
sqltype = sqltype.Substring(0, sqltype.IndexOf("("));
}
sqltype += "(" + column.length + ")";
}
int changeColumnCount = 0;
var changeitem = column.BackupChangedProperties["Name"];
if (changeitem != null)
{
changeColumnCount++;
#region 改名
database.ExecSqlString($"alter table \"{newTableName}\" rename \"{changeitem.OriginalValue.ToString().ToLower()}\" to \"{column.Name.ToLower()}\"");
#endregion
}
changeitem = column.BackupChangedProperties["IsAutoIncrement"];
if (changeitem != null)
{
changeColumnCount++;
#region 变更自增长
setColumn_IsAutoIncrement(database, column, newTableName, column.IsAutoIncrement.Value);
#endregion
}
changeitem = column.BackupChangedProperties["IsPKID"];
if (changeitem != null)
{
changeColumnCount++;
#region 变更主键
if (column.IsPKID == false)
{
//去除主键;//删除主建
//var pkeyIndexName = database.ExecSqlString($"select indexname from pg_indexes where tablename='{newTableName}' and indexname='{oldTableName}_pkey'").ToSafeString();
//if (pkeyIndexName.Length > 0)
//{
// database.ExecSqlString($"ALTER TABLE {newTableName} DROP CONSTRAINT IF EXISTS {oldTableName}_pkey");
// database.ExecSqlString($"DROP INDEX IF EXISTS {oldTableName}_pkey");
//}
database.ExecSqlString($"ALTER TABLE \"{newTableName}\" DROP CONSTRAINT IF EXISTS {oldTableName}_pkey");
database.ExecSqlString($"DROP INDEX IF EXISTS {oldTableName}_pkey");
}
else
{
//设为主键;
database.ExecSqlString($"ALTER TABLE \"{newTableName}\" ADD PRIMARY KEY (\"{column.Name.ToLower()}\")");
}
#endregion
}
bool defaultvalueChanged = false;
changeitem = column.BackupChangedProperties["defaultValue"];
if (changeitem != null)
{
defaultvalueChanged = true;
changeColumnCount++;
#region 默认值
//删除默认值
database.ExecSqlString($"alter table \"{newTableName}\" ALTER COLUMN \"{column.Name.ToLower()}\" DROP DEFAULT");
#endregion
}
if (column.BackupChangedProperties["dbType"] != null || column.BackupChangedProperties["length"] != null)
{
changeColumnCount++;
#region 变更类型
sqltype = getSqlType(column);
string sql = $"alter table \"{newTableName}\" ALTER COLUMN \"{column.Name.ToLower()}\" TYPE {(sqltype + (column.length.IsNullOrEmpty()?"":$"({column.length})"))} using \"{column.Name.ToLower()}\"::{sqltype}";
database.ExecSqlString(sql);
#endregion
}
public void ChangeTable(EntityDB.IDatabaseService database, string oldTableName, string newTableName,
EJ.DBColumn[] addColumns, EJ.DBColumn[] changed_columns, EJ.DBColumn[] deletedColumns, Func <List <EJ.DBColumn> > getColumnsFunc
, IndexInfo[] indexInfos)
{
List <EJ.DBColumn> changedColumns = new List <EJ.DBColumn>(changed_columns);
oldTableName = oldTableName.ToLower();
newTableName = newTableName.ToLower();
//先判断表明是否更改
if (oldTableName != newTableName)
{
//更改表名
database.ExecSqlString("EXEC sp_rename '" + oldTableName + "', '" + newTableName + "'");
}
//获取那个索引存在了
var existIndexed = checkIfIdxChanged(database, newTableName, indexInfos);
dropTableIndex(database, newTableName, existIndexed.ToArray());
foreach (var column in deletedColumns)
{
ChangeColumnHandler.HandleDelete(database, newTableName, column);
}
//将取消主键的列放前面处理
if (true)
{
var column = changedColumns.FirstOrDefault(m => m.BackupChangedProperties["IsPKID"] != null && (bool)m.BackupChangedProperties["IsPKID"].OriginalValue == true);
if (column != null && column.IsPKID == false)
{
changedColumns.Remove(column);
changedColumns.Insert(0, column);
}
}
foreach (var column in changedColumns)
{
ChangeColumnHandler.HandleChange(database, newTableName, column);
}
foreach (var column in addColumns)
{
ChangeColumnHandler.HandleNewColumn(database, newTableName, column);
}
if (indexInfos != null && indexInfos.Length > 0)
{
foreach (var c in indexInfos)
{
if (existIndexed.Contains(c))
{
continue;
}
createIndex(database, newTableName, c);
}
}
}
/// <summary>
/// 返回没有变化的索引
/// </summary>
/// <param name="database"></param>
/// <param name="tablename"></param>
/// <param name="idxConfigs"></param>
/// <returns></returns>
List <IndexInfo> checkIfIdxChanged(EntityDB.IDatabaseService database, string tablename, IndexInfo[] idxConfigs)
{
tablename = tablename.ToLower();
List <IndexInfo> result = new List <IndexInfo>();
List <IndexInfo> existKeys = new List <IndexInfo>();
using (var sp_helpResult = database.SelectDataSet("sp_help [" + tablename + "]"))
{
foreach (var dtable in sp_helpResult.Tables)
{
if (dtable.Columns.Any(m => m.ColumnName == "index_keys"))
{
foreach (WayDataRow drow in dtable.Rows)
{
string existColumnString = drow["index_keys"].ToString();
string indexName = drow["index_name"].ToString();
string index_description = drow["index_description"].ToString();
if (index_description.Contains("primary key") == false)
{
//去除空格
string flag = existColumnString.Split(',').ToSplitString();
string dbname = flag.Split(',').OrderBy(m => m).ToArray().ToSplitString().ToLower();
//再排序，不要在去除空格之前排序
existKeys.Add(new IndexInfo
{
Name = indexName,
IsUnique = index_description.Contains("unique"),
IsClustered = index_description.Contains("clustered") && !index_description.Contains("nonclustered"),
ColumnNames = new string[] { dbname },
});
}
else
{
if (idxConfigs.Count(m => m.IsClustered) > 0)
{
//去除主键
database.ExecSqlString("alter table [" + tablename + "] drop constraint " + indexName);
//设为主键
string flag = existColumnString.Split(',').ToSplitString();
string ppname = flag.Split(',').OrderBy(m => m).ToArray().ToSplitString(",", "[{0}]").ToLower();
database.ExecSqlString("alter table [" + tablename + "] add constraint " + indexName + " primary key NONCLUSTERED (" + ppname + ")");
}
}
}
break;
}
}
}
foreach (var nowConfigItem in idxConfigs)
{
string myname = nowConfigItem.ColumnNames.OrderBy(m => m).ToArray().ToSplitString().ToLower();
var fined = existKeys.FirstOrDefault(m => m.IsUnique == nowConfigItem.IsUnique && m.IsClustered == nowConfigItem.IsClustered && m.ColumnNames[0].ToLower() == myname);
if (fined != null)
{
nowConfigItem.Name = fined.Name;
result.Add(nowConfigItem);
}
}
return(result);
}
public void CreateTable(EntityDB.IDatabaseService db, EJ.DBTable table, EJ.DBColumn[] columns
, IndexInfo[] IDXConfigs)
{
string sqlstr;
sqlstr = @"
CREATE TABLE [" + table.Name.ToLower() + @"] (
";
foreach (EJ.DBColumn column in columns)
{
var dbtype = GetSqlServerType(column);
sqlstr += "[" + column.Name.ToLower() + "] [" + dbtype + "]";
if (dbtype.IndexOf("char") >= 0)
{
if (!string.IsNullOrEmpty(column.length))
{
sqlstr += " (" + column.length + ")";
}
else
{
sqlstr += " (50)";
}
}
else
{
if (!string.IsNullOrEmpty(column.length))
{
sqlstr += " (" + column.length + ")";
}
}
if (column.IsAutoIncrement == true)
{
sqlstr += " IDENTITY (1, 1)";
}
if (column.CanNull == false || column.IsPKID == true || column.IsAutoIncrement == true)
{
sqlstr += " NOT";
}
sqlstr += " NULL ";
if (!string.IsNullOrEmpty(column.defaultValue))
{
string defaultValue = column.defaultValue.Trim();
sqlstr += " CONSTRAINT [DF_" + table.Name.ToLower() + "_" + column.Name.ToLower() + "] DEFAULT ('" + defaultValue.Replace("'", "''") + "')";
}
sqlstr += ",";
}
if (sqlstr.EndsWith(","))
{
sqlstr = sqlstr.Remove(sqlstr.Length - 1);
}
sqlstr += ")";
db.ExecSqlString(sqlstr);
foreach (var column in columns)
{
if (column.IsPKID == true)
{
//设为主键
db.ExecSqlString("alter table [" + table.Name.ToLower() + "] add constraint PK_" + table.Name.ToLower() + "_" + column.Name.ToLower() + " primary key ([" + column.Name.ToLower() + "])");
}
}
if (IDXConfigs != null && IDXConfigs.Length > 0)
{
foreach (var c in IDXConfigs)
{
createIndex(db, table.Name.ToLower(), c);
}
}
}
public void CreateTable(EntityDB.IDatabaseService db, EJ.DBTable table, EJ.DBColumn[] columns, IndexInfo[] indexInfos)
{
string sqlstr;
sqlstr = @"
CREATE TABLE [" + table.Name.ToLower() + @"] (
";
for (int i = 0; i < columns.Length; i++)
{
var column = columns[i];
if (i > 0)
{
sqlstr += ",\r\n";
}
string dbtype = getSqliteType(column);
sqlstr += "[" + column.Name.ToLower() + "] " + getSqliteType(column) + "";
if (column.IsPKID == true)
{
sqlstr += " PRIMARY KEY ";
}
if (column.IsAutoIncrement == true)
{
sqlstr += " AUTOINCREMENT ";
}
if (column.CanNull == false || column.IsPKID == true || column.IsAutoIncrement == true)
{
sqlstr += " NOT";
}
sqlstr += " NULL ";
if (dbtype.Contains("char") || dbtype.Contains("text"))
{
sqlstr += " COLLATE NOCASE ";//查询时忽略大小写
}
if (!string.IsNullOrEmpty(column.defaultValue))
{
string defaultValue = column.defaultValue.Trim();
sqlstr += " DEFAULT '" + defaultValue.Replace("'", "''") + "'";
}
}
sqlstr += ")";
db.ExecSqlString(sqlstr);
if (indexInfos != null && indexInfos.Length > 0)
{
foreach (var config in indexInfos)
{
string keyname = table.Name.ToLower() + "_ej_" + config.ColumnNames.OrderBy(m => m).ToArray().ToSplitString("_").ToLower();
string type = "";
if (config.IsUnique || config.IsClustered)
{
type += "UNIQUE ";
}
//if (config.IsClustered)
//{
// throw new Exception("sqlite暂不支持定义聚集索引");
//}
db.ExecSqlString("CREATE " + type + " INDEX " + keyname + " ON [" + table.Name.ToLower() + "](" + config.ColumnNames.OrderBy(m => m).ToArray().ToSplitString().ToLower() + ")");
//CREATE UNIQUE INDEX index_t1 ON t1(a, b, c};
// 第二种：
// CREATE UNIQUE INDEX index_a_t1 ON t1(a);
//DROP INDEX IF EXISTS testtable_idx;
}
}
}
public void GetViews(EntityDB.IDatabaseService db, out List <EJ.DBTable> tables, out List <EJ.DBColumn> columns)
{
tables = new List <EJ.DBTable>();
columns = new List <EJ.DBColumn>();
}
static void Test(EJ.Databases dbconfig)
{
IDatabaseDesignService dbservice = EntityDB.Design.DBHelper.CreateDatabaseDesignService((EntityDB.DatabaseType)(int) dbconfig.dbType);
EntityDB.IDatabaseService db = null;
dbservice.Drop(dbconfig);
try
{
dbservice.Create(dbconfig);
db = EntityDB.DBContext.CreateDatabaseService(dbconfig.conStr, (EntityDB.DatabaseType)(int) dbconfig.dbType);
List <EJ.DBColumn> allColumns = new List <EJ.DBColumn>();
List <EntityDB.Design.IndexInfo> allindexes = new List <EntityDB.Design.IndexInfo>();
#region CreateTable
if (true)
{
EJ.DBTable tableUser = new EJ.DBTable();
tableUser.Name = "User";
allColumns.Add(new EJ.DBColumn()
{
IsPKID = true,
CanNull = false,
Name = "Id",
dbType = "int",
IsAutoIncrement = true,
});
allColumns.Add(new EJ.DBColumn()
{
Name = "Name",
dbType = "varchar",
length = "50",
defaultValue = "a'b,c"
});
CreateTableAction _CreateTableAction = new CreateTableAction(tableUser, allColumns.ToArray(), allindexes.ToArray());
_CreateTableAction.Invoke(db);
DeleteTableAction _delaction = new DeleteTableAction(tableUser.Name);
_delaction.Invoke(db);
//再次创建
_CreateTableAction.Invoke(db);
}
#endregion
allColumns.Clear();
allindexes.Clear();
allColumns.Add(new EJ.DBColumn()
{
IsPKID = true,
CanNull = false,
Name = "Id",
dbType = "int",
IsAutoIncrement = true,
});
allColumns.Add(new EJ.DBColumn()
{
Name = "C1",
dbType = "varchar",
length = "50",
defaultValue = "a'b,c"
});
allColumns.Add(new EJ.DBColumn()
{
Name = "C2",
dbType = "varchar",
length = "50",
defaultValue = "abc"
});
allColumns.Add(new EJ.DBColumn()
{
Name = "C3",
dbType = "int",
defaultValue = "8"
});
allColumns.Add(new EJ.DBColumn()
{
Name = "Text1",
dbType = "text",
});
//索引
allindexes.Add(new EntityDB.Design.IndexInfo()
{
ColumnNames = new string[] { "C1" },
IsUnique = true,
});
allindexes.Add(new EntityDB.Design.IndexInfo()
{
ColumnNames = new string[] { "C2", "C3" },
});
EJ.DBTable table = new EJ.DBTable();
table.Name = "Test";
#region CreateTable
if (true)
{
CreateTableAction _CreateTableAction = new CreateTableAction(table, allColumns.ToArray(), allindexes.ToArray());
_CreateTableAction.Invoke(db);
foreach (var c in allColumns)
{
c.ChangedProperties.Clear();
c.BackupChangedProperties.Clear();
}
checkColumns(dbservice, db, table.Name, allColumns, allindexes);
}
#endregion
#region 测试自增长id
if (true)
{
Way.EntityDB.CustomDataItem dataitem = new EntityDB.CustomDataItem("test", "id", null);
dataitem.SetValue("c1", "C1");
dataitem.SetValue("c2", "C2");
dataitem.SetValue("c3", 3);
db.Insert(dataitem);
if (dataitem.GetValue("id") == null)
{
throw new Exception("测试自增长id失败");
}
db.ExecSqlString("delete from test");
}
#endregion
#region ChangeTable1
if (true)
{
EJ.DBColumn[] newcolumns = new EJ.DBColumn[2];
newcolumns[0] = (new EJ.DBColumn()
{
Name = "N0",
dbType = "varchar",
length = "30",
defaultValue = "t'b"
});
newcolumns[1] = (new EJ.DBColumn()
{
Name = "N1",
dbType = "int",
defaultValue = "18"
});
EJ.DBColumn[] changedColumns = new EJ.DBColumn[2];
changedColumns[0] = allColumns.FirstOrDefault(m => m.Name == "C3");
changedColumns[0].Name = "C3_changed";
changedColumns[0].dbType = "varchar";
changedColumns[0].defaultValue = "1'a";
changedColumns[0].CanNull = false;
changedColumns[0].length = "88";
changedColumns[1] = allColumns.FirstOrDefault(m => m.Name == "Id");
changedColumns[1].IsAutoIncrement = false;
changedColumns[1].IsPKID = false;
changedColumns[1].CanNull = true;
EJ.DBColumn[] deletecolumns = new EJ.DBColumn[1];
deletecolumns[0] = allColumns.FirstOrDefault(m => m.Name == "C2");
allColumns.Remove(deletecolumns[0]);
allindexes.Clear();
allindexes.Add(new EntityDB.Design.IndexInfo()
{
ColumnNames = new string[] { "N0", "C3_changed" },
IsUnique = true,
IsClustered = true
});
var otherColumns = (from m in allColumns
where changedColumns.Contains(m) == false
select m).ToArray();
new ChangeTableAction(table.Name, "Test2", newcolumns, changedColumns, deletecolumns, otherColumns, allindexes.ToArray())
.Invoke(db);
table.Name = "Test2";
allColumns.AddRange(newcolumns);
foreach (var c in allColumns)
{
c.ChangedProperties.Clear();
c.BackupChangedProperties.Clear();
}
checkColumns(dbservice, db, table.Name, allColumns, allindexes);
}
#endregion
#region ChangeTable2
if (true)
{
EJ.DBColumn[] newcolumns = new EJ.DBColumn[0];
EJ.DBColumn[] changedColumns = new EJ.DBColumn[1];
changedColumns[0] = allColumns.FirstOrDefault(m => m.Name == "Id");
changedColumns[0].IsAutoIncrement = true;
changedColumns[0].IsPKID = true;
changedColumns[0].CanNull = false;
EJ.DBColumn[] deletecolumns = new EJ.DBColumn[0];
var otherColumns = (from m in allColumns
where changedColumns.Contains(m) == false
select m).ToArray();
new ChangeTableAction(table.Name, "Test3", newcolumns, changedColumns, deletecolumns, otherColumns, allindexes.ToArray())
.Invoke(db);
table.Name = "Test3";
allColumns.AddRange(newcolumns);
foreach (var c in allColumns)
{
c.ChangedProperties.Clear();
c.BackupChangedProperties.Clear();
}
checkColumns(dbservice, db, table.Name, allColumns, allindexes);
}
#endregion
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (db != null)
{
db.DBContext.Dispose();
}
}
}
public void CreateTable(EntityDB.IDatabaseService db, EJ.DBTable table, EJ.DBColumn[] columns, IndexInfo[] indexInfos)
{
//db.ExecSqlString("drop table if exists " + table.Name.ToLower() + "");
string sqlstr;
sqlstr = @"
CREATE TABLE """ + table.Name.ToLower() + @""" (
";
for (int i = 0; i < columns.Length; i++)
{
var column = columns[i];
if (i > 0)
{
sqlstr += ",\r\n";
}
string sqltype = getSqlType(column);
if (string.IsNullOrEmpty(column.length) == false)
{
if (sqltype.Contains("("))
{
sqltype = sqltype.Substring(0, sqltype.IndexOf("("));
}
sqltype += "(" + column.length + ")";
}
sqlstr += "\"" + column.Name.ToLower() + "\" " + sqltype;
if (column.CanNull == false || column.IsPKID == true || column.IsAutoIncrement == true)
{
sqlstr += " NOT";
}
sqlstr += " NULL ";
if (column.IsPKID == true)
{
sqlstr += " PRIMARY KEY ";
}
if (!string.IsNullOrEmpty(column.defaultValue))
{
string defaultValue = column.defaultValue.Trim();
sqlstr += " DEFAULT '" + defaultValue.Replace("'", "''") + "'";
}
}
sqlstr += ")";
db.ExecSqlString(sqlstr);
foreach (var column in columns)
{
if (column.IsAutoIncrement == true)
{
setColumn_IsAutoIncrement(db, column, table.Name.ToLower(), true);
}
}
if (indexInfos != null && indexInfos.Length > 0)
{
foreach (var config in indexInfos)
{
createIndex(db, table.Name.ToLower(), config);
}
}
}
public void DeleteTable(EntityDB.IDatabaseService database, string tableName)
{
database.ExecSqlString("DROP TABLE [" + tableName.ToLower() + "]");
}
void dropTableIndex(EntityDB.IDatabaseService database, string table, string indexName)
{
table = table.ToLower();
database.ExecSqlString("ALTER TABLE \"" + table.ToLower() + "\" DROP CONSTRAINT IF EXISTS " + indexName.ToLower() + "");
database.ExecSqlString("DROP INDEX IF EXISTS " + indexName.ToLower() + "");
}
public void DeleteTable(EntityDB.IDatabaseService database, string tableName)
{
database.ExecSqlString(string.Format("DROP TABLE IF EXISTS \"{0}\"", tableName.ToLower()));
}