Category Archives: Software

Making Relational Databases Hierarchical

I’m not one who really enjoys writing code. In fact, I tend to avoid it whenever possible as it’s just not that interesting to me. That said from time to time I have projects to complete where no existing software quite meets my needs and writing my own makes the most sense. This summer I was in the midst of collecting information and publishing a large extended family directory.

Clearly, the best way to do this is to store the data in a database. Because relational databases are most prevalent, I already have quite a bit of experience with MySQL and I knew I wanted to eventually make the data available via a PHP web application I decided to go with a MySQL backend. The trick here is that families aren’t as relational as you might originally think. Families are really hierarchical data (parents, children, grandchildren, etc.) and relational databases have a hard time storing and recalling things based on these types of relationships. For example, a typical family query might be one for a list of all descendants of someone. As it turns out families aren’t the only hierarchical data people might try to store in a relational database. Any business database which shows managers and reporting personnel is also a hierarchical data set albeit usually with fewer levels and much less complication (no need to track divorces, remarriages, spouses, etc.)

I was able to find and read up on several methods for storing the relational information but the one I ended up using is that proposed by Rob Volk of SQLTeam where the expensive (computationally) hierarchical relationships are stored in two additional fields for each record (depth and lineage) with parseable separators between generations. The database is initially loaded through a series of several (expensive) recursive queries but then can be maintained through triggers when information is added or updated.

Exploring Music Notation

In addition to my technical interests I’m involved in many other things including music. Great advances have been made in the integration of music and computers in the last 20 years. As with many other industries, computers have changed the way music is created, disseminated and recorded. One of the many areas where computers have been used is in the typesetting of sheet music. This has allowed for major music publishers to reduce costs and composers, arrangers and musicians to widely disseminate music without the need for an engraver and publisher.

Like many advances the transition from hand engraved music scores to computer typeset scores has not been without problems. Most notably computer typeset music can be harder to read and interpret by musicians and simply doesn’t look as musical or beautiful as hand engraved music. LilyPond has an excellent and illustrative essay on the problems of computerized engraving which I encourage you to read.

The Finale program by Make Music (formerly Coda Music) and Sibleus by Avid are undoubtedly the biggest players in the music notation software field but both suffer from the problems of automated engraving (lacking life, more difficult to read and interpret, etc.) but what is a composer/arranger to do? Assuming you can’t afford to hire a professional hand engraver (if you can even still find one) the answer is quite clear actually. Lamenting the decline of music score quality a group of developer/musicians got together and wrote a software program called LilyPond. When used correctly this program produces some of the most beautifully engraved music you can find. Best of all the program is free and open-source meaning that anyone can see the code that is used to generate the music and contribute fixes and enhancements. The program is cross-platform and can work on Linux, Macintosh OSX and Windows (though Linux and Macintosh are admittedly easier to get running).

As it turns out there is a bit of a catch though… While programs like Finale and Sibleus offer a graphical notation editor, LilyPond is a specialized program which does nothing more than automatic engraving (typesetting) based on a textual input file. This means to use LilyPond you need to learn how to manually describe the music in the LilyPond formatted text file, a significant impediment for many composers/arrangers. Or do you?

Recently there has been quite a bit of movement on developing some free and open source music notation programs, many of which support exporting to the LilyPond format. While the exported LilyPond file may still need some manual tweaking to get the best possible score it’s certainly much easier than entering an entire score into LilyPond format by hand.

One of the oldest and best known LilyPond compatible editors is NtEd (of NoteEdit lineage) by J. Anders. Though not immediately apparent from the feature list, this Linux software supports exporting to LilyPond. A more recent entrant to the music notation space is Denemo, a multi-platform WYSIWYG notation editor designed specifically for LilyPond. Finally, one of the most promising solutions seems to be MuseScore, a project stemming from the MusE Midi/Audio sequencer. MuseScore looks to be one of the most consumer friendly options with packages for Linux, Macintosh and Windows readily available and a fairly decent engraving engine itself as well as LilyPond support.

Open Source Virtualization with VirtualBox

There are many technologies which I am very much on top of because I use them on a regular basis, here are others that I interact with periodically and it’s enough to stay abreast of developments and do basic troubleshooting but from time to time there are technologies that I’m only peripherally aware of and have only a basic understanding of. One such technology is virtualization or virtual machine software.

For almost ten years I’ve been hearing about software like that made by VMware which allows for a virtual computer to run inside of a host operating system. To this day I haven’t done anything more with this type of software than to fire it up and see that indeed it does work. It’s not that I don’t see the advantages, it’s just that I haven’t personally encountered a situation where I can justify the time and effort it would take to set it up. That said I do like to know what’s going on in all areas of technology and what I’ve been hearing lately is some movement in the open source virtualization arena.

For some years now I’ve known about some projects such as Xen, Bochs and QEMU. The problem with these solutions is they are really not open source replacements for commercial virtual machine software like VMware. I’ve heard great things about Xen and it’s ability to virtualize Linux systems (on Linux systems). While this is valuable in many cases it’s not for most of what I want to do which is to run a guest OS on an entirely different host OS. Bochs is more on target but this is an effort to emulate the x86 platform enitrely in software, a bit heavy duty (and with significant speed costs) for what I normally would want to do which would be to run an x86 guest OS on an x86 host, for example a Windows guest on a Linux host. QEMU has the upper hand here. While it’s still a big heavy emulator there is some closed source accelerator code which can help in x86 on x86 situations. Of course the closed source part is a bit of a drag. Still the real problem with all of these is that they are incredibly more difficult to configure (and especially to configure and setup a new guest OS on) than their commercial counterparts.

Well, the world may be changing. What I’ve been hearing recently is that an open source project from Sun called VirtualBox is looking like it will give some of the commercial vendors a run for their money (so to speak). There is no doubt that VirtualBox is still in the early stages of life but the development team seems to be putting some real effort into it and new releases have been timely. I’ll be excited to follow the continual development of this product.

The Use of S3 and EC2 for Remote Backup

Even before the introduction of the Amazon S3 storage service I was intrigued bye the possibilities of secure backup over the Internet. Over the years I’ve evaluated a number of possibilities such as the use of rsync and Unison either to my own remote servers or to a service. I’m really not too interested in the commercial vendors as most of their software works on Windows or maybe Mac and my files reside on a Linux fileserver. It only makes sense that my backup solution should run on the Linux server as well.

None of these solutions seemed to quite fit the bill for me because of expense, concerns about data security or speed. Since the introduction of S3 I have started playing around with some of the scripts and software which have been developed to take advantage of these powerful services. I was still disappointed though mostly because of some data encryption concerns (on the storage system, not in transit) and the potential charges associated with backing up data to the S3 service. Ideally I would want something rsync like which would only transfer the changed parts of the files instead of recopying the entire file or directory. Unfortunately there is no built in support for anything like this in the low-level S3 system. So after playing with many scripts that suggested they would be able to do something along these lines and remaining unimpressed I decided to put things on hold for a while longer.

Eventually Amazon released the EC2 cloud computing platform but that still didn’t seem particularly useful for my purposes because of the lack of persistent storage between sessions. Once the elastic block storage became available things got more interesting. Now that I could retain data between sessions I had visions of a backup script which would launch an EC2 instance, mount an EBS volume and run rsync or Unison to backup directories on my local server to the remote site. I started playing around with EC2 and soon discovered that although it is very powerful it is a monster to control unless you are writing your own application from the ground up. For a simple job like this that should be easily accomplished by a script it can be a nightmare with several shell variables to set and paths to keep straight. Never mind the several encryption keys and the changing SSH host identifier to deal with. Eventually with some help from two fantastic blog entries (Ereblog and Free Wisdom Online) I was able to get something working…mostly.

It’s quite a fragile thing and you have to make sure that things are executed in the correct paths and with the correct environment variables set. In addition the returned data from the control commands is just awked from the output so it could easily break if the control package were updated, etc. The final nails in the coffin for me were my increased backup storage requirements for photos, audio and video which are huge and can change the economics of doing remote backup quickly. Even for a slimmed down set of documents I found the process to be too slow and fragile for my needs. In the end I have gone back to hauling hard drives with data backups off site and using the rsync program locally to sync these periodically with my live storage.

*Edited 2/2/09 to fix the several times I mistakenly called EC2 EC3 although I knew better. Thanks to the commenter for pointing this out!

The Open Source Microsoft Access Alternative

Databases are a wonderful tool for organizing all those bits of information in your life. While open source technology took database backend technology by storm (MySQL anyone?) there remains a gap in desktop database technology. Let’s say you wanted to create a database for your address book. You could certainly do it in MySQL and write a PHP front end for it and make it web based but this really seems like overkill for a personal address book, it also seems like a lot of work.

You could also do it in a spreadsheet program but you give up a lot of advantages of a database (especially a relational database) when you do so. In an effort to fill this void between the massive SQL database with frontend application and the spreadsheet Microsoft offers Microsoft Access. This is both a banckend database engine and a frontend design package in one which allows you to generate forms for updating data as well as reports. As a bonus if your database is too big for it’s engine you can connect via ODBC to a bigger backend such as SQL.

Unfortunately, this segment of database tools has been largely overlooked by open source software, especially in the Windows environment. This is probably not without reason as middle-level database tools like this, even Microsoft Access, are often too complicated for most end users and too limiting for most developers. In fact, if you asked many Microsoft Office users what the “Access” program does they probably wouldn’t be able to tell you. Still, if you need a quick database form for entering data it’s tough to beat this type of application. Perhaps the most widely known open source office suite, OpenOffice, has has made an attempt at an Access alternative in their “Base” tool but, frankly, it leaves a lot to be desired.

A better choice is the KOffice program, Kexi. Like Microsoft Access, Kexi can serve as a combination backend/frontend or as a frontend to a remote backend database. Kexi provides scripting through the python and ruby languauges in addition to the basic tables, forms and reports. In fact, the only real problem with Kexi is that it is not available in an open source version for Windows.

Because KOffice relies on the Qt graphics toolkit it was not made available in an open source version on the Win32 platform. Recognizing the interest in an Access alternative Kexi was ported to Windows and a commercial version is available for $72. The winds of change are in the air though. Trolltech which makes the Qt toolkit has released the Windows version of their toolkit under the GPL meaning Qt based apps can now be made available in Windows under an open source license.

Based on this development the KDE developers have started porting applications, including KOffice and Kexi, over to Windows. Because of the large codebase and complex nature of KOffice it’s going to take a while to get things stable on Windows (they’re currently at Alpha 10) but someday in the not too distant future there will be a good open source alternative to Microsoft Access on Windows. You can see the progress being made and check out the alpha on the KDE for Windows site. In the meantime KOffice/Kexi is available for use on Linux and Mac.

Movable Type Goes Open Source

For reasons I can only speculate about two of my most popular articles to date remain “The Next Big Thing In Blogging Software” and “a year later: an overview of multiblog software options“. The first was written over four years ago and the second just under three years ago. In the online world that is eons.

One might ask that if these have proven to be such popular articles why not update them more frequently. To be honest about it this blog is as much for me to remember and track my interests and solutions to technical problems as it is to share knowledge and information with you the reader. Given the significant amount of time which was invested in installing, testing and reviewing the blog software choices and the return on investment it simply doesn’t make sense to spend the time to do an annual or even semi-annual update. This is primarily because I have been extremely happy with my chosen solution, b2evolution and despite the continued prevalence of WordPress in the blogosphere I see no compelling reason to change and one good reason to stay with b2evolution, multiblogging. Despite the continued development of WordPressMU it remains a sort of kludge which may or may not work in your specific instance. b2evolution, on the other hand, was built from the ground up to support multiple users and blogs so support exists throughout the product. This is reason enough for me to stick with b2evolution, the blogging software that I still believe is undervalued and an excellent choice for the vast majority of independent blogging sites.

