rname and reserved names

[Simon Ashley]

Haven't used rname before but the way I read it it should work with reserved names. Have changed a given example to read:

db.define_table('easy_name',
     Field('position', rname='"this_is_the_field_name"'),
     rname='"this_is_the_easy_name_table"')

but it returns an error as follows:

Traceback (most recent call last):
  File "C:\w2p_5\gluon\restricted.py", line 220, in restricted
    exec ccode in environment
  File "C:/w2p_5/applications/test_1/models/db_1.py", line 11, in <module>
    rname='"this_is_the_easy_name_table"')
  File "C:\w2p_5\gluon\dal.py", line 8223, in define_table
    table = self.lazy_define_table(tablename,*fields,**args)
  File "C:\w2p_5\gluon\dal.py", line 8240, in lazy_define_table
    table = table_class(self, tablename, *fields, **args)
  File "C:\w2p_5\gluon\dal.py", line 8754, in __init__
    check_reserved(field_name)
  File "C:\w2p_5\gluon\dal.py", line 7931, in check_reserved_keyword
    'invalid table/column name "%s" is a "%s" reserved SQL/NOSQL keyword' % (name, backend.upper()))
SyntaxError: invalid table/column name "position" is a "ALL" reserved SQL/NOSQL keyword

Running 2.9.5

There there something I've overlooked? TIA

[Limedrop]

The error message suggests that it is the field name (ie, "position") that is reserved.

[Simon Ashley]

Yep,  agree with that but to quote Niphlod "

"For the ones in need of:

- accessing legacy tables with some funny names 

- use reserved keywords for table and field names  "

https://groups.google.com/forum/#!msg/web2py/_q5qcARON4E/6JLCHM3eQHAJ 

[Limedrop]

I think you just need to turn the warning off. 

db = DAL(..., check_reserved=None)

[Simon Ashley]

Thanks. I could, but need to check this for postgres. Seems a little bit strange when I have a look at the sql.log file:

timestamp: 2014-07-21T15:57:35.749000
CREATE TABLE auth_cas(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER REFERENCES auth_user (id) ON DELETE CASCADE,
    created_on TIMESTAMP,
    service CHAR(512),
    ticket CHAR(512),
    renew CHAR(1)
);
success!
timestamp: 2014-07-21T15:59:39.060000
CREATE TABLE "this_is_the_easy_name_table"(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    "this_is_the_field_name" CHAR(512)
);
success!

and when I look at the table generated, I get this:

(dp1
S'position1'
p2
(dp3
S'length'
p4
I512
sS'unique'
p5
I00

 

Note that I've changed position to position1 to escape the error and generate the table. I would have thought that it would have generated the rname. I might try a fresh dal.py source.

[Limedrop]

This worked for me on postgresql 9.1...

db = DAL('%s%s' % (dal_connection, app_database))
rname = db._adapter.__class__.QUOTE_TEMPLATE % 'ALL'
db.define_table('test', Field('whatever', rname=rname))

Note that I used "ALL" rather than "position" as position is not a reserved word on postgresql

[Simon Ashley]

Thanks but basically, all we're trying to do is to specifically map the DAL field name to the name as stored in the database, and ascertain why our original scenario/ approach fails. 

(reserved names maybe only a subset of issues we're trying to avoid.

Have a feeling that a QUOTE_TEMPLATE solution may turn out to be inflexible. I think we are at cross purposes).

[Niphlod]

wait a sec.
there are two distinct set of "problems". One is avoiding using reserved keywords, that is a "bad choice" in respect of what usually are "verbs" in t-sql dialects. in this matter, the check_reserved argument deals with (most of) them avoiding to create some table or some field named "xxx" if the "xxx" is a reserved keyword in t-sql dialects.
Then there is quoting. This is "solved" by using rname, that basically deals with quoting all entities (tables and fields) to avoid treating those "verbs" as verbs, and instead correctly signalling it's an entity.
Here is probably where there's the "short-circuit" you're experiencing (I'd say a bug, but needs testing).
If you're already quoting correctly entities, then the "check_reserved" argument should not alert you that you're using an entity name that is a "verb".

Is this more clear ?

[Simon Ashley]

Thanks Niphlod, its the latter. A question just concerning rname/ quoting 101. 

Haven't used it before, and trying baby steps to see how it works.

(couldn't see it hitting the relevant sections of dal.py)

A simple working example would give me a push in the right direction.

While Limedrop raises interesting points which I''ll explore, its not where my headspace is currently at.

[Niphlod]

if it's what I think, then 

db = DAL(...., check_reserved=None)
db.define_table('easy',
       Field('position', rname='"difficult_name_for_the_field"'),
rname='"difficult_name_for_table"')

should work (albeit, if it does, it shows "the bug" because if you're using rname, then you don't need to check for reserved keywords).

The point of rname (in addition to give the possibility to use the proper quoting directly) is also to separate model from the underling structure.

Usually I'm dealing with table names like t_ABCD_Alpha_Blocking_Common_Dolls and fields like ABCD_CreationDateForTicket (very enteprisey.... ^_^''') ... until rname my code was full of

db(db.t_ABCD_Alpha_Blocking_Common_Dolls.ABCD_CreationDateForTicket > request.now).select(db.t_ABCD_Alpha_Blocking_Common_Dolls.ALL)

that eventually turned up to be

tb = db.t_ABCD_Alpha_Blocking_Common_Dolls
db(tb.ABCD_CreationDateForTicket > request.now ).select(tb.ALL)

but it was a vibe-killer.

Now I can

db.define_table('ABCD',
Field('CreateDate', rname='"ABCD_CreationDateForTicket"'),
rname='"t_ABCD_Alpha_Blocking_Common_Dolls"')

and use 

db(db.ABCD.CreateDate > request.now).select(db.ABCD.ALL)

[Michele Comitini]

You can also use explicit quoting of entities at DAL level.  It works transparently so you can use the same names in python and in the DB schema.

db = DAL('postgres://...', ...,ignore_field_case=False, entity_quoting=True)

db.define_table('table1', Field('column'), Field('COLUMN'))

print db(db.table1.COLUMN != db.table1.column).select()

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Simon Ashley]

Thanks Niphlod. The missing piece was check_reserved=None. Had left it at the default. Thanks Michele.