Rails core - Jan 2006 - Is :primary on Column meant to indicate if the column is the primary key?

I''ve been working on the SQL Server adapter recently and have a
question
about the intended use of the :primary attribute on the Column class.  Is it
meant to hold a boolean indicating if the column is the primary key?  After
reviewing the various adapters it doesn''t appear that any of them use
the
primary attribute.  I''d like to make use of the attribute so that when
I
dump a SQL Server database using db_schema_dump I can get the :primary_key
option on the create_table set appropriately.  The database I''m dumping
is a
non-standard Rails db (no "id" column for the primary key).  Could
someone
confirm that my intended use of the primary attribute on Column is
appropriate?

Thanks,
Steve


_______________________________________________
Rails-core mailing list
Rails-core@lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails-core

Steve Eichert wrote:
> I''ve been working on the SQL Server adapter recently and have a
question
> about the intended use of the :primary attribute on the Column class.  Is
it
> meant to hold a boolean indicating if the column is the primary key?  After
> reviewing the various adapters it doesn''t appear that any of them
use the
> primary attribute.  I''d like to make use of the attribute so that
when I
> dump a SQL Server database using db_schema_dump I can get the :primary_key
> option on the create_table set appropriately.  The database I''m
dumping is a
> non-standard Rails db (no "id" column for the primary key). 
Could someone
> confirm that my intended use of the primary attribute on Column is
> appropriate?
>
> Thanks,
> Steve
>   
The primary attribute gets set in AciveRecord::Base.columns:

      def columns
        unless @columns
          @columns = connection.columns(table_name, "#{name} Columns")
          @columns.each {|column| column.primary = column.name == 
primary_key}
        end
        @columns
      end

and records the fact that a certain column object represents a primary 
key column.

-- stefan

-- 
For rails performance tuning, see: http://railsexpress.de/blog
Subscription: http://railsexpress.de/blog/xml/rss20/feed.xml

Thanks for those details Stefan.  What I''m looking to do is make it so
connection.columns sets the primary flag so that when I do a db_schema_dump
I can have the create_table have the correct :primary_key value (assuming
the default "id" isn''t being used).

So if I have:

CREATE TABLE Users (
  UserID int NOT NULL IDENTITY(1,1) PRIMARYKEY,
  Name varchar(100)
)

and do a db_schema_dump I want to get:

create_table "Users", :primary_key => "UserID" do |t|
  t.column "Name", :string, :limit => 100
end

Currently since the connection.columns doesn''t set the primary flag for
the
primary key column and since SchemaDumper doesn''t use
ActiveRecord::Base
(which would set the primary flag) I get:

create_table "users", :id => false do |t|
  t.column "UserID", :integer, :null => false
  t.column "Name", :string, :limit => 100
end

Do people agree that this would be a good change?  Can anyone offer insight
into what other adapters might be able to do here?

Thanks,
- Steve