For those that have forgotten once upon a time the independent blogging software market was ruled by Greymatter and after it’s discontinuation by Movable Type. There were no other serious contenders. All was good in the land of the blogger, then the sky fell. As I wrote four years ago…

On May 13, 2004 Six Apart, the company behind Movable Type, announced the long-awaited version 3.0. With this blog entry they also single handedly managed to start the demise of the Movable Type monopoly and changed the face of blogging software forever.

What they did was try to commercialize what had been free software while maintaining a crippled free version to placate complainers. As it turned out this was perhaps the biggest mistake Six Apart ever made. As bloggers such as myself became vocal about these changes and provided developing alternatives which were improving on a daily basis the vast majority of independent bloggers abandoned Movable Type for other platforms such as WordPress and b2evolution. I have an unsubstantiated hunch that my prediction of the demise of Six Apart became a haunting reality for the company who saw customers fleeing by the thousands. Although they retained some market share, particularly among the commercial bloggers it would never be the same for Movable Type, once the king of the bloggers.

Despite attempts to rectify the situation and improve the pricing structure it seems that eventually the stubborn Six Apart came to realize the gravity of their mistake. In December 2007, more than three years after that infamous day, Six Apart made what I believe to be one last ditch effort to regain the market share they once had. It was then that Six Apart announced “as of today, and forever forward” Movable Type would be open source. Finally a victory for those who complained so mightily about that initial pricing structure.

How does this change things? It doesn’t really. Movable Type will never again see the market penetration it once had. The decision to go open source is far too late to have that kind of transformational effect. The market has become far too diluted and there is no single competitor (WordPress would be closest) to try and overtake. If it would have been made shortly after the original backlash we would probably all still be running Movable Type for out blogging needs as many of the other contenders would never have seen the development influx they did in the weeks and months after the MT 3.0 announcement. Certainly there is now a possibility that over time Movable Type will innovate and become a serious contender but for the time being it will remain a WordPress (and b2evolution) world. I applaud the move made by Six Apart and it probably will keep the Movable Type software alive and viable for the time being but it’s too bad this lesson was such a hard one for Six Apart. Better late than never. At lest the sentiment is right.

FOSS Disk Imaging

I’ve written before suggesting the use of Linux for open source drive imaging and it seems there has been some movement in this direction. About a year after my initial posting the folks at PackRatStudios posted this article with a list of free and open source alternatives to the Symantec Ghost software. A quick look at the utilities they reviewed indicates that there is still much work to be done on using Linux as a disk imaging platform, particularly when it comes to ease of use and filesystem (NTFS in particular) support. On the other hand we’re much further along than we were and progress is clearly being made.

Revisiting Open Source Whole Drive Encryption: TrueCrypt vs. DiskCryptor

About a month and a half ago I wrote about open source whole disk encryption software (this was just before TrueCrypt 5 came out) and mentioned an open source program called DiskCryptor which has been available since late fall and was the first open source whole drive encryption (system partition encryption) utility to support Windows that I’m aware of.

DiskCryptor has releases hosted on SourceForge and additional information on the primary developer’s website. Though the developer’s site is in Russian the Google translation facility does an ok job of translating it.

I started using DiskCryptor a few weeks before TrueCrypt 5 came out and was really impressed. Once TrueCrypt was released I tried that and while I do appreciate some aspects of the super redundancy in TrueCrypt whole disk encryption I soon went back to using DiskCryptor for a couple of reasons.

First, I had problems with TrueCrypt blue-screening on me and sometimes preventing my system from shutting down properly (it would sometimes reboot instead of shutting down). This made me quite uncomfortable as I was trusting my data to the software. I understand there have been a few patches to TrueCrypt since I tested version 5.0 which fixes some of the problems people were having and which I have not tried yet but there are other reasons I prefer DiskCryptor.

Second, while all the hand holding and redundant systems in TrueCrypt do make it (to some extent) dummy-resistant they are actually quite a pain when being utilized by a power user and there is no way to bypass them. In some cases it is either inconvenient or unnecessary to create a recovery CD. DiskCryptor does not require that a recovery CD be created and has different, perhaps more robust methods of recovering the data should the need arise.

