public void UpdateDbInfo()
{
string newPath = this.textBox2.Text;
if (!Directory.Exists(newPath) && !File.Exists(newPath))
{
AddLog("下载文件：" + this.textBox9.Text);
FileHelp.DownloadFiles(this.textBox9.Text, xzPath + "\\hdkzgateway.db");
newPath = xzPath + "\\hdkzgateway.db";
}
SqLiteHelp sqLiteHelp = new SqLiteHelp("Data Source=" + newPath);
string oldPath = xzPath + "\\old\\hdkzgateway.db";
SShHelp.Instance.DownloadFiles("/usr/local/api/hdkzApi/hdkzgateway.db", oldPath);
File.Copy(oldPath, xzPath + "\\old\\oldhdkzgateway.db", true);
SqLiteHelp oldSqLiteHelp2 = new SqLiteHelp($"Data Source={oldPath}");
List<string> tableNames = sqLiteHelp.FindAllTable();
List<string> oldTableNames = oldSqLiteHelp2.FindAllTable();
Dictionary<string, List<string[]>> addField = new Dictionary<string, List<string[]>>();
Dictionary<string, List<string[]>> updateField = new Dictionary<string, List<string[]>>();
Dictionary<string, List<string[]>> delField = new Dictionary<string, List<string[]>>();
List<string> addTableName = new List<string>();
foreach (var name in tableNames.Where(e=> !e.Contains("_old_") && !e.Contains("Historys_")).ToList())
{
bool isNameExist = false;
List<string[]> fieldList = sqLiteHelp.FindTableField(name);
foreach (var oldName in oldTableNames)
{
if (name.Equals(oldName))
{
isNameExist = true;
List<string[]> oldFieldList = oldSqLiteHelp2.FindTableField(oldName);
foreach (string[] oldField in oldFieldList)
{
bool isDel = false;
foreach (string[] field in fieldList)
{
if (oldField[1].Equals(field[1]))
{
isDel = false;
break;
}
else
isDel = true;
}
if(isDel)
{
if (!delField.ContainsKey(name))
{
List<string[]> fields = new List<string[]> { oldField };
delField.Add(name, fields);
}
else
{
delField[name].Add(oldField);
}
}
}
foreach (string[] field in fieldList)
{
bool isExist = false;
foreach (string[] oldField in oldFieldList)
{
if (field[1].Equals(oldField[1]))
{
isExist = true;
if (field[0] == oldField[0]
&& field[1] == oldField[1]
&& field[2] == oldField[2]
&& field[3] == oldField[3]
&& field[4] == oldField[4]
&& field[5] == oldField[5])
{
break;
}
else
{
if(!updateField.ContainsKey(name))
{
List<string[]> fields = new List<string[]> { field };
updateField.Add(name, fields);
}
else
{
updateField[name].Add(field);
}
}
}
}
if (!isExist)
{
if (!addField.ContainsKey(name))
{
List<string[]> fields = new List<string[]> { field };
addField.Add(name, fields);
}
else
{
addField[name].Add(field);
}
}
}
break;
}
}
if(!isNameExist)
{
addTableName.Add(name);
}
}
foreach (var item in updateField)
{
item.Value.ForEach(x =>
{
//string updateSql = $"ALTER TABLE {item.Key} ALTER COLUMN {x[1]} {(x[3].Equals(1) ? "DROP NOT NULL" : "SET NULL")}";
//oldSqLiteHelp2.ExecuteSql(updateSql);
});
}
foreach (var item in addField)
{
item.Value.ForEach(x =>
{
string addSql = $"ALTER TABLE {item.Key} ADD COLUMN `{x[1]}` {x[2]} {(x[3].Equals(1) ? "NOT NULL" : "")}";
AddLog("添加字段：" + addSql + "\r\n");
oldSqLiteHelp2.ExecuteSql(addSql);
});
}
foreach (var item in delField)
{
item.Value.ForEach(e =>
{
string delSql = $"ALTER TABLE {item.Key} DROP `{e[1]}`";
AddLog("删除字段：" + delSql + "\r\n");
oldSqLiteHelp2.ExecuteSql(delSql);
});
}
addTableName.ForEach(name =>
{
List<string[]> fieldList = sqLiteHelp.FindTableField(name);
string createTable = $"CREATE TABLE {name} (";
string pk = "";
foreach (string[] field in fieldList)
{
createTable += field[1]+$" `{field[2]}` "+ (field[3].Equals(1) ? "NOT NULL" : "")+",";
if (field[5].Equals("1"))
{
pk= field[1];
}
}
if (!string.IsNullOrEmpty(pk))
{
createTable += $"CONSTRAINT \"PK_{name}\" PRIMARY KEY (\"" + pk + "\")";
}
else
{
createTable = createTable.Substring(0, createTable.Length - 1);
}
createTable += ");";
AddLog("创建表：" + createTable + "\r\n");
oldSqLiteHelp2.ExecuteSql(createTable);
});
oldTableNames.ForEach(name =>
{
if(tableNames.IndexOf(name) == -1 && !name.Contains("_old_") && !name.Contains("Historys_"))
{
AddLog("删除表："+name+ "\r\n");
string sql = "DROP TABLE " + name;
oldSqLiteHelp2.ExecuteSql(sql);
}
});
sqLiteHelp.Clone();
oldSqLiteHelp2.Clone();
Thread.Sleep(2000);
File.Copy(oldPath, xzPath + "\\new\\hdkzgateway.db",true);
SShHelp.Instance.Upload("/usr/local/api/hdkzApi", xzPath + "\\new\\hdkzgateway.db");
}
using System.Collections.Generic;
using System.Data.SQLite;
namespace Pack
{
public class SqLiteHelp
{
public string ConnectionString { get; set; }
public SQLiteConnection connection { get; set; }
public SqLiteHelp(string ConnectionString) {
this.ConnectionString = ConnectionString;
connection = new SQLiteConnection(ConnectionString);
connection.Open();
}
public void Clone()
{
connection.Clone();
connection.Dispose();
}
public List<string> FindAllTable(string sql = "SELECT name FROM sqlite_master WHERE type ='table'")
{
SQLiteCommand command = new SQLiteCommand(sql, connection);
SQLiteDataReader reader = command.ExecuteReader();
List<string> results = new List<string>();
while (reader.Read())
{
string[] row = new string[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
row[i] = reader[i].ToString();
}
results.Add(row[0]);
}
return results;
}
public List<string[]> FindTableField(string table)
{
SQLiteCommand command = new SQLiteCommand("PRAGMA table_info(" + table + ")", connection);
SQLiteDataReader reader = command.ExecuteReader();
List<string[]> results = new List<string[]>();
while (reader.Read())
{
string[] row = new string[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
row[i] = reader[i].ToString();
}
results.Add(row);
}
return results;
}
public int ExecuteSql(string sql)
{
SQLiteCommand command = new SQLiteCommand(sql, connection);
int data = command.ExecuteNonQuery();
return data;
}
}
}