If you have the setup, give it a try.
On Jun 10, 6:59 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
> Idea:
>
> Theoretically speaking of course.... if I were to create a virtual
> machine loaded with an apache instance and web2py on mod_wsgi. Using
> the various methods of *faking* an autonumber field on different
> virtual machines, for the sake of scientific research.
>
> Using the apache AB testing upon a page of web2py that performs an
> insert into the database it could be potential to determine any errors
> with the *faked* autonumber system.
>
> In this case, the original method of using a query for last_mem_id
> *should* fail under an extensive AB test. This would be determined
> since the AB tests keep track of how many 500 server errors are
> returned... in the web2py case, this would be the ``IntegrityError``
> issued from the database complaining about a duplicate value.
>
> So... using this same logic we can implement the other method of
> *faking* autonumber by creating a secondary table sequence manually in
> web2py. Using this same test we can verify if the server fails under
> high insertion load, or if it handles things correctly. This would
> look like Massimo's suggestion earlier.
>
> Also, the new autonumber support could be testing the exact same way.
>
> Does this way of testing make logical sense? Do you think it would
> work? What I am getting at is that if Massimo's suggestion of using a
> secondary table and compute to calculate will hold up to the AB
> testing then that would be the way to do things on a cross-database
> level. When you use SERIAL in postgres, it is effectively creating a
> secondary table to use as a sequence value that can correctly handle
> race-time conditions.
>
> --
> Thadeus
>
> On Wed, Jun 9, 2010 at 11:05 AM, mdipierro <mdipie...@cs.depaul.edu> wrote:
> > This is not going to stay, it is just for you to look at.
> > Consider defining the field as integer and create a trigger using SQL
> > to autofill this field.
>
> > On Jun 9, 10:52 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
> >> well, I am posting in trunk a modifiled sql.py that
>
> >> allows Field('name','autoincrement') and generates the following code
> >> for postgresql (only postgresql). Give it a try.
>
> >> Massimo
>
> >> On Jun 9, 10:38 am, Thadeus Burgess <thade...@thadeusb.com> wrote:
>
> >> > 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
