dabo Commit
Revision 4822
Date: 2008-12-11 14:28:44 -0800 (Thu, 11 Dec 2008)
Author: Cito
Trac: http://trac.dabodev.com/dabo/changeset/4822
Changed:
U trunk/dabo/db/dbPostgreSQL.py
Log:
Some improvements of the PostgreSQL support (see ticket #1189).
Diff:
Modified: trunk/dabo/db/dbPostgreSQL.py
===================================================================
--- trunk/dabo/db/dbPostgreSQL.py 2008-12-11 18:52:01 UTC (rev 4821)
+++ trunk/dabo/db/dbPostgreSQL.py 2008-12-11 22:28:44 UTC (rev 4822)
@@ -20,7 +20,7 @@
port = str(connectInfo.Port)
if not port or port == "None":
port = "5432"
-
+
DSN = "host=%s port=%s dbname=%s user=%s password=%s" %
(connectInfo.Host,
port, connectInfo.Database, connectInfo.User,
connectInfo.revealPW())
# Instead of blindly appending kwargs here, it would be
preferable to only accept
@@ -39,45 +39,30 @@
def getDictCursorClass(self):
# the new psycopg 2.0 supports DictCursor
import psycopg2.extras as cursors
- return cursors.DictCursor
+ return cursors.DictCursor
def escQuote(self, val):
# escape backslashes and single quotes, and
# wrap the result in single quotes
- sl = "\\"
- qt = "\'"
- return qt + val.replace(sl, sl+sl).replace(qt, sl+qt) + qt
+ return "'%s'" % val.replace('\\', '\\\\').replace("'", "''")
def formatDateTime(self, val):
""" We need to wrap the value in quotes. """
- sqt = "'" # single quote
- val = self._stringify(val)
- return "%s%s%s" % (sqt, val, sqt)
+ return "'%s'" % self._stringify(val)
def getTables(self, cursor, includeSystemTables=False):
- # jfcs 11/01/04 assumed public schema
- # jfcs 01/22/07 added below to support schema
- # thanks to Phillip J. Allen who provided a Select state that
filtered for the user name
- if includeSystemTables:
- sqltablestr = (("SELECT schemaname || '.' || tablename
AS tablename FROM pg_tables WHERE has_table_privilege('%s', schemaname || '.'
|| tablename, 'SELECT')") % self.conn_user)
- else:
- #sqltablestr = (("SELECT schemaname || '.' || tablename
AS tablename FROM pg_tables WHERE (schemaname not like 'pg_%s' and schemaname
not like 'information%s') and has_table_privilege('%s', schemaname || '.' ||
tablename, 'SELECT')") % ('%', '%', self.conn_user))
- # jfcs 06/19/08
- sqltablestr = (("""SELECT schemaname || '.' ||
tablename AS tablename
- FROM pg_tables
- WHERE (schemaname not like 'pg_%s'
- and schemaname not like
'information%s')
- and has_table_privilege('%s',
schemaname || '.' || tablename, 'SELECT')
- """) % ("%", "%", self.conn_user))
- cursor.execute(sqltablestr)
- rs = cursor.getDataSet()
- tables = []
- for record in rs:
- tables.append(record["tablename"])
- return tuple(tables)
+ query = ["SELECT schemaname||'.'||tablename AS tablename"
+ " FROM pg_tables WHERE"]
+ if not includeSystemTables:
+ query.append("(schemaname NOT LIKE 'pg_%' AND "
+ "schemaname NOT LIKE 'information%')
AND")
+ query.append("has_schema_privilege(schemaname, 'usage') AND "
+
"has_table_privilege(schemaname||'.'||tablename, 'select')")
+ cursor.execute(' '.join(query))
+ return tuple(record["tablename"] for record in
cursor.getDataSet())
def getTableRecordCount(self, tableName, cursor):
@@ -85,60 +70,38 @@
return cursor.getDataSet()[0]["ncount"]
- def getFields(self, tableName, cursor):
- """JFCS support for 7.4 and greater
- Requires that each table have a primary key"""
+ def getFields(self, tableName, cursor, includeSystemFields=False):
try:
- localSchemaName, localTableName = tableName.split(".",
1)
+ schemaName, tableName = tableName.split(".", 1)
except ValueError:
- raise ValueError,_("Please use schema-qualified
datasource names (e.g. 'public.%s')" )% tableName
-
-
- cursor.execute("""SELECT a.attname, t.typname from pg_attribute
a, pg_type t, pg_class c
- LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
- where a.attrelid = c.oid
- and a.atttypid = t.oid
- AND n.nspname || '.'||c.relname = '%s'
- order by c.relname, a.attname""" % tableName)
-
- rs = cursor.getDataSet()
-
- #the code below may not work with 7.4 due to the use of the
function generate_series()
- #However a postgres function can be added to simulate
generate_series()
- #CREATE OR REPLACE FUNCTION generate_series(int, int) RETURNS
setof int AS
- #'BEGIN
- #FOR i IN $1..$2
- #LOOP
- #RETURN NEXT i;
- #END LOOP;
- #RETURN;
- #END; ' LANGUAGE plpgsql;
-
- sqlstr = """SELECT n.nspname AS schema_name, c.relname AS
table_name,
- c.oid AS table_oid, a.attname AS column_name, idx.n + 1 AS
ordinal_position
- FROM pg_class c, pg_attribute a, pg_index i, pg_namespace n,
generate_series(0, 31) idx(n)
- WHERE c.oid = a.attrelid AND c.oid = i.indrelid AND i.indisprimary AND
a.attnum = i.indkey[idx.n]
- AND NOT a.attisdropped
- AND has_schema_privilege(n.oid, 'USAGE'::text)
- AND n.nspname NOT LIKE 'pg!_%s' ESCAPE '!'
- AND has_table_privilege(c.oid, 'SELECT'::text)
- AND c.relnamespace = n.oid and c.relname = '%s' and n.nspname = '%s'
""" % ('%', localTableName, localSchemaName)
-
- cursor.execute(sqlstr)
- rs2 = cursor.getDataSet()
- if rs2 == ():
- thePKFieldName = None
- else:
- #thestr = rs2[0][3]
- thePKFieldName = rs2[0]["column_name"]
-
+ schemaName = None
+ sql = ["SELECT a.attname, t.typname,"
+ # Note that the generate_series() function does
not exist
+ # in Postgres < 8.0, but it can easily be
added, and that
+ # in Postgres > 8.1, we could use the ANY
syntax instead.
+ " EXISTS(SELECT * FROM generate_series(0, 31)
idx(n)"
+ " WHERE a.attnum = i.indkey[idx.n]) AS
isprimary"
+ " FROM pg_class c"
+ " JOIN pg_namespace n ON n.oid = c.relnamespace"
+ " JOIN pg_attribute a ON a.attrelid = c.oid"
+ " JOIN pg_type t ON t.oid = a.atttypid"
+ " LEFT JOIN pg_index i ON i.indrelid = c.oid
AND i.indisprimary",
+ "WHERE c.relname = '%s'" % tableName]
+ if schemaName:
+ sql.append("AND n.nspname = '%s'" % schemaName)
+ if not includeSystemFields:
+ sql.append("AND a.attname NOT IN "
+ " ('ctid', 'cmin', 'cmax', 'tableoid',
'xmax', 'xmin')")
+ sql.append("AND has_schema_privilege(n.oid, 'usage')"
+ " AND has_table_privilege(c.oid, 'select')"
+ " AND pg_table_is_visible(c.oid)"
+ " ORDER BY c.relname, a.attname")
+ cursor.execute(' '.join(sql))
fields = []
- for r in rs:
- name = r["attname"]
- fldType =r["typname"]
- pk = False
- if thePKFieldName is not None:
- pk = (name in thePKFieldName)
+ for r in cursor.getDataSet():
+ name = r["attname"].strip()
+ fldType = r["typname"]
+ pk = r["isprimary"]
if "int" in fldType:
fldType = "I"
elif "char" in fldType :
@@ -177,10 +140,9 @@
fldType = "C"
elif "oid" in fldType:
fldType = "I"
- #fldType = "G"
else:
fldType = "?"
- fields.append((name.strip(), fldType, pk))
+ fields.append((name, fldType, pk))
return tuple(fields)
@@ -188,7 +150,7 @@
""" By default, the update SQL statement will be in the form of
tablename.fieldname
but Postgres does not accept this syntax. If not, change
- this method to return an empty string, or whatever should
+ this method to return an empty string, or whatever should
preceed the field name in an update statement.
Postgres needs to return an empty string."""
return ""
@@ -198,15 +160,15 @@
""" Most backends will return a non-zero number if there are
updates.
Some do not, so this will have to be customized in those cases.
"""
- return
+ return
def noResultsOnDelete(self):
""" Most backends will return a non-zero number if there are
deletions.
Some do not, so this will have to be customized in those cases.
"""
- #raise dException.dException, _("No records deleted")
- return
+ # raise dException.dException(_("No records deleted"))
+ return
def flush(self, cursor):
@@ -219,54 +181,57 @@
def getLastInsertID(self, cursor):
""" Return the ID of the last inserted row, or None.
-
- When inserting a new record in a table that auto-generates a PK
(such
+
+ When inserting a new record in a table that auto-generates a PK
(such
as a serial data type) value, different databases have their
own way of retrieving that value.
- With Postgres a sequence is created. The SQL statement
determines the sequence name
+ With Postgres a sequence is created. The SQL statement
determines the sequence name
('table_pkid_seq') and needs three parameters the schema name,
table name, and the primary
key field for the table.
cursor.KeyField = primary field
cursor.Table = returns 'schema.table' for the cursor
-
+
Postgres uses 'currval(sequence_name)' to determine the last
value of the session.
If two different sessions are open (two users accessing the
same table for example)
currval() will return the correct value for each session.
-
+
"""
-
+
try:
- localSchemaName, localTableName =
cursor.Table.split(".",1) ##tableName.split(".", 1)
+ schemaName, tableName = tableName.split(".", 1)
except ValueError:
- raise ValueError,_("Please use schema-qualified
datasource names (e.g. 'public.%s')" )% tableName
-
+ schemaName = None
- tempCursor =self._connection.cursor()
-
#JFCS 01/13/08 changed the select statement to allow primary
fields that were not based
#on a serial data type to work.
# special thanks to Lorenzo Alberton for his help with parsing
of the fields.
# It has been confirmed that the statement works with 7.4
through 8.3.x
- sql="""
- SELECT substring((SELECT substring(pg_get_expr(d.adbin,
d.adrelid) for 128)
- FROM pg_attrdef d
- WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND
a.atthasdef)
- FROM 'nextval[^'']*''([^'']*)')
- FROM pg_attribute a
- LEFT JOIN pg_class c ON c.oid = a.attrelid
- LEFT JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum =
a.attnum AND a.atthasdef
- LEFT JOIN pg_namespace n ON c.relnamespace = n.oid WHERE
(c.relname = %s)
- AND a.attname = %s and n.nspname=%s AND NOT a.attisdropped AND
a.attnum > 0 AND pg_get_expr(d.adbin, d.adrelid) LIKE 'nextval%%'
- """
+ sql = ["SELECT curval(substring((SELECT substring("
+ "pg_get_expr(d.adbin, d.adrelid) for 128)) as
curval"
+ " FROM pg_attrdef d WHERE d.adrelid =
a.attrelid"
+ " AND d.adnum = a.attnum AND a.atthasdef)"
+ " FROM 'nextval[^'']*''([^'']*)')"
+ " FROM pg_attribute a"
+ " LEFT JOIN pg_class c ON c.oid = a.attrelid"
+ " LEFT JOIN pg_attrdef d ON d.adrelid =
a.attrelid"
+ " AND d.adnum = a.attnum AND a.atthasdef"
+ " LEFT JOIN pg_namespace n ON c.relnamespace =
n.oid",
+ "WHERE a.attname = '%s'" % cursor.KeyField,
+ "AND (c.relname = '%s')" % tableName]
+ if schemaName:
+ sql.append(" AND n.nspname = '%s'" % schemaName)
+ else:
+ sql.append(" AND pg_table_is_visible(c.oid)")
+ sql.append("NOT a.attisdropped AND a.attnum > 0"
+ " AND pg_get_expr(d.adbin, d.adrelid) LIKE
'nextval%'")
- tempCursor.execute(sql, ( localTableName, cursor.KeyField,
localSchemaName))
- rs = tempCursor.fetchall()
- #if rs is None:
- #dabo.dbActivityLog.write("no data in getLastInsertID")
-
- sqlWithseq_name="""select currval('%s') as curval""" %
(rs[0][0], )
- tempCursor.execute(sqlWithseq_name)
- rs = tempCursor.fetchall()
- if not rs[0][0] is None:
- return rs[0][0]
+ tempCursor = self._connection.cursor()
+ try:
+ tempCursor.execute(' '.join(sql))
+ rs = tempCursor.fetchone()
+ finally:
+ tempCursor.close()
+ if not rs or rs[0] is None:
+ raise AttributeError("Unable to determine the sequence
used"
+ " or the sequence returns a strange
value.")
else:
- raise AttributeError, "Unable to determine the sequence
used or the sequence return a strange value."
+ return rs[0]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
Searchable Archives: http://leafe.com/archives/search/dabo-dev
This message:
http://leafe.com/archives/byMID/[email protected]