Third, DiskCryptor supports hibernation! This is reason enough to use DiskCryptor for many laptop users. I understand that TrueCrypt 5.1 includes hibernation support but it appears a bug may have been introduced at the same time with dire consequences for drive security. Read about this bug in English and see the code problem in Russian. This may be fixed in TreuCrypt 5.1a but is not specifically mentioned as fixed in the TrueCrypt changelog as far as I can see.

Fourth, DiskCryptor has (in my mind) more robust/useful recovery options. This is for several reasons. While there is no recovery CD or extensive boot loader decryption ala TrueCrypt the encrypted volumes are fully compatible with standard TrueCrypt encrypted volumes (including pre-TrueCrypt 5). This means you can take a DiskCryptor encrypted volume and physically attach the drive to another system or boot into another OS and then mount and decrypt the drive with TrueCrypt. You cannot even do this with TrueCrypt encrypted drives as the technology behind TrueCrypt whole drive encryption is not compatible with regular TrueCrypt encrypted volumes. To me this is really exciting and useful as it allows me to move drives between systems and retain access to the encrypted data. There is also a BartPE plugin for DiskCryptor so you can boot from a BartPE CD and decrypt/access the encrypted drive. Finally, support is in version 0.3 (coming out shortly) for installing the DiskCryptor boot code on other media (eg. flash memory keys, CD-ROMs, etc.)

Fifth, DiskCryptor appears to be faster than TrueCrypt 5 WDE. At least on my system I noticed no slowdown with DiskCryptor but TrueCrypt 5 significantly slowed down my disk intensive operations. This is a major reason I personally switched back to DiskCryptor and I’m not the only one as evidenced by some posts in the DiskCryptor forums which indicate that in terms of MB/s DiskCryptor is as much as twice as fast as TrueCrypt 5, at least on some systems. Based on my experience I would agree. I understand there have been some performance enhancements in TrueCrypt 5.1a which include some assembly optimization (which was already a part of DiskCryptor) and I have not had a chance to test this latest version yet but believe speed improvements have also been made in the latest version of DiskCryptor which may still give it the edge.

Sixth, the development of DiskCryptor is both more active and more responsive to users than TrueCrypt. “ntldr” the developer of DiskCryptor has been very open to suggestions and very responsive to users through the forum on their website http://freed0m.org/forum the same cannot be said for TrueCrypt. Based on what I’ve seen from various TrueCrypt users they have been often ignored by the TrueCrypt developers who seem to be a small group of developers who do not respond particularly well to users or accept development assistance (one of the major benefits of open source development). The disenfranchised users include the DiskCryptor developer “ntldr” along with OS X users who started a project called OS X Crypt because of the unresponsive nature of TrueCrypt developers. I think this potentially will be a huge problem for TrueCrypt and it makes me somewhat concerned about the motives and long term success of the TrueCrypt development team. This is also manifested in the somewhat restrictive nature of the TrueCrypt source license compared with other open source licenses such as the GPL (which is used by DiskCryptor). While TrueCrypt may be open source it is most definitely not GPL software and not GPL compatible (read about the issues of including this with GPL software here)

There is one downside to DiskCryptor, there is currently no real help file or instructions for using it but I was able to figure it out by looking at the menu options all of which seem fairly straightforward to me. This is an acknowledged flaw and is being actively worked on by a few DiskCryptor users. In the meantime the primary developer is more concerned about enhancing the feature set and eradicating bugs than on developing documentation, an understandable position for many volunteer software developers.

Communication and publicity is not a strong suit for DiskCryptor and this may be partially to the fact that English is not the first language of the developer. In my opinion this, more than anything, is holding back what is otherwise an excellent (and in my mind superior to TrueCrypt) product. Much of the information is available but it’s in the DiskCryptor forums which contain a mix of Russian and English making them not the most user friendly way to learn about the software. There has also been little tech press coverage of the program.

