How can we help you today? How can we help you today?

Procedure to generate migration scripts

I'm developing a PHP application with Oracle backend. I keep all the SQL code used to generate the DB objects (tables, views, functions...) in plain text files and all the source code is stored into a Subversion repository. When it's time to release a new version I create a tag within the repository. It's easy for me to install any given version from scratch but I'm looking for a way to generate a SQL script that allows to upgrade from one version to another in a live database server, i.e., were there's already user data that cannot be discarded.

My first approach was to create two new databases in my local development box (Oracle 10g XE):

- FOO_V1
- FOO_V2

I fed each one with the appropriate objects from each version. Then I configured FOO_V1 as TARGET and FOO_V2 as SOURCE. The synchronization wizard generates code that looks correct (beyond some minor glitches) but I the comparison result panel claims that all objects that exist in both DBs are different because... 
CREATE TABLE "FOO_V1"."MY_TABLE" (

... is not the same as:
CREATE TABLE "FOO_V2"."MY_TABLE" (

Am I doing something wrong? Do I need to get a second server so both databases can have the same name? Is it just work in progress?

I'd appreciate any tip.


P.S. Object names are double-quoted in the generated SQL. If I recall correctly, that makes them case-sensible for the Oracle engine, which is not the case of either databases. Please take it into account for the final release and make it automatic or configurable.
kAlvaro
0

Comments

4 comments

Sort by
  • Alice E
    Hi,

    Thanks for your feedback.

    For objects that are identical except for the schema name, the SQL Differences pane at the bottom will highlight the schema difference in orange, but the object should appear in the 'identical objects' group.

    If this isn't the case then is it possible for you to send me (alice.easey@red-gate.com) an example of the script on both sides (source and target) where the object is identical but appearing as different?

    Thanks,

    Alice.
    Alice E
    0
  • Michelle T
    While quoting the identifiers makes that particular instance case-sensitive, I believe that you can continue to refer to them in a case-insensitive manner afterwards as long as they remain in all upper case. If this is not how it works then please correct me :).
    Michelle T
    0
  • kAlvaro
    While quoting the identifiers makes that particular instance case-sensitive, I believe that you can continue to refer to them in a case-insensitive manner afterwards as long as they remain in all upper case. If this is not how it works then please correct me :).

    I decided to test and post the results...
    CREATE TABLE foo (ID NUMBER);
INSERT INTO foo (ID) VALUES (1); -- OK
INSERT INTO "foo" (ID) VALUES (1); -- ERR
INSERT INTO FOO (ID) VALUES (1); -- OK
INSERT INTO "FOO" (ID) VALUES (1); -- OK

CREATE TABLE "foo" (ID NUMBER);
INSERT INTO foo (ID) VALUES (1); -- ERR
INSERT INTO "foo" (ID) VALUES (1); -- OK
INSERT INTO FOO (ID) VALUES (1); -- ERR
INSERT INTO "FOO" (ID) VALUES (1); -- ERR

    ... but I got lost... Oracle is totally insane.
    kAlvaro
    0
  • Michelle T
    I believe the rule is that identifiers are actually case-sensitive all the time, but if you don't put quotes around your input Oracle kindly turns everything into uppercase for you...
    Michelle T
    0

Add comment

Please sign in to leave a comment.