Home » Blog

LightSpeed Identity Generation

Tagged as LightSpeed

How you identify your entities is often based on attributes of the business problem your solving with some personal preferences applied as well. For example, if you have a desire for ease of generation and don’t care about aesthetics you may choose GUIDs. Alternatively, if you want to use database generated integers, Sequence or Identity column may be the most appropriate.

Fortunately, LightSpeed supports several of the most popular identity generation methods. Our of the box we support:

• KeyTable (default)
• Sequence number
• GUID (Globally Unique Identifier)
• Identity column

KeyTable Identity Generation

KeyTable identity generation (Fowler PoEAA) uses a single table in your database that stores the current identity value. This allows LightSpeed to secure a block of identities (the default size being 10 and is configurable) and use them to set the identity value of newly created identities in your system. This works because every identity in the database, even in different tables, is unique.

KeyTable is a great identity method if you need high performance from you database as LightSpeed does not need to flush new entities to obtain identity values. That means less round trips to the database and therefore a more speedy application.

A small setup step is required to use KeyTable, you will need to run the KeyTable.sql schema file for your database to create the necessary table. This script can be found in your LightSpeed install directory.

This method uses an integer under the covers and therefore your entities should be implemented as using an integer:

public class MyModelClass : Entity<int>

Sequence Identity Generation

Sequence identities are similar to the KeyTable identity generation but are natively supplied by the database engine. This means using sequence identity generation is limited to databases that support it: Oracle and PostgreSQL.

As with the KeyTable identity generation method, Sequence provides a high performance mechanism for creating identities. And like the KeyTable identity generation, some setup is required. You need to run the Sequence.sql schema file for your database to create the necessary schema changes. This script can be found in your LightSpeed install directory.

This method uses an integer and therefore your entities should be implemented as using an integer:

public class MyModelClass : Entity<int>

Guid Identity Generation

Guid identities are great for systems that require strong uniqueness (think replication etc.) and are also extremely practical from a generation perspective.

However, while not an issue for many systems, GUIDs can have an impact on application aesthetics. If you need to place an identity as a query string parameter for instance you may not want a Guid displayed as they are reasonable ugly.

No surprises here, this method uses a Guid and therefore your entities should be implemented as using a Guid:

public class MyModelClass : Entity<Guid>

Identity Column Identity Generation

Identity Column identity generation supports auto-incrementing identity columns such as common in SQL Server. This approach, while supported, is not a recommended method for identity generation. Using this identity type means that LightSpeed cannot do optimized batching as well as it can with other identity methods. This is because LightSpeed needs to obtain the new identity value and update any in-memory associated objects before continuing the flush process.

This method also uses an integer and therefore your entities should be implemented as using an integer:

public class MyModelClass : Entity<int>

Configuring identity generation

Identity generation is set on the LightSpeed context, either in code or by using the LightSpeed configuration section in the .config file for your application.

LightSpeedContext.IdentityMethod = IdentityMethod.KeyTable;

or

LightSpeedContext.IdentityMethod = IdentityMethod.Guid;

or

LightSpeedContext.IdentityMethod = IdentityMethod.Sequence;

or

LightSpeedContext.IdentityMethod = IdentityMethod.IdentityColumn;

When using the KeyTable or Sequence types you can also fine tune the IdentityBlockSize. The default value is 10 and in most applications you will not need to change this default.

LightSpeedContext.IdentityBlockSize = 20;

For information about configuring these values using the config file for your application check out the online documentation for LightSpeed configuration.

If you have any questions or queries regarding identity generation with LightSpeed either post a comment or post into the LightSpeed forums and we will happily provide the answers you need.

Hope that helps,

John-Daniel Trask

Add your comments
Posted by John-Daniel Trask on 26 August 2007

11 Responses to “LightSpeed Identity Generation”

1. cool… keep these coming.

   Srdjan Aug 26th, 2007

