Spiked Software Coding Articles

I quite often have the need to compare the structure of 2 databases between servers, or sometimes on the same server. Whilst RedGate provide a very good solution, i prefer not to pay for software unless i really have to. I figure, i’m a software developer, i should be able to make one… or modify an existing open source one so suite my needs.

Now, there is already a very good solution, called Open DBDiff. Hosted on CodePlex, http://www.codeplex.com/OpenDBiff/. This does almost everything i need it to do and it’s open source, so i figure i will build upon what is already there. As there is no point in re-inventing the wheel. I have a few features that i have added (re-sizable window panes, and the biggest which is a visual diff tool.)

So i have created a project on google hosting, as this is an open project and i intend on keeping my code up to date with and further developments to Open DBDiff, please check here or on the google code project page here. I’ll be posting from time to time, but i intend to be updating quite regularly, as there are a few features on my list. Feel free to create issues and add suggestions of features.

If you send alot of text messages, but usually you’re in front of a PC, you could use things like MyMobiler, which is a great tool for essentially remote viewing your mobile over USB/Wifi. However, i can’t get to my phone using USB/WiFi at work, so i needed another option, as i can type hella faster on a keyboard than i can on my phone. And i have an X1, which has a keyboard!

So i wrote 2me2u, which comes in 2 parts. You have the application which runs on the phone, and you have a website, which can be anywhere in the world.

Firstly, the website. Requirements are…

1. PHP5 Enabled Web Server (Apache, IIS, LightHTTPd, etc)
2. PHP5-sqlite (Needs to be sqlite2, will be upgraded in the future)
3. Web Server needs to be exposed externally.

So, not many. If you can meet all those criteria, then we are sorted. We can get the website deployed, check it’s working, then talk about the application.

Download the website here, 2me2u website. Extract it to any folder you want inside your website, for this tutorial, i’ll be using http://www.spikedsoftware.co.uk/2me2u/. I suggest sticking to that folder, until it’s all working, then move it around all you like.

If you navigate to http://<yourdomain>/2me2u/install.php, the installer will start. Please read what it says, as it will either write to your config.php file, or it’ll ask you to do it manually because it doesn’t have permission to write to that file. Once it’s completed, refresh the page and it should let you know it’s already installed. So we’re ready to rock! Get a test SMS ready, by going to http://<yourdomain>/2me2u/ and filling in the form, with your own mobile number and a message to yourself. Click send, and assuming you have no errors, there will be a message in the queue. Before we move on, open the config.php where you have put the website, and copy the value from the $Key variable. We’ll need this later.

Congratulations, you have the website setup. Let’s configure and deploy the application to your Windows Mobile phone. Please ensure you have .NET 3.5 CF installed on your device, as it’s required.

Download the application, 2me2u. I extract this to the root of my memory card, so i can find it easily. i.e. /Storage Card/2me2u/. Once extracted, we have a little bit of configuration to do. Go into the folder where you just extracted the 2me2u zip file, and open the file 2me2u.exe.config in your favourite text editor.

We have 2 fields to set, we have a ‘URL’ and a ‘Key’.

The URL is easy, it needs to know exactly where you deployed the website. This would be… http://<yourdomain>/2me2u/ (don’t forget the trailing slash!)

The Key field, is a little trickier, because we need to know what Key the website has generated, so we can talk to it. Remember that Key field i told you to get out of the config.php file earlier? Now is the time to use it, please put that in the value of this field.

You will notice there are two other fields, these can be left alone or edited. If you want to receive delivery reports when a text message has been sent, change the value from false to true. The other field, is editable from inside the application.

Now, on your device, run the application. Navigate to /Storage Card/2me2u/ and run the 2me2u.exe. You should be presented with a simple form, that has a slider, an exit button and a status label. Slide the slider around, so a value at which you prefer. REMEMBER: This will use your mobiles Internet to check for new messages to send, make sure you have a data plan. Once you have set the value, just wait. Where it says ‘idle’ will change every X seconds that you have specified and it will check for new messages. If there are any to send, it’ll send them in the order you created them.

And that’s it. Simple and effective. Very early release, more features coming soon. Any questions/comments please either reply here, or preferably on xda-developers.com thread.

Ever used VirtualBox? I depend on it. I run an all linux desktop, but i require a virtual machine hosted by VirtualBox for Visual Studio (ASP.NET Developer by trade). However, for the last few months, my VirtualBox has had all my USB Devices greyed out, which is a pain in the arse.

You can normally solve this simply by executing VirtualBox as the root user… i.e.

