Levenshtein distance

The Levenshtein distance between two words is the minimum number of single-character edits (insertion, deletion, substitution) required to change one word into the other. – Wikipedia

To implement in MySQL, use the following function – if using phpmyadmin you will need to set a different delimiter such as ‘$’ instead of ‘;’ to create this function.

CREATE FUNCTION levenshtein( s1 VARCHAR(255), s2 VARCHAR(255) ) 
    DECLARE s1len, s2len, i, j, c, ctemp, cost INT; 
    DECLARE s1char CHAR; 
    -- max strlen=255 
    DECLARE cv0, cv1 VARBINARY(256); 
    SET s1len = CHARLENGTH(s1), s2len = CHARLENGTH(s2), cv1 = 0x00, j = 1, i = 1, c = 0; 
    IF s1 = s2 THEN 
      RETURN 0; 
    ELSEIF s1len = 0 THEN 
      RETURN s2len; 
    ELSEIF s2len = 0 THEN 
      RETURN s1len; 
      WHILE j <= s2len DO 
        SET cv1 = CONCAT(cv1, UNHEX(HEX(j))), j = j + 1; 
      END WHILE; 
      WHILE i <= s1len DO 
        SET s1char = SUBSTRING(s1, i, 1), c = i, cv0 = UNHEX(HEX(i)), j = 1; 
        WHILE j <= s2len DO 
          SET c = c + 1; 
          IF s1char = SUBSTRING(s2, j, 1) THEN
SET cost = 0; ELSE SET cost = 1; END IF; SET c
temp = CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10) + cost; IF c > ctemp THEN SET c = ctemp; END IF; SET ctemp = CONV(HEX(SUBSTRING(cv1, j+1, 1)), 16, 10) + 1; IF c > ctemp THEN
SET c = c_temp;
END IF; SET cv0 = CONCAT(cv0, UNHEX(HEX(c))), j = j + 1; END WHILE; SET cv1 = cv0, i = i + 1; END WHILE; END IF; RETURN c; END;

Artful Software

The following is example usage – it returns the name column ordered by the Levenshtein distance – the lowest number is the closest match.

SELECT name, LEVENSHTEIN(name, 'string') AS distance 
        FROM table ORDER BY distance ASC

Fifa Trader

Fifa Trader connects to Fifa 13′s unpublished API to allow automated bidding and selling of Ultimate Team cards. Connection to FIFA servers possible from work done by numerous others on GitGub, my GitHub fork contains a current working app for ps3 accounts: https://github.com/samdoidge/FIFA13-Ultimate-Team-Search.

Search Screen
Search screen once logged in. Twitter Bootstrap is used for the UI, third party graphics used to present data in a nice way.

Add view
Adding to a buy list will cause a CRON script to be ran several times a minute searching for target cards. Once a bid has been place it will keep checking bidding if that card is still the lowest priced available within a few minutes left of the auction.

Buy Screen
Lists current and recent transactions.

The site can be found at: FifaTrader.com


I created a site to test mongoDB, with Save a Tweet – storing my data in one ‘blob’. I like the simplicity, and not having to worry about relationships or changing the scheme down the line. I won’t be using this in place of MySQL on most projects but the use case is large storage of data than can be clumped together logically, and for that it is great.

Negatives? On my Ubuntu VPS, the mongod process stops. Has happened twice over a few months and I’m not sure why, probably down to my VPS restarting but it’s always a headache to fix. For my reference, and anyone else:

mongod --fork --dbpath /var/lib/mongodb --logpath /var/log/mongodb.log

to have mongo running as a process again, specifying my dbpath was required. A mongod –repair may have been needed in here as well.

SVN tips

SVN is still around, some things I have found useful

Add all files to SVN within current directory (recursively)

svn add . --force

Remove files from an add, within a folder (before commit)

 svn revert --recursive folder_name


What is Markdown?

Markdown is a text-to-HTML conversion tool for web writers. Markdown allows you to write using an easy-to-read, easy-to-write plain text format, then convert it to structurally valid XHTML (or HTML). Daring Fireball

Why is it good?

