execute (<<-SQL)
CREATE TABLE "tasks" (
"id" serial primary key,
"number" serial,
"version" integer DEFAULT 0 NOT NULL,
"latest_version" boolean DEFAULT 't' NOT NULL,
"hidden" boolean DEFAULT 'f' NOT NULL,
"type" character varying (1) NOT NULL,
"created_at" timestamp
);
SQL
I need the number field to be serial, i.e. to be automatically filled
when mising it in the hash for create method. I had to write sql-code,
because for postgresql adapter cannot create serial-fields. But in
this case when trying to add
task = Task.create ( :type => "c")
error is raised:
PGError: ERROR: null value in column "number" violates not-null
constraint
: INSERT INTO "tasks" ( "number", "latest_version", "type", "version",
"hidden", "created_at") VALUES (NULL, 't', E'c ', 0,' f ', '2009-09-07
08:43:13.534476 ') RETURNING "id"
i.e. NULL is specified for number field. Can I exclude the number
field from the list for INSERT or maybe there is any other more
correct way to cope with it?
NOTE: This will only work with postgresql - need different code for
mysql etc.
This example mods the integer field :counter as a serial starting at 1
and incrementing by 3.
For some reason not known to me I haven't been able to figure out the
way to say
execute "ALTER TABLE simple_models ALTER COLUMN counter SET DEFAULT
nextval('simple_models_counter_seq')"
It is for versioning. I have a pair (number, version) as another
method to identificate a row in the table, so "number" and "id" are
absolutely independent. So, to my mind, more low-level language and
libraries are better in this situation.
How is id + version different from number + version? id and number are
both auto-incrementing.
I may have multiple objects with the same number, but with different
versions. Id is unique.
I don't see how Java is going to make this problem (with the design)
go away.
There is a little 'framework' I am using. It is more clear for me to
use raw sql-code for creating tables and manipulating with their data.
Abstracting from sql and specific DBMS in rails doesn't mean you
shouldn't learn them and know, how they work. But with JDBC I am not
bound by limitations of different adapters. (PS: I don't want to start
holy wars here )
How is id + version different from number + version? id and number are
both auto-incrementing.
I may have multiple objects with the same number, but with different
versions. Id is unique.
Then number is not auto-incrementing, and there is no problem. There's
even a Rails plugin called acts_as_versioned that does exactly what
you're describing.
I don't see how Java is going to make this problem (with the design)
go away.
There is a little 'framework' I am using. It is more clear for me to
use raw sql-code for creating tables and manipulating with their data.
I did things this way for about 8 years. Then I discovered Rails and
now I wouldn't want to do without migrations and ActiveRecord. I
suggest you put a bit of time into learning Rails before you decide it's
"more clear" to do without it.
Abstracting from sql and specific DBMS in rails doesn't mean you
shouldn't learn them and know, how they work.
Absolutely right.
But with JDBC I am not
bound by limitations of different adapters.
From the little I know of JDBC, I don't think this is accurate.
(PS: I don't want to start
holy wars here )
Then why are you making statements like this on the Rails list?
I still don't understand the problem completely, but will
ActiveRecord::Base#increment will help? You can write an activerecord
callback (before_create/after_create) and call increment on the number
attribute?
There's even a Rails plugin called acts_as_versioned
acts_as_versioned creates a new table, I prefer to avoid this in my
situation.
I didn't realize that acts_as_versioned needs a new table. However:
1. There's no reason to avoid adding tables if that's what's best to
model the data; and
2. Even with one table, the scenario you describe only needs one
autoincrement field.
)