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 //{ //} }
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 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 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)); }
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)); }
/// <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 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); }
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 + "]"); }
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 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); }
/// <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 + "]"); } }
/// <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 ChangeTable(EntityDB.IDatabaseService database, string oldTableName, string newTableName, EJ.DBColumn[] addColumns, EJ.DBColumn[] changedColumns, EJ.DBColumn[] deletedColumns, Func <List <EJ.DBColumn> > getColumnsFunc , IndexInfo[] indexInfos) { 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) { deletecolumn(database, newTableName.ToLower(), column.Name.ToLower()); } foreach (var column in changedColumns) { int changeColumnCount = 0; var changeitem = column.BackupChangedProperties["Name"]; if (changeitem != null) { changeColumnCount++; #region 改名 database.ExecSqlString($"EXEC sp_rename '[{newTableName.ToLower()}].[{changeitem.OriginalValue}]', '{column.Name.ToLower()}', 'COLUMN'"); #endregion } changeitem = column.BackupChangedProperties["IsAutoIncrement"]; if (changeitem != null) { changeColumnCount++; var flagColumnName = "_tempcolumn"; while (Convert.ToInt32(database.ExecSqlString($"Select count(*) from syscolumns Where ID=OBJECT_ID('{newTableName}') and name='{flagColumnName}'")) > 0) { flagColumnName += "1"; } #region 变更自增长 if (column.IsAutoIncrement == false) { //去掉自增长 database.ExecSqlString($"alter table [{newTableName.ToLower()}] add {flagColumnName.ToLower()} {column.dbType}"); database.ExecSqlString($"update [{newTableName.ToLower()}] set {flagColumnName.ToLower()}=[{column.Name.ToLower()}]"); deletecolumn(database, newTableName.ToLower(), column.Name.ToLower()); database.ExecSqlString($"EXEC sp_rename '[{newTableName.ToLower()}].{flagColumnName.ToLower()}', '{column.Name.ToLower()}', 'COLUMN'"); } else { //设为自增长 database.ExecSqlString($"alter table [{newTableName.ToLower()}] add {flagColumnName.ToLower()} {column.dbType} IDENTITY (1, 1)"); deletecolumn(database, newTableName.ToLower(), column.Name.ToLower()); database.ExecSqlString($"EXEC sp_rename '[{newTableName.ToLower()}].{flagColumnName.ToLower()}', '{column.Name.ToLower()}', 'COLUMN'"); } //去掉自增长后,由于原来的列删除了,所以如果原来是主键,必须重新设置为主键 if (column.IsPKID == true) { //主键不允许为空 database.ExecSqlString($"alter table [{newTableName.ToLower()}] alter column [{column.Name.ToLower()}] [{column.dbType}] not null"); changeitem = column.BackupChangedProperties["IsPKID"]; if (changeitem == null) { //标识IsPKID发生变化 column.BackupChangedProperties["IsPKID"] = new EntityDB.DataValueChangedItem() { OriginalValue = false, }; } } #endregion } changeitem = column.BackupChangedProperties["IsPKID"]; if (changeitem != null) { changeColumnCount++; #region 变更主键 if (column.IsPKID == false) { //去除主键 string sql = @" DECLARE @NAME SYSNAME DECLARE @TB_NAME SYSNAME SET @TB_NAME = '" + newTableName + @"' SELECT TOP 1 @NAME = NAME FROM SYS.OBJECTS WITH(NOLOCK) WHERE TYPE_DESC ='PRIMARY_KEY_CONSTRAINT' AND PARENT_OBJECT_ID = (SELECT OBJECT_ID FROM SYS.OBJECTS WITH(NOLOCK) WHERE NAME = @TB_NAME ) SELECT @NAME "; var constraintName = database.ExecSqlString(sql).ToSafeString(); if (constraintName.IsNullOrEmpty() == false) { //如果constraintName没有值,那么就是变更自增长字段的时候,原来字段被删除了 database.ExecSqlString($"ALTER TABLE {newTableName.ToLower()} DROP CONSTRAINT {constraintName}"); } } else { //设为主键 database.ExecSqlString("alter table [" + newTableName.ToLower() + "] add constraint PK_" + newTableName.ToLower() + "_" + column.Name.ToLower() + " primary key ([" + column.Name.ToLower() + "])"); } #endregion } bool defaultvalueChanged = false; changeitem = column.BackupChangedProperties["defaultValue"]; if (changeitem != null) { defaultvalueChanged = true; changeColumnCount++; #region 默认值 //获取默认值的id var defaultSettingID = database.ExecSqlString($"Select cdefault from syscolumns Where ID=OBJECT_ID('{newTableName.ToLower()}') and name='{column.Name.ToLower()}'"); if (defaultSettingID != null && Convert.ToInt32(defaultSettingID) != 0) { var defaultKeyName = database.ExecSqlString($"select name from sysObjects where type='D' and id={defaultSettingID}"); if (defaultKeyName != null) { //如果进到这里,那么表示原来有默认值 database.ExecSqlString($"alter table [{newTableName}] drop constraint {defaultKeyName}"); } } #endregion } if (column.BackupChangedProperties.Count > changeColumnCount) { #region 如果其他地方还有更改 string sql = "alter table [" + newTableName.ToLower() + "] alter column [" + column.Name.ToLower() + "] [" + column.dbType + "]"; if (column.dbType.IndexOf("char") >= 0) { if (!string.IsNullOrEmpty(column.length)) { sql += " (" + column.length + ")"; } else { sql += " (50)"; } } else { if (!string.IsNullOrEmpty(column.length)) { sql += " (" + column.length + ")"; } } //先改变字段类型,下面再设置默认值,和非null if (column.IsPKID == true || column.IsAutoIncrement == true) { database.ExecSqlString(sql); } else { database.ExecSqlString(sql + " NULL"); } if (column.CanNull == false && !string.IsNullOrEmpty(column.defaultValue)) { string defaultValue = column.defaultValue.Trim(); database.ExecSqlString("update [" + newTableName.ToLower() + "] set [" + column.Name.ToLower() + "]='" + defaultValue.Replace("'", "''") + "' where [" + column.Name.ToLower() + "] is null"); } if (column.CanNull == false || column.IsPKID == true || column.IsAutoIncrement == true) { database.ExecSqlString(sql + " NOT NULL"); } #endregion } #region 设置默认值 if (defaultvalueChanged && !string.IsNullOrEmpty(column.defaultValue)) { string sql = ""; string defaultValue = column.defaultValue.Trim(); sql += "alter table [" + newTableName + "] add constraint DF_" + newTableName.ToLower() + "_" + column.Name.ToLower() + " default '" + defaultValue.Replace("'", "''") + "' for [" + column.Name.ToLower() + "]"; if (sql.Length > 0) { database.ExecSqlString(sql); } database.ExecSqlString("update [" + newTableName.ToLower() + "] set [" + column.Name.ToLower() + "]='" + defaultValue.Replace("'", "''") + "' where [" + column.Name.ToLower() + "] is null"); } #endregion } foreach (var column in addColumns) { #region 新增字段 string sql = "alter table [" + newTableName.ToLower() + "] add [" + column.Name.ToLower() + "] [" + column.dbType + "]"; if (column.dbType.IndexOf("char") >= 0) { if (!string.IsNullOrEmpty(column.length)) { sql += " (" + column.length + ")"; } else { sql += " (50)"; } } else { if (!string.IsNullOrEmpty(column.length)) { sql += " (" + column.length + ")"; } } if (column.IsAutoIncrement == true) { sql += " IDENTITY (1, 1)"; } if (column.CanNull == false || column.IsPKID == true || column.IsAutoIncrement == true) { sql += " NOT"; } sql += " NULL "; if (!string.IsNullOrEmpty(column.defaultValue)) { string defaultValue = column.defaultValue.Trim(); sql += " default '" + defaultValue.Replace("'", "''") + "' with values"; } database.ExecSqlString(sql); if (column.IsPKID == true) { database.ExecSqlString("nalter table [" + newTableName.ToLower() + "] add constraint pk_" + newTableName.ToLower() + "_" + column.Name.ToLower() + " primary key ([" + column.Name.ToLower() + "])"); } #endregion } if (indexInfos != null && indexInfos.Length > 0) { foreach (var c in indexInfos) { if (existIndexed.Contains(c)) { continue; } createIndex(database, newTableName, c); } } }
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 ChangeTable(EntityDB.IDatabaseService database, string oldTableName, string newTableName, EJ.DBColumn[] addColumns, EJ.DBColumn[] changedColumns, EJ.DBColumn[] deletedColumns, Func <List <EJ.DBColumn> > getColumnsFunc, IndexInfo[] _indexInfos) { oldTableName = oldTableName.ToLower(); newTableName = newTableName.ToLower(); List <IndexInfo> indexInfos = new List <IndexInfo>(_indexInfos); bool needToDeleteTable = (deletedColumns.Length > 0 || changedColumns.Length > 0); if (needToDeleteTable) { string changetoname = oldTableName + "_2"; while (Convert.ToInt32(database.ExecSqlString("select count(*) from sqlite_master where type='table' and name='" + changetoname + "'")) > 0) { changetoname = changetoname + "_2"; } //删除索引 deleteAllIndex(database, oldTableName); database.ExecSqlString("ALTER TABLE [" + oldTableName + "] RENAME TO [" + changetoname + "]"); oldTableName = changetoname; EJ.DBTable dt = new EJ.DBTable() { Name = newTableName, }; //PRAGMA table_info([project]) 用name type var allColumns = getColumnsFunc(); for (int i = 0; i < allColumns.Count; i++) { var columnid = allColumns[i].id; if (deletedColumns.Any(m => m.id == columnid) || changedColumns.Any(m => m.id == columnid)) { allColumns.RemoveAt(i); i--; } } allColumns.AddRange(addColumns); allColumns.AddRange(changedColumns); CreateTable(database, dt, allColumns.ToArray(), _indexInfos); //获取原来所有字段 List <string> oldColumnNames = new List <string>(); List <string> newColumnNames = new List <string>(); using (var dtable = database.SelectTable("select * from [" + oldTableName + "] limit 0,1")) { foreach (var c in dtable.Columns) { if (deletedColumns.Count(m => m.Name.ToLower() == c.ColumnName.ToLower()) == 0) { var newName = changedColumns.FirstOrDefault(m => m.BackupChangedProperties.Count(p => p.Key == "Name" && ((DataValueChangedItem)p.Value).OriginalValue.ToSafeString().ToLower() == c.ColumnName.ToLower()) > 0); oldColumnNames.Add("[" + c.ColumnName.ToLower() + "]"); if (newName != null) { newColumnNames.Add("[" + newName.Name.ToLower() + "]"); } else { newColumnNames.Add("[" + c.ColumnName.ToLower() + "]"); } } } } string oldfields = oldColumnNames.ToArray().ToSplitString(); string newfields = newColumnNames.ToArray().ToSplitString(); if (oldColumnNames.Count > 0) { //把旧表数据拷贝到新表 database.ExecSqlString("insert into [" + newTableName.ToLower() + "] (" + newfields + ") select " + oldfields + " from [" + oldTableName.ToLower() + "]"); } database.ExecSqlString("DROP TABLE [" + oldTableName.ToLower() + "]"); } else { var need2dels = checkIfIdxChanged(database, oldTableName.ToLower(), indexInfos); foreach (string delName in need2dels) { deleteIndex(database, oldTableName.ToLower(), delName.ToLower()); } if (oldTableName.ToLower() != newTableName.ToLower()) { database.ExecSqlString("ALTER TABLE [" + oldTableName.ToLower() + "] RENAME TO [" + newTableName.ToLower() + "]"); } foreach (var column in addColumns) { #region 新增字段 var dbtype = getSqliteType(column); string sql = "alter table [" + newTableName.ToLower() + "] add [" + column.Name.ToLower() + "] " + dbtype; if (column.IsPKID == true) { sql += " PRIMARY KEY "; } if (column.IsAutoIncrement == true) { sql += " AUTOINCREMENT "; } if (column.CanNull == false || column.IsPKID == true || column.IsAutoIncrement == true) { sql += " NOT"; } sql += " NULL "; if (dbtype.Contains("char") || dbtype.Contains("text")) { sql += " COLLATE NOCASE "; } if (!string.IsNullOrEmpty(column.defaultValue)) { string defaultValue = column.defaultValue.Trim(); sql += " DEFAULT '" + defaultValue.Replace("'", "''") + "'"; } database.ExecSqlString(sql); #endregion } foreach (var config in indexInfos) { string keyname = newTableName.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暂不支持定义聚集索引"); //} database.ExecSqlString("CREATE " + type + " INDEX " + keyname + " ON [" + newTableName.ToLower() + "](" + config.ColumnNames.OrderBy(m => m).ToArray().ToSplitString().ToLower() + ")"); } } }
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 }
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())); }
void dropTableIndex(EntityDB.IDatabaseService database, string table, string indexName) { indexName = indexName.ToLower(); table = table.ToLower(); database.ExecSqlString("ALTER TABLE `" + table + "` DROP INDEX `" + indexName + "`"); }
public void CreateTable(EntityDB.IDatabaseService db, EJ.DBTable table, EJ.DBColumn[] columns, IndexInfo[] indexInfos) { //db.ExecSqlString("drop table if exists `" + table.Name + "`"); 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.dbType); 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.IsAutoIncrement == true) { sqlstr += " AUTO_INCREMENT "; } if (!string.IsNullOrEmpty(column.defaultValue)) { string defaultValue = column.defaultValue.Trim(); sqlstr += " DEFAULT '" + defaultValue.Replace("'", "''") + "'"; } } for (int i = 0; i < columns.Length; i++) { var column = columns[i]; if (column.IsPKID == true) { sqlstr += ",\r\nPRIMARY KEY (`" + column.Name.ToLower() + "`)"; } } if (indexInfos != null && indexInfos.Length > 0) { foreach (var config in indexInfos) { string type = ""; if (config.IsUnique || config.IsClustered) { type += "UNIQUE "; } //if (config.IsClustered) // throw new Exception("MySql不支持定义聚集索引"); string keyname = table.Name.ToLower() + "_ej_" + config.ColumnNames.OrderBy(m => m).ToArray().ToSplitString("_").ToLower(); sqlstr += (",\r\n" + type + " KEY `" + keyname + "`(" + config.ColumnNames.OrderBy(m => m).ToArray().ToSplitString(",", "`{0}`").ToLower() + ")"); } } sqlstr += ") ENGINE=InnoDB DEFAULT CHARSET=utf8"; db.ExecSqlString(sqlstr); }
public void DeleteTable(EntityDB.IDatabaseService database, string tableName) { database.ExecSqlString("DROP TABLE [" + tableName.ToLower() + "]"); }
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); } } }
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 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; } } }
/// <summary> /// 删除表所有索引 /// </summary> /// <param name="database"></param> /// <param name="tableName"></param> void deleteIndex(EntityDB.IDatabaseService database, string tableName, string name) { tableName = tableName.ToLower(); name = name.ToLower(); database.ExecSqlString("DROP INDEX IF EXISTS [" + name + "]"); }