[PonyORM-list] Oracle tablenames.

[Alexander Kozlovsky]

The thing is that SQLAlchemy and Pony ORM have different approaches to
user-specified table names.

SQLAlchemy treats all-lowercase table names as 'case-insensitive' and
converts such names in Oracle to all-uppercase. This has the benefit of
portability, that is, if a programmer in SQLAlchemy specifies __tablename__
= 'lowercase_name' then this name will be treated as case-insensitive
disregarding of the current database. But the downside of such approach is
that you cannot specify real all-lowercase table name in Oracle, because
such name will be always converted to upper-case.

Because of this SQLAlchemy peculiarity, sqlacodegen returns table name as
all-lowercase, whereas in reality it is all-uppercase.

In contrast, Pony ORM doesn't do any mangling with the explicitly specified
table names. You write it in lowercase, you get lowercase. Such table name
will not be cross-database portable in terms of case insensitivity, but
this gives the full control to programmer. But in the absence of the
explicit table name declaration Pony ORM constructs table name from the
entity name, and such name is constructed as case-insensitive
(all-lowercase in MySQL and PostgreSQL, and all-uppercase in Oracle).

> I used sqlacodengen and ran it towards the database schema (yes I am
lazy) .
> It produces the classes automatically but with  SQLAlchemy syntax.
> I then wrote a small script to translate the syntax to pony.orm'ish.

I think that your script should do the following transformation of table
name generated by sqlacodegen: If table name is all-lowercase and the
database is Oracle, then table name should be converted to all-uppercase.

> I will get around to test this better soon but I wish sqlcodegen had
"--pony.orm" arg. It wold make life so much easier . :-)

Thanks for the suggestion, we'll try to make pool request to sqlacodegen
for supporting Pony ORM syntax, or write similar tool for Pony ORM by
ourselves.

Regards,
Alexander Kozlovsky



On Tue, Jul 29, 2014 at 8:58 AM, Bjarni Hákonarson <bjarni at prentvorur.is>
wrote:

> Thanks for the reply.
>
> I am sure I violated bunch of rules in this approach but "users are
> stupid" is in my opinion the first rule in software development.
>
> Full disclosure:
>
> What I did was this.
>
> I used sqlacodengen and ran it towards the database schema (yes I am lazy)
> .
> It produces the classes automatically but with  SQLAlchemy syntax.
>
> I then wrote a small script to translate the syntax to pony.orm'ish.
>
> The result is as an example below.
>
> class Customer(db.Entity):
>     _table_ = 'customers'
>
>     pk = PrimaryKey(int, auto=True)
>     name = Optional(unicode)
>     natid = Required(int)
>     address = Optional(unicode)
>     address2 = Optional(unicode)
>     state = Optional(unicode)
>     zip = Optional(unicode)
>     city = Optional(unicode)
>     phone = Optional(int)
>     billing_contact = Optional(unicode)
>     sysadmin = Optional(unicode)
>     active = Optional(int, default=1)
>     site = Set("Sites")
>     person = Set("Persons")
>
> I generally prefer to create my database schema myself using  naming
> conventions like PK for Primary keys and fk_<table>
> as the first columns if applicable. Create sequences and insert triggers
> because so many things come free that way.
>
> This time I finished writing the app and used cx_Oracle direct with the
> associating, painstaking finger work.
>
> I will get around to test this better soon but I wish sqlcodegen had
> "--pony.orm" arg. It wold make life so much easier . :-)
>
> Kind regards,
> Bjarni
>
>
> _______________________________________________
> ponyorm-list mailing list
> ponyorm-list at ponyorm.com
> /ponyorm-list
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: </ponyorm-list/attachments/20140729/c0518ec8/attachment.html>