Mercurial > p > roundup > code

comparison roundup/backends/back_postgresql.py @ 6935:b5062cb5c2a2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
flake8 fixes
author John Rouillard <rouilj@ieee.org>
date Thu, 08 Sep 2022 13:41:26 -0400
parents b0dbc13a835a
children 5b41018617f2
comparison
equal deleted inserted replaced
6934:37e2e3897ddf 6935:b5062cb5c2a2
5 # disclaimer are retained in their original form. 5 # disclaimer are retained in their original form.
6 # 6 #
7 '''Postgresql backend via psycopg2 for Roundup.''' 7 '''Postgresql backend via psycopg2 for Roundup.'''
8 __docformat__ = 'restructuredtext' 8 __docformat__ = 'restructuredtext'
9 9
10 import os, shutil, time 10 import logging
11 import os
12 import shutil
13 import time
14
11 ISOLATION_LEVEL_READ_UNCOMMITTED = None 15 ISOLATION_LEVEL_READ_UNCOMMITTED = None
12 ISOLATION_LEVEL_READ_COMMITTED = None 16 ISOLATION_LEVEL_READ_COMMITTED = None
13 ISOLATION_LEVEL_REPEATABLE_READ = None 17 ISOLATION_LEVEL_REPEATABLE_READ = None
14 ISOLATION_LEVEL_SERIALIZABLE = None 18 ISOLATION_LEVEL_SERIALIZABLE = None
15 19
16 import psycopg2 20 import psycopg2 # noqa: E402
17 from psycopg2.extensions import QuotedString 21 from psycopg2 import ProgrammingError # noqa: E402
18 from psycopg2.extensions import ISOLATION_LEVEL_READ_UNCOMMITTED 22 from psycopg2.extensions import QuotedString # noqa: E402
19 from psycopg2.extensions import ISOLATION_LEVEL_READ_COMMITTED 23 from psycopg2.extensions import ISOLATION_LEVEL_READ_UNCOMMITTED # noqa: F401 E402
20 from psycopg2.extensions import ISOLATION_LEVEL_REPEATABLE_READ 24 from psycopg2.extensions import ISOLATION_LEVEL_READ_COMMITTED # noqa: E402
21 from psycopg2.extensions import ISOLATION_LEVEL_SERIALIZABLE 25 from psycopg2.extensions import ISOLATION_LEVEL_REPEATABLE_READ # noqa: E402
22 from psycopg2 import ProgrammingError 26 from psycopg2.extensions import ISOLATION_LEVEL_SERIALIZABLE # noqa: E402
23 from psycopg2.extensions import TransactionRollbackError 27 from psycopg2.extensions import TransactionRollbackError # noqa: F401 E402
24 28
25 import logging 29 from roundup import hyperdb # noqa: E402
26 30 from roundup.backends import rdbms_common # noqa: E402
27 from roundup import hyperdb, date 31 from roundup.backends import sessions_rdbms # noqa: E402
28 from roundup.backends import rdbms_common 32
29 from roundup.backends import sessions_rdbms 33 isolation_levels = {
30 34 'read uncommitted': ISOLATION_LEVEL_READ_COMMITTED,
31 isolation_levels = \ 35 'read committed': ISOLATION_LEVEL_READ_COMMITTED,
32 { 'read uncommitted': ISOLATION_LEVEL_READ_COMMITTED 36 'repeatable read': ISOLATION_LEVEL_REPEATABLE_READ,
33 , 'read committed': ISOLATION_LEVEL_READ_COMMITTED 37 'serializable': ISOLATION_LEVEL_SERIALIZABLE
34 , 'repeatable read': ISOLATION_LEVEL_REPEATABLE_READ 38 }
35 , 'serializable': ISOLATION_LEVEL_SERIALIZABLE 39
36 }
37 40
38 def connection_dict(config, dbnamestr=None): 41 def connection_dict(config, dbnamestr=None):
39 ''' read_default_group is MySQL-specific, ignore it ''' 42 ''' read_default_group is MySQL-specific, ignore it '''
40 d = rdbms_common.connection_dict(config, dbnamestr) 43 d = rdbms_common.connection_dict(config, dbnamestr)
41 if 'read_default_group' in d: 44 if 'read_default_group' in d:
42 del d['read_default_group'] 45 del d['read_default_group']
43 if 'read_default_file' in d: 46 if 'read_default_file' in d:
44 del d['read_default_file'] 47 del d['read_default_file']
45 return d 48 return d
46 49
50
47 def db_create(config): 51 def db_create(config):
48 """Clear all database contents and drop database itself""" 52 """Clear all database contents and drop database itself"""
49 command = "CREATE DATABASE \"%s\" WITH ENCODING='UNICODE'"%config.RDBMS_NAME 53 command = "CREATE DATABASE \"%s\" WITH ENCODING='UNICODE'" % config.RDBMS_NAME
50 if config.RDBMS_TEMPLATE: 54 if config.RDBMS_TEMPLATE:
51 command = command + " TEMPLATE=%s" % config.RDBMS_TEMPLATE 55 command = command + " TEMPLATE=%s" % config.RDBMS_TEMPLATE
52 logging.getLogger('roundup.hyperdb').info(command) 56 logging.getLogger('roundup.hyperdb').info(command)
53 db_command(config, command) 57 db_command(config, command)
54 58
59
55 def db_nuke(config): 60 def db_nuke(config):
56 """Clear all database contents and drop database itself""" 61 """Clear all database contents and drop database itself"""
57 command = 'DROP DATABASE "%s"'% config.RDBMS_NAME 62 command = 'DROP DATABASE "%s"' % config.RDBMS_NAME
58 logging.getLogger('roundup.hyperdb').info(command) 63 logging.getLogger('roundup.hyperdb').info(command)
59 db_command(config, command) 64 db_command(config, command)
60 65
61 if os.path.exists(config.DATABASE): 66 if os.path.exists(config.DATABASE):
62 shutil.rmtree(config.DATABASE) 67 shutil.rmtree(config.DATABASE)
68
63 69
64 def db_command(config, command, database='postgres'): 70 def db_command(config, command, database='postgres'):
65 '''Perform some sort of database-level command. Retry 10 times if we 71 '''Perform some sort of database-level command. Retry 10 times if we
66 fail by conflicting with another user. 72 fail by conflicting with another user.
67 73
68 Since PostgreSQL version 8.1 there is a database "postgres", 74 Since PostgreSQL version 8.1 there is a database "postgres",
69 before "template1" seems to have been used, so we fall back to it. 75 before "template1" seems to have been used, so we fall back to it.
70 Compare to issue2550543. 76 Compare to issue2550543.
71 ''' 77 '''
72 template1 = connection_dict(config) 78 template1 = connection_dict(config)
73 template1['database'] = database 79 template1['database'] = database
74 80
80 raise hyperdb.DatabaseError(message) 86 raise hyperdb.DatabaseError(message)
81 87
82 conn.set_isolation_level(0) 88 conn.set_isolation_level(0)
83 cursor = conn.cursor() 89 cursor = conn.cursor()
84 try: 90 try:
85 for n in range(10): 91 for _n in range(10):
86 if pg_command(cursor, command): 92 if pg_command(cursor, command):
87 return 93 return
88 finally: 94 finally:
89 conn.close() 95 conn.close()
90 raise RuntimeError('10 attempts to create database failed') 96 raise RuntimeError('10 attempts to create database failed')
97
91 98
92 def pg_command(cursor, command): 99 def pg_command(cursor, command):
93 '''Execute the postgresql command, which may be blocked by some other 100 '''Execute the postgresql command, which may be blocked by some other
94 user connecting to the database, and return a true value if it succeeds. 101 user connecting to the database, and return a true value if it succeeds.
95 102
106 ) 113 )
107 for msg in msgs: 114 for msg in msgs:
108 if msg in response: 115 if msg in response:
109 time.sleep(0.1) 116 time.sleep(0.1)
110 return 0 117 return 0
111 raise RuntimeError (response) 118 raise RuntimeError(response)
112 return 1 119 return 1
120
113 121
114 def db_exists(config): 122 def db_exists(config):
115 """Check if database already exists""" 123 """Check if database already exists"""
116 db = connection_dict(config, 'database') 124 db = connection_dict(config, 'database')
117 try: 125 try:
118 conn = psycopg2.connect(**db) 126 conn = psycopg2.connect(**db)
119 conn.close() 127 conn.close()
120 return 1 128 return 1
121 except: 129 except Exception:
122 return 0 130 return 0
131
123 132
124 class Sessions(sessions_rdbms.Sessions): 133 class Sessions(sessions_rdbms.Sessions):
125 def set(self, *args, **kwargs): 134 def set(self, *args, **kwargs):
126 try: 135 try:
127 sessions_rdbms.Sessions.set(self, *args, **kwargs) 136 sessions_rdbms.Sessions.set(self, *args, **kwargs)
132 # another client just updated, and we're running on 141 # another client just updated, and we're running on
133 # serializable isolation. 142 # serializable isolation.
134 # see http://www.postgresql.org/docs/7.4/interactive/transaction-iso.html 143 # see http://www.postgresql.org/docs/7.4/interactive/transaction-iso.html
135 self.db.rollback() 144 self.db.rollback()
136 145
146
137 class Database(rdbms_common.Database): 147 class Database(rdbms_common.Database):
138 """Postgres DB backend implementation 148 """Postgres DB backend implementation
139 149
140 attributes: 150 attributes:
141 dbtype: 151 dbtype:
152 implements_intersect = 1 162 implements_intersect = 1
153 163
154 def sql_open_connection(self): 164 def sql_open_connection(self):
155 db = connection_dict(self.config, 'database') 165 db = connection_dict(self.config, 'database')
156 logging.getLogger('roundup.hyperdb').info( 166 logging.getLogger('roundup.hyperdb').info(
157 'open database %r'%db['database']) 167 'open database %r' % db['database'])
158 try: 168 try:
159 conn = psycopg2.connect(**db) 169 conn = psycopg2.connect(**db)
160 except psycopg2.OperationalError as message: 170 except psycopg2.OperationalError as message:
161 raise hyperdb.DatabaseError(message) 171 raise hyperdb.DatabaseError(message)
162 172
163 cursor = conn.cursor() 173 cursor = conn.cursor()
164 if ISOLATION_LEVEL_REPEATABLE_READ is not None: 174 if ISOLATION_LEVEL_REPEATABLE_READ is not None:
165 lvl = isolation_levels [self.config.RDBMS_ISOLATION_LEVEL] 175 lvl = isolation_levels[self.config.RDBMS_ISOLATION_LEVEL]
166 conn.set_isolation_level(lvl) 176 conn.set_isolation_level(lvl)
167 177
168 return (conn, cursor) 178 return (conn, cursor)
169 179
170 def sql_new_cursor(self, name='default', conn=None, *args, **kw): 180 def sql_new_cursor(self, name='default', conn=None, *args, **kw):
263 # Convert all String properties to TEXT 273 # Convert all String properties to TEXT
264 self._convert_string_properties() 274 self._convert_string_properties()
265 275
266 # convert session / OTK *_time columns to REAL 276 # convert session / OTK *_time columns to REAL
267 for name in ('otk', 'session'): 277 for name in ('otk', 'session'):
268 self.sql('drop index %ss_key_idx'%name) 278 self.sql('drop index %ss_key_idx' % name)
269 self.sql('drop table %ss'%name) 279 self.sql('drop table %ss' % name)
270 self.sql('''CREATE TABLE %ss (%s_key VARCHAR(255), 280 self.sql('''CREATE TABLE %ss (%s_key VARCHAR(255),
271 %s_value VARCHAR(255), %s_time REAL)'''%(name, name, name, 281 %s_value VARCHAR(255), %s_time REAL)''' % (name, name,
272 name)) 282 name, name))
273 self.sql('CREATE INDEX %ss_key_idx ON %ss(%s_key)'%(name, name, 283 self.sql('CREATE INDEX %ss_key_idx ON %ss(%s_key)' % (name, name,
274 name)) 284 name))
275 285
276 def fix_version_3_tables(self): 286 def fix_version_3_tables(self):
277 rdbms_common.Database.fix_version_3_tables(self) 287 rdbms_common.Database.fix_version_3_tables(self)
278 self.sql('''CREATE INDEX words_both_idx ON public.__words 288 self.sql('''CREATE INDEX words_both_idx ON public.__words
279 USING btree (_word, _textid)''') 289 USING btree (_word, _textid)''')
280 290
281 def _add_fts_table(self): 291 def _add_fts_table(self):
282 self.sql('CREATE TABLE __fts (_class VARCHAR(255), ' 292 self.sql(
283 '_itemid VARCHAR(255), _prop VARCHAR(255), _tsv tsvector)' 293 'CREATE TABLE __fts (_class VARCHAR(255), '
294 '_itemid VARCHAR(255), _prop VARCHAR(255), _tsv tsvector)'
284 ) 295 )
285 296
286 self.sql('CREATE INDEX __fts_idx ON __fts USING GIN (_tsv)') 297 self.sql('CREATE INDEX __fts_idx ON __fts USING GIN (_tsv)')
287 298
288 def fix_version_6_tables(self): 299 def fix_version_6_tables(self):
306 317
307 def add_new_columns_v2(self): 318 def add_new_columns_v2(self):
308 # update existing tables to have the new actor column 319 # update existing tables to have the new actor column
309 tables = self.database_schema['tables'] 320 tables = self.database_schema['tables']
310 for name in tables: 321 for name in tables:
311 self.sql('ALTER TABLE _%s add __actor VARCHAR(255)'%name) 322 self.sql('ALTER TABLE _%s add __actor VARCHAR(255)' % name)
312 323
313 def __repr__(self): 324 def __repr__(self):
314 return '<roundpsycopgsql 0x%x>' % id(self) 325 return '<roundpsycopgsql 0x%x>' % id(self)
315 326
316 def sql_stringquote(self, value): 327 def sql_stringquote(self, value):
318 single-quotes around it... ''' 329 single-quotes around it... '''
319 return str(QuotedString(str(value)))[1:-1] 330 return str(QuotedString(str(value)))[1:-1]
320 331
321 def sql_index_exists(self, table_name, index_name): 332 def sql_index_exists(self, table_name, index_name):
322 sql = 'select count(*) from pg_indexes where ' \ 333 sql = 'select count(*) from pg_indexes where ' \
323 'tablename=%s and indexname=%s'%(self.arg, self.arg) 334 'tablename=%s and indexname=%s' % (self.arg, self.arg)
324 self.sql(sql, (table_name, index_name)) 335 self.sql(sql, (table_name, index_name))
325 return self.cursor.fetchone()[0] 336 return self.cursor.fetchone()[0]
326 337
327 def create_class_table(self, spec, create_sequence=1): 338 def create_class_table(self, spec, create_sequence=1):
328 if create_sequence: 339 if create_sequence:
329 sql = 'CREATE SEQUENCE _%s_ids'%spec.classname 340 sql = 'CREATE SEQUENCE _%s_ids' % spec.classname
330 self.sql(sql) 341 self.sql(sql)
331 342
332 return rdbms_common.Database.create_class_table(self, spec) 343 return rdbms_common.Database.create_class_table(self, spec)
333 344
334 def drop_class_table(self, cn): 345 def drop_class_table(self, cn):
335 sql = 'drop table _%s'%cn 346 sql = 'drop table _%s' % cn
336 self.sql(sql) 347 self.sql(sql)
337 348
338 sql = 'drop sequence _%s_ids'%cn 349 sql = 'drop sequence _%s_ids' % cn
339 self.sql(sql) 350 self.sql(sql)
340 351
341 def newid(self, classname): 352 def newid(self, classname):
342 sql = "select nextval('_%s_ids') from dual"%classname 353 sql = "select nextval('_%s_ids') from dual" % classname
343 self.sql(sql) 354 self.sql(sql)
344 return str(self.cursor.fetchone()[0]) 355 return str(self.cursor.fetchone()[0])
345 356
346 def setid(self, classname, setid): 357 def setid(self, classname, setid):
347 sql = "select setval('_%s_ids', %s) from dual"%(classname, int(setid)) 358 sql = "select setval('_%s_ids', %s) from dual" % (classname,
359 int(setid))
348 self.sql(sql) 360 self.sql(sql)
349 361
350 def clear(self): 362 def clear(self):
351 rdbms_common.Database.clear(self) 363 rdbms_common.Database.clear(self)
352 364
353 # reset the sequences 365 # reset the sequences
354 for cn in self.classes: 366 for cn in self.classes:
355 self.cursor.execute('DROP SEQUENCE _%s_ids'%cn) 367 self.cursor.execute('DROP SEQUENCE _%s_ids' % cn)
356 self.cursor.execute('CREATE SEQUENCE _%s_ids'%cn) 368 self.cursor.execute('CREATE SEQUENCE _%s_ids' % cn)
369
357 370
358 class PostgresqlClass: 371 class PostgresqlClass:
359 order_by_null_values = '(%s is not NULL)' 372 order_by_null_values = '(%s is not NULL)'
360 case_insensitive_like = 'ILIKE' 373 case_insensitive_like = 'ILIKE'
361 374
375
362 class Class(PostgresqlClass, rdbms_common.Class): 376 class Class(PostgresqlClass, rdbms_common.Class):
363 pass 377 pass
378
379
364 class IssueClass(PostgresqlClass, rdbms_common.IssueClass): 380 class IssueClass(PostgresqlClass, rdbms_common.IssueClass):
365 pass 381 pass
382
383
366 class FileClass(PostgresqlClass, rdbms_common.FileClass): 384 class FileClass(PostgresqlClass, rdbms_common.FileClass):
367 pass 385 pass
368 386
369 # vim: set et sts=4 sw=4 : 387 # vim: set et sts=4 sw=4 :
Roundup Issue Tracker: http://roundup-tracker.org/