).I'll post some of the differences to the bug tracker, but in general, when you change a database def, be sure to do it in both places!
Thanks
Jason
This is a general plea to all Moodle developers hacking away on 1.6. Please don't forget the postgres users. I've turned up some very large discrepancies between the mysql definitions and the postgres definitions in the questions module and the database module (why have you abandoned us, Martin? Why?).
I'll post some of the differences to the bug tracker, but in general, when you change a database def, be sure to do it in both places!
Thanks
Jason
).I'll post some of the differences to the bug tracker, but in general, when you change a database def, be sure to do it in both places!
Thanks
Jason
Also remember that when you are adding a column in mysql, postgres does not supoprt the ADD COLUMN.. AFTER syntax. So either add your new column to the end of the current table, or you'll need to write some very ugly sql to copy the data from the old table into a new table with the correct column order.
People should *always* be using the table_column() function to modify or add fields - it should be taking care of this on both platforms.
Apologies for discrepencies in Database, Jason, it's not intentional I assure you because they look OK to me! 
Yes, please file bug reports ... This is why we have these testing periods. Most developers don't use PostgreSQL at all, so we rely on those who do to test Moodle there.
Hopefully these will be a thing of the past in Moodle 1.7, which looks like it'll finally have the XML schema we've talked about for so long.
Yes, please file bug reports ... This is why we have these testing periods. Most developers don't use PostgreSQL at all, so we rely on those who do to test Moodle there.
Hopefully these will be a thing of the past in Moodle 1.7, which looks like it'll finally have the XML schema we've talked about for so long.
Hi,
I have been trying to look at this. Please excuse the DDL statements as I am working with several tools and removing some of the noise its reporting to me. Here is so far what I have found:
lesson_essay table does not seem to exist on mysql, in this case it looks be the other way around. Or maybe the intent was not to use lessay_essay table. Anyone with better historical understanding of lesson module?
DROP TABLE mdl_lesson_essay;
-- lesson_essay table only exists on postgresql, was upgraded on 2004072100
-- missing on both mysql.sql and mysql.php
Table scorm and excercise_submissions is out of sync if its a fresh install, but will have no problem if its been upgraded. So I guess I can file a bug report for the 2 tables?
ALTER TABLE mdl_scorm
ADD COLUMN version character varying(9) DEFAULT ''::character varying NOT NULL;
-- properly added on postgres7.php version 2005041600, but not present on postgres7.sql
ALTER TABLE mdl_exercise_submissions
ADD COLUMN late smallint DEFAULT 0::smallint NOT NULL;
-- properly added on postgres7.php version 2003121000, but not present on postgres7.sql
Question type rqp seems to be out of sync, but I am not sure how to go about this. Maybe its in active development?
CREATE TABLE mdl_question_rqp_servers (
typeid integer DEFAULT 0 NOT NULL,
can_author smallint DEFAULT 0::smallint NOT NULL,
can_render smallint DEFAULT 0::smallint NOT NULL,
url character varying(255) DEFAULT ''::character varying NOT NULL,
id serial NOT NULL
);
ALTER TABLE mdl_question_rqp_types
DROP COLUMN cloning_server,
DROP COLUMN flags,
DROP COLUMN rendering_server;
I ignored the differences between mysql and postgresql in terms of column default values and not null constraints. I also ignored for the differences in indexes. I also ignored and removed any data type differences, hopefully I did not miss columns that where intentionally changed to a different data type. Anyone else looking for differences with pgsql and mysql?
Jun
I have been trying to look at this. Please excuse the DDL statements as I am working with several tools and removing some of the noise its reporting to me. Here is so far what I have found:
lesson_essay table does not seem to exist on mysql, in this case it looks be the other way around. Or maybe the intent was not to use lessay_essay table. Anyone with better historical understanding of lesson module?
DROP TABLE mdl_lesson_essay;
-- lesson_essay table only exists on postgresql, was upgraded on 2004072100
-- missing on both mysql.sql and mysql.php
Table scorm and excercise_submissions is out of sync if its a fresh install, but will have no problem if its been upgraded. So I guess I can file a bug report for the 2 tables?
ALTER TABLE mdl_scorm
ADD COLUMN version character varying(9) DEFAULT ''::character varying NOT NULL;
-- properly added on postgres7.php version 2005041600, but not present on postgres7.sql
ALTER TABLE mdl_exercise_submissions
ADD COLUMN late smallint DEFAULT 0::smallint NOT NULL;
-- properly added on postgres7.php version 2003121000, but not present on postgres7.sql
Question type rqp seems to be out of sync, but I am not sure how to go about this. Maybe its in active development?
CREATE TABLE mdl_question_rqp_servers (
typeid integer DEFAULT 0 NOT NULL,
can_author smallint DEFAULT 0::smallint NOT NULL,
can_render smallint DEFAULT 0::smallint NOT NULL,
url character varying(255) DEFAULT ''::character varying NOT NULL,
id serial NOT NULL
);
ALTER TABLE mdl_question_rqp_types
DROP COLUMN cloning_server,
DROP COLUMN flags,
DROP COLUMN rendering_server;
I ignored the differences between mysql and postgresql in terms of column default values and not null constraints. I also ignored for the differences in indexes. I also ignored and removed any data type differences, hopefully I did not miss columns that where intentionally changed to a different data type. Anyone else looking for differences with pgsql and mysql?
Jun
Hi Jun,
Yes, the lesson_essay table should not exist
Since the only two references to this table exist in postgres7.php and .sql then I'll try to remove them as soon as possible. Unfortunately, now I have time, but CVS is not responding 
Cheers,
Mark
Yes, the lesson_essay table should not exist
Since the only two references to this table exist in postgres7.php and .sql then I'll try to remove them as soon as possible. Unfortunately, now I have time, but CVS is not responding Cheers,
Mark
Thanks Mark. I will include a drop table for lesson essay on my patch.
As for scorm.version it seems that it must exist but an upgrade script on postgres7.php was dropping it. I think it was mistakenly thought to get the postgres upgrade cleanup. It exists on mysql and was upgraded on mysql.php as of scorm 2005041600. Putting it back on my patch.
As for scorm.version it seems that it must exist but an upgrade script on postgres7.php was dropping it. I think it was mistakenly thought to get the postgres upgrade cleanup. It exists on mysql and was upgraded on mysql.php as of scorm 2005041600. Putting it back on my patch.
I will include a drop table for lesson essay on my path.
Great! It also needs to be removed from the postgres7.sql file. Are you going to do that as well, or shall I do it when I can?
Cheers,
Mark
Great! It also needs to be removed from the postgres7.sql file. Are you going to do that as well, or shall I do it when I can?
Cheers,
Mark
Hi Mark,
I had removed it from the postgres7.sql file. Thanks.
Jun
I had removed it from the postgres7.sql file. Thanks.
Jun
Hi,
If anyone is interested on how is the postgresql fixes is getting a long, you can see the fixes here. Its pretty much done, just doing some more testing.
I would glady accept sanitized 1.5 stable pg dumps for testing.
If anyone is interested on how is the postgresql fixes is getting a long, you can see the fixes here. Its pretty much done, just doing some more testing.
I would glady accept sanitized 1.5 stable pg dumps for testing.