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

http://example.com/

or something like

http://example.com/

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.

Generating timetables with Python

I hope I’ll be going next semester to Germany as an Erasmus student, and I’m now struggling with the Learning Agreement, courses, and timetables. In particular, there are a lot of interesting courses being offered by my guest university, but there are many factors to take into account when deciding what course you’re going to take, and one of them is timetable compatibility.

So, after a lot of struggle with the university’s website and trying to find some timetables, I’ve thought of making a timetable where I’ll put all the courses I’m interested in, so that I can see which ones are compatible with each other, i.e., which ones don’t overlap.

But the timetable has time slots of 15 minutes, from 8:00 to 18:15, and, of course, I’m naturally lazy. I don’t want to create a spreadsheet with as many rows as the 15-minutes-slots there are in the timetable!

And here’s when Python comes to the rescue!

I’ve coded a Python class that represents time, and which has methods for addition and logical comparison:

class Time:
    def __init__(self, hour, minutes):
        self.hour = hour
        self.minutes = minutes
    
    def normalize(self):
        hour = self.hour
        minutes = self.minutes
        
        quotient = minutes / 60
        if quotient > 0:
            hour += quotient
            minutes = minutes % 60
        
        self.hour = hour
        self.minutes = minutes
        
        return self
    
    def __add__(self, t):
        """add two times (sum)"""
        hour = self.hour + t.hour
        minutes = self.minutes + t.minutes
        res = Time(hour, minutes)
        res.normalize()
        return res
    
    def __mul__(self, k):
        """multiply a time and an integer constant k (product)"""
        hour = self.hour * k
        minutes = self.minutes * k
        res = Time(hour, minutes)
        res.normalize()
        return res
    
    def __lt__(self, t):
        """less than"""
        if self.hour < t.hour or (self.hour == t.hour and self.minutes < t.minutes):
            return True
        else:
            return False
    
    def __eq__(self, t):
        """equal"""
        if self.hour == t.hour and self.minutes == t.minutes:
            return True
        else:
            return False
    
    def __le__(self, t):
        """less or equal"""
        return self < t or self == t
    
    def __gt__(self, t):
        """greater than"""
        return not self <= t
    
    def __ge__(self, t):
        """greater or equal"""
        return self > t or self == t
    
    def __ne__(self, t):
        """not equal"""
        return not self == t
    
    def __str__(self):
        hour = fill(str(self.hour), 2, '0')
        minutes = fill(str(self.minutes), 2, '0')
        return '%s:%s' % (hour, minutes)

I’ve implemented all the basic logical operators for comparison, because they’re very easy to implement, but we won’t be needing all of them.

In addition, I’ve also coded the __str__ method, which allows you to convert Time objects into Python strings by just using the str() function. This method needs a function called fill, which is intended to pad or fill a string with some character, so that the string reaches a given maximum size. That is, for example, when you have a string '8' that you want to be 2 characters long; you would fill it with zeroes until you reach that length: '08'. The code for this function is pretty simple:

def fill(s, size, c=' ', position='before'):
    """s: string; c: char"""
    if position == 'before':
        s = c * (size - len(s)) + s
    elif position == 'after':
        s += c * (size - len(s))
    return s

And, finally, this is the function to generate the time slots from a start time up to a given end time by some time increment:

def generate_timetable(start_time, interval=Time(0, 15), times=5, end_time=None):
    timetable = []
    
    if end_time is None:
        end_time = start_time + interval*times
    
    time = start_time
    while time < end_time:
        timetable.append(tuple([time, time + interval]))
        time += interval
    
    return timetable

In my case, I’ll just execute the following in the Python interpreter:

start_time = Time(8, 0)
end_time = Time(18, 15)
for start, end in generate_timetable(start_time, end_time=end_time):
    print '%s-%s' % (start, end)

This prints the time slots that should go into each row of the spreadsheet, so I’ll just have to copy that, and I’ll be able to start filling my timetable!