- changed milestone to 0.5.xx
- changed title to Wrong syntax of "CREATE INDEX" for sqlite with schemas
Wrong syntax of "CREATE INDEX" for sqlite with schemas
It is tested with version 0.5.4p2 (and code is the same on the trunk). I have 2 databases attached to the session ('auth' and 'gate').
Here is the syntax of the "CREATE INDEX" statement:
* CREATE UNIQUE INDEX ix_auth_doom_users_login ON auth.doom_users (login)
[BR]
--> this is the wrong syntax generated by SQLAlchemy
* CREATE UNIQUE INDEX auth.ix_auth_doom_users_login ON doom_users (login)
[BR]
--> this is the syntax understood by SQLite
The "database-name" qualifier should be on the index name, not on the table name.
Reference: * http://www.sqlite.org/lang_createindex.html
Error stack:
File "/home/florent/.buildout/eggs/SQLAlchemy-0.5.4p2-py2.5.egg/sqlalchemy/schema.py", line 1786, in create_all
File "/home/florent/.buildout/eggs/SQLAlchemy-0.5.4p2-py2.5.egg/sqlalchemy/engine/base.py", line 1129, in create
File "/home/florent/.buildout/eggs/SQLAlchemy-0.5.4p2-py2.5.egg/sqlalchemy/engine/base.py", line 1158, in _run_visitor
File "/home/florent/.buildout/eggs/SQLAlchemy-0.5.4p2-py2.5.egg/sqlalchemy/sql/visitors.py", line 89, in traverse
File "/home/florent/.buildout/eggs/SQLAlchemy-0.5.4p2-py2.5.egg/sqlalchemy/sql/visitors.py", line 200, in traverse
File "/home/florent/.buildout/eggs/SQLAlchemy-0.5.4p2-py2.5.egg/sqlalchemy/sql/visitors.py", line 194, in traverse_using
File "/home/florent/.buildout/eggs/SQLAlchemy-0.5.4p2-py2.5.egg/sqlalchemy/sql/compiler.py", line 831, in visit_metadata
File "/home/florent/.buildout/eggs/SQLAlchemy-0.5.4p2-py2.5.egg/sqlalchemy/sql/visitors.py", line 79, in traverse_single
File "/home/florent/.buildout/eggs/SQLAlchemy-0.5.4p2-py2.5.egg/sqlalchemy/sql/compiler.py", line 874, in visit_table
File "/home/florent/.buildout/eggs/SQLAlchemy-0.5.4p2-py2.5.egg/sqlalchemy/sql/visitors.py", line 79, in traverse_single
File "/home/florent/.buildout/eggs/SQLAlchemy-0.5.4p2-py2.5.egg/sqlalchemy/sql/compiler.py", line 980, in visit_index
File "/home/florent/.buildout/eggs/SQLAlchemy-0.5.4p2-py2.5.egg/sqlalchemy/engine/base.py", line 1812, in execute
File "/home/florent/.buildout/eggs/SQLAlchemy-0.5.4p2-py2.5.egg/sqlalchemy/engine/base.py", line 824, in execute
File "/home/florent/.buildout/eggs/SQLAlchemy-0.5.4p2-py2.5.egg/sqlalchemy/engine/base.py", line 888, in _execute_text
File "/home/florent/.buildout/eggs/SQLAlchemy-0.5.4p2-py2.5.egg/sqlalchemy/engine/base.py", line 896, in __execute_context
File "/home/florent/.buildout/eggs/SQLAlchemy-0.5.4p2-py2.5.egg/sqlalchemy/engine/base.py", line 950, in _cursor_execute
File "/home/florent/.buildout/eggs/SQLAlchemy-0.5.4p2-py2.5.egg/sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception
OperationalError: (OperationalError) near ".": syntax error u'CREATE UNIQUE INDEX ix_auth_doom_users_login ON auth.doom_users (login)' ()
Comments (13)
-
repo owner -
repo owner - changed milestone to 0.6.xx
the tedious part of this ticket is that I dont really know how to use schemas with SQLite. if someone could provide a test for this that would be gurrreat, thx
-
Account Deleted With SQLite, the database is contained in a single file.
But you can attach multiple databases to the same connection. Then each database behaves like a "schema" regarding SQL syntax.
More details :[BR] http://www.sqlite.org/lang_attach.html
And about commit involving multiple files:[BR] http://www.sqlite.org/atomiccommit.html#section_5_0 (see section 5)
-
Account Deleted Here is the test case which fails:
>>> import sqlalchemy >>> from sqlalchemy import * >>> sqlalchemy.__version__ '0.5.6' >>> metadata = MetaData() >>> users = Table('users', metadata, ... Column('id', Integer, primary_key=True), ... Column('login', String, index=True), ... schema='auth' ... ) >>> engine = create_engine('sqlite:////tmp/sqldemo_main.db', echo=False) >>> engine.execute('ATTACH "/tmp/sqldemo_auth.db" AS auth') <sqlalchemy.engine.base.ResultProxy object at 0x7ffb0d507450> >>> engine.execute('PRAGMA database_list').fetchall() [u'main', u'/tmp/sqldemo_main.db'), (2, u'auth', u'/tmp/sqldemo_auth.db')]((0,) >>> metadata.create_all(engine) >>> Traceback (most recent call last): File "<stdin>", line 1, in <module> File ".../SQLAlchemy-0.5.6-py2.5.egg/sqlalchemy/schema.py", line 1796, in create_all bind.create(self, checkfirst=checkfirst, tables=tables) ... File ".../SQLAlchemy-0.5.6-py2.5.egg/sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) sqlalchemy.exc.OperationalError: (OperationalError) near ".": syntax error u'CREATE INDEX ix_auth_users_login ON auth.users (login)' () >>>
-
Account Deleted Another example with a single in-memory database (prefix of default database is always 'main'). * First table does not have an index on "login" --> OK * Second table declares an index on "login" --> KO
>>> from sqlalchemy import * >>> metadata = MetaData() >>> engine = create_engine('sqlite:///:memory:', echo=False) >>> >>> users_ok = Table('users_ok', metadata, ... Column('id', Integer, primary_key=True), ... Column('login', String), ... schema='main' ... ) >>> metadata.create_all(engine) # IT WORKS >>> >>> users_ko = Table('users_ko', metadata, ... Column('id', Integer, primary_key=True), ... Column('login', String, index=True), ... schema='main' ... ) >>> metadata.create_all(engine) # IT FAILS Traceback (most recent call last): (...) sqlalchemy.exc.OperationalError: (OperationalError) near ".": syntax error u'CREATE INDEX ix_main_users_ko_login ON main.users_ko (login)' () >>>
-
Account Deleted Proposed patch (against 0.5.6):
--- sqlalchemy/databases/sqlite.py 2009-12-02 11:18:57.000000000 +0100 +++ sqlalchemy/databases/sqlite.py 2009-12-02 14:22:47.000000000 +0100 @@ -614,6 +614,19 @@ colspec += " NOT NULL" return colspec + def visit_index(self, index): + preparer = self.preparer + self.append("CREATE ") + if index.unique: + self.append("UNIQUE ") + self.append("INDEX %s ON %s (%s)" \ + % (preparer.format_index(index, + name=self._validate_identifier(index.name, True)), + preparer.format_table(index.table, use_schema=False), + ', '.join(preparer.quote(c.name, c.quote) + for c in index.columns))) + self.execute() + class SQLiteIdentifierPreparer(compiler.IdentifierPreparer): reserved_words = set([ 'add', 'after', 'all', 'alter', 'analyze', 'and', 'as', 'asc', @@ -638,6 +651,16 @@ def __init__(self, dialect): super(SQLiteIdentifierPreparer, self).__init__(dialect) + def format_index(self, index, use_schema=True, name=None): + """Prepare a quoted index and schema name.""" + + if name is None: + name = index.name + result = self.quote(name, index.quote) + if not self.omit_schema and use_schema and getattr(index.table, "schema", None): + result = self.quote_schema(index.table.schema, index.table.quote_schema) + "." + result + return result + dialect = SQLiteDialect dialect.poolclass = pool.SingletonThreadPool dialect.statement_compiler = SQLiteCompiler
-
Account Deleted - attached sa_issue7439.diff
patch for 0.5.6
-
repo owner - changed milestone to 0.5.xx
needs also a patch for 0.6 and a unit test in test/dialect/test_sqlite.
-
Account Deleted - attached sa06_issue7439.diff
patch for 0.6 trunk
-
Account Deleted Patches can be reviewed and merged in trunk and 0.5.
-
repo owner - changed milestone to 0.5.7
oh and you fixed the sldate test I whacked the other day, nice !
-
repo owner - changed status to resolved
awesome, thanks for the patches ! 048f70ce85d254eff0b5ccf16fd4e15274b0bc6a acb2faedcd1502248e6db46228112133094dd3cf
-
repo owner - removed milestone
Removing milestone: 0.5.7 (automated comment)
- Log in to comment