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'
11 import os, shutil, popen2, time
12 import psycopg
14 from roundup import hyperdb, date
15 from roundup.backends import rdbms_common
17 def db_create(config):
18 """Clear all database contents and drop database itself"""
19 if __debug__:
20 print >> hyperdb.DEBUG, '+++ create database +++'
21 name = config.POSTGRESQL_DATABASE['database']
22 n = 0
23 while n < 10:
24 cout,cin = popen2.popen4('createdb %s'%name)
25 cin.close()
26 response = cout.read().split('\n')[0]
27 if response.find('FATAL') != -1:
28 raise RuntimeError, response
29 elif response.find('ERROR') != -1:
30 if not response.find('is being accessed by other users') != -1:
31 raise RuntimeError, response
32 if __debug__:
33 print >> hyperdb.DEBUG, '+++ SLEEPING +++'
34 time.sleep(1)
35 n += 1
36 continue
37 return
38 raise RuntimeError, '10 attempts to create database failed'
40 def db_nuke(config, fail_ok=0):
41 """Clear all database contents and drop database itself"""
42 if __debug__:
43 print >> hyperdb.DEBUG, '+++ nuke database +++'
44 name = config.POSTGRESQL_DATABASE['database']
45 n = 0
46 if os.path.exists(config.DATABASE):
47 shutil.rmtree(config.DATABASE)
48 while n < 10:
49 cout,cin = popen2.popen4('dropdb %s'%name)
50 cin.close()
51 response = cout.read().split('\n')[0]
52 if response.endswith('does not exist') and fail_ok:
53 return
54 elif response.find('FATAL') != -1:
55 raise RuntimeError, response
56 elif response.find('ERROR') != -1:
57 if not response.find('is being accessed by other users') != -1:
58 raise RuntimeError, response
59 if __debug__:
60 print >> hyperdb.DEBUG, '+++ SLEEPING +++'
61 time.sleep(1)
62 n += 1
63 continue
64 return
65 raise RuntimeError, '10 attempts to nuke database failed'
67 def db_exists(config):
68 """Check if database already exists"""
69 db = getattr(config, 'POSTGRESQL_DATABASE')
70 try:
71 conn = psycopg.connect(**db)
72 conn.close()
73 if __debug__:
74 print >> hyperdb.DEBUG, '+++ database exists +++'
75 return 1
76 except:
77 if __debug__:
78 print >> hyperdb.DEBUG, '+++ no database +++'
79 return 0
81 class Database(rdbms_common.Database):
82 arg = '%s'
84 def sql_open_connection(self):
85 db = getattr(self.config, 'POSTGRESQL_DATABASE')
86 try:
87 conn = psycopg.connect(**db)
88 except psycopg.OperationalError, message:
89 raise hyperdb.DatabaseError, message
91 cursor = conn.cursor()
93 return (conn, cursor)
95 def open_connection(self):
96 if not db_exists(self.config):
97 db_create(self.config)
99 if __debug__:
100 print >>hyperdb.DEBUG, '+++ open database connection +++'
102 self.conn, self.cursor = self.sql_open_connection()
104 try:
105 self.load_dbschema()
106 except:
107 self.rollback()
108 self.init_dbschema()
109 self.sql("CREATE TABLE schema (schema TEXT)")
110 self.sql("CREATE TABLE dual (dummy integer)")
111 self.sql("insert into dual values (1)")
112 self.create_version_2_tables()
114 def create_version_2_tables(self):
115 # OTK store
116 self.cursor.execute('''CREATE TABLE otks (otk_key VARCHAR(255),
117 otk_value VARCHAR(255), otk_time FLOAT(20))''')
118 self.cursor.execute('CREATE INDEX otks_key_idx ON otks(otk_key)')
120 # Sessions store
121 self.cursor.execute('''CREATE TABLE sessions (
122 session_key VARCHAR(255), session_time FLOAT(20),
123 session_value VARCHAR(255))''')
124 self.cursor.execute('''CREATE INDEX sessions_key_idx ON
125 sessions(session_key)''')
127 # full-text indexing store
128 self.cursor.execute('CREATE SEQUENCE ___textids_ids')
129 self.cursor.execute('''CREATE TABLE __textids (
130 _textid integer primary key, _class VARCHAR(255),
131 _itemid VARCHAR(255), _prop VARCHAR(255))''')
132 self.cursor.execute('''CREATE TABLE __words (_word VARCHAR(30),
133 _textid integer)''')
134 self.cursor.execute('CREATE INDEX words_word_idx ON __words(_word)')
136 def add_actor_column(self):
137 # update existing tables to have the new actor column
138 tables = self.database_schema['tables']
139 for name in tables.keys():
140 self.cursor.execute('ALTER TABLE _%s add __actor '
141 'VARCHAR(255)'%name)
143 def __repr__(self):
144 return '<roundpsycopgsql 0x%x>' % id(self)
146 def sql_stringquote(self, value):
147 ''' psycopg.QuotedString returns a "buffer" object with the
148 single-quotes around it... '''
149 return str(psycopg.QuotedString(str(value)))[1:-1]
151 def sql_index_exists(self, table_name, index_name):
152 sql = 'select count(*) from pg_indexes where ' \
153 'tablename=%s and indexname=%s'%(self.arg, self.arg)
154 self.cursor.execute(sql, (table_name, index_name))
155 return self.cursor.fetchone()[0]
157 def create_class_table(self, spec):
158 sql = 'CREATE SEQUENCE _%s_ids'%spec.classname
159 if __debug__:
160 print >>hyperdb.DEBUG, 'create_class_table', (self, sql)
161 self.cursor.execute(sql)
163 return rdbms_common.Database.create_class_table(self, spec)
165 def drop_class_table(self, cn):
166 sql = 'drop table _%s'%cn
167 if __debug__:
168 print >>hyperdb.DEBUG, 'drop_class', (self, sql)
169 self.cursor.execute(sql)
171 sql = 'drop sequence _%s_ids'%cn
172 if __debug__:
173 print >>hyperdb.DEBUG, 'drop_class', (self, sql)
174 self.cursor.execute(sql)
176 def create_journal_table(self, spec):
177 cols = ',' . join(['"%s" VARCHAR(255)'%x
178 for x in 'nodeid date tag action params' . split()])
179 sql = 'CREATE TABLE "%s__journal" (%s)'%(spec.classname, cols)
180 if __debug__:
181 print >>hyperdb.DEBUG, 'create_journal_table', (self, sql)
182 self.cursor.execute(sql)
183 self.create_journal_table_indexes(spec)
185 def create_multilink_table(self, spec, ml):
186 sql = '''CREATE TABLE "%s_%s" (linkid VARCHAR(255),
187 nodeid VARCHAR(255))'''%(spec.classname, ml)
189 if __debug__:
190 print >>hyperdb.DEBUG, 'create_class', (self, sql)
192 self.cursor.execute(sql)
193 self.create_multilink_table_indexes(spec, ml)
195 def newid(self, classname):
196 sql = "select nextval('_%s_ids') from dual"%classname
197 if __debug__:
198 print >>hyperdb.DEBUG, 'setid', (self, sql)
199 self.cursor.execute(sql)
200 return self.cursor.fetchone()[0]
202 def setid(self, classname, setid):
203 sql = "select setval('_%s_ids', %s) from dual"%(classname, int(setid))
204 if __debug__:
205 print >>hyperdb.DEBUG, 'setid', (self, sql)
206 self.cursor.execute(sql)
209 class Class(rdbms_common.Class):
210 pass
211 class IssueClass(rdbms_common.IssueClass):
212 pass
213 class FileClass(rdbms_common.FileClass):
214 pass