SQLServerToSQLite 程序代码的分析翻译及学习(二、根据数据库描述对象创建SQLite数据库)

来源：互联网发布：php rides 缓存编辑：程序博客网时间：2024/06/06 02:30

在上一篇中，我们将待转换的SQLserver数据库以自定义对象的形式提取出来了，下一步就是：根据数据库描述对象创建SQLite数据库。原理其实很简单就是拼接一大堆符合SQLite语法的建表语句。

2.1首先我们先要创建SQLite数据库连接字符串：

/// <summary>/// 创建SQLite数据库连接字符串/// </summary>/// <param name="sqlitePath">SQlite文件路径</param>/// <returns>SQLite数据库连接字符串string</returns>private static string CreateSQLiteConnectionString(string sqlitePath, string password){    SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder();    builder.DataSource = sqlitePath;    if (password != null)        builder.Password = password;    builder.PageSize = 4096;    builder.UseUTF16Encoding = true;    string connstring = builder.ConnectionString;    return connstring;}

2.2之后我们就要根据数据表对象模型创建SQLite数据表了：

2.2.1创建数据表字段：

        /// <summary>        /// 创建数据字段的语句        /// </summary>        /// <param name="col">字段对象</param>        /// <returns>创建字段的语句</returns>        private static string BuildColumnStatement(ColumnSchema col, TableSchema ts, ref bool pkey)        {            StringBuilder sb = new StringBuilder();            sb.Append("\t[" + col.ColumnName + "]\t");            // 对标识字段进行特殊处理            if (col.IsIdentity)            {                if (ts.PrimaryKey.Count == 1 && (col.ColumnType == "tinyint" || col.ColumnType == "int" || col.ColumnType == "smallint" ||                    col.ColumnType == "bigint" || col.ColumnType == "integer"))                {                    sb.Append("integer PRIMARY KEY AUTOINCREMENT");                    pkey = true;                }                else                    sb.Append("integer");            }            else            {                if (col.ColumnType == "int")                    sb.Append("integer");                else                {                    sb.Append(col.ColumnType);                }                if (col.Length > 0)                    sb.Append("(" + col.Length + ")");            }            if (!col.IsNullable)                sb.Append(" NOT NULL");            if (col.IsCaseSensitivite.HasValue && !col.IsCaseSensitivite.Value)                sb.Append(" COLLATE NOCASE");            string defval = StripParens(col.DefaultValue);            defval = DiscardNational(defval);            _log.Debug("DEFAULT VALUE BEFORE [" + col.DefaultValue + "] AFTER [" + defval + "]");            if (defval != string.Empty && defval.ToUpper().Contains("GETDATE"))            {                _log.Debug("converted SQL Server GETDATE() to CURRENT_TIMESTAMP for column [" + col.ColumnName + "]");                sb.Append(" DEFAULT (CURRENT_TIMESTAMP)");            }            else if (defval != string.Empty && IsValidDefaultValue(defval))                sb.Append(" DEFAULT " + defval);            return sb.ToString();        }

2.2.2创建数据表创建索引：

        /// <summary>        /// 创建索引的语句        /// </summary>        /// <param name="tableName">数据表名称</param>        /// <param name="indexSchema">表索引对象那个t</param>        /// <returns>返回创建索引的语句</returns>        private static string BuildCreateIndex(string tableName, IndexSchema indexSchema)        {            StringBuilder sb = new StringBuilder();            sb.Append("CREATE ");            if (indexSchema.IsUnique)                sb.Append("UNIQUE ");            sb.Append("INDEX [" + tableName + "_" + indexSchema.IndexName + "]\n");            sb.Append("ON [" + tableName + "]\n");            sb.Append("(");            for (int i = 0; i < indexSchema.Columns.Count; i++)            {                sb.Append("[" + indexSchema.Columns[i].ColumnName + "]");                if (!indexSchema.Columns[i].IsAscending)                    sb.Append(" DESC");                if (i < indexSchema.Columns.Count - 1)                    sb.Append(", ");            } // for            sb.Append(")");            return sb.ToString();        }

2.2.2创建生成数据表语句：

