Mercurial > p > roundup > code

comparison test/test_postgresql.py @ 7719:3071db43bfb6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
feat: issue2550852 - support using a specified PostgreSQL db schema Finally after 7 years this is closed. roundup/backends/back_postgresql.py: Support use of schema when specified in RDBMS_NAME. Stuart McGraws code is finally merged 8-). test/test_postgresql.py, test/conftest.py: Run all postgresql tests in the schema db as well. Also make sure that db_nuke raises an error when trying to delete the schema test database. Conftest defines pg_schema mark that can be used to exclude schema tests with pytest -m "not pg_schema". roundup/configuration.py: change doc on RDBMS_NAME to include db.schema form. .travis.yml, .github/workflows/ci-test.yml: create schema test db; add user for testing with schema; grant new user create privs for schema. doc/installation.txt: Reference to roundup-admin init deleting schema added. doc/mysql.txt doc/postgresql.txt: New documentation on psql/mysql commands to set up a production db. doc/upgrading.txt: mention schema support, also document service setting for selecting connection from pg_service.conf. doc/reference.txt: update config.ini documentation for RDBMS_NAME.
author John Rouillard <rouilj@ieee.org>
date Wed, 27 Dec 2023 22:52:14 -0500
parents 521d98231e5c
children 8147f6deac9f
comparison
equal deleted inserted replaced
7718:3da452f4a3ac 7719:3071db43bfb6
18 import unittest 18 import unittest
19 19
20 import pytest 20 import pytest
21 from roundup.hyperdb import DatabaseError 21 from roundup.hyperdb import DatabaseError
22 from roundup.backends import get_backend, have_backend 22 from roundup.backends import get_backend, have_backend
23 from roundup.backends.back_postgresql import db_command, db_schema_split
23 24
24 from .db_test_base import DBTest, ROTest, config, SchemaTest, ClassicInitTest 25 from .db_test_base import DBTest, ROTest, config, SchemaTest, ClassicInitTest
25 from .db_test_base import ConcurrentDBTest, HTMLItemTest, FilterCacheTest 26 from .db_test_base import ConcurrentDBTest, HTMLItemTest, FilterCacheTest
26 from .db_test_base import ClassicInitBase, setupTracker, SpecialActionTest 27 from .db_test_base import ClassicInitBase, setupTracker, SpecialActionTest
27 from .rest_common import TestCase as RestTestCase 28 from .rest_common import TestCase as RestTestCase
50 def setup_class(cls): 51 def setup_class(cls):
51 # nuke the db once for the class. Handles the case 52 # nuke the db once for the class. Handles the case
52 # where an aborted test run (^C during setUp for example) 53 # where an aborted test run (^C during setUp for example)
53 # leaves the database in an unusable, partly configured state. 54 # leaves the database in an unusable, partly configured state.
54 try: 55 try:
55 cls.nuke_database(cls) 56 cls.nuke_database()
56 except: 57 except Exception as m:
57 # ignore failure to nuke the database. 58 # ignore failure to nuke the database if it doesn't exist.
58 pass 59 # otherwise abort
60 if str(m.args[0]) == (
61 'database "%s" does not exist' % config.RDBMS_NAME):
62 pass
63 else:
64 raise
59 65
60 def setUp(self): 66 def setUp(self):
61 pass 67 pass
62 68
63 def tearDown(self): 69 def tearDown(self):
64 self.nuke_database() 70 self.nuke_database()
65 71
72 @classmethod
66 def nuke_database(self): 73 def nuke_database(self):
67 # clear out the database - easiest way is to nuke and re-create it 74 # clear out the database - easiest way is to nuke and re-create it
68 self.module.db_nuke(config) 75 self.module.db_nuke(config)
69 76
70 77 @skip_postgresql
71 @skip_postgresql 78 class postgresqlSchemaOpener:
72 class postgresqlDBTest(postgresqlOpener, DBTest, unittest.TestCase): 79
73 def setUp(self): 80 RDBMS_NAME="rounduptest_schema.rounduptest"
74 # set for manual integration testing of 'native-fts' 81 RDBMS_USER="rounduptest_schema"
75 # It is unset in tearDown so it doesn't leak into other tests. 82
76 # FIXME extract test methods in DBTest that hit the indexer 83 if have_backend('postgresql'):
77 # into a new class (DBTestIndexer). Add DBTestIndexer 84 module = get_backend('postgresql')
78 # to this class. 85
79 # Then create a new class in this file: 86 def setup_class(cls):
80 # postgresqlDBTestIndexerNative_FTS 87 # nuke the schema for the class. Handles the case
81 # that imports from DBestIndexer to test native-fts. 88 # where an aborted test run (^C during setUp for example)
82 # config['INDEXER'] = 'native-fts' 89 # leaves the database in an unusable, partly configured state.
83 postgresqlOpener.setUp(self) 90 config.RDBMS_NAME=cls.RDBMS_NAME
84 DBTest.setUp(self) 91 config.RDBMS_USER=cls.RDBMS_USER
85 92
86 def tearDown(self): 93 database, schema = db_schema_split(config.RDBMS_NAME)
87 # clean up config to prevent leak if native-fts is tested 94
88 config['INDEXER'] = '' 95 try:
89 DBTest.tearDown(self) 96 cls.nuke_database()
90 postgresqlOpener.tearDown(self) 97 except Exception as m:
91 98 # ignore failure to nuke the database if it doesn't exist.
99 # otherwise abort
100 if str(m.args[0]) == (
101 'schema "%s" does not exist' % schema):
102 pass
103 else:
104 raise
105
106 def setUp(self):
107 # make sure to override the rdbms settings.
108 # before every test.
109 config.RDBMS_NAME=self.RDBMS_NAME
110 config.RDBMS_USER=self.RDBMS_USER
111
112 def tearDown(self):
113 self.nuke_database()
114 config.RDBMS_NAME="rounduptest"
115 config.RDBMS_USER="rounduptest"
116
117 @classmethod
118 def nuke_database(self):
119 # clear out the database - easiest way is to nuke and re-create it
120 self.module.db_nuke(config)
121
122 @skip_postgresql
123 class postgresqlPrecreatedSchemaDbOpener:
124 """Open the db where the user has only schema create rights.
125 The test tries to nuke the db and should result in an exception.
126
127 RDBMS_NAME should not have the .schema on it as we want to
128 operate on the db itself with db_nuke.
129 """
130
131 RDBMS_NAME="rounduptest_schema"
132 RDBMS_USER="rounduptest_schema"
133
134 if have_backend('postgresql'):
135 module = get_backend('postgresql')
136
137 def setup_class(cls):
138 # nuke the schema for the class. Handles the case
139 # where an aborted test run (^C during setUp for example)
140 # leaves the database in an unusable, partly configured state.
141 config.RDBMS_NAME=cls.RDBMS_NAME
142 config.RDBMS_USER=cls.RDBMS_USER
143
144 def setUp(self):
145 # make sure to override the rdbms settings.
146 # before every test.
147 config.RDBMS_NAME=self.RDBMS_NAME
148 config.RDBMS_USER=self.RDBMS_USER
149
150 def tearDown(self):
151 config.RDBMS_NAME="rounduptest"
152 config.RDBMS_USER="rounduptest"
153
154 @classmethod
155 def nuke_database(self):
156 # clear out the database - easiest way is to nuke and re-create it
157 self.module.db_nuke(config)
158
159 @skip_postgresql
160 class postgresqlAdditionalDBTest():
92 def testUpgrade_6_to_7(self): 161 def testUpgrade_6_to_7(self):
93 162
94 # load the database 163 # load the database
95 self.db.issue.create(title="flebble frooz") 164 self.db.issue.create(title="flebble frooz")
96 self.db.commit() 165 self.db.commit()
223 test_double, -1) 292 test_double, -1)
224 293
225 self.assertEqual(self.db.database_schema['version'], 8) 294 self.assertEqual(self.db.database_schema['version'], 8)
226 295
227 @skip_postgresql 296 @skip_postgresql
297 class postgresqlDBTest(postgresqlOpener, DBTest,
298 postgresqlAdditionalDBTest, unittest.TestCase):
299 def setUp(self):
300 # set for manual integration testing of 'native-fts'
301 # It is unset in tearDown so it doesn't leak into other tests.
302 # FIXME extract test methods in DBTest that hit the indexer
303 # into a new class (DBTestIndexer). Add DBTestIndexer
304 # to this class.
305 # Then create a new class in this file:
306 # postgresqlDBTestIndexerNative_FTS
307 # that imports from DBestIndexer to test native-fts.
308 # config['INDEXER'] = 'native-fts'
309 postgresqlOpener.setUp(self)
310 DBTest.setUp(self)
311
312 def tearDown(self):
313 # clean up config to prevent leak if native-fts is tested
314 config['INDEXER'] = ''
315 DBTest.tearDown(self)
316 postgresqlOpener.tearDown(self)
317
318 @skip_postgresql
319 @pytest.mark.pg_schema
320 class postgresqlDBTestSchema(postgresqlSchemaOpener, DBTest,
321 postgresqlAdditionalDBTest, unittest.TestCase):
322 def setUp(self):
323 # set for manual integration testing of 'native-fts'
324 # It is unset in tearDown so it doesn't leak into other tests.
325 # FIXME extract test methods in DBTest that hit the indexer
326 # into a new class (DBTestIndexer). Add DBTestIndexer
327 # to this class.
328 # Then create a new class in this file:
329 # postgresqlDBTestIndexerNative_FTS
330 # that imports from DBestIndexer to test native-fts.
331 # config['INDEXER'] = 'native-fts'
332 postgresqlSchemaOpener.setUp(self)
333 DBTest.setUp(self)
334
335 def tearDown(self):
336 # clean up config to prevent leak if native-fts is tested
337 config['INDEXER'] = ''
338 DBTest.tearDown(self)
339 postgresqlSchemaOpener.tearDown(self)
340
341
342 @skip_postgresql
228 class postgresqlROTest(postgresqlOpener, ROTest, unittest.TestCase): 343 class postgresqlROTest(postgresqlOpener, ROTest, unittest.TestCase):
229 def setUp(self): 344 def setUp(self):
230 postgresqlOpener.setUp(self) 345 postgresqlOpener.setUp(self)
231 ROTest.setUp(self) 346 ROTest.setUp(self)
232 347
233 def tearDown(self): 348 def tearDown(self):
234 ROTest.tearDown(self) 349 ROTest.tearDown(self)
235 postgresqlOpener.tearDown(self) 350 postgresqlOpener.tearDown(self)
351
352 @skip_postgresql
353 @pytest.mark.pg_schema
354 class postgresqlROTestSchema(postgresqlSchemaOpener, ROTest,
355 unittest.TestCase):
356 def setUp(self):
357 postgresqlSchemaOpener.setUp(self)
358 ROTest.setUp(self)
359
360 def tearDown(self):
361 ROTest.tearDown(self)
362 postgresqlSchemaOpener.tearDown(self)
236 363
237 364
238 @skip_postgresql 365 @skip_postgresql
239 class postgresqlConcurrencyTest(postgresqlOpener, ConcurrentDBTest, 366 class postgresqlConcurrencyTest(postgresqlOpener, ConcurrentDBTest,
240 unittest.TestCase): 367 unittest.TestCase):
245 372
246 def tearDown(self): 373 def tearDown(self):
247 ConcurrentDBTest.tearDown(self) 374 ConcurrentDBTest.tearDown(self)
248 postgresqlOpener.tearDown(self) 375 postgresqlOpener.tearDown(self)
249 376
250 377 @skip_postgresql
251 @skip_postgresql 378 @pytest.mark.pg_schema
252 class postgresqlJournalTest(postgresqlOpener, ClassicInitBase, 379 class postgresqlConcurrencyTestSchema(postgresqlSchemaOpener, ConcurrentDBTest,
253 unittest.TestCase): 380 unittest.TestCase):
254 backend = 'postgresql' 381 backend = 'postgresql'
255 def setUp(self): 382 def setUp(self):
256 postgresqlOpener.setUp(self) 383 postgresqlSchemaOpener.setUp(self)
257 ClassicInitBase.setUp(self) 384 ConcurrentDBTest.setUp(self)
258 self.tracker = setupTracker(self.dirname, self.backend) 385
259 db = self.tracker.open('admin') 386 def tearDown(self):
260 self.id = db.issue.create(title='initial value') 387 ConcurrentDBTest.tearDown(self)
261 db.commit() 388 postgresqlSchemaOpener.tearDown(self)
262 db.close() 389
263 390
264 def tearDown(self): 391
265 try: 392 @skip_postgresql
266 self.db1.close() 393 class postgresqlAdditionalJournalTest():
267 self.db2.close()
268 except psycopg2.InterfaceError as exc:
269 if 'connection already closed' in str(exc): pass
270 else: raise
271 ClassicInitBase.tearDown(self)
272 postgresqlOpener.tearDown(self)
273 394
274 def _test_journal(self, expected_journal): 395 def _test_journal(self, expected_journal):
275 id = self.id 396 id = self.id
276 db1 = self.db1 = self.tracker.open('admin') 397 db1 = self.db1 = self.tracker.open('admin')
277 db2 = self.db2 = self.tracker.open('admin') 398 db2 = self.db2 = self.tracker.open('admin')
306 def testConcurrentRepeatableRead(self): 427 def testConcurrentRepeatableRead(self):
307 self.tracker.config.RDBMS_ISOLATION_LEVEL='repeatable read' 428 self.tracker.config.RDBMS_ISOLATION_LEVEL='repeatable read'
308 exc = self.module.TransactionRollbackError 429 exc = self.module.TransactionRollbackError
309 self.assertRaises(exc, self._test_journal, []) 430 self.assertRaises(exc, self._test_journal, [])
310 431
432 @skip_postgresql
433 class postgresqlJournalTest(postgresqlOpener, ClassicInitBase,
434 postgresqlAdditionalJournalTest,
435 unittest.TestCase):
436 backend = 'postgresql'
437 def setUp(self):
438 postgresqlOpener.setUp(self)
439 ClassicInitBase.setUp(self)
440 self.tracker = setupTracker(self.dirname, self.backend)
441 db = self.tracker.open('admin')
442 self.id = db.issue.create(title='initial value')
443 db.commit()
444 db.close()
445
446 def tearDown(self):
447 try:
448 self.db1.close()
449 self.db2.close()
450 except psycopg2.InterfaceError as exc:
451 if 'connection already closed' in str(exc): pass
452 else: raise
453 ClassicInitBase.tearDown(self)
454 postgresqlOpener.tearDown(self)
455
456
457 @skip_postgresql
458 @pytest.mark.pg_schema
459 class postgresqlJournalTestSchema(postgresqlSchemaOpener, ClassicInitBase,
460 postgresqlAdditionalJournalTest,
461 unittest.TestCase):
462 backend = 'postgresql'
463 def setUp(self):
464 postgresqlSchemaOpener.setUp(self)
465 ClassicInitBase.setUp(self)
466 self.tracker = setupTracker(self.dirname, self.backend)
467 db = self.tracker.open('admin')
468 self.id = db.issue.create(title='initial value')
469 db.commit()
470 db.close()
471
472 def tearDown(self):
473 try:
474 self.db1.close()
475 self.db2.close()
476 except psycopg2.InterfaceError as exc:
477 if 'connection already closed' in str(exc): pass
478 else: raise
479 ClassicInitBase.tearDown(self)
480 postgresqlSchemaOpener.tearDown(self)
481
311 482
312 @skip_postgresql 483 @skip_postgresql
313 class postgresqlHTMLItemTest(postgresqlOpener, HTMLItemTest, 484 class postgresqlHTMLItemTest(postgresqlOpener, HTMLItemTest,
314 unittest.TestCase): 485 unittest.TestCase):
315 backend = 'postgresql' 486 backend = 'postgresql'
321 HTMLItemTest.tearDown(self) 492 HTMLItemTest.tearDown(self)
322 postgresqlOpener.tearDown(self) 493 postgresqlOpener.tearDown(self)
323 494
324 495
325 @skip_postgresql 496 @skip_postgresql
497 @pytest.mark.pg_schema
498 class postgresqlHTMLItemTestSchema(postgresqlSchemaOpener, HTMLItemTest,
499 unittest.TestCase):
500 backend = 'postgresql'
501 def setUp(self):
502 postgresqlSchemaOpener.setUp(self)
503 HTMLItemTest.setUp(self)
504
505 def tearDown(self):
506 HTMLItemTest.tearDown(self)
507 postgresqlSchemaOpener.tearDown(self)
508
509
510 @skip_postgresql
326 class postgresqlFilterCacheTest(postgresqlOpener, FilterCacheTest, 511 class postgresqlFilterCacheTest(postgresqlOpener, FilterCacheTest,
327 unittest.TestCase): 512 unittest.TestCase):
328 backend = 'postgresql' 513 backend = 'postgresql'
329 def setUp(self): 514 def setUp(self):
330 postgresqlOpener.setUp(self) 515 postgresqlOpener.setUp(self)
332 517
333 def tearDown(self): 518 def tearDown(self):
334 FilterCacheTest.tearDown(self) 519 FilterCacheTest.tearDown(self)
335 postgresqlOpener.tearDown(self) 520 postgresqlOpener.tearDown(self)
336 521
522 @skip_postgresql
523 @pytest.mark.pg_schema
524 class postgresqlFilterCacheTestSchema(postgresqlSchemaOpener, FilterCacheTest,
525 unittest.TestCase):
526 backend = 'postgresql'
527 def setUp(self):
528 postgresqlSchemaOpener.setUp(self)
529 FilterCacheTest.setUp(self)
530
531 def tearDown(self):
532 FilterCacheTest.tearDown(self)
533 postgresqlSchemaOpener.tearDown(self)
534
337 535
338 @skip_postgresql 536 @skip_postgresql
339 class postgresqlSchemaTest(postgresqlOpener, SchemaTest, unittest.TestCase): 537 class postgresqlSchemaTest(postgresqlOpener, SchemaTest, unittest.TestCase):
340 def setUp(self): 538 def setUp(self):
341 postgresqlOpener.setUp(self) 539 postgresqlOpener.setUp(self)
342 SchemaTest.setUp(self) 540 SchemaTest.setUp(self)
343 541
344 def tearDown(self): 542 def tearDown(self):
345 SchemaTest.tearDown(self) 543 SchemaTest.tearDown(self)
346 postgresqlOpener.tearDown(self) 544 postgresqlOpener.tearDown(self)
545
546
547 @skip_postgresql
548 @pytest.mark.pg_schema
549 class postgresqlSchemaTestSchema(postgresqlSchemaOpener, SchemaTest,
550 unittest.TestCase):
551 def setUp(self):
552 postgresqlSchemaOpener.setUp(self)
553 SchemaTest.setUp(self)
554
555 def tearDown(self):
556 SchemaTest.tearDown(self)
557 postgresqlSchemaOpener.tearDown(self)
347 558
348 559
349 @skip_postgresql 560 @skip_postgresql
350 class postgresqlClassicInitTest(postgresqlOpener, ClassicInitTest, 561 class postgresqlClassicInitTest(postgresqlOpener, ClassicInitTest,
351 unittest.TestCase): 562 unittest.TestCase):
357 def tearDown(self): 568 def tearDown(self):
358 ClassicInitTest.tearDown(self) 569 ClassicInitTest.tearDown(self)
359 postgresqlOpener.tearDown(self) 570 postgresqlOpener.tearDown(self)
360 571
361 572
573 @skip_postgresql
574 @pytest.mark.pg_schema
575 class postgresqlClassicInitTestSchema(postgresqlSchemaOpener, ClassicInitTest,
576 unittest.TestCase):
577 backend = 'postgresql'
578 def setUp(self):
579 postgresqlSchemaOpener.setUp(self)
580 ClassicInitTest.setUp(self)
581
582 def tearDown(self):
583 ClassicInitTest.tearDown(self)
584 postgresqlSchemaOpener.tearDown(self)
585
586
362 from .session_common import SessionTest 587 from .session_common import SessionTest
363 @skip_postgresql 588 @skip_postgresql
364 class postgresqlSessionTest(postgresqlOpener, SessionTest, unittest.TestCase): 589 class postgresqlSessionTest(postgresqlOpener, SessionTest, unittest.TestCase):
365 s2b = lambda x,y : y 590 s2b = lambda x,y : y
366 591
369 SessionTest.setUp(self) 594 SessionTest.setUp(self)
370 def tearDown(self): 595 def tearDown(self):
371 SessionTest.tearDown(self) 596 SessionTest.tearDown(self)
372 postgresqlOpener.tearDown(self) 597 postgresqlOpener.tearDown(self)
373 598
599
600 @skip_postgresql
601 @pytest.mark.pg_schema
602 class postgresqlSessionTestSchema(postgresqlSchemaOpener, SessionTest,
603 unittest.TestCase):
604 s2b = lambda x,y : y
605
606 def setUp(self):
607 postgresqlSchemaOpener.setUp(self)
608 SessionTest.setUp(self)
609 def tearDown(self):
610 SessionTest.tearDown(self)
611 postgresqlSchemaOpener.tearDown(self)
612
613
374 @skip_postgresql 614 @skip_postgresql
375 class postgresqlSpecialActionTestCase(postgresqlOpener, SpecialActionTest, 615 class postgresqlSpecialActionTestCase(postgresqlOpener, SpecialActionTest,
376 unittest.TestCase): 616 unittest.TestCase):
377 backend = 'postgresql' 617 backend = 'postgresql'
378 def setUp(self): 618 def setUp(self):
382 def tearDown(self): 622 def tearDown(self):
383 SpecialActionTest.tearDown(self) 623 SpecialActionTest.tearDown(self)
384 postgresqlOpener.tearDown(self) 624 postgresqlOpener.tearDown(self)
385 625
386 @skip_postgresql 626 @skip_postgresql
387 class postgresqlRestTest (RestTestCase, unittest.TestCase): 627 @pytest.mark.pg_schema
388 backend = 'postgresql' 628 class postgresqlSpecialActionTestCaseSchema(postgresqlSchemaOpener,
629 SpecialActionTest,
630 unittest.TestCase):
631 backend = 'postgresql'
632 def setUp(self):
633 postgresqlSchemaOpener.setUp(self)
634 SpecialActionTest.setUp(self)
635
636 def tearDown(self):
637 SpecialActionTest.tearDown(self)
638 postgresqlSchemaOpener.tearDown(self)
639
640 @skip_postgresql
641 class postgresqlRestTest (postgresqlOpener, RestTestCase, unittest.TestCase):
642 backend = 'postgresql'
643 def setUp(self):
644 postgresqlOpener.setUp(self)
645 RestTestCase.setUp(self)
646
647 def tearDown(self):
648 RestTestCase.tearDown(self)
649 postgresqlOpener.tearDown(self)
650
651
652 @skip_postgresql
653 @pytest.mark.pg_schema
654 class postgresqlRestTestSchema(postgresqlSchemaOpener, RestTestCase,
655 unittest.TestCase):
656 backend = 'postgresql'
657 def setUp(self):
658 postgresqlSchemaOpener.setUp(self)
659 RestTestCase.setUp(self)
660
661 def tearDown(self):
662 RestTestCase.tearDown(self)
663 postgresqlSchemaOpener.tearDown(self)
664
665
666 @skip_postgresql
667 @pytest.mark.pg_schema
668 class postgresqlDbDropFailureTestSchema(postgresqlPrecreatedSchemaDbOpener,
669 unittest.TestCase):
670
671 def test_drop(self):
672 """Verify that the schema test database can not be dropped."""
673
674 with self.assertRaises(RuntimeError) as m:
675 self.module.db_nuke(config)
676
677
678 self.assertEqual(m.exception.args[0],
679 'must be owner of database rounduptest_schema')
680
681
389 682
390 # vim: set et sts=4 sw=4 : 683 # vim: set et sts=4 sw=4 :
Roundup Issue Tracker: http://roundup-tracker.org/