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