Re: [SQLObject] Oracle BLOBs and SOBLobCol, and metaclass interaction

Ian Bicking =D0=C9=DB=C5=D4:
> Dmitry Cheryasov wrote:
>
>> Hello.
>>
>> BLOBs and CLOBs as Oracle provides them have quite different=20
>> semantics than strings. To accomodate this, I'd like to define an=20
>> Oracle-specific subclass of [SO]BLOBCol and probably [SO]StringCol.
>
>
> What's the difference between a BLOB and a CLOB? Do they act like=20
> BLOBs on other databases? It might make sense for CLOB columns=20
> (whatever they are) to be activated by an option on BLOBs that only=20
> Oracle pays attention to.
CLOB is a character type (like TEXT in postgres), BLOB is a binary type.
The main difference from 'normal' types is that actual retrieval of data =
(potentially quite huge) must be done explicitly, by a special call to a =
CLOB/BLOB object that the driver returns in the resultset. Whatever=20
retrieval discipline would be, I need a way to call that clob.read()=20
method to provide column value %)
> There's no way to lazily load columns right now; that would be a nice=20
> feature. I'd call it "lazyLoad" instead of "demand_load".
How much work (and where) would that take? I'm weighing my chances of=20
participating.
>> If yes, how?
>
> Thus the question: does SQLObject support the second approach, i.e.=20
> database-specific column types?
>
> For each column type there are SQL create statements specific to the=20
> database, so you overload those methods (e.g., oracleSQL()). Those=20
> really should be extracted so you don't have to edit col.py to do=20
> this, but they aren't yet.
Sorry, but I need to provide some specific *python* code for those pesky =
clob/blob columns :-\
>> What I probably need is the metaclass to retrieve right column=20
>> classes from the database adapter when a descendeant of SQLObject is=20
>> being created.
>
>
> Because the database backend is not necessarily known at the time the=20
> SQLObject class is created, this is not possible. But there's a=20
> couple ways to handle database-specific funkiness, so it should be=20
> possible somehow.
OK, I'll stick to database-specific column types for now, in hope that=20
the code could be reused in the future. Too bad I'll need to poison=20
col.py with utterly oracle-specific code.
--=20
WBR, Dmitry Cheryasov

Thread view

Hello.
BLOBs and CLOBs as Oracle provides them have quite different semantics
than strings. To accomodate this, I'd like to define an Oracle-specific
subclass of [SO]BLOBCol and probably [SO]StringCol.
Probably I could create Oracle-specific subclasses of those column
classes, and have user write smth like:
class Foo(SQLObject):
long_text = OracleClobCol() # farewell portability
picture = OracleBlobCol() # happy rewriting if another db is needed
Instead, I'd rather make it look more standard, like
class Foo(SQLObject):
long_text = StringCol() # no size implies CLOB
picture = BlobCol() # like any other blob column
big_picture = BlobCol(demand_load = True) # load the huge data on
actual read, not on fetch
Thus the question: does SQLObject support the second approach, i.e.
database-specific column types?
If yes, how?
What I probably need is the metaclass to retrieve right column classes
from the database adapter when a descendeant of SQLObject is being created.
--
WBR, Dmitry Cheryasov

