Example #1
0
        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;");
            //}
        }
 /// <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;
 }
Example #3
0
        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);
                }

            }
        }
Example #4
0
        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();
            }
        }