private void OnTableSchemaReaderProgressChanged(TableSchema lastProcessed, int processed, int remaining)
{
var handler = TableSchemaReaderProgressChanged;
if (handler != null)
{
handler(this, new TableSchemaReaderProgressChangedEventArgs(lastProcessed, processed, remaining));
}
}
public static IList<TriggerSchema> GetForeignKeyTriggers(TableSchema dt)
{
IList<TriggerSchema> result = new List<TriggerSchema>();
foreach (ForeignKeySchema fks in dt.ForeignKeys)
{
result.Add(GenerateInsertTrigger(fks));
result.Add(GenerateUpdateTrigger(fks));
result.Add(GenerateDeleteTrigger(fks));
}
return result;
}
private static void AddTableTriggers(SQLiteConnection conn, TableSchema dt)
{
IList<TriggerSchema> triggers = TriggerBuilder.GetForeignKeyTriggers(dt);
foreach (TriggerSchema trigger in triggers)
{
SQLiteCommand cmd = new SQLiteCommand(WriteTriggerSchema(trigger), conn);
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Used when creating the CREATE TABLE DDL. Creates a single row
/// for the specified column.
/// </summary>
/// <param name="columnSchema">The column schema</param>
/// <returns>A single column line to be inserted into the general CREATE TABLE DDL statement</returns>
private static string BuildColumnStatement(ColumnSchema columnSchema, TableSchema tableSchema, ref bool pkey)
{
var sb = new StringBuilder();
sb.Append("\t[" + columnSchema.ColumnName + "]\t");
// Special treatment for IDENTITY columns
if (columnSchema.IsIdentity)
{
if (tableSchema.PrimaryKey.Count == 1 && (columnSchema.ColumnType == "tinyint" || columnSchema.ColumnType == "int" || columnSchema.ColumnType == "smallint" || columnSchema.ColumnType == "bigint" || columnSchema.ColumnType == "integer"))
{
sb.Append("integer PRIMARY KEY AUTOINCREMENT");
pkey = true;
}
else
{
sb.Append("integer");
}
}
else
{
sb.Append(columnSchema.ColumnType == "int" ? "integer" : columnSchema.ColumnType);
if (columnSchema.Length > 0)
{
sb.Append("(" + columnSchema.Length + ")");
}
}
if (!columnSchema.IsNullable)
{
sb.Append(" NOT NULL");
}
if (columnSchema.IsCaseSensitive.HasValue && !columnSchema.IsCaseSensitive.Value)
{
sb.Append(" COLLATE NOCASE");
}
string defval = StripParens(columnSchema.DefaultValue);
defval = DiscardNational(defval);
_log.Debug("DEFAULT VALUE BEFORE [" + columnSchema.DefaultValue + "] AFTER [" + defval + "]");
if (defval != string.Empty && defval.ToUpper().Contains("GETDATE"))
{
_log.Debug("converted SQL Server GETDATE() to CURRENT_TIMESTAMP for column [" + columnSchema.ColumnName + "]");
sb.Append(" DEFAULT (CURRENT_TIMESTAMP)");
}
else if (defval != string.Empty && IsValidDefaultValue(defval))
{
sb.Append(" DEFAULT " + defval);
}
return sb.ToString();
}
/// <summary>
/// returns the CREATE TABLE DDL for creating the SQLite table from the specified
/// table schema object.
/// </summary>
/// <param name="ts">The table schema object from which to create the SQL statement.</param>
/// <returns>CREATE TABLE DDL for the specified table.</returns>
private static string BuildCreateTableQuery(TableSchema ts)
{
var 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");
}
}
// add primary keys...
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(", ");
}
}
sb.Append(")\n");
}
else
{
sb.Append("\n");
}
// add foreign keys...
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");
}
}
}
sb.Append("\n");
sb.Append(");\n");
// Create any relevant indexes
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;
}
/// <summary>
/// Creates the CREATE TABLE DDL for SQLite and a specific table.
/// </summary>
/// <param name="conn">The SQLite connection</param>
/// <param name="dt">The table schema object for the table to be generated.</param>
private static void AddSQLiteTable(SQLiteConnection conn, TableSchema dt)
{
// Prepare a CREATE TABLE DDL statement
string stmt = BuildCreateTableQuery(dt);
// Execute the query in order to actually create the table.
var cmd = new SQLiteCommand(stmt, conn);
cmd.ExecuteNonQuery();
}
/// <summary>
/// Builds a SELECT query for a specific table. Needed in the process of copying rows
/// from the SQL Server database to the SQLite database.
/// </summary>
/// <param name="ts">The table schema of the table for which we need the query.</param>
/// <returns>The SELECT query for the table.</returns>
private static string BuildSqlServerTableQuery(TableSchema ts)
{
var sb = new StringBuilder();
sb.Append("SELECT ");
for (int i = 0; i < ts.Columns.Count; i++)
{
sb.Append("[" + ts.Columns[i].ColumnName + "]");
if (i < ts.Columns.Count - 1)
{
sb.Append(", ");
}
}
sb.Append(" FROM " + ts.TableSchemaName + "." + "[" + ts.TableName + "]");
return sb.ToString();
}
/// <summary>
/// Creates a command object needed to insert values into a specific SQLite table.
/// </summary>
/// <param name="ts">The table schema object for the table.</param>
/// <returns>A command object with the required functionality.</returns>
private static SQLiteCommand BuildSQLiteInsert(TableSchema ts)
{
var res = new SQLiteCommand();
var sb = new StringBuilder();
sb.Append("INSERT INTO [" + ts.TableName + "] (");
for (int i = 0; i < ts.Columns.Count; i++)
{
sb.Append("[" + ts.Columns[i].ColumnName + "]");
if (i < ts.Columns.Count - 1)
{
sb.Append(", ");
}
}
sb.Append(") VALUES (");
var pnames = new List<String>();
for (int i = 0; i < ts.Columns.Count; i++)
{
string pname = "@" + GetNormalizedName(ts.Columns[i].ColumnName, pnames);
sb.Append(pname);
if (i < ts.Columns.Count - 1)
{
sb.Append(", ");
}
DbType dbType = GetDbTypeOfColumn(ts.Columns[i]);
var prm = new SQLiteParameter(pname, dbType, ts.Columns[i].ColumnName);
res.Parameters.Add(prm);
// Remember the parameter name in order to avoid duplicates
pnames.Add(pname);
}
sb.Append(")");
res.CommandText = sb.ToString();
res.CommandType = CommandType.Text;
return res;
}
/// <summary>
/// Add foreign key schema object from the specified components (Read from SQL Server).
/// </summary>
/// <param name="ts">The table schema to whom foreign key schema should be added to</param>
private void CreateForeignKeySchema(TableSchema ts)
{
ts.ForeignKeys = new List<ForeignKeySchema>();
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
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);
}
}
}
}
/// <summary>
/// Creates a TableSchema object using the specified SQL Server connection and the name of the table for which we need to create the schema.
/// </summary>
/// <param name="tableName">The name of the table for which we want to create a table schema.</param>
/// <param name="tableSchemaName">The name of the schema containing the table for which we want to create a table schema.</param>
/// <returns>A table schema object that represents our knowledge of the table schema</returns>
private TableSchema CreateTableSchema(string tableName, string tableSchemaName)
{
TableSchema res = new TableSchema();
res.TableName = tableName;
res.TableSchemaName = tableSchemaName;
res.Columns = new List<ColumnSchema>();
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
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);
}
int length = reader["CSIZE"] != DBNull.Value ? Convert.ToInt32(reader["CSIZE"]) : 0;
SqlServerToSQLite.ValidateDataType(dataType);
// Note that not all data type names need to be converted because
// SQLite establishes type affinity by searching certain strings
// in the type name. For example - everything containing the string
// 'int' in its type name will be assigned an INTEGER affinity
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 = SqlServerToSQLite.FixDefaultValueString(colDefault);
ColumnSchema col = new ColumnSchema();
col.ColumnName = colName;
col.ColumnType = dataType;
col.Length = length;
col.IsNullable = isNullable;
col.IsIdentity = isIdentity;
col.DefaultValue = SqlServerToSQLite.AdjustDefaultValue(colDefault);
res.Columns.Add(col);
}
}
// Find PRIMARY KEY information
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);
}
}
// Find COLLATE information for all columns in the table
SqlCommand cmd4 = new SqlCommand(@"EXEC sp_tablecollations '" + tableSchemaName + "." + tableName + "'", conn);
using (SqlDataReader reader = cmd4.ExecuteReader())
{
while (reader.Read())
{
bool? isCaseSensitive = null;
string colName = (string)reader["name"];
if (reader["tds_collation"] != DBNull.Value)
{
byte[] mask = (byte[])reader["tds_collation"];
isCaseSensitive = (mask[2] & 0x10) == 0;
}
if (isCaseSensitive.HasValue)
{
// Update the corresponding column schema.
foreach (ColumnSchema csc in res.Columns)
{
if (csc.ColumnName == colName)
{
csc.IsCaseSensitive = isCaseSensitive;
break;
}
}
}
}
}
try
{
// Find index information
SqlCommand cmd3 = new SqlCommand(@"exec sp_helpindex '" + tableSchemaName + "." + 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"];
// Don't add the index if it is actually a primary key index
if (desc.Contains("primary key")) { continue; }
IndexSchema index = BuildIndexSchema(indexName, desc, keys);
res.Indexes.Add(index);
}
}
}
catch (Exception ex)
{
_log.Error("Error in \"CreateTableSchema\"", ex);
_log.Warn("failed to read index information for table [" + tableName + "]");
}
}
return res;
}
public TableSchemaReaderProgressChangedEventArgs(TableSchema lastProcessedTable, int tablesProcessed, int tablesRemaining)
{
LastProcessedTable = lastProcessedTable;
TablesProcessed = tablesProcessed;
TablesRemaining = tablesRemaining;
}
