Converting a SQLite3 database to MySQL

I have need to migrate some sqlite3 databases to mysql for a couple django-cms projects at work. Typically in the past
I’ve used fixtures in Django to get this done, but I usually have to align the planets and concoct the elixir of
life to get the fixtures to migrate properly. It usually has to do with foreign key errors something. This is something
that should just be easy, but in my experience with Django, it never is.

Theres’s a couple posts on stackoverflow with various scripts to convert content from sqlite to mysql, but none of
them lined up the planets just right.

Then I happened on this page where there’s a python script by this guy that is not based on the others. And it just worked.
The script looks like:

convert.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
#! /usr/bin/env python
import sys
def main():
print "SET sql_mode='NO_BACKSLASH_ESCAPES';"
for line in sys.stdin:
processLine(line)
def processLine(line):
if (
line.startswith("PRAGMA") or
line.startswith("BEGIN TRANSACTION;") or
line.startswith("COMMIT;") or
line.startswith("DELETE FROM sqlite_sequence;") or
line.startswith("INSERT INTO \"sqlite_sequence\"")
):
return
line = line.replace("AUTOINCREMENT", "AUTO_INCREMENT")
line = line.replace("DEFAULT 't'", "DEFAULT '1'")
line = line.replace("DEFAULT 'f'", "DEFAULT '0'")
line = line.replace(",'t'", ",'1'")
line = line.replace(",'f'", ",'0'")
in_string = False
newLine = ''
for c in line:
if not in_string:
if c == "'":
in_string = True
elif c == '"':
newLine = newLine + '`'
continue
elif c == "'":
in_string = False
newLine = newLine + c
print newLine
if __name__ == "__main__":
main()

Usage

1
$ sqlite3 mydb.sqlite .dump | python convert.py > out.sql

Thank you Behrang Noroozinia from internet land, you solved a long-standing problem of mine.

Networked local vagrant boxes for automation testing

This post is a bit of a ‘note to self’. I am tinkering with Vagrant boxes today trying to flesh out some ansible
and I need to get the boxes to talk to each other locally. I know about the vagrant multi-machine setup, but I was
already partly committed to having 2 individual boxes set up before I discovered it.

So, the trick is, set the network configuration in your Vagrantfile to “private_network”:

box-a
1
config.vm.network "private_network", ip: "192.168.2.4"
box-b
1
config.vm.network "private_network", ip: "192.168.2.5"

With the IPs set differently it seems to work, and the host is accessible as well. Note that my host subnet is 192.168.1.x.
Probably not the right way, but it works for now.

af

Revitalizing the blog with Hexo, Concrete, and PM2

Just a super-geeky post. I was using node-wheat for blogging for a while because it was super-easy to set up, and would just operate off a git repo. Pretty cool, but I never got the time to add some of the features I wanted, and it doesn’t appear to be maintained any more, despite it’s coolness.

Anyway, I just converted the blog to hexo which is very similar (uses markdown as well) but has some super-sweet features, namely gists, jsfiddle integration, and some other fun stuff. On top, it generates all the static files so the site is super-fast.

The setup

  • Github: basically my editor, accessible anywhere
  • DigitalOcean: $5 hosting, full control
  • Hexo: blog engine, some really excellent features (for a programmer)
  • Concrete: super simple ci server, hovers the repo and gets notifications from Github, regenerates static files with hexo
  • node-static: actually serves the site
  • pm2: kickass process monitor for node, think supervisor if you’re from python-land but with some neat features.

That’s it. Super-simple setup, easy to maintain, updates automatically, and no plugins to get hacked and create spam :) Hopefully with some new shiny I will make more time to blog about current challenges.