PostgreSQL support

Dear Gábor,

I am using the same thread to report problems, to avoid filling list.

When clicking on:
Localization server › Connector for drupal.org

Connector for drupal.org
* Configurer
* Scan

Contents of /tmp/drupal-6.6.tar.gz have been scanned.
warning: pg_fetch_object() expects parameter 1 to be resource, array given in /home/jmpoure/bulle-immobiliere.org/trunk/drupal/includes/database.pgsql.inc on line 175.

Any idea?

I am testing further and there is a problem running DELETE queries in :
l10n_community.admin.inc

    * warning: pg_query() [function.pg-query]: Query failed: ERREUR: erreur de syntaxe sur ou près de « LEFT » LINE 2: LEFT JOIN l10n_community_release AS r ON e.rid = r.rid ^ in /home/jmpoure/bulle-immobiliere.org/trunk/drupal/includes/database.pgsql.inc on line 138.
    * user warning: query: DELETE FROM l10n_community_error AS e LEFT JOIN l10n_community_release AS r ON e.rid = r.rid WHERE r.pid = 1 in /home/jmpoure/bulle-immobiliere.org/trunk/drupal/modules/l10n_server/l10n_community/l10n_community.admin.inc on line 237.
    * warning: pg_query() [function.pg-query]: Query failed: ERREUR: erreur de syntaxe sur ou près de « , » LINE 1: DELETE FROM l10n_community_project AS p, l10n_community_rele... ^ in /home/jmpoure/bulle-immobiliere.org/trunk/drupal/includes/database.pgsql.inc on line 138.
    * user warning: query: DELETE FROM l10n_community_project AS p, l10n_community_release AS r, l10n_community_file AS f, l10n_community_line AS l INNER JOIN p ON p.pid = r.pid INNER JOIN f ON r.rid = f.rid INNER JOIN l ON f.fid = l.fid WHERE p.pid = 1 in /home/jmpoure/bulle-immobiliere.org/trunk/drupal/modules/l10n_server/l10n_community/l10n_community.admin.inc on line 247.

In PostgreSQL, you cannot use LEFT AND RIGHT JOINS directly in DELETE clauses.
You need to use a nested query.

Example: line 233

DELETE FROM {l10n_community_error} e
     LEFT JOIN {l10n_community_release} r ON e.rid = r.rid
     WHERE r.pid = %d

Should be written:

DELETE FROM {l10n_community_error}
WHERE eid IN
(SELECT e.eid FROM {l10n_community_error} e
LEFT JOIN {l10n_community_release} r ON e.rid = r.rid
WHERE r.pid = %d)

This kind of query will run anywhere on any database.
I admit this is more heavy.

Could you confirm this query runs without problem under MySQL?
Should I submit a patch for DELETE clauses?

There are other minor problems in pages.inc, line 485:

      $result = db_query_range("SELECT COUNT(DISTINCT t.sid) sum, u.name, u.uid FROM {l10n_community_translation} t LEFT JOIN {users} u ON t.uid_entered = u.uid WHERE t.is_active = 1 AND t.is_suggestion = 0 AND t.language = '%s' GROUP BY t.uid_entered ORDER BY sum DESC", $id, 0, 10);

fails to run under PostgreSQL.

It should be:

      $result = db_query_range("SELECT COUNT(DISTINCT t.sid) AS count_active, u.name, u.uid FROM {l10n_community_translation} t LEFT JOIN {users} u ON t.uid_entered = u.uid WHERE t.is_active = 1 AND t.is_suggestion = 0 AND t.language = '%s' GROUP BY t.uid_entered, u.name, u.uid ORDER BY count_active DESC", $id, 0, 10);

When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions, since there would be more than one possible value to return for an ungrouped column.

Could you confirm that the modified query runs smoothy under MySQL?
Then i will submit a patch.

For the same reasons:
$result = db_query("SELECT COUNT(DISTINCT s.sid) AS string_count, p.pid, p.title, p.uri FROM {l10n_community_project} p INNER JOIN {l10n_community_release} r ON p.pid = r.pid INNER JOIN {l10n_community_file} f ON r.rid = f.rid INNER JOIN {l10n_community_line} l ON f.fid = l.fid INNER JOIN {l10n_community_string} s ON l.sid = s.sid GROUP BY p.pid, p.title, p.uri;");

