File Coverage

=head1 NAME

App::Access2CSV::Exporter - Export the tables of a Microsoft Access database to CSV files

=head1 VERSION

Version 0.001.0

=head1 SYNOPSIS

        use App::Access2CSV::Exporter;

        # 1. The simplest case: every table, into the current folder
        my $exporter = App::Access2CSV::Exporter->new();
        my $status = $exporter->run('shop.accdb');   # 0 = all OK, 1 = some failed

        # 2. Some tables, into a folder, for Excel, replacing old files
        my $exporter = App::Access2CSV::Exporter->new(
                output_dir => 'exports',
                tables     => ['Customers', 'Orders'],
                encoding   => 'utf8-bom',
                overwrite  => 1,
        );
        $exporter->run('shop.accdb');

        # 3. Only look: print the table list and row counts, write nothing
        App::Access2CSV::Exporter->new(dry_run => 1, show_counts => 1)->run('shop.accdb');

        # 4. Inside a larger program: no progress lines, a log, and full
        #    error handling
        use Log::Abstraction;

        my $exporter = App::Access2CSV::Exporter->new({
                output_dir => '/srv/exports',
                progress   => 0,
                logger     => Log::Abstraction->new(logger => '/var/log/export.log'),
        });
        my $status = eval { $exporter->run('/data/shop.accdb') };
        if(!defined $status) {
                die "Nothing was exported: $@";       # for example, the file is missing
        } elsif($status == 1) {
                warn "Some tables were not exported; see the log\n";
        }

=head1 DESCRIPTION

This module does the real work of the C<access2csv> program.  It writes
one CSV file for each table of a Microsoft Access database.

It runs three programs from the B<mdbtools> package: C<mdb-tables> (to
list the tables), C<mdb-export> (to get each table as CSV) and, only when
row counts are wanted, C<mdb-count>.  They must be in your C<PATH>.

Access's own internal tables (names starting with C<MSys>, C<USys> or
C<~>) are skipped.

Each file is first written to a hidden temporary file in the output
folder, and renamed to its real name only when it is complete.  So a
failed export never leaves a half-written CSV file, and an old file is
only replaced by a complete new one.  New files get the usual
permissions (0666 minus your umask).

An exporter can be used for more than one C<run>.  Each C<run> starts
again with the same file names, so running twice gives the same files.

The mdbtools programs are looked up only in absolute C<PATH> folders, so
a program planted in the current folder is never run, and they are
started with a cleaned environment (see L<App::Access2CSV/SECURITY>).
The module works under taint mode (C<perl -T>).  Table names are
printed and logged with control characters escaped, so a hostile name
cannot send escape sequences to your terminal.

Table names and the database path are handed to mdbtools as separate
arguments, never through a shell, and after a C<--> marker.  So names
containing shell characters (C<; | E<gt> $( )>), spaces or newlines, or
starting with C<->, are always treated as names, never as commands or
options.  A table name can never place a file outside the output
folder: C</> and C<\> are replaced, and names cannot start with a dot.

An existing entry at the target name - including a symbolic link, even a
broken one - counts as "already exists".  With C<overwrite>, the link
itself is replaced; the file it pointed to is never written.

The rules for file names are described in
L<App::Access2CSV/How the CSV files are named>.

=head1 ENCODING

=over 4

=item * B<CSV data.>  mdbtools gives UTF-8.  With C<encoding> set to
C<utf8> or C<utf8-bom> the bytes are copied exactly, so every character,
including emoji and non-Latin scripts, is kept.  C<utf8-bom> also writes
the three-byte UTF-8 "byte order mark" first, which Microsoft Excel
needs.  With C<cp1252>, each line is converted to Windows-1252; if a line
has a character that Windows-1252 does not have (for example Greek,
Chinese or an emoji), that table fails and nothing is written for it.

=item * B<Database path and output_dir.>  These are passed to the operating
system unchanged.  Give them as byte strings (the form you get from
C<@ARGV> or C<readdir>).  Non-ASCII names work on systems whose file names
are UTF-8, such as Linux and macOS.

