Ticket #409: sqlrelaypg.py

File sqlrelaypg.py, 7.1 KB (added by hugo, 19 years ago)

first take at an sqlrelay backend for postgresql

Line 
1"""
2PostgreSQL database backend for Django using sqlrelay.
3
4Requires sqlrelay bindings: http://sqlrelay.sourceforge.net/
5"""
6
7from django.core.db import base, typecasts
8from SQLRelay import PySQLRDB as Database
9
10DatabaseError = Database.DatabaseError
11
12class SQLRelayCursorWrapper(Database.SQLRCursor):
13 """
14 Django uses "format" style placeholders, but sqlrelay uses "named parameter" style.
15 This fixes it -- but note that if you want to use a literal "%s" in a query,
16 you'll need to use "%%s" (which I belive is true of other wrappers as well).
17 """
18
19 def execute(self, query, params=[]):
20 (query, params) = self.convert_query(query, params)
21 return Database.SQLRCursor.execute(self, query, params)
22
23 def executemany(self, query, params=[]):
24 query = self.convert_query(query, params[0])
25 return Database.SQLRCursor.executemany(self, query, params)
26
27 def convert_query(self, query, params):
28 return (query.replace('%s', '?'), dict([(str(idx+1), v) for (idx, v) in enumerate(params)]))
29 qdict = {}
30 for idx, param in enumerate(params):
31 p = query.find('%s')
32 if p >= 0:
33 pname = ':val%d' % idx
34 query = query[:p] + pname + query[p+2:]
35 qdict[pname] = param
36 else:
37 raise SyntaxError, query
38
39class DatabaseWrapper:
40 def __init__(self):
41 self.connection = None
42 self.queries = []
43
44 def cursor(self):
45 from django.conf.settings import DATABASE_USER, DATABASE_NAME, DATABASE_HOST, DATABASE_PORT, DATABASE_PASSWORD, DEBUG, TIME_ZONE
46 if self.connection is None:
47 if DATABASE_NAME == '':
48 from django.core.exceptions import ImproperlyConfigured
49 raise ImproperlyConfigured, "You need to specify DATABASE_NAME in your Django settings file."
50 self.connection = Database.connect(DATABASE_HOST, int(DATABASE_PORT or '9000'), '', DATABASE_USER, DATABASE_PASSWORD)
51 cursor = SQLRelayCursorWrapper(self.connection.connection)
52 cursor.execute("SET TIME ZONE '%s'" % TIME_ZONE)
53 cursor.execute("BEGIN")
54 if DEBUG:
55 return base.CursorDebugWrapper(cursor, self)
56 return cursor
57
58 def commit(self):
59 return self.connection.commit()
60
61 def rollback(self):
62 if self.connection:
63 return self.connection.rollback()
64
65 def close(self):
66 if self.connection is not None:
67 self.connection.close()
68 self.connection = None
69
70def dictfetchone(cursor):
71 "Returns a row from the cursor as a dict"
72 return cursor.dictfetchone()
73
74def dictfetchmany(cursor, number):
75 "Returns a certain number of rows from a cursor as a dict"
76 return cursor.dictfetchmany(number)
77
78def dictfetchall(cursor):
79 "Returns all rows from a cursor as a dict"
80 return cursor.dictfetchall()
81
82def get_last_insert_id(cursor, table_name, pk_name):
83 cursor.execute("SELECT CURRVAL('%s_%s_seq')" % (table_name, pk_name))
84 return cursor.fetchone()[0]
85
86def get_date_extract_sql(lookup_type, table_name):
87 # lookup_type is 'year', 'month', 'day'
88 # http://www.postgresql.org/docs/8.0/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
89 return "EXTRACT('%s' FROM %s)" % (lookup_type, table_name)
90
91def get_date_trunc_sql(lookup_type, field_name):
92 # lookup_type is 'year', 'month', 'day'
93 # http://www.postgresql.org/docs/8.0/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
94 return "DATE_TRUNC('%s', %s)" % (lookup_type, field_name)
95
96def get_limit_offset_sql(limit, offset=None):
97 sql = "LIMIT %s" % limit
98 if offset and offset != 0:
99 sql += " OFFSET %s" % offset
100 return sql
101
102def get_random_function_sql():
103 return "RANDOM()"
104
105def get_table_list(cursor):
106 "Returns a list of table names in the current database."
107 cursor.execute("""
108 SELECT c.relname
109 FROM pg_catalog.pg_class c
110 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
111 WHERE c.relkind IN ('r', 'v', '')
112 AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
113 AND pg_catalog.pg_table_is_visible(c.oid)""")
114 return [row[0] for row in cursor.fetchall()]
115
116def get_relations(cursor, table_name):
117 """
118 Returns a dictionary of {field_index: (field_index_other_table, other_table)}
119 representing all relationships to the given table. Indexes are 0-based.
120 """
121 cursor.execute("""
122 SELECT con.conkey, con.confkey, c2.relname
123 FROM pg_constraint con, pg_class c1, pg_class c2
124 WHERE c1.oid = con.conrelid
125 AND c2.oid = con.confrelid
126 AND c1.relname = %s
127 AND con.contype = 'f'""", [table_name])
128 relations = {}
129 for row in cursor.fetchall():
130 try:
131 # row[0] and row[1] are like "{2}", so strip the curly braces.
132 relations[int(row[0][1:-1]) - 1] = (int(row[1][1:-1]) - 1, row[2])
133 except ValueError:
134 continue
135 return relations
136
137def quote_name(name):
138 if name.startswith('"') and name.endswith('"'):
139 return name # Quoting once is enough.
140 return '"%s"' % name
141
142OPERATOR_MAPPING = {
143 'exact': '=',
144 'iexact': 'ILIKE',
145 'contains': 'LIKE',
146 'icontains': 'ILIKE',
147 'ne': '!=',
148 'gt': '>',
149 'gte': '>=',
150 'lt': '<',
151 'lte': '<=',
152 'startswith': 'LIKE',
153 'endswith': 'LIKE',
154 'istartswith': 'ILIKE',
155 'iendswith': 'ILIKE',
156}
157
158# This dictionary maps Field objects to their associated PostgreSQL column
159# types, as strings. Column-type strings can contain format strings; they'll
160# be interpolated against the values of Field.__dict__ before being output.
161# If a column type is set to None, it won't be included in the output.
162DATA_TYPES = {
163 'AutoField': 'serial',
164 'BooleanField': 'boolean',
165 'CharField': 'varchar(%(maxlength)s)',
166 'CommaSeparatedIntegerField': 'varchar(%(maxlength)s)',
167 'DateField': 'date',
168 'DateTimeField': 'timestamp with time zone',
169 'EmailField': 'varchar(75)',
170 'FileField': 'varchar(100)',
171 'FilePathField': 'varchar(100)',
172 'FloatField': 'numeric(%(max_digits)s, %(decimal_places)s)',
173 'ImageField': 'varchar(100)',
174 'IntegerField': 'integer',
175 'IPAddressField': 'inet',
176 'ManyToManyField': None,
177 'NullBooleanField': 'boolean',
178 'OneToOneField': 'integer',
179 'PhoneNumberField': 'varchar(20)',
180 'PositiveIntegerField': 'integer CHECK (%(name)s >= 0)',
181 'PositiveSmallIntegerField': 'smallint CHECK (%(name)s >= 0)',
182 'SlugField': 'varchar(50)',
183 'SmallIntegerField': 'smallint',
184 'TextField': 'text',
185 'TimeField': 'time',
186 'URLField': 'varchar(200)',
187 'USStateField': 'varchar(2)',
188}
189
190# Maps type codes to Django Field types.
191DATA_TYPES_REVERSE = {
192 16: 'BooleanField',
193 21: 'SmallIntegerField',
194 23: 'IntegerField',
195 25: 'TextField',
196 869: 'IPAddressField',
197 1043: 'CharField',
198 1082: 'DateField',
199 1083: 'TimeField',
200 1114: 'DateTimeField',
201 1184: 'DateTimeField',
202 1266: 'TimeField',
203 1700: 'FloatField',
204}
Back to Top