Dmitry Cheryasov wrote:
> Hello.
>
> BLOBs and CLOBs as Oracle provides them have quite different semantics
> than strings. To accomodate this, I'd like to define an Oracle-specific
> subclass of [SO]BLOBCol and probably [SO]StringCol.
What's the difference between a BLOB and a CLOB? Do they act like BLOBs
on other databases? It might make sense for CLOB columns (whatever they
are) to be activated by an option on BLOBs that only Oracle pays
attention to.
> Probably I could create Oracle-specific subclasses of those column
> classes, and have user write smth like:
> class Foo(SQLObject):
> long_text = OracleClobCol() # farewell portability
> picture = OracleBlobCol() # happy rewriting if another db is needed
>
> Instead, I'd rather make it look more standard, like
> class Foo(SQLObject):
> long_text = StringCol() # no size implies CLOB
> picture = BlobCol() # like any other blob column
> big_picture = BlobCol(demand_load = True) # load the huge data on
> actual read, not on fetch
Yes, that seems right to me; if Oracle's CLOB is like unsized strings
("TEXT") on other databases, then it should be defined in the same way.
There's no way to lazily load columns right now; that would be a nice
feature. I'd call it "lazyLoad" instead of "demand_load".
> Thus the question: does SQLObject support the second approach, i.e.
> database-specific column types?
> If yes, how?
For each column type there are SQL create statements specific to the
database, so you overload those methods (e.g., oracleSQL()). Those
really should be extracted so you don't have to edit col.py to do this,
but they aren't yet.
> What I probably need is the metaclass to retrieve right column classes
> from the database adapter when a descendeant of SQLObject is being created.
Because the database backend is not necessarily known at the time the
SQLObject class is created, this is not possible. But there's a couple
ways to handle database-specific funkiness, so it should be possible
somehow.
--
Ian Bicking / ianb@... / http://blog.ianbicking.org

Ian Bicking =D0=C9=DB=C5=D4:
> Dmitry Cheryasov wrote:
>
>> Hello.
>>
>> BLOBs and CLOBs as Oracle provides them have quite different=20
>> semantics than strings. To accomodate this, I'd like to define an=20
>> Oracle-specific subclass of [SO]BLOBCol and probably [SO]StringCol.
>
>
> What's the difference between a BLOB and a CLOB? Do they act like=20
> BLOBs on other databases? It might make sense for CLOB columns=20
> (whatever they are) to be activated by an option on BLOBs that only=20
> Oracle pays attention to.
CLOB is a character type (like TEXT in postgres), BLOB is a binary type.
The main difference from 'normal' types is that actual retrieval of data =
(potentially quite huge) must be done explicitly, by a special call to a =
CLOB/BLOB object that the driver returns in the resultset. Whatever=20
retrieval discipline would be, I need a way to call that clob.read()=20
method to provide column value %)
> There's no way to lazily load columns right now; that would be a nice=20
> feature. I'd call it "lazyLoad" instead of "demand_load".
How much work (and where) would that take? I'm weighing my chances of=20
participating.
>> If yes, how?
>
> Thus the question: does SQLObject support the second approach, i.e.=20
> database-specific column types?
>
> For each column type there are SQL create statements specific to the=20
> database, so you overload those methods (e.g., oracleSQL()). Those=20
> really should be extracted so you don't have to edit col.py to do=20
> this, but they aren't yet.
Sorry, but I need to provide some specific *python* code for those pesky =
clob/blob columns :-\
>> What I probably need is the metaclass to retrieve right column=20
>> classes from the database adapter when a descendeant of SQLObject is=20
>> being created.
>
>
> Because the database backend is not necessarily known at the time the=20
> SQLObject class is created, this is not possible. But there's a=20
> couple ways to handle database-specific funkiness, so it should be=20
> possible somehow.
OK, I'll stick to database-specific column types for now, in hope that=20
the code could be reused in the future. Too bad I'll need to poison=20
col.py with utterly oracle-specific code.
--=20
WBR, Dmitry Cheryasov

Dmitry Cheryasov wrote:
> Ian Bicking =D0=C9=DB=C5=D4:
>=20
>> Dmitry Cheryasov wrote:
>>
>>> Hello.
>>>
>>> BLOBs and CLOBs as Oracle provides them have quite different=20
>>> semantics than strings. To accomodate this, I'd like to define an=20
>>> Oracle-specific subclass of [SO]BLOBCol and probably [SO]StringCol.
>>
>>
>>
>> What's the difference between a BLOB and a CLOB? Do they act like=20
>> BLOBs on other databases? It might make sense for CLOB columns=20
>> (whatever they are) to be activated by an option on BLOBs that only=20
>> Oracle pays attention to.
>=20
>=20
> CLOB is a character type (like TEXT in postgres), BLOB is a binary type.
> The main difference from 'normal' types is that actual retrieval of dat=
a=20
> (potentially quite huge) must be done explicitly, by a special call to =
a=20
> CLOB/BLOB object that the driver returns in the resultset. Whatever=20
> retrieval discipline would be, I need a way to call that clob.read()=20
> method to provide column value %)
>=20
>> There's no way to lazily load columns right now; that would be a nice=20
>> feature. I'd call it "lazyLoad" instead of "demand_load".
>=20
>=20
> How much work (and where) would that take? I'm weighing my chances of=20
> participating.
Well, from what you say, it's really not necessary then -- SQLObject=20
will return the special object, and you can choose to read it or not.=20
This won't be portable across databases (other databases don't return=20
objects you read from), and implies that it should be a different column=20
type specific to Oracle.
Generally doing per-column lazy loading wouldn't be a lot of work, but=20
it's deep in SQLObject, so it's probably not a good place to get started.
If you wanted to just transparently load the data, regardless of whether=20
the users wants to use it, you could add a converter for the Oracle BLOB=20
type. This would be done with the SOCol.createValidators() method.
For inserting BLOBs, I'm not sure; I get the impression that requires=20
database parameters to do on Oracle, that there's no literal syntax that=20
can be put in SQL. Oleg is still working database parameter support in=20
a branch.
>>> If yes, how?
>>
>>
>> Thus the question: does SQLObject support the second approach, i.e.=20
>> database-specific column types?
>>
>> For each column type there are SQL create statements specific to the=20
>> database, so you overload those methods (e.g., oracleSQL()). Those=20
>> really should be extracted so you don't have to edit col.py to do=20
>> this, but they aren't yet.
>=20
>=20
> Sorry, but I need to provide some specific *python* code for those pesk=
y=20
> clob/blob columns :-\
If you look at col.SOBLOBCol, you'll notice it has a _postgresType,=20
_mysqlType, and a _mssqlType. You'd add a _oracleType, and also that=20
method to SOCol, SOStringCol, etc.
>>> What I probably need is the metaclass to retrieve right column=20
>>> classes from the database adapter when a descendeant of SQLObject is=20
>>> being created.
>>
>>
>>
>> Because the database backend is not necessarily known at the time the=20
>> SQLObject class is created, this is not possible. But there's a=20
>> couple ways to handle database-specific funkiness, so it should be=20
>> possible somehow.
>=20
>=20
> OK, I'll stick to database-specific column types for now, in hope that=20
> the code could be reused in the future. Too bad I'll need to poison=20
> col.py with utterly oracle-specific code.
Yeah, that's just the way it works now for all databases, so don't worry=20
too much about it. Are you working off the trunk, the 0.6.1-oracle=20
branch, or...? I'd like to keep all the Oracle work synchronized=20
between the people doing it.
--=20
Ian Bicking / ianb@... / http://blog.ianbicking.org

