Rails core - Mar 2006 - Converting AR Test Suite to use AR::Schema - Feedback needed

Hi,

I''m working on converting the test suite over to AR::Schema.
I''m finding there are a lot of barriers to doing this due to somewhat
limited customization support in AR::Schema.

Here are some ideas (some implemented already) to extend AR::Schema, but
still keep it (relatively) true to its original design.

Foreign Key Support (MySQL in this instance):

create_table(:accounts) do |t|
  t.column :company_id, :integer, :null => false, :foreign_key_table =>
:companies
end

=> 

create table accounts (
  id int(11) not null auto_increment primary key,
  company_id int(11) not null foreign key (`company_id`) references
`companies`(`id`)
)

I''ve got this working great for all adapters already. Though not shown
here,
you can also pass :foreign_key_column for non-standard primary key column
names.


Since there are times where it doesn''t make sense for AR::Schema to
support
a specific column type/definition, what are your ideas on this (from the
postgres.sql):

CREATE TABLE geometrics (
  id serial primary key,
  a_point point,
  a_line_segment lseg,
  ...
);

Can be generated as such:

create_table(:geometrics) do |t|
  t.column :a_point, :custom, :type => "point default (1,3)",
  t.column :a_line_segment, :custom, :type => "lseg",
  ...
end

The keywords ":custom and :type" aren''t important here, the
concept is.
":describe and :description", ":define and :definition" are
alternatives.
I think this would be a nice addition, but it depends on how pure you want
to be, personally I lost all my pureness from years of PHP programming.

Wait, there''s more...

Most adapters create an auto incrementing column differently. Some are built
into the primary key column definition (MySQL, SQL Server), others use
explicit sequences, others use functions etc.. combine this with the
ability/desire to set the starting sequence number and it gets all messed up
(which most of the AR unit tests schemas do).
While there are no elegant solutions, I do believe its possible to support
all adapters, by adding an empty create_sequence method in `module
SchemaStatements` and have the adapters create their own if needed. To
handle setting the start value, here''s one idea:

create_sequence(:accounts, :name =>
''public.account_id_seq'', :start => 100)
create_table(:accounts) do |t|
...
end


Which adds whatever method the adaptor needs to implement the auto-increment
column and set the start value. Some do it on the primary key column
definition (SQL Server), some call a separate function (PGSQL), MySQL adds
it as a table option after the closing ) etc. Option B for this one would be
to change all the AR unit tests to not require the non-standard start
sequence.

And more...

Primary Keys. While the convention is certainly to have an auto_incrementing
integer primary key column, even the AR unit tests have table definitions
with string based primary keys and some db_definitions have multi-column PKs
defined. AR::Schema already has the ability to turn off auto generation of
the primary key, I also propose a method to add an explicit PRIMARY KEY
(column), for example, from an existing test fixture:

create_table :subscribers do |t|
  t.column :nick, :string, :limit => 100, :null => false
  t.column :name, :string, :limit => 100
  t.add_primary_key :nick
end

=>

CREATE TABLE `subscribers` (
  `nick` varchar(100) NOT NULL,
  `name` varchar(100) default NULL,
  PRIMARY KEY (`nick`)
) TYPE=InnoDB;

t.add_primary_key(:categories, :posts) would generate a multi-column pk

While there are a ton more issues to deal with, I''ll save those for
later to
not cloud these issues since these are the bigger ones I/we would need to
tackle to achieve moving the unit tests to AR::Schema.

I await your ideas/comments.

Thanks,

Bob Silva
http://www.railtie.net/

+1 for also paying attention to primary_key_prefix_type when creating  
primary keys. :)

