Primary keys in nested dataset

A main issue I had to solve, so that I could cover cases of nested datasets (with nesting level >2) was how to handle primary key values if it belongs to nested dataset. Client datasets consider two nested datasets of the same "depth level" to be different and this means that for example values 1,2,3 of one nested dataset and 1,2,3 of another represent different records of the detail table (in back-end database). Of course, under normal circumstances this is not a problem since these values are temporary and are replaced by a permanent value from the database back-end.
However, this was a major problem related to use of these values in my components as primary key values referenced by some foreign keys of another Cds (not belonging to this Delta Tree). I had to implement custom Autoincrement (decrement) behavior of these primary keys, so that values they take are unique among all nested datasets of the same "depth". This made applying updates also safer (using my custom solution) and building complicated file based apps (with many CDSs relating to each other) feasible

Found a work around for D2005

I continued testing, this time on Delphi 6 and the same bug that occurs on D2005 came up. I tried to figure out what was the problem and since in all cases I used D2005 midas.dll the problem should be somewhere inside DBClient.pas. I compared the relevant code and found out that only on D7+supplemental database update (in CloneCursor implementation) a call to CancelRange was made (in case Reset parameter is true). I just added this call in my overridden CloneCursor method and this way fixed the problem.
What I don't understand though is why was this change not included in D2005 version of DBClient.pas. May be a call to CancelRange causes another problem I can't imagine of.

A rather clumsy solution

I finally made up my mind and decided how to handle the issues I referred to in my previous post. Even though I hate this solution, I did add a comment in my help file and in my ReadMe suggesting avoiding using UndoLastChange and the rest. On the other hand, I provide (in my "Interbase" sample application an example of how UndoLastChange (and only this) can be used safely without concerning about braking referential integrity.
As for the matter of reconciliation errors, I followed a little bit more radical approach (even though still clumsy). I decided not to "publish" OnReconcileError in TKTClientDataSet, hook OnReconcileError to a custom handler that does some validation and "redirects" to another event a made called OnKTReconcileError. This way I added the functionality I wanted.
P.S. I hate this "KT" that I am using here and there when I am trying to point that something is specific on the components I've made. (This represents my inability of finding more descriptive names- probably lack of imagination!)

More bugs!

As I keep testing on my components, I find a lot of bugs (as expected). Some of them are easy fixing, but there are others that require some important changes. The most important (and difficult to deal with) is the following:
What I care most with these components is to achieve preserving data integrity in client side. But, I found out that if UndoLastChange, RevertRecord or raCancel (through standard reconciliation dlg) is called against a "master" dataset, then data integrity is lost!
And the most difficult thing about it is that none of the above methods is declared as virtual! I will have to "reintroduce" them so that I am able to handle integrity (notify "detail" datasets about the change). That has of course very important side- effects (for example standard TClientDataset related actions could not be used any more). An another option is just to update my help file and inform developers using my components to avoid calling the above methods. I really hate this idea though, because this would be very restrictive.

Should I redesign?

Recently, I re-read the excellent bdn article about dynamic Midas constraints and I remembered how much impressed I was about this capability of Datasnap. You can build a really thin client and don't even have to redeploy your client application and constraints can be altered as necessary.
What I've been thinking though is that the way KT Data Components are designed is contradicting to Midas philosophy. Forming (or manually copying) referential constraints in client side somehow spoils multi-tier theory (that is business rules and constraints should be defined on application server and simply be fetched to client).
Redesigning my components is of course an option. I could do this (although not easy) by defining a primary key collection property of a TKTDataSetProvider (representing Provider.Dataset primary key for each dataset that is included in the nested dataset structure that Provider forms) and a foreign key collection (from persistent fields belonging to the remote datamodule) for each primary key (possibly with a treeview like property editor).
I could then override Cds.GetRecords somehow to include these properties and this way enforce referential constraints in client side without having to manually redefine them. Of course, I should redesign Cds.ForeignKeys property so that it is not based on TField.Name, but TField.FieldName (finding a way to qualify FieldNames so that they are unique)
This is indeed very complex and in many aspects seems a better approach to design and implement from scratch, than trying to modify current implementation.
On the other hand, I don't know if referential integrity constraints alter so often (related for example to a check constraint e.g. "Salary>12000 and Salary<35000". A change in referential integrity constraints of a database more often represents a radical change in whole application design and that means that chances are that the client application would also have to re-build.
Besides, only TField.CustomConstraint properties are included in datapackets. In practice (and as far as I know), record level constraints should also be manually defined in client side (through Cds.Constraints propery).
Taking all these in account, may be leaving the implementation of my components as is should probably be enough. I don't know though (I repeat I don't have much experience to judge correctly) which aprroach is best. If anyone using my components has any suggestion, or comment, I certainly would like to have some kind of feedback

