diff options
Diffstat (limited to 'content/posts/2005-03-11-three-language-showdown.html')
| -rw-r--r-- | content/posts/2005-03-11-three-language-showdown.html | 251 |
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 => '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 +</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' => '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"; + } +</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> <!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) . "'"; + } + + ?> +</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->connect</code> includes passing <code>{ +AutoCommit => 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> + |