Moodle in English: Postgresql compatibility?

Postgresql compatibility?

by Johnathan Kemp - Wednesday, October 8, 2008, 10:30 PM

I am running tests with postgresql 8.2.10 and 8.3.4 and am finding a number of errors occuring with the game module.

I am not sure if some of the issues are caused by moodle/lib/dmllib.php or with the way calls to it are being made. However some of the errors appear to be directly related to the game files being incompatible with the syntax and functions of postgresql.

I am no expert and I could be wrong

but here are a couple of examples.

Attempting to run importsudoku.php results in

Fatal error: Call to undefined function execute_sql() in C:\Apache2\htdocs\moodle\mod\game\db\importsudoku.php on line 4

Attempting to run a hangman game which has been set up with a quiz, glossary or questions results in the following error.

ERROR: LIMIT #,# syntax is not supported HINT: Use separate LIMIT and OFFSET clauses.

The above error also results when trying to run a Sudoku or hidden picture against a glossary.

I don't know if I have failed to set up something critical in the questions and quizes but the Crossword and Cryptex both work from the Glossary but not from a quiz or question, which fail to find any word. However the snakes and ladders works fine from both quiz and glossary.

If someone could confirm that this module works with postgresql that would be useful, as would any pointers as to what I might be doing wrong.

environment is
Windows 2000, apache 2.2.8 postgresql 8.3.4 (some tests also run against postgresql 8.2.10) php 5.2.5 and moodle 1.9+ build 20081007.

Thanks

Johnathan

Re: Postgresql compatibility?

by Vasilis Daloukas - Friday, October 10, 2008, 3:52 AM

I will test it. I tested only with mysql.

Average of ratings: -

Re: Postgresql compatibility?

by Vasilis Daloukas - Sunday, October 12, 2008, 3:14 AM

I made some change for compatibility with database postgres.
If you still have problems please tell me.

Average of ratings: -

Re: Postgresql compatibility?

by Johnathan Kemp - Monday, October 13, 2008, 10:55 PM

Hello Vasilis,

Thank you for your help.

I have downloaded the latest files from CVS this morning but hit the following when I used CVS update.

P backuplib.php
cvs update: checksum failure after patch to ./backuplib.php; will refetch
P locallib.php
cvs update: checksum failure after patch to ./locallib.php; will refetch
cvs client: refetching unpatchable files
cvs update: warning: 'backuplib.php' was lost
U backuplib.php
cvs update: warning: 'locallib.php' was lost
U locallib.php

Success, CVS operation completed

I checked the date stamp of the files named and they had today's date so I am guessing they are OK.

I have now run tests against every type of game. Below are details of any issues I have found.

Hangman
Glossary worked OK, but with either Question or Quiz the following error message was displayed when I tried to play the game.

Error: Turn off debugging to hide this error.

SELECT mdl_question.id, mdl_question.questiontext as questiontext, mdl_question_answers.answer as answertext, mdl_question.id as questionid, 0 as glossaryentryid, '' as attachment FROM mdl_question,mdl_question_answers WHERE mdl_question.id=4 AND mdl_question_answers.question=4 AND mdl_question.hidden=0(with limits 0, 100)

Found more than one record in get_record_sql !

Array

 [4] => Array

 [questiontext] => What musical instrument comes in types such as free bass, stradella, piano and button

 [answertext] => Violin

 [questionid] => 4

 [glossaryentryid] => 0

 [attachment] =>

And after the continue button

Notice: Trying to get property of non-object in C:\Apache2\htdocs\moodle\mod\game\hangman\play.php on line 36

Notice: Trying to get property of non-object in C:\Apache2\htdocs\moodle\mod\game\hangman\play.php on line 80

Notice: Trying to get property of non-object in C:\Apache2\htdocs\moodle\mod\game\hangman\play.php on line 81

Notice: Trying to get property of non-object in C:\Apache2\htdocs\moodle\mod\game\hangman\play.php on line 82

Notice: Trying to get property of non-object in C:\Apache2\htdocs\moodle\mod\game\hangman\play.php on line 83

Notice: Undefined property: stdClass::$try in C:\Apache2\htdocs\moodle\mod\game\hangman\play.php on line 365

Warning: Division by zero in C:\Apache2\htdocs\moodle\mod\game\hangman\play.php on line 380

Crossword
Glossary was OK. Quiz and Question fail in that they only find one short answer question and construct a crossword which asks that question, but only displays numbers for each letter of the answer horizontally and the number 1 down the left column. No squares are displayed and there is no clickable section of the crossword matrix.

Cryptex
Glossary is OK
Quiz and Question fail in that it only finds one of the short answer questions and creates a single line cryptex that contains the answer and no other random letters.

Millionaire
This works for both Quiz and Question. One point worth noting.
When you change the default game setting from hangman to Millionaire, the source changes to offer Question or Quiz, with Questions selected, but the menu below this is still select Glossary. If you want to use Questions, you have to change to Quiz and back again to Questions to obtain a listing of the Question categories.

Sudoku
All version of Sudoku worked OK.

Snakes and Ladders
All version worked OK.

