Last 5 comments
117 years ago
Rafael:  Thank you very much, I was having a huge headache to solve the very same problem!
123 years ago
Ray:  Having the same problem. Very frustrating. Luckily, for some reason my released version worked on the iPad itself, but now I can't get it to run in the simulator. Getting no such table, which I'm guessing is an initialization error. Will continue to investigate.
123 years ago
Jeremy:  FYI, I've just tried it with the SQLite 3.7.0 preview and the same problem occurs.
Also, I'm not using any extra third-party libraries with my SQLite, so the problem isn't your Unicode extension.
123 years ago
Jeremy:  I'm having the same problem with compiling SQLite against iOS 4 for the iPad simulator, but in my case it works fine running on an actual iPad (also works in the iPhone simulator and on an iPod Touch).
Same problem with, 3.6.23, and at least back to 3.6.21. Compiling against iOS 3.2 makes it work, though that's not really an option for iPhone (as opposed to iPad) apps.
I have no idea what to do about it or how big a problem it really is...
124 years ago
Pascal:  The problem seems to have deep roots, however there is a solution, see the updated post. :)
The archive
March 2011  (1)
July 2010  (1)
July 2009  (1)
March 2009  (1)
July 2008  (3)
June 2008  (1)
May 2008  (3)
March 2008  (1)
July 2007  (1)
June 2007  (3)
May 2007  (1)
April 2007  (1)
July 2006  (2)
June 2006  (6)

Eponyms for the Mac now available

Friday, March 25th 2011 - 13:58
Icon.pngAfter some delays, the first version of Eponyms for the Mac, aka MacEponyms, is now available at the Mac App Store.

As the iOS and Android versions, this application accesses the database created and maintained by Andrew Yee, updates can be downloaded right from within the application. There currently are 28 categories such as Anatomy, Gynecology and Radiology, you can search all eponyms as well as star eponyms to create your own eponym list. The two Random buttons display a random eponym from the currently active list, either only the title or only the description, so you can memorize your eponyms.

See Eponyms in the Mac App Store right here, and let us know what you think on our support site.

Temporarily remove specific localizations from your iPhone/Mac App

Thursday, July 22th 2010 - 20:23 • 1 update Friday, October 15th 2010 - 15:14
Since our translators didn't work as fast as we coders did, we were in need to strip one specific language – Spanish – from an iPhone App – MedCalc – which we'll be shipping next week. And because MedCalc has more than 140 XIB files, the same number of XML files and the double amount of strings files, it would be very tedious to manually delete all Spanish localizations (not to mention to re-add them once the translators are done). So I needed a way to tell Xcode not to include one specific language for now, for which I didn't find a solution.

