locked
Manual erasing columns in DEPLOYED application RRS feed

  • Question

  • Hello!

    i wonder if anyone has experience with following...

    LightSwitch framework will make new colmns in tables of already deployed applications ...

    but at updating WILL NOT REMOVE SQL columns which are erased in LightSwitch...   Can i erase those columns in SQL MANAGEMET STUDIO?

    i.m. they can be erased, but is it SAFE?

    i.m.    if i have PRODUCTS table and i have Column    PRODUCT.PICTURE - when i do not need that Column anymore, can i manualy erase it, as LightSwitch will not erase it on it's own?

    if this is safe -- i can find it SQL Management under   Databases//MYDATABASE//Tables//dbo.Products//Columns//PICTURE

    as i will not use this column anymore - CAN I ERASE IT?

    if i erase it here, should i erase it on some other places, as well?

    should i find/erase this field at some other places ?

    or is it best just to let it be?

    THANKS AGAIN!

    Monday, March 26, 2012 10:41 PM

Answers

  • /* Backup the table before you drop the column */ SELECT * INTO dbo.myDBobject_Backup_20120328 FROM dbo.myDBobject GO /* Drop the target column from the table */ ALTER TABLE dbo.myDBObject DROP COLUMN RedundantPictureColumn GO /* Confirm that the column has been removed */ SELECT * FROM dbo.myDBObject GO

    Hope this helps.  I thought it might be useful to show you the T-SQL code to allow you to backup the table data before dropping the column in a manner that allows you to not have to organize for a full DB backup before you proceed.

    If you want to restore the column concerned:

    /* Add the column back to the original table */
    ALTER TABLE
    ADD RedundantPictureColumn nvarbinary(max) NULL
    GO
    /* Then we update the existing records in the table with the value in the backup table. */
    ;WITH bkp as 
    (
      SELECT 
        myObjectId, -- Assuming a simple Id field as the PK
        RedundantPictureColumn
      FROM dbo.myDBObject_Backup_20120328
      WHERE RedundantPictureColumn IS NOT NULL -- ignores blank rows
    )
    UPDATE mo
    SET mo.RedundantPictureColumn = bkp.RedundantPictureColumn
    FROM dbo.MyObject mo
    INNER JOIN bkp
    ON mo.MyObjectId = bkp.myObjectId
    /* Then do a simple select to confirm that you've got the data back. */
    SELECT * FROM dbo.myObject

    HTH

    Jeremy Huppatz
    Managing Consultant: Solitaire Systems

    • Proposed as answer by Otomii Lu Thursday, March 29, 2012 2:21 AM
    • Marked as answer by Otomii Lu Thursday, April 5, 2012 2:06 AM
    Wednesday, March 28, 2012 1:30 PM
  • Kostas,

    yes, you're right.  And it works like that.

    1. i first remove column of table in LightSwitch (that column is not to be used any more).

    2. publish fails - as it wanrs about possible data loss

    3. running Sql Management Studio - i remove that column manually

    4. re-publish - and all works great.

    i hope that this is "safe" up to certain degree?    i.m. i am making backups, but there is no reason for databse to stop working at some point, or something like that?

    • Proposed as answer by Otomii Lu Thursday, March 29, 2012 2:21 AM
    • Marked as answer by Otomii Lu Thursday, April 5, 2012 2:06 AM
    Wednesday, March 28, 2012 6:16 PM

