Rails - Dec 2005 - SQLite primary key schema

So after successfully getting the test SQLite db to run from memory, I
tried running some tests but found that somewhere in the schema
translation from Postgres (my devel database) and SQLite Rails fails
to properly set the ''id'' column, which breaks all kinds of
things.

Take as an example my ''users'' table. In Postgres the schema is
(generated with pgAdmin III):
CREATE TABLE users
(
  id serial NOT NULL,
  login varchar(80),
  "password" varchar(255),
  created_on timestamp,
  updated_on timestamp,
  avatar varchar(255),
  CONSTRAINT users_pkey PRIMARY KEY (id)
)
WITH OIDS;
ALTER TABLE users OWNER TO george;

Everything works just dandy until we cross over to the SQLite world:
CREATE TABLE users (
    id serial NOT NULL,
    login character varying(80),
    "password" character varying(255),
    created_on timestamp without time zone,
    updated_on timestamp without time zone,
    avatar character varying(255)
);

That ''id serial NOT NULL'' should clearly be an ''id
INTEGER PRIMARY KEY
NOT NULL'', but regardless of how much I play with the settings in
''sqlite_adapter.rb'' and
''postgres_adapter.rb'', I still get the same
result. Does anyone have any brilliant ideas?

Thanks in advance,
Dan

Hi,

On 12/25/05, Daniel Hackney
<chrono325-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> So after successfully getting the test SQLite db to run from memory, I
> tried running some tests but found that somewhere in the schema
> translation from Postgres (my devel database) and SQLite Rails fails
> to properly set the ''id'' column, which breaks all kinds
of things.
>
> Take as an example my ''users'' table. In Postgres the
schema is
> (generated with pgAdmin III):
> CREATE TABLE users
> (
>   id serial NOT NULL,
>   login varchar(80),
>   "password" varchar(255),
>   created_on timestamp,
>   updated_on timestamp,
>   avatar varchar(255),
>   CONSTRAINT users_pkey PRIMARY KEY (id)
> )
> WITH OIDS;
> ALTER TABLE users OWNER TO george;
>
> Everything works just dandy until we cross over to the SQLite world:
> CREATE TABLE users (
>     id serial NOT NULL,
>     login character varying(80),
>     "password" character varying(255),
>     created_on timestamp without time zone,
>     updated_on timestamp without time zone,
>     avatar character varying(255)
> );
>
> That ''id serial NOT NULL'' should clearly be an
''id INTEGER PRIMARY KEY
> NOT NULL'', but regardless of how much I play with the settings in
> ''sqlite_adapter.rb'' and
''postgres_adapter.rb'', I still get the same
> result. Does anyone have any brilliant ideas?
How is the schema being loaded into the SQLite database? Do you have
"config.active_record.schema_format = :ruby" set in
config/environment.rb (and a schema.rb in db/)?  Please show the exact
steps you''re using to run your tests.

--
sam

On Dec 25, 2005, at 19:19, Sam Stephenson wrote:
> On 12/25/05, Daniel Hackney
<chrono325-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>> So after successfully getting the test SQLite db to run from memory
Were you using the parameters you sent to the other thread (which  
didn''t actually give an in-memory database)? Or did you really  
accomplish it?

I didn''t succeed at all by now. Should I touch something in config/ 
environment.rb? I have everything as rails generates (1.0).

-- fxn

Well then, it seems it was a simple problem of not RTFMing. Setting
"config.active_record.schema_format = :ruby" took care of it. It makes
sense now that it was using the same SQL schema as the devel database
by default. Thank you very much to Sam Stephenson for pointing that
out.

It seems that I still have a bit of work before getting the in-memory
SQLite database to work (using ":memory" does not give errors because
it just uses the file :memory). So that is still a mystery. I got the
sqlite schema dump by changing dbfile to db/test.db and then just
dumping its schema like normal.

Thanks again for the help,
Daniel Hackney


On 12/25/05, Xavier Noria <fxn-xlncskNFVEJBDgjK7y7TUQ@public.gmane.org>
wrote:
> On Dec 25, 2005, at 19:19, Sam Stephenson wrote:
>
> > On 12/25/05, Daniel Hackney
<chrono325-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> >> So after successfully getting the test SQLite db to run from
memory
>
> Were you using the parameters you sent to the other thread (which
> didn''t actually give an in-memory database)? Or did you really
> accomplish it?
>
> I didn''t succeed at all by now. Should I touch something in
config/
> environment.rb? I have everything as rails generates (1.0).
>
> -- fxn
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>