But there is a pretty straightforward solution: Let Xcode build the App with all the present localizations and just before code-signing kicks in delete the Spanish.lproj folder and all localized files are gone. You can easily do this by adding a Run Script build phase to the target, containing this one line of code (it's a Bash Script):
rm -r "${TARGET_BUILD_DIR}/${PRODUCT_NAME}.app/Spanish.lproj"

Code Signing always kicks in after all build phases have completed, so just put that build phase at the end of your current phases.

Props to macmoonshine from for proposing this solution!

Update October 15, 2010
Note that this increases the time to build because Xcode recreates all Spanish files, so you might want to disable this step during testing.

Again and again and again Installing DBDmysql under OS X

Wednesday, October 7th 2009 - 17:11
With every new release of Mac OS X, the need to re-install Perl's MySQL driver "DBD::mysql" arises again. And every single time it does not work out of the box (i.e. using cpan). Since I'm giving up using Perl and write my new scripts (yes, also the Shell scripts) in PHP, partially because of problems like this, I was able to postpone dealing with this problem, which arised after installing Snow Leopard - up until today.

If you have the problem it is most likely (and has always been) due to wrong paths to MySQL's header files. The fix is simple, and if you google for it people were complaining about the same problem even under OS X 10.2. Here's the solution: Build the thing manually, giving the right paths to Makefile.PL:

$ perl Makefile.PL \ --cflags="-I/usr/local/mysql/include -Os -arch x86_64 -arch i386 -fno-common" \ --libs="-L/usr/local/mysql/lib -lmysqlclient -lz -lm"

Automated snapshot backups using rsync and launchd over ssh

Monday, October 5th 2009 - 18:46
I needed to backup the contents of a file sharing directory of a server in our LAN to the shiny new mirrored 1 TB RAID inside my Mac Pro. Having a simple backup is nice, but it's even better to have snapshots, with the possibility to jump back to any date and have the exact representation of the disk as it was back then (TimeMachine also does this pretty well for Macs).

Always creating a complete backup would be a waste of disk space par excellence and in my case - backup via LAN - also pretty hard on the network. Fortunately there is rsync, and there's our friends ssh and launchd. rsync takes advantage of *NIX file system hardlinks and when called with the right arguments only backs up new files since the last backup, pointing to the older copy of a file if the file has not changed in the meantime. Mike Rubel has written up a nice article covering these aspects of rsync, check it out.

Thanks to rsync, backing up remote content to a local disk (or vice versa) is pretty easy, but there are some things to consider when automating the whole process. I've written a script that currently runs successfully on my Mac; it's called once daily at night (maybe I should say "every 24 hours") by launchd and uses ssh's shared keys feature to authenticate the machine.

» Grab the script right here.


Here's a little breakdown of the process. Since we use launchd on this one, the script is run as the root user, keep that in mind!

The script takes two arguments, first argument is the SOURCE directory and the second argument the TARGET directory. The script then creates a folder with the current date inside the TARGET directory and performs a backup of SOURCE into this directory using rsync.

At the top of the script you can set some options, then follows a check whether you really provided the two arguments, the TARGET directory is created if it does not yet exist, and the name for the new snapshot is created using the date function. This code is all pretty straightforward, no need to show it here.

Since we want to use incremental backups, we're going to tell rsync in which directory it can find the latest backup, so that rsync only needs to transfer the new files. I use ls -U to get the latest directory first - what puzzles me here is that the list I get is reversed, means that the newest directory appears at the bottom. According to the man page it should be the other way round, now I don't know whether I didn't understand the man page or whether this is a Bug in Snow Leopard. If you're going to use this script verify this behavior and use head -n1 instead of tail -n1!
REFERENCE=$(ls -U "$TARGET" | tail -n1) # why the hell does this sort the oldest files first?? if [ ! -d "$TARGET/$REFERENCE" ]; then REFERENCE='' fi

SSH Authentification

Then comes the tricky part with SSH. In order to not have to type the SSH password each time the backup runs, we use SSH public key authentification, which is always a good idea anyway. (If you don't know what this is, you might want to read a little).
But since we want to use this script from launchd, and the environment of launchd is not aware of ssh-agent, ssh won't know our SSH key and will ask for the password, nevertheless. So we need to ssh-add our key to the ssh-agent every time launchd performs our script. This works - as long as you have no passphrase for your ssh key set (which you should have!).
If you have a passphrase, we take advantage of SSH_ASKPASS: If there is no display where the script could ask for a password, it launches the program given as SSH_ASKPASS - this program should return the pass - typically this is a password input window. But we don't want a window, so we provide a program that simply returns our passkey. I have a script that simply echo-es my password, named 'catp', in root's home directory, chmodded to 0700. THIS IS A SECURITY RISK, however small, but I've found no other solution to this so far.

So we check whether SSH is needed (we assume this when an @ is present in either directory path), set DISPLAY to none and give our password returning script to SSH_ASKPASS, then setup ssh-agent and ssh-add our key:
if [ 'xx'"$SOURCE" = 'xx'$(echo "$SOURCE" | grep @) ] || [ 'xx'"$TARGET" = 'xx'$(echo "$TARGET" | grep @) ]; then export DISPLAY=none:0.0 export SSH_ASKPASS='/var/root/catp' eval $(/usr/bin/ssh-agent -s) >/dev/null /usr/bin/ssh-add >/dev/null fi

Calling rsync

Finally we can call rsync. We first check whether there actually is an older backup, if not we just create a new one. If there already is a backup, we tell rsync to first look in the directory --link-dest for the files before transferring them again.

Assume a file has not changed and is already present on the backup. rsync now sees this file, sees that it's still the same, and instead of copying it again creates a file system hardlink to this file. You now have two filenames (one in the new and one in the older directory), that point to the same file, and you can still "delete" the old file - as long as another name points to the file, the file is not deleted. And as you now see, this way you can delete any backup, the other existing backups will always be unaffected, as if the file had been copied for every backup. Really nice!

Note I use --numeric-ids since I backup to another machine than where the data is stored.
# no reference, so most likely first backup! if [ 'xx' = $REFERENCE'xx' ]; then echo $(date "$LOGDATEFORMAT")" --> Going to create first backup in $SNAP_NAME" rsync --numeric-ids -a "$SOURCE/" "$TARGET/$SNAP_NAME/"
# we have a reference, create a new snapshot else echo $(date "$LOGDATEFORMAT")" --> Going to create snapshot in $SNAP_NAME, hardlinking to $REFERENCE" rsync -a --numeric-ids --delete --link-dest="$TARGET/$REFERENCE" "$SOURCE/" "$TARGET/$SNAP_NAME/" fi

Deleting old backups

This part is actually pretty basic, one could certainly expand this code. You can set a max lifetime of the backups, which I set to 180 days. Snapshots older than 180 days will be deleted by the following code.
Note I got some errors using find ... -exec rm -r {} \;, so I used a simple for loop. :)
if [[ $PURGE_IF_OLDER > 0 ]]; then for oldsnapshot in $(find "$TARGET" -ctime +"$PURGE_IF_OLDER"d -depth 1); do # got errors using -exec rm -r {} \; rm -r "$oldsnapshot" done fi

Automating with launchd

Now it's time to tell launchd to start this backup regularly. It's easiest to use Lingon to do this. Lingon is no longer being developed, but still works for me.
Create a new Users Daemons entry, give it a name under (1) and provide what it has to do under (2). For me this is:
/etc/ /Volumes/MyRaid/Backups/save_me

Then under (3) you can set to have it run whenever you like. I run it every night at 23:45 - it would probably be even better to let it run at around 3:00 in the night, but then the date of the backup folders would always be one day off. Minor issue, though.

Here is the complete XML, to be placed inside /Library/LaunchDaemons. If you do this manually, don't forget to sudo launchctl load -w /Library/LaunchDaemons/ch.my_new_backup_deamon.plist.
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" ""> <plist version="1.0"> <dict> <key>Label</key> <string>ch.my_new_backup_deamon</string> <key>ProgramArguments</key> <array> <string>/etc/</string> <string></string> <string>/Volumes/MyRaid/Backups/save_me</string> </array> <key>StandardErrorPath</key> <string>/var/log/backup_error.log</string> <key>StandardOutPath</key> <string>/var/log/backup.log</string> <key>StartCalendarInterval</key> <dict> <key>Hour</key> <integer>23</integer> <key>Minute</key> <integer>45</integer> </dict> </dict> </plist>

Have fun!

Static SQLite Library with Unicode Support for the iPhone

Saturday, August 8th 2009 - 12:25 • 1 update Tuesday, June 29th 2010 - 00:21
For my Eponyms iPhone App I wanted to bless SQLite with the ability to handle special chars - especially German Umlaute - like they should be handled, which e.g. is treating ä like a when sorting. Currently these Umlauts appear after Z, which is not what we want.

SQLite offers the ability to compile with complete Unicode support, provided by the ICU Project, but that will bloat the library and is not too easy to cross compile to the iPhone platform. This is also what Ioannis Epaminonda thought and he took action and provides a solution including "only" the desired features of the Unicode package, resulting in a much smaller footprint.

Here's how to get this to work for your SQLite-using iPhone App, implemented as a static library:

Create your own SQLite static library

  1. Create a new Project in Xcode, use the iPhone » Library » Cocoa Touch Static Library Template. Pick a good name, the library will be called libYourName.a in the end
  2. Download the SQLite Amalgamation source - that is all of SQLite in one (well, two with the additional header) file. Add the Header and the main file to the currently empty project
  3. Download Ioannis source code files, and add the two files (.h and .c) to your project. Your project should now look like this:
  4. GroupsAndFiles.png
  5. Add SQLITE_CORE and SQLITE_ENABLE_UNICODE preprocessor definitions:
  6. Call up the project's information window, select the "Build" tab and click the gogwheel icon, then select "Add User-Defined Setting". Add the two statements under the keyword GCC_PREPROCESSOR_DEFINITIONS.
  7. By default the library template will enable Objective C code, but we don't need this. Remove the -ObjC flag from the Project's Other Linker Flags in the project's information window and remove the Foundation Framework from the frameworks:

  8. Remove_ObjC_Flag.png
  9. At the very last, the unicode c file still contains the line #include <sqlite3.h>. This will include the system-wide header file, but we want it to include the header file we've just downloaded. So we add "." to our header search paths (don't check the recursive checkbox when asked):

  10. Add_header_search_path.png
At this point you can hit the Build Button and Xcode will already generate the libX.a static library file which you could already use. But we're taking advantage of Xcode's ability to cross reference projects, this way you'll only have to build your original project and the library will be built using your current build settings; for each build you'll have the latest version of your static library for your current settings, which is quite convenient.

Add the library to your iPhone project

  1. Open your iPhone project in Xcode, the SQLite project still open
  2. Drag and drop the SQLite project into the Groups & Files Section of your iPhone project:

  3. DaD_the_project_file.png
    In the sheet that pops out, do NOT check the copy items into...; we want a reference to that project, not a copy:
  4. Now we tell Xcode to use our own SQLite library, not the general one we've used so far. Remove the SQLite library from Frameworks in case it's still there. No need to add our own there, it's already known to Xcode since we added a reference to our project. But we need to tell Xcode that it must copy this library to the application bundle, otherwise your users will not get that library and the app will of course not run. Our library appears right where we inserted the reference to the library project, you can simply drag it from there down to the Copy Bundle Resources target group. In case Xcode did not do this automatically, also make sure that it appears in the Link Binary With Libraries group.

  5. Copy_and_Link.png
  6. Xcode will use the build settings of the main project also for linked projects, so make sure you add the GCC_PREPROCESSOR_DEFINITIONS also to the settings of your iPhone project (See step 4 of Create your own SQLite static library)
  7. We need to load these Unicode extensions on App launch and unload them before App quit; I've included the corresponding functions in the function that initially opens a database connection and the function that closes it again:
    • On launch: sqlite3_unicode_load();
    • Before quit: sqlite3_unicode_free();
  8. That should be all, compile and Run!

I hope this helps, in case I missed something leave a comment. In case you want to do cross referencing more often, there's a more dedicated way in doing this by adding constants to Xcode and define a shared build directory. See Clint Harris Blog for how to do this.

Update June 29, 2010

As mentioned in the comments, using your own SQLite library on the iPad, built against iOS 4, does not work; SQLite does not initialize the database correctly. Some people have looked into this and at least Evan an me have posted to the sqlite-dev and sqlite-users mailing list; no solution so far.

However there is a workaround - for those using the unicode extension mentioned above, you can still use it:
  • Link your app against Apple-provided libsqlite3.dylib
  • Add » sqlite_unicode.h and » sqlite_unicode.c to your project (I've mirrored the sourcecode here since Ioannis Epaminonda's original blog seems to be gone)
  • Define SQLITE_CORE and SQLITE_ENABLE_UNICODE as preprocessor definitions
  • Build and go

You can then use the extension as before. If you want full FTS3 support, this is also possible, see Evan's blog post for this.

QuickLook Plugin for CSV files

Sunday, July 5th 2009 - 23:01 • 3 updates, last Wednesday, March 10th 2010 - 10:53
This one bugged me a long time, and over the weekend I decided that it's finally time to roll it: I created a simple QuickLook Plugin for CSV files.
The nice thing is - the Finder also uses this Plugin to create the Finder Icons. For speed reasons I decided to limit the display to the first 200 rows maximum, but since this thing is Open Source, you can change that whenever you like. The project website is here: There you can also find a DMG containing a pre-compiled binary ready to use after download.

One thing that I'm not so sure is the UTI (Uniform Type Identifier) for CSV files. The plugin currently registers for the types listed in the Wiki, but OS X handles CSV files somewhat strange. I think the UTI for CSV files should be public.csv, analog to public.html or public.plain-text. But (at least on my Macs) OS X uses dyn.ah62d4rv4ge80g650. Well, whatever. If you discover other UTIs, please tell me so I can include these in the plugin. Otherwise it won't work for you.

Preview_2.png Icons.png

Update Sept 8. 2009
Since Mac OS X 10.6 Snow Leopard, Apple uses public.comma-separated-values-text as UTI. Fine with me. :)

Update March 10, 2010
Released a small update, it should now also display previews for FileMaker exports, files ending in ".tsv" and set the badge item accordingly to "tab" instead of "csv".
For older articles, browse the archive.