initial commit, including theme

1 files changed, 251 insertions, 0 deletions

diff --git a/content/posts/2005-03-11-three-language-showdown.html b/content/posts/2005-03-11-three-language-showdown.html
new file mode 100644
index 0000000..f374d64
--- /dev/null
+++ b/content/posts/2005-03-11-three-language-showdown.html
@@ -0,0 +1,251 @@
+---
+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>
+