Wrong syntax of "CREATE INDEX" for sqlite with schemas

[Former user]

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)' ()

[Mike Bayer]

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

[Former user]

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)

[Former user]

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)' ()
>>>

[Former user]

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)' ()
>>>

[Former user]

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

[Former user]

patch for 0.5.6

[Mike Bayer]

needs also a patch for 0.6 and a unit test in test/dialect/test_sqlite.

[Former user]

patch for 0.6 trunk

[Former user]

Patches can be reviewed and merged in trunk and 0.5.

[Mike Bayer]

oh and you fixed the sldate test I whacked the other day, nice !

[Mike Bayer]

awesome, thanks for the patches ! 048f70ce85d254eff0b5ccf16fd4e15274b0bc6a acb2faedcd1502248e6db46228112133094dd3cf

[Mike Bayer]

Removing milestone: 0.5.7 (automated comment)