NAME

App::Wubot::SQLite - the wubot library for working with SQLite

VERSION

version 0.3.10

SYNOPSIS

    use App::Wubot::SQLite;

    my $sqlite = App::Wubot::SQLite->new( { file => '/path/to/db.sqlite' } );

    # insert data into 'mytable'.  table is automatically created if
    # it doesn't exist.
    $sqlite->insert( 'mytable', { abc => 'xyz' }, { abc => 'TEXT' } );

    # insert another row into 'mytable'.  'xyz' column is
    # automatically added.
    $sqlite->insert( 'mytable', { abc => 'def', xyz => 'abc' }, { abc => 'TEXT', xyz => 'TEXT" } );

DESCRIPTION

Wubot uses SQLite for a wide variety of uses, including:

  - asynchronous message queues
  - monitors
  - reactions
  - viewing data in the web interface

Most of the heavy lifting is accomplished by SQL::Abstract. See the documentation there for more information.

App::Wubot::SQLite adds a number of features including:

  - allow schema to be define in external config files
  - add missing tables if schema is available
  - add missing columns that are defined in schema but missing from a table

SCHEMAS

Schema files are read from ~/wubot/schemas. Each table's schema lives in a file named {tablename}.yaml. You can bypass the schema files by specifying the complete schema as a hashref. If you pass in a string for the schema, then that string will be used as a subdirectory in ~/wubot/schemas. This allows you to group similar schemas together without having to use strange names for the tables. Here are some examples:

  # no schema specified, look for the schema file in
  # ~/wubot/schemas/mytable.yaml
  $sqlite->insert( 'mytable', { abc => 'xyz' } );

  # bypass schema files, specify schema in insert
  $sqlite->insert( 'mytable', { abc => 'xyz' }, { abc => 'TEXT' } );

  # schema directory specified, look for the schema file in
  # ~/wubot/schemas/foo/mytable.yaml
  $sqlite->insert( 'mytable', { abc => 'xyz' }, 'foo' );

A number of schemas are distributed in the wubot tarball in the config/schemas subdirectory. These schemas should be copied to ~/wubot/schemas before running wubot.

Each time a table schema is needed, the schema config file will be checked to see if it has been updated; if so, the schema file will be re-read. This allows you to change the schema without re-starting the process. Note that while missing columns can be added, but columns are not dynamically removed, and an existing column type is never altered.

SUBROUTINES/METHODS

create_table( $tablename, $schema )

Create a table using the specified schema.

If no schema is provided, the schema directory will be checked for a schema file named '{tablename}.yaml'.

If no schema is found, a fatal error will be thrown.

get_tables()

Get a list of all tables defined in the SQLite database.

If the database contains a table named 'sqlite_sequence', then that table will be omitted from the returned list of tables.

check_schema( $table, $schema, $failok )

Check the schema for a given tablename. Returns the schema.

The schema is option. If a schema is passed into the method, then that schema will be used; otherwise the schema directory will be checked for a file named {tablename}.yaml. If the file has changed since the last time the file was read, then the file will be re-read.

If no schema is found, a 'no schema specified or found for table' exception will be thrown.

If the 'failok' flag is true, then failure to find a schema will not throw an exception, but will simply write a log message at debug level.

insert( $table, $entry_h, $schema )

Insert a single row into the named table.

Only columns defined in the schema will be inserted. Any keys in the entry hash that are not found in the schema will be ignored.

If an 'id' field is defined in the entry, it will be ignored even if the id is defined in the schema. Wubot expects that an 'id' field is always of the type 'INTEGER PRIMARY KEY AUTOINCREMENT'.

This method uses the 'insert' method on SQL::Abstract. See the documentation there for more information.

The 'id' of the row that was inserted will be returned.

The get_prepared method is used to prepare the statement handle, so a missing table or any missing columns will be added. See the get_prepared method documentation for more information.

update( $table, $entry_h, $where, $schema )

Update a row in the table described by the 'where' clause.

This method uses the 'update' method on SQL::Abstract. See the documentation there for more information.

Only columns defined in the schema will be updated. Any keys in the entry hash that are not found in the schema will be ignored.

If an 'id' field is defined in the entry, it will be ignored even if the id is defined in the schema. Wubot expects that an 'id' field is always of the type 'INTEGER PRIMARY KEY AUTOINCREMENT'.

The get_prepared method is used to prepare the statement handle, so a missing table or any missing columns will be added. See the get_prepared method documentation for more information.

insert_or_update( $table, $entry_h, $where, $schema )

If a row exists in the table that matches the 'where' clause, then calls the update() method on that row. If not, then calls the insert() method. See the documentation on the 'update' and 'insert' methods for more information.

select( $options_h )

This method takes the following options:

  - fields - a list of fields to return, defaults to *
  - where - SQL::Abstract 'where'
  - order - SQL::Abstract 'order'
  - limit - maximum number of rows to return
  - callback - method to be executed on all matching rows

This method uses the SQL::Abstract 'select' method to return one or more rows. See the documentation there for more details.

If no callback() method is defined, then all matching rows will be returned. Using a callback() may be more efficient if a large dataset is returned since it does not require all rows to be stored in memory.

The get_prepared method is used to prepare the statement handle, so a missing table or any missing columns will be added. See the get_prepared method documentation for more information.

query( $statement, $callback )

Execute the specified SQL statement.

If no callback() method is defined, then all matching rows will be returned. Using a callback() may be more efficient if a large dataset is returned since it does not require all rows to be stored in memory.

Note that this method does not do any quoting of the statement, so if it contains any data from external sources, it may be vulnerable to a SQL injection attack!

delete( $table, $conditions )

Delete rows from a table that match the specified conditions. See the 'delete' method on SQL::Abstract for more details.

vacuum()

Run the 'vacuum' command. Rebuilds the database to reclaim space from deleted items.

get_prepared( $table, $schema, $command )

Given a SQL command, attempt to prepare the statement.

If the prepare() method throws a 'no such table' error, then the table will be created using the specified schema.

If the prepare() method throws a 'no such column' error, then if the column is defined in the schema, then the add_column method will be called to add the missing column.

add_column( $table, $column, $type )

Add a column with the specified name and type to the schema by calling:

  ALTER TABLE $table ADD COLUMN $column $type

connect()

Calls the DBI->connect method to open a handle to the SQLite database file.

The open database handles are cached in a global variable, so multiple attempts to call connect() on the same database file will return the same database handle rather than creating multiple handles to the same file.

disconnect()

Close the database handle for a database by calling the disconnect() method on the database handle.

get_schema( $table, $directory )

Given the name of a table, get the schema for that table.

The schema will be cached in memory, along with the timestamp on the schema config file. Any time this method is called, it will look at the timestamp on the schema file to see if it has changed. If so, the schema file will be reloaded. This allows you to dynamically change the schema without having to restart the wubot processes.