This discussion is archived
4 Replies Latest reply: Apr 12, 2011 3:41 AM by 671304 RSS

Database schema changed - cannot open workbench project

671304 Newbie
Currently Being Moderated
We are restructuring our database, and since we did not delete the publications/publication items first, I am now unable to open the Workbench project in our test environment.

About 30 out of 55 tables have been changed (removed columns, some tables dropped, some structural changes)

When this happened earlier, I would manually edit the project XML file to get i to open. It has been a painful lesson that I obviously did not learn from...

How can I, with the least amount of effort, open my project again? Would it be easier to uninstall Olite Mobile server and delete the related tables in the DB and start from scratch?

Thanks.

Marius
  • 1. Re: Database schema changed - cannot open workbench project
    winnie.hu Newbie
    Currently Being Moderated
    Hi Marius,

    Since the base tables' schema changed, so the SQL queries for the related pub items don't make sense any more.

    What project did you mean? Did you mean .mpj project created by the Mobile Development Workbench or your own application project?

    For the MDW's .mpj project, that might not be usable any more.

    For those base tables whose schema changed, you need to:
    1. Remove the related pub item from it's encompassing publication;
    2. Drop the old pub item, re-create new pub item base on the same table;
    3. Add the new created pub item to its previous publication again if you still want this.

    The re-installation of Mobile Server won't help correct the pub items to refer to the new tables schema. If you re-install your Mobile Server instance referring to the old Oracle Database, then the repository will remain unchanged. If you want to re-build the Mobile Server's repository (in the Oracle Database) from scratch, then you can do this instead:
    1. Login Oracle Database with "SYSTEM" or "DBA" privileges;
    2. Drop mobileadmin schema cascade;
    3. Run %ORACLE_HOME%\mobile\server\admin\repwizard.bat(on Windows) or repwizard.sh(on Linux) to re-create the repository.
    - Note: Are you experimenting in your test environment or production environment? Be very careful to re-create the Mobile Server's repository, since this will cleanup all publications, publication items, users, applications already created on the Mobile Server, as you said, this equals to re-installing the Mobile Server from scratch.

    Winnie Hu
    Oracle Berkeley DB and Oracle Lite Development
    winnie.hu at oracle dot com

    Edited by: winnie.hu on Apr 11, 2011 10:55 PM

    Edited by: winnie.hu on Apr 11, 2011 10:56 PM
  • 2. Re: Database schema changed - cannot open workbench project
    671304 Newbie
    Currently Being Moderated
    Thanks, Winnie.

    I realize that my post could be more presicely formulated, but you set me on the right track, I think.

    Yes, it's the .mpj project file I was talking about. Since the app schema is changed, I get the error message stating that my colums count does not match (or something similar, I don't have the message right now) and the project won't load. Which is expected.

    Since I don't know enough about the inner workings of Mobile server, I thought I needed to open the project so that I could remove the pub items in a "tidy manner".

    The other option (from scrach):

    I didn't know about the repwizard.bat, so I thought that the easiest way I could make sure that Mobile server was "cleanly" configured was by dropping the mobileadmin schema, dropping the error queue (ceq$...) and other "mobile server tables" in my app schema and do a clean install.

    Since this is my test evironment, I will go for the "from scratch" option. I have all the new publication items SQL defined, so it should not be that much of a job, hopefully.

    I see from your three "from-scratch" steps nothing is done to the "mobile server tables" in the app schema (CEQ$'s on so on). I presume I need to manually drop these as well?

    Thanks.

    Marius
  • 3. Re: Database schema changed - cannot open workbench project
    winnie.hu Newbie
    Currently Being Moderated
    Hi Marius,

    The repwizard.bat resides in %ORACLE_HOME%\mobile\server\admin folder, the %ORACLE_HOME% is the root folder where you installed your Mobile Server instance. This option can save you some time by avoiding coping binaries from installation package to the %ORACLE_HOME%.

    Yes, the error queue tables as well as other tables (CEQ$, CVR$, CLG$), reside in the application's schema, and you need to manually drop them. Mobile Server create additional tables serving your application table in this format: <TABLE_NAME_PREFIX>$<APPLICATION_TABLE_NAME>, for example, you can list all the serving tables from your application's schema by below query:

    SQL> select table_name from all_tables where table_name like '%$APPLICATION_TABLE_NAME%';
    TABLE_NAME
    ---------------------
    APPLICATION_TABLE_0001
    CEQ$APPLICATION_TABLE_0001
    CVR$APPLICATION_TABLE_0001
    CLG$APPLICATION_TABLE_0001

    Winnie Hu
    Oracle Berkeley DB and Oracle Lite Development
    winnie.hu at oracle dot com

    Edited by: winnie.hu on Apr 12, 2011 1:55 AM

    Edited by: winnie.hu on Apr 12, 2011 2:01 AM
  • 4. Re: Database schema changed - cannot open workbench project
    671304 Newbie
    Currently Being Moderated
    Thank you Winnie, you have helped save a lot of time!

    Marius

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points