Re: Re: Automatically altering tables from different schema's

I want to point out that the problem is more subtle than you may think

Indeed. The difference between two versions of a table can be missing columns, which is fairly easy to manage, or more subtle things like NULL/NOT NULL, primary key, constraints, and a host of other things.

Consider Sybase's CREATE TABLE syntax:

create table [database .[owner ].]table_name (column_name datatype
    [default {constant_expression  | user | null}]
    {[{identity | null | not null}]
        [off row | [ in row [ (size_in_bytes) ] ]
    [[constraint constraint_name ]
        {{unique | primary key}
        [clustered | nonclustered] [asc | desc]
        [with { fillfactor = pct, max_rows_per_page = num_rows, }
                reservepagegap = num_pages }]
        [on segment_name]
        | references [[database .]owner .]ref_table
            [(ref_column )]
            [match full]
            | check (search_condition)}]}
            [match full]...
    | [constraint constraint_name]
        {{unique | primary key} 
            [clustered | nonclustered]
            (column_name [asc | desc]
                [{, column_name [asc | desc]}...])
            [with { fillfactor = pct, max_rows_per_page = num_rows ,
                reservepagegap = num_pages } ]
            [on segment_name]
        |foreign key (column_name [{,column_name}...])
            references [[database.]owner.]ref_table
                [(ref_column [{, ref_column}...])]
                [match full]
        | check (search_condition) ... }
    [{, {next_column | next_constraint}}...])
    [lock {datarows | datapages | allpages }]
    [with { max_rows_per_page = num_rows, 
            exp_row_size = num_bytes, 
            reservepagegap = num_pages,
            identity_gap = value } ] 
    [on segment_name ]
    [ [ external table ] at pathname ]
[download]

Obviously, creating an UPDATE TABLE request that will automatically make the two versions of a table identical becomes non-trivial if all of these syntax elements need to be handled.

Comment on Re: Re: Automatically altering tables from different schema's Download Code

Replies are listed 'Best First'.
Re: Re: Re: Automatically altering tables from different schema's by liz (Monsignor) on Jan 08, 2004 at 19:33 UTC
I was going to go for MySQL and SQLite, because those are the itches that I need to scratch. Other will have to add their favourite database drivers themselves ;-) Liz	[reply]