Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 11 to 20 of 20 total |
RecordID as TField ? |
Wed, Apr 9 2008 8:46 AM | Permanent Link |
Rolf Frei eicom GmbH | NO!!! Never! This will break all my code and as such this is a no go. If at
all it should be the last field in the fields, as this will not break exsiting code. Also for a major release this is a show stopper change. Regards Rolf "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schrieb im Newsbeitrag news:AE30EA88-D81C-498D-A493-E72AC76D8F86@news.elevatesoft.com... > Bruno Krayenbuhl was kind enough to forward some potential code changes to > DBISAM in regard to surfacing the RecordID internal field as a TField > component, thus permitting its usage in FindKey, Locate, SetRange, etc. > However, this involves surfacing the TField somewhere in the Fields > property for the TDBISAMTable and TDBISAMQuery components, preferrably in > the first (0) position. The possible issue with this is that it will mess > with the FieldCount property (increasing it by 1) and the ordering of > existing TFields will be bumped down by 1 position. > > Does this sound like something that you would be willing to tolerate in > terms of a change in an upcoming minor release, or not ? > > Thanks for your input, > > -- > Tim Young > Elevate Software > www.elevatesoft.com > |
Wed, Apr 9 2008 9:05 AM | Permanent Link |
As it happens, my new version of an app uses a heck of a lot more SQL, but
I had (and may still do) some code that does a lot of fiddling with the field lists. If DBISAM was to do something like this, it would probably break it big-time. IIRC I allowed the user to sort the fields, so you'd have to be able to cope with having your [0] index moved elsewhere too. Making it an option would be fine, so long as it defaulted to current behaviour. /Matthew Jones/ | |
Wed, Apr 9 2008 11:00 AM | Permanent Link |
Bruno Krayenbuhl | Hello,
I thank Tim for taking your opinions and I suppose that means that, at first glance, what I suggested will not break the internals of DBISAM itself. Windows XP pro, BDS 2006, DBISAM426b2 ****** MAKE BACKUP's AND DONT TRY IT IF YOU ARE NOT USED TO RECREATING .BPL's FROM SCRATCH ********* WARNING Code still buggy in design mode. I have already dropped the request to insert this "feature" in TDBISAMTable class. Instead I have created a subclass TDBISAMTrix=class(TDBISAMTable) and will post in the binaries 2 .inc files that have to be inserted at precise locations in isamtb and and one procedure replacement contained in the > comments at the beginning of C:\BDSApps\DBISAM extensions\DBISAMTrix_IMPL.Inc < to patch dbisamen. I tested them with 426b2. TDBISAMTrix means TDBISAM Table Record Id eXtension. There is also a .dpk to build the .BPL with the new component. You'll have to correct paths in the .dpk to your own settings. When you have managed to replace dbisam.bpl with the new .bpl, you can add TDBISAMTrix tables to your forms for testing. No need to recompile or restart DBSRVR. Anyway, just having a look at the supplied snipets of code will give you an idea about my stuff. ****** MAKE BACKUP's AND DONT TRY IT IF YOU ARE NOT USED TO RECREATING .BPL's FROM SCRATCH ********* See prjDBISAMTrix.exe from the ZIP. Session is set to stLocal and the .DAT and .IDX files should reside in the same directory as the .exe They are absolutely unmodified 426b2 tables so you can explore them with DBSys. Now the question of WHY do it. Mr Robert SEAN opens a small accounting firm. He engages Anna Lisa RICH as an apprentice. He calls her Ali and she calls him Bob. Now he wants to setup a very simple agenda using Delphi and DBISAM. Table EMPLOYEE EMP_ID String[4] // Primary key on this field EMP_NAME String[30] Table AGENDA AG_EMP_ID String[4] AG_WORK_DATE Date AG_WORK_DESCRIPTION String[64] Now what key do we put on that table ? It is clear that they will do multiple different tasks in a day, but the programming must remain simple. The form will hold 2 BDGrids arranged with a master/detail connection of Table AGENDA to EMPLOYEE on AG_EMP_ID->EMP_ID. So, always to keep thing simple, the primary key for AGENDA will be made of (AG_EMP_ID, AG_WORK_DATE, RECORDID). RECORDID will take care of multiple rows in a single day. They start with 2 records in EMPLOYEE 'ALI','Anna Lisa RICH' 'BOB','Robert SEAN' and pile rows of job description in AGENDA. BOB and ALI each add about 2000 rows in AGENDA every year. Five year have gone by. There are now 20 employees in Bob's firm all throwing data at AGENDA. Time has come to put something more reasonable in EMP_IP so they change : 'ALI' to 'RALI' initials of LastName+FirstName 'BOB' to 'SBOB' and so on for the others Now what happens with the Master/Detail relashionsip. It is broken so they'll have to build a Query to change all 'ALI' to 'RALI' etc... in AGENDA. The same problem will arise when Ali maries Steve JONES, to keep the rule with EMP_ID 'RALI' will be changed to 'JALI'. That's the moment you will start thinking about what COMMIT count means in update SQL. BUT, JUST BEFORE changing the EMP_ID rules, Tim has added the TDBISAMTrix subclass that allows using RecordId as a "quite normal" TField. Why does it help ? We add a additional index on EMPLOYEE called IxRecId, unique, field RecordID. (Works standard in DBISAM) We add a integer field AG_EMP_RECID to AGENDA. (Standard DBISAM) We run a Update SQL join that puts EMPLOYE.RecorId in the field AG_EMP_RECID joined on AG_EMP_ID=EMP_ID (Standard DBISAM) We replace the primary index of AGENDA with Key made of (AG_EMP_RECID, AG_WORK_DATE, RECORDID) and we can now drop the AG_EMP_ID field of AGENDA. (Standard DBISAM) We change the Master/Detail relationship to AGENDA.AG_EMP_RECID->EMPLOYEE.RecordId >>>ONLY POSSIBLE IF RECORDID CAN BE ACCESSED AS A TField<<<. This will take care of any future changes in the naming scheme for AG_EMP_ID thus never requiring anymore SQL updates to table AGENDA. Now I hear you all saying, just put an AutoInc field instead of using RecordId. I reply that RecordId is the safest guarantied unique field in a DBISAMTable and it is, hidden, but there. It survives rebuilds, repairs, optimize etc... I have many uses for that behvior but can very well live (at my own risks) without it beeing introduced in any official way in DBIsam. What I basically wanted to know was if Tim found a serious DBISAM integrity flaw with my stuff. I modified DBSys to create Browse.BrowseTable as a TDBISAMTrix and a few changes in the Actions Menu so RecordId can be specified when it is part of a table key. It works apparently without flaw to access table on an unmodified DBSrvr. Bruno |
Wed, Apr 9 2008 12:52 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Matthew,
<< Making it an option would be fine, so long as it defaulted to current behaviour. >> I'm having Bruno post his code here in the Binaries newsgroup as a TDBISAMTable descendant instead so as to not cause any major disruptions in the DBISAM code. If customers want to search on the RecordID field, then they can use his component. If not, then they can just ignore it. I know that there are a few customers that wanted this functionality, but I could never put it in DBISAM due to the issues raised in this thread. So, at the very least, they will now have the option. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Apr 9 2008 5:41 PM | Permanent Link |
Sean McCall | Bruno,
I think you've actually demonstrated why it isn't needed. What was selected as the primary key in the five year old system was a poor choice because it is both identifying information for the users as well as the internal record identifier / primary key. You said this is for an old database, so the fix is to add a new field, make it unique, make it the primary key, and adjust your master-detail and other programming for the change. The only difference in steps between your TFIeld addition and the way I think it would normally be fixed is adding the new field to the table and setting it's value. The major part of the work is updating the application to work with the new field, so there is not a lot to be saved by having a ready made alternative exposed for you. Initializing the new field is easy because you can assign the record ID to the new field which makes it unique. One of the things you like about your hacked RecordID field is that it is hidden - but once you expose it is available to grids and controls just like any other field and so I assume you are just hiding it by setting it's visible property to false. You've taken the step to descend from TDBISAMTable - if you need your primary key to be hidden by default it is easy - just have the descendant set the visible property of the fields used by the primary index to false and you'll get the same result. Perhaps the problem is that you want both the record ID (as I will call the abbreviated name) and the primary key to be unique. That belongs in a BeforePost event and it is very easy to do. If there is a matching ID that does not have the same primary key, you've found a duplicate. I haven't looked, but I would bet DBISAM even has a constraint for unique values in an index which would make this a breeze. I don't use auto-inc fields, but you suggest that they don't always remain the same through repairs, optimizes, or whatever. I find this hard to believe since changing this field would break master-detail relationships. But if there really are issues with an auto-inc, make the new primary key field an integer. If you don't want to mess with finding the next value (easy with SQL Max() or Table.Last), you've descended from TDBISAMTable so have your field shadow the value of the recordID. On way would be to override DoBeforePost to save the dataset state and DoAfterPost and set the primary key to the RecordID. If this isn't easy enough, then a request for tim would be the ability to identify a table field that will be set to match the RecordID automatically on posting a new record or have the ability to get the next available record ID for a table. Could be as simple as an AutoInc functionality option - AutoIncMatchRecordID or a more general PrimaryKeyMatchRecordID. I'm sure this would be much simpler to implement than exposing the recordID as a field and having the ability would break no code. Your situation seems to be a pretty run of the mill problem and I have a hard time seeing the justification for tinkering with the internals of DBISAM. In fact, I think you're asking for trouble by creating non-standard behavior by patching the code. When DBISAM has updates, you'll have to modify the code each time with your patches. Who knows what kind of issues you'll have if you decided to move to ElevateDB. You are much safer by making your changes in a TDBISAMTable descendant than doing things that require changing the database engine source code. Maybe I'm missing something and if I have, it's nice that you've posted your code for others to use. Sean Bruno Krayenbuhl wrote: > Hello, > > I thank Tim for taking your opinions and I suppose that means that, at first glance, what > I suggested will not break the internals of DBISAM itself. > > Windows XP pro, BDS 2006, DBISAM426b2 > > ****** MAKE BACKUP's AND DONT TRY IT IF YOU ARE NOT USED TO RECREATING .BPL's FROM SCRATCH > ********* > > WARNING Code still buggy in design mode. > > I have already dropped the request to insert this "feature" in TDBISAMTable class. > Instead I have created a subclass TDBISAMTrix=class(TDBISAMTable) and will post in the > binaries 2 > .inc files that have to be inserted at precise locations in isamtb and and one procedure > replacement contained in the > > > comments at the beginning of C:\BDSApps\DBISAM extensions\DBISAMTrix_IMPL.Inc < > > to patch dbisamen. I tested them with 426b2. TDBISAMTrix means TDBISAM Table Record Id > eXtension. > > There is also a .dpk to build the .BPL with the new component. You'll have to correct > paths in the > .dpk to your own settings. > > When you have managed to replace dbisam.bpl with the new .bpl, you can add TDBISAMTrix > tables to > your forms for testing. No need to recompile or restart DBSRVR. > > Anyway, just having a look at the supplied snipets of code will give you an idea about my > stuff. > > ****** MAKE BACKUP's AND DONT TRY IT IF YOU ARE NOT USED TO RECREATING .BPL's FROM SCRATCH > ********* > > > See prjDBISAMTrix.exe from the ZIP. Session is set to stLocal and the .DAT and .IDX files > should > reside in the same directory as the .exe They are absolutely unmodified 426b2 tables so > you can > explore them with DBSys. > > Now the question of WHY do it. > > Mr Robert SEAN opens a small accounting firm. He engages Anna Lisa RICH as an apprentice. > He calls her Ali and she calls him Bob. > > Now he wants to setup a very simple agenda using Delphi and DBISAM. > > Table EMPLOYEE > EMP_ID String[4] // Primary key on this field > EMP_NAME String[30] > > Table AGENDA > AG_EMP_ID String[4] > AG_WORK_DATE Date > AG_WORK_DESCRIPTION String[64] > > Now what key do we put on that table ? It is clear that they will do multiple different > tasks in a day, but the programming must remain simple. The form will hold 2 BDGrids > arranged with a master/detail connection of Table AGENDA to EMPLOYEE on AG_EMP_ID->EMP_ID. > > So, always to keep thing simple, the primary key for AGENDA will be made of (AG_EMP_ID, > AG_WORK_DATE, RECORDID). RECORDID will take care of multiple rows in a single day. > > They start with 2 records in EMPLOYEE > 'ALI','Anna Lisa RICH' > 'BOB','Robert SEAN' > > and pile rows of job description in AGENDA. BOB and ALI each add about 2000 rows in AGENDA > every year. > Five year have gone by. There are now 20 employees in Bob's firm all throwing data at AGENDA. > > Time has come to put something more reasonable in EMP_IP so they change : > 'ALI' to 'RALI' initials of LastName+FirstName > 'BOB' to 'SBOB' > and so on for the others > > Now what happens with the Master/Detail relashionsip. It is broken so they'll have to > build a Query to change all 'ALI' to 'RALI' etc... in AGENDA. The same problem will arise > when Ali maries Steve JONES, to keep the rule with EMP_ID 'RALI' will be changed to > 'JALI'. That's the moment you will start thinking about what COMMIT count means in update > SQL. > > BUT, JUST BEFORE changing the EMP_ID rules, Tim has added the TDBISAMTrix subclass that > allows using RecordId as a "quite normal" TField. Why does it help ? > > We add a additional index on EMPLOYEE called IxRecId, unique, field RecordID. (Works > standard in DBISAM) > We add a integer field AG_EMP_RECID to AGENDA. (Standard DBISAM) > We run a Update SQL join that puts EMPLOYE.RecorId in the field AG_EMP_RECID joined on > AG_EMP_ID=EMP_ID (Standard DBISAM) > We replace the primary index of AGENDA with Key made of (AG_EMP_RECID, AG_WORK_DATE, > RECORDID) and we can now drop the AG_EMP_ID field of AGENDA. (Standard DBISAM) > We change the Master/Detail relationship to AGENDA.AG_EMP_RECID->EMPLOYEE.RecordId >>>ONLY > POSSIBLE IF RECORDID CAN BE ACCESSED AS A TField<<<. This will take care of any future > changes in the naming scheme for AG_EMP_ID thus never requiring anymore SQL updates to > table AGENDA. > > Now I hear you all saying, just put an AutoInc field instead of using RecordId. I reply > that RecordId is the safest guarantied unique field in a DBISAMTable and it is, hidden, > but there. It survives rebuilds, repairs, optimize etc... > > I have many uses for that behvior but can very well live (at my own risks) without it > beeing introduced in any official way in DBIsam. What I basically wanted to know was if > Tim found a serious DBISAM integrity flaw with my stuff. > > I modified DBSys to create Browse.BrowseTable as a TDBISAMTrix and a few changes in the > Actions Menu so RecordId can be specified when it is part of a table key. It works > apparently without flaw to access table on an unmodified DBSrvr. > > Bruno > |
Thu, Apr 10 2008 8:01 AM | Permanent Link |
Bruno Krayenbuhl | Sean,
thank you very much for your comments. > Your situation seems to be a pretty run of the mill problem and I have a > hard time seeing the justification for tinkering with the internals of > DBISAM. In fact, I think you're asking for trouble by creating > non-standard behavior by patching the code. When DBISAM has updates, > you'll have to modify the code each time with your patches. Who knows > what kind of issues you'll have if you decided to move to ElevateDB. You > are much safer by making your changes in a TDBISAMTable descendant than > doing things that require changing the database engine source code. TDBISAMTable descendant : I do it already to override Lookup and Locate so I can do some internal caching on client workstations with some very basic synchronization rules. That greatly diminishes network trafic and charge on the server. This way I have nearly no non DB Controls at all on my forms and that simplifies development. Initialy, I wrote a unit TDBISAMTrix that contained TDBISAMTrix=class(TDBISAMTable) and did satisfy my purposes BUT it is 603 lines long and involves so much cheating, such as peeking and modifying private fields in numerous classes that I find it easier to add my include instructions at the right place in dbisamtb and patch the required procedure in dbisamen. As for what reason, except the simplified example I exposed, I would like to go at the RecordID data is that I want to evolve my existing local caching TDBISAMTable to add minimal and GENERIC referential integrity checks, cascading deletes and updates. And there is only one field that already and always exists, has no duplicates in the same table and lives unchanged thru any official restructuring in any TDBIsamTable. It is RecordID. It is an Integer and Integers are the fastest, cheapest and most simple field type to include in an index. My currently running applications use PARADOX, and I have had lots of trouble (buggy MS network communication up to and including version 98SE) until all my clients had Windows NT/2000/XP workstations. I'am afraid about similar problems resurfacing in Vista. So I have to move on. DBISAM is a perfect and very satisfying replacement, right sized, source code, some realy nice features I like and use, stable. And it is possible to write neat stored procs in the server. All this I like. Bruno |
Thu, Apr 10 2008 9:49 AM | Permanent Link |
Sean McCall | Bruno,
Sounds like you have it under control. I also am an old paradox user and still maintain two large apps that use it. I learned long ago what a great advantage there is to only using a descendant of a TxxxxTable component in an app even if you start with nothing but TMyTable = TDBISAMTable. It is so easy to make application wide fixes when you have a common descended control. I completely abstract my database access with a homegrown system to do generic referential integrity, client side buffering, cached lookups, and all sorts of stuff that makes data management easy. I actually have a parallel object structure for a session, database, table, and add one for a cursor and write everything generically so I can swap from using DBISAM4 to ElevateDB to TClientSet or anything else or any combination I want based storage by just swapping out a database engine driver. This is a single line code change. I could hook to a customer's Oracle database if I want - all I need to do is fill in some abstract methods in my database engine object. I think I get why you want to expose and use RecordID. It is a ready made stable key generator. The problem is that it is specific to DBISAM. Since you have put in so much effort to make things work generically it seems you should extend that generic behavior to your primary key. This will make sure your code is ready to make the move to a next generation database product if you want. It could be Elevate 2 - which sounds very interesting - or maybe something else. Remember that your problem is with auto-inc, not a primary key. There is no way any of those functions you mentioned could change an ordinary field or else they would be completely worthless. I also wouldn't worry about the size of your code. 603 lines is not very much. My main database abstraction unit contains about 8K lines of code. To give you an idea of what you can do with your own primary key. I use a large integer primary key, which I call a record serial number, for nearly all my records. My routines are coded to support any kind of primary key including multi-field keys, but I rarely use them. One pre-defined way I use the serial number primary key is a variation I call a global serial number. Each table in a database is assigned a 4 digit base-10 code. The key stored is a combination of it's sequence number and the database code. For example, the second record in table 1010 is 21010, the third is 31010. This allows me to be able to have foreign keys that can refer to groups of tables with common characteristics. For example - customers and vendors could both have addresses and be check payees. The lookup caching can both determine the table to look in and the record to find in that table from a single key. You can do some pretty cool stuff easily with this system. Of course, it is your call. I definitely wouldn't take the chance of tinkering directly with the database engine - if you accidentally break something in the code your data will be at risk. Then again, I don't have the big picture of what you are doing, so I still could be missing something. Good luck with it, Sean Bruno Krayenbuhl wrote: > Sean, > > thank you very much for your comments. > >> Your situation seems to be a pretty run of the mill problem and I have a >> hard time seeing the justification for tinkering with the internals of >> DBISAM. In fact, I think you're asking for trouble by creating >> non-standard behavior by patching the code. When DBISAM has updates, >> you'll have to modify the code each time with your patches. Who knows >> what kind of issues you'll have if you decided to move to ElevateDB. You >> are much safer by making your changes in a TDBISAMTable descendant than >> doing things that require changing the database engine source code. > > TDBISAMTable descendant : I do it already to override Lookup and Locate so I can do some > internal caching on client workstations with some very basic synchronization rules. That > greatly diminishes network trafic and charge on the server. This way I have nearly no non > DB Controls at all on my forms and that simplifies development. > > Initialy, I wrote a unit TDBISAMTrix that contained TDBISAMTrix=class(TDBISAMTable) and > did satisfy my purposes BUT it is 603 lines long and involves so much cheating, such as > peeking and modifying private fields in numerous classes that I find it easier to add my > include instructions at the right place in dbisamtb and patch the required procedure in > dbisamen. > > As for what reason, except the simplified example I exposed, I would like to go at the > RecordID data is that I want to evolve my existing local caching TDBISAMTable to add > minimal and GENERIC referential integrity checks, cascading deletes and updates. And there > is only one field that already and always exists, has no duplicates in the same table and > lives unchanged thru any official restructuring in any TDBIsamTable. It is RecordID. > It is an Integer and Integers are the fastest, cheapest and most simple field type to > include in an index. > > My currently running applications use PARADOX, and I have had lots of trouble (buggy MS > network communication up to and including version 98SE) until all my clients had Windows > NT/2000/XP workstations. I'am afraid about similar problems resurfacing in Vista. So I > have to move on. > > DBISAM is a perfect and very satisfying replacement, right sized, source code, some realy > nice features I like and use, stable. And it is possible to write neat stored procs in the > server. All this I like. > > > Bruno > |
Thu, Apr 10 2008 10:09 AM | Permanent Link |
Rolf Frei eicom GmbH | Why can't we simply write this SQL to get the RowID as part of your
resultset? SELECT RowID, Field1, Field2 FROM Table So there is the RowID for anyone who needs it and it doesn't break code. This is something I would love to get, but it shouldn't automaticly added to any query. Regards Rolf "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schrieb im Newsbeitrag news:1B898032-F141-4DD4-B7D6-E44C86CD18D3@news.elevatesoft.com... > Matthew, > > << Making it an option would be fine, so long as it defaulted to current > behaviour. >> > > I'm having Bruno post his code here in the Binaries newsgroup as a > TDBISAMTable descendant instead so as to not cause any major disruptions > in the DBISAM code. If customers want to search on the RecordID field, > then they can use his component. If not, then they can just ignore it. > > I know that there are a few customers that wanted this functionality, but > I could never put it in DBISAM due to the issues raised in this thread. > So, at the very least, they will now have the option. > > -- > Tim Young > Elevate Software > www.elevatesoft.com > |
Thu, Apr 10 2008 1:30 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Rolf,
<< Why can't we simply write this SQL to get the RowID as part of your resultset? >> You can. The issue has always revolved around FindKey, SetRange, etc. as opposed to the SQL. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Apr 10 2008 10:50 PM | Permanent Link |
Sean McCall | Bruno,
I know you are using the RecordID as the primary key. How are you using it in Master-Detail relationships since the master record's RecordID is not in any way automatically part of a detail record? I guess I am curious as to why you need "SetRange" functionality. In my mind, a primary key should have no special significance other than as a unique identifier for a record, so setting a range on this value puzzles me unless you are also using it as a proxy for entry order - something I don't think I'd take for granted. Again, I haven't looked at the code but I'd venture to guess that somewhere in the engine there is the ability to efficiently locate a record based on it's RecordID, so exposing that would cover FindKey/Locate. I don't want to imply that there is no need to do what you are doing - I am not writing your program nor do I have a grasp of your needs - just trying to understand. Sean Tim Young [Elevate Software] wrote: > Rolf, > > << Why can't we simply write this SQL to get the RowID as part of your > resultset? >> > > You can. The issue has always revolved around FindKey, SetRange, etc. as > opposed to the SQL. > |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Monday, April 29, 2024 at 05:23 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |