Am 26.04.2012 23:22, schrieb Michael Bayer: > > On Apr 26, 2012, at 5:11 PM, Cornelius Kölbel wrote: > > > Hello Michael, > > > thanks for the answer, thanks for sqlalchemy and thanks for the link. > > > Finally indeed we installed all packages via pip and at least the option > > problem was gone. > > > Unfortunately we experience another problem. > > Our model specification contains several sqlalchemy.types.UnicodeText() > > columns. > > They worked fine under mysql, postgres and sqlite, but we are > > experiencing the (logical) problem with oracle. (Now it is getting > > hard, since I am not the oracle expert). > > > So the tables get created with the columns -- specified as UnicodeText > > in the model -- as CLOB/NCLOB on oracle. Ok, that's the way it is > > described here > > http://docs.sqlalchemy.org/en/latest/core/types.html#sql-standard-types. > > > But this leads to an uncomfortable behaviour: since the behaviour with a > > mysql or postgres backend will be different to that of the oracle > > backend. While our application is able to search, sort and filter > > (where) those columns in mysql and postgres it will fail with oracle as > > a backend, since in oracle NCLOBs can not be searched, filtered or > > sorted, and we get the error message: inconsistent datatypes: expected - > > got NCLOB. > > > What I am aiming at is: an application with sqlalchemy will behave > > differently if the backend is either mysql (will work) or oracle (will > > break). > > Should the mapping of the unicodetext to the column data type have same > > effects on different databases? > > Bad thing: to my understanding oracle provides no unlimited searchable > > string/character data type. > > > SQLA can only approach backend-agnostic behavior to a certain degree. > The behavior of unlimited-size text fields stored using LOB > methodologies is one area where this behavior can't really continue; > there are great behavioral differences in these datatypes across many > backends, not just Oracle. > > in SQL we generally don't try comparing or sorting unlimited-size > BLOB, CLOB columns. Even on databases which support this, it is > vastly inefficient as you can't really index unlimited-size columns > reasonably. > > If you have columns which you need to use in filter criterion or > sorting, they should be VARCHAR on all backends. PG supports VARCHAR > of unlimited size, Oracle up to 4000 characters. Not sure about > MySQL, but in any case columns that are used in comparisons should be > of limited size. > > If you're doing full text searching, you should be using text > searching extensions for that - Oracle should have something available > in this area as well (according to this: > http://www.oracle.com/pls/db111/portal.portal_db?frame=&selected=7 it > was introduced in Oracle 11g). > > To do text searching in an agnostic way with SQLAlchemy can be > achieved with custom function and type constructs, once you have > decided what the equivalent operations will be across all the target > backends. > > > We probably would have been better off with using Unicode(4000) or something like that. We will do some thinking. Thanks a and kind regards Cornelius
signature.asc
Description: OpenPGP digital signature
