From 4b6c0e31385f5f27a151088c0a2b614495c4e589 Mon Sep 17 00:00:00 2001 From: Paul Duncan Date: Thu, 14 Oct 2021 12:47:50 -0400 Subject: initial commit, including theme --- .../posts/2005-03-11-three-language-showdown.html | 251 +++++++++++++++++++++ 1 file changed, 251 insertions(+) create mode 100644 content/posts/2005-03-11-three-language-showdown.html (limited to 'content/posts/2005-03-11-three-language-showdown.html') 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 +--- + +

+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. +

+ -- cgit v1.2.3