Inspiring

Question

ORM: Both UUID and Identity keys at the same time... no luck

Forum|Forum|15 years ago
February 25, 2010
4 replies
3503 views

I want a database table to have both UUID and numeric incremental keys.

In the persistent component sites.cfc, I can have this:

property name="siteNumId" fieldtype="id" generator="native" type="numeric" ormtype="integer";

...which works great. Also, I can have this instead:

property name="userId" fieldtype="id" generator="assigned" ormtype="string" length="36";

...which works great too (I'm setting the uuids myself).

If I try to use them both at the same time, the ORM builds the database fine, but when trying to insert new objects to the table, the incremental column tries to insert null value, which naturally raises an exception.

So, I can have either auto- or manually generated UUIDs, - or - Incremental numeric IDs. But not both.

So, if anyone knows a neat trick how to go around this issue, that'd be nice...

My database is MSSQL, and I'm very new to the ORM/Hibernate thingie. Any tips appreciated!

Database access

This topic has been closed for replies.

T

TLC-IT

Inspiring

I use both UUID and sequential-integer columns under MSSQL all the time.

When you define the integer field, specify "Identify Specification = Yes."
When you define the UUID field, specify a default value of "(newid())".

The only potential "gotcha" with an ORM is that it needs to be smart enough to know how to retrieve the new record's key. Well, an SQL "Insert" query can reliably return an auto-generated integer primary key, but if you want to know the assigned UUID you're probably going to have to re-query to get it.

Such is the plus-and-minus of dealing with your typical ORM. All of them have some incarnation of this type of issue.

FernisAuthor

Inspiring

Thanks Adam and TLC,

I am also used to both incremental id and uuid in mssql, that's why I was keen into finding how it works with ORM.

I've decided to ditch ORM in my larger CMS project, main reasons being:

* The fact that I cannot store ORM username and password in ColdFusion variables and use them from there, but they have to be stored in the ColdFusion DSN on the server. That makes hosted solutions on shared coldfusion serves a real security nightmare.

* Limitations with cfscript (I cannot have dynamic evaluated getters and setters in code), which means Lot of additional code.

* ColdFusion's awesome caching possibilities, also the new page fragment caching rocks.

* Performance gains (in some cases)

* It's time for me to improve my SQL skills anyway.

* Queries can be cfscripted, which is awesome, even though nobody cares what lies beneath some closed cfclib/ folder

* I usually init my functions by having at least the UUID value, so I always know what's going int the database. Finding out the ID (int) is easy then.

Downsides include:

* I hate to deal separately with MySQL when it comes to SQL table altering (luckily only in CMS setup and version updates)

* Performance losses (in some cases, where I'm lazy, and haven't thought of things through)

--

-Fernis - fernis.net - ColdFusion Developer For Hire

A

Adam Cameron.

Inspiring

I was able to get the ID & UUID kinda working, but had to use some DB-specific code, and also the columns would not be created as a composite PK, even if they kinda behaved like one.

component persistent=true {

    property name="id" fieldType="id" ormtype="long" column="tst_id" generator="native";
    property name="guid" fieldType="column" column="tst_guid" dbdefault="newid()" generated="insert";

    property name="value" fieldType="column" ormtype="string" column="tst_value";

}

I'm still trying to decipher the docs sufficiently to get both those columns created as a composite key...

--

Adam

A

Anonymous

Is what's shown below, your end goal? Just want to be clear about your final result.

MS-SQL 2K8

CREATE TABLE dbo.Table_1

(

id int NOT NULL IDENTITY (1, 1),

uid uniqueidentifier NOT NULL,

value nvarchar(MAX) NOT NULL

) ON [PRIMARY]

TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE dbo.Table_1 ADD CONSTRAINT

DF_Table_1_uid DEFAULT newid() FOR uid

GO

ALTER TABLE dbo.Table_1 ADD CONSTRAINT

PK_Table_1 PRIMARY KEY CLUSTERED

(

id,

uid

) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

ALTER TABLE dbo.Table_1 SET (LOCK_ESCALATION = TABLE)

GO

FernisAuthor

Inspiring

Grandnaqel: Yes, that looks exactly what I want. I want auto-generated incremental numeric id and uuid to be the primary key together (and indexed).

Another option is just to have the numeric id not as primary key, but indexed still. Haven't looked for the docs yet for adding indexes at all... probably not possible with ColdFusion abstracted ORM functions anyway, I'd guess.

Adam: Thanks if you're still looking for the solution. And yes, I'm going for the hybrid parentid-thingie as well.

FernisAuthor

Inspiring

For the nested set tree SQL problem, I came up with a workaround.

There are easy queries on the web for how to get the full tree (all nodes) with their depth.

There are also examples of how to get all child folders.

The problem is, that a query which returns only a portion of the tree *with* the item depths (relative to the start node) is quite complex, and include a subquery in a FROM clause, which HQL does not support.

Performance-wise, I have no idea how much I lose - probably a lot - but it was pretty simple doing nested selects, i.e.

SELECT all items with depths WHERE node.lft IN (SELECT subnodes from node x)

The downside is that the query still returns the absolute depth of all child nodes, and not relative to the starting node, but I can live with that, and have my folder functions deal with it - querying first the depth of the starting node, and then passing it as a variable to themain query, modifying the results.

I wish I was a guru with SQL JOINs etc., but unfortunately I'm not.

A

Adam Cameron.

Inspiring

I use a hybrid of the standard nested set model. I use left/right for hierarchical operations, but also store a parentId value, which I use solely for getting a node's parent, or a parent node's immediate children. (or, indeed, a node's siblings). This is more simple and a lot more performant than running all those subqueries one needs to, with a vanilla nested set.

--

Adam

A

Anonymous

Use an after trigger to populate that second value on Insert. If your using MSSQL, use a GO in your SQL then continuing in the same cfquery, issue a select on that value into an aliased field, as well as the scope_identity value and you'll have access to both of those number, and finally retrieve it from the query result using a cfset to a local Variables scope var so you can use it later, Mine is a fraction simpler than you will need but heres an example.

Insert into LogMaster ( Insert_Date ) values ( getDate() )

SELECT scope_identity() as NewLogID

</cfquery>

A

Anonymous

Why not just build it right into the table?

Auto Identity for the ID column
For the UUID column just use the function NEWID()

A

Adam Cameron.

Inspiring

One of the chief ideas of using an ORM system is that you don't need to busy yourself with DB schema considerations like this: the ORM allows for the solution to be written into the model, and Hibernate (in this case) will handle the DB side of things, irrespective of the underlying DB platform.

I think the question was how to do it with Hibernate, not simply how to do it.

I was going to look at this this evening and get an answer back to you (the OP), but I'm out of time, unfortunately. If no-one's come back to you by Saturday (I'm going to the pub tomorrow night!), I'll suss it out then.

--

Adam

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded