commit Dec 3 "quoting of tablenames" is causing some grief

[Tim Richardson]

After the commit 14bcad629 (tablename quoting) I'm getting a lot of broken SQLFORM.grids on a mssql server.


I get errors like this in the  HTML for the SQLFORM.grid

Version:0.9 StartHTML:00000158 EndHTML:00000552 StartFragment:00000192 EndFragment:00000516 SourceURL:http://127.0.0.1:8050/olap/default/merge_patients Query Not Supported: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]The multi-part identifier "patient_entity_link.id" could not be bound. (4104) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]The multi-part identifier "patient_entity_link.id" could not be bound. (4104)'


this is not a table definition using rname, and it does not use legacy id.  Reverting to the prior commit avoids the error.

[mcm]

Try to follow the same fix as the one applied by Alan for mongodb.  See the end of this thread: https://groups.google.com/d/msg/web2py-developers/v_O5eD61n9k/a34JFl2cHvoJ

[villas]

 Quoting fields and tables breaks Firebird too.  This is because it makes them case-sensitive[1].

My code relies on case-insensitivity and so I have a problem.  

For now,  I changed dal.py and set QUOTE_TEMPLATE = '%s' in the Firebird section.

Is there a better way to set this constant? 

If not,  can we please make this change to dal to preserve backwards compatibility?

Regards,  D

[1] http://www.firebirdfaq.org/faq76/

[Massimo DiPierro]

@Michele, can we set

QUOTE_TEMPLATE = '%s' 

in BaseAdapter so that all adapters that do not extend get this default?

--
-- 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/groups/opt_out.

[Michele Comitini]

This can be done, but probably the best option is enabling quoting by using a parameter in DAL() call with a default for backward compatibility.

A way to support this kind of forward compatibility for the future, is having support for DAL revision numbers inside databases folder  so that new features are applied only to newly created databases, while older keep working the same.  What do you think?

2013/12/9 Massimo DiPierro <massimo....@gmail.com>

[villas]

>> parameter in DAL()

+1

[Michele Comitini]

@Massimo

I don't remember and I cannot find any rule about case sensitivity of entity names in the book?

Should we specify this better? 

2013/12/9 villas <vill...@gmail.com>

>> parameter in DAL()

+1

[villas]

Suggested: 

   DAL(... quote_template=None,  ...)

Ideally maybe:

   DAL(... quote_template=None,  entities_uppercase=False  ...)

[Niphlod]

watch out for blindly added parameters....  too many parameters is what got SQLFORM.grid messy and ugly.

[villas]

Hi Niphlod,

The new DAL feature is great and I would like to make it available,  rather than just disabling it. 

Re: quote_template.  To preserve backwards compatibility,  I ask to turn the new quoting feature off (for Firebird).  However,  in the future I might wish to turn it back on and I need to be able to do that without changing the dal.py code.  How might I do that?

Re: 'entities_uppercase'.  Just a suggestion to use the new quoting feature with legacy code (usually lowercase) and DBs (usually uppercase).  Just nice-to-have.

Thanks to everyone working on these improvements!

D

[Massimo DiPierro]

I agree with Niphlod on this one. We should try avoid adding parameters to DAL(...) 

I think the philosophy should be:

- try support as many possible quirks at the db engine level

- do not give too much at the DAL syntax level (it is python and there should be only one way of doing things)

Massimo

[Michele Comitini]

Consistent case support in names is important and it is the way python works and what one expects from a modern database engine.

Now you can do:

db.define_table('t1', Field('f1'))

db.define_table('T1', Field('F1'))

db(db.T1.F1==db.t1.f1).select()

On many databases you can also do:

db.define_table('tæblò', Field('àéè'))

One can refer to those as: db['tæblò']['àéè'].  If DAL supported python3.x  they could be also accessed as: db.tæblò.àéè

Yet there is a bunch of databases which still (we are almost in 2014) have troubles in managing case sensitive entity names.  So this is a problem.

2013/12/10 Massimo DiPierro <massimo....@gmail.com>

[villas]

@Massimo/Niphlod.  Thanks for your comments. 

Re: Firebird.  

Currently my apps are broken. No user would expect to have to alter dal.py to get a working system. 

Please consider either:

(a) add QUOTE_TEMPLATE = '%s' in the Firebird section of dal.py to effectively remove the feature. 

   or 

(b) deal with the quirk.  Converting web2py entity names to uppercase (to match the uppercase in the DB) may work for most (if not all).

Regards,

D

[villas]

Re:  FIREBIRD

Sorry to bring this up again,  but dal.py still needs fixing?  

If nothing else,  could we have the temporary fix:

QUOTE_TEMPLATE = '%s'   in the Firebird section

Thanks.  D

[Tim Richardson]

I thought I posted this but I can't see it.
Latest release (R-2.8.2) has
    QUOTE_TEMPLATE = "[%s]"
in the mssql adapter

In trunk an mssql table with an id key defined is not working
ie the table has Field('patient_id','id')

a simple controller function
grid = SQLFORM.grid(db.table)

results in:

Traceback

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14.

Traceback (most recent call last): File "D:\web2py_git\gluon\restricted.py", line 217, in restricted exec ccode in environment File "D:/web2py_git/applications/olap/controllers/default.py", line 261, in <module> File "D:\web2py_git\gluon\globals.py", line 372, in <lambda> self._caller = lambda f: f() File "D:/web2py_git/applications/olap/controllers/default.py", line 257, in test_legacy grid = SQLFORM.grid(hcn.db.cm_patient) File "D:\web2py_git\gluon\sqlhtml.py", line 2439, in grid id = row[field_id] File "D:\web2py_git\gluon\dal.py", line 7359, in __getitem__ raise ae AttributeError: 'Row' object has no attribute 'patient_id'

[Tim Richardson]

Is someone on this?

[Alan Etkin]

Is someone on this?

Tim, file an issue in the google code project page. Mind that you can temporarily use workaround posted by mcm at

https://groups.google.com/d/msg/web2py-developers/v_O5eD61n9k/a34JFl2cHvoJ

>>> db._adapter.QUOTE_TEMPLATE = "%s"

[Niphlod]

BTW2: I'm on this

[Niphlod]

Allright, I just sent a PR to fix (hopefully) the issues.
Let's have a conversation about DAL changes (@mcm, chime in please since you're the "author" of lots of those changes)....

The purpose of the original patch seems a lot meaningful to me, but we're incurring in some **major** issues (namely, there are 2 only contributors trying to tidy up things :-P ) and I think the main problem is the "agenda" behind this major overhaul.
In my POV, web2py **shouldn't** allow table names with . inside, such as db.define_table('a.b.c'), same thing goes for Fields (i.e., no Field('a.b.c'))
This is mainly due to the fact that accessing that table with db['a.b.c'] is cumbersome and takes managing the resultset a bit harder. Same thing goes for the field needing to be accessed as row['a.b.c'].
I think that web2py should "stick" to db.table.field, that can take whatever name is needed with the support of rname.

The big thing I think you're trying to achieve is working with schemas (I have that wish too), but alas I think this got lost in the process....
The only "commodity" in declaring table names with dots inside would in fact be accessing different schemas, but then we should refactor all the parsing because in a lot of places web2py expects tablename.fieldname notation.
Indeed I find the task intimidating, but I don't see any "beautification" of the code in the process....
Also if we come up with the refactoring, still declaring a table as db.define_table('a.b.c', Field('a')) keeps all the subsequent code in the need of doing db['a.b.c'].

Since the same thing **could** be achieved (ATM the quoting is syntactically correct but prevents schemas access, because it's done as "a.b.c") with db.define_table('abc', Field('a'), rname='"a"."b"."c"') in which the user is responsible for quoting correctly schema, table etc..... keeping DAL code easier and app's code more concise, I'd support only the latter...in summary: let's support ASCII simple table and field names and for any "special needs" that were unachievable before let's stick to "promote" rname as the solution, in which is the user that needs to know how quoting works for his backend.

Right now I put a return on some tests just to make them pass......but I think some of those **should** be dropped, specifically the ones with 'a.b.c' in table or field names (also edited, at least for the fields that didn't make any sense to me)

@all : do we all agree ? Also, I'd reenable the tests that make sure that Fields don't start with an underscore, etc, to prevent clashing ..... the eventual limitation of having a column named _truncate in the backend (in the unlikely case) can be circumvented with rname too.....

NB @all: we must agree on something and go further working, else we'll be stuck with complaining users for a long long time. In the latter case I think the only way to support such big changes would be to start off a branch, and I don't think we can do that in the "long run"

[Niphlod]

PS: we also should figure out a way to support case sensitivity support in Windows.
filenames are NOT case sensitive on Win and so even if the code supports it (db.C and db.c), you can't have
blablabla_C.table
blablabla_c.table
on windows at the same time.

[Massimo DiPierro]

This is what I think:

1) we should support any table name at the database level  (including with .)

2) we should restrict table names at the Python levels to valid Pyhton names (no .)

3) there should be a mapping between the two

4) at the Python level we should not allow db.C and db.c because of .table conflicts on windows but it should be possible to map two different names into distinct tables "c" and "C" at the Python level. By default Python models names should be mapped into lower case table names and assume case insensitivity.

5) all default tables names should be quoted unless an explicit mapping has been given for a table.

Example:

Python DB

db.mytable "mytable" (default)

db.MyYable "MyTable" (default)

db.mytable "MyTable" (if mapped)

db.mytable "a.b" (if mapped)

db["a.b"] "a.b" (NOT ALLOWED)

Niphlod, I think we are on the same page on this. Perhaps we should reinstate the rname attribute.

I like the idea of delegating quoting to the adapter.

Massimo

[Massimo DiPierro]

I also agree with a previous proposal of breaking the DAL into multiple files. One per each adapter. For example:

dal/__init__.py
dal/core.py
dal/adapter_base.py
dal/adapter_sqlite.py
etc.

I like this better instead of adapters/sqlite.py in order to avoid naming conflicts when doing import sqlite.

[villas]

Please bear in mind,  for legacy Firebird data there is usually a mismatch:

Python:   db.mytable        

DB:         MYTABLE

If there were a way to make all entity names uppercase,  and/or,  disable the quoting,  then we can have some backwards compatibility.

Thanks.

[Alan Etkin]

 

In my POV, web2py **shouldn't** allow table names with . inside, such as db.define_table('a.b.c'), same thing goes for Fields (i.e., no Field('a.b.c'))
This is mainly due to the fact that accessing that table with db['a.b.c'] is cumbersome and takes managing the resultset a bit harder. Same thing goes for the field needing to be accessed as row['a.b.c'].
I think that web2py should "stick" to db.table.field, that can take whatever name is needed with the support of rname.

+1 on all the above

 

let's support ASCII simple table and field names and for any "special needs" that were unachievable before let's stick to "promote" rname as the solution, in which is the user that needs to know how quoting works for his backend.

 
+1 for this too.

BTW: I belive rname is not documented in dal docstrings and it should even if experimental. A search in the book does not return results either. It needs docs since (as I stated before) is not self explanatory.

[Alan Etkin]

 

4) at the Python level we should not allow db.C and db.c because of .table conflicts on windows but it should be possible to map two different names into distinct tables "c" and "C" at the Python level. By default Python models names should be mapped into lower case table names and assume case insensitivity.

+1. I think this will avoid a lot of issues.

EDIT:

About "... but it should be possible to map two different names into distinct tables "c" and "C" at the Python level ...". I'm probably missing something, but I don't find any reason to support two Python references to ThisIsMyTable and thisismytable. I belive having such tables is not good practice and therefore it should not be encouraged.

[Alan Etkin]

I also agree with a previous proposal of breaking the DAL into multiple files. One per each adapter.

This will turn dal way lighter but AFAIK this can lead to all sorts of namespace/platform issues and must be thoroughly tested for bw compatibility. And I think it will need doing some adapter rewrite also.

EDIT: by rewrite I mean changes to each adapter in dal

[Massimo DiPierro]

For firebird the quoting could default to no-quoting and tables could be always upper cased. It should be backward compatible.

[Massimo DiPierro]

I applied the patch but we need quoting for MSSQL tables. This is only a temporary solution.

On Dec 15, 2013, at 8:31 AM, Niphlod wrote:

[Michele Comitini]

I am travelling.  Just to confirm what written by Niphlod (go on with the changes you proposed).  There are many issues related to quoting.  Most of them are cause by too many meanings and uses by different components of the "." symbol.  I'll be back with more details later...

[Jonathan Lundell]

With NoSQLAdapter in adapter_base.py, presumably?

[Massimo DiPierro]

If not done already, would you mind opening a ticket about this?

[Tim Richardson]

Will do soon.
I spent some hours debugging this but any fixes I would have made could only have been hacks and the discussion here is at architecture level.
However, dal.py is not as scary as I thought.

[Michele Comitini]

Since I see some good discussion here I wish to contribute some ideas, it the hope of not being too repetitive and/or boring.

I think that with the introduced modification, web2py DAL will benefit of ease of maintenance and at the same time speed improvements, because profiling will be more effective.  So even if in the meantime we will have to suffer a little I  am rather optimistic that in the end it will be rewarding. 

We have to move on different directions, to better orientate ourselves, I wish to bring attention to the standards involved.  Not that standards are always correct and better than current practices, but often they are a starting point for a reasoning that could eventually bring to the conclusion that those standards need to be violated for good.  When there is a limitation, we have something "substandard", which is not a bad thing per se, once it is explicit for everyone.

Let's start with SQL-92 standard, here is a copy: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt.

At pag 84 and following we have the definition of <delimited identifiers>.

We have also another standard to respect if we decide that a database entity must always be accessed as a python (2.5+) named attribute (i.e. db.tab1 and db['tab1']) and avoid item only notation (i.e. that would allow, only for instance, db['tab   1']).  Perfectly reasonable, so the standard is: http://docs.python.org/2/reference/lexical_analysis.html#identifiers 

The intersection of the 2 is the python-2.5+ standard since it's a subset of the SQL-92 standard.  On python3+ there would a much wider support of SQL-92 identifiers.  But using python-2.5+ identifiers is fine, given that we know it explicitly and we prevent the programmer from doing differently.

Now the real issue is that some common databases violate SQL-92 standard, most notably mysql and sqlite.  On those database case support for delimited identifiers works differently if the entity is a field or a table.  On mysql this is because of poor design, they chose to use as table identifier a filename, so allowed identifiers depend on the underlying filesystem. On sqlite3 they probably did managed a trade-off between the standard and speed or size.

The above further restricts the choice for identifiers, because while python allows db.T and db.t as 2 distinct attributes, at the same time those databases do not because they  are not SQL-92 compliant.  We could use a map: db.define_table('T',...) to sql 'create table t_  ...' (just an example).

But this would be very similar to the situation we are facing now with using 'F'/'T' instead of native booleans just because sqlite has no truly boolean type. Should we probably  enforce something at python level, like lower case only table names?

Those are not just piddling issues, because many db generate other entities name (such as sequences or indexes) based on their on algorithms, and DAL sometimes needs to access to those objects.

About the "." we must consider that it's used as:

 1. table.field separator by most db engines (as per SQL-92)

 2. it is used as attribute name separator by python

 3. also the DAL uses it internally to separate a table name from a field name in many different places

 4. the grid is also making some assumption on that

So even if delimited identifiers (SQL-92) or python strings and consequently python item names can contain "." inside it is better to forbid it and allow it only in rname.

One may find that "rname" is the solution to all of the above? It is a very powerful tool, but it is not for the faint hearted.  There are  issues with it, that are again due to substandard databases:

  - reference names

  - sequence names

  - migrations

  - moving from one db to another (ala export via csv)

My POV is that the beauty of DAL is a great abstraction from the underlying database engine, I would like to keep it.  Daily I have to fight with legacy databases that challenge that so rname comes to the rescue, as quoting and executesql, but for databases created with the DAL from start we should always take care of keeping the portability.  

 

mic

 

2013/12/15 Massimo DiPierro <massimo....@gmail.com>

[Massimo Di Pierro]

I have found the "." also used at the database to separate namespace.table. I think there is agreement on most issues. One issue remain in my opinion. How to allow the mapping between the python names and the sql names without the use of a variable (rname)?

I think you and Michele are the two people who have put the most work on this issue. You are both near/around Milan. Why don't you meet for a coffee and make a proposal you both agree on?

The current situation is messed up and this needs to be resolved asap or we may have to revert some of the new features until we get a better solution.

Massimo

[Tim Richardson]

The current situation is messed up and this needs to be resolved asap or we may have to revert some of the new features until we get a better solution.

Or branch?

[Michele Comitini]

I am favorable to the branch or a clone to avoid messing with main trunk.

Branching will be effective if someone would test the branch databases especially old ones and/or legacy ones, and different database engines.

Is anyone willing to help especially with proprietary db engines (mssql is already under the scrutiny of Niplhod)?  The ultimate goal is to find working combination of QUOTING_TEMPLATE and REGEX_TABLE_DOT_FIELD that makes

the chosen adapter work on older db without or with minimum number of problems.

Meanwhile on the master branch in web2py we have to decide whether revert or go with a default of QUOTE_TEMPLATE='%s'.  This requires consensus at least among those who have looked in the code.

