Sunday, March 15, 2009

Importing NULL values into Sqlite

Finally starting up the blog again. I'm hoping to document troublesome things I've run so that it will maybe help somebody else.

Today's challenge was Sqlite's .import statement. Apparently, there is no way to import a NULL value into a database. Even for an integer or float field, a blank value will simply be stored as an empty string. You have to use an update statement after the fact to fix the value:
update tablename set field=NULL where field='';

The real problem was the way this problem presented itself in Django. I was trying to run a command that updated entries in the database, and I kept getting the following error:
ValueError: invalid literal for int() with base 10: ''
in a function called get_db_prep_value. Not very intuitive, but I finally figured out the issue by debugging it with Winpdb. Changing all my non-char fields that allow NULL values to proper NULLs fixed the problem.

Tuesday, March 18, 2008

I recently put my latest creation online, a mashup of Seattle area restaurant health inspections www.safefoodfinder.com. This mashup uses Google Maps API with the ExtJS Javascript library to provide some UI elements including a nice layout with tabbed/collapsible side bars, an auto-complete search box, and a tabbed main window. Adding simple dialog boxes is also very easy. The tabbed main window is a great way to show more information about a marker than will fit in a reasonably-sized pop-up bubble. I've seen some mashups that link from a bubble to an external site. The problem with that is that the entire Google Map needs to re-load when the user comes back from the link.

The primary down-side to this approach is the large size of the ExtJS library. I've tried to mitigate this by creating a package of only the needed parts of the library and tagging it for caching (YSlow is a great tool). I've also found that the tabbed interface confuses some users. They don't immediately realize how to get back to the main map tab. Hitting back will leave the site completely. ExtJS doesn't provide any history handling yet, although I know other libraries like YUI will intercept the back button and display a previous tab.

In the spirit of open cooperation, I've provided the non-minified Javascript code for the site at http://www.safefoodfinder.com/map-debug.js. I think it's a fairly decent example of object-oriented Javascript.

I know Google announced a while back a partnership with CleanScores to provide inspection data with restaurant reviews, but I thought it would be worthwhile to have a map that concentrates on health inspections.

I also modified my prior web site www.runwayfinder.com, a pre-flight planning tool for pilots, to use ExtJS. It shows how the dialog box works. When a user first visits the site, a modal dialog pops up displaying recent news about the site.

Labels: