1
2
3 """
4 PostgreSQL extended database driver.
5 """
6
7 __docformat__ = "restructuredtext en"
8
9
10
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
22
23
24 VENDOR = 'PostgreSQL Global Development Group'
25 PRODUCT = 'PostgreSQL'
26
27
28
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
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
63
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
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
147
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
171
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