path: root/content/posts/2005-03-11-three-language-showdown.html

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

<p>
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 <a href='http://ruby-lang.org/'>Ruby</a>, <a href='http://perl.org/'>Perl</a>, and <a href='http://php.net/'>PHP</a>, for your viewing (comparing, contrasting, commenting, etc) pleasure:
</p>

<p>
<b>Ruby:</b>
</p>

<div style='font-size: 75%; background-color: #eee; padding: 10px;
margin: 10px 30px 10px 30px; border: 1px solid black;'>
<pre><code>  #!/usr/bin/env ruby

  # load the mysql library
  require 'mysql'

  # all our options go here
  opt = {
    :host =&gt; 'localhost', # server to connect to (usually localhost)
    :user =&gt; 'USER',      # replace USER with username
    :pass =&gt; 'PASSWORD',  # replace PASSWORD with password
    :db   =&gt; 'DATABASE',  # replace DATABASE with database name
    :tbl  =&gt; 'TABLE',     # replace TABLE with table name
    :fld  =&gt; '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) &amp;&amp; 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
</code></pre></div>

<p>
<b>Perl:</b>
</p>

<div style='font-size: 75%; background-color: #eee; padding: 10px;
margin: 10px 30px 10px 30px; border: 1px solid black;'>
<pre><code>  #!/usr/bin/perl

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

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

  my %opt = {
    'host' =&gt; 'localhost',  # server to connect to (in our case, localhost)
    'user' =&gt; 'USER',       # replace USER with username
    'pass' =&gt; 'PASSWORD',   # replace PASSWORD with password
    'db'   =&gt; 'DATABASE',   # replace DATABASE with database
    'tbl'  =&gt; 'TABLE',      # replace TABLE with table name
    'fld'  =&gt; '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 &amp;&amp; -r $path);

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

  # build DSN string, connect to database
  $dsn = "dbi:mysql:database=" . $opt{'db'} . ";host=" . $opt{'host'};
  $db = DBI-&gt;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 = &lt;STDIN&gt;;

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

<p>
<b>PHP:</b>
</p>

<div style='font-size: 75%; background-color: #eee; padding: 10px;
margin: 10px 30px 10px 30px; border: 1px solid black;'>
<pre><code>  &lt;!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" 
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1.dtd"&gt;
  &lt;?php 
  require_once 'DB.php';

  echo "&lt;?xml version='1.0' encoding='iso-8859-1'?&gt;\n"; 

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

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

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

      &lt;?php
        # check to see if the file was uploaded
        if ($file = $_FILES['csvfile']) {
          echo "&lt;hr /&gt;\nResults:&lt;br /&gt;";
          handle_file($file);
        }
      ?&gt;
    &lt;/body&gt;
  &lt;/html&gt;
  &lt;?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-&gt;getMessage());

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

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

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

  ?&gt;
</code></pre></div>

<p>
Now before the pedant geek armada nails me to the wall: Yes, I know the
<em>new</em> way of calling <code>DBI-&gt;connect</code> includes passing <code>{ 
AutoCommit =&gt; 1 }</code> as a fourth parameter, and yes, I know that I should be 
passing the <acronym title='Data Source Name'>DSN</acronym> an
<code>array</code> to <code>DB::connect</code>, and yes, I know that <a
href='http:/ruby-lang.org/'>Ruby</a> has <acronym title='Database Interface'>DBI</acronym> too!  And finally, yes, I
know a dependency on <a href='http://mysql.com/'>MySQL</a> 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 <a href='http://mysql.com/'>MySQL</a> and <a
href='http://postgresql.org/'>Postgres</a>, and the nuances of their
respective regex engines.  So bugger off :D.
</p>