/ 
DBIx-Inline-0.17
/ DBIx::Inline::Manual

NAME

DBIx::Inline::Manual - Recipes for DBIx::Inline

DESCRIPTION

The users manual for DBIx::Inline. The pod for the actual module was beginning to get too big, so I decided to move it to its own manual.

CONTENT

Starting off

Connecting

There are a few ways to begin using DBIx::Inline - using models, connecting directly within your package or using sqlite. If you're wanting to reuse the same schema over and over again you're best method would to create a YAML file, put your configuration in there and reuse the model when you need it. If it's just going to be a small once-off, it may be easier to just connect directly. If, however, you're wanting a basic SQLite based connection, using the sqlite method may be right for you.

Using Models

A simple YAML file will look like following. DBIx::Inline will search for inline.yml by default, but you can rename it as anything then call it with config('/path/to/file.yml') afterwards.

---
MySchema:
  connect: 'SQLite:mydb.db'

FooSchema:
  connect: 'Pg:host=localhost;dbname=foo'
  user: 'foo',
  pass: 'foopass'

That gives us two reusable schemas. One using a simple SQLite driver, and the other Postgres. To use them in our package we reference them using model like so.

 package main;

 my $schema = main->model('MySchema');
 my $foo = main->model('FooSchema')->resultset('a_table')->all;

You can also use config to move the config somewhere else..

package main;

use base 'DBIx::Class';

main->config('/var/schemas/myschema.yml');
my $schema = main->model('Foo');

We successfully now have two schemas running in the same package. The first one returns a schema, the second one chained a resultset onto it. You can also chain the config onto the model if you wish.

my $rs = main->config('/path/to/config.yml')->model('Foo')->resultset('this');

The other way is connecting directly.

Using the connect Schema method

Connecting directly is easy enough with connect

my $schema = main->connect(
    dbi     => 'Pg:host=localhost;dbname=foo',
    user    => 'foo_user',
    pass    => 'baz_pass',
);

Obviously you can leave out the user and pass if you're using SQLite. And remember, you can chain a table to a schema.

my $schema = main->connect(
    dbi => 'SQLite:/path/to/sqlite.db'
)->resultset('my_table');

Once your schema has a resultset (at this point a resultset is just a table.. I need to rename the schema method from resultset to table, really), you can start searching, finding, updating, etc. A resultset is just a class holding multiple records.

ResultSet

Searching

The main part to doing anything at all in DBIx::Inline is returning a resulset via search. Search is a powerful method with a few different options. I'll show you how to return a simple resultset, and how to use it to paginate results for a web query with ease. First up, let's perform a simple search.

# search the table where the rows status = 'active', but only return the id and name
my $rs = $table->search([qw/id name/], { status => 'active' });

# search the table where the rows status = 'active', but only return 5 results
# also, order by id first
my $rs = $table->search([], { status => 'active' }, { rows => 5, order => ['id'] });

# now paginate the entire resultset returning 5 records at a time
my $page1 = $table->all->search([], {}, { page => 1, rows => 5 });
my $page2 = $table->all->search([], {}, { page => 2, rows => 5 });

# get the first and last records of a result by chaining
my $first = $table->all->first;
my $last = $table->search([], { code => 5485 })->last;

Inserting

Inserting requires nothing but a resultset. However, should you want to return the last inserted key with insert_id, then you will need to set a primary key. This is very handy when wanting to see if the query worked or not. You can set the primary key on its own or chain it with a resultset.

$rs->primary_key('id'); # on its own
$rs = main->model('Foo')->resultset('users')->primary_key('id')

As you'll notice most methods are chainable. To actually insert a new row you just supply the required keys in a hash

# bring up the resultset
$rs = main->model('Foo')
  ->resultset('users')
  ->primary_key('id');

$rs->insert({
    user     => 'test',
    password => 'testpass',
    name     => 'Mr Foo',
});

# technically, the user should be added, let's make sure we get a return ID
print "Added user with ID# " . $rs->insert_id
    if $rs->insert_id;

Inserting actually returns the successful new row as a Result. So you can do things like..

my $user = $rs->insert({
    user => 'foo',
    pass => 'fooness',
    name => 'The Foo',
});

$user->load_accessors;
print "New Name: " . $user->name;
print "Last Insert Primary key: " . $rs->insert_id . "\n"; # or you could use $user->id

What to do with ResultSet results

You can iterate through results using next.

while(my $row = $rs->next) {
    say $row->{column_name};
}

Alternatively, you can update or delete them

my $rs = $table->search([], { foo => 'baz' });
$rs->update({ status => 'active' });
$rs->delete;

Less SQL queries = happy.

When you iterate through a resultset using next, it returns the result as a DBIx::Inline::Result. Another way to return a result from a resultset is using find. If you use 'find' it will return a single row.

my $result = $rs->find([], { foo => 'baz' });
say $result->{name};

As you can see it works the same as search, but will not return a resultset, and only returns 1 row.

Results

There isn't a great deal you can do with results compared to resulsets - they simply hold the row data for the current result in a hash.

$result->{name}
$result->{id}
etc..

You can create accessors for all of columns with load_accessors

$result->load_accessors;
say $result->name;
say $result->id;

But what if you have long column names and want them shortened? No problem, use accessorize.

$result->accessorize(
    name => 'long_name_column',
    id   => 'repeat_id',
);

say $result->name; # instead of $result->{long_name_column}

If you pass arguments after load_accessors to any of the subroutines it creates you will update their value.

$result->load_accesors;
$result->name('New Name');

Using models in more depth

Models can play a huge part with DBIx::Inline, if you let them. You assign a resultset to a model in the config file, or even create a relationship between two tables.

Assigning ResultSets

Let's assign a resultset by default.

---
User:
  connect: 'SQLite:/path/to/db'
  resultset: users

Foo:
  connect: 'SQLite:/path/to/db'

Above we created two models that point to the same database. The User model is simply a shortcut to the users resultset, so we can ..

my $rs = main->model('User'); # we now have the complete list of users

Without the resultset included we could use the Foo model.

my $rs = main->model('Foo')->resultset('users')->all;

Using Relationships

Relationships are very handy because it takes care of joining for you. As an example, if I had two tables authors and books, and I wanted to see what books were written by a particular author there are a few ways I could go about this: Multiple queries, a SQL Join or I could use relationships in my model. Let's take a look.

---
Author:
  connect: 'SQLite:/path/to/db'
  related:
    authors: 'id <-> books(author_id)'

To break it down, related tells DBIx::Inline we're going to use a relationship. The first key authors is the main table we want to lookup. id is the column name in authors we want to match, books is the table to search for the related information in, then the author_id refers to the authors id. Once you do this, DBIx::Inline will create an accessor called authors (or whatever you called your main related table) that merges the data from the books table with the authors table, only returning the books with whatever search arguments you pass to the accessor.

my $authors = main->model('Author')->resultset('authors');
my $books = $authors->authors({ id => 2 });
# we now have all the books written by the Author with ID of 2
while(my $row = $books->next) {
    $row->load_accessors;
    print $row->book_title; # The title of the book from the books table
    print $row->author_name; # The Authors name from the authors table
}