PostgreSQL integration issue

[84.le0n]

Hi all,
I've developed PostgreSQL driver for Joomla platform, now I'm integrating it in cms but I've found some issue that I wish submit you.

First of all, a today issue with SEF: some days ago I've tested cms frontend and all was ok, now seems that something goes wrong and the cause is SEF enabled in configuration, disabling it all goes ok.

The second is the real issue of my integration: my frontend is all correct, you can checkout my branch from github (now I'm writing with mobile phone, I don't remember that link sorry) and test it, the problem is on backend because I can't login.
During debug session I've found that it stops after UPDATE #__session query.
Can someone helps me fixing this issue?

Thank you all!

Eng. Gabriele Pongelli

AVVERTENZE AI SENSI DEL D.LGS. 196/2003
Le informazioni contenute in questo messaggio di posta elettronica e negli eventuali files allegati, sono da considerarsi strettamente riservati. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceveste per errore questo messaggio, Vi preghiamo cortesemente di darcene notizia all'indirizzo e-mail di cui sopra e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema; costituisce comportamento contrario ai principi dettati dal D.lgs. 196/2003 il trattenere il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse.
This electronic transmission is strictly confidential and intended solely for the addresses. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify us and delete the received data as soon as possible.

[JM Simonet]

>Hi all,
>I've developed PostgreSQL driver for Joomla platform, now I'm
>integrating it in cms but I've found some issue that I wish submit
>you.
>
>First of all, a today issue with SEF: some days ago I've tested cms
>frontend and all was ok, now seems that something goes wrong and the
>cause is SEF enabled in configuration, disabling it all goes ok.
>
>The second is the real issue of my integration: my frontend is all
>correct, you can checkout my branch from github (now I'm writing
>with mobile phone, I don't remember that link sorry) and test it,
>the problem is on backend because I can't login.
>During debug session I've found that it stops after UPDATE #__session query.
>Can someone helps me fixing this issue?

Could this be related to the same issue we have with Finder when
error reporting is set to anything else than "None" ?
See
http://joomlacode.org/gf/project/joomla/tracker/?action=TrackerItemEdit&tracker_item_id=27498

JM

>
>--
>You received this message because you are subscribed to the Google
>Groups "Joomla! CMS Development" group.
>To post to this group, send an email to joomla-...@googlegroups.com.
>To unsubscribe from this group, send email to
>joomla-dev-cm...@googlegroups.com.
>For more options, visit this group at
>http://groups.google.com/group/joomla-dev-cms?hl=en-GB.

--
>Please keep the Subject wording in your answers
This e-mail and any attachments may be confidential. You must not
disclose or use the information contained in this e-mail if you are
not the
intended recipient. If you have received this e-mail in error, please
notify us immediately and delete the e-mail and all copies.
-----------------------------------------------------------
Jean-Marie Simonet / infograf768
Joomla Production Working group
Joomla! Translation Coordination Team

[Jacqui Caren]

On 27/12/2011 09:08, 84.le0n wrote:
> Hi all,
> I've developed PostgreSQL driver for Joomla platform, now I'm integrating it in cms but I've found some issue that I wish submit you.
>
> First of all, a today issue with SEF: some days ago I've tested cms frontend and all was ok, now seems that something goes wrong and the cause is SEF enabled in configuration, disabling it all goes ok.
>
> The second is the real issue of my integration: my frontend is all correct, you can checkout my branch from github (now I'm writing with mobile phone, I don't remember that link sorry) and test it, the problem is on backend because I can't login.
> During debug session I've found that it stops after UPDATE #__session query.
> Can someone helps me fixing this issue?

Have you checked the postgreSQL error logs? It is an encoding issue?

Following this thread and from past experience with PG I assume you have the database amd Pg driver *both* using UNICODE
to ensure there are no driver transcoding issues?

If the answer is no...
I have in the past hit issues with language encodings to and from unicode for certain .eu and .cn charsets - in the end
we had to hack our own transcodings to allow for certain "odd" windows encodings - these may be shipped with
various windows products (office etc) and O/S distributions and they are not identical :-(

Jacqui

[84.le0n]

@JM Simonet
I've tried to change reporting to "developer", "maximum" and "none",
in each case I got same result, a clear page without any kind of error
message.

@Jacqui
I've watched error log, last error presented is about ten hours ago
when I had problems with malformed query from frontend that were
corrected.
I don't think there are transcoding issue, everything is configured
for UTF-8 and I haven't log about that.
Can you try to test my cms branch on a PostgreSQL database?

Any other ideas?
Someone that has tried my branch?

Thank you!
Eng. Gabriele Pongelli.

[84.le0n]

I've updated cms, that issues persist.
My test site works only without SEF and backend login is not done
while #__session table is updated correctly.

These are database queries

12 Queries Logged
SELECT "data"
FROM "nbw6c_session"
WHERE "session_id" = 'cqbndnhb0onmh2biu4jhv0ag97'
SELECT "session_id"
FROM "nbw6c_session"
WHERE "session_id" = 'cqbndnhb0onmh2biu4jhv0ag97'
LIMIT 1 OFFSET 0
SELECT extension_id AS "id", element AS "option", params, enabled
FROM nbw6c_extensions
WHERE "type" = 'component'
AND "element" = 'com_languages'
SELECT extension_id AS "id", element AS "option", params, enabled
FROM nbw6c_extensions
WHERE "type" = 'component'
AND "element" = 'com_users'
SELECT id, rules
FROM "nbw6c_viewlevels"
SELECT folder AS type, element AS name, params
FROM nbw6c_extensions
WHERE enabled >= 1
AND type ='plugin'
AND state >= 0
AND access IN (1,1)
ORDER BY ordering
SELECT template, s.params
FROM nbw6c_template_styles as s
LEFT JOIN nbw6c_extensions as e
ON e.type='template'
AND e.element='s.template'
AND e.client_id=s.client_id
WHERE s.client_id = 1
AND home = '1'
ORDER BY home
SELECT extension_id AS "id", element AS "option", params, enabled
FROM nbw6c_extensions
WHERE "type" = 'component'
AND "element" = 'com_login'
SELECT m.id, m.title, m.module, m.position, m.showtitle, m.params
FROM nbw6c_modules AS m
LEFT JOIN nbw6c_extensions AS e
ON e.element = m.module
AND e.client_id = m.client_id
WHERE m.module ='mod_login'
AND m.client_id = 1
AND e.enabled = 1
ORDER BY m.position, m.ordering
SELECT element
FROM nbw6c_extensions
WHERE type='language'
AND state=0
AND enabled=1
AND client_id=1
SELECT m.id, m.title, m.module, m.position, m.content, m.showtitle,
m.params, mm.menuid
FROM nbw6c_modules AS m
LEFT JOIN nbw6c_modules_menu AS mm
ON mm.moduleid = m.id
LEFT JOIN nbw6c_extensions AS e
ON e.element = m.module
AND e.client_id = m.client_id
WHERE m.published = 1
AND e.enabled = 1
AND (m.publish_up = '1970-01-01 00:00:00' OR m.publish_up <=
'2011-12-28 19:50:02')
AND (m.publish_down = '1970-01-01 00:00:00' OR m.publish_down >=
'2011-12-28 19:50:02')
AND m.access IN (1,1)
AND m.client_id = 1
AND (mm.menuid = 0 OR mm.menuid <= 0)
ORDER BY m.position, m.ordering
UPDATE "nbw6c_session"
SET "data" = '__default|a:6:{s:15:"session.counter";i:1;s:19:"session.timer.start";i:1325101802;s:18:"session.timer.last";i:1325101802;s:17:"session.timer.now";i:1325101802;s:22:"session.client.browser";s:116:"Mozilla/5.0
(Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/535.7 (KHTML, like
Gecko) Chrome/16.0.912.63
Safari/535.7";s:13:"session.token";s:32:"a97c2c2e644eea008a04057842898c7b";}',
"time" = '1325101802'
WHERE "session_id" = 'cqbndnhb0onmh2biu4jhv0ag97'
10 Query Types Logged, Sorted by Occurrences.
SELECT Tables:
3 × SELECT extension_id AS "id", element AS "option", params, enabled
FROM nbw6c_extensions
1 × SELECT m.id, m.title, m.module, m.position, m.showtitle, m.params
FROM nbw6c_modules AS m
LEFT JOIN nbw6c_extensions AS e
ON e.element = m.module
AND e.client_id = m.client_id
1 × SELECT element
FROM nbw6c_extensions
1 × SELECT template, s.params
FROM nbw6c_template_styles as s
LEFT JOIN nbw6c_extensions as e
ON e.type='template'
AND e.element='s.template'
AND e.client_id=s.client_id
1 × SELECT m.id, m.title, m.module, m.position, m.content,
m.showtitle, m.params, mm.menuid
FROM nbw6c_modules AS m
LEFT JOIN nbw6c_modules_menu AS mm
ON mm.moduleid = m.id
LEFT JOIN nbw6c_extensions AS e
ON e.element = m.module
AND e.client_id = m.client_id
1 × SELECT folder AS type, element AS name, params
FROM nbw6c_extensions
1 × SELECT "session_id"
FROM "nbw6c_session"
1 × SELECT id, rules
FROM "nbw6c_viewlevels
1 × SELECT "data"
FROM "nbw6c_session"
OTHER Tables:
1 × UPDATE "nbw6c_session"
SET "data" = '__default|a:6:{s:15:"session.counter";i:1;s:19:"session.timer.start";i:1325101802;s:18:"session.timer.last";i:1325101802;s:17:"session.timer.now";i:1325101802;s:22:"session.client.browser";s:116:"Mozilla/5.0
(Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/535.7 (KHTML, like
Gecko) Chrome/16.0.912.63
Safari/535.7";s:13:"session.token";s:32:"a97c2c2e644eea008a04057842898c7b";}',
"time" = '1325101802'

Thank you for any help!
Eng. Gabriele Pongelli.

[84.le0n]

This is the call stack just before closing session:

------------------
site/libraries/joomla/session/storage/database.php.JSessionStorageDatabase->write
: lineno 104() site/libraries/joomla/session/storage/database.php at
line 104

site/libraries/joomla/session/session.php.session_write_close : lineno
633() site/libraries/joomla/session/session.php at line 633

site/libraries/joomla/session/session.php.JSession->close : lineno
633() site/libraries/joomla/session/session.php at line 633

site/libraries/joomla/session/session.php.JSession->__destruct :
lineno 137() site/libraries/joomla/session/session.php at line 137

site/libraries/joomla/session/session.php.JApplication->close : lineno
0() site/libraries/joomla/session/session.php at line 0

--** here Application is closed **---
site/libraries/joomla/application/application.php.JApplication->redirect
: lineno 462() site/libraries/joomla/application/application.php at
line 462

site/administrator/components/com_login/controller.php.LoginController->login
: lineno 64() site/administrator/components/com_login/controller.php
at line 64

site/libraries/joomla/application/component/controller.php.JController->execute
: lineno 754() site/libraries/joomla/application/component/controller.php
at line 754

site/administrator/components/com_login/login.php.require_once :
lineno 24() site/administrator/components/com_login/login.php at line
24

site/libraries/joomla/application/component/helper.php.JComponentHelper::executeComponent
: lineno 387() site/libraries/joomla/application/component/helper.php
at line 387

site/libraries/joomla/application/component/helper.php.JComponentHelper::renderComponent
: lineno 357() site/libraries/joomla/application/component/helper.php
at line 357

site/administrator/includes/application.php.JAdministrator->dispatch :
lineno 153() site/administrator/includes/application.php at line 153

--** Application dispatch in index.php **--
site/administrator/index.php.{main} : lineno 47()
site/administrator/index.php at line 47
------------------

Inside redirect call the flow goes to line 457-459 where is written
header with 303 , then closed application.

Can anyone understand why is it closed ?

Thank you,
Eng. Gabriele Pongelli.

[Ofer Cohen]

Hey Gabriele

Where can I found the latest version of the postgresql driver?

Thanks

Ofer Cohen

[84.le0n]

Hi Ofer,
this is my branch on which I'm putting changes for PostgreSQL integration
https://github.com/gpongelli/joomla-cms/tree/postgresql

Feel free to add this as remote and then make pull requests.

Thank you!
Eng. Gabriele Pongelli.

2011/12/28 Ofer Cohen <ofer...@gmail.com>:

[84.le0n]

Hi Ofer and other,
have you found other bugs inside postgresql version of cms ?

Eng. Gabriele Pongelli.

[Ofer Cohen]

I've remark some comment into your pull request.

We've an issue with the soundex which cannot be implemented into PostgreSQL without contrib package.

IMHO, it's time for merging into the Joomla master branch and mark it as experimental. That's would give us more testing till 2.5.1.

Ofer Cohen
Joomlics Anonymous Group

[84.le0n]

I saw your comment and I've added remaining check.

About soundex I've started another discussion with results of comparison between PHP and MySQL soundex call, read that mail ;)

Seems there's no regression between beta and GA.
For any other problem I'm here.

[84.le0n]

I've found an error in "Extension Manager: Update", Database tab.
For PostgreSQL it returns an empty page, mysql shows a query error instead.
I'm watching the code and I've found a class that I've never heard about: JSchemaChangeset and JSchemaChangeItem .
Can somebody tell me what is their job?
Why were they added?

Thank you,

[Ofer Cohen]

Could you supply the error log?

Ofer Cohen

[Mark Dexter]

Those were added for 2.5.0 in order to check whether the update queries in com_admin had been run or not. Contact me and I can discuss with you. Thanks. Mark

[84.le0n]

@Ofer
This is the log I got
JDatabaseMySQL::query: 1064 - You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to use near '"#__languages" WHERE field = '\"ordering\"''
at line 1 SQL=SHOW COLUMNS IN "#__languages" WHERE field =
'\"ordering\"'

Return to Control Panel

Call stack
# Function Location
1 JAdministrator->dispatch() /Users/gabriele/Sites/tst/administrator/index.php:46
2 JComponentHelper::renderComponent() /Users/gabriele/Sites/tst/administrator/includes/application.php:152
3 JComponentHelper::executeComponent() /Users/gabriele/Sites/tst/libraries/joomla/application/component/helper.php:357
4 require_once() /Users/gabriele/Sites/tst/libraries/joomla/application/component/helper.php:388
5 JController->execute() /Users/gabriele/Sites/tst/administrator/components/com_installer/installer.php:22
6 InstallerController->display() /Users/gabriele/Sites/tst/libraries/joomla/application/component/controller.php:754
7 InstallerViewDatabase->display() /Users/gabriele/Sites/tst/administrator/components/com_installer/controller.php:60
8 JSchemaChangeset->check() /Users/gabriele/Sites/tst/administrator/components/com_installer/views/database/view.html.php:31
9 JSchemaChangeitem->check() /Users/gabriele/Sites/tst/libraries/cms/schema/changeset.php:101
10 JDatabase->loadObject() /Users/gabriele/Sites/tst/libraries/cms/schema/changeitem.php:166
11 JDatabaseMySQL->query() /Users/gabriele/Sites/tst/libraries/joomla/database/database.php:1080
12 JError::raiseError() /Users/gabriele/Sites/tst/libraries/joomla/database/database/mysql.php:542
13 JError::raise() /Users/gabriele/Sites/tst/libraries/joomla/error/error.php:251

The error is quote character for #__languages , it should be ` and not " .

@ Mark
I'm trying to understand it so I can add PostgreSQL's one.

[Ofer Cohen]

PostgreSQL have the SHOW COLUMNS ability?

Ofer Cohen

[JCR - Lab]

Le 28/01/2012 15:46, Ofer Cohen a écrit :

PostgreSQL have the SHOW COLUMNS ability?

Hi,

SHOW COLUMNS is a MySQL shortcut for the following:

1. SELECT
2.    column_name
3. FROM
4.    information_schema.COLUMNS
5. WHERE
6.   table_name = 'your_table_name_here'

which works in PostgreSQL too.

[Ofer Cohen]

Hey Gabriele,

After some investigating, I realised that PostgreSQL should implement object of type JSchemaChangeitem.

See for example JSchemaChangeitemmysql (cms/schema/changeitemmysql.php). There is also same inheritance for MsSQL (sqlsrv) in master branch of Joomla-CMS.

Thanks

Ofer Cohen
Joomlics Anonymous Group

On 01/28/2012 11:39 AM, 84.le0n wrote:

[84.le0n]

Il giorno 28/gen/2012, alle ore 21:45, Ofer Cohen <ofer...@gmail.com> ha scritto:

Hey Gabriele,

After some investigating, I realised that PostgreSQL should implement object of type JSchemaChangeitem.

I've already started doing a JSchemaChangeItempostgresql with some other changes to load correct files.

About SHOW COLUMN, JCR is right, but as you can see that error log is for MySQL database class that I got while running same steps to see what happens with MySQL, I told you that there is an error with mysql and I've reported it ;)

[84.le0n]

Ofer, can you pull my last changes and test if Extension Manager ->
Database tab works as expected ?

Thank you,
Eng Gabriele Pongelli.

[Ofer Cohen]

Hey Gabriele

In Joomla 2.5.0 MySQL database, I've the next output in the database tab of extension manager:
Database schema version (in #__schemas): 2.5.0-2012-01-14.
Database driver: mysqli.
62 database changes were checked successfully.
13 database changes did not alter table structure and were skipped.

In you branch PostgreSQL database, I've the next output in the database tab of extension manager:
Database schema version (in #__schemas): **not found**.
Database driver: postgresql.
71 database changes were checked successfully.
332 database changes did not alter table structure and were skipped.

I don't know why there is "not found" in the first line.

That's all :-)

Ofer Cohen
Joomlics Anonymous Group

[Mark Dexter]

It means there is no value in the #__schemas table (column=version_id) for id=700. That value is used to check whether or not to run update queries from com_admin during the update. Mark

[84.le0n]

Any new issues?
Till now last issue is about SOUNDEX, right?
Meanwhile I've added exporter, importer and their test classes to my platform branch.

@Ofer
About schema, that tab tells you differences between your database schema (doing database query) and that expected schema coming from sql update file .
So try to change a database table definition or a sql update file and check if this difference is present inside that tab, I did this changing a query file and it found this difference.