- Column() supports a keyword argument "sqlite_autoincrement", which

applies the SQLite keyword "AUTOINCREMENT" to columns within DDL -
will prevent generation of a separate PRIMARY KEY constraint.
[ticket:1016]
- added docs
- fixed underlines in mysql.rst

6 files changed, 88 insertions, 16 deletions

diff --git a/CHANGES b/CHANGES
index fcd7a1163..775d4c014 100644
--- a/CHANGES
+++ b/CHANGES
@@ -661,20 +661,25 @@ CHANGES
       the MSSQL dialect documentation for more information.
 
 - sqlite
-    - DATE, TIME and DATETIME types can now take optional storage_format and
-      regexp argument. storage_format can be used to store those types using
-      a custom string format. regexp allows to use a custom regular expression
-      to match string values from the database.
+    - DATE, TIME and DATETIME types can now take optional storage_format
+      and regexp argument. storage_format can be used to store those types
+      using a custom string format. regexp allows to use a custom regular
+      expression to match string values from the database.
     - Time and DateTime types now use by a default a stricter regular
-      expression to match strings from the database. Use the regexp argument
-      if you are using data stored in a legacy format.
+      expression to match strings from the database. Use the regexp
+      argument if you are using data stored in a legacy format.
     - __legacy_microseconds__ on SQLite Time and DateTime types is not
-      supported anymore. You should use the storage_format argument instead.
+      supported anymore. You should use the storage_format argument
+      instead.
     - Date, Time and DateTime types are now stricter in what they accept as
-      bind parameters: Date type only accepts date objects (and datetime ones,
-      because they inherit from date), Time only accepts time objects, and
-      DateTime only accepts date and datetime objects.
-
+      bind parameters: Date type only accepts date objects (and datetime
+      ones, because they inherit from date), Time only accepts time
+      objects, and DateTime only accepts date and datetime objects.
+    - Column() supports a keyword argument "sqlite_autoincrement", which
+      applies the SQLite keyword "AUTOINCREMENT" to columns within DDL - 
+      will prevent generation of a separate PRIMARY KEY constraint.
+      [ticket:1016]
+      
 - new dialects
     - postgresql+pg8000
     - postgresql+pypostgresql (partial)
diff --git a/doc/build/reference/dialects/mysql.rst b/doc/build/reference/dialects/mysql.rst
index c310f6c52..c45da7423 100644
--- a/doc/build/reference/dialects/mysql.rst
+++ b/doc/build/reference/dialects/mysql.rst
@@ -139,7 +139,7 @@ MySQL Column Types
    :show-inheritance:
 
 MySQL-Python Notes
---------------
+--------------------
 
 .. automodule:: sqlalchemy.dialects.mysql.mysqldb
 
@@ -149,7 +149,7 @@ OurSQL Notes
 .. automodule:: sqlalchemy.dialects.mysql.oursql
 
 MyConnPY Notes
---------------
+----------------
 
 .. automodule:: sqlalchemy.dialects.mysql.myconnpy
 
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 235a17a66..27fc9b462 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -20,7 +20,25 @@ These types represent dates and times as ISO formatted strings, which also nicel
 support ordering.   There's no reliance on typical "libc" internals for these functions
 so historical dates are fully supported.
 
+Auto Incrementing Beahvior
+--------------------------
 
