Hi,
attached is a patch against MiddleKit to provide preliminary support
for PostgreSQL. We are using the patch locally, it seems to work
just fine. Its a bit unpolished but, well,... here it is :-)
Known issues:
- sample data not supported
- requires a patch against the PostgreSQL python bindings
(only 7.2+ supported)
The reason that a patch against the PostgreSQL python bindings is required
is that MiddleKit makes uses of a database-assigned ID for newly
inserted database objects. It retrieves this through a MySQL-specific
server function. Postgres has an equivalent in the 'C' glue for its
binding but this equivalent is not exposed by the python-part of the driver.
(its called 'lastoid').
The second reason is that we put mxDateTime support into the python
part of the Postgres driver so that some assumptions MiddleKit makes
about the type of the returned data of datetime columns hold. I suppose
there is a way to do that little bit of processing in the MiddleKit patch
directly and if anyone can point me in the right direction there, I would
truly appreciate it.
any and all feedback very welcome!
Ingo
--- src/interfaces/python/pgdb.py 2001-06-15 04:23:18.000000000 +0200
+++ /usr/lib/python2.1/site-packages/pgdb.py 2002-04-04 23:07:49.000000000 +0200
@@ -135,6 +135,13 @@
value = string.replace(value, "$", "")
value = string.replace(value, ",", "")
value = float(value)
+ elif typ == DATETIME_TIME:
+ hour, min, sec = value.split(':')
+ value = DateTime.DateTimeDelta(0, int(hour),
+ int(min), int(sec))
+ elif typ == DATETIME_DATE:
+ year, mon, day = value.split('-')
+ value = DateTime.DateTime(int(year), int(mon), int(day))
elif typ == DATETIME:
# format may differ ... we'll give string
pass
@@ -225,6 +232,10 @@
self.rowcount = totrows
self.description = None
+ # name chose for compat with psycopg
+ def lastoid(self):
+ return self.__source.oidstatus()
+
def fetchone(self):
res = self.fetchmany(1, 0)
try:
@@ -410,6 +421,8 @@
DATETIME = pgdbType(
'abstime', 'reltime', 'tinterval', 'date', 'time', 'timespan', 'timestamp'
)
+DATETIME_TIME = pgdbType('time')
+DATETIME_DATE = pgdbType('date')
# OIDs are used for everything (types, tables, BLOBs, rows, ...). This may cause
# confusion, but we are unable to find out what exactly is behind the OID (at
diff -uNr Webware/MiddleKit/Design/Generate.py Webware-MK/MiddleKit/Design/Generate.py
--- Webware/MiddleKit/Design/Generate.py Tue Jul 31 16:38:25 2001
+++ Webware-MK/MiddleKit/Design/Generate.py Mon May 20 14:34:32 2002
@@ -60,7 +60,7 @@
class Generate:
def databases(self):
- return ['MSSQL', 'MySQL'] # @@ 2000-10-19 ce: should build this
dynamically
+ return ['MSSQL', 'MySQL', 'PostgreSQL' ] # @@ 2000-10-19 ce: should
+build this dynamically
def main(self, args=sys.argv):
opt = self.options(args)
diff -uNr Webware/MiddleKit/Design/PostgreSQLPythonGenerator.py
Webware-MK/MiddleKit/Design/PostgreSQLPythonGenerator.py
--- Webware/MiddleKit/Design/PostgreSQLPythonGenerator.py Thu Jan 1 01:00:00
1970
+++ Webware-MK/MiddleKit/Design/PostgreSQLPythonGenerator.py Mon May 20 14:34:32
+2002
@@ -0,0 +1,5 @@
+from SQLPythonGenerator import SQLPythonGenerator
+
+
+class PostgreSQLPythonGenerator(SQLPythonGenerator):
+ pass
diff -uNr Webware/MiddleKit/Design/PostgreSQLSQLGenerator.py
Webware-MK/MiddleKit/Design/PostgreSQLSQLGenerator.py
--- Webware/MiddleKit/Design/PostgreSQLSQLGenerator.py Thu Jan 1 01:00:00 1970
+++ Webware-MK/MiddleKit/Design/PostgreSQLSQLGenerator.py Mon May 20 14:34:32
+2002
@@ -0,0 +1,78 @@
+from SQLGenerator import SQLGenerator
+
+
+class PostgreSQLSQLGenerator(SQLGenerator):
+
+ def sqlSupportsDefaultValues(self):
+ return 1
+
+
+class Klasses:
+
+ def dropDatabaseSQL(self, dbName):
+ # errors are ignored
+ return 'DROP DATABASE "%s";\n' % dbName
+
+ def dropTablesSQL(self):
+ sql = []
+ names = self.auxiliaryTableNames()[:]
+ names.reverse()
+ for tableName in names:
+ sql.append('drop table "%s";\n' % tableName)
+ klasses = self._model._allKlassesInOrder[:]
+ klasses.reverse()
+ for klass in klasses:
+ sql.append('drop table "%s";\n' % klass.name())
+ sql.append('\n')
+ return ''.join(sql)
+
+ def createDatabaseSQL(self, dbName):
+ return 'create database "%s";\n' % dbName
+
+ def useDatabaseSQL(self, dbName):
+ return '\c "%s"\n\n' % dbName
+
+
+class Klass:
+
+ def primaryKeySQLDef(self, generator):
+ return '\t%s serial not null primary key,\n' % self.sqlIdName()
+
+
+## AFAIK not supported
+
+#class EnumAttr:
+#
+# def sqlType(self):
+# enums = ['"%s"' % enum for enum in self.enums()]
+# enums = ', '.join(enums)
+# enums = 'enum(%s)' % enums
+# return enums
+#
+# def sampleValue(self, value):
+# assert value in self._enums, 'value = %r, enums = %r' % (value,
+self._enums)
+# return repr(value)
+
+
+class StringAttr:
+
+ def sqlType(self):
+ # @@ 2000-11-11 ce: cache this
+ if not self.get('Max', None):
+ return 'varchar(100) /* WARNING: NO LENGTH SPECIFIED */'
+ max = int(self['Max']) # @@ 2000-11-12 ce: won't need int() after
+using types
+ if max>65535:
+ return 'longtext'
+ if max>255:
+ return 'text'
+ if self.has_key('Min') and self['Min'] and int(self['Min'])==max:
+ return 'char(%s)' % max
+ else:
+ return 'varchar(%s)' % max
+
+
+class ObjRefAttr:
+
+ def sqlType(self):
+ # @@ 2001-02-04 ce: Is this standard SQL? If so, it can be moved up.
+ return 'bigint /* %s */' % self['Type']
Binary files Webware/MiddleKit/Design/PostgreSQLSQLGenerator.pyc and
Webware-MK/MiddleKit/Design/PostgreSQLSQLGenerator.pyc differ
diff -uNr Webware/MiddleKit/Design/SQLGenerator.py
Webware-MK/MiddleKit/Design/SQLGenerator.py
--- Webware/MiddleKit/Design/SQLGenerator.py Fri Mar 29 03:17:54 2002
+++ Webware-MK/MiddleKit/Design/SQLGenerator.py Mon May 20 14:34:32 2002
@@ -291,21 +291,20 @@
name varchar(100)
);
''')
- wr('insert into _MKClassIds (id, name) values\n')
id = 1
values = []
for klass in self._model._allKlassesInOrder:
- values.append('\t(%s, %r)' % (id, klass.name()))
+ wr('insert into _MKClassIds (id, name) values ')
+ wr('\t(%s, %r);\n' % (id, klass.name()))
klass.setId(id)
id += 1
- wr(',\n'.join(values))
- wr(';\n\n')
+ wr('\n')
def didWriteCreateSQL(self, generator, out):
sql = generator.setting('PostSQL', None)
if sql:
out.write('/* PostSQL start */\n' + sql + '\n/* PostSQL end
*/\n\n')
- out.write('show tables\n\n')
+ out.write('\d\n\n')
out.write('/* end of generated SQL */\n')
def writeDeleteAllRecords(self, generator, file):
Binary files Webware/MiddleKit/Design/SQLGenerator.pyc and
Webware-MK/MiddleKit/Design/SQLGenerator.pyc differ
diff -uNr Webware/MiddleKit/Run/PostgreSQLObjectStore.py
Webware-MK/MiddleKit/Run/PostgreSQLObjectStore.py
--- Webware/MiddleKit/Run/PostgreSQLObjectStore.py Thu Jan 1 01:00:00 1970
+++ Webware-MK/MiddleKit/Run/PostgreSQLObjectStore.py Mon May 20 14:34:32 2002
@@ -0,0 +1,81 @@
+import sys
+from SQLObjectStore import SQLObjectStore
+from MiddleKit.Run.ObjectKey import ObjectKey
+import pgdb as dbi
+from pgdb import Warning, DatabaseError
+
+class PostgreSQLObjectStore(SQLObjectStore):
+ """
+ PostgresObjectStore does the obvious: it implements an object store backed by
+a PostgreSQL database.
+
+ The connection arguments passed to __init__ are:
+ - host
+ - user
+ - passwd
+ - port
+ - unix_socket
+ - client_flag
+
+ You wouldn't use the 'db' argument, since that is determined by the model.
+ """
+
+ def newConnection(self):
+ args = self._dbArgs.copy()
+ args['database'] = self._model.sqlDatabaseName()
+ return self.dbapiModule().connect(**args)
+
+ def _insertObject(self, object, unknownSerialNums):
+ # basically the same as the SQLObjectStore-
+ # version but modified to retrieve the
+ # serialNum via the oid (for which we need
+ # class-name, unfortunately)
+ sql = object.sqlInsertStmt(unknownSerialNums)
+ conn, curs = self.executeSQL(sql)
+
+ oid = curs.lastoid()
+ className = object.__class__.__name__
+ curs.execute("select %s from %s where oid=%d" %
+ ( className + "id", className, oid ) )
+ conn.commit()
+
+ object.setSerialNum(curs.fetchone()[0])
+ object.setKey(ObjectKey().initFromObject(object))
+ object.setChanged(0)
+
+ self._objects[object.key()] = object
+
+ def dbapiModule(self):
+ return dbi
+
+ def _executeSQL(self, cur, sql):
+ try:
+ cur.execute(sql)
+ except Warning, e:
+ if not self.setting('IgnoreSQLWarnings', 0):
+ raise
+
+ def saveChanges(self):
+ conn, cur = self.connectionAndCursor()
+ try:
+
+ SQLObjectStore.saveChanges(self)
+ except DatabaseError:
+ conn.rollback()
+ raise
+ except Warning:
+ if not self.setting('IgnoreSQLWarnings', 0):
+ conn.rollback()
+ raise
+
+ sys.stderr.write("Committing changes\n")
+ conn.commit()
+
+# Mixins
+
+class StringAttr:
+ def sqlValue(self, value):
+ # Postgres provides a quoting function for string -- use it.
+ if value is None:
+ return 'NULL'
+ else:
+ return str(dbi._quote(value))
