[San-Diego-pm] Perl Meeting on Monday, May 9th

[Tkil]

A few questions and answers that came up at tonights meeting:

------------------------------------------------------------------------

1. What do we do about broken links that start with

     http://www.perl.com/CPAN-local/...

The old CPAN mirror setup worked so that if you just used

  http://www.perl.com/CPAN/

It would redirect you to another mirror in an attempt to spread out
the load over the replicated archive.  This sometimes ended up not
working, and other times being very sub-optimal.  So more than a few
people got into the habit of using the "CPAN-local" link on perl.com,
which would always take you to the copy of the repository that lived
on perl.com itself.  Since that site was owned and funded by O'Reilly,
it had good connectivity and bandwidth.

Apparently that's been moved around a bit.  CPAN activities now seem
to be consolidated under

  http://cpan.org/

And they have a pretty zippy copy of the repository right there.  Joel
pointed out some dead links like:

  http://www.perl.com/CPAN-local/modules/index.html
  http://www.perl.com/CPAN-local/scripts/index.html
  http://www.perl.com/CPAN-local/ports/index.html
  http://www.perl.com/CPAN-local/src/index.html

Just looking at the front page of cpan.org, it seems that those have
been transplanted to:

  http://cpan.org/modules/index.html
  http://cpan.org/scripts/index.html
  http://cpan.org/ports/index.html
  http://cpan.org/src/index.html

So that takes care of that.

Another resource to keep handy is:

  http://search.cpan.org/

------------------------------------------------------------------------

2. How can a perl program discover the names of columns in SQL tables
   (or, more generally, the column names returned by a given query)?

Answer: Use the $statement_handle->{NAME_lc} property.  More details
are in "perldoc DBI".  The only hiccup is that it returns an array
ref; this means that you have to unpack it, and you should probably
make a copy of it before you modify it.

Example:

  sub columns_in_table ( $ $ )
  {
    my ( $dbh, $table ) = @_;
    my $sth = $dbh->prepare( "SELECT * FROM $table" );
    my $col_names_aref = $sth->{NAME_lc};
    return @$col_names_aref;
  }

  my $dbh = DBI->connect( ... );
  my @employee_cols = columns_in_table( 'employee' );

This is the DBI abstraction over a "reflection" capability present in
most RDBMSes: they usually have "metadata" tables that describe
tables, columns in tables, constraints on those columns, etc.
Unfortunately, that "system catalog" information is not standardized,
so every RDBMS does it slightly differently.  Using the DBI methods
can isolate a programmer from that.

Using DBI also means that you can get the column names from arbitrary
queries:

  my $sql = <<SQL
  SELECT a.foo, b.baz, AVG( b.fnord ) AS alert_level
    FROM my_table a, other_table b
    WHERE a.foo_id = b.foo_id
    GROUP BY a.foo, b.baz
  SQL

  my $sth = $dbh->prepare( $sql );

  my @cols = @{ $sth->{NAMES_lc} };
  # @cols should now have: "foo", "baz", "alert_level"

Which is something you'd be hard-pressed to do with the system
catalog.

Joel also mentioned filtering (as regards to which columns to show).
You can use the array of column names to figure out which indexes to
display, but if you're not going to display a column, why fetch it at
all?  That is, don't put it in the SELECT in the first place.

In extreme cases you can even get a performance win by not fetching
unnecessary data (especially if those columns are wide; an example
might be fetching all the contents of posts off a "web bbs" system but
not actually displaying those contents -- that's a lot of I/O that is
for naught.)

------------------------------------------------------------------------

3. How can you access HTML form controls by control name from
   JavaScript, when that name is itself stored in a separate variable?
   (This is similar to the problem of "variable that names a variable"
   in perl, also known as symbolic refs; frowned upon, but in a
   language such as JavaScript that (SFAIK) lacks proper
   references...)

JavaScript treats all objects (including forms) as dictionaries; while
you can put a literal name after a dot to index into such a
dictionary, you can also use square brackets with a string value.
That is, these two lines have the same effect:

  var control = document.my_form.my_widget;
  var control = document.my_form["my_widget"];

For a simple example, see

  http://scrye.com/~tkil/pm/js-by-name.html

I use this function to toggle the state of the named control within
form "f1".

  function toggle( name )
  {
      var control = document.f1[name];
      control.checked = ! control.checked;
  }

You might have noticed that you can even parameterize away the form
name; since I needed a more general toggle for my second example
there, I made "toggle2" which relies on the fact that all four of
these (executable) lines have the same effect:

  // same as above
  var control = document.my_form.my_widget;
  var control = document.my_form["my_widget"];

  // parameterize away the form name, too, if you like.
  var control = document["my_form"].my_widget;
  var control = document["my_form"]["my_widget"];

Here's "toggle2":

  function toggle2( form, name )
  {
      var control = document[form][name];
      control.checked = ! control.checked;
  }

You can use any sort of string construction to build the name; an
obvious use is working with matrices or other highly repetitive
structures.  As an example, I built up a 3x3 matrix with checkboxes
named m(x)(y):

  m00 m10 m20
  m01 m11 m21
  m02 m12 m22

Then I added buttons that flip a row (by calling "flip( 'row', 0 )"
for row 0, or "flip( 'col', 0 )" for flipping column 0):

  <tr>
    <td><input type="checkbox" name="m00" /></td>
    <td><input type="checkbox" name="m10" /></td>
    <td><input type="checkbox" name="m20" /></td>
    <td><input type="button" value="flip row"
               onClick="javascript:flip('row','0')" /></td>
  </tr>

Last, we need to implement the function "flip" that builds up each
control name and toggles it within form "f2":

  function flip( dir, which )
  {
      var i, name;
      for ( i = 0; i < 3; ++i )
      {
          if ( dir == "row" )
          {
              name = "m" + i + which;
          }
          else
          {
              name = "m" + which + i;
          }
          toggle2( "f2", name );
      }
  }

Finally, you can also iterate through all the elements in an object /
dictionary with the "for ( i in ... )" syntax.  This seems to be a
nice reference on this sort of javascript fun:

  http://www.comptechdoc.org/independent/web/cgi/javamanual/javaloops.html

(And I'm by no means a DHTML expert; if others want to comment or
correct this example, please feel free.  The examples were tested
under IE6 (XP Pro SP2), FireFox 1.0.3 (Linux), and Safari 1.3 (OSX
10.3); only issue was the rendering of the "flip col" button on
Safari, but I was abusing it anyway...).

Cheers,
t.