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(); } }