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

[Fernis]

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!

[Guest]

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.

<cfquery datasource="#DataSource#" name="LOGGINGMaster" >

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

SELECT scope_identity() as  NewLogID

</cfquery>

<CFSet PK = LOGGINGMaster.NewLogID >

[Guest]

Why not just build it right into the table?

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

[Adam Cameron.]

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

[Guest]

Ooops, my bad...I only skim read the post.

[Fernis]

Thanks,

That was exactly my point - how to do it without ORMExecuteQuery, just inserting new Entities...

I wonder what happens if one mixes CFQUERY queries in an application with ORMExecuteQuery.

What about data persistence? ORM doesn't know anything about CFQUERY queries...

Got another small problem with HQL - which isn't accepting parenthesis () after FROM keyboard.

http://stackoverflow.com/questions/316267/help-with-writing-a-sql-query-for-nested-sets shows an example which works in MySQL, and with small modifications (ditch the order by in the subquery, and have "node.lft,node.name" in all ORDER BYs and GROUP BYs) in MSSQL as well.

HQL throws an error if it finds parenthesis () between FROM and WHERE. Thus ORMExecuteQuery cannot perform "SELECT 'a' FROM mytable, (SELECT 'a' FROM b) as virtualtable", so I need to figure out an alternative SQL approach for this...  and I need to know if I can mix direct CFQUERY functions with ORM. How's the ColdFusion architecture supporting this? Should I lock code for ORM table inserts and verify that CFQUERY doesn't read it at the same time?

Any opinions would be highly appreciated...

-Fernis

[Adam Cameron.]

At the end of the day, Hibernate is just performing the same sort of DB operations as a <cfquery> tag is doing.  The only difference is there's a level of abstraction between the DB and the application code.

One consideration I guess is that operations performed on entities in the application code don't necessarily get flushed back to the DB immediately, but it'll all be transactional, so as long as any writes you were doing in the <cfquery> calls were likewise transactional (and they really really need to be with nested set operations), you should be fine.

Another thing you'd need to consider with ORM-based operations would be that you could not rely on left/right values being "fresh" (or, to the point: accurate) all the time, as other operations could have altered the values since the entites were fetched.  This is no different as with normal DB ops though, I guess.  One needs to work with just the ID, and not make any assumptions about the left and right values between when the data was fetched and when the data is updated.

--

Adam

[Fernis]

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.

[Adam Cameron.]

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

[Adam Cameron.]

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

[Guest]

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

[Fernis]

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.

[Adam Cameron.]

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

I am still trying to track down a solution for this, yes.

--

Adam

[Adam Cameron.]

I've found out (not via my own toil, just by asking someone who knows a lot more about this than me) that this is simply not possibly. Hibernate doesn't support it, so CF can't support it.

It seems like if you have the composite key then you need to provide the values for the properties yourself in your application code.

--

Adam

[TLC-IT]

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.

[Fernis]

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