added indexes to schema/ansisql/engine

slightly different index syntax for mysql
fixed mysql Time type to convert from a timedelta to time
tweaks to date unit tests for mysql

5 files changed, 123 insertions, 11 deletions

diff --git a/lib/sqlalchemy/ansisql.py b/lib/sqlalchemy/ansisql.py
index a300dc639..ed0f829fb 100644
--- a/lib/sqlalchemy/ansisql.py
+++ b/lib/sqlalchemy/ansisql.py
@@ -168,6 +168,9 @@ class ANSICompiler(sql.Compiled):
     def visit_fromclause(self, fromclause):
         self.froms[fromclause] = fromclause.from_name
 
+    def visit_index(self, index):
+        self.strings[index] = index.name
+        
     def visit_textclause(self, textclause):
         if textclause.parens and len(textclause.text):
             self.strings[textclause] = "(" + textclause.text + ")"
@@ -200,7 +203,7 @@ class ANSICompiler(sql.Compiled):
 
     def visit_function(self, func):
         self.strings[func] = func.name + "(" + string.join([self.get_str(c) for c in func.clauses], ', ') + ")"
-    
+        
     def visit_compound_select(self, cs):
         text = string.join([self.get_str(c) for c in cs.selects], " " + cs.keyword + " ")
         for tup in cs.clauses:
@@ -531,12 +534,26 @@ class ANSISchemaGenerator(sqlalchemy.engine.SchemaIterator):
 
     def visit_column(self, column):
         pass
+
+    def visit_index(self, index):
+        self.append('CREATE ')
+        if index.unique:
+            self.append('UNIQUE ')
+        self.append('INDEX %s ON %s (%s)' \
+                    % (index.name, index.table.name,
+                       string.join([c.name for c in index.columns], ', ')))
+        self.execute()
+        
     
 class ANSISchemaDropper(sqlalchemy.engine.SchemaIterator):
+    def visit_index(self, index):
+        self.append("\nDROP INDEX " + index.name)
+        self.execute()
+        
     def visit_table(self, table):
         self.append("\nDROP TABLE " + table.fullname)
         self.execute()
 
 
 class ANSIDefaultRunner(sqlalchemy.engine.DefaultRunner):
-    pass
\ No newline at end of file
+    pass
diff --git a/lib/sqlalchemy/databases/mysql.py b/lib/sqlalchemy/databases/mysql.py
index 0afac7df3..04bdc24fa 100644
--- a/lib/sqlalchemy/databases/mysql.py
+++ b/lib/sqlalchemy/databases/mysql.py
@@ -4,7 +4,7 @@
 # This module is part of SQLAlchemy and is released under
 # the MIT License: http://www.opensource.org/licenses/mit-license.php
 
-import sys, StringIO, string, types, re
+import sys, StringIO, string, types, re, datetime
 
 import sqlalchemy.sql as sql
 import sqlalchemy.engine as engine
@@ -40,6 +40,13 @@ class MSDate(sqltypes.Date):
 class MSTime(sqltypes.Time):
     def get_col_spec(self):
         return "TIME"
+    def convert_result_value(self, value, engine):
+        # convert from a timedelta value
+        if value is not None:
+            return datetime.time(value.seconds/60/60, value.seconds/60%60, value.seconds - (value.seconds/60*60))
+        else:
+            return None
+            
 class MSText(sqltypes.TEXT):
     def get_col_spec(self):
         return "TEXT"
@@ -135,6 +142,9 @@ class MySQLEngine(ansisql.ANSISQLEngine):
     def schemagenerator(self, **params):
         return MySQLSchemaGenerator(self, **params)
 
+    def schemadropper(self, **params):
+        return MySQLSchemaDropper(self, **params)
+
     def get_default_schema_name(self):
         if not hasattr(self, '_default_schema_name'):
             self._default_schema_name = text("select database()", self).scalar()
@@ -276,3 +286,7 @@ class MySQLSchemaGenerator(ansisql.ANSISchemaGenerator):
         else:
             return ""
 
+class MySQLSchemaDropper(ansisql.ANSISchemaDropper):
+    def visit_index(self, index):
+        self.append("\nDROP INDEX " + index.name + " ON " + index.table.name)
+        self.execute()
diff --git a/lib/sqlalchemy/engine.py b/lib/sqlalchemy/engine.py
index ac6df0f9e..d553eee2d 100644
--- a/lib/sqlalchemy/engine.py
+++ b/lib/sqlalchemy/engine.py
@@ -294,13 +294,13 @@ class SQLEngine(schema.SchemaEngine):
         for the "rowcount" function on a statement handle.  """
         return True
         
-    def create(self, table, **params):
-        """creates a table within this engine's database connection given a schema.Table object."""
-        table.accept_visitor(self.schemagenerator(**params))
+    def create(self, entity, **params):
+        """creates a table or index within this engine's database connection given a schema.Table object."""
+        entity.accept_visitor(self.schemagenerator(**params))
 
