thr3ads.net - Rails - Migrations: Manually create primary key (MySQL) [May 2008]

If this information is useful, please help other people find it:
Share via:

demonGeek

2008-May-27 23:37 UTC

Migrations: Manually create primary key (MySQL)

I need to use a UUID as a primary key.

I have everything working right now with the exception of being able
to create the column in MySQL (v4) as a primary key.

The migration looks like this:

 create_table :groups, :id => false do |t|
      t.string :id, :limit => 36, :null => false
      t.string :name, :limit => 100, :null => false
      t.string :description, :limit => 500
      t.timestamps
 end

All I want to do is specify that my replcement ''id'' column is
the
primary key and have the generated SQL create that column as the
primary key in the MySQL table.

Any help would be appreciated.

Thanks.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Bill Walton

2008-May-28 00:17 UTC

head link

Re: Migrations: Manually create primary key (MySQL)

Hi,
demonGeek wrote:
> I need to use a UUID as a primary key.
Why?  It sounds to me like you''re coming to the party with a solution
in
mind rather than asking "how should I do this with Rails."  Using a
UUID as
a Primary Key implies you want it to have meaning.  It shouldn''t, in
Rails.
Don''t optimize your database structure for performance yet.  Optimize
your
code for maintainability.  Just my $0.02.

Best regards,
Bill


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

demonGeek

2008-May-28 00:38 UTC

head link

Re: Migrations: Manually create primary key (MySQL)

Because I want to.

Like I said, I already know how to use a UUID with rails - so what is
it you think I should be asking?

The question was: how do I get a migration to create a primary key
column in a database - the only thing that has to do with Rails is
that the migration is written in Rails.

Creating a primary key on a database column is about optimizing the
database, I don''t see what that has to do with optimizing Rails code.
I''m not trying to imply any meaning into that other than the obvious
one.

On May 27, 6:17 pm, "Bill Walton"
<bill.wal...-xwVYE8SWAR3R7s880joybQ@public.gmane.org>
wrote:> Hi,
>
> demonGeek wrote:
> > I need to use a UUID as a primary key.
>
> Why?  It sounds to me like you''re coming to the party with a
solution in
> mind rather than asking "how should I do this with Rails."  Using
a UUID as
> a Primary Key implies you want it to have meaning.  It shouldn''t,
in Rails.
> Don''t optimize your database structure for performance yet.
 Optimize your
> code for maintainability.  Just my $0.02.
>
> Best regards,
> Bill--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Bill Walton

2008-May-28 01:03 UTC

head link

Re: Migrations: Manually create primary key (MySQL)

From
http://www.google.com/search?q=rails+create+a+primary+key+in+a+migratiion&ie
=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US:official&client=firefox-a

http://lists.rubyonrails.org/pipermail/rails/2006-April/036134.html


HTH,
Bill

----- Original Message -----
From: "demonGeek"
<aptompkins-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
To: "Ruby on Rails: Talk"
<rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org>
Sent: Tuesday, May 27, 2008 8:38 PM
Subject: [Rails] Re: Migrations: Manually create primary key (MySQL)



Because I want to.

Like I said, I already know how to use a UUID with rails - so what is
it you think I should be asking?

The question was: how do I get a migration to create a primary key
column in a database - the only thing that has to do with Rails is
that the migration is written in Rails.

Creating a primary key on a database column is about optimizing the
database, I don''t see what that has to do with optimizing Rails code.
I''m not trying to imply any meaning into that other than the obvious
one.



On May 27, 6:17 pm, "Bill Walton"
<bill.wal...-xwVYE8SWAR3R7s880joybQ@public.gmane.org>
wrote:> Hi,
>
> demonGeek wrote:
> > I need to use a UUID as a primary key.
>
> Why? It sounds to me like you''re coming to the party with a
solution in
> mind rather than asking "how should I do this with Rails." Using
a UUID as
> a Primary Key implies you want it to have meaning. It shouldn''t,
in Rails.
> Don''t optimize your database structure for performance yet.
Optimize your
> code for maintainability. Just my $0.02.
>
> Best regards,
> Bill

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

demonGeek

2008-May-28 01:38 UTC

head link

Re: Migrations: Manually create primary key (MySQL)

Thanks Bill, I already saw that one while researching the subject
before posting.

Unfortunately that doesn''t do what I need - it just renames the
default integer ''id'' key.

Since I need to change the data type of the column, the default key is
disabled (:id => false).

I had expected to be able to do something like:

      t.string :id, :limit => 36, :null => false, :primary_key => true

to explicitly inidcate the primary key column, but that doesn''t seem
to be possible.



