Mercurial > p > roundup > code
annotate roundup/backends/back_postgresql.py @ 5096:e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
Also adds new config.ini setting called indexer to select
indexer. See ``doc/upgrading.txt`` for details. Initial patch
done by David Wolever. Patch modified (see ticket or below for
changes), docs updated and committed.
I have an outstanding issue with test/test_indexer.py. I have to
comment out all imports and tests for indexers I don't have (i.e.
mysql, postgres) otherwise no tests run.
With that change made, dbm, sqlite (rdbms), xapian and whoosh indexes
are all passing the indexer tests.
Changes summary:
1) support native back ends dbm and rdbms. (original patch only fell
through to dbm)
2) Developed whoosh stopfilter to not index stopwords or words outside
the the maxlength and minlength limits defined in index_common.py.
Required to pass the extremewords test_indexer test. Also I
removed a call to .lower on the input text as the tokenizer I chose
automatically does the lowercase.
3) Added support for max/min length to find. This was needed to pass
extremewords test.
4) Added back a call to save_index in add_text. This allowed all but
two tests to pass.
5) Fixed a call to:
results = searcher.search(query.Term("identifier", identifier))
which had an extra parameter that is an error under current whoosh.
6) Set limit=None in search call for find() otherwise it only return
10 items. This allowed it to pass manyresults test
Also due to changes in the roundup code removed the call in
indexer_whoosh to
from roundup.anypy.sets_ import set
since we use the python builtin set.
| author | John Rouillard <rouilj@ieee.org> |
|---|---|
| date | Sat, 25 Jun 2016 20:10:03 -0400 |
| parents | 05c857e5dbed |
| children | 57452bc6d989 |
| 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 # |
|
2005
fc52d57c6c3e
documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents:
1920
diff
changeset
|
7 '''Postgresql backend via psycopg for Roundup.''' |
|
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 |
|
3659
bfb120928309
update for latest version of psycopg2 (patch-ish [SF#429391])
Richard Jones <richard@users.sourceforge.net>
parents:
3475
diff
changeset
|
15 try: |
|
bfb120928309
update for latest version of psycopg2 (patch-ish [SF#429391])
Richard Jones <richard@users.sourceforge.net>
parents:
3475
diff
changeset
|
16 import psycopg |
|
3715
50add98cbbac
change as per last message in patch [SF#1429391]
Richard Jones <richard@users.sourceforge.net>
parents:
3687
diff
changeset
|
17 from psycopg import QuotedString |
|
3918
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
18 from psycopg import ProgrammingError |
|
4887
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
19 TransactionRollbackError = ProgrammingError |
|
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
20 try: |
|
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
21 from psycopg.extensions import ISOLATION_LEVEL_READ_UNCOMMITTED |
|
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
22 from psycopg.extensions import ISOLATION_LEVEL_READ_COMMITTED |
|
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
23 from psycopg.extensions import ISOLATION_LEVEL_REPEATABLE_READ |
|
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
24 from psycopg.extensions import ISOLATION_LEVEL_SERIALIZABLE |
|
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
25 except ImportError: |
|
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
26 pass |
|
3659
bfb120928309
update for latest version of psycopg2 (patch-ish [SF#429391])
Richard Jones <richard@users.sourceforge.net>
parents:
3475
diff
changeset
|
27 except: |
|
3715
50add98cbbac
change as per last message in patch [SF#1429391]
Richard Jones <richard@users.sourceforge.net>
parents:
3687
diff
changeset
|
28 from psycopg2 import psycopg1 as psycopg |
|
50add98cbbac
change as per last message in patch [SF#1429391]
Richard Jones <richard@users.sourceforge.net>
parents:
3687
diff
changeset
|
29 from psycopg2.extensions import QuotedString |
|
4887
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
30 from psycopg2.extensions import ISOLATION_LEVEL_READ_UNCOMMITTED |
|
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
31 from psycopg2.extensions import ISOLATION_LEVEL_READ_COMMITTED |
|
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
32 from psycopg2.extensions import ISOLATION_LEVEL_REPEATABLE_READ |
|
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
33 from psycopg2.extensions import ISOLATION_LEVEL_SERIALIZABLE |
|
3918
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
34 from psycopg2.psycopg1 import ProgrammingError |
|
4887
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
35 from psycopg2.extensions import TransactionRollbackError |
|
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
|
36 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
|
37 |
|
1911
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
38 from roundup import hyperdb, date |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
39 from roundup.backends import rdbms_common |
|
3918
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
40 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
|
41 |
|
4887
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
42 isolation_levels = \ |
|
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
43 { 'read uncommitted': ISOLATION_LEVEL_READ_COMMITTED |
|
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
44 , 'read committed': ISOLATION_LEVEL_READ_COMMITTED |
|
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
45 , 'repeatable read': ISOLATION_LEVEL_REPEATABLE_READ |
|
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
46 , 'serializable': ISOLATION_LEVEL_SERIALIZABLE |
|
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
47 } |
|
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
48 |
|
3099
519b92df37dc
handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents:
3088
diff
changeset
|
49 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
|
50 ''' 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
|
51 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
|
52 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
|
53 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
|
54 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
|
55 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
|
56 return d |
|
519b92df37dc
handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents:
3088
diff
changeset
|
57 |
|
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 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
|
59 """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
|
60 command = "CREATE DATABASE \"%s\" WITH ENCODING='UNICODE'"%config.RDBMS_NAME |
|
4471
4f353d71d716
Configuration issue:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4421
diff
changeset
|
61 if config.RDBMS_TEMPLATE : |
|
4f353d71d716
Configuration issue:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4421
diff
changeset
|
62 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
|
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) |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
65 |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
66 def db_nuke(config, fail_ok=0): |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
67 """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
|
68 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
|
69 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
|
70 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
|
71 |
|
2256
0b198ed096af
fixes for py2.1 (booleans, sigh)
Richard Jones <richard@users.sourceforge.net>
parents:
2243
diff
changeset
|
72 if os.path.exists(config.DATABASE): |
|
0b198ed096af
fixes for py2.1 (booleans, sigh)
Richard Jones <richard@users.sourceforge.net>
parents:
2243
diff
changeset
|
73 shutil.rmtree(config.DATABASE) |
|
0b198ed096af
fixes for py2.1 (booleans, sigh)
Richard Jones <richard@users.sourceforge.net>
parents:
2243
diff
changeset
|
74 |
|
4514
f6c49df25048
PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents:
4471
diff
changeset
|
75 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
|
76 '''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
|
77 fail by conflicting with another user. |
|
4514
f6c49df25048
PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents:
4471
diff
changeset
|
78 |
|
f6c49df25048
PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents:
4471
diff
changeset
|
79 Since PostgreSQL version 8.1 there is a database "postgres", |
|
f6c49df25048
PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents:
4471
diff
changeset
|
80 before "template1" seems to habe been used, so we fall back to it. |
|
f6c49df25048
PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents:
4471
diff
changeset
|
81 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
|
82 ''' |
|
3099
519b92df37dc
handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents:
3088
diff
changeset
|
83 template1 = connection_dict(config) |
|
4514
f6c49df25048
PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents:
4471
diff
changeset
|
84 template1['database'] = database |
|
3918
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
85 |
|
2243
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
86 try: |
|
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
87 conn = psycopg.connect(**template1) |
|
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
88 except psycopg.OperationalError, message: |
|
4514
f6c49df25048
PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents:
4471
diff
changeset
|
89 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
|
90 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
|
91 raise hyperdb.DatabaseError(message) |
|
3918
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
92 |
|
2243
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
93 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
|
94 cursor = conn.cursor() |
|
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
95 try: |
|
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
96 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
|
97 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
|
98 return |
|
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
99 finally: |
|
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
100 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
|
101 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
|
102 |
|
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
103 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
|
104 '''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
|
105 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
|
106 |
|
3717
5770f1802cd0
better conflict retry in postgresql backend [SF#1552809]
Richard Jones <richard@users.sourceforge.net>
parents:
3715
diff
changeset
|
107 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
|
108 ''' |
|
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
109 try: |
|
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
110 cursor.execute(command) |
|
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
111 except psycopg.ProgrammingError, err: |
|
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
112 response = str(err).split('\n')[0] |
|
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
113 if response.find('FATAL') != -1: |
|
4357
13b3155869e0
Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents:
4239
diff
changeset
|
114 raise RuntimeError(response) |
|
3863
1d47f70a99bf
Seems like not all versions of postgres-drivers contain the string "ERROR"...
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3858
diff
changeset
|
115 else: |
|
3717
5770f1802cd0
better conflict retry in postgresql backend [SF#1552809]
Richard Jones <richard@users.sourceforge.net>
parents:
3715
diff
changeset
|
116 msgs = [ |
|
5770f1802cd0
better conflict retry in postgresql backend [SF#1552809]
Richard Jones <richard@users.sourceforge.net>
parents:
3715
diff
changeset
|
117 '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
|
118 'could not serialize access due to concurrent update', |
|
5770f1802cd0
better conflict retry in postgresql backend [SF#1552809]
Richard Jones <richard@users.sourceforge.net>
parents:
3715
diff
changeset
|
119 ] |
|
5770f1802cd0
better conflict retry in postgresql backend [SF#1552809]
Richard Jones <richard@users.sourceforge.net>
parents:
3715
diff
changeset
|
120 can_retry = 0 |
|
5770f1802cd0
better conflict retry in postgresql backend [SF#1552809]
Richard Jones <richard@users.sourceforge.net>
parents:
3715
diff
changeset
|
121 for msg in msgs: |
|
5770f1802cd0
better conflict retry in postgresql backend [SF#1552809]
Richard Jones <richard@users.sourceforge.net>
parents:
3715
diff
changeset
|
122 if response.find(msg) == -1: |
|
5770f1802cd0
better conflict retry in postgresql backend [SF#1552809]
Richard Jones <richard@users.sourceforge.net>
parents:
3715
diff
changeset
|
123 can_retry = 1 |
|
5770f1802cd0
better conflict retry in postgresql backend [SF#1552809]
Richard Jones <richard@users.sourceforge.net>
parents:
3715
diff
changeset
|
124 if can_retry: |
|
5770f1802cd0
better conflict retry in postgresql backend [SF#1552809]
Richard Jones <richard@users.sourceforge.net>
parents:
3715
diff
changeset
|
125 time.sleep(1) |
|
5770f1802cd0
better conflict retry in postgresql backend [SF#1552809]
Richard Jones <richard@users.sourceforge.net>
parents:
3715
diff
changeset
|
126 return 0 |
|
4357
13b3155869e0
Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents:
4239
diff
changeset
|
127 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
|
128 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
|
129 |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
130 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
|
131 """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
|
132 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
|
133 try: |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
134 conn = psycopg.connect(**db) |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
135 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
|
136 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
|
137 except: |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
138 return 0 |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
139 |
|
3918
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
140 class Sessions(sessions_rdbms.Sessions): |
|
3924
21d3d7eeea8c
assorted pyflakes fixes
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3920
diff
changeset
|
141 def set(self, *args, **kwargs): |
|
3918
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
142 try: |
|
3927
97ae174f7a0e
add self to Sessions.set
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3924
diff
changeset
|
143 sessions_rdbms.Sessions.set(self, *args, **kwargs) |
|
3918
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
144 except ProgrammingError, err: |
|
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
145 response = str(err).split('\n')[0] |
|
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
146 if -1 != response.find('ERROR') and \ |
|
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
147 -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
|
148 # 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
|
149 # serializable isolation. |
|
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
150 # 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
|
151 self.db.rollback() |
|
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
152 |
|
1911
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
153 class Database(rdbms_common.Database): |
|
5096
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
4887
diff
changeset
|
154 """Postgres DB backend implementation |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
4887
diff
changeset
|
155 |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
4887
diff
changeset
|
156 attributes: |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
4887
diff
changeset
|
157 dbtype: |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
4887
diff
changeset
|
158 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
|
159 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
|
160 module when using native text search mode. |
|
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 |
|
3048
d9b4224f955c
merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents:
2745
diff
changeset
|
167 # 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
|
168 implements_intersect = 1 |
|
d9b4224f955c
merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents:
2745
diff
changeset
|
169 |
|
3918
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
170 def getSessionManager(self): |
|
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
171 return Sessions(self) |
|
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
172 |
|
1911
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
173 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
|
174 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
|
175 logging.getLogger('roundup.hyperdb').info( |
|
67bef70ab9b9
- more logger fixes, sorry for the noise.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4420
diff
changeset
|
176 '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
|
177 try: |
|
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
178 conn = psycopg.connect(**db) |
|
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
179 except psycopg.OperationalError, message: |
|
4357
13b3155869e0
Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents:
4239
diff
changeset
|
180 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
|
181 |
|
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
182 cursor = conn.cursor() |
|
4887
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
183 if ISOLATION_LEVEL_REPEATABLE_READ is not None: |
|
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
184 lvl = isolation_levels [self.config.RDBMS_ISOLATION_LEVEL] |
|
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
185 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
|
186 |
|
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
187 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
|
188 |
|
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
189 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
|
190 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
|
191 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
|
192 |
|
2082
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
193 self.conn, self.cursor = self.sql_open_connection() |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
194 |
|
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
195 try: |
|
2073
261c2e6ceb1e
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2005
diff
changeset
|
196 self.load_dbschema() |
|
2736
402d6d556558
postgres backend open doesn't hide corruption in schema [SF#956375]
Richard Jones <richard@users.sourceforge.net>
parents:
2721
diff
changeset
|
197 except psycopg.ProgrammingError, message: |
|
3739
f1363e19121a
fix error detection for non-EN locales of postgres [SF#1592249]
Richard Jones <richard@users.sourceforge.net>
parents:
3717
diff
changeset
|
198 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
|
199 raise |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
200 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
|
201 self.init_dbschema() |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
202 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
|
203 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
|
204 self.sql("insert into dual values (1)") |
|
2073
261c2e6ceb1e
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2005
diff
changeset
|
205 self.create_version_2_tables() |
|
261c2e6ceb1e
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2005
diff
changeset
|
206 |
|
261c2e6ceb1e
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2005
diff
changeset
|
207 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
|
208 # OTK store |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
209 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
|
210 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
|
211 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
|
212 |
|
3f6024ab2c7a
That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents:
2089
diff
changeset
|
213 # Sessions store |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
214 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
|
215 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
|
216 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
|
217 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
|
218 sessions(session_key)''') |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
219 |
|
2093
3f6024ab2c7a
That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents:
2089
diff
changeset
|
220 # 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
|
221 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
|
222 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
|
223 _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
|
224 _itemid VARCHAR(255), _prop VARCHAR(255))''') |
|
3918
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
225 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
|
226 _textid integer)''') |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
227 self.sql('CREATE INDEX words_word_idx ON __words(_word)') |
|
3858
bb30bbfc7cdd
Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3739
diff
changeset
|
228 self.sql('CREATE INDEX words_by_id ON __words (_textid)') |
|
bb30bbfc7cdd
Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3739
diff
changeset
|
229 self.sql('CREATE UNIQUE INDEX __textids_by_props ON ' |
|
bb30bbfc7cdd
Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3739
diff
changeset
|
230 '__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
|
231 |
|
2413
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2256
diff
changeset
|
232 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
|
233 # 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
|
234 self._convert_journal_tables() |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2256
diff
changeset
|
235 |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2256
diff
changeset
|
236 # 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
|
237 self._convert_string_properties() |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2256
diff
changeset
|
238 |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2256
diff
changeset
|
239 # 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
|
240 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
|
241 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
|
242 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
|
243 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
|
244 %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
|
245 name)) |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
246 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
|
247 name)) |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2256
diff
changeset
|
248 |
|
2745
b284ff7af3fb
add optimised index on postgresql text index table
Richard Jones <richard@users.sourceforge.net>
parents:
2736
diff
changeset
|
249 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
|
250 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
|
251 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
|
252 USING btree (_word, _textid)''') |
|
b284ff7af3fb
add optimised index on postgresql text index table
Richard Jones <richard@users.sourceforge.net>
parents:
2736
diff
changeset
|
253 |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
254 def add_actor_column(self): |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
255 # update existing tables to have the new actor column |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
256 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
|
257 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
|
258 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
|
259 |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
260 def __repr__(self): |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
261 return '<roundpsycopgsql 0x%x>' % id(self) |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
262 |
|
3687
ff9f4ca42454
Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents:
3685
diff
changeset
|
263 def sql_commit(self, fail_ok=False): |
|
ff9f4ca42454
Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents:
3685
diff
changeset
|
264 ''' Actually commit to the database. |
|
ff9f4ca42454
Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents:
3685
diff
changeset
|
265 ''' |
|
4421
67bef70ab9b9
- more logger fixes, sorry for the noise.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4420
diff
changeset
|
266 logging.getLogger('roundup.hyperdb').info('commit') |
|
3687
ff9f4ca42454
Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents:
3685
diff
changeset
|
267 |
|
ff9f4ca42454
Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents:
3685
diff
changeset
|
268 try: |
|
ff9f4ca42454
Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents:
3685
diff
changeset
|
269 self.conn.commit() |
|
ff9f4ca42454
Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents:
3685
diff
changeset
|
270 except psycopg.ProgrammingError, message: |
|
ff9f4ca42454
Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents:
3685
diff
changeset
|
271 # we've been instructed that this commit is allowed to fail |
|
ff9f4ca42454
Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents:
3685
diff
changeset
|
272 if fail_ok and str(message).endswith('could not serialize ' |
|
ff9f4ca42454
Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents:
3685
diff
changeset
|
273 'access due to concurrent update'): |
|
4421
67bef70ab9b9
- more logger fixes, sorry for the noise.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4420
diff
changeset
|
274 logging.getLogger('roundup.hyperdb').info( |
|
67bef70ab9b9
- more logger fixes, sorry for the noise.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4420
diff
changeset
|
275 'commit FAILED, but fail_ok') |
|
3687
ff9f4ca42454
Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents:
3685
diff
changeset
|
276 else: |
|
ff9f4ca42454
Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents:
3685
diff
changeset
|
277 raise |
|
ff9f4ca42454
Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents:
3685
diff
changeset
|
278 |
|
ff9f4ca42454
Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents:
3685
diff
changeset
|
279 # open a new cursor for subsequent work |
|
ff9f4ca42454
Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents:
3685
diff
changeset
|
280 self.cursor = self.conn.cursor() |
|
ff9f4ca42454
Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents:
3685
diff
changeset
|
281 |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
282 def sql_stringquote(self, value): |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
283 ''' psycopg.QuotedString returns a "buffer" object with the |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
284 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
|
285 return str(QuotedString(str(value)))[1:-1] |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
286 |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
287 def sql_index_exists(self, table_name, index_name): |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
288 sql = 'select count(*) from pg_indexes where ' \ |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
289 '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
|
290 self.sql(sql, (table_name, index_name)) |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
291 return self.cursor.fetchone()[0] |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
292 |
|
2424
74474ec41050
argh! backwards compat
Richard Jones <richard@users.sourceforge.net>
parents:
2413
diff
changeset
|
293 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
|
294 if create_sequence: |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2256
diff
changeset
|
295 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
|
296 self.sql(sql) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
297 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
298 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
|
299 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
300 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
|
301 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
|
302 self.sql(sql) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
303 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
304 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
|
305 self.sql(sql) |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
306 |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
307 def newid(self, classname): |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
308 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
|
309 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
|
310 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
|
311 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
312 def setid(self, classname, setid): |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
313 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
|
314 self.sql(sql) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
315 |
|
3310
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3155
diff
changeset
|
316 def clear(self): |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3155
diff
changeset
|
317 rdbms_common.Database.clear(self) |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3155
diff
changeset
|
318 |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3155
diff
changeset
|
319 # 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
|
320 for cn in self.classes: |
|
3310
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3155
diff
changeset
|
321 self.cursor.execute('DROP SEQUENCE _%s_ids'%cn) |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3155
diff
changeset
|
322 self.cursor.execute('CREATE SEQUENCE _%s_ids'%cn) |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3155
diff
changeset
|
323 |
|
3685
4d9adb8bc3b1
Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3659
diff
changeset
|
324 class PostgresqlClass: |
|
4d9adb8bc3b1
Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3659
diff
changeset
|
325 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
|
326 case_insensitive_like = 'ILIKE' |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
327 |
|
3685
4d9adb8bc3b1
Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3659
diff
changeset
|
328 class Class(PostgresqlClass, rdbms_common.Class): |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
329 pass |
|
3685
4d9adb8bc3b1
Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3659
diff
changeset
|
330 class IssueClass(PostgresqlClass, rdbms_common.IssueClass): |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
331 pass |
|
3685
4d9adb8bc3b1
Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3659
diff
changeset
|
332 class FileClass(PostgresqlClass, rdbms_common.FileClass): |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
333 pass |
|
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
334 |
|
3920
416606b09b27
fix vim modelines
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3918
diff
changeset
|
335 # vim: set et sts=4 sw=4 : |
