The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
Pye-SQL-1.000001
River stage zero No dependents
/ Pye::SQL

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:

You will also need the appropriate driver for your database:

BUGS AND LIMITATIONS

Please report any bugs or feature requests to bug-Pye-SQL@rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=Pye-SQL.

SUPPORT

You can find documentation for this module with the perldoc command.

perldoc Pye::SQL

You can also look for information at:

AUTHOR

Ido Perlmuter <ido@ido50.net>

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.