franzone.com

The Problem

I recently had a problem. I am a software developer and as such I use source control. For this purpose I generally prefer Subversion. I have been keeping my source repositories on an external drive with the intention to back them up to another secure location periodically (yeah, that never happens). I looked into online subversion hosting, but it seems a bit pricey for my few meager projects. I have a web host, but they don’t host subversion repositories over HTTPS as I’ve used in other situations. What to do?

The Solution

Subversion over SSH to the rescue! I actually have shell access to my web host (I can login to the server via SSH) and doing some quick research I discovered that I can run Subversion through an SSH tunnel. Cool! So how might one set this up?

Setting Up The Server

Prerequisites for the server are that you can login to your server via ssh. Got that? Good. Now while logged into your (Linux) server make sure that subversion is installed using the following commands:

which svn
which svnadmin
which svnserve

I recently had the need to be able to monitor a directory on an FTP server for changes and to then be notified of those changes. I’ve been working a lot in PHP lately so I decided to use that to implement my script.

FTP Access

FTP (file transfer protocol) access in PHP is a breeze. It’s as simple as this:

// Connect to FTP host
$conn = ftp_connect($host, $port) or die("Could not connect to {$host}\n");
 
// Login
if(ftp_login($conn, $user, $pass)) {
  // Retrieve directory listing
  $files = ftp_nlist($conn, $remote_dir);
}
 
// Close Connect
ftp_close($conn);

Easy, right? Of course I left out a few pieces of the puzzle, but I just wanted to show how easy it is to actually get to an FTP server, login and get a directory listing.

Comparisons

After retrieving the listing which is an array I remove out the “.” and “..” listings as they are special system directories meaning “the current directory” and “the parent directory” respectively.

$ftpFiles = array();
foreach($files as $file)
{
  // Get just the filename without directory information
  $thisFile = basename($file);
  if($thisFile != '.' && $thisFile != '..')
  {
    // Append to our new array
    $ftpFiles[] = $thisFile;
  }
}

Now the header for this section is Comparisons, so what exactly are we comparing to? Each time this script runs we store a serialized version of this array out to file. I read the file into an array and compare it with the one we just created from the FTP directory.

$currentFiles = array();
if(file_exists($cache_file))
{
  // Read contents of file
  $handle = fopen($cache_file, "r");
  if($handle)
  {
    $contents = fread($handle, filesize($cache_file));
    fclose($handle);
 
    // Unserialize our array from the file contents
    $currentFiles = unserialize($contents);
  }
}

I use Java as my main Linux server programming language these days and so I usually have a Bash script to go along with each program. The Bash script help encapsulate creating the proper CLASSPATH needed to run the Java program, calls the correct Java class and also passes through any command line parameters that I might need. Recently I had several Java programs that I wrote and bundled inside the same jar file which I needed to call separately. They had all of the same CLASSPATH and environment requirements, so why should I create separate Bash scripts for each one?

What’s In A Name?

Instead of a separate Bash script for each Java program I used a neat little feature of Linux called symbolic linking. I create in a “bin” subdirectory my main Bash script and then link to it using distinct names from the main program directory. The script then detects what names is being used to call it and executes the appropriate Java class. Some of the Bash code looks like this:

#!/bin/bash
# Filename: JavaCaller
 
# Get the name of the script (or the name it was called as)
RSCRIPT="`basename $0`"
 
# Do not allow the script to be called directly
if [ "${RSCRIPT}" = "JavaCaller" ]; then
  echo "Please do not call script directly."
  exit 1
 
# Run the Java class that matches the script name
else
  RUN_CLASS="com.franzone.${RSCRIPT}"
fi
 
# Run the java program
java ${RUN_CLASS}|> $@

Notice the $@ command line parameter in the call to the Java class? That simply passes on the command line parameters used to call the script to the Java program.

So you can see, if I had a Java program in the com.franzone package who’s class name was TestOne, then I could create a symbolic link to my JavaCaller script to call it like this:

ln -s bin/JavaCaller TestOne

