With all the work I'd been doing the last few days with the conversion of the Alerts from a Java Properties file-based system to a database-driven system, I needed to make an editor page for the bulk of the data. It used a ton of AJAX, but it works very nicely and I'm able to put this behind me. Very nice to get this conversion done.
Today I spent a good bit of the day trying to create a nice, user subscription system for my alerts. I needed it to be clear, but easy to use. With the database schema I had, I needed to use a left outer join to get all the alerts the user could possibly subscribe to. It was pretty fun to get it all worked out.
The core was a simple stored procedure that pulled all the data into a nice table:
CREATE PROCEDURE dumpUserAlerts @username VARCHAR(80) AS BEGIN -- make a table to hold a little more than we'll be needing CREATE TABLE #everything ( name VARCHAR(32), description VARCHAR(8000), portfolios VARCHAR(4096), chat bit, email bit ) INSERT INTO #everything SELECT a.name, a.description, a.value AS portfolios, r.chat, r.email FROM ( SELECT fa.alertID, fa.name, fa.description, fp.value FROM Alerts fa, AlertParams fp WHERE fa.active=1 AND fa.alertID=fp.alertID AND fp.name='Portfolio' AND fp.setName='default' ) a LEFT OUTER JOIN AlertRecv r ON r.username=@username AND r.setName='default' AND r.alertID=a.alertID -- update the wildcard portfolios to something readable by the user UPDATE #everything SET portfolios='[All Portfolios]' WHERE portfolios='[*]' -- now let's add the portfolios to the end of the description UPDATE #everything SET description=description + '<br><p class="hilite">' + REPLACE(portfolios,',',', ') + '</p>' -- now let's show the user what they were meant to see SELECT Name, Description, Chat, email AS Mail FROM #everything END
What I really like about the way this is coming together is the coordination of the SQL stored procedures, the Java Tomcat server (with its' servlets), and the web GUI with JavaScript. At my last job I did something like this, but not to this extent. I didn't have the nice JavaScript front-end... OK, I did, but the project didn't make use of it.
What I like is that I have three places to put code: the JavaScript, the Java, and the SQL. This means that I can distribute a solution in the most efficient manner possible. I can make use of the fact that the SQL can be changed independently of the Java or JavaScript, and that makes it a very powerful tool.
This is just how I like to code - across the entire system. Not just in one little slice. The code above was then massaged by the Java to remove NULL values and put in HTML INPUT check boxes and then using the onChange tag, these were able to send back messages to the same database to effect the change of the subscriptions.
Very nice.
Today I spent time taking the database schema I made yesterday and making it look like a Java Properties file so that I could easily create Properties objects and then feed them to the alerts I had and not have to worry about changing the rest of the code. The problem is, the database schema was logical, but it wasn't anything like what the previous Properties file layout looked like.
The ChatTo and MailTo properties were at the same level as the rest of the parameters, but in the new database schema, it's an entirely new (and separate) table, with links to an existing table for the email address. It's different, all right. After a little messing around in SQSH, I came up with the following stored procedure that takes a set name and returns two columns - keys and values, and from this, I can easily feed a newly created Properties object and we're good to go.
CREATE PROCEDURE sp_getProperties @setName VARCHAR(80) AS BEGIN -- get an answer table to hold the whole lot of it CREATE TABLE #answer ( name VARCHAR(256), VALUE VARCHAR(4096) ) -- get the list of the alertIDs that are in this set CREATE TABLE #interesting ( alertID INT ) INSERT INTO #interesting SELECT DISTINCT a.alertID FROM Alerts a, ALertParams p WHERE p.setName=@setName AND a.alertID=p.alertID AND a.active=1 -- get the list of all active, interesting alerts DECLARE @id INT, @val VARCHAR(4096) SELECT @val='' SELECT @val=@val+CASE WHEN @val='' THEN '' ELSE ';' END+a.name FROM Alerts a WHERE a.active=1 -- ...and now that it's built, put it into the answer INSERT INTO #answer VALUES('Alerts', @val) -- run through them all, processing each in turn DECLARE actives cursor FOR SELECT alertID FROM #interesting OPEN actives fetch NEXT FROM actives INTO @id while @@fetch_status = 0 BEGIN -- get the ClassName for this active guy INSERT INTO #answer SELECT 'Alert.'+a.name+'.ClassName' AS name, a.classname AS VALUE FROM Alerts a WHERE a.alertID=@id -- get the VersionID for this active guy INSERT INTO #answer SELECT 'Alert.'+a.name+'.VersionID' AS name, a.versionID AS VALUE FROM Alerts a WHERE a.alertID=@id -- get the bulk of the parameters for the alert... INSERT INTO #answer SELECT 'Alert.'+name+'.'+p.name AS name, p.value AS VALUE FROM Alerts a, AlertParams p WHERE a.alertID=@id AND a.alertID=p.alertID AND p.setName=@setName -- get the ChatTo list for this active guy SELECT @val='' SELECT @val=@val+CASE WHEN @val='' THEN '' ELSE ';' END+u.username FROM AlertRecv r, Users u WHERE r.alertID=@id AND r.username=u.username AND r.chat=1 AND r.setName=@setName -- ...now that it's built, insert it into the answer IF @val<>'' INSERT INTO #answer SELECT 'Alert.'+a.name+'.ChatTo' AS name, @val AS VALUE FROM Alerts a WHERE a.alertID=@id -- get the MailTo list for this active guy SELECT @val='' SELECT @val=@val+CASE WHEN @val='' THEN '' ELSE ';' END+u.username FROM AlertRecv r, Users u WHERE r.alertID=@id AND r.username=u.username AND r.email=1 AND r.setName=@setName -- ...now that it's built, insert it into the answer IF @val<>'' INSERT INTO #answer SELECT 'Alert.'+a.name+'.MailTo' AS name, @val AS VALUE FROM Alerts a WHERE a.alertID=@id fetch NEXT FROM actives INTO @id END close actives deallocate actives -- now return this to the caller SELECT name, VALUE FROM #answer END
Now while I'll be the first to admit that this is not the most efficient way of getting the data into a format that I can easily parse, it's pretty fool-proof. I wanted to make the Java parsing as simple as possible, and this does the job - a simple table with two columns - names and values to put in the map. Can't get any simpler than that.
I will say that some of the parts that I think are clever are the building of the strings that are really individual rows in the database - like the list of active alerts, or the list of ChatTo or MailTo recipients:
SELECT @val='' SELECT @val=@val+CASE WHEN @val='' THEN '' ELSE ';' END+a.name FROM Alerts a WHERE a.active=1 -- ...and now that it's built, put it into the answer INSERT INTO #answer VALUES('Alerts', @val)
We know that the select statement contains an implicit loop, and by using the case statement in the select, we get the delimiters in the right place - as opposed to an unnecessary trailing, or leading, one. I have to give credit for the use of the case to a co-worker. Pretty nice and concise.
With this, I'm able to finish the replacement of the Properties file with a database schema, and that's what I needed. In the coming days I need to make an editor in my web app, and then put a user-serviceable subscription page together so that the users can update what they get on their own. That will be a nice win for the web app.
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.
I was really surprised this morning to see that Xcode 3.2.1 was out on Software Updates. The notes on the developer web site say:
Xcode 3.2.1 is an update release of the developer tools for Mac OS X. This release provides bug fixes in gdb, Interface Builder, Instruments, llvm-gcc and Clang optimizer, Shark, and Xcode and must be installed on Mac OS X 10.6 Snow Leopard and higher.
so it appears to have updated components of the Xcode suite as opposed to actually updating the Xcode GUI app. Interesting.
Certainly had to get that.
Today I've been up to my neck (again) in the code I inherited that I seem to be bound to with rope and Crazy Glue. This time, I was trying to add to the Flex client a little data that was in at least one of the XML data feeds. Turns out, it wasn't in all three of the necessary XML data feeds, and therein lies my sorry tale of woe today.
The original author believed that, when it came to JUnit tests, if a few are good, a lot are really super, and dozens are really far out. While that might be true, the problem today turned out that the data driving the tests wasn't contained 100% within the JUnit tests, but also took data from the database, etc. and when things changed, as they have in the last few weeks, you run into a lot of broken tests, with no real way to easily fix them.
There are a few things that unit tests - even automated unit tests, are really good for. But they are all basically small, utility class functionality that isn't dependent on date, or time of day, or anything not controlled by the test. When you have things like database configurations, or expiring instruments, then you need to be very careful, because when you come back to the tests after a prolonged period of inactivity, you can get into a really bad place... and that's where I found myself today.
There were errors all over the place, and the one line of code change could not have accounted for the failures. There was something else, but what? The problem with JUnit tests is that you typically only get an error and an exception stack. It's up to you to dig in there to see what the problem is. Sure, the unit tests I've made have a lot of sub-tests in them so that it's pretty darn easy to see what caused the problem, but that's not required. Unfortunately, these tests I was working with were very complex, and no real intermediate results, so it took me about an hour to figure out the problem: database changes.
This is where the unit test concept really fails. It took a simple one-line change and drug it out to more than an hour of work. This is not what the proponents of unit testing put forth. In theory, since I just added one thing, they would say I'd only see a different where that caused an output difference. But that's ideal, and this is the real world.
So I got the changes in, and the tests corrected for the new data in the database, and things are OK for now. But in a few months, it might happen again. A 10 min change drags out into an hour-long test-fixing enterprise. Gack!
Today I had a most uncomfortable experience. We are using Subversion as our SCM, and while it's generally quite nice, it's a little difficult to branch with it. More to the point, it's difficult enough that some folks simply refuse to branch. This really makes the SCM a one-way train: no backing up... no detours... always ahead, straight ahead. It's really selling the product short, but more importantly it's very limiting.
So today I had changes that needed to go in, but needed database changes before they could go to UAT. It's all checked in because it's running great in Test, and I expected the database changes to be made this afternoon. Bummer is, they aren't going to be made, it seems, this afternoon, but that's not really the problem.
The problem was a teammate wanted to push to UAT with changes, and had updated before making their changes, which is something you should do. Problem is, they can't push to test until the database changes are done. So what's the fix?
Well, if we're using SVN, the solution would be to branch off the trunk one revision back of my checkins, add in the changes to the system on the branch, check it in, and publish it to UAT. Then, merge the branch back in on the trunk and everything is fine. There's a record of what went to UAT, and it's the "textbook" way of getting around this problem.
But what happens if you are unwilling to branch SVN? Well... you might ask your co-worker (that would be me) to back out their changes so you could put your changes on the trunk without any branching. This certainly works, because then I'll put them back in and everyone would be happy: the person that didn't want to branch wouldn't have to, and I'd have to back-out and then put back in again, my changes.
Wait a sec... doesn't sound like everyone is happy. I'm getting the short-end of the stick.
Given that people aren't going to want to use branching, the best solution (in my opinion) is a Distributed SCM - like git. With git, I would have had my code checked-in, but not pushed to the central repository. I'd build that into the "push to UAT" script so that the central repository always has what's in UAT, but every developer is free to SCM whatever they want without having to worry about others using that repository.
It really is a very unique way of looking at the problem: use a distributed model so that you don't have to interact with others except at times of mutual agreement.
I'm going to be pushing to use git at the Shop now. This is just not something I want to have to go through again. It's only happened once, but that was more than enough. I can't make people learn to use the tools, so the next best thing is to isolate myself from them.
Today I spent a good bit of the morning finishing up a few things on my first yearly review here at The Shop. One of the most disappointing things about the review was that I needed to create a development plan for the coming year. This makes sense then you're talking about a 27 year old kid - but by 47, if I'm not on the top of my game - and able to stay there, then I shouldn't be in this position. It's like asking Tiger Woods "Yo, Tiger - what are your plans for a better game next year?"
I'm not saying I'm the Tiger Woods of coding, but I also don't need to have a development plan because people are put-off by my intensity, and some are scared to approach me. While I don't see this as a wonderful asset, I have to say that the review pointed out that my skills, speed, quality of work, and communication are the best around. "Raising the bar..." and "Setting the standard..." were quotes from the review.
So yeah, I can be focused and intense, and this can put off some folks. I don't mean it to happen, but then again, it's not like it's a mystery, either. Anyway... I needed to finish up some stuff with the review. Tomorrow it'll be over, and we'll see what happens as a consequence of this with the bonus. That will be very enlightening.
Once I was done with that, I had to mess around with this web app I inherited, and once again, found how horribly built this code is. The original author decided that comments were unnecessary at the time, and subsequently was not able to help me with anything I needed to figure out. He's a nice guy... I like him, personally... but if I were his manager, I'd tell him that if he did this again, I'd have him on app support for six months. It's a disgrace.
So I had to struggle through this bad codebase trying to figure out what needed to be done, and while it was only about 3 hrs of work, it seemed like an eternity. I want to do a good job, but it's tasks like this that make me want to scream.
When I got done with that, I was able to spend a little time fiddling with Filemaker Pro 10 on the application for a friend's office that needed a few changes. Interestingly, the changes were very minor - the format of a date field, the change of a report field, the calculation of the rows in a table... pretty simple stuff. It took me no more than an hour and all the things they've been worrying about for months were fixed.
It's amazing how unimportant customer service is to some folks. The original developer was getting $80/month for this app, and they wanted a few changes. He probably could have changed them in 10 mins, but chose not to. Things kept getting nasty, and finally, he told them it's over. That's a first - the developer firing the client. But that's when they came to me.
Too bad. He's out $80/month and they get what they wanted all along. His loss.
One of the tools that has made the last year of my professional coding very nice is the H2 Database Engine. It's 100% Java, it's fast, it's flat-file or in-memory, and it's just as rock-solid as PostgreSQL. I love both of these databases, and while I can use others, if I get the choice, I'll use one of these.
H2 is amazing as an in-memory database, but it does file-system database as well. It's got all the things you'd want in a database: multi-version concurrency control, triggers, foreign keys - the works. It's super simple to use as well. Could not be easier.
Love it when we see things improving in it.
Today I saw that Björn was able to release MacVim Snapshot 51 with a lot of additions - including a Core Text renderer that should be the fastest way to render text on the Mac in Leopard (and Snow Leopard). While I haven't seen a lot of lagging rendering, it's always nice to see the best you can see when you're hoping around and scrolling in an editor - like Vim.
Again, Vim is probably my favorite tool for working with text, and MacVim is an excellent port to the Mac. It's really something you have to try.