Wrong syntax of "CREATE INDEX" for sqlite with schemas

Issue #1439 resolved
Former user created an issue

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)

  1. Mike Bayer repo owner

    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

  2. Former user 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)' ()
    >>>
    
  3. Former user 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)' ()
    >>>
    
  4. Former user 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
    
  5. Log in to comment