Migration Script!

Apr 25, 2012 at 10:24 AM

The first thing i want to say that: This is a great project! 

I found a problem when i want to generate a migration script,

the statement is generated run with a lot of error. Because the generated statements ending with a semicolon ";" and the after statement that use new column, or function ... cant found it.

Ex:

ALTER TABLE [dbo].[CITY] ADD COLUMN [CITY_ID] ;

CREATE VIEW ABC

AS 

SELECT CITY_ID FROM [dbo].[CITY]

GO

--> Statement Create View will not execute

Apr 26, 2012 at 4:11 AM

Another problem, I just think but not test.

In Sqlserver: When we modifined a table and the another function that retrieve data from that table as (SELECT * from [ModifinedTable] ) is not changed.

So that when we generate script only the table has changed. So that the function will be not updated.

Coordinator
May 8, 2012 at 2:58 PM

Quick clarification: in most (all?) SQL dialects, you can end a SQL command with a ";" and put several commands in a batch. But in SqlServer and Oracle there are rules about what can be in a batch - for instance, CREATE VIEW must be the first statement in a batch. So in a script there is a special command to execute the batch - "GO" in SqlServer and / in Oracle.

Now, when you use CompareSchemas.Execute to generate a migration script, it should put in the batch statements. The CompareViews.Execute method will write "GO" before the first DROP/CREATE VIEW. For your scenario (add or change columns, add or change view) it should work.

Are you creating the migration script using the MigrationGenerator? That just gives you the SQL for each migration operation (AddColumn, AddView etc). It is lower level, so it doesn't assemble the migrations into batches. CompareSchemas is using MigrationGenerator, but it adds the logic for batches. 

If you are using the MigrationGenerator, the easiest and quickest way to ensure it's batch safe is to add "GO" statements between each operation. Something like this...

var script += migrationGenerator.AddColumn(table, newColumn);
script += Environment.NewLine + "GO" + Environment.NewLine;
script += migrationGenerator.AddView(view);

Regarding the second problem... 

If the function has "SELECT * " instead of selecting named columns, it will still work with different columns, so no change is needed :) 

If you are modifying columns in code (migrationGenerator.AddColumn), the schema model won't update functions and procedures that reference it- unfortunately that's not easy to do!

 

Feb 17 at 7:07 AM
I am also trying to generate a migration script for a new table using sql server ce 4.0 The solution to the "go" problem above does not seem to work for my scenario as the script has a parsing error at the alter statement due to not having the go. There does not seem to be any way to manually inject the go before the Alter statement because it is part of the Add column syntax. Any help on how to get around this would be much appreciated. Thanks.
                      foreach (var columnInfo in columns)
            {
                var dbType = ClrToSqlTypeMapper.GetDbType(columnInfo.ReflectionInfo.PropertyType);
                var newColumn = newTable.AddColumn(columnInfo.AttributeInfo.ColumnName, dbType);

                switch (dbType)
                {
                    case DbType.AnsiString:
                    case DbType.AnsiStringFixedLength:
                    case DbType.String:
                    case DbType.StringFixedLength:
                        newColumn.AddLength(400);
                        break;
                }

                if (columnInfo == primaryKey)
                {
                    newColumn.AddPrimaryKey(string.Format("PK_{0}_{1}", tableInfo.TableName,
                        columnInfo.AttributeInfo.ColumnName));
                }
            }

            var migration = new DdlGeneratorFactory(ProviderNameToSqlType(_providerName)).MigrationGenerator();
            var script = migration.AddTable(newTable);
CREATE TABLE [InternalDbAnnotations]
(
  [Id] UNIQUEIDENTIFIER NOT NULL,
  [ObjectType] NVARCHAR (400)  NOT NULL,
  [DbName] NVARCHAR (400)  NOT NULL
);
ALTER TABLE [InternalDbAnnotations] ADD CONSTRAINT [PK_InternalDbAnnotations_Id] PRIMARY KEY ([Id]);
Coordinator
Feb 18 at 7:15 PM
SQLServerCe cannot batch statements at all.

Programmatically you can use both Utilities.ScriptTools.SplitScript and ScriptTools.SplitBySemicolon to break it down into a string array, each element of which can be executed.

I've just committed 32028 , which separates all SQLServer Ce statements by GO instead of semicolon. That means you can paste it into Sql Server Compact Toolbox, or your favorite tool, and just execute the script.