Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 5 of 5 total |
ALTER TABLE question |
Thu, Mar 9 2006 4:12 AM | Permanent Link |
"TorB." | Hi!
When I distribute big updates of my programs the clients has an option of keeping their existing database. In these cases only the program files get installed. I then need to make sure that the existing database is compatible with the existing program version. Today I use a special program for this. This program is automatically run after the installation, and it is executing a series og hard coded SQL calls which is checking every field in every table in the database and. if neccessary it is doing an ALTER TABLE / REDEFINE command. I would like to make the update program a bit more flexible. In stead of doing hard coded SQL calls I would like it to load and execute SQL files that I supply. Do I need one SQL file for each table or is it possible to do the process on all tables from one big SQL file? I also understand that the ALTER TABLE / REDEFINE command does nothing if the table already has the correct format. Can somebody confirm this? Kind regards TorB. |
Thu, Mar 9 2006 8:05 AM | Permanent Link |
"Ralf Mimoun" | TorB. wrote:
.... > I would like to make the update program a bit more flexible. In stead > of doing hard coded SQL calls I would like it to load and execute SQL > files that I supply. Do I need one SQL file for each table or is it > possible to do the process on all tables from one big SQL file? ne way to do so would be DB Extensions. The Schema component has versioning capability, so you can define what SQL statement must be executed to go from 2.01 to 2.02. I use that very successful in a huge system. Another way I use for an older application: I have all SQL statements to update eg. the Article table from 1.0 to 1.1 in another table, called Updates. The columns: Table name, Minor version, Major version, SQL statement, BLOB value (if I have to insert BLOBs), description. That table is compressed and streamed into a file by a small utility I wrote. That update file is distributed with each new version of the application, At startup, the application looks for the file. If it exists, the application loads and uncompress it, streams it in a table and compares the version numbers for each record. If the record has a higher version number than the existing table, the SQL statement is executed. After that, the user gets a message if something went wrong, and the update file is renamed. Works since years without problems. Ralf |
Thu, Mar 9 2006 8:59 AM | Permanent Link |
"Iztok Lajovic" | Tor,
we are using a bit different approach as Ralf does. Regarding possibility that the user wants to load data from archives with different creation date (in our case that could be a year or more) which could possibly contain data tables with different major and minor version numbers we have to dinamically convert table structure from any version number to today's version number. Each of our applications has a module with structure definition of all tables which are used by application. After restructuring particular table we use DBSYS's Reverse Engineer Table function and put in this module the latest structure of a table. After that we add manually code for description of structure difference from previous table version and at apply the difference with AlterTable function. For example: with TableToCreate do begin DatabaseName := baza.databaseName; // 'baza' is function parameter, ie actual DBISAMDatabase sessionName := baza.sessionName; TableName := 'trans'; Exclusive := True; if (not Exists) then begin with FieldDefs do begin Clear; Add('Ident', ftAutoInc, 0, False, '', '', '', '', fcNoChange, 0); Add('Logic', ftInteger, 0, False, '0', '', '', '', fcNoChange, 0); :::::: // more than 40 fields are inserted here Add('OznArh', ftString, 2, False, '', '', '', '', fcNoChange, 0); end; with IndexDefs do begin Clear; Add('', 'Ident', [ixPrimary, ixUnique], '', icNone); Add('logic', 'Logic;Ident', [], '', icNone); end; CreateTable(1060, 1, 11, False, '', 'transactions', 4096, 512, 0, '', StopWords,spaceChars,includeChars); // we have StopWords etc. defined separately end; active := false; // 1. structure change FieldDefs.Update; IndexDefs.update; if (userMajorVersion = 1) and (userMinorVersion = 0) then begin FieldDefs.insert(28,37,'Param',ftInteger,0,False,'0','','','',fcNoChange); AlterTable(1060, 1, 1, False, '', 'transactions', 4096, 512, -1, '', StopWords,spaceChars,includeChars); FieldDefs.Update; IndexDefs.update; end; // 2. structure change :::::: // for each table structure change here should be inserted code between 1. and 2. structure change // with appropriate code for concrete structure change When new user starts application for first time, the table is created, at subsequent runs only version numbers are tested: if user reloaded data from time before particular table structure change, the above code is applied and table structure is updated to today's structure. If there are more than one structure changes all of them will applied subsequently. HTH Iztok Lajovic |
Thu, Mar 9 2006 9:45 AM | Permanent Link |
"TorB." | Hi Ralf!
Thank you for your feedback, it sounds interesting. I'll look into this. Kind regards TorB. **** "Ralf Mimoun" <nospam@rad-on.de> skrev i melding news:C77F357B-3054-4E07-A2C3-D18B12620DF7@news.elevatesoft.com... > TorB. wrote: > ... >> I would like to make the update program a bit more flexible. In stead >> of doing hard coded SQL calls I would like it to load and execute SQL >> files that I supply. Do I need one SQL file for each table or is it >> possible to do the process on all tables from one big SQL file? > > ne way to do so would be DB Extensions. The Schema component has > versioning capability, so you can define what SQL statement must be > executed to go from 2.01 to 2.02. I use that very successful in a huge > system. > > Another way I use for an older application: I have all SQL statements to > update eg. the Article table from 1.0 to 1.1 in another table, called > Updates. The columns: Table name, Minor version, Major version, SQL > statement, BLOB value (if I have to insert BLOBs), description. That table > is compressed and streamed into a file by a small utility I wrote. That > update file is distributed with each new version of the application, At > startup, the application looks for the file. If it exists, the application > loads and uncompress it, streams it in a table and compares the version > numbers for each record. If the record has a higher version number than > the existing table, the SQL statement is executed. After that, the user > gets a message if something went wrong, and the update file is renamed. > Works since years without problems. > > Ralf |
Thu, Mar 9 2006 9:48 AM | Permanent Link |
"TorB." | Hi Iztok!
Thank you for your feedback. My point is that I do not want to hard code the ALTER TABLE process. I want to supply SQL files that are loaded and then executed. That gives me a possibility to only supply the SQL files in some cases. Kind regards TorB. ***** "Iztok Lajovic" <iztok.lajovic@amis.net> skrev i melding news:0ABE4EAB-D03B-4B27-A72C-15BCF02C6A03@news.elevatesoft.com... > Tor, > > we are using a bit different approach as Ralf does. Regarding possibility > that the user wants to load data from archives with different creation > date (in our case that could be a year or more) which could possibly > contain data tables with different major and minor version numbers we have > to dinamically convert table structure from any version number to today's > version number. > > Each of our applications has a module with structure definition of all > tables which are used by application. After restructuring particular table > we use DBSYS's Reverse Engineer Table function and put in this module the > latest structure of a table. After that we add manually code for > description of structure difference from previous table version and at > apply the difference with AlterTable function. > > For example: > > with TableToCreate do begin > DatabaseName := baza.databaseName; // 'baza' is function parameter, > ie actual DBISAMDatabase > sessionName := baza.sessionName; > TableName := 'trans'; > Exclusive := True; > if (not Exists) then begin > with FieldDefs do begin > Clear; > Add('Ident', ftAutoInc, 0, False, '', '', '', '', fcNoChange, 0); > Add('Logic', ftInteger, 0, False, '0', '', '', '', fcNoChange, > 0); > :::::: // more > than 40 fields are inserted here > Add('OznArh', ftString, 2, False, '', '', '', '', fcNoChange, 0); > end; > with IndexDefs do begin > Clear; > Add('', 'Ident', [ixPrimary, ixUnique], '', icNone); > Add('logic', 'Logic;Ident', [], '', icNone); > end; > CreateTable(1060, 1, 11, False, '', 'transactions', 4096, 512, 0, > '', > StopWords,spaceChars,includeChars); // we have > StopWords etc. defined separately > end; > active := false; > // 1. structure change > FieldDefs.Update; > IndexDefs.update; > if (userMajorVersion = 1) and (userMinorVersion = 0) then begin > > FieldDefs.insert(28,37,'Param',ftInteger,0,False,'0','','','',fcNoChange); > AlterTable(1060, 1, 1, False, '', 'transactions', 4096, 512, -1, > '', > StopWords,spaceChars,includeChars); > FieldDefs.Update; > IndexDefs.update; > end; > > // 2. structure change > :::::: // for each table structure change here should be > inserted code between 1. and 2. structure change > // with appropriate code for concrete structure change > > > When new user starts application for first time, the table is created, at > subsequent runs only version numbers are tested: if user reloaded data > from time before particular table structure change, the above code is > applied and table structure is updated to today's structure. If there are > more than one structure changes all of them will applied subsequently. > > HTH > > Iztok Lajovic > > > |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |