David S. Gale

Programming, Life, and what I think about it all

WriteTrack, Version 2!

by 1 Comment

I’m very pleased to announce that I’ve released the new version of WriteTrack. The underlying concept hasn’t changed, but the engine has been updated to APEX 5.0, the theme is a variant of the Universal Theme, and I’ve implemented several improvements that are possible because of the new APEX engine. Which means that it’s faster, more secure, and just plain looks better than ever!

If you’re a writer, please try it out. If you’re not a writer but are at all interested in trying it, now’s a great time to start preparing for National Novel Writing Month, which was the driving force behind my creating WriteTrack in the first place.

Have fun, and keep writing!

Announcing WriteTrack

by Leave a Comment

It's about a month later than I intended, but I've finally updated the wordcount tracker I built for NaNoWriMo last year to allow for users to create their own personal challenges. I'll be blogging about some things I learned in the process over the next week or so, but I wanted to get the overall announcement out now.

Oh, and I've decided to call it WriteTrack.

Time zones, or, You’re logging in from where?

by Leave a Comment

This is going to be one of those posts where I talk about an issue I'm working with, but where I don't even pretend to have a definitive answer. So, what follows is what I've managed to figure out, but there may be a better way (in fact, I kind of hope there is).

My NaNoWriMo tracker has proven to be more popular than I originally expected–I now have over 50 registered users. And looking at my traffic sources, a problem occurred to me–I'd designed the application without considering the fact that it'd be used by people all over the world. And since almost all of its functionality is very time-focused, this was a problem.

The first thing I needed to fix was to change my view logic–there were several instances where I used sysdate (don't we all?), and those needed to be changed to current_date. sysdate gives the current database time, while current_date shifts it to the local time in the current session.

Of course, that requires that we actually be able to set the time zone for the current session. APEX gives us a handy procedure–apex_util.set_session_time_zone. This procedure does exactly what we want, though the timezone must be passed as an offset from UTC. More on that later.

Next, we need to provide the users with a list of timezones to chose from. I really struggled with this one; in the end, the best I could come up with was:

select tzname||' ('||tm||')' d, tzname r
from (select distinct tn.tzname, substr(tz_offset(tn.tzname),1,6) tm
      from v$TIMEZONE_NAMES tn
)
order by decode(substr(tm,1,1),
        '+', 24 + to_number(substr(tm,2,2)),
             24 - to_number(substr(tm,2,2))) desc,
    to_number(substr(tm,5,2)) desc,
    tzname

This results in a very long list, but ensures that the users are able to find something that matches their location. Just storing the offsets would make for a smaller list, but then you have to worry about locations that don't observe Daylight Savings Time (or, conversely, those that do). tzname is varchar2(64), so I added that to my central users table; I could have added it to an application-specific config table, but someone's location isn't going to change based on which application they're using.

Finally, I created an application-level process to call apex_util.set_session_time_zone(tz_offset(…)) with the timezone stored in the user table. This ensures that the timezone is properly set, regardless of which page the user lands on.

The one feature I wasn't able to add that I wanted to was a field next to the timezone selector that would update when the user chose a new timezone and display the current time in that timezone. I'm not sure why this wasn't working; I tried several different dynamic actions, and found that on my test page, at least, it was using the original value of the select box instead of the selected value. I'm going to have to do some more testing to see if I can figure out what I was doing wrong there.

When a cartesian join is right

by Leave a Comment

Generally, one of the first mistakes someone will make when they write SQL is to create a cartesian join by leaving off part or all of their where clause. This generally results in queries that take forever to finish (if they ever do), and have far too much information in them. If you do an explain plan and you see “cartesian join”, the natural impulse is that you've done something bone-headedly stupid and that you need to fix it. And this is almost always the case.

But when I was building the NaNo tracker (see the previous post), I found myself needing a cartesian join. It felt weird, and I admit that I had trouble typing it, but it was the right tool for the job.

When a user first logs into the tracker and starts a challenge (there's only one to chose from so far, but I've got plans), they are taken to a page with a calendar on it. All of the days of the challenge are populated with default data. I briefly considered putting a loop into the registration process that would auto-populate these defaults, but I was concerned that that might result in a lot of useless data cluttering up my tables; alternatively, I could add a lot of special-case code to my application to handle the “null” situations, but that was also an unattractive solution. No, I only wanted to save a record when a user actually changed something, and avoiding special-case code is always good. So, instead, I drive the calendar off of a view. Ok, a series of views. And the core data of the view actually doesn't exist in any table.

First, I use a nifty trick with hierarchical SQL to get all of the dates between the start and end dates of the challenge:

select c.challenge_id,
  c.start_date + v.lvl cal_date
from challenge c,
  ( select challenge_id, level - 1 lvl
    from challenge c2
    connect by level <= (c2.end_date - c2.start_date + 1)) v
where c.challenge_id = v.challenge_id

Then, I join this against the user data to get a view that has a record for each user for each date using--yep--a cartesian join:

select d.challenge_id, u.user_id, d.cal_date
from dates_v d,
   	users u

No where clause at all (ok, that's not quite true--I filter the users down to those who can use the application; then again, I'm also changing the table names out of paranoia). This allows me to treat my default values as if they are actual rows in the database, without having to actually create them until they're needed, and I don't have to create any special-case logic within my application. All of this magic is encapsulated into two simple views, and because of the nature of cartesian joins, it just works.

NaNo.DavidSGale.com

by Leave a Comment

I'm pleased to announce the release of my first publicly available APEX application. It's a tracker for your progress through National Novel Writing Month; here's a…trailer? presentation? um…teaser for it:

Of course, it's an initial release, so there may be some issues (though as noted before, many of those got caught in the documentation phase), and there are new features that I'm looking forward to adding (soon!).

Some history: when my wife first participated in NaNoWriMo in 2003, we quickly realized that the standard “Just write 1,667 words per day” advice falls short of the reality–some days you're busier than others, and so can't write as much. And if you don't plan for those and write more on the days you can, then you will fall behind, get frustrated, and (probably) quit. So I put together a file in Excel which adjusted how much she needed to write based on her expected availability, and it worked very well. (Yes, she made her target!) I used the file when I tried my hand at NaNo in 2009, and believe that it helped me to win as well.

But the problem with the Excel file was that it wasn't very easy to customize. Each time it was reused, I needed to tweak its setup; if I used it for something other than a 30-day challenge (which we did), I needed to do a fair amount of work to fix the math.

Enter APEX. Since it's driven by a real database, I can build it once and let the database handle the logic. If I want to set up a new challenge, it's as simple as creating a new record with start and end dates and a total target; everything else happens automatically. (I'm using some tricks to make this happen, which I'll be blogging about soon.)

I had a lot of fun participating in NaNoWriMo last year, and am pleased to be able to contribute to the enjoyment of others by making this tool available. 

Update: As oh-so-gently pointed out to me below, I neglected to post any screenshots. To rectify that, you can view a bunch here