Please add a post_create_index method on sqlalchemy.sq.compiler.DDLCompiler

[Former user]

Several databases can have additional options which could be included on index creation. SQLAlchemy itself has already post_create_table which makes each database dialect could be flexible enough extending this method to add specific option which supported by the database, however the similar method does not exist on index creation, I propose this method, post_create_index, could be added also for index creation which by this, any database dialect could avoid reimplementing tasks which actually has been covered by sqlalchemy.sq.compiler.DDLCompiler.visit_create_index(...)

[Mike Bayer]

I'd rather see specific "CREATE INDEX" syntaxes to be implemented by various dialects. Patches for such features would include unit tests and whatever refactorings are needed on the base compiler - although in this case its quite easy just to override visit_create_index, grab the text from the superclass, and append. "post_create" is a potential implementation detail of an as-yet unnamed new feature so this is as yet not really a valid ticket.

[Former user]

Yes, I agree that basicly it is quite easy to just override and grab source of superclass. However, i believe it is not a good behaviour in long term use since any future modification in parent-class will always need to be revisit in each descendent dialects. I love your idea on post_create_table which eliminating the need to reimplement visit_create_table and reducing burden to maintain each dialects supported, i just hope it could be added also on visit_create_index. However, I do agree that each refactoring on base class should be carefully tested, is there any thing I could do as a reporter to help you providing somethings that should be included on this issue? i could not find any page referring something like "how to contribute".

[Mike Bayer]

All I need to see are the specific CREATE INDEX syntaxes you're looking to create, and how we'd modify the "Index()" construct to support specifying them in an agnostic way. Rendering the string is the trivial part.

At the moment its not at all clear to me at what point in the string "post_create_index" would insert its contents, or if we need additional strings at other points in the "CREATE INDEX" statement for various backend features.

[Former user]

Ok, suppose I'd like to specify which tablespace would be used for a table and its index i could specify, for example:

Table('mstable', metadata,
   Column('data', String(32)),
   mssql_tablespace='TMVXLD',
   mssql_index_tablespace='TMVXLI'
 )

With post_create_index I could add a consistent way on MSSQL dialect such as:

    # Defines SQL statement to be executed after table creation
    def post_create_table(self, table):
        table_opts = [       if table.kwargs.has_key('mssql_tablespace'):
            arg = table.kwargs['mssql_tablespace'](]
)
            table_opts.append(' '.join(('ON', arg)))
        # elif ....
        return ' '.join(table_opts)

    # Defines SQL statement to be executed after index creation
    def post_create_index(self, index):
        index_opts = [       if index.kwargs.has_key('mssql_index_tablespace'):
            arg = index.kwargs['mssql_index_tablespace'](]
)
            index_opts.append(' '.join(('ON', arg)))
        # elif ....
        return ' '.join(index_opts)

something that we could do in current version is to reimplement visit_create_index which this makes index creation should be treat in a slightly different way:

    def visit_create_index(self, create):
        index_opts = [self).visit_create_index(create)](super(MyMSSQLDialect,)
        index= create.element        
        if index.kwargs.has_key('mssql_index_tablespace'):
            arg = index.kwargs['mssql_index_tablespace']('mssql_index_tablespace')
            index_opts.append(' '.join(('ON', arg)))
        # elif ....
        return ' '.join(index_opts)

[Mike Bayer]

So yeah, the latter approach is what I'd go with. And in fact you can get all of this behavior right now by simply using the @compiles extension. I'm not sure what I think of arguments like mssql_tablespace, mssql_index_tablespace, only that they seem extremely specific, including that you're not even using the Index() construct to specify index-specific options. Its easier to keep things like this as @compiles recipes, unless we worked up a comprehensive system to support all such "mssql_" options on Table, Index, etc. Otherwise its just two little options that you happened to need for something, whereas other people need entirely different options that aren't there.

[Mike Bayer]

OK, so , here I'm looking for tickets of the form, "support MS-SQL TABLESPACE and INDEX TABLESPACE" options, and an API would be proposed, which would probably be "mssql_tablespace" on the Index() and Table() objects. The compiler extension can provide these at the moment. This ticket only refers to a potential implementation detail of a particular feature so closing this.