![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM Third Party Announcements » View Thread |
Messages 1 to 10 of 11 total |
![]() |
Fri, Mar 17 2006 10:07 AM | Permanent Link |
Chris Erdal | Hi,
I've just discovered Context Database Extensions after beginning to add some onDelete database events to my emerging first-ever application using DBISAM. It would seem to be the bee's knees as far as painlessly managing: - database structure evolution over time, particularly from version x1.y1 to version x2.y2 where all 4 variables can take any intermediate values, depending on frequency of upgrading for the various users. - Relational integrity I need some help, though, in getting my neurones around how to implement it, if anyone has the time. I have a beta version (v1.3) out with a client (my daughter, as it happens, so she won't be my first PAYING client ![]() I have been developing since installing it in her workshop, so the database has already evolved a bit. (v1.5) I want to upgrade her database to the new version, so I used ContExt to reverse_engineer v1.3. I added the existing relationships, up to now handled by my database events, and removed my handlers. I saved the version in ContExt. Now I want to tell ContExt to pick up v1.5 (added tables, changed fields and indexes) by reverse-engineering it. 1/ How do I do that without obliterating v1.3? Or do I have to "catch up" manually? 2/ How do I then set up things so that when opening v1.3 ContExt automatically upgrades the database to v1.5? Thanks for any pointers, and in particular, is there a straightforward tutorial around - the help file is pretty good as far as it goes, but the juicy bits are still "under construction", unfortunately. -- Chris |
Fri, Mar 17 2006 11:48 AM | Permanent Link |
Michael Baytalsky | Hi Chris,
I assume you're using 2.x version of extensions & designer. > Now I want to tell ContExt to pick up v1.5 (added tables, changed fields > and indexes) by reverse-engineering it. > > 1/ How do I do that without obliterating v1.3? Or do I have to "catch > up" manually? If you do reverse engineering you will not overwrite 1.3. Did you create a version checkpoint for 1.3? I presume you did. Each version checkpoint stores difference between new and prev. version. So your sequence of actions normally should look like this: 1. Reverse engineer older database 2. Create version checkpoint 1.3 (this will produce create database script for all table, since this is the first version. You can manually clean up that script if you want to or leave it as is). 3. Reverse engineer newer database 4. Create version checpoint - it will be 1.4. It will again produce update script, only this time you will need it to evolve schema. Both versions (their difference to be precise) will be stored in schema history. You will be able to compare them or revert to the older one, etc. (see right mouse menu in Checkpoints window). If your database already has version 1.5 structure and you don't want to apply any updates, you will have to "catch up" manually, by forcing its version to 1.5. You can do that in SQL console by using setversion command. setversion 1.5 This will set the version of database to 1.5, so the new update will no be applied to it, until you have 1.6 and up. You can do this also at run-time by using SetVersion method of TDBISAMDatabaseExt. The version label is stored in system.dat table - this is how the system knows which version your database is and which updates should be applied. > 2/ How do I then set up things so that when opening v1.3 ContExt > automatically upgrades the database to v1.5? I assume you need to do that at runtime. In the designer you can do so manually by using Change\Update Database structure from Tools menu. At run-time, you should use the following code when opening the database for the first time: Database.Open; // If version is not current (or newer), run update scripts stored in schema if not Database.IsVersionCurrent(True) then Database.UpdateDatabase; Your database component (TDBISAMDatabaseExt) should be connected to TDatabaseSchema, which should contain the latest schema. The command above will execute all database scripts from current database version to the latest version in schema. > Thanks for any pointers, and in particular, is there a straightforward > tutorial around - the help file is pretty good as far as it goes, but the > juicy bits are still "under construction", unfortunately. Unfortunately, yes, we're a bit behind on documentation and tutorials at this point ;^). But it's getting there. I'm working on finishing help for the designer and as soon as it's done will work on updating help for components. Regards, Michael |
Fri, Mar 17 2006 1:15 PM | Permanent Link |
Chris Erdal | Hi Michael,
Thanks for your full and prompt reply. I'll follow up in-line: Michael Baytalsky <mike@contextsoft.com> wrote in news:4958B5B3-0AED-4A4C-9F86-6D6E812E24DE@news.elevatesoft.com: > Hi Chris, > > I assume you're using 2.x version of extensions & designer. Yes. > 1. Reverse engineer older database > 2. Create version checkpoint 1.3 (this will produce create database > script for all table, since this is the first version. You can > manually clean up that script if you want to or leave it as is). I had to change NUMERIC(0) to NUMERIC(0,4) for it to create a new database from scratch. Is this where I should use a TDBEngineProfile component? At this point I added the relationships. Can I copy-and-paste them to the next reverse-engineered version, or do I need to start again? > 3. Reverse engineer newer database I tried this from the D7 IDE and got an exception after agreeing to overwrite my current schema definitions, at 400340F47 in module rtl70.bpl. Read of address 00000008 followed by a blinking catastrophic error box saying: Access violation at 0043F512 in module CTXDBD~1.EXE. Read of address 00000058 at which point I had to kill CTXDBD~1.EXE so I tried again using the Database Designer directly, with the same result. > 4. Create version checpoint - it will be 1.4. It will again produce > update script, only this time you will need it to evolve schema. This is where I was wondering about how to recover the relationships from v1.3. But first I've got to get past the access violation. From here onwards, I suppose one should never touch the database structure outside your Database Designer, otherwise manual intervention is required to catch up? > Both versions (their difference to be precise) will be stored > in schema history. You will be able to compare them or revert to > the older one, etc. (see right mouse menu in Checkpoints window). > ... > ... > The version label is stored in system.dat table - this is how > the system knows which version your database is and which updates > should be applied. > ... > ... At run-time, you should > use the following code when opening the database for the > first time: > Database.Open; > // If version is not current (or newer), run update scripts stored > in schema > > if not Database.IsVersionCurrent(True) then > Database.UpdateDatabase; > > Your database component (TDBISAMDatabaseExt) should be connected > to TDatabaseSchema, which should contain the latest schema. The > command above will execute all database scripts from current > database version to the latest version in schema. > That's the magic bit - it just upgrades whatever is required? And I don't have to spend hours writing, checking, testing and rewriting all the different variations from v X1.y1 to v X2.y2 ? > Unfortunately, yes, we're a bit behind on documentation and tutorials > at this point ;^). But it's getting there. I'm working on finishing > help for the designer and as soon as it's done will work on updating > help for components. don't forget to announce it here when it's ready. And what do you have planned for upgrading to ElevateDB? Will it be straightforward? > Regards, > Michael Thanks. Have a good weekend. -- Chris |
Sat, Mar 18 2006 8:32 AM | Permanent Link |
Michael Baytalsky | Hi Chris,
From your replies, I see that I'm not sure that I understood precisely what you're doing. How about e-mailing me your schema so I can see better what you're trying to accomplish. I will try answering inline, but some of the things I don't seem to understand. >> 1. Reverse engineer older database >> 2. Create version checkpoint 1.3 (this will produce create database >> script for all table, since this is the first version. You can >> manually clean up that script if you want to or leave it as is). > > I had to change NUMERIC(0) to NUMERIC(0,4) for it to create a new > database from scratch. I don't understand this bit, unfortunately. > Is this where I should use a TDBEngineProfile component? Generally, you never need to use that component, unless you're planning to generate SQL - which is the purpose of that component - on run-time. It is highly advisable to generate all SQL at design-time, unless you're trying to write some sort of database management application. > At this point I added the relationships. There's no need to restructure database (with DBISAM at least) after adding RI, because DBISAM 3-4 doesn't support RI, so it works from schema on client side and no updates to the database structure is necessary. > Can I copy-and-paste them to the next reverse-engineered version, or do I > need to start again? No, you cannot copy-paste schema objects, only parts of diagram. Are you saying, that reverse engineering destroyed (override) your RI? I'll need to test that and let you know. >> 3. Reverse engineer newer database > > I tried this from the D7 IDE and got an exception after agreeing to > overwrite my current schema definitions, at 400340F47 in module > rtl70.bpl. Read of address 00000008 I have strong suspicion, that you're using different version of DBExt in IDE, then the designer. This problem should not happen even if you change things in IDE, although it's not advisable and I might prohibit this in future releases. >> 4. Create version checpoint - it will be 1.4. It will again produce >> update script, only this time you will need it to evolve schema. > > This is where I was wondering about how to recover the relationships from > v1.3. But first I've got to get past the access violation. Please, send me your module (pas + dfm) with the schema component and the dsd file (diagram + schema) located in the same folder. I shell test it and see why you're getting the AV. > From here onwards, I suppose one should never touch the database > structure outside your Database Designer, otherwise manual intervention > is required to catch up? No, it really doesn't matter how you change schema, cause you can only change its current state and not the version history (later is not accessible from IDE anyway), so the designer will pickup whatever you changed and compare to the latest checkpoint to produce update script, i.e. catch up with database. > That's the magic bit - it just upgrades whatever is required? And I don't > have to spend hours writing, checking, testing and rewriting all the > different variations from v X1.y1 to v X2.y2 ? There's no magic there ![]() When you check the database version against the version of schema it goes through all Updates and executes the ones with Update.Version > Database.Version. After each execute it sets Database.Version := Update.Version. There's no mystery there, except for generating those scripts, which is done in designer when you create next checkpoint and you can adjust them manually if you like. > don't forget to announce it here when it's ready. I sure won't forget that ![]() > And what do you have planned for upgrading to ElevateDB? Will it be > straightforward? It will be totally straightforward. All you need is to change target database to ElevateDB (once it's out) and it will generate SQL for all foreign keys, etc. Regards, Michael |
Mon, Mar 20 2006 9:59 AM | Permanent Link |
Chris Erdal | Michael,
Thanks for trying to drag me into the starting-blocks! I'll try again, step by step. (Ignoring relationships for the time being) I have a version of the database in "production" with a beta version of my program, which manages the rental of violins to families with musical kids. That version is unknown to Context Database Extensions. I have set up a test area on my PC. Using the Reverse Engineer function of Elevate Software's DBSys I generate and save a SQL script, from that earlier version of the database, called newData1.sql. With that script I create an empty copy of the database in the test directory Data1. I repeat the process for the current development version of the database saving another script called newData2.sql, and an empty copy of the new version of that database in the test directory Data2. What I'm trying to obtain is a Schema containing the initial version and the ability to upgrade it to the current version when I next deploy it to my beta-tester's site. I open your Database Designer and Reverse Engineer Data1, create a Version Checkpoint, and save the result to "Reverse engineer Data1.dsd". I close Database Designer (since I can't see a "clear everything" choice in the File menu) and repeat the operation with Data2, saving it to "Reverse engineer Data2.dsd". I close Database Designer again and reopen it. I attempt to import the SQL script newData1.sql, but I get an error: Syntax error near "AUTOINC" at line 5 in pos 21. the first part of the script I was trying to import is: -------------------------------------------------------- /* SQL-92 Table Creation Script with DBISAM Extensions */ CREATE TABLE IF NOT EXISTS "Achats" ( "achNum" AUTOINC, "achFournisseur" INTEGER NOT NULL, "achFacture" VARCHAR(20), "achDateFacture" DATE NOT NULL, "achmontant HT" DECIMAL(0,4) NOT NULL, "achmontantTVA" DECIMAL(0,4), "achDateCompta" DATE, PRIMARY KEY ("achNum") COMPRESS NONE DESCRIPTION 'Factures d''Achat' LOCALE CODE 1036 USER MAJOR VERSION 1 USER MINOR VERSION 2 ); -------------------------------------------------------- So I forget about importing DBSys-generated SQL, and reopen the dsd file "Reverse engineer Data1.dsd". I double-click on Versions / 1.1, and the first part of the SQL script is -------------------------------------------------------- -- ## TargetDB: DBISAM4; CREATE TABLE IF NOT EXISTS "Achats" ( "achNum" AUTOINC, "achFournisseur" INT NOT NULL, "achFacture" VARCHAR(20), "achDateFacture" DATE NOT NULL, "achmontant HT" NUMERIC(0,4) NOT NULL, "achmontantTVA" NUMERIC(0,4), "achDateCompta" DATE, PRIMARY KEY ("achNum") ); -------------------------------------------------------- You will see I have removed the [..] around "IF NOT EXISTS" etc in your dbisam4.dpb file, and it was here that I replaced something (but I'm not exactly sure what, as I didn't save the default version) so that NUMERIC fields are always forced to NUMERIC(0,4) since by default I was being given NUMERIC(0) which was rejected on execution. I now want to add the move up to the new version, so I use the function "Import Database Schema" and open "Reverse engineer Data1.dsd". I select all objects, and get a warning that "Achats" already exists. I click "Yes to all", and am returned to the main window after what seems to be a successful import. I right-click on Versions to choose "Create Version Checkpoint...", but the SQL is not what I expected: -------------------------------------------------------- -- ## TargetDB: DBISAM4; ALTER TABLE "Achats" DROP COLUMN IF EXISTS "achNum", DROP COLUMN IF EXISTS "achFournisseur", DROP COLUMN IF EXISTS "achFacture", DROP COLUMN IF EXISTS "achDateFacture", DROP COLUMN IF EXISTS "achmontant HT", DROP COLUMN IF EXISTS "achmontantTVA", DROP COLUMN IF EXISTS "achDateCompta", ADD COLUMN IF NOT EXISTS "achNum" AUTOINC AT 1, ADD COLUMN IF NOT EXISTS "achFournisseur" INT AT 2 NOT NULL, ADD COLUMN IF NOT EXISTS "achFacture" VARCHAR(20) AT 3, ADD COLUMN IF NOT EXISTS "achDateFacture" DATE AT 4 NOT NULL, ADD COLUMN IF NOT EXISTS "achmontant HT" NUMERIC(0,4) AT 5 NOT NULL, ADD COLUMN IF NOT EXISTS "achmontantTVA" NUMERIC(0,4) AT 6, ADD COLUMN IF NOT EXISTS "achDateCompta" DATE AT 7, ADD PRIMARY KEY ("achNum"); ...etc. -------------------------------------------------------- I close the Database Designer, reopen it, and load "Reverse engineer Data1.dsd". I choose "Tools / Reverse Engineer Database..." and open Data2. I agree to overwrite everything, and am returned to the main window, but my Version 1.1 has disappeared. However it is still visible in the Chekpoints window, bottom left. So I right-click on Versions and choose "Create Version Checkpoint...", but the script generated starts by DROP every table, then recreates them, except the Achats table for wich it generates the following rather surprising code: ------------------------------------------------------------ RENAME TABLE IF EXISTS "TVATaux" TO "Achats"; ALTER TABLE "Achats" DROP COLUMN IF EXISTS "tvtNum", DROP COLUMN IF EXISTS "tvtTVA", DROP COLUMN IF EXISTS "tvtDateEffet", DROP COLUMN IF EXISTS "tvtTaux", ADD COLUMN IF NOT EXISTS "achNum" AUTOINC AT 1, ADD COLUMN IF NOT EXISTS "achFournisseur" INT AT 2 NOT NULL, ADD COLUMN IF NOT EXISTS "achFacture" VARCHAR(20) AT 3, ADD COLUMN IF NOT EXISTS "achDateFacture" DATE AT 4 NOT NULL, ADD COLUMN IF NOT EXISTS "achmontant HT" NUMERIC(0,4) AT 5 NOT NULL, ADD COLUMN IF NOT EXISTS "achmontantTVA" NUMERIC(0,4) AT 6, ADD COLUMN IF NOT EXISTS "achDateCompta" DATE AT 7, ADD PRIMARY KEY ("achNum"); ------------------------------------------------------------ so I cancel, and choose File / Revert to saved. I now choose Tools / Compare Schemas, and select Current Schema / Version 1.1 on the left, and Browse / "Reverse engineer Data2.dsd" / Version 1.1 on the right. This gives a script which seems fine: -------------------------------------------------------- -- ## TargetDB: DBISAM4; ALTER TABLE "Clients" REDEFINE PRIMARY KEY ("cliNum"); DROP INDEX IF EXISTS "Clients"."ixClients"; ALTER TABLE "Fabrication" REDEFINE PRIMARY KEY ("fabNum"); ALTER TABLE "Instruments" REDEFINE PRIMARY KEY ("insNum"); DROP INDEX IF EXISTS "Instruments"."ixInstruments"; DROP INDEX IF EXISTS "Locations"."ixLocDateDebut"; CREATE INDEX IF NOT EXISTS "ixLocDateDebut" ON "Locations"("locDateDebut"); ALTER TABLE "Reglements" REDEFINE PRIMARY KEY ("rglNum"); ALTER TABLE "ReleveBancaire" REDEFINE COLUMN IF EXISTS "TRNAMT" NUMERIC(0,4); ALTER TABLE "TarifsLocation" REDEFINE PRIMARY KEY ("tarLocation","tarDateDebut"); -------------------------------------------------------- , but when I click on Import I get the message: Syntax error near "4" at line 26 in pos 50. So perhaps my modifications to the dbp file to force NUMERIC(0,4) have upset the script generation process at this point? I hope this is clear to you, and look forward to your comments, if you have time. -- Chris |
Mon, Mar 20 2006 10:10 AM | Permanent Link |
Chris Erdal | Michael,
Thanks for trying to drag me into the starting-blocks! I'll try again, step by step. (Ignoring relationships for the time being) I have a version of the database in "production" with a beta version of my program, which manages the rental of violins to families with musical kids. That version is unknown to Context Database Extensions. I have set up a test area on my PC. Using the Reverse Engineer function of Elevate Software's DBSys I generate and save a SQL script, from that earlier version of the database, called newData1.sql. With that script I create an empty copy of the database in the test directory Data1. I repeat the process for the current development version of the database saving another script called newData2.sql, and an empty copy of the new version of that database in the test directory Data2. What I'm trying to obtain is a Schema containing the initial version and the ability to upgrade it to the current version when I next deploy it to my beta-tester's site. I open your Database Designer and Reverse Engineer Data1, create a Version Checkpoint, and save the result to "Reverse engineer Data1.dsd". I close Database Designer (since I can't see a "clear everything" choice in the File menu) and repeat the operation with Data2, saving it to "Reverse engineer Data2.dsd". I close Database Designer again and reopen it. I attempt to import the SQL script newData1.sql, but I get an error: Syntax error near "AUTOINC" at line 5 in pos 21. the first part of the script I was trying to import is: -------------------------------------------------------- /* SQL-92 Table Creation Script with DBISAM Extensions */ CREATE TABLE IF NOT EXISTS "Achats" ( "achNum" AUTOINC, "achFournisseur" INTEGER NOT NULL, "achFacture" VARCHAR(20), "achDateFacture" DATE NOT NULL, "achmontant HT" DECIMAL(0,4) NOT NULL, "achmontantTVA" DECIMAL(0,4), "achDateCompta" DATE, PRIMARY KEY ("achNum") COMPRESS NONE DESCRIPTION 'Factures d''Achat' LOCALE CODE 1036 USER MAJOR VERSION 1 USER MINOR VERSION 2 ); -------------------------------------------------------- So I forget about importing DBSys-generated SQL, and reopen the dsd file "Reverse engineer Data1.dsd". I double-click on Versions / 1.1, and the first part of the SQL script is -------------------------------------------------------- -- ## TargetDB: DBISAM4; CREATE TABLE IF NOT EXISTS "Achats" ( "achNum" AUTOINC, "achFournisseur" INT NOT NULL, "achFacture" VARCHAR(20), "achDateFacture" DATE NOT NULL, "achmontant HT" NUMERIC(0,4) NOT NULL, "achmontantTVA" NUMERIC(0,4), "achDateCompta" DATE, PRIMARY KEY ("achNum") ); -------------------------------------------------------- You will see I have removed the [..] around "IF NOT EXISTS" etc in your dbisam4.dpb file, and it was here that I replaced something (but I'm not exactly sure what, as I didn't save the default version) so that NUMERIC fields are always forced to NUMERIC(0,4) since by default I was being given NUMERIC(0) which was rejected on execution. I now want to add the move up to the new version, so I use the function "Import Database Schema" and open "Reverse engineer Data2.dsd". I select all objects, and get a warning that "Achats" already exists. I click "Yes to all", and am returned to the main window after what seems to be a successful import. I right-click on Versions to choose "Create Version Checkpoint...", but the SQL is not what I expected: -------------------------------------------------------- -- ## TargetDB: DBISAM4; ALTER TABLE "Achats" DROP COLUMN IF EXISTS "achNum", DROP COLUMN IF EXISTS "achFournisseur", DROP COLUMN IF EXISTS "achFacture", DROP COLUMN IF EXISTS "achDateFacture", DROP COLUMN IF EXISTS "achmontant HT", DROP COLUMN IF EXISTS "achmontantTVA", DROP COLUMN IF EXISTS "achDateCompta", ADD COLUMN IF NOT EXISTS "achNum" AUTOINC AT 1, ADD COLUMN IF NOT EXISTS "achFournisseur" INT AT 2 NOT NULL, ADD COLUMN IF NOT EXISTS "achFacture" VARCHAR(20) AT 3, ADD COLUMN IF NOT EXISTS "achDateFacture" DATE AT 4 NOT NULL, ADD COLUMN IF NOT EXISTS "achmontant HT" NUMERIC(0,4) AT 5 NOT NULL, ADD COLUMN IF NOT EXISTS "achmontantTVA" NUMERIC(0,4) AT 6, ADD COLUMN IF NOT EXISTS "achDateCompta" DATE AT 7, ADD PRIMARY KEY ("achNum"); ...etc. -------------------------------------------------------- I close the Database Designer, reopen it, and load "Reverse engineer Data1.dsd". I choose "Tools / Reverse Engineer Database..." and open Data2. I agree to overwrite everything, and am returned to the main window, but my Version 1.1 has disappeared. However it is still visible in the Chekpoints window, bottom left. So I right-click on Versions and choose "Create Version Checkpoint...", but the script generated starts by DROP every table, then recreates them, except the Achats table for wich it generates the following rather surprising code: ------------------------------------------------------------ RENAME TABLE IF EXISTS "TVATaux" TO "Achats"; ALTER TABLE "Achats" DROP COLUMN IF EXISTS "tvtNum", DROP COLUMN IF EXISTS "tvtTVA", DROP COLUMN IF EXISTS "tvtDateEffet", DROP COLUMN IF EXISTS "tvtTaux", ADD COLUMN IF NOT EXISTS "achNum" AUTOINC AT 1, ADD COLUMN IF NOT EXISTS "achFournisseur" INT AT 2 NOT NULL, ADD COLUMN IF NOT EXISTS "achFacture" VARCHAR(20) AT 3, ADD COLUMN IF NOT EXISTS "achDateFacture" DATE AT 4 NOT NULL, ADD COLUMN IF NOT EXISTS "achmontant HT" NUMERIC(0,4) AT 5 NOT NULL, ADD COLUMN IF NOT EXISTS "achmontantTVA" NUMERIC(0,4) AT 6, ADD COLUMN IF NOT EXISTS "achDateCompta" DATE AT 7, ADD PRIMARY KEY ("achNum"); ------------------------------------------------------------ so I cancel, and choose File / Revert to saved. I now choose Tools / Compare Schemas, and select Current Schema / Version 1.1 on the left, and Browse / "Reverse engineer Data2.dsd" / Version 1.1 on the right. This gives a script which seems fine: -------------------------------------------------------- -- ## TargetDB: DBISAM4; ALTER TABLE "Clients" REDEFINE PRIMARY KEY ("cliNum"); DROP INDEX IF EXISTS "Clients"."ixClients"; ALTER TABLE "Fabrication" REDEFINE PRIMARY KEY ("fabNum"); ALTER TABLE "Instruments" REDEFINE PRIMARY KEY ("insNum"); DROP INDEX IF EXISTS "Instruments"."ixInstruments"; DROP INDEX IF EXISTS "Locations"."ixLocDateDebut"; CREATE INDEX IF NOT EXISTS "ixLocDateDebut" ON "Locations"("locDateDebut"); ALTER TABLE "Reglements" REDEFINE PRIMARY KEY ("rglNum"); ALTER TABLE "ReleveBancaire" REDEFINE COLUMN IF EXISTS "TRNAMT" NUMERIC(0,4); ALTER TABLE "TarifsLocation" REDEFINE PRIMARY KEY ("tarLocation","tarDateDebut"); -------------------------------------------------------- , but when I click on Import I get the message: Syntax error near "4" at line 26 in pos 50. So perhaps my modifications to the dbp file to force NUMERIC(0,4) have upset the script generation process at this point? I hope this is clear to you, and look forward to your comments, if you have time. -- Chris |
Mon, Mar 20 2006 7:45 PM | Permanent Link |
Michael Baytalsky | Hi Chris,
Okey, now I understand ![]() of dbisam4.dbp file (for DBISAM 4, please copy it over your version and, please do backup before changing it again ![]() This version fixes the problem with AUTOINC parsing and NUMERIC/DECIMAL output (I actually knew about them, but was under the impression, that I will be releasing the new update soon, so I didn't issue a quick fix ![]() I will not comment on your post, instead I will try to explain how this should be done and why you didn't succeed. You didn't succeed because importing and overwriting a table in 2.02 causes the table to be... well overwritten ![]() generator assumes, that it needs to drop and recreate all fields. This is not what you intended to do and you should never override a table when importing from other schema, because it will naturally result in drop and create (although, this has been changed, so in the next release it will recreate the whole table, rather then just individual fields). This behavior is intended currently. If I decide to change it in future, I will make sure to put it into Release Notes. Also, you will not be currently able to import relations into the schema with DBISAM, because dbisam doesn't have foreign keys defined in SQL and the only way to import things is by using SQL. -- step 1: creating initial schema for version 1.3 0. Run the designer from IDE by double clicking on your schema component. 1. Use the profile attached to import the structure from SQL file produced by dbsys. I recommend always using SQL import, as opposed to reverse engineering of physical databases. If you come across any problems, please e-mail me your sql file - we'll be fighting all issues with sql import until it works 100% of the time ![]() 2. Produce version checkpoint. It will be 1.1 by default, but you can mark it 1.3 if you like to. Save and close the designer. -- step 2: creating separate temporary schema for version 1.5 3. Run the designer from outside of IDE, because you need to operate with another schema (you can also have two instances opened). (The Designer has SDI interface, so naturally, there's no Close or Clear option, but there's File/New command instead). 4. Import second SQL file (ver 1.5) into a separate schema and save it to disk as schema2.dsd. Close the designer. -- step 3: compare two schemas and produce difference 5. Open the designer from IDE (for our main schema) and go to Tools\Compare Schemas. 6. Load previously saved schema2.dsd as the destination schema. Press compare to view differences. 7. Press view SQL, to see the SQL, that alters 1.3 -> 1.5. Press Import button, to import these changes into your current schema. Note, that if you select MS SQL 2000 for target database, it should allow you to import relations if you already have them in schema2.dsd. Otherwise, you should use DBISAM. -- step 4: creating 1.5 checkpoint 8. Close Compare dialog. We'll no longer need schema2.dsd file - it has been used temporarily to see what's changed between versions. 9. Produce new checkpoint 1.5. Save the schema, close the designer. We're done working with the schema. One thing, that you should always do though, is running Update SQL against demo databases to ensure that it actually works. You can do so from the Designer, using Tool/Create Update Database. You can try creating new database or updating existing 1.3 database. In order to mark existing 1.3 database as having 1.3 version, open SQL Console and execute command: setversion 1.3 This is only to test that your 1.5 script works. See below on how to do this at runtime. -- step 5: making your runtime code recognize database The database, distributed to your clients, wasn't marked by any version, so reading its version will return -1.-1. However, it's not empty, so you don't want to execute your first update script, because it contains create table statements and should only be executed to create initial database if necessary. The easiest way to handle this is to open the update item (double click on that item under Versions in schema treeview) and clean up all the SQL. This will work fine, assuming all databases you will ever encounter are either 1.3 or marked, i.e. you never encounter an empty database. If you're planning to use this scripts to create databases from scratch, you should instead mark your existing databases with version 1.3 on startup if it has no version, but is not empty (e.g. contains one of the tables you can recognize). For example: Database.Open; if (Database.Version.Major < 0) and tblMyTable.Exists then Database.Version := SchemaVersion(1, 3); if not Database.IsVersionCurrent(True) then Database.UpdateDatabase; ----------------------- All of the above may seem more complex, than it actually is once you get more familiar with the tool. You will only have to do the importing once, so it shouldn't be a big deal. In the long run it will save you hours and hours not having to think about what has changed between versions 1.5 and 2.34 - you will always be able to see the different in Compare dialog and produce SQL, etc. I think your example shows, that there's certain lack of import feature (both in importing and in compare tool) and I will address it in one of the nearest releases. Regards, Michael Chris Erdal wrote: > Michael, > > Thanks for trying to drag me into the starting-blocks! > > I'll try again, step by step. (Ignoring relationships for the time being) > > I have a version of the database in "production" with a beta version of > my program, which manages the rental of violins to families with musical > kids. > > That version is unknown to Context Database Extensions. > > I have set up a test area on my PC. > > Using the Reverse Engineer function of Elevate Software's DBSys I > generate and save a SQL script, from that earlier version of the > database, called newData1.sql. > > With that script I create an empty copy of the database in the test > directory Data1. > > I repeat the process for the current development version of the database > saving another script called newData2.sql, and an empty copy of the new > version of that database in the test directory Data2. > > What I'm trying to obtain is a Schema containing the initial version and > the ability to upgrade it to the current version when I next deploy it to > my beta-tester's site. > > I open your Database Designer and Reverse Engineer Data1, create a > Version Checkpoint, and save the result to "Reverse engineer Data1.dsd". > > I close Database Designer (since I can't see a "clear everything" choice > in the File menu) and repeat the operation with Data2, saving it to > "Reverse engineer Data2.dsd". > > I close Database Designer again and reopen it. I attempt to import the > SQL script newData1.sql, but I get an error: > > Syntax error near "AUTOINC" at line 5 in pos 21. > > the first part of the script I was trying to import is: > -------------------------------------------------------- > /* SQL-92 Table Creation Script with DBISAM Extensions */ > > CREATE TABLE IF NOT EXISTS "Achats" > ( > "achNum" AUTOINC, > "achFournisseur" INTEGER NOT NULL, > "achFacture" VARCHAR(20), > "achDateFacture" DATE NOT NULL, > "achmontant HT" DECIMAL(0,4) NOT NULL, > "achmontantTVA" DECIMAL(0,4), > "achDateCompta" DATE, > PRIMARY KEY ("achNum") COMPRESS NONE > DESCRIPTION 'Factures d''Achat' > LOCALE CODE 1036 > USER MAJOR VERSION 1 > USER MINOR VERSION 2 > ); > -------------------------------------------------------- > > So I forget about importing DBSys-generated SQL, and reopen the dsd file > > "Reverse engineer Data1.dsd". > > I double-click on Versions / 1.1, and the first part of the SQL script is > > -------------------------------------------------------- > -- ## TargetDB: DBISAM4; > > CREATE TABLE IF NOT EXISTS "Achats" ( > "achNum" AUTOINC, > "achFournisseur" INT NOT NULL, > "achFacture" VARCHAR(20), > "achDateFacture" DATE NOT NULL, > "achmontant HT" NUMERIC(0,4) NOT NULL, > "achmontantTVA" NUMERIC(0,4), > "achDateCompta" DATE, > PRIMARY KEY ("achNum") > ); > -------------------------------------------------------- > > You will see I have removed the [..] around "IF NOT EXISTS" etc in your > dbisam4.dpb file, and it was here that I replaced something (but I'm not > exactly sure what, as I didn't save the default version) so that NUMERIC > fields are always forced to NUMERIC(0,4) since by default I was being > given NUMERIC(0) which was rejected on execution. > > I now want to add the move up to the new version, so I use the function > "Import Database Schema" and open "Reverse engineer Data1.dsd". > > I select all objects, and get a warning that "Achats" already exists. I > click "Yes to all", and am returned to the main window after what seems > to be a successful import. > > I right-click on Versions to choose "Create Version Checkpoint...", but > the SQL is not what I expected: > -------------------------------------------------------- > -- ## TargetDB: DBISAM4; > > ALTER TABLE "Achats" > DROP COLUMN IF EXISTS "achNum", > DROP COLUMN IF EXISTS "achFournisseur", > DROP COLUMN IF EXISTS "achFacture", > DROP COLUMN IF EXISTS "achDateFacture", > DROP COLUMN IF EXISTS "achmontant HT", > DROP COLUMN IF EXISTS "achmontantTVA", > DROP COLUMN IF EXISTS "achDateCompta", > ADD COLUMN IF NOT EXISTS "achNum" AUTOINC AT 1, > ADD COLUMN IF NOT EXISTS "achFournisseur" INT AT 2 NOT NULL, > ADD COLUMN IF NOT EXISTS "achFacture" VARCHAR(20) AT 3, > ADD COLUMN IF NOT EXISTS "achDateFacture" DATE AT 4 NOT NULL, > ADD COLUMN IF NOT EXISTS "achmontant HT" NUMERIC(0,4) AT 5 NOT NULL, > ADD COLUMN IF NOT EXISTS "achmontantTVA" NUMERIC(0,4) AT 6, > ADD COLUMN IF NOT EXISTS "achDateCompta" DATE AT 7, > ADD PRIMARY KEY ("achNum"); > > ...etc. > -------------------------------------------------------- > > I close the Database Designer, reopen it, and load > "Reverse engineer Data1.dsd". > I choose "Tools / Reverse Engineer Database..." and open Data2. > > I agree to overwrite everything, and am returned to the main window, but > my Version 1.1 has disappeared. However it is still visible in the > Chekpoints window, bottom left. > > So I right-click on Versions and choose "Create Version Checkpoint...", > but the script generated starts by DROP every table, then recreates them, > except the Achats table for wich it generates the following rather > surprising code: > > ------------------------------------------------------------ > RENAME TABLE IF EXISTS "TVATaux" TO "Achats"; > ALTER TABLE "Achats" > DROP COLUMN IF EXISTS "tvtNum", > DROP COLUMN IF EXISTS "tvtTVA", > DROP COLUMN IF EXISTS "tvtDateEffet", > DROP COLUMN IF EXISTS "tvtTaux", > ADD COLUMN IF NOT EXISTS "achNum" AUTOINC AT 1, > ADD COLUMN IF NOT EXISTS "achFournisseur" INT AT 2 NOT NULL, > ADD COLUMN IF NOT EXISTS "achFacture" VARCHAR(20) AT 3, > ADD COLUMN IF NOT EXISTS "achDateFacture" DATE AT 4 NOT NULL, > ADD COLUMN IF NOT EXISTS "achmontant HT" NUMERIC(0,4) AT 5 NOT NULL, > ADD COLUMN IF NOT EXISTS "achmontantTVA" NUMERIC(0,4) AT 6, > ADD COLUMN IF NOT EXISTS "achDateCompta" DATE AT 7, > ADD PRIMARY KEY ("achNum"); > ------------------------------------------------------------ > > > so I cancel, and choose File / Revert to saved. > > I now choose Tools / Compare Schemas, and select > > Current Schema / Version 1.1 on the left, and > Browse / "Reverse engineer Data2.dsd" / Version 1.1 on the right. > > This gives a script which seems fine: > > -------------------------------------------------------- > -- ## TargetDB: DBISAM4; > > > ALTER TABLE "Clients" > REDEFINE PRIMARY KEY ("cliNum"); > > DROP INDEX IF EXISTS "Clients"."ixClients"; > > ALTER TABLE "Fabrication" > REDEFINE PRIMARY KEY ("fabNum"); > > ALTER TABLE "Instruments" > REDEFINE PRIMARY KEY ("insNum"); > > DROP INDEX IF EXISTS "Instruments"."ixInstruments"; > > > DROP INDEX IF EXISTS "Locations"."ixLocDateDebut"; > > CREATE INDEX IF NOT EXISTS "ixLocDateDebut" ON > "Locations"("locDateDebut"); > > ALTER TABLE "Reglements" > REDEFINE PRIMARY KEY ("rglNum"); > > ALTER TABLE "ReleveBancaire" > REDEFINE COLUMN IF EXISTS "TRNAMT" NUMERIC(0,4); > > ALTER TABLE "TarifsLocation" > REDEFINE PRIMARY KEY ("tarLocation","tarDateDebut"); > -------------------------------------------------------- > > , but when I click on Import I get the message: > > Syntax error near "4" at line 26 in pos 50. > > So perhaps my modifications to the dbp file to force NUMERIC(0,4) have > upset the script generation process at this point? > > I hope this is clear to you, and look forward to your comments, if you > have time. Attachments: dbisam4.dbp |
Wed, Mar 22 2006 5:33 AM | Permanent Link |
Chris Erdal | Michael Baytalsky <mike@contextsoft.com> wrote in news:5DEC0112-05DC-4ED6-
A61B-EAD4FC13279A@news.elevatesoft.com: > Okey, now I understand ![]() > of dbisam4.dbp file (for DBISAM 4, please copy it over your > version and, please do backup before changing it again ![]() > This version fixes the problem with AUTOINC parsing > and NUMERIC/DECIMAL output (I actually knew about them, but > was under the impression, that I will be releasing the new > update soon, so I didn't issue a quick fix ![]() > Thanks Michael, > I will not comment on your post, instead I will try to explain > I'll do the same. Thanks for the new dbisam4.dbp file. I've backed it up (!) and modified it to create BCD fields for NUMERIC SQL fields, as I need the exact decimals for accounting purposes: ---------------------------------------------------------------------- datatype.NUMERIC=NUMERIC[(<Precision!=0><Precision>[,<Scale!=><Scale>])] <fieldtype=BCD> datatype.DECIMAL=DECIMAL[(<Precision!=0><Precision>[,<Scale!=><Scale>])] <fieldtype=BCD> ---------------------------------------------------------------------- To simplify things, I'm using version 1.1 as the pre-existing version out there, and version 1.2 as my development version. I've done as you said, and now have a schema component with both versions saved in it on my DataModule. I decided to leave out all relationships and recreate them on the schema once the SQL is fixed. I added your code to set the version to 1.1 if not defined: ---------------------------------------------------------------------- CJRentalDB.Open; if (CJRentalDB.Version.Major <= 0) and tblClients.Exists then CJRentalDB.Version := SchemaVersion(1, 1); if not CJRentalDB.IsVersionCurrent(True) then begin if FlagUpdatingDB then exit; FlagUpdatingDB := true; try CJRentalDB.UpdateDatabase; finally FlagUpdatingDB := false; end; end; ---------------------------------------------------------------------- but now I get 2 consecutive errors on 2 consecutive startups. First, I get "The table System already exists", which leaves the database in version 1.1 when I close the application. (Presumably because setting the version number creates the previously inexistant System table which wasn't there in the version 1.1 script). Next (after restarting the application) I get "Table or backup file Xxxxx does not exist" - now this is a table that I have removed in the development version, and DROP TABLE Xxxxx is the first operation in the SQL for going from version 1.1 to 1.2: ---------------------------------------------------------------------- -- ## TargetDB: DBISAM4; DROP TABLE "SophieClients"; CREATE TABLE "System" ( "SchemaName" VARCHAR(127), "ReplicationID" INTEGER, "SnapshotID" INTEGER, "MinorVersion" INTEGER, "MajorVersion" INTEGER, "Schema" BLOB, PRIMARY KEY ("RecordID") COMPRESS NONE LOCALE CODE 1036 USER MAJOR VERSION 1 USER MINOR VERSION 2 ); .... ---------------------------------------------------------------------- As the system is supposed to know exactly what is where at every stage in the evolution of the database, I'm loath to activate "IF NOT EXISTS" etc, so that should something catastrophic occur (e.g. someone removing or altering a table outside my program), the error is not just ignored. So how do I add a failsafe mechanism to the UpdateDatabase method? Should I backup the whole database before launching the UpdateDatabase, and in the event of an exception revert the schema to the current version and restore the backed-up database (and send myself an email with a trace file attached so that I can tell the client what's happened and send him a new version of the program)? Or should this be automatically integrated into the UpdateDatabase method? ![]() One last thing - I had added descriptions to the tables such as "Familles d'articles pour comptabilité" and this gave an error because the apostrophe broke the text value. You'll need to make this failsafe. -- Chris |
Wed, Mar 22 2006 8:10 AM | Permanent Link |
Michael Baytalsky | Hi Chris,
> I've done as you said, and now have a schema component with both versions > saved in it on my DataModule. I decided to leave out all relationships and > recreate them on the schema once the SQL is fixed. Just to clarify: only the current schema (1.2) is saved in your data module. 1.1 checkpoint is saved in dsd file together with the diagram, cause you don't need it at run-time, so no reason to store it in dfm. The updates collection contains only scripts and not the checkpoint itself. > I added your code to set the version to 1.1 if not defined: > > ---------------------------------------------------------------------- > CJRentalDB.Open; > if (CJRentalDB.Version.Major <= 0) and tblClients.Exists then > CJRentalDB.Version := SchemaVersion(1, 1); > if not CJRentalDB.IsVersionCurrent(True) then > begin > if FlagUpdatingDB then > exit; > FlagUpdatingDB := true; > try > CJRentalDB.UpdateDatabase; > finally > FlagUpdatingDB := false; > end; > end; > ---------------------------------------------------------------------- > > but now I get 2 consecutive errors on 2 consecutive startups. > > First, I get "The table System already exists", which leaves the database > in version 1.1 when I close the application. I'm not sure I understand this problem. At which point this error happens? Your database update script is not supposed to create, drop or alter system table in any way. You should delete System table from schema and make sure it never appears there. System table should be invisible to schema. > (Presumably because setting the version number creates the previously > inexistant System table which wasn't there in the version 1.1 script). If the table was inexistent, then it should be created successfully and not raise the above error. > Next (after restarting the application) I get "Table or backup file Xxxxx > does not exist" - now this is a table that I have removed in the > development version, and DROP TABLE Xxxxx is the first operation in the SQL > for going from version 1.1 to 1.2: This is a normal conflict where your database does not have expected structure. If you do the first step correctly then upgrading should go without errors. > CREATE TABLE "System" ( This code should never be there in the > PRIMARY KEY ("RecordID") COMPRESS NONE If you're working with DBISAM v.4 make sure you don't have Primary key defined for RecordID field - this is not supported. Just delete that index in your schema, it should not be there. This is not related to System table, because that table should never be created or altered in script. The reason you have it is because you have probably imported it using SQL or something. > As the system is supposed to know exactly what is where at every stage in > the evolution of the database, I'm loath to activate "IF NOT EXISTS" etc, > so that should something catastrophic occur (e.g. someone removing or > altering a table outside my program), the error is not just ignored. I agree with you on this one. It is possible to activate IF NOT EXISTS (but don't remove square brackets [], use [<!>IF NOT EXISTS] instead -<!> sign means, that this "optional" statement will always be produced). Removing square brackets will break importing function. > So how do I add a failsafe mechanism to the UpdateDatabase method? There's not way. The only way to do so is backup your database before applying update. Generally, DBISAM produces ?BK files for all updated tables, but I'd still rather backup the database. This is not physically possible, cause transactional DDL is not supported (not only by dbisam, but by most engines). > Should I backup the whole database before launching the UpdateDatabase, and > in the event of an exception revert the schema to the current version and > restore the backed-up database (and send myself an email with a trace file > attached so that I can tell the client what's happened and send him a new > version of the program)? That would be a correct approach. > Or should this be automatically integrated into the UpdateDatabase method? > ![]() This is not very easy thing to do, alas. Maybe we'll add this in future. > One last thing - I had added descriptions to the tables such as "Familles > d'articles pour comptabilité" and this gave an error because the apostrophe > broke the text value. You'll need to make this failsafe. This is a bug, I'll see how this can be corrected. If you still have problems, please, contact me by e-mail and attach your dsd file. Regards, Michael |
Wed, Mar 22 2006 10:02 AM | Permanent Link |
Chris Erdal | Michael Baytalsky <mike@contextsoft.com> wrote in
news:3BDDB5D6-060D-4444-8A6B-D01816932A15@news.elevatesoft.com: > I'm not sure I understand this problem. At which point this error > happens? Your database update script is not supposed to create, drop > or alter system table in any way. You should delete System table from > schema and make sure it never appears there. System table should be > invisible to schema. > ... >> CREATE TABLE "System" ( > This code should never be there > ... > . This is not related > to System table, because that table should never be created or altered > in script. The reason you have it is because you have probably > imported it using SQL or something. I understand now. I created my first two scripts with DBSys from the databases I had in the two places - my archive copy of the working version "out there" (v1.1) and my current development version (v1.2) after attempting to add Database Extensions. So of course DBSys saw the System table in the development version as it had already been created by ContExt, but if I had never attempted to use Database Extensions it wouldn't have been. > (but don't remove square brackets [], use [<!>IF NOT EXISTS] instead > -<!> sign means, that this "optional" statement will always be > produced). Removing square brackets will break importing function. > I hadn't found any in-depth explanation of how to modify the dbp file - thanks for that info. >> Or should this be automatically integrated into the UpdateDatabase >> method? ![]() > This is not very easy thing to do, alas. Maybe we'll add this in > future. I don't expect it'll be possible - you should simply suggest a BEST WAY of doing it in your help file, I think. > If you still have problems, please, contact me by e-mail and attach > your dsd file. Thanks very much for your detailed and very prompt explanations. I feel much happier about ContExt now, especially as I also see from yesterday's thread about "DisplayName for SQL columns" in the dbisam newsgroup that another problem I was just starting to address is automatically handled by this great addon! -- Chris |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, August 14, 2024 at 03:12 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |