Red Gate forums :: View topic - Procedure to generate migration scripts
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
Schema Compare for Oracle Beta
Schema Compare for Oracle Beta forum

Procedure to generate migration scripts

Search in Schema Compare for Oracle Beta forum
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.
Jump to:  
Author Message
kAlvaro



Joined: 18 Jan 2010
Posts: 9
Location: Spain

PostPosted: Mon Jan 18, 2010 5:03 pm    Post subject: Procedure to generate migration scripts Reply with quote

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:

CREATE TABLE "FOO_V1"."MY_TABLE" (


... is not the same as:

Code:
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
Back to top
View user's profile Send private message
alice.easey



Joined: 27 Feb 2008
Posts: 93
Location: Red Gate

PostPosted: Tue Jan 19, 2010 10:47 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Michelle Taylor



Joined: 30 Oct 2006
Posts: 528
Location: Red Gate Software

PostPosted: Tue Jan 19, 2010 4:51 pm    Post subject: Reply with quote

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 Smile.
Back to top
View user's profile Send private message
kAlvaro



Joined: 18 Jan 2010
Posts: 9
Location: Spain

PostPosted: Tue Jan 19, 2010 5:22 pm    Post subject: Re: Reply with quote

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 Smile.


I decided to test and post the results...

Code:
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
Back to top
View user's profile Send private message
Michelle Taylor



Joined: 30 Oct 2006
Posts: 528
Location: Red Gate Software

PostPosted: Wed Jan 20, 2010 5:44 pm    Post subject: Reply with quote

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...
Back to top
View user's profile Send private message
Display posts from previous:   
This topic is locked: you cannot edit posts or make replies. All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group