Re: [Rails-core] UUID primary keys for ActiveRecord

[Mike Moore]

On Aug 7, 2012, at 12:26 AM, Chris Lloyd <christop...@gmail.com> wrote:

Now that ActiveRecord supports uuid datatypes (#6713) I suspect that having UUID instead of auto-incrementing primary keys will become fairly popular. It's already possible to do this yourself but perhaps it should be built into ActiveRecord?

I would like to see this.

[Ken Collins]

I suspect that many people have been working successfully with uuid, guid, or any other db-specific unique identifier data type for years now. I know this has been the case in the SQL Server adapter. So finding these implementations for hints would be a great start. Here are some links to my work in the SQL Server adapter.

Wiki Article

http://git.io/Ttb6mg

Implementation & Test Details

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/lib/active_record/connection_adapters/sqlserver/database_statements.rb#L186-192

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/test/schema/sqlserver_specific_schema.rb#L80-89

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/test/cases/specific_schema_test_sqlserver.rb#L161

So there are a few things going on here. A summary:

1) SQL Server has a notion of unique identifiers where one is random and the other sequential. One is faster, the other more secure, etc. Which is better for a column primary key is debatable I think. Especially since sequential is not even sequential per table, but per system.

2) It is really easy to setup a schema where the database champions assigning a new unique identifier. However, getting that key back on create/insert is a bit tricky since most low level connection modes do not treat unique identifier columns as real primary keys. Hence if you let the db generate the value, an insert would have to be followed by another crafted select. The SQL Server supers up to ActiveRecord's #last_inserted_id and falls back to SCOPE_IDENTITY() which is fine for normal primary keys, but I do not know of a way to do the same (even assuming I reflected on the table having unique identifier or not) to get the random string that is a uuid column. A show stopper in itself.

3) The challenges of #2 are the reason I tell people in the wiki article that it is much easier if you assign the unique identifier in a simple call back. 

For these reasons, I am not seeing a way that ActiveRecord can champion a datatype like this at a low level. It seems to me to have always supported unique identifiers in simple patterns and that may be good enough. Thoughts?

 - Ken

On Aug 7, 2012, at 2:17 AM, Chris Lloyd wrote:

(Cross post from #7278)

Now that ActiveRecord supports uuid datatypes (#6713) I suspect that having UUID instead of auto-incrementing primary keys will become fairly popular. It's already possible to do this yourself but perhaps it should be built into ActiveRecord?

I'm happy to contribute a patch but was wanting to solicit feedback on wether this is wanted and if anybody had ideas on how the configuration should go.

Chris

[Jeremy Evans]

On Tue, Aug 7, 2012 at 5:53 AM, Ken Collins <k...@metaskills.net> wrote:
> 2) It is really easy to setup a schema where the database champions
> assigning a new unique identifier. However, getting that key back on
> create/insert is a bit tricky since most low level connection modes do not
> treat unique identifier columns as real primary keys. Hence if you let the
> db generate the value, an insert would have to be followed by another
> crafted select. The SQL Server supers up to ActiveRecord's #last_inserted_id
> and falls back to SCOPE_IDENTITY() which is fine for normal primary keys,
> but I do not know of a way to do the same (even assuming I reflected on the
> table having unique identifier or not) to get the random string that is a
> uuid column. A show stopper in itself.

ActiveRecord should be able to take the same approach that Sequel
uses, returning all of the inserted column values when inserting using
OUTPUT:

DB.create_table(:ts) do
String :id, :primary_key=>true, :default=>Sequel.function(:newid)
String :name
end
class T < Sequel::Model; end
T.create(:name=>'foo')
# SQL: INSERT INTO [TS] ([NAME]) OUTPUT [INSERTED].* VALUES (N'foo')
# => #<T @values={:id=>"41BE3F26-8DE4-46FA-9A92-582CA33835B2", :name=>"foo"}>

Jeremy

[Ken Collins]

Jeremy,

That is just beautiful! In your example, that is just a string column with a default, not a real [uniqueidentifier] data type. But I suspect that is moot since this should work with any datatype.

So the adapter could support something like that. If ActiveRecord did move forward with it, we might need something like @uuid ivar/accessor to the Column object... perhaps not tho. Maybe the SQL Server adapter could just use the OUTPUT method pointed to the primary key column no matter what and it should all work now and in the future. I may look into that one day.

- Ken

[Aaron Patterson]

On Mon, Aug 06, 2012 at 11:17:40PM -0700, Chris Lloyd wrote:
> (Cross post from #7278 <https://github.com/rails/rails/issues/7278>)
>
> Now that ActiveRecord supports uuid datatypes (#6713<https://github.com/rails/rails/issues/6713>)

> I suspect that having UUID instead of auto-incrementing primary keys will
> become fairly popular. It's already possible to do this yourself but
> perhaps it should be built into ActiveRecord?

Just a hunch, but I don't think much needs to be changed in AR. If
we're doing anything in AR to assume that the id is an integer, it's
probably a bug.

The main place where we'll need changes is in migrations and schema.rb
generation.

> I'm happy to contribute a patch but was wanting to solicit feedback on
> wether this is wanted and if anybody had ideas on how the configuration
> should go.

I think the main changes will be in the migrations. Probably need to
somewhat change the api of the create_table method calls. If you can
get something working (even partially) send a link here, or a PR and we
can work on it together.

--
Aaron Patterson
http://tenderlovemaking.com/

[Jay Feldblum]

The only reason to ask the database for a newly-inserted record's ID, for sequential numeric IDs, is because the database maintains the sequence and only the database can get the next number in the sequence.

But when using UUIDs, there is no reason to ask the database for a newly-inserted record's ID. Instead, the application can generate these UUIDs itself. This is, for example, what Hibernate and Mongoid do.

[Ken Collins]

> But when using UUIDs, there is no reason to ask the database for a newly-inserted record's ID.

There is if ActiveRecord uses it for a primary key.

> Instead, the application can generate these UUIDs itself. This is, for example, what Hibernate and Mongoid do.

This is what my wiki article describes and how technically ActiveRecord has supported this notion for many years. But if there is a more formal notion of supporting unique identifiers as primary keys, then we have two options to engineer a solution. Either pre assigned callbacks that allow the app to generate them, not desired as it would require some extra config to hint that this primary key is a unique identifier. The other option is to make it transparent and push the concern down to the adapters to return that value.

- Ken

[Michael Pavling]

On 8 August 2012 14:33, Ken Collins <k...@metaskills.net> wrote:
>
>> But when using UUIDs, there is no reason to ask the database for a newly-inserted record's ID.
>
> There is if ActiveRecord uses it for a primary key.

But ActiveRecord will have generated it, and given it to the DB to
insert - the DB won't have generated it; so there's no need to ask for
it back when AR already has it.

[Ken Collins]

But when using UUIDs, there is no reason to ask the database for a newly-inserted record's ID.

There is if ActiveRecord uses it for a primary key.

But ActiveRecord will have generated it, and given it to the DB to
insert - the DB won't have generated it; so there's no need to ask for
it back when AR already has it.

Yea, if you go that route in how ActiveRecord implements it. Like I said, that is how I tell people to do it in the wiki article or the SQL Server adapter.

 - Ken

[Chris Lloyd]

Aaron, as you mention it's pretty easy easy to do this already, but it's off the beaten path. On 3.2.6 I've included the postgres_ext gem to handle UUID types in Postgres correctly. Migrations/models are also easy: https://gist.github.com/3344006

So far everything works A-OK. It just seems as though it would be nicer with some syntax in the migration. Perhaps `create_table(:foos, :id => :uuid)`?

Chris

[Aaron Patterson]

On Mon, Aug 13, 2012 at 01:55:41PM -0700, Chris Lloyd wrote:
> Aaron, as you mention it's pretty easy easy to do this already, but it's
> off the beaten path. On 3.2.6 I've included the postgres_ext gem to handle
> UUID types in Postgres correctly. Migrations/models are also
> easy: https://gist.github.com/3344006

I'd rather we do creation like this:

CREATE TABLE x (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
y integer
);

Then a model with uuid primary keys should act the same as one using
auto incrementing keys. IIRC, there is code in AR that depends on the
database generating the record's ID. If the DB generated the uuid,
everything should Just Work(tm) (I think).

> So far everything works A-OK. It just seems as though it would be nicer
> with some syntax in the migration. Perhaps `create_table(:foos, :id =>
> :uuid)`?

Can we just modify the migration code to use the above form of
CREATE TABLE?

[Matthew Thorley]

Somebody wrote a patch for Postgres UUID support in Rails. It was accepted and is available in edge.

https://github.com/rails/rails/pull/6713

On Thursday, January 10, 2013 12:00:39 PM UTC-7, Matthew Thorley wrote:

Did anything ever become of this?

I'm solving this same problem now and wondering whether I'm duplicating some one else's effort. 

The approach I am taking is to writing a Rails extension that changes the default behavior for all migrations, in a way that all models get an id primary key that is type uuid and set by the database. 

I'm doing it in a Postgres specific way.