On Jun 9, 2011, at 5:10 PM, MikeCo wrote:
> This should recreate the same table, and will do so in 0.5. In 0.6 and
> 0.7 the default is rendered as DEFAULT A B without the single quote
> enclosing 'A B' leading to an OperationalError exception. The
> interesting thing is that if step 1 is done by creating a Table
> instance directly in the metadata, then the missing single quotes are
> generated correctly. It appears that the root cause is that when
> reflecting the table from sqlite (and maybe other databases as well)
> the the c.server_default.arg property is missing the single quotes in
> 0.6+, but the quotes are present in 0.5. I suspect that DDL generation
> blindly plugs in whatever is present in c.server_default.arg leading
> to the error.
The column inspection features of each backend tend to give to us the default
value appropriately quoted. The SQLite dialect itself is stripping out the
quotes, most likely due to an artifact of 0.5's system which was inconsistent
in its treatment of quoting around server defaults.
Narrowing down your test gives us:
from sqlalchemy import *
from sqlalchemy.schema import CreateTable
e = create_engine('sqlite:///', echo=True)
#e = create_engine('mysql://scott:tiger@localhost/test', echo=True)
#e = create_engine('postgresql://scott:tiger@localhost/test', echo='debug')
meta = MetaData(e)
dflt_tbl = Table('test_default', meta,
Column('id', Integer, primary_key=True),
Column('int_dflt', Integer, server_default=text(str(0))),
Column('str_dflt', String(10), server_default=text("'A B'"))
)
meta.create_all()
m2 = MetaData(e)
t = Table("test_default", m2, autoload=True)
print CreateTable(dflt_tbl).compile(dialect=e.dialect)
print CreateTable(t).compile(dialect=e.dialect)
MySQL and PG both produce a table definition that maintains the default.
SQLite's doesn't due to line 643 of base.py.
Here's a patch:
diff -r f9faaf09e7b7 lib/sqlalchemy/dialects/sqlite/base.py
--- a/lib/sqlalchemy/dialects/sqlite/base.py Wed Jun 08 17:56:00 2011 -0400
+++ b/lib/sqlalchemy/dialects/sqlite/base.py Thu Jun 09 18:28:22 2011 -0400
@@ -637,10 +637,9 @@
row = c.fetchone()
if row is None:
break
- (name, type_, nullable, default, has_default, primary_key) =
(row[1], row[2].upper(), not row[3], row[4], row[4] is not None, row[5])
+ (name, type_, nullable, default, has_default, primary_key) = \
+ (row[1], row[2].upper(), not row[3], row[4], row[4] is not
None, row[5])
name = re.sub(r'^\"|\"$', '', name)
- if default:
- default = re.sub(r"^\'|\'$", '', default)
match = re.match(r'(\w+)(\(.*?\))?', type_)
if match:
coltype = match.group(1)
this is ticket 2189 targeted at 0.7.2, mostly needs tests in this regard (since
the quoting clearly isn't tested):
http://www.sqlalchemy.org/trac/ticket/2189
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
