Procedure to generate migration scripts

Early Access Program for Schema Compare for Oracle

Moderators: David Atkinson, eddie davis, richardjm, alice.easey, Tom Harris

Procedure to generate migration scripts

Postby kAlvaro » Mon Jan 18, 2010 5:03 pm

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...
Code: Select all
CREATE TABLE \"FOO_V1\".\"MY_TABLE\" (


... is not the same as:

Code: Select all
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.
Álvaro G. Vicario - Burgos, Spain
kAlvaro
 
Posts: 9
Joined: Mon Jan 18, 2010 11:33 am
Location: Spain

Postby alice.easey » Tue Jan 19, 2010 10:47 am

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.easey
 
Posts: 93
Joined: Wed Feb 27, 2008 12:56 pm
Location: Red Gate

Postby Michelle Taylor » Tue Jan 19, 2010 4:51 pm

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 Taylor
 
Posts: 529
Joined: Mon Oct 30, 2006 12:45 pm
Location: Red Gate Software

Re:

Postby kAlvaro » Tue Jan 19, 2010 5:22 pm

Michelle Taylor wrote: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...

Code: Select all
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.
Álvaro G. Vicario - Burgos, Spain
kAlvaro
 
Posts: 9
Joined: Mon Jan 18, 2010 11:33 am
Location: Spain

Postby Michelle Taylor » Wed Jan 20, 2010 5:44 pm

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 Taylor
 
Posts: 529
Joined: Mon Oct 30, 2006 12:45 pm
Location: Red Gate Software


Return to Schema Compare for Oracle Beta

Who is online

Users browsing this forum: No registered users and 1 guest