Also notice the "AS" when counting.

Don't blame me. This is probably SQL99 and running the code under PostgreSQL ensures that the SQL will run anywhere.

Here is a patch for the page.inc SQL code.
As it is quite straightforward, it should work.

Now, please inform me if

DELETE FROM {l10n_community_error}
WHERE eid IN
(SELECT e.eid FROM {l10n_community_error} e
LEFT JOIN {l10n_community_release} r ON e.rid = r.rid
WHERE r.pid = %d)

runs smothly under MySQL so that i can generate another patch.
Kind regards, JMP

Okay, now I have a real problem.
PostgreSQL does not allow to delete ROWs in several tables simultaneously.

So this query fails (line 260):

DELETE s, t FROM {l10n_community_string} s
LEFT JOIN {l10n_community_translation} t ON t.sid = s.sid
WHERE s.sid NOT IN (SELECT sid FROM {l10n_community_line})

I will ask for advice at PostgreSQL.org and report back.
Kind regards, JMP

Exactly, this is why this multiple delete is problematic.

I plan to ask support to core PostgreSQL hackers at PostgreSQL to ask information. At first I would like to know if this is SQL99 compliant and what PostgreSQL plans to do. In the past, I asked for features and they were sometimes added in PostgreSQL. PostgreSQL hackers have the goal to develop a reference database and they wron't move in any direction when a feature is not SQL99 compliant. Based on their analysis, we can decide to write a specific PostgreSQL code or transform the delete query into two queries. Or whatever you decide. I will be back soon, being very motivated by your module.

I am also part of kdenlive projects, which is a video editor for GNU/Linux. We plan to use Drupal to internationalize our PO files. So I will be looking for a hack to load and update ou POTs into the database. I am really motivated !!! And I will stay active on the module to help supporing it.

I will test and report shortly. Thanks.

Sorry, I was busy and I am back. There is a small problem :

 * warning: pg_query() [function.pg-query]: Query failed: ERREUR: la colonne « u.name » doit apparaître dans la clause GROUP BY ou doit être utilisé dans une fonction d'agrégat in /home/html/drupal/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT COUNT(DISTINCT t.sid) AS sum, u.name, u.uid FROM l10n_community_translation t LEFT JOIN users u ON t.uid_entered = u.uid WHERE t.is_active = 1 AND t.is_suggestion = 0 AND t.language = 'fr' GROUP BY t.uid_entered ORDER BY sum DESC LIMIT 10 OFFSET 0 in /home/html/drupal/sites/all/modules/l10n_server/l10n_community/pages.inc on line 501.

Patch attached.

Also, BINARY is not SQL92 compliant. It is an extension from MySQL:

* warning: pg_query() [function.pg-query]: Query failed: ERREUR: le type « binary » n'existe pas LINE 1: ...nslation t ON s.sid = t.sid WHERE t.translation = BINARY 'No... ^ in /home/html/drupal/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT s.sid FROM l10n_community_string s LEFT JOIN l10n_community_translation t ON s.sid = t.sid WHERE t.translation = BINARY 'Nombre de projets à parcourir simultanément' AND t.is_active = 1 AND t.language = 'fr' AND s.sid = 1 in /home/html/drupal/sites/all/modules/l10n_server/l10n_community/l10n_community.module on line 851.

I guess it should be : SELECT s.sid FROM l10n_community_string s LEFT JOIN l10n_community_translation t ON s.sid = t.sid WHERE t.translation = 'Nombre de projets à parcourir simultanément' AND t.is_active = 1 AND t.language = 'fr' AND s.sid = 1

No ? Anyway, this kind of optimization is done by the parser. Why do you cast to BINARY. I don't understand the logic.

Patch attached. Please review this patch, as I don't understand the logic behind this choice of "BINARY". Bye.

I added three translations to the local testing database. Project displays:

Progress status

Status overview

* 0 contributors
* 438 strings to translate
* 0 translations recorded
* 0 suggestions awaiting approval

Top contributors

People with most approved translations

* admin - 3 translations

