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
>
