SQLite has been my life as of late and because of this I’m discovering of a number of tricks. The one I’ll reveal today, once again makes absolutely no sense at all. (Barney shed some light on my previous find, so it wasn’t as astonishing)

Let’s say we have a database of people, and in this database, we separate names into first and last names—this comes in handy for sorting by last name, etc. In our case, we want to retrieve the names into a combined-form, such as name=Jonnie Hallman instead of firstname=Jonnie, lastname=Hallman. This could easily be done, after the fact, in Actionscript, but we want to optimize the process. SQL wizard, James Hall, suggests the following query:

// Correct in SQL // Incorrect in SQLite
SELECT (firstname + ” ” + lastname) AS fullname FROM tablename;

It doesn’t receive an error in SQLite, but it does return a value of zero for that column. In SQL it works though. Here’s the query that works in SQLite:

// Correct in SQLite
SELECT (firstname || ” ” || lastname) AS fullname FROM tablename;

Stunned?—I am. Why would the logical “OR” equivalent be used to combine two strings? If you have the answer to the insanity, please enlighten me. If not, enjoy the tip!

[update] Thanks for the correction on the logical “OR,” not bitwise.

I definitely don’t need another clock, nor do I need anything, besides my thermostat, telling me what the temperature in my apartment is … but WoodStation makes me rethink those claims. This beauty is a clock, an indoor weather station, and a block of wood. It even has an unnecessary proximity sensor that turns off the LED lights when too close. And at $99, it’s a bit of a stretch, but it will certainly add some variety to your aluminum-filled room.

via Uncrate »

I’m working on storing all tweets viewed in DestroyTwitter into a local SQLite database to improve performance with filtering and paging. I’ve already been using databases in order for Groups to work, but to avoid characters that might be trouble for the SQLite queries, I would simply run the escape() method to URL-encode the string. Lately, I’ve been on this testing-high, making sure every bit of code I write is the most efficient to my knowledge. After testing escape() vs String.replace(), I realized that the latter is ten times faster. Of course, it makes perfect sense.

My lazy self months ago used escape() without thinking, but now that I know the incredible speed difference, I’ve created an addSlashes() method to use a regular expression to prefix each single quote with a slash (\). This resulted in an error. After a bit of research aka Googling, I came across a mail archive that said that SQLite escapes single quotes a bit differently. Instead of a slash, you must escape it with another single quote. In all honesty, this makes no sense to me, but it works. Here are examples of what works and what doesn’t:

// Wrong – unescaped apostrophe
INSERT INTO tablename (id, name, text) VALUES (1, ‘Jonnie’, ‘That’s what she said’);

// Wrong – incorrectly escaped apostrophe
INSERT INTO tablename (id, name, text) VALUES (1, ‘Jonnie’, ‘That\’s what she said’);

// Correct – unescaped apostrophe
INSERT INTO tablename (id, name, text) VALUES (1, ‘Jonnie’, ‘That’’s what she said’);

Please note that the correct query above uses two separate single quotes, not one double quote.

If you answer yes, please comment what regular expression you use.

I’ve decided to rethink things a bit in DestroyTwitter, so I’ll be conducting a survey over the next few weeks. Today’s question is whether in-app photo viewing is working within the main window or should it be its own window? There are benefits to both, but what do you think?

Over on Wired.com, there’s an interesting post on the elephant in the room—Craigslist’s sad, sad website design. We all think about it when looking for apartments or spending a night on casual encounters, but nothing is done. Apparently, Craigslist stated they have no interest in improving the design of the site, so Wired gathered a few designers together to give it a shot. Honestly, you could simply change the blue/purple link text to anything and it would make a world of difference—that goes for you too, YouTube (say that three times fast).

via Bobby Solomon »

If there any other answers not listed here, feel free to fill up the comments. Also, if you’d like to extend your answer, further commenting is encouraged.

After 12 years of programming, I finally learned how breakpoints work. I tweeted about it and, surprisingly, a number of people asked for a tutorial—sure thing! I must say, though, that I haven’t double-checked that the way I’m using breakpoints is entirely correct, so hit me over the head if this post is a farce.

First, let’s start with the example code. Above is a simple test that draws a handful of randomly-colored stripes, as seen below. Click on the image to see a close-up of the code.

Now, what I want to do is see what the hex value is for the fourth stripe from the top. Prior to yesterday, I would’ve simply thrown a trace method in the loop and looked at the console. It works, but what if I want more information—I’d have to change the trace and recompile. Also, what if I want to prevent the code from proceeding past the loop—in case there’s a runtime error that I don’t want to deal with right now.

What I do is set a breakpoint after the loop. I do this by right-clicking the line number after the __build (); call. If I set the breakpoint on the same line number as the function, it will break before calling it.

This is what a breakpoint looks like.

This is what a breakpoint looks like on drugs, or after I run the debugger. At this point, it will stop and highlight the line you set the breakpoint on.

Head on over to the Variables view and it will appear as above. Expand the this item and it will show an [inherited] item. Expand that and you get a HUGE list of variables—some of which you’ve never imagined existed.

Scroll down to the array of objects (_objects) that held the colors along with the n value of each. Typically, you could just trace an array and it will display just fine in the console. That works only when the array consists of printable variables, such as numbers, strings, booleans, or classes that have a handy toString (); method. Unfortunately, this example uses Objects in the array, and if I traced the array, it would only show [object Object], [object Object], [object Object], etc. Luckily, I set a breakpoint.

Now, expand that _objects item in the Variables view and it will display the items in the array, annotated by the n value of the object—so we actually didn’t need to set one in the first place.

I pick out the fourth color down the list, expand it, and I have my values. It tells me the hex value as an integer along with the n value of the stripe.

Lastly, if you want to continue on with the debugging, click the Resume button in the Debug view.

This is an extremely simple example of how breakpoints can be used. In a real world scenario, I can set up multiple breakpoints, disable ones I’m not using, and enable them when I want to use them. I’m sure there’s a whole lot more you can do, but this example is based off how I use them without looking at documentation.

I haven’t written a tutorial post in a long while, but this has inspired me to continue with more. I’ll be sure to document my profiling workflow soon, which is essential for any developers concerned with memory usage and hanging references.

Ajaxload is a nifty tool for the Web 2.0 crowd. With a few simple steps, it will generate one of the ever-popular, animated loaders. As you can see from the menu below, there’s a whole slew of them. The bonus of Ajaxload is that you can specify the foreground and background colors, including transparent as an option.

via Ben Kutil »

QLOCKTWO goes straight to the list of Things to Buy If Money Were No Object. It’s such a gorgeous clock with a fantastic idea behind it. I immediately thought, this would look great in my office, right? Unfortunately, the downside of this beauty is the $1200 price tag. Maybe someday, when I get back into Arduino programming, I can build one myself.

via Design Milk »