postgres kickin my ass w00p

6 files changed, 118 insertions, 50 deletions

diff --git a/lib/sqlalchemy/ansisql.py b/lib/sqlalchemy/ansisql.py
index 95aa47cde..4f90d485c 100644
--- a/lib/sqlalchemy/ansisql.py
+++ b/lib/sqlalchemy/ansisql.py
@@ -138,8 +138,11 @@ class ANSICompiler(sql.Compiled):
         while self.binds.setdefault(key, bindparam) is not bindparam:
             key = "%s_%d" % (bindparam.key, count)
             count += 1
-        self.strings[bindparam] = ":" + key
+        self.strings[bindparam] = self.bindparam_string(key)
 
+    def bindparam_string(self, name):
+        return ":" + name
+        
     def visit_alias(self, alias):
         self.froms[alias] = self.get_from_text(alias.selectable) + " " + alias.name
         self.strings[alias] = self.get_str(alias.selectable)
@@ -221,7 +224,7 @@ class ANSICompiler(sql.Compiled):
             self.binds[b.shortname] = b
             
         text = ("INSERT INTO " + insert_stmt.table.name + " (" + string.join([c[0].name for c in colparams], ', ') + ")" +
-         " VALUES (" + string.join([":" + c[1].key for c in colparams], ', ') + ")")
+         " VALUES (" + string.join([self.bindparam_string(c[1].key) for c in colparams], ', ') + ")")
          
         self.strings[insert_stmt] = text
 
@@ -231,7 +234,7 @@ class ANSICompiler(sql.Compiled):
             if isinstance(p, BindParamClause):
                 self.binds[p.key] = p
                 self.binds[p.shortname] = p
-                return ":" + p.key
+                return self.bindparam_string(p.key)
             else:
                 p.accept_visitor(self)
                 if isinstance(p, ClauseElement):
diff --git a/lib/sqlalchemy/databases/postgres.py b/lib/sqlalchemy/databases/postgres.py
index 375f3c177..a4361af67 100644
--- a/lib/sqlalchemy/databases/postgres.py
+++ b/lib/sqlalchemy/databases/postgres.py
@@ -15,47 +15,87 @@
 # along with this library; if not, write to the Free Software
 # Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 
-import sys, StringIO, string
+import sys, StringIO, string, types, re
 
 import sqlalchemy.sql as sql
+import sqlalchemy.engine as engine
 import sqlalchemy.schema as schema
 import sqlalchemy.ansisql as ansisql
+import sqlalchemy.types as sqltypes
 from sqlalchemy.ansisql import *
 
+class PGNumeric(sqltypes.Numeric):
+    def get_col_spec(self):
+        return "NUMERIC(%(precision)s, %(length)s)" % {'precision': self.precision, 'length' : self.length}
+class PGInteger(sqltypes.Integer):
+    def get_col_spec(self):
+        return "INTEGER"
+class PGDateTime(sqltypes.DateTime):
+    def get_col_spec(self):
+        return "TIMESTAMP"
+class PGText(sqltypes.TEXT):
+    def get_col_spec(self):
+        return "TEXT"
+class PGString(sqltypes.String):
+    def get_col_spec(self):
+        return "VARCHAR(%(length)s)" % {'length' : self.length}
+class PGChar(sqltypes.CHAR):
+    def get_col_spec(self):
+        return "CHAR(%(length)s)" % {'length' : self.length}
+class PGBinary(sqltypes.Binary):
+    def get_col_spec(self):
+        return "BLOB"
+class PGBoolean(sqltypes.Boolean):
+    def get_col_spec(self):
+        return "BOOLEAN"
+        
 colspecs = {
-    schema.INT : "INTEGER",
-    schema.CHAR : "CHAR(%(length)s)",
-    schema.VARCHAR : "VARCHAR(%(length)s)",
-    schema.TEXT : "TEXT",
-    schema.FLOAT : "NUMERIC(%(precision)s, %(length)s)",
-    schema.DECIMAL : "NUMERIC(%(precision)s, %(length)s)",
-    schema.TIMESTAMP : "TIMESTAMP",
-    schema.DATETIME : "TIMESTAMP",
-    schema.CLOB : "TEXT",
-    schema.BLOB : "BLOB",
-    schema.BOOLEAN : "BOOLEAN",
+    sqltypes.Integer : PGInteger,
+    sqltypes.Numeric : PGNumeric,
+    sqltypes.DateTime : PGDateTime,
+    sqltypes.String : PGString,
+    sqltypes.Binary : PGBinary,
+    sqltypes.Boolean : PGBoolean,
+    sqltypes.TEXT : PGText,
+    sqltypes.CHAR: PGChar,
 }
 
