[PonyORM-list] Oracle tablenames.

[Alexander Kozlovsky]

Hi Bjarni!

In order to get table name from the entity name, Pony ORM by default converts
entity name to upper case in Oracle, and to lower case in MySQL and
PostgreSQL:
http://doc.ponyorm.com/entities.html#mapping-customization

For example, if your entity called Customer, the corresponding table name
will be "CUSTOMER" in Oracle and "customer" in PostgreSQL and MySQL. This
way it is possible to use table name in queries without quotes.

It is very strange that your table names are lowercase in Oracle. It may be
that your entity definitions override default entity name and specify
lower-case name explicitly.

For example, if your entity is declared as:

    class MyEntity(db.Entity):
        a = Required(int)
        b = Optional(int)

then in Oracle the default table name will be "MYENTITY". But if you define
entity as:

    class MyEntity(db.Entity):
        _table_ = "my_table"
        a = Required(int)
        b = Optional(int)

then in Oracle the corresponding table name will be "my_table". If you want
to explicitly specify table name then you should specify upper-case name in
Oracle:

    class MyEntity(db.Entity):
        _table_ = "MY_TABLE"
        a = Required(int)
        b = Optional(int)

Do you use the "_table_" option in your entity definitions?

Regards,
Alexander



On Mon, Jul 28, 2014 at 3:17 PM, Bjarni Hákonarson <bjarni at prentvorur.is>
wrote:

> Hi all,
>
> and thank you authors for a great product.
>
> I come from a oracle-database background and I am relatively new to python
> and an absolute infant in pony.orm
>
> I am now in the midst of creating an application that uses existing Oracle
> database schema. I have been playing around with pony.orm and I think it
> would save me a ton of work if I could use that. I have already tested it
> towards a seperate schema and having it to create it own tables and
> everything works just great. However when I a ran it against a copy of the
> original schema it complaints it cannot find TABLE_SITE table.
>
> The problem is that pony.orm forces tablenames to lowercase. That in
> itself would be fine except that also forces you to use parenthesis around
> tablenames in Oracle queries.
>
> (see:
> http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements008.htm)
>
> The results are like this: (actual names changed)
>
> SQL> select * from tab;
>
> TNAME       TABTYPE CLUSTERID
> ------------------------------ ------- ----------
> CUSTOMERS_SITE       TABLE
> customers       TABLE
> persons        TABLE
> readings       TABLE
>
> SQL> select name from customers;
> select name from customers
>                  *
> ERROR at line 1:
> ORA-00942: table or view does not exist
>
> Of course using "select name from "customers"; would work fine. The
> problem is that it would mean code-change in other applications using the
> same schema and that doesn't work. An older application accessing the
> pony.orm created schema results in:
>
>   File
> "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/reflection.py",
> line 512, in reflecttable
>     raise exc.NoSuchTableError(table.name)
> sqlalchemy.exc.NoSuchTableError: sites
>
>
> Is there any way to override the behaviour of pony.orm when creating
> tables for Oracle?
>
> 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/20140728/0acce3af/attachment.html>