Archive for July, 2010

Published by Rolf on 15 Jul 2010

How to restore a SQL Server database from a backup file

Recently I volunteered to port a website from ColdFusion/SQL Server to PHP and MySql. But rather than getting the database snapshot as a SQL script, which would have been easy to migrate, I was given the database in the form of a SQL Server backup, which has two parts: an LDF log fie and an MDF data file.

Now not being particularly skilled in SQL Server, I had to hunt around for what to do with this. It turned out to be ultimately pretty easy. Just install SQL Server 2008 via the Web Installer, reboot, and then launch the administration interface and run this SQL statement to import the backup file as a database:

RESTORE DATABASE mydb FROM disk = 'C:\\path\\to\\mydb.bak'
WITH REPLACE, MOVE 'mydb_Dat' TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL10.SQLEXPRESS\MSSQL\\DATA\\mydbdat.mdf',
MOVE 'mydb_log' TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL10.SQLEXPRESS\\MSSQL\\DATA\\mydblog.ldf'

Then choose “Script to File” to output the database to an ordinary SQL script, which then can be imported into MySQL.

Published by Rolf on 10 Jul 2010

Quick steps to configure SSL encryption on a WAMP server

There are a number of guides for this on the web, all of varying accuracy. These are the minimal steps I followed to get it working for development on a stock WAMP server installation:

  1. Enable ssl_module in the Apache module list
  2. Create the self-signed certificate keys as per Section 3 of http://tud.at/programm/apache-ssl-win32-howto.php3:
    cd c:\path\to\wamp\bin\apache\Apache2.2.11\conf
     
    # Set a PEM passphrase of 4 characters or more.  You can leave the challenge password blank.
    openssl req -config openssl.cnf -new -out my-server.csr
     
    # Set the "Common Name" to localhost, accepting the rest of the defaults.
    openssl rsa -in privkey.pem -out my-server.key
     
    openssl x509 -in my-server.csr -out my-server.cert -req -signkey my-server.key -days 365
  3. In your conf/extra/httpd-vhosts.conf, add this:
    ### SSL
    # see http://www.modssl.org/docs/2.8/ssl_reference.html for more info
     
    # change this from "sem" to avoid startup errors
    SSLMutex default
     
    SSLRandomSeed startup builtin
    SSLSessionCache none
     
    Listen 443
    <VirtualHost *:443>
    ErrorLog logs/ssl.log
    LogLevel info
    # You can later change "info" to "warn" if everything is OK
     
    SSLEngine On
    SSLCertificateFile conf/my-server.cert
    SSLCertificateKeyFile conf/my-server.key
     
    # copy the <Directory> information from the appropriate HTTP virtual host
    DocumentRoot "C:/path/to/docroot"
    <Directory />
        ...
    </Directory>
    </VirtualHost>
  4. Restart Apache.

That should do it, then just navigate to http://localhost:443. You’ll get browser warnings complaining that the certificate is self-signed and thus probably bogus, but it will work otherwise.

Published by Rolf on 06 Jul 2010

Installing Subversion on CentOS – be careful what you read

The CentOS directions are pretty good – follow them to the letter. The directions in the installed /etc/httpd/conf.d/subversion.conf – less so. In particular, your <Location> node should look something like this, in order to work (ignore the rest):

<Location /repos>
  DAV svn
  # path to your repo here
  SVNPath /var/www/svn/repos
 
  AuthType Basic
  AuthName "Subversion repos"
  AuthUserFile /etc/svn-auth-conf
  Require valid-user
</Location>

Published by Rolf on 05 Jul 2010

Saving out a playlist to share it

Apple, and to a lesser extent, Microsoft, go out of their way to make sharing music painful – even in the simple case of sharing a playlist of songs with your buddy (which clearly falls under Fair Use!). For example, suppose you made a playlist for yourself (of music you own), put it on an iPod, and then played it for a friend. If they liked it and wanted it on their iPod as well, how would you share it with them? You can’t “send” them a playlist – you’d have to copy the songs one by one to a hard drive or DVD, then hand it to your friend, he plugs it into his computer, he copies over the songs, then he takes the time to recreate your playlist manually in iTunes. It would be funny if the state of the art wasn’t so sad!

In just this situation, I ended up writing a short PHP console script to do this semi-automatically. It parses a playlist file in PLS format, then copies the files to a central directory, which can then be copied to an external hard drive. This would then be plugged into your friend’s PC, and the music copied to the destination of choice. Finally, it outputs a modified PLS file with all the paths changed.

No magic here, just a few lines of code to make the tedious task of sharing music you like, somewhat less so.

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
// saveplaylist.php
$copy_folder = 'H:\\temp\\'; // path to a central directory
$destination_folder = 'C:\\My Music\\';  // the destination on your friend's PC
$playlist_file = 'C:\Users\JohnDoe\Music\Playlists\myplaylist.pls';  // the playlist file
$contents = file_get_contents($playlist_file);
 
$newplaylist = $contents;
 
$matches = array();
if (preg_match_all('/File(.*)=(.*)$/msU', $contents, $matches, PREG_SET_ORDER))
{
	foreach ($matches as $match)
	{
		$file = $match[2];
		$file = trim($file);
		$name = pathinfo($file, PATHINFO_BASENAME);
		echo "Copying {$file}...\n";
		$copy_file = $copy_folder . $name;
		$destination_file = $destination_folder . $name;
		copy($file, $copy_file);
		$escaped_filename = str_replace('\\', '\\\\', $match[2]);
		$escaped_filename = str_replace('.', '\\.', $escaped_filename);
		$escaped_filename = str_replace('[', '\\[', $escaped_filename);
		$escaped_filename = str_replace(']', '\\]', $escaped_filename);
		$escaped_filename = str_replace('(', '\\(', $escaped_filename);
		$escaped_filename = str_replace(')', '\\)', $escaped_filename);
		$newplaylist = preg_replace('/File'.$match[1].'='.$escaped_filename."/", 'File'.$match[1].'='.$destination_file, $newplaylist);
	}
}
echo '<hr>';
echo $newplaylist;