DBIx-XHTML_Table

 view release on metacpan or  search on metacpan

lib/DBIx/XHTML_Table.pm  view on Meta::CPAN

    push @$ref, $next;
    return $next;
}

# always returns an array ref
sub _refinate {
    my ($self,$ref) = @_;
    $ref = undef if ref($ref) eq 'ARRAY' && scalar( @$ref ) < 1;
    $ref = [@{$self->{'fields_arry'}}] unless defined $ref;
    $ref = [$ref] unless ref $ref eq 'ARRAY';
    return [map {$_ =~ /^\d+$/ ? $self->_lookup_name($_) || $_ : $_} @$ref];
}

sub _merge_attribs {
    my ($hash1,$hash2) = @_;

    return $hash1 unless $hash2;
    return $hash2 unless $hash1;

    return {%$hash2,%$hash1};
}

sub _lookup_name {
    my ($self,$index) = @_;
    return $self->{'fields_arry'}->[$index];
}

sub _lookup_index {
    my ($self,$name) = @_;
    return $self->{'fields_hash'}->{$name};
}

sub _reset_fields_hash {
    my $self = shift;
    my $i    = 0;
    $self->{fields_hash} = { map { $_ => $i++ } @{$self->{fields_arry}} };
}

# assigns a non-DBI supplied data table (2D array ref)
sub _do_black_magic {
    my ($self,$ref,$headers) = @_;
    croak "bad data" unless ref( $ref->[0] ) eq 'ARRAY';
    $self->{'fields_arry'} = $headers ? [@$headers] : [ @{ shift @$ref } ];
    $self->{'fields_hash'} = $self->_reset_fields_hash();
    $self->{'rows'}        = $ref;
}

# disconnect database handle if i created it
sub DESTROY {
    my ($self) = @_;
    unless ($self->{'keep_alive'}) {
        $self->{'dbh'}->disconnect if defined $self->{'dbh'};
    }
}

1;
__END__

=head1 NAME

DBIx::XHTML_Table - SQL query result set to XHTML table.