All replies

  • Hi,

    "i.m. they can be erased, but is it SAFE?"

    it is not safe.

    There is no good walkthrough for your issue for you want to erase columns and a certain amount of generated codes need to be changed.

    Maybe you should backup your data, and create a new database for your application.

    (I will try to find more ways to deal with this issue)
    • Edited by Otomii Lu Wednesday, March 28, 2012 7:23 AM
    Wednesday, March 28, 2012 3:36 AM
  • @Otomii

    If the columns in the tables do not represent entity properties any more in the model, why not to delete them? My understanding is that LS fails correctly update the database schema, which is something I can live with. If there is absolutely no reference to this columns/fields that are deprecated in the model why not to delete them? Although I do not use native LS datasources I would be very interested to know that. If you could please elaborate.

    @Miroslav

    The best way to test is what Otomi suggests. Backup and then remove the columns from the database schema and see what happens. For an even more complete test. After deleting try to re-deploy and see what happens.


    1+1 = 3 for large values of 1

    Wednesday, March 28, 2012 6:26 AM
  • /* Backup the table before you drop the column */ SELECT * INTO dbo.myDBobject_Backup_20120328 FROM dbo.myDBobject GO /* Drop the target column from the table */ ALTER TABLE dbo.myDBObject DROP COLUMN RedundantPictureColumn GO /* Confirm that the column has been removed */ SELECT * FROM dbo.myDBObject GO

    Hope this helps.  I thought it might be useful to show you the T-SQL code to allow you to backup the table data before dropping the column in a manner that allows you to not have to organize for a full DB backup before you proceed.

    If you want to restore the column concerned:

    /* Add the column back to the original table */
    ALTER TABLE
    ADD RedundantPictureColumn nvarbinary(max) NULL
    GO
    /* Then we update the existing records in the table with the value in the backup table. */
    ;WITH bkp as 
    (
      SELECT 
        myObjectId, -- Assuming a simple Id field as the PK
        RedundantPictureColumn
      FROM dbo.myDBObject_Backup_20120328
      WHERE RedundantPictureColumn IS NOT NULL -- ignores blank rows
    )
    UPDATE mo
    SET mo.RedundantPictureColumn = bkp.RedundantPictureColumn
    FROM dbo.MyObject mo
    INNER JOIN bkp
    ON mo.MyObjectId = bkp.myObjectId
    /* Then do a simple select to confirm that you've got the data back. */
    SELECT * FROM dbo.myObject

    HTH

    Jeremy Huppatz
    Managing Consultant: Solitaire Systems

    • Proposed as answer by Otomii Lu Thursday, March 29, 2012 2:21 AM
    • Marked as answer by Otomii Lu Thursday, April 5, 2012 2:06 AM
    Wednesday, March 28, 2012 1:30 PM
  • Kostas,

    yes, you're right.  And it works like that.

    1. i first remove column of table in LightSwitch (that column is not to be used any more).

    2. publish fails - as it wanrs about possible data loss

    3. running Sql Management Studio - i remove that column manually

    4. re-publish - and all works great.

    i hope that this is "safe" up to certain degree?    i.m. i am making backups, but there is no reason for databse to stop working at some point, or something like that?

    • Proposed as answer by Otomii Lu Thursday, March 29, 2012 2:21 AM
    • Marked as answer by Otomii Lu Thursday, April 5, 2012 2:06 AM
    Wednesday, March 28, 2012 6:16 PM
  • If Otomii, speaking on behalf of MS, says it's not safe I cannot say that you will have no problem. All I can say is. trusting my instincts, if I had a similar issue I would change the schema by hand. But I would take my own responsibility for it. ;-).

    1+1 = 3 for large values of 1

    Wednesday, March 28, 2012 6:31 PM
  • For what it's worth, if you're working with an extrinsic data source (i.e. attached external data) you can update your remote schema, update the data source and "Hey, presto!" - schema updated.  This is why I don't like working with the intrinsic data sources.

    The T-SQL meta-code I wrote above will be perfectly safe in an extrinsic database.  Against an INTRINSIC database, Kosta and Otomii are right... you might be asking for trouble.


    Jeremy Huppatz
    Managing Consultant: Solitaire Systems

    Wednesday, March 28, 2012 6:54 PM
  • Hi Miroslav and Kostas,

    Since Schema Deployment has the potential of changing a real production database, decision was made back then to be really safe:
    Back up the database before changes.(it is the most important thing we should do)
    Don’t ignore column orders.
    Check for new constraints.
    And even with the backup, block on possible data loss.

    Hi Miroslav,

    I'm glad to see that it works great after your operating.

    I cannot confirm it is safe(Maybe I miss something), but personally, I think if it is working after your operating, you have successfully.

    I have some reasons to prove my speculation.

    1.For the LightSwitch is using MVVM model, so the screens have no relationship with the viewmodels(viewmodel is the data in the screen,it is on the left of the screen designer). So if we delete a column, we should delete the column in viewmodel and screen also to make the project what we want. And I've created a simple project and found that if we delete a column in the table, entity's viewmodel and screen have also deleted the column. The deleting operation will change the whole project to a new style.

    2.Database just provide data.For the whole project have changed, I think if you can run the project with all operating works properly, I consider a success.

    Thursday, March 29, 2012 2:11 AM
  • Hi Miroslav,

    As you mentioned,"all works great."

    I think all the generated code have changed to what we want for the generate operation is base on our table structure.

    Hope you can continue this topic if you have any question or trouble.

    And Big thinks to gedulous for the safe solution.



    • Edited by Otomii Lu Thursday, March 29, 2012 2:25 AM
    Thursday, March 29, 2012 2:21 AM
  • Hey Otomii,

    i am contuning to "experiment" about this topic as I (we? :) )  need to find way of deloying  elegant & robust application for my customer.

    once i get this more/less satisfactory, i will provide Forum readers with TEST PASSWORD for them to check my application - both to help me and also (perhaps) to learn something new and maybe get new inspiration on "level of depth" LightSwitch application can have - as i am woprkign here with some really almost extreme things for "coding optional" system.

    What do You think about this? 

    Thursday, March 29, 2012 5:48 PM
  • Thursday, March 29, 2012 6:05 PM
  • :)

    Hi Miroslav,

    Thanks for your effort, I'm glad that you can share the forums with your experience of your work.:)

    Friday, March 30, 2012 2:43 AM