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