Rails - Sep 2009 - 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

-------------


On Sep 7, 6:29 am, adrianopol
<adriano...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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.

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

On Sep 8, 10:42 pm, Rick
<richard.t.ll...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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
>
> -------------
>
> On Sep 7, 6:29 am, adrianopol
<adriano...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > 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.

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
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

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-
s.net> wrote:
> 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-Koserhttp://www.marnen.org
> mar...-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
> --
> Posted viahttp://www.ruby-forum.com/.

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.

On Sep 9, 10:36 am, adrianopol
<adriano...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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-
>
> s.net> wrote:
> > 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-Koserhttp://www.marnen.org
> > mar...-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
> > --
> > Posted viahttp://www.ruby-forum.com/.

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

On Sep 9, 9:48 pm, "E. Litwin"
<elit...-ur4TIblo6goN+BqQ9rBEUg@public.gmane.org>
wrote:
> 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.
>
> On Sep 9, 10:36 am, adrianopol
<adriano...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > 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-
>
> > s.net> wrote:
> > > 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-Koserhttp://www.marnen.org
> > > mar...-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
> > > --
> > > Posted viahttp://www.ruby-forum.com/.

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 :-) )
Then why are you making statements like this on the Rails list? :)

Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

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




On Mon, Sep 7, 2009 at 3:59 PM, adrianopol
<adrianopol-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
> 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.
> >
>

> 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? :)
Because originally I tried to write application using rails.

On Sep 10, 12:41 am, Marnen Laibow-Koser <rails-mailing-l...@andreas-
s.net> wrote:
> 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 :-) )
>
> Then why are you making statements like this on the Rails list? :)
>
> Best,
> --
> Marnen Laibow-Koserhttp://www.marnen.org
> mar...-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
> --
> Posted viahttp://www.ruby-forum.com/.

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
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

On Sep 10, 7:03 am, adrianopol
<adriano...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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 Jones