Christopher Browne wrote:
> sowjanya v <sowjuec at gmail.com> writes:
>   
>> I have two databases db1 and db2. db2 is replica of db1. Now i have added a
>> column in a table in db1. Since schema changes do not propogate by itself to
>> db2, need to execute execute script. But the input for that is a sql file with
>> all  difference in schema. Hence please let me know how replicate the schema
>> changes. If i do the diff of two schemas, it only shows the lines where the
>> difference is present and not the sql command to be executed on db2 to make it
>> same as db1. Please do reply immediately.
>>     
>
> It sounds to me as though you're trying to use an SCM's "diff" command
> to find the difference between schemas.
>
> At simplest level, this would be like having two files:
>
> % cat schema1.sql
> create table t1 (
>   id serial primary key,
>   name text not null unique,
>   created_on timestamptz default now()
> );
> % cat schema2.sql
> create table t1 (
>   id serial primary key,
>   name text not null unique,
>   created_on timestamptz NOT NULL default now(),
>   updated_on timestamptz NOT NULL default now()
> );
> % diff schema1.sql schema2.sql
> 4c4,5
> <   created_on timestamptz default now()
> ---
>   
>>   created_on timestamptz NOT NULL default now(),
>>   updated_on timestamptz NOT NULL default now()
>>     
>
> If you're using CVS, Subversion, or such, the commands might be a bit
> different, but the output would be pretty similar.
>
> What you'd presumably need, in order to set up EXECUTE SCRIPT to make
> this change, is a SQL script consisting of something like the following:
>
>   -- Upgrade from schema1.sql to schema2.sql
>   alter table t1 alter column created_on set not null;
>   alter table t1 add column updated_on timestamptz;
>   update t1 set updated_on = '2010-05-10'; -- somewhat arbitrary value
>   alter table t1 alter column updated_on set default now();
>   alter table t1 alter column updated_on set not null;
>
> There isn't a way to get a text-file-oriented SCM to generate that set
> of 5 SQL statements.
>
> There exist some tools that *try* to create "differencing scripts."
> Some of our folks at Afilias have had some success with DBSolo
> <http://www.dbsolo.com/>, though that success was fairly limited.
>   
There are other tools like Aquafold (http://www.aquafold.com) which does 
a good job as well.
> DBSolo might be able to figure out the 4 ALTER TABLE statements, but
> it's not aware that the data requires alteration (e.g. - the UPDATE
> statement), and that's not something that seems reasonable to expect a
> tool to mechanically figure out for you.
>
> In effect, you need to generate the update script.
>
> And, not inicidentally, if you've already altered the first database,
> db1, then replication is more than likely already quite badly broken.
> Cleaning up after the problem is liable to be a lot more burdensome in
> understanding all the details.
>   


-- 
Chirag Dave  416-673-4102
Database Administrator, Afilias Canada Corp.
cdave at ca.afilias.info