-
-def engine(**params):
-    return PGSQLEngine(**params)
+def engine(opts, **params):
+    return PGSQLEngine(opts, **params)
 
 class PGSQLEngine(ansisql.ANSISQLEngine):
-    def __init__(self, **params):
+    def __init__(self, opts, module = None, **params):
+        if module is None:
+            self.module = __import__('psycopg2')
+        else:
+            self.module = module
+        self.opts = opts or {}
         ansisql.ANSISQLEngine.__init__(self, **params)
 
     def connect_args(self):
-        return [[], {}]
+        return [[], self.opts]
 
-    def compile(self, statement, bindparams):
-        compiler = PGCompiler(self, statement, bindparams)
-        statement.accept_visitor(compiler)
-        return compiler
+
+    def type_descriptor(self, typeobj):
+        return sqltypes.adapt_type(typeobj, colspecs)
 
     def last_inserted_ids(self):
         return self.context.last_inserted_ids
 
+    def compiler(self, statement, bindparams):
+        return PGCompiler(self, statement, bindparams)
+
+    def schemagenerator(self, proxy, **params):
+        return PGSchemaGenerator(proxy, **params)
+
+    def reflecttable(self, table):
+        raise "not implemented"
+        
+    def last_inserted_ids(self):
+        return self.context.last_inserted_ids
+
     def pre_exec(self, connection, cursor, statement, parameters, echo = None, compiled = None, **kwargs):
+        if True: return
         if compiled is None: return
         if getattr(compiled, "isinsert", False):
             last_inserted_ids = []
@@ -70,25 +110,33 @@ class PGSQLEngine(ansisql.ANSISQLEngine):
                     last_inserted_ids.append(newid)
             self.context.last_inserted_ids = last_inserted_ids
 
-    def dbapi(self):
-        return None
-#        return psycopg
+    def post_exec(self, connection, cursor, statement, parameters, echo = None, compiled = None, **kwargs):
+        if compiled is None: return
+        if getattr(compiled, "isinsert", False):
+            self.context.last_inserted_ids = [cursor.lastrowid]
 
-    def columnimpl(self, column):
-        return PGColumnImpl(column)
+    def dbapi(self):
+        return self.module
 
     def reflecttable(self, table):
         raise NotImplementedError()
 
 class PGCompiler(ansisql.ANSICompiler):
-    pass
-
-class PGColumnImpl(sql.ColumnSelectable):
-    def get_specification(self):
-        coltype = self.column.type
-        if isinstance(coltype, types.ClassType):
-            key = coltype
+    def bindparam_string(self, name):
+        return "%(" + name + ")s"
+
+class PGSchemaGenerator(ansisql.ANSISchemaGenerator):
+    def get_column_specification(self, column):
+        colspec = column.name
+        if column.primary_key and isinstance(column.type, types.Integer):
+            colspec += " SERIAL"
         else:
-            key = coltype.__class__
-
-        return self.name + " " + colspecs[key] % {'precision': getattr(coltype, 'precision', None), 'length' : getattr(coltype, 'length', None)}
+            colspec += " " + column.column.type.get_col_spec()
+
+        if not column.nullable:
+            colspec += " NOT NULL"
+        if column.primary_key:
+            colspec += " PRIMARY KEY"
+        if column.foreign_key:
+            colspec += " REFERENCES %s(%s)" % (column.column.foreign_key.column.table.name, column.column.foreign_key.column.name) 
+        return colspec
diff --git a/lib/sqlalchemy/databases/sqlite.py b/lib/sqlalchemy/databases/sqlite.py
index 62443771f..d613728cb 100644
--- a/lib/sqlalchemy/databases/sqlite.py
+++ b/lib/sqlalchemy/databases/sqlite.py
@@ -141,12 +141,12 @@ class SQLiteCompiler(ansisql.ANSICompiler):
 
 class SQLiteSchemaGenerator(ansisql.ANSISchemaGenerator):
     def get_column_specification(self, column):
-        colspec = column.name + " " + column.column.type.get_col_spec()
-        if not column.column.nullable:
+        colspec = column.name + " " + column.type.get_col_spec()
+        if not column.nullable:
             colspec += " NOT NULL"
-        if column.column.primary_key:
+        if column.primary_key:
             colspec += " PRIMARY KEY"
