Mercurial > p > roundup > code

comparison roundup/backends/back_postgresql.py @ 2514:091711fb2f8c

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Initial logging integration: replace all debug prints with logging calls... ...clean up and replace some with info() logs.
author Richard Jones <richard@users.sourceforge.net>
date Fri, 02 Jul 2004 05:22:09 +0000
parents 74474ec41050
children a9e1fff1e793
comparison
equal deleted inserted replaced
2512:f5542d92307a 2514:091711fb2f8c
14 from roundup import hyperdb, date 14 from roundup import hyperdb, date
15 from roundup.backends import rdbms_common 15 from roundup.backends import rdbms_common
16 16
17 def db_create(config): 17 def db_create(config):
18 """Clear all database contents and drop database itself""" 18 """Clear all database contents and drop database itself"""
19 if __debug__:
20 print >> hyperdb.DEBUG, '+++ create database +++'
21 command = 'CREATE DATABASE %s'%config.POSTGRESQL_DATABASE['database'] 19 command = 'CREATE DATABASE %s'%config.POSTGRESQL_DATABASE['database']
20 config.logging.getLogger('hyperdb').info(command)
22 db_command(config, command) 21 db_command(config, command)
23 22
24 def db_nuke(config, fail_ok=0): 23 def db_nuke(config, fail_ok=0):
25 """Clear all database contents and drop database itself""" 24 """Clear all database contents and drop database itself"""
26 if __debug__:
27 print >> hyperdb.DEBUG, '+++ nuke database +++'
28 command = 'DROP DATABASE %s'% config.POSTGRESQL_DATABASE['database'] 25 command = 'DROP DATABASE %s'% config.POSTGRESQL_DATABASE['database']
26 config.logging.getLogger('hyperdb').info(command)
29 db_command(config, command) 27 db_command(config, command)
30 28
31 if os.path.exists(config.DATABASE): 29 if os.path.exists(config.DATABASE):
32 shutil.rmtree(config.DATABASE) 30 shutil.rmtree(config.DATABASE)
33 31
64 if response.find('FATAL') != -1: 62 if response.find('FATAL') != -1:
65 raise RuntimeError, response 63 raise RuntimeError, response
66 elif response.find('ERROR') != -1: 64 elif response.find('ERROR') != -1:
67 if response.find('is being accessed by other users') == -1: 65 if response.find('is being accessed by other users') == -1:
68 raise RuntimeError, response 66 raise RuntimeError, response
69 if __debug__:
70 print >> hyperdb.DEBUG, '+++ SLEEPING +++'
71 time.sleep(1) 67 time.sleep(1)
72 return 0 68 return 0
73 return 1 69 return 1
74 70
75 def db_exists(config): 71 def db_exists(config):
76 """Check if database already exists""" 72 """Check if database already exists"""
77 db = getattr(config, 'POSTGRESQL_DATABASE') 73 db = getattr(config, 'POSTGRESQL_DATABASE')
78 try: 74 try:
79 conn = psycopg.connect(**db) 75 conn = psycopg.connect(**db)
80 conn.close() 76 conn.close()
81 if __debug__:
82 print >> hyperdb.DEBUG, '+++ database exists +++'
83 return 1 77 return 1
84 except: 78 except:
85 if __debug__:
86 print >> hyperdb.DEBUG, '+++ no database +++'
87 return 0 79 return 0
88 80
89 class Database(rdbms_common.Database): 81 class Database(rdbms_common.Database):
90 arg = '%s' 82 arg = '%s'
91 83
92 def sql_open_connection(self): 84 def sql_open_connection(self):
93 db = getattr(self.config, 'POSTGRESQL_DATABASE') 85 db = getattr(self.config, 'POSTGRESQL_DATABASE')
86 self.config.logging.getLogger('hyperdb').info('open database %r'%db)
94 try: 87 try:
95 conn = psycopg.connect(**db) 88 conn = psycopg.connect(**db)
96 except psycopg.OperationalError, message: 89 except psycopg.OperationalError, message:
97 raise hyperdb.DatabaseError, message 90 raise hyperdb.DatabaseError, message
98 91
101 return (conn, cursor) 94 return (conn, cursor)
102 95
103 def open_connection(self): 96 def open_connection(self):
104 if not db_exists(self.config): 97 if not db_exists(self.config):
105 db_create(self.config) 98 db_create(self.config)
106
107 if __debug__:
108 print >>hyperdb.DEBUG, '+++ open database connection +++'
109 99
110 self.conn, self.cursor = self.sql_open_connection() 100 self.conn, self.cursor = self.sql_open_connection()
111 101
112 try: 102 try:
113 self.load_dbschema() 103 self.load_dbschema()
119 self.sql("insert into dual values (1)") 109 self.sql("insert into dual values (1)")
120 self.create_version_2_tables() 110 self.create_version_2_tables()
121 111
122 def create_version_2_tables(self): 112 def create_version_2_tables(self):
123 # OTK store 113 # OTK store
124 self.cursor.execute('''CREATE TABLE otks (otk_key VARCHAR(255), 114 self.sql('''CREATE TABLE otks (otk_key VARCHAR(255),
125 otk_value VARCHAR(255), otk_time REAL)''') 115 otk_value VARCHAR(255), otk_time REAL)''')
126 self.cursor.execute('CREATE INDEX otks_key_idx ON otks(otk_key)') 116 self.sql('CREATE INDEX otks_key_idx ON otks(otk_key)')
127 117
128 # Sessions store 118 # Sessions store
129 self.cursor.execute('''CREATE TABLE sessions ( 119 self.sql('''CREATE TABLE sessions (
130 session_key VARCHAR(255), session_time REAL, 120 session_key VARCHAR(255), session_time REAL,
131 session_value VARCHAR(255))''') 121 session_value VARCHAR(255))''')
132 self.cursor.execute('''CREATE INDEX sessions_key_idx ON 122 self.sql('''CREATE INDEX sessions_key_idx ON
133 sessions(session_key)''') 123 sessions(session_key)''')
134 124
135 # full-text indexing store 125 # full-text indexing store
136 self.cursor.execute('CREATE SEQUENCE ___textids_ids') 126 self.sql('CREATE SEQUENCE ___textids_ids')
137 self.cursor.execute('''CREATE TABLE __textids ( 127 self.sql('''CREATE TABLE __textids (
138 _textid integer primary key, _class VARCHAR(255), 128 _textid integer primary key, _class VARCHAR(255),
139 _itemid VARCHAR(255), _prop VARCHAR(255))''') 129 _itemid VARCHAR(255), _prop VARCHAR(255))''')
140 self.cursor.execute('''CREATE TABLE __words (_word VARCHAR(30), 130 self.sql('''CREATE TABLE __words (_word VARCHAR(30),
141 _textid integer)''') 131 _textid integer)''')
142 self.cursor.execute('CREATE INDEX words_word_idx ON __words(_word)') 132 self.sql('CREATE INDEX words_word_idx ON __words(_word)')
143 133
144 def fix_version_2_tables(self): 134 def fix_version_2_tables(self):
145 # Convert journal date column to TIMESTAMP, params column to TEXT 135 # Convert journal date column to TIMESTAMP, params column to TEXT
146 self._convert_journal_tables() 136 self._convert_journal_tables()
147 137
148 # Convert all String properties to TEXT 138 # Convert all String properties to TEXT
149 self._convert_string_properties() 139 self._convert_string_properties()
150 140
151 # convert session / OTK *_time columns to REAL 141 # convert session / OTK *_time columns to REAL
152 c = self.cursor
153 for name in ('otk', 'session'): 142 for name in ('otk', 'session'):
154 c.execute('drop index %ss_key_idx'%name) 143 self.sql('drop index %ss_key_idx'%name)
155 c.execute('drop table %ss'%name) 144 self.sql('drop table %ss'%name)
156 c.execute('''CREATE TABLE %ss (%s_key VARCHAR(255), 145 self.sql('''CREATE TABLE %ss (%s_key VARCHAR(255),
157 %s_value VARCHAR(255), %s_time REAL)'''%(name, name, name, 146 %s_value VARCHAR(255), %s_time REAL)'''%(name, name, name,
158 name)) 147 name))
159 c.execute('CREATE INDEX %ss_key_idx ON %ss(%s_key)'%(name, name, 148 self.sql('CREATE INDEX %ss_key_idx ON %ss(%s_key)'%(name, name,
160 name)) 149 name))
161 150
162 def add_actor_column(self): 151 def add_actor_column(self):
163 # update existing tables to have the new actor column 152 # update existing tables to have the new actor column
164 tables = self.database_schema['tables'] 153 tables = self.database_schema['tables']
165 for name in tables.keys(): 154 for name in tables.keys():
166 self.cursor.execute('ALTER TABLE _%s add __actor ' 155 self.sql('ALTER TABLE _%s add __actor VARCHAR(255)'%name)
167 'VARCHAR(255)'%name)
168 156
169 def __repr__(self): 157 def __repr__(self):
170 return '<roundpsycopgsql 0x%x>' % id(self) 158 return '<roundpsycopgsql 0x%x>' % id(self)
171 159
172 def sql_stringquote(self, value): 160 def sql_stringquote(self, value):
175 return str(psycopg.QuotedString(str(value)))[1:-1] 163 return str(psycopg.QuotedString(str(value)))[1:-1]
176 164
177 def sql_index_exists(self, table_name, index_name): 165 def sql_index_exists(self, table_name, index_name):
178 sql = 'select count(*) from pg_indexes where ' \ 166 sql = 'select count(*) from pg_indexes where ' \
179 'tablename=%s and indexname=%s'%(self.arg, self.arg) 167 'tablename=%s and indexname=%s'%(self.arg, self.arg)
180 self.cursor.execute(sql, (table_name, index_name)) 168 self.sql(sql, (table_name, index_name))
181 return self.cursor.fetchone()[0] 169 return self.cursor.fetchone()[0]
182 170
183 def create_class_table(self, spec, create_sequence=1): 171 def create_class_table(self, spec, create_sequence=1):
184 if create_sequence: 172 if create_sequence:
185 sql = 'CREATE SEQUENCE _%s_ids'%spec.classname 173 sql = 'CREATE SEQUENCE _%s_ids'%spec.classname
186 if __debug__: 174 self.sql(sql)
187 print >>hyperdb.DEBUG, 'create_class_table', (self, sql)
188 self.cursor.execute(sql)
189 175
190 return rdbms_common.Database.create_class_table(self, spec) 176 return rdbms_common.Database.create_class_table(self, spec)
191 177
192 def drop_class_table(self, cn): 178 def drop_class_table(self, cn):
193 sql = 'drop table _%s'%cn 179 sql = 'drop table _%s'%cn
194 if __debug__: 180 self.sql(sql)
195 print >>hyperdb.DEBUG, 'drop_class', (self, sql)
196 self.cursor.execute(sql)
197 181
198 sql = 'drop sequence _%s_ids'%cn 182 sql = 'drop sequence _%s_ids'%cn
199 if __debug__: 183 self.sql(sql)
200 print >>hyperdb.DEBUG, 'drop_class', (self, sql)
201 self.cursor.execute(sql)
202 184
203 def newid(self, classname): 185 def newid(self, classname):
204 sql = "select nextval('_%s_ids') from dual"%classname 186 sql = "select nextval('_%s_ids') from dual"%classname
205 if __debug__: 187 self.sql(sql)
206 print >>hyperdb.DEBUG, 'setid', (self, sql)
207 self.cursor.execute(sql)
208 return self.cursor.fetchone()[0] 188 return self.cursor.fetchone()[0]
209 189
210 def setid(self, classname, setid): 190 def setid(self, classname, setid):
211 sql = "select setval('_%s_ids', %s) from dual"%(classname, int(setid)) 191 sql = "select setval('_%s_ids', %s) from dual"%(classname, int(setid))
212 if __debug__: 192 self.sql(sql)
213 print >>hyperdb.DEBUG, 'setid', (self, sql)
214 self.cursor.execute(sql)
215 193
216 194
217 class Class(rdbms_common.Class): 195 class Class(rdbms_common.Class):
218 pass 196 pass
219 class IssueClass(rdbms_common.IssueClass): 197 class IssueClass(rdbms_common.IssueClass):
Roundup Issue Tracker: http://roundup-tracker.org/