/// <summary>/// 返回创建表的语句/// table schema object./// </summary>/// <param name="ts">数据表描述对象</param>/// <returns>创建特定表的描述语句</returns>private static string BuildCreateTableQuery(TableSchema ts){    StringBuilder sb = new StringBuilder();    sb.Append("CREATE TABLE [" + ts.TableName + "] (\n");    bool pkey = false;    for (int i = 0; i < ts.Columns.Count; i++)    {        ColumnSchema col = ts.Columns[i];        string cline = BuildColumnStatement(col, ts, ref pkey);        sb.Append(cline);        if (i < ts.Columns.Count - 1)            sb.Append(",\n");    }    //<span style="font-family:Arial, Helvetica, sans-serif;">添加主键</span>    if (ts.PrimaryKey != null && ts.PrimaryKey.Count > 0 & !pkey)    {        sb.Append(",\n");        sb.Append("    PRIMARY KEY (");        for (int i = 0; i < ts.PrimaryKey.Count; i++)        {            sb.Append("[" + ts.PrimaryKey[i] + "]");            if (i < ts.PrimaryKey.Count - 1)                sb.Append(", ");        } // for        sb.Append(")\n");    }    else        sb.Append("\n");    //<span style="font-family:Arial, Helvetica, sans-serif;">添加外键</span>    if (ts.ForeignKeys.Count > 0)    {        sb.Append(",\n");        for (int i = 0; i < ts.ForeignKeys.Count; i++)        {            ForeignKeySchema foreignKey = ts.ForeignKeys[i];            string stmt = string.Format("    FOREIGN KEY ([{0}])\n        REFERENCES [{1}]([{2}])",                        foreignKey.ColumnName, foreignKey.ForeignTableName, foreignKey.ForeignColumnName);            sb.Append(stmt);            if (i < ts.ForeignKeys.Count - 1)                sb.Append(",\n");        } // for    }    sb.Append("\n");    sb.Append(");\n");    //<span style="font-family:Arial, Helvetica, sans-serif;">创建相关索引</span>    if (ts.Indexes != null)    {        for (int i = 0; i < ts.Indexes.Count; i++)        {            string stmt = BuildCreateIndex(ts.TableName, ts.Indexes[i]);            sb.Append(stmt + ";\n");        }     }     string query = sb.ToString();    return query;}再一步就是：从SQLServer数据库中读取行来填充创建好的SQLite数据库了。原理相同，都是拼接SQL，这里就不在赘述了，呵呵，有兴趣的同学可以看源代码。道理都是一样的，就是拼接原生的SQL命令语句，此外还有很多小细节的部分，比如匹配好SQLServer和SQLite语法不同的部分，LironLevi考虑的很周全，请看下面的代码，就是例证：/// 小的验证方法,以确保我们不要错过任何没有的例外/// </summary>/// <param name="dataType">待验证的数据类型</param>private static void ValidateDataType(string dataType){    if (dataType == "int" || dataType == "smallint" ||        dataType == "bit" || dataType == "float" ||        dataType == "real" || dataType == "nvarchar" ||        dataType == "varchar" || dataType == "timestamp" ||        dataType == "varbinary" || dataType == "image" ||        dataType == "text" || dataType == "ntext" ||        dataType == "bigint" ||        dataType == "char" || dataType == "numeric" ||        dataType == "binary" || dataType == "smalldatetime" ||        dataType == "smallmoney" || dataType == "money" ||        dataType == "tinyint" || dataType == "uniqueidentifier" ||        dataType == "xml" || dataType == "sql_variant" || dataType == "datetime2" || dataType == "date" || dataType == "time" ||        dataType == "decimal" || dataType == "nchar" || dataType == "datetime")        return;    throw new ApplicationException("Validation failed for data type [" + dataType + "]");}

还有这个默认值的匹配

/// <summary>/// 更多的默认值调整状况/// </summary>/// <param name="val">调整值</param>/// <returns>返回合适的值得字符串</returns>private static string AdjustDefaultValue(string val){    if (val == null || val == string.Empty)        return val;    Match m = _defaultValueRx.Match(val);    if (m.Success)        return m.Groups[1].Value;    return val;}

有空的话，我会将LironLevi的这个程序重新的重构成为一个数据库转换帮助类库，就当练习着玩了,,看完了SQLServerToSQLite程序的代码，感觉很明显，以色列的
这位大牛前辈LironLevi只想着马上实现功能，并没有做很好的扩展，也没有整理项目的代码，致使这个程序的代码看起来相当的凌乱，好了，就说到这里了~~~

0 0