Mercurial > p > roundup > code

comparison roundup/backends/back_postgresql.py @ 7719:3071db43bfb6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
feat: issue2550852 - support using a specified PostgreSQL db schema Finally after 7 years this is closed. roundup/backends/back_postgresql.py: Support use of schema when specified in RDBMS_NAME. Stuart McGraws code is finally merged 8-). test/test_postgresql.py, test/conftest.py: Run all postgresql tests in the schema db as well. Also make sure that db_nuke raises an error when trying to delete the schema test database. Conftest defines pg_schema mark that can be used to exclude schema tests with pytest -m "not pg_schema". roundup/configuration.py: change doc on RDBMS_NAME to include db.schema form. .travis.yml, .github/workflows/ci-test.yml: create schema test db; add user for testing with schema; grant new user create privs for schema. doc/installation.txt: Reference to roundup-admin init deleting schema added. doc/mysql.txt doc/postgresql.txt: New documentation on psql/mysql commands to set up a production db. doc/upgrading.txt: mention schema support, also document service setting for selecting connection from pg_service.conf. doc/reference.txt: update config.ini documentation for RDBMS_NAME.
author John Rouillard <rouilj@ieee.org>
date Wed, 27 Dec 2023 22:52:14 -0500
parents 3da452f4a3ac
children 8147f6deac9f
comparison
equal deleted inserted replaced
7718:3da452f4a3ac 7719:3071db43bfb6
7 '''Postgresql backend via psycopg2 for Roundup.''' 7 '''Postgresql backend via psycopg2 for Roundup.'''
8 __docformat__ = 'restructuredtext' 8 __docformat__ = 'restructuredtext'
9 9
10 import logging 10 import logging
11 import os 11 import os
12 import re
12 import shutil 13 import shutil
13 import time 14 import time
14 15
15 ISOLATION_LEVEL_READ_UNCOMMITTED = None 16 ISOLATION_LEVEL_READ_UNCOMMITTED = None
16 ISOLATION_LEVEL_READ_COMMITTED = None 17 ISOLATION_LEVEL_READ_COMMITTED = None
45 del d['read_default_group'] 46 del d['read_default_group']
46 if 'read_default_file' in d: 47 if 'read_default_file' in d:
47 del d['read_default_file'] 48 del d['read_default_file']
48 return d 49 return d
49 50
51 def db_schema_split(database_name):
52 ''' Split database_name into database and schema parts'''
53 if '.' in database_name:
54 return database_name.split ('.')
55 return [database_name, '']
50 56
51 def db_create(config): 57 def db_create(config):
52 """Clear all database contents and drop database itself""" 58 """Clear all database contents and drop database itself"""
53 command = ("CREATE DATABASE \"%s\" WITH ENCODING='UNICODE'" % 59 db_name, schema_name = db_schema_split(config.RDBMS_NAME)
54 get_database_name(config)) 60 if not schema_name:
55 if config.RDBMS_TEMPLATE: 61 command = "CREATE DATABASE \"%s\" WITH ENCODING='UNICODE'" % db_name
56 command = command + " TEMPLATE=%s" % config.RDBMS_TEMPLATE 62 if config.RDBMS_TEMPLATE:
57 logging.getLogger('roundup.hyperdb').info(command) 63 command = command + " TEMPLATE=%s" % config.RDBMS_TEMPLATE
58 db_command(config, command) 64 logging.getLogger('roundup.hyperdb').info(command)
59 65 db_command(config, command)
66 else:
67 command = "CREATE SCHEMA \"%s\" AUTHORIZATION \"%s\"" % (schema_name, config.RDBMS_USER)
68 logging.getLogger('roundup.hyperdb').info(command)
69 db_command(config, command, db_name)
60 70
61 def db_nuke(config): 71 def db_nuke(config):
62 """Clear all database contents and drop database itself""" 72 """Drop the database (and all its contents) or the schema."""
63 command = 'DROP DATABASE "%s"' % get_database_name(config) 73 db_name, schema_name = db_schema_split(config.RDBMS_NAME)
64 74 if not schema_name:
65 logging.getLogger('roundup.hyperdb').info(command) 75 command = 'DROP DATABASE "%s"'% db_name
66 db_command(config, command) 76 logging.getLogger('roundup.hyperdb').info(command)
67 77 db_command(config, command)
78 else:
79 command = 'DROP SCHEMA "%s" CASCADE' % schema_name
80 logging.getLogger('roundup.hyperdb').info(command)
81 db_command(config, command, db_name)
68 if os.path.exists(config.DATABASE): 82 if os.path.exists(config.DATABASE):
69 shutil.rmtree(config.DATABASE) 83 shutil.rmtree(config.DATABASE)
70
71 84
72 def get_database_name(config): 85 def get_database_name(config):
73 '''Get database name using config.RDBMS_NAME or config.RDBMS_SERVICE. 86 '''Get database name using config.RDBMS_NAME or config.RDBMS_SERVICE.
74 87
75 If database specifed using RDBMS_SERVICE does not exist, 88 If database specifed using RDBMS_SERVICE does not exist,
122 135
123 Since PostgreSQL version 8.1 there is a database "postgres", 136 Since PostgreSQL version 8.1 there is a database "postgres",
124 before "template1" seems to have been used, so we fall back to it. 137 before "template1" seems to have been used, so we fall back to it.
125 Compare to issue2550543. 138 Compare to issue2550543.
126 ''' 139 '''
127 template1 = connection_dict(config) 140 template1 = connection_dict(config, 'database')
141 db_name, schema_name = db_schema_split(template1['database'])
128 template1['database'] = database 142 template1['database'] = database
129 143
130 try: 144 try:
131 conn = psycopg2.connect(**template1) 145 conn = psycopg2.connect(**template1)
132 except psycopg2.OperationalError as message: 146 except psycopg2.OperationalError as message:
133 if str(message).find('database "postgres" does not exist') >= 0: 147 if not schema_name:
134 return db_command(config, command, database='template1') 148 if re.search(r'database ".+" does not exist', str(message)):
149 return db_command(config, command, database='template1')
135 raise hyperdb.DatabaseError(message) 150 raise hyperdb.DatabaseError(message)
136 151
137 conn.set_isolation_level(0) 152 conn.set_isolation_level(0)
138 cursor = conn.cursor() 153 cursor = conn.cursor()
139 try: 154 try:
140 for _n in range(10): 155 for _n in range(10):
141 if pg_command(cursor, command): 156 if pg_command(cursor, command):
142 return 157 return
143 finally: 158 finally:
144 conn.close() 159 conn.close()
145 raise RuntimeError('10 attempts to create database failed when running: %s' % command) 160 raise RuntimeError('10 attempts to create database or schema failed when running: %s' % command)
146 161
147 162
148 def pg_command(cursor, command): 163 def pg_command(cursor, command, args=()):
149 '''Execute the postgresql command, which may be blocked by some other 164 '''Execute the postgresql command, which may be blocked by some other
150 user connecting to the database, and return a true value if it succeeds. 165 user connecting to the database, and return a true value if it succeeds.
151 166
152 If there is a concurrent update, retry the command. 167 If there is a concurrent update, retry the command.
153 ''' 168 '''
154 try: 169 try:
155 cursor.execute(command) 170 cursor.execute(command, args)
156 except psycopg2.DatabaseError as err: 171 except psycopg2.DatabaseError as err:
157 response = str(err).split('\n')[0] 172 response = str(err).split('\n')[0]
158 if "FATAL" not in response: 173 if "FATAL" not in response:
159 msgs = ( 174 msgs = (
160 'is being accessed by other users', 175 'is being accessed by other users',
162 ) 177 )
163 for msg in msgs: 178 for msg in msgs:
164 if msg in response: 179 if msg in response:
165 time.sleep(0.1) 180 time.sleep(0.1)
166 return 0 181 return 0
167 raise RuntimeError(response) 182 raise RuntimeError(response, command, args)
168 return 1 183 return 1
169 184
170 185
171 def db_exists(config): 186 def db_exists(config):
172 """Check if database already exists""" 187 """Check if database or schema already exists"""
173 db = connection_dict(config, 'database') 188 db = connection_dict(config, 'database')
189 db_name, schema_name = db_schema_split(db['database'])
190 if schema_name:
191 db['database'] = db_name
174 try: 192 try:
175 conn = psycopg2.connect(**db) 193 conn = psycopg2.connect(**db)
176 conn.close() 194 if not schema_name:
177 return 1 195 conn.close()
196 return 1
178 except Exception: 197 except Exception:
179 return 0 198 return 0
199 # <schema_name> will have a non-false value here; otherwise one
200 # of the above returns would have returned.
201 # Get a count of the number of schemas named <schema_name> (either 0 or 1).
202 command = "SELECT COUNT(*) FROM information_schema.schemata WHERE schema_name = %s"
203 cursor = conn.cursor()
204 pg_command(cursor, command, (schema_name,))
205 count = cursor.fetchall()[0][0]
206 conn.close()
207 return count # 'count' will be 0 or 1.
180 208
181 209
182 class Sessions(sessions_rdbms.Sessions): 210 class Sessions(sessions_rdbms.Sessions):
183 def set(self, *args, **kwargs): 211 def set(self, *args, **kwargs):
184 try: 212 try:
223 # used by some code to switch styles of query 251 # used by some code to switch styles of query
224 implements_intersect = 1 252 implements_intersect = 1
225 253
226 def sql_open_connection(self): 254 def sql_open_connection(self):
227 db = connection_dict(self.config, 'database') 255 db = connection_dict(self.config, 'database')
256 db_name, schema_name = db_schema_split (db['database'])
257 if schema_name:
258 db['database'] = db_name
259
228 # database option always present: log it if not null 260 # database option always present: log it if not null
229 if db['database']: 261 if db['database']:
230 logging.getLogger('roundup.hyperdb').info( 262 logging.getLogger('roundup.hyperdb').info(
231 'open database %r' % db['database']) 263 'open database %r' % db['database'])
232 if 'service' in db: # only log if used 264 if 'service' in db: # only log if used
240 cursor = conn.cursor() 272 cursor = conn.cursor()
241 if ISOLATION_LEVEL_REPEATABLE_READ is not None: 273 if ISOLATION_LEVEL_REPEATABLE_READ is not None:
242 lvl = isolation_levels[self.config.RDBMS_ISOLATION_LEVEL] 274 lvl = isolation_levels[self.config.RDBMS_ISOLATION_LEVEL]
243 conn.set_isolation_level(lvl) 275 conn.set_isolation_level(lvl)
244 276
277 if schema_name:
278 self.sql ('SET search_path TO %s' % schema_name, cursor=cursor)
279 # Commit is required so that a subsequent rollback
280 # will not also rollback the search_path change.
281 self.sql ('COMMIT', cursor=cursor)
245 return (conn, cursor) 282 return (conn, cursor)
246 283
247 def sql_new_cursor(self, name='default', conn=None, *args, **kw): 284 def sql_new_cursor(self, name='default', conn=None, *args, **kw):
248 """ Create new cursor, this may need additional parameters for 285 """ Create new cursor, this may need additional parameters for
249 performance optimization for different backends. 286 performance optimization for different backends.
Roundup Issue Tracker: http://roundup-tracker.org/