sqlobject-discuss

Ok, I've created a sqlobject like so:
class Output(SQLObject):
print_job =3D ForeignKey("PrintJob", notNone =3D True)
piece_id =3D IntCol(notNone =3D True)
...
Currently I have about 1.7 million rows in there. SQLObject created the
`id' column for the table as `int'. MySQL's int has a value from
-2147483648 to 2147483647. I suspect that I'll be at the maximum INT valu=
e
shortly. Is there an `easy' way to reset the ID column to use BIGINT? I
suspect I'll have to regenerate the models, somehow specifying the id, but
I'm not sure.
Thanks!
jw
--
"Government does not solve problems; it subsidizes them."
Ronald Reagan

On Wed, Mar 29, 2006 at 03:07:15PM -0600, Jaime Wyant wrote:
> Is there an `easy' way to reset the ID column to use BIGINT?
ALTER TABLE...
I don't remember if MySQL can change the type of a column in ALTER TABLE.
If it cannot you need a temporary table...
Oleg.
--
Oleg Broytmann http://phd.pp.ru/ phd@...
Programmers don't die, they just GOSUB without RETURN.

Jaime Wyant wrote:
> Ok, I've created a sqlobject like so:
>
> class Output(SQLObject):
> print_job = ForeignKey("PrintJob", notNone = True)
> piece_id = IntCol(notNone = True)
>
> ...
>
> Currently I have about 1.7 million rows in there. SQLObject created the
> `id' column for the table as `int'. MySQL's int has a value from
> |-2147483648| |to 2147483647. |I suspect that I'll be at the maximum
> INT value shortly. Is there an `easy' way to reset the ID column to use
> BIGINT? I suspect I'll have to regenerate the models, somehow
> specifying the id, but I'm not sure.
>
> Thanks!
> jw
>
Jaime,
if piece_id is a primary key you can't do that in SQLObject.
I tried with Firebird to use BIGINT for pk's. No way!
Uwe

Actually I have some positive xp in that ;)
I`ve patch sqlobject:
1) In mysqlconnection.py I`ve replaced INT type with INT UNSIGNED type in
createIDColumn and joinSQLType functions.
2) In cols.py I`ve replaced INT with INT UNSIGNED in SOKeyCol._mysqlType.
It works fine for me, but I`m not shore if I miss something. And I`m sorry - I
can`t create patch for now. I think you can replace INT with BIGINT this way
and It should work fine (don`t forget to change type of ID in database).
On Thursday 30 March 2006 17:07, Uwe Grauer wrote:
> Jaime Wyant wrote:
> > Ok, I've created a sqlobject like so:
> >
> > class Output(SQLObject):
> > print_job = ForeignKey("PrintJob", notNone = True)
> > piece_id = IntCol(notNone = True)
> >
> > ...
> >
> > Currently I have about 1.7 million rows in there. SQLObject created the
> > `id' column for the table as `int'. MySQL's int has a value from
> >
> > |-2147483648| |to 2147483647. |I suspect that I'll be at the maximum
> >
> > INT value shortly. Is there an `easy' way to reset the ID column to use
> > BIGINT? I suspect I'll have to regenerate the models, somehow
> > specifying the id, but I'm not sure.
> >
> > Thanks!
> > jw
>
> Jaime,
>
> if piece_id is a primary key you can't do that in SQLObject.
> I tried with Firebird to use BIGINT for pk's. No way!
>
> Uwe
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting language
> that extends applications into web and mobile media. Attend the live
> webcast and join the prime developer group breaking into this new coding
> territory!
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
> _______________________________________________
> sqlobject-discuss mailing list
> sqlobject-discuss@...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Vetlugin Yury wrote:
> Actually I have some positive xp in that ;)
> I`ve patch sqlobject:
> 1) In mysqlconnection.py I`ve replaced INT type with INT UNSIGNED type in
> createIDColumn and joinSQLType functions.
> 2) In cols.py I`ve replaced INT with INT UNSIGNED in SOKeyCol._mysqlType.
>
> It works fine for me, but I`m not shore if I miss something. And I`m sorry - I
> can`t create patch for now. I think you can replace INT with BIGINT this way
> and It should work fine (don`t forget to change type of ID in database).
>
Yes, but thats a dirty hack!
I was told that there is no way in SQLObject to use pk's which aren't
INT's.
There is no way to specify what the PK-Type is.
Uwe
> On Thursday 30 March 2006 17:07, Uwe Grauer wrote:
>> Jaime Wyant wrote:
>>> Ok, I've created a sqlobject like so:
>>>
>>> class Output(SQLObject):
>>> print_job = ForeignKey("PrintJob", notNone = True)
>>> piece_id = IntCol(notNone = True)
>>>
>>> ...
>>>
>>> Currently I have about 1.7 million rows in there. SQLObject created the
>>> `id' column for the table as `int'. MySQL's int has a value from
>>>
>>> |-2147483648| |to 2147483647. |I suspect that I'll be at the maximum
>>>
>>> INT value shortly. Is there an `easy' way to reset the ID column to use
>>> BIGINT? I suspect I'll have to regenerate the models, somehow
>>> specifying the id, but I'm not sure.
>>>
>>> Thanks!
>>> jw
>> Jaime,
>>
>> if piece_id is a primary key you can't do that in SQLObject.
>> I tried with Firebird to use BIGINT for pk's. No way!
>>
>> Uwe
>>
>>
>> -------------------------------------------------------
>> This SF.Net email is sponsored by xPML, a groundbreaking scripting language
>> that extends applications into web and mobile media. Attend the live
>> webcast and join the prime developer group breaking into this new coding
>> territory!
>> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
>> _______________________________________________
>> sqlobject-discuss mailing list
>> sqlobject-discuss@...
>> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting language
> that extends applications into web and mobile media. Attend the live webcast
> and join the prime developer group breaking into this new coding territory!
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
> _______________________________________________
> sqlobject-discuss mailing list
> sqlobject-discuss@...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>

On Thu, Mar 30, 2006 at 04:17:22PM +0200, Uwe Grauer wrote:
> I was told that there is no way in SQLObject to use pk's which aren't
> INT's.
> There is no way to specify what the PK-Type is.
Currently primary keys can be of types int and str.
Oleg.
--
Oleg Broytmann http://phd.pp.ru/ phd@...
Programmers don't die, they just GOSUB without RETURN.

Jaime Wyant wrote:
> Currently I have about 1.7 million rows in there. SQLObject created
> the `id' column for the table as `int'. MySQL's int has a value from
> |-2147483648| |to 2147483647. |I suspect that I'll be at the maximum
> INT value shortly. Is there an `easy' way to reset the ID column to
> use BIGINT? I suspect I'll have to regenerate the models, somehow
> specifying the id, but I'm not sure.
You've used about 1/1200th of your PK ID space. Are you really
generating records at such a rate that this is a concern? If so, simply
switching to unsigned isn't going to buy you much time.
J

To put that in perspective a little, at a row consumption rate of 1
million a day it'll take approx 5.88 years to hit the limit.
Comfortably out of range you might think but I'd sure hate to be the
one to alter such a colossal table when the time came. And there are
tables out in web land that hit these kind of figures - take some of
the larger social networking sites for example. In a sizable system
where perhaps there are multiple web servers to handle the traffic
along with replication etc, it's likely that time until overflow will
be greatly reduced.
Being able to specify an ID of BIGINT (bigserial in Postgres) would
be one less thing to worry about.
Justin
On 1 Apr 2006, at 08:17, Jeremy Fitzhardinge wrote:
> Jaime Wyant wrote:
>> Currently I have about 1.7 million rows in there. SQLObject
>> created the `id' column for the table as `int'. MySQL's int has a
>> value from |-2147483648| |to 2147483647. |I suspect that I'll be
>> at the maximum INT value shortly. Is there an `easy' way to reset
>> the ID column to use BIGINT? I suspect I'll have to regenerate
>> the models, somehow specifying the id, but I'm not sure.
> You've used about 1/1200th of your PK ID space. Are you really
> generating records at such a rate that this is a concern? If so,
> simply switching to unsigned isn't going to buy you much time.
>
> J
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting
> language
> that extends applications into web and mobile media. Attend the
> live webcast
> and join the prime developer group breaking into this new coding
> territory!
> http://sel.as-us.falkag.net/sel?
> cmd=lnk&kid=110944&bid=241720&dat=121642
> _______________________________________________
> sqlobject-discuss mailing list
> sqlobject-discuss@...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

I'm not generating records that quickly, no.
In all honesty, I misread the width of the int. But I should've known it
was around 2 billion. Thanks for bringing me back down to earth. :)
jw
On 3/31/06, Jeremy Fitzhardinge <jeremy@...> wrote:
>
> Jaime Wyant wrote:
> > Currently I have about 1.7 million rows in there. SQLObject created
> > the `id' column for the table as `int'. MySQL's int has a value from
> > |-2147483648| |to 2147483647. |I suspect that I'll be at the maximum
> > INT value shortly. Is there an `easy' way to reset the ID column to
> > use BIGINT? I suspect I'll have to regenerate the models, somehow
> > specifying the id, but I'm not sure.
> You've used about 1/1200th of your PK ID space. Are you really
> generating records at such a rate that this is a concern? If so, simply
> switching to unsigned isn't going to buy you much time.
>
> J
>
--
"Government does not solve problems; it subsidizes them."
Ronald Reagan