Mercurial > p > roundup > code

annotate 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
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
1 # Copyright (c) 2003 Martynas Sklyzmantas, Andrey Lebedev <andrey@micro.lt>
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
2 #
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
3 # This module is free software, and you may redistribute it and/or modify
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
4 # under the same terms as Python, so long as this copyright message and
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
5 # disclaimer are retained in their original form.
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
6 #
5751
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
7 '''Postgresql backend via psycopg2 for Roundup.'''
2005
fc52d57c6c3e documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents: 1920
diff changeset
8 __docformat__ = 'restructuredtext'
fc52d57c6c3e documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents: 1920
diff changeset
9
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
10 import logging
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
11 import os
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
12 import re
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
13 import shutil
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
14 import time
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
15
4887
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
16 ISOLATION_LEVEL_READ_UNCOMMITTED = None
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
17 ISOLATION_LEVEL_READ_COMMITTED = None
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
18 ISOLATION_LEVEL_REPEATABLE_READ = None
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
19 ISOLATION_LEVEL_SERIALIZABLE = None
5118
57452bc6d989 issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents: 5096
diff changeset
20
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
21 import psycopg2 # noqa: E402
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
22 from psycopg2 import ProgrammingError # noqa: E402
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
23 from psycopg2.extensions import QuotedString # noqa: E402
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
24 from psycopg2.extensions import ISOLATION_LEVEL_READ_UNCOMMITTED # noqa: F401 E402
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
25 from psycopg2.extensions import ISOLATION_LEVEL_READ_COMMITTED # noqa: E402
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
26 from psycopg2.extensions import ISOLATION_LEVEL_REPEATABLE_READ # noqa: E402
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
27 from psycopg2.extensions import ISOLATION_LEVEL_SERIALIZABLE # noqa: E402
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
28 from psycopg2.extensions import TransactionRollbackError # noqa: F401 E402
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
29
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
30 from roundup import hyperdb # noqa: E402
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
31 from roundup.backends import rdbms_common # noqa: E402
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
32 from roundup.backends import sessions_rdbms # noqa: E402
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
33
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
34 isolation_levels = {
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
35 'read uncommitted': ISOLATION_LEVEL_READ_COMMITTED,
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
36 'read committed': ISOLATION_LEVEL_READ_COMMITTED,
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
37 'repeatable read': ISOLATION_LEVEL_REPEATABLE_READ,
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
38 'serializable': ISOLATION_LEVEL_SERIALIZABLE
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
39 }
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
40
4887
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
41
3099
519b92df37dc handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents: 3088
diff changeset
42 def connection_dict(config, dbnamestr=None):
519b92df37dc handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents: 3088
diff changeset
43 ''' read_default_group is MySQL-specific, ignore it '''
519b92df37dc handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents: 3088
diff changeset
44 d = rdbms_common.connection_dict(config, dbnamestr)
4357
13b3155869e0 Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents: 4239
diff changeset
45 if 'read_default_group' in d:
3099
519b92df37dc handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents: 3088
diff changeset
46 del d['read_default_group']
4357
13b3155869e0 Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents: 4239
diff changeset
47 if 'read_default_file' in d:
3099
519b92df37dc handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents: 3088
diff changeset
48 del d['read_default_file']
519b92df37dc handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents: 3088
diff changeset
49 return d
519b92df37dc handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents: 3088
diff changeset
50
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
51 def db_schema_split(database_name):
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
52 ''' Split database_name into database and schema parts'''
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
53 if '.' in database_name:
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
54 return database_name.split ('.')
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
55 return [database_name, '']
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
56
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
57 def db_create(config):
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
58 """Clear all database contents and drop database itself"""
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
59 db_name, schema_name = db_schema_split(config.RDBMS_NAME)
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
60 if not schema_name:
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
61 command = "CREATE DATABASE \"%s\" WITH ENCODING='UNICODE'" % db_name
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
62 if config.RDBMS_TEMPLATE:
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
63 command = command + " TEMPLATE=%s" % config.RDBMS_TEMPLATE
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
64 logging.getLogger('roundup.hyperdb').info(command)
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
65 db_command(config, command)
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
66 else:
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
67 command = "CREATE SCHEMA \"%s\" AUTHORIZATION \"%s\"" % (schema_name, config.RDBMS_USER)
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
68 logging.getLogger('roundup.hyperdb').info(command)
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
69 db_command(config, command, db_name)
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
70
5319
62de601bdf6f Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5248
diff changeset
71 def db_nuke(config):
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
72 """Drop the database (and all its contents) or the schema."""
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
73 db_name, schema_name = db_schema_split(config.RDBMS_NAME)
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
74 if not schema_name:
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
75 command = 'DROP DATABASE "%s"'% db_name
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
76 logging.getLogger('roundup.hyperdb').info(command)
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
77 db_command(config, command)
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
78 else:
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
79 command = 'DROP SCHEMA "%s" CASCADE' % schema_name
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
80 logging.getLogger('roundup.hyperdb').info(command)
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
81 db_command(config, command, db_name)
2256
0b198ed096af fixes for py2.1 (booleans, sigh)
Richard Jones <richard@users.sourceforge.net>
parents: 2243
diff changeset
82 if os.path.exists(config.DATABASE):
0b198ed096af fixes for py2.1 (booleans, sigh)
Richard Jones <richard@users.sourceforge.net>
parents: 2243
diff changeset
83 shutil.rmtree(config.DATABASE)
0b198ed096af fixes for py2.1 (booleans, sigh)
Richard Jones <richard@users.sourceforge.net>
parents: 2243
diff changeset
84
7714
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
85 def get_database_name(config):
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
86 '''Get database name using config.RDBMS_NAME or config.RDBMS_SERVICE.
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
87
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
88 If database specifed using RDBMS_SERVICE does not exist,
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
89 the error message is parsed for the database name. This
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
90 will fail if the error message changes. The alternative is
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
91 to try to find and parse the .pg_service .ini style file on
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
92 unix/windows. This is less palatable.
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
93
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
94 If the database specified using RDBMS_SERVICE does exist, (i.e. we
7716
0ccc6d3ba9f2 fix: spelling fix in comment.
John Rouillard <rouilj@ieee.org>
parents: 7715
diff changeset
95 are doing a nuke operation), use psycopg.extensions.ConnectionInfo
7714
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
96 to get the dbname. This requires psycopg2 > 2.8 from 2018.
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
97 '''
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
98
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
99 if config.RDBMS_NAME:
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
100 return config.RDBMS_NAME
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
101
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
102 template1 = connection_dict(config)
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
103 try:
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
104 conn = psycopg2.connect(**template1)
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
105 except psycopg2.OperationalError as message:
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
106 import re
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
107 # extract db name from error:
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
108 # 'connection to server at "127.0.0.1", port 5432 failed: \
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
109 # FATAL: database "rounduptest" does not exist\n'
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
110 # ugh.
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
111 #
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
112 # Database name is any character sequence not including a " or
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
113 # whitespace. Arguably both are allowed by:
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
114 #
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
115 # https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
116 #
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
117 # with suitable quoting but ... really.
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
118 search = re.search(
7718
3da452f4a3ac fix: make regexp a raw string
John Rouillard <rouilj@ieee.org>
parents: 7717
diff changeset
119 r'FATAL:\s+database\s+"([^"\s]*)"\s+does\s+not\s+exist',
7714
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
120 message.args[0])
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
121 if search:
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
122 dbname = search.groups()[0]
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
123 return dbname
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
124
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
125 raise hyperdb.DatabaseError(
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
126 "Unable to determine database from service: %s" % message)
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
127
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
128 dbname = psycopg2.extensions.ConnectionInfo(conn).dbname
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
129 conn.close()
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
130 return dbname
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
131
4514
f6c49df25048 PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4471
diff changeset
132 def db_command(config, command, database='postgres'):
2243
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
133 '''Perform some sort of database-level command. Retry 10 times if we
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
134 fail by conflicting with another user.
4514
f6c49df25048 PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4471
diff changeset
135
f6c49df25048 PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4471
diff changeset
136 Since PostgreSQL version 8.1 there is a database "postgres",
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
137 before "template1" seems to have been used, so we fall back to it.
4514
f6c49df25048 PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4471
diff changeset
138 Compare to issue2550543.
2243
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
139 '''
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
140 template1 = connection_dict(config, 'database')
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
141 db_name, schema_name = db_schema_split(template1['database'])
4514
f6c49df25048 PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4471
diff changeset
142 template1['database'] = database
3918
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
143
2243
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
144 try:
5751
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
145 conn = psycopg2.connect(**template1)
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
146 except psycopg2.OperationalError as message:
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
147 if not schema_name:
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
148 if re.search(r'database ".+" does not exist', str(message)):
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
149 return db_command(config, command, database='template1')
4357
13b3155869e0 Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents: 4239
diff changeset
150 raise hyperdb.DatabaseError(message)
3918
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
151
2243
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
152 conn.set_isolation_level(0)
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
153 cursor = conn.cursor()
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
154 try:
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
155 for _n in range(10):
2243
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
156 if pg_command(cursor, command):
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
157 return
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
158 finally:
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
159 conn.close()
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
160 raise RuntimeError('10 attempts to create database or schema failed when running: %s' % command)
2243
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
161
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
162
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
163 def pg_command(cursor, command, args=()):
2243
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
164 '''Execute the postgresql command, which may be blocked by some other
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
165 user connecting to the database, and return a true value if it succeeds.
3918
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
166
3717
5770f1802cd0 better conflict retry in postgresql backend [SF#1552809]
Richard Jones <richard@users.sourceforge.net>
parents: 3715
diff changeset
167 If there is a concurrent update, retry the command.
2243
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
168 '''
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
169 try:
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
170 cursor.execute(command, args)
5751
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
171 except psycopg2.DatabaseError as err:
2243
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
172 response = str(err).split('\n')[0]
6332
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
173 if "FATAL" not in response:
5118
57452bc6d989 issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents: 5096
diff changeset
174 msgs = (
3717
5770f1802cd0 better conflict retry in postgresql backend [SF#1552809]
Richard Jones <richard@users.sourceforge.net>
parents: 3715
diff changeset
175 'is being accessed by other users',
5770f1802cd0 better conflict retry in postgresql backend [SF#1552809]
Richard Jones <richard@users.sourceforge.net>
parents: 3715
diff changeset
176 'could not serialize access due to concurrent update',
5118
57452bc6d989 issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents: 5096
diff changeset
177 )
6332
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
178 for msg in msgs:
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
179 if msg in response:
5118
57452bc6d989 issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents: 5096
diff changeset
180 time.sleep(0.1)
57452bc6d989 issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents: 5096
diff changeset
181 return 0
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
182 raise RuntimeError(response, command, args)
2243
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
183 return 1
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
184
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
185
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
186 def db_exists(config):
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
187 """Check if database or schema already exists"""
3099
519b92df37dc handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents: 3088
diff changeset
188 db = connection_dict(config, 'database')
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
189 db_name, schema_name = db_schema_split(db['database'])
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
190 if schema_name:
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
191 db['database'] = db_name
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
192 try:
5751
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
193 conn = psycopg2.connect(**db)
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
194 if not schema_name:
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
195 conn.close()
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
196 return 1
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
197 except Exception:
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
198 return 0
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
199 # <schema_name> will have a non-false value here; otherwise one
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
200 # of the above returns would have returned.
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
201 # Get a count of the number of schemas named <schema_name> (either 0 or 1).
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
202 command = "SELECT COUNT(*) FROM information_schema.schemata WHERE schema_name = %s"
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
203 cursor = conn.cursor()
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
204 pg_command(cursor, command, (schema_name,))
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
205 count = cursor.fetchall()[0][0]
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
206 conn.close()
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
207 return count # 'count' will be 0 or 1.
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
208
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
209
3918
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
210 class Sessions(sessions_rdbms.Sessions):
3924
21d3d7eeea8c assorted pyflakes fixes
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3920
diff changeset
211 def set(self, *args, **kwargs):
3918
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
212 try:
3927
97ae174f7a0e add self to Sessions.set
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3924
diff changeset
213 sessions_rdbms.Sessions.set(self, *args, **kwargs)
5248
198b6e810c67 Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents: 5118
diff changeset
214 except ProgrammingError as err:
3918
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
215 response = str(err).split('\n')[0]
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
216 if -1 != response.find('ERROR') and \
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
217 -1 != response.find('could not serialize access due to concurrent update'):
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
218 # another client just updated, and we're running on
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
219 # serializable isolation.
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
220 # see http://www.postgresql.org/docs/7.4/interactive/transaction-iso.html
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
221 self.db.rollback()
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
222
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
223
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
224 class Database(rdbms_common.Database):
5096
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
225 """Postgres DB backend implementation
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
226
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
227 attributes:
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
228 dbtype:
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
229 holds the value for the type of db. It is used by indexer to
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
230 identify the database type so it can import the correct indexer
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
231 module when using native text search mode.
7668
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
232
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
233 import_savepoint_count:
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
234 count the number of savepoints that have been created during
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
235 import. Once the limit of savepoints is reached, a commit is
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
236 done and this is reset to 0.
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
237
5096
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
238 """
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
239
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
240 arg = '%s'
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
241
5096
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
242 dbtype = "postgres"
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
243
7668
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
244 import_savepoint_count = 0
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
245
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
246 # Value is set from roundup-admin using db.config["RDBMS_SAVEPOINT_LIMIT"]
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
247 # or to the default of 10_000 at runtime. Use 0 here to trigger
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
248 # initialization.
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
249 savepoint_limit = 0
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
250
3048
d9b4224f955c merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents: 2745
diff changeset
251 # used by some code to switch styles of query
d9b4224f955c merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents: 2745
diff changeset
252 implements_intersect = 1
d9b4224f955c merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents: 2745
diff changeset
253
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
254 def sql_open_connection(self):
3099
519b92df37dc handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents: 3088
diff changeset
255 db = connection_dict(self.config, 'database')
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
256 db_name, schema_name = db_schema_split (db['database'])
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
257 if schema_name:
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
258 db['database'] = db_name
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
259
7696
4af0d235b570 feat(db): support using postgresql service connection file
John Rouillard <rouilj@ieee.org>
parents: 7668
diff changeset
260 # database option always present: log it if not null
4af0d235b570 feat(db): support using postgresql service connection file
John Rouillard <rouilj@ieee.org>
parents: 7668
diff changeset
261 if db['database']:
4af0d235b570 feat(db): support using postgresql service connection file
John Rouillard <rouilj@ieee.org>
parents: 7668
diff changeset
262 logging.getLogger('roundup.hyperdb').info(
4af0d235b570 feat(db): support using postgresql service connection file
John Rouillard <rouilj@ieee.org>
parents: 7668
diff changeset
263 'open database %r' % db['database'])
4af0d235b570 feat(db): support using postgresql service connection file
John Rouillard <rouilj@ieee.org>
parents: 7668
diff changeset
264 if 'service' in db: # only log if used
4af0d235b570 feat(db): support using postgresql service connection file
John Rouillard <rouilj@ieee.org>
parents: 7668
diff changeset
265 logging.getLogger('roundup.hyperdb').info(
4af0d235b570 feat(db): support using postgresql service connection file
John Rouillard <rouilj@ieee.org>
parents: 7668
diff changeset
266 'open database via service %r' % db['service'])
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
267 try:
5751
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
268 conn = psycopg2.connect(**db)
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
269 except psycopg2.OperationalError as message:
4357
13b3155869e0 Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents: 4239
diff changeset
270 raise hyperdb.DatabaseError(message)
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
271
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
272 cursor = conn.cursor()
4887
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
273 if ISOLATION_LEVEL_REPEATABLE_READ is not None:
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
274 lvl = isolation_levels[self.config.RDBMS_ISOLATION_LEVEL]
4887
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
275 conn.set_isolation_level(lvl)
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
276
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
277 if schema_name:
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
278 self.sql ('SET search_path TO %s' % schema_name, cursor=cursor)
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
279 # Commit is required so that a subsequent rollback
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
280 # will not also rollback the search_path change.
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
281 self.sql ('COMMIT', cursor=cursor)
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
282 return (conn, cursor)
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
283
6332
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
284 def sql_new_cursor(self, name='default', conn=None, *args, **kw):
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
285 """ Create new cursor, this may need additional parameters for
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
286 performance optimization for different backends.
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
287 """
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
288 use_name = self.config.RDBMS_SERVERSIDE_CURSOR
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
289 kw = {}
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
290 if use_name:
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
291 kw['name'] = name
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
292 if conn is None:
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
293 conn = self.conn
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
294 return conn.cursor(*args, **kw)
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
295
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
296 def open_connection(self):
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
297 if not db_exists(self.config):
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
298 db_create(self.config)
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
299
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
300 self.conn, self.cursor = self.sql_open_connection()
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
301
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
302 try:
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
303 self.load_dbschema()
5248
198b6e810c67 Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents: 5118
diff changeset
304 except ProgrammingError as message:
3739
f1363e19121a fix error detection for non-EN locales of postgres [SF#1592249]
Richard Jones <richard@users.sourceforge.net>
parents: 3717
diff changeset
305 if str(message).find('schema') == -1:
2736
402d6d556558 postgres backend open doesn't hide corruption in schema [SF#956375]
Richard Jones <richard@users.sourceforge.net>
parents: 2721
diff changeset
306 raise
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
307 self.rollback()
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
308 self.init_dbschema()
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
309 self.sql("CREATE TABLE schema (schema TEXT)")
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
310 self.sql("CREATE TABLE dual (dummy integer)")
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
311 self.sql("insert into dual values (1)")
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
312 self.create_version_2_tables()
6587
4f8fc55730e1 Add words_both_idx to newly created databases.
John Rouillard <rouilj@ieee.org>
parents: 6433
diff changeset
313 self.fix_version_3_tables()
5319
62de601bdf6f Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5248
diff changeset
314 # Need to commit here, otherwise otk/session will not find
62de601bdf6f Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5248
diff changeset
315 # the necessary tables (in a parallel connection!)
62de601bdf6f Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5248
diff changeset
316 self.commit()
6604
0d99ae7c8de6 Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents: 6599
diff changeset
317 self._add_fts_table()
0d99ae7c8de6 Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents: 6599
diff changeset
318 self.commit()
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
319
6610
db3f0ba75b4a Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents: 6604
diff changeset
320 def checkpoint_data(self, savepoint="importing"):
db3f0ba75b4a Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents: 6604
diff changeset
321 """Create a subtransaction savepoint. Allows recovery/retry
db3f0ba75b4a Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents: 6604
diff changeset
322 of operation in exception handler because
db3f0ba75b4a Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents: 6604
diff changeset
323 postgres requires a rollback in case of error
db3f0ba75b4a Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents: 6604
diff changeset
324 generating exception. Used with
7668
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
325 restore_connection_on_error to handle uniqueness
6610
db3f0ba75b4a Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents: 6604
diff changeset
326 conflict in import_table().
7668
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
327
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
328 Savepoints take memory resources. Postgres keeps all
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
329 savepoints (rather than overwriting) until a
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
330 commit(). Commit every ~10,000 savepoints to prevent
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
331 running out of memory on import.
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
332
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
333 NOTE: a commit outside of this method will not reset the
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
334 import_savepoint_count. This can result in an unneeded
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
335 commit on a new cursor (that has no savepoints) as there is
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
336 no way to find out if there is a savepoint or how many
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
337 savepoints are opened on a db connection/cursor.
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
338
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
339 Because an import is a one shot deal and not part of a long
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
340 running daemon (e.g. the roundup-server), I am not too
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
341 worried about it. It will just slow the import down a tad.
6433
c1d3fbcdbfbd issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents: 6332
diff changeset
342 """
7668
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
343
6610
db3f0ba75b4a Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents: 6604
diff changeset
344 self.sql('SAVEPOINT %s' % savepoint)
6433
c1d3fbcdbfbd issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents: 6332
diff changeset
345
7668
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
346 self.import_savepoint_count += 1
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
347
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
348 if not self.savepoint_limit:
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
349 if "RDBMS_SAVEPOINT_LIMIT" in self.config.keys():
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
350 # note this config option is created on the fly
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
351 # by admin.py::do_import. It is never listed in
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
352 # config.ini.
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
353 self.savepoint_limit = self.config["RDBMS_SAVEPOINT_LIMIT"]
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
354 else:
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
355 self.savepoint_limit = 10000
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
356
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
357 if self.import_savepoint_count > self.savepoint_limit:
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
358 # track savepoints and commit every 10000 (or user value)
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
359 # so we don't run postgres out of memory. An import of a
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
360 # customer's tracker ran out of memory after importing
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
361 # ~23000 items with: psycopg2.errors.OutOfMemory: out of
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
362 # shared memory HINT: You might need to increase
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
363 # max_locks_per_transaction.
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
364
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
365 self.commit()
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
366 self.import_savepoint_count = 0
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
367
6610
db3f0ba75b4a Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents: 6604
diff changeset
368 def restore_connection_on_error(self, savepoint="importing"):
db3f0ba75b4a Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents: 6604
diff changeset
369 """Postgres leaves a connection/cursor in an unusable state
db3f0ba75b4a Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents: 6604
diff changeset
370 after an error. Rollback the transaction to a
db3f0ba75b4a Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents: 6604
diff changeset
371 previous savepoint and permit a retry of the
db3f0ba75b4a Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents: 6604
diff changeset
372 failed statement. Used with checkpoint_data to
db3f0ba75b4a Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents: 6604
diff changeset
373 handle uniqueness conflict in import_table().
6433
c1d3fbcdbfbd issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents: 6332
diff changeset
374 """
6610
db3f0ba75b4a Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents: 6604
diff changeset
375 self.sql('ROLLBACK TO %s' % savepoint)
6433
c1d3fbcdbfbd issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents: 6332
diff changeset
376
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
377 def create_version_2_tables(self):
2093
3f6024ab2c7a That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents: 2089
diff changeset
378 # OTK store
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
379 self.sql('''CREATE TABLE otks (otk_key VARCHAR(255),
6806
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6610
diff changeset
380 otk_value TEXT, otk_time float)''')
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
381 self.sql('CREATE INDEX otks_key_idx ON otks(otk_key)')
2093
3f6024ab2c7a That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents: 2089
diff changeset
382
3f6024ab2c7a That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents: 2089
diff changeset
383 # Sessions store
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
384 self.sql('''CREATE TABLE sessions (
6806
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6610
diff changeset
385 session_key VARCHAR(255), session_time float,
2721
1cd01cf106e1 extend OTK and session table value cols to TEXT [SF#1031271]
Richard Jones <richard@users.sourceforge.net>
parents: 2719
diff changeset
386 session_value TEXT)''')
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
387 self.sql('''CREATE INDEX sessions_key_idx ON
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
388 sessions(session_key)''')
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
389
2093
3f6024ab2c7a That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents: 2089
diff changeset
390 # full-text indexing store
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
391 self.sql('CREATE SEQUENCE ___textids_ids')
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
392 self.sql('''CREATE TABLE __textids (
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
393 _textid integer primary key, _class VARCHAR(255),
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
394 _itemid VARCHAR(255), _prop VARCHAR(255))''')
6593
e70e2789bc2c issue2551189 - increase text search maxlength
John Rouillard <rouilj@ieee.org>
parents: 6587
diff changeset
395 self.sql('''CREATE TABLE __words (_word VARCHAR(%s),
e70e2789bc2c issue2551189 - increase text search maxlength
John Rouillard <rouilj@ieee.org>
parents: 6587
diff changeset
396 _textid integer)''' % (self.indexer.maxlength + 5))
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
397 self.sql('CREATE INDEX words_word_idx ON __words(_word)')
3858
bb30bbfc7cdd Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3739
diff changeset
398 self.sql('CREATE INDEX words_by_id ON __words (_textid)')
bb30bbfc7cdd Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3739
diff changeset
399 self.sql('CREATE UNIQUE INDEX __textids_by_props ON '
bb30bbfc7cdd Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3739
diff changeset
400 '__textids (_class, _itemid, _prop)')
2093
3f6024ab2c7a That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents: 2089
diff changeset
401
2413
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
402 def fix_version_2_tables(self):
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
403 # Convert journal date column to TIMESTAMP, params column to TEXT
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
404 self._convert_journal_tables()
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
405
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
406 # Convert all String properties to TEXT
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
407 self._convert_string_properties()
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
408
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
409 # convert session / OTK *_time columns to REAL
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
410 for name in ('otk', 'session'):
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
411 self.sql('drop index %ss_key_idx' % name)
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
412 self.sql('drop table %ss' % name)
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
413 self.sql('''CREATE TABLE %ss (%s_key VARCHAR(255),
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
414 %s_value VARCHAR(255), %s_time REAL)''' % (name, name,
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
415 name, name))
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
416 self.sql('CREATE INDEX %ss_key_idx ON %ss(%s_key)' % (name, name,
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
417 name))
2413
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
418
2745
b284ff7af3fb add optimised index on postgresql text index table
Richard Jones <richard@users.sourceforge.net>
parents: 2736
diff changeset
419 def fix_version_3_tables(self):
b284ff7af3fb add optimised index on postgresql text index table
Richard Jones <richard@users.sourceforge.net>
parents: 2736
diff changeset
420 rdbms_common.Database.fix_version_3_tables(self)
7715
95f0002e85c4 fix: remove 'public' schema from __words table in fix_version_3_tables
John Rouillard <rouilj@ieee.org>
parents: 7714
diff changeset
421 self.sql('''CREATE INDEX words_both_idx ON __words
2745
b284ff7af3fb add optimised index on postgresql text index table
Richard Jones <richard@users.sourceforge.net>
parents: 2736
diff changeset
422 USING btree (_word, _textid)''')
b284ff7af3fb add optimised index on postgresql text index table
Richard Jones <richard@users.sourceforge.net>
parents: 2736
diff changeset
423
6604
0d99ae7c8de6 Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents: 6599
diff changeset
424 def _add_fts_table(self):
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
425 self.sql(
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
426 'CREATE TABLE __fts (_class VARCHAR(255), '
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
427 '_itemid VARCHAR(255), _prop VARCHAR(255), _tsv tsvector)'
6604
0d99ae7c8de6 Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents: 6599
diff changeset
428 )
0d99ae7c8de6 Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents: 6599
diff changeset
429
0d99ae7c8de6 Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents: 6599
diff changeset
430 self.sql('CREATE INDEX __fts_idx ON __fts USING GIN (_tsv)')
0d99ae7c8de6 Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents: 6599
diff changeset
431
6599
39189dd94f2c issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents: 6593
diff changeset
432 def fix_version_6_tables(self):
39189dd94f2c issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents: 6593
diff changeset
433 # Modify length for __words._word column.
39189dd94f2c issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents: 6593
diff changeset
434 c = self.cursor
39189dd94f2c issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents: 6593
diff changeset
435 sql = 'alter table __words alter column _word type varchar(%s)' % (
39189dd94f2c issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents: 6593
diff changeset
436 self.arg)
39189dd94f2c issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents: 6593
diff changeset
437 # Why magic number 5? It was the original offset between
39189dd94f2c issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents: 6593
diff changeset
438 # column length and maxlength.
39189dd94f2c issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents: 6593
diff changeset
439 c.execute(sql, (self.indexer.maxlength + 5,))
39189dd94f2c issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents: 6593
diff changeset
440
6604
0d99ae7c8de6 Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents: 6599
diff changeset
441 self._add_fts_table()
0d99ae7c8de6 Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents: 6599
diff changeset
442
6806
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6610
diff changeset
443 def fix_version_7_tables(self):
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6610
diff changeset
444 # Modify type for session.session_time/otk.otk_time column.
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6610
diff changeset
445 # float is double precision 15 signifcant digits
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6610
diff changeset
446 sql = 'alter table sessions alter column session_time type float'
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6610
diff changeset
447 self.sql(sql)
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6610
diff changeset
448 sql = 'alter table otks alter column otk_time type float'
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6610
diff changeset
449 self.sql(sql)
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6610
diff changeset
450
6913
b0dbc13a835a fix missed renaming from revision 98d3bf8ffb19 done 2004-04-18
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
451 def add_new_columns_v2(self):
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
452 # update existing tables to have the new actor column
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
453 tables = self.database_schema['tables']
4357
13b3155869e0 Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents: 4239
diff changeset
454 for name in tables:
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
455 self.sql('ALTER TABLE _%s add __actor VARCHAR(255)' % name)
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
456
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
457 def __repr__(self):
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
458 return '<roundpsycopgsql 0x%x>' % id(self)
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
459
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
460 def sql_stringquote(self, value):
5751
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
461 ''' psycopg2.QuotedString returns a "buffer" object with the
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
462 single-quotes around it... '''
3715
50add98cbbac change as per last message in patch [SF#1429391]
Richard Jones <richard@users.sourceforge.net>
parents: 3687
diff changeset
463 return str(QuotedString(str(value)))[1:-1]
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
464
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
465 def sql_index_exists(self, table_name, index_name):
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
466 sql = 'select count(*) from pg_indexes where ' \
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
467 'tablename=%s and indexname=%s' % (self.arg, self.arg)
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
468 self.sql(sql, (table_name, index_name))
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
469 return self.cursor.fetchone()[0]
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
470
2424
74474ec41050 argh! backwards compat
Richard Jones <richard@users.sourceforge.net>
parents: 2413
diff changeset
471 def create_class_table(self, spec, create_sequence=1):
2413
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
472 if create_sequence:
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
473 sql = 'CREATE SEQUENCE _%s_ids' % spec.classname
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
474 self.sql(sql)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
475
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
476 return rdbms_common.Database.create_class_table(self, spec)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
477
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
478 def drop_class_table(self, cn):
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
479 sql = 'drop table _%s' % cn
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
480 self.sql(sql)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
481
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
482 sql = 'drop sequence _%s_ids' % cn
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
483 self.sql(sql)
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
484
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
485 def newid(self, classname):
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
486 sql = "select nextval('_%s_ids') from dual" % classname
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
487 self.sql(sql)
3977
732a37da3a10 Fix for postgres 8.3 compatibility (and bug) (patch [SF#2030479])
Richard Jones <richard@users.sourceforge.net>
parents: 3927
diff changeset
488 return str(self.cursor.fetchone()[0])
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
489
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
490 def setid(self, classname, setid):
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
491 sql = "select setval('_%s_ids', %s) from dual" % (classname,
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
492 int(setid))
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
493 self.sql(sql)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
494
3310
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
495 def clear(self):
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
496 rdbms_common.Database.clear(self)
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
497
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
498 # reset the sequences
4357
13b3155869e0 Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents: 4239
diff changeset
499 for cn in self.classes:
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
500 self.cursor.execute('DROP SEQUENCE _%s_ids' % cn)
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
501 self.cursor.execute('CREATE SEQUENCE _%s_ids' % cn)
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
502
3310
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
503
3685
4d9adb8bc3b1 Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3659
diff changeset
504 class PostgresqlClass:
4d9adb8bc3b1 Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3659
diff changeset
505 order_by_null_values = '(%s is not NULL)'
4787
4a017661e414 Closed issue2550805 (Postgresql should search title case insensitive), by Tom Ekberg.
Bernhard Reiter <bernhard@intevation.de>
parents: 4570
diff changeset
506 case_insensitive_like = 'ILIKE'
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
507
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
508
3685
4d9adb8bc3b1 Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3659
diff changeset
509 class Class(PostgresqlClass, rdbms_common.Class):
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
510 pass
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
511
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
512
3685
4d9adb8bc3b1 Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3659
diff changeset
513 class IssueClass(PostgresqlClass, rdbms_common.IssueClass):
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
514 pass
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
515
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
516
3685
4d9adb8bc3b1 Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3659
diff changeset
517 class FileClass(PostgresqlClass, rdbms_common.FileClass):
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
518 pass
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
519
3920
416606b09b27 fix vim modelines
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3918
diff changeset
520 # vim: set et sts=4 sw=4 :
Roundup Issue Tracker: http://roundup-tracker.org/