[Solved] Schema migration and default values.

[Jose Mejia]

Hi.

I'm currently use version 2015.1.225.1 of OA and sqlite. I have schema update which includes default values for some columns. ​For simplicity, let's suppose that I'm adding only one colum of type double with default value of 5.0. I have following code:

using(var updateContext = new OpenAccessContext(conStr, config, metadata))
               {
                   var schemaHandler = updateContext.GetSchemaHandler();
                   string script;
                   if (schemaHandler.DatabaseExists())
                   {
                       SchemaUpdateProperties schemaUpdProps = new SchemaUpdateProperties
                           {
                                   CheckConstraint = true,
                                   CheckExtraColumns = true,
                                   CheckExtraIndexes = true,
                                   CheckIndex = true,
                                   CheckLength = true,
                                   CheckNulls = true,
                                   CheckPK = true,
                                   CheckScale = true,
                                   CheckType = true
                           };
                       SchemaUpdateInfo schemaUpdateInfo = schemaHandler.CreateUpdateInfo(schemaUpdProps);
                       script = schemaHandler.CreateUpdateDDLScript(schemaUpdProps);
                   }
                   else
                   {
                       schemaHandler.CreateDatabase();
                       script = schemaHandler.CreateDDLScript();
                   }
                   if (!string.IsNullOrEmpty(script))
                       schemaHandler.ExecuteDDLScript(script);
               }

So, after  schemaHandler.CreateUpdateDDLScript(schemaUpdProps)  is called I have following sql:

-- add column for field _property4
ALTER TABLE [ChildClass] ADD [Property4] double NOT NULL DEFAULT 0;

Obviously it is not desired default value. How can I solve it? Should I  load my custom sql scripts for this situation or somehow parse and  substitute default value?

Thanks in advance.

[Jose Mejia]

Forget to mention that default value I'm setting via constructor. It seems that there is no another wya to do this...

[Boyan]

Hello Jose,

You have taken the correct approach to creating a migration script. There is one thing you should known - Telerik Data Access will not create a script that sets default values of the columns on the database side in a model-first scenario. Let me explain what are the two approaches one could take to assure an object is inserted with a default value for one or more of its properties:

1. The first one, is to set a default value in the constructor of newly initialized object. This way, when this object is persisted in the database by adding it to the context and eventually calling SaveChanges(), the value of the defaulted property will in inserted in the database. This is logic used purely on the client-side and does not involve any database schema specifics.

2. The second approach is useful when working with columns that have default values being set by the database server. In such cases you could use the HasDefaultValue method in a persistent type mapping to instruct Telerik Data Access to omit a property from its insert statements allowing the database server to populate that column with its database set default value. It is important to note that creating a database out of such a model will not result in columns with default values on the database side. This means that you will have to prepare a script yourself altering those columns to have a default value. 

Please refer to this documentation article that addresses both approaches.

I hope this is helpful. Do let us know if you have any further questions.

Regards,
Boyan
Telerik

 

[Jose Mejia]

​Hi, Boyan!

Thank you for reply. I understand your explanation, but what is problem to generate migration script which alter table, column(s) and select default value either as provided in editor(model) or create data objects and read value from fields?  Now I have to do it in two steps: generate update script, edit it manually and execute it. Or use 3-d party tools. It would be great to do it in a single operation in OA.

[Boyan]

Hello Jose,

As this is currently unsupported, an extra step will be required to set the actual default values in the database as you suggested in the workflow you described.

With our latest release the Visual Designer features are deprecated and we do not plan any development in that direction. Nevertheless if you are really interested in such feature and you do want to see it implemented with the Fluent Mapping approach please post a feature request in our Ideas and Feedback Portal - it will be much appreciated.

Do let us know if we could assist you further.

Regards,
Boyan
Telerik