NAME Pye::SQL - Log with Pye into MySQL, PostgreSQL or SQLite SYNOPSIS use Pye::SQL; my $pye = Pye::SQL->new( db_type => 'mysql', # or 'pgsql' or 'sqlite' database => 'my_log_database', table => 'myapp_logs' ); # now start logging $pye->log($session_id, "Some log message", { data => 'example data' }); # inspect the logs from the command line pye -b SQL -t mysql -d my_log_database -T myapp_logs DESCRIPTION This package provides a relational SQL backend for the Pye logging system. It currently supports MySQL, PostgreSQL and SQLite. All of these database systems will require prior creation of the target database and table. Read on for notes and suggestions for each supported database system. USING THE pye COMMAND LINE UTILITY The pye command line utility used to inspect logs supports basic options that are consistent across all backends. Anything else you provide is passed to the "new( %options )" constructor, MySQL When creating a table for logs, use something like this: CREATE TABLE logs ( session_id VARCHAR(60) NOT NULL, date DATETIME(6) NOT NULL, text TEXT NOT NULL, data TEXT ); CREATE INDEX logs_per_session ON logs (session_id); For the "session_id" and "text" columns, note that the data type definition is purely a suggestion. Use your own judgment as to which data types to use, and what lengths, according to your application. PostgreSQL It is recommended to use PostgreSQL version 9.3 and up, supporting JSON or JSONB columns. When creating a table for logs, use something like this: CREATE TABLE logs ( session_id VARCHAR(60) NOT NULL, date TIMESTAMP WITH TIME ZONE NOT NULL, text TEXT NOT NULL, data JSON ); CREATE INDEX ON logs (session_id); If using v9.4 or up, "data" might better be a "JSONB" column. As with MySQL, use your own judgment for the data type and length of "session_id" and "text", according to your application. If you're planning on running your own queries on the "data" column, you will need to create an index on it. Read PostgreSQL's online documentation on JSON data types for more information. SQLite When using SQLite as a backend, create the following table structure: CREATE TABLE logs ( session_id TEXT NOT NULL, date TEXT NOT NULL, text TEXT NOT NULL, data TEXT ); CREATE INDEX logs_per_session ON logs (session_id); Note that, as opposed to other database systems, SQLite will take the path to the database file as the "database" parameter, instead of a database name. You can also provide ":memory:" for an in-memory database. CONSTRUCTOR new( %options ) Create a new instance of this class. The following options are supported: * db_type - the type of database ("mysql", "pgsql" or "sqlite"), required * database - the name of the database to connect to, defaults to "logs" (if using SQLite, this will be the path to the database file) * table - the name of the table to log into, defaults to "logs" The following options are supported by MySQL and PostgreSQL: * host - the host of the database server, defaults to 127.0.0.1 * port - the port of the database server, defaults to 3306 for MySQL, 5432 for PostgreSQL OBJECT METHODS The following methods implement the Pye role, so you should refer to "Pye" for their documentation. Some methods, however, have some backend-specific notes, so keep reading. log( $session_id, $text, [ \%data ] ) If "\%data" is provided, it will be encoded to JSON before storing in the database. session_log( $session_id ) list_sessions( [ \%opts ] ) Takes all options defined by Pye. The "sort" option, however, takes a standard "ORDER BY" clause definition, e.g. "id ASC". This will default to "date DESC". CONFIGURATION AND ENVIRONMENT "Pye" requires no configuration files or environment variables. DEPENDENCIES "Pye" depends on the following CPAN modules: * Carp * DBI * JSON::MaybeXS * Role::Tiny You will also need the appropriate driver for your database: * DBD::mysql for MySQL * DBD::Pg for PostgreSQL * DBD::SQLite for SQLite BUGS AND LIMITATIONS Please report any bugs or feature requests to "bug-Pye-SQL@rt.cpan.org", or through the web interface at . SUPPORT You can find documentation for this module with the perldoc command. perldoc Pye::SQL You can also look for information at: * RT: CPAN's request tracker * AnnoCPAN: Annotated CPAN documentation * CPAN Ratings * Search CPAN AUTHOR Ido Perlmuter LICENSE AND COPYRIGHT Copyright (c) 2015, Ido Perlmuter "ido@ido50.net". This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either version 5.8.1 or any later version. See perlartistic and perlgpl. The full text of the license can be found in the LICENSE file included with this module. DISCLAIMER OF WARRANTY BECAUSE THIS SOFTWARE IS LICENSED FREE OF CHARGE, THERE IS NO WARRANTY FOR THE SOFTWARE, TO THE EXTENT PERMITTED BY APPLICABLE LAW. EXCEPT WHEN OTHERWISE STATED IN WRITING THE COPYRIGHT HOLDERS AND/OR OTHER PARTIES PROVIDE THE SOFTWARE "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE SOFTWARE IS WITH YOU. SHOULD THE SOFTWARE PROVE DEFECTIVE, YOU ASSUME THE COST OF ALL NECESSARY SERVICING, REPAIR, OR CORRECTION. IN NO EVENT UNLESS REQUIRED BY APPLICABLE LAW OR AGREED TO IN WRITING WILL ANY COPYRIGHT HOLDER, OR ANY OTHER PARTY WHO MAY MODIFY AND/OR REDISTRIBUTE THE SOFTWARE AS PERMITTED BY THE ABOVE LICENCE, BE LIABLE TO YOU FOR DAMAGES, INCLUDING ANY GENERAL, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES ARISING OUT OF THE USE OR INABILITY TO USE THE SOFTWARE (INCLUDING BUT NOT LIMITED TO LOSS OF DATA OR DATA BEING RENDERED INACCURATE OR LOSSES SUSTAINED BY YOU OR THIRD PARTIES OR A FAILURE OF THE SOFTWARE TO OPERATE WITH ANY OTHER SOFTWARE), EVEN IF SUCH HOLDER OR OTHER PARTY HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.