New Posts New Posts RSS Feed: IIdGenerator using combined primary-keys
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

IIdGenerator using combined primary-keys
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
tj62 View Drop Down
Groupie
Groupie
Avatar

Joined: 21-May-2009
Location: Iceland
Posts: 81 		Post Options Post Options   Quote tj62 Quote  Post ReplyReply Direct Link To This Post Topic: IIdGenerator using combined primary-keys
    Posted: 04-Jan-2011 at 4:31am
I recently started implementing the IIdGenerator interface as I need pritty advanced id-generation. There are some rules I'm forced to follow when creating this, as the database is shared with some old software systems written in C++ and more. Unfortunately I'm not allowed to change the database using GUID or autoincrements. Let me simplify things by explaining a general example. Let us say we have two tabels defined such:

tableA
  int idA  // primary key
  varchar(50) aName
 
tableB
  int idA // partial primary key and foreighn key to tableA
  int idB // partial primary key
  varchar(50) bName
 
As you se, the primary key of tableB is made up by two integers where the first one is a foreighn key to tableA.
The rules are such whenever I create an entry in tableB, idB has to increment from one (1). Example
 
TableA
   idA = 1
   TableB
       idA = 1
       idB = 1 (for the first record under TableA.idA=1)
       idB = 2 (for the second record under TableA.idA=1)
       idB = 3 (for the third record under TableA.idA=1)
 
TableA
  idA = 2
   TableB
       idA = 2
       idB = 1 (for the first record under TableA.idA=2)
       idB = 2 (for the second record under TableA.idA=2)
 
So I allways have to stard idB from 1 in TableB, for each new record in TableA

The problem is how can I in the server side call to

public UniqueIdMap GetRealIdMap(UniqueIdCollection tempIds, IDataSourceKey dataSourceKey)

find out for what idA it is called when requesting real Id for for TableB.idB? If I had that information it would be easy for my to request a valid idB from the database in GetRealIdMap. But without it I can't.

Second question is If I add entities to TableA as well as TableB and then perform save (both eintities with temporary Ids) is it guranteed that the TableA entity is saved first before calling GetRealIdMap for the TableB entity so that I know the real idA at that time...or is it all done in one call to GetRealIdMap?
Back to Top
tj62 View Drop Down
Groupie
Groupie
Avatar

Joined: 21-May-2009
Location: Iceland
Posts: 81 		Post Options Post Options   Quote tj62 Quote  Post ReplyReply Direct Link To This Post Posted: 05-Jan-2011 at 1:23am
Any idea before I start working on a horrible workaround?
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post Posted: 10-Jan-2011 at 4:19pm
Hi tj62,
 
Unfortunately, you won't be able to reference an entity (or its ID for that matter) in the IIdGenerator. You could still use it to generate your ids, but not if the ids to be generated are dependent on another row value as criteria.
 
What I can suggest is to implement this logic in an stored procedure. There you can access the FK (idA), check for the max value of the partial primary key (idB) for the rows matching idA, and then assign the value of idB properly.
 
Best regards,
   Silvio.
Back to Top
tj62 View Drop Down
Groupie
Groupie
Avatar

Joined: 21-May-2009
Location: Iceland
Posts: 81 		Post Options Post Options   Quote tj62 Quote  Post ReplyReply Direct Link To This Post Posted: 11-Jan-2011 at 1:08am
Hi Sbelini,
Yes that was actualy the work around I was going to take. Actualy I will do it in a trigger as follows (for those that might run into the same problem) I will let the trigger maintain the idA and idB for the old software (update and insert triggers). However I will change the primary/foreighn keys to serial (autoincrement) and maintain those from the silverlight system.
Such actually, I will have two key systems in the same table. Rather ugly but as the old application will disappear with the year(s) I can one day remove idA and idB. This also frees me from maintaing bad-keying system in the new Silverlight systems. It's quite a work re-keying all the foreighn tables (about 40 tables), but in the end I think it will pay off.
 
Regards
  TJ
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down