Now from the main directory I could call the ./TestOne script (symbolic link) which would actually execute the JavaCaller script with the TestOne name. That would cause my Bash script to call the TestOne Java class. Cool, eh?

I know you’ve all asked that question before, right? Well maybe not, but I recently had the need to detect what the DATATYPE for arbitrary columns in arbitrary tables were inside a MySQL database using PHP. Turns out that it isn’t all that hard to get.

The main thing about using this method is that we are simply sending SQL queries to MySQL which instruct it to give us the required information. The first one is to retrieve a list of tables for the currently selected database:

$result = mysql_query("SHOW TABLES FROM $database", $conn);

The next time is when we retrieve the columns for the table and the column definitions:

$tableName = $row[0];
$cols = mysql_query("SHOW COLUMNS FROM $tableName", $conn);

Notice that I looped through the results of the first query (the table names). I then use the value of the table names returned from the query to build the query for extracting the column definitions.

Finally, you might ask why I decided to throw all of the results into an array? I could just as easily (probably more easily) have output the results to the console as I ran through the queries. Well, in my situation I needed to actually store and compare the results at a later time. So that’s what I did! Following is an entire code sample that should work; just fill in your login credentials and have fun!

<?php
  // Configuration
  $host = 'localhost:3306';
  $user = '';
  $pass = '';
  $database = '';
 
  // Connect to the MySQL database
  $conn = mysql_connect($host, $user, $pass) or die("Could not connect to database\n");
  mysql_select_db($database, $conn) or die("Could not connect to $database\n");
 
  // Query to get list of tables
  $result = mysql_query("SHOW TABLES FROM $database", $conn);
  if($result)
  {
    $output = array();
    while($row = mysql_fetch_array($result))
    {
      $tableName = $row[0];
 
      // Get meta data
      $cols = mysql_query("SHOW COLUMNS FROM $tableName", $conn);
      if($cols)
      {
        while($col = mysql_fetch_assoc($cols))
        {
          // Add this table to our associative array
          if(!array_key_exists($tableName, $output)) {
            $output[$tableName] = array();
          }
          // Add the column definition to our associative array
          $output[$tableName][$col['Field']] = $col['Type'];
        }
        mysql_free_result($cols);
      }
    }
    // Output results
    foreach($output as $table => $cols)
    {
      echo "===== $table =====\n";
      foreach($cols as $field => $type)
      {
        echo "$field : $type\n";
      }
    }
  }
 
  // Free result
  if($result)
    mysql_free_result($result);
  // Close connection
  if($conn)
    mysql_close($conn);
?>

I use NetVibes for my browser home page and RSS feed reader and I’ve noticed over time that some of the feeds that I subscribe to have pictures embedded within their feed summary. Why doesn’t my feed have a picture in it? I thought that this was really neat because it gives you a quick visual queue as to what the article may be about. It is also eye-catching so that among 5 or so feeds on a page you are instantly drawn to the one with pictures. So how can you get a picture into your feed?

RSS 2.0 Specification

The RSS 2.0 specification has an <enclosure> element available at the <item> level. This element can specify various types of media that you want attached to your feed item. In our case we want that to be an image. The element should look something like this:

<enclosure url=”[URL To Media File]” length=”[Size Of Media File]” type=”[Mime Type Of Media File]” />

All three attributes are required:

1. url - This tells the feed reader where the media file is located
2. length - This is how big the media file is in bytes
3. type - This is the standard MIME type of the file

Here is the situation. You are writing a program in Java. You need to access data in a Microsoft SQL Server via stored procedure and the data has an XML field. A what field? SQL Server 2005 introduces a new datatype for XML. It is essentially a blob field that you can perform XML operations on. For the Java program though, it doesn’t really care about that functionality and can just access it to write it out to a file (or process it inline).

JDBC Support

First off you are going to need to check your JDBC driver documentation thoroughly for which operations it supports. There also seems to be some variation in the actual implementation. For example, I tried used Microsoft’s own JDBC driver in my project but it caused a strange SQL Server error to be thrown when writing back to an XML field. I could not get around it (at least not in a timely fashion) so I switched to using the JTDS JDBC driver (go open-source!).

