Although I consider keeping SQL schema versioned a good habit, and several great solutions exist that automatize the task of creating migration scripts to transform the schema of the database from version A to B, for most of my projects, I find it sufficient to record a hand-crafted piece of SQL in the project/issue log. For latter, I mostly use Redmine, which offers a nice REST-style API for the issue tracker. Since it returns XML, I chose XSL to do the necessary transformations to extract the SQL statements stored in the issue logs.
For purposes of configuration, I chose something already in the system: Git, my choice of SCM solution. One can store hierarchical key-value pairs in a systemwide, user- or repository-specific way, all transparently accessible through a simple command line interface. For purposes of bridging the gap between Git and the XML/XSL, I chose shell scripting and xsltproc since producing a working prototype is only a matter of minutes.
The end product is a shell script that extracts the Git-style history
expression from command line and passes it directly to the git logcommand,
which in turn parses it just like the user would assume. The output is
formatted in a way that the only output is the first lines of commit messages
in the specified range of commits. If the commancd fails, the original
message is shown, so the script doesn't need to know anything about
git commit range parsing or other internals.
GL=$(git log --pretty=format:"%s" --abbrev-commit )
if [ $? -ne 0 ]; then
echo "Git error occured: $GL" 1>&2
exit 1
fi
Since the HTML-formatted issue log messages are double-encoded in the API XML
output, two round of XSL transformation needs to be done. The first round
extracts log entries probably containing SQL fragments and with the output
method set to text, it decodes HTML entities embedded into XML.
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>
<xsl:template match="/">
<xsl:for-each
select="issue/journals/journal/notes[contains(text(), 'sql')]">
<xsl:value-of select="text()"/>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
The above XSL takes the output of the XML REST API and produces XHTML fragments
for every issue log entry. The following part of the shell script extracts the
issue numbers from the commit messages (egrep and sed), calls the issue API
(curl) with each ID exactly once (sort -u), passes the output through
the first XSL and concatenate these along with an artifical/fake XML root in
order to produce well-formed XML, ready for the second pass.
echo '<?xml version="1.0" encoding="utf-8"?><fakeroot>'
echo "$GL" | egrep -o '#[0-9]+' | sort -u | sed 's/#//' \
| while read ISSUE; do
curl --silent "$BASE/issues/$ISSUE.xml?key=$KEY&include=journals" \
| xsltproc "$DIR/notes.xsl" -
done
echo '</fakeroot>'
The second pass extracts code tags with language set as sql, and the method
is again set to text, causing a second expansion of HTML entities. The output
of this final XSL transformation is a concatenation of SQL statements required
to transform the database schema to be in sync with the commit range specified.
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>
<xsl:template match="/">
<xsl:for-each select="fakeroot/pre/code[@class = 'sql']">
<xsl:value-of select="normalize-space(text())"/>
<xsl:text> </xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
While this stylesheet follows almost the same logic as the first one, it's
worth noting the usage of normalize-space() and a literal newline, which
formats the output in a nice way – SQL fragments are separated from each other
by a single newline, no matter if there's any trailing or leading whitespace
present in the code. The code is available under MIT license on GitHub.