view release on metacpan or  search on metacpan

docs/database.html  view on Meta::CPAN

      <tr>
        <th>Name</th>
        <th>Type</th>
        <th>Nullable</th>
        <th>Purpose</th>
      </tr>
      <tr>
        <td>id</td>
        <td>Integer. Must be unique.</td>
        <td>No</td>
        <td>Primary Key</td>
      </tr>
      <tr>
        <td>name</td>
        <td>String (max length 20 chars).</td>
        <td>No</td>
        <td>The name of the extra information.</td>
      </tr>
      <tr>
        <td>val</td>
        <td>String (max length 100 chars).</td>
        <td>No</td>
        <td>The value of the extra information.</td>
      </tr>
      <tr>
        <td>event</td>
        <td>Integer.</td>
        <td>No</td>
        <td>Foreign key reference to the event table</td>
      </tr>
    </table>

    <p>Note that when old entries in the events table are anonymised
    that all extra information associated with an event is
    deleted. Contrast this with the tags table where they are all
    kept, it is assumed that all tags are &quot;safe&quot;.</p>

    <h2>The BuzzSaw::DB API</h2>

    <p>When you need to make arbitrary buzzsaw database queries you
    could just use SQL (with or without the perl DBI interface) and
    that will give you everything you need. However there is a handy
    API based on the excellent Perl <code>DBIx::Class</code> module
    which can make creating complex queries much simpler. This API is
    exposed via the <code>BuzzSaw::DB</code> module. It can be used
    like this:</p>

<pre>
   use BuzzSaw::DB;

   my $db = BuzzSaw::DB->new( name => 'logdb',
                              user => 'fred',
                              pass => 'letmein' );

   my $schema = $db->schema;

   my @events = $schema->resultset('Event')->search( { hostname => 'foo' } );
</pre>

    <p>More typically you will not want to specify the user
    credentials in the script itself so the better approach is to use
    a configuration file. That can be done like this:</p>

<pre>
   use BuzzSaw::DB;

   my $db = BuzzSaw::DB->new_with_config();

   my $schema = $db->schema;
</pre>

<p>The default configuration file is <code>/etc/buzzsaw/db.yaml</code>
but it can be overridden like so:</p>

<pre>
   use BuzzSaw::DB;

   my $db = BuzzSaw::DB->new_with_config( configfile => 'buzzsaw_db.yaml' );

   my $schema = $db->schema;
</pre>

<p>The format of the configuration file must be YAML. It is a simple
hash of key-value pairs which map to the various attributes in
the <code>BuzzSaw::DB</code> module (e.g. user, pass, name, host,
port).</p>

<p>As shown above, to get access to the <code>DBIx::Class</code>
schema object you need to use the <code>schema</code> accessor. There
are result set classes available for all DB tables (named <code>Event</code>, <code>Tag</code>,
<code>ExtraInfo</code>, <code>Log</code>
and <code>CurrentProcessing</code>). These result sets are used for
querying the entries in the table. The parameters of an entry in the
result set can be easily queried through the accessors. Here's an
example:</p>

<pre>
   my $schema = $db->schema;

   my @events = $schema->resultset('Event')->search( { hostname => 'foo' } );

   for my $event (@events) {
      my $time = $event->logtime;
      my $program = $event->program;

      print "$time $program\n";
   }
</pre>

<p>The full details for each result set can be found by looking at the
perl docs for the modules, they are in
the <code>BuzzSaw::DB::Schema::Result</code> namespace
(e.g. <code>BuzzSaw::DB::Schema::Result::Event</code>).</p>

<p>Thanks to <code>DBIx::Class</code> it is possible to easily
retrieve the data associated with an event which is stored in a
foreign table (e.g. <em>tags</em> and <em>extra_info</em>). Here is an
example of retrieving the tags for an event:</p>

<pre>
  for my $event (@events) {