Mercurial > p > roundup > code

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

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
fix(db): Make using pg_service work again. When I did the merge of schema support I broke pg_service.conf support by replacing get_database_name with db_schema_split. This commit fixes it. Also this commit returns the schema if one is specified in pg_service.conf. back_postgresql.py: Replace calls to db_schema_split() with get_database_schema_names() (new name for get_database_name()). Rename db_schema_split to _db_schema_split. It now returns a tuple (dbname, schema) rather than a list. It is used only by get_database_schema_names() which also returns tuples. get_database_schema_names() can also get schema info for the service (if present) as specified by pg_service.conf. Add get_database_user() to get the user from either RDBMS_USER or pg_service.conf. (User needed for creating schema, so not needed before schema patch. import re at the top of file and remove lower import. Remove some schema code from db_command as it's not needed. The database conection is done to either postgresql or template1 existing databases. This command never connects to the roundp specified db. test/test_postgresql.py: Reorganize top level imports, add import os. Replace import of db_schema_split with get_database_schema_names. Also replace calls to db_schema_split. Create new Opener for the service file. Set PGSERVICEFILE to point to test/pg_service.conf. Add three new classes to test Service: 1) using regular db 2) using schema within db 3) Unable to parse schema name from pg_service.conf. The last doesn't need a db. Number 1 and 2 reuse the tests in ROTest to verify db connectivity. test/pg_service.conf: three service connections for: db only, db and schema, and incorrectly specified schema test cases. doc/upgrading.txt: updated to current status. Included example schema definition in service file.
author John Rouillard <rouilj@ieee.org>
date Thu, 28 Dec 2023 15:13:42 -0500
parents 3071db43bfb6
children bbc99def147a
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):
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
551 sql = "select setval('_%s_ids', %s) from dual" % (classname,
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/