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
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 #
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 :

Roundup Issue Tracker: http://roundup-tracker.org/