10 Jun 2009

Getting Your Groups out of Nambu (Update)

Posted by khk

Update: If you are running the new Nambu 1.2 and are fed up with all the bugs, the SQL commands you need are slightly different. I’ll list them at the end. We now return to our regularly scheduled program…

A while ago I switched from TweetDeck to Nambu (via a few other stops in between). At fist I loved it, then I got used to it’s quirks, and now I’d like to move to something that actually works most of the time… Why is it so hard to write a good Twitter client?!?

Sorry, that rant wasn’t planned, it just happened… Let’s get back on track: As you may remember, a few days ago I posted instructions about how to extract group information from TweetDeck. Now that I’m considering ditching Nambu, I need a way to do the same with that application.

Background

After a little poking around the filesystem, I discovered that Nambu is also using the SQLite database system, even though it’s not an Adobe AIR application. The reason my be that it’s just using the database system comes with Mac OS X…

So, I dumped the database, and tried to find where the users and groups are stored in that database, and came up with a SQL command to extract that data the same way as with TweetDeck. Because Nambu is a Mac-only application, that makes these instructions Mac-only too. If you’ve already read my TweetDeck instructions, you will find a few things that I’m duplicating here for the benefit of new readers.

Extracting the Data

The process requires a little bit of post procesing (e.g. in a spread sheet application like Numbers, OpenOffice.org or Excel), but once you are done, you will have access to the group and user data.

On the Mac, the sqlite3 application actually comes with the operating system, and you can start it’s interface in a terminal window by typing the following command:

sqlite3

You get out of the application by using the .quit command (the dot is important).

The Nambu database file is stored in ~/Library/Application Support/Nambu and the file is called Nambu.db

Now start the Termainal application and change to the directory that contains the Nambu.db file. In that directory create a new text file named sql.txt with the following content (if you followed my TweetDeck instructions, you will notice that the structure is the same, just the table and field names are different):

SELECT ZTWITTERGROUP.ZNAME, ZTWITTERUSER.ZNAME, ZTWITTERUSER.ZSCREENNAME
FROM ZTWITTERUSER
JOIN Z_18USERS ON ZTWITTERUSER.Z_PK = Z_18USERS.Z_21USERS
JOIN ZTWITTERGROUP ON ZTWITTERGROUP.Z_PK = Z_18USERS.Z_18GROUP
GROUP BY ZTWITTERUSER.ZSCREENNAME
ORDER BY ZTWITTERGROUP.ZNAME;

We are almost there…

Now go back to the command tool and run the following command

cat sql.txt | sqlite3 Nambu.db

This will print all the users who are in groups – you probably want that in a file so that you can import it into Excel:

cat sql.txt | sqlite3 Nambu.db > group_data.txt

That’s it. Import it into a spread sheet program (make sure that you select “|” as field delimiter character) and do whatever you want to do with your group data.

Update: When you upgrade to Nambu 1.2, the database gets updated too, and some of the tables and fields now have new names. For Nambu 1.2, you need to use the following SQL sequence instead:

SELECT ZTWITTERGROUP.ZNAME, ZTWITTERUSER.ZNAME, ZTWITTERUSER.ZSCREENNAME
FROM ZTWITTERUSER
JOIN Z_24USERS ON ZTWITTERUSER.Z_PK = Z_24USERS.Z_26USERS
JOIN ZTWITTERGROUP ON ZTWITTERGROUP.Z_PK = Z_24USERS.Z_24GROUP
GROUP BY ZTWITTERUSER.ZSCREENNAME
ORDER BY ZTWITTERGROUP.ZNAME;

Subscribe to Comments

One Response to “Getting Your Groups out of Nambu (Update)”

  1. […] written a number of posts before that explained how to extract group information from TweetDeck and other Twitter applications, we can use these techniques to extract the group members and use the Twitter API to create new […]