Autoincrement for non-id column

There is a table:

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?

Thanks.

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’)”

in the migration file.

db/migrations/NNNNN_create_simple_models.rb

class CreateSimpleModels < ActiveRecord::Migration
def self.up
create_table :simple_models do |t|
t.string :name
t.integer :counter
t.timestamps
end
execute “CREATE SEQUENCE simple_models_counter_seq OWNED BY
simple_models.counter INCREMENT BY 3 START WITH 1”
end

def self.down
drop_table :simple_models
end
end


models/simple_model.rb

class SimpleModel < ActiveRecord::Base
validates_uniqueness_of :name
before_create :increment_counter

private

def increment_counter
value = ActiveRecord::Migration::execute “SELECT nextval
(‘simple_models_counter_seq’)”
self.counter = value[0][“nextval”]
end
end


Thanks! Frankly speaking, I’ve decided to use java for this project.
It’s not good to fight with framework’s logic)

adrianopol wrote:

Thanks! Frankly speaking, I’ve decided to use java for this project.
It’s not good to fight with framework’s logic)

So learn it instead of fighting with it. Anyway, while I’m surprised
that Rails migrations can’t do what you want here, I also can’t see how
more than one autoincrement field in a table would be useful: if you
have one, then you can calculate all the others from it.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

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.

On Sep 9, 9:17 pm, Marnen Laibow-Koser <rails-mailing-l…@andreas-

How is id + version different from number + version? id and number are
both auto-incrementing.

I don’t see how Java is going to make this problem (with the design)
go away.

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 :slight_smile: )

adrianopol wrote:

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 :slight_smile: )

Then why are you making statements like this on the Rails list? :slight_smile:

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

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 did things this way for about 8 years.
This is very helpful to work with low-level things for some time)

Then why are you making statements like this on the Rails list? :slight_smile:
Because originally I tried to write application using rails.

On Sep 10, 12:41 am, Marnen Laibow-Koser <rails-mailing-l…@andreas-

adrianopol wrote:

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.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

On Sep 10, 7:03 am, adrianopol [email protected] wrote:

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 did things this way for about 8 years.

This is very helpful to work with low-level things for some time)

There’s nothing like reinventing the wheel to really run up those
billable hours!

–Matt J.

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?

Reference:
http://api.rubyonrails.org/classes/ActiveRecord/Base.html#M002338

Thanks,
Abhinav

अभिनव
http://twitter.com/abhinav

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs