SQLTABLE fails with mongodb adapter after dal enhancement

[Alan Etkin]

I think it is afger this change

http://code.google.com/p/web2py/source/detail?r=cfff332a4c9698f93467b9d6cdaef0fce4a92664

A simple appadmin table query raises this exception:

Traceback (most recent call last):
  File "/home/spametki/web2py/web2py-git/gluon/restricted.py", line 217, in restricted
    exec ccode in environment
  File "/home/spametki/web2py/web2py-git/applications/mongodb/views/appadmin.html", line 189, in <module>
    </p>
  File "/home/spametki/web2py/web2py-git/gluon/sqlhtml.py", line 2899, in __init__
    columns = ['.'.join(sqlrows.db._adapter.REGEX_TABLE_DOT_FIELD.match(c).groups()) for c in sqlrows.colnames]
AttributeError: 'NoneType' object has no attribute 'groups'

sqlrows.colnames returns a list of "tablename.fieldname" str objects but it seems that it doesn't have the expected syntax for the regular expression.

Any idea on how to fix this?

[Michele Comitini]

You can try to create a regexp in the adapter that works in your model.

db = DAL(<mongodb connection args>)

db._adapter.REGEX_TABLE_DOT_FIELD = re.compile(r"([^.]+)\.([^.])") # this is just an example

once you find it make the modification in the mongodb adapter in dal.py and make a PR.

2013/12/6 Alan Etkin <spam...@gmail.com>

--
-- 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.

[Alan Etkin]

You can try to create a regexp in the adapter that works in your model.

Undoing the regex pattern change from the mentioned patch appears to solve the problem. Tried the match method and it finds the columns with the "table.field" notation. I'm not a regex guru, but according to my understanding of the Python docs the new pattern requires column names to end with a ".", so this matches:

this.is.my.column.name

but this doesn't

this.is

. Is that the intended behavior?

EDIT: the second column notation also matches, (since "." matches anything except a new line).

[Jonathan Lundell]

On 6 Dec 2013, at 3:29 PM, Alan Etkin <spam...@gmail.com> wrote:

> You can try to create a regexp in the adapter that works in your model.
>

> Undoing the regex pattern change from the mentioned patch appears to solve the problem. Tried the match method and it finds the columns with the "table.field" notation. I'm not a regex guru, but according to my understanding of the Python docs the new pattern requires column names to end with a ".", so this matches:
>
> this.is.my.column.name
>
> but this doesn't
>
> this.is
>
> . Is that the intended behavior?

What are you trying to match?

I see that this is the DAL default:

REGEX_TABLE_DOT_FIELD = re.compile('^(\w+)\.([^.]+)$')

It only matches strings with a single dot.

What are you actually trying to match?

[Michele Comitini]

Every adapter needs the following attributes set properly in pair:

QUOTE_TEMPLATE -> to send queries with correct entity quoting

REGEX_TABLE_DOT_FIELD -> to split table and field when parsing responses from the db engine.

the code inherited by the BaseAdapter does this (through a metaclass):

        self.REGEX_TABLE_DOT_FIELD = re.compile(r'^' + \

                                               self.QUOTE_TEMPLATE % REGEX_NO_GREEDY_ENTITY_NAME + \

                                               r'\.' + \

                                               self.QUOTE_TEMPLATE % REGEX_NO_GREEDY_ENTITY_NAME + \

                                               r'$')

i.e. compiles automatically a regexp based on the QUOTING_TEMPLATE

for instance postgresql:

QUOTING_TEMPLATE='"%s"'

creates

REGEXP_TABLE_DOT_FIELD=re.compile(r'^"(.+?)"\."(.+?)"')

Up to now the code has been tested on (postgresql, mysql, sqlite) BUT NOT ON mongodb.

Most database engines have a way to allow a characters besides case insensitive ASCII in the name of entities (call them tables and fields).  But mongodb is different and so is the select() method of the adapter, which is not yet upgraded to cope with the new quoting logic.

As a first try you can set QUOTING_TEMPLATE as class property of MongodbAdapter in dal.py or overwrite it in your model just after DAL instantiation.

QUOTING_TEMPLATE='%s' 

i.e. no quoting at all, this could make the adapter work again, in the meantime we upgrade the select() method. 

2013/12/7 Alan Etkin <spam...@gmail.com>

You can try to create a regexp in the adapter that works in your model.

Undoing the regex pattern change from the mentioned patch appears to solve the problem. Tried the match method and it finds the columns with the "table.field" notation. I'm not a regex guru, but according to my understanding of the Python docs the new pattern requires column names to end with a ".", so this matches:

this.is.my.column.name

but this doesn't

this.is

. Is that the intended behavior?

[Alan Etkin]

Every adapter needs the following attributes set properly in pair:

QUOTE_TEMPLATE -> to send queries with correct entity quoting

REGEX_TABLE_DOT_FIELD -> to split table and field when parsing responses from the db engine.

My bad, I didn't find the requirement within the framework documentation.

> Up to now the code has been tested on (postgresql, mysql, sqlite) BUT NOT ON mongodb.

Well, actually I did test the adapter while debugging an issue and found this another one.

> Most database engines have a way to allow a characters besides case insensitive ASCII in the name of entities (call them tables and
> fields).

Until this is requested for support (and in case that the storage systems that are not relational dbs actually support extended charsets for defining objects) I think we can safely return to the former quoting/table naming to prevent this and further issues (only with NoSQLAdapter subclasses). Unless there are objections, I can send the patch.

> or overwrite it in your model just after DAL instantiation.

I'd prefer not requiring users to add configuration commands so their apps keep working with new versions. It would be somewhat against web2py style.

[Alan Etkin]

> What are you trying to match?

As you mentioned, the new pattern does work too for inputs like "mytable.myfield" (colnames returned by the mongodb .select method), so I guess the actual problem is with the default base adapter quote template.

[Jonathan Lundell]

On 7 Dec 2013, at 2:49 AM, Alan Etkin <spam...@gmail.com> wrote:

> What are you trying to match?

As you mentioned, the new pattern does work too for inputs like "mytable.myfield" (colnames returned by the mongodb .select method), so I guess the actual problem is with the default base adapter quote template.

The default QUOTE_TEMPLATE is '"%s"', which assumes that both tablename and fieldname are separately quoted (it's not obvious why PostgreSQLAdapter overrides QUOTE_TEMPLATE with the default value).

How about defining QUOTE_TEMPLATE as '"?%s"?' 

That would match any of:

mytable.myfield

"mytable.myfield"

"mytable"."myfield"

Or, for a universal one: '''[`"']?%s[`"']?'''

That would work for all adapters, assuming that none had embedded "foreign" quotes in their table/field names.

BTW, it's mildly confusing that class Row uses the global REGEX_TABLE_DOT_FIELD. I assume it's because the quotes have already been stripped, but having the same name...

[Michele Comitini]

the global REGEX_TABLE_DOT_FIELD is legacy, I agree it should go away, but could be that some other module depends on it.

2013/12/7 Jonathan Lundell <jlun...@pobox.com>

--

[Michele Comitini]

2013/12/7 Alan Etkin <spam...@gmail.com>

Every adapter needs the following attributes set properly in pair:

QUOTE_TEMPLATE -> to send queries with correct entity quoting

REGEX_TABLE_DOT_FIELD -> to split table and field when parsing responses from the db engine.

My bad, I didn't find the requirement within the framework documentation.

Why your bad? it's not documented at all, it was only partially supported before and I started trying to make it more complete.

Once we agree on a stable API we will have to document it.  One important aspect of the API is that Field and Table have a new 

property "sqlsafe" this contains the name of the entity quoted. [ Actually "sqlsafe" has been poorly chosen (by me) and should become "querysafe" or something similar since we do not deal with sql only...].

.sqlsafe is "rname" aware, i.e. if you use rname attribute, in the entity definition, sqlsafe will use that asis.

 

> Up to now the code has been tested on (postgresql, mysql, sqlite) BUT NOT ON mongodb.

Well, actually I did test the adapter while debugging an issue and found this another one. 

 

> Most database engines have a way to allow a characters besides case insensitive ASCII in the name of entities (call them tables and
> fields).

Until this is requested for support (and in case that the storage systems that are not relational dbs actually support extended charsets for defining objects) I think we can safely return to the former quoting/table naming to prevent this and further issues (only with NoSQLAdapter subclasses). Unless there are objections, I can send the patch.

> or overwrite it in your model just after DAL instantiation.

I'd prefer not requiring users to add configuration commands so their apps keep working with new versions. It would be somewhat against web2py style.

I fully agree with you. I was only suggesting to use in the models while experimenting so that you do not have to restart web2py each time.

[Jonathan Lundell]

On 7 Dec 2013, at 12:20 PM, Michele Comitini <michele....@gmail.com> wrote:

2013/12/7 Alan Etkin <spam...@gmail.com>

Every adapter needs the following attributes set properly in pair:

QUOTE_TEMPLATE -> to send queries with correct entity quoting

REGEX_TABLE_DOT_FIELD -> to split table and field when parsing responses from the db engine.

My bad, I didn't find the requirement within the framework documentation.

Why your bad? it's not documented at all, it was only partially supported before and I started trying to make it more complete.

Once we agree on a stable API we will have to document it.  One important aspect of the API is that Field and Table have a new 

property "sqlsafe" this contains the name of the entity quoted. [ Actually "sqlsafe" has been poorly chosen (by me) and should become "querysafe" or something similar since we do not deal with sql only...].

.sqlsafe is "rname" aware, i.e. if you use rname attribute, in the entity definition, sqlsafe will use that asis.

I hadn't noticed sqlsafe, which shares QUOTE_TEMPLATE. So my suggestion of a generalized QUOTE_TEMPLATE wouldn't work.

[Alan Etkin]

I hadn't noticed sqlsafe, which shares QUOTE_TEMPLATE. So my suggestion of a generalized QUOTE_TEMPLATE wouldn't work.

This change

http://code.google.com/p/web2py/source/detail?r=4c83d34ec7b0c7319390f21107275d997363c51a

uses mcm suggestion to fix the quote template issue. It works with mongo and imap but I have not tested it with other nosql adapters. Maybe it should be advertised in web2py-users so it can be tested with gae also.

[Tim Richardson]

In trunk the mssql adapter has  QUOTE_TEMPLATE = "[%s]"



I have not got my head into dal.py very much yet.
However, I am having trouble with SQLFORM.grid(db.table) when db.table has a id field set.
In this case, table has field('patient_id','id')

and on
grid = SQLFORM.grid(db.table)
 I get

Traceback

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18.

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/patient_form/controllers/default.py", line 373, in <module> File "D:\web2py_git\gluon\globals.py", line 372, in <lambda> self._caller = lambda f: f() File "D:\web2py_git\gluon\tools.py", line 3239, in f return action(*a, **b) File "D:/web2py_git/applications/patient_form/controllers/default.py", line 94, in select_record_1TE grid = common_functions.select_record_grid(entity_db,"1TE") File "applications\patient_form\modules\common_functions.py", line 49, in select_record_grid grid =SQLFORM.grid(entity_db.cm_patient#query#, 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'

I'm guessing this is due to the changes discussed in this thread.