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