NHibernate: how to give a name to your Primary Key using SchemaExport (in SQL Server/ SQL Express)
Posted by Guardian in NHibernate on Friday 20 March 2009 at 3:09 PM
If in your project, you let NHibernate generate the database from the information you provide with the schema mappings (a thing extremely useful, especially for testing cause you can build up the database in engines like SQLite)...
mmm...where did I heard something similar ?!?!? ah yes...in my previous post: NHibernate: how to control Delete Rule and Update Rule in a foreign key when using SchemaExport
This time I want to focus on giving a proper name to your Primary Key in the table (which is usually auto-generated in NHibernate 1.2, have to check in 2.0+), this can be useful when you need to generate a database dynamically and then you need to build up update or synchronize it with successive versions using SQL scripts.
NHibernate doesn’t offer (yet) a facility to give a name to your primary key (nothing that I found however, I admit I’m not an NHibernate guru, but an average user). You can use an approach similar to that exposed in my previous post.
In this example I’m using SQL Server/SQL Express as my database engine and the queries are built with that in mind.
1: <?xml version="1.0" encoding="utf-8" ?>
2: <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="false">
3: <class name="SID.Sphera.Controls.Extended.ImageRegion.Entities.ImageSheetData, SID.Sphera.Controls.Extended"
4: table="ImageRegionImageSheetData" lazy="false">
5: <id name="_Id" access="field" column="IRISD_Id" type="guid">
6: <generator class="guid" />
7: </id>
8: <property name="_Name" access="field" column="IRISD_Name" type="string" not-null="true" />
9: <property name="_ResourceId" access="field" column="IRISD_ResourceId" type="guid" not-null="true" />
10: <property name="_Width" access="field" column="IRISD_Width" not-null="true" type="int" />
11: <property name="_Height" access="field" column="IRISD_Height" not-null="true" type="int" />
12: <property name="_BackgroundImageId" access="field" column="IRISD_BackgroundImageId" type="guid"
13: not-null="false" />
14: <bag name="_sensitiveRegions" access="field" cascade="all-delete-orphan" lazy="false">
15: <key column="IRIRD_ParentImageSheetId" foreign-key="FK_IRIRD_IRISD" />
16: <one-to-many class="SID.Sphera.Controls.Extended.ImageRegion.Entities.ImageRegionData, SID.Sphera.Controls.Extended" />
17: </bag>
18: </class>
19: <!-- Primary Key Rename -->
20: <database-object>
21: <create>
22: DECLARE @pkName Varchar(255)
23: ;
24: SET @pkName= (
25: SELECT [name] FROM sysobjects
26: WHERE [xtype] = 'PK'
27: AND [parent_obj] = OBJECT_ID(N'[dbo].[ImageRegionImageSheetData]')
28: )
29: ;
30: Exec sp_rename @pkName, 'PK_ImageRegionImageSheetData', 'OBJECT'
31: </create>
32: <drop></drop>
33: </database-object>
34: </hibernate-mapping>
Look at line 24: with this query you get the actual name of the primary key which was generated by NHibernate, this is specific to SQL server/SQL express and is you use a different database engine you have to adapt those queries (I know you loose the decoupling to the database engine offered by NHibernate, but you can setup some strategies to load different mappings according to you current dialect).
In line 30 we use a system stored procedure that allow us to rename the object we got before.
If you know a different and more elegant way to reach the goal, please leave me a comment here.
-
-
License
Except were otherwise noted the articles, libraries, code snippets and any work presented in this blog by Giorgetti Alessandro is licensed under a Creative Commons Attribuzione 3.0 Unported License.
#1 da Guardian - Saturday March 2010 alle 01:15
It seems to me that the database structure is poorly designed...if I got it well the primary key of Table B is formed by values got from the table A right? the relation should be the inverse...RefernceId in table A should map to PK in table B. Which table get populated first? if it's the table A the first thing that comes into my mind is you have to map the TableB entity using an Assigned id generator and save the objects in a explicit way.
#2 da gwez - Saturday March 2010 alle 01:15
Hi, Thanks for your post, interesting. Tried to reuse the concept of; It gives me an error saying 'foreign-key' attribute not declared. The issue I am trying to solve is; I have two tables that forms a relation ship but not on the pk; TableA Id {PK} ReferenceId Text1 Text2 TableB Id{PK} maps to referenceId Text1 Text2 … … … I can not change the database structure due to legacy reasons. Any suggestions? Gwez
#3 da Guardian - Saturday March 2010 alle 01:15
Very good hint, thank you a lot.
#4 da Paolo - Saturday March 2010 alle 01:15
Hi, I've changed a little bit your code, to save me the work of changeing all the PKs name, one by one. In this way I need only to execute this script once, at the end of the GenerateSchema process. Hope can help. Bye. This is the code: DECLARE @PKname nvarchar(255), @TName nvarchar(255), @TName2 nvarchar(258) DECLARE PKCursor CURSOR FOR SELECT PK.name, T.name AS Tname FROM sys.sysobjects AS PK INNER JOIN sys.sysobjects AS T ON PK.parent_obj = T.id WHERE (PK.xtype = 'PK') OPEN PKCursor FETCH NEXT FROM PKCursor INTO @PKname, @TName WHILE @@FETCH_STATUS = 0 BEGIN SET @TName2 = 'PK_' + @TName PRINT 'table ' + @TName + ' : renaming ' + @PKname + ' in ' + @TName2 Exec sp_rename @PKname, @TName2, 'OBJECT' FETCH NEXT FROM PKCursor INTO @PKname, @TName END CLOSE PKCursor DEALLOCATE PKCursor