PostgreSQL related bugs

pedro42

SYSTEM: Slackware 14.0
EPESI: epesi-1.4.2-rev10136
WEB: Apache 2.4.3
PHP: 5.4.11
DB: PostgreSQL 9.2.3

Few days ago a installed Epesi include all free premium modules. It looks very well, so i'm testing it and preparing for production use. I know, PostgreSQL is not the major DB for web development, but for me is important. EPESI is probably tested on MySQL and so, there are some bugs related to other databases. There is the first, what i found.

PostgreSQL doesn't like doublequotes as string separators and in INSERT statement, the construction INSERT INTO ... ON DUPLICATE ... is not valid.

So there is my first patch for ContactPhotos. I hope, it doesn!t break MySQL.

Hm, i can!t upload the patch, extension patch is not allowed, extension txt is not allowed, bettes is exe ? ;-)

So there is it in message body:

diff --git a/modules/CRM/Contacts/Photo/PhotoCommon_0.php b/modules/CRM/Contacts/Photo/PhotoCommon_0.php
index 1d91121..eb2689a 100644
--- a/modules/CRM/Contacts/Photo/PhotoCommon_0.php
+++ b/modules/CRM/Contacts/Photo/PhotoCommon_0.php
@@ -40,7 +40,7 @@ class CRM_Contacts_PhotoCommon extends ModuleCommon {

public static function add_photo($contact_id, $filename) {
self::del_photo($contact_id, $filename);
- DB::Execute('INSERT INTO '.self::table_name.' VALUES (%d,%s) ON DUPLICATE KEY UPDATE filename=%s', array($contact_id, $filename, $filename));
+ DB::Execute('INSERT INTO '.self::table_name.' VALUES (%d,%s)', array($contact_id, $filename));
}

public static function get_photo($contact_id) {
@@ -53,7 +53,7 @@ class CRM_Contacts_PhotoCommon extends ModuleCommon {

$in = self::Instance();
unlink($in->get_data_dir() . $filename);
- DB::Execute('DELETE FROM `'.self::table_name.'` WHERE `contact_id`=%d', array($contact_id));
+ DB::Execute('DELETE FROM '.self::table_name.' WHERE contact_id=%d', array($contact_id));
}
}

ajb

Hi,

thanks for that. Yes it's true that PostrgreSQL is not so tested as MySQL, but here I don't know what has happened, because I see in history that some other queries were fixed to work with Postgre, but those two has left unchanged. Great thanks for your patch.

Well... I've also tried to upload some kind of files, but no other than zip is working. I'll opt to improve this forum setting because it's annoying.

Regards,
Adam

pedro42

Hi,

there is another similar - doublequotes again

pedro42

Hi,

and more. I couldn't run Asset module, because there was an error in query construction. This is probably Adodb bug, not EPESI. When i tried to use Asset module, i saw this SQL error:

ERROR: operator does not exist: smallint = boolean at character 86
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
STATEMENT: SELECT COUNT(*) FROM crm_assets_data_1 AS r WHERE active=1 AND (false OR (r.f_active = true AND r.f_active IS NOT NULL))

Full error log is in the attachment zip file.
Field 'active' is type boolean, but the '1' is type smallint (or int, bigint, numeric, real etc.). PostgreSQL is strong and doesn't allow to mix boolean and integer types together in comparison. I'm not going to repair Adodb, so what to do ?

PostgreSQL is fortunately smart enough and we can teach it, what to do with comparison boolean and integer types. The solution is to create new operators and related functions, which will be used in case comparison boolean and numeric types. I created those operators for integer types only (smallint, int and bigint). I hope, Adodb will not generate comparison between boolean and other numeric types (double precision etc.)

In the attachment, there is full error log before creating the operators, sql file for creating the operators and related functions, sql for drop the operators and functions from database if you don't want it anymore. There are the sql files in the patch form also.

Be careful about the schema where you want to create the operators and functions (line 'set search_path..' is commented out).

Petr

pedro42

I;m sorry, in my previous post i made a mistake. Field active is type smallint in all tables in Epesi. The error is in r.f_active = true. r.f_active is smallint, not boolean.

STATEMENT: SELECT COUNT(*) FROM crm_assets_data_1 AS r WHERE active=1 AND (false OR (r.f_active = true AND r.f_active IS NOT NULL))

pedro42

Another doublequotes in RC-extension.

ajb

Thanks for your posts. I'll test postgre and apply your changes before next release.

pedro42

I'm back 🙂. Thank you or accepting the patches.

I finally found the bugs in Assets module. The CREATE OPERATOR works, but it's really crazy way how to solve this problem. It's in data type - the "active" field in tables is type integer (smallint), but in PHP code is sometimes type boolean. PostgreSQL doesn't like this.

So there is patch for Assets module. It repairs data types.

jasiek

Pedro42,

Thank you very much for your bug fix 🙂
We'll test it and incorporate it in the next release.

pedro42

Hi,

i tried to create an issue on github, but it doesn't support attachments (patches) ? I don't know how to add file to issue.

The first one is when i run cron, i get syntax error in Messenger. Time interval in PG must be singleuoted, for example

SELECT now() + interval '4 minute';

I don't know if it's compatible with MySQL.

The second issue is in Activity Report. When i chose notes and/or files in the form, there is variable type incompatibility. The php script creates big query with lot of UNIONs:(see attached sql.zip).

The first field r_id (ual.local as r_id) is varchar type. The others r_id's are integer type, so i get error:
ERROR: UNION types character varying and integer cannot be matched.

For me is the simplest way to retype integer to varchar, for example crm_assets_id::varchar as r_id.

Patches are attached, but - again - i know nothing about compatibility with MySQL.

Petr

ajb

Hi,

thanks for your patches, I'll look at them tomorrow and apply them.

I've checked right now that this is not valid MySql :/



SELECT now() + interval '4 minute';

Mysql working version is without quotes



SELECT now() + interval 4 minute;

You can create an account on GitHub, fork EPESI repo, edit "dev" branch an make a pull request 🙂 But of course you can post patches here as well 🙂
Github only allows to attach images (drag&drop) in issues.

Regards,
Adam

pedro42

Thank you, ajb. I think there is not enough bugs to make fork 😉

pedro42

This is small issue, probably not related to PostgreSQL only. This patch get rid of annoying messages in log.



ERROR:  duplicate key value violates unique constraint "tools_whoisonline_users_pkey"

DETAIL:  Key (session_name)=(cvjq6rpnna92961il7so93ilp0) already exists.

STATEMENT:  INSERT INTO tools_whoisonline_users(session_name,user_login_id) VALUES( $1, $2)

Edit: i uploaded v2 of the patch, the first was wrong.

ajb

Thanks!

All patches applied and pushed to github with mysql compatibility changes.

Epesi ENS | Epesi Academy | epesi.cloud PaaS | GitHub

Help Me! | Download from SourceForge