Caching The Data Dictionary

Dollar Bills

Not this type of cash

In the last couple of posts I have talked about extracting table names using DBI->table_info() and extracting column names using DBI->column_info().

If for example I had a tetchy DBA who didn’t want me to hit the database every time I wanted this information, or I was too impatient to wait for socket set-up/teardown, I might consider caching the returned data.

In this case, the module I need is CHI, the unified cache handling interface.

Putting everything together with a basic command line interface gives me this script.

use strict;
use warnings;

use CHI;
use DBI;
use File::Basename;
use Getopt::Long;

use constant CACHE_EXPIRY => "1 day";

my $database = 'wordpress';
my $user = 'readonly_user';
my $password = '...';

my $dsn = "DBI:mysql:database=$database";
my $dbh = DBI->connect($dsn, $user, $password);

my $cache = CHI->new(driver => 'File',
                     root_dir => "$ENV{HOME}/.dbInfoCache");

my %args = parseArgs();
if (defined($args{help})) {
    usage();
} elsif (defined($args{names})) {
    print join("\n", @{getTables($dbh, $cache)}), "\n";
    exit 0;
}

my $tables;

if (defined($args{table})) {
    $tables = [$args{table}];
} elsif (defined($args{all})) {
    $tables = getTables($dbh, $cache);
} else {
    usage();
}

my $noPrefix = @$tables == 1;
foreach my $table (@$tables) {
    print join("\n", map { ($noPrefix ? '' : "$table." ) . $_ }
                     @{getColumns($dbh, $cache, $table)}), "\n\n";
}

sub parseArgs {
    my %args;
    GetOptions(\%args,
               "all",
               "help",
               "names|tablenames",
               "table=s",);
    return %args;
}

sub usage {
    my $script = basename($0);
    print <<"EOF"
$script -names | -all | -table <table name> | -help
-names : return the names of the tables only
-all   : return columns for all of the tables
-table : return columns for just this table
EOF
    ;
    exit 0;
}

sub getTables {
    my ($db, $cache) = @_;
    my $cacheKey = "tables";
    my $tables = $cache->get($cacheKey);
    # $tables will be undefined if the cache entry has expired or
    # never been set
    unless (defined $tables) {
        warn "Info: getting tables from DB\n";
        $tables = getTablesFromDb($db);
        # CHI->set($key, $value, $time) automatically serializes references
        $cache->set($cacheKey, $tables, CACHE_EXPIRY);
    }
    return $tables;
}

sub getTablesFromDb {
    my ($db) = @_;
    my $sth = $dbh->table_info();
    my @tables;
    while (defined(my $row = $sth->fetchrow_hashref())) {
        push @tables, $row->{TABLE_NAME};
    }
    return \@tables;
}

sub getColumns {
    my ($db, $cache, $table) = @_;
    my $cacheKey = "columns.$table";
    my $columns = $cache->get($cacheKey);
    # $columns will be undefined if the cache entry has expired or
    # never been set
    unless (defined $columns) {
        warn "Info: getting columns.$table from DB\n";
        $columns = getColumnsFromDb($db, $table);
        # CHI->set($key, $value, $time) automatically serializes references
        $cache->set($cacheKey, $columns, CACHE_EXPIRY);
    }
    return $columns;
}

sub getColumnsFromDb {
    my ($db, $table) = @_;
    my $sth = $dbh->column_info(undef, undef, $table, '%');
    $sth->execute();
    my @results;
    while (defined(my $row = $sth->fetchrow_hashref())) {
        push @results, $row->{COLUMN_NAME};
    }
    return \@results;
}

Dollar Bills by rychlepozicky.com

Posted in Perl | Tagged , , | Leave a comment

Perl DBI table_info method

Alphabetical Filing

Just as column_info() returns information about the table columns in your database, so table_info() returns information about the tables themselves.

my $sth = $dbh->table_info();

print join(", ", @{$sth->{NAME}});
print "\n";
while (defined(my $row = $sth->fetchrow_hashref())) {
    print "Table: $row->{TABLE_NAME}\n";
}

Results

TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS
Table: wp_commentmeta
Table: wp_comments
Table: wp_links
Table: wp_options
Table: wp_postmeta
Table: wp_posts
Table: wp_term_relationships
Table: wp_term_taxonomy
Table: wp_terms
Table: wp_usermeta
Table: wp_users

The third parameter to table_info can do an SQL ‘like’ match against the table names. For example:

my $sth = $dbh->table_info(undef, undef, '%post%');

will return just the wp_postmeta and wp_posts tables.


Alphabetical filing by Marcin Wichary

Posted in Perl | Tagged , | Leave a comment

Database Agnostic Data Dictionary Spelunking with Perl DBI

typewriter

The various databases all have different ways of querying the internal data dictionary, to find out which tables are available and which columns they contain. Perl DBI provides a unifying interface so that you can extract table and column data from any supported database using the same API.

The method to extract column names and other meta data is called column_info() and there is a similar table_info method.

use DBI;

my $database = 'wordpress';
my $user = 'readonly';
my $password = 'password';