Because it’s simple. When I do get around to writing a post, I want it to be as painless as possible, I don’t want to have to think about styling, layout, etc. I’ve done away with using WordPress admin for posting, now using wp-svbtle – a markdown editor, cleaner interface and a level simpler. The wp stands for WordPress – it sits on top of the installation.

Was this post a waste of time?

Probably (:

Git tips

Conflicts – if you just want to choose one version of the file or the other, you can use:

git checkout --ours filename.c


git checkout --theirs filename.c

Then mark as resolved and commit through:

git add filename.c
git commit -m "using theirs"

Copying a specific commit from one branch to another, goto the the branch you want to bring the commit into, and reference the commit hash:

git cherry-pick e87568fa

And you are done :)


cp -r /source-folder /destination-folder

will not preserve permissions – you want to preverse permissions if backing up folder on a live server.

cp -a /source-folder /destination-folder

does the backup you want :)

using wildcard in a transfer ignores hidden files.

rsync -avz * /dest/folder

will ignore your .htaccess and any other hidden files, just copy the directory or use . within the directory, not * to transfer al.

rsync -avz . /dest/folder

Database backups to Dropbox

I’m automating my database backups and storing them with Dropbox. You need Dropbox running on your server for this – it’s a great guide, but note one key point : when ‘This client is not linked to any account…’ appears – leave the service open, do not press Ctrl + C, enter the URL in the browser – when it says ‘welcome’ – then you can close.

Here I will show how to automate database backups and add them to Dropbox:

First we need a shell script to for our cron job to run, using your favourite terminal editor (I use vi):

vi script.sh

db-backup.sh is probably a better name. To backup a database useful commands are provided – mysql has mysqldump. To take a backup of the whole database enter the following in your .sh file:

mysqldump db_name -u username -ppassword > /path/to/dropbox/folder/

This command dumps the whole database, you can specify specific tables if wanted – look into the mysqldump command. I put my database dumps into a Database folder within my Dropbox folder on the server.

Once we have our shell script – run it via

sh script.sh 

to check it works, if so, let’s automate the running of this with a cron:

crontab -e

then add the following:

0 0 * * * /bin/sh /path/to/script.sh

This will have the script being run every day at midnight.

Save a Tweet

I’ve created Save a Tweet as a quick hack to experiment with mongoDB. With so many people saying something controversial, then deleting them, and a news article vaguely stating what was said, this site provides a way to link to what was tweet.

Using Git with Dropbox

note: I’ve set this up on Mac OS X.

I’ve cancelled my beanstalkapp subscription, gone for a paid Dropbox one, and am moving my repos to Dropbox.

cd ~/Dropbox
mkdir -p Sites/your-repo-name/your-repo-name.git
cd your-repo-name
git --bare init

This creates your Git repository in Dropbox. (change Sites + myrepo to your preference). Next I go into my local folder and add this to the Dropbox repo:

cd ~/Sites/projectfolder
git init .
git add .
git commit -m "Initial commit"
git remote add dropbox ~/Dropbox/Sites/your-repo-name/
git push dropbox master

You should now have a local copy of your remote repo already configured with your dropbox remote. You can start making changes to your project and when ready, push them back to the remote:

git push dropbox master  

And finally, when you want to sync the remote repository with your local copy, you can:

git pull dropbox master  

To take a clone a copy of your project to your current directory:

git clone -o dropbox ~/Dropbox/Sites/your-repo-name/  

The one downside to this is not being able to clone-to or pull from your repo on a remote server. For this I simply scp from my local to remote server for deployment.

scp -rp ~/Sites/projectfolder username@serverip:/project/path

Update: The following guide allows you to have Dropbox running on a remote linux server: Dropbox on remote server. I am only syncing my Sites folder on the server and it works swimmingly. Pro tip: When linking your server – keep the service running on until it says successful – stopping it won’t link the server. One issue is syncing time between your server – it can be few minutes to sync – not due to size but just time between checks

Precaution: A possible issue with using Git with Dropbox is keeping your data in sync – ensure your Dropbox repository’s are in sync (green tick) before pushing or pulling to the them.

This guide is a crude amalgamation of two nice and simple guides I googled, FreshMob – Using Dropbox as a Git repository, Bradley Wright – Using Dropbox as a Git repository but it has been working for me.