Wednesday, February 08, 2006

XML/XSL based changelog from ViewCVS

For some time now I have been working on generating useful changelogs from a ViewVC/ViewCVS MySQL database. After the hassles I talked about already I finally got it working quite fine. Because the people who are interested in the changelogs like GUIs, I wrote a little Swing application around the core functionality that I can't give away. But the real work is done in just a single SQL query and some XML/XSLT processing.

Basically there are three steps to be done:

  1. Query ViewCVS database
  2. Generate XML based on the result
  3. Transform with XSLT to something readable

I use a query like the following, because we usually do changelogs per branch and for a certain period of time. There is no easy way to query tags, because they are not part of the database, but we found it quite convenient to just keep a list of tags and their associated timestamps. To query the head, look for branch=''.

select c.type, c.ci_when, p.who, b.branch, 
       SUBSTRING_INDEX(d.dir, '/', 1) as project, 
       cast(concat_ws('/', d.dir,f.file) as CHAR) as file, 
       c.revision, descs.description
from checkins c 
  join branches b on
  join files f on
  join dirs d on
  join repositories r on
  join descs on
  join people p on
where b.branch='aBranchname'
  and ci.when between 'timestamp1' and 'timestamp2'
order by project, description, dir, file, revision

If you like to query by checkin comment, you might consider placing a fulltext index on the description column in the descs table.

The result from that can now be translated into XML. The schema is similar to that of the script, but I adapted it a little to better suit our needs, e. g. to allow grouping by a checkin ticket number. The following code fragment performs the generation of an XML document. It needs to be "padded" somewhat to be runnable.

I have attached a file that contains the following code and some more utilities, including the XSL stylesheet. Unfortunately I cannot upload an archive containing the original files.

 * Create a Map based on the ResultSet containing the rows from the ViewCVS query.
 * @param aResultSet the ResultSet 
 * @return a Map, having the detected ticket numbers as keys and Lists of CvsCommitEntrys as values.
 * @throws SQLException if something unexpected happens on the JDBC layer
private Map generateEntryMap(ResultSet aResultSet) throws SQLException {
 Map entryMap = new HashMap();
 String ticketNumber;
 Pattern tPattern = Pattern.compile("(WK|RWK|RDE|RIT|RQ|REQ)\\d{1,8}");
 while ( {
  CvsCommitEntry tEntry = new CvsCommitEntry();
   String tType = aResultSet.getString("type");
  if ("Remove".equals(tType)) {
  } else {
   Matcher tMatcher = tPattern.matcher(tEntry.getDescription());
  if (tMatcher.find()) {
   ticketNumber =;
  } else {
   // this text if no ticket number was found
   ticketNumber = "ohne Fehlernummer";
  if (entryMap.containsKey(ticketNumber)) {
   List tList = (List)entryMap.get(ticketNumber);
  } else {
   List tList = new Vector();
   entryMap.put(ticketNumber, tList);
 return entryMap;
 * Creates an XML file or console output based on a Map of CvsCommitEntrys.
 * @param someEntries the Map of entries to process
private void generateOutput(Map someEntries)  {
 Map tParamMap = new HashMap();
 /* where to put the resulting file */
 String tOutputDir = "/tmp/outputdir";
 ByteArrayOutputStream tByteArrayOutputStream = new ByteArrayOutputStream();
 PrintWriter outwriter = new PrintWriter(new OutputStreamWriter(tByteArrayOutputStream));
 DateFormat tDateOnly = new SimpleDateFormat("yyyy-MM-dd", Locale.ENGLISH);
 DateFormat tTimeOnly = new SimpleDateFormat("HH:mm", Locale.ENGLISH);
 DateFormat tDateTime = new SimpleDateFormat("yyyy-MM-dd, HH:mm", Locale.ENGLISH);
 DateFormat tWeekdayFormat = new SimpleDateFormat("EEEE", Locale.ENGLISH);

 /* the following are just for the headline of the changelog */
 tParamMap.put("starttag", "tagname");
 tParamMap.put("endtag", "anothertagname");
 tParamMap.put("branch", "branchname");
 outwriter.println(startTag("changelog", tParamMap));
 Object[] tKeys = someEntries.keySet().toArray();
 for (int i = 0; i < tKeys.length; i++) {
  String tKey = (String)tKeys[i];
  List tEntries = (List)someEntries.get(tKey);
  CvsCommitEntry tEntry = (CvsCommitEntry)tEntries.get(0); // get the first for some common data
  outwriter.println(startTag("errorcode") + tEntry.getErrorNumber() + endTag("errorcode"));
  outwriter.println(startTag("date") + tDateOnly.format(tEntry.getTime()) + endTag("date"));
  outwriter.println(startTag("weekday") + tWeekdayFormat.format(tEntry.getTime()) + endTag("weekday"));
  outwriter.println(startTag("author") + tEntry.getAuthor() + endTag("author"));
  for (Iterator tIterator = tEntries.iterator(); tIterator.hasNext();) {
  CvsCommitEntry tCurrentEntry = (CvsCommitEntry);
   if (tCurrentEntry.getErrorNumber().equals("ohne Fehlernummer")) {
    // no ticket number was found, add the author to each entry 
    outwriter.println(startTag("author") + tCurrentEntry.getAuthor() + endTag("author"));
   outwriter.println(startTag("name") + replaceXmlSpecialChars(tCurrentEntry.getFile()) + endTag("name"));
   outwriter.println(startTag("cvsstate") + tCurrentEntry.getState() + endTag("cvsstate"));
   outwriter.println(startTag("revision") + tCurrentEntry.getRevision() + endTag("revision"));
   outwriter.println(startTag("branch") + tCurrentEntry.getBranch() + endTag("branch"));
   outwriter.println(startTag("tag") + endTag("tag"));
   if (tCurrentEntry.getErrorNumber().equals("ohne Fehlernummer")) {
    outwriter.println(startTag("time") + tDateTime.format(tCurrentEntry.getTime()) + endTag("time"));
   } else {
    outwriter.println(startTag("time") + tTimeOnly.format(tCurrentEntry.getTime()) + endTag("time"));
   outwriter.println(startTag("msg") + replaceXmlSpecialChars(tCurrentEntry.getDescription())
     + endTag("msg"));



The resulting XML can be transformed with the included XSL template. For more information on the grouping based on the "Muenchian Method" used in the stylesheet refer to this very good description. I learned how to do it from there, too.

If you put XML and XSL into the same directory, opening the XML with a browser will usually cause it to render the output according to the stylesheet automatically. Of course you can also use Xalan or some other processor to render it statically.

No comments: