| Author |
Message |
kAlvaro
Joined: 18 Jan 2010
Posts: 9
Location: Spain
|
 Posted: Mon Jan 18, 2010 5:03 pm Post subject: 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...
| 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 |
|
 |
alice.easey
Joined: 27 Feb 2008
Posts: 93
Location: Red Gate
|
| Posted: Tue Jan 19, 2010 10:47 am Post subject: |
|
|
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 |
|
|
Michelle Taylor
Joined: 30 Oct 2006
Posts: 519
Location: Red Gate Software
|
| Posted: Tue Jan 19, 2010 4:51 pm Post subject: |
|
|
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  . |
|
| Back to top |
|
|
kAlvaro
Joined: 18 Jan 2010
Posts: 9
Location: Spain
|
| Posted: Tue Jan 19, 2010 5:22 pm Post subject: Re: |
|
|
| 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: |
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 |
|
|
Michelle Taylor
Joined: 30 Oct 2006
Posts: 519
Location: Red Gate Software
|
| Posted: Wed Jan 20, 2010 5:44 pm Post subject: |
|
|
| 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 |
|
|
|
|
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