=item * B<Table names> (in C<tables>).  They are compared with the names
that C<mdb-tables> prints, which are UTF-8 bytes.  So give UTF-8 byte
strings, not decoded Perl character strings.  If you have a decoded
string, use C<Encode::encode('UTF-8', $name)> first.  The CSV file name is
made from the same bytes, so non-ASCII names and emoji are kept.

=item * B<Messages.>  All messages are plain ASCII English.

=back

=head1 COMMON PITFALLS

=over 4

=item * B<undef means "use the default", not "false".>  In C<new>,
C<< overwrite => undef >> is the same as not giving C<overwrite> at all.
To switch something off, give C<0>.

=item * B<An empty table list exports nothing.>  C<< tables => undef >>
(or no C<tables>) means "all tables".  C<< tables => [] >> means "no
tables": nothing is exported, and C<run> returns 0.

=item * B<Table names are case-sensitive.>  C<'orders'> does not match the
table C<Orders>.  Names that do not match any table give a warning.

=item * B<A failing logger does not stop the export.>  If the logger dies
(for example, its disk is full), C<run> warns once with "Cannot write to
the log", stops logging for this exporter, and carries on exporting.

=item * B<run can croak.>  C<run> returns 1 when some tables fail, but it
croaks (throws an exception) when nothing can be exported at all: the
database is missing or unreadable, mdbtools is not installed, or the
output folder cannot be created.  Wrap C<run> in C<eval> if your program
must keep going.

=item * B<run may change the show_counts setting.>  If C<show_counts> is
on but C<mdb-count> cannot be found, C<run> warns and switches
C<show_counts> off for this exporter.

=item * B<Settings are copied, not shared.>  C<new> makes its own copy of
the C<tables> list; changing your array later has no effect.  Settings are
not merged in depth: a new C<tables> list replaces the default completely.

=item * B<Warnings go through carp.>  Failed tables and unknown table names
are reported with C<carp>, so they appear on standard error (or in your
C<$SIG{__WARN__}> handler) even when a logger is given.

=item * B<Load the module with use, not require.>  Protection of the
private methods is set up at compile time.  After a run-time
C<require> Perl prints "Too late to run CHECK block" and the protection is
missing.

=back

=head1 METHODS

=head2 new

=head3 Purpose

Make a new exporter with your settings.  Nothing is checked on disk yet.

=head3 Arguments

Named arguments, either as a list or as one hash reference.  All of them
are optional.  An argument whose value is C<undef> is ignored, so its
default is used.

=over 4

=item C<output_dir> - the folder for the CSV files.  Default: the current folder.

=item C<tables> - an array reference of table names to export.  Default:
all tables.  An empty array means no tables.

=item C<overwrite> - true to replace CSV files that already exist.  Default: false.

=item C<verbose> - true to log extra detail.  Default: false.

=item C<dry_run> - true to only print what would be written.  Default: false.

=item C<show_counts> - true to report row counts (needs C<mdb-count>).  Default: false.

=item C<progress> - true to print C<[n/total] table> lines to standard
error.  Default: true.

=item C<encoding> - C<utf8>, C<utf8-bom> or C<cp1252>.  Default: C<utf8>.

=item C<logger> - an object with C<debug>, C<info> and C<warn> methods,
such as a L<Log::Abstraction> object.  Default: no logging.

=item C<language> - a language code such as C<en> for messages.  Default:
taken from the locale (see L<App::Access2CSV::I18N>).

=back

=head3 Returns

A new C<App::Access2CSV::Exporter> object.

=head3 Side Effects

None.  Your C<$@>, C<$!> and C<$_> are left as they were.

=head3 Usage

        my $exporter = App::Access2CSV::Exporter->new({ dry_run => 1 });

