pydal rname postgres no capital letter and pydal complain about table not be create

[Richard]

Hi,

I am using pydal for another project... The database get created by Django and we use the rname feature in both Django (db_column = rname) and pydal... With mssql4 adapter and mssql server entities get created correctly with capital letter camel case... But with postgres psycopg2 pydal complain that "tablename" is not there even if fake migrate succeed in generating table pydal memory file so pydal is able to connect... But once I try to use the database to insert things pydal complain that entity is not defined with entity small caps names... I had noticed that when I create database schema with pydal it create entity with small caps letters...

So I wonder if there isn't an issue with pydal and postgres adapter code in particular that lead to this situation??

I use pydal version 17.11 under ubuntu 16.04

Thanks for help

I am a bit in the hurry with this project...

Richard

[Richard]

I do use it with python 3.5.2

[Richard Vézina]

UP

--
-- mail from:GoogleGroups "web2py-developers" mailing list
make speech: web2py-d...@googlegroups.com
unsubscribe: web2py-develop...@googlegroups.com
details : http://groups.google.com/group/web2py-developers
the project: http://code.google.com/p/web2py/
official : http://www.web2py.com/
---
You received this message because you are subscribed to the Google Groups "web2py-developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py-develop...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Dave S]

On Wednesday, January 16, 2019 at 6:34:19 AM UTC-8, Richard wrote:

UP

This probably belongs in the web2py-users group, and you may want to file a ticket on GitHub.

<URL:https://groups.google.com/forum/#!forum/web2py>
<URL:https://github.com/web2py/web2py/issues>

A post in this group would be focused on a candidate fix.

/dps

[Richard Vézina]

I asked here because usually I want to assert be for filling a bug report not to create a false positive and also maybe have a faster fix as it might ring a bell to core devs where to look at... It really appear to me to be a bug though as I double tested it and entity get created properly at least in MSSQL, I didn't tested with SQLite though... And even if it get created with CamelCase properly in MSSQL once you start using the DAL to insert stuff in it complains about missing "lower case" name entity... So there is maybe more then one issue involve, I am not sure where to dig and didn't have much to do the digging so far...

Thanks

Richard

[Richard Vézina]

SQLite get created camel case properly... And work with importing data work correctly...

I have looked into that more deeper and I think that there's might be different things that are going on that trigger this issue...

Something I didn't know as I had never used Camel Case in backend name before is that Postgres being compliant to SQL Standard is not supporting lower or UPPER case except if you double quotes the entities and fields names :

https://stackoverflow.com/questions/20878932/are-postgresql-column-names-case-sensitive

That's probably what does MSSQL too as bracket are used as double quote within MSSQL...

So the things with pydal is that there is a couple of switches to unlock Camel Case usage in backend entity/field name... Which are :

ignore_field_case=False,
entity_quoting=True

That when used together at connection string level allow usage of Camel Case...

Although this is not working when you add rname into the mix with Postgres only as far as I can see... So, either rname is not intent to be tempered with once you set something there's it stick as is in the backend which is not the case for Postgres which seems a "bug" or because of the Postgres being pecular in how it needs to be used to use case sensitive character it makes rname API kind of impossible to be working consistenly between Postgres and other backends... I will have a look at if we couldn't just specify that if rname are used with Postgres that table being double quotes as this just might been overlooked by devs...

Stay tune, while I get back if no one else can get it to work before me...

Richard

[Richard Vézina]

Some more digging and related issue : https://github.com/web2py/pydal/issues/491

So it was possible back in time to have case sensitive table/field names apparently... It not clear how it was playing and if it was supported with "rname" though...

[Richard Vézina]

Try pydal version 16.11 rname upper case character supported with sqlite and not with postgres even back then... This was before major changes made by Nextghost that had touch things related and rname...

https://github.com/web2py/pydal/commits?author=nextghost

[Niphlod]

Don't know where it stands now, but back in the day rname was supposed to "decouple" completely the name of the underlying "supporting" object (table or column on the backend) from the name of the "supported" object (DAL.Table or DAL.Field). This is because DAL is lovely but suffers from some limitation when you can't embrace the naming convention we're all used to.
To put it in another words, it was created to make sure cornercases were covered when you were managing backend structures pre-created. By extension it also meant that what you passed to rname was the already quoted literal representation of what the backend calls that object, because "no one should tamper with rname if it is explicitely set". In the original vision of rname, casing and quoting logic did NOT apply any transformation to rname. Casing and quoting could ONLY change fields and table name logic when no rname was explicitely set.

[Richard Vézina]

I got you!

And rname seems to work according to what you say except in case of Postgres because I guess it more strictly adherence to SQL standard or because the rname has to take into account thie peculiar behavior or postgres where you need to double quote table/fields name to get proper case sensitive names...

I would say that the rname feature should out of the box pass to the adapter or driver, it not clear to me which one or if both, the entity_quoting=True and ignore_field_case=False flags so the API work consistently for all the backend... Meaning entity_quoting=True and ignore_field_case=False are only intent to be used to "tamper" with pydal table/field name the one use in the backend and the python code so they get properly translate with the proper casing to the backend database.

I had look at the code last night I can't figure it out where I should pass the quoting and case flag to the rname feature so for postgres specifically we resolve the actual issue...

There might also be a MSSQL specific issue because I recall that even if pydal were creating model with proper cases in SQL server it was throwing error once you try to insert stuff complaining that entity in lower cases wasn't exist, but I am not sure with all the tests I had been doing...

Thanks a lot for your insight Simone, I miss seeing you around :D

Richard

On Wed, Jan 16, 2019 at 11:38 PM Niphlod <nip...@gmail.com> wrote:

Don't know where it stands now, but back in the day rname was supposed to "decouple" completely the name of the underlying "supporting" object (table or column on the backend) from the name of the "supported" object (DAL.Table or DAL.Field). This is because DAL is lovely but suffers from some limitation when you can't embrace the naming convention we're all used to.
To put it in another words, it was created to make sure cornercases were covered when you were managing backend structures pre-created. By extension it also meant that what you passed to rname was the already quoted literal representation of what the backend calls that object, because "no one should tamper with rname if it is explicitely set". In the original vision of rname, casing and quoting logic did NOT apply any transformation to rname. Casing and quoting could ONLY change fields and table name logic when no rname was explicitely set.

[Richard Vézina]

Actually the second issue is widespread... I was attempting to work around the issue while using sqlite temporary as entity/field get create with proper casing in it, but as soon as you try start to using it pydal complain about missing "lower_case_table_name"...

So there is 2 differents related issue :

1) Get Postgres to create rname "TableName" properly (DDL SQL) and;

2) Make pydal use the proper "TableName" in the backend (DML SQL)

Richard