On May 27, 7:03 pm, "Bill Walton"
<bill.wal...-xwVYE8SWAR3R7s880joybQ@public.gmane.org>
wrote:>
Fromhttp://www.google.com/search?q=rails+create+a+primary+key+in+a+migrat...
>
=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US:official&client=firefox-a
>
> http://lists.rubyonrails.org/pipermail/rails/2006-April/036134.html
>
> HTH,
> Bill
>
>
>
> ----- Original Message -----
> From: "demonGeek"
<aptompk...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> To: "Ruby on Rails: Talk"
<rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org>
> Sent: Tuesday, May 27, 2008 8:38 PM
> Subject: [Rails] Re: Migrations: Manually create primary key (MySQL)
>
> Because I want to.
>
> Like I said, I already know how to use a UUID with rails - so what is
> it you think I should be asking?
>
> The question was: how do I get a migration to create a primary key
> column in a database - the only thing that has to do with Rails is
> that the migration is written in Rails.
>
> Creating a primary key on a database column is about optimizing the
> database, I don''t see what that has to do with optimizing Rails
code.
> I''m not trying to imply any meaning into that other than the
obvious
> one.
>
> On May 27, 6:17 pm, "Bill Walton"
<bill.wal...-xwVYE8SWAR3R7s880joybQ@public.gmane.org> wrote:
> > Hi,
>
> > demonGeek wrote:
> > > I need to use a UUID as a primary key.
>
> > Why? It sounds to me like you''re coming to the party with a
solution in
> > mind rather than asking "how should I do this with Rails."
Using a UUID as
> > a Primary Key implies you want it to have meaning. It
shouldn''t, in Rails.
> > Don''t optimize your database structure for performance yet.
Optimize your
> > code for maintainability. Just my $0.02.
>
> > Best regards,
> > Bill- Hide quoted text -
>
> - Show quoted text ---~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Shane Pinnell

2008-Jun-26 22:01 UTC

head link

Re: Migrations: Manually create primary key (MySQL)

demonGeek wrote:> Thanks Bill, I already saw that one while researching the subject
> before posting.
> 
> Unfortunately that doesn''t do what I need - it just renames the
> default integer ''id'' key.
> 
> Since I need to change the data type of the column, the default key is
> disabled (:id => false).
> 
> I had expected to be able to do something like:
> 
>       t.string :id, :limit => 36, :null => false, :primary_key =>
true
> 
> to explicitly inidcate the primary key column, but that doesn''t
seem
> to be possible.

AFAIK you cannot use a string as the "id" in a table when using Rails.
It is part of the convention over customization mantra, which takes some 
getting used to!
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Matthew Rudy Jacobs

2008-Jun-26 23:09 UTC

head link

Re: Migrations: Manually create primary key (MySQL)

demonGeek wrote:> I need to use a UUID as a primary key.
> 
> I have everything working right now with the exception of being able
> to create the column in MySQL (v4) as a primary key.
> 
> The migration looks like this:
> 
>  create_table :groups, :id => false do |t|
>       t.string :id, :limit => 36, :null => false
>       t.string :name, :limit => 100, :null => false
>       t.string :description, :limit => 500
>       t.timestamps
>  end
> 
> All I want to do is specify that my replcement ''id''
column is the
> primary key and have the generated SQL create that column as the
> primary key in the MySQL table.
> 
> Any help would be appreciated.
> 
> Thanks.
back to the question;

so rails migrations by default effectively does this,

create_table :giraffes, :id => false do
  t.column :id, :primary_key
end

http://github.com/rails/rails/tree/master/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb
line 96ish.

so, :primary_key is a column type, rather than an extra property.

This makes sense, for 99.99999% of purposes.

and, so we end up called "type_to_sql" with the type=:primary_key,
in mysql this renders "int(11) DEFAULT NULL auto_increment PRIMARY
KEY"
(line 176 of the mysql connection adapter)
ie. it''s not very easy to hack this...

so. my only reasonable suggestion;

create_table :giraffes, :id => false do
  t.string :id
  ...
end
execute("ALTER TABLE giraffes ADD PRIMARY KEY id")

or something, as this isn''t a normal case.
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Helen Poon

2008-Jul-10 19:06 UTC

head link

Re: Migrations: Manually create primary key (MySQL)

My problem is very similar.  I want to use id as primary key.  But the 
default data type of integer is too small,  I would like the id field to 
be able to hold a Java long (ie BIGINT in mysql,  sqlserver and DB2).

There is no way that I can supply :limit to the id field?  I tried to 
use change_column after the table is created with id being the primary 
key and that was unsuccessful (not allowed to change the column because 
it is the primary key field).

Any suggestions for me?

Since my migration step will be applied to various databases (mysql, 
sqlserver, db2, oracle), I don''t have the option to run sql using 
execute() because the sql can be different for these databases.

Any help will be greatly appreciated!
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Calvin Tang

2010-May-05 06:50 UTC

head link

Re: Migrations: Manually create primary key (MySQL)

