Thread: [SQLObject] Know bugs in Firebird
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
Menu
▾
▴
sqlobject-discuss
|
From: Ian B. <ia...@co...> - 2003-09-27 23:03:15
|
I sent this to James, but then I though Ian or someone else might want to look at these. Currently these are the known issues. James may have fixes for some of these that I lost. * kinterbasdb seems to rollback if you don't explicitly commit. I don't think there's any autocommit mode, which is how a lot of the tests are meant to be run. I've had this problem with SQLite as well, I think. Anyway, I think if _runWithConnection does a begin/commit, that should simulate autocommit, but I was getting problems with that. So now it tries to begin/commit but ignores errors. It's dumb. Maybe you have a better idea. * NamesTest doesn't create the table correctly. Is "names" a reserved word? * DeleteSelectTest gives an odd error, "Cursor unknown". Not sure what's up with that. * ValidationText, I get a bunch of 'conversion error from string "BLOB".' * SliceTest -- I think you had some changes with this which I may have missed in the files you sent me. Then, like SQLite, the string quoting has to be fixed. But I'll have to think about that some. Ian |
|
From: Ian S. <ia...@et...> - 2003-09-28 02:36:10
|
"Ian Bicking" <ia...@co...> wrote in message news:A6B...@co...... > I sent this to James, but then I though Ian or someone else might want > to look at these. Thanks. > * NamesTest doesn't create the table correctly. Is "names" a reserved > word? Yes. It occured to me in the few hours working with SQLObject that there are a number of simple ways to break it. Two things that would help novices would be a list of reserved words for each DB and a "__MaxIdentifierLength" property (31chars for Interbase as I recall, James?). Remedy could be failure or name-mangling. Whatever, some warning would be nice. The complete list of keywords can be found in the IB 6 manuals starting at page 184 Section "Keywords". Firebird has added some more but I'm not sure this is well documented. If you decide you want to add reserved word support just say how you want this formatted and I'll contribute it. - Ian Sparks. |
|
From: James R. <jcr...@ya...> - 2003-09-28 20:09:51
|
--- Ian Sparks <ia...@et...> wrote: > It occured to me in the few hours working with > SQLObject that there are a > number of simple ways to break it. Two things that > would help novices would > be a list of reserved words for each DB and a > "__MaxIdentifierLength" > property (31chars for Interbase as I recall, > James?). Remedy could be > failure or name-mangling. Whatever, some warning > would be nice. That is correct. The RDB$RELATIONS table has RDB$RELATION_NAME (the field where the field name is stored) defined as char(31). I think this may have changed in 1.5, though. James __________________________________ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com |
|
From: Ian B. <ia...@co...> - 2003-09-28 20:46:02
|
On Saturday, September 27, 2003, at 09:41 PM, Ian Sparks wrote: >> * NamesTest doesn't create the table correctly. Is "names" a reserved >> word? > > Yes. > > It occured to me in the few hours working with SQLObject that there > are a > number of simple ways to break it. Two things that would help novices > would > be a list of reserved words for each DB and a "__MaxIdentifierLength" > property (31chars for Interbase as I recall, James?). Remedy could be > failure or name-mangling. Whatever, some warning would be nice. > > The complete list of keywords can be found in the IB 6 manuals > starting at > page 184 Section "Keywords". Firebird has added some more but I'm not > sure > this is well documented. If you decide you want to add reserved word > support > just say how you want this formatted and I'll contribute it. It's probably easier if we just start quoting all the columns. That's pretty easy with automatic SQL generation anyway. Postgres uses "column", MySQL `column`, what does Firebird use? (SQLite?) That doesn't fix the 31 character limit. Raising an exception would be best in that case. It confuses me terribly when I hit that limit (I think Postgres has the same limit) and names get truncated. Ian |
|
From: Luke O. <lu...@me...> - 2003-09-28 23:10:44
|
Quoting Ian Bicking <ia...@co...>: > It's probably easier if we just start quoting all the columns. That's > pretty easy with automatic SQL generation anyway. Postgres uses > "column", MySQL `column`, what does Firebird use? (SQLite?) Just a caution, in PostgreSQL quoting column/table names makes the name case-sensitive. So "Users" doesn't match dbname users but Users does. :) Not a big issue, although it may break some existing schema (including ones created by SQLObject with a not-all-lowercase db style). > That doesn't fix the 31 character limit. Raising an exception would be > best in that case. It confuses me terribly when I hit that limit (I > think Postgres has the same limit) and names get truncated. 31 chars in Postgres 7.2, 63 chars in 7.3+. I believe it's a silent truncation though, so SO will need to be aware of these things beforehand to make an exception, and also keep in mind that the limit is actually less when you consider that automatic sequence and constraint names need to fit in the limit (tablename_colname_seq is the default sequence name template). Personally I'm not sure SQLObject needs to deal with all these cases, they're just part of the responsibility of the developer in choosing a database platform. But it would be reasonable to make a db-specific faq, detailing these things to watch out for that SO does behind the scenes. - Luke |
|
From: Ian S. <ia...@et...> - 2003-10-01 13:04:29
|
"Ian Bicking" <ia...@co...> wrote in message news:A61...@co...... > It's probably easier if we just start quoting all the columns. That's > pretty easy with automatic SQL generation anyway. Postgres uses > "column", MySQL `column`, what does Firebird use? (SQLite?) Firebird/Interbase quoting example : CREATE TABLE "my test" ( id INT NOT NULL, username VARCHAR(20) NOT NULL, "my other test" VARCHAR(2) ) What Luke says about quoting and case-sensitivity is the same for Firebird too. For the above table this is valid : SELECT * FROM "my test" this is not : SELECT * FROM "MY TEST" neither is this : SELECT "my OTHER test" FROM "my test" Overall quoting seems a good let-out of our reserved word problems but at a cost.... - Ian S. |
|
From: Dirk E. <D....@ex...> - 2003-10-01 13:38:21
|
I used Ian's Firebird/Interbase quoting example and got the same results for Oracle. - Dirk -- -------------------------------------------------------------------------- Dirk Evers Bioinformatics Research Scientist Exelixis Deutschland Fon: +49 (0)7071-9655-16 Spemannstr. 35 Fax: +49 (0)7071-9655-96 D-72076 Tuebingen, Germany URL: http://www.exelixis.com/ -------------------------------------------------------------------------- |
|
From: James R. <jcr...@ya...> - 2003-09-28 20:43:03
|
--- Ian Bicking <ia...@co...> wrote:
> I sent this to James, but then I though Ian or
> someone else might want
> to look at these.
I got it, thanks.
> Currently these are the known issues. James may
> have fixes for some of
> these that I lost.
>
> * kinterbasdb seems to rollback if you don't
> explicitly commit. I
> don't think there's any autocommit mode, which is
> how a lot of the
> tests are meant to be run. I've had this problem
> with SQLite as well,
> I think.
I haven't run the test suite in the "mostly live"
version within the last two weeks. But, when I
changed the supportTransaction attribute in
FirebirdConnection to true, but set the
SQLObjectTest.supportTransaction to false, many of the
test failures went away. Go figure.
FB wants to put everything in a transaction (even a
select).
> Anyway, I think if _runWithConnection does a
> begin/commit, that should
> simulate autocommit, but I was getting problems with
> that. So now it
> tries to begin/commit but ignores errors. It's
> dumb. Maybe you have a
> better idea.
Because it's so transactional, my idea (which I don't
even like) is to add (some sort of) a
requiresTransaction logic to SO.
> * NamesTest doesn't create the table correctly. Is
> "names" a reserved
> word?
Yes.
> * DeleteSelectTest gives an odd error, "Cursor
> unknown". Not sure
> what's up with that.
That particular error is new to the cvs version (it
worked fine in .4). I think when the _iterSelect
added the 'yield' call, things went wierd.
>
> * ValidationText, I get a bunch of 'conversion error
> from string
> "BLOB".'
That error (while cryptic) is caused by FB not doing a
implicit convert for an int value sent (by the
Validator). If you change the 'default' value (kw arg
in test.py to '100' instead of just 100) that one
should go away also.
> * SliceTest -- I think you had some changes with
> this which I may have
> missed in the files you sent me.
The changes were in DBConnection.py. Since FB puts
the limit clause directly after the 'select'
statement, I had to change a couple of your calls (see
below):
def queryForSelect(self, select):
ops = select.ops
cls = select.sourceClass
if ops.get('lazyColumns', 0):
q = "SELECT %s.%s FROM %s WHERE " % \
(cls._table, cls._idName,
", ".join(select.tables))
else:
q = "SELECT %s.%s, %s FROM %s WHERE " % \
(cls._table, cls._idName,
", ".join(["%s.%s" % (cls._table,
col.dbName)
for col in
cls._SO_columns]),
", ".join(select.tables))
########### changes start###################
select.selectBegin = q
return self.whereClauseForSelect(select)
############changes end######################
def whereClauseForSelect(self, select, limit=1,
order=1):
q = str(select.clause)
ops = select.ops
def clauseList(lst, desc=False):
if type(lst) not in (type([]), type(())):
lst = [lst]
lst = [clauseQuote(i) for i in lst]
if desc:
lst = [SQLBuilder.DESC(i) for i in
lst]
return ', '.join([SQLBuilder.sqlRepr(i)
for i in lst])
def clauseQuote(s):
if type(s) is type(""):
if s.startswith('-'):
desc = True
s = s[1:]
else:
desc = False
assert SQLBuilder.sqlIdentifier(s),
"Strings in clauses are expected to be column
identifiers. I got: %r" % s
if
select.sourceClass._SO_columnDict.has_key(s):
s =
select.sourceClass._SO_columnDict[s].dbName
if desc:
return
SQLBuilder.DESC(SQLBuilder.SQLConstant(s))
else:
return SQLBuilder.SQLConstant(s)
else:
return s
if order and ops.get('groupBy'):
q = "%s GROUP BY %s" % (q,
clauseList(ops['groupBy']))
if order and ops.get('dbOrderBy'):
q = "%s ORDER BY %s" % (q,
clauseList(ops['dbOrderBy'], ops.get('reversed',
False)))
start = ops.get('start', 0)
end = ops.get('end', None)
#####################changes start#############
if hasattr(select, 'selectBegin'):
q = ' '.join([select.selectBegin, q])
#####################changes end ##############
if limit and (start or end):
# @@: Raising an error might be an
annoyance, but some warning is
# in order.
#assert ops.get('orderBy'), "Getting a
slice of an unordered set is unpredictable!"
q = self._queryAddLimitOffset(q, start,
end)
return q
I will double check all of this against the latest
version.
James
__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X