Converting a Java Properties Config to a Database Schema
Today I started the work on a conversion in my webapp's codebase from a Java Properties file-based system to a database-based system because the users were asking for an editing page for their alerts. It's understandable, I created the alerts config simply thinking that I'd be the one adding and removing people from the lists, but in the end, the users wanted to be able to control this themselves. While it's possible to have them edit the Properties object data and then write it out to the file system, that makes for a mess in trying to read it. I've run into that before.
So I needed to make the conversion without a huge impact to the codebase. I started out with a pretty standard Java Properties file:
# list all the alerts by name that we'll be using Alerts=One;Two;Three # this is the config for alert 'One' Alert.One.ClassName=com.bobbeaty.alerts.FlipFlop Alert.One.VersionID=20091029 Alert.One.SilenceInMins=10 Alert.One.AlertExpr=(pnl > 50000) Alert.One.Message="The p/l exceeds $50,000: " + pnl Alert.One.ChatTo=drbob;liza;joe # this is the config for alert 'Two' Alert.Two.ClassName=com.bobbeaty.alerts.BaseAlert Alert.Two.VersionID=20091111 Alert.Two.HoldTimeInMins=5 Alert.Two.AlertExpr=(leadFuture == leadFutureLast) Alert.Two.Message="The lead future has not moved in 5 mins: " + leadFuture Alert.Two.ChatTo=drbob;liza # this is the config for alert 'Three' Alert.Three.ClassName=com.bobbeaty.alerts.BarrierBreach Alert.Three.VersionID=20091011 Alert.Three.States=T;T;T;T;T;F;F;F;F;F Alert.Three.n=-5..5 Alert.Three.AlertExpr=(pnl > n*50000) Alert.Three.Message="The p/l exceeds $50,000: " + pnl Alert.One.ChatTo=joe;mimi;nina
OK, they're all made up, but the idea is that this system is very flexible, and while a database-system will be nice to have, it's a lot more work to put together than this. There's the fact that this is an a corporate environment, so you can't just make databases ad hoc, and such.
I thought (briefly) about using SQLite3, but it's Java API is JNI, and that's something I'm just not ready to do. I could have used H2, but that system makes multiple files for a single database, and that's just a little too much clutter for me. I wanted something simple, and easy to work with.
In the end, I had to give in and go with the corporate 'standard': MS SQL Server. OK, it's life. But I then needed to try to come up with a way to easily edit the ChatTo arguments of each alert, and there was also a MailTo property that I just didn't include in this example. Meaning: I couldn't just have a single table with two columns and leave it at that. I needed to have something that was a little better designed than that.
So I started looking at the ways in which I was going to need to use this data. First, I needed to have something that was going to hold the basics of the alert:
CREATE TABLE Alerts ( alertID INT IDENTITY, name VARCHAR(132) NOT NULL, description VARCHAR(4096) NOT NULL, classname VARCHAR(256) NOT NULL, versionID INT NOT NULL, active bit NOT NULL DEFAULT 1, CONSTRAINT Alerts_PK PRIMARY KEY (alertID) )
I toyed a lot with whether or not to include more of the common attributes, like AlertExpr and a few others, but in the end, I didn't want to have these records fiddled with much. I thought it was better to just activate/deactivate them and that's it. The rest should be set once and then left alone.
Clearly, the versionID is going to be updated, but the reason it's in the table, and Java Properties scheme originally, is that I would make changes to the alerts, and the state would be saved so that on restart it didn't re-alert for the same conditions. This meant that I needed to know when the saved state was stale, and that's where the versionID comes in.
If the versionID changes from what's saved for the named alert, we assume that there's something about the alert that fundamentally changed. That means that the state can't be trusted, and so has to start from scratch. It's just like Java's RMI serialVersionUID, in that regard. Which is, of course, where I got the idea.
The bulk of the parameters for the alerts could be held in a very simple table:
CREATE TABLE AlertParams ( alertID INT, setName VARCHAR(80) NOT NULL, name VARCHAR(132) NOT NULL, VALUE VARCHAR(4096), CONSTRAINT AlertParamsPK PRIMARY KEY (alertID, setName, name), CONSTRAINT AlertParams_ID_FK FOREIGN KEY (alertID) REFERENCES Alerts (alertID) )
Here, I'm just going for the basic key/value pairs that comprise the lion share of the configuration parameters for the alerts. Pretty simple stuff. The last table was a little more of a toughie.
We already had a permissioning table in the database, which had the primary key field of username, which the web app would get from the logged-in user - or a login box, whichever was easier to get. I decided to expand this table and add in the email address of this user so that we could use this one table as the 'target locator' for the alerts. The chat id was the username, and with the email address, all I needed to add was a table that cross-referenced the alerts to the users, and how they wanted to be notified:
CREATE TABLE AlertRecv ( alertID INT, setName VARCHAR(80) NOT NULL, username VARCHAR(80) NOT NULL, chat bit NOT NULL DEFAULT 0, email bit NOT NULL DEFAULT 0, CONSTRAINT AlertParamsPK PRIMARY KEY (alertID, setName, username), CONSTRAINT AlertParams_ID_FK FOREIGN KEY (alertID) REFERENCES Alerts (alertID) )
With this, we could easily get the list of active (or inactive) alerts and a description. That was important to me for the web page. Also, I needed to be able to easily edit the alerts the users were getting, and with this simple cross-reference table, I can build a servlet that will quickly check/uncheck the bit fields in the table for the given user/alert combination.
It took me several hours to come to this design. I tried to simplify it to two tables, or one, and each time I ended up with much harder maintenance tasks to do the things I could have easily done with a text editor and the Properties file. I was really trying to make it pretty simple to get this converted as I didn't have a lot of time to make the conversion and be sure it worked.
After playing with it in my head (and adding in all the values from the Properties file), I was convinced that it would work. Now all I had to do was to build the SQL to get the data out of the tables in a way that it's easy to build a Java Properties object, and then test that I actually had something that worked.