Weekblog: Day 3 – Baby steps!

29 08 2008

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.

Weekblog: Day 3 – The JDBC Whubbidah huh?

28 08 2008

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

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

Weekblog: Day 2 – or ‘That didn’t go as planned’

27 08 2008

As the title says, yesterday hardly went as planned, as evidenced by me just getting around to posting at 9:30 this morning.

The Geekery. It is everywhere!
First, the day was full of IT-related geekiness, trying to get SASL authentication working on the the mail gateway for the Greater Homeport Server Cluster. What should have been an hour long setup and fiddling turned into a 6 hour “Learn what SASL is, learn what TLS is, learn how Postfix manages it, learn how Cyrus implements SASL, learn how to set up standard authentication, and learn how to do this all on an extremely busy mail gateway without killing all inbound mail” process.

In the end, we were only partially successful. The main impetus for this setup was making it so Cat and I could send mail from our iPhones without using Googles SMTP gateway (if you have a gmail account, you can use SASL authentication and send mail from anywhere via smtp.google.com. The drawback? Any mail going through that gateway gets it’s From: line rewritten to the gmail account ID.)

We got most bits working, except for finding out at the end that v2.0.2 firmware on the iPhone has a problem with self-signed certificates on TLS-enabled mail servers (but this is only for SMTP connections – Safari et al has no problem with self signed certs. Annoying). One assumes Apple will be fixing this soon, but it means we can’t have encrypted inbound mail connections from the iPhone. Adam has suggested there is a list of certs that free and Apple certified, I may end up going that route.

Oh yeah, and about the project I’m supposed to be working on…
But that’s not what I came here to talk about.

The point of this week’s blogging was to get focused on CONGO v2 and make some progress on getting toward an Alpha release. To accomplish this, I need to get 2 hours or so a night of fairly focused work time.

Last night I failed.

The main culprit? Lack of sleep. I went out to dinner with my mom last night (mm, steak!), and ended up getting home around 8:30. Not too bad, thought I, I still have a couple hours before bed. Flopped down on the couch, had the laptop out, tossed a movie in, and started work and… fell asleep right on the couch. An hour and a half later, I woke up (now about 10;30), was immensely groggy, and decided “Right, that’s it. Bed.” and flopped right into bed, sleeping like the dead til 8am this morning. Total sleep time? About 10.5 hrs. How I feel today? Stiff, but caught up.

Unfortunately, this meant I got only a minimal amount of work done on CONGO last night. I fixed some of the Notes DAO setups, and started work on modifying the ShowRegistrant functions to enable the notes browsing. I’m trying to decide if the Notes listing should be attached directly to an instance of a Registrant, which means it gets populated whenever a Registrant is instantiated. This really only happens on single views (ala, getting a list of Registrants from a search query doesn’t load the entire Registrant, just an index), so it shouldn’t make things too burdensome. It has the advantage of making everything easily accessible if you have the Registrant at hand. Right now a registrant’s attendance history is attached on instantiation (for Arisia, this is perhaps 20 rows, but that’s somewhat unusual). If I don’t embed the list in the Registrant object, I’ll need a second set of lookups and queries when viewing a registrant, which is a little cumbersome.

For now I think I’ll embed it, and hook up the DAO’s appropriately. It’ll make the display logic a lot easier (data is at hand), and if we end up beating the bejeezus out of the database for largish events, I’ll look at optimizing it.

Onward and forward. Today I need to focus on the paying job and finish up an annoying script, hoping to get it audited and approved by the other engineer. Have I mentioned that Perl is not my favorite language? Oh there is a rant pending…

Weekblog: Day 1

26 08 2008

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) {

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

Weekblog: Day 0

25 08 2008

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. 🙂

A WeekBlog – A Post a Day – CONGOv2

24 08 2008

So this week I’m faced with a situation I haven’t had in front of me in, well, as long as I’ve been married… at least as long as I’ve had kids in my life.
This week Cat is up in Maine with Zach. I have my normal work going on during the day, but no commitments for the evenings.
A unique opportunity to be sure.
The Plan [tm]
So here’s what I’m going to do. A few months ago I started work on a complete rewrite of CONGO. The rewrite is underway, and has been getting attention fairly regularly over the last couple weeks, but I need to make the final push to an alpha release.
This week, I will dedicate 2 hours a night every night to continuing work on CONGO v2, with the goal of reaching an alpha-testable version by the end of the week. I will also make a post each night with an idea of where my progress is on the rewrite, and what I’ve accomplished. (I do reserve the right to post the next morning if I’m up until Oh-Dark-Thirty coding and fall asleep in the middle of writing an exception handler.)
Anyone wishing to follow the riveting details of my work, I subscribe to the Commit Early, Commit Often philosophy of source code control, so when I’m working, you’ll see commits firing off pretty quickly. If this sounds interesting, you can sign onto the mailing list, or, if you’re uber-hip, subscribe to the RSS feed.
Yeah? So? Why us?
So why bombard ya’ll with my chattering? Well, I work better with encouragement, or if I know folks care about what I’m doing. Curious about bits of CONGO? Ask! Wanna help out? Give a “wow, kick butt, dude. Go for it” comment or two.
Hopefully I’ll have some work to show for tonight, but if not, stay tuned for truly exciting blow by blow Java coding!

So… shiny…

20 08 2008

So, 3 days I’ve had my iPhone now, and I tell ya, it’s hard to put down. So hard I’ve had a hard time finding time to write to the blog about how much I’m enjoying it.

Here’s a couple highlights of my first, realistically, 2.25 days with the phone. It’s a black 16gig iPhone 3G, just to make sure everyone is on board.

  • Within two hours of powering up the phone (20 minutes of which I was driving), I had successfully attached to my SSL IMAP server at home and was reading my inbox, connected to my gmail account, AND attached to the Exchange server at work. All quite seamlessly. The accounts play well with each other, I can set up Fetch or manual updates (so cute having the phone next to me go “chime!” when I get new mail, but for busy inboxes, gets old fast).
  • Getting my Contacts synced from Google Contacts was a little trickier, but iTunes for windows just BARELY had enough functionality to make this possible. But, it sure was nice getting all 400 some odd contacts back into my address book.
  • The iTunes Appstore is wonderful. Many good applications available, and it’s trivial to install them either via iTunes, or directly via the appstore link on the phone.
  • It’s very hard to keep reminding myself this is a 3G, unlimited Data plan unit. Once I got past that mental block I tuned into RadioParadise, hit the streaming 3G feed, and listening to the station all the way home. That’s a 45 minute driving commute, RP didn’t burp once, feeding music to a moving car in Boston at 128kbps. Just too sexy for words.
  • I’ve ordered a simple skin from Gelaskins – I’m constantly afraid of scratching the phone – having some sort of barrier to keep it’s gleaming body away from my pocket change is a must.
  • The keyboard took some getting used to – but with Apple’s smart fingertip placement, AND a very intelligent replacement algorithm that ‘just plain works’ (none of the ‘did you mean This?’ nonsense. 99% of the time it guesses right, and you don’t have to do anything, it just replaces the text for you, after letting you know via a very nice animation that it was doing so).

In short. I love it. I’ve named it Speicus.

More kvelling later.