Mercurial > p > roundup > code

annotate roundup/backends/back_postgresql.py @ 7698:4e37a7833708

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
doc: add FTS5 requirement for sqlite backend. Document the requirement for FTS5 if you are using the sqlite backend. Tonu Mikk reported issues with using a SQLite that was missing FTS5 support (RedHat 7 sqlite with python 3.6). Even though FTS5 has been in the sqlite amalgamation since 2015-10-14 (v3.9.0) RedHat chose to not build their package with that. This leads to a traceback when initializing the database.
author John Rouillard <rouilj@ieee.org>
date Sat, 11 Nov 2023 19:39:57 -0500
parents 4af0d235b570
children b41750bf9f03
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
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
12 import shutil
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
13 import time
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
14
4887
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
15 ISOLATION_LEVEL_READ_UNCOMMITTED = None
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
16 ISOLATION_LEVEL_READ_COMMITTED = None
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
17 ISOLATION_LEVEL_REPEATABLE_READ = None
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
18 ISOLATION_LEVEL_SERIALIZABLE = None
5118
57452bc6d989 issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents: 5096
diff changeset
19
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
20 import psycopg2 # noqa: E402
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
21 from psycopg2 import ProgrammingError # noqa: E402
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
22 from psycopg2.extensions import QuotedString # noqa: E402
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
23 from psycopg2.extensions import ISOLATION_LEVEL_READ_UNCOMMITTED # noqa: F401 E402
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
24 from psycopg2.extensions import ISOLATION_LEVEL_READ_COMMITTED # noqa: E402
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
25 from psycopg2.extensions import ISOLATION_LEVEL_REPEATABLE_READ # noqa: E402
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
26 from psycopg2.extensions import ISOLATION_LEVEL_SERIALIZABLE # noqa: E402
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
27 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
28
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
29 from roundup import hyperdb # noqa: E402
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
30 from roundup.backends import rdbms_common # noqa: E402
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
31 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
32
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
33 isolation_levels = {
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
34 'read uncommitted': ISOLATION_LEVEL_READ_COMMITTED,
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
35 'read committed': ISOLATION_LEVEL_READ_COMMITTED,
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
36 'repeatable read': ISOLATION_LEVEL_REPEATABLE_READ,
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
37 'serializable': ISOLATION_LEVEL_SERIALIZABLE
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
38 }
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
39
4887
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
40
3099
519b92df37dc handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents: 3088
diff changeset
41 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
42 ''' 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
43 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
44 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
45 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
46 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
47 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
48 return d
519b92df37dc handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents: 3088
diff changeset
49
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
50
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
51 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
52 """Clear all database contents and drop database itself"""
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
53 command = "CREATE DATABASE \"%s\" WITH ENCODING='UNICODE'" % config.RDBMS_NAME
6332
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
54 if config.RDBMS_TEMPLATE:
4471
4f353d71d716 Configuration issue:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 4421
diff changeset
55 command = command + " TEMPLATE=%s" % config.RDBMS_TEMPLATE
4420
9655a1b65974 - more logger fixes -- use correct hierarchical logger names...
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 4357
diff changeset
56 logging.getLogger('roundup.hyperdb').info(command)
2243
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
57 db_command(config, command)
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
58
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
59
5319
62de601bdf6f Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5248
diff changeset
60 def db_nuke(config):
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
61 """Clear all database contents and drop database itself"""
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
62 command = 'DROP DATABASE "%s"' % config.RDBMS_NAME
4421
67bef70ab9b9 - more logger fixes, sorry for the noise.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 4420
diff changeset
63 logging.getLogger('roundup.hyperdb').info(command)
2243
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
64 db_command(config, command)
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
65
2256
0b198ed096af fixes for py2.1 (booleans, sigh)
Richard Jones <richard@users.sourceforge.net>
parents: 2243
diff changeset
66 if os.path.exists(config.DATABASE):
0b198ed096af fixes for py2.1 (booleans, sigh)
Richard Jones <richard@users.sourceforge.net>
parents: 2243
diff changeset
67 shutil.rmtree(config.DATABASE)
0b198ed096af fixes for py2.1 (booleans, sigh)
Richard Jones <richard@users.sourceforge.net>
parents: 2243
diff changeset
68
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
69
4514
f6c49df25048 PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4471
diff changeset
70 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
71 '''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
72 fail by conflicting with another user.
4514
f6c49df25048 PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4471
diff changeset
73
f6c49df25048 PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4471
diff changeset
74 Since PostgreSQL version 8.1 there is a database "postgres",
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
75 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
76 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
77 '''
3099
519b92df37dc handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents: 3088
diff changeset
78 template1 = connection_dict(config)
4514
f6c49df25048 PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4471
diff changeset
79 template1['database'] = database
3918
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
80
2243
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
81 try:
5751
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
82 conn = psycopg2.connect(**template1)
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
83 except psycopg2.OperationalError as message:
4514
f6c49df25048 PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4471
diff changeset
84 if str(message).find('database "postgres" does not exist') >= 0:
f6c49df25048 PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4471
diff changeset
85 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
86 raise hyperdb.DatabaseError(message)
3918
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
87
2243
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
88 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
89 cursor = conn.cursor()
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
90 try:
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
91 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
92 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
93 return
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
94 finally:
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
95 conn.close()
4357
13b3155869e0 Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents: 4239
diff changeset
96 raise RuntimeError('10 attempts to create database failed')
2243
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
97
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
98
2243
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
99 def 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
100 '''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
101 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
102
3717
5770f1802cd0 better conflict retry in postgresql backend [SF#1552809]
Richard Jones <richard@users.sourceforge.net>
parents: 3715
diff changeset
103 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
104 '''
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
105 try:
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
106 cursor.execute(command)
5751
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
107 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
108 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
109 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
110 msgs = (
3717
5770f1802cd0 better conflict retry in postgresql backend [SF#1552809]
Richard Jones <richard@users.sourceforge.net>
parents: 3715
diff changeset
111 '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
112 '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
113 )
6332
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
114 for msg in msgs:
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
115 if msg in response:
5118
57452bc6d989 issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents: 5096
diff changeset
116 time.sleep(0.1)
57452bc6d989 issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents: 5096
diff changeset
117 return 0
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
118 raise RuntimeError(response)
2243
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
119 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
120
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
121
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
122 def db_exists(config):
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
123 """Check if database already exists"""
3099
519b92df37dc handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents: 3088
diff changeset
124 db = connection_dict(config, 'database')
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
125 try:
5751
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
126 conn = psycopg2.connect(**db)
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
127 conn.close()
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
128 return 1
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
129 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
130 return 0
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
131
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
132
3918
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
133 class Sessions(sessions_rdbms.Sessions):
3924
21d3d7eeea8c assorted pyflakes fixes
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3920
diff changeset
134 def set(self, *args, **kwargs):
3918
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
135 try:
3927
97ae174f7a0e add self to Sessions.set
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3924
diff changeset
136 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
137 except ProgrammingError as err:
3918
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
138 response = str(err).split('\n')[0]
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
139 if -1 != response.find('ERROR') and \
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
140 -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
141 # 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
142 # serializable isolation.
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
143 # 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
144 self.db.rollback()
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
145
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
146
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
147 class Database(rdbms_common.Database):
5096
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
148 """Postgres DB backend implementation
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
149
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
150 attributes:
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
151 dbtype:
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
152 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
153 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
154 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
155
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
156 import_savepoint_count:
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
157 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
158 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
159 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
160
5096
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
161 """
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
162
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
163 arg = '%s'
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
164
5096
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
165 dbtype = "postgres"
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
166
7668
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
167 import_savepoint_count = 0
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
168
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
169 # 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
170 # 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
171 # initialization.
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
172 savepoint_limit = 0
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
173
3048
d9b4224f955c merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents: 2745
diff changeset
174 # 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
175 implements_intersect = 1
d9b4224f955c merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents: 2745
diff changeset
176
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
177 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
178 db = connection_dict(self.config, 'database')
7696
4af0d235b570 feat(db): support using postgresql service connection file
John Rouillard <rouilj@ieee.org>
parents: 7668
diff changeset
179 # 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
180 if db['database']:
4af0d235b570 feat(db): support using postgresql service connection file
John Rouillard <rouilj@ieee.org>
parents: 7668
diff changeset
181 logging.getLogger('roundup.hyperdb').info(
4af0d235b570 feat(db): support using postgresql service connection file
John Rouillard <rouilj@ieee.org>
parents: 7668
diff changeset
182 'open database %r' % db['database'])
4af0d235b570 feat(db): support using postgresql service connection file
John Rouillard <rouilj@ieee.org>
parents: 7668
diff changeset
183 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
184 logging.getLogger('roundup.hyperdb').info(
4af0d235b570 feat(db): support using postgresql service connection file
John Rouillard <rouilj@ieee.org>
parents: 7668
diff changeset
185 '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
186 try:
5751
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
187 conn = psycopg2.connect(**db)
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
188 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
189 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
190
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
191 cursor = conn.cursor()
4887
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
192 if ISOLATION_LEVEL_REPEATABLE_READ is not None:
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
193 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
194 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
195
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
196 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
197
6332
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
198 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
199 """ 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
200 performance optimization for different backends.
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
201 """
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
202 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
203 kw = {}
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
204 if use_name:
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
205 kw['name'] = name
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
206 if conn is None:
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
207 conn = self.conn
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
208 return conn.cursor(*args, **kw)
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
209
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
210 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
211 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
212 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
213
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
214 self.conn, self.cursor = self.sql_open_connection()
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
215
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
216 try:
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
217 self.load_dbschema()
5248
198b6e810c67 Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents: 5118
diff changeset
218 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
219 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
220 raise
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
221 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
222 self.init_dbschema()
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
223 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
224 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
225 self.sql("insert into dual values (1)")
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
226 self.create_version_2_tables()
6587
4f8fc55730e1 Add words_both_idx to newly created databases.
John Rouillard <rouilj@ieee.org>
parents: 6433
diff changeset
227 self.fix_version_3_tables()
5319
62de601bdf6f Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5248
diff changeset
228 # 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
229 # 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
230 self.commit()
6604
0d99ae7c8de6 Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents: 6599
diff changeset
231 self._add_fts_table()
0d99ae7c8de6 Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents: 6599
diff changeset
232 self.commit()
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
233
6610
db3f0ba75b4a Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents: 6604
diff changeset
234 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
235 """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
236 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
237 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
238 generating exception. Used with
7668
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
239 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
240 conflict in import_table().
7668
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
241
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
242 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
243 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
244 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
245 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
246
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
247 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
248 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
249 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
250 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
251 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
252
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
253 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
254 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
255 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
256 """
7668
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
257
6610
db3f0ba75b4a Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents: 6604
diff changeset
258 self.sql('SAVEPOINT %s' % savepoint)
6433
c1d3fbcdbfbd issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents: 6332
diff changeset
259
7668
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
260 self.import_savepoint_count += 1
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
261
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
262 if not self.savepoint_limit:
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
263 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
264 # 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
265 # 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
266 # config.ini.
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
267 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
268 else:
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
269 self.savepoint_limit = 10000
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
270
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
271 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
272 # 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
273 # 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
274 # 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
275 # ~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
276 # 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
277 # max_locks_per_transaction.
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
278
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
279 self.commit()
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
280 self.import_savepoint_count = 0
5b41018617f2 fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents: 6935
diff changeset
281
6610
db3f0ba75b4a Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents: 6604
diff changeset
282 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
283 """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
284 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
285 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
286 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
287 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
288 """
6610
db3f0ba75b4a Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents: 6604
diff changeset
289 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
290
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
291 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
292 # OTK store
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
293 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
294 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
295 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
296
3f6024ab2c7a That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents: 2089
diff changeset
297 # Sessions store
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
298 self.sql('''CREATE TABLE sessions (
6806
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6610
diff changeset
299 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
300 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
301 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
302 sessions(session_key)''')
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
303
2093
3f6024ab2c7a That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents: 2089
diff changeset
304 # 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
305 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
306 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
307 _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
308 _itemid VARCHAR(255), _prop VARCHAR(255))''')
6593
e70e2789bc2c issue2551189 - increase text search maxlength
John Rouillard <rouilj@ieee.org>
parents: 6587
diff changeset
309 self.sql('''CREATE TABLE __words (_word VARCHAR(%s),
e70e2789bc2c issue2551189 - increase text search maxlength
John Rouillard <rouilj@ieee.org>
parents: 6587
diff changeset
310 _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
311 self.sql('CREATE INDEX words_word_idx ON __words(_word)')
3858
bb30bbfc7cdd Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3739
diff changeset
312 self.sql('CREATE INDEX words_by_id ON __words (_textid)')
bb30bbfc7cdd Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3739
diff changeset
313 self.sql('CREATE UNIQUE INDEX __textids_by_props ON '
bb30bbfc7cdd Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3739
diff changeset
314 '__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
315
2413
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
316 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
317 # 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
318 self._convert_journal_tables()
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
319
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
320 # 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
321 self._convert_string_properties()
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
322
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
323 # 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
324 for name in ('otk', 'session'):
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
325 self.sql('drop index %ss_key_idx' % name)
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
326 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
327 self.sql('''CREATE TABLE %ss (%s_key VARCHAR(255),
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
328 %s_value VARCHAR(255), %s_time REAL)''' % (name, name,
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
329 name, name))
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
330 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
331 name))
2413
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
332
2745
b284ff7af3fb add optimised index on postgresql text index table
Richard Jones <richard@users.sourceforge.net>
parents: 2736
diff changeset
333 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
334 rdbms_common.Database.fix_version_3_tables(self)
b284ff7af3fb add optimised index on postgresql text index table
Richard Jones <richard@users.sourceforge.net>
parents: 2736
diff changeset
335 self.sql('''CREATE INDEX words_both_idx ON public.__words
b284ff7af3fb add optimised index on postgresql text index table
Richard Jones <richard@users.sourceforge.net>
parents: 2736
diff changeset
336 USING btree (_word, _textid)''')
b284ff7af3fb add optimised index on postgresql text index table
Richard Jones <richard@users.sourceforge.net>
parents: 2736
diff changeset
337
6604
0d99ae7c8de6 Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents: 6599
diff changeset
338 def _add_fts_table(self):
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
339 self.sql(
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
340 'CREATE TABLE __fts (_class VARCHAR(255), '
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
341 '_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
342 )
0d99ae7c8de6 Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents: 6599
diff changeset
343
0d99ae7c8de6 Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents: 6599
diff changeset
344 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
345
6599
39189dd94f2c issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents: 6593
diff changeset
346 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
347 # 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
348 c = self.cursor
39189dd94f2c issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents: 6593
diff changeset
349 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
350 self.arg)
39189dd94f2c issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents: 6593
diff changeset
351 # 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
352 # column length and maxlength.
39189dd94f2c issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents: 6593
diff changeset
353 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
354
6604
0d99ae7c8de6 Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents: 6599
diff changeset
355 self._add_fts_table()
0d99ae7c8de6 Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents: 6599
diff changeset
356
6806
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6610
diff changeset
357 def fix_version_7_tables(self):
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6610
diff changeset
358 # 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
359 # 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
360 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
361 self.sql(sql)
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6610
diff changeset
362 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
363 self.sql(sql)
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6610
diff changeset
364
6913
b0dbc13a835a fix missed renaming from revision 98d3bf8ffb19 done 2004-04-18
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
365 def add_new_columns_v2(self):
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
366 # update existing tables to have the new actor column
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
367 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
368 for name in tables:
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
369 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
370
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
371 def __repr__(self):
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
372 return '<roundpsycopgsql 0x%x>' % id(self)
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
373
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
374 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
375 ''' psycopg2.QuotedString returns a "buffer" object with the
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
376 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
377 return str(QuotedString(str(value)))[1:-1]
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
378
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
379 def sql_index_exists(self, table_name, index_name):
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
380 sql = 'select count(*) from pg_indexes where ' \
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
381 '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
382 self.sql(sql, (table_name, index_name))
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
383 return self.cursor.fetchone()[0]
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
384
2424
74474ec41050 argh! backwards compat
Richard Jones <richard@users.sourceforge.net>
parents: 2413
diff changeset
385 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
386 if create_sequence:
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
387 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
388 self.sql(sql)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
389
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
390 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
391
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
392 def drop_class_table(self, cn):
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
393 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
394 self.sql(sql)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
395
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
396 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
397 self.sql(sql)
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
398
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
399 def newid(self, classname):
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
400 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
401 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
402 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
403
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
404 def setid(self, classname, setid):
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
405 sql = "select setval('_%s_ids', %s) from dual" % (classname,
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
406 int(setid))
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
407 self.sql(sql)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
408
3310
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
409 def clear(self):
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
410 rdbms_common.Database.clear(self)
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
411
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
412 # 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
413 for cn in self.classes:
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
414 self.cursor.execute('DROP SEQUENCE _%s_ids' % cn)
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
415 self.cursor.execute('CREATE SEQUENCE _%s_ids' % cn)
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
416
3310
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
417
3685
4d9adb8bc3b1 Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3659
diff changeset
418 class PostgresqlClass:
4d9adb8bc3b1 Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3659
diff changeset
419 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
420 case_insensitive_like = 'ILIKE'
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
421
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
422
3685
4d9adb8bc3b1 Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3659
diff changeset
423 class Class(PostgresqlClass, rdbms_common.Class):
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
424 pass
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
425
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
426
3685
4d9adb8bc3b1 Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3659
diff changeset
427 class IssueClass(PostgresqlClass, rdbms_common.IssueClass):
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
428 pass
6935
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
429
b5062cb5c2a2 flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6913
diff changeset
430
3685
4d9adb8bc3b1 Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3659
diff changeset
431 class FileClass(PostgresqlClass, rdbms_common.FileClass):
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
432 pass
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
433
3920
416606b09b27 fix vim modelines
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3918
diff changeset
434 # vim: set et sts=4 sw=4 :
Roundup Issue Tracker: http://roundup-tracker.org/