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 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[] 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() + ")"); } } }