glossary and PostgreSQL problem

glossary and PostgreSQL problem

by Karol Grondzak -
Number of replies: 6

Hi,

I had a problem to use glossary module under my moodle - 2004010900 using PostgreSQL.

First, in table glossary_entries, column approved is missing. After adding it I realized that function UCASE does not work with PostgreSQL. After changing UCASE to UPPER I realized that there are another problems:

(postgres7): SELECT * FROM mdl_glossary_displayformats WHERE fid = '0' LIMIT 1  


Warning: PostgreSQL query failed: ERROR: Relation "mdl_glossary_displayformats" does not exist in /var/www/moodle-1.2/lib/adodb/drivers/adodb-postgres64.inc.php on line 589
ERROR: Relation "mdl_glossary_displayformats" does not exist : ERROR: Relation "mdl_glossary_displayformats" does not exist

And also:

(postgres7): SELECT ge.concept pivot, ge.* FROM mdl_glossary_entries ge WHERE (ge.glossaryid = 3 or ge.sourceglossaryid = 3) AND (ge.approved != 0 OR ge.userid = 3) ORDER BY ge.concept LIMIT 0, 10  


Warning: PostgreSQL query failed: ERROR: parser: parse error at or near "pivot" in /var/www/moodle-1.2/lib/adodb/drivers/adodb-postgres64.inc.php on line 589
ERROR: parser: parse error at or near "pivot" : ERROR: parser: parse error at or near "pivot"

Average of ratings: -
In reply to Karol Grondzak

Re: glossary and PostgreSQL problem

by Martin Dougiamas -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
Thanks for that, Karol.  Hopefully Will has time to look into it, otherwise I will.

There are a lot fewer Moodlers using/testing PostgreSQL (and on top of that PostgreSQL seems to break things every time they make a release angry) so we need all the help we can get identifying and fixing bugs like this, especially now as we move towards a new release.
Average of ratings: -
In reply to Martin Dougiamas

Re: glossary and PostgreSQL problem

by Williams Castillo -
I'll take care of it tonight...

I also wanted to create a couple of indexes in the main tables of the glossary in order to improve the speed of the module a bit.

This is a good excuse to make it so.. smile

However, the problem surprise me as that was one of the problems I was well aware when doing queries... Most of the places where a query was not common to all the dbms, (I think) I made the necessary steps to avoid such problems... sad

Anyway... I'll solve it tonight.

Regards,
Will
Average of ratings: -
In reply to Karol Grondzak

Re: glossary and PostgreSQL problem

by Williams Castillo -
Well.. Several fields were missing in the PosgreSQL squema... blush

cvs:/moodle/mod/glossary/db/postgres7.sql was changed. Please, get the version 1.12 and update your database with this file.. It should solve your problems. Please, tell me if you have any further problems.

Will
Average of ratings: -
In reply to Williams Castillo

Re: glossary and PostgreSQL problem

by Karol Grondzak -

Hi Will,

thank you for your work. It was a progress but it did not slove my problem completly. There was still a lot of problems. First of all - even the sql script did not work for me - I had to change it. But even when I had all tables OK, there were other problems - in sql.php were still some ucase functions and there was an error with naming columns. In my PostgreSQL select ge.xx pivot does not work, I has to be select ge.xxx as pivot. There was also problem with one select using LIMIT - result was that the listing of entries was always empty.

If you are interested, I can send you files from mod/glossary/* to find all my changes.

Average of ratings: -
In reply to Karol Grondzak

Re: glossary and PostgreSQL problem

by Williams Castillo -
Hi Karol,

Please, can you tell me the exact steps you are doing prior to get that error?
Also, could you please post the exact error message you are getting?
I don't know what that sql.php file is nor where that query are so please, help me to help you...

Thanks,

Will
PS: yes, it would be helpful if, thus, you send me your glossary/* files (zipped, please).
Average of ratings: -
In reply to Karol Grondzak

Re: glossary and PostgreSQL problem

by Williams Castillo -
Hi,

Please, refer to this discussion.

Hopefully, all compatibilities issues have been solved... Let us know.

Will
Average of ratings: -