my $dsn = "DBI:mysql:database=$database";
my $dbh = DBI->connect($dsn, $user, $password);

my @tables = qw(wp_options wp_users wp_comments wp_posts);
foreach my $table (@tables) {
    print join("\n", sort map { "$table.$_" } getColumnNames($table));
    print "\n";
}

sub getColumnNames {
    my ($table) = @_;
    my $sth = $dbh->column_info(undef, undef, $table, '%');
    $sth->execute();
    my @results;
    while (defined(my $row = $sth->fetchrow_hashref())) {
        push @results, $row->{COLUMN_NAME};
    }
    return @results;
}

column_info() returns many other fields besides the name of the column.

Here is a simpler version of getColumnNames(). It probably uses column_info() behind the scenes.

sub getColumnNames {
    my ($table) = @_;
    my $sth = $dbh->prepare("SELECT * FROM $table WHERE 1=2");
    $sth->execute();
    return @{$sth->{NAME}};
}

Typewriter by Valeriana Solaris
This post was originally from www.perlsofwisdom.net

Posted in Perl, Programming | Tagged , | Leave a comment

Emacs String Functions

Characters and Strings

Strings are a sequence of characters. Unlike many languages emacs lisp doesn’t differentiate between characters and integers. Evaluating ?A returns 65. Therefore you don’t need an ord or char function.

To convert a character (or integer) into a string use (string ...).

(string 65)  ;; "A" is the same as
(string ?A)  ;; this

(string 10)  ;; these two are
(string ?\n) ;; also the same

To extract a character from a string use (elt ...).

(elt "ABC" 1) ;; 66 -> the ASCII value of B

Slicing and Dicing Strings

The basic function for slicing and dicing strings is (substring ...) You pass it the string and from and to indexes. 0 indicates the start of the string and negative indexes measure length from the end.

(defvar data "1234567890123456789012345678901234567890")

(substring data 0 4)   ;; 1234
(substring data 35)    ;; 67890
(substring data 35 -1) ;; 6789
(substring data -3 -1) ;; 89

To split a string use (split-string ...) defined in subr.el.

(split-string "a,b:c" "[,:]") ;; ("a" "b" "c")

You can join it back together with (mapconcat ...) which also takes a function with which you can transform each element.

(mapconcat (lambda (e) e) '("abc" "def" "ghi" "klm") ",")

;; gives --> "abc,def,ghi,klm"

Join strings together without a separator using (concat ...)

(concat "Hello " "[" "World" "] " "!!!") ;; "Hello [World] !!!"

Convert strings to uppercase using (upcase ...). There are also capitalize, downcase and upcase-initials.

(upcase "abc") ;; "ABC"

(capitalize "abc def") ;; "Abc Def"

Strings and Regular Expressions

The PLEAC has a nice example of substituting variables within a string using regular expressions.

$AGE = 17;
$text = 'I am $AGE years old'; # note single quotes
$text =~ s/(\$\w+)/$1/eeg;     # finds my() variables

Here is an imperfect translation into emacs-lisp.

(defvar AGE 17)
(defvar text "I am $AGE years old")

(when (string-match "$\\([A-Z]+\\)" text)
  (concat (substring text 0 (match-beginning 0))
          (format "%s" (symbol-value (intern (match-string 1 text))))
          (substring text (match-end 0))))

Unpack

Bindat is a library that provides similar to functionality to pack and unpack in perl. This is the example given.

# get a 5-byte string, skip 3, then grab 2 8-byte strings, then the rest
($leading, $s1, $s2, $trailing) =
    unpack("A5 x3 A8 A8 A*", $data);

The obvious translation doesn’t quite work – there might be a minor bug-ette in the library. This fails with an args-out-of-range error.

(require 'bindat)

(bindat-unpack '((leading str 5)
                 (fill 3)
                 (s1 str 8)
                 (s2 str 8)
                 (rest str (eval (- (length data) bindat-idx))))
               data)

Subtracting an extra one leaves one character off rest as expected.

;; ((rest . "567890123456789")
;;  (s2 . "78901234")
;;  (s1 . "90123456")
;;  (leading . "12345"))

Fortunately, I have a workaround that extends the source string with a dummy character and then takes 1 fewer characters. (yes, it is pretty horrible).

(bindat-unpack '((leading str 5)
                 (fill 3)
                 (s1 str 8)
                 (s2 str 8)
                 (rest str (eval (- (length data) bindat-idx 1))))
               (concat data "x"))

And the full equivalent would be something like this. As the association list is built in reverse order we need to reverse the parameter list.

(multiple-value-bind (rest s2 s1 leading)
    (mapcar (lambda (e) (cdr e))
            (bindat-unpack '((leading str 5)
                             (fill 3)
                             (s1 str 8)
                             (s2 str 8)
                             (rest str (eval (- (length data) bindat-idx 1))))
                           (concat data "x")))
            (insert "\n")
            (insert (format "[%s] [%s] [%s] [%s]" leading s1 s2 rest)))
Posted in Emacs | Leave a comment