-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
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. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.11 (Darwin) Comment: GPGTools - http://gpgtools.org iQEcBAEBAgAGBQJPmbx9AAoJEDMCOcHE2v7hEIgH/3eWesu/GYk2zQttQvIrCxUp gYPWlL1HWbQzVsSzdabsiVm5TE3sUU4/UBv5WHqPvjzR4frG88Ylh/5y7WKU69OZ bwt5OoZ189FfyTBO9ewm0WGKR5QtM7JT5SreYscQFP4J1G7Lq8jzNMwrb7nouxae 4qYs3BcUfHYzdfbRZMGv+kIgA9TOf+RBYUVmwECvh3LjRrJDnVoy6a4Aquc0CBed TFG3TawQibykDr0FinPXU6umFKzXAtYYhJtdD02BqXMAIIZa2rpvXRgcGnqr8QYY HvztxJ+TOUH7Gdc0xHRkRNSuA/TXFGmDoRpFUum3TyC3lTd+a1zHQQaG8NoTnaI= =M5iT -----END PGP SIGNATURE----- -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