I am not so much trying to make the case by myself that DiskCryptor is a better product for everyone, though it was for me. I am trying to bring some attention to the first open source whole disk encryption program (there was even a Wikipedia vote where it was decided to eliminate the page for DiskCryptor as non-notable and where people seriously questioned if it was just a knock off of TrueCrypt 5!) and encourage others to talk about and try DiskCryptor. Certainly the program could use some English language press if it is to grow significantly. Hopefully by explaining my reasons for selecting DiskCryptor as my choice I’ve encouraged you to at least keep an open mind and try the software then write and share your experience with others.

Whole Disk Encryption

My laptop is one of the IBM (Lenovo) Thinkpads which includes a fingerprint reader and TPM chip which can be used to both unlock the system at boot and log on to Windows using software supplied with the computer. One thing that the supplied software does not do but something that I’ve been interested in doing is whole disk encryption (something also called by a few other names depending on the vendor and software.

You can learn more about whole disk encryption in this article written by Bruce Schneier a couple of months ago or from the Wikipedia article. Essentially the idea is to encrypt the entire hard drive rather than a small subset of files. Obviously this does not protect the files while the computer is operating but is especially useful if you have a laptop (something prone to being stolen) and want to ensure that if someone stole it the data on it would be useless. While some free utilities such as TrueCrypt have allowed you to encrypt entire volumes they have not allowed you to encrypt the boot drive, at least not when using the Windows operating system. You see the trick with encrypting the boot drive is that you need to unencrypt it for the system to boot so a driver must be loaded at boot time which will prompt the user for a password and thus unlock the key allowing the drive to be unencrypted and the system booted. Until recently there were no free or open source programs which allowed you to do this with the Windows OS (solutions for Linux were available).

In the span of just over a month that has all changed. In December a Russian security consultant released the open source program DiskCryptor (and on SourceForge) which allows you to install a Windows driver (which can be renamed for extra obscurity) which will encrypt your drive and also allow you to install a boot time driver onto the disk which allows for the encryption of the boot volume. The encryption algorithm and container is TrueCrypt compatible so if need be you can access the drive by putting it in another computer which has TrueCrypt installed and mount the volume (with the appropriate password of course). This is an especially nice touch as it ensures some kind of compatibility between the open source projects and makes data recovery from an otherwise dead system a bit less problematic. I’ve been successfully running DiskCryptor on my laptop boot drive for several weeks now and have found the program works as advertised though there is essentially no help file or other documentation so you have to learn the program by playing around with it and looking at menus.

Later today TrueCrypt plans to release version 5.0 of their popular open source encryption software which among other things promises to include a boot driver for Windows systems which will allow the encryption of the boot drive. I plan to try out this software once it becomes available. I am excited to see that there will be two open source solutions to whole drive encryption and look forward to improvements in one or both of the programs.

A few things to note. Neither of these solutions (as far as I’m aware) supports the TPM chip and fingerprint reader in my laptop. This means that you need to enter a separate password to unlock the hard drive in addition to unlocking the computer. It also means that the encryption is all taking place in software and utilizing CPU cycles and slowing down drive access times. While I haven’t noticed a pronounced effect in my usual word processing and Internet browsing on this system I can see that this might be problematic for a media or gaming intensive situation. Hopefully advancements to these solutions will allow for better integration with hardware acceleration and authentication to improve this situation.

Free utility to create ISO CD-ROM images

Occasionally I have the need to have someone who is not as technically literate as myself send me a CD image. In the past I’ve recommended a number of shareware utilities (most of which are for ISO manipulation including extraction) but these are often more powerful than what the person needs and the associated cost is a disincentive. More recently I’ve found a piece of freeware from Lucersoft called LC ISO creator which does nothing but create ISO images of CDs and DVDs. It’s pretty difficult to make a mistake with a piece of software this simple.

Now if only there was a good open-source cross-platform GUI ISO manipulation utility that allowed for the creation, extraction and modification of ISO files. I’ve been really happy with InfraRecorder so I’m hoping something similar comes along in this vein.