On Mar 1, 2006, at 4:08 PM, Bob Silva wrote:
> Hi,
>
> I''m working on converting the test suite over to AR::Schema.
> I''m finding there are a lot of barriers to doing this due to
somewhat
> limited customization support in AR::Schema.
>
> Here are some ideas (some implemented already) to extend  
> AR::Schema, but
> still keep it (relatively) true to its original design.
>
> Foreign Key Support (MySQL in this instance):
>
> create_table(:accounts) do |t|
>   t.column :company_id, :integer, :null =>  
> false, :foreign_key_table =>
> :companies
> end
>
> =>
>
> create table accounts (
>   id int(11) not null auto_increment primary key,
>   company_id int(11) not null foreign key (`company_id`) references
> `companies`(`id`)
> )
>
> I''ve got this working great for all adapters already. Though not  
> shown here,
> you can also pass :foreign_key_column for non-standard primary key  
> column
> names.
>
>
> Since there are times where it doesn''t make sense for AR::Schema
to
> support
> a specific column type/definition, what are your ideas on this  
> (from the
> postgres.sql):
>
> CREATE TABLE geometrics (
>   id serial primary key,
>   a_point point,
>   a_line_segment lseg,
>   ...
> );
>
> Can be generated as such:
>
> create_table(:geometrics) do |t|
>   t.column :a_point, :custom, :type => "point default (1,3)",
>   t.column :a_line_segment, :custom, :type => "lseg",
>   ...
> end
>
> The keywords ":custom and :type" aren''t important here,
the concept
> is.
> ":describe and :description", ":define and :definition"
are
> alternatives.
> I think this would be a nice addition, but it depends on how pure  
> you want
> to be, personally I lost all my pureness from years of PHP  
> programming.
>
> Wait, there''s more...
>
> Most adapters create an auto incrementing column differently. Some  
> are built
> into the primary key column definition (MySQL, SQL Server), others use
> explicit sequences, others use functions etc.. combine this with the
> ability/desire to set the starting sequence number and it gets all  
> messed up
> (which most of the AR unit tests schemas do).
> While there are no elegant solutions, I do believe its possible to  
> support
> all adapters, by adding an empty create_sequence method in `module
> SchemaStatements` and have the adapters create their own if needed. To
> handle setting the start value, here''s one idea:
>
> create_sequence(:accounts, :name =>
''public.account_id_seq'', :start
> => 100)
> create_table(:accounts) do |t|
> ...
> end
>
>
> Which adds whatever method the adaptor needs to implement the auto- 
> increment
> column and set the start value. Some do it on the primary key column
> definition (SQL Server), some call a separate function (PGSQL),  
> MySQL adds
> it as a table option after the closing ) etc. Option B for this one  
> would be
> to change all the AR unit tests to not require the non-standard start
> sequence.
>
> And more...
>
> Primary Keys. While the convention is certainly to have an  
> auto_incrementing
> integer primary key column, even the AR unit tests have table  
> definitions
> with string based primary keys and some db_definitions have multi- 
> column PKs
> defined. AR::Schema already has the ability to turn off auto  
> generation of
> the primary key, I also propose a method to add an explicit PRIMARY  
> KEY
> (column), for example, from an existing test fixture:
>
> create_table :subscribers do |t|
>   t.column :nick, :string, :limit => 100, :null => false
>   t.column :name, :string, :limit => 100
>   t.add_primary_key :nick
> end
>
> =>
>
> CREATE TABLE `subscribers` (
>   `nick` varchar(100) NOT NULL,
>   `name` varchar(100) default NULL,
>   PRIMARY KEY (`nick`)
> ) TYPE=InnoDB;
>
> t.add_primary_key(:categories, :posts) would generate a multi- 
> column pk
>
> While there are a ton more issues to deal with, I''ll save those
for
> later to
> not cloud these issues since these are the bigger ones I/we would  
> need to
> tackle to achieve moving the unit tests to AR::Schema.
>
> I await your ideas/comments.
>
> Thanks,
>
> Bob Silva
> http://www.railtie.net/
>
>
>
> _______________________________________________
> Rails-core mailing list
> Rails-core@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails-core

On 3/1/06, Bob Silva <me@bobsilva.com> wrote:
> Hi,
>
> I''m working on converting the test suite over to AR::Schema.
> I''m finding there are a lot of barriers to doing this due to
somewhat
> limited customization support in AR::Schema.
>
> Here are some ideas (some implemented already) to extend AR::Schema, but
> still keep it (relatively) true to its original design.
>
> Foreign Key Support (MySQL in this instance):
>
> create_table(:accounts) do |t|
>   t.column :company_id, :integer, :null => false, :foreign_key_table
=>
> :companies
> end
>
> =>
>
> create table accounts (
>   id int(11) not null auto_increment primary key,
>   company_id int(11) not null foreign key (`company_id`) references
> `companies`(`id`)
> )
>
> I''ve got this working great for all adapters already. Though not
shown here,
> you can also pass :foreign_key_column for non-standard primary key column
> names.
>
there''s a plugin that requires some hacking but it''s a decent
approach
to supporting foreign keys.  I mentioned on IRC i''d be down to workout
a plugin for this.