-        if column.column.foreign_key:
-            colspec += " REFERENCES %s(%s)" % (column.column.foreign_key.column.table.name, column.column.foreign_key.column.name) 
+        if column.foreign_key:
+            colspec += " REFERENCES %s(%s)" % (column.foreign_key.column.table.name, column.foreign_key.column.name) 
         return colspec
 
diff --git a/lib/sqlalchemy/mapper.py b/lib/sqlalchemy/mapper.py
index 842d67f19..f64074b8b 100644
--- a/lib/sqlalchemy/mapper.py
+++ b/lib/sqlalchemy/mapper.py
@@ -397,9 +397,17 @@ class Mapper(object):
                 
 #                print "SAVE_OBJ we are " + hash_key(self) + " obj: " +  obj.__class__.__name__ + repr(id(obj))
                 params = {}
+
                 for col in table.columns:
-                    if col.primary_key and hasattr(obj, "_instance_key"):
-                        params[col.table.name + "_" + col.key] = self._getattrbycolumn(obj, col)
+                    if col.primary_key:
+                        if hasattr(obj, "_instance_key"):
+                            params[col.table.name + "_" + col.key] = self._getattrbycolumn(obj, col)
+                        else:
+                            # its an INSERT - if its NULL, leave it out as pgsql doesnt 
+                            # like it for an autoincrement
+                            value = self._getattrbycolumn(obj, col)
+                            if value is not None:
+                                params[col.key] = value
                     else:
                         params[col.key] = self._getattrbycolumn(obj, col)
 
@@ -730,7 +738,7 @@ class PropertyLoader(MapperProperty):
 
     def _compile_synchronizers(self):
         def compile(binary):
-            if binary.operator != '=':
+            if binary.operator != '=' or not isinstance(binary.left, schema.Column) or not isinstance(binary.right, schema.Column):
                 return
 
             if binary.left.table == binary.right.table:
@@ -998,7 +1006,11 @@ class EagerLoader(PropertyLoader):
             towrap = self.parent.table
 
         if self.secondaryjoin is not None:
-            statement._outerjoin = sql.outerjoin(towrap, self.secondary, self.secondaryjoin).outerjoin(self.target, self.primaryjoin)
+            print self.secondary.name
+            print str(self.secondaryjoin)
+            print self.target.name
+            print str(self.primaryjoin)
+            statement._outerjoin = sql.outerjoin(towrap, self.secondary, self.primaryjoin).outerjoin(self.target, self.secondaryjoin)
         else:
             statement._outerjoin = towrap.outerjoin(self.target, self.primaryjoin)
 
diff --git a/lib/sqlalchemy/schema.py b/lib/sqlalchemy/schema.py
index 8952f038b..9fad004b1 100644
--- a/lib/sqlalchemy/schema.py
+++ b/lib/sqlalchemy/schema.py
@@ -80,7 +80,7 @@ class Table(SchemaItem):
         self.name = name
         self.columns = OrderedProperties()
         self.c = self.columns
-        self.foreign_keys = OrderedProperties()
+        self.foreign_keys = []
         self.primary_keys = []
         self.engine = engine
         self._impl = self.engine.tableimpl(self)
@@ -204,7 +204,6 @@ class ForeignKey(SchemaItem):
             else:
                 self._column = self._colspec
 
-            self.parent.table.foreign_keys[self._column.key] = self
         return self._column
             
     column = property(lambda s: s._init_column())
@@ -212,6 +211,7 @@ class ForeignKey(SchemaItem):
     def _set_parent(self, column):
         self.parent = column
         self.parent.foreign_key = self
+        self.parent.table.foreign_keys.append(self)
         
 class Sequence(SchemaItem):
     """represents a sequence, which applies to Oracle and Postgres databases."""
diff --git a/lib/sqlalchemy/sql.py b/lib/sqlalchemy/sql.py
index 03cba3956..6322b9522 100644
--- a/lib/sqlalchemy/sql.py
+++ b/lib/sqlalchemy/sql.py
@@ -463,6 +463,11 @@ class Join(Selectable):
 
     primary_keys = property (lambda self: [c for c in self.left.columns if c.primary_key] + [c for c in self.right.columns if c.primary_key])
 
+
+    def group_parenthesized(self):
+        """indicates if this Selectable requires parenthesis when grouped into a compound statement"""
+        return False
+
     def hash_key(self):
         return "Join(%s, %s, %s, %s)" % (repr(self.left.hash_key()), repr(self.right.hash_key()), repr(self.onclause.hash_key()), repr(self.isouter))