This SQL needs to be placed in a file called mysql.sql in the db folder of the module. If other database types are to be supported, additional files need to be created here for those vendors. For instance, the PostGreSQL 7 equivalent schema definition would be stored in a file named postgres7.sql. An accompanying PHP file is also necessary to install the SQL script. Again, a separate file is required for each vendor. The default mysql.php file included with the NEWMODULE template is all that is necessary for the first version of the module. Subsequently, switch statements can be used to execute SQL statements that update the database schema depending upon the version installed.

In order to comply with the Moodle API, the SQL statements stored in the scripts should use the prefix “prefix_” instead of “mdl_”. This is because, as part of the Moodle installation, it is possible for the user to nominate a table prefix – so it shouldn’t be hard-coded into the scripts. Instead, using the prefix_ template, Moodle will automatically update the script prior to execution, making it automatically adapt to the user’s configuration. Thus, our mysql.sql file now looks like this:

CREATE TABLE prefix_draganddrop(

  `id` int(10) unsigned NOT NULL auto_increment,

  `course` int(10) unsigned NOT NULL default ‘0’,

  `name` varchar(255) NOT NULL,

  `title` varchar(255) NOT NULL,

  `answer` varchar(255) NOT NULL,

  `timemodified` int(10) unsigned NOT NULL default ‘0’,

  PRIMARY KEY  (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE prefix_draganddrop_images (

  `id` int(11) NOT NULL auto_increment,

  `url` varchar(255) NOT NULL,

  `ddid` int(11) NOT NULL,

  PRIMARY KEY  (`id`)

);