Another night of hax0ring, with not as much progress as I’d like, again. This time though it was mostly distraction as opposed to actual problems getting in the way. I did finish up the DAO for the Notes, and note creation and updating is working correctly. I’m having a little problem with auto-populating the TIMESTAMP columns, but I’ll figure that out. I’m just glad Struts is playing nice with the DAO and Doing The Right Thing [tm]

Oddly, I’m sort of daunted by the next steps, which are going to really start impacting the workflow in CONGO – making direct changes to how Notes are handled, how registrations are managed, and how history is kept. Up until now, things have been pretty much duplicating functionality (or at least adding to existing limited functionality). But I’m looking at rewriting the Registration page and how history and status are being kept. This is at the core of how CONGO works – the very reason that, with something like 50,000 registrations having been run through the software since it’s first rollout, we haven’t corrupted a single table, or ended up with a mysterious situation where a users status is wrong or lost in a way that was not easily determined.

I’ll get past this, I know, but it still makes me nervous.

On the “face of the future’ front, though, we have the emerging view of what the database will look like. This first image is the old view of CONGO’s tables. No foreign keys whatsoever, that means only programmatically enforced referential integrity.

Here’s what my working v2 table structure looks like. I’ve only been working with address contacts and notes so far, as well as conference master data, but it’s all starting to tie together as it should. By the way, these images are generated via dbVisualizer, an awesome generic SQL database tool that has a free mode that’s extremely useful (and includes ERD diagrams like this 🙂

Bit by bit, I’m moving forward.

Today, durnit, I was going to get some work done. I specifically scheduled time after $dayjob to spend working on CONGO v2, and ya know what? I actually did it!

Tonight’s joy was parking at the Panera in Marlborough, plugging in the iPhone for music, and getting down to coding. With the network being twitchy in the restaurant, I actually had about 2 hours of uninterrupted focus time to code, and I was productive!

I finished up much of the underlying work on the Notes system (the DAO and Data object are done), and was working on wiring up the Notes interface into Coconut (the web front end). One of my goals with this whole project is to upgrade how I’m managing database resources, and doing things ‘properly’. For instance, CONGO v1 had absolutely no referential integrity mechanisms in place. No foreign keys of any sort. This meant I could easily have had orphaned data in many of the tables, there was just no way to tell.

The first step to setting up proper RI is to use foreign keys to link one table to another. The newly formed Notes table now has foreign keys linking to the reg_master table (which holds registrant master information). I did learn a couple basic concepts while doing this though. For instance, if the source of a key reference is null, then the foreign key constraint is not checked (handy!). If it has any other value, it must have a corresponding key in the target table. I found out quickly that specifying source columns as NOT NULL in the database scheme wasn’t going to work, particularly on columns that get populated later.

The other thing I wanted to fix was date handling. I had a mishmash of DATE column types and raw text fields storing dates in the old version. MySQL (and most databases) have a handy column type called TIMESTAMP that can not only store time down to fractions of a second, but also support basic math operations (“Show me all rows that have been modified in ‘now() – 3 HOURS’ is a valid construct).

Here’s the problem though. MySQL DATETIME columns cannot be null. So what if I don’t have a date to put into the column yet? Well, the docs say the schema should say ‘colname TIMESTAMP DEFAULT 0’ – I did this, and it seemed to take. But when I tried to retrieve the row in my DAO, I was getting this error:

java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 6 to TIMESTAMP.

The offending line causing this was (‘where’ is a Note object, postDate is a java.sql.Timestamp, and ‘fromwhat’ is a ResultSet):

where.postDate = fromwhat.getTimestamp("note_postdate");

I completely and totally don’t understand this. I’m wondering if this is a bug in the JDBC driver I’m using, or what, but I should not be trying to load a date in the format 0000-00-00 into the java.sql.Timestamp object. It should be returning NULL, and therefore ‘postDate’ should be null, as it was when it was created.

I ended up having to code logic around it to say basically “Don’t try to load the note_postdate value unless circumstances can guarantee there was a value there.” I tried various approaches for ‘Is this column going to be null?’ – but it really appears as if the JDBC driver is delivering a Timestamp object, even though the source is null.

It gets a little better. In the MySQL docs, they say, as I mentioned above, that the declaration for a TIMESTAMP without a value should be ‘default 0’. But after I create my tables from my schema (using that syntax), and then do a ‘SHOW CREATE TABLE reg_notes’, lo and behold, I see:

CREATE TABLE `reg_notes` (
`note_id` int(8) NOT NULL auto_increment,
`note_rid` int(8) NOT NULL default '0',
`note_cid` int(8) default NULL,
`note_postrid` int(8) default NULL,
`note_ackrid` int(8) default NULL,
`note_postdate` timestamp NOT NULL default '0000-00-00 00:00:00',
`note_ackdate` timestamp NOT NULL default '0000-00-00 00:00:00',
`note_message` varchar(100) default NULL,
KEY `id` (`note_id`),
KEY `rid` (`note_rid`),
KEY `note_cid` (`note_cid`),
KEY `note_postrid` (`note_postrid`),
KEY `note_ackrid` (`note_ackrid`),
CONSTRAINT `reg_notes_ibfk_1` FOREIGN KEY (`note_rid`) REFERENCES `reg_master` (`master_rid`) ON DELETE CASCADE,
CONSTRAINT `reg_notes_ibfk_2` FOREIGN KEY (`note_cid`) REFERENCES `con_detail` (`con_cid`) ON DELETE CASCADE,
CONSTRAINT `reg_notes_ibfk_3` FOREIGN KEY (`note_postrid`) REFERENCES `reg_master` (`master_rid`) ON DELETE CASCADE,
CONSTRAINT `reg_notes_ibfk_4` FOREIGN KEY (`note_ackrid`) REFERENCES `reg_master` (`master_rid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Observant folks may note that the note_postdate and note_ackdate columns now have default values of ‘0000-00-00 00:00:00’. Not what I specified in my creation SQL.

I’m past this now, and it’s working, but if anyone has ideas about why this is behaving this way, I’d love to hear it. It’s damned frustrating.

UPDATE 08/28/2008 – Through the magic of RTFM and some nudges from Imre, my table definition was wrong… Here’s what it should be:

note_postdate TIMESTAMP null DEFAULT null,
note_ackdate TIMESTAMP null DEFAULT null,

This little bit of MySQL fun is in fact documented, it’s just buried in the TIMESTAMP page on dev.mysql.com

I didn’t get as far as I had hoped to tonight, due to a variety of distractions – it was an impressive list of “things that get in the way of previous plans” (Work actually had work for me to do, then a stop by the Mosaic work site to check on some supplies, then dinner, then home to find the freezer has stopped freezing (due to a broken icemaker), then helping out Cat with some finance stuff so we can get our Mortgage application in. Ung!

Finally I sat down, and with The Time Machine on as background entertainment (not so good a movie – great for background noise – and what the heck is with Jeremy Irons? Is there no movie so bad he won’t act in it? Sheesh) – I got down to work.

There were some great comments from yesterday on how to handle historical triggers on database updates. There’s some challenges to using triggers (such as “who made this update?” – though using a ‘last updated by’ column on every record could fix this, I’m still not sure if it’s a good idea). Jonah pointed out also that using database triggers limits the portability of the database layer. Given that MySQL isn’t going anywhere, and that it’s getting more and more attention as an enterprise-level database system, I’m not that worried about it, but I am taking that into consideration.

Tonight though I needed to find something short term to work through, so I focused on how ‘notes’ are applied to registrants. In the past, I would make a “NOTE” entry in the history table. These NOTEs could be anything from just an informational note by the operator (“This person was nice”) to something important (“Paid by check”). Having NOTEs in history was “okay” but without filtering, it was hard to see operator comments in a meaningful way.

I also had a concept of ‘NOTICE’ records. These are essentially NOTEs to be displayed to the operator whenever a registrant logged in. They would show up as red messages on the registrant detail screen, and the operator would have to acknowledge the NOTICE before proceeding (that’s the theory – in fact there was no logic to enforce this). These NOTICEs were very, er, noticeable to the operator, and were great for things like “DO NOT ADMIT THIS PERSON UNDER ANY CIRCUMSTANCE” (yes, we had a few of them, and I know of one instance where the CONGO ‘NOTICE’ record actually did prevent them from entering the event. Yay!). Generally though they were used for innocuous things like “STILL OWES $25” or the like.

For v2, I’m seperating out the NOTICE / NOTES into a separate table simple called ‘Notes’. The option to making any note entry a ‘notice’ will be on the note creation, and it’ll just be a flag on the record. Also the record will keep track of when the note was created, and by whom, as well as when it was acknowledged, and who acknowledged it.

The basic DAO structure is there, as is the data object, and I’ve created the schema file for it as well. Nothing is wired in yet, but I’m also still noodling around how I want the record to look, so I’m sure there’ll be more work moving forward.

The other bit I did tonight was trying to squash a problem I’m having with the JDBC connection pooling. I’m using the C3PO connection pooling tool, and if CONGO has been idle for a while, I’ll get a connection communications failure. Googling around hasn’t showed me anything immediately obvious, though others have said they’re having the same problem. What I’m trying now is adding some auto reconnection options to the JDBC connection string, so my c3po setup now looks like this:

private void configurePool() {
logger.debug("Empty connection pool, recreating...");
ComboPooledDataSource cpds = new ComboPooledDataSource();
try {
cpds.setDriverClass( "com.mysql.jdbc.Driver" );
} catch (PropertyVetoException e) {
e.printStackTrace();
}
cpds.setJdbcUrl("jdbc:mysql://localhost/congo?autoReconnect=true&autoReconnectForPools=true");
cpds.setUser("root");
logger.debug("Initted.");
applicationMap.put("cpds",cpds);
}

I won’t know if it’s working yet until I idle long enough to force a timeout. I’ll let ya’ll know.

Just quick update on this. Last night I did get some coding time in after blog-posting. The fix this time was a problem with Create Registrant that was throwing SQL errors all over the place. Turns out I never actually completed writing the DAO for the Registrant data object, and the create() method was broken in a ton of interesting ways.
There’s still a twitch in restoring the state to the editor after creation – it’s not reloading the new registrant into the sessionspace after creation, so you end up with a sort of ‘blank’ registrant screen. Exiting and coming back shows the new entry, so it is creating it, just isn’t reloading.
I’m sort of worried about keeping history right now. The old code did everything in the history table programmatically – so whenever I made a change to a registrant’s information, I had to make a call to logEntry() to create a new history record. So, naturally, there was some inconsistencies in the logging. It also didn’t help that the history table was critical information. It was part informational, part auditing. In fact, some parts of CONGO used the history table to recalculate statistics and status on registrants. (Note – that is SO not going to be in the rewrite).
Anyway – regarding logging, I’m looking into learning MySQL triggers to have registrant history updated automatically whenever a change happens. That way I don’t have to always remember to put in a logging call. We’ll see if this is viable (for instance, how does the trigger know what the ID of the operator is? The one who is actually making the change?)
Stay tuned. 🙂