(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? 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 -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To view this discussion on the web visit https://groups.google.com/d/msg/rubyonrails-core/-/GT6lasIkuWwJ. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
On Aug 7, 2012, at 12:26 AM, Chris Lloyd <christopher.lloyd@gmail.com> wrote: 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? I would like to see this. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
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-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
On Tue, Aug 7, 2012 at 5:53 AM, Ken Collins <ken@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 -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
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 On Aug 7, 2012, at 11:33 AM, Jeremy Evans wrote:> On Tue, Aug 7, 2012 at 5:53 AM, Ken Collins <ken@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-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
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/
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. On Tuesday, August 7, 2012 8:53:01 AM UTC-4, Ken Collins wrote:> > > 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 <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? > > 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 > > >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To view this discussion on the web visit https://groups.google.com/d/msg/rubyonrails-core/-/_oRJSJtuVHEJ. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
> 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 -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
On 8 August 2012 14:33, Ken Collins <ken@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. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
>>> 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 -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
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 -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To view this discussion on the web visit https://groups.google.com/d/msg/rubyonrails-core/-/YFvc3P4KCOEJ. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
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/3344006I''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? -- Aaron Patterson http://tenderlovemaking.com/
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. On Monday, August 13, 2012 3:40:57 PM UTC-6, Aaron Patterson wrote:> > 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? > > -- > Aaron Patterson > http://tenderlovemaking.com/ >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To view this discussion on the web visit https://groups.google.com/d/msg/rubyonrails-core/-/M5pQQvsEiq4J. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
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. > > On Monday, August 13, 2012 3:40:57 PM UTC-6, Aaron Patterson wrote: >> >> 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? >> >> -- >> Aaron Patterson >> http://tenderlovemaking.com/ >> >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To view this discussion on the web visit https://groups.google.com/d/msg/rubyonrails-core/-/ak-i7ldqNQIJ. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.