Hidden Picture
When configuring the Hidden Picture game clicking on the "Set the text at the bottom" link resulted in

Notice: Undefined property: stdClass::$course in C:\Apache2\htdocs\moodle\mod\game\bottomtext.php on line 35

This message resulted whether text had been entered for text at the bottom, or not.

All versions failed to play.

I have four entries in the Glossary, each in the category "Testing" and each with a picture attached to them.

Attempting to play a game resulted in the following error

There are no glossary entries on glossary 'jkTestForGame' with an attached picture

Stack trace:

line 1651 of lib\deprecatedlib.php: call to debugging()
line 110 of mod\game\hiddenpicture\play.php: call to error()
line 33 of mod\game\hiddenpicture\play.php: call to game_hiddenpicture_selectglossaryentry()
line 132 of mod\game\attempt.php: call to game_hiddenpicture_continue()

line 81 of mod\game\attempt.php: call to game_create()

I hope this helps in getting things working even better.

Kind regards

Johnathan

Average of ratings: -

Re: Re: Postgresql compatibility?

by Vasilis Daloukas - Tuesday, October 14, 2008, 5:02 AM

Please check it again and tell me which problems still exist?

Average of ratings: -

Re: Re: Postgresql compatibility?

by Johnathan Kemp - Wednesday, October 15, 2008, 6:01 PM

Hello Vasilis,

I have updated from CVS with one slight issue but I believe the updated locallib.php was successfully downloaded. The CVS messages were as follows (I have edited out the login messages)

Sequence of CVS update

P bottomtext.php
P locallib.php
cvs update: checksum failure after patch to ./locallib.php; will refetch
cvs client: refetching unpatchable files
cvs update: warning: 'locallib.php' was lost
U locallib.php

Success, CVS operation completed.

I compared the locallib.php file from CVS and my current version and it appears that I have successfully obtained your updated file.

Now for the testing.

Hangman Glossary

continues to work correctly

Hangman - questions

This now offers a game, but it appears to select the wrong answer.
Question A has answers
1) Accordion grade 100%,
2) Melodeon grade none,
3) Violin grade none

Question B has answers
1) Mozart grade 100%,
2) Beethoven grade none
3) Bach grade none

test games

1. asked question A but used answer 3)
2. asked question B but used answer 3)
3. asked question B but used answer 3)
4. asked question B but used answer 3)
5. asked question A but used answer 3)

So rather than using the question with the 100% grade it is taking the last question in the list?

Hangman - quiz

test games

1. asked question B but used answer 3)
2. asked question B but used answer 3)
3. asked question A but used answer 3)

So it looks like this is behaving the same as above.

Crossword Glossary

Continues to work correctly

Crossword Questions and Crossword Quiz

This is still showing incorrectly, it only uses one question from the two short answer questions available.

Using question A above, where the answer is Accordion, the crossword displayed just shows

1 2 3 4 5 6
1

This also suggests that it is working with the wrong answer i.e. violin

I tried both the Check Crossword and the End of Crossword Game buttons to try to refresh the faulty display, but they appeared to have no effect.

Cryptex Glossary

Continues to work correctly

Cryptex Questions and Cryptex Quiz

In 9 tests (5 with questions and 4 with Quiz) it always only uses question A, building a one word cryptex that displays the word "VIOLIN".

The actual game mechanism is working, but the generation of the cryptex is failing to detect the correct answer, is failing to detect more than one question and is failing to generate any squares that are not part of a correct answer.

Millionaire Questions and Quiz

This is working correctly.

At present I have only two test questions, so they keep repeating. Is there any mechanism in place to ensure that once there are more than 15 questions available, that the same question will not be asked twice in the same game?

Sudoku

All versions are working correctly. When it uses the short answer questions it correctly identifies the right answers i.e for question A) Accordion and for question B) Mozart.

Snakes and Ladders

All versions are working correctly.

Hidden picture (all versions)

When configuring the "Set the text at the bottom" this now configures correctly without throwing an error.

However none of the versions of hidden picture are showing a picture, instead they throw the following message. Also the text entered to be displayed at the bottom of the screen fails to be displayed (probably because of the error?).

There are no glossary entries on glossary 'jkTestForGame' with an attached picture

Stack trace:

* line 1651 of lib\deprecatedlib.php: call to debugging()

* line 110 of mod\game\hiddenpicture\play.php: call to error()

* line 33 of mod\game\hiddenpicture\play.php: call to game_hiddenpicture_selectglossaryentry()

* line 132 of mod\game\attempt.php: call to game_hiddenpicture_continue()

* line 81 of mod\game\attempt.php: call to game_create()

Thanks again for your help.

Johnathan

Average of ratings: -

Re: Re: Re: Postgresql compatibility?

by Vasilis Daloukas - Thursday, October 16, 2008, 5:58 AM

Hi Johnathan,

Hangman - questions
Hangman - quiz
Crossword Questions and Crossword Quiz
Cryptex Questions and Cryptex Quiz

I fixed the problem

Hidden picture (all versions)

You have to attach pictures (.jpg files in each glossary entry)