The Stored Procedure

Let us say that the stored procedure in SQL Server looks something like this:

CREATE procedure [GetSomeData] (@NumberParameter int, @NumberParameter2 int=NULL)
AS
BEGIN
 
SELECT IDField, SomeText, LargeXMLField, DateTimeField
FROM dbo.SomeTable
WHERE @NumberParamter=NumberFieldID
 
END

In a recent article I outlined how to modify the output of the wp_list_pages() template function in WordPress. Later on, however, I found that having the <span> tags that I added in every single title was not desirable. So instead of using a filter I wrote a small bit of custom code to iterate through my pages instead of using wp_list_pages(). Here is how to do it.

Wherever you need to output your pages just use some code like this:

$pages = get_pages('');
if($pages)
{
  foreach($pages as $page)
  {
    // Echo your page listing here
    // Use : $page->ID
    //     : $page->page_title
    //     : the_permalink($page->ID)
  }
}

I was recently working on a new WordPress theme when I had need to modify the title of pages before output. The style I was attempting to use involved wrapping <span> tags around the title (inside of the href tags). The wp_list_pages() function returns pages (which are basically the same as posts) and has several arguments that you can pass to customize it’s output. However, modifying the title output is not one of the arguments.

Filters to the rescue! I simply opened up the functions.php file and added a function like this:

function title_span_filter($content)
{
  return '<span>' . $content . '</span>';
}

Pretty simple, eh? All that is left to do is to register this function as an appropriate filter, which you can do like this:

// Add Filters
add_filter('the_title', 'title_span_filter', 1);

The first parameter tell WordPress which function/activity you want to “hook” into. The second parameter is the name of your function. The final parameter is a priority specifier. The lower this priority number the earlier it will execute.

Done! Now instead of output like this:

<li class="page_item"><a href="...">Page Title</a></li>

I get this:

<li class="page_item"><a href="..."><span>Page Title</span></a></li>

For extra credit you actually throw all of this into a plugin. That way you wouldn’t necessarily have to modify any of your theme files.

Many people use the MySQL open source relational database server. Mostly for websites, but I’ve also seen it used elsewhere. One problem that I recently ran into with my local development installation is… “what was my root password again?” Aaaggghh!

OK, sorry. Don’t panic; it can be recovered. A quick survey of the internet and I found this very simple and very useful how-to Recover lost MySQL root password mini-HowTo. Is is very well written and easy to follow… but, it only covers the process from a Linux installation. The basic trick here in either platform is to restart your MySQL server in and tell it to skip the grants table. So here is my addition on the Windoze platform.

Recover Your Lost root Password In MySQL (Windows)

1. Stop the MySQL server
   1. You can accomplish this by opening the Services control panel applet, finding the MySQL service listing and clicking the Stop icon.
   2. Alternately you can open up the command line and type > net stop MySQL
2. Start the MySQL server and skip grants
   1. Go to the MySQL installation directory and then the \bin directory under that (in the console)
   2. Type > mysqld-nt --skip-grant-tables

To tag heaven. Ah, yes… tags. The teacher’s pet of Web 2.0. The category list on this blog had become rather unwieldy as I just plugged in new categories whenever I did not feel like putting any thought into organization. I never really liked that method and so when WordPress implemented tags I figured I’d make the move. The only problem was converting all of my old categories into tags. This was quite an undertaking and it actually took some MySQL DB tweaking… muahahaha!

Converting Categories To Tags

Fear not, though! There is a Category to Tag converter in the WordPress admin under Imports. This came in quite handy. However, if you did like me and waited too long you may have some terms that are both a category and a term. For instance I used Apple as a category for a while and then started tagging posts with Apple instead. The Category To Tag converter will not convert your category in this instance… bummer. If you need to solve this problem go back and remove all of the Apple tags from posts and mark them with the equivalent category. Once that tag is completely gone from all posts you can delete the tag in your system. How do you do that?

Well, using phpMyAdmin or another similar SQL tool for accessing your MySQL database issue the following query:

SELECT * FROM `wp_terms` WHERE `name` = 'Apple'