Primary Key Control with Transfer (Transfer Tutorial Part 2)
Previously we looked at doing basic CRUD operations in Transfer, and I was going to look at some of the composition options that are available, but I decided we would look at primary keys instead first.Primary keys are an integral part of almost any database driven application, and consequently are an integral part of Transfer as well.
When a Business Object is inserted into the database, a primary key value will need to be supplied, either by the database generating the unique value, or by code generating the unique value.
A Business Object will need to know the value of its primary key, so that it knows what record it is representing.
Transfer will handle this process for you, and how it does that is configured through the <id> element that is found within the Transfer configuration file .
There are three different ways that Transfer does this:
Retrieves Database Generated Primary Keys
By default, Transfer will attempt to retrieve numeric keys that are generated by the database. Understandably, this happens differently for each supported database -
- Microsoft SQL Server: Generates Identity value
Example:<id name="IDPost" type="numeric" />
This will retrieve the last inserted Identity key that was generated by SQL Server. - MySQL: Generates 'auto increment' values
Example:<id name="IDPost" type="numeric" />
This will retrieve the last inserted auto increment value that was generated by MySQL - PostGreSQL: Generates primary key values through sequences
Example<id name="IDPost" type="numeric" sequence="post_seq" />
This will retrieve the last sequence value that was by incremented by PostGres.
Transfer can Generate Primary Keys
Transfer can also generate primary key values for you. There are three different types of primary keys that it can generate -
- Numeric
Example:<id name="IDPost" type="numeric" generate="true" />
This will generate a unique numeric value for the table - UUID
Example:<id name="IDPost" type="UUID" generate="true" />
This will generate a UUID value for the primary key - GUID
Example:<id name="IDPost" type="GUID" generate="true" />
This will generate a GUID value for the primary key
Manually Override Transfer
Last, but not least, you are able to manually override Transfer's primary key controls to insert a record with a primary key value of your choosing.
A Business Object generated by Transfer will have a set() method for its primary key. If this value is set before the Object is inserted into the database, Transfer will attempt to insert it using this value.
For example,
<cfscript>
post = transfer.new("post.Post");
post.setIDPost("george");
transfer.save(post);
</cfscript>
This will attempt to insert the Post with the primary key value of "george".
With these three options, you should have all the control you need to manage primary keys within the database.
More details on Transfer can be found here .
Comments
Posted by barry.b on 13 July 2006 10:18 PM
Mark, what happens if you're dealing with a database that has no official primary keys - every last table has multi-field unique indexes only?
my last job was like that, not a primary key to be seen. they had autonumbers tho, but only generating short sequences of numbers to give uniqueness to the other three, four or five columns....
Posted by Mark on 14 July 2006 09:42 AM
That is a good question barry, and without looking at the DB specifically I would probably look at the following options -
1) You have to designate a primary key <id> for an object - whether or not it is an actual designated 'primary key' in the db is actually irrelevent, as long as it has a unique value, it's okay.
2) You are able to use the readByProperty() and readByWhere() funtions to retrieve objects via other unqiue value fields on a table.
see: http://www.compoundtheory.com/transfer/documentation/database.html
3) Depending on which value in the DB is setup as the <id> for the table, you could then use any of the above techniques to ensure that it is populate correctly on inserts.
Does that answer your question?
Posted by barry.b on 14 July 2006 10:00 AM
"You have to designate a primary key <id> for an object "
but you mean only one single field, yes? what about concatenated keys - uniqueness across two or more fields. that's a no go?
tableA
---------
PKeyA
FieldA
tableB
--------
PKeyB
FieldB
TableJoin
------------
PKeyA
PKeyB
FieldC
why not do "this" you ask?...
TableJoin2
------------
PKeyJoin2
FKeyA
FKeyB
FieldC
because you have to add extra logic so there can only be one combination of FKeyA + FKeyB
Posted by Mark on 14 July 2006 10:12 AM
Barry,
Yes, if you want to administer TableJoin using Transfer, you are not going to be able to.
Transfer does assume that there is a unique ID for a given table.
It does not require an ID for manytomany composition, as that is handled in the composition configuration.
In the above situation, I would have designed TableJoin with an ID, simply to make life easy to change the value of FieldC when required.
If FieldC did not exist in on the table, then TableJoin would fit into the manytomany composition model as described above.
I'm totally up for listening to how you could possibly configure Transfer to handle this scenario - but remember it has to work in a composition model as well.
Posted by barry.b on 14 July 2006 11:18 AM
Sorry Mark....I refreshed the page and ir re-posted my previous comment.
you've got valid points there, I'll conceed. I suppose Transfer will cure 95% of all (db persisting) ills...
Posted by Mark on 14 July 2006 01:36 PM
Barry,
I'll delete your double post, no worries.
You are totally correct in your statement - Transfer will not be the be all and end all of ALL DB persistance models.
I don't think that there is any way that anything generic CAN be.
I would suggest looking into the manytomany composition model for Transfer, (example provided in tBlog example) - it may provide the functionality you need.