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.