2. [...] Previously LightSpeed only supported auto-incrementing identities for SQL Server however we’ve extended this functionality to PostgreSQL, MySQL and SQLite for developers working with those databases. We still suggest that developers should use other identity generation methods as highlighted in this earlier post about how LightSpeed handles identity generation. [...]

   Mindscape - MindBlog Nov 26th, 2007

3. How about compound keys?

   ProjectId //primary
   PersonId //primary

   In this case, the application will create the key by setting:

   personProject.projectId = ProjectID;
   personProject.personId = PersonID;

   and thus key generation should be turned off.

   But… it does not seem to be possible to turn if off.

   Could ‘off’ be added as a ‘feature’?

   -Bart

   Bart McLeod Jun 29th, 2009

4. We’ve been looking at composite key support for LightSpeed 3, and one of the issues is indeed how to handle key generation. At present this requires the entity to override GeneratedId() (which bypasses the IdentityMethod and therefore effectively turns off key generation), but we’re hoping to come up with an externalised strategy for this if time permits.

   Ivan Towlson Jun 29th, 2009

5. Good that your are planning for it!

   If I understand it right, our problems are solved if we override GeneratedId() and let it do nothing? But we will then have to override it again if we regenerate the model?

   Bart McLeod Jun 29th, 2009

6. You can’t “let it do nothing” — GeneratedId() must return a value (and it must be suitable for use as a unique key). Put another way, every entity must have an identity, whether that identity is generated by the database (e.g. IdentityColumn), by LightSpeed (e.g. Guid) or by the entity itself (GeneratedId override). But yes, if you override GeneratedId() then LightSpeed will not attempt to generate its own keys: it will use yours instead.

   Note that the identity is always represented by the Id property. This will still be the case in the composite key scenario. So in your example, setting .ProjectId and .PersonId is *not* setting the identity. What we’re envisaging is that a composite key is represented by a record type e.g. PersonProject might inherit from Entity , so its Id property is of type PersonProjectId, where PersonProjectId has the PersonId and ProjectId fields. The problem is therefore how to generate PersonProjectId instances to identity new entities, and at the moment we haven’t got a better answer than “override GeneratedId() to return a PersonProjectId” (instead of, say, an int or a GUID). But it’s early days yet!

   Ivan Towlson Jun 29th, 2009

7. Oh, and regarding overriding GeneratedId again when you regenerate the model, you would implement GeneratedId in a partial class, so it won’t be overwritten when you regenerate the model. Of course if the set of keys changes (effectively, the identity type changes) then you would need to update the implementation to reflect this.

   Ivan Towlson Jun 29th, 2009

8. I must say you guys are really fast at answering these questions.

   For others to know: we bought a LightSpeed license (professional version) and we are very satisfied in general).

   I will try your recommendations, because I like the idea of overriding the GenerateID() method instead of cripling the datamodel to accommodate for autoincrementing keys.

   If I encounter any problems I will let you know. Is there an example or a screencast of an override of this function?

   Bart McLeod Jun 30th, 2009

9. Thanks for the feedback Bart :-)

   There isn’t a screencast about identity generation however we are planning to release a bunch more casts around the LightSpeed 3.0 launch so I’ll make a note that you’re interested in one regarding entity generation and try to get one made.

   I hope that helps.

   John-Daniel Trask Jun 30th, 2009

10. I had no success with overriding GenerateId().

    So I will move this topic to the forum and see if we can work it out there.

    Bart McLeod Jul 1st, 2009

11. I ran into a different scenario today, where I would like one of my tables to have a Guid ID(because other apps also insert values in this table) but would like to keep the rest on KeyTable for efficiency.
    That did not work of course and I was reminded that the context requires one single Identity generation method.
    Would it be an idea to have a table override the context wide ID generation method (I can see that that would not make ID generation and object insertion easier, but hey, it would be a great challenge for you guys to get it working :-) ).

    Gerard van de Ven Feb 1st, 2010

Leave a Reply