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,Robert Corsaro,"The database schema you use during upgrade seems to have incorrect field types: {{{ #!python SCHEMA = [ Table('subscription', key='id')[ Column('id', auto_increment=True), Column('time', type='int64'), Column('changetime', type='int64'), Column('class'), Column('sid'), Column('authenticated', type='int'), Column('distributor'), Column('format'), Column('priority', type='int'), Column('adverb') ], Table('subscription_attribute', key='id')[ Column('id', auto_increment=True), Column('sid'), Column('authenticated', type='int'), Column('class'), Column('realm'), Column('target') ] ] }}} Specifically: 1. 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: {{{ File ""/usr/local/lib/python2.6/dist-packages/TracAnnouncer-0.12.1.dev-py2.6.egg/announcer/model.py"", line 350, in do_select """""", (sid,authenticated,klass)) File ""/usr/lib/python2.6/dist-packages/trac/db/util.py"", line 122, in execute return self.cursor.execute(sql_escape_percent(sql), args) ProgrammingError: operator does not exist: integer = boolean LINE 5: AND authenticated=true ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. }}} Or maybe the filed type is correct but explicit type casts should be added indeed? 2. What type should have 'time' fields? {{{ File ""/usr/local/lib/python2.6/dist-packages/TracAnnouncer-0.12.1.dev-py2.6.egg/announcer/model.py"", line 79, in do_insert subscription['class'])) File ""/usr/lib/python2.6/dist-packages/trac/db/util.py"", line 122, in execute return self.cursor.execute(sql_escape_percent(sql), args) ProgrammingError: column ""time"" is of type bigint but expression is of type timestamp with time zone LINE 5: VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAM... ^ HINT: You will need to rewrite or cast the expression. }}} I have fixed that with {{{ VALUES (EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), %s, %s, %s, %s, %s, %s, %s) }}} But I don't know if it's the correct way. Probably there are other similar bugs that should be fixed as well. ",defect,closed,normal,AnnouncerPlugin,critical,duplicate,patch database API,Steffen Hoffmann,0.12