Mercurial > p > roundup > code
comparison test/test_sqlite.py @ 6806:bdd28b244839
- issue2551223 - fix timestamp truncation in mysql and postgresql
The data types used to represent timestamps in pg and mysql for
ephemeral tables: sessions and otks don't have enough signifcant
digits to work. As a result the timestamps are rounduped (up/down)
rsuling in the stored timestamp being 2 minutes (pg) or 2-3
hours(mysql) off from what it should be.
Modify db schema to use a numeric type that preserves more significant
figures. Implement schema upgrade. Document need for upgrade in
upgrading.txt.
Write tests for schema upgrade.
Implement test for updateTimestamp method on BasicDatabase that showed
this issue in the first place. Write overrides for test for
anydbm/memorydb which store timestamp properly or not at all.
| author | John Rouillard <rouilj@ieee.org> |
|---|---|
| date | Mon, 25 Jul 2022 17:20:20 -0400 |
| parents | 044dcf3608a2 |
| children | 3f60a71b0812 |
comparison
equal
deleted
inserted
replaced
| 6805:09d9c646ca89 | 6806:bdd28b244839 |
|---|---|
| 17 | 17 |
| 18 import unittest, os, shutil, time | 18 import unittest, os, shutil, time |
| 19 import sqlite3 as sqlite | 19 import sqlite3 as sqlite |
| 20 | 20 |
| 21 from roundup.backends import get_backend, have_backend | 21 from roundup.backends import get_backend, have_backend |
| 22 from roundup.backends.sessions_sqlite import Sessions, OneTimeKeys | |
| 22 | 23 |
| 23 from .db_test_base import DBTest, ROTest, SchemaTest, ClassicInitTest, config | 24 from .db_test_base import DBTest, ROTest, SchemaTest, ClassicInitTest, config |
| 24 from .db_test_base import ConcurrentDBTest, FilterCacheTest | 25 from .db_test_base import ConcurrentDBTest, FilterCacheTest |
| 25 from .db_test_base import SpecialActionTest | 26 from .db_test_base import SpecialActionTest |
| 26 from .rest_common import TestCase as RestTestCase | 27 from .rest_common import TestCase as RestTestCase |
| 39 | 40 |
| 40 # load the database | 41 # load the database |
| 41 self.db.issue.create(title="flebble frooz") | 42 self.db.issue.create(title="flebble frooz") |
| 42 self.db.commit() | 43 self.db.commit() |
| 43 | 44 |
| 44 if self.db.database_schema['version'] != 7: | 45 if self.db.database_schema['version'] > 7: |
| 46 # make testUpgrades run the downgrade code only. | |
| 47 if hasattr(self, "downgrade_only"): | |
| 48 # we are being called by an earlier test | |
| 49 self.testUpgrade_7_to_8() | |
| 50 self.assertEqual(self.db.database_schema['version'], 7) | |
| 51 else: | |
| 52 # we are being called directly | |
| 53 self.downgrade_only = True | |
| 54 self.testUpgrade_7_to_8() | |
| 55 self.assertEqual(self.db.database_schema['version'], 7) | |
| 56 del(self.downgrade_only) | |
| 57 elif self.db.database_schema['version'] != 7: | |
| 45 self.skipTest("This test only runs for database version 7") | 58 self.skipTest("This test only runs for database version 7") |
| 46 | 59 |
| 47 self.db.database_schema['version'] = 6 | 60 self.db.database_schema['version'] = 6 |
| 48 | 61 |
| 49 # dropping _fts | 62 # dropping _fts |
| 69 self.db.sql("select * from __fts") | 82 self.db.sql("select * from __fts") |
| 70 | 83 |
| 71 # we should be at the current db version | 84 # we should be at the current db version |
| 72 self.assertEqual(self.db.database_schema['version'], | 85 self.assertEqual(self.db.database_schema['version'], |
| 73 self.db.current_db_version) | 86 self.db.current_db_version) |
| 87 | |
| 88 def testUpgrade_7_to_8(self): | |
| 89 # load the database | |
| 90 self.db.issue.create(title="flebble frooz") | |
| 91 self.db.commit() | |
| 92 | |
| 93 if self.db.database_schema['version'] != 8: | |
| 94 self.skipTest("This test only runs for database version 8") | |
| 95 | |
| 96 # set up separate session/otk db's. | |
| 97 self.db.Otk = OneTimeKeys(self.db) | |
| 98 self.db.Session = Sessions(self.db) | |
| 99 | |
| 100 handle={} | |
| 101 handle['otk'] = self.db.Otk | |
| 102 handle['session'] = self.db.Session | |
| 103 | |
| 104 # verify they don't truncate long ints. | |
| 105 test_double = 1658718284.7616878 | |
| 106 for tablename in ['otk', 'session']: | |
| 107 Bdb = handle[tablename] | |
| 108 Bdb.sql( | |
| 109 'insert into %(name)ss(%(name)s_key, %(name)s_time, %(name)s_value) ' | |
| 110 'values("foo", %(double)s, "value");'%{'name': tablename, | |
| 111 'double': test_double} | |
| 112 ) | |
| 113 | |
| 114 Bdb.cursor.execute('select %(name)s_time from %(name)ss ' | |
| 115 'where %(name)s_key = "foo"'%{'name': tablename}) | |
| 116 | |
| 117 self.assertAlmostEqual(Bdb.cursor.fetchone()[0], | |
| 118 test_double, -1) | |
| 119 | |
| 120 # cleanup or else the inserts after the upgrade will not | |
| 121 # work. | |
| 122 Bdb.sql("delete from %(name)ss where %(name)s_key='foo'"%{ | |
| 123 'name': tablename} ) | |
| 124 | |
| 125 self.db.database_schema['version'] = 7 | |
| 126 | |
| 127 if hasattr(self,"downgrade_only"): | |
| 128 return | |
| 129 | |
| 130 # test upgrade altering row | |
| 131 self.db.post_init() | |
| 132 | |
| 133 # verify they keep all signifcant digits before the decimal point | |
| 134 for tablename in ['otk', 'session']: | |
| 135 Bdb = handle[tablename] | |
| 136 Bdb.sql( | |
| 137 'insert into %(name)ss(%(name)s_key, %(name)s_time, %(name)s_value) ' | |
| 138 'values("foo", %(double)s, "value");'%{'name': tablename, | |
| 139 'double': test_double} | |
| 140 ) | |
| 141 | |
| 142 Bdb.cursor.execute('select %(name)s_time from %(name)ss ' | |
| 143 'where %(name)s_key = "foo"'%{'name': tablename}) | |
| 144 | |
| 145 self.assertAlmostEqual(Bdb.cursor.fetchone()[0], | |
| 146 test_double, -1) | |
| 147 | |
| 148 self.assertEqual(self.db.database_schema['version'], 8) | |
| 149 | |
| 74 | 150 |
| 75 class sqliteROTest(sqliteOpener, ROTest, unittest.TestCase): | 151 class sqliteROTest(sqliteOpener, ROTest, unittest.TestCase): |
| 76 pass | 152 pass |
| 77 | 153 |
| 78 | 154 |