Module sqldiff File

The module sqldiff config file allows for updates to the datamodel for a module. The file is checked on 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. While iterating, if the current module database version is less than the given version, the sql in the sql tag is executed.

The xml doctype for the config file should be defined as:

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

(Contains 0 to n diff tags)

diff

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)>