mic

2013/12/17 Tim Richardson <t...@growthpath.com.au>

The current situation is messed up and this needs to be resolved asap or we may have to revert some of the new features until we get a better solution.

Or branch?

--

[Tim Richardson]

I'll test; a branch makes it easy (I often do my development in trunk but I can't at the moment).

You received this message because you are subscribed to a topic in the Google Groups "web2py-developers" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py-developers/4hzZ4HkNCTo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py-develop...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

--
Tim Richardson

[Niphlod]

nobody is looking at Oracle IMHO.

MSSQL should be fixed in trunk, leaving 'a.b.c' out of the picture.
I don't see many users trying to fix adapters and testing them, and frankly I don't have the time to fire up n virtual instances just to run tests on databases that I'll never use.
Branching off is a cool idea only if there is manpower behind it. A long-lived fork makes no sense ATM: I'm not seeing that much development and new features that could make us hurry in releasing e.g. 2.9.0.
Given that we agreed on the details we can move on: I raised the question to fire up the discussion and to choose a direction, that seems now very well defined.
Several mistakes had been made in the last two or 3 releases, so I'd take a little bit of breath before releasing a new version.

The only huge problem we're facing right now is case sensitivity and the current "naming scheme" of the .table names that in Windows will never work out. Choosing a different naming scheme would probably end in loops in the code or requiring manual work to "migrate" just the .table files to a new name..... does anyone have a solution for that ?

[Niphlod]

The only huge problem we're facing right now is case sensitivity and the current "naming scheme" of the .table names that in Windows will never work out. Choosing a different naming scheme would probably end in loops in the code or requiring manual work to "migrate" just the .table files to a new name..... does anyone have a solution for that ?

correction for the above.
Given point 4) on @massimo's pixel-perfect summary, db.c and db.C - without rname - will never cohexist in the same model, so we're fine. This is a perfectly sane "limitation", given though that db.C will be treated as
select .... from "C"
while db.c will be treated as
select .... from "c"

[Tim Richardson]

deleted my last post ... badly setup virtual machine.

On Wed, Dec 18, 2013 at 11:43 AM, Tim Richardson <t...@growthpath.com.au> wrote:

On Wednesday, 18 December 2013 06:48:24 UTC+11, Niphlod wrote:

nobody is looking at Oracle IMHO

MSSQL should be fixed in trunk, leaving 'a.b.c' out of the picture.

for me trunk is still broken.
in dal.py line 7335 , __get_item__ falls through to converting the field name to a string, and looking for it.
The variable key = 'cm_patient.patient_id'
but row_extra looks like this:

<Row {'[cm_patient].[patient_id]': 0L, '[cm_patient].[surname]': None}>

this works better.

 key=str(k.sqlsafe)

--
-- 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 a topic in the Google Groups "web2py-developers" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py-developers/4hzZ4HkNCTo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py-develop...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

--
Tim Richardson

[Tim Richardson]

On Wednesday, 18 December 2013 06:48:24 UTC+11, Niphlod wrote:

nobody is looking at Oracle IMHO.

MSSQL should be fixed in trunk, leaving 'a.b.c' out of the picture.

thanks, I confirm it is working so http://code.google.com/p/web2py/issues/detail?id=1817 is closable.

 

I don't see many users trying to fix adapters and testing them,

I can help, I use mssql mostly at this stage. 

[Niphlod]

here's the situation on adapters.... sqlite, postgresql, mysql are included in CI with travis (meaning they're checked at every commit).
Even if trunk **could potentially** have some tests not passing, they are readily fixed and generally it doesn't happen that web2py master on github brakes tests (the failing kinda "exclude" the merge into master because @massimo wants to play safe ^_^)

I check MSSQL weekly manually.
The only "big" outside the picture ATM IMHO is Oracle.
I never saw anyone running tests "continously" for Firebird, DB2, Ingres, Sybase, Informix, Teradata, Cubrid and SAPDB.
Then there is the family of NoSQL, for which tests are "totally" missing: mongodb, couchdb, gae and IMAP (not quite a NoSQL per se but has lots of limits).... I never had to play with DAL and all of the untested engines, and although some of them are still marked as experimental, I don't see anyone trying to figure out a consistent API or writing tests that could - at least - notify if the subset of operations allowed is broken or not.
To my knowledge, at least couchdb and mongodb are available on travis-ci, so once tests are written, they could very well be starting to phase out from the "experimental" status to the "stable" one. Tests also forces people to think to what the adapter actually can support instead of randomizing "notimplemented" here and there :-P

tl;dr: I'm not worried about "the big ones" but I'm a little scared that the complete test suite never passed for any of the "lesser known" relational engines.
The nosql world is a totally different matter (being untested and experimental) so I can't really vouch for "maintaining backward compatibility" without someone actually testing trunk regularly :-P

[Alan Etkin]

 

Then there is the family of NoSQL, for which tests are "totally" missing: mongodb, couchdb, gae and IMAP (not quite a NoSQL per se but has lots of limits)

True. I'd add issues for for tasks (or maybe list them in the roadmap) about mongodb and imap. Not for couchdb since I've never used it.

I don't see anyone trying to figure out a consistent API or writing tests that could - at least - notify if the subset of operations allowed is broken or not.

Okay, this should be noted also in the issues/roadmap entries. About a consistent api, is there a consistent dal api specified somewhere? It could be very useful for adjusting the specific adapters to a general pattern
 

To my knowledge, at least couchdb and mongodb are available on travis-ci, so once tests are written, they could very well be starting to phase out from the "experimental" status to the "stable" one.

Really? Nice. Maybe I can add some simple/basic tests so we can start checking changes.
 

Tests also forces people to think to what the adapter actually can support instead of randomizing "notimplemented" here and there :-P

Well, maybe there are some not supported that were naively put instead of the more concrete not implemented
 

