Re: Comparing table structures - Question...

In article <39213CFA.59258A90@compuserve.com>, Wolfgang Zager <zager@compuserve.com> writes >"Stephens, Alan" wrote: >> >> Using Informix Dynamic Server 7.3 on Solaris >> >> I have an installation procedure for my software which at the moment >> isnt >> very intelligent. Each patch has to contain any alterations to the table >> structures needed. Then every few mths I have to ammalgamate all >> of those alter scripts into one for simplicity. >> >> What I want to have as part of my install, is a way of comparing the >> structure of each table on the customers database with the latest >> structure in the install, and if necessary alter the table structure on >> the fly. >> >> I figure this is possible and somebody has probably done it before. >> Presumably one way would be to run dbschema -d databasename -t tabname >> , parse the table structure, compare it with my uptodate table >> structure, and alter >> it if necessary. However, this sounds like a very complex and >> troublesome task. >> >> Are there any other neater solutions ? >> >> TIA, >> Al. > >Something which works very well for us is to create a table, in which >You store a version number for all Your other tables. >When doing an alter, we first check the stored version number an then >decide which alters have to be done. > >Wolfgang This works fine for us too. We have a tcl script per table that combines both the table creation and subsequent modification in the one file. It compares it's internal version against the one in the database and applies changes accordingly. Turns updating customer databases into a pretty 'routine' procedure! Andrew Lennard andy@kontron.demon.co.uk

>>>>> " " == Andy Lennard <andy@kontron.demon.co.uk> writes: > We have a tcl script per table that combines both the table > creation and subsequent modification in the one file. It > compares it's internal version against the one in the database > and applies changes accordingly. > Turns updating customer databases into a pretty 'routine' > procedure! Can you post this tcl script? I'd like to try it out. Mark -- "A fanatic is one who can't change his mind and won't change the subject." -- Winston Churchill

In article <dc4s7ymsjd.fsf@shower.nws.noaa.gov>, Mark Oberfield <oberfiel@haze.nws.noaa.gov> writes >>>>>> " " == Andy Lennard <andy@kontron.demon.co.uk> writes: > > > We have a tcl script per table that combines both the table > > creation and subsequent modification in the one file. It > > compares it's internal version against the one in the database > > and applies changes accordingly. > > > Turns updating customer databases into a pretty 'routine' > > procedure! > >Can you post this tcl script? I'd like to try it out. > >Mark There is a master script to handle database creation, if necessary, and one script per table. The master script has a list of all tables, and invokes the individual table scripts. The individual table scripts look like... # tcl script to create database table "note_records" # Get the current version number of this table set fd [sql open "select version from dbase_tables where tabname = \\"note_records\\""] set version [lindex [sql fetch $fd] 0] sql close $fd #------------------------------------------------------- # Version 1 if {$version < 1} { sql run "create table note_records \\ (\\ note_serial_int integer not null,\\ edit_count integer not null,\\ line_number integer not null,\\ key char(40),\\ note_datim integer not null,\\ note_text char(200) not null,\\ hcp_id integer not null,\\ enter_datim integer not null,\\ note_type char(20) not null,\\ datim_to integer\\ );" sql run "create index note_rec1 on note_records (note_serial_int);" set version 1 } #------------------------------------------------------- # Version 2 if {$version < 2} { sql run "alter table note_records modify next size 128" set version 2 } #------------------------------------------------------- # Version 3 if {$version < 3} { # Insert your updates here # Set version # Remember to set the variable "version" to the appropriate value when you've inserted your change } # Update the version number of the table sql run "update dbase_tables set version = $version where tabname = \\"note_records\\"" Hope that helps. Andy. Andrew Lennard andy@kontron.demon.co.uk