Problem: give the neatest possible complete program which does the given
database select using the first_name given on the command line
and outputs the
table in the following format: first line: column names, sorted, tab separated,
followed by one line per row of the matching data, tab separated. Brevity is
good. The DB is on the local host and the connection parameters are:
user: foo
pass: bar
db: common
type: MySQL
The SQL select is: SELECT * FROM person p, email e, company c, operating_system o WHERE p.first_name = ? AND e.person_id = p.id AND c.person_id = p.id and o.person_id = p.id ORDER BY p.last_name LIMIT 10
> ./pracdb Joe | cat -t
company_name^Iemail^Ifirst_name^Iid^Ilast_name^Ioperating_system^Iperson_id
Jones, Inc.^Ijjones@example.com^IJoe^I2^IJones^IMacOS^I2
Smith, Inc.^Ijsmith@example.com^IJoe^I1^ISmith^IWindows^I1
Languages given alphabetical order:
C
#include <mysql/mysql.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
int strcmp_wrapper(void *a, void *b) {
return strcmp(*((char **)a), *((char **)b));
}
int main(int argc, char *argv[]) {
MYSQL dbh;
MYSQL_RES *result;
MYSQL_ROW row;
MYSQL_FIELD *fields;
char *sql, *first_name, ***field_names;
size_t len;
unsigned int i, j, num_fields, *col_order;
mysql_init(&dbh);
mysql_real_connect(&dbh, NULL , "foo" , "bar" , "common" , 0 , NULL , 0 );
len = strlen(argv[1 ]);
first_name = (char *) malloc(sizeof (char ) * (len * 2 + 1 ));
mysql_real_escape_string(&dbh, first_name, argv[1 ], (unsigned long ) len);
asprintf(&sql, " \
SELECT * \
FROM person p, email e, company c, operating_system o \
WHERE p.first_name = ' %s ' \
AND e.person_id = p.id AND c.person_id = p.id and o.person_id = p.id \
ORDER BY p.last_name \
LIMIT 10 \
" , first_name);
free(first_name);
mysql_query(&dbh, sql);
free(sql);
result = mysql_store_result(&dbh);
num_fields = mysql_num_fields(result);
fields = mysql_fetch_fields(result);
field_names = (char ***) malloc(sizeof (char **) * num_fields);
for (i = 0 ; i < num_fields; i++) {
field_names[i] = &fields[i].name;
}
qsort(field_names, num_fields, sizeof (char *), strcmp_wrapper);
col_order = (unsigned int *) malloc(sizeof (unsigned int ) * num_fields);
for (i = 0 ; i < num_fields; i++) {
for (j = 0 ; j < num_fields; j++) {
if (fields[i].name == *field_names[j]) {
col_order[i] = j;
continue ;
}
}
fputs(*field_names[i], stdout );
putchar((i == (num_fields-1 )) ? '\n' : '\t' );
}
free(field_names);
while ((row = mysql_fetch_row(result))) {
for (i = 0 ; i < num_fields; i++) {
fputs(row[col_order[i]], stdout );
putchar((i == (num_fields-1 )) ? '\n' : '\t' );
}
}
free(col_order);
free(fields);
mysql_free_result(result);
exit(0 );
}
Haskell
import List
import Data.List
import Database.HDBC
import Database.HDBC.ODBC
import System
import Maybe
main = do
dbh <- connectODBC "DSN=people"
sth <- prepare dbh
" SELECT * \
\ FROM person p, email e, company c, operating_system o \
\ WHERE p.first_name = ? AND e.person_id = p.id \
\ AND c.person_id = p.id and o.person_id = p.id \
\ ORDER BY p.last_name \
\ LIMIT 10 "
args <- getArgs
execute sth [toSql $ head args]
rows <- fetchAllRowsAL' sth
let cols = sort $ map fst $ head rows
putStrLn $ intercalate " \t " cols
let sortRow row = map (\ col -> fromSql $ fromJust $ lookup col row) cols
mapM_ putStrLn $ map (intercalate " \t " . sortRow) rows
Perl
use DBI;
my $dbh = DBI->connect ('DBI:mysql:common' , 'foo' , 'bar' , { RaiseError => 1 });
my @rows = @{$dbh ->selectall_arrayref(q{
SELECT *
FROM person p, email e, company c, operating_system o
WHERE p.first_name = ?
AND e.person_id = p.id AND c.person_id = p.id and o.person_id = p.id
ORDER BY p.last_name
LIMIT 10
} , { Columns => {} }, $ARGV [0 ])};
my @cols = sort keys %{$rows [0 ]};
print (join (" \t " , @cols ), " \n " );
print (join (" \t " , @$_ {@cols }), " \n " ) for @rows ;
PHP
<?php
require_once 'DB.php ';
$ db = DB:: connect( 'mysql://foo:bar@localhost/common ') ;
$ rows = $ db -> getAll( '
SELECT *
FROM person p, email e, company c, operating_system o
WHERE p.first_name = ?
AND e.person_id = p.id AND c.person_id = p.id and o.person_id = p.id
ORDER BY p.last_name
LIMIT 10
', array ( $ _SERVER [ 'argv '][ 1 ]) , DB_FETCHMODE_ASSOC) ;
$ cols = array_keys ( $ rows [ 0 ]) ;
sort ( $ cols ) ;
print join ( "\t ", $ cols ) . "\n ";
foreach ( $ rows as $ row ) {
$ line = array () ;
foreach ( $ cols as $ col ) $ line [] = $ row [ $ col ] ;
print join ( "\t ", $ line ) . "\n ";
}
?>
Python
import sys
import MySQLdb
db = MySQLdb.connect(user='foo ',passwd='bar ',db='common ',host='localhost ')
c = db.cursor(MySQLdb.cursors.DictCursor)
c.execute('''
SELECT *
FROM person p, email e, company c, operating_system o
WHERE p.first_name = %s
AND e.person_id = p.id AND c.person_id = p.id and o.person_id = p.id
ORDER BY p.last_name
LIMIT 10
''', sys.argv[1])
rows = c.fetchall()
cols = rows[0].keys()
cols.sort()
print '\t '.join(cols)
for r in rows:
print '\t '.join([ str(r[i]) for i in cols ])
Ruby
require ' dbi '
dbh = DBI .connect(' dbi:mysql:common ' , ' foo ' , ' bar ' )
rows = dbh.select_all(%q{
SELECT *
FROM person p, email e, company c, operating_system o
WHERE p.first_name = ?
AND e.person_id = p.id AND c.person_id = p.id and o.person_id = p.id
ORDER BY p.last_name
LIMIT 10
} , ARGV [0 ])
cols = rows[0 ].column_names.sort
puts cols.join("\t" )
rows.each {|row | puts row[cols].join("\t" ) }