Mercurial > p > roundup > code
comparison roundup/backends/back_postgresql.py @ 6604:0d99ae7c8de6
Allow Roundup to use PostgreSQL database native full text search
back_postgreql.py - schema version changes for schema version 7.
configuration.py - added indexer_language checks for postgresql. Hardcoded
list for now.
Docs admin_guide and upgrading
Tests.
This also restructures the version upgrade tests for the rdbms
backends. They can run all of them now as the proper cascade is
developed to roll back changes to version 6.
| author | John Rouillard <rouilj@ieee.org> |
|---|---|
| date | Thu, 27 Jan 2022 19:48:48 -0500 |
| parents | 39189dd94f2c |
| children | db3f0ba75b4a |
comparison
equal
deleted
inserted
replaced
| 6603:57dc15ad648d | 6604:0d99ae7c8de6 |
|---|---|
| 198 self.create_version_2_tables() | 198 self.create_version_2_tables() |
| 199 self.fix_version_3_tables() | 199 self.fix_version_3_tables() |
| 200 # Need to commit here, otherwise otk/session will not find | 200 # Need to commit here, otherwise otk/session will not find |
| 201 # the necessary tables (in a parallel connection!) | 201 # the necessary tables (in a parallel connection!) |
| 202 self.commit() | 202 self.commit() |
| 203 self._add_fts_table() | |
| 204 self.commit() | |
| 203 | 205 |
| 204 def checkpoint_data(self): | 206 def checkpoint_data(self): |
| 205 """Commit the state of the database. Allows recovery/retry | 207 """Commit the state of the database. Allows recovery/retry |
| 206 of operation in exception handler because postgres | 208 of operation in exception handler because postgres |
| 207 requires a rollback in case of error generating exception | 209 requires a rollback in case of error generating exception |
| 262 def fix_version_3_tables(self): | 264 def fix_version_3_tables(self): |
| 263 rdbms_common.Database.fix_version_3_tables(self) | 265 rdbms_common.Database.fix_version_3_tables(self) |
| 264 self.sql('''CREATE INDEX words_both_idx ON public.__words | 266 self.sql('''CREATE INDEX words_both_idx ON public.__words |
| 265 USING btree (_word, _textid)''') | 267 USING btree (_word, _textid)''') |
| 266 | 268 |
| 269 def _add_fts_table(self): | |
| 270 self.sql('CREATE TABLE __fts (_class VARCHAR(255), ' | |
| 271 '_itemid VARCHAR(255), _prop VARCHAR(255), _tsv tsvector)' | |
| 272 ) | |
| 273 | |
| 274 self.sql('CREATE INDEX __fts_idx ON __fts USING GIN (_tsv)') | |
| 275 | |
| 267 def fix_version_6_tables(self): | 276 def fix_version_6_tables(self): |
| 268 # Modify length for __words._word column. | 277 # Modify length for __words._word column. |
| 269 c = self.cursor | 278 c = self.cursor |
| 270 sql = 'alter table __words alter column _word type varchar(%s)' % ( | 279 sql = 'alter table __words alter column _word type varchar(%s)' % ( |
| 271 self.arg) | 280 self.arg) |
| 272 # Why magic number 5? It was the original offset between | 281 # Why magic number 5? It was the original offset between |
| 273 # column length and maxlength. | 282 # column length and maxlength. |
| 274 c.execute(sql, (self.indexer.maxlength + 5,)) | 283 c.execute(sql, (self.indexer.maxlength + 5,)) |
| 275 | 284 |
| 285 self._add_fts_table() | |
| 286 | |
| 276 def add_actor_column(self): | 287 def add_actor_column(self): |
| 277 # update existing tables to have the new actor column | 288 # update existing tables to have the new actor column |
| 278 tables = self.database_schema['tables'] | 289 tables = self.database_schema['tables'] |
| 279 for name in tables: | 290 for name in tables: |
| 280 self.sql('ALTER TABLE _%s add __actor VARCHAR(255)'%name) | 291 self.sql('ALTER TABLE _%s add __actor VARCHAR(255)'%name) |