Beispiel #1
0
        /// <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);
        }
Beispiel #2
0
        /// <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);
        }
Beispiel #3
0
        //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);
        }