public static IList<TriggerSchema> GetForeignKeyTriggers(TableSchema dt)
{
IList<TriggerSchema> result = new List<TriggerSchema>();
foreach (ForeignKeySchema fks in dt.ForeignKeys)
{
StringBuilder sb = new StringBuilder();
result.Add(GenerateInsertTrigger(fks));
result.Add(GenerateUpdateTrigger(fks));
result.Add(GenerateDeleteTrigger(fks));
}
return result;
}
/// <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="conn">The SQL Server connection to use</param>
/// <param name="tableName">The name of the table for which we wants to create the table schema.</param>
/// <returns>A table schema object that represents our knowledge of the table schema</returns>
private static TableSchema CreateTableSchema(SqlConnection conn, string tableName, string tschma)
{
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);
// 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 = "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);
} // while
} // using
// 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);
} // while
} // using
// Find COLLATE information for all columns in the table
SqlCommand cmd4 = new SqlCommand(
@"EXEC sp_tablecollations '" + tschma + "." + 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"];
if ((mask[2] & 0x10) != 0)
isCaseSensitive = false;
else
isCaseSensitive = true;
} // if
if (isCaseSensitive.HasValue)
{
// Update the corresponding column schema.
foreach (ColumnSchema csc in res.Columns)
{
if (csc.ColumnName == colName)
{
csc.IsCaseSensitivite = isCaseSensitive;
break;
}
} // foreach
} // if
} // while
} // using
try
{
// Find index information
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"];
// 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);
} // while
} // using
}
catch (Exception ex)
{
_log.Warn("failed to read index information for table [" + tableName + "]");
} // catch
return res;
}
/// <summary>
/// Used when creating the CREATE TABLE DDL. Creates a single row
/// for the specified column.
/// </summary>
/// <param name="col">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 col, TableSchema ts, ref bool pkey)
{
StringBuilder sb = new StringBuilder();
sb.Append("\t\""+col.ColumnName + "\"\t\t");
// Special treatment for IDENTITY columns
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();
}
/// <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)
{
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");
} // foreach
// 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(", ");
} // for
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");
} // for
}
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");
} // for
} // if
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);
_log.Info("\n\n" + stmt + "\n\n");
// Execute the query in order to actually create the table.
SQLiteCommand 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)
{
StringBuilder 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(", ");
} // for
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)
{
SQLiteCommand res = new SQLiteCommand();
StringBuilder 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(", ");
} // for
sb.Append(") VALUES (");
List<string> 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]);
SQLiteParameter 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);
} // for
sb.Append(")");
res.CommandText = sb.ToString();
res.CommandType = CommandType.Text;
return res;
}
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>
/// Add foreign key schema object from the specified components (Read from SQL Server).
/// </summary>
/// <param name="conn">The SQL Server connection to use</param>
/// <param name="ts">The table schema to whom foreign key schema should be added to</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);
}
}
}
/// <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="conn">The SQL Server connection to use</param>
/// <param name="tableName">The name of the table for which we wants to create the table schema.</param>
/// <returns>A table schema object that represents our knowledge of the table schema</returns>
private static TableSchema CreateTableSchema(OracleConnection conn, string tableName, string tschma)
{
TableSchema res = new TableSchema();
res.TableName = tableName;
res.TableSchemaName = tschma;
res.Columns = new List<ColumnSchema>();
OracleCommand cmd = new OracleCommand(@"SELECT CNAME, COLTYPE, WIDTH, NULLS, DEFAULTVAL FROM COL WHERE TNAME = '" + tableName + "' ORDER BY COLNO", conn);
using (OracleDataReader reader = cmd.ExecuteReader())
{
_log.Debug("begin:" + tableName);
while (reader.Read())
{
object tmp = reader["CNAME"];
if (tmp is DBNull)
continue;
string colName = (string)reader["CNAME"];
tmp = reader["DEFAULTVAL"];
string colDefault;
if (tmp is DBNull)
colDefault = string.Empty;
else
colDefault = (string)tmp;
tmp = reader["NULLS"];
bool isNullable = ((string)tmp == "NULL");
string dataType = (string)reader["COLTYPE"];
bool isIdentity = false;
// if (reader["IDENT"] != DBNull.Value)
// isIdentity = ((int)reader["IDENT"]) == 1 ? true : false;
int length = reader["WIDTH"] != DBNull.Value ? Convert.ToInt32(reader["WIDTH"]) : 0;
//colDefault = FixDefaultValueString(colDefault);
ColumnSchema col = new ColumnSchema();
col.ColumnName = colName;
col.ColumnType = GetDbTypeByOracleType(dataType);
col.Length = length;
col.IsNullable = isNullable;
col.IsIdentity = isIdentity;
col.DefaultValue = AdjustDefaultValue(colDefault);
res.Columns.Add(col);
} // while
_log.Debug("end:" + tableName);
} // using
// Find PRIMARY KEY information
/* OracleCommand cmd2 = new OracleCommand(@"select cu.column_name from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name ='" + tableName + "'", conn);
using (OracleDataReader reader = cmd2.ExecuteReader())
{
res.PrimaryKey = new List<string>();
while (reader.Read())
{
string colName = (string)reader["COLUMN_NAME"];
res.PrimaryKey.Add(colName);
} // while
} // using
*/
// Find COLLATE information for all columns in the table
/* OracleCommand cmd4 = new OracleCommand(
@"EXEC sp_tablecollations '" + tschma + "." + tableName + "'", conn);
using (OracleDataReader 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"];
if ((mask[2] & 0x10) != 0)
isCaseSensitive = false;
else
isCaseSensitive = true;
} // if
if (isCaseSensitive.HasValue)
{
// Update the corresponding column schema.
foreach (ColumnSchema csc in res.Columns)
{
if (csc.ColumnName == colName)
{
csc.IsCaseSensitivite = isCaseSensitive;
break;
}
} // foreach
} // if
} // while
} // using
*/
/*
try
{
// Find index information
OracleCommand cmd3 = new OracleCommand(
@"exec sp_helpindex '" + tschma + "." + tableName + "'", conn);
using (OracleDataReader 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);
} // while
} // using
}
catch (Exception ex)
{
_log.Warn("failed to read index information for table [" + tableName + "]");
} // catch
*/
return res;
}
/// <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 BuildOracleTableQuery(TableSchema ts)
{
StringBuilder 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(", ");
} // for
sb.Append(" FROM " + ts.TableName);// + " where rownum <1000 ");
return sb.ToString();
}