Mercurial > p > roundup > code
comparison test/test_postgresql.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 | 09d9c646ca89 |
| children | 9ff091537f43 |
comparison
equal
deleted
inserted
replaced
| 6805:09d9c646ca89 | 6806:bdd28b244839 |
|---|---|
| 82 | 82 |
| 83 # load the database | 83 # load the database |
| 84 self.db.issue.create(title="flebble frooz") | 84 self.db.issue.create(title="flebble frooz") |
| 85 self.db.commit() | 85 self.db.commit() |
| 86 | 86 |
| 87 if self.db.database_schema['version'] != 7: | 87 if self.db.database_schema['version'] > 7: |
| 88 # consider calling next testUpgrade script to roll back | 88 # make testUpgrades run the downgrade code only. |
| 89 # schema to version 7. | 89 if hasattr(self, "downgrade_only"): |
| 90 # we are being called by an earlier test | |
| 91 self.testUpgrade_7_to_8() | |
| 92 self.assertEqual(self.db.database_schema['version'], 7) | |
| 93 else: | |
| 94 # we are being called directly | |
| 95 self.downgrade_only = True | |
| 96 self.testUpgrade_7_to_8() | |
| 97 self.assertEqual(self.db.database_schema['version'], 7) | |
| 98 del(self.downgrade_only) | |
| 99 elif self.db.database_schema['version'] != 7: | |
| 90 self.skipTest("This test only runs for database version 7") | 100 self.skipTest("This test only runs for database version 7") |
| 91 | 101 |
| 92 # remove __fts table/index; shrink length of __words._words | 102 # remove __fts table/index; shrink length of __words._words |
| 93 # trying to insert a long word in __words._words should fail. | 103 # trying to insert a long word in __words._words should fail. |
| 94 # trying to select from __fts should fail | 104 # trying to select from __fts should fail |
| 137 | 147 |
| 138 self.db.sql("select * from __fts") | 148 self.db.sql("select * from __fts") |
| 139 | 149 |
| 140 self.assertEqual(self.db.database_schema['version'], | 150 self.assertEqual(self.db.database_schema['version'], |
| 141 self.db.current_db_version) | 151 self.db.current_db_version) |
| 152 | |
| 153 def testUpgrade_7_to_8(self): | |
| 154 """ change _time fields in BasicDatabases to double """ | |
| 155 # load the database | |
| 156 self.db.issue.create(title="flebble frooz") | |
| 157 self.db.commit() | |
| 158 | |
| 159 if self.db.database_schema['version'] != 8: | |
| 160 self.skipTest("This test only runs for database version 8") | |
| 161 | |
| 162 # change otk and session db's _time value to their original types | |
| 163 sql = "alter table sessions alter column session_time type REAL;" | |
| 164 self.db.sql(sql) | |
| 165 sql = "alter table otks alter column otk_time type REAL;" | |
| 166 self.db.sql(sql) | |
| 167 | |
| 168 # verify they truncate long ints. | |
| 169 test_double = 1658718284.7616878 | |
| 170 for tablename in ['otk', 'session']: | |
| 171 self.db.sql( | |
| 172 'insert into %(name)ss(%(name)s_key, %(name)s_time, %(name)s_value) ' | |
| 173 "values ('foo', %(double)s, 'value');"%{'name': tablename, | |
| 174 'double': test_double} | |
| 175 ) | |
| 176 | |
| 177 self.db.cursor.execute('select %(name)s_time from %(name)ss ' | |
| 178 "where %(name)s_key = 'foo'"%{'name': tablename}) | |
| 179 | |
| 180 self.assertNotAlmostEqual(self.db.cursor.fetchone()[0], | |
| 181 test_double, -1) | |
| 182 | |
| 183 # cleanup or else the inserts after the upgrade will not | |
| 184 # work. | |
| 185 self.db.sql("delete from %(name)ss where %(name)s_key='foo'"%{ | |
| 186 'name': tablename} ) | |
| 187 | |
| 188 self.db.database_schema['version'] = 7 | |
| 189 | |
| 190 if hasattr(self,"downgrade_only"): | |
| 191 return | |
| 192 | |
| 193 # test upgrade altering row | |
| 194 self.db.post_init() | |
| 195 | |
| 196 # verify they keep all signifcant digits before the decimal point | |
| 197 for tablename in ['otk', 'session']: | |
| 198 self.db.sql( | |
| 199 'insert into %(name)ss(%(name)s_key, %(name)s_time, %(name)s_value) ' | |
| 200 "values ('foo', %(double)s, 'value');"%{'name': tablename, | |
| 201 'double': test_double} | |
| 202 ) | |
| 203 | |
| 204 self.db.cursor.execute('select %(name)s_time from %(name)ss ' | |
| 205 "where %(name)s_key = 'foo'"%{'name': tablename}) | |
| 206 | |
| 207 self.assertAlmostEqual(self.db.cursor.fetchone()[0], | |
| 208 test_double, -1) | |
| 209 | |
| 210 self.assertEqual(self.db.database_schema['version'], 8) | |
| 142 | 211 |
| 143 @skip_postgresql | 212 @skip_postgresql |
| 144 class postgresqlROTest(postgresqlOpener, ROTest, unittest.TestCase): | 213 class postgresqlROTest(postgresqlOpener, ROTest, unittest.TestCase): |
| 145 def setUp(self): | 214 def setUp(self): |
| 146 postgresqlOpener.setUp(self) | 215 postgresqlOpener.setUp(self) |