1 #
2 # Copyright (c) 2003 Martynas Sklyzmantas, Andrey Lebedev <andrey@micro.lt>
3 #
4 # This module is free software, and you may redistribute it and/or modify
5 # under the same terms as Python, so long as this copyright message and
6 # disclaimer are retained in their original form.
7 #
8 # psycopg backend for roundup
9 #
11 from roundup.backends.rdbms_common import *
12 from roundup.backends import rdbms_common
13 import psycopg
14 import os, shutil
16 class Maintenance:
17 """ Database maintenance functions """
18 def db_nuke(self, config):
19 """Clear all database contents and drop database itself"""
20 config.POSTGRESQL_DATABASE['database'] = 'template1'
21 db = Database(config, 'admin')
22 db.conn.set_isolation_level(0)
23 db.sql("DROP DATABASE %s" % config.POSTGRESQL_DBNAME)
24 db.sql("CREATE DATABASE %s" % config.POSTGRESQL_DBNAME)
25 if os.path.exists(config.DATABASE):
26 shutil.rmtree(config.DATABASE)
27 config.POSTGRESQL_DATABASE['database'] = config.POSTGRESQL_DBNAME
29 def db_exists(self, config):
30 """Check if database already exists"""
31 try:
32 db = Database(config, 'admin')
33 return 1
34 except:
35 return 0
37 class Database(Database):
38 arg = '%s'
40 def open_connection(self):
41 db = getattr(self.config, 'POSTGRESQL_DATABASE')
42 try:
43 self.conn = psycopg.connect(**db)
44 except psycopg.OperationalError, message:
45 raise DatabaseError, message
47 self.cursor = self.conn.cursor()
49 try:
50 self.database_schema = self.load_dbschema()
51 except:
52 self.rollback()
53 self.database_schema = {}
54 self.sql("CREATE TABLE schema (schema TEXT)")
55 self.sql("CREATE TABLE ids (name VARCHAR(255), num INT4)")
57 def close(self):
58 self.conn.close()
60 def __repr__(self):
61 return '<psycopgroundsql 0x%x>' % id(self)
63 def sql_fetchone(self):
64 return self.cursor.fetchone()
66 def sql_fetchall(self):
67 return self.cursor.fetchall()
69 def sql_stringquote(self, value):
70 return psycopg.QuotedString(str(value))
72 def save_dbschema(self, schema):
73 s = repr(self.database_schema)
74 self.sql('INSERT INTO schema VALUES (%s)', (s,))
76 def load_dbschema(self):
77 self.cursor.execute('SELECT schema FROM schema')
78 schema = self.cursor.fetchone()
79 if schema:
80 return eval(schema[0])
82 def save_journal(self, classname, cols, nodeid, journaldate,
83 journaltag, action, params):
84 params = repr(params)
85 entry = (nodeid, journaldate, journaltag, action, params)
87 a = self.arg
88 sql = 'INSERT INTO %s__journal (%s) values (%s, %s, %s, %s, %s)'%(
89 classname, cols, a, a, a, a, a)
91 if __debug__:
92 print >>hyperdb.DEBUG, 'addjournal', (self, sql, entry)
94 self.cursor.execute(sql, entry)
96 def load_journal(self, classname, cols, nodeid):
97 sql = 'SELECT %s FROM %s__journal WHERE nodeid = %s' % (
98 cols, classname, self.arg)
100 if __debug__:
101 print >>hyperdb.DEBUG, 'getjournal', (self, sql, nodeid)
103 self.cursor.execute(sql, (nodeid,))
104 res = []
105 for nodeid, date_stamp, user, action, params in self.cursor.fetchall():
106 params = eval(params)
107 res.append((nodeid, date.Date(date_stamp), user, action, params))
108 return res
110 def create_class_table(self, spec):
111 cols, mls = self.determine_columns(spec.properties.items())
112 cols.append('id')
113 cols.append('__retired__')
114 scols = ',' . join(['"%s" VARCHAR(255)' % x for x in cols])
115 sql = 'CREATE TABLE "_%s" (%s)' % (spec.classname, scols)
117 if __debug__:
118 print >>hyperdb.DEBUG, 'create_class', (self, sql)
120 self.cursor.execute(sql)
121 return cols, mls
123 def create_journal_table(self, spec):
124 cols = ',' . join(['"%s" VARCHAR(255)' % x
125 for x in 'nodeid date tag action params' . split()])
126 sql = 'CREATE TABLE "%s__journal" (%s)'%(spec.classname, cols)
128 if __debug__:
129 print >>hyperdb.DEBUG, 'create_class', (self, sql)
131 self.cursor.execute(sql)
133 def create_multilink_table(self, spec, ml):
134 sql = '''CREATE TABLE "%s_%s" (linkid VARCHAR(255),
135 nodeid VARCHAR(255))''' % (spec.classname, ml)
137 if __debug__:
138 print >>hyperdb.DEBUG, 'create_class', (self, sql)
140 self.cursor.execute(sql)
142 # Static methods
143 nuke = Maintenance().db_nuke
144 exists = Maintenance().db_exists
146 class PsycopgClass:
147 def find(self, **propspec):
148 """Get the ids of nodes in this class which link to the given nodes."""
150 if __debug__:
151 print >>hyperdb.DEBUG, 'find', (self, propspec)
153 # shortcut
154 if not propspec:
155 return []
157 # validate the args
158 props = self.getprops()
159 propspec = propspec.items()
160 for propname, nodeids in propspec:
161 # check the prop is OK
162 prop = props[propname]
163 if not isinstance(prop, Link) and not isinstance(prop, Multilink):
164 raise TypeError, "'%s' not a Link/Multilink property"%propname
166 # first, links
167 l = []
168 where = []
169 allvalues = ()
170 a = self.db.arg
171 for prop, values in propspec:
172 if not isinstance(props[prop], hyperdb.Link):
173 continue
174 if type(values) is type(''):
175 allvalues += (values,)
176 where.append('_%s = %s' % (prop, a))
177 else:
178 allvalues += tuple(values.keys())
179 where.append('_%s in (%s)' % (prop, ','.join([a]*len(values))))
180 tables = []
181 if where:
182 self.db.sql('SELECT id AS nodeid FROM _%s WHERE %s' % (
183 self.classname, ' and '.join(where)), allvalues)
184 l += [x[0] for x in self.db.sql_fetchall()]
186 # now multilinks
187 for prop, values in propspec:
188 vals = ()
189 if not isinstance(props[prop], hyperdb.Multilink):
190 continue
191 if type(values) is type(''):
192 vals = (values,)
193 s = a
194 else:
195 vals = tuple(values.keys())
196 s = ','.join([a]*len(values))
197 query = 'SELECT nodeid FROM %s_%s WHERE linkid IN (%s)'%(
198 self.classname, prop, s)
199 self.db.sql(query, vals)
200 l += [x[0] for x in self.db.sql_fetchall()]
202 if __debug__:
203 print >>hyperdb.DEBUG, 'find ... ', l
205 # Remove duplicated ids
206 d = {}
207 for k in l:
208 d[k] = 1
209 return d.keys()
211 return l
213 class Class(PsycopgClass, rdbms_common.Class):
214 pass
215 class IssueClass(PsycopgClass, rdbms_common.IssueClass):
216 pass
217 class FileClass(PsycopgClass, rdbms_common.FileClass):
218 pass