+Background on SQLite's autoincrement is at: http://sqlite.org/autoinc.html
+
+Two things to note:
+
+* The AUTOINCREMENT keyword is **not** required for SQLite tables to
+  generate primary key values automatically. AUTOINCREMENT only means that
+  the algorithm used to generate ROWID values should be slightly different.
+* SQLite does **not** generate primary key (i.e. ROWID) values, even for
+  one column, if the table has a composite (i.e. multi-column) primary key.
+  This is regardless of the AUTOINCREMENT keyword being present or not.
+
+To specifically render the AUTOINCREMENT keyword on a SQLAlchemy column
+when rendering DDL, add the flag ``sqlite_autoincrement=True``::
+
+    Column('id', Integer, primary_key=True, sqlite_autoincrement=True)
+    
 """
 
 import datetime, re, time
@@ -238,8 +256,32 @@ class SQLiteDDLCompiler(compiler.DDLCompiler):
 
         if not column.nullable:
             colspec += " NOT NULL"
+
+        if column.primary_key and \
+             column.table.kwargs.get('sqlite_autoincrement', False) and \
+             len(column.table.primary_key.columns) == 1 and \
+             isinstance(column.type, sqltypes.Integer) and \
+             not column.foreign_keys:
+             colspec += " PRIMARY KEY AUTOINCREMENT"
+            
         return colspec
 
+    def visit_primary_key_constraint(self, constraint):
+        # for columns with sqlite_autoincrement=True,
+        # the PRIMARY KEY constraint can only be inline
+        # with the column itself.
+        if len(constraint.columns) == 1:
+            c = list(constraint)[0]
+            if c.primary_key and \
+                c.table.kwargs.get('sqlite_autoincrement', False) and \
+                isinstance(c.type, sqltypes.Integer) and \
+                not c.foreign_keys:
+                return ''
+ 
+        return super(SQLiteDDLCompiler, self).\
+                    visit_primary_key_constraint(constraint)
+ 
+
     def visit_create_index(self, create):
         index = create.element
         preparer = self.preparer
diff --git a/lib/sqlalchemy/schema.py b/lib/sqlalchemy/schema.py
index d3a15dc8b..455582c87 100644
--- a/lib/sqlalchemy/schema.py
+++ b/lib/sqlalchemy/schema.py
@@ -509,7 +509,8 @@ class Column(SchemaItem, expression.ColumnClause):
             SERIAL on Postgresql, and IDENTITY on MS-SQL.  It does 
             *not* issue AUTOINCREMENT for SQLite since this is a
             special SQLite flag that is not required for autoincrementing
-            behavior.
+            behavior.  See the SQLite dialect documentation for
+            information on SQLite's AUTOINCREMENT.
             
           * The column will be considered to be available as 
             cursor.lastrowid or equivalent, for those dialects which
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index a41a149d1..f589e4e4e 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -982,7 +982,9 @@ class DDLCompiler(engine.Compiled):
         # On some DB order is significant: visit PK first, then the
         # other constraints (engine.ReflectionTest.testbasic failed on FB2)
         if table.primary_key:
-            text += ", \n\t" + self.process(table.primary_key)
+            pk = self.process(table.primary_key)
+            if pk:
+                text += ", \n\t" + pk
         
         const = ", \n\t".join(p for p in 
                         (self.process(constraint) for constraint in table.constraints 
diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py
index 7fced60d9..2aca6b776 100644
--- a/test/dialect/test_sqlite.py
+++ b/test/dialect/test_sqlite.py
@@ -3,7 +3,7 @@
 from sqlalchemy.test.testing import eq_, assert_raises, assert_raises_message
 import datetime
 from sqlalchemy import *
-from sqlalchemy import exc, sql
+from sqlalchemy import exc, sql, schema
 from sqlalchemy.dialects.sqlite import base as sqlite, pysqlite as pysqlite_dialect
 from sqlalchemy.test import *
 
@@ -528,4 +528,26 @@ class MatchTest(TestBase, AssertsCompiledSQL):
                                            ).order_by(matchtable.c.id).execute().fetchall()
         eq_([1, 3], [r.id for r in results])
 
+class TestAutoIncrement(TestBase, AssertsCompiledSQL):
+
+    def test_sqlite_autoincrement(self):
+        table = Table('autoinctable', MetaData(),
+                      Column('id', Integer, primary_key=True),
+                      Column('x', Integer, default=None),
+                      sqlite_autoincrement=True)
+        self.assert_compile(
+            schema.CreateTable(table),
+            "CREATE TABLE autoinctable (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, x INTEGER)",
+            dialect=sqlite.dialect()
+        )
+
+    def test_sqlite_no_autoincrement(self):
+        table = Table('noautoinctable', MetaData(),
+                      Column('id', Integer, primary_key=True),
+                      Column('x', Integer, default=None))
+        self.assert_compile(
+            schema.CreateTable(table),
+            "CREATE TABLE noautoinctable (id INTEGER NOT NULL, x INTEGER, PRIMARY KEY (id))",
+            dialect=sqlite.dialect()
+        )