Helen Poon wrote:> My problem is very similar.  I want to use id as primary key.  But the 
> default data type of integer is too small,  I would like the id field to 
> be able to hold a Java long (ie BIGINT in mysql,  sqlserver and DB2).
> 
> There is no way that I can supply :limit to the id field?  I tried to 
> use change_column after the table is created with id being the primary 
> key and that was unsuccessful (not allowed to change the column because 
> it is the primary key field).
> 
> Any suggestions for me?
> 
> Since my migration step will be applied to various databases (mysql, 
> sqlserver, db2, oracle), I don''t have the option to run sql using 
> execute() because the sql can be different for these databases.
> 
> Any help will be greatly appreciated!
Seems the belows works:

1. create the table without primary key
2. change_column <table_name>, <the_intended_primary_key_field>, 
<CHAR(10) PRIMARY KEY>
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Marnen Laibow-Koser

2010-May-05 12:10 UTC

head link

Re: Migrations: Manually create primary key (MySQL)

[Please quote when replying.  This is a mailing list, not just a Web 
forum.]

demonGeek wrote:> Because I want to.
>
That''s not a sufficient answer.  If you need a feature, be prepared to 
explain how it adds value for you.  If you can''t explain how something 
adds value, you probably don''t actually need it.
> Like I said, I already know how to use a UUID with rails - so what is
> it you think I should be asking?
You should be thinking about *why* you want to use a UUID, and whether 
it''s actually the appropriate solution in the context of Rails.
> 
> The question was: how do I get a migration to create a primary key
> column in a database - the only thing that has to do with Rails is
> that the migration is written in Rails.
> 
> Creating a primary key on a database column is about optimizing the
> database, I don''t see what that has to do with optimizing Rails
code.
Rails expects the database to be set up a certain way.  In return, it 
gives you a lot of nice stuff.
> I''m not trying to imply any meaning into that other than the
obvious
> one.
No one said you were.  But please understand the framework before trying 
to fight it. :)

I seem to recall that there''s a plugin or something to make Rails work 
with UUIDs, but I''ve never used it.

Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

JeffV

2010-May-08 17:24 UTC

head link

Re: Migrations: Manually create primary key (MySQL)

My company expects some tables to exceed 2^31-1 rows and so we are
thinking we need a 64 bit PK. Has anyone gotten this to work?

Thanks, Jeff

On May 5, 5:10 am, Marnen Laibow-Koser
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:> [Please quote when replying.  This is a mailing list, not just a Web
> forum.]
>
> demonGeek wrote:
> > Because I want to.
>
> That''s not a sufficient answer.  If you need a feature, be
prepared to
> explain how it adds value for you.  If you can''t explain how
something
> adds value, you probably don''t actually need it.
>
> > Like I said, I already know how to use a UUID with rails - so what is
> > it you think I should be asking?
>
> You should be thinking about *why* you want to use a UUID, and whether
> it''s actually the appropriate solution in the context of Rails.
>
>
>
> > The question was: how do I get a migration to create a primary key
> > column in a database - the only thing that has to do with Rails is
> > that the migration is written in Rails.
>
> > Creating a primary key on a database column is about optimizing the
> > database, I don''t see what that has to do with optimizing
Rails code.
>
> Rails expects the database to be set up a certain way.  In return, it
> gives you a lot of nice stuff.
>
> > I''m not trying to imply any meaning into that other than the
obvious
> > one.
>
> No one said you were.  But please understand the framework before trying
> to fight it. :)
>
> I seem to recall that there''s a plugin or something to make Rails
work
> with UUIDs, but I''ve never used it.
>
> Best,
> --
> Marnen Laibow-Koserhttp://www.marnen.org
> mar...-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
> --
> Posted viahttp://www.ruby-forum.com/.
>
> --
> You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
> For more options, visit this group
athttp://groups.google.com/group/rubyonrails-talk?hl=en.
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Andrew P.

2010-Dec-25 00:12 UTC

head link

Re: Migrations: Manually create primary key (MySQL)

JeffV wrote in post #910774:> My company expects some tables to exceed 2^31-1 rows and so we are
> thinking we need a 64 bit PK. Has anyone gotten this to work?
>
> Thanks, Jeff
I have created a preliminary version of a railtie that changes 
activerecord to use UUIDs, however only sqlite3 is tested and working. I 
plan to add mysql support and better docs in the next few days.

Please note that it is a work in progress, but may suit your needs.

You can find it at https://github.com/standardtoaster/rails-uuid

-Andrew

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Rails - May 2008 - Migrations: Manually create primary key (MySQL)

Migrations: Manually create primary key (MySQL)

Re: Migrations: Manually create primary key (MySQL)

Re: Migrations: Manually create primary key (MySQL)

Re: Migrations: Manually create primary key (MySQL)

Re: Migrations: Manually create primary key (MySQL)

Re: Migrations: Manually create primary key (MySQL)

Re: Migrations: Manually create primary key (MySQL)

Re: Migrations: Manually create primary key (MySQL)

Re: Migrations: Manually create primary key (MySQL)

Re: Migrations: Manually create primary key (MySQL)

Re: Migrations: Manually create primary key (MySQL)

Re: Migrations: Manually create primary key (MySQL)