--- date: "2005-03-11T04:13:57Z" title: Three Language Showdown ---

Earlier this evening I wrote an example of how to read a list of field values to delete from an input file. That's not particularly interesting. The fact that I wrote it in three different languages is, though. So, without any further ado, the world's simplest database application, written in Ruby, Perl, and PHP, for your viewing (comparing, contrasting, commenting, etc) pleasure:

Ruby:

  #!/usr/bin/env ruby

  # load the mysql library
  require 'mysql'

  # all our options go here
  opt = {
    :host => 'localhost', # server to connect to (usually localhost)
    :user => 'USER',      # replace USER with username
    :pass => 'PASSWORD',  # replace PASSWORD with password
    :db   => 'DATABASE',  # replace DATABASE with database name
    :tbl  => 'TABLE',     # replace TABLE with table name
    :fld  => 'FIELD',     # name of field to match against
  }

  # check first command-line argument for the filename
  unless path = ARGV.shift
    $stderr.puts "ERROR: missing filename"
    exit -1
  end

  # check to make sure the specified file exists and is readable
  unless File.exists?(path) && File.readable?(path)
    $stderr.puts "ERROR: missing file or unreadable file: #{path}"
    exit -1
  end

  # read a list of IDs from the file, excluding blank lines
  ids = File.readlines(path).map { |id| id.strip }.grep(/[^\s]/)

  # connect to server, select database
  db = Mysql::connect(opt[:host], opt[:user], opt[:pass])
  db.select_db(opt[:db])

  # build a big friggin list of ids for the query below
  ids_str = ids.map { |id| "'#{id.gsub(/'/, "''")}'" }.join(',')

  # build query
  query = "DELETE FROM #{opt[:tbl]} WHERE #{opt[:fld]} IN (#{id_str})"

  # print the query out on the screen
  puts query
  
  # check to make sure this really what we want to do 
  puts "Execute this query? (y/N)"
  if gets =~ /^y/
    # user entered "y" or "Y", so execute the query
    db.query(query)
    puts "Done."
  else
    puts "Cancelled."
  end

Perl:

  #!/usr/bin/perl

  # turn on warnings and strict interpretation
  use warnings;
  use strict;

  # load DBI module (see [2] below)
  use DBI;

  my %opt = {
    'host' => 'localhost',  # server to connect to (in our case, localhost)
    'user' => 'USER',       # replace USER with username
    'pass' => 'PASSWORD',   # replace PASSWORD with password
    'db'   => 'DATABASE',   # replace DATABASE with database
    'tbl'  => 'TABLE',      # replace TABLE with table name
    'fld'  => 'FIELD',      # replace FIELD with name of field
  };

  # declare vars
  my ($path, $fh, @ids, $id_str, $dsn, $db, $query, $response, $a);

  # get filename
  $path = shift @ARGV;

  # check path to make sure it's legit
  die "ERROR: Missing filename\n" unless $path;
  die "Missing or unreadable file: '$path'\n" unless (-e $path && -r $path);

  # read ids from file, stripping out blank lines
  open $fh, $path or die "Couldn't open file: $!\n";
  @ids = grep { /[^\s]/ } <$fh>;
  close $fh;
  chomp @ids;

  # build DSN string, connect to database
  $dsn = "dbi:mysql:database=" . $opt{'db'} . ";host=" . $opt{'host'};
  $db = DBI->connect($dsn, $opt{'user'}, $opt{'pass'});
  
  # build id list string
  $id_str = join(',', map { $a = $_; $a =~ s/'/''/g; "'$a'" } @ids);

  # build query string
  $query = "DELETE FROM " . $opt{'tbl'} .
           " WHERE " . $opt{'fld'} . " IN ($id_str)";

  # print query out on screen
  print "$query\n";

  # check to make sure user really wants to execute query
  print "Execute this query? (y/N)";
  $response = <STDIN>;

  # check response
  if ($response =~ /^y/i) {
    # user said 'y' or 'Y', execute query
    $db->do($query);
    print "Done.\n";
  } else {
    print "Cancelled...\n";
  }

PHP:

  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" 
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1.dtd">
  <?php 
  require_once 'DB.php';

  echo "<?xml version='1.0' encoding='iso-8859-1'?>\n"; 

  # options
  $opt = array(
    'host' => 'localhost',  # database server (in our case, localhost)
    'user' => 'USERNAME',   # replace USERNAME with user
    'pass' => 'PASSWORD',   # replace PASSWORD with password
    'db'   => 'DATABASE',   # replace DATABASE with database
    'tbl'  => 'TABLE',      # replace TABLE with table name
    'fld'  => 'FIELD',      # replace FIELD with match field
  );

  # set page title
  $page_title = 'PHP MySQL Sample Code';
  ?>
  <html lang='en'>
    <head>
      <title><?php echo $page_title; ?></title>
    </head>

    <body>
      <form method='post' action='<?php echo $PHP_SELF; ?>'
            enctype='multipart/form-data'> 
         <!-- PHP needs this nonsense :/ -->
         <input type='hidden' name='MAX_FILE_SIZE' value='30000' />
         File: <input type='file' name='csvfile' />
         <input type='submit' value='Delete IDs from file' />
      </form>

      <?php
        # check to see if the file was uploaded
        if ($file = $_FILES['csvfile']) {
          echo "<hr />\nResults:<br />";
          handle_file($file);
        }
      ?>
    </body>
  </html>
  <?php

  #####################
  # UTILITY FUNCTIONS #
  #####################
  
  # 
  # handle file upload
  #
  function handle_file($file) {
    global $opt;

    # check to see if there was an error:
    if ($file['error'])
      die(__LINE__ . ": Error handling file upload: {$file['error']}");

    # no error, go ahead and read the list of IDs
    $ids = array_map('trim', file($file['tmp_name']));

    # build ID string
    $id_str = join(',', array_map('quote_str_cb', $ids));

    # build DSN string
    $dsn = "mysql://{$opt['user']}:{$opt['pass']}@{$opt['host']}/{$opt['db']}";

    # connect to database
    $db = DB::connect($dsn);
    if (DB::isError($db))
      die(__LINE__ . ": Couldn't connect to database: " . $db->getMessage());

    # build/print query string
    $query = "DELETE FROM {$opt['tbl']} WHERE {$opt['fld']} IN ($id_str)";
    echo "$query";

    # execute query
    $err = $db->query($query);
    if (DB::isError($err))
      die(__LINE__ . ": Couldn't query database: " . $err->getMessage());
  }

  #
  # escape quotes in string, and return quoted version of string
  #
  function quote_str_cb($str) {
      return "'" . str_replace("'", "''", $str) . "'";
  }

  ?>

Now before the pedant geek armada nails me to the wall: Yes, I know the new way of calling DBI->connect includes passing { AutoCommit => 1 } as a fourth parameter, and yes, I know that I should be passing the DSN an array to DB::connect, and yes, I know that Ruby has DBI too! And finally, yes, I know a dependency on MySQL is totally 2001, but I wrote these examples to address a specific problem. The original email includes all sorts of extra stuff, including explanations of differences between MySQL and Postgres, and the nuances of their respective regex engines. So bugger off :D.