tl;dr: I'm not worried about "the big ones" but I'm a little scared that the complete test suite never passed for any of the "lesser known" relational engines.

As long as they are experimental engines, I wouldn't care if some test do not pass provided that it is turned off and documented until someone willing to contribute fixes it. I suppose those issues are consistent with their experimental nature.
 

The nosql world is a totally different matter (being untested and experimental) so I can't really vouch for "maintaining backward compatibility" without someone actually testing trunk regularly

 
Nosql dbs have less (or maybe a few) users. Isn't so? That means less (or a few and sometimes no) people testing and fixing and enhancing. So clearly mantaining backwards compatibility request more time. IMO, this would need a slower release cicle. Anyway, I agree with the need of having a basic suite for supported api commands as a requirement for any dal adapter.

[villas]

Firebird... 
DAL is still broken and I have to patch it with  QUOTE_TEMPLATE = "%s"
Whatever we wish to do about this in the future,  could we now patch it in trunk so I don't have to keep altering DAL on every update?
Should I raise an issue?
Thanks,  D

[Niphlod]

is 2.8.2 broken ?

[Niphlod]

BTW: I'm not sure everyone agreed in limiting DAL to be case-insensitive just to support Firebird "bydefault" case-insensitivity with no quotes.

So, let's define better the issue you're having because I'm starting to loose track of it........

If your current issue is coming from having in models

db.define_table('ABC', Field('cde'))

and using in your controllers

db(db.abc.cde != '').select()

then I'd advise to start refactoring your app. There definitely won't be support for case-mismatch in app's code for the next version.

Firebird is "case-insensitive" only for non-quoted queries, but we're progressing towards "all things will be quoted".
This enables Firebird to behave as every other db out there (except that "t" and "T" may not cohexist, e.g. in Mysql on win): this is what I got from reading firebird docs.

So, what's really the issue with having a sane quoting in Firebird (assuming app's code is all in "matching case") ?

[villas]

Hi Niphlod

In all my apps,  irrespective as to which DB I am using,  I do everything in lowercase:
    db.define_table('abc', Field('cde'))

In the case of Firebird,  by default the DB creates the table.field  as  ABC.CDE

My two problems are:

• I must refactor my models as:  db.define_table('ABC', Field('CDE')) to make it work,  but only for Firebird connections?
• If I subsequently change the DB connection, I fear some incompatibility/inconsistency and more refactoring.
  

Why should we refactor code just for Firebird -  web2py is supposed to be DB agnostic?  Furthermore,  there is a clear breakage of backwards compatibility.

All I am asking is that our apps can continue working even if this means we cannot have the new benefits of quoted entity names for Firebird.

I am open to all ideas but obviously reluctant to revisit and refactor all my previous projects.

Many thanks,  D

[Niphlod]

Ah, ok then. Sorry for the misunderstanding. Not sure when FDB got the "stable" phase, I was assuming it was still experimental (and to my knowledge, you're the only one using it)

Can we agree on the fact that for newly created apps the current trunk works without issues ?

If yes, probably we should stick to "poor" fdb support just to avoid backward compatibility issues, and create a firebird2 adapter that is going to be recommended over the "old" firebird. The old will stick to "no quoting at all" just for old app's sake.

BTW: What you **should** to do in the end with current trunk (or "future" trunk) is

db.define_table("abc", Field("cde", rname='"CDE"'), rname='"ABC"')

The whole point of "pushing" rname is that you don't need to refactor any code and change table and columns names "at will", but I see nonetheless that if backward compatibility is a must, it's not viable.

[Michele Comitini]

just after dal instantiation you can try something similar to the following to safe you some writing (*untested*):

db._define_table = db.define_table

def fdb_define_table(self, *args, **kwargs):

   if not kwargs.has_key('rname'):

        kwargs['rname'] = args[0].upper()
       

   return self._define_table(*args, **kwargs)

db.define_table = fdb_define_table

if it works you can do the something analogous with Field.

mic

2014/1/15 Niphlod <nip...@gmail.com>

[villas]

Hi Niphlod

Some DBs have a configurable setting for case-sensitivity.  

Some auto-uppercase

Some auto-lowercase

Some vary according to platform

Some invoke case-sensitivity through quoting.

Case sensitivity is a nightmare,  but most of us would just accept the standard defaults.  My thoughts are these...

Each DB adapter has a "case" setting default of:   upper | lower | mixed.  The adapter then quotes everything and applies the case (if upper or lower) to the SQL.  

The DB connection could expose 'case', to give some tweak control.  This might seldom be used but programmers working with mixed case should be given the opportunity to choose this masochistic road :) 

.

This idea gives us the advantage of quoting whilst providing some 'case' control.

Regards,  D

[Niphlod]

AFAIK all major players support case-sensitivity for identifiers out of the box (please point to docs stating otherwise)

MySQL has an issue because on linux you can have both "Hello" and "hello" as tables but on Win you can't. However it's minor since it can be circumvented with rname.

Let's face it: firebird adapter as it was simply didn't care about case: it was poorly programmed...its not entirely firebird fault, but DAL's one - and ours - to promote such adapter to the stable status (if it ever was pushed to stable)

[villas]

Yes,  I think most DBs permit case-sensitivity,  but have differing defaults.  

Regarding Firebird,  I don't think that's fair at all.  I think Fb is very similar to Postgres,  except:  Pg defaults to lowercase entities rather than uppercase.

Therefore if you are in the habit of writing lowercase in your code (as we surely all do),  that matches lowercase in the Pg DB.  That was lucky for Pg users now that the entities are quoted!  However,  it doesn't mean that Fb is wrong!

What I suggest is this:

We write this code:   db.define_table('abc', Field('cde'))

We would expect something like this SQL:   create table "abc" ( "cde" varchar(512) )

In Fb adapter we simply convert that SQL  to uppercase.  In Pg to lowercase.

   

