id,summary,reporter,owner,description,type,status,priority,component,severity,resolution,keywords,cc,release
8065,Database schema is incorrect or casts are needed (PostgreSQL 8.4 database),dbely,doki_pen,The database schema you use during upgrade seems to have incorrect field types:\r\n\r\n{{{\r\n#!python\r\n    SCHEMA = [\r\n        Table('subscription'_ key='id')[\r\n            Column('id'_ auto_increment=True)_\r\n            Column('time'_ type='int64')_\r\n            Column('changetime'_ type='int64')_\r\n            Column('class')_\r\n            Column('sid')_\r\n            Column('authenticated'_ type='int')_\r\n            Column('distributor')_\r\n            Column('format')_\r\n            Column('priority'_ type='int')_\r\n            Column('adverb')\r\n        ]_\r\n        Table('subscription_attribute'_ key='id')[\r\n            Column('id'_ auto_increment=True)_\r\n            Column('sid')_\r\n            Column('authenticated'_ type='int')_\r\n            Column('class')_\r\n            Column('realm')_\r\n            Column('target')\r\n        ]\r\n    ]\r\n\r\n}}}\r\n\r\nSpecifically:\r\n\r\n1. What type should have 'authenticated' field? It's declared as int but used as bool. Due to that in many places I have a crash like following:\r\n\r\n{{{\r\n  File "/usr/local/lib/python2.6/dist-packages/TracAnnouncer-0.12.1.dev-py2.6.egg/announcer/model.py"_ line 350_ in do_select\r\n    """_ (sid_authenticated_klass))\r\n  File "/usr/lib/python2.6/dist-packages/trac/db/util.py"_ line 122_ in execute\r\n    return self.cursor.execute(sql_escape_percent(sql)_ args)\r\nProgrammingError: operator does not exist: integer = boolean\r\nLINE 5:                  AND authenticated=true\r\n                                          ^\r\nHINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.\r\n}}}\r\n\r\nOr maybe the filed type is correct but explicit type casts should be added indeed?\r\n\r\n2. What type should have 'time' fields?\r\n\r\n{{{\r\n  File "/usr/local/lib/python2.6/dist-packages/TracAnnouncer-0.12.1.dev-py2.6.egg/announcer/model.py"_ line 79_ in do_insert\r\n    subscription['class']))\r\n  File "/usr/lib/python2.6/dist-packages/trac/db/util.py"_ line 122_ in execute\r\n    return self.cursor.execute(sql_escape_percent(sql)_ args)\r\nProgrammingError: column "time" is of type bigint but expression is of type timestamp with time zone\r\nLINE 5:                  VALUES (CURRENT_TIMESTAMP_ CURRENT_TIMESTAM...\r\n                                 ^\r\nHINT:  You will need to rewrite or cast the expression.\r\n}}}\r\n\r\nI have fixed that with\r\n{{{\r\nVALUES (EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)_ EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)_ %s_ %s_ %s_ %s_ %s_ %s_ %s)\r\n}}}\r\n\r\nBut I don't know if it's the correct way.\r\n\r\nProbably there are other similar bugs that should be fixed as well.\r\n,defect,closed,normal,AnnouncerPlugin,critical,duplicate,patch database API,hasienda,0.12