Mercurial > p > roundup > code

comparison roundup/backends/back_postgresql.py @ 7723:8147f6deac9f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
fix(db): Make using pg_service work again. When I did the merge of schema support I broke pg_service.conf support by replacing get_database_name with db_schema_split. This commit fixes it. Also this commit returns the schema if one is specified in pg_service.conf. back_postgresql.py: Replace calls to db_schema_split() with get_database_schema_names() (new name for get_database_name()). Rename db_schema_split to _db_schema_split. It now returns a tuple (dbname, schema) rather than a list. It is used only by get_database_schema_names() which also returns tuples. get_database_schema_names() can also get schema info for the service (if present) as specified by pg_service.conf. Add get_database_user() to get the user from either RDBMS_USER or pg_service.conf. (User needed for creating schema, so not needed before schema patch. import re at the top of file and remove lower import. Remove some schema code from db_command as it's not needed. The database conection is done to either postgresql or template1 existing databases. This command never connects to the roundp specified db. test/test_postgresql.py: Reorganize top level imports, add import os. Replace import of db_schema_split with get_database_schema_names. Also replace calls to db_schema_split. Create new Opener for the service file. Set PGSERVICEFILE to point to test/pg_service.conf. Add three new classes to test Service: 1) using regular db 2) using schema within db 3) Unable to parse schema name from pg_service.conf. The last doesn't need a db. Number 1 and 2 reuse the tests in ROTest to verify db connectivity. test/pg_service.conf: three service connections for: db only, db and schema, and incorrectly specified schema test cases. doc/upgrading.txt: updated to current status. Included example schema definition in service file.
author John Rouillard <rouilj@ieee.org>
date Thu, 28 Dec 2023 15:13:42 -0500
parents 3071db43bfb6
children bbc99def147a
comparison
equal deleted inserted replaced
7722:7e2552cc0b62 7723:8147f6deac9f
46 del d['read_default_group'] 46 del d['read_default_group']
47 if 'read_default_file' in d: 47 if 'read_default_file' in d:
48 del d['read_default_file'] 48 del d['read_default_file']
49 return d 49 return d
50 50
51 def db_schema_split(database_name): 51 def _db_schema_split(database_name):
52 ''' Split database_name into database and schema parts''' 52 ''' Split database_name into database and schema parts'''
53 if '.' in database_name: 53 if '.' in database_name:
54 return database_name.split ('.') 54 return database_name.split ('.')
55 return [database_name, ''] 55 return (database_name, '')
56 56
57 def db_create(config): 57 def db_create(config):
58 """Clear all database contents and drop database itself""" 58 """Clear all database contents and drop database itself"""
59 db_name, schema_name = db_schema_split(config.RDBMS_NAME) 59 db_name, schema_name = get_database_schema_names(config)
60 if not schema_name: 60 if not schema_name:
61 command = "CREATE DATABASE \"%s\" WITH ENCODING='UNICODE'" % db_name 61 command = "CREATE DATABASE \"%s\" WITH ENCODING='UNICODE'" % db_name
62 if config.RDBMS_TEMPLATE: 62 if config.RDBMS_TEMPLATE:
63 command = command + " TEMPLATE=%s" % config.RDBMS_TEMPLATE 63 command = command + " TEMPLATE=%s" % config.RDBMS_TEMPLATE
64 logging.getLogger('roundup.hyperdb').info(command) 64 logging.getLogger('roundup.hyperdb').info(command)
65 db_command(config, command) 65 db_command(config, command)
66 else: 66 else:
67 command = "CREATE SCHEMA \"%s\" AUTHORIZATION \"%s\"" % (schema_name, config.RDBMS_USER) 67 command = "CREATE SCHEMA \"%s\" AUTHORIZATION \"%s\"" % (
68 schema_name, get_database_user_name(config))
68 logging.getLogger('roundup.hyperdb').info(command) 69 logging.getLogger('roundup.hyperdb').info(command)
69 db_command(config, command, db_name) 70 db_command(config, command, db_name)
70 71
71 def db_nuke(config): 72 def db_nuke(config):
72 """Drop the database (and all its contents) or the schema.""" 73 """Drop the database (and all its contents) or the schema."""
73 db_name, schema_name = db_schema_split(config.RDBMS_NAME) 74 db_name, schema_name = get_database_schema_names(config)
74 if not schema_name: 75 if not schema_name:
75 command = 'DROP DATABASE "%s"'% db_name 76 command = 'DROP DATABASE "%s"'% db_name
76 logging.getLogger('roundup.hyperdb').info(command) 77 logging.getLogger('roundup.hyperdb').info(command)
77 db_command(config, command) 78 db_command(config, command)
78 else: 79 else:
80 logging.getLogger('roundup.hyperdb').info(command) 81 logging.getLogger('roundup.hyperdb').info(command)
81 db_command(config, command, db_name) 82 db_command(config, command, db_name)
82 if os.path.exists(config.DATABASE): 83 if os.path.exists(config.DATABASE):
83 shutil.rmtree(config.DATABASE) 84 shutil.rmtree(config.DATABASE)
84 85
85 def get_database_name(config): 86 def get_database_schema_names(config):
86 '''Get database name using config.RDBMS_NAME or config.RDBMS_SERVICE. 87 '''Get database and schema names using config.RDBMS_NAME or service
87 88 defined by config.RDBMS_SERVICE.
88 If database specifed using RDBMS_SERVICE does not exist, 89
89 the error message is parsed for the database name. This 90 If database specifed using RDBMS_SERVICE does not exist, the
90 will fail if the error message changes. The alternative is 91 error message is parsed for the database name. This database
91 to try to find and parse the .pg_service .ini style file on 92 can then be created by calling code. Parsing will fail if the
92 unix/windows. This is less palatable. 93 error message changes. The alternative is to try to find and
93 94 parse the .pg_service .ini style file on unix/windows. This is
94 If the database specified using RDBMS_SERVICE does exist, (i.e. we 95 less palatable.
95 are doing a nuke operation), use psycopg.extensions.ConnectionInfo 96
96 to get the dbname. This requires psycopg2 > 2.8 from 2018. 97 If the database specified using RDBMS_SERVICE exists, (e.g. we
98 are doing a nuke operation), use
99 psycopg.extensions.ConnectionInfo to get the dbname. Also parse
100 the search_path options setting to get the schema. Only the
101 first element of the search_path is returned. This requires
102 psycopg2 > 2.8 from 2018.
97 ''' 103 '''
98 104
99 if config.RDBMS_NAME: 105 if config.RDBMS_NAME:
100 return config.RDBMS_NAME 106 return _db_schema_split(config.RDBMS_NAME)
101 107
102 template1 = connection_dict(config) 108 template1 = connection_dict(config)
103 try: 109 try:
104 conn = psycopg2.connect(**template1) 110 conn = psycopg2.connect(**template1)
105 except psycopg2.OperationalError as message: 111 except psycopg2.OperationalError as message:
106 import re
107 # extract db name from error: 112 # extract db name from error:
108 # 'connection to server at "127.0.0.1", port 5432 failed: \ 113 # 'connection to server at "127.0.0.1", port 5432 failed: \
109 # FATAL: database "rounduptest" does not exist\n' 114 # FATAL: database "rounduptest" does not exist\n'
110 # ugh. 115 # ugh.
111 # 116 #
118 search = re.search( 123 search = re.search(
119 r'FATAL:\s+database\s+"([^"\s]*)"\s+does\s+not\s+exist', 124 r'FATAL:\s+database\s+"([^"\s]*)"\s+does\s+not\s+exist',
120 message.args[0]) 125 message.args[0])
121 if search: 126 if search:
122 dbname = search.groups()[0] 127 dbname = search.groups()[0]
123 return dbname 128 # To use a schema, the db has to have been precreated.
129 # So return '' for schema if database does not exist.
130 return (dbname, '')
124 131
125 raise hyperdb.DatabaseError( 132 raise hyperdb.DatabaseError(
126 "Unable to determine database from service: %s" % message) 133 "Unable to determine database from service: %s" % message)
127 134
128 dbname = psycopg2.extensions.ConnectionInfo(conn).dbname 135 dbname = psycopg2.extensions.ConnectionInfo(conn).dbname
136 schema = ''
137 options = psycopg2.extensions.ConnectionInfo(conn).options
129 conn.close() 138 conn.close()
130 return dbname 139
140 # Assume schema is first word in the search_path spec.
141 # , (for multiple items in path) and whitespace (for another option)
142 # end the schema name.
143 m = re.search(r'search_path=([^,\s]*)', options)
144 if m:
145 schema = m.group(1)
146 if not schema:
147 raise ValueError('Unable to get schema for service: "%s" from options: "%s"' % (template1['service'], options))
148
149 return (dbname, schema)
150
151 def get_database_user_name(config):
152 '''Get database username using config.RDBMS_USER or return
153 user from connection created using config.RDBMS_SERVICE.
154
155 If the database specified using RDBMS_SERVICE does exist, (i.e. we
156 are doing a nuke operation), use psycopg.extensions.ConnectionInfo
157 to get the user. This requires psycopg2 > 2.8 from 2018.
158 '''
159 if config.RDBMS_USER:
160 return config.RDBMS_USER
161
162 template1 = connection_dict(config)
163 try:
164 conn = psycopg2.connect(**template1)
165 except psycopg2.OperationalError as message:
166 # extract db name from error:
167 # 'connection to server at "127.0.0.1", port 5432 failed: \
168 # FATAL: database "rounduptest" does not exist\n'
169 # ugh.
170 #
171 # Database name is any character sequence not including a " or
172 # whitespace. Arguably both are allowed by:
173 #
174 # https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
175 #
176 # with suitable quoting but ... really.
177 search = re.search(
178 r'FATAL:\s+database\s+"([^"\s]*)"\s+does\s+not\s+exist',
179 message.args[0])
180 if search:
181 dbname = search.groups()[0]
182 # To have a user, the db has to exist already.
183 # so return '' for user.
184 return ''
185
186 raise hyperdb.DatabaseError(
187 "Unable to determine database from service: %s" % message)
188
189 user = psycopg2.extensions.ConnectionInfo(conn).user
190 conn.close()
191
192 return user
131 193
132 def db_command(config, command, database='postgres'): 194 def db_command(config, command, database='postgres'):
133 '''Perform some sort of database-level command. Retry 10 times if we 195 '''Perform some sort of database-level command. Retry 10 times if we
134 fail by conflicting with another user. 196 fail by conflicting with another user.
135 197
136 Since PostgreSQL version 8.1 there is a database "postgres", 198 Since PostgreSQL version 8.1 there is a database "postgres",
137 before "template1" seems to have been used, so we fall back to it. 199 before "template1" seems to have been used, so we fall back to it.
138 Compare to issue2550543. 200 Compare to issue2550543.
139 ''' 201 '''
140 template1 = connection_dict(config, 'database') 202 template1 = connection_dict(config, 'database')
141 db_name, schema_name = db_schema_split(template1['database'])
142 template1['database'] = database 203 template1['database'] = database
143 204
144 try: 205 try:
145 conn = psycopg2.connect(**template1) 206 conn = psycopg2.connect(**template1)
146 except psycopg2.OperationalError as message: 207 except psycopg2.OperationalError as message:
147 if not schema_name: 208 if re.search(r'database ".+" does not exist', str(message)):
148 if re.search(r'database ".+" does not exist', str(message)): 209 return db_command(config, command, database='template1')
149 return db_command(config, command, database='template1')
150 raise hyperdb.DatabaseError(message) 210 raise hyperdb.DatabaseError(message)
151 211
152 conn.set_isolation_level(0) 212 conn.set_isolation_level(0)
153 cursor = conn.cursor() 213 cursor = conn.cursor()
154 try: 214 try:
184 244
185 245
186 def db_exists(config): 246 def db_exists(config):
187 """Check if database or schema already exists""" 247 """Check if database or schema already exists"""
188 db = connection_dict(config, 'database') 248 db = connection_dict(config, 'database')
189 db_name, schema_name = db_schema_split(db['database']) 249 db_name, schema_name = get_database_schema_names(config)
190 if schema_name: 250 if schema_name:
191 db['database'] = db_name 251 db['database'] = db_name
192 try: 252 try:
193 conn = psycopg2.connect(**db) 253 conn = psycopg2.connect(**db)
194 if not schema_name: 254 if not schema_name:
251 # used by some code to switch styles of query 311 # used by some code to switch styles of query
252 implements_intersect = 1 312 implements_intersect = 1
253 313
254 def sql_open_connection(self): 314 def sql_open_connection(self):
255 db = connection_dict(self.config, 'database') 315 db = connection_dict(self.config, 'database')
256 db_name, schema_name = db_schema_split (db['database']) 316 db_name, schema_name = get_database_schema_names(self.config)
257 if schema_name: 317 if schema_name:
258 db['database'] = db_name 318 db['database'] = db_name
259 319
260 # database option always present: log it if not null 320 # database option always present: log it if not null
261 if db['database']: 321 if db['database']:
Roundup Issue Tracker: http://roundup-tracker.org/