/// <summary>
/// SQLite 转换到 MySQL
/// </summary>
/// <param name="SQLite_ConnectionString">源 SQLite 数据库连接串</param>
/// <param name="MySQL_ConnectionString">目标 MySQL 数据库连接串</param>
/// <param name="isWithData">是否携带所有的数据进行转换</param>
/// <param name="ignoreViewRelyon">是否忽略视图依赖关系</param>
/// <param name="description">错误描述</param>
/// <returns></returns>
public bool SQLiteToMySQL(string SQLite_ConnectionString, string MySQL_ConnectionString, bool isWithData, bool ignoreViewRelyon, ref string description)
{
description = "";
#region 连接数据库 & 读取SQLite 结构到 Model
if (Path.IsPathRooted(SQLite_ConnectionString))
{
SQLite_ConnectionString = "data source=" + SQLite_ConnectionString + ";Version=3";
}
SQLiteConnection conn_s = Database.DatabaseAccess.CreateDataConnection <SQLiteConnection>(SQLite_ConnectionString);
if ((conn_s == null) || (conn_s.State != ConnectionState.Open))
{
description = "连接源数据库发生错误，请检查网站源数据库文件(基本数据库文件)";
return(false);
}
Model.Database model = SQLiteToModel(conn_s, ignoreViewRelyon);
if (model == null)
{
conn_s.Close();
description = "从原数据中读取表、视图结构发生错误";
return(false);
}
MySqlConnection conn_t = Database.DatabaseAccess.CreateDataConnection <MySqlConnection>(MySQL_ConnectionString);
if ((conn_t == null) || (conn_t.State != ConnectionState.Open))
{
conn_s.Close();
description = "连接目标数据库发生错误，请检查连接字符串";
return(false);
}
#endregion
MySqlTransaction trans = null;
MySqlCommand cmd_mysql = new MySqlCommand();
cmd_mysql.CommandTimeout = 600;
cmd_mysql.Connection = conn_t;
StringBuilder sb = new StringBuilder();
SQLiteCommand cmd_sqlite = new SQLiteCommand();
cmd_sqlite.Connection = conn_s;
SQLiteDataReader dr_sqlite = null;
#region 升迁表、索引
sb.AppendLine("SET FOREIGN_KEY_CHECKS=0;\r\n");
for (int i = 0; i < model.Tables.Count; i++)
{
Table table = model.Tables[i];
sb.AppendLine("DROP TABLE IF EXISTS `" + table.Name + "`;");
sb.Append("CREATE TABLE `" + table.Name + "` (");
for (int j = 0; j < table.FieldCount; j++)
{
if (j > 0)
{
sb.Append(", ");
}
sb.Append("`" + table.Fields[j].Name + "` " + MySQL_MergeDbType(table.Fields[j].DbType, table.Fields[j].Length) + (table.Fields[j].IsPRIMARY_KEY ? " PRIMARY KEY" : "") + (table.Fields[j].IsNOT_NULL ? " NOT NULL" : " NULL") + " " + MySQL_MergeDefaultValue(table.Fields[j].DbType, table.Fields[j].DefaultValue));
if (table.Fields[j].IsAUTO_INCREMENT)
{
sb.Append(" AUTO_INCREMENT");
}
}
if (table.Indexs.Count > 0)
{
foreach (Model.Index index in table.Indexs)
{
sb.Append(", ");
sb.Append("KEY " + index.Name.Replace("[", "`").Replace("]", "`") + " " + index.Body.Replace("[", "`").Replace("]", "`"));
}
}
if (string.Compare(table.Name, "T_Products", true) == 0)
{
sb.AppendLine(") ENGINE=MyISAM DEFAULT CHARSET=utf8;");
}
else
{
sb.AppendLine(") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
}
}
#endregion
#region 升迁数据
if (isWithData)
{
for (int i = 0; i < model.Tables.Count; i++)
{
Table table = model.Tables[i];
cmd_sqlite.CommandText = "select * from " + table.Name;
dr_sqlite = cmd_sqlite.ExecuteReader();
if (dr_sqlite.HasRows)
{
sb.AppendLine("\r\nLOCK TABLES `" + table.Name + "` WRITE;");
int no = 0;
while (dr_sqlite.Read())
{
sb.Append((no == 0) ? ("INSERT INTO `" + table.Name + "` VALUES ") : ",");
no++;
sb.Append("(");
for (int j = 0; j < table.FieldCount; j++)
{
sb.Append((j == 0) ? "" : ", ");
int quotesType = MySQL_QuotesDbType(table.Fields[j].DbType);
string value = "";
bool isDBNull = false;
// 此 try 是防止 System.Convert.IsDBNull(dr_sqlite[j]) 这句异常，当 SQLite 中插入了异常数据时，这句会异常，很难排查。
try
{
isDBNull = System.Convert.IsDBNull(dr_sqlite[j]);
}
catch
{
isDBNull = true;
}
if (isDBNull)
{
value = "NULL";
}
else
{
if (quotesType == 0)
{
value = Shove.Convert.StrToDouble(dr_sqlite[j].ToString(), 0).ToString();
}
else if (quotesType == 1)
{
value = Shove.Convert.StrToBool(dr_sqlite[j].ToString(), false) ? "1" : "0";
}
else
{
if ((table.Name == "T_WXUsers") && (table.Fields[j].Name == "Name"))
{
value = "'" + String.ConvertEncoding(dr_sqlite[j].ToString(), Encoding.Default, Encoding.UTF8).Replace("\\", "\\\\").Replace("'", "\\'").Replace("\"", "\\\"").Replace("\r\n", "\\r\\n") + "'";
}
else
{
value = "'" + dr_sqlite[j].ToString().Replace("\\", "\\\\").Replace("'", "\\'").Replace("\"", "\\\"").Replace("\r\n", "\\r\\n") + "'";
//value = "'" + Regex.Escape(dr_sqlite[j].ToString()) + "'";
}
}
}
sb.Append(value);
}
sb.Append(")");
}
sb.AppendLine(";");
sb.AppendLine("UNLOCK TABLES;");
}
dr_sqlite.Close();
}
}
#endregion
#region 创建 SQLite 中存在，而 MySQL 中不存在的时间类函数
sb.AppendLine();
sb.AppendLine("DROP FUNCTION IF EXISTS `strftime`;");
//sb.AppendLine("DELIMITER ;;");
sb.AppendLine("CREATE FUNCTION `strftime`(`format` varchar(100),`timestring` datetime) RETURNS varchar(1000) CHARSET utf8");
sb.AppendLine("BEGIN");
sb.AppendLine(" DECLARE result VARCHAR(1000);");
sb.AppendLine(" DECLARE len INT;");
sb.AppendLine(" DECLARE i INT;");
sb.AppendLine(" DECLARE format_start BIT;");
sb.AppendLine(" DECLARE current_char VARCHAR(1);");
sb.AppendLine("");
sb.AppendLine(" SET result = '';");
sb.AppendLine(" SET format_start = 0;");
sb.AppendLine(" SET len = LENGTH(format);");
sb.AppendLine(" SET i = 1;");
sb.AppendLine("");
sb.AppendLine(" LOOP1: WHILE i <= len DO");
sb.AppendLine(" SET current_char = SUBSTR(format, i, 1);");
sb.AppendLine("");
sb.AppendLine(" IF format_start = 0 THEN");
sb.AppendLine(" IF current_char <> '%' THEN");
sb.AppendLine(" SET result = CONCAT(result, current_char);");
sb.AppendLine(" ELSE");
sb.AppendLine(" SET format_start = 1;");
sb.AppendLine(" END IF;");
sb.AppendLine("");
sb.AppendLine(" SET i = i + 1;");
sb.AppendLine(" ITERATE LOOP1;");
sb.AppendLine(" END IF;");
sb.AppendLine("");
sb.AppendLine(" SET format_start = 0;");
sb.AppendLine("");
sb.AppendLine(" IF current_char LIKE BINARY 'Y' THEN");
sb.AppendLine(" SET result = CONCAT(result, YEAR(timestring));");
sb.AppendLine(" ELSEIF current_char LIKE BINARY 'm' THEN");
sb.AppendLine(" SET result = CONCAT(result, MONTH(timestring));");
sb.AppendLine(" ELSEIF current_char LIKE BINARY 'd' THEN");
sb.AppendLine(" SET result = CONCAT(result, DAYOFMONTH(timestring));");
sb.AppendLine(" ELSEIF current_char LIKE BINARY 'H' THEN");
sb.AppendLine(" SET result = CONCAT(result, HOUR(timestring));");
sb.AppendLine(" ELSEIF current_char LIKE BINARY 'M' THEN");
sb.AppendLine(" SET result = CONCAT(result, MINUTE(timestring));");
sb.AppendLine(" ELSEIF current_char LIKE BINARY 'S' THEN");
sb.AppendLine(" SET result = CONCAT(result, SECOND(timestring));");
sb.AppendLine(" ELSEIF current_char LIKE BINARY 'j' THEN");
sb.AppendLine(" SET result = CONCAT(result, DAYOFYEAR(timestring));");
sb.AppendLine(" ELSEIF current_char LIKE BINARY 'f' THEN");
sb.AppendLine(" SET result = CONCAT(result, SECOND(timestring), '.000');");
sb.AppendLine(" ELSEIF current_char LIKE BINARY 's' THEN");
sb.AppendLine(" SET result = CONCAT(result, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP('1970-01-01 0:0:0'));");
sb.AppendLine(" ELSEIF current_char LIKE BINARY 'w' THEN");
sb.AppendLine(" SET result = CONCAT(result, WEEKDAY(timestring) + 1);");
sb.AppendLine(" ELSEIF current_char LIKE BINARY 'W' THEN");
sb.AppendLine(" SET result = CONCAT(result, WEEK(timestring));");
sb.AppendLine(" ELSE");
sb.AppendLine(" SET result = CONCAT(result, current_char);");
sb.AppendLine(" END IF;");
sb.AppendLine("");
sb.AppendLine(" SET i = i + 1;");
sb.AppendLine(" END WHILE LOOP1;");
sb.AppendLine("");
sb.AppendLine(" RETURN result;");
sb.AppendLine("END");
sb.AppendLine(";");
//sb.AppendLine(";;");
//sb.AppendLine("DELIMITER ;");
sb.AppendLine("");
#endregion
#region 升迁视图(忽略视图依赖关系的方式)
if (ignoreViewRelyon && (model.Views.Count > 0))
{
sb.AppendLine();
for (int i = 0; i < model.Views.Count; i++)
{
string ViewName = model.Views[i].Name;
sb.AppendLine();
sb.AppendLine("DROP VIEW IF EXISTS `" + ViewName + "`;");
sb.AppendLine(MySQL_ReplaceViewKeyword(model.Views[i].Body));
}
}
#endregion
//IO.File.WriteFile("C:\\aaaa\\shovemysql.sql", sb.ToString(), Encoding.UTF8);
#region 执行命令
trans = conn_t.BeginTransaction();
cmd_mysql.Transaction = trans;
cmd_mysql.CommandText = sb.ToString();
try
{
cmd_mysql.ExecuteNonQuery();
}
catch (Exception e)
{
trans.Rollback();
conn_s.Close();
conn_t.Close();
description = "升迁数据库过程中发生了错误：" + e.Message;
return(false);
}
#endregion
#region 升迁视图(第二种方式，需要处理视图依赖关系的方式，与第一种方式互斥)
if ((!ignoreViewRelyon) && (model.Views.Count > 0))
{
IList <View> RemainViews = new List <View>();
IList <View> RemainViews_2 = new List <View>();
for (int i = 0; i < model.Views.Count; i++)
{
string ViewName = model.Views[i].Name;
string CmdString = "DROP VIEW IF EXISTS `" + ViewName + "`;\r\n";
CmdString += MySQL_ReplaceViewKeyword(model.Views[i].Body);
cmd_mysql.CommandText = CmdString;
try
{
cmd_mysql.ExecuteNonQuery();
}
catch
{
// 记录下错误的视图，第一轮结束后，再创建一次。因为有视图嵌套的情况
RemainViews.Add(model.Views[i]);
}
}
for (int i = 0; i < RemainViews.Count; i++)
{
string ViewName = RemainViews[i].Name;
string CmdString = "DROP VIEW IF EXISTS `" + ViewName + "`;\r\n";
CmdString += MySQL_ReplaceViewKeyword(RemainViews[i].Body);
cmd_mysql.CommandText = CmdString;
try
{
cmd_mysql.ExecuteNonQuery();
}
catch
{
// 记录下错误的视图，第二轮结束后，再创建一次。因为有视图嵌套的情况
RemainViews_2.Add(RemainViews[i]);
}
}
for (int i = 0; i < RemainViews_2.Count; i++)
{
string ViewName = RemainViews_2[i].Name;
string CmdString = "DROP VIEW IF EXISTS `" + ViewName + "`;\r\n";
CmdString += MySQL_ReplaceViewKeyword(RemainViews_2[i].Body);
cmd_mysql.CommandText = CmdString;
try
{
cmd_mysql.ExecuteNonQuery();
}
catch (Exception e)
{
trans.Rollback();
conn_s.Close();
conn_t.Close();
description = "更新视图“" + ViewName + "”发生错误：" + e.Message;
return(false);
}
}
}
#endregion
trans.Commit();
conn_s.Close();
conn_t.Close();
return(true);
}
/// <summary>
/// SQLite 转换到 MSSQL
/// </summary>
/// <param name="SQLite_ConnectionString">源 SQLite 数据库连接串</param>
/// <param name="MSSQL_ConnectionString">目标 SQLServer 数据库连接串</param>
/// <param name="IsWithData">是否携带所有的数据进行转换</param>
/// <param name="ignoreViewRelyon">是否忽略视图依赖关系</param>
/// <param name="Description">错误描述</param>
/// <returns></returns>
public bool SQLiteToMSSQL(string SQLite_ConnectionString, string MSSQL_ConnectionString, bool IsWithData, bool ignoreViewRelyon, ref string Description)
{
Description = "";
#region 连接数据库 & 读取SQLite 结构到 Model
if (Path.IsPathRooted(SQLite_ConnectionString))
{
SQLite_ConnectionString = "data source=" + SQLite_ConnectionString + ";Version=3";
}
SQLiteConnection conn_s = Database.DatabaseAccess.CreateDataConnection <SQLiteConnection>(SQLite_ConnectionString);
if ((conn_s == null) || (conn_s.State != ConnectionState.Open))
{
Description = "连接源数据库发生错误，请检查网站源数据库文件(基本数据库文件)";
return(false);
}
Model.Database model = SQLiteToModel(conn_s, ignoreViewRelyon);
if (model == null)
{
conn_s.Close();
Description = "从原数据中读取表、视图结构发生错误";
return(false);
}
SqlConnection conn_t = Database.DatabaseAccess.CreateDataConnection <SqlConnection>(MSSQL_ConnectionString);
if ((conn_t == null) || (conn_t.State != ConnectionState.Open))
{
conn_s.Close();
Description = "连接目标数据库发生错误，请检查连接字符串";
return(false);
}
#endregion
SqlTransaction trans = null;
SqlCommand cmd_mssql = new SqlCommand();
cmd_mssql.CommandTimeout = 600;
cmd_mssql.Connection = conn_t;
StringBuilder sb = new StringBuilder();
SQLiteCommand cmd_sqlite = new SQLiteCommand();
cmd_sqlite.Connection = conn_s;
SQLiteDataReader dr_sqlite = null;
#region 升迁表、索引
for (int i = 0; i < model.Tables.Count; i++)
{
Table table = model.Tables[i];
sb.AppendLine("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'" + table.Name + "') AND type in (N'U')) DROP TABLE " + table.Name + ";");
sb.Append("CREATE TABLE " + table.Name + " (");
for (int j = 0; j < table.FieldCount; j++)
{
if (j > 0)
{
sb.Append(", ");
}
sb.Append("[" + table.Fields[j].Name + "] " + MSSQL_MergeDbType(table.Fields[j].DbType, table.Fields[j].Length) + (table.Fields[j].IsAUTO_INCREMENT ? " IDENTITY(1,1)" : "") + (table.Fields[j].IsPRIMARY_KEY ? " PRIMARY KEY" : "") + (table.Fields[j].IsNOT_NULL ? " NOT NULL" : " NULL") + " " + MSSQL_MergeDefaultValue(table.Fields[j].DbType, table.Fields[j].DefaultValue));
}
sb.AppendLine(");");
foreach (Index index in table.Indexs)
{
sb.AppendLine("CREATE INDEX " + index.Name + " on " + table.Name + " " + index.Body + ";");
}
sb.AppendLine();
}
#endregion
#region 升迁数据
if (IsWithData)
{
for (int i = 0; i < model.Tables.Count; i++)
{
// 为 Varchar(Max) 设置属性
//if (CmdString.ToUpper().Contains("(MAX)"))
//{
// Cmd.CommandText = "exec sp_tableoption [" + table.Name + "], 'large value types out of row', 'on'";
// Cmd.ExecuteNonQuery();
//}
Table table = model.Tables[i];
cmd_sqlite.CommandText = "select * from " + table.Name;
dr_sqlite = cmd_sqlite.ExecuteReader();
if (dr_sqlite.HasRows)
{
bool HasIDENTITY = MSSQL_GetHasIDENTITY(table);
if (HasIDENTITY)
{
sb.AppendLine("SET IDENTITY_INSERT [" + table.Name + "] ON;");
}
while (dr_sqlite.Read())
{
string ColumnList = "INSERT INTO [" + table.Name + "] (";
string ValueList = ") VALUES (";
for (int j = 0; j < table.FieldCount; j++)
{
ColumnList += ((j == 0) ? "" : ", ");
ValueList += ((j == 0) ? "" : ", ");
int quotesType = MSSQL_QuotesDbType(table.Fields[j].DbType);
string value = "";
bool isDBNull = false;
// 此 try 是防止 System.Convert.IsDBNull(dr_sqlite[j]) 这句异常，当 SQLite 中插入了异常数据时，这句会异常，很难排查。
try
{
isDBNull = System.Convert.IsDBNull(dr_sqlite[j]);
}
catch
{
isDBNull = true;
}
if (isDBNull)
{
value = "NULL";
}
else
{
if (quotesType == 0)
{
value = Shove._Convert.StrToDouble(dr_sqlite[j].ToString(), 0).ToString();
}
else if (quotesType == 1)
{
value = Shove._Convert.StrToBool(dr_sqlite[j].ToString(), false) ? "1" : "0";
}
else
{
value = "'" + dr_sqlite[j].ToString().Replace("'", "''").Replace(((char)0xA0).ToString(), " ") + "'";
}
}
ColumnList += "[" + table.Fields[j].Name + "]";
ValueList += value;
}
sb.AppendLine(ColumnList + ValueList + ");");
}
if (HasIDENTITY)
{
sb.AppendLine("SET IDENTITY_INSERT [" + table.Name + "] OFF;");
}
sb.AppendLine();
}
dr_sqlite.Close();
}
sb.AppendLine();
}
#endregion
//Shove._IO.File.WriteFile("C:\\aaaa\\shovemssql_1.sql", sb.ToString(), Encoding.UTF8);
#region 执行第一次命令
trans = conn_t.BeginTransaction();
cmd_mssql.Transaction = trans;
cmd_mssql.CommandText = sb.ToString();
try
{
cmd_mssql.ExecuteNonQuery();
}
catch (Exception e)
{
trans.Rollback();
conn_s.Close();
conn_t.Close();
Description = "升迁数据库过程中发生了错误(1)：" + e.Message;
return(false);
}
trans.Commit();
#endregion
sb = new StringBuilder();
#region 创建 SQLite 中存在，而 MSSQL 中不存在的时间类函数
if (!Shove.Database.MSSQL.ExecuteSQLScript(MSSQL_ConnectionString, Properties.Resources.SQLiteToMSSQL_VIEW))
{
conn_s.Close();
conn_t.Close();
Description = "升迁数据库过程中发生了错误：创建模拟 SQLite 时间类视图遇到错误。";
return(false);
}
#endregion
#region 升迁视图(忽略视图依赖关系的方式)
if (ignoreViewRelyon && (model.Views.Count > 0))
{
for (int i = 0; i < model.Views.Count; i++)
{
string ViewName = model.Views[i].Name;
sb.AppendLine();
sb.AppendLine("IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[" + ViewName + "]')) DROP VIEW [" + ViewName + "]\r\nGO");
sb.AppendLine(MSSQL_ReplaceViewKeyword(model.Views[i].Body) + "\r\nGO");
}
//Shove._IO.File.WriteFile("C:\\aaaa\\shovemssql_2.sql", sb.ToString(), Encoding.UTF8);
#region 执行第二次命令
if (!Shove.Database.MSSQL.ExecuteSQLScript(MSSQL_ConnectionString, sb.ToString()))
{
conn_s.Close();
conn_t.Close();
Description = "升迁数据库过程中发生了错误(2)：以忽略依赖关系创建视图遇到错误。";
return(false);
}
#endregion
}
#endregion
#region 升迁视图(第二种方式，需要处理视图依赖关系的方式，与第一种方式互斥)
trans = conn_t.BeginTransaction();
cmd_mssql.Transaction = trans;
if ((!ignoreViewRelyon) && (model.Views.Count > 0))
{
IList <View> RemainViews = new List <View>();
IList <View> RemainViews_2 = new List <View>();
for (int i = 0; i < model.Views.Count; i++)
{
string ViewName = model.Views[i].Name;
cmd_mssql.CommandText = "IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[" + ViewName + "]')) BEGIN DROP VIEW [" + ViewName + "] END";
try
{
cmd_mssql.ExecuteNonQuery();
cmd_mssql.CommandText = MSSQL_ReplaceViewKeyword(model.Views[i].Body);
cmd_mssql.ExecuteNonQuery();
}
catch
{
// 记录下错误的视图，第一轮结束后，再创建一次。因为有视图嵌套的情况
RemainViews.Add(model.Views[i]);
}
}
for (int i = 0; i < RemainViews.Count; i++)
{
string ViewName = RemainViews[i].Name;
cmd_mssql.CommandText = "IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[" + ViewName + "]')) BEGIN DROP VIEW [" + ViewName + "] END";
try
{
cmd_mssql.ExecuteNonQuery();
cmd_mssql.CommandText = MSSQL_ReplaceViewKeyword(RemainViews[i].Body);
cmd_mssql.ExecuteNonQuery();
}
catch
{
// 记录下错误的视图，第二轮结束后，再创建一次。因为有视图嵌套的情况
RemainViews_2.Add(RemainViews[i]);
}
}
for (int i = 0; i < RemainViews_2.Count; i++)
{
string ViewName = RemainViews_2[i].Name;
cmd_mssql.CommandText = "IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[" + ViewName + "]')) BEGIN DROP VIEW [" + ViewName + "] END";
try
{
cmd_mssql.ExecuteNonQuery();
cmd_mssql.CommandText = MSSQL_ReplaceViewKeyword(RemainViews_2[i].Body);
cmd_mssql.ExecuteNonQuery();
}
catch (Exception e)
{
trans.Rollback();
conn_s.Close();
conn_t.Close();
Description = "更新视图“" + ViewName + "”发生错误：" + e.Message;
return(false);
}
}
}
#endregion
trans.Commit();
conn_s.Close();
conn_t.Close();
return(true);
}
//private Regex regex_view_relyon = new Regex(@"(?<L0>[^(]+?)[(](?<L1>[\d]+?)[)]", RegexOptions.Compiled | RegexOptions.IgnoreCase);
//private string[] SQLKEYWOTDS = new string[] { " LEFT ", " GROUP ", " WHERE " };
/// <summary>
/// SQLite 数据库解析到 Model
/// </summary>
/// <param name="conn"></param>
/// <param name="ignoreViewRelyon">忽略视图依赖关系的分析</param>
/// <returns></returns>
private Model.Database SQLiteToModel(SQLiteConnection conn, bool ignoreViewRelyon)
{
if ((conn == null) || (conn.State != ConnectionState.Open))
{
return(null);
}
Shove.DatabaseFactory.Convert.Model.Database model = new Shove.DatabaseFactory.Convert.Model.Database();
#region 分析 Table、Index
SQLiteDataAdapter da = new SQLiteDataAdapter("select name, sql from sqlite_master where type = 'table' order by name;", conn);
DataTable dt = new DataTable();
da.Fill(dt);
SQLiteDataAdapter da2 = new SQLiteDataAdapter("select tbl_name, name, sql from sqlite_master where type = 'index' order by rootpage;", conn);
DataTable dt2 = new DataTable();
da2.Fill(dt2);
if (dt != null)
{
Regex regex_index = new Regex(@"CREATE[\s\t\r\n\v\f]+?INDEX[\s\t\r\n\v\f]+?(?<L0>[^]]+?[]])[\s\t\r\n\v\f]+?on[^(]+?(?<L1>[(][^)]+?[)])", RegexOptions.Compiled | RegexOptions.IgnoreCase);
foreach (DataRow dr in dt.Rows)
{
string TableName = dr["name"].ToString();
if ((TableName.ToLower() == "sqlite_master") || (TableName.ToLower() == "sqlite_temp_master") || (TableName.ToLower() == "sqlite_sequence"))
{
continue;
}
IList <Field> cols = SplitColumns(conn, TableName, dr["sql"].ToString());
if ((cols == null) || (cols.Count < 1))
{
continue;
}
Table table = new Table(TableName);
for (int i = 0; i < cols.Count; i++)
{
table.AddField(cols[i]);
}
DataRow[] drs = dt2.Select("tbl_name='" + TableName + "'");
foreach (DataRow dr2 in drs)
{
if ((dr2["sql"] != null) && (dr2["sql"].ToString().Trim() != ""))
{
Match m = regex_index.Match(dr2["sql"].ToString());
if (m.Success)
{
Index index = new Index(m.Groups["L0"].Value, m.Groups["L1"].Value);
table.AddIndex(index);
}
}
}
model.AddTable(table);
}
}
#endregion
#region 分析 View
SQLiteCommand Cmd = new SQLiteCommand("select name, sql from sqlite_master where type = 'view' order by name desc;", conn);
SQLiteDataReader dr3 = Cmd.ExecuteReader();
if (dr3 != null)
{
while (dr3.Read())
{
string ViewName = dr3["name"].ToString();
if (ViewName.StartsWith("`") || ViewName.StartsWith("[") || ViewName.StartsWith("\""))
{
ViewName = ViewName.Substring(1, ViewName.Length - 2);
}
model.AddView(new View(ViewName, FilterViewStatement(dr3["sql"].ToString())));
}
}
dr3.Close();
#endregion
#region 分析视图依赖关系
//if (!ignoreViewRelyon)
//{
// for (int i = 0; i < model.Views.Count; i++)
// {
// string viewName = model.Views[i].Name;
// for (int j = 0; j < model.Views.Count; j++)
// {
// if (j == i)
// {
// continue;
// }
// }
// }
//}
#endregion
return(model);
}
