looking at the unit tests of activerecord leads me to think the db is
completely responsible for auto-incrementing the id field and rails does no
magic here. can someone confirm that this is a true statement?
that being the case - what is the preferred way to write database agnostic sql
for one''s schema since each of the dbs has slightly different syntax
for this
functionality... does anyone know how to do
postgres:
create table t(
id serial,
data text,
primary key (id)
);
mysql:
create table t(
id int(11) not null auto_increment,
data text,
primary key (id)
);
sqlite:
create table t(
id integer primary key,
data text,
);
with a single schema that will result in an auto-incrementing id in each db?
cheers.
-a
--
==============================================================================|
EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov
| PHONE :: 303.497.6469
| When you do something, you should burn yourself completely, like a good
| bonfire, leaving no trace of yourself. --Shunryu Suzuki
===============================================================================
> looking at the unit tests of activerecord leads me to think the db is > completely responsible for auto-incrementing the id field and rails > does no > magic here. can someone confirm that this is a true statement?Correct.> that being the case - what is the preferred way to write database > agnostic sql > for one''s schema since each of the dbs has slightly different syntax > for this > functionality... does anyone know how to doI don''t think you can, at least until the vendors get their DDLs all in alignment. BTW, in PostgreSQL, it''s better say "serial primary key" rather than just "serial", since I believe they no longer implicitly create a unique index for serial columns. The serial keyword, for instance, creates an associated sequence and a default value for the column. MySQL and SQLite don''t have sequences, so there''s really no analogue there. Regards, -- Dave Steinberg http://www.geekisp.com/ http://www.steinbergcomputing.com/
On Wed, 9 Mar 2005, Dave Steinberg wrote:>> looking at the unit tests of activerecord leads me to think the db is >> completely responsible for auto-incrementing the id field and rails does no >> magic here. can someone confirm that this is a true statement? > > Correct.thanks.>> that being the case - what is the preferred way to write database agnostic >> sql for one''s schema since each of the dbs has slightly different syntax >> for this functionality... does anyone know how to do > > I don''t think you can, at least until the vendors get their DDLs all in > alignment. BTW, in PostgreSQL, it''s better say "serial primary key" rather > than just "serial", since I believe they no longer implicitly create a > unique index for serial columns.right - typo ;-)> The serial keyword, for instance, creates an associated sequence and a > default value for the column. MySQL and SQLite don''t have sequences, so > there''s really no analogue there.i guess i''ll have to generate the sql then - no big deal. thanks again. -a -- ==============================================================================| EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov | PHONE :: 303.497.6469 | When you do something, you should burn yourself completely, like a good | bonfire, leaving no trace of yourself. --Shunryu Suzuki ===============================================================================
Ara.T.Howard <Ara.T.Howard-32lpuo7BZBA@public.gmane.org> wrote:> looking at the unit tests of activerecord leads me to think the db is > completely responsible for auto-incrementing the id field and rails does no > magic here.correct.> that being the case - what is the preferred way to write database agnostic sql > for one''s schema since each of the dbs has slightly different syntax for this > functionality... does anyone know how to doI haven''t used sqlite, but in using postgresql and mysql, I definte the tables in one file, and then have db specific files containing appropriate alter table commands: -- t.sql create table t ( id int not null, data text, primary key (id) ); -- t-pgsql.sql create sequence t_id_seq; alter table t alter id set default nextval(''t_id_seq''); -- t-mysql.sql alter table modify id int not null auto_increment; Dave
> i guess i''ll have to generate the sql then - no big deal. thanks > again.A abstract schema definition add-on is coming to Active Record as its needed for the new Migration framework I''m doing (in which you can describe incremental changes to the database and move up and down in versions, see the archives for more). -- David Heinemeier Hansson, http://www.basecamphq.com/ -- Web-based Project Management http://www.rubyonrails.org/ -- Web-application framework for Ruby http://www.loudthinking.com/ -- Broadcasting Brain
On Wed, 9 Mar 2005, Dave Lee wrote:> Ara.T.Howard <Ara.T.Howard-32lpuo7BZBA@public.gmane.org> wrote: >> looking at the unit tests of activerecord leads me to think the db is >> completely responsible for auto-incrementing the id field and rails does no >> magic here. > > correct. > >> that being the case - what is the preferred way to write database agnostic sql >> for one''s schema since each of the dbs has slightly different syntax for this >> functionality... does anyone know how to do > > I haven''t used sqlite, but in using postgresql and mysql, I definte > the tables in one file, and then have db specific files containing > appropriate alter table commands: > > -- t.sql > create table t ( > id int not null, > data text, > primary key (id) > ); > > -- t-pgsql.sql > create sequence t_id_seq; > alter table t alter id set default nextval(''t_id_seq''); > > -- t-mysql.sql > alter table modify id int not null auto_increment;smart - i''ll take it! -a -- ==============================================================================| EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov | PHONE :: 303.497.6469 | When you do something, you should burn yourself completely, like a good | bonfire, leaving no trace of yourself. --Shunryu Suzuki ===============================================================================
On Wed, 9 Mar 2005 10:21:21 -0700 (MST), Ara.T.Howard <Ara.T.Howard-32lpuo7BZBA@public.gmane.org> wrote:> On Wed, 9 Mar 2005, Dave Steinberg wrote: > > I don''t think you can, at least until the vendors get their DDLs all in > > alignment. BTW, in PostgreSQL, it''s better say "serial primary key" rather > > than just "serial", since I believe they no longer implicitly create a > > unique index for serial columns. > > right - typo ;-)What typo? You said "primary key (id)" later in the table definition. -- One Guy With A Camera http://rbpark.ath.cx