=head1 SYNOPSIS

  use DBIx::XHTML_Table;

  # database credentials - fill in the blanks
  my ($data_source,$usr,$pass) = ();

  my $table = DBIx::XHTML_Table->new($data_source,$usr,$pass);

  $table->exec_query("
      select foo from bar
      where baz='qux'
      order by foo
  ");

  print $table->output();

  # stackable method calls:
  print DBIx::XHTML_Table
    ->new($data_source,$usr,$pass)
    ->exec_query('select foo,baz from bar')
    ->output();

  # and much more - read on ...

=head1 DESCRIPTION

B<DBIx::XHTML_Table> is a DBI extension that creates an HTML
table from a database query result set. It was created to fill
the gap between fetching data from a database and transforming
that data into a web browser renderable table. DBIx::XHTML_Table is
intended for programmers who want the responsibility of presenting
(decorating) data, easily. This module is meant to be used in situations
where the concern for presentation and logic seperation is overkill.
Providing logic or editable data is beyond the scope of this module,
but it is capable of doing such.

=head1 CODE FREEZE

For the most part, no new functionality will be added to this module.
Only bug fixes and documentation corrections/additions. All new efforts
will be directed towards the rewrite of this distribution, B<DBIx::HTML>.

This distribution features a more flexible interface with fewer methods and
logically named argument parameters. At the core is an HTML attribute generator:

=over 4

=item * L<Tie::Hash::Attribute>

=back

Which is used by an HTML tag generator:

=over 4

=item * L<HTML::AutoTag>

=back

lib/DBIx/XHTML_Table.pm  view on Meta::CPAN

  # order is essential here
  my $headers = $sth->{'NAME'};
  my $rows    = $sth->fetchall_arrayref();

  # do something to $rows

  my $table = DBIx::XHTML_Table->new($rows,$headers);

If $headers is not supplied, then the first row from the
first argument will be shifted off and used instead.
While obtaining the data from a database is the entire
point of this module, there is nothing stopping you from
simply hard coding it:

  my $rows = [
     [ qw(Head1 Head2 Head3) ],
     [ qw(foo bar baz)       ],
     [ qw(one two three)     ],
     [ qw(un deux trois)     ]
  ];

  my $table = DBIx::XHTML_Table->new($rows);

And that is why $headers is optional.

=back

=head1 OBJECT METHODS

=over 4

=item B<exec_query>

  $table->exec_query($sql[,$bind_vars])

Pass the query off to the database with hopes that data will be 
returned. The first argument is scalar that contains the SQL
code, the optional second argument can either be a scalar for one
bind variable or an array reference for multiple bind vars:

  $table->exec_query('
      select bar,baz from foo
      where bar = ?
      and   baz = ?
  ',[$foo,$bar]);

exec_query() also accepts a prepared DBI::st handle:

  my $sth = $dbh->prepare('
      select bar,baz from foo
      where bar = ?
      and   baz = ?
  ');

  $table->exec_query($sth,[$foo,$bar]);

Consult the DBI documentation for more details on bind vars.

After the query successfully executes, the results will be
stored interally as a 2-D array. The XHTML table tags will
not be generated until the output() method is invoked.

=item B<output>

  $scalar = $table->output([$attribs])

Renders and returns the XHTML table. The only argument is
an optional hash reference that can contain any combination
of the following keys, set to a true value. Most of the
time you will not want to use this argument, but there are
three times when you will:

  # 1 - do not display a thead section
  print $table->output({ no_head => 1 });

This will cause the thead section to be suppressed, but
not the caption if you set one. The
column foots can be suppressed by not calculating totals, and
the body can be suppressed by an appropriate SQL query. The
caption and colgroup cols can be suppressed by not modifying
them. The column titles are the only section that has to be
specifically 'told' not to generate, and this is where you do that.

  # 2 - do not format the headers with ucfirst
  print $table->output({ no_ucfirst => 1 });

This allows you to bypass the automatic upper casing of the first
word in each of the column names in the table header. If you just
wish to have them displayed as all lower case, then use this
option, if you wish to use some other case, use map_head()

  # 3 - 'squash' the output HTML table
  print $table->output({ no_indent => 1 });

This will result in the output having no text aligning whitespace,
that is no newline(\n) and tab(\t) characters. Useful for squashing
the total number of bytes resulting from large return sets.

You can combine these attributes, but there is no reason to use
no_ucfirst in conjunction with no_head.

Note: versions prior to 0.98 used a two argument form:

  $scalar = $table->output([$sans_title,$sans_whitespace])

You can still use this form to suppress titles and whitespace,
but warnings will be generated.

HTML encoding of table cells is turned off by default, but can
be turned on via:

  $table->{encode_cells} = 1;

=item B<get_table>

  $scalar = $table->get_table([ {attribs} ])

Deprecated - use output() instead.

=item B<modify>

  $table->modify($tag,$attribs[,$cols])

This method will store a 'memo' of what attributes you have assigned
to various tags within the table. When the table is rendered, these
memos will be used to create attributes. The first argument is the
name of the tag you wish to modify the attributes of. You can supply
any tag name you want without fear of halting the program, but the
only tag names that are handled are <table> <caption> <thead> <tfoot>
<tbody> <colgroup> <col> <tr> <th> and <td>. The tag name will be
converted to lowercase, so you can practice safe case insensitivity.

The next argument is a reference to a hash that contains the
attributes you wish to apply to the tag. For example, this
sets the attributes for the <table> tag:

  $table->modify('table',{
     border => '2',
     width  => '100%'
  });

  # a more Perl-ish way
  $table->modify(table => {
     border => 2,
     width  => '100%',
  });

  # you can even specify CSS styles
  $table->modify(td => {
     style => 'color: blue; text-align: center',
  });

  # there is more than one way to do it
  $table->modify(td => {
     style => {
        color        => 'blue',
        'text-align' => 'center',
     }
  });

Each key in the hash ref will be lower-cased, and each value will be 
surrounded in quotes. Note that typos in attribute names will not
be caught by this module. Any attribute can be used, valid XHTML
attributes tend be more effective. And yes, JavaScript works too.

You can even use an array reference as the key values:

  $table->modify(td => {
     bgcolor => [qw(red purple blue green yellow orange)],
  }),

As the table is rendered row by row, column by column, the
elements of the array reference will be 'rotated'
across the <td> tags, causing different effects depending
upon the number of elements supplied and the number of
columns and rows in the table. The following is the preferred
XHTML way with CSS styles:

  $table->modify(th => {
     style => {
        background => ['#cccccc','#aaaaaa'],
     }
  });

See the set_row_color() and set_col_color() methods for more info.

The last argument to modify() is optional and can either be a scalar
representing a single column or area, or an array reference
containing multilple columns or areas. The columns will be
the corresponding names of the columns from the SQL query,
or their anticipated index number, starting at zero.
The areas are one of three values: HEAD, BODY, or FOOT.
The columns and areas you specify are case insensitive.

  # just modify the titles
  $table->modify(th => {
     bgcolor => '#bacaba',
  }, 'head');

  # only <td> tags in column FOO will be set
  $table->modify(td => {
     style => 'text-align: center'
  },'foo');

  # <td> tags for the second and third columns (indexes 1 and 2)
  $table->modify(td => {
     style => 'text-align: right'
  },[1,2]);

You cannot currently mix areas and columns in the same method call.
That is, you cannot set a specific column in the 'head' area,
but not the 'body' area. This _might_ change in the future, but
such specific needs are a symptom of needing a more powerful tool.

As of Version 1.10, multiple calls to modfiy() are inheritable.
For example, if you set an attribute for all <td> tags and set
another attribute for a specific column, that specific column
will inherit both attributes:

  $table->modify(td => {foo => 'bar'});
  $table->modify(td => {baz => 'qux'},'Salary');

In the preceding code, all <td> tags will have the attribute
'foo = "bar"', and the <td> tags for the 'Salary' column will
have the attributes 'foo = "bar"' and 'baz = "qux"'. Should
you not this behavior, you can 'erase' the unwanted attribute
by setting the value of an attribute to the empty string:

  $table->modify(td => {foo => 'bar'});
  $table->modify(td => {foo =>'', baz => 'qux'},'Salary');

Note the use of the empty string and not undef or 0. Setting
the value to undef will work, but will issue a warning if you
have warnings turned on. Setting the value to 0 will set the
value of the attribute to 0, not remove it.

A final caveat is setting the <caption> tag. This one breaks
the signature convention:

  $table->modify(tag => $value, $attrib);

Since there is only one <caption> allowed in an XHTML table,
there is no reason to bind it to a column or an area:

  # with attributes
  $table->modify(
     caption => 'A Table Of Contents',
     { align => 'bottom' }
  );

  # without attributes
  $table->modify(caption => 'A Table Of Contents');

The only tag that cannot be modified by modify() is the <col>
tag. Use add_col_tag() instead.

=item B<modify_tag>

  $table->modify_tag($tag,$attribs[,$cols])

Deprecated, use the easier to type modify() instead.

=item B<add_col_tag>

  $table->add_col_tag($cols)

Add a new <col> tag and attributes. The only argument is reference
to a hash that contains the attributes for this <col> tag. Multiple
<col> tags require multiple calls to this method. The <colgroup> tag
pair will be automatically generated if at least one <col> tag is
added.

Advice: use <col> and <colgroup> tags wisely, don't do this:

  # bad
  for (0..39) {
    $table->add_col_tag({
       foo => 'bar',
    });
  }

When this will suffice:

  # good
  $table->modify(colgroup => {
     span => 40,
     foo  => 'bar',
  });

You should also consider using <col> tags to set the attributes
of <td> and <th> instead of the <td> and <th> tags themselves,
especially if it is for the entire table. Notice the use of the
get_col_count() method in this example to span the entire table:

  $table->add_col_tag({
     span  => $table->get_col_count(),
     style => 'text-align: center',
  });

=item B<map_cell>

  $table->map_cell($subroutine[,$cols])

lib/DBIx/XHTML_Table.pm  view on Meta::CPAN

=item B<new>

Things with the stuff.

=item B<reset>

Stuff with the things.

=back

=head1 TAG REFERENCE

    TAG        CREATION    BELONGS TO AREA
+------------+----------+--------------------+
| <table>    |   auto   |       ----         |
| <caption>  |  manual  |       ----         |
| <colgroup> |   both   |       ----         |
| <col>*     |  manual  |       ----         |
| <thead>    |   auto   |       head         |
| <tbody>    |   auto   |       body         |
| <tfoot>    |   auto   |       foot         |
| <tr>       |   auto   |  head,body,foot    |
| <td>       |   auto   |       body         |
| <th>       |   auto   |  head,body,foot    |
+------------+-------------------------------+

 * All tags use modify() to set attributes
   except <col>, which uses add_col_tag() instead

=head1 BUGS

If you have found a bug, typo, etc. please visit Best Practical Solution's
CPAN bug tracker at http://rt.cpan.org:

E<lt>http://rt.cpan.org/NoAuth/Bugs.html?Dist=DBIx-XHTML_TableE<gt>

or send mail to E<lt>bug-DBIx-XHTML_Table#rt.cpan.orgE<gt>

(you got this far ... you can figure out how to make that
a valid address ... and note that i won't respond to bugs
sent to my personal address any longer)

=head1 ISSUES

=over 4

=item Problems with 'SELECT *'

Users are recommended to avoid 'select *' and instead
specify the names of the columns. Problems have been reported
using 'select *' with SQLServer7 will cause certain 'text' type 
columns not to display. I have not experienced this problem
personally, and tests with Oracle and MySQL show that they are not
affected by this. SQLServer7 users, please help me confirm this. :)

=item Not specifying <body> tag in CGI scripts

I anticipate this module to be used by CGI scripts, and when
writing my own 'throw-away' scripts, I noticed that Netscape 4
will not display a table that contains XHTML tags IF a <body>
tag is NOT found. Be sure and print one out.

=back

=head1 CREDITS

Briac [OeufMayo] PilprE<eacute> for the name.

Mark [extremely] Mills for patches and suggestions.

Jim Cromie for presenting the whole spreadsheet idea.

Stephen Nelson for documentation/code corrections.

Matt Sergeant for DBIx::XML_RDB.

Aaron [trs80] Johnson for convincing me into writing add and drop cols.

Richard Piacentini and Tim Alexander for recommending DBIx::Password and Apache::DBI compatability and Slaven Rezic for recommending using UNIVERSAL::isa().

Perl Monks for the education.

=head1 SEE ALSO 

DBI

=head1 AUTHOR 

Jeff Anderson

=head1 COPYRIGHT

Copyright 2017 Jeff Anderson.

This program is free software; you can redistribute it and/or modify it
under the terms of the the Artistic License (2.0). You may obtain a
copy of the full license at:

L<http://www.perlfoundation.org/artistic_license_2_0>

Any use, modification, and distribution of the Standard or Modified
Versions is governed by this Artistic License. By using, modifying or
distributing the Package, you accept this license. Do not use, modify,
or distribute the Package, if you do not accept this license.

If your Modified Version has been derived from a Modified Version made
by someone other than you, you are nevertheless required to ensure that
your Modified Version complies with the requirements of this license.

This license does not grant you the right to use any trademark, service
mark, tradename, or logo of the Copyright Holder.

This license includes the non-exclusive, worldwide, free-of-charge
patent license to make, have made, use, offer to sell, sell, import and
otherwise transfer the Package with respect to any patent claims
licensable by the Copyright Holder that are necessarily infringed by the
Package. If you institute patent litigation (including a cross-claim or
counterclaim) against any party alleging that the Package constitutes
direct or contributory patent infringement, then this Artistic License
to you shall terminate on the date that such litigation is filed.