Generic search on composite keys and a generic routine...

I've been struggle trying to build a routine to make generic searches on composite keys without knowing how many fields the 'compkey' had. The help states that you have 3 ways of making this kind of search:

1.- hreadseekfirst(ORDERS,CompKey,hbuildkeyvalue())

2.- hreadseekfirst(ORDERS,CompKey,[Value1,Value2,etc...])

3.- hreadseekfirst(ORDERS,CompKey,sKeyValues)

I'm trying in the third way because of the generic aproach, but only search for the first value of the 'CompKey'. (I've been using Convert() for numeric values and Complete(svalue,Charact(0)) for the string values without luck)

There is somebody doing something like this?

TIA,

Pedro Santana Viera

Hi Pedro,

some years back I managed to get things with composite keys (mixed text & numerics) right. After some struggle I found out that its not worth the endeavour because those keys aren't readable by humans and have a bunch of other deficiencies. This is nothing against PC Soft and their field type 'composite key' but everything against keys which aren't properly formatted and unreadable by programmers and users.

My solution (hey, I know, one of you will be upset again ..):

I'm using triggers extensively (wished there was a trigger on HRead-operations too!) and in that trigger-procedure (on HAdd & HModify) I'm building 'composite keys' in string format. Strings guarantee for proper sorting, can easily be read by humans, parts of can easily be extracted and so on. So, a store number (integer) plus an alphanumeric part number will become "00155__AB6-12345" and so on. Further I'm using the same triggers to build keys for directly feeding combo boxes, without any text fields as an explanation. For example, a customer number and the short customer description will be combined into something like "__5688 - Manufacturer of useless Goods" this is great in a combo. You need to write the trigger procedure only once and it will work all over the application, any place. With HF C/S there will be even stored trigger procedures in WD12 real soon now.

Kind regards,
Guenter

sorry for the delay and thank you for the answer. The problem for me is that I have many files with this composite keys and code that make use of it. If I can I prefer do not touch that code. (In other side, I still wonder that the simple thing is the better approach)

The question is still in the air:

Somebody had built this kind of search on composite keys and how?

Ej.:
hReadSeekFirst(ORDERS,sCompositeKey,sCompositeKeyValues)

TIA

Pedro Santana Viera

Hello Pedro

Regardless of how the composite value is created in the file, the lookup key must contain x number of segments that match the start of the value in the file and you can fill out the remainder with hMinVal and HMaxVal. In terms of using a Windev constructed file composite value, the safest way to construct the lookup key is by using HbuildKeyValue()

I have always found it odd, given that Windev is a database centric language, that the index functionality is one of it's poorest features.
The fact that PCSoft had to actually provide a function to create a workable lookup key highlights the strangeness of their composite key structure.

Regards
Al

Hi Pedro...

I did that a few years ago... Basically, I was doing it using
hbuildkeyvalue (even if the function didn't exist at the time, in wd 5.5)...

The first thing to do is to use the hlistxxx functions to find out how
many fields are in your key, then to find out the type of the fields,
and build the key values accordingly...

it's perfectly doable, a little complex, but doable

best regards

-- Fabrice Harari International WinDev, WebDev and WinDev mobile Consulting More information on [www.fabriceharari.com]

Yo Pedro,

mayby this will help you out



How to create one key
open the analyse
Select your table

Example:
Table DossierAdress
Fields:
DossierID id from table Dossier
AdresId id from table Adres
Type nr 1-9: 1 =agent, 2 = customer, 3 = ,…
And so on…

Go to the last veld in the file items and give it a name like

IX_Dossier_Adress_Type name is given because we want to have a key on the different adresses of an dossier with an type

In the Type combobox you select than the word “Composite key”
When leaving this field there should be open another screen
There you can select the fields of that file you want to merge in the same key
Select DossierId then AdresID then Type

Save it, key is created

Then in the code you can refer to that key

When you have the next records
DossierID AdresID Type the IX_Dossier_Adress_Type
125 300 1 1253001
125 300 4 1253004
2500 6 1 250061
2500 6 2 250062
2500 4 1 250041



Searching:

sCompositeKeyValues is string // always string
sCompositeKeyValues = valueOfDossier + valueOfAdres + valueOfType

hReadSeekFirst(DossierAdress, IX_Dossier_Adress_Type ,sCompositeKeyValues)


let me know if this was helpfull

Marc :xcool:

the records are

DossierID - AdresID -Type -IX_Dossier_Adress_Type
125 - 300 - 1 - 1253001
125 - 300 - 4 - 1253004
2500 - 6 - 1 - 250061
2500 - 6 - 2 - 250062
2500 - 4 - 1 - 250041

I think I know what you are saying. And this is what I did:

PROCEDURE GetCompositeKeyValue(sTableName is string,sCompositeKeyList is string, sValuesList is string)

i is int = 1
sCompositeKeyItem is string
sItemsList is string = HListItem(sTableName,hLstDetail)
sItem is string
sValueItem is string
sResultado is string = ""


// Recorremos la lista de valores de busqueda para completarlos
sValueItem = ExtractString(sValuesList,firstRank,"/")
WHILE sValueItem <> EOT
sCompositeKeyItem = ExtractString(sCompositeKeyList,i,"+")
sItem = ExtractString(sItemsList,firstRank,CR)
WHILE sItem <> EOT
IF Upper(ExtractString(sItem,1)) = Upper(sCompositeKeyItem) THEN
SWITCH ExtractString(sItem,3)
CASE hItemBoolean:

CASE hItemDate6,...
hItemDate8,...
hItemDateTime,...
hItemTimeLength,...
// hItemInteger1,... ----> I don't know why WD11 says it don't exist
hItemInteger2,...
hItemInteger4,...
hItemInteger8,...
hItemUnsignedInteger1,...
hItemUnsignedInteger2,...
hItemUnsignedInteger4,...
hItemUnsignedInteger8,...
hItemHour,...
hItemCurrency,...
hItemReal4,...
hItemReal8,...
hItemRealTurbo:
sResultado = sresultado + convert(sValueItem)
CASE hItemText:
sResultado = sResultado + Complete(sValueItem,extractstring(sitem,4),Charact(0))

END
END
sItem = ExtractString(sItemsList,nextRank,CR)
END
sValueItem = ExtractString(sValuesList,nextRank,"/")
i++
END

RESULT sResultado


In the end, only search for the first field on the key index with this function ¿?

Do you see something wrong?

TIA

Pedro Santana Viera

thank you for your help Marc, see my answer to Fabrice to see what I did at first.

Researching with the Windev Help I read that WD7 to WD11 had a different way to manage the spaces after the text in the database and I thought I can quit Complete(sValueItem, Size,Charact(0)) and put sResultado = sResultado + sValueItem in the routine. All in all, this not work for me (in my example one field string(2) and another unsigned int on 4 bytes). It did the same result: only search (or find) for the string field.


Best regards,
Pedro Santana Viera

Thank you for your answer Al, I know I can do that with HbuildKeyValue, but I don't want to make this routine especific for only one case. All in all, I'm with you, not only the composite index but no conditional index and the likes. But I like so many other things of Windev I can't count all together. I will see what I can do.

Best regards,
Pedro Santana Viera

Hi,

I'm having also composite keys which form a unique identifier of a record in some cases. However, in order to be compliant to relational algebra, those database tables (as every other) also contain a primary unique auto increment identifier (or GUID for cross system uniqueness if required) which is used as foreign key in the other database tables to relate to the table with the composite key.

For searching and lookup functions I have defined queries on the table using the fields from the composite key (and joining other tables in if required to have a denormalized view on all data required). When I only need to search on part of the composite key, I just pass in values for the fields I want to look upon and null in the ones I want to discard. This makes that I hardly need any of the composite key hxxx functionality in WX (I rarely use it, even for intermediate n-n relation tables since these also have a unique identifier aside from the unique composite key) and that my database layout is fully compliant with standard relational normalization rules. Every tupple (~=record) has only one unique primary identifier, namely the auto-increment ID field. Aside from this a unique composite key field may exist but it is never the primary unique key, just another unique key. This is why I very rarely need any of the hxxx composite key functions...

Just my 2 cents,

Peter H.

Hello Pedro...

I didn't test it, so I cannot be sure of course, but that's the spirit...
What I can tell you is that you can simply your code using hbuildkeyvalue (it now exist, so why not use it)...

to do that:
- just test how many components are in the key you are using
- Use a procedure with a number of parameters big enough for all your case (say 10, as you probably will never have more than 10 fields in a composite key)
- then do a switch case on the number of fields in the key
switch iFieldsInKey
case 2
sResult=hbuildkeyValue(File, Key, Param1, Param2)
case 3
sResult=hbuildkeyValue(File, Key, Param1, Param2, Param3)
and so on

Best regards

Fabrice

Quote
Pedro Santana Viera
I think I know what you are saying. And this is what I did:

PROCEDURE GetCompositeKeyValue(sTableName is string,sCompositeKeyList is string, sValuesList is string)

i is int = 1
sCompositeKeyItem is string
sItemsList is string = HListItem(sTableName,hLstDetail)
sItem is string
sValueItem is string
sResultado is string = ""


// Recorremos la lista de valores de busqueda para completarlos
sValueItem = ExtractString(sValuesList,firstRank,"/")
WHILE sValueItem <> EOT
sCompositeKeyItem = ExtractString(sCompositeKeyList,i,"+")
sItem = ExtractString(sItemsList,firstRank,CR)
WHILE sItem <> EOT
IF Upper(ExtractString(sItem,1)) = Upper(sCompositeKeyItem) THEN
SWITCH ExtractString(sItem,3)
CASE hItemBoolean:

CASE hItemDate6,...
hItemDate8,...
hItemDateTime,...
hItemTimeLength,...
// hItemInteger1,... ----> I don't know why WD11 says it don't exist
hItemInteger2,...
hItemInteger4,...
hItemInteger8,...
hItemUnsignedInteger1,...
hItemUnsignedInteger2,...
hItemUnsignedInteger4,...
hItemUnsignedInteger8,...
hItemHour,...
hItemCurrency,...
hItemReal4,...
hItemReal8,...
hItemRealTurbo:
sResultado = sresultado + convert(sValueItem)
CASE hItemText:
sResultado = sResultado + Complete(sValueItem,extractstring(sitem,4),Charact(0))

END
END
sItem = ExtractString(sItemsList,nextRank,CR)
END
sValueItem = ExtractString(sValuesList,nextRank,"/")
i++
END

RESULT sResultado


In the end, only search for the first field on the key index with this function ¿?

Do you see something wrong?

TIA

Pedro Santana Viera

thank you Peter for your answer,

Quote
Peter H.
I'm having also composite keys which form a unique identifier of a record in some cases. However, in order to be compliant to relational algebra, those database tables (as every other) also contain a primary unique auto increment identifier (or GUID for cross system uniqueness if required) which is used as foreign key in the other database tables to relate to the table with the composite key.

my problem with this kind of search is when the ID (I never do auto ID, but managed ID) has not meaning for the user, and the composite key is the main data to search for (Example: Order number on a Customer with several shops where they are not connected online. We use a counter, say A, B, C, etc... and a correlative number for every counter. Shop 1 orders: A/1, A/2, A/3, etc... . Shop 2 orders: B/1, B/2, B/3, etc... and the like.)If I need to search an order y need to do it with the composite key.


best regards,
Pedro Santana Viera

thank you for your help Fabrice, I like that idea and make a generic routine with it.

I understand now what Al says about the strangeness of the composite key structure.

Best regards
Pedro Santana Viera

Hi Pedro,

Then still... Why not just use a unique ID as primary unique key and as a foreign key in related tables and execute a (join) query on the fields in the primary table that need to be filtered or queried on. I don't see the issue here...:confused:

Just my 2 cents,

Peter H.