Module sqldiff File

The module sqldiff config file allows for updates to the datamodel for a module. This is an outdated way to do things, modules should now use the liquibase.xml way in modules.

The file is checked on every startup of the module. The child diff tags are iterated over in a top down fashion. The earliest diff should be at the top and the most recent diff should be at the bottom. Each "diff" element is executed one at a time. As each one runs, the "version" is stored in a Global Property in the user's database: moduleid.database_version. The next time the module is started, ONLY "diff"s that have a "version" GREATER THAN the version stored in the moduleid.database_version will be run. This allows for easy upgrading for module users and module devs don't need to worry about who has what version.

It is very bad practice to edit "diff" elements. You should always add new ones. This is especially true after you release your module.

However, while developing your module you are free to edit the sql in the diffs. Before reinstalling the module though, you need to make sure your moduleid.database_version is LESS THAN the "version" of the diffs you want to run.

Put this at the top of your sqldiff.xml file for autocompletion help (this dtd isn't required by openmrs):

<!DOCTYPE sqldiff PUBLIC "-//OpenMRS//DTD OpenMRS SQL Diff Config 1.0//EN" "http://resources.openmrs.org/doctype/sqldiff-1.0.dtd">

(The sqldiff file contains 0 to n diff tags)

The "diff" element contains:

version: String referring to the unique extension point labeled the same in the code
author: Any string referring to the person that created this. Usually use for openmrs id username.
date: Only used for convenience. Should be the date the sql was added to the file
description: Text describing what the sql diff is doing. Only used for convenience when reading through sqldiff file later
sql: Contains the sql statements. Separate statements should be delimited by semi colons: ";"

example sqldiff.xml file:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqldiff PUBLIC "-//OpenMRS//DTD OpenMRS SQL Diff Config 1.0//EN" "http://resources.openmrs.org/doctype/sqldiff-1.0.dtd">

<sqldiff version="1.0">
	<help>
		USE:
			The diffs are ordered by datamodel version number.
			The script can be run in a top down fashion and is
			expected to not failor overwrite old data

		EXPECT:
			- "use business-database-name;" was called prior to
			   calling this script
	</help>

	<diff>
		<version>0.8</version>
		<author>Ben Wolfe</author>
		<date>May 10th 2010</date>
		<description>
			Creating the formenty_xsn table to store the infopath xsn files
		</description>
		<sql>

			CREATE TABLE IF NOT EXISTS `formentry_xsn` (
			  `formentry_xsn_id` int(11) NOT NULL auto_increment,
			  `form_id` int(11) NOT NULL,
			  `xsn_data` longblob NOT NULL,
			  `creator` int(11) NOT NULL default '0',
			  `date_created` datetime NOT NULL default '0000-00-00 00:00:00',
			  `archived` int(1) NOT NULL default '0',
			  `archived_by` int(11) default NULL,
			  `date_archived` datetime default NULL,
			  PRIMARY KEY  (`formentry_xsn_id`),
			  KEY `User who created formentry_xsn` (`creator`),
			  KEY `Form with which this xsn is related` (`form_id`),
			  KEY `User who archived formentry_xsn` (`archived_by`),
			  CONSTRAINT `User who created formentry_xsn` FOREIGN KEY (`creator`) REFERENCES `users` (`user_id`),
			  CONSTRAINT `Form with which this xsn is related` FOREIGN KEY (`form_id`) REFERENCES `form` (`form_id`),
			  CONSTRAINT `User who archived formentry_xsn` FOREIGN KEY (`archived_by`) REFERENCES `users` (`user_id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;

		</sql>
	</diff>

	<diff>
		<version>0.9</version>
		<author>Ben Wolfe</author>
		<date>May 18th 2010</date>
		<description>
			Adding uuid column to formentry_xsn table
		</description>
		<sql>
			ALTER TABLE formentry_xsn ADD uuid CHAR(38);
			UPDATE formentry_xsn SET uuid = UUID() WHERE uuid is null;
			ALTER TABLE formentry_xsn MODIFY uuid char(38) NOT NULL;
			CREATE UNIQUE INDEX formentry_xsn_uuid_index ON formentry_xsn (uuid);
		</sql>
	</diff>

</sqldiff>

Notice that a SECOND "diff" element was appended instead of modifying the first one!

sqldiff-1.0.dtd:

If you really want it, the viewable contents of http://resources.openmrs.org/doctype/sqldiff-1.0.dtd made available here:

<?xml version="1.0" encoding="UTF-8"?>

<!--
Top level configuration element.
\-->
<!ELEMENT sqldiff (
(help?),
(diff*)
)>
<!ATTLIST sqldiff version CDATA #FIXED "1.0">

<!ELEMENT diff (version, author, date, description, sql)>