Is this normal that 0 translations are recorded when admin has 3? Do I miss something.

Anyway, congrats, it seems to work perfectly excepte a few hints.

Another small issue:

 * warning: pg_query() [function.pg-query]: Query failed: ERREUR: la colonne « p.title » doit apparaître dans la clause GROUP BY ou doit être utilisé dans une fonction d'agrégat in /home/html/drupal/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT COUNT(DISTINCT s.sid) AS string_count, p.pid, p.title, p.uri FROM l10n_community_project p INNER JOIN l10n_community_release r ON p.pid = r.pid INNER JOIN l10n_community_file f ON r.rid = f.rid INNER JOIN l10n_community_line l ON f.fid = l.fid INNER JOIN l10n_community_string s ON l.sid = s.sid GROUP BY p.pid; in /home/html/drupal/sites/all/modules/l10n_server/l10n_community/pages.inc on line 479.

Patch attached.

By the way, the explore project page is empty. Explore languages works. So there should be something in explore projects.

This query is empty:

SELECT COUNT(DISTINCT s.sid) AS string_count, p.pid, p.title, p.uri
FROM l10n_community_project p INNER JOIN l10n_community_release r ON p.pid = r.pid
INNER JOIN l10n_community_file f ON r.rid = f.rid
INNER JOIN l10n_community_line l ON f.fid = l.fid
INNER JOIN l10n_community_string s ON l.sid = s.sid
GROUP BY p.pid, p.title, p.uri

Another problem in admin:

user warning: query: SELECT p.pid, p.title project_title, r.rid, r.title release_title, COUNT(l.lineno) as occurance_count FROM l10n_community_project p INNER JOIN l10n_community_release r ON p.pid = r.pid INNER JOIN l10n_community_file f ON r.rid = f.rid INNER JOIN l10n_community_line l ON f.fid = l.fid INNER JOIN l10n_community_string s ON l.sid = s.sid WHERE s.sid = 1 GROUP BY r.rid ORDER by p.pid, r.rid in /home/html/drupal/sites/all/modules/l10n_server/l10n_community/ajax.inc on line 26.

Patch attached.

By the way, this query is empty on my test server.

This nested deletion on INNER JOINS is not permitted in PostgreSQL

 * warning: pg_query() [function.pg-query]: Query failed: ERREUR: erreur de syntaxe sur ou près de « e » LINE 1: DELETE e FROM l10n_community_error e ^ in /home/html/drupal/includes/database.pgsql.inc on line 139.
    * user warning: query: DELETE e FROM l10n_community_error e LEFT JOIN l10n_community_release r ON e.rid = r.rid WHERE r.pid = 1 in /home/html/drupal/sites/all/modules/l10n_server/l10n_community/l10n_community.admin.inc on line 237.
    * warning: pg_query() [function.pg-query]: Query failed: ERREUR: erreur de syntaxe sur ou près de « p » LINE 1: DELETE p, r, f, l FROM l10n_community_project p ^ in /home/html/drupal/includes/database.pgsql.inc on line 139.
    * user warning: query: DELETE p, r, f, l FROM l10n_community_project p INNER JOIN l10n_community_release r ON p.pid = r.pid INNER JOIN l10n_community_file f ON r.rid = f.rid INNER JOIN l10n_community_line l ON f.fid = l.fid WHERE p.pid = 1 in /home/html/drupal/sites/all/modules/l10n_server/l10n_community/l10n_community.admin.inc on line 247.

Could you write seperate SQL queries for each table and run them simultaneously in one query with ;

Also here:

* warning: pg_query() [function.pg-query]: Query failed: ERREUR: erreur de syntaxe sur ou près de « s » LINE 1: DELETE s, t FROM l10n_community_string s ^ in /home/html/drupal/includes/database.pgsql.inc on line 139.
* user warning: query: DELETE s, t FROM l10n_community_string s LEFT JOIN l10n_community_translation t ON t.sid = s.sid WHERE s.sid NOT IN (SELECT sid FROM l10n_community_line) in /home/html/drupal/sites/all/modules/l10n_server/l10n_community/l10n_community.admin.inc on line 263.

We need two seperate queries.

Setting status to needs review.