Package grizzled :: Package db :: Module postgresql
[hide private]
[frames] | no frames]

Source Code for Module grizzled.db.postgresql

  1  # $Id: f485c4e2802f66973d04d5047ee9d3e5cfd249ce $ 
  2   
  3  """ 
  4  PostgreSQL extended database driver. 
  5  """ 
  6   
  7  __docformat__ = "restructuredtext en" 
  8   
  9  # --------------------------------------------------------------------------- 
 10  # Imports 
 11  # --------------------------------------------------------------------------- 
 12   
 13  import os 
 14  import sys 
 15  import re 
 16   
 17  from grizzled.db.base import (DBDriver, Error, Warning, TableMetadata, 
 18                                IndexMetadata, RDBMSMetadata) 
 19   
 20  # --------------------------------------------------------------------------- 
 21  # Constants 
 22  # --------------------------------------------------------------------------- 
 23   
 24  VENDOR  = 'PostgreSQL Global Development Group' 
 25  PRODUCT = 'PostgreSQL' 
 26   
 27  # --------------------------------------------------------------------------- 
 28  # Classes 
 29  # --------------------------------------------------------------------------- 
 30   
31 -class PostgreSQLDriver(DBDriver):
32 """DB Driver for PostgreSQL, using the psycopg2 DB API module.""" 33 34 TYPE_RE = re.compile('([a-z ]+)(\([0-9]+\))?') 35
36 - def get_import(self):
37 import psycopg2 38 return psycopg2
39
40 - def get_display_name(self):
41 return "PostgreSQL"
42
43 - def do_connect(self, 44 host='localhost', 45 port=None, 46 user='', 47 password='', 48 database='default'):
49 dbi = self.get_import() 50 dsn = 'host=%s dbname=%s user=%s password=%s' %\ 51 (host, database, user, password) 52 return dbi.connect(dsn=dsn)
53
54 - def get_rdbms_metadata(self, cursor):
55 cursor.execute('SELECT version()') 56 rs = cursor.fetchone() 57 if rs is None: 58 result = RDBMSMetadata(VENDOR, PRODUCT, 'unknown') 59 else: 60 result = RDBMSMetadata(VENDOR, PRODUCT, rs[0]) 61 62 return result
63
64 - def get_table_metadata(self, table, cursor):
65 self._ensure_valid_table(cursor, table) 66 sel = """\ 67 SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), 68 (SELECT substring(d.adsrc for 128) 69 FROM pg_catalog.pg_attrdef d 70 WHERE d.adrelid = a.attrelid AND 71 d.adnum = a.attnum AND a.atthasdef) AS DEFAULT, 72 a.attnotnull, 73 a.attnum, 74 a.attrelid as table_oid 75 FROM pg_catalog.pg_attribute a 76 WHERE a.attrelid = 77 (SELECT c.oid FROM pg_catalog.pg_class c 78 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 79 WHERE (pg_table_is_visible(c.oid)) AND c.relname = '%s' 80 AND c.relkind in ('r','v')) 81 AND a.attnum > 0 82 AND NOT a.attisdropped 83 ORDER BY a.attnum""" 84 85 cursor.execute(sel % table) 86 rs = cursor.fetchone() 87 results = [] 88 while rs is not None: 89 column = rs[0] 90 coltype = rs[1] 91 null = not rs[3] 92 93 match = self.TYPE_RE.match(coltype) 94 if match: 95 coltype = match.group(1) 96 size = match.group(2) 97 if size: 98 size = size[1:-1] 99 if 'char' in coltype: 100 max_char_size = size 101 precision = None 102 else: 103 max_char_size = None 104 precision = size 105 106 data = TableMetadata(column, 107 coltype, 108 max_char_size, 109 precision, 110 0, 111 null) 112 results += [data] 113 rs = cursor.fetchone() 114 115 return results
116
117 - def get_index_metadata(self, table, cursor):
118 self._ensure_valid_table(cursor, table) 119 # First, issue one query to get the list of indexes for the table. 120 index_names = self.__get_index_names(table, cursor) 121 122 # Now we need two more queries: One to get the columns in the 123 # index and another to get descriptive information. 124 results = [] 125 for name in index_names: 126 columns = self.__get_index_columns(name, cursor) 127 desc = self.__get_index_description(name, cursor) 128 results += [IndexMetadata(name, columns, desc)] 129 130 return results
131
132 - def get_tables(self, cursor):
133 134 sel = "SELECT tablename FROM pg_tables " \ 135 "WHERE tablename NOT LIKE 'pg_%' AND tablename NOT LIKE 'sql\_%'" 136 cursor.execute(sel) 137 table_names = [] 138 rs = cursor.fetchone() 139 while rs is not None: 140 table_names += [rs[0]] 141 rs = cursor.fetchone() 142 143 return table_names
144
145 - def __get_index_names(self, table, cursor):
146 # Adapted from the pgsql command "\d indexname", PostgreSQL 8. 147 # (Invoking the pgsql command with -E shows the issued SQL.) 148 149 sel = "SELECT n.nspname, c.relname as \"IndexName\", c2.relname " \ 150 "FROM pg_catalog.pg_class c " \ 151 "JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid " \ 152 "JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid " \ 153 "LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner " \ 154 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace " \ 155 "WHERE c.relkind IN ('i','') " \ 156 "AND n.nspname NOT IN ('pg_catalog', 'pg_toast') " \ 157 "AND pg_catalog.pg_table_is_visible(c.oid) " \ 158 "AND c2.relname = '%s'" % table.lower() 159 160 cursor.execute(sel) 161 index_names = [] 162 rs = cursor.fetchone() 163 while rs is not None: 164 index_names += [rs[1]] 165 rs = cursor.fetchone() 166 167 return index_names
168
169 - def __get_index_columns(self, index_name, cursor):
170 # Adapted from the pgsql command "\d indexname", PostgreSQL 8. 171 # (Invoking the pgsql command with -E shows the issued SQL.) 172 173 sel = "SELECT a.attname, " \ 174 "pg_catalog.format_type(a.atttypid, a.atttypmod), " \ 175 "a.attnotnull " \ 176 "FROM pg_catalog.pg_attribute a, pg_catalog.pg_index i " \ 177 "WHERE a.attrelid in " \ 178 " (SELECT c.oid FROM pg_catalog.pg_class c " \ 179 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace " \ 180 " WHERE pg_catalog.pg_table_is_visible(c.oid) " \ 181 "AND c.relname ~ '^(%s)$') " \ 182 "AND a.attnum > 0 AND NOT a.attisdropped " \ 183 "AND a.attrelid = i.indexrelid " \ 184 "ORDER BY a.attnum" % index_name 185 cursor.execute(sel) 186 columns = [] 187 rs = cursor.fetchone() 188 while rs is not None: 189 columns += [rs[0]] 190 rs = cursor.fetchone() 191 192 return columns
193
194 - def __get_index_description(self, index_name, cursor):
195 sel = "SELECT i.indisunique, i.indisprimary, i.indisclustered, " \ 196 "a.amname, c2.relname, " \ 197 "pg_catalog.pg_get_expr(i.indpred, i.indrelid, true) " \ 198 "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, " \ 199 "pg_catalog.pg_class c2, pg_catalog.pg_am a " \ 200 "WHERE i.indexrelid = c.oid AND c.relname ~ '^(%s)$' " \ 201 "AND c.relam = a.oid AND i.indrelid = c2.oid" % index_name 202 cursor.execute(sel) 203 desc = '' 204 rs = cursor.fetchone() 205 if rs is not None: 206 if str(rs[1]) == "True": 207 desc += "(PRIMARY) " 208 209 if str(rs[0]) == "True": 210 desc += "Unique" 211 else: 212 desc += "Non-unique" 213 214 if str(rs[2]) == "True": 215 desc += ", clustered" 216 else: 217 desc += ", non-clustered" 218 219 if rs[3] is not None: 220 desc += " %s" % rs[3] 221 222 desc += ' index' 223 224 if desc == '': 225 desc = None 226 227 return desc
228