Ian, Dmitry:
I hadn't worked on Oracle support for a while and hadn't posted any=
=20
code. Dmitry started working on Oracle support from scratch. If=
=20
Dmitry's code started with 0.7, it might make sense for Dmitry to sta=
rt=20
a new branch (0.7-oracle) and ignore the 0.6.1-oracle I imported (or=
=20
take a look at it if it helps).
Matthew
Ian Bicking wrote:
> Dmitry Cheryasov wrote:
>
>> Ian Bicking =D0=C9=DB=C5=D4:
>>
>>> Dmitry Cheryasov wrote:
>>>
>>>> Hello.
>>>>
>>>> BLOBs and CLOBs as Oracle provides them have quite different=
=20
>>>> semantics than strings. To accomodate this, I'd like to define a=
n=20
>>>> Oracle-specific subclass of [SO]BLOBCol and probably [SO]StringC=
ol.
>>>
>>>
>>>
>>>
>>> What's the difference between a BLOB and a CLOB? Do they act lik=
e=20
>>> BLOBs on other databases? It might make sense for CLOB columns=
=20
>>> (whatever they are) to be activated by an option on BLOBs that on=
ly=20
>>> Oracle pays attention to.
>>
>>
>>
>> CLOB is a character type (like TEXT in postgres), BLOB is a binary=
type.
>> The main difference from 'normal' types is that actual retrieval o=
f=20
>> data (potentially quite huge) must be done explicitly, by a specia=
l=20
>> call to a CLOB/BLOB object that the driver returns in the resultse=
t.=20
>> Whatever retrieval discipline would be, I need a way to call that=
=20
>> clob.read() method to provide column value %)
>>
>>> There's no way to lazily load columns right now; that would be a=
=20
>>> nice feature. I'd call it "lazyLoad" instead of "demand_load".
>>
>>
>>
>> How much work (and where) would that take? I'm weighing my chances=
of=20
>> participating.
>
>
> Well, from what you say, it's really not necessary then -- SQLObjec=
t=20
> will return the special object, and you can choose to read it or no=
t.=20
> This won't be portable across databases (other databases don't retu=
rn=20
> objects you read from), and implies that it should be a different=
=20
> column type specific to Oracle.
>
> Generally doing per-column lazy loading wouldn't be a lot of work, =
but=20
> it's deep in SQLObject, so it's probably not a good place to get st=
arted.
>
> If you wanted to just transparently load the data, regardless of=
=20
> whether the users wants to use it, you could add a converter for th=
e=20
> Oracle BLOB type. This would be done with the=20
> SOCol.createValidators() method.
>
> For inserting BLOBs, I'm not sure; I get the impression that requir=
es=20
> database parameters to do on Oracle, that there's no literal syntax=
=20
> that can be put in SQL. Oleg is still working database parameter=
=20
> support in a branch.
>
>>>> If yes, how?
>>>
>>>
>>>
>>> Thus the question: does SQLObject support the second approach, i.=
e.=20
>>> database-specific column types?
>>>
>>> For each column type there are SQL create statements specific to =
the=20
>>> database, so you overload those methods (e.g., oracleSQL()). Tho=
se=20
>>> really should be extracted so you don't have to edit col.py to do=
=20
>>> this, but they aren't yet.
>>
>>
>>
>> Sorry, but I need to provide some specific *python* code for those=
=20
>> pesky clob/blob columns :-\
>
>
> If you look at col.SOBLOBCol, you'll notice it has a _postgresType,=
=20
> _mysqlType, and a _mssqlType. You'd add a _oracleType, and also th=
at=20
> method to SOCol, SOStringCol, etc.
>
>>>> What I probably need is the metaclass to retrieve right column=
=20
>>>> classes from the database adapter when a descendeant of SQLObjec=
t=20
>>>> is being created.
>>>
>>>
>>>
>>>
>>> Because the database backend is not necessarily known at the time=
=20
>>> the SQLObject class is created, this is not possible. But there'=
s a=20
>>> couple ways to handle database-specific funkiness, so it should b=
e=20
>>> possible somehow.
>>
>>
>>
>> OK, I'll stick to database-specific column types for now, in hope=
=20
>> that the code could be reused in the future. Too bad I'll need to=
=20
>> poison col.py with utterly oracle-specific code.
>
>
> Yeah, that's just the way it works now for all databases, so don't=
=20
> worry too much about it. Are you working off the trunk, the=20
> 0.6.1-oracle branch, or...? I'd like to keep all the Oracle work=
=20
> synchronized between the people doing it.
>
>
--=20
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% Matthew Cahn
% Principal Systems Analyst
% Bristol-Myers Squibb Company
% Mailbox code: H23-05
% P.O. Box 4000
% Princeton, NJ 08543-4000
%
% Phone: (609) 252-3477
% Fax: (609) 252-6030
% Email: Matthew.Cahn@...
% Wireless email: matthew_cahn@...
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%