-    def drop(self, table, **params):
-        """drops a table within this engine's database connection given a schema.Table object."""
-        table.accept_visitor(self.schemadropper(**params))
+    def drop(self, entity, **params):
+        """drops a table or index within this engine's database connection given a schema.Table object."""
+        entity.accept_visitor(self.schemadropper(**params))
 
     def compile(self, statement, parameters, **kwargs):
         """given a sql.ClauseElement statement plus optional bind parameters, creates a new
@@ -329,6 +329,14 @@ class SQLEngine(schema.SchemaEngine):
         database-specific behavior."""
         return sql.ColumnImpl(column)
 
+    def indeximpl(self, index):
+        """returns a new sql.IndexImpl object to correspond to the given Index
+        object. An IndexImpl provides SQL statement builder operations on an
+        Index metadata object, and a subclass of this object may be provided
+        by a SQLEngine subclass to provide database-specific behavior.
+        """
+        return sql.IndexImpl(index)
+    
     def get_default_schema_name(self):
         """returns the currently selected schema in the current connection."""
         return None
diff --git a/lib/sqlalchemy/schema.py b/lib/sqlalchemy/schema.py
index bb926053d..a9c6a4d96 100644
--- a/lib/sqlalchemy/schema.py
+++ b/lib/sqlalchemy/schema.py
@@ -19,8 +19,8 @@ from sqlalchemy.util import *
 from sqlalchemy.types import *
 import copy, re, string
 
-__all__ = ['SchemaItem', 'Table', 'Column', 'ForeignKey', 'Sequence', 'SchemaEngine', 'SchemaVisitor', 'PassiveDefault', 'ColumnDefault']
-
+__all__ = ['SchemaItem', 'Table', 'Column', 'ForeignKey', 'Sequence', 'Index',
+           'SchemaEngine', 'SchemaVisitor', 'PassiveDefault', 'ColumnDefault']
 
 class SchemaItem(object):
     """base class for items that define a database schema."""
@@ -455,6 +455,53 @@ class Sequence(DefaultGenerator):
         """calls the visit_seauence method on the given visitor."""
         return visitor.visit_sequence(self)
 
+class Index(SchemaItem):
+    """Represents an index of columns from a database table
+    """
+
+    def __init__(self, name, *columns, **kw):
+        """Constructs an index object. Arguments are:
+
+        name : the name of the index
+
+        *columns : columns to include in the index. All columns must belong to
+        the same table, and no column may appear more than once.
+
+        **kw : keyword arguments include:
+
+        unique=True : create a unique index
+        """
+        self.name = name
+        self.columns = columns
+        self.unique = kw.pop('unique', False)
+        self._init_items()
+
+    def _init_items(self):
+        # make sure all columns are from the same table
+        # FIXME: and no column is repeated
+        self.table = None
+        for column in self.columns:
+            if self.table is None:
+                self.table = column.table
+            elif column.table != self.table:
+                # all columns muse be from same table
+                raise ValueError("All index columns must be from same table. "
+                                 "%s is from %s not %s" % (column,
+                                                           column.table,
+                                                           self.table))
+        # set my _impl from col.table.engine
+        self._impl = self.table.engine.indeximpl(self)
+        
+    def accept_visitor(self, visitor):
+        visitor.visit_index(self)
+    def __str__(self):
+        return repr(self)
+    def __repr__(self):
+        return 'Index("%s", %s%s)' % (self.name,
+                                      ', '.join([repr(c)
+                                                 for c in self.columns]),
+                                      (self.unique and ', unique=True') or '')
+        
 class SchemaEngine(object):
     """a factory object used to create implementations for schema objects.  This object
     is the ultimate base class for the engine.SQLEngine class."""
@@ -464,6 +511,11 @@ class SchemaEngine(object):
     def columnimpl(self, column):
         """returns a new implementation object for a Column (usually sql.ColumnImpl)"""
         raise NotImplementedError()
+    def indeximpl(self, index):
+        """returns a new implementation object for an Index (usually
+        sql.IndexImpl)
+        """
+        raise NotImplementedError()
     def reflecttable(self, table):
         """given a table, will query the database and populate its Column and ForeignKey 
         objects."""
diff --git a/lib/sqlalchemy/sql.py b/lib/sqlalchemy/sql.py
index 293880bf3..03c94c5e3 100644
--- a/lib/sqlalchemy/sql.py
+++ b/lib/sqlalchemy/sql.py
@@ -1430,4 +1430,25 @@ class Delete(UpdateBase):
             self.whereclause.accept_visitor(visitor)
         visitor.visit_delete(self)
 
+class IndexImpl(ClauseElement):
+
+    def __init__(self, index):
+        self.index = index
+        self.name = index.name
+        self._engine = self.index.table.engine
+
+    table = property(lambda s: s.index.table)
+    columns = property(lambda s: s.index.columns)
         
+    def hash_key(self):
+        return self.index.hash_key()
+    def accept_visitor(self, visitor):
+        visitor.visit_index(self.index)
+    def compare(self, other):
+        return self.index is other
+    def create(self):
+        self._engine.create(self.index)
+    def drop(self):
+        self._engine.drop(self.index)
+    def execute(self):
+        self.create()