Database schema update

8 posts, 0 answers
  1. Saene
    Saene avatar
    13 posts
    Member since:
    Sep 2008

    Posted 12 Nov 2008 Link to this post

    Hi,

    I have a database schema issue, and I would like to have some opinions.

    Imagine a object car. In the first version of the software I develop, my customers requested my to manage 4 wheels on each car: so I had a collection of wheels in my class car. 3 tables were created:
    • car
    • wheel
    • car_wheel

    In the second version of the software, my customers informed my that they did not need 4 wheels anymore, but only one per car. No problem for me, I just had to remove the wheel collection from my class car, and instead put a attribute wheel.
    2 tables are needed in that case

    • car
    • wheel

    So when I update the database schema with the VSChema utility, everything works find, except that the table car_wheel remains in the database.
    When I want to remove a car from the database, I first remove the associated wheel, and when I want to remove the car itself, an error is raised, telling that some reference of the car still exist in the car_wheel table, and that the removal of the car is forbidden.

    Am I missing something in the schema update process, or do I have a possibility to remove the unused table car_wheel?

    If not I have to remove all entries from the table manually before trying to remove my car?

    Regards,

    Antoine.

  2. Alfred Ortega
    Alfred Ortega avatar
    193 posts
    Member since:
    May 2005

    Posted 12 Nov 2008 Link to this post

    Telerik,
    I think this is the same bug I noticed (See Post Here) in that the ORM mapping file is not being regenerated, only the code files.

    Al
  3. DevCraft banner
  4. Saene
    Saene avatar
    13 posts
    Member since:
    Sep 2008

    Posted 14 Nov 2008 Link to this post

    Hi,

    do you think there is a way to have a workaround concerning this problem, as it is a blocking point for me.

    I use the VSchema to update the database, and all my customers have not the same version of the software, and thus the same database schema.

    I want to let VSchema manage the database updates.

    Thanks for your response,

    Antoine.
  5. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 15 Nov 2008 Link to this post

    Hello Antoine,

    There is an still artifact of your first schema creation steps in the database. Because the join table generated by your first schema generation has a foreign key reference to car it keeps your data inserted into the first schema in a no more deletable state.

    The only way is to drop the join table with all constraints.

    There are several ways to do that. My prefered way is not to ship vschema to the customer but let vschema generate the dll script that can be applied to the customer's database during update.

    If you once have the ddl script it is easy to add the drop statements.

    To do so vschema has the -direct option. If that is set it to -direct- we are only generating the script. If you do not want to apply the dll script inside the installer you can still use vschema and let it execute your ddl script only.

    Kind regards,
    Jan Blessenohl
    the Telerik team

    Check out Telerik Trainer, the state of the art learning tool for Telerik products.
  6. Saene
    Saene avatar
    13 posts
    Member since:
    Sep 2008

    Posted 19 Nov 2008 Link to this post

    Thanks Jan for your response.

    However, I really don't want to change the way the update of database schema is done. I find very convenient the fact that VSChema makes all the job alone, without having to generate and update the schema manually.

    You say: "Because the join table generated by your first schema generation has a foreign key reference to car it keeps your data inserted into the first schema in a no more deletable state."

    But this join table is not used anymore, and I don't see why the VSChema should not manage this case. To my mind, he should be able to detect that this table is not used, and delete it.

    Anyway, another solution for me would be either to delete this table from my application, or at least to be able to empty it, so that no integrity errors would occur anymore when deleting my cars and their new single wheels.

    Is there a way to do that?

    Regards,

    Antoine.
  7. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 24 Nov 2008 Link to this post

    Hello Antoine,

    yes, you can do that.

    We do not want to delete tables that have nothing to do with us. With MS Sql Server it might be OK to clean up, but if you think about Oracle where many schemas are in one database you do not want to drop the tables of you collogues.

    All the best,
    Jan Blessenohl
    the Telerik team

    Check out Telerik Trainer, the state of the art learning tool for Telerik products.
  8. Saene
    Saene avatar
    13 posts
    Member since:
    Sep 2008

    Posted 24 Nov 2008 Link to this post

    Thanks for you response,

    I'm sorry to insist, by I do not know how to remove or clear the table from the application, as
    • The table is not known and cannot be accessed by an OQL query
    • The SQL queries forbid any other instruction than SELECT

    Any help would be welcome,

    Antoine.

  9. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 24 Nov 2008 Link to this post

    Hi Saene,

    There are several ways to do it. One is to use vschema.exe on the client side to execute an sql script that drops the table.

    If you want to do it inside your code you can open an ADO.NET connection in parallel and execute the drop directly. Or you can make a dummy class without fields that maps to the join table (just set the table name). This will drop the additonal columns then.

    Regards,
    Jan Blessenohl
    the Telerik team

    Check out Telerik Trainer, the state of the art learning tool for Telerik products.
Back to Top
DevCraft banner