private static void DropAllTables()
{
var keys = Repo.GetAllForeignKeys();
foreach (var constraint in keys)
{
Repo.ExecuteNonQuery(string.Format("ALTER TABLE {0} DROP CONSTRAINT {1}", constraint.ConstraintTableName,
constraint.ConstraintName));
}
var tbList = Repo.GetAllTableNames();
tbList.ForEach(tb => Repo.ExecuteNonQuery("DROP TABLE " + tb));
ClearCache();
}
/// <summary>
/// Gets the column information (name,metadata,description) for a given table. This method is internal static so it can be reused in ColumnMenuCommandHandler
/// </summary>
internal static IList <TableColumnInfo> GetSiblingColumnInfo(IRepository repo, string parentTable)
{
var lst = new List <TableColumnInfo>();
var descCols = ExplorerControl.DescriptionCache.Where(d => d.Parent == parentTable).ToList();
var cols = repo.GetAllColumns().Where(c => c.TableName == parentTable);
var pkList = repo.GetAllPrimaryKeys().Where(p => p.TableName == parentTable).Select(p => p.ColumnName).ToList();
var fkList = repo.GetAllForeignKeys().Where(f => f.ConstraintTableName == parentTable).Select(f => f.ColumnName).ToList();
string isNull = "not null", fk = "", pk = "";
foreach (var item in cols)
{
if (pkList.Contains(item.ColumnName))
{
pk = "PK, ";
}
if (fkList.Contains(item.ColumnName))
{
fk = "FK, ";
}
if (item.IsNullable == YesNoOption.YES)
{
isNull = "null";
}
var type = item.ShortType;
var desc = descCols.Where(d => d.Object == item.ColumnName).Select(s => s.Description).SingleOrDefault();
lst.Add(new TableColumnInfo()
{
Name = item.ColumnName,
Metadata = string.Format("{0}{1}{2} {3}", pk, fk, type, isNull),//space between type & isNUll always exists
Description = desc
});
pk = "";
fk = "";
isNull = "not null";
}
return(lst);
}
private void Init(IRepository repository, string outFile)
{
_outFile = outFile;
_repository = repository;
_sbScript = new StringBuilder(10485760);
_tableNames = _repository.GetAllTableNames();
_allColumns = _repository.GetAllColumns();
_allForeignKeys = repository.GetAllForeignKeys();
_allPrimaryKeys = repository.GetAllPrimaryKeys();
if (!repository.IsServer())
_allIndexes = repository.GetAllIndexes();
string scriptEngineBuild = AssemblyFileVersion;
if (_repository.IsServer())
{
// Check if datatypes are supported when exporting from server
// Either they can be converted, are supported, or an exception is thrown (if not supported)
// Currently only sql_variant is not supported
foreach (Column col in _allColumns)
{
col.CharacterMaxLength = Helper.CheckDateColumnLength(col.DataType, col);
col.DateFormat = Helper.CheckDateFormat(col.DataType);
// Check if the current column points to a unique identity column,
// as the two columns' datatypes must match
bool refToIdentity = false;
Dictionary<string, Constraint> columnForeignKeys = new Dictionary<string, Constraint>();
// Fix for multiple constraints with same columns
var _tableKeys = _allForeignKeys.Where(c => c.ConstraintTableName == col.TableName);
foreach (var constraint in _tableKeys)
{
if (!columnForeignKeys.ContainsKey(constraint.Columns.ToString()))
{
columnForeignKeys.Add(constraint.Columns.ToString(), constraint);
}
}
if (columnForeignKeys.ContainsKey(string.Format("[{0}]", col.ColumnName)))
{
var refCol = _allColumns.Where(c => c.TableName == columnForeignKeys[string.Format("[{0}]", col.ColumnName)].UniqueConstraintTableName
&& string.Format("[{0}]", c.ColumnName) == columnForeignKeys[string.Format("[{0}]", col.ColumnName)].UniqueColumnName).FirstOrDefault();
if (refCol != null && refCol.AutoIncrementBy > 0)
{
refToIdentity = true;
}
}
col.ServerDataType = col.DataType;
// This modifies the datatype to be SQL Compact compatible
col.DataType = Helper.CheckDataType(col.DataType, col, refToIdentity, _preserveDateAndDateTime2);
}
}
_sbScript.AppendFormat("-- Script Date: {0} {1} - ErikEJ.SqlCeScripting version {2}", DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString(), scriptEngineBuild);
_sbScript.AppendLine();
if (!string.IsNullOrEmpty(_outFile) && !_repository.IsServer())
{
GenerateDatabaseInfo();
}
//if (!string.IsNullOrEmpty(_outFile) && _sqlite)
//{
// _sbScript.AppendLine("SELECT 1;");
// _sbScript.AppendLine("PRAGMA foreign_keys=OFF;");
// _sbScript.AppendLine("BEGIN TRANSACTION;");
//}
}
public static void CreateDiffScript(IRepository sourceRepository, IRepository targetRepository,IGenerator generator, bool includeTargetDrops)
{
List<string> sourceTables = sourceRepository.GetAllTableNames();
List<string> targetTables = targetRepository.GetAllTableNames();
// Script each table not in the target
foreach (string tableName in sourceTables.Except(targetTables))
{
generator.GenerateTableCreate(tableName);
}
foreach (string tableName in sourceTables.Except(targetTables))
{
generator.GeneratePrimaryKeys(tableName);
}
foreach (string tableName in sourceTables.Except(targetTables))
{
List<string> tableIndexes = sourceRepository.GetIndexesFromTable(tableName).Select(i => i.IndexName).Distinct().ToList();
foreach (var index in tableIndexes)
{
generator.GenerateIndexScript(tableName, index);
}
}
foreach (string tableName in sourceTables.Except(targetTables))
{
generator.GenerateForeignKeys(tableName);
}
// Drop each table in the target but not the source
if (includeTargetDrops)
{
foreach (string tableName in targetTables.Except(sourceTables))
{
generator.GenerateTableDrop(tableName);
}
}
//For each table both in target and source
foreach (string tableName in sourceTables.Intersect(targetTables))
{
// Check columns for the table: Dropped, added or changed ?
IEnumerable<Column> sourceColumns = from c in sourceRepository.GetColumnsFromTable()
where c.TableName == tableName
select c;
IEnumerable<Column> targetColumns = from c in targetRepository.GetColumnsFromTable()
where c.TableName == tableName
select c;
// Added columns
foreach (var column in sourceColumns.Except(targetColumns, new ColumnComparer()))
{
generator.GenerateColumnAddScript(column);
}
// Same columns, check for changes
foreach (var sourceColumn in sourceColumns.Intersect(targetColumns, new ColumnComparer()))
{
bool altered = false;
// Check if they have any differences:
var targetColumn = (from c in targetColumns
where c.TableName == sourceColumn.TableName && c.ColumnName == sourceColumn.ColumnName
select c).Single();
if (sourceColumn.IsNullable != targetColumn.IsNullable)
altered = true;
if (sourceColumn.NumericPrecision != targetColumn.NumericPrecision)
altered = true;
if (sourceColumn.NumericScale != targetColumn.NumericScale)
altered = true;
if (sourceColumn.AutoIncrementBy != targetColumn.AutoIncrementBy)
altered = true;
if (sourceColumn.CharacterMaxLength != targetColumn.CharacterMaxLength)
altered = true;
if (sourceColumn.DataType != targetColumn.DataType)
altered = true;
if (altered)
generator.GenerateColumnAlterScript(sourceColumn);
// Changed defaults is special case
if (!targetColumn.ColumnHasDefault && sourceColumn.ColumnHasDefault)
{
generator.GenerateColumnSetDefaultScript(sourceColumn);
}
if (!sourceColumn.ColumnHasDefault && targetColumn.ColumnHasDefault)
{
generator.GenerateColumnDropDefaultScript(sourceColumn);
}
// If both columns have defaults, but they are different
if ((sourceColumn.ColumnHasDefault && targetColumn.ColumnHasDefault) && (sourceColumn.ColumnDefault != targetColumn.ColumnDefault))
{
generator.GenerateColumnSetDefaultScript(sourceColumn);
}
}
//Check primary keys
List<PrimaryKey> sourcePK = sourceRepository.GetAllPrimaryKeys().Where(p => p.TableName == tableName).ToList();
List<PrimaryKey> targetPK = targetRepository.GetAllPrimaryKeys().Where(p => p.TableName == tableName).ToList();
// Add the PK
if (targetPK.Count == 0 && sourcePK.Count > 0)
{
generator.GeneratePrimaryKeys(tableName);
}
// Do we have the same columns, if not, drop and create.
if (sourcePK.Count > 0 && targetPK.Count > 0)
{
if (sourcePK.Count == targetPK.Count)
{
//Compare columns
for (int i = 0; i < sourcePK.Count; i++)
{
if (sourcePK[i].ColumnName != targetPK[i].ColumnName)
{
generator.GeneratePrimaryKeyDrop(sourcePK[i], tableName);
generator.GeneratePrimaryKeys(tableName);
break;
}
}
}
// Not same column count, just drop and create
else
{
generator.GeneratePrimaryKeyDrop(sourcePK[0], tableName);
generator.GeneratePrimaryKeys(tableName);
}
}
// Check indexes
List<Index> sourceIXs = sourceRepository.GetIndexesFromTable(tableName);
List<Index> targetIXs = targetRepository.GetIndexesFromTable(tableName);
// Check added indexes (by name only)
foreach (var index in sourceIXs)
{
var targetIX = targetIXs.Where(s => s.IndexName == index.IndexName);
if (targetIX.Count() == 0)
{
generator.GenerateIndexScript(index.TableName, index.IndexName);
}
}
// Check foreign keys
List<Constraint> sourceFKs = sourceRepository.GetAllForeignKeys(tableName);
List<Constraint> targetFKs = targetRepository.GetAllForeignKeys(tableName);
// Check added foreign keys (by name only)
foreach (var fk in sourceFKs)
{
Constraint targetFK = targetFKs.Where(s => s.ConstraintName == fk.ConstraintName).SingleOrDefault();
if (targetFK == null)
{
generator.GenerateForeignKey(fk);
}
}
// Check deleted FKs (by name only)
foreach (var fk in targetFKs)
{
Constraint sourceFK = sourceFKs.Where(s => s.ConstraintName == fk.ConstraintName).SingleOrDefault();
if (sourceFK == null)
{
generator.GenerateForeignKeyDrop(fk);
}
}
// Check deleted indexes (by name only)
foreach (var index in targetIXs)
{
var sourceIX = sourceIXs.Where(s => s.IndexName == index.IndexName);
if (sourceIX.Count() == 0)
{
generator.GenerateIndexOnlyDrop(index.TableName, index.IndexName);
}
}
// Dropped columns
foreach (var column in targetColumns.Except(sourceColumns, new ColumnComparer()))
{
generator.GenerateColumnDropScript(column);
}
}
}
/// <summary>
/// Gets the column information (name,metadata,description) for a given table. This method is internal static so it can be reused in ColumnMenuCommandHandler
/// </summary>
internal static IList<TableColumnInfo> GetSiblingColumnInfo(IRepository repo, string parentTable)
{
List<TableColumnInfo> lst = new List<TableColumnInfo>();
var desc_cols = ExplorerControl.DescriptionCache.Where(d => d.Parent == parentTable).ToList();
var cols = repo.GetAllColumns().Where(c => c.TableName == parentTable);
var pkList = repo.GetAllPrimaryKeys().Where(p => p.TableName == parentTable).Select(p => p.ColumnName);
var fkList = repo.GetAllForeignKeys().Where(f => f.ConstraintTableName == parentTable).Select(f => f.ColumnName);
string isNull = "not null", fk = "", pk = "", type = "";
foreach (var item in cols)
{
if (pkList.Contains(item.ColumnName)) { pk = "PK, "; }
if (fkList.Contains(item.ColumnName)) { fk = "FK, "; }
if (item.IsNullable == YesNoOption.YES) { isNull = "null"; }
type = item.ShortType;
string desc = desc_cols.Where(d => d.Object == item.ColumnName).Select(s => s.Description).SingleOrDefault();
lst.Add(new TableColumnInfo()
{
Name = item.ColumnName,
Metadata = string.Format("{0}{1}{2} {3}", pk, fk, type, isNull),//space between type & isNUll always exists
Description = desc
});
pk = "";
fk = "";
isNull = "not null";
}
return lst;
}
public static void CreateDiffScript(IRepository sourceRepository, IRepository targetRepository, IGenerator generator, bool includeTargetDrops)
{
List <string> sourceTables = sourceRepository.GetAllTableNames();
List <string> targetTables = targetRepository.GetAllTableNames();
// Script each table not in the target
foreach (string tableName in sourceTables.Except(targetTables))
{
generator.GenerateTableCreate(tableName);
}
foreach (string tableName in sourceTables.Except(targetTables))
{
generator.GeneratePrimaryKeys(tableName);
}
foreach (string tableName in sourceTables.Except(targetTables))
{
List <string> tableIndexes = sourceRepository.GetIndexesFromTable(tableName).Select(i => i.IndexName).Distinct().ToList();
foreach (var index in tableIndexes)
{
generator.GenerateIndexScript(tableName, index);
}
}
foreach (string tableName in sourceTables.Except(targetTables))
{
generator.GenerateForeignKeys(tableName);
}
// Drop each table in the target but not the source
if (includeTargetDrops)
{
foreach (string tableName in targetTables.Except(sourceTables))
{
generator.GenerateTableDrop(tableName);
}
}
//For each table both in target and source
foreach (string tableName in sourceTables.Intersect(targetTables))
{
// Check columns for the table: Dropped, added or changed ?
IEnumerable <Column> sourceColumns = from c in sourceRepository.GetAllColumns()
where c.TableName == tableName
select c;
IEnumerable <Column> targetColumns = from c in targetRepository.GetAllColumns()
where c.TableName == tableName
select c;
// Added columns
foreach (var column in sourceColumns.Except(targetColumns, new ColumnComparer()))
{
generator.GenerateColumnAddScript(column);
}
// Same columns, check for changes
foreach (var sourceColumn in sourceColumns.Intersect(targetColumns, new ColumnComparer()))
{
bool altered = false;
// Check if they have any differences:
var targetColumn = (from c in targetColumns
where c.TableName == sourceColumn.TableName && c.ColumnName == sourceColumn.ColumnName
select c).Single();
if (sourceColumn.IsNullable != targetColumn.IsNullable)
{
altered = true;
}
if (sourceColumn.NumericPrecision != targetColumn.NumericPrecision)
{
altered = true;
}
if (sourceColumn.NumericScale != targetColumn.NumericScale)
{
altered = true;
}
if (sourceColumn.AutoIncrementBy != targetColumn.AutoIncrementBy)
{
altered = true;
}
if (sourceColumn.CharacterMaxLength != targetColumn.CharacterMaxLength)
{
altered = true;
}
if (sourceColumn.DataType != targetColumn.DataType)
{
altered = true;
}
if (altered)
{
generator.GenerateColumnAlterScript(sourceColumn);
}
// Changed defaults is special case
if (!targetColumn.ColumnHasDefault && sourceColumn.ColumnHasDefault)
{
generator.GenerateColumnSetDefaultScript(sourceColumn);
}
if (!sourceColumn.ColumnHasDefault && targetColumn.ColumnHasDefault)
{
generator.GenerateColumnDropDefaultScript(sourceColumn);
}
// If both columns have defaults, but they are different
if ((sourceColumn.ColumnHasDefault && targetColumn.ColumnHasDefault) && (sourceColumn.ColumnDefault != targetColumn.ColumnDefault))
{
generator.GenerateColumnSetDefaultScript(sourceColumn);
}
}
//Check primary keys
List <PrimaryKey> sourcePK = sourceRepository.GetAllPrimaryKeys().Where(p => p.TableName == tableName).ToList();
List <PrimaryKey> targetPK = targetRepository.GetAllPrimaryKeys().Where(p => p.TableName == tableName).ToList();
// Add the PK
if (targetPK.Count == 0 && sourcePK.Count > 0)
{
generator.GeneratePrimaryKeys(tableName);
}
// Do we have the same columns, if not, drop and create.
if (sourcePK.Count > 0 && targetPK.Count > 0)
{
if (sourcePK.Count == targetPK.Count)
{
//Compare columns
for (int i = 0; i < sourcePK.Count; i++)
{
if (sourcePK[i].ColumnName != targetPK[i].ColumnName)
{
generator.GeneratePrimaryKeyDrop(sourcePK[i], tableName);
generator.GeneratePrimaryKeys(tableName);
break;
}
}
}
// Not same column count, just drop and create
else
{
generator.GeneratePrimaryKeyDrop(sourcePK[0], tableName);
generator.GeneratePrimaryKeys(tableName);
}
}
// Check indexes
List <Index> sourceIXs = sourceRepository.GetIndexesFromTable(tableName);
List <Index> targetIXs = targetRepository.GetIndexesFromTable(tableName);
// Check added indexes (by name only)
foreach (var index in sourceIXs)
{
var targetIX = targetIXs.Where(s => s.IndexName == index.IndexName);
if (targetIX.Count() == 0)
{
generator.GenerateIndexScript(index.TableName, index.IndexName);
}
}
// Check foreign keys
List <Constraint> sourceFKs = sourceRepository.GetAllForeignKeys().Where(fk => fk.ConstraintTableName == tableName).ToList();
List <Constraint> targetFKs = targetRepository.GetAllForeignKeys().Where(fk => fk.ConstraintTableName == tableName).ToList();
// Check added foreign keys (by name only)
foreach (var fk in sourceFKs)
{
Constraint targetFK = targetFKs.Where(s => s.ConstraintName == fk.ConstraintName).SingleOrDefault();
if (targetFK == null)
{
generator.GenerateForeignKey(fk);
}
}
// Check deleted FKs (by name only)
foreach (var fk in targetFKs)
{
Constraint sourceFK = sourceFKs.Where(s => s.ConstraintName == fk.ConstraintName).SingleOrDefault();
if (sourceFK == null)
{
generator.GenerateForeignKeyDrop(fk);
}
}
// Check deleted indexes (by name only)
foreach (var index in targetIXs)
{
var sourceIX = sourceIXs.Where(s => s.IndexName == index.IndexName);
if (sourceIX.Count() == 0)
{
generator.GenerateIndexOnlyDrop(index.TableName, index.IndexName);
}
}
// Dropped columns
foreach (var column in targetColumns.Except(sourceColumns, new ColumnComparer()))
{
generator.GenerateColumnDropScript(column);
}
}
}
private void Init(IRepository repository, string outFile)
{
_outFile = outFile;
_repository = repository;
_sbScript = new StringBuilder(10485760);
_tableNames = _repository.GetAllTableNames();
_allColumns = _repository.GetColumnsFromTable();
_allForeignKeys = repository.GetAllForeignKeys();
_allPrimaryKeys = repository.GetAllPrimaryKeys();
if (_repository.IsServer())
{
// Check if datatypes are supported when exporting from server
// Either they can be converted, are supported, or an exception is thrown (if not supported)
// Currently only sql_variant is not supported
foreach (Column col in _allColumns)
{
col.CharacterMaxLength = Helper.CheckDateColumnLength(col.DataType, col);
col.DateFormat = Helper.CheckDateFormat(col.DataType);
// Check if the current column points to a unique identity column,
// as the two columns' datatypes must match
bool refToIdentity = false;
Dictionary<string, Constraint> columnForeignKeys = new Dictionary<string, Constraint>();
// Fix for multiple constraints with same columns
var _tableKeys = _allForeignKeys.Where(c => c.ConstraintTableName == col.TableName);
foreach (var constraint in _tableKeys)
{
if (!columnForeignKeys.ContainsKey(constraint.Columns.ToString()))
{
columnForeignKeys.Add(constraint.Columns.ToString(), constraint);
}
}
if (columnForeignKeys.ContainsKey(string.Format("[{0}]", col.ColumnName)))
{
var refCol = _allColumns.Where(c => c.TableName == columnForeignKeys[string.Format("[{0}]", col.ColumnName)].UniqueConstraintTableName
&& string.Format("[{0}]", c.ColumnName) == columnForeignKeys[string.Format("[{0}]", col.ColumnName)].UniqueColumnName).Single();
if (refCol != null && refCol.AutoIncrementBy > 0)
{
refToIdentity = true;
}
}
// This modifies the datatype to be SQL Compact compatible
col.DataType = Helper.CheckDataType(col.DataType, col, refToIdentity);
}
_sbScript.AppendFormat("-- Script Date: {0} {1} - Generated by Export2SqlCe version {2}", DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString(), System.Reflection.Assembly.GetExecutingAssembly().GetName().Version);
}
else
{
_sbScript.AppendFormat("-- Script Date: {0} {1} - Generated by ExportSqlCe version {2}", DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString(), System.Reflection.Assembly.GetExecutingAssembly().GetName().Version);
}
_sbScript.AppendLine();
if (!string.IsNullOrEmpty(_outFile) && !_repository.IsServer())
{
_sbScript.Append("-- Database information:");
_sbScript.AppendLine();
foreach (var kv in _repository.GetDatabaseInfo())
{
_sbScript.Append("-- ");
_sbScript.Append(kv.Key);
_sbScript.Append(": ");
_sbScript.Append(kv.Value);
_sbScript.AppendLine();
}
_sbScript.AppendLine();
// Populate all tablenames
_sbScript.Append("-- User Table information:");
_sbScript.AppendLine();
_sbScript.Append("-- ");
_sbScript.Append("Number of tables: ");
_sbScript.Append(_tableNames.Count);
_sbScript.AppendLine();
foreach (string tableName in _tableNames)
{
Int64 rowCount = _repository.GetRowCount(tableName);
_sbScript.Append("-- ");
_sbScript.Append(tableName);
_sbScript.Append(": ");
_sbScript.Append(rowCount);
_sbScript.Append(" row(s)");
_sbScript.AppendLine();
}
_sbScript.AppendLine();
}
}
