加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 站长学院 > MsSql教程 > 正文

SQLServerToSQLite 程序代码的分析翻译及学习(一、SQLServer数据

发布时间：2020-12-12 12:44:42 所属栏目：MsSql教程 来源：网络整理
导读：? ? ? ? ?本人是个小菜，转行当码农有一小段时间了，工作一段时间后，感觉提高水平的最好方法还是阅读大量的优质代码，以下是我对 以色列大牛名字叫LironLevi写的开源程序 SQLServerToSQLiteDBConverter进行的代码分析，相信各位大牛牛们已经看过了源代码，

? ? ? ? ?本人是个小菜，转行当码农有一小段时间了，工作一段时间后，感觉提高水平的最好方法还是阅读大量的优质代码，以下是我对以色列大牛名字叫LironLevi写的开源程序SQLServerToSQLiteDBConverter进行的代码分析，相信各位大牛牛们已经看过了源代码，也有许多跟我一样只是用过但没仔细读过的菜菜们，闲言少叙了，步入正题了：?

? ? ? 读一段代码，先要弄清楚开发者的意图，Liron Levi为何要写这个程序，很简单，给懒得学SQLite的人预备：直接设计出SQLServer版本的库，一转换完事了，或者为方便转移和管理从母库中提取的部分数据。其二，还要知晓程序的依赖，即编写该程序除了相应编程语言的语法外，还需哪些技术，这个程序很明显需要了解SQLserver和SQLite的数据库的语法区别。其三，则是众所周知的面向对象化了，Liron Levi把数据库转换相关信息进行了实体对象化分类如下：

    /// 数据库字段类（描述数据库字段）
    public class ColumnSchema
    {
        public string ColumnName; public string ColumnType; public int Length;
        public bool IsNullable; public string DefaultValue; public bool IsIdentity;
        public bool? IsCaseSensitivite = null;
    }
    /// 索引描述
    public class IndexSchema
    {
        public string IndexName; public bool IsUnique; public List<IndexColumn> Columns;
    }
    /// 索引字段描述
    public class IndexColumn
    {
        public string ColumnName; public bool IsAscending;
    }
    /// 外键描述
    public class ForeignKeySchema
   {
	public string TableName;public string ColumnName; public string ForeignTableName;  public string ForeignColumnName;
	public bool CascadeOnDelete; public bool IsNullable;
    }
    /// 数据表描述
    public class TableSchema
    {
        public string TableName; public string TableSchemaName; public List<ColumnSchema> Columns;
        public List<string> PrimaryKey;public List<ForeignKeySchema> ForeignKeys; public List<IndexSchema> Indexes;
    }
    /// 视图描述
    public class ViewSchema
    {
        /// 视图的名称
        public string ViewName;
        /// 创建视图的声明语句
        public string ViewSQL;
    }
    /// 触发事件枚举
    public enum TriggerEvent
    {
        Delete,Update,Insert
    } 
    /// 触发类型
    public enum TriggerType
    {
        After,Before
    } 
    /// 触发描述
    public class TriggerSchema
    {
        public string Name; public TriggerEvent Event; public TriggerType Type;
        public string Body; public string Table;
    }
    /// 数据库描述
    public class DatabaseSchema
    {
        public List<TableSchema> Tables = new List<TableSchema>();
        public List<ViewSchema> Views = new List<ViewSchema>();
    }
实体类抽象出来了，剩下的就是思路了，思路很简单四个基本步骤：

一、从内存中读取SQLServer数据库创建数据库描述对象
DatabaseSchema ds = ReadSqlServerSchema(sqlConnString,handler,selectionHandler);
二、根据步骤一中生成的数据库描述对象创建这个SQLite数据库
CreateSQLiteDatabase(sqlitePath,ds,password,viewFailureHandler,createViews);
三、从SQLServer数据库中读取行数据来填充这个新创建的SQLite数据库
CopySqlServerRowsToSQLiteDB(sqlConnString,sqlitePath,ds.Tables,handler);
四、根据外键约束添加表之间的触发对象
?AddTriggersForForeignKeys(sqlitePath,handler);

下面对各个步骤进行实体方法的分析：

一、从内存中读取SQLServer数据库创建数据库描述对象

1、提取SqlServer的数据库名称：select distinct [name] from sysdatabases

2、从内存结构中读取SQLServer数据库的详细信息创建数据库描述对象：DatabaseSchema

SqlCommand cmd = new SqlCommand(@"select * from INFORMATION_SCHEMA.TABLES  where TABLE_TYPE = 'BASE TABLE'",conn);
2.1创建SQLServer数据表对象：

2.1.1创建字段对象：

TableSchema res = new TableSchema();
res.TableName = tableName;
res.TableSchemaName = tschma;
res.Columns = new List<ColumnSchema>();
SqlCommand cmd = new SqlCommand(@"SELECT COLUMN_NAME,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE," +
@" (columnproperty(object_id(TABLE_NAME),COLUMN_NAME,'IsIdentity')) AS [IDENT]," +
@"CHARACTER_MAXIMUM_LENGTH AS CSIZE " +
"FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + tableName + "' ORDER BY " +
"ORDINAL_POSITION ASC",conn);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
    object tmp = reader["COLUMN_NAME"];
    if (tmp is DBNull)
        continue;
    string colName = (string)reader["COLUMN_NAME"];

    tmp = reader["COLUMN_DEFAULT"];
    string colDefault;
    if (tmp is DBNull)
        colDefault = string.Empty;
    else
        colDefault = (string)tmp;
    tmp = reader["IS_NULLABLE"];
    bool isNullable = ((string)tmp == "YES");
    string dataType = (string)reader["DATA_TYPE"];
    bool isIdentity = false;
    if (reader["IDENT"] != DBNull.Value)
        isIdentity = ((int)reader["IDENT"]) == 1 ? true : false;
    int length = reader["CSIZE"] != DBNull.Value ? Convert.ToInt32(reader["CSIZE"]) : 0;

    ValidateDataType(dataType);
    //请注意,并不是所有的数据类型名称需要转换,因为SQLite建立搜索特定的字符串类型关联的类型名称。
    if (dataType == "timestamp")
        dataType = "blob";
    else if (dataType == "datetime" || dataType == "smalldatetime" || dataType == "date" || dataType == "datetime2" || dataType == "time")
        dataType = "datetime";
    else if (dataType == "decimal")
        dataType = "numeric";
    else if (dataType == "money" || dataType == "smallmoney")
        dataType = "numeric";
    else if (dataType == "binary" || dataType == "varbinary" ||
        dataType == "image")
        dataType = "blob";
    else if (dataType == "tinyint")
        dataType = "smallint";
    else if (dataType == "bigint")
        dataType = "integer";
    else if (dataType == "sql_variant")
        dataType = "blob";
    else if (dataType == "xml")
        dataType = "varchar";
    else if (dataType == "uniqueidentifier")
        dataType = "guid";
    else if (dataType == "ntext")
        dataType = "text";
    else if (dataType == "nchar")
        dataType = "char";
    if (dataType == "bit" || dataType == "int")
    {
        if (colDefault == "('False')")
            colDefault = "(0)";
        else if (colDefault == "('True')")
            colDefault = "(1)";
    }
    colDefault = FixDefaultValueString(colDefault);
    ColumnSchema col = new ColumnSchema();
    col.ColumnName = colName;
    col.ColumnType = dataType;
    col.Length = length;
    col.IsNullable = isNullable;
    col.IsIdentity = isIdentity;
    col.DefaultValue = AdjustDefaultValue(colDefault);
    res.Columns.Add(col);
} 
}
2.1.2找到主键信息 

SqlCommand cmd2 = new SqlCommand(@"EXEC sp_pkeys '" + tableName + "'",conn);
using (SqlDataReader reader = cmd2.ExecuteReader())
{
    res.PrimaryKey = new List<string>();
    while (reader.Read())
    {
        string colName = (string)reader["COLUMN_NAME"];
        res.PrimaryKey.Add(colName);
    } 
}
2.1.3找到索引信息 

SqlCommand cmd3 = new SqlCommand( @"exec sp_helpindex '" + tschma + "." + tableName + "'",conn);
using (SqlDataReader reader = cmd3.ExecuteReader())
{
    res.Indexes = new List<IndexSchema>();
    while (reader.Read())
    {
        string indexName = (string)reader["index_name"];
        string desc = (string)reader["index_description"];
        string keys = (string)reader["index_keys"];
        // 不加给主键增加索引
        if (desc.Contains("primary key")) continue;
        IndexSchema index = BuildIndexSchema(indexName,desc,keys);
        res.Indexes.Add(index);
    } 
}
2.1.4创建索引描述对象： 
/// <summary>
/// 创建索引描述对象(Read from SQL Server).
/// </summary>
/// <param name="indexName">索引名称</param>
/// <param name="desc">索引描述</param>
/// <param name="keys">索引的键</param>
/// <returns>索引的描述对象</returns>
private static IndexSchema BuildIndexSchema(string indexName,string desc,string keys)
{
    IndexSchema res = new IndexSchema();
    res.IndexName = indexName;
    // 判断是否是唯一索引
    string[] descParts = desc.Split(',');
    foreach (string p in descParts)
    {
        if (p.Trim().Contains("unique"))
        {
            res.IsUnique = true;
            break;
        }
    } // foreach
    // 检查键的排序规则
    res.Columns = new List<IndexColumn>();
    string[] keysParts = keys.Split(',');
    foreach (string p in keysParts)
    {
        Match m = _keyRx.Match(p.Trim());
        if (!m.Success)
        {
            throw new ApplicationException("Illegal key name [" + p + "] in index [" +indexName + "]");
        }
        string key = m.Groups[1].Value;
        IndexColumn ic = new IndexColumn();
        ic.ColumnName = key;
        if (m.Groups[2].Success)
            ic.IsAscending = false;
        else
            ic.IsAscending = true;
        res.Columns.Add(ic);
    }
    return res;
}
2.1.5创建外键约束描述对象： 
/// <summary>
/// 加一个外键描述对象 从一个特殊的对象中
/// </summary>
/// <param name="conn">SQLServer连接字符串</param>
/// <param name="ts">需要的数据表描述对象</param>
private static void CreateForeignKeySchema(SqlConnection conn,TableSchema ts)
{
    ts.ForeignKeys = new List<ForeignKeySchema>();
    SqlCommand cmd = new SqlCommand(
        @"SELECT " +
        @"  ColumnName = CU.COLUMN_NAME," +
        @"  ForeignTableName  = PK.TABLE_NAME," +
        @"  ForeignColumnName = PT.COLUMN_NAME," +
        @"  DeleteRule = C.DELETE_RULE," +
        @"  IsNullable = COL.IS_NULLABLE " +
        @"FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C " +
        @"INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME " +
        @"INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME " +
        @"INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME " +
        @"INNER JOIN " +
        @"  ( " +
        @"    SELECT i1.TABLE_NAME,i2.COLUMN_NAME " +
        @"    FROM  INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 " +
        @"    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME " +
        @"    WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' " +
        @"  ) " +
        @"PT ON PT.TABLE_NAME = PK.TABLE_NAME " +
        @"INNER JOIN INFORMATION_SCHEMA.COLUMNS AS COL ON CU.COLUMN_NAME = COL.COLUMN_NAME AND FK.TABLE_NAME = COL.TABLE_NAME " +
        @"WHERE FK.Table_NAME='" + ts.TableName + "'",conn);

    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            ForeignKeySchema fkc = new ForeignKeySchema();
            fkc.ColumnName = (string)reader["ColumnName"];
            fkc.ForeignTableName = (string)reader["ForeignTableName"];
            fkc.ForeignColumnName = (string)reader["ForeignColumnName"];
            fkc.CascadeOnDelete = (string)reader["DeleteRule"] == "CASCADE";
            fkc.IsNullable = (string)reader["IsNullable"] == "YES";
            fkc.TableName = ts.TableName;
            ts.ForeignKeys.Add(fkc);
        }
    }
}
2.1.6创建SQLServer视图描述对象： 
SqlCommand cmd = new SqlCommand(@"SELECT TABLE_NAME,VIEW_DEFINITION  from INFORMATION_SCHEMA.VIEWS",conn);
using (SqlDataReader reader = cmd.ExecuteReader())
{
    int count = 0;
    while (reader.Read())
    {
        ViewSchema vs = new ViewSchema();
        if (reader["TABLE_NAME"] == DBNull.Value) continue;
        if (reader["VIEW_DEFINITION"] == DBNull.Value) continue;
        vs.ViewName = (string)reader["TABLE_NAME"];
        vs.ViewSQL = (string)reader["VIEW_DEFINITION"];
        // 从所有视图定义中移除所有.dbo字符串
        vs.ViewSQL = removedbo.Replace(vs.ViewSQL,string.Empty);
        views.Add(vs);
        count++;
        CheckCancelled();
        handler(false,true,50 + (int)(count * 50.0 / views.Count),"Parsed view " + vs.ViewName);
        _log.Debug("parsed view schema for [" + vs.ViewName + "]");
    } 
}
好了步骤一就基本完成了， 步骤一的细节部分大家可以参考 Liron Levi的源代码（不过注释是英文的哦）,我也会把翻译过来的源代码优化一下传到CSDN上，感兴趣的同学可以学习一下，改进改进就可以弄个数据库通用转换类库哦，，，共同进步嘛。有空我会把剩下的步骤详细的写在后面的博文中，欢迎菜菜们学习，共同进步~~~

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同