NAME

HTTP::AppServer::Plugin::Database - Plugin for HTTP::AppServer that allows easy DBI database access.

SYNOPSIS

  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',
  );

DESCRIPTION

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.

Plugin configuration

DBEngine => name

The database engine to use, e.g. 'mysql'. This is a valid DBI engine name.

DBName => name

The database name.

DBUsername => name

The username of the database user.

DBPassword => password

The password of the database user.

DBHost => host

The host of the database, default is "localhost".

SchemaDir => path

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.

UseVersion => version

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.

Installed URL handlers

None.

Installed server properties

None.

Installed server methods

find()

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()) {
    # ...
  }
  # ...

findall()

Same as find() but returns not a result but all entries matching as plain Perl array.

create()

To insert a record, use the create() method:

  my $id = $server->create(
    -table => "...",
    -row => { name => "...", ... },
  );

update()

To update fields in a record, use the update() method:

  my $success = $server->update(
    -table => "...",
    -set => { name => "...", ... },
    -where => { ... },
    -wherelike => { ... },
  );

remove()

To delete records, use the remove() method:

  my $query = $server->remove(
    -table => "...",
    -where => { ... },
    -wherelike => { ... },      
  );

query()

Any kind of other query can be executed using the query() method:

  my $query = $server->query( $sql );

load()

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:

1 The table must have a column named id.

2 Field names are not allowed to contain colons, dots or newline characters.

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.

SEE ALSO

HTTP::AppServer, HTTP::AppServer::Plugin

AUTHOR

Tom Kirchner, <tom@tkirchner.com>

COPYRIGHT AND LICENSE

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.

cpanm HTTP::AppServer

perl -MCPAN -e shell
install HTTP::AppServer