Postgres
CREATE TABLE foo (
id integer PRIMARY KEY SERIAL,
bar varchar,
did integer DEFAULT SERIAL);
Or...
CREATE SEQUENCE seq_foo_did START 20000;
CREATE TABLE foo (
id integer PRIMARY KEY SERIAL,
bar varchar,
did integer DEFAULT nextval('seq_foo_did'));
SQLite however does not support multiple auto-increment fields
unfortunately, so on sqlite it must be done the way of creating
another table with a blank field and use its id as a sequence
(basically like how postgres does behind the scenes).
MySQL also supports multiple autonumber fields.
Having two autonumber fields is an absolute requirement of the system
and there is no way around this. Unfortunately I don't have time to
work on implementing this in web2py, and I could probably re-write my
app in something else faster than it would take me to implement this
in the DAL since I am not familiar enough with the DALs internal
quirks.
I can't do anything for at least 3 weeks programming wise, so I won't
even be getting around to fixing this issue until then.
--
Thadeus
On Wed, Jun 9, 2010 at 9:26 AM, mdipierro <mdipie...@cs.depaul.edu> wrote:
> I am not sure about the postgresql solution. Tell me how you do it in
> SQL and i tell you how to do in web2pyese.
>
> On Jun 9, 9:01 am, Thadeus Burgess <thade...@thadeusb.com> wrote:
>> Great. What about sqlite?
>>
>> --
>> Thadeus
>>
>> On Wed, Jun 9, 2010 at 8:41 AM, mdipierro <mdipie...@cs.depaul.edu> wrote:
>> > In postgresql you get it native:
>>
>> > Field('yourtfield',SQLCustomType('integer','SERIAL PRIMARY
>> > KEY',encoder=(lambda x: int(x)),decoder=(lambda x:x)))
>>
>> > On Jun 9, 5:28 am, Thadeus Burgess <thade...@thadeusb.com> wrote:
>> >> That is the thing, its *almost* the same, but its not a true postgres
>> >> sequence. Postgres already has many years of development making sure
>> >> their auto number works, why can't I just use that instead of trying
>> >> to hack around the limitations of a system?
>>
>> >> I don't have a choice. I *must* have native support for autonumber, or
>> >> I have to use another system that already allows me to.
>>
>> >> --
>> >> Thadeus
>>
>> >> On Tue, Jun 8, 2010 at 10:22 PM, mdipierro <mdipie...@cs.depaul.edu>
>> >> wrote:
>> >> > If it were possible to do a SQL insert without the dummy filed this
>> >> > almost the same as creating a sequence. web2py can create a table
>> >> > without any field but the "id", but I do not do not how to do an
>> >> > insert without any field value.
>>
>> >> > On Jun 8, 8:12 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
>> >> >> This *might* work. You are right, it is still horrible... It might be
>> >> >> *effectively* accomplishing the same thing that sequences do on
>> >> >> PostgreSQL, however I still wouldn't use it in production as it feels
>> >> >> "hacky". I already have to re-design this table, so I might as well do
>> >> >> it 100% right.
>>
>> >> >> I never expected the scale of inserts that happened yesterday and
>> >> >> today, nor had any proper benchmarking been done previously if it
>> >> >> could handle this kind of sudden rush of traffic.
>>
>> >> >> --
>> >> >> Thadeus
>>
>> >> >> On Tue, Jun 8, 2010 at 4:36 PM, mdipierro <mdipie...@cs.depaul.edu>
>> >> >> wrote:
>> >> >> > I know this horrible but it does solve some of the problems...
>>
>> >> >> > db.define_table('whopper_seq',Field('dummy'))
>>
>> >> >> > db.define_table('yourtable',...
>> >> >> > Field("whopper_id", "integer",compute=lambda r:
>> >> >> > db.whopper_seq.insert(dummy=None))
>> >> >> > ...)
>>
>> >> >> > On Jun 7, 8:29 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
>> >> >> >> I have a problem.
>>
>> >> >> >> I have this in the database....
>>
>> >> >> >> Field("whopper_id", "string", default=None, unique=True),
>>
>> >> >> >> The thing with whopper_id is it always stores numbers. Said numbers
>> >> >> >> are anywhere from 20000 to 60000.
>>
>> >> >> >> Also upon entering a new entry, I do the following
>>
>> >> >> >> last_whopper_id = db(db.table.id > 0).select(db.table.whopper_id,
>> >> >> >> orderby=~db.table.whopper_id, limit=(0,1)).first().whopper_id
>> >> >> >> db.insert(whopper_id = (int(last_whopper_id) + 1))
>>
>> >> >> >> So I do all this juju just to get the number to autoincrement.
>>
>> >> >> >> The problem is, this structure is bad... first I'm storing integers
>> >> >> >> in
>> >> >> >> a string field, and then manually incrementing them!!!!
>>
>> >> >> >> I get errors like... IntegrityError: duplicate key value violates
>> >> >> >> unique constraint "table_whopper_id_key"... when two requests come
>> >> >> >> in
>> >> >> >> to create a record within miliseconds of each other.
>>
>> >> >> >> Here is where I need some help please.
>>
>> >> >> >> I need to convert this entire field, into an autoincrementing
>> >> >> >> integer
>> >> >> >> performed by the database, however ALL current whopper_ids must stay
>> >> >> >> EXACTLY the same.
>>
>> >> >> >> I don't know how to accomplish this with web2py. I know what I
>> >> >> >> want...
>>
>> >> >> >> Field("whopper_id", "integer", unique=True, autoincrement=True)
>>
>> >> >> >> But how do I convert all existing whopper_ids over and keep them
>> >> >> >> the exact same?
>>
>> >> >> >> Is this even possible with web2py and the DAL?
>>
>> >> >> >> --
>> >> >> >> Thadeus
>
