SQLServerToSQLite 程序代码的分析翻译及学习(一、SQLServer数据库对象化提取)

来源：互联网发布：怎样在淘宝编辑：程序博客网时间：2024/06/09 01:01

本人是个小菜，转行当码农有一小段时间了，工作一段时间后，感觉提高水平的最好方法还是阅读大量的优质代码，以下是我对以色列大牛名字叫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, handler, viewFailureHandler, createViews);
三、从SQLServer数据库中读取行数据来填充这个新创建的SQLite数据库
CopySqlServerRowsToSQLiteDB(sqlConnString, sqlitePath, ds.Tables, password, handler);
四、根据外键约束添加表之间的触发对象
AddTriggersForForeignKeys(sqlitePath, ds.Tables, password, 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上，感兴趣的同学可以学习一下，改进改进就可以弄个数据库通用转换类库哦，，，共同进步嘛。有空我会把剩下的步骤详细的写在后面的博文中，欢迎菜菜们学习，共同进步~~~

0 0