If someone wants mixed case,  they explicitly ask for it at the connection eg   DAL(...  casesensitive=False).  

AFAIK everyone sees there is a benefit for quoting,  but no one has asked for mixed case within the DB on this list (have they?).  For those exceptional programmers that want mixed case,  let them specify it.  IMO it is a bad practice and should be discouraged, but there may be legacy data out there.

The rest of us can use the customary defaults for our chosen DB and enjoy backwards compatibility AND quoting.  Our code stays the same and works even when the DB is changed.  I mean I could switch from Pg to Fb and it would still work,  and I think that's what the user probably expects.

[Alan Etkin]

Yes,  I think most DBs permit case-sensitivity,  but have differing defaults. 

About experimental state of Firebird adapter:

(Version 2.3.2 (2012-12-17 15:03:30) stable)

"...This file contains the DAL support for many relational databases,
including:
- SQLite & SpatiaLite
- MySQL
- Postgres
- Firebird
- Oracle
- MS SQL
- DB2
- Interbase
- Ingres
- Informix (9+ and SE)
- SapDB (experimental)
- Cubrid (experimental)
- CouchDB (experimental)
- MongoDB (in progress)
- Google:nosql
- Google:sql
- Teradata
- IMAP (experimental)..."

Firebird is not marked as experimental, therefore anything that worked before (and was specified in documentation) should work as is, without refactoring, modifying or tweaking anything. No?

I think the best option is a new subclass supporting case sensitivity given that dal will be converted to a library, so I suppose adding a new adapter would not be a problem. But if the quoting issue also affects other adapters, perhaps a best option is villas's DAL optional parameter. In that case I belive that we could set the default case sensitivity option per-adapter, enabling it by default for those engines without known issues.

[Tim Richardson]

code like this is going to need refactoring, it seems:

old code: (sqlite)

...
        sum_value = db.chore.chore_value.sum()
        children_totals = db((db.job.id.belongs(ids)) &
                      (db.auth_user.id == db.job.child) &
                      (db.chore.id == db.job.chore)
        ).select(db.auth_user.username,sum_value,groupby=db.auth_user.username)
        for row in children_totals:
            children_payout[row.auth_user.username] = row._extra['SUM(chore.chore_value)']
...

now needs to become

                 ... = row._extra['SUM("chore"."chore_value")']

[Michele Comitini]

quoting is done for you if you use the expressions:

row[sum_value]

2014/1/16 Tim Richardson <t...@growthpath.com.au>

[Tim Richardson]

On Friday, 17 January 2014 00:27:27 UTC+11, mcm wrote:

quoting is done for you if you use the expressions:

row[sum_value]

of course. Thanks.  

[Niphlod]

About experimental state of Firebird adapter:

(Version 2.3.2 (2012-12-17 15:03:30) stable)

.......

I truly believe it's a major miss from our point of view (kinda of "marketing gone bad"). Yes, we do support loads of backends but no, not all are tested nor they are actively maintained.
The teradata adapter is in a flaky state too, with ingres and informix very close to that state.
The utterly complete lack of maintainers since the initial commit sheds some light into the "attention" that we (as contributors and as community members) pay to those.

@villas: I started just with rname support to fiddle with some sneaky table names at work.... auto-quoting was a feature briefly showed/introduced (by mcm) and very well "welcomed" by all main developers, and let's face it.... it is/was probably the biggest limitation just after poor handling of non integer pkeys and/or multiple pkeys.

I think we all reached the point where we agree that for newly-created apps the "auto-quoting" behaviour works without going into exceptions (little bonus the possibility to use the dreaded reserved keywords "natively").

Let's discuss on how to manage backward compatibility at the API level (in the meantime mcm and I are working towards cleaning the current API and writing more tests).

[Tim Richardson]

Regarding rname and table quoting documentation (book), any non-obvious points to be covered or use-cases worth mentioning as interesting examples?

[Niphlod]

Given that it's not finished yet I'm having a really hard time telling what the API should look like....the docs will come later, I hope :-P

[Michele Comitini]

I suppose modularization of adapters will also make easier to manage stable and experimental code.

One could expect to have a module directory where experimental adapters live, before they are moved to stable.

Thanx to Simone's work on the test automation the stability ranking is becoming much more objective, rather than subjective, consequently the stable/experimental labels  will represent much better the real state of things.

mic

2014/1/16 Niphlod <nip...@gmail.com>

[villas]

Hi Niphlod

>> I think we all reached the point where we agree that for newly-created apps the "auto-quoting" behaviour works
>> without going into exceptions (little bonus the possibility to use the dreaded reserved keywords "natively").

New apps are one thing,  but we need a solution to work backwards as well as forwards. 

IMO  auto-quoting should be an optional setting.  In the great majority of cases it should be superfluous.  Not having to bother worrying about reserved words might seem like a good thing,  but developers should still learn to avoid them like the PLAGUE and always preserve the possibility of using clear and clean SQL.  We should be able to access our DBs through all external tools and other external apps.  Reserved words are a long term curse.  Therefore I believe that auto-quoting should be an optional setting for legacy data only.

Whatever solution is adopted,  please take into consideration the following:

1. Developers may wish to retain their DB defaults (as previously was the case). eg in Firebird uppercase entities.  In Postgres lowercase. 

2. There may be special requirements for legacy data which might be upper/lower or case-sensitive.  Developers will have to accommodate the case-sensitivity settings of the DB they are accessing.

3. Most developers will wish to continue writing all their python code in lowercase, but use the DB default for whatever DB they are using.  This is a very important consideration as developers may change backends later and must be able to do so without changing their code.  We must protect the current portability of our codebase.

I do not understand why there should be a 'block' on using new parameters for DAL.  Parameters with sensible defaults are often the easiest and most flexible solution.  I therefore strongly believe we should do something like this:   

