HTTP::AppServer::Plugin::Database - Plugin for HTTP::AppServer that allows easy DBI database access.
use HTTP::AppServer; my $server = HTTP::AppServer->new(); $server->plugin('Database', DBEngine => 'mysql', DBName => 'ui', DBUsername => 'root', DBPassword => '', DBHost => 'localhost', SchemaDir => './db', UseVersion => '4.00', );
Plugin for HTTP::AppServer that provides a connection to a DBI database handle (relational database). On top of that it provides simplified calling of common database queries, such as select, update, delete etc.
The database engine to use, e.g. 'mysql'. This is a valid DBI engine name.
The database name.
The username of the database user.
The password of the database user.
The host of the database, default is "localhost".
This plugin allows for automatic migration of database schemas. The schemas are stored in the SchemaDir directory in this form (example):
/1.00-up.sql /1.00-down.sql /1.01-up.sql /1.01-down.sql /2.00-up.sql /2.00-down.sql /...
Versions should start at 1.00. In this example, version 1.00 is created by executing 1.00-up.sql and undone by executing 1.00-down.sql.
When loaded, this plugin migrates the database to the schema of that version. That means all *.sql files in SchemaDir that lie on the path from the current database version to the UseVersion version are beeing executed.
The implicit version '0.00' can be used to create a state for the database where all migrations are undone.
None.
To retrieve records from the database, use the select() method:
my $query = $server->find( -tables => [qw(mytable1 mytable2 ...)], -where => { name => "...", ... }, -wherelike => {...}, -group => [qw(id name ...)], -order => [qw(id name ...)], -limit => 10, -distinct => 1, -columns => [qw(id name ...)], -joins => { name => name, ... }, -sortdir => 'asc', # or 'desc' );
To access the records of the result set, use the normal DBI methods:
my $array = $query->fetchrow_arrayref(); my $hash = $query->fetchrow_hashref(); while (my $record = $query->fetchrow_arrayref()) { # ... } # ...
Same as find() but returns not a result but all entries matching as plain Perl array.
To insert a record, use the create() method:
my $id = $server->create( -table => "...", -row => { name => "...", ... }, );
To update fields in a record, use the update() method:
my $success = $server->update( -table => "...", -set => { name => "...", ... }, -where => { ... }, -wherelike => { ... }, );
To delete records, use the remove() method:
my $query = $server->remove( -table => "...", -where => { ... }, -wherelike => { ... }, );
Any kind of other query can be executed using the query() method:
my $query = $server->query( $sql );
This method is used to import a text file that contains a number of records into a certain table in the database. This is nice, if you set up many databases for an application and want to insert some default data all at once.
Example:
load( 'my_project', 'default_data', 'my_table' );
This example will load the file data/my_project/default_data.txt from the configured private directory into the database table named "my_table" (in the configured database).
The data file must be in a certain format. Here is an example:
[1] name:Mr.X age:23 [2] name:Mr.Y age:56 bio. Born in 1980, Mr.Y was the first to invent the toaster. [3] name:Mrs.Y age:25
Each data file can contain zero or more records, each of which starts with a line containing the id of the record in brackets. Each line after that contains a field value, which starts with the field name followed by a colon (":"), followed by the field value up to the end of line (without the newline).
If a field value contains newlines, the fieldname must be followed by a dot (instead of a colon) and the following lines are considered the value of the field. The field value lines must contain a space character (space or horizontal tab) at the line start, which identifies them as field value lines but is ignored.
Due to the format, certain restrictions apply to data that is stored in data files:
When inserted in the database, CGI::WebToolkit checks first, if a certain row with that id already exists. If so, nothing happens. In almost all cases you do not want to have your data in the database be overwritten by data from data files.
Empty lines in data files and space characters before the colon are completely ignored.
HTTP::AppServer, HTTP::AppServer::Plugin
Tom Kirchner, <tom@tkirchner.com>
Copyright (C) 2010 by Tom Kirchner
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.10.0 or, at your option, any later version of Perl 5 you may have available.
To install HTTP::AppServer, copy and paste the appropriate command in to your terminal.
cpanm
cpanm HTTP::AppServer
CPAN shell
perl -MCPAN -e shell install HTTP::AppServer
For more information on module installation, please visit the detailed CPAN module installation guide.