Key Generators

Hello,

I have two questions about key generators:

1) why are they required? For example, SQL Server (and many other database engines) support auto-incrementing identity columns that are often used as primary keys. Why not just declare them as such instead of using this concept of a separate key generator table?

2) because key generator tables for a specific type are shared (I think the default should be to create separate tables following the 'TableName-Generator' naming pattern), if you want to create a separate generator you have to name it using the KeyGenerator attribute. Why does DO always append "_Generator" to the name specified instead of using the name as given?

99% of the time keys are just named "ID" and auto-increment from 1 (occasionally you have GUID columns needing NEWSEQUENTIALID()). I am wondering what kind of scenarios led to this concept of key generators in DO because they do seem to complicate matters.

Thanks, Eric.

key-generator

asked Feb 21 '14 at 02:12

ericmutta
13●4●4●6

2 Answers:

oldest newest most voted

Hello Eric,

having separate key generator tables (or sequences if they are supported by the database) greatly simplifies pipeline. For example it becames possible to batch insert created objects because key values could be obtained before entities are saved to the database. Another example is referencing newly created entity, if identity column would be used directly such references would require update after changes are saved.

Another important reason is the ability to have persistent interfaces across hierarchies which requires objects in different hierarchies to have non-overlapping key values. In other words such hierarchies should have the same key generator.

Special suffix is added to key generator tables to avoid naming conflicts with regular user tables. If you wanted to specifically use the same table for key generation, this will not work. Key generator tables have specific layout and handled specifically by DO: for example set of keys are fetched and cached in the Domain to minimize number of requests to the database.

answered Feb 21 '14 at 02:43

Denis Krjuchkov
1793●2●5

Thanks for the quick response Denis!

I just came from reading about the HiLo algorithm and the idea of "offline key generation" where the database server is not contacted to generate keys.

This is a really interesting technique and the benefit of batch inserts that can be derived from it is definitely a plus that I hadn't considered.

To give you some background to many of my questions, I make desktop-based accounting and project management software. My application runs on the same machine as SQL server so reducing the number of round trips to the database is not all that important and non-batched updates don't cost as much as they would over the network.

Now that I understand this, I am beginning to conclude that the key feature of DO (automatic database creation) also implies you should keep your hands off the database completely and interact with it only through DO sessions (e.g. this key generator design makes it really difficult to write SQL scripts that insert records directly into the database because DO is not available in that case to handle key generation).

Do you have a collection of use-cases where people out there are using DO in real-world applications? I'd like to know the kind of situations where code-first database applications are a good fit given the different mindset required. Or a set of best practices perhaps.

Many thanks, Eric.

answered Feb 21 '14 at 03:16

ericmutta
13●4●4●6

DO fully supports manual data insertion and modification, you just have to agree on how keys are generated. If you're using GUIDs any standard algorithm would work. For integer keys you could either replicate DO algorithm or (if executing from the same application) you could ask DO to generate keys for you. Finally it's possible to write your own key generator and ask DO to use it. Currently the only hard requirement is the abibility to generate keys without creating corresponding rows in the database.

(Feb 21 '14 at 07:38) Denis Krjuchkov

Your answer

Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!

toggle preview

community wiki

Subscription:

Once you sign in you will be able to subscribe for any updates here

Tags:

key-generator ×8

Asked: Feb 21 '14 at 02:12

Seen: 4,520 times

Last updated: Feb 21 '14 at 07:38

Subscription:

Related questions