Please check it again.

Best regards
Vasilis

Average of ratings: -

Re: Re: Re: Postgresql compatibility?

by Johnathan Kemp - Monday, October 20, 2008, 6:11 PM

Hello Vasilis,

Here are the results of further testing following your recent modifications.

Hangman Glossary

continues to work correctly

Hangman - questions and quiz

This offers a game and selects a correct answer to base the hangman on. However after guessing the word correctly the message displayed after "Congratulations" is not the feedback to the correct answer, but appears to be consistently the feedback to the last incorrect question.

Crossword all versions

Appear to be working correctly

Cryptex all versions

Appear to be working correctly

Millionaire Questions and Quiz

This is working correctly.

Sudoku all versions

Appear to be working correctly

Snakes and Ladders all versions

Appear to be working correctly.

Would it be possible after entering an answer, to reset the focus to the answer box, ready for the next answer to be entered?

Hidden picture (all versions)

I have been able to identify the source of the problem with the Hidden Picture game failing to find any glossary entries with pictures attached. The identification of picture file extensions is case sensitive. It works fine if I attach a file myfile.jpg but fails if the file is called myfile.JPG

In earlier tests my attached pictures had names that had been generated in upper case!

I have two test accounts a learner test account and a teacher test account. As I have now built up a number of test attempts I decided to delete the test attempts for the teacher account. When logged on as the teacher test account I went to each game results page, selected the attempts made by the teacher test account and changed the "with select" drop down to Delete. All games appear to produce the same error messages as shown below. As the error message duplicates for each question I have removed some of the duplicate messages.

Debugging is set to show all reasonable PHP debug messages.

The following error text is displayed for each attempt deleted, except for the last remaining attempt for a user

ERROR: relation "mdl_game_questions" does not exist

DELETE FROM mdl_game_questions WHERE attemptid = '45'

* line 1377 of lib\dmllib.php: call to debugging()

* line 41 of mod\game\report\overview\report.php: call to delete_records()

* line 72 of mod\game\report.php: call to game_report->display()

The last remaining attempt for a user produces this error message,

Could not find any user attempts

ERROR: relation "mdl_game_questions" does not exist

DELETE FROM mdl_game_questions WHERE attemptid = '72'

* line 1377 of lib\dmllib.php: call to debugging()

* line 41 of mod\game\report\overview\report.php: call to delete_records()

* line 72 of mod\game\report.php: call to game_report->display()

Kind regards

Johnathan

Game Results screen - deleting test game results

Average of ratings: -

Re: Re: Re: Re: Postgresql compatibility?

by Vasilis Daloukas - Monday, October 27, 2008, 7:28 AM

Please check it again

Average of ratings: -

Re: Re: Re: Re: Postgresql compatibility?

by Johnathan Kemp - Tuesday, October 28, 2008, 12:13 AM

Hello Vasilis,

The hangman questions and hangman quiz games are now returning the correct answer feedback.

The deletion of game attempts now appears to work without error.

The one issue that I am still hitting is the use of the picture game. However the situation has changed. Prior to the latest changes the file extension appeared to be case sensitive, now I can't get the game to run at all, whether the pictures end in .jpg or .JPG

When I try to run a game the following errors are displayed.

ERROR: column reference "id" is ambiguous LINE 1: SELECT id,attachment FROM mdl_glossary_entries ge,mdl_glossa... ^

SELECT id,attachment FROM mdl_glossary_entries ge,mdl_glossary_entries_categories gec WHERE ge.glossaryid=1 AND gec.entryid = ge.id AND gec.categoryid = 1 AND concept NOT LIKE '% %' AND attachment LIKE '%.%'
line 686 of lib\dmllib.php: call to debugging()
line 609 of lib\dmllib.php: call to get_recordset_sql()
line 930 of lib\dmllib.php: call to get_recordset_select()
line 98 of mod\game\hiddenpicture\play.php: call to get_records_select()
line 33 of mod\game\hiddenpicture\play.php: call to game_hiddenpicture_selectglossaryentry()
line 132 of mod\game\attempt.php: call to game_hiddenpicture_continue()
line 81 of mod\game\attempt.php: call to game_create()

There are no glossary entries on glossary 'jkTestForGame' with an attached picture.

Kind regards

Johnathan

Average of ratings: -

Re: Re: Re: Re: Re: Postgresql compatibility?

by Vasilis Daloukas - Tuesday, October 28, 2008, 4:30 AM

Please check it again

Average of ratings: -

Re: Postgresql compatibility?

by Johnathan Kemp - Tuesday, October 28, 2008, 8:05 PM

Hello Vasilis,

Thanks for all your quick responses.

I can confirm that the picture games (Glossary, quiz and question) are now displaying pictures whether they are .jpg or .JPG and the "ID is ambiguous" error is now also resolved.

I think that completes this thread.

Kind regards

Johnathan

Average of ratings: -

Re: Re: Postgresql compatibility?

by Vasilis Daloukas - Wednesday, October 29, 2008, 7:38 AM

Thanks for your help.

Average of ratings: -

Game (plugin)

Postgresql compatibility?