sqlobject-discuss

[SQLObject] how to get generator number

[<pet...@tp...>]

Hi,
I would like to use IDs generated by generator in my application. I am using
Firebird DB.

Now I go like:

genVal = connection.queryOne("SELECT GEN_ID( GEN_PERSON, 1 ) FROM
RDB$DATABASE")

or for Firebird 2.0

genVal = connection.queryOne("SELECT NEXT VALUE FOR GEN_PERSON FROM
RDB$DATABASE")

Is there some other (more Pythonic) way how to get generator value?

Thanks for your reply

Petr Jakes

Re: [SQLObject] how to get generator number

[Oleg B. <ph...@ph...>]

On Wed, Nov 07, 2007 at 11:30:13PM +0100, Petr Jake?? wrote:
> I would like to use IDs generated by generator in my application.

   SQLObject either allows the user to generate an ID (outside of
SQLObject) or delegates the job of generating an ID to the connection. If
you want to generate an ID at the backend and pass it to INSERT queries you
can either run the queries to generate an ID (it's what you are doing now)
or create your own connection class and override _queryInsertID() method.
See how the method works for Postgres (pgconnection.py).

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] how to get generator number

[<pet...@tp...>]

Pgo+ID4gSSB3b3VsZCBsaWtlIHRvIHVzZSBJRHMgZ2VuZXJhdGVkIGJ5IGdlbmVyYXRvciBpbiBt
eSBhcHBsaWNhdGlvbi4KPgo+ICAgU1FMT2JqZWN0IGVpdGhlciBhbGxvd3MgdGhlIHVzZXIgdG8g
Z2VuZXJhdGUgYW4gSUQgKG91dHNpZGUgb2YKPiBTUUxPYmplY3QpIG9yIGRlbGVnYXRlcyB0aGUg
am9iIG9mIGdlbmVyYXRpbmcgYW4gSUQgdG8gdGhlIGNvbm5lY3Rpb24uIElmCj4geW91IHdhbnQg
dG8gZ2VuZXJhdGUgYW4gSUQgYXQgdGhlIGJhY2tlbmQgYW5kIHBhc3MgaXQgdG8gSU5TRVJUIHF1
ZXJpZXMKPiB5b3UKPiBjYW4gZWl0aGVyIHJ1biB0aGUgcXVlcmllcyB0byBnZW5lcmF0ZSBhbiBJ
RCAoaXQncyB3aGF0IHlvdSBhcmUgZG9pbmcgbm93KQo+IG9yIGNyZWF0ZSB5b3VyIG93biBjb25u
ZWN0aW9uIGNsYXNzIGFuZCBvdmVycmlkZSBfcXVlcnlJbnNlcnRJRCgpIG1ldGhvZC4KPiBTZWUg
aG93IHRoZSBtZXRob2Qgd29ya3MgZm9yIFBvc3RncmVzIChwZ2Nvbm5lY3Rpb24ucHkpLgoKVGhh
bmtzIGZvciBxdWljayByZXBseSBPbGVnLApZZXMsIEkgd291bGQgbGlrZSB0byBwYXNzIGFuIElE
IHRvIHRoZSBJTlNFUlQgc3RhdGVtZW5zIChhcyBhIHByaW1hcnkga2V5CmZvciBtYXN0ZXIgcmVj
b3JkcyBhbmQgZm9yZWlnbiBrZXkgZm9yIGRldGFpbCByZWNvcmRzKQpUaWxsIG5vdyBJIGFtIHF1
aXRlIGhhcHB5IHdpaHQ6CmdlblZhbE1hc3RlciA9IGNvbm5lY3Rpb24ucXVlcnlPbmUoIlNFTEVD
VCBHRU5fSUQoIEdFTl9QRVJTT04sIDEgKSBGUk9NClJEQiREQVRBQkFTRSIpCmdlblZhbERldGFp
bCA9IGNvbm5lY3Rpb24ucXVlcnlPbmUoIlNFTEVDVCBHRU5fSUQoIEdFTl9QRVJTT04sIDEgKSBG
Uk9NClJEQiREQVRBQkFTRSIpCkkganVzdCB3b25kZXJlZCBpZiB0aGVyZSBpcyBhIG1ldGhvZCB3
aXRoaW4gdGhlIFNRTE9iamVjdCB0byBkbyB0aGlzIGpvYi4KUmVnYXJkcwpQZXRyIEpha2VzCgoK
Cj4KPiBPbGVnLgo+IC0tCj4gICAgIE9sZWcgQnJveXRtYW5uICAgICAgICAgICAgaHR0cDovL3Bo
ZC5wcC5ydS8gICAgICAgICAgICBwaGRAcGhkLnBwLnJ1Cj4gICAgICAgICAgIFByb2dyYW1tZXJz
IGRvbid0IGRpZSwgdGhleSBqdXN0IEdPU1VCIHdpdGhvdXQgUkVUVVJOLgo+Cj4gLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLQo+IFRoaXMgU0YubmV0IGVtYWlsIGlzIHNwb25zb3JlZCBieTogU3BsdW5rIEluYy4K
PiBTdGlsbCBncmVwcGluZyB0aHJvdWdoIGxvZyBmaWxlcyB0byBmaW5kIHByb2JsZW1zPyAgU3Rv
cC4KPiBOb3cgU2VhcmNoIGxvZyBldmVudHMgYW5kIGNvbmZpZ3VyYXRpb24gZmlsZXMgdXNpbmcg
QUpBWCBhbmQgYSBicm93c2VyLgo+IERvd25sb2FkIHlvdXIgRlJFRSBjb3B5IG9mIFNwbHVuayBu
b3cgPj4gaHR0cDovL2dldC5zcGx1bmsuY29tLwo+IF9fX19fX19fX19fX19fX19fX19fX19fX19f
X19fX19fX19fX19fX19fX19fX19fCj4gc3Fsb2JqZWN0LWRpc2N1c3MgbWFpbGluZyBsaXN0Cj4g
c3Fsb2JqZWN0LWRpc2N1c3NAbGlzdHMuc291cmNlZm9yZ2UubmV0Cj4gaHR0cHM6Ly9saXN0cy5z
b3VyY2Vmb3JnZS5uZXQvbGlzdHMvbGlzdGluZm8vc3Fsb2JqZWN0LWRpc2N1c3MKPgoKCgotLSAK
SW5nLiBQZXRyIEpha2XFoQpUUEMgc3BvbC4gcyByLm8uClByxa9teXNsb3bDoSAxMTM3CjUwNiAw
MSAgSmnEjcOtbgpDemVjaCByZXB1YmxpYywgRXVyb3BlCnd3dy50cGMuY3oKbW9iaWxlOiArNDIw
LTc3Ny0xNzI0NTAK

Re: [SQLObject] how to get generator number

[Oleg B. <ph...@ph...>]

On Wed, Nov 07, 2007 at 11:51:04PM +0100, Petr Jake?? wrote:
> I just wondered if there is a method within the SQLObject to do this job.

   There is, but it is rather complex. You have to create your own
connection class whose _queryInsertID() will do the job internally.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] how to get generator number

[<pet...@tp...>]

On Nov 7, 2007 11:53 PM, Oleg Broytmann <ph...@ph...> wrote:

> On Wed, Nov 07, 2007 at 11:51:04PM +0100, Petr Jake?? wrote:
> > I just wondered if there is a method within the SQLObject to do this
> job.
>
>   There is, but it is rather complex. You have to create your own
> connection class whose _queryInsertID() will do the job internally.


Yes, but AFAIK it is necessary to have generator named GEN_<tablename> for
each table. I would like to use one generator only for all tables (I would
like to have every ID in the database unique).

Petr

>
>
> Oleg.
> --
>     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
>           Programmers don't die, they just GOSUB without RETURN.
>

Re: [SQLObject] how to get generator number

[Oleg B. <ph...@ph...>]

On Thu, Nov 08, 2007 at 12:10:39AM +0100, Petr Jake?? wrote:
> Yes, but AFAIK it is necessary to have generator named GEN_<tablename> for
> each table.

   Thank you for reminding. As I don't know Firebird I didn't look into
firebirdconnection.py. Now I just looked and found its _queryInsertID()
really uses generators. It construct the name of the generators using the
name of the table: 'GEN_%s' % table, but it allows the user to override it:

class MyTable(SQLObject):
   class sqlmeta:
      idSequence = "MyGEN"

> I would like to use one generator only for all tables (I would
> like to have every ID in the database unique).

   So you have to add sqlmeta and idSequence to all your tables. If that
idSequence is one for all tables you can create the parent sqlmeta and
inherit that fixed name everywhere:

class gen_sqlmeta:
   idSequence = "MyGEN"

class MyTable(SQLObject):
   class sqlmeta(gen_sqlmeta):
      pass

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] how to get generator number

[<pet...@tp...>]

On Nov 8, 2007 10:57 AM, Oleg Broytmann <ph...@ph...> wrote:

> On Thu, Nov 08, 2007 at 12:10:39AM +0100, Petr Jake?? wrote:
> It construct the name of the generators using the
> name of the table: 'GEN_%s' % table, but it allows the user to override
> it:
>
> class MyTable(SQLObject):
>   class sqlmeta:
>      idSequence = "MyGEN"
>
> > I would like to use one generator only for all tables (I would
> > like to have every ID in the database unique).
>
>   So you have to add sqlmeta and idSequence to all your tables. If that
> idSequence is one for all tables you can create the parent sqlmeta and
> inherit that fixed name everywhere:
>
> class gen_sqlmeta:
>   idSequence = "MyGEN"
>
> class MyTable(SQLObject):
>   class sqlmeta(gen_sqlmeta):
>      pass

Thanks, it looks promising.

Anyway I thing I have to catch the idSequence which is used as a primary key
in the master tabel record (row)  because I need to insert this value to the
detail table(s) as foreign key(s). Or am I missing or overlooking something?

Petr Jakes

Re: [SQLObject] how to get generator number

[Oleg B. <ph...@ph...>]

On Thu, Nov 08, 2007 at 11:29:01AM +0100, Petr Jake?? wrote:
> Anyway I thing I have to catch the idSequence which is used as a primary key
> in the master tabel record (row)  because I need to insert this value to the
> detail table(s) as foreign key(s). Or am I missing or overlooking something?

   After the row has been inserted into the master table the generated