http://wiki.rubyonrails.com/rails/pages/Foreign+Key+Schema+Dumper+Plugin
>
> Since there are times where it doesn''t make sense for AR::Schema
to support
> a specific column type/definition, what are your ideas on this (from the
> postgres.sql):
>
> CREATE TABLE geometrics (
>   id serial primary key,
>   a_point point,
>   a_line_segment lseg,
>   ...
> );
>
> Can be generated as such:
>
> create_table(:geometrics) do |t|
>   t.column :a_point, :custom, :type => "point default (1,3)",
>   t.column :a_line_segment, :custom, :type => "lseg",
>   ...
> end
I think :custom would make for a cool plugin but something seems
awkward about it to me.  I don''t know why. :)
>
> The keywords ":custom and :type" aren''t important here,
the concept is.
> ":describe and :description", ":define and :definition"
are alternatives.
> I think this would be a nice addition, but it depends on how pure you want
> to be, personally I lost all my pureness from years of PHP programming.
>
> Wait, there''s more...
>
> Most adapters create an auto incrementing column differently. Some are
built
> into the primary key column definition (MySQL, SQL Server), others use
> explicit sequences, others use functions etc.. combine this with the
> ability/desire to set the starting sequence number and it gets all messed
up
> (which most of the AR unit tests schemas do).
> While there are no elegant solutions, I do believe its possible to support
> all adapters, by adding an empty create_sequence method in `module
> SchemaStatements` and have the adapters create their own if needed. To
> handle setting the start value, here''s one idea:
>
I dig the idea of specifying a start sequence, I''m doing this in a
round about way at work currently. :(
> create_sequence(:accounts, :name =>
''public.account_id_seq'', :start => 100)
> create_table(:accounts) do |t|
> ...
> end
>
>
> Which adds whatever method the adaptor needs to implement the
auto-increment
> column and set the start value. Some do it on the primary key column
> definition (SQL Server), some call a separate function (PGSQL), MySQL adds
> it as a table option after the closing ) etc. Option B for this one would
be
> to change all the AR unit tests to not require the non-standard start
> sequence.
>
> And more...
>
> Primary Keys. While the convention is certainly to have an
auto_incrementing
> integer primary key column, even the AR unit tests have table definitions
> with string based primary keys and some db_definitions have multi-column
PKs
> defined. AR::Schema already has the ability to turn off auto generation of
> the primary key, I also propose a method to add an explicit PRIMARY KEY
> (column), for example, from an existing test fixture:
>
> create_table :subscribers do |t|
>   t.column :nick, :string, :limit => 100, :null => false
>   t.column :name, :string, :limit => 100
>   t.add_primary_key :nick
> end
>
create_table :subscribers, :primary_key => :nick do |t|
 ...
end
> =>
>
> CREATE TABLE `subscribers` (
>   `nick` varchar(100) NOT NULL,
>   `name` varchar(100) default NULL,
>   PRIMARY KEY (`nick`)
> ) TYPE=InnoDB;
>
> t.add_primary_key(:categories, :posts) would generate a multi-column pk
While I don''t personally like composite keys maybe something like
create_table :articles, :primary_key => [:categories, :posts] do |t|
 ..
end
>
> While there are a ton more issues to deal with, I''ll save those
for later to
> not cloud these issues since these are the bigger ones I/we would need to
> tackle to achieve moving the unit tests to AR::Schema.
>
> I await your ideas/comments.
>
> Thanks,
>
> Bob Silva
> http://www.railtie.net/
>
>
>
> _______________________________________________
> Rails-core mailing list
> Rails-core@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails-core
>

--
Corey Donohoe
http://www.atmos.org/
corey.donohoe@gmail.com

> create_table :subscribers, :primary_key => :nick do |t|
>  ...
> end
Unless I''m being a total idiot in reading the code, this creates an
integer
based primary key column and doesn''t allow an arbitrary primary key
column
like in the example I provided.

Bob Silva
http://www.railtie.net/

