Have you used Stanza yet?

If you haven't heard it, now now is a good time to do it. Stanza is probably the best app I have used on the iPhone yet. It's also probably the app that makes the best of the iPhone's look and feel and its connectivity to give you a wholesome experience. So, what does it do? Allow you to read books. What else does it do? Let me list it out:

• Allows you to directly download e-books to the phone from free source e-books like Feedbooks, Project Gutenburg etc.
• Allows you to bring in PDFs into your phone through their Desktop counterpart app, called Stanza Desktop.
• Allows you to read both in portrait and landscape modes.
• Allows you to read either in black-on-white or on white-or-black.
• Allows you to change the contrast, brightness, font size to improve your reading experience.
• Takes up full screen to increase reading space.
• Flipping pages is super easy (tap on right for next and on left for previous page) and fast (none of that waiting business)
• Works as expected when a call comes in while you are reading - picks up where you left off.
• Do you like to add annotations or bookmarks as you read for future reference? You can do that too. 

As far as I am concerned, the only thing that is missing from this to completely replace Kindle/dedicated-ebook-readers is the ability to buy e-books from paid sources. But I don't care. There is a lot of free e-books available without copyright and of course I have PDFs (papers, short stories, etc) that I have to always clear up and Stanza is the way to do it.

And the icing on the cake? Its free. Yes, it really is.

Conditional MySQL statements: Lifesaver on occasions

Have you tried building a pageview counter? Lets say you want to count the number of pageviews on a site/page on the following granularity - today, yesterday and accumulated. Now, lets assume that the Table is defined as follows:

CREATE TABLE pageviews (
     page_id varchar(100) not null,
     last_update_time int(11) not null default UNIX_TIMESTAMP(),
     today int(11) not null default 0,
     yesterday int(11) not null default 0,
     acc int(11) not null default 0
);

Now, your counter would normally apply a query like this to increment the counter:
update pageviews set today = today+1, acc = acc + 1, last_update_time = UNIX_TIMESTAMP() where page_id = 'xxx';

But when do you move the today counter to yesterday? Remember that after the stroke of midnight, today count should switch to yesterday and reset to 0. You could always write a cronjob which would do this for all the rows in the table at the stroke of midnight, but the trouble is that as the number of rows increase, the execution time of such a query increase and in the meanwhile (say between 0001hrs and 0010hrs), queries on the page counting client would either be blocked or be incorrectly executed making it an undesirable situation.

The solution to that is a conditional MySQL statement like the following:

UPDATE pageviews SET 
        yesterday = CASE WHEN last_update_time > xxx_ts THEN yesterday ELSE today END,
        today     = CASE WHEN last_update_time > xxx_ts THEN today + 1 ELSE 1     END, 
        acc = acc + 1, last_update_time = UNIX_TIMESTAMP() WHERE page_id = 'xxx';
where xxx_ts is the unix timestamp of the stroke of midnight of today. 

This is essentially crunching all that complex logic of having to worry about the switch time into the query. Remember that at the MySQL end, all the queries will be applied one after the other, and hence even if the same query is run one after the other, it will be set correctly.