Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 17 total |
test DB structure on startup |
Thu, Nov 30 2006 8:36 AM | Permanent Link |
"Harry de Boer" | LS
What's the best way to programmaticly test on startup of an app that: -all tables are there -all fields are there -all fields have the correct length, type and constraints -all indexes are there -all blobfiles are there is there an example somewhere (we dont'own the source) -dbIsam 3.3 D6 Regards, Harry |
Thu, Nov 30 2006 9:05 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Harry
If you don't have the source you'll need a second app to check things out before you start the first app (I'd probably use ShellExecute, but there are other ways of launching apps from within another app). I don't have an example but two ways suggest themselves FIRST IDEA 1. Make sure you have a complete, empty copy of all the tables stored in another directory. 2. Create an app with 2 TDBISAMTables on it 3. Use FindFirst/FindNext to generate a list of the .dat files in the known complete directory (or create a list of the tables that should be there) 4. For each table in the known good directory assign the directory to table1's databasename, and its name to the tablename, assign the apps directory to table2's databasename and the same name to tablename (remember to close table1 and table2 first) 5. Check table2's existance using Exists, if it does proceed 6. open and refresh fielddefs and indexdefs for both tables 7. loop round fielddefs in table1 and compare to fielddefs in table2 (as a quick check just compare counts) 8. ditto for index defs 9. if everythings OK shellexecute(app) and close your check app Sorry no example but this shouldn't take more than 30 mins to set up. Roy Lambert |
Thu, Nov 30 2006 9:47 AM | Permanent Link |
"Robert" | "Harry de Boer" <harry@staaf.nl> wrote in message news:5BAC9E77-C6B5-4F27-84E9-3D757AC687F4@news.elevatesoft.com... > LS > > What's the best way to programmaticly test on startup of an app that: 1. Update user xx version every time you upgrade the table structure. 2. Have your program test version before you attempt to open the tables. You can do that without opening the table. I don't think there is a need to set the user version on all tables, just one of the tables is OK. As to determining that all tables are there, I guess one could loop thru all the tables and do an if exists. Robert |
Thu, Nov 30 2006 10:05 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Harry
Woops I forgot the second idea. Its similar to the first but rather than keep a copy of the tables you embed the information into your code. Better in that the tables can't be corrupted but more difficult to maintain. Roy Lambert |
Thu, Nov 30 2006 10:35 AM | Permanent Link |
"Harry de Boer" | Robert,
Is there a *quick* way to change the table version for every table in the db (in SQL if possible)? Regards, Harry "Robert" <ngsemail2005withoutthis@yahoo.com.ar> schreef in bericht news:16D010D6-931C-486C-8261-CA51092DCF6E@news.elevatesoft.com... > > "Harry de Boer" <harry@staaf.nl> wrote in message > news:5BAC9E77-C6B5-4F27-84E9-3D757AC687F4@news.elevatesoft.com... > > LS > > > > What's the best way to programmaticly test on startup of an app that: > > 1. Update user xx version every time you upgrade the table structure. > 2. Have your program test version before you attempt to open the tables. You > can do that without opening the table. > > I don't think there is a need to set the user version on all tables, just > one of the tables is OK. As to determining that all tables are there, I > guess one could loop thru all the tables and do an if exists. > > Robert > > > |
Thu, Nov 30 2006 11:09 AM | Permanent Link |
"Robert" | "Harry de Boer" <harry@staaf.nl> wrote in message news:D936816C-A96F-4273-97E8-841CDE5C32AA@news.elevatesoft.com... > Robert, > > Is there a *quick* way to change the table version for every table in the > db > (in SQL if possible)? > The command is ALTER TABLE "XXXX.DAT" USER MAJOR VERSION 400 USER MINOR VERSION 1; I don't think there is a way to do it for the whole database, I think you need to fire a command for each table. In V4, you can get all the database tables in a stringlist, and then just loop thru the stringlist. I don't think that is available in V3. If you have tTables defined in a datamodule, you can basically accomplish the same thing by looping thru all the components in the datamodule and testing for class = tDBISAMTable. Otherwise, it is a hardocoded list, I'm afraid maxtables = 3; const tname : array [1..MaxTables] of string = 'table1, table2, table3'; for j := 1 to maxtables do begin query.sql.add( 'alter table ' + tname[j] + etc // you could use this same loop to test for table exists end; query.execSQL; Robert |
Thu, Nov 30 2006 11:10 AM | Permanent Link |
"Iztok Lajovic" | Harry,
we do it programatically before opening database(s). We have a separate module in which we have results of Tim's reverse engineering code for each table. After that for each table the version is tested and if the anderlaying table has different verion number all necesssary table restructure actions take place. Here is code snippet if you need it: // Tim's Reverse Engineering Code, which we replace each time when we restructure a table: with TableToCreate do begin DatabaseName := DB.databaseName; // DB is a TDBISAMDatabase component sessionName := DB.sessionName; TableName := 'kart'; Exclusive := True; if (not Exists) then begin with FieldDefs do begin Clear; Add('Ident', ftInteger, 0, False, '', '', '', '', fcNoChange, 0); Add('Class', ftInteger, 0, False, '', '', '', '', fcNoChange, 0); Add('DatDoc', ftDate, 0, False, '', '', '', '', fcNoChange, 0); Add('IdProm', ftInteger, 0, False, '', '', '', '', fcNoChange, 0); Add('Stran', ftSmallint, 0, False, '', '', '', '', fcNoChange, 0); Add('DatPre', ftDate, 0, False, '', '', '', '', fcNoChange, 0); Add('DatZap', ftDate, 0, False, '', '', '', '', fcNoChange, 0); end; with IndexDefs do begin Clear; Add('', 'Ident;Class;DatDoc;IdProm;Stran', [ixPrimary, ixUnique], '', icNone); Add('promet', 'IdProm', [], '', icNone); end; CreateTable(1060, 1, 6, False, '', // if there is no such table we add one with highest version number 'sample Table', 4096, 512, 0, '', StopWords,spaceChars,includeChars); // we have Stopwords and spaceChars and includeChars in separate module end; // end of Tim's Reverse Engineering Code // here we add our code to restructure each table structure version: active := false; if (userMajorVersion = 1) and (userMinorVersion = 1) then begin FieldDefs.Update; IndexDefs.update; // adding a new field FieldDefs.Insert(0,6,'Class',ftInteger,0,false,'','','','',fcNoChange); AlterTable(1060, 1, 2, False, '', // upgrade to version 1.2 'sample Table', 4096, 512, -1, '', StopWords,spaceChars,includeChars); end; if (userMajorVersion = 1) and (userMinorVersion = 2) then begin FieldDefs.Update; IndexDefs.update; if FieldDefs.IndexOf('archive') <> -1 then // this field is no longer used FieldDefs.Delete(FieldDefs.IndexOf('archive')); AlterTable(1060, 1, 3, False, '', // upgrade to version 1.3 'sample Table', 4096, 512, -1, '', StopWords,spaceChars,includeChars); end; ::: ::: // and so on for every table structure version We found this method very handy because there is not much to program each time after table restructuring. And the important think to consider is also that user can restore data (for whatever reason) from an old archive and with this method he/she has always up to date table structures. There is no need to have empty reference tables stored in a separate directory - if you have, than there is a need to update them too. Regards Iztok Lajovic |
Thu, Nov 30 2006 12:03 PM | Permanent Link |
Michael Baytalsky | Hi Harry,
I recommend you to take a look at our Database Extensions Suite. http://www.contextsoft.com/products/ctxdbext/ download trial: http://www.contextsoft.com/products/ctxdbext/downloads.jsp What you can do with it is: 1. Have database schema stored in your DataModule (dfm) in TDatabaseSchema component 2. Design it in a very powerful designer, with schema version control (which allows you to compare or rollback to prior versions) 3. Test if the database is correct version on startup and update it automatically by executing pre-generated SQL scripts (scripts are generated by Designer, but you can also generate on run-time) or 4. Reverse engineer database and compare with your stored schema and automatically generate SQL ALTER to update it (this approach is not recommended - you should never execute any SQL without prior examining it manually). The suite relies on storing database version number in a single row "system" table. The scripts to upgrade from any version to any version is stored in schema in your dfm. Alternatively could be stored in files (dsd or xml). The startup code, that I use in this case is: if not Database.IsVersionCurrent(True {or newer}) then Database.UpdateDatabase; The example can be found in Demos\EZBooks under DBISAM3 or 4. Lots of databases supported, including DBISAM 3 & 4. Regards, Michael Harry de Boer wrote: > LS > > What's the best way to programmaticly test on startup of an app that: > -all tables are there > -all fields are there > -all fields have the correct length, type and constraints > -all indexes are there > -all blobfiles are there > > is there an example somewhere (we dont'own the source) > -dbIsam 3.3 D6 > > Regards, Harry > > |
Thu, Nov 30 2006 11:38 PM | Permanent Link |
"Huhtaman" | Harry:
Keeping a version number on all of the tables is time consuming. What we did is similar to what Michael Did. We put the versioning into the exe file. Every time we load a new exe file into the client site, we run our restructure utility. The restructure utility takes a "snapshot" of the database that matches the exe - we store the "snapshot into 2 DBISAM files. When a new update is run, we download the exe, the 2 "snapshot" files, then we run our restructure utility. This has worked great for maintaining a customer base of 150+ customers. Our next version will be a windows update like version. It will attempt to download and install new versions at night. Neil Huhta "Harry de Boer" <harry@staaf.nl> wrote in message news:5BAC9E77-C6B5-4F27-84E9-3D757AC687F4@news.elevatesoft.com... > LS > > What's the best way to programmaticly test on startup of an app that: > -all tables are there > -all fields are there > -all fields have the correct length, type and constraints > -all indexes are there > -all blobfiles are there > > is there an example somewhere (we dont'own the source) > -dbIsam 3.3 D6 > > Regards, Harry > > |
Fri, Dec 1 2006 5:30 AM | Permanent Link |
"Malcolm" | I vote we build up the pressure until Tim adds the
structures and methods into ElevateDB to allow it to examine the physical structure, compare it to the current application version's catalog, generate the restructuring SQL and execute it. Just think - on connecting to the darabase.. with each table do begin if not Exists then CreateTable else if not CompareTable then UpgradeTable; end; That might just tempt me to upgrade. |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |