Backend for PostgreSQL

[fgsl]

Well, friends, I have found the source of exception that was indicating an error in a LEFT JOIN.

I was trying to understand who was guilty in error trace. Then I have discovered that:

1) The applications are sorted to be installed.
2) The default order of installation is Tinebase, Admin and Addressbook.
3) The method _getSelect() of Tinebase_Group_Sql mounts a LEFT JOIN with table addressbook_lists if Addressbook application is installed. It is verified by attribute _addressBookInstalled.

The problem is how this attribute is configured.

The constructor method of Tinebase_Group_Sql uses method describeTable() of Zend_Db_Adapter for checking if Addressbook application is installed. If it doesn't happen an error that throws an exception, the attribute _addressBookInstalled is set to true.

It works for MySQL (and it should work for Oracle, I think), but the describeTable() doesn't throw exception for PostgreSQL when table addressbook doesn't exist.

So, Tine 2.0 try to execute and SELECT with a LEFT JOIN without the table to be joined.

I have solved this problem this way, I change the code of __construct() of Tinebase_Group_Sql:

Code: Select all

       
 try {
            $this->_db->describeTable(SQL_TABLE_PREFIX . 'addressbook');
            $this->_addressBookInstalled = true;   
        } catch (Zend_Db_Statement_Exception $zdse) {
            // nothing to do
        }


for that:

Code: Select all

       
 try {
            $tableDescription = $this->_db->describeTable(SQL_TABLE_PREFIX . 'addressbook');
            if (!empty($tableDescription))
            {
               $this->_addressBookInstalled = true;   
            }
        } catch (Zend_Db_Statement_Exception $zdse) {
            // nothing to do
        }


After that change, the LEFT JOIN exception disappeared.

This is not a bug (now), but I open an issue as a improvement suggestion: http://forge.tine20.org/mantisbt/view.php?id=4626

Well, I am updating the file with changes now, but be aware that it is necessary to change the method __construct() of Tinebase_Group_Sql. I will include only if my issue is not accepted.

Now I try to discover why I now need to discover why my user is not authenticated.

[lkneschke]

I'll merge the Setup/* files into git and then I'll have a look at the other problems.

[lkneschke]

I merged most of the files to git and refactored some classes to enable support for PostgreSQL.

In general I committed all Setup/* classes directly to the master branch. I'll check if I can import the other files also to the master branch. If it breaks to much I'll create a separate branch where the PostgreSQL code can stabilize.

Another note. I don't think we need to support enum fields in the sql backends. They seem to make problems with other databases then MySQL and we don't need that feature in the database. We check the valid values in our PHP classes anyway. So if you like, just create the enum fields as varchar fields in the backend. We will migrate the existing enum fields to normal varchar fields.

[lkneschke]

It's all in GIT now.

Please do a checkout from GIT and verify that I have merged everything correctly.

You can define the database backendtype now in config.inc.php and enter setup.php to install the applications.

We still need to fix the GROUP BY problem. If you have an idea, how to rewrite the queries it would be no problem to change them in all files.

Please base your new patches on a current git checkout (see http://www.tine20.org/wiki/index.php/De ... t_from_SVN). I'll prepare a separate branch next week, where you can commit your changes directly.

Did you receive your Tine 2.0 GIT login data already?

[lkneschke]

I just discovered another problem.

When I try to install the application, the creation of the initial user account fails, because of following (MySQL specific?) query (see Tinebase_User_Sql line 253):

... DATE_ADD(`last_login_failure_at`, INTERVAL 15 MINUTE) > NOW() ...

Seems like DATE_ADD is MySQL specific. Do you have any idea how get the same result with ANSI SQL statements.

See: http://www.postgresql.org/docs/8.2/inte ... etime.html and http://dev.mysql.com/doc/refman/5.5/en/ ... tions.html

[AirMike]

Hi all,

first of all I'am so glad that supporting other databases is in progress.

I have encountered similar problems with functions like NOW(), GROUP_CONCAT() or MD5() which are not present in Oracle.

Because this functions are used in many places and I didn't wan't to change the code, I decided to create Oracle functions where possible, eg. I created function NOW() which returns SYSDATE for Oracle, or for GROUP_CONCAT() function I found for Oracle on Internet (http://www.oracle-base.com/articles/mis ... niques.php)

Maybe some things can be solved by creating functions for specific database.

[fgsl]

Hello, Lars.

Thank you for feedback and for commit of issue 0004626.

I will follow your advice in relation to the enum fields.

I will checkout the current GIT, but I didn't receive my login data. Do you can send me, or better, a way to recover it?

Initially, I thought to solve the question about date comparison extracting the SQL from Tinebase_User_Sql. So, we could have classes Tinebase_User_Sql_Mysql, Tinebase_User_Sql_Oracle and Tinebase_User_Sql_Pgsql, implementing a Tinebase_User_Sql_Interface. So, non-standard expressions reside in specific classes. I have tested that and it works. But I will try to discover an alternative with SQL ANSI.

Now that you've reassured me about the issue of changing the queries with GROUP BY, I'll also look for an standard alternative to solve it.

[lkneschke]

I fixed the date problem already in GIT. There is no need to implement special classes any more.

Only the GROUP BY problem is left.

I'll send you the GIT login data via email.

[lkneschke]

Next problem is this:

http://www.postgresql.org/docs/8.4/inte ... -enum.html

This query does not work:

Code: Select all

SELECT "container"."id" FROM "tine20_container" AS "container" WHERE ("container"."is_deleted" = 0) AND (("container"."application_id" = '42fd6c67325b598d7d5fb921a9a7b9019ae594b4')) AND (("container"."name" LIKE 'Internal Contacts')) AND (("container"."type" LIKE 'shared'))


because of

Code: Select all

FEHLER: Operator existiert nicht: container_enum_type ~~ unknown
SQL Status:42883

Comparing enums does not work without casting. As we don't need them anyway, we should get rid of them, to solve this problem.

[ph_il]

i removed all enums (exception: voipmanager tables) yesterday.

http://forge.tine20.org/mantisbt/view.php?id=4634