NAME

Text::ANSITable::SQLStyleSheet - Pretty tables with SQL-generated styles

SYNOPSIS

  use Text::ANSITable::SQLStyleSheet;
  use DBI;

  my $dbh = DBI->connect('dbi:SQLite:dbname=:memory:');

  my $sth = $dbh->prepare(q{
    WITH RECURSIVE
    ints AS (
      SELECT 1 AS value
      UNION ALL
      SELECT value + 1 AS value FROM ints
    )
    SELECT value FROM ints LIMIT 10
  });

  $sth->execute();

  my $t = Text::ANSITable::SQLStyleSheet->from_sth($sth, q{
    SELECT
      *,
      JSON_OBJECT(
        'fgcolor',
        PRINTF(
          '%02x%02x%02x',
          ABS(RANDOM()) % 256,
          ABS(RANDOM()) % 256,
          ABS(RANDOM()) % 256
        )
      ) AS __row_style
    FROM
      data
  });

  # a table with integers in random colours.
  print $t->draw;

DESCRIPTION

When you frequently look at report tables from SQL queries in your terminal and wish for a little bit of extra style, this module allows you to specify styles as (part of) SQL queries.

You can do this either quick and dirty in your data queries, or by letting this module store your data temporarily in an in-memory SQLite database before your "style sheet" is applied.

CONSTRUCTOR

from_sth( $sth, $query )

Fetches all rows from $sth into a data table in a temporary in-memory SQLite database and then executes $query in that database.

The $query argument is optional; if omitted, data and styles are taken directly from $sth as if you had called

  from_sth($sth, 'SELECT * FROM data')

but no temporary database is created. This tight coupling between data and style computation can be more convenient in some situations.

NOTE: While $query will always be executed against SQLite, this module does not care which database driver $sth is associated with. It does try to create the temporary table with the right type affinity so SQLite does not suddenly treat integers as strings or otherwise, but that depends on cooperation on part of the driver.

The style sheet query is expected to add columns named __column_style, __row_style, __cell_style to the result set. Values in these columns are JSON-encoded objects, see the template below for reference. The structure of the JSON objects mirrors the configurable styles that Text::ANSITable supports. Styles with a NULL value are ignored and are not passed to Text::ANSITable. All style columns are optional. Column styles are taken only from the first row.

In addition to the styles supported by Text::ANSITable, this module supports an additional pseudo-style for cells named value. If specified, the value overrides the value that would otherwise be used for the cell. This allows you, for instance, to work with the full data in the "style sheet", and abbreviate or otherwise transform it for display.

The $query argument can also be a CODE reference. The code will be executed after the temporary database has been created with the database handle as only argument, and is expected to return an executed statement handle. That gives callers a chance to install additional functions onto the handle or pass arguments to the query.

  my $t = Text::ANSITable::SQLStyleSheet->from_sth($sth, sub {
    my ($dbh) = @_;

    $dbh->sqlite_create_function('truncate', 2, sub {
      my ($string, $max_length) = @_;
      ...
    });

    my $sth = $dbh->prepare(q{
      WITH 
      args AS (
        SELECT ? AS max_length
      )
      SELECT
        ... truncate(long_text, args.max_length) ...
      FROM  
        data
          JOIN args
    });

    $sth->execute( 100 );

    return $sth;
  });

The return value is a Text::ANSITable object.

TEMPLATE FOR SQLITE

  WITH 
  data AS (
    SELECT
    ...
  )
  SELECT
    *
    ,
    JSON_OBJECT(
      'column_name',
      JSON_OBJECT(
        -- 'fgcolor', NULL,
        -- 'bgcolor', NULL,
        -- 'align', NULL,
        -- 'valign', NULL,
        -- 'formats', NULL
        -- pseudo-style not passed to Text::ANSITable
        -- 'value', NULL 
      )
      ,
      'other_column',
      JSON_OBJECT(
        ...
      )
    ) AS __cell_style
    ,
    JSON_OBJECT(
      -- 'align', NULL,
      -- 'valign', NULL,
      -- 'height', NULL,
      -- 'vpad', NULL,
      -- 'tpad', NULL,
      -- 'bpad', NULL,
      -- 'fgcolor', NULL,
      -- 'bgcolor', NULL
    ) AS __row_style
    ,
    JSON_OBJECT(
      'column_name',
      JSON_OBJECT(
        -- 'align', NULL,
        -- 'valign', NULL,
        -- 'pad', NULL,
        -- 'lpad', NULL,
        -- 'rpad', NULL,
        -- 'width', NULL,
        -- 'formats', NULL,
        -- 'fgcolor', NULL,
        -- 'bgcolor', NULL,
        -- 'type', NULL,
        -- 'wrap', NULL
      )
      ,
      'other_column',
      JSON_OBJECT(
        ...
      )
    ) AS __column_style
  FROM
    data

TODO

Unfortunately https://github.com/DBD-SQLite/DBD-SQLite/issues/36 affects this module when your data query (the $sth handle you pass in) is executed against a SQLite database. The columns in the temporary database might then be associated with the wrong column affinity, which can result in odd behavior in your style sheet query.

BUG REPORTS

• https://github.com/hoehrmann/Text-ANSITable-SQLStyleSheet/issues

• mailto:bug-Text-ANSITable-SQLStyleSheet@rt.cpan.org

• http://rt.cpan.org/NoAuth/Bugs.html?Dist=Text-ANSITable-SQLStyleSheet

SEE ALSO

  * Text::ANSITable

AUTHOR / COPYRIGHT / LICENSE

  Copyright (c) 2018 Bjoern Hoehrmann <bjoern@hoehrmann.de>.
  This module is licensed under the same terms as Perl itself.