DAL(... quote_template=None|default|chr,  force_entity_case=None|upper|lower ...)

quote_template = None:   Default, no quoting
quote_template = default: Apply DB adapter default quoting
quote_template = 'chr':     Apply specific 'chr' for quoting

force_entity_case = None:  Default;  each DB adapter sets its customary default
force_entity_case = upper|lower:  DB adapter forces entities to upper|lower case.

I hope that the above can find some support as I believe it will comprehenively resolve the issue.

Best regards,  David.

[Alan Etkin]

 

I do not understand why there should be a 'block' on using new parameters for DAL.  Parameters with sensible defaults are often the easiest and most flexible solution.  I therefore strongly believe we should do something like this:   

DAL(... quote_template=None|default|chr,  force_entity_case=None|upper|lower ...)

Neither do I, this way it should be easier to handle compatibility issues with legacy apps. @Niphlod, What are the issues about keeping the original quoting behavior unless specified in DAL constructor?

[Niphlod]

to be fair I'm a bit tired of even thinking to new cool features to add only to see them trashed away for backward's compatibility's sake and/or seeing the API complicate even further.
mcm is working on the auto-quoting, so he should be really the one to reply to these posts.
Frankly, ATM it seems that turning it off by default is the only way to preserve backwards compatibility, but I'm - clearly :-P - not happy about it.
 

[Michele Comitini]

to be fair I'm a bit tired of even thinking to new cool features to add only to see them trashed away for backward's compatibility's sake and/or seeing the API complicate even further.

@Niphlod I share the same feeling, furthermore I suspect some core developers do leave for other frameworks because of that.
I had legacy databases that, before rname and quoting, were impossible to use under web2py not because the API said so, but because naming of other objects and migration had issues with case management.  For instance names related to constraints had all sort of issues because case did not match the case of the entity they were referring.  If I were to test the framework and found issues to a 1992 standard (SQL92), and found nothing in the documentation I would think the framework is too buggy and go away.

@Tim,@Alan I think quoting was buggy from the beginning, because, this must be clear, quoting was there already, it quoted only at times and differently, depending on the driver.  Before, application code written following the "book" specs could rise an error even on some of the "supported" drivers. I suppose there are still bugs here and there, but being well defined, now a bug is a bug whether in the application or in the framework and has to be fixed and can be fixed.

@All Now, going back to the question of choosing the default, turning off the quoting is very simple, it can be done by setting the quote template to return identity i.e. '%s'.  And this turns off quoting for real, while before quoting was used "sometimes" so even with that default some  (once buggy) adapters would *NOT* behave exactly as before, simply because now they are not as buggy as before.

The questions are: "Compatibility to what? Where is the definition?" AND/OR "Do we want to retain backward compatibility or do we want to retain a bug?"

Please respond to the questions above then vote what is the best default and we will be able to move on. Opinion of everyone here is valuable so please express yourself!

p.s. If you are interested in my humble opinion, I think that if one has code that relies on a framework bug to keep compatibility, she/he should not upgrade the framework to new releases or fix the problem in his/her code, asking the framework to retain bugs to the end of time is a bit naive. ;-)

 

mcm is working on the auto-quoting, so he should be really the one to reply to these posts.
Frankly, ATM it seems that turning it off by default is the only way to preserve backwards compatibility, but I'm - clearly :-P - not happy about it.
 

--

[villas]

Hi mcm / niphlod

I know that quoting and rname are excellent features.  Backwards compatibility is also important.  Regarding

asking the framework to retain bugs to the end of time is a bit naive

To which bug are you referring?  Perhaps there is a misunderstanding.

Best regards, D

[Tim Richardson]

BTW, My initial post in this thread was not a backwards compatibility issue to me, it was a bug in the new quoting functionality, (since fixed).

I've used new functionality which is not stable via experimental, and some things have been deprecated and then removed (like windows service support)  In these cases applications have to be adapted for new releases, a fine tradeoff. There is always the option of staying on the current release until apps are adapted, if necessary.

If the existing quoting functionality is buggy or a security risk, it's a no-brainer.

I'm persuaded a bit more by mcm/Niphlod and I think the new functionality, and their effort, justifies their recommended course of action. There is no doubt in my mind that users running into problems will be well supported.

[Alan Etkin]

I was not in search of a philosophical contest about the meaning of compatibility or asking for trashing new cool features. I'm sorry if there was a misunderstanding.

If I were to test the framework and found issues to a 1992 standard (SQL92), and found nothing in the documentation I would think the framework is too buggy and go away.

Did web2py specifications ever included a SQL92 compliance compromise note somewhere? The new quoting is an enhancement and/or a new feature, not a fix of a buggy feature, and old apps relying in the old behavior should keep working as they were coded with new versions, if they used a interface which was not marked as experimental.
 

@Tim,@Alan I think quoting was buggy from the beginning, because, this must be clear, quoting was there already, it quoted only at times and differently, depending on the driver.  Before, application code written following the "book" specs could rise an error even on some of the "supported" drivers. I suppose there are still bugs here and there, but being well defined, now a bug is a bug whether in the application or in the framework and has to be fixed and can be fixed.

 
You say there was a bug in dal because the adapters did not behave consistently about quoting? Were they supposed to be consistent?. I think that one of the purposes of having adapter subclasses is allowing those changes between each object. I don't think it is good policy to expect old code to adapt because of enhancements to the core if your documentation is stating the opposite.

@All Now, going back to the question of choosing the default, turning off the quoting is very simple, it can be done by setting the quote template to return identity i.e. '%s'.  And this turns off quoting for real, while before quoting was used "sometimes" so even with that default some  (once buggy) adapters would *NOT* behave exactly as before

 
I agree with the default neutral quoting for those adapters that have reported issues, as it seems it would work for the most of the cases.

@villas

Can you post the relevant code of the app that fails after the new feature addition?.

Maybe it can help to decide wether it is actually supported by the api or it is relying in undocumented behavior.

 

p.s. If you are interested in my humble opinion, I think that if one has code that relies on a framework bug

What is actually the framework bug? Could you give links about it? Was it proposed and accepted as a bug?

[Michele Comitini]

@Alan

When I say it is a bug, it means that the behaviour is unexpected and or undefined.  I also consider a bug when the framework raises a meaningless error that does not allow the programmer take proper action to fix it without resorting to debugging the framework code itself.

Take a stable version and do same CamelCase naming of fields and tables.  Than make references from one to another.

Make some complex queries including aggregates/expressions.  Do it in postgresql and sqlite.

Part of the issue: http://code.google.com/p/web2py/issues/detail?id=1593

Of course that was only the tip of the iceberg.

2014/1/23 Alan Etkin <spam...@gmail.com>

--

[Michele Comitini]

@Alan I see the problem of SQL92 the other way around.  Being nothing declared in web2py documentation and no check on format of entity names done with consequent error messages and or warnings, what standard do one has to expect from talking to a SQL92 compatible RDBMS?

About philosophy: nobody gets offended here and no one should, anyway I empathize with niphlod, because I know he, like all of us, has put some effort in improving life for all at least web2py wise ;-) .  In the end a software framework is backed by some kind of philosophy itself else it doesn't hold up.  Discussing about it, to an extent is always a good thing... or not?

mic

2014/1/23 Michele Comitini <michele....@gmail.com>

[Michele Comitini]

@Vilas

About the bug see my replies to @Alan

[Michele Comitini]

@Villas sorry... my eyes crossed over the 2 "l" :-(

2014/1/23 Michele Comitini <michele....@gmail.com>

[Alan Etkin]

> About philosophy: nobody gets offended here and no one should, anyway I empathize with niphlod, because I know he, like all of us, has
> put some effort in improving life for all at least web2py wise ;-) .  In the end a software framework is backed by some kind of philosophy
> itself else it doesn't hold up.  Discussing about it, to an extent is always a good thing... or not?

Philosophy thougths are good. You Don't Leave Home Without Them. I just was stressing my concern about old apps broken after a change in the core, regardless of other subject.

[villas]

@Mcm 

>>@Villas sorry... my eyes crossed over the 2 "l" :-(        

No worries,  I respond to both versions!   :)

@Alan

If I update to latest trunk,  existing Firebird apps throw this:

ProgrammingError: (-204, 'isc_dsql_prepare: \n  Dynamic SQL Error\n  SQL error code = -204\n  Table unknown\n  auth_user\n  At line 1, column 34')

The reason is that default Fb behaviour is to auto-uppercase entities (if they are unquoted).  So the auth_user table is known as AUTH_USER in the DB.  In trunk now there is auto-quoting,  therefore the SQL is,  for example:  select "id" from "auth_user".  It doesn't work!

Clearly, to make it work the SQL would have to be written as: 
    select id from auth_user 
    --or-- 
    select "ID" from "AUTH_USER"

Moving forward...  

IMO the following fixes should be unacceptable:
1. Having to patch dal.py after every update (as I currently do).
2. Refactoring all existing python code which uses Fb to use uppercase: e.g. db.define_table('ABC', Field('CDE'))
3. To change all my existing databases to suit the new behaviour.

IMO the following are workable ideas:
1. A minimal work-around fix is at least to expose the QUOTE_TEMPLATE variable so that we can disable quoting e.g.
    DAL( ... quote_template='' ...)

2. For DB adapters to use the default case of the DB in question. 
    e.g.  db.define_table('abc', Field('cde')) 
        in the Fb adapter:  create table "ABC" ("ID" integer, "CDE" varchar(512))
        in the Pg adapter:  create table "abc" ("id" integer, "cde" varchar(512))

3. Optimally, to use parameters as follows:
    DAL(... quote_template=None|default|str,
               force_entity_case=None|upper|lower ...)

    where,

    quote_template = None:   Default, no quoting
    quote_template = default: Apply DB adapter default quoting

    quote_template = 'str':     Apply specific 'str' for quoting

    force_entity_case = None:  Default;  each DB adapter sets its customary default
    force_entity_case = upper|lower:  DB adapter forces entities to upper|lower case.

In summary, 
To fix this issue comprehensively and allow full b/c we should consider 3. 
To force developers to use quoting,  but leaving gaps in the b/c we could consider 2.
To minimally allow apps to work,  we could allow turning off quoting in 1.

I hope I have written this clearly on this occasion,  but please let me know if you have any doubts.

[Alan Etkin]

3. Optimally, to use parameters as follows:
    DAL(... quote_template=None|default|str,
               force_entity_case=None|upper|lower ...)

    where,
    quote_template = None:   Default, no quoting
    quote_template = default: Apply DB adapter default quoting
    quote_template = 'str':     Apply specific 'str' for quoting
    force_entity_case = None:  Default;  each DB adapter sets its customary default
    force_entity_case = upper|lower:  DB adapter forces entities to upper|lower case.

I think this is the too complicated api Niphlod was talking about. I'm not sure it is currently supported, but perhaps it would be better to have DAL accept a quote_template=<string format> | <lambda s: ...> to override baseadapter defaults and add a default lambda in firebird adapter to automatically handle the case mismatch issue.

[villas]

@Alan

Yes my suggestion 3 does cover all the bases with two parameters  :)
However,  you appear to favour a combination of suggestions 1 and 2.  This is also workable.

Re:  Auto-quoting
This is an excellent and useful feature.  However,  creating tables with case-insensitive fields is often unnecessary and even disadvantageous in some cases.  DBs are accessed via other external apps and tools and other users should not be forced into quoting all entities just because web2py developers think it should be compulsory. I strongly believe therefore that we should be able to turn off auto-quoting when required.

Thanks for everyone's consideration.