Soon a small black box will enter our living rooms, and that will change the way we spend time in front of TVs forever.  It will happen on Nov. 4, 2010, and the name of this phenomenon is Kinect.

In my student days I was an avid gamer and has played virtually all “big” games that came out for  PC, luckily for me those days it wasn’t such a flood as now. And I remember well the events that changed the game industry as we knew it. Those milestones were:

• Doom - the first popular first-person shooter, 1993;
• 3dfx Voodoo - the first 3D accelerator, 1996;
• Nvidia GeForce 256 - the first 3D accelerator with integrated geometry GPU, 1999.

After that, I stopped actively playing games did not follow the gaming news. And returning sporadically into the game world I was discovering that nothing actually has changed. Well, the resolution was higher, graphics better and explosions more colourful. But the gameplay still remained the same - defined by Doom at the beginning of 90s. Of the remarkable events only the release of Nintendo Wii in 2006 comes to mind, which changed our perception of computer games. They were no longer something available only to nerds. Now the whole family could play and, more importantly, while moving rather than sitting still.  Perhaps it was then, when Microsoft saw that and decided to take the idea to a new level.

Kinect is a small box connected to the Xbox 360. It has 2 cameras that watch the player, detect the position of his body and movement, and thereby enable you to control events on the screen. Motion Control in a pure form – simple and brilliant. No controllers, no wires. According to Microsoft’s statements, one Kinect can completely digitise movements of  two players and track the positions of four more. The players may be standing or sitting. In addition, Kinect has multi-array microphone through which it can recognise the voice of his “master” and obey his orders.

Microsoft presented Kinect a year ago at E3 exhibition, then it carried the working title “Project Natal”. And immediately it was clear that it would be a revolution, if only Microsoft would be able to deliver on promises. Now, when Kinect is very close to the release, Microsoft has distributed sample devices to leading gaming magazines. And now we can say firmly - Microsoft succeeded. According to the lucky ones who played with Kinect, watching first time the character on the screen waiving its hands in coordination with you movements is fascinating. Motion Control is not perfect, but still very good.

This is what a gameplay with Kinect looks like:

I predict that Kinect will be a huge hit. It along with the new Xbox 360 will take the market by storm. Potential customers will have to stand in a queue for hours to buy it. And for some time after launch it will not be possible just to walk into a store and buy one, as it is now impossible to buy an iPhone 4. Neither Sony, nor Nintendo has anything comparable.

So, we are on the eve of the revolution. Now it is the turn of gaming companies  to help us fully explore this bright new world.

As I have already shown you, implicit type conversion is one of the most dangerous features of Oracle SQL and PL/SQL. It is dangerous because it happens automatically without your knowledge and may lead to unpredictable results. These problems are most common when dealing with DATE conversions, but not limited to them.

For example, let’s have a look at this Stackoverflow.com question by James Collins. James had a problem, the following query was slow:

SELECT a1.*
FROM   people a1
WHERE  a1.ID LIKE '119%'
AND ROWNUM < 5

Despite column A1.ID was indexed, the index wasn’t used and the explain plan looked like this:

SELECT STATEMENT ALL_ROWS
Cost: 67 Bytes: 2,592 Cardinality: 4 2 COUNT STOPKEY 1 TABLE ACCESS FULL TABLE people
Cost: 67 Bytes: 3,240 Cardinality: 5

James was wondering why.

Well, the key to the issue lies, as it often happens with Oracle, in an implicit data type conversion. Because Oracle is capable to perform automatic data conversions in certain cases, it sometimes does that without you knowing. And as a result, performance may suffer or code may behave not exactly like you expect.

In our case that happened because ID column was NUMBER. You see, LIKE pattern-matching condition expects to see character types as both left-hand and right-hand operands. When it encounters a NUMBER, it implicitly converts it to VARCHAR2. Hence, that query was basically silently rewritten to this:

SELECT a1.*
FROM   people a1
WHERE  To_Char(a1.ID) LIKE '119%'
AND ROWNUM < 5

That was bad for 2 reasons:

1. The conversion was executed for every row, which was slow;
2. Because of a function (though implicit) in a WHERE predicate, Oracle was unable to use the index on A1.ID column.

If you came across a problem like that, there is a number of ways to resolve it. Some of the possible options are:

1. Create a function-based index on A1.ID column:

CREATE INDEX people_idx5 ON people (To_char(ID));

2. If you need to match records on first 3 characters of ID column, create another column of type NUMBER containing just these 3 characters and use a plain = operator on it.
3. Create a separate column ID_CHAR of type VARCHAR2 and fill it with TO_CHAR(id). Then index it and use instead of ID in your WHERE condition.
4. Or, as David Aldridge pointed out: “It might also be possible to rewrite the predicate as ID BETWEEN 1190000 and 1199999, if the values are all of the same order of magnitude. Or if they’re not then ID = 119 OR ID BETWEEN 1190 AND 1199, etc.”

Of course if you choose to create an additional column based on existing ID column, you need to keep those 2 synchronized. You can do that in batch as a single UPDATE, or in an ON-UPDATE trigger, or add that column to the appropriate INSERT and UPDATE statements in your code.

James choose to create a function-based index and it worked like a charm.

SYSDATE is one of the most commonly used Oracle functions. Indeed, whenever you need the current date or time, you just type SYSDATE and you’re done. However, sometimes it’s not all that simple. There are a few confusions associated with SYSDATE that are pretty common and, if not understood, can cause a lot of damage.

First of all, SYSDATE returns not just current date, but date and time combined. More precisely, the current date and time down to a second. If just a date is needed, TRUNC function has to be applied, that is, TRUNC(SYSDATE). For a sake of a good database design, date should not be confused with date/time. For example, if a column in a table is called transaction_date, it would be natural for it to contain a date, but not date/time. That may lead to a major confusion. Let’s imagine there is a table BANK_TRANSACTIONS containing the following fields:

txn_no     INTEGER,
txn_amount NUMBER(14,2),
txn_date   DATE

The last field is of the most interest to us. Apparently its data type is DATE, but is it a date or date/time? We can’t tell by just looking at the table definition. Nonetheless, it is a very important thing to know. A common case for using DATE columns is including them in date range queries. Forexample, if we wanted to get all the bank transactions from 1 January 2009 to 31 July 2009 we could write this:

SELECT txn_no,
       txn_amount
FROM   bank_transactions
WHERE  txn_date BETWEEN To_date('01-JAN-2009','DD-MON-YYYY')
AND    To_date('31-JUL-2009','DD-MON-YYYY')

And that would be fine if TXN_DATE were a date column. But if it is a date/time, we would just have missed a whole day worth of data. And it is because, as I said, DATE data type can hold date/time down to a second. That means that for 31 July 2009 it could hold values ranging from 0:00am to 11:59pm. But because TO_DATE('31-JUL-2009', 'DD-MON-YYYY') is basically an equivalent to TO_DATE('31-JUL-2009 00:00:00', 'DD-MON-YYYY HH24:MI:SS'), all the transactions happened after 0:00am on 31 July 2009 would be missed out.

That kind of mistake is pretty common. Sometimes it’s hard to tell by just looking at the data whether a particular DATE column can have date portion. Even if all the values in there are rounded to 0:00 hours, that doesn’t mean that a different time value can’t appear there in the future. The data dictionary can’t help us here either – DATE type is always the same whether it contains time or not. (By the way, Oracle recommends using TIMESTAMP type for new projects, but that is a whole different story.)

If you are working with an existing table and you are not sure, you can use a fool-proof method like this:

SELECT txn_no,
       txn_amount
FROM   bank_transactions
WHERE  txn_date BETWEEN To_date('01-JAN-2009','DD-MON-YYYY')
AND    To_date('31-JUL-2009','DD-MON-YYYY') + 1 – 1/24/3600

+1 – 1/24/3600 here means “Plus 1 day minus 1 second”. That is because “1” in DATE type means “1 day”, “1/24” - 1 hour, and there are 3600 seconds in an hour.

The above expression will retrieve all the transactions from “01 January 2009 0:00am” to “31 July 2009 0:00am plus 1 day minus 1 second”, i.e. to “31 July 2009 23:59pm”.

If you are charged with designing an application and need to create a table with a DATE column, it is worth to keep yourself and others from future confusions by a simple trick: name columns that only contain date portions as DATE and add TIME to the name of the columns that you know will contain time components. In our case it would be prudent to call the date/time column TXN_DATE_TIME.

The second issue I’d like to discuss is much more subtle, but can do even more damage.

Imagine that you are charged with developing a report that returns all the transaction for the previous month. It looks like a job for SYSDATE! You fetch your trusty keyboard and after a few minutes of typing you come up with something like this:

SELECT txn_no,
       txn_amount
FROM   bank_transactions
WHERE  txn_date BETWEEN Last_day(Add_months(Trunc(SYSDATE),-2)) + 1
AND    Last_day(Add_months(Trunc(SYSDATE),-1))

