On a second thought of this matter....this is nonsense.
If a table has two autoincrement fields ("id" and "other") they always
contain the same values or values that differ for a constant offset
(If the started with different values). That is why some database
backends probably do not even support it and that is why I did not add
web2py APIs to do this.
The only reason to to have two auto-increments fields is if one "id"
belongs to the table and "other: belongs to multiple tables, i.e. its
value has to be unique for records stored in multiple tables. In this
latter case it is not an auto increment fields but a referent to a
table with an auto increment field and the proposed solution of using
an auxiliary table is more than a hack, but probably the way to do go.
Going back to your original case whopper_id is, naively, 20000+id so
you do not need a second auto-increment fields. The problem is that
because whopper_id<60000, this leaves you only 40000 records available
unless you recycle the values. Using an auto-increment would allow you
to recycle values.
Massimo
On Jun 9, 9:26 am, mdipierro <[email protected]> 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 <[email protected]> wrote:
>
> > Great. What about sqlite?
>
> > --
> > Thadeus
>
> > On Wed, Jun 9, 2010 at 8:41 AM, mdipierro <[email protected]> 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 <[email protected]> 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 <[email protected]>
> > >> 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 <[email protected]> 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 <[email protected]>
> > >> >> 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 <[email protected]> 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