=head3 EXAMPLE

        # Export two tables as Windows-1252, replacing older files, with a log
        my $exporter = App::Access2CSV::Exporter->new(
                output_dir => 'out',
                tables     => ['Customers', 'Orders'],
                encoding   => 'cp1252',
                overwrite  => 1,
                logger     => Log::Abstraction->new(logger => 'export.log'),
        );

=head3 API SPECIFICATION

=head4 Input

        {
                output_dir  => { type => 'string', min => 1, optional => 1 },
                tables      => { type => 'arrayref', element_type => 'string', optional => 1 },
                overwrite   => { type => 'boolean', optional => 1 },
                verbose     => { type => 'boolean', optional => 1 },
                dry_run     => { type => 'boolean', optional => 1 },
                show_counts => { type => 'boolean', optional => 1 },
                progress    => { type => 'boolean', optional => 1 },
                encoding    => { type => 'string', memberof => ['utf8', 'utf8-bom', 'cp1252'], optional => 1 },
                logger      => { type => 'object', can => ['debug', 'info', 'warn'], optional => 1 },
                language    => { type => 'string', optional => 1 },
        }

Valid and invalid values (tested in F<t/domain.t>):

        output_dir  valid:   any path of 1 character or more ("0" is valid),
                             including non-ASCII names given as UTF-8 bytes
                    invalid: "" (below the minimum), references
        tables      valid:   undef (all tables), [] (no tables), one name,
                             many names; names are matched byte for byte
                    invalid: anything but an array reference; elements
                             that are references
        booleans    valid:   exactly 1 true TRUE yes on  /  0 false FALSE no off
        (overwrite, invalid: everything else, e.g. "", 2, -1, "True", "Yes",
         verbose,            "0.0", " 1"
         dry_run, show_counts, progress)
        encoding    valid:   exactly utf8, utf8-bom, cp1252
                    invalid: other spellings (UTF8, utf-8, CP1252, " utf8")
        logger      valid:   an object with debug, info and warn methods
                    invalid: an object missing any of them, a plain hash,
                             a string
        language    valid:   any string; "" means "use the environment"
                    invalid: references

=head4 Output

        {
                type => 'object',
                isa  => 'App::Access2CSV::Exporter',
        }

=head3 MESSAGES

All are fatal, and read "Invalid setting: REASON".  The REASON part
comes from L<Params::Validate::Strict> and is not translated; for example:

        +--------------------------------------+------------------------------+-----------------------------+
        | Message                              | Meaning                      | What to do                  |
        +--------------------------------------+------------------------------+-----------------------------+
        | Invalid setting: Unknown parameter   | X is not a known setting     | Remove X, or fix its        |
        |  'X'                                 |                              | spelling                    |
        | Invalid setting: Parameter           | This encoding is not         | Use utf8, utf8-bom or       |
        |  'encoding' (X) must be one of utf8, | supported                    | cp1252                      |
        |  utf8-bom, cp1252                    |                              |                             |
        | Invalid setting: Parameter 'logger'  | logger is not an object      | Give a logger object        |
        |  must be an object                   |                              |                             |
        | Invalid setting: Parameter 'tables'  | tables is not an array       | Give an array reference     |
        |  must be ...                         | reference                    |                             |
        +--------------------------------------+------------------------------+-----------------------------+

=cut

=head2 run

=head3 Purpose

Export the selected tables of one database to CSV files.  In dry-run mode,
only print what would be exported.

=head3 Arguments

=over 4

=item C<database> (string, required) - the path of the C<.mdb> or C<.accdb> file.
The name is taken literally: C<-> is a file called C<->.  (Reading from
standard input is a feature of the command-line program; see
L<App::Access2CSV/Reading the database from standard input>.)

=back

You can give it on its own, C<< $exporter->run('shop.accdb') >>, or as a
hash reference, C<< $exporter->run({ database => 'shop.accdb' }) >>.

=head3 Returns

C<0> if every selected table was exported, or in dry-run mode.
C<1> if at least one table was not exported (the others were).

=head3 Side Effects

=over 4

=item * Creates the output folder if needed (not in dry-run mode, and
not when no table is selected).

=item * Writes one CSV file per table (not in dry-run mode).

=item * Prints progress lines to standard error, if C<progress> is on.

=item * Prints the dry-run list to standard output, in dry-run mode.

=item * Sends messages to the logger, if there is one.

=item * Warns (with C<carp>) about each table that failed, about unknown
names in C<tables>, and about a missing C<mdb-count>.

=item * Croaks, before writing anything, if the database cannot be read, a
needed mdbtools program is missing, C<mdb-tables> fails, or the output
folder cannot be created.

=item * Switches C<show_counts> off for this exporter if C<mdb-count> is
missing.

=item * While it runs, handles the signals INT, QUIT, TERM and HUP (only
those you have not set a handler for yourself; they are restored when
C<run> returns).  Any of them stops the run: the table being exported is
discarded - its temporary file deleted, any old CSV file left as it was -
no further table is started, and C<run> croaks.  Pressing Ctrl-C, which
also stops the mdbtools program, has the same effect.

=item * Leaves your C<$@>, C<$!>, C<$?>, C<$_>, C<$.> and any pending C<alarm>
as they were (except that a croak sets C<$@> in your C<eval>, as usual).

=back

=head3 Usage

        exit $exporter->run('shop.accdb');

=head3 EXAMPLE

        my $exporter = App::Access2CSV::Exporter->new(output_dir => 'out');

        # eval catches the fatal errors; the return value covers the rest
        my $status = eval { $exporter->run('shop.accdb') };
        if(!defined $status) {
                print STDERR "Nothing was exported: $@";
        } elsif($status) {
                print STDERR "Some tables failed; see the warnings above\n";
        } else {
                print "Done\n";
        }

=head3 API SPECIFICATION

=head4 Input

        {
                database => {
                        type     => 'string',
                        min      => 1,
                        optional => 0,
                },
        }

Valid and invalid values (tested in F<t/domain.t>):

        database    valid:   a readable regular file
                    invalid: "" (below the 1-character minimum), undef,
                             a missing file, a folder, a device or FIFO,
                             an unreadable file
                    edges:   each part of the path may be up to 255 bytes;
                             256 gives "File name too long"

The table names that mdbtools reports are data, not arguments, but they
have limits of their own:

        length      a CSV file name is the table name plus ".csv", and the
                    file system limits file names, so table names up to 251
                    units work and longer ones fail (that table only).  The
                    unit depends on the file system: Linux counts bytes (125
                    u-umlauts, 2 bytes each, fit; 126 do not), macOS counts
                    characters (up to 251 of any letter fit).  Access allows
                    at most 64 characters, well within either limit.
        characters  non-ASCII letters, emoji, joined emoji, combining marks
                    and right-to-left text are kept byte for byte.
                    Characters that are unsafe in file names - including
                    invisible text-direction controls such as U+202E - are
                    replaced by "_".
        collisions  the first name has no suffix, then _2, _3, ... _10 ...
        cp1252      U+00FF and the Euro sign convert; U+0100 and above
                    (except the few Windows-1252 symbols), the C1 controls
                    U+0080-U+009F and emoji make the table fail.

=head4 Output

        {
                type => 'integer',
                min  => 0,
                max  => 1,
        }

=head3 MESSAGES