You create a few lines in BANK_TRANSACTIONS table, run a few unit tests to make sure your code works and check it into the source control. Job done! You congratulate yourself on the productive work and spend the rest of the day reading your friends' blogs and dreaming about your next vacation. And the next day you move on to another task and get as busy as ever.

After some time, which may be a few days or months, depending on the pace of the project, the code you wrote gets migrated into the UAT environment. And a task force of a few testers and end users is assigned to test the report you wrote. And as it often happens in UAT, they are going to test in on real data they extracted from the production system – that is, the last year’s data.

Got it? Last year’s.

The final stages of testing, such as UAT, have to prove that the system does what it is expected to do in conditions that resemble the production as closely as possible. And the best way to do that is to test it on the retrospective production data – the data that is proven. That makes it possible to compare the outcome to the actual production system, and thus, prove or disprove that the new system works.

That sounds reasonable. But one of the implications for you is that BANK_TRANSACTIONS table is not going to contain previous month’s transactions. Hence, your report will be blank. You can’t rewind back time because you hard-coded SYSDATE, which has only one meaning – “right now”. Test failed.

If you have known that when you wrote it, you wouldn’t have used the SYSDATE. You would use a parameter, something like v_run_date, which you could set to whatever date you wanted. And that would do. Well, now you know.

You only need a single look at Sydney Opera House to recognise that it is a work of art. Any masterpiece is like that – you don’t need to do a throughout examination of Mona Lisa’s smile to realise its beauty – you see it instantly. Perfection needs no explanation, it works on subconscious level.

The same applies to the software engineering too. Great code always looks good. It is always carefully formatted, indented and commented. By just looking at it you can tell that it is a work of art. Such code will always work, do what it is supposed to do and have a very few bugs. Because whoever wrote that code cared a lot about it. And you can safely assume that if anyone has put a lot of effort into making the code looking good, he has put at least as much effort into designing and debugging it.

What is even more important, carefully carved code is easier to maintain. In modern software projects any single procedure gets tweaked and rewritten tens of times. If you are a programmer, good chances that even in a project that you work on right now you inherited some code that was written years ago, maybe from people who long left the company. And when you finish with it, it will not be the end of the story – the code will be passed to QA and finally to the production support. And then the cycle will start again. Hence, whatever you program, it’s not just about you. You don’t know how many people will be looking into your code trying to make sense of it. And you can help them immensely by making it is easy to read and understand now. Whether you will be remembered as a good programmer or cursed depends on it.

So, you made an effort to write the code that works. Now make an extra step – make it beautiful.

Those who know me know that I’ve been searching for a perfect ebook reader device for years. I’ve used mobile phones, Palm PDAs, pocket PCs, laptops… About 7 years ago I got excited about prospects of E-Ink technology, promising to deliver just what I needed. Unfortunately, E-Ink has turned out to be utter disappointment, so far delivering little but bold promises. It took years and years for the first E-Ink devices to turn up on the market.

A few years ago I finally got my hands onto Sony PRS-500 E-Ink reader. Got disappointed with it after a week and sold it. A year ago I bought another E-Ink reader - that time BeBook. I still have it, but now my wife uses it. And I’ve got myself something better. That is iPhone.

Don’t get me wrong. I still like E-Ink readers. But even after all those years E-Ink remains to be a promising technology. Guys, it’s about time to deliver on promises. Maybe one day… But now, E-Ink has more cons than pros: devices are expensive, screens are slow, the screen contrast is poor. And versatility is even poorer - every E-Ink reader is practically useless for anything but reading, making you carry yet another device in your bag. And if all those weren’t enough, many manufacturers lock the devices to just a few supported DRM-enabled book formats (this is for you, Amazon).

In few short years, iPhone delivered what E-Ink failed in decade. The truth is, the iPhone’s screen is brilliant. I don’t know how they did it, but it’s bright, highly contast and it (almost) doesn’t fade in sunlight. Yes, it’s resolution is not as high as of some VGA PDAs and laptops, but it still looks better than any other LCD screen I’ve seen. Reading from that screen is a pleasure. Honestly, if I am provided a choice to read from either E-Ink (in its current state) and iPhone’s LCD, I’d probably choose iPhone.

And unlike the dedicated readers, iPhone is immensely versatile. I don’t know the statistics, but it’s got to be the most popular mobile software platform out there. Thousands and thousands of software titles are available. And there is no problems with ebook types either - Software like Stanza and Calibre make reading almost any kind of book a simple task.

All in all, for all of you who has been waiting for a perfect reader - the wait is almost over. iPhone has delivered yet another revolution. Now is the time for a major publishing houses to wake up.