The primary attribute gets set in
AciveRecord::Base.columns:
>
>       def columns
>         unless @columns
>           @columns = connection.columns(table_name, "#{name}
Columns")
>           @columns.each {|column| column.primary = column.name =>
primary_key}
>         end
>         @columns
>       end
>
> and records the fact that a certain column object represents a primary
> key column.
>
> -- stefan
>
> --
> For rails performance tuning, see: http://railsexpress.de/blog
> Subscription: http://railsexpress.de/blog/xml/rss20/feed.xml
>
> _______________________________________________
> Rails-core mailing list
> Rails-core@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails-core
>

_______________________________________________
Rails-core mailing list
Rails-core@lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails-core

> What I'm looking to do is make it so
> connection.columns sets the primary flag so that when I do a db_schema_dump
> I can have the create_table have the correct :primary_key value (assuming
> the default "id" isn't being used).
>
> So if I have:
>
> CREATE TABLE Users (
>   UserID int NOT NULL IDENTITY(1,1) PRIMARYKEY,
>   Name varchar(100)
> )
>
> and do a db_schema_dump I want to get:
>
> create_table "Users", :primary_key => "UserID" do
|t|
>   t.column "Name", :string, :limit => 100
> end
>
> Currently since the connection.columns doesn't set the primary flag for
the
> primary key column and since SchemaDumper doesn't use
ActiveRecord::Base
> (which would set the primary flag) I get:
>
> create_table "users", :id => false do |t|
>   t.column "UserID", :integer, :null => false
>   t.column "Name", :string, :limit => 100
> end
> Do people agree that this would be a good change?  Can anyone offer insight
> into what other adapters might be able to do here?
To me it's pretty clear the schema dumper should dump the whole
schema, including information about primary keys.  However, I don't
think the right way to get this info is through
ActiveRecord::Base.primary_key.

ActiveRecord::Base.primary_key uses declarations in the model classes
(set_primary_key, primary_key_prefix_type) to determine the primary
key.  However, the schema dumper gets all of its information directly
from the database.  To work correctly, I think the schema dumper
should get primary key info directly from the db as well, though this
would mean writing new code for most, if not all the adapters.  One
added benefit if this was done, might be that primary key declarations
could be removed from the model code completely, making things simpler
for those using quirky primary keys.

Hope this is clear,

Tom
--
email : tom at popdog.net

_______________________________________________
Rails-core mailing list
Rails-core@lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails-core

>
> To me it''s pretty clear the schema dumper should dump the whole
> schema, including information about primary keys.  However, I
don''t
> think the right way to get this info is through
> ActiveRecord::Base.primary_key.

Agreed.  My intention was to update the columns method on the connection
adapter to set the primary attribute to true for the primary key.  The one
thing which we ran into while doing some work on this is that for tables
with a primary key that is made up of more then one field we probably need
to take the declaration of the primary key out of the table definition and
put it as a seperate "add_primary_key" method that would then be
recognized
by ActiveRecord::Migration.

create_table :my_link_table, :id => false do |t|
  t.column :table1_id, :integer
  t.column :table2_id, :integer
end

add_primary_key :my_link_table, :table1_id, :table2_id

ActiveRecord::Base.primary_key uses declarations in the model
classes
> (set_primary_key, primary_key_prefix_type) to determine the primary
> key.  However, the schema dumper gets all of its information directly
> from the database.  To work correctly, I think the schema dumper
> should get primary key info directly from the db as well, though this
> would mean writing new code for most, if not all the adapters.  One
> added benefit if this was done, might be that primary key declarations
> could be removed from the model code completely, making things simpler
> for those using quirky primary keys.

The only downside that I can think of is that the query for retrieving
columns with a primary key may be more costly then a query just for the
column details.  For the SQL Server Adapter getting the primary key requires
including a LEFT OUTER  JOIN on a sub-query.

- Steve


_______________________________________________
Rails-core mailing list
Rails-core@lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails-core

> Agreed.  My intention was to update the columns method on the connection
> adapter to set the primary attribute to true for the primary key.  The one
> thing which we ran into while doing some work on this is that for tables
> with a primary key that is made up of more then one field we probably need
> to take the declaration of the primary key out of the table definition and
> put it as a seperate "add_primary_key" method that would then be
recognized
> by ActiveRecord::Migration.
I don't think rails supports primary keys across multiple columns,
though work may be being done on this.  Currently though, many methods
(such as find(id)) assume a single-column primary key.  I'm not sure
the best way to resolve this.  I'd probably look to getting it working
without multiple column PKs, then building on that.

Also, its worth considering how to get the PK from a table definition.
 I think the PK belongs to the table, rather than just being a
property of a column.
> The only downside that I can think of is that the query for retrieving
> columns with a primary key may be more costly then a query just for the
> column details.  For the SQL Server Adapter getting the primary key
requires
> including a LEFT OUTER  JOIN on a sub-query.
In production mode this won't be an issue as the columns information
is only loaded once.

Tom

_______________________________________________
Rails-core mailing list
Rails-core@lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails-core

> I don''t think rails supports primary keys across multiple columns,
> though work may be being done on this.  Currently though, many methods
> (such as find(id)) assume a single-column primary key.  I''m not
sure
> the best way to resolve this.  I''d probably look to getting it
working
> without multiple column PKs, then building on that.

This need was strictly for Migrations not for model objects.  We currently
have our link tables have the ID''s for each table it''s linking
defined as a
multiple column primary key.

Also, its worth considering how to get the PK from a table
definition.
> I think the PK belongs to the table, rather than just being a
> property of a column.

I definitely agree that having a primary key at the table definition level
would be good.

In production mode this won''t be an issue as the columns
information
> is only loaded once.

Ok, I assumed this was the case but I wanted to bring it up just to make
sure.

- Steve


_______________________________________________
Rails-core mailing list
Rails-core@lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails-core