This is a migrated thread and some comments may be shown as answers.

Will CreateUpdateDDLScript also create delete statements?

6 Answers 103 Views
Development (API, general questions)
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Vincent
Top achievements
Rank 1
Vincent asked on 10 Mar 2011, 01:06 PM

Hi,

We are currently building our product based on Open Access ORM. Our product needs to be able to update the database schema when a new version of the product is available. We have found that generation of the change script can be done by the ISchemaHandler.CreateUpdateDDLScript and the execute of CreateUpdateDDLScript. However, we are wondering if this also deletes tables or columns from the database that are no longer used in the new version of the model? This is something we don't want, since we don't want to risk loosing data of our customers. If it does create deletes for these tables or columns, is there a way to prevent it?

Kind Regards,

Vincent

6 Answers, 1 is accepted

Sort by
0
Accepted
Ady
Telerik team
answered on 15 Mar 2011, 05:19 PM
Hi Vincent,

 The 'CreateUpdateDDLScript' method creates the script to migrate the database to the required state, based on the current domain model. If there are tables that are no longer required, no DROP TABLE statements are generated. OpenAccess does not delete unrequired tables and it's data. If there is an extra column in a required table, an 'ALTER TABLE [xxx] DROP COLUMN [yy]' statement is generated. There is currently no way to avoid this. Nevertheless, you can always inspect the script generated by this method (manually or programmatically) and remove such statements, before actually executing it.
Alternatively you can write code to backup the data to another database  and then delete the extra columns.

Do get back in case you need further assisstance.

Kind regards,
Ady
the Telerik team
Registration for Q1 2011 What’s New Webinar Week is now open. Mark your calendar for the week starting March 21st and book your seat for a walk through all the exciting stuff we ship with the new release!
0
Vincent
Top achievements
Rank 1
answered on 16 Mar 2011, 04:23 PM

Hi Ady,

Thanks for your reply. So if I understand your answer correctly the following is true:

f I have a table X with columns P, Q, R and S and I remove column S from the model and add a column T, the script that will be generated is:
 'ALTER TABLE [X] DROP COLUMN [S]'

 'ALTER TABLE [X] ADD COLUMN [T]'

Or will it leave column S in the database? So the generated script will be:

 'ALTER TABLE [X] ADD COLUMN [T]'

Kind regards,

Vincent

0
Accepted
Ady
Telerik team
answered on 17 Mar 2011, 12:06 PM
Hi Vincent,

 If you remove the field corresponding to column 'S' , OpenAccess detects that the column is not required and will generate an 'ALTER TABLE [X] DROP COLUMN [S] statement. For new columns it will generate 'ALTER TABLE [X] ADD COLUMN [T] statments.

Kind regards,
Ady
the Telerik team
0
Sally
Top achievements
Rank 1
answered on 06 Jan 2016, 11:42 AM

Can I have a response for this....

Now, Telerik Open Access has NO way of auto-generating C# headers from an existing database, since it is depreciated and not with VS2015.

  

Now I have to manually write down each table, each column field info to feed it to Telerik Fluent mapping API.

Is there a way to disable the drop column, add column commands, being issued from Telerik?

Here's a use-case example.

This Telerik, I have spend 3 week coding the fluent code manually since VS2015 has no automatic mapper and rLinqQ is not available in VS2015.

There is no code-generation feature in VS2015 and Telerik Open Access 2015.3.xxxx version requests that developers do tedious hand-coded, which field names are prone to spelling-mistakes.

When someone adds a column, I don't expect Telerik Open Access to drop a column, then add a new column because I made a spelling mistake on the field name.

 

Please advise.

(Paying customer)

 

0
Sally
Top achievements
Rank 1
answered on 06 Jan 2016, 11:57 AM

Use case examples where Telerik drops a whole table.:

 

- A person changes field-names a bit. Since Telerik OpenAccess 2015.3 issues drop columns, if the field-names are all different, there is no easy way to update RLinq as the fluent mapping has to be done manually. (Either i'm missing something or I'm not seeing this in the documentation.) If Telerik drops all columns, and re-builds it, it would be suicidal on live production database.

 

- A person adds an extra column, which is not used by the App. Maybe for another app, so Telerik Open Access suddenly drops the column. Lot of data will be lost. Is there a way to disable automatic field updates? Do you understand that if on live production database, this would be another strike against using Telerik Open Access?

 

- Spelling mistakes on field names. This is an automatic Russian roulette death. Since there is no easy way to do fluent mapping automatically (it used to be available). Also, there's this use-case where you have to compile with partial field information. If I run the app, and Telerik Open Access drops all the other fields, you can imagine why this product is considered "not serious".

 

Please advise what to do, since, there is no automatically code generation for field-names, and it is data-suicide to make partial fluent mapping only to Telerik issue drop columns commands on tables that are partially mapped.

 

 

 

 

 

0
Boyan
Telerik team
answered on 08 Jan 2016, 01:04 PM
Hello Sally,

Thank you for your feedback.

I would like to inform you that such code generation tool is indeed in our future plans and we plan to release it soon. We are going to announce it on the Telerik Blogs page.

With regards to your schema migration questions - when Telerik Data Access creates a migration script it also exposes description of the changes contained in that migration script so you could inspect those changes without the need of parsing the actual script.
This allows you to programically detect changes that you consider breaking or unsafe and react on that situation depending the exact case. For more information all the features exposed in the API please refer to this documentation article.

In the referenced article, you will notice that the IsComplex() method which return true in case of structural changes in the script. This will detect columns that are to be dropped as well. 
To utilize it, you can update the schema migration code on your side in similar way to the one shown below:

if (schemaHandler.DatabaseExists())
{
    var info = schemaHandler.CreateUpdateInfo(new SchemaUpdateProperties());
 
    if (info.IsComplex == true)
    {
        throw new InvalidOperationException("Compldex schema changes detected. Please have a look");
    }
    else
    {
        script = schemaHandler.CreateUpdateDDLScript(null);
    }
}

I would suggest you to play a bit with that API and come up with a set-up that matches your requirements. When a complex schema migration comes, you could manually inspect the script, making sure that it would indeed have the decried outcome or adjust the model accordingly.

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

Regards,
Boyan
Telerik
 
Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
Tags
Development (API, general questions)
Asked by
Vincent
Top achievements
Rank 1
Answers by
Ady
Telerik team
Vincent
Top achievements
Rank 1
Sally
Top achievements
Rank 1
Boyan
Telerik team
Share this question
or