value becomes its ID, so you only need to get .id and use it as the foreign
key.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] how to get generator number

[<pet...@tp...>]

T24gTm92IDgsIDIwMDcgMTE6MzMgQU0sIE9sZWcgQnJveXRtYW5uIDxwaGRAcGhkLnBwLnJ1PiB3
cm90ZToKCj4gT24gVGh1LCBOb3YgMDgsIDIwMDcgYXQgMTE6Mjk6MDFBTSArMDEwMCwgUGV0ciBK
YWtlPz8gd3JvdGU6Cj4gPiBBbnl3YXkgSSB0aGluZyBJIGhhdmUgdG8gY2F0Y2ggdGhlIGlkU2Vx
dWVuY2Ugd2hpY2ggaXMgdXNlZCBhcyBhIHByaW1hcnkKPiBrZXkKPiA+IGluIHRoZSBtYXN0ZXIg
dGFiZWwgcmVjb3JkIChyb3cpICBiZWNhdXNlIEkgbmVlZCB0byBpbnNlcnQgdGhpcyB2YWx1ZSB0
bwo+IHRoZQo+ID4gZGV0YWlsIHRhYmxlKHMpIGFzIGZvcmVpZ24ga2V5KHMpLiBPciBhbSBJIG1p
c3Npbmcgb3Igb3Zlcmxvb2tpbmcKPiBzb21ldGhpbmc/Cj4KPiAgIEFmdGVyIHRoZSByb3cgaGFz
IGJlZW4gaW5zZXJ0ZWQgaW50byB0aGUgbWFzdGVyIHRhYmxlIHRoZSBnZW5lcmF0ZWQKPiB2YWx1
ZSBiZWNvbWVzIGl0cyBJRCwgc28geW91IG9ubHkgbmVlZCB0byBnZXQgLmlkIGFuZCB1c2UgaXQg
YXMgdGhlCj4gZm9yZWlnbgo+IGtleS4KCgpZZXMsICBJIGNhbiBpbWFnaW5lIHlvdXIgc2NlbmFy
aW8gd2hlbiBJIGFtIHdyaXRuZyB0aGUgbWFzdGVyIHJlY29yZCBhbmQgSQpkbyBub3Qga25vdyBk
ZXRhaWwgcmVjb3JkKHMpIHZhbHVlcy4gV2hlbiBJIGtub3cgYm90aCwgbWFzdGVyIHJlY29yZCB2
YWx1ZXMKQU5EIGRldGFpbCByZWNvcmQocykgdmFsdWVzIGluIHRoZSB0aW1lIG9mIGluc2VydGlu
ZyBtYXN0ZXIgcmVjb3JkLCBJIHRoaW5nCml0IGlzICJsZXNzIGV4cGVuc2l2ZSIgdG8gaGF2ZSBp
ZCBpbiB0aGUgbWVtb3J5IGFuZCB1c2UgaXQgdGhpcyB3YXksIHRoYW4KImdldCAuaWQiIGZvciBl
YWNoIGRldGFpbCByZWNvcmQuCgpCdXQgYW55d2F5LCB0aGFuayB5b3VyIHZlcnkgbXVjaCBmb3Ig
c2hvd2luZyBtZSAoYW5kIEkgaG9wZSBvdGhlcnMgaW4gdGhpcwpsaXN0IGFzIHdlbGwpIHdoaWNo
IHdheSB0byBnby4KClBldHIgSmFrZXMKCgo+Cj4gT2xlZy4KPiAtLQo+ICAgICBPbGVnIEJyb3l0
bWFubiAgICAgICAgICAgIGh0dHA6Ly9waGQucHAucnUvICAgICAgICAgICAgcGhkQHBoZC5wcC5y
dQo+ICAgICAgICAgICBQcm9ncmFtbWVycyBkb24ndCBkaWUsIHRoZXkganVzdCBHT1NVQiB3aXRo
b3V0IFJFVFVSTi4KPgoKCgotLSAKSW5nLiBQZXRyIEpha2XFoQpUUEMgc3BvbC4gcyByLm8uClBy
xa9teXNsb3bDoSAxMTM3CjUwNiAwMSAgSmnEjcOtbgpDemVjaCByZXB1YmxpYywgRXVyb3BlCnd3
dy50cGMuY3oKbW9iaWxlOiArNDIwLTc3Ny0xNzI0NTAK