Every database supports mechanism to define primary keys and numeric sequence generator. so being and Data Access Layer NHibernate also provide mechanism to handle primary keys and numeric sequence generator.
Before moving to NHibernate numeric sequence generator element, here is a brief description of Primary key and numeric sequence generator(Identity).
Note: Query uses TSQL (Microsoft SQL Server) as its scripting language.
What is Primary Key
A primary key is a table column that serves a special purpose. Each database table needs a primary key because it ensures row-level accessibility. If you choose an appropriate primary key, you can specify a primary key value, which lets you query each table row individually and modify each row without altering other rows in the same table. The values that compose a primary key column are unique; no two values are the same. Each table has one and only one primary key, which can consist of one or many columns.
What is Identity Column
"An identity column creates a numeric sequence for you". You can specify a column as an identity in the CREATE TABLE statement:
CREATE TABLE dbo.Tasks ( TaskId int identity(1,1), TaskDesc varchar(200) )
The identity clause specifies that the column TaskId is going to be an identity column. The first record added will automatically be assigned a value of 1 (the
seed) and each subsequent record will be assigned a value 1 higher (the
increment) than the previous inserted row.
This is how Microsoft SQL Sever provides the mechanism to generate numeric ids in a sequence.
Now coming towards NHibernate which not only provide configuration mechanism to use existing sqlserver functionality but also provide its own generator mechanism.
Primary KeyMapped classes must declare the primary key column of the database table. Most classes will also have a property holding the unique identifier of an instance. The <id> element defines the mapping from that property to the primary key column.
<id
name="PropertyName" (1)
type="typename" (2)
column="column_name" (3)
unsaved-value="anynonenullid_value" (4)
access="fieldpropertynosetterClassName(5)">
<generator class="generatorClass"/>
</id>
| (1) | name (optional): The name of the identifier property. |
| (2) | type (optional): A name that indicates the NHibernate type. |
| (3) | column (optional - defaults to the property name): The name of the primary key column. |
| (4) | unsaved-value (optional - defaults to a "sensible" value): An identifier property value that indicates that an instance is newly instantiated (unsaved), distinguishing it from transient instances that were saved or loaded in a previous session. |
| (5) | access (optional - defaults to property): The strategy NHibernate should use for accessing the property value. |
If the name attribute is missing, it is assumed that the class has no identifier property.
The unsaved-value attribute is almost never needed in NHibernate 1.0.
There is an alternative <composite-id> declaration to allow access to legacy data with composite keys. We strongly discourage its use for anything else.
The required <generator> child element names a .NET class used to generate unique identifiers for instances of the persistent class. If any parameters are required to configure or initialize the generator instance, they are passed using the <param> element.
<id name="Id" type="Int64" column="uid" unsaved-value="0">
<generator class="NHibernate.Id.TableHiLoGenerator">
<param name="table">uid_table</param>
<param name="column">next_hi_value_column</param>
</generator>
</id>
All generators implement the interface NHibernate.Id.IIdentifierGenerator. This is a very simple interface; some applications may choose to provide their own specialized implementations. However, NHibernate provides a range of built-in implementations. There are shortcut names for the built-in generators:
- increment
-
generates identifiers of type Int64, Int16 or Int32 that are unique only when no other process is inserting data into the same table. Do not use in a cluster.
- identity
-
supports identity columns in DB2, MySQL, MS SQL Server and Sybase. The identifier returned by the database is converted to the property type using Convert.ChangeType. Any integral property type is thus supported.
- sequence
-
uses a sequence in DB2, PostgreSQL, Oracle or a generator in Firebird. The identifier returned by the database is converted to the property type using Convert.ChangeType. Any integral property type is thus supported.
- hilo
-
uses a hi/lo algorithm to efficiently generate identifiers of type Int16, Int32 or Int64, given a table and column (by default hibernate_unique_key and next_hi respectively) as a source of hi values. The hi/lo algorithm generates identifiers that are unique only for a particular database. Do not use this generator with a user-supplied connection.
- seqhilo
-
uses a hi/lo algorithm to efficiently generate identifiers of type Int16, Int32 or Int64, given a named database sequence.
- uuid.hex
-
uses System.Guid and its ToString(string format) method to generate identifiers of type string. The length of the string returned depends on the configured format.
- uuid.string
-
uses a new System.Guid to create a byte[] that is converted to a string.
- guid
-
uses a new System.Guid as the identifier.
- guid.comb
-
- native
-
picks identity, sequence or hilo depending upon the capabilities of the underlying database.
- assigned
-
lets the application to assign an identifier to the object before Save() is called.
- foreign
-
uses the identifier of another associated object. Usually used in conjunction with a <one-to-one> primary key association.
The hilo and seqhilo generators provide two alternate implementations of the hi/lo algorithm, a favorite approach to identifier generation. The first implementation requires a "special" database table to hold the next available "hi" value. The second uses an Oracle-style sequence (where supported).
<id name="Id" type="Int64" column="cat_id">
<generator class="hilo">
<param name="table">hi_value</param>
<param name="column">next_value</param>
<param name="max_lo">100</param>
</generator>
</id>
<id name="Id" type="Int64" column="cat_id">
<generator class="seqhilo">
<param name="sequence">hi_value</param>
<param name="max_lo">100</param>
</generator>
</id>
Unfortunately, you can't use hilo when supplying your own IDbConnection to NHibernate. NHibernate must be able to fetch the "hi" value in a new transaction.
<id name="Id" type="String" column="cat_id">
<generator class="uuid.hex">
<param name="format">format_value</param>
<param name="seperator">seperator_value</param>
</generator>
</id>
The UUID is generated by calling Guid.NewGuid().ToString(format). The valid values for format are described in the MSDN documentation. The default seperator is - and should rarely be modified. The format determines if the configured seperator can replace the default seperator used by the format.
1.3. UUID String Algorithm
The UUID is generated by calling Guid.NewGuid().ToByteArray() and then converting the byte[] into a char[]. The char[] is returned as a String consisting of 16 characters.
The guid identifier is generated by calling Guid.NewGuid(). To address some of the performance concerns with using Guids as primary keys, foreign keys, and as part of indexes with MS SQL the guid.comb can be used. The benefit of using the guid.comb with other databases that support GUIDs has not been measured.
1.4. Identity columns and Sequences
For databases which support identity columns (DB2, MySQL, Sybase, MS SQL), you may use identity key generation. For databases that support sequences (DB2, Oracle, PostgreSQL, Interbase, McKoi, SAP DB) you may use sequence style key generation. Both these strategies require two SQL queries to insert a new object.
<id name="Id" type="Int64" column="uid">
<generator class="sequence">
<param name="sequence">uid_sequence</param>
</generator>
</id>
<id name="Id" type="Int64" column="uid" unsaved-value="0">
<generator class="identity"/>
</id>
For cross-platform development, the native strategy will choose from the identity, sequence and hilo strategies, dependent upon the capabilities of the underlying database.
1.5. Assigned Identifiers
If you want the application to assign identifiers (as opposed to having NHibernate generate them), you may use the assigned generator. This special generator will use the identifier value already assigned to the object's identifier property. Be very careful when using this feature to assign keys with business meaning (almost always a terrible design decision).
Due to its inherent nature, entities that use this generator cannot be saved via the ISession's SaveOrUpdate() method. Instead you have to explicitly specify to NHibernate if the object should be saved or updated by calling either the Save() or Update() method of the ISession.
Posts
