This project is read-only.

Database Installer

Jan 16, 2010 at 10:33 PM
Edited Jan 16, 2010 at 10:37 PM

Hi,

I'm evaluating nHydrate to find out the capabilites of updating the Database as the Model changes.

A simple additional field has worked fine. But I am interested in whether the correct mode of opertion is to run Generate and the Database Installer as a pair.

Can you make changes to the Model and run Generate multiple times without running the Database Installer, then when ready run the Database Installer.

Do you increment the Version after a Generate and the Database Installer pair, and never beween the Generate and Database Installer runs.

Thanks

John

Jan 17, 2010 at 12:25 AM

The version number is controlled by you. You increment it when your business rules apply. This does not affect the upgrade path. Each time your generate there is a SQL file generated in the installer project. It is the differences from the current model to the new model. This is a cummulative script. So if you generate 10 times the ten files will have an upgrade path in them. So to answer your question, you do NOT need to run the installer assembly on each generation. The upgrade will run the file in order and execute the scripts. So if you added 1 field and generated and did this 10 times, you would have 10 SQL files with one field addition each. These will be run in order inside of a transaction. The upgrade works very well but you can fool it. Keep in mind if you add a non-null field with no default value to an existing table that already has data in it, the upgrade will fail (no way around this of course). You need to either add a default or add a little SQL to the generated file to add the field as nullable, populate it with whatever data you wish, then make it non-nullable. That is the only condition I can think of of the top of my head.

Jan 17, 2010 at 7:44 PM
Edited Jan 17, 2010 at 8:14 PM

Hi,
Thanks for the reply and the clarifcation of the design methodology, and what you say about the cumulative effect of multiple Generate actions, seems to me to be a logical approach.

I have revised my tests and can confirm if you add new fields one at a time running Generate each time, this works fine. Clearly all of the 0_0_0_1_N_GeneratedScript.sql were executed in order.

However my initial tests included a field name change, which does cause the update process to break, with this error

{"Invalid column name 'field1xx'."}

Where I am changing Field1 to Field1XX, Generating and then adding Field3, Generating and Updating

 

--Generated Upgrade For Version 0.0.0.1.5
--Generated on 2010-01-17 19:50:22

if not exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = 'Field1' and o.name = 'EMPLOYEE')AND not exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = 'Field1XX' and o.name = 'EMPLOYEE')
EXEC sp_rename @objname = 'EMPLOYEE.Field1', @newname = 'Field1XX', @objtype = 'COLUMN'
GO

if exists (select * from dbo.sysindexes where name = 'IDX_EMPLOYEE_FIELD1XX')
DROP INDEX [IDX_EMPLOYEE_FIELD1XX] ON [EMPLOYEE]

select 'ALTER TABLE [EMPLOYEE] DROP CONSTRAINT ' + [name] as 'sql' into #t from sysobjects where id IN( select sc.cdefault FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id WHERE SO.xtype = 'U' and SO.NAME = 'EMPLOYEE' and SC.NAME = 'Field1XX' )
declare @sql nvarchar (1000)
SELECT @sql = MAX([sql]) from #t
exec (@sql)
drop table #t
if exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = 'Field1XX' and o.name = 'EMPLOYEE')
BEGIN
if exists (select * from dbo.sysindexes where name = 'IDX_EMPLOYEE_FIELD1XX')
DROP INDEX [IDX_EMPLOYEE_FIELD1XX] ON [EMPLOYEE]

ALTER TABLE [EMPLOYEE] ALTER COLUMN [Field1XX] [VarChar] (1) NULL
END

GO

 

--Generated Upgrade For Version 0.0.0.1.6
--Generated on 2010-01-17 19:51:41

if not exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = 'Field3' and o.name = 'EMPLOYEE')
ALTER TABLE [EMPLOYEE] ADD [Field3] [VarChar] (1) NULL

GO

 

 

I'm sure this is very straightforward for you to reproduce.

Thanks

John

 

Jan 18, 2010 at 12:23 AM
Edited Jan 18, 2010 at 12:25 AM

Hi,

It seems to me to be an error in the generated script that does a field name change.

The first clause should not have a NOT in the part that checks for the presence of the original field name.

This works

if exists
     (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = 'FieldTwoYY' and o.name = 'EMPLOYEE')
AND not exists
     (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = 'FieldTwoZZ' and o.name = 'EMPLOYEE')
EXEC sp_rename @objname = 'EMPLOYEE.FieldTwoYY', @newname = 'FieldTwoZZ', @objtype = 'COLUMN'
GO

Thanks

John

Jan 18, 2010 at 2:39 AM

You are correct. The "NOT" was an error. Please download the latest version for a fix to this issue. However you will have to manually fix that file (I see you already did), since those SQL files are never overwritten. It generates a new one each time.