SQLite Manager for OS X

I’m currently working on some web projects using Django, which is a pretty nice Python framework for web development, and I run all my tests with a SQLite database. During development, the models are very dynamic and its fields are always changing, so I need the database tables to reflect those changes, but the utilities bundled with Django only create tables, but don’t alter them when models change.

So, the way to overcome this workaround is quite simple: just drop the tables and run Django’s table creation utility again. However, that’s a bit of a hassle to do through SQL queries when you’ve got plenty of tables to drop.

For managing SQLite databases, I’ve always used sqliteman, which is multiplatform and free. There are binaries for Windows and Linux, and I compiled it on OS X and even packaged the binary into an OS X .app, so that I could just run it like any other OS X app. But sqliteman has still got an issue which makes it uncomfortable for droping a bunch of tables: in the table tree view, you cannot select more than one table to drop them at once, and, besides, there is no shortcut to drop tables (you must ctrl-click and delete tables from the context menu!). And I definitely do not want to drop tables one-by-one and by-hand, nor do I want to have to write a drop query for each table!

For managing MySQL databases from OS X, I’ve always used CocoaMySQL, which evolved to Sequel Pro. This has a pretty, native interface for OS X and… It allows you to select multiple tables and delete them like you’ve always wanted: with shift-selection! It doesn’t provide any shortcuts for deleting though, but I can live with that, and that gives you more safety, too (it prevents you from deleting by mistake).

So, the quest for the Holy Manager arises: I want a GUI manager for SQLite databases that runs on OS X, allows batch dropping of tables, and —of course— is free! A simple search for “sqlite manager interface mac” yields some results (though not all of them are free):

MesaSQLite looks promising, but it’s paid software and I didn’t get to know the limitations of the trial. Lita looks very simple, so at first sight I’m not sure if it will allow batch dropping, and also the look & feel is not suited for the OS X posh kids. Base is also quite promising and very good-looking, and the trial limits sessions to 15 minutes and custom SQL queries will only return 5 rows. Well, I don’t think it’ll take me 15 minutes to drop some tables, and dropping doesn’t yield any rows… But Base does not allow to drop many tables at once 😦 However, MesaSQLite does! Anyway, I’ll keep both, since Base might be useful some day, too.

PS: Yeah, I know Safari is a memory monster. I might switch some day 🙂

Reading URLs from OS X clipboard with PyObjC

I’m definitely lazy. I don’t like making efforts bigger than needed, under any circumstances. And, when it comes to downloading a (quite big) bunch of files, I don’t like doing it manually. That’s why I’m currently using jDownloader on my iMac. However, it’s still got an issue: when I copy URLs from Safari, it instantly recognizes that URLs have been copied to the clipboard and adds them for download; but, when I copy HTML links from Safari whose text is just text (not an URL), this does not work anymore, and I then have to copy the links one by one, which really bothers me, because I’m lazy (and because I have to waste a lot of time for that).

So, the problem is that everything works fine when I copy something like


or something like


but it doesn’t work anymore when the link is like

this is a link to an example domain

And, since I’m lazy, I decided I had to write a program to copy a bunch of links at once, without all that hassle. The first idea that came to my mind was to write a Python script that used mechanize to copy to the clipboard all URLs in a web page, but that didn’t seem like the prettiest solution at that moment.

But then a second approach occurred to me: I could just copy the web page’s text to the clipboard and let a program read the richt text, HTML or whatever from the clipboard and find the URLs. Besides, that seemed to be an interesting program to write. I started considering RubyCocoa for that, but a deep knowledge of sorcery is needed to be able to use Xcode, and I’m not such a good sorcerer. After that, my tests with Qt weren’t successful, so I decided to go search on the internet for some recipes to access the OS X clipboard with Python.

After digging for a while, I found and article on Python and the Mac Clipboard which pointed me in the right direction: use PyObjC to access the NSPasteboard class. After taking a look at the documentation and testing a bit, supported by Python’s built-in dir() and help() functions, I found that

  1. all of the items that are currently in the pasteboard are returned as an NSArray by NSPasteboard.pasteboardItems method,
  2. the list of all types to which each pasteboard item can be converted is returned as an NSArray by the NSPasteboardItem.types method, and
  3. all text copied from Safari can be accessed both as com.apple.webarchive with the NSPasteboardItem.dataForType_ method, and as public.rtf with the NSPasteboardItem.stringForType_ method.
Given that we’re interested in getting the URLs from the richt text representation, the best approach was to use regular expressions to extract all hyperlinks from the RTF document representation of the web page clip copied, resulting in the following Python code:
from AppKit import NSPasteboard

import re

pb = NSPasteboard.generalPasteboard()
pattern = re.compile(r'HYPERLINK "(.+)"')
for item in pb.pasteboardItems():
	if 'public.rtf' in item.types():
		matches = pattern.findall(item.stringForType_('public.rtf'))
		for url in matches:
			print url

You can then copy the output URLs manually to the clipboard, which would make jDownloader to recognize them, or you could also improve the script to add copying capabilities, so that the script would copy the output to the clipboard again.