Note: this post has nothing to do with the usual photography/travel stuff I normally post about.  Also, it’s rather long and technical so if you have no interest in creating Anki decks from your ChinesePod lessons just ignore this one.

So, I’ve been using ChinesePod.com for a little over a year now now to help me with my Chinese studies.  It’s a great resource and while I have had a couple of issues with their mobile apps in the past I would definitely recommend them (if you’re studying Chinese that is; if not then you’re probably not interested in this post).

However, there are a few things I don’t like about the service and the main one is the inability to batch download the audio files for each lesson.  I don’t just mean the 3 main audio files for the lesson/dialogue/review sections, but also each audio file for the individual sentences of the dialogue and expansion as well as the vocabulary.  So many times I’ve been on the tube and wanted to hear a word or phrase only to be denied due to the lack of a data connection.  In addition to that, it’s just slightly annoying to have to wait 4-5 seconds each time the app fetches the audio I want to hear.

I’m also not a big fan of their flash cards, especially compared to another app that I use every day to help with my studies: Anki.  It’s a brilliant software that uses spaced-repetition to help with your learning, but it can be a bit unwieldy at times (a new version was just released, so that may just be me getting used to it).

Anyway, what I’ve wanted for a while was a way to export my current ChinesePod lessons (including the expansion sentences and vocabulary) into Anki decks.  Ideally I’d have the English word or phrase on the front along with the Chinese characters that match the answer (I’m still in the early learning stages of the characters, so this might be considered cheating if you know more than I do).  When the card is ‘flipped’ the audio plays and the pinyin complete with tone marks is displayed, along with the option to replay the audio.

Once I managed to get the info out of my ChinesePod app, this was all pretty easy.  Here’s an example of what an exported ChinesePod lesson looks like as an Anki deck (in this case, the Vocabulary deck):

Here’s an example of what a single lesson looks like in my Anki deck

The front of the card.

And the back of the card. The audio plays automatically when the card is flipped, and tapping the play button will repeat it as many times as you like.

So if that seems like something you’d be interested in doing, then read on. I’ve shared that particular deck on AnkiWeb if you’d like to check it out before diving in.  You can find it here.

Another note: I think I’d rate the process as Intermediate in terms of technical know-how.  This might be a bit much for your standard user, but I’ll try to detail things out as much as possible.

What you’ll need:

1. An active subscription to ChinesePod. This isn’t about getting around having to pay ChinesePod for their service.  For roughly $10/month (if you pay for a 2 year premium sub) it’s a great service and my intention is not to undermine what they’re doing. If you’re studying Chinese, I can’t think of a good reason why you wouldn’t want to subscribe.  I have a premium subscription, but I think the basic one excludes some stuff that might make you unable to get all of the Anki decks listed like the vocab or expansions.
2. The latest version of the phone app. As I’m writing this today, that’s version 3.2.3 for the Android folks.  This may not work at all for iDevice folks, but I know this won’t work for version 2 of the Android app (the database is totally different). January 7th, 2014 Edit: I’ve upgraded to version 3.8.9 and this process still works.  I had to make a couple of changes to the source once again, so you might want to re-download it all if you’re working from a previous download.
3. A rooted/jailbroken phone.  I can’t speak for certainty about needing to jailbreak your iDevice, but I assume that’s required in order to access the chinesepod database on your phone/tablet.  I wasn’t able to access it on my unrooted Android tablet, but was fine with my rooted phone.  This was the most difficult step for me as I hadn’t done this before, but there’s loads of sites out there to help with this stage.
4. SQLite.  As far as I can tell, this is the database used by chinesepod and we’ll need some way getting at the information store.  You can download it free here. Drop the executable somewhere easy to find like C:\SQLite
5. L/WAMP. This just stands for Linux/Windows, Apache, MySQL and PHP.  This step could be done in many ways, but as I’m a PHP developer these are the tools I’m familiar with.  “When the only tool you have is a hammer, every problem is a nail.”  You can download a free, one click installer with WampServer.  I haven’t used it before, but it seems like it will do the job (I typically use this one, but I can’t remember if MySQL is bundled with it).
6. PHPMyAdmin. This is just a tool to help administer our database.  You’ll want to drop it into wherever your webroot directory is (a quick search tells me that WampServer uses C:\wamp\www). There are some configuration changes you’ll need to make (user/password etc), but they’re pretty straight forward.

Once you have all of that sorted, simply follow these steps:

Copy the chinesepod database file to your computer*

On my phone, this file was located in /data/data/com.chinesepod/databases/ and the file we’re looking for is called chinesepod4.  I had to copy this file to the sdcard on my phone and then plug the phone into the PC to copy it across.  For ease of the next step, copy the file to C:\SQLite or wherever you placed your sqlite executable in step 2.

Extract the database*

I’m going to assume your sqlite.exe file is in C:\SQLite and your chinesepod4 file is in the same folder.  Open a command prompt, browse to that directory and do the following:

You’ll end up with a text file called chinesepodExport.sql which we’ll need to manually edit now.  I plan to create a little tool to parse this and import it directly in the future.  I just went ahead and created a parsing script so we don’t need to mess with manually editing the export file.

Create the MySQL tables.

SQLite and MySQL don’t share the exact same syntax so the export we made earlier won’t just import as is (we’ll need to edit it in the next step.  I’ll include a download link at the end that will have the SQL script to create the tables, but here is the code if you’d prefer to copy/paste. You’ll need to do this through PHPMyAdmin:

As you can see, I created a database called cpod and just imported everything into it.  To be honest we only really need the api_cache table for our purposes, but I didn’t feel like filtering out the inserts of the other two so I’ve just left them in.  Also, I added in a unique field for the api_cache table which should prevent any duplicates if you’re trying to import some of the same lessons.

Copy the PHP files to your webroot

There are 3 php files that are included in the download below.  They’re very simple and probably shouldn’t be used as a style guide, but they get the job done.  You should see index.php, import.php and config.php, all three should go into the same directory.

Configure your settings

There are a couple of things you’ll need to change in the config.php file to make sure the scripts will run properly on your machine.  Your database settings should be pretty close to what’s there already, though you may need to change the user/pass if you set up a default user.  So that just leaves your download directories to set up; below is more or less what mine looks like.

The $ankiDir is the existing directory from your Anki installation (I’m assuming you have it installed).  Just change this path so that it points to the Anki in your installation and you’re golden.

Browse to the PHP scripts

Once everything is configured you should be able to run the PHP files.  Open your web browser of choice and type http://localhost into the address bar.  ‘localhost’ basically tells your browser you want to load a page from your local webserver, so it should load our index.php file assuming you’ve placed it in the web root.  You should see something like this:

The default screen with our file import option (shown in FireFox)

If you’re seeing something similar, then well done!  That was the tricky part, the rest is just importing your sql and then creating the Anki decks which is relatively straightforward in comparison.

Import the data from your App*

This step will move the data we extracted earlier from our app into the local databases.  Click browse to find the file, which should be here if you’re using the same details as my example: C:\SQLite\chinesepodExport.sql and then just click Import.

This should be a fairly quick process and you should see a success/fail message shortly.  Hopefully you’ll end up with a screen similar to this:

Your screen should look something like this after you’ve imported your lessons from the app.

Create your Anki Decks*

I’ve left a few options to download the full audio files and the pdf if you’re into that kind of thing, but I don’t really use these outside of the app (and you can pull them down via RSS anyway, which is much easier).  That leaves us with the anki link, which is what will generate our csv files that we can import into Anki. (note: it technically creates a semicolon separated file, but that’s too much of a mouthful to keep saying).

So, find the deck you want to import into Anki and click the corresponding anki link.  This step may take a little while as the scripts are pulling down every audio file you’ll need at the same time as creating your csv file.  Depending on your connection, it may take a minute or two to complete the process so just be patient.  If it times out you can just run it again and it will pick up where it left off.  Once it’s complete you should see this on your screen and the following image should be similar to what you see in your your csvImport folder:

An Anki deck that has successfully downloaded all of the audio files and created the csv files.

My local directory structure with the 3 Anki decks waiting to be imported.

You can import these directly into Anki as is, but I like to keep my ChinesePod decks separate from my other learning so these are the steps I take.

1. Create a deck called ChinesePod and open that deck.
2. Click File > Import
3. Select one of the csv files to import. I’ll choose Dialogue as an example
4. In the Anki Import window, click the below button to change the destination deck and then click Add in the window that opens up

Change our destination deck.

5. Enter the following as your new deck name. This will create a ‘Sending a Large File’ deck under our ChinesePod deck and then a Dialogue deck underneath that:

Anki uses :: as a deck separator.

6. Make sure that ‘Allow HTML in fields’ is checked.  You shouldn’t have to change the front/back/tags fields, so just click Import.
7. Do the same for the Vocabulary and Expansion, and you’re done!

So, now what?  Well, you’re going to have to go through a few these steps every time you want to add new lessons to your collection.  I’ve marked the steps you’ll need to repeat each time you want to add new lessons with a handy *, but basically the process will be to copy the database out of your app, extract it using SQLite and finally importing that into the little system we’ve set up here.  It may seem tedious but it’s still faster than trying to create these decks manually with all of the audio in place.

I should also quickly mention the delete option.  Removing lessons from your App will have no effect on this list, so you’ll need to manually delete them here when you’re finished with them.

I realize there are a few gaping holes in these instructions, but I’m happy to answer any questions you might have in the comments below.  I hope this helps out one or two folks out there until the folks at ChinesePod set things up so we can export directly from the website or app.

The files for this project are here: ankiExport.
This zip file should contain index.php, import.php, config.php and database.sql.

07/01/2014 Update: I’ve added a link at the top of the index page to Create Listening Decks.  This will spin through all of your lessons and pull out all of the sentences from the expansion section of the lessons and creates Anki decks based on the lesson level.  In my case I’ve ended up with 2 decks, one for Elementary and one for Intermediate.  The front of the card is just the audio and the back includes pinyin, Chinese & English.  If you have a lot of lessons you may need to run it a few times as it’ll likely timeout trying to download all of the audio files.

I was apparently a well behaved little boy this year.  Well enough to warrant and excellent gift of Lego from my visiting mom which just so happened to provide me with the rest of the cast to complete the fellowship.

I’ve been putting together a few bits and bobs that I’d need to try to recreate this movie poster with Lego minifigs and finally had everything I needed.  So, this is how it turned out:

I’m quite pleased with the final result.  I think the mountains in the background are the real weak point overall and I know what I’d do differently if I were to shoot as similar image again.  Just about everything on the minifigs is Lego with a few exceptions.  Gimli’s axe is blu-tac’d to his helmet, Bormir’s shield is actually a Euro 5¢ coin and Aragorn has a bit of floral foam as a blowing cloak plus a blu-tac backpack and a needle for whatever that skinny thing is supposed to be sticking out there.  An arrow maybe?  Who carries one arrow?  Especially with Legolas right there.  You’re hardly going to add much to his ranged offensive power with a single arrow.  Now that I mention it, he’s not actually carrying a bow.  Maybe he’s at the back of the line because he’s just another pack mule?  It’s possible I may have been staring at this setup for too long…

Here’s a quick shot of the setup.  I had my lovely assistant/wife Danni hold some lumps of stuffing (aka: clouds) just above the mountains for the upper cloud effect in the image.  Apart from that, it was pretty much as you see it here in the setup.  Well, and some minor colour tweaks in Photoshop.

This has sort of become a tradition of sorts around our place.  Come to visit and we’re very likely going to Richmond Park.  I think we both just really love it there but need some sort of excuse to make the trip out.  Especially now as it’s a couple of bus rides away.

We had much more success finding the deer than we did at our last family outing.  The winter weather had killed off most of the shoulder-height ferns so there wasn’t anywhere for the deer to hide.  Well, apart from being somewhere in the vastness that is Richmond Park.  Fortunately we didn’t have to wait too long to find them, we just needed to be willing to get our shoes a bit muddy.  It has definitely rained this year!

I’ve been enough times now that I can act as a bit of a mini tour-guide, provided I can keep to the general middle area of the park.  We did a wide loop around the ponds and through as many of the treed in areas as we could.  I enjoy the wide open spaces as much as the next prairie raised lad, but the bitter winds had us seeking as much shelter as possible in order to prolong our hike.

All in all a very successful deer spotting sojourn.  I think mom was quite happy with the wildlife, which is saying something for someone who grew up in Vancouver with the Rocky Mountains on her doorstep.  There are a few more images in the gallery, but they’re mostly family images.  If you’re after more deer photos, I was at the park with gusto earlier in the year to get my fill of deer shots.

This post is mainly here as a showcase for the the amazing Christmas Goose that we cooked this year.  I don’t want to waste any time, so here it is!

Not to toot my own horn, but does that not look like it’s right out of a magazine?  We were all incredibly pleased with how it turned out, especially as most of us had never even eaten a goose let alone cooked one.  How did it taste?  Well, the flavour was really lovely with all of the citrus fruit and spices we rammed into it while cooking, but if I’m honest it was a bit dry.  And there isn’t much meat on one of these guys compared to a turkey.  The estimate was for 5-6 people with this goose, but only one of us had any leftovers the next day before it was all gone.

The sides didn’t turn out to terribly either.  I got my sweet potatoes smothered in marshmallows and I added enough bacon and butter to the brussels sprouts to make them palatable for everyone.  The ‘roasted potatoes’ where more like ‘soft boiled in goose fat’.  Not was I was aiming for, but if I were to write a cook book my secret for everything would be to soak it in goose fat. Instant deliciousness!

I’m making myself hungry again, so I’ll end on this photo of one of the cute new ornaments mom hauled over from the mother land.  There are a few more images of our Christmas day in the gallery!

For the past couple of years we’ve been doing Christmas portraits and this year was no different.  We had mom visiting so it was another special year, though the photos were a bit trickier to do in our tiny flat.  Still, we’re happy with the results and very happy to have been able to share the holidays with my mom (who, by the way, is a terrible cheater when playing any type of board/card/dice game.  That’s right mom, it’s on the Internet so it must be true!).

I realize now that I probably shouldn’t have posted the link to our 2010 Christmas photos.  The keen viewers out there might have realized that I’m wearing the same sweater/jumper.  I seem to only have two that are suitable and just rotate them out each year.  Look out for the return of the purple one in 2013!

Here is my lovely (London based) family for the holidays.

I didn’t want to bore you with too many photos of the same three people over and over again, so here’s some of the snacks we had that night.  I don’t know about you, but it makes me painfully hungry just looking at that photo.  I don’t know why I save mini-burgers for special occasions.  They really should be an everyday affair.

There’s a few more family style images in the gallery.