Mercurial > p > roundup > code

annotate roundup/backends/back_postgresql.py @ 8295:bbc99def147a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
fix: issue2550815 - roundup-admin import for postgres causes id to be skipped. Fixed code so that the id used to set the sequence will be returned and not skipped. (Tom Ekberg diagnosed and supplied the fix.)
author John Rouillard <rouilj@ieee.org>
date Sun, 19 Jan 2025 19:37:48 -0500
parents 8147f6deac9f
children cfa7d43a3658
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
7723
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
51 def _db_schema_split(database_name):
7719
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 ('.')
7723
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
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"""
7723
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
59 db_name, schema_name = get_database_schema_names(config)
7719
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:
7723
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
67 command = "CREATE SCHEMA \"%s\" AUTHORIZATION \"%s\"" % (
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
68 schema_name, get_database_user_name(config))
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
69 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
70 db_command(config, command, db_name)
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
71
5319
62de601bdf6f Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5248
diff changeset
72 def db_nuke(config):
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
73 """Drop the database (and all its contents) or the schema."""
7723
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
74 db_name, schema_name = get_database_schema_names(config)
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
75 if not schema_name:
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
76 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
77 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
78 db_command(config, command)
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
79 else:
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
80 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
81 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
82 db_command(config, command, db_name)
2256
0b198ed096af fixes for py2.1 (booleans, sigh)
Richard Jones <richard@users.sourceforge.net>
parents: 2243
diff changeset
83 if os.path.exists(config.DATABASE):
0b198ed096af fixes for py2.1 (booleans, sigh)
Richard Jones <richard@users.sourceforge.net>
parents: 2243
diff changeset
84 shutil.rmtree(config.DATABASE)
0b198ed096af fixes for py2.1 (booleans, sigh)
Richard Jones <richard@users.sourceforge.net>
parents: 2243
diff changeset
85
7723
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
86 def get_database_schema_names(config):
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
87 '''Get database and schema names using config.RDBMS_NAME or service
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
88 defined by config.RDBMS_SERVICE.
7714
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
89
7723
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
90 If database specifed using RDBMS_SERVICE does not exist, the
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
91 error message is parsed for the database name. This database
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
92 can then be created by calling code. Parsing will fail if the
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
93 error message changes. The alternative is to try to find and
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
94 parse the .pg_service .ini style file on unix/windows. This is
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
95 less palatable.
7714
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
96
7723
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
97 If the database specified using RDBMS_SERVICE exists, (e.g. we
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
98 are doing a nuke operation), use
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
99 psycopg.extensions.ConnectionInfo to get the dbname. Also parse
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
100 the search_path options setting to get the schema. Only the
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
101 first element of the search_path is returned. This requires
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
102 psycopg2 > 2.8 from 2018.
7714
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
103 '''
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
104
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
105 if config.RDBMS_NAME:
7723
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
106 return _db_schema_split(config.RDBMS_NAME)
7714
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
107
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
108 template1 = connection_dict(config)
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
109 try:
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
110 conn = psycopg2.connect(**template1)
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
111 except psycopg2.OperationalError as message:
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
112 # extract db name from error:
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
113 # '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
114 # 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
115 # ugh.
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 # 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
118 # whitespace. Arguably both are allowed by:
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
119 #
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
120 # 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
121 #
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
122 # with suitable quoting but ... really.
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
123 search = re.search(
7718
3da452f4a3ac fix: make regexp a raw string
John Rouillard <rouilj@ieee.org>
parents: 7717
diff changeset
124 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
125 message.args[0])
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
126 if search:
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
127 dbname = search.groups()[0]
7723
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
128 # To use a schema, the db has to have been precreated.
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
129 # So return '' for schema if database does not exist.
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
130 return (dbname, '')
7714
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
131
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
132 raise hyperdb.DatabaseError(
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
133 "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
134
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
135 dbname = psycopg2.extensions.ConnectionInfo(conn).dbname
7723
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
136 schema = ''
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
137 options = psycopg2.extensions.ConnectionInfo(conn).options
7714
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
138 conn.close()
7723
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
139
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
140 # Assume schema is first word in the search_path spec.
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
141 # , (for multiple items in path) and whitespace (for another option)
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
142 # end the schema name.
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
143 m = re.search(r'search_path=([^,\s]*)', options)
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
144 if m:
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
145 schema = m.group(1)
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
146 if not schema:
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
147 raise ValueError('Unable to get schema for service: "%s" from options: "%s"' % (template1['service'], options))
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
148
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
149 return (dbname, schema)
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
150
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
151 def get_database_user_name(config):
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
152 '''Get database username using config.RDBMS_USER or return
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
153 user from connection created using config.RDBMS_SERVICE.
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
154
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
155 If the database specified using RDBMS_SERVICE does exist, (i.e. we
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
156 are doing a nuke operation), use psycopg.extensions.ConnectionInfo
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
157 to get the user. This requires psycopg2 > 2.8 from 2018.
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
158 '''
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
159 if config.RDBMS_USER:
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
160 return config.RDBMS_USER
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
161
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
162 template1 = connection_dict(config)
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
163 try:
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
164 conn = psycopg2.connect(**template1)
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
165 except psycopg2.OperationalError as message:
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
166 # extract db name from error:
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
167 # 'connection to server at "127.0.0.1", port 5432 failed: \
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
168 # FATAL: database "rounduptest" does not exist\n'
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
169 # ugh.
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
170 #
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
171 # Database name is any character sequence not including a " or
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
172 # whitespace. Arguably both are allowed by:
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
173 #
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
174 # https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
175 #
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
176 # with suitable quoting but ... really.
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
177 search = re.search(
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
178 r'FATAL:\s+database\s+"([^"\s]*)"\s+does\s+not\s+exist',
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
179 message.args[0])
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
180 if search:
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
181 dbname = search.groups()[0]
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
182 # To have a user, the db has to exist already.
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
183 # so return '' for user.
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
184 return ''
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
185
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
186 raise hyperdb.DatabaseError(
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
187 "Unable to determine database from service: %s" % message)
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
188
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
189 user = psycopg2.extensions.ConnectionInfo(conn).user
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
190 conn.close()
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
191
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
192 return user
7714
b41750bf9f03 fix: figure out dbname when using pg_service.
John Rouillard <rouilj@ieee.org>
parents: 7696
diff changeset
193
4514
f6c49df25048 PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4471
diff changeset
194 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
195 '''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
196 fail by conflicting with another user.
4514
f6c49df25048 PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4471
diff changeset
197
f6c49df25048 PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4471
diff changeset
198 Since PostgreSQL version 8.1 there is a database "postgres",
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
199 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
200 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
201 '''
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
202 template1 = connection_dict(config, 'database')
4514
f6c49df25048 PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4471
diff changeset
203 template1['database'] = database
3918
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
204
2243
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
205 try:
5751
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
206 conn = psycopg2.connect(**template1)
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
207 except psycopg2.OperationalError as message:
7723
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
208 if re.search(r'database ".+" does not exist', str(message)):
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
209 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
210 raise hyperdb.DatabaseError(message)
3918
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
211
2243
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
212 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
213 cursor = conn.cursor()
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
214 try:
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
215 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
216 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
217 return
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
218 finally:
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
219 conn.close()
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
220 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
221
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
222
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
223 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
224 '''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
225 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
226
3717
5770f1802cd0 better conflict retry in postgresql backend [SF#1552809]
Richard Jones <richard@users.sourceforge.net>
parents: 3715
diff changeset
227 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
228 '''
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
229 try:
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
230 cursor.execute(command, args)
5751
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
231 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
232 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
233 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
234 msgs = (
3717
5770f1802cd0 better conflict retry in postgresql backend [SF#1552809]
Richard Jones <richard@users.sourceforge.net>
parents: 3715
diff changeset
235 '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
236 '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
237 )
6332
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
238 for msg in msgs:
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
239 if msg in response:
5118
57452bc6d989 issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents: 5096
diff changeset
240 time.sleep(0.1)
57452bc6d989 issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents: 5096
diff changeset
241 return 0
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
242 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
243 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
244
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
245
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
246 def db_exists(config):
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
247 """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
248 db = connection_dict(config, 'database')
7723
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
249 db_name, schema_name = get_database_schema_names(config)
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
250 if schema_name:
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
251 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
252 try:
5751
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
253 conn = psycopg2.connect(**db)
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
254 if not schema_name:
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
255 conn.close()
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
256 return 1
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
257 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
258 return 0
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
259 # <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
260 # 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
261 # 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
262 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
263 cursor = conn.cursor()
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
264 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
265 count = cursor.fetchall()[0][0]
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
266 conn.close()
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
267 return count # 'count' will be 0 or 1.
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
268
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
269
3918
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
270 class Sessions(sessions_rdbms.Sessions):
3924
21d3d7eeea8c assorted pyflakes fixes
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3920
diff changeset
271 def set(self, *args, **kwargs):
3918
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
272 try:
3927
97ae174f7a0e add self to Sessions.set
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3924
diff changeset
273 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
274 except ProgrammingError as err:
3918
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
275 response = str(err).split('\n')[0]
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
276 if -1 != response.find('ERROR') and \
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
277 -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
278 # 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
279 # serializable isolation.
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
280 # 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
281 self.db.rollback()
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
282
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
283
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
284 class Database(rdbms_common.Database):
5096
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
285 """Postgres DB backend implementation
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
286
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
287 attributes:
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
288 dbtype:
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
289 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
290 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
291 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
292
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
293 import_savepoint_count:
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
294 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
295 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
296 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
297
5096
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
298 """
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
299
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
300 arg = '%s'
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
301
5096
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
302 dbtype = "postgres"
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
303
7668
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
304 import_savepoint_count = 0
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
305
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
306 # 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
307 # 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
308 # initialization.
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
309 savepoint_limit = 0
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
310
3048
d9b4224f955c merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents: 2745
diff changeset
311 # 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
312 implements_intersect = 1
d9b4224f955c merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents: 2745
diff changeset
313
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
314 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
315 db = connection_dict(self.config, 'database')
7723
8147f6deac9f fix(db): Make using pg_service work again.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
316 db_name, schema_name = get_database_schema_names(self.config)
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
317 if schema_name:
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
318 db['database'] = db_name
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
319
7696
4af0d235b570 feat(db): support using postgresql service connection file
John Rouillard <rouilj@ieee.org>
parents: 7668
diff changeset
320 # 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
321 if db['database']:
4af0d235b570 feat(db): support using postgresql service connection file
John Rouillard <rouilj@ieee.org>
parents: 7668
diff changeset
322 logging.getLogger('roundup.hyperdb').info(
4af0d235b570 feat(db): support using postgresql service connection file
John Rouillard <rouilj@ieee.org>
parents: 7668
diff changeset
323 'open database %r' % db['database'])
4af0d235b570 feat(db): support using postgresql service connection file
John Rouillard <rouilj@ieee.org>
parents: 7668
diff changeset
324 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
325 logging.getLogger('roundup.hyperdb').info(
4af0d235b570 feat(db): support using postgresql service connection file
John Rouillard <rouilj@ieee.org>
parents: 7668
diff changeset
326 '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
327 try:
5751
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
328 conn = psycopg2.connect(**db)
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
329 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
330 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
331
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
332 cursor = conn.cursor()
4887
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
333 if ISOLATION_LEVEL_REPEATABLE_READ is not None:
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
334 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
335 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
336
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
337 if schema_name:
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7718
diff changeset
338 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
339 # 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
340 # 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
341 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
342 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
343
6332
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
344 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
345 """ 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
346 performance optimization for different backends.
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
347 """
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
348 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
349 kw = {}
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
350 if use_name:
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
351 kw['name'] = name
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
352 if conn is None:
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
353 conn = self.conn
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
354 return conn.cursor(*args, **kw)
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
355
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
356 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
357 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
358 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
359
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
360 self.conn, self.cursor = self.sql_open_connection()
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
361
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
362 try:
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
363 self.load_dbschema()
5248
198b6e810c67 Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents: 5118
diff changeset
364 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
365 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
366 raise
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
367 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
368 self.init_dbschema()
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
369 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
370 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
371 self.sql("insert into dual values (1)")
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
372 self.create_version_2_tables()
6587
4f8fc55730e1 Add words_both_idx to newly created databases.
John Rouillard <rouilj@ieee.org>
parents: 6433
diff changeset
373 self.fix_version_3_tables()
5319
62de601bdf6f Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5248
diff changeset
374 # 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
375 # 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
376 self.commit()
6604
0d99ae7c8de6 Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents: 6599
diff changeset
377 self._add_fts_table()
0d99ae7c8de6 Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents: 6599
diff changeset
378 self.commit()
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
379
6610
db3f0ba75b4a Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents: 6604
diff changeset
380 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
381 """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
382 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
383 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
384 generating exception. Used with
7668
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
385 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
386 conflict in import_table().
7668
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
387
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
388 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
389 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
390 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
391 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
392
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
393 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
394 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
395 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
396 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
397 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
398
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
399 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
400 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
401 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
402 """
7668
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
403
6610
db3f0ba75b4a Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents: 6604
diff changeset
404 self.sql('SAVEPOINT %s' % savepoint)
6433
c1d3fbcdbfbd issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents: 6332
diff changeset
405
7668
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
406 self.import_savepoint_count += 1
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
407
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
408 if not self.savepoint_limit:
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
409 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
410 # 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
411 # 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
412 # config.ini.
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
413 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
414 else:
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
415 self.savepoint_limit = 10000
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
416
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
417 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
418 # 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
419 # 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
420 # 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
421 # ~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
422 # 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
423 # max_locks_per_transaction.
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
424
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
425 self.commit()
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
426 self.import_savepoint_count = 0
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
427
6610
db3f0ba75b4a Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents: 6604
diff changeset
428 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
429 """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
430 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
431 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
432 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
433 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
434 """
6610
db3f0ba75b4a Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents: 6604
diff changeset
435 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
436
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
437 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
438 # OTK store
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
439 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
440 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
441 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
442
3f6024ab2c7a That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents: 2089
diff changeset
443 # Sessions store
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
444 self.sql('''CREATE TABLE sessions (
6806
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6610
diff changeset
445 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
446 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
447 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
448 sessions(session_key)''')
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
449
2093
3f6024ab2c7a That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents: 2089
diff changeset
450 # 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
451 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
452 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
453 _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
454 _itemid VARCHAR(255), _prop VARCHAR(255))''')
6593
e70e2789bc2c issue2551189 - increase text search maxlength
John Rouillard <rouilj@ieee.org>
parents: 6587
diff changeset
455 self.sql('''CREATE TABLE __words (_word VARCHAR(%s),
e70e2789bc2c issue2551189 - increase text search maxlength
John Rouillard <rouilj@ieee.org>
parents: 6587
diff changeset
456 _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
457 self.sql('CREATE INDEX words_word_idx ON __words(_word)')
3858
bb30bbfc7cdd Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3739
diff changeset
458 self.sql('CREATE INDEX words_by_id ON __words (_textid)')
bb30bbfc7cdd Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3739
diff changeset
459 self.sql('CREATE UNIQUE INDEX __textids_by_props ON '
bb30bbfc7cdd Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3739
diff changeset
460 '__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
461
2413
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
462 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
463 # 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
464 self._convert_journal_tables()
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
465
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
466 # 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
467 self._convert_string_properties()
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
468
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
469 # 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
470 for name in ('otk', 'session'):
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
471 self.sql('drop index %ss_key_idx' % name)
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
472 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
473 self.sql('''CREATE TABLE %ss (%s_key VARCHAR(255),
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
474 %s_value VARCHAR(255), %s_time REAL)''' % (name, name,
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
475 name, name))
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
476 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
477 name))
2413
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
478
2745
b284ff7af3fb add optimised index on postgresql text index table
Richard Jones <richard@users.sourceforge.net>
parents: 2736
diff changeset
479 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
480 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
481 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
482 USING btree (_word, _textid)''')
b284ff7af3fb add optimised index on postgresql text index table
Richard Jones <richard@users.sourceforge.net>
parents: 2736
diff changeset
483
6604
0d99ae7c8de6 Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents: 6599
diff changeset
484 def _add_fts_table(self):
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
485 self.sql(
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
486 'CREATE TABLE __fts (_class VARCHAR(255), '
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
487 '_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
488 )
0d99ae7c8de6 Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents: 6599
diff changeset
489
0d99ae7c8de6 Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents: 6599
diff changeset
490 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
491
6599
39189dd94f2c issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents: 6593
diff changeset
492 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
493 # 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
494 c = self.cursor
39189dd94f2c issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents: 6593
diff changeset
495 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
496 self.arg)
39189dd94f2c issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents: 6593
diff changeset
497 # 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
498 # column length and maxlength.
39189dd94f2c issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents: 6593
diff changeset
499 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
500
6604
0d99ae7c8de6 Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents: 6599
diff changeset
501 self._add_fts_table()
0d99ae7c8de6 Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents: 6599
diff changeset
502
6806
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6610
diff changeset
503 def fix_version_7_tables(self):
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6610
diff changeset
504 # 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
505 # 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
506 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
507 self.sql(sql)
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6610
diff changeset
508 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
509 self.sql(sql)
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6610
diff changeset
510
6913
b0dbc13a835a fix missed renaming from revision 98d3bf8ffb19 done 2004-04-18
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
511 def add_new_columns_v2(self):
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
512 # update existing tables to have the new actor column
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
513 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
514 for name in tables:
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
515 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
516
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
517 def __repr__(self):
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
518 return '<roundpsycopgsql 0x%x>' % id(self)
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
519
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
520 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
521 ''' psycopg2.QuotedString returns a "buffer" object with the
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
522 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
523 return str(QuotedString(str(value)))[1:-1]
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
524
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
525 def sql_index_exists(self, table_name, index_name):
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
526 sql = 'select count(*) from pg_indexes where ' \
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
527 '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
528 self.sql(sql, (table_name, index_name))
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
529 return self.cursor.fetchone()[0]
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
530
2424
74474ec41050 argh! backwards compat
Richard Jones <richard@users.sourceforge.net>
parents: 2413
diff changeset
531 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
532 if create_sequence:
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
533 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
534 self.sql(sql)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
535
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
536 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
537
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
538 def drop_class_table(self, cn):
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
539 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
540 self.sql(sql)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
541
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
542 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
543 self.sql(sql)
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
544
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
545 def newid(self, classname):
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
546 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
547 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
548 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
549
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
550 def setid(self, classname, setid):
8295
bbc99def147a fix: issue2550815 - roundup-admin import for postgres causes id to be skipped.
John Rouillard <rouilj@ieee.org>
parents: 7723
diff changeset
551 sql = "select setval('_%s_ids', %s, false) from dual" % (classname,
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
552 int(setid))
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
553 self.sql(sql)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
554
3310
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
555 def clear(self):
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
556 rdbms_common.Database.clear(self)
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
557
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
558 # 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
559 for cn in self.classes:
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
560 self.cursor.execute('DROP SEQUENCE _%s_ids' % cn)
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
561 self.cursor.execute('CREATE SEQUENCE _%s_ids' % cn)
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
562
3310
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
563
3685
4d9adb8bc3b1 Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3659
diff changeset
564 class PostgresqlClass:
4d9adb8bc3b1 Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3659
diff changeset
565 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
566 case_insensitive_like = 'ILIKE'
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
567
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
568
3685
4d9adb8bc3b1 Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3659
diff changeset
569 class Class(PostgresqlClass, rdbms_common.Class):
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
570 pass
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
571
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
572
3685
4d9adb8bc3b1 Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3659
diff changeset
573 class IssueClass(PostgresqlClass, rdbms_common.IssueClass):
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
574 pass
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
575
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
576
3685
4d9adb8bc3b1 Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3659
diff changeset
577 class FileClass(PostgresqlClass, rdbms_common.FileClass):
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
578 pass
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
579
3920
416606b09b27 fix vim modelines
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3918
diff changeset
580 # vim: set et sts=4 sw=4 :
Roundup Issue Tracker: http://roundup-tracker.org/