Mercurial > p > roundup > code

annotate roundup/backends/back_postgresql.py @ 6332:6a6b4651be1f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use server-side cursor for postgres in some cases In filter, filter_iter, and _materialize_multilinks, use named cursor with postgresql. This turns of client-side cursor handling and avoids *large* roundup process (or wsgi process) in case of large results. Fixes issue2551114.
author Ralf Schlatterbeck <rsc@runtux.com>
date Thu, 04 Mar 2021 12:55:21 +0100
parents 5cb6e6b594b0
children c1d3fbcdbfbd
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
4239
5e3991670011 remove unused, deprecated import
Richard Jones <richard@users.sourceforge.net>
parents: 3977
diff changeset
10 import os, shutil, time
4887
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
11 ISOLATION_LEVEL_READ_UNCOMMITTED = None
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
12 ISOLATION_LEVEL_READ_COMMITTED = None
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
13 ISOLATION_LEVEL_REPEATABLE_READ = None
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
14 ISOLATION_LEVEL_SERIALIZABLE = None
5118
57452bc6d989 issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents: 5096
diff changeset
15
5751
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
16 import psycopg2
5118
57452bc6d989 issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents: 5096
diff changeset
17 from psycopg2.extensions import QuotedString
57452bc6d989 issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents: 5096
diff changeset
18 from psycopg2.extensions import ISOLATION_LEVEL_READ_UNCOMMITTED
57452bc6d989 issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents: 5096
diff changeset
19 from psycopg2.extensions import ISOLATION_LEVEL_READ_COMMITTED
57452bc6d989 issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents: 5096
diff changeset
20 from psycopg2.extensions import ISOLATION_LEVEL_REPEATABLE_READ
57452bc6d989 issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents: 5096
diff changeset
21 from psycopg2.extensions import ISOLATION_LEVEL_SERIALIZABLE
5751
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
22 from psycopg2 import ProgrammingError
5118
57452bc6d989 issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents: 5096
diff changeset
23 from psycopg2.extensions import TransactionRollbackError
57452bc6d989 issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents: 5096
diff changeset
24
3155
57b60bda9473 Python 2.3 minimum version - bye bye roundup.rlog, you had a short life.
Richard Jones <richard@users.sourceforge.net>
parents: 3099
diff changeset
25 import logging
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
26
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
27 from roundup import hyperdb, date
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
28 from roundup.backends import rdbms_common
3918
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
29 from roundup.backends import sessions_rdbms
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
30
4887
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
31 isolation_levels = \
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
32 { 'read uncommitted': ISOLATION_LEVEL_READ_COMMITTED
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
33 , 'read committed': ISOLATION_LEVEL_READ_COMMITTED
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
34 , 'repeatable read': ISOLATION_LEVEL_REPEATABLE_READ
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
35 , 'serializable': ISOLATION_LEVEL_SERIALIZABLE
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
36 }
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
37
3099
519b92df37dc handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents: 3088
diff changeset
38 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
39 ''' 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
40 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
41 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
42 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
43 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
44 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
45 return d
519b92df37dc handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents: 3088
diff changeset
46
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
47 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
48 """Clear all database contents and drop database itself"""
4515
6467fd9a3afd The PostgreSQL backend quotes database names now for CREATE and DROP...
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4514
diff changeset
49 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
50 if config.RDBMS_TEMPLATE:
4471
4f353d71d716 Configuration issue:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 4421
diff changeset
51 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
52 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
53 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
54
5319
62de601bdf6f Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5248
diff changeset
55 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
56 """Clear all database contents and drop database itself"""
4515
6467fd9a3afd The PostgreSQL backend quotes database names now for CREATE and DROP...
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4514
diff changeset
57 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
58 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
59 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
60
2256
0b198ed096af fixes for py2.1 (booleans, sigh)
Richard Jones <richard@users.sourceforge.net>
parents: 2243
diff changeset
61 if os.path.exists(config.DATABASE):
0b198ed096af fixes for py2.1 (booleans, sigh)
Richard Jones <richard@users.sourceforge.net>
parents: 2243
diff changeset
62 shutil.rmtree(config.DATABASE)
0b198ed096af fixes for py2.1 (booleans, sigh)
Richard Jones <richard@users.sourceforge.net>
parents: 2243
diff changeset
63
4514
f6c49df25048 PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4471
diff changeset
64 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
65 '''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
66 fail by conflicting with another user.
4514
f6c49df25048 PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4471
diff changeset
67
f6c49df25048 PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4471
diff changeset
68 Since PostgreSQL version 8.1 there is a database "postgres",
5751
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
69 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
70 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
71 '''
3099
519b92df37dc handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents: 3088
diff changeset
72 template1 = connection_dict(config)
4514
f6c49df25048 PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4471
diff changeset
73 template1['database'] = database
3918
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
74
2243
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
75 try:
5751
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
76 conn = psycopg2.connect(**template1)
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
77 except psycopg2.OperationalError as message:
4514
f6c49df25048 PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4471
diff changeset
78 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
79 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
80 raise hyperdb.DatabaseError(message)
3918
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
81
2243
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
82 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
83 cursor = conn.cursor()
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
84 try:
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
85 for n in range(10):
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
86 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
87 return
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
88 finally:
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
89 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
90 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
91
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
92 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
93 '''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
94 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
95
3717
5770f1802cd0 better conflict retry in postgresql backend [SF#1552809]
Richard Jones <richard@users.sourceforge.net>
parents: 3715
diff changeset
96 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
97 '''
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
98 try:
20507f6759fc postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents: 2107
diff changeset
99 cursor.execute(command)
5751
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
100 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
101 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
102 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
103 msgs = (
3717
5770f1802cd0 better conflict retry in postgresql backend [SF#1552809]
Richard Jones <richard@users.sourceforge.net>
parents: 3715
diff changeset
104 '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
105 '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
106 )
6332
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
107 for msg in msgs:
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
108 if msg in response:
5118
57452bc6d989 issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents: 5096
diff changeset
109 time.sleep(0.1)
57452bc6d989 issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents: 5096
diff changeset
110 return 0
57452bc6d989 issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents: 5096
diff changeset
111 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
112 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
113
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
114 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
115 """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
116 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
117 try:
5751
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
118 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
119 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
120 return 1
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
121 except:
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
122 return 0
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
123
3918
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
124 class Sessions(sessions_rdbms.Sessions):
3924
21d3d7eeea8c assorted pyflakes fixes
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3920
diff changeset
125 def set(self, *args, **kwargs):
3918
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
126 try:
3927
97ae174f7a0e add self to Sessions.set
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3924
diff changeset
127 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
128 except ProgrammingError as err:
3918
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
129 response = str(err).split('\n')[0]
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
130 if -1 != response.find('ERROR') and \
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
131 -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
132 # 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
133 # serializable isolation.
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
134 # 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
135 self.db.rollback()
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
136
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
137 class Database(rdbms_common.Database):
5096
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
138 """Postgres DB backend implementation
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
139
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
140 attributes:
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
141 dbtype:
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
142 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
143 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
144 module when using native text search mode.
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
145 """
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
146
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
147 arg = '%s'
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
148
5096
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
149 dbtype = "postgres"
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
150
3048
d9b4224f955c merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents: 2745
diff changeset
151 # 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
152 implements_intersect = 1
d9b4224f955c merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents: 2745
diff changeset
153
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
154 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
155 db = connection_dict(self.config, 'database')
4421
67bef70ab9b9 - more logger fixes, sorry for the noise.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 4420
diff changeset
156 logging.getLogger('roundup.hyperdb').info(
67bef70ab9b9 - more logger fixes, sorry for the noise.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 4420
diff changeset
157 'open database %r'%db['database'])
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
158 try:
5751
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
159 conn = psycopg2.connect(**db)
5cb6e6b594b0 issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents: 5319
diff changeset
160 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
161 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
162
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
163 cursor = conn.cursor()
4887
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
164 if ISOLATION_LEVEL_REPEATABLE_READ is not None:
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
165 lvl = isolation_levels [self.config.RDBMS_ISOLATION_LEVEL]
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4787
diff changeset
166 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
167
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
168 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
169
6332
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
170 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
171 """ 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
172 performance optimization for different backends.
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
173 """
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
174 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
175 kw = {}
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
176 if use_name:
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
177 kw['name'] = name
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
178 if conn is None:
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
179 conn = self.conn
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
180 return conn.cursor(*args, **kw)
6a6b4651be1f Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5751
diff changeset
181
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
182 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
183 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
184 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
185
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 self.conn, self.cursor = self.sql_open_connection()
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
187
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
188 try:
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
189 self.load_dbschema()
5248
198b6e810c67 Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents: 5118
diff changeset
190 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
191 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
192 raise
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
193 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
194 self.init_dbschema()
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
195 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
196 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
197 self.sql("insert into dual values (1)")
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
198 self.create_version_2_tables()
5319
62de601bdf6f Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5248
diff changeset
199 # 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
200 # 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
201 self.commit()
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
202
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
203 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
204 # OTK store
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
205 self.sql('''CREATE TABLE otks (otk_key VARCHAR(255),
2721
1cd01cf106e1 extend OTK and session table value cols to TEXT [SF#1031271]
Richard Jones <richard@users.sourceforge.net>
parents: 2719
diff changeset
206 otk_value TEXT, otk_time REAL)''')
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
207 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
208
3f6024ab2c7a That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents: 2089
diff changeset
209 # Sessions store
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
210 self.sql('''CREATE TABLE sessions (
2413
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
211 session_key VARCHAR(255), session_time REAL,
2721
1cd01cf106e1 extend OTK and session table value cols to TEXT [SF#1031271]
Richard Jones <richard@users.sourceforge.net>
parents: 2719
diff changeset
212 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
213 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
214 sessions(session_key)''')
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
215
2093
3f6024ab2c7a That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents: 2089
diff changeset
216 # 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
217 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
218 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
219 _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
220 _itemid VARCHAR(255), _prop VARCHAR(255))''')
3918
c8899c4bf6ad safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3863
diff changeset
221 self.sql('''CREATE TABLE __words (_word VARCHAR(30),
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
222 _textid integer)''')
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
223 self.sql('CREATE INDEX words_word_idx ON __words(_word)')
3858
bb30bbfc7cdd Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3739
diff changeset
224 self.sql('CREATE INDEX words_by_id ON __words (_textid)')
bb30bbfc7cdd Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3739
diff changeset
225 self.sql('CREATE UNIQUE INDEX __textids_by_props ON '
bb30bbfc7cdd Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3739
diff changeset
226 '__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
227
2413
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
228 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
229 # 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
230 self._convert_journal_tables()
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
231
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
232 # 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
233 self._convert_string_properties()
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
234
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
235 # 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
236 for name in ('otk', 'session'):
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
237 self.sql('drop index %ss_key_idx'%name)
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
238 self.sql('drop table %ss'%name)
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
239 self.sql('''CREATE TABLE %ss (%s_key VARCHAR(255),
2413
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
240 %s_value VARCHAR(255), %s_time REAL)'''%(name, name, name,
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
241 name))
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
242 self.sql('CREATE INDEX %ss_key_idx ON %ss(%s_key)'%(name, name,
2413
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
243 name))
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
244
2745
b284ff7af3fb add optimised index on postgresql text index table
Richard Jones <richard@users.sourceforge.net>
parents: 2736
diff changeset
245 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
246 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
247 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
248 USING btree (_word, _textid)''')
b284ff7af3fb add optimised index on postgresql text index table
Richard Jones <richard@users.sourceforge.net>
parents: 2736
diff changeset
249
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
250 def add_actor_column(self):
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
251 # update existing tables to have the new actor column
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
252 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
253 for name in tables:
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
254 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
255
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
256 def __repr__(self):
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
257 return '<roundpsycopgsql 0x%x>' % id(self)
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
258
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
259 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
260 ''' psycopg2.QuotedString returns a "buffer" object with the
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
261 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
262 return str(QuotedString(str(value)))[1:-1]
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
263
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
264 def sql_index_exists(self, table_name, index_name):
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
265 sql = 'select count(*) from pg_indexes where ' \
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
266 '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
267 self.sql(sql, (table_name, index_name))
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
268 return self.cursor.fetchone()[0]
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
269
2424
74474ec41050 argh! backwards compat
Richard Jones <richard@users.sourceforge.net>
parents: 2413
diff changeset
270 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
271 if create_sequence:
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2256
diff changeset
272 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
273 self.sql(sql)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
274
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
275 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
276
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
277 def drop_class_table(self, cn):
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
278 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
279 self.sql(sql)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
280
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
281 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
282 self.sql(sql)
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
283
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
284 def newid(self, classname):
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
285 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
286 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
287 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
288
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
289 def setid(self, classname, setid):
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
290 sql = "select setval('_%s_ids', %s) from dual"%(classname, int(setid))
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
291 self.sql(sql)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
292
3310
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
293 def clear(self):
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
294 rdbms_common.Database.clear(self)
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
295
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
296 # 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
297 for cn in self.classes:
3310
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
298 self.cursor.execute('DROP SEQUENCE _%s_ids'%cn)
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
299 self.cursor.execute('CREATE SEQUENCE _%s_ids'%cn)
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
300
3685
4d9adb8bc3b1 Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3659
diff changeset
301 class PostgresqlClass:
4d9adb8bc3b1 Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3659
diff changeset
302 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
303 case_insensitive_like = 'ILIKE'
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
304
3685
4d9adb8bc3b1 Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3659
diff changeset
305 class Class(PostgresqlClass, rdbms_common.Class):
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
306 pass
3685
4d9adb8bc3b1 Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3659
diff changeset
307 class IssueClass(PostgresqlClass, rdbms_common.IssueClass):
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
308 pass
3685
4d9adb8bc3b1 Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3659
diff changeset
309 class FileClass(PostgresqlClass, rdbms_common.FileClass):
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
310 pass
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
311
3920
416606b09b27 fix vim modelines
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3918
diff changeset
312 # vim: set et sts=4 sw=4 :
Roundup Issue Tracker: http://roundup-tracker.org/