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 '''Postgresql backend via psycopg for Roundup.'''
9 __docformat__ = 'restructuredtext'
12 from roundup import hyperdb, date
13 from roundup.backends import rdbms_common
14 import psycopg
15 import os, shutil, popen2
17 class Database(rdbms_common.Database):
18 arg = '%s'
20 def sql_open_connection(self):
21 db = getattr(self.config, 'POSTGRESQL_DATABASE')
22 try:
23 self.conn = psycopg.connect(**db)
24 except psycopg.OperationalError, message:
25 raise hyperdb.DatabaseError, message
27 self.cursor = self.conn.cursor()
29 try:
30 self.load_dbschema()
31 except:
32 self.rollback()
33 self.database_schema = {}
34 self.sql("CREATE TABLE schema (schema TEXT)")
35 self.sql("CREATE TABLE ids (name VARCHAR(255), num INT4)")
36 self.sql("CREATE INDEX ids_name_idx ON ids(name)")
37 self.create_version_2_tables()
39 def create_version_2_tables(self):
40 self.cursor.execute('CREATE TABLE otks (key VARCHAR(255), '
41 'value VARCHAR(255), __time NUMERIC)')
42 self.cursor.execute('CREATE INDEX otks_key_idx ON otks(key)')
43 self.cursor.execute('CREATE TABLE sessions (key VARCHAR(255), '
44 'last_use NUMERIC, user VARCHAR(255))')
45 self.cursor.execute('CREATE INDEX sessions_key_idx ON sessions(key)')
47 def __repr__(self):
48 return '<roundpsycopgsql 0x%x>' % id(self)
50 def sql_stringquote(self, value):
51 ''' psycopg.QuotedString returns a "buffer" object with the
52 single-quotes around it... '''
53 return str(psycopg.QuotedString(str(value)))[1:-1]
55 def sql_index_exists(self, table_name, index_name):
56 sql = 'select count(*) from pg_indexes where ' \
57 'tablename=%s and indexname=%s'%(self.arg, self.arg)
58 self.cursor.execute(sql, (table_name, index_name))
59 return self.cursor.fetchone()[0]
61 def create_class_table(self, spec):
62 cols, mls = self.determine_columns(spec.properties.items())
63 cols.append('id')
64 cols.append('__retired__')
65 scols = ',' . join(['"%s" VARCHAR(255)' % x for x in cols])
66 sql = 'CREATE TABLE "_%s" (%s)' % (spec.classname, scols)
68 if __debug__:
69 print >>hyperdb.DEBUG, 'create_class', (self, sql)
71 self.cursor.execute(sql)
72 return cols, mls
74 def create_journal_table(self, spec):
75 cols = ',' . join(['"%s" VARCHAR(255)' % x
76 for x in 'nodeid date tag action params' . split()])
77 sql = 'CREATE TABLE "%s__journal" (%s)'%(spec.classname, cols)
79 if __debug__:
80 print >>hyperdb.DEBUG, 'create_class', (self, sql)
82 self.cursor.execute(sql)
84 def create_multilink_table(self, spec, ml):
85 sql = '''CREATE TABLE "%s_%s" (linkid VARCHAR(255),
86 nodeid VARCHAR(255))''' % (spec.classname, ml)
88 if __debug__:
89 print >>hyperdb.DEBUG, 'create_class', (self, sql)
91 self.cursor.execute(sql)
93 class Class(rdbms_common.Class):
94 pass
95 class IssueClass(rdbms_common.IssueClass):
96 pass
97 class FileClass(rdbms_common.FileClass):
98 pass