"fatal" means C<run> croaks and nothing is exported.  "per table" means
only that table fails; C<run> warns, logs, and carries on.

        +-----------------------------------------+------------------------------+-------------------------------+
        | Message                                 | Meaning                      | What to do                    |
        +-----------------------------------------+------------------------------+-------------------------------+
        | Interrupted by SIGx: stopped, and the   | Ctrl-C, Ctrl-\\, kill or a   | Run again; tables finished    |
        |  table being exported was discarded     | closed terminal stopped the  | before the interruption are   |
        |  (fatal)                                | run                          | complete                      |
        | run() must be called on an object       | run was called on the class  | Call new() first, then run()  |
        |  created by new() (fatal)               | or on something that is not  | on the object it returns      |
        |                                         | an exporter                  |                               |
        | Cannot read database F: E (fatal)       | F does not exist, or cannot  | Check the path                |
        |                                         | be reached; E is the reason  |                               |
        |                                         | from the operating system    |                               |
        | Database F is not a regular file (fatal)| F is a folder or a device    | Give the database file        |
        | Database F is not readable (fatal)      | No permission to read F      | Fix the permissions           |
        |                                         | (never happens for root)     |                               |
        | Required program not found in PATH: P   | mdbtools is not installed,   | Install mdbtools, or fix PATH |
        |  (fatal)                                | or not in PATH               |                               |
        | mdb-tables failed with exit status N: E | mdbtools cannot read the     | Check that F is a real Access |
        |  (fatal)                                | file                         | database                      |
        | Cannot create output directory D: E     | The folder cannot be made;   | Check permissions and path    |
        |  (fatal)                                | E is the reason for D itself |                               |
        |                                         | (e.g. "Not a directory" when |                               |
        |                                         | a file is in the way)        |                               |
        | Cannot count the rows of T: E (warning) | mdb-count failed for table T | The table is still exported   |
        |                                         | (only with show_counts)      | (dry run: count shown as "?") |
        |  ... mdb-count printed no number: "X"   | mdb-count's answer was not   | As above; X shows what it     |
        |                                         | just a number                | printed                       |
        | Tables not found in database: T         | Names in tables are not in   | Check spelling and case       |
        |  (warning)                              | the database                 |                               |
        | mdb-count not found in PATH; row counts | show_counts is on, but       | Install mdb-count, or turn    |
        |  are unavailable (warning)              | mdb-count is missing         | show_counts off               |
        | FAILED: T: E (warning, logged)          | Table T was not exported,    | See E, one of the messages    |
        |                                         | because of E                 | below                         |
        | Output file already exists: F (use      | F exists (a symbolic link,   | Set overwrite, or use another |
        |  --overwrite to replace it) (per table) | even a broken one, counts)   | output_dir                    |
        |                                         | and overwrite is off         |                               |
        | mdb-export failed with exit status N: E | mdbtools could not read this | Check the table in Access     |
        |  (per table)                            | table                        |                               |
        | P was killed by signal N (per table,    | The program was stopped from | Check memory and system       |
        |  or fatal for mdb-tables)               | outside                      | limits                        |
        | P could not be run: E (per table, or    | The program was found but    | Check its permissions and     |
        |  fatal for mdb-tables)                  | could not be started         | that it is a real program     |
        | Table T, line N: cannot be represented  | A character is not in        | Use utf8 or utf8-bom          |
        |  in cp1252 (per table)                  | Windows-1252                 |                               |
        | Table T, line N: output of mdb-export   | mdbtools gave bytes that are | Check the MDB_ICONV setting   |
        |  is not valid UTF-8 (per table)         | not UTF-8                    |                               |
        | Cannot write F: E (per table)           | The file could not be        | Check permissions and free    |
        |                                         | written or renamed into place| disk space                    |
        | Cannot write to the log: E (warning,    | The logger failed.  Exports  | Check the log's disk or       |
        |  once)                                  | go on; logging stops         | destination                   |
        +-----------------------------------------+------------------------------+-------------------------------+

=head3 PSEUDOCODE

        check the argument
        stop (croak) unless the database is a readable file
        find mdb-tables and mdb-export (croak if missing),
             and mdb-count if row counts are wanted (warn if missing)
        forget the file names given out by any earlier run
        tables := the sorted user tables, filtered by "tables"
                  (warn about names that are not found)
        if dry run:
                print the table -> file list (row count "?" with a warning
                      if a count fails)
                return 0
        if there are tables to export:
                create the output folder (croak if that fails)
        for each table:
                print "[n/total] table" if progress is on
                try to export the table
                if that failed: warn, log, and count the failure
                (a failed row count after the file is in place is only a
                 warning; the table still counts as exported)
        log the summary
        return 1 if any table failed, else 0

=cut