0008404: Preferences do not work with Postgres

[Gloirin]

Reported by cassiano.dalpizzol on 21 May 2013 13:20

Version: git master

When using Postgres as database the Preferences tab do not work.

It happens only on the master branch, kristina branch is ok.

on the log the folowing lines are logged

7668e dc66d 90799127000 - 2013-05-21T13:05:05+00:00 INFO (6): Tinebase_Server_Json::_handle::187 is JSON request. method: Tinebase.searchPreferencesForApplication
7668e dc66d 90799127000 - 2013-05-21T13:05:05+00:00 DEBUG (7): Tinebase_Frontend_Json::searchPreferencesForApplication::765 Got 5 preferences for app Tinebase
7668e dc66d 90799127000 - 2013-05-21T13:05:05+00:00 WARN (4): Tinebase_Server_Json::_handleException::253 Zend_Db_Statement_Exception -> SQLSTATE[42883]: Undefined function: 7 ERRO: operador não existe: character varying = integer
LINE 1: ... LIKE 'locale')) AND (("preferences"."account_id" = 0)) AND ...
^
HINT: Nenhum operador corresponde com o nome e o(s) tipo(s) de argumento(s) informados. Você precisa adicionar conversões de tipo explícitas.
7668e dc66d 90799127000 - 2013-05-21T13:05:05+00:00 NOTICE (5): Tinebase_Server_Json::_logExceptionTrace::320 #0 .../library/Zend/Db/Statement.php(284): Zend_Db_Statement_Pdo->_execute(Array)
#1 .../library/Zend/Db/Adapter/Abstract.php(468): Zend_Db_Statement->execute(Array)
#2 .../library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(Object(Zend_Db_Select), Array)
#3 .../Tinebase/Backend/Sql/Abstract.php(679): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select))
#4 .../Tinebase/Backend/Sql/Abstract.php(501): Tinebase_Backend_Sql_Abstract->_fetch(Object(Zend_Db_Select))
#5 .../Tinebase/Preference/Abstract.php(170): Tinebase_Backend_Sql_Abstract->search(Object(Tinebase_Model_PreferenceFilter), NULL, false)
#6 .../Tinebase/Preference/Abstract.php(700): Tinebase_Preference_Abstract->search(Object(Tinebase_Model_PreferenceFilter))
#7 .../Tinebase/Preference/Abstract.php(496): Tinebase_Preference_Abstract->_getDefaultPreference('locale')
#8 .../Tinebase/Frontend/Json.php(1023): Tinebase_Preference_Abstract->resolveOptions(Object(Tinebase_Model_Preference))
#9 .../Tinebase/Frontend/Json.php(770): Tinebase_Frontend_Json->_multipleRecordsToJson(Object(Tinebase_Record_RecordSet), Object(Tinebase_Model_PreferenceFilter))
#10 [internal function]: Tinebase_Frontend_Json->searchPreferencesForApplication('Tinebase', '')
#11 .../library/Zend/Server/Abstract.php(232): call_user_func_array(Array, Array)
#12 .../Zend/Json/Server.php(558): Zend_Server_Abstract->_dispatch(Object(Zend_Server_Method_Definition), Array)
#13 .../Zend/Json/Server.php(197): Zend_Json_Server->_handle()
#14 .../Tinebase/Server/Json.php(231): Zend_Json_Server->handle(Object(Zend_Json_Server_Request))
#15 .../Tinebase/Server/Json.php(101): Tinebase_Server_Json->_handle(Object(Zend_Json_Server_Request))
#16 .../Tinebase/Core.php(230): Tinebase_Server_Json->handle()
#17 .../index.php(17): Tinebase_Core::dispatchRequest()
#18 {main}

Steps to reproduce: Log in tine20 on a Postgres instalation.

try to open the preferences tab

[Gloirin]

Comment posted by fvisic on 22 May 2013 21:00

https://gerrit.tine20.org/tine20/#/c/2051/

[Gloirin]

Comment posted by pschuele on 23 May 2013 07:50

@cassiano: could you please test the fix by Filip?

[Gloirin]

Comment posted by cassiano.dalpizzol on 23 May 2013 22:15

I made the tests and works fine here.

Both in MySQL and Postgres the '0' corrects the problem

[Gloirin]

Comment posted by pschuele on 24 May 2013 08:21

thanks for testing, i'll submit the change.

[Gloirin]

Comment posted by pschuele on 24 May 2013 08:21

thanks Filip, for fixing this.

[Gloirin]

Comment posted by pschuele on 24 May 2013 09:46

it looks like we still have a problem here:

1. Tinebase_PreferenceTest::testSearchCalendarPreferencesForAnyone
   [exec] Zend_Db_Statement_Exception: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: character varying = integer
   [exec] LINE 1: ...preferences" WHERE (((("preferences"."account_id" = 0)) AND ...
   [exec] ^
   [exec] HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
   [exec]
   [exec] <http://ci.tine20.org/job/tine20pgsql/ws/tine20/Tinebase/Backend/Sql/Abstract.php&gt;:679
   [exec] <http://ci.tine20.org/job/tine20pgsql/ws/tine20/Tinebase/Backend/Sql/Abstract.php&gt;:501
   [exec] <http://ci.tine20.org/job/tine20pgsql/ws/tine20/Tinebase/Preference/Abstract.php&gt;:170
   [exec] <http://ci.tine20.org/job/tine20pgsql/ws/tine20/Tinebase/Frontend/Json.php&gt;:763
   [exec]

[Gloirin]

Comment posted by pschuele on 30 May 2013 12:06

see https://gerrit.tine20.org/tine20/#/c/1990/6/tine20/Tinebase/Model/PreferenceFilter.php

maybe we need to

• create a new Id filter that fixes the value type (convert to string)
  OR
• allow to give the value type to the filter as an option

[Gloirin]

Comment posted by pschuele on 30 May 2013 12:08

• allow to give the value type to the filter as an option
  -> that might be the best solution

'string' should be default and only the container id filter should have 'integer'.

[Gloirin]

Comment posted by pschuele on 4 Jun 2013 13:03

just merged the change into the master.

we'll check if this can be improved further by asking the db schema which type the id column has.

[Gloirin]

Comment posted by AirMike on 12 Jun 2013 13:10

You should use is_numeric instead of ctype_digit when enforcing int value because passing integer as id is evaluated as FALSE instead of TRUE

[Gloirin]

Comment posted by pschuele on 12 Jun 2013 13:34

@Lars: could you have a look again? would is_numeric be better?