> -----Original Message-----
> From: rails-core-bounces@lists.rubyonrails.org [mailto:rails-core-
> bounces@lists.rubyonrails.org] On Behalf Of Corey Donohoe
> Sent: Wednesday, March 01, 2006 7:24 PM
> To: rails-core@lists.rubyonrails.org
> Subject: Re: [Rails-core] Converting AR Test Suite to use AR::Schema -
> Feedback needed
> 
> On 3/1/06, Bob Silva <me@bobsilva.com> wrote:
> > Hi,
> >
> > I''m working on converting the test suite over to AR::Schema.
> > I''m finding there are a lot of barriers to doing this due to
somewhat
> > limited customization support in AR::Schema.
> >
> > Here are some ideas (some implemented already) to extend AR::Schema,
but
> > still keep it (relatively) true to its original design.
> >
> > Foreign Key Support (MySQL in this instance):
> >
> > create_table(:accounts) do |t|
> >   t.column :company_id, :integer, :null => false,
:foreign_key_table =>
> > :companies
> > end
> >
> > =>
> >
> > create table accounts (
> >   id int(11) not null auto_increment primary key,
> >   company_id int(11) not null foreign key (`company_id`) references
> > `companies`(`id`)
> > )
> >
> > I''ve got this working great for all adapters already. Though
not shown
> here,
> > you can also pass :foreign_key_column for non-standard primary key
> column
> > names.
> >
> 
> there''s a plugin that requires some hacking but it''s a
decent approach
> to supporting foreign keys.  I mentioned on IRC i''d be down to
workout
> a plugin for this.
> 
> http://wiki.rubyonrails.com/rails/pages/Foreign+Key+Schema+Dumper+Plugin
> 
> >
> > Since there are times where it doesn''t make sense for
AR::Schema to
> support
> > a specific column type/definition, what are your ideas on this (from
the
> > postgres.sql):
> >
> > CREATE TABLE geometrics (
> >   id serial primary key,
> >   a_point point,
> >   a_line_segment lseg,
> >   ...
> > );
> >
> > Can be generated as such:
> >
> > create_table(:geometrics) do |t|
> >   t.column :a_point, :custom, :type => "point default
(1,3)",
> >   t.column :a_line_segment, :custom, :type => "lseg",
> >   ...
> > end
> 
> I think :custom would make for a cool plugin but something seems
> awkward about it to me.  I don''t know why. :)
> 
> >
> > The keywords ":custom and :type" aren''t important
here, the concept is.
> > ":describe and :description", ":define and
:definition" are
> alternatives.
> > I think this would be a nice addition, but it depends on how pure you
> want
> > to be, personally I lost all my pureness from years of PHP
programming.
> >
> > Wait, there''s more...
> >
> > Most adapters create an auto incrementing column differently. Some are
> built
> > into the primary key column definition (MySQL, SQL Server), others use
> > explicit sequences, others use functions etc.. combine this with the
> > ability/desire to set the starting sequence number and it gets all
> messed up
> > (which most of the AR unit tests schemas do).
> > While there are no elegant solutions, I do believe its possible to
> support
> > all adapters, by adding an empty create_sequence method in `module
> > SchemaStatements` and have the adapters create their own if needed. To
> > handle setting the start value, here''s one idea:
> >
> 
> I dig the idea of specifying a start sequence, I''m doing this in a
> round about way at work currently. :(
> 
> > create_sequence(:accounts, :name =>
''public.account_id_seq'', :start =>
> 100)
> > create_table(:accounts) do |t|
> > ...
> > end
> >
> >
> > Which adds whatever method the adaptor needs to implement the auto-
> increment
> > column and set the start value. Some do it on the primary key column
> > definition (SQL Server), some call a separate function (PGSQL), MySQL
> adds
> > it as a table option after the closing ) etc. Option B for this one
> would be
> > to change all the AR unit tests to not require the non-standard start
> > sequence.
> >
> > And more...
> >
> > Primary Keys. While the convention is certainly to have an
> auto_incrementing
> > integer primary key column, even the AR unit tests have table
> definitions
> > with string based primary keys and some db_definitions have
multi-column
> PKs
> > defined. AR::Schema already has the ability to turn off auto
generation
> of
> > the primary key, I also propose a method to add an explicit PRIMARY
KEY
> > (column), for example, from an existing test fixture:
> >
> > create_table :subscribers do |t|
> >   t.column :nick, :string, :limit => 100, :null => false
> >   t.column :name, :string, :limit => 100
> >   t.add_primary_key :nick
> > end
> >
> create_table :subscribers, :primary_key => :nick do |t|
>  ...
> end
> 
> > =>
> >
> > CREATE TABLE `subscribers` (
> >   `nick` varchar(100) NOT NULL,
> >   `name` varchar(100) default NULL,
> >   PRIMARY KEY (`nick`)
> > ) TYPE=InnoDB;
> >
> > t.add_primary_key(:categories, :posts) would generate a multi-column
pk
> 
> While I don''t personally like composite keys maybe something like
> create_table :articles, :primary_key => [:categories, :posts] do |t|
>  ..
> end
> 
> >
> > While there are a ton more issues to deal with, I''ll save
those for
> later to
> > not cloud these issues since these are the bigger ones I/we would need
> to
> > tackle to achieve moving the unit tests to AR::Schema.
> >
> > I await your ideas/comments.
> >
> > Thanks,
> >
> > Bob Silva
> > http://www.railtie.net/
> >
> >
> >
> > _______________________________________________
> > Rails-core mailing list
> > Rails-core@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails-core
> >
> 
> 
> --
> Corey Donohoe
> http://www.atmos.org/
> corey.donohoe@gmail.com
> _______________________________________________
> Rails-core mailing list
> Rails-core@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails-core

On 3/1/06, Bob Silva <me@bobsilva.com> wrote:
> > create_table :subscribers, :primary_key => :nick do |t|
> >  ...
> > end
>
> Unless I''m being a total idiot in reading the code, this creates
an integer
> based primary key column and doesn''t allow an arbitrary primary
key column
> like in the example I provided.
>
>
Nope it was me being the idiot, I was kinda surprised that it did
this.  It doesn''t respect the type you give the column if it''s
the
primary key. :(

mysql> describe subscribers;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| nick  | int(11)      |      | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)



--
Corey Donohoe
http://www.atmos.org/
corey.donohoe@gmail.com