DB Schema maintenance -- Postgres needs help

DB Schema maintenance -- Postgres needs help

by Martín Langhoff -
Number of replies: 2
I whipped together a nasty mix of Perl and shell to do a quick'n'dirty check for missing indexes in Postgres. Here's what it's telling me: 

$ ~/mdldevtools/dbschemachecks.pl
Index count mismatch
  ./backup/db/mysql.sql: 7
  ./backup/db/postgres7.sql: 5
Index count mismatch
  ./lib/db/mysql.sql: 102
  ./lib/db/postgres7.sql: 86
Index count mismatch
  ./mod/choice/db/mysql.sql: 10
  ./mod/choice/db/postgres7.sql: 7
Index count mismatch
  ./mod/exercise/db/mysql.sql: 9
  ./mod/exercise/db/postgres7.sql: 15
Index count mismatch
  ./mod/forum/db/mysql.sql: 26
  ./mod/forum/db/postgres7.sql: 25
Index count mismatch
  ./mod/lesson/db/mysql.sql: 18
  ./mod/lesson/db/postgres7.sql: 19
Index count mismatch
  ./mod/quiz/db/mysql.sql: 51
  ./mod/quiz/db/postgres7.sql: 50
Index count mismatch
  ./mod/resource/db/mysql.sql: 3
  ./mod/resource/db/postgres7.sql: 2
Index count mismatch
  ./mod/scorm/db/mysql.sql: 9
  ./mod/scorm/db/postgres7.sql: 6
Index count mismatch
  ./mod/survey/db/mysql.sql: 12
  ./mod/survey/db/postgres7.sql: 10
Index count mismatch
  ./mod/workshop/db/mysql.sql: 14
  ./mod/workshop/db/postgres7.sql: 24

Perhaps we should aim to make it clean for 1.5 release?
Average of ratings: -
In reply to Martín Langhoff

Re: DB Schema maintenance -- Postgres needs help

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
I don't think it's counting primary keys ... eg resources look the same to me.  Still plenty of differences in there.
In reply to Martin Dougiamas

Re: DB Schema maintenance -- Postgres needs help

by Martín Langhoff -
It's a simplistic script, and resources is declaring the same index twice: "PRIMARY KEY (id);UNIQUE KEY (id)"

Primary keys are unique by design under MySQL, so it's a harmless redundancy. Well, no so harmless, it already hurt the credibility of my script ;)

I've put the script in cvs:contrib/devtools

(edit: damn! I thought we had cvs:foo/bar filters but it must be bugtracker-only)