sudo VirtualBox

And voila, all your USB Devices will appear and be selectable in VirtualBox. However it’s a bit of a pain doing this every time, and why should I? It’s my PC, they are my devices. I’ll decide when i will and wont use them!

The problem will most likely be, that your user account won’t be in the vboxusers group since you installed the VirtualBox application. Which again, is a pain. Small problem… small fix! Add your user to the group.

sudo usermod -a -G vboxusers

Reboot (logout will probably suffice…) and voila, your USB devices will no longer be greyed out in VirtualBox.

Any of you who saw my previous article on Backup MySQL 5 with one simple line in Ubuntu, you will probably be thinking it was a bit lacking, and while maybe helpful, if i wanted to restore one database, it was a pain to go trauling through the entire script file just to find the database i am after. So, i have written a script that will run through any MySQL database and backup any databases into seperate directories, all time stamped and compressed to perfection.

It’s very simple to use, however you will need to customise it to your needs, but not to worry, i will take you through that.

First up, we need to download the MySQL Backup Script so that we can begin editing it, and have some backups of our MySQL Database.

$ cd ~/
$ wget http://www.spikedsoftware.co.uk/blog/wp-content/uploads/2009/08/mysqlbackup
$ chmod u+x mysqlbackup

So now we have our script, we have chmodded it to allow us to execute the script so we can test it. First up, we need to edit it, so that we can set our preferences for our database. I use gedit, but you can use anything you want.

$ gedit mysqlbackup

Now, at the top of the mysql database script, we have a bunch of variable declarations, this should be all you need to edit. Lets take a look at them, they should all be very self explanatory.

USER='root'; # The username
PASS='password'; # The password
PORT=3306; # The port
HOST='localhost'; # The host

Adjust these to allow the mysql backup script to connect to your database correctly. You can test your details by using this command…

$ mysql -uUSER -pPASS -hHOST -PPort -e "show databases";

If this works, you should see a simple ASCII table, listing all of the database’s sitting inside of your MySQL Server. So, make sure those details are inside the mysql backup script, then we need to edit the next section.

#List of all of the databases that we want to ignore...
DbToIgnore='information_schema mysql';

Quite simple, this is a space delimited list of database we DON’T want to back up, for example, if for some reason you had a test database in your server that you didn’t care about, you could all it to this list and then it will never get backed up.

#Path to which the backups should e placed
BackupRoot='/var/backup/mysql/';

This is the location under which all backups will be stored, it will create the entire directory structure if it has to, that is not a problem. Just make sure the user running the script has sufficient privileges to write to that folder.

#Compression Level (9 = Best, 1 = Fast)
CompressionLevel=1

Another simple one, pick a number between 1 and 9. Setting the value to 9 will give you the best compression possible (using gzip) and 1 will give you the worst compression, but will be faster in execution. It’s entirely up to you, by default it’s set to the fastest.

#Date Format for the FileNames
DateFormat='%d-%m-%Y_%H-%M-%S'

Now, being an English guy, i like my date in the format of DD-MM-YYYY HH-MM-SS, as you can see, this is easily modified to suite your needs. Feel free to adjust this if you need to.

Right, our script should be configured and ready to rock. Let’s try it! Don’t worry, even if your details are incorrect, *nothing* is written to your database. Let’s run it!

$ ./mysqlbackup

You should either get a MySQL Connection error, or it should be backing up your database server! It should give you updates after each database has been backed up. Example output…

Ignoring Db - information_schema
Backup Folder (/home/dean/mysqlbackup/) Exists
Sub-Backup Folder (/home/dean/mysqlbackup/database1) Exists
Executing backup of database1...Completed at 31-08-2009_22-30-56
Backup Folder (/home/dean/mysqlbackup/) Exists
Sub-Backup Folder (/home/dean/mysqlbackup/database2) Exists
Executing backup of database2...Completed at 31-08-2009_22-31-06
Backup Folder (/home/dean/mysqlbackup/) Exists
Sub-Backup Folder (/home/dean/mysqlbackup/database3) Exists
Executing backup of database3...Completed at 31-08-2009_22-31-09
Ignoring Db - mysql
...
Database backup has been completed. Please drop by http://www.spikedsoftware.co.uk

Then when it has finished, go off and check the folder where you asked backups to be made, and check to make sure they are all there. And that should be it.

Now you have all your databases backed up into individual time stamped files, sorted into sub-directories for ease of access.

That’s all from me for now, if anyone has any ideas as to how i could upgrade this mysql backup script or make it efficient, please do leave a comment.

Thanks for reading!