DAL migration problem
baloan
I'm using PostgreSQL. Changing db.py from
db.define_table('position',
Field("day", 'date', unique = True),
Field("quantity", "double"),
)
to
db.define_table('position',
Field("date", 'date', unique = True),
Field("quantity", "double"),
)
causes:
Traceback (most recent call last):
File "D:\Home\web\web2py\gluon\restricted.py", line 173, in
restricted
exec ccode in environment
File "applications\invest\models/db.py", line 95, in <module>
Field("quantity", "double"),
File "D:\Home\web\web2py\gluon\sql.py", line 1247, in define_table
t._create(migrate=migrate, fake_migrate=fake_migrate)
File "D:\Home\web\web2py\gluon\sql.py", line 1703, in _create
fake_migrate=fake_migrate)
File "D:\Home\web\web2py\gluon\sql.py", line 1757, in _migrate
self._db._execute(sub_query)
File "D:\Home\web\web2py\gluon\sql.py", line 950, in <lambda>
self._execute = lambda *a, **b: self._cursor.execute(*a, **b)
ProgrammingError: column "date" does not exist
LINE 1: UPDATE position SET date__tmp=date;
^
I expected those changes to be migrated automatically. What is wrong?
How to fix?
Regards, Andreas
mdipierro
you have
Field("date", 'date', unique = True)
"date" is a reserved keyword and cannot be the name of a field/
column.
Massimo
Richard
which wasted some time - it will be neat when the new DAL takes care
of these problems! (It will, right?)
mdipierro
current DAL. It will be easy to add this feature when done.
villas
Unfortunately so many people only avoid the ones for their own chosen
database, which is such a shame when it means their code is not
portable.
Anyway, this checklist is my favourite reference when I have a
doubt:
http://drupal.org/node/141051
--David
Thadeus Burgess
-Thadeus
> --
> You received this message because you are subscribed to the Google Groups "web2py-users" group.
> To post to this group, send email to web...@googlegroups.com.
> To unsubscribe from this group, send email to web2py+un...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/web2py?hl=en.
>
>
baloan
Btw, DATE is not marked as a reserved keyword on PostgreSQL and I can
create DATE columns using SQL or the admin tools (pgadmin).
Consequently I was doubly confused as DATE appears as a web2py
reserved word to me.
@Massimo: in your leisure time ;-) you may consider to change the
ProgrammingError exception to give a hint I used a "web2py/SQL
reserved word".
Regards, Andreas
Thadeus Burgess
the Field() class.
For now just the common SQL keywords, which I'm sure can be extended
to include backend-specific keywords.
-Thadeus
Thadeus Burgess
most logical I see is the Field.__init__ constructor.
-Thadeus
Thadeus Burgess
DAL.__init__ can have for fields if fieldname in
self._adapter.KEYWORDS then raise SyntaxError
-Thadeus
Thadeus Burgess
"role" is a sql keyword in ansi-sql, it will be a reserved word in the
next version of Transact-SQL/Sql-server, the other databases should be
fine, and is used in auth.
"table_name" is a reserved keyword in postgresql -
http://www.postgresql.org/docs/8.3/static/sql-keywords-appendix.html
other databases seem to be fine.
Hrm... there are lots of "reserved keywords" in postgresql that I have
used for column names. Makes it annoying when something like "key" is
a perfect name for your column but its reserved. Why does it not fail
then?
-Thadeus
Jonathan Lundell
> Actually, we can use the Adaptors to hold specific keywords. So in
> DAL.__init__ can have for fields if fieldname in
> self._adapter.KEYWORDS then raise SyntaxError
It's be nice to have a way to take advantage of the keyword lists in Adapters that you're not necessarily using. Say I'm developing with SQLite with the intention of moving to MySQL in production; I'd like to be testing against MySQL keywords from the beginning, to avoid nasty surprises later.
I'm not sure what it'd look like; I'd settle for a "strict" name check that used the union of all the keywords. I assume that's not practical, though, if the lists are in unused Adapters?
Thadeus Burgess
I'm not sure what keywords we really want to use or not, but this is a
starting point.
I added two lists to BaseAdapter global. KEYWORDS_ALL and KEYWORDS_COMMON.
My thought is that no tablename or column name should pass through
KEYWORDS_COMMON, then KEYWORDS_ALL can be used to check integration
with other database types. So if it is in KEYWORDS_ALL just log a
warning.
Then each adapter will have their own set of reserved words that will
be stored in a list named KEYWORDS. So a tablename or column name will
fail if it is in either KEYWORDS_COMMON or KEYWORDS, and will send a
warning if it is in KEYWORDS_ALL.
I think this could go into sql.py as just fail on KEYWORDS_COMMON and
warn on KEYWORDS_ALL, and then the new dal will break it down by
database specifics.
What do you think?
It is then just one more step to add to DAL.__init__ a
target_sql_dialect which will use the KEYWORDS of the target adapter
so that you can catch these early on.
I have tested the patch in my system and its actually caught a
handfull of naming no-nos.
-Thadeus
Wes James
Jonathan Lundell
> You can view a proposed change, I have sent this to Massimo to look at.
>
> http://code.google.com/r/thadeusburgess-web2py/source/detail?r=e875496cc5978200fb6c0aa0f85a8df1a945df21
>
> I'm not sure what keywords we really want to use or not, but this is a
> starting point.
>
> I added two lists to BaseAdapter global. KEYWORDS_ALL and KEYWORDS_COMMON.
>
> My thought is that no tablename or column name should pass through
> KEYWORDS_COMMON, then KEYWORDS_ALL can be used to check integration
> with other database types. So if it is in KEYWORDS_ALL just log a
> warning.
>
> Then each adapter will have their own set of reserved words that will
> be stored in a list named KEYWORDS. So a tablename or column name will
> fail if it is in either KEYWORDS_COMMON or KEYWORDS, and will send a
> warning if it is in KEYWORDS_ALL.
>
> I think this could go into sql.py as just fail on KEYWORDS_COMMON and
> warn on KEYWORDS_ALL, and then the new dal will break it down by
> database specifics.
>
> What do you think?
I think it's a pretty good start. A couple of nits, though.
1. The test ought to be encapsulated somewhere, presumably in dal.py, so you're not repeating the logic so many places.
2. I'm not so sure about logging a warning; seems like it'd be noisy. Could it maybe be a property of a table that defaults to COMMON but can be set to ALL at table-creation time? Or something like that? I probably don't care about making the test in production, after all.
3. KEYWORDS_COMMON as a name: I assume you mean "keywords that are commonly reserved"? It also suggests "keywords that are common to all adapters". Regardless, it's ambiguous; at the very least the criteria for inclusion should be documented in comments with the lists.
Thadeus Burgess
And then this needs to be done for sql.py just in case
2. Yes very chatty. What about the following?
DAL('sqlite....', pool_size=1, check_reserve=None)
DAL('sqlite....', pool_size=1, check_reserve='common')
DAL('sqlite....', pool_size=1, check_reserve='all')
DAL('sqlite....', pool_size=1, check_reserve='postgres')
DAL('sqlite....', pool_size=1, check_reserve='mssql')
This way we can define the level of check. Then the check will be
placed in a function of db. db._verify_reserved_keyword(keyword) which
will perform the checks based on the self.check_reserve.
None would be no name checking, to keep backwards compatibility and
for production systems. comments?
3. How is it ambiguous, you hit the definition right on target. Its
common SQL commands that without these commands you don't have SQL,
and if you use one of these terms in the current system you get an
OperationalError anyways.
-Thadeus
Jonathan Lundell
> 1. Its only in two places. Once for tablename, and once for fieldname.
> And then this needs to be done for sql.py just in case
DRY
>
> 2. Yes very chatty. What about the following?
>
> DAL('sqlite....', pool_size=1, check_reserve=None)
> DAL('sqlite....', pool_size=1, check_reserve='common')
> DAL('sqlite....', pool_size=1, check_reserve='all')
> DAL('sqlite....', pool_size=1, check_reserve='postgres')
> DAL('sqlite....', pool_size=1, check_reserve='mssql')
>
> This way we can define the level of check. Then the check will be
> placed in a function of db. db._verify_reserved_keyword(keyword) which
> will perform the checks based on the self.check_reserve.
> None would be no name checking, to keep backwards compatibility and
> for production systems. comments?
Makes sense. How about accepting a list?
>
> 3. How is it ambiguous, you hit the definition right on target. Its
> common SQL commands that without these commands you don't have SQL,
> and if you use one of these terms in the current system you get an
> OperationalError anyways.
OK...
Richard
I develop with sqlite but deploy on postgres/GAE and almost always get
caught by this.
Thadeus Burgess
I made it DRY. Also you pass a list of options. Here is example usage
db = DAL('sqlite....', pool_size...., check_reserve=['common',
'postgres', 'mysql'])
check_reserve is a list of backends to use, with two extra terms 'all'
and 'common'. It defaults to None, which performs no check.
If you set common, it will check the KEYWORDS_COMMON list, and throw a
SyntaxError.
If you set all, it will check the KEYWORDS_ALL list, and print a
warning message.
If you set an adapter name, it will check the adapters KEYWORDS and
throw a SyntaxError.
This only effects the new dal, (dal.py) since i do not have time to
implement this for sql.py.
As Jonathan Lundell suggests in his mercurial post, you can code
review this change. Once we are happy, Massimo can pull the changes
in.
-Thadeus
Thadeus Burgess
specific keywords lists.
-Thadeus
Jonathan Lundell
> Ok take a look at the following proposed changes
I'll try to look at it later today; I'm off to work now.
Is the check made every time, or only when a table is actually being created (or migrated)? The latter should be adequate, at least by default, and would have less impact on performance.
A side thought: the lists probably ought to be frozensets, not arrays, for performance. And KEYWORDS_ALL ought to be a union (calculated once) of the other sets.
Thadeus Burgess
B) Every time unless you set check_reserve to None. In production you
would have check_reserve to None.
C) It should be a union, and that is exactly what its documentation I
added says. But we can't do a union until we actually create database
specific keywords!!!! until then ALL will have to suffice.
-Thadeus
mdipierro
On Feb 3, 12:33 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
> A) Bring home the bacon !
>
> B) Every time unless you set check_reserve to None. In production you
> would have check_reserve to None.
>
> C) It should be a union, and that is exactly what its documentation I
> added says. But we can't do a union until we actually create database
> specific keywords!!!! until then ALL will have to suffice.
>
> -Thadeus
>
Thadeus Burgess
sql.py is way to spaghetti and will take longer than I have available
to make it work for sql.py.
When will we be able to use the new dal?
-Thadeus
mdipierro
Before I can make it work on GAE I need to abstract a little more
about insert and select.
Massimo
On Feb 3, 2:55 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
> I know I didn't put one there.
>
> sql.py is way to spaghetti and will take longer than I have available
> to make it work for sql.py.
>
> When will we be able to use the new dal?
>
> -Thadeus
>
Jonathan Lundell
> Ok take a look at the following proposed changes
Comments.
* Could we use 'check_reserved' instead of 'check_reserve'? It sounds more natural to me.
* Capitalize SQL in the error message strings
* dal.py is already much too long. Could the wordlists live somewhere else?
* Is 'print' the most appropriate way to show the warning message? How about at least sending it to stderr, or see the next point:
* I don't entirely get the rationale for the distinction between the warning message and raising a syntax error, at least not based on the selected list. Since this is intended for development, not production, why not just make it a syntax error and be done with it? Or if it's important, make it a separate option.
* I remain uneasy about the idea of doing this on every call (vs calls that make actual changes in the db). I don't know what the performance overhead is of the lookup (I suspect that frozenset would be faster--at the very least something immutable), so maybe it's a non-concern. I'm also uneasy about proliferating flags that you have to remember to turn off for production; it's too easy to forget about once it's working.
Thadeus Burgess
Why not.
>
> * Capitalize SQL in the error message strings
It is capitalized.
>
> * dal.py is already much too long. Could the wordlists live somewhere else?
No, they belong with their relative adapters. Object Oriented devel at its best.
>
> * Is 'print' the most appropriate way to show the warning message? How about at least sending it to stderr, or see the next point:
>
> * I don't entirely get the rationale for the distinction between the warning message and raising a syntax error, at least not based on the selected list. Since this is intended for development, not production, why not just make it a syntax error and > be done with it? Or if it's important, make it a separate option.
>
here is my rational. the reason for displaying an error is because the
KEYWORDS_ALL contains all known sql keywords. Therefore it contains
keywords that might not necessarily effect your deployment, so it
shouldn't stop the software from running, but at least log a warning
letting you know so that you can change it if you want. Sure we can
print to stderr. KEYWORDS_ALL is a placeholder until we devote the
time to creating adapter specific lists. In fact once each adapter has
their own list KEYWORDS_ALL probably won't exist any more, and the
`all` option will use a union of all the lists.
You said it yourself, you don't want more proliferating flags, so no
separate option. However if anyone else has an opinion on the matter?
>
> * I remain uneasy about the idea of doing this on every call (vs calls that make actual changes in the db). I don't know what the performance overhead is of the lookup (I suspect that frozenset would be faster--at the very least something immutable), so maybe it's a non-concern. I'm also uneasy about proliferating flags that you have to remember to turn off for production; it's too easy to forget about once it's working.
I don't understand what you are so concerned about the performance
overhead (like a fraction of a fraction of a fraction of a tiny
fraction of a nanosecond that it takes?)
Let me qoute you again "Since this is intended for development, not
production..."
...I made my case, as long as check_reserved is set to None it won't
do the check. (None is the default)
And about the flags... you have to change many other options when
moving into deployment (such as db connection string to the DAL...
which ill be damned if its not in the same constructor as this flag).
So let me get this straight, you want the option to target multiple
database types that YOU want... but what..., web2py is supposed to
play God and read your mind? How else would web2py determine what
target backends you are looking for.
Then you raise a good point, there is no sense in adding a flag just
for one option, perhaps a better name such as ``target_adapters=[]``
instead of check_reserve. That way the list could be used for multiple
purposes that might arise in the future. such as higher compatibility
layers that check joins vs. pagination etc.
Jonathan Lundell
>> * Could we use 'check_reserved' instead of 'check_reserve'? It sounds more natural to me.
>
> Why not.
>
>>
>> * Capitalize SQL in the error message strings
>
> It is capitalized.
raise SyntaxError, 'invalid name \'%s\': is a \'common\' reserved sql keyword' % name
>
>>
>> * dal.py is already much too long. Could the wordlists live somewhere else?
>
> No, they belong with their relative adapters. Object Oriented devel at its best.
I was thinking of COMMON & ALL.
>
>>
>> * Is 'print' the most appropriate way to show the warning message? How about at least sending it to stderr, or see the next point:
>>
>> * I don't entirely get the rationale for the distinction between the warning message and raising a syntax error, at least not based on the selected list. Since this is intended for development, not production, why not just make it a syntax error and > be done with it? Or if it's important, make it a separate option.
>>
>
> here is my rational. the reason for displaying an error is because the
> KEYWORDS_ALL contains all known sql keywords. Therefore it contains
> keywords that might not necessarily effect your deployment, so it
> shouldn't stop the software from running, but at least log a warning
> letting you know so that you can change it if you want. Sure we can
> print to stderr. KEYWORDS_ALL is a placeholder until we devote the
> time to creating adapter specific lists. In fact once each adapter has
> their own list KEYWORDS_ALL probably won't exist any more, and the
> `all` option will use a union of all the lists.
>
> You said it yourself, you don't want more proliferating flags, so no
> separate option. However if anyone else has an opinion on the matter?
Here's my argument for always a syntax error: why not stop it from running? Once I get the error, I'm either going to turn off the check, or fix the error. I'm not going to leave it logging any more than I'll leave it raising an exception, since it's going to log on every request. (BTW, if I'm developing through the GUI, where do I see the prints?)
>
>>
>> * I remain uneasy about the idea of doing this on every call (vs calls that make actual changes in the db). I don't know what the performance overhead is of the lookup (I suspect that frozenset would be faster--at the very least something immutable), so maybe it's a non-concern. I'm also uneasy about proliferating flags that you have to remember to turn off for production; it's too easy to forget about once it's working.
>
> I don't understand what you are so concerned about the performance
> overhead (like a fraction of a fraction of a fraction of a tiny
> fraction of a nanosecond that it takes?)
If it takes less than a nanosecond, then I have no concern at all. (You must have a very fast Python interpreter.)
>
> Let me qoute you again "Since this is intended for development, not
> production..."
>
> ...I made my case, as long as check_reserved is set to None it won't
> do the check. (None is the default)
>
> And about the flags... you have to change many other options when
> moving into deployment (such as db connection string to the DAL...
> which ill be damned if its not in the same constructor as this flag).
>
> So let me get this straight, you want the option to target multiple
> database types that YOU want... but what..., web2py is supposed to
> play God and read your mind? How else would web2py determine what
> target backends you are looking for.
I don't think I follow you here.
>
> Then you raise a good point, there is no sense in adding a flag just
> for one option, perhaps a better name such as ``target_adapters=[]``
> instead of check_reserve. That way the list could be used for multiple
> purposes that might arise in the future. such as higher compatibility
> layers that check joins vs. pagination etc.
Sure. A nit: our target isn't an adapter, it's the database variant. Adapters don't have keywords; databases do. If you take my meaning.
Thadeus Burgess
attempting to call your column, it could be 'select', 'Select',
'SELect'. It just seemed more logical to display the name that you
wrote. Though it can be capitalized. I thought you were referring to
the name of the database it was part of.
2. Yes, but then you wouldn't be able to do
self._adapter.KEYWORDS_COMMON or db._adapter.KEY.... I don't see a
reason for the following either, adding yet another import to
web2py... (though I agree, I don't like how long dal/sql.py is either)
# pseudocode
class BaseAdapter(..):
KEYWORDS_COMMON = from keywords import KEYWORDS_COMMON
3. Good point, I agree, SyntaxError it is.
4. Its no different than the "if if hasattr(self,tablename) or
tablename[0] == '_': and if tablename in self.tables: checks that
already exist there.
5. You say that you don't want to proliferate more flags into the
web2py environment. I ask, how then would you know what database you
want to check against?
6. So all in support for target_databases for the name instead of
check_reserved?
-Thadeus
Jonathan Lundell
> 1. I do not capitalize name because this is actually what you are
> attempting to call your column, it could be 'select', 'Select',
> 'SELect'. It just seemed more logical to display the name that you
> wrote. Though it can be capitalized. I thought you were referring to
> the name of the database it was part of.
I'm not sure what you're replying to here. I was suggesting changing the literal string 'sql' to 'SQL'.
>
> 2. Yes, but then you wouldn't be able to do
> self._adapter.KEYWORDS_COMMON or db._adapter.KEY.... I don't see a
> reason for the following either, adding yet another import to
> web2py... (though I agree, I don't like how long dal/sql.py is either)
>
> # pseudocode
> class BaseAdapter(..):
> KEYWORDS_COMMON = from keywords import KEYWORDS_COMMON
Perhaps the long-term solution is to look at how dal.py could be modularized.
>
> 3. Good point, I agree, SyntaxError it is.
>
> 4. Its no different than the "if if hasattr(self,tablename) or
> tablename[0] == '_': and if tablename in self.tables: checks that
> already exist there.
I'm conjecturing that testing against a long mutable array is expensive. I could be wrong, of course.
>
> 5. You say that you don't want to proliferate more flags into the
> web2py environment. I ask, how then would you know what database you
> want to check against?
Ah, I see. No, that's not my point. My point is not proliferation of options in general; it's proliferation of options that need to be changed for production.
It's not that hard to work around, of course; I can always define a global production flag.
Thadeus Burgess
thought you ment the keyword that was being thrown off.
It will be an immutable array (tuple). The bracket just slipped in there :P
Any ideas you could share on modularizing dal.py that could allow this
to be in another file?
-Thadeus
Jonathan Lundell
> Ok I get what you mean on changing sql to SQL. Misunderstanding, I
> thought you ment the keyword that was being thrown off.
>
> It will be an immutable array (tuple). The bracket just slipped in there :P
>
> Any ideas you could share on modularizing dal.py that could allow this
> to be in another file?
Sadly, no.
Where it really jumps out at me is looking at the diffs on Google Code; it ends up being a very long page. And I didn't notice a way to find the next diff except for scrolling (not that I looked very hard).
villas
I will send to you a file with the Firebird keywords via email. There
are differences between versions but I have tried to amalgamate all.
I am never quite sure about the non-reserved keywords, so I include
those too.
Note that my file will only include the Firebird words. Let me know if
you would like me to edit it further.
Thank you for working on this.
--David
Jonathan Lundell
> As an added note, I could use some help in creating the database
> specific keywords lists.
(I might have sent this before?)
Thadeus Burgess
into a python tuple object.
-Thadeus
Jonathan Lundell
> Yes, there is much work to be done formatting each one of those lists
> into a python tuple object.
That part isn't so bad. I need to know which ones you want. Here's MySQL 5.5:
'ACCESSIBLE',
'ADD',
'ALL',
'ALTER',
'ANALYZE',
'AND',
'AS',
'ASC',
'ASENSITIVE',
'BEFORE',
'BETWEEN',
'BIGINT',
'BINARY',
'BLOB',
'BOTH',
'BY',
'CALL',
'CASCADE',
'CASE',
'CHANGE',
'CHAR',
'CHARACTER',
'CHECK',
'COLLATE',
'COLUMN',
'CONDITION',
'CONSTRAINT',
'CONTINUE',
'CONVERT',
'CREATE',
'CROSS',
'CURRENT_DATE',
'CURRENT_TIME',
'CURRENT_TIMESTAMP',
'CURRENT_USER',
'CURSOR',
'DATABASE',
'DATABASES',
'DAY_HOUR',
'DAY_MICROSECOND',
'DAY_MINUTE',
'DAY_SECOND',
'DEC',
'DECIMAL',
'DECLARE',
'DEFAULT',
'DELAYED',
'DELETE',
'DESC',
'DESCRIBE',
'DETERMINISTIC',
'DISTINCT',
'DISTINCTROW',
'DIV',
'DOUBLE',
'DROP',
'DUAL',
'EACH',
'ELSE',
'ELSEIF',
'ENCLOSED',
'ESCAPED',
'EXISTS',
'EXIT',
'EXPLAIN',
'FALSE',
'FETCH',
'FLOAT',
'FLOAT4',
'FLOAT8',
'FOR',
'FORCE',
'FOREIGN',
'FROM',
'FULLTEXT',
'GRANT',
'GROUP',
'HAVING',
'HIGH_PRIORITY',
'HOUR_MICROSECOND',
'HOUR_MINUTE',
'HOUR_SECOND',
'IF',
'IGNORE',
'IGNORE_SERVER_IDS',
'IGNORE_SERVER_IDS',
'IN',
'INDEX',
'INFILE',
'INNER',
'INOUT',
'INSENSITIVE',
'INSERT',
'INT',
'INT1',
'INT2',
'INT3',
'INT4',
'INT8',
'INTEGER',
'INTERVAL',
'INTO',
'IS',
'ITERATE',
'JOIN',
'KEY',
'KEYS',
'KILL',
'LEADING',
'LEAVE',
'LEFT',
'LIKE',
'LIMIT',
'LINEAR',
'LINES',
'LOAD',
'LOCALTIME',
'LOCALTIMESTAMP',
'LOCK',
'LONG',
'LONGBLOB',
'LONGTEXT',
'LOOP',
'LOW_PRIORITY',
'MASTER_HEARTBEAT_PERIOD',
'MASTER_HEARTBEAT_PERIOD',
'MASTER_SSL_VERIFY_SERVER_CERT',
'MATCH',
'MAXVALUE',
'MAXVALUE',
'MEDIUMBLOB',
'MEDIUMINT',
'MEDIUMTEXT',
'MIDDLEINT',
'MINUTE_MICROSECOND',
'MINUTE_SECOND',
'MOD',
'MODIFIES',
'NATURAL',
'NO_WRITE_TO_BINLOG',
'NOT',
'NULL',
'NUMERIC',
'ON',
'OPTIMIZE',
'OPTION',
'OPTIONALLY',
'OR',
'ORDER',
'OUT',
'OUTER',
'OUTFILE',
'PRECISION',
'PRIMARY',
'PROCEDURE',
'PURGE',
'RANGE',
'READ',
'READ_WRITE',
'READS',
'REAL',
'REFERENCES',
'REGEXP',
'RELEASE',
'RENAME',
'REPEAT',
'REPLACE',
'REQUIRE',
'RESIGNAL',
'RESIGNAL',
'RESTRICT',
'RETURN',
'REVOKE',
'RIGHT',
'RLIKE',
'SCHEMA',
'SCHEMAS',
'SECOND_MICROSECOND',
'SELECT',
'SENSITIVE',
'SEPARATOR',
'SET',
'SHOW',
'SIGNAL',
'SIGNAL',
'SMALLINT',
'SPATIAL',
'SPECIFIC',
'SQL',
'SQL_BIG_RESULT',
'SQL_CALC_FOUND_ROWS',
'SQL_SMALL_RESULT',
'SQLEXCEPTION',
'SQLSTATE',
'SQLWARNING',
'SSL',
'STARTING',
'STRAIGHT_JOIN',
'TABLE',
'TERMINATED',
'THEN',
'TINYBLOB',
'TINYINT',
'TINYTEXT',
'TO',
'TRAILING',
'TRIGGER',
'TRUE',
'UNDO',
'UNION',
'UNIQUE',
'UNLOCK',
'UNSIGNED',
'UPDATE',
'USAGE',
'USE',
'USING',
'UTC_DATE',
'UTC_TIME',
'UTC_TIMESTAMP',
'VALUES',
'VARBINARY',
'VARCHAR',
'VARCHARACTER',
'VARYING',
'WHEN',
'WHERE',
'WHILE',
'WITH',
'WRITE',
'XOR',
'YEAR_MONTH',
'ZEROFILL',
Thadeus Burgess
-Thadeus
Thadeus Burgess
Should they be included, excluded, separated ? non-reserved words are
acceptable and do not get in the way of your queries.
-Thadeus
Jonathan Lundell
> A quick question, what is your opinion on non-reserved keywords ?
> Should they be included, excluded, separated ? non-reserved words are
> acceptable and do not get in the way of your queries.
I don't know enough to have an opinion, but if they're not reserved, what the heck?
Thadeus Burgess
-Thadeus
Thadeus Burgess
Ok take a look at this now. I am happy with this. It only adds around
15kb added memory on my system, none if you have the check disabled.
I did move the keywords to a separate file. You were right in that it
would make dal.py much bigger than any of us would bear. It only
imports the tuples if you are using check_reserved.
The advantage, sql.py supports the checking too! :)
Here is how to use it. Note you will probably want to disable this for
production. Just set check_reserved=None (the default)
db = DAL('sqlite://mydb.db', check_reserved=['mssql', 'postgres'])
check_reserve -- list of adapters to check tablenames and column names
against sql reserved keywords
Is a list containing the adapter names, all, or common.
(Default None)
common -- List of sql keywords that are common to all
database types such as "SELECT, INSERT". (recommended)
all -- Checks against all known SQL keywords. (not recommended)
<adaptername> -- Checks against the specific adapters
list of keywords (recommended)
<adaptername>_nonreserved -- Checks against the specific
adapters list of nonreserved keywords. (if available)
I have added mssql, and oracle.
I need
'sqlite': SQLITE,
'db2': DB2,
'informix': INFORMIX,
'ingres': INGRES,
'ingresu': INGRES,
'jdbc:sqlite': JDBCSQLITE,
'jdbc:postgres': JDBCPOSTGRESQL,
-Thadeus
villas
> A quick question, what is your opinion on non-reserved keywords ?
> Should they be included, excluded, separated ? non-reserved words are
> acceptable and do not get in the way of your queries.
Re: "Non-reserved":
It seems that the definition of non-reserved varies a little depending
on the DB.
The widest definition appears to be: any word which is recognised by
the parser.
A more common definition appears to be: a word which has a special
meaning in a particular context but which may be used as an identifier
in other contexts.
I have noticed that a non-reserved word can often be upgraded to be a
reserved word in a later DB version.
Conclusion: It seems safer to include non-reserved words too.
However, if your code checker can identify both, brilliant.
Thadeus Burgess
POSTGRES for reserved words and POSTGRES_NONRESERVED, so you can check
both if you so desire.
To check nonreserved keywords as well then just append _nonreserved
after the adapter name
check_reserved = ['postgres', 'postgres_nonreserved']
-Thadeus