Will CreateUpdateDDLScript also create delete statements?

7 posts, 2 answers
  1. Vincent
    Vincent avatar
    4 posts
    Member since:
    Jan 2011

    Posted 10 Mar 2011 Link to this post

    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

  2. Answer
    Ady
    Admin
    Ady avatar
    589 posts

    Posted 15 Mar 2011 Link to this post

    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!
  3. DevCraft banner
  4. Vincent
    Vincent avatar
    4 posts
    Member since:
    Jan 2011

    Posted 16 Mar 2011 Link to this post

    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

  5. Answer
    Ady
    Admin
    Ady avatar
    589 posts

    Posted 17 Mar 2011 Link to this post

    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
  6. Sally
    Sally avatar
    3 posts
    Member since:
    Aug 2015

    Posted 06 Jan Link to this post

    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)

     

  7. Sally
    Sally avatar
    3 posts
    Member since:
    Aug 2015

    Posted 06 Jan Link to this post

    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.

     

     

     

     

     

  8. Boyan
    Admin
    Boyan avatar
    100 posts

    Posted 08 Jan Link to this post

    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.
Back to Top
DevCraft banner