NAME

App::SQLiteUtils - Utilities related to SQLite

VERSION

This document describes version 0.006 of App::SQLiteUtils (from Perl distribution App-SQLiteUtils), released on 2023-02-24.

DESCRIPTION

This distribution includes several utilities:

1. csv2sqlite

2. import-csv-to-sqlite

3. list-sqlite-columns

4. list-sqlite-tables

FUNCTIONS

import_csv_to_sqlite

Usage:

 import_csv_to_sqlite(%args) -> [$status_code, $reason, $payload, \%result_meta]

Import a CSV file into SQLite database.

This tool utilizes the sqlite3 command-line client to import a CSV file into SQLite database. It pipes the following commands to the sqlite3 CLI:

 .mode csv
 .import CSVNAME TABLENAME

where CSVNAME is the CSV filename and TABLENAME is the table name.

If CSV filename is not specified, will be assumed to be - (stdin).

If table name is not specified, it will be derived from the CSV filename (basename) with extension removed. - will become stdin. All non-alphanumeric characters will be replaced with _ (underscore). If filename starts with number, t prefix will be added. If table already exists, a suffix of _2, _3, and so on will be added. Some examples:

 CSV filename          Table name         Note
 ------------          ----------         ----
 -                     stdin
 -                     stdin_2            If 'stdin' already exists
 /path/to/t1.csv       t1
 /path/to/t1.csv       t1_2               If 't1' already exists
 /path/to/t1.csv       t1_3               If 't1' and 't1_2' already exist
 ./2.csv               t2
 report 2021.csv       report_2021
 report 2021.rev1.csv  report_2021

Note that the sqlite3 CLI client can be used non-interactively as well. You can pipe the commands to its stdin, e.g.:

 % echo -e ".mode csv\n.import /PATH/TO/FILE.CSV TABLENAME" | sqlite3 DB_FILE

But this utility gives you convenience of picking a table name automatically.

This function is not exported.

Arguments ('*' denotes required arguments):

• csv_file => filename (default: "-")

  (No description)

• db_file* => filename

  (No description)

• table => str

  (No description)

Returns an enveloped result (an array).

First element ($status_code) is an integer containing HTTP-like status code (200 means OK, 4xx caller error, 5xx function error). Second element ($reason) is a string containing error message, or something like "OK" if status is 200. Third element ($payload) is the actual result, but usually not present when enveloped result is an error response ($status_code is not 2xx). Fourth element (%result_meta) is called result metadata and is optional, a hash that contains extra information, much like how HTTP response headers provide additional metadata.

Return value: (any)

list_sqlite_columns

Usage:

 list_sqlite_columns(%args) -> any

See also the .schema and .fullschema meta-command of the sqlite3 CLI.

This function is not exported.

Arguments ('*' denotes required arguments):

• db_file* => filename

  (No description)

• table* => str

  (No description)

Return value: (any)

list_sqlite_tables

Usage:

 list_sqlite_tables(%args) -> any

See also the .tables meta-command of the sqlite3 CLI.

This function is not exported.

Arguments ('*' denotes required arguments):

• db_file* => filename

  (No description)

Return value: (any)

HOMEPAGE

Please visit the project's homepage at https://metacpan.org/release/App-SQLiteUtils.

SOURCE

Source repository is at https://github.com/perlancar/perl-App-SQLiteUtils.

SEE ALSO

App::DBIUtils

AUTHOR

perlancar <perlancar@cpan.org>

CONTRIBUTING

To contribute, you can send patches by email/via RT, or send pull requests on GitHub.

Most of the time, you don't need to build the distribution yourself. You can simply modify the code, then test via:

 % prove -l

If you want to build the distribution (e.g. to try to install it locally on your system), you can install Dist::Zilla, Dist::Zilla::PluginBundle::Author::PERLANCAR, Pod::Weaver::PluginBundle::Author::PERLANCAR, and sometimes one or two other Dist::Zilla- and/or Pod::Weaver plugins. Any additional steps required beyond that are considered a bug and can be reported to me.

COPYRIGHT AND LICENSE

This software is copyright (c) 2023, 2021 by perlancar <perlancar@cpan.org>.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

BUGS

Please report any bugs or feature requests on the bugtracker website https://rt.cpan.org/Public/Dist/Display.html?Name=App-SQLiteUtils

When submitting a bug or request, please include a test-file or a patch to an existing test-file that illustrates the bug or desired feature.

cpanm App::SQLiteUtils

perl -MCPAN -e shell
install App::SQLiteUtils