Nested datasets better handled

I finally finished testing and debugging a custom solution I made and this way I am now able to handle "connector" fields of nested datasets properly (and automatically). I solved issues I refered to in a previous post (having to handle OnGetConnectorFldIdx event, and cds.OnNewRecord). Now, everything is handled automatically (if appropriate PrimaryKey and ForeignKeys properties are set).
In addition, I fixed some bugs related to clone support and during reconciliation. The latter bothered a lot and the reason is that Datasnap doesn't give a lot of chances for a component developer, so I had to find a custom solution.
The results OleVariant returned by a datasetprovider component consists of errors and values propagated back from server. In fact, it is in TCustomClientDataset format and I had to dig into this (trial-error) to be able to distinguish between "real" errors and values I want automatically to be merged to Cds.

Porting to D2005 will have to wait

Unfortunately, D2005 version of KT Data components will have to wait (although I prepared all the IFDEFs). In fact, as my components base heavily on CloneCursor method (with Reset parameter to true), this doesn't seem to work properly in D2005 (with Update 2 installed). I've built a test case app and sent to QC#11339.
Description:
When mastersource and masterfields of a Cds are set and you call CloneCursor with Reset parameter set to true, then this doesn't properly reset the clone's mastersource and masterfields properties, so that all records are inluded (even though, it works perfectly well on Delphi 7)

How Applying updates works

I have to clarify some things relevant to TKTDatasetprovider and how it applies updates. Truth is I should have added more comments in that part of code and should have made it more clear. If someone checks the code of KTProvider.pas probably he will get confused. What is TIntegerPair, what is TDependOnInfo?

(I have to say that from now on I'll use the term "master cds" to denote the "least dependent dataset" and the term "detail cds" to denote the "most dependent cds"- Don't confuse with master-detail relationships)

I'll try to make it clear here:

The main issue I had building a way to wrap all updates in one call to server was this: If I have a newly inserted record in a "master" cds ( and I have either an insertion or an update in a "detail" cds that uses this new master primary key value, how is it possible to update the foreign key value with the new value during applying updates, since we are talking about two different deltas?
It is a usual technique to give a negative value to the primary key of a cds when inserting a new record in a client dataset (in a multitier application) and give it new positive values (from a generator function) in BeforeUpdateRecords, during applying updates. This value will then be propagated back to the client (if poPropogateChanges is set which is the default behavior in TKTDataSetProvider).
If a foreign key (belonging to a different cds, not in a nested dataset) references this negative value, then this cds should probably be refreshed after applying updates.
I tried to find a way to bypass this extra round-trip and that made my code a little bit complicated. I implemented the following steps:

1. I call provider.ApplyUpdates once for all deltas, but I don't make any real updating in this step (I set BeforeUpdateRecord Applied parameter to True). What I do is call the generator function and fill the IntPairList property of TKTDataSetProvider.
• This property is a TList of TIntegerPair objects. These objects (as their name actually says ) have two Integer fields: OldValue and NewValue. The other fields (ParentIdValue and QualifiedFieldName) are just used for "precise navigation" to the relevant OldValue. OldValue takes the negative value set on client side and NewValue is the new value we just got using the generator function.
2. I repeat the previous step, only this time I dohe actual applying of updates and in particular only insertions in a order from the "least dependent" to the "most dependent" Delta. At this step I use the values from IntPairList property and assign the new primary key value (always in a BeforeUpdateRecord handler). At this step I update and any negative foreign key value and I use DependOnInfoList property to achieve this.
• DependOnInfoList is set in client side and is sent along with the OleVariant which contains deltas. It has information that allows us to "navigate" to the "correct" IntPairList that has the new value that we want our foreign key field to be assigned to.
  
3. I repeat the previous step, only this time I do it in the reverse order and apply updates only relevant to modifications or deletions.

May be the above custom solution is very complicated, but the goal is to avoid having to refresh the data of a "detail" cds, each time I apply updates and to be generic enough to cover complicated cases. This can be combined with a technique to check if some other user has applied any changes and only then refreshing data (I have an example in my help file-in Cds.NeedsUpdate property). Using the above code all new values set on app server automatically propagate back to client and get merged (preserving data integrity) (Of course, this happens only if transaction commits)
May be I could achieve the same thing with simpler implementation, but I am not a professional component developer and I can't think of a simpler work around. The truth is that it is working as it is. (And I've tested it in a complex application with 23 tables and one nested dataset structure 4 levels deep). Anyway, any suggestion is always welcomed!

Working with nested datasets

I have to make clear one thing (I've omitted from my Help file- I'll update it in a future release) relevant to forming a master/ detail relationship on app server and having a nested dataset structure on client side. ForeignKeys property doesn't apply to fields representing the "connector" field in nested datasets. I'll be more specific with an example (the same as in a previous post):

1. Create the following tables in a back-end database
• Categories
• CatID:Integer
• Name: VarChar(50)
• Items
• ItemID:Integer
• Name: VarChar(50)
• ItemCat
• ItemCatID:Integer
• CatID:Integer
• ItemID:Integer
  
2. Create relationships
• ItemCat.CatID foreign key referencing Categories.CatID with on update rule set to CASCADE and delete rule set to SET NULL
• ItemCat.ItemID foreign key referencing Items.ItemID with on update and delete rule both set to CASCADE
3. Build an application server and form a master/ detail relationship between Items (master table) and ItemCat (detail table) and hook a TKTDataSetProvider to Items dataset. Create another TKTDatasetprovider and hook it on Categories dataset
4. Build a client application and create three Cds (and build their persistent fields):
• CategoriesCds (set providername prop to respective TKTDatasetprovider component)
• ItemsCds (set providername prop to respective TKTDatasetprovider component)
• ItemCatCds (Set DataSetField prop to the created DatasetField of ItemsCds)
5. You should only set CategoriesCds.ForeignKeys property to "ItemsCatCdsCatID=9" and NOT ItemsCds.ForeignKeys:="ItemsCatCdsItemID=10". That is, you should only set the ForeignKeys property for a field that doesn't belong to the same nested dataset structure.
6. In addition, you should set a handler for ItemsCatCds OnNewRecord event and add this line (or else ItemsCatCdsItemID will have a null value and this will create problems when trying to use BatchApplyUpdates) :
• ItemsCatCdsItemID.Value:=ItemsCdsItemID.Value;
  

I'll probably add better support (solve issues 5 and 6) to nested dataset structures in a newer version of KT Data Components.

Part of Midess

I contacted Dan Miser today and KT Data Components became a part of Midas essential pack (Midess) open source project. Since, Midess is a well known project in Datasnap developers I think that this will enhance feedback on my components. Thanks Dan!

Why did I build KT Data Components?

My components I refer to in my previous post are called KT Data Components. They are actually just a ClientDataSet and a DataSetProvider descendant. The reason I began this effort is because DataSnap/midas didn't cover my needs in a particular point: many-to-many relationships and I tried to enhance functionality so that I could use these components the way I would like them to be used. When I began writing the components I was unaware (and I still have little knowledge) of ADO.NET. My motivation became stronger as I read some of Dan Miser's older BDN articles and used midas essential pack components
In particular, the use of clientdataset as lookup tables seemed very convenient, as was the technique of wrapping multi-clientdataset updates in a transaction. To be more specific, I' ll start with this:
All Datasnap documentation I've read about says that the presentation layer of a multitier application should be represented by a thin client, that could be able of working (optionally) as a briefcase model. This way business rules and logic should be on an application server and the client side would only be able to represent the user interface. TClientDataSet was designed with this in mind. But, what impressed me from the beginning is how master-detail relationships are handled in Datasnap. The forming of one object structure (ClientDataSet with master and nested datasets) in client side and simply having to manipulate the MasterCds is really a pioneer technology, even for contemporary standards. The nested dataset structure even if it can handle m-d-d-... relationships very well, it has an obvious drawback. It isn't able to handle many-to-many relationships in the same elegant way.
For example, let's say we have a database where we have a categories table and and items table and we would like an item to belong to more than one category. We should create a third table (ItemsCat) to represent the relationship and possibly add some kind of referential constraints to preserve data integrity. How can this kind of data be manipulated using Datasnap? I could say that there are the following ways:

• We could form a master- detail relationship on application server using as master the Categories table and as details the ItemsCat table. We could do that also between the Items (master) and ItemsCat (details) table, and then fetch to client side two nested dataset structures having two different views of data. This solution is not acceptable though as we consume network bandwidth having to move ItemsCat twice
• We can create a join query on these tables (in the back-end database) and "provide" the client with the data of this query. The user then, is able to manipulate the query and the provider should handle all the steps needed to update the join. This is the most used approach, although this way the client is "really" thin and users are now accustomed to better user interfaces
  
• We could create three DataSetProvider components for each table and three clientdataset components in client side and form the link in clientside (using mastersource and masterfields properties of clientdataset). This is reasonable of course, only in cases where all tables are not very large, since this way we oppose the client/server theory of building applications (i.e. fetching only data that are necessary for user to interact with)
• Suppose Items table has 100000 records and categories table has 100 records. I believe this is quite frequent (not all tables of a database have millions of records). We could form on application server a master- detail relationship between Items (master) and ItemCat (detail) and fetch as many records as appropriate to client (through clientdataset Packetrecords property) in a nested dataset structure form. We could use a separate TDataSetProvider component on server hooked to categories table and create the respective client dataset (in client app). Then we could easily have this table CategoriesCds work as a lookup table (form a lookupfield on nested ItemsCatCds dataset). This is the approach (more or less) proposed by Dan Miser when he refers to static lookup tables.

I believe that the last two solutions (and the last one in particular) are the best way to create a "not so thin client" application. But, there comes an apparent problem . Ok, we say that categories table is static (or rarely changing), but what happens, if for example a user deletes a record from this table (Cds)? This is up to the developer to decide of course. We have (in client side) two clientdataset components (a simple and a nested structure). I believe that there should be some kind of easy way to handle problems like that and that's why I built my components. TKTClientDataset component, straightly inherited from TClientDataSet simply has a new string property (ForeignKeys) that defines what happens when a user updates this component's primary key. Unfortunately, since my component is rather simple I have bound Fields[0] and defined it implicitly as the primary key. In addition, all foreign keys and Fields[0] (primary keys) should be of TIntegerField type. But anyway, it suits my needs at this moment and covers the problem noted above. If I delete a record from CategoriesCds table, then the foreign key field can be set to null, or automatically be deleted (cascade deletes), according to the property attributes I've set in design time.
And what about, applying updates. These are handled automatically by my BatchApplyUpdates method, they are wrapped in a transaction (as appropriate) and they are applied in the correct order (i.e. if I had cascade deleted in the previous example, then ItemsCat deleted record would have been applied first and Categories deleted record would follow).
Of course, there are a lot of things to be done so that KT Data Components become more elegant. First of all, I should make a new property called primary key that denotes the field that should be used as such (and cutoff dependency on Fields[0]). Second, may be I should add support to all field types (not just TIntegerField). Third, I have to find a way to handle circular references and many more...
On the other hand, I don't know if I will be able to achieve these goals (at least without help) since the components, as they are, cover all my needs at the time. Besides, I've heard that .NET DataSet may be handles all these issues as we are talking and not a lot of developers might be interested in Datasnap anymore. I'll give it a try though and if anyone is interested in asking anything relevant, I would be very glad to answer. In addition, any suggestions, or help are welcomed too!

Entering this blog thing

Yes, I finally took the decision to follow this new trend. I am not the kind of person who likes to share a lot of his thoughts with others on the net, but I do believe this "blog" thing is a tool that can enhance my communication by sharing thoughts that I allow myself to share. As for the title of my blog I have to say that "Remote" thoughts is because I still feel a little stranger to this and because the thoughts I would like to share here are relevant to remote (distributed) application building.
A few words about me. I am an unemployed physician at the moment (and for the following 2-3 months) and I have been able to get deeper in what is my main hobby since childhood: programming.
I've been using Delphi for the past 7 years and in the past I used all forms of Basic you could ever imagine (from Amstrad CPC464, QuickBasic and VisualBasic, until version 4). I have built a lot of applications (mainly for personal use ) and the nature of my job (medicine) led me to emphasize on database programming. I learned a lot about SQL, normalizing databases e.t.c and fact is I learned Delphi in parallel.
Recently, I finally "published" my first component (in http://cc.borland.com/ccweb.exe/listing?id=23068) without any restriction and this blog will be used to share thoughts about it and how it can be enhanced