SQLite primary key schema

Rails
1

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

2

Hi,

On 12/25/05, Daniel H. [email protected] wrote:

login varchar(80),
CREATE TABLE users (
‘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

3

On Dec 25, 2005, at 19:19, Sam S. wrote:

On 12/25/05, Daniel H. [email protected] 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

4

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 S. 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 H.