NAME

DBIx::Oro - Simple Relational Database Accessor

SYNOPSIS

  use DBIx::Oro;

  # Create new object
  my $oro = DBIx::Oro->new(

    # Create an SQLite in-memory DB and initialize
    ':memory:' => sub {

      # Initialize tables with direct SQL
      $_->do(
        'CREATE TABLE User (
           id    INTEGER PRIMARY KEY,
           name  TEXT,
           age   TEXT
        )'
      ) or return -1;
    }
  );

  # Execute SQL directly
  $oro->do(
    'CREATE TABLE Post (
       time     INTEGER,
       msg      TEXT,
       user_id  INTEGER
    )'
  );

  # Wrap multiple actions in transactions
  $oro->txn(
    sub {

      # Insert simple data
      my $rv = $_->insert(User => {
        name => 'Akron',
        age  => '20'
      });

      # Easily rollback transaction
      return -1 unless $rv;

      # Get latest inserted id
      my $user_id = $_->last_insert_id;

      # Bulk insert data with default values
      $_->insert(Post => [
        [ time => time ],
        [ user_id => $user_id ],
        'msg'] => (
          ['Hello World!'],
          ['Seems to work!'],
          ['I can insert bulk messages ...'],
          ['And I can stop.']
        )
      ) or return -1;
  });

  # Load a dataset based on a unique condition
  my $user = $oro->load(User => { name => 'Akron' });

  print $user->{age}; # '20'

  # Count the number of entries on a table
  print $oro->count('Post'); # '4'

  # Select multiple datasets based on conditions
  my $msgs = $oro->select(Post => ['msg'] => {
    msg => { like => '%wo%' }
  });

  # Results are simple datastructures
  print $_->{msg} . "\n" foreach @$msgs;
  # 'Hello World!'
  # 'Seems to work!'

  # Create joined tables
  my $join = $oro2->table([
    User => ['name'] => { id => 1 },
    Post => ['msg']  => { user_id => 1 }
  ]);

  # Select on joined tables and send data to a callback
  $join->select({
      name   => 'Akron',
      msg    => { not_glob => 'And*' },
      -limit => 2
    } => sub {
      print $_->{name}, ': ', $_->{msg}, "\n";
    });
  # Akron: Hello World!
  # Akron: I can insert bulk messages ...

  # Investigate generated SQL data for debugging
  print $join->last_sql;

  # 'SELECT User.name AS `name`, Post.msg AS `msg`
  # FROM User, Post WHERE User.id = Post.user_id
  # AND Post.msg NOT GLOB ? AND User.name = ?
  # LIMIT ?'

DESCRIPTION

DBIx::Oro is a database accessor that provides basic functionalities to work with simple relational databases, especially in a web environment.

Its aim is not to be a complete abstract replacement for SQL communication with DBI, but to make common tasks easier. For now it's focused on SQLite - but first steps to make it less dependent on SQLite are done. It should be fork- and thread-safe.

See Driver::SQLite and Driver::MySQL for database specific drivers.

DBIx::Oro is a development release! Do not rely on any API methods, especially on those marked as experimental.

ATTRIBUTES

dbh

  my $dbh = $oro->dbh;
  $oro->dbh(DBI->connect('...'));

The DBI database handle.

driver

  print $oro->driver;

The driver (e.g., SQLite or MySQL) of the Oro instance.

last_insert_id

  my $id = $oro->last_insert_id;

The globally last inserted id regarding the database connection.

last_sql

  print $oro->last_sql;
  my ($sql, $from_cache) = $oro->last_sql;

The last executed SQL command.

In array context this will also return a value indicating if the request was a real database request. If the last result was returned by a cache, the value is true, otherwise false.

Note: This is for debugging purposes only - the returned SQL may not be valid due to reformatting.

The array return is EXPERIMENTAL and may change without warnings.

METHODS

new

  my $oro = DBIx::Oro->new('test.sqlite');
  $oro = DBIx::Oro->new('test.sqlite' => sub {
    shift->do(
      'CREATE TABLE Person (
          id    INTEGER PRIMARY KEY,
          name  TEXT NOT NULL,
          age   INTEGER
      )');
  });
  $oro = DBIx::Oro->new(
    driver   => 'MySQL',
    database => 'TestDB',
    user     => 'root',
    password => ''
  );

Creates a new Oro database handle.

Accepts a driver attribute (supported are currently SQLite and MySQL) all attributes accepted by this specific driver.

If only a string value is given, this will be treated as a filename of a DBIx::Oro::Driver::SQLite object. If the filename is :memory:, this will be an in-memory SQLite database. If the database file does not already exist, it is created. An additional callback function may be passed, that serves as the init attribute of the SQLite Driver's new.

The class name of the return object may change without warnings!

insert

  $oro->insert(Person => {
    id   => 4,
    name => 'Peter',
    age  => 24,
    address => \"SELECT address FROM Address where id = 4",
    country => [\"SELECT country FROM County where id = ?", 3]
  });
  $oro->insert(Person =>
    ['id', 'name'] => [4, 'Peter'], [5, 'Sabine']
  );

Inserts a new row to a given table for single insertions.

Expects the table name and a hash reference of values to insert. In case the values are scalar references, the string is directly used as an SQL statement. In case the values are array references and the first element is a scalar reference, the string is directly used as an SQL statement and the following values are inserted for placeholders.

For multiple insertions, it expects the table name to insert, an array reference of the column names and an arbitrary long array of array references of values to insert.

  $oro->insert(Person =>
    ['prename', [ surname => 'Meier' ]] =>
      map { [$_] } qw/Peter Sabine Frank/
  );

For multiple insertions with defaults, the array reference for column names can contain array references itself with a column name followed by the default value. This value is inserted for each inserted entry and is especially useful for n:m relation tables.

Note: The treatment of scalar and array references as insertion values is EXPERIMENTAL and may change without warnings.

update

  my $rows = $oro->update(Person => { name => 'Daniel' }, { id => 4 });

Updates values of an existing row of a given table.

Expects the table name to update, a hash reference of values to update, and optionally a hash reference with conditions, the rows have to fulfill. In case of scalar values, identity is tested. In case of array references, it is tested, if the field value is an element of the set.

Returns the number of rows affected.

merge

  $oro->merge(Person => { age => 29 }, { name => 'Daniel' });

Updates values of an existing row of a given table, otherwise inserts them (so called upsert).

Expects the table name to update or insert, a hash reference of values to update or insert, and optionally a hash reference with conditions, the rows have to fulfill. In case of scalar values, identity is tested. In case of array references, it is tested, if the field value is an element of the set.

Scalar condition values will be inserted, if the fields do not exist.

select

  my $users = $oro->select('Person');
  $users = $oro->select(Person => ['id', 'name']);
  $users = $oro->select(Person =>
    ['id'] => {
      age    => 24,
      name   => ['Daniel', 'Sabine'],
      rights => [\"SELECT right FROM Rights WHERE right = ?", 2]
    });
  $users = $oro->select(Person => ['name:displayName']);

  $oro->select(
    Person => sub {
      print $_->{id}, "\n";
      return -1 if $_->{name} eq 'Peter';
    });

  my $age = 0;
  $oro->select(
    Person => ['id', 'age'] => {
      name => { like => 'Dani%' }} =>
        sub {
          print $_->{id}, "\n";
          $age += $_->{age};
          return -1 if $age >= 100;
    });

Returns an array reference of rows as hash references of a given table, that meet a given condition.

Expects the table name of the selection and optionally an array reference of fields, optionally a hash reference with conditions and restrictions all rows have to fulfill, and optionally a callback, which is released after each row, passing the row as a hash reference.

If a callback is given, the method has no return value. If the callback returns -1, the data fetching is aborted.

In case of scalar values, identity is tested for the condition. In case of array references, it is tested, if the field value is an element of the set or, if the first element is a scalar reference, the string is taken as SQL directly and all following elements are parameters. In case of scalar references, the string is taken as SQL directly. In case of hash references, the keys of the hash represent operators to test with (see below).

Fields can be column names or SQL functions. With a colon you can define aliases of field names, like with count(field):field_count.

The callback is EXPERIMENTAL and may change without warnings.

Operators

When checking with hash references, several operators are supported.

  my $users = $oro->select(
    Person => {
      name => {
        like     => '%e%',
        not_glob => 'M*'
      },
      age => {
        between => [18, 48],
        ne      => 30,
        not     => [45,46]
      }
    }
  );

Supported operators are < (lt), > (gt), = (eq), <= (le), >= (ge), != (ne). String comparison operators like like and similar are supported. To negate the latter operators you can prepend not_. The between and not_between operators are special as they expect a two value array reference as their operand. The single not operator accepts an array reference as a set and is true, if the value is not element of the set. To test for existence, use value => { not => undef }.

Multiple operators for checking with the same column are supported.

Operators are EXPERIMENTAL and may change without warnings.

Restrictions

In addition to conditions, the selection can be restricted by using special restriction parameters, all prepended by a - symbol:

  my $users = $oro->select(
    Person => {
      -order    => ['-age','name'],
      -group    => [ age => { age => { gt => 42 } } ]
      -offset   => 1,
      -limit    => 5,
      -distinct => 1
    }
  );

-order

Sorts the result set by field names. Field names can be scalars or array references of field names ordered by priority. A leading minus of the field name will use descending, otherwise ascending order.
-group

Groups the result set by field names. Especially useful with aggregation operators like count(). Field names can be scalars or array references of field names ordered by priority. In case of an array reference, the final element can be a hash reference, giving a having condition.
-limit

Limits the number of rows in the result set.
-offset

Sets the offset of the result set.
-distinct

Boolean value. If set to a true value, only distinct rows are returned.

Joined Tables

Instead of preparing a select on only one table, it's possible to use any number of tables and perform a simple equi-join:

  $oro->select(
    [
      Person =>    ['name:author', 'age'] => { id => 1 },
      Book =>      ['title'] => { author_id => 1, publisher_id => 2 },
      Publisher => ['name:publisher', 'id:pub_id'] => { id => 2 }
    ] => {
      author => 'Akron'
    }
  );

Join-Selects accept an array reference with a sequence of table names, optional field array references and optional hash references containing numerical markers for the join. If the field array reference is not given, all columns of the table are selected. If the array reference is empty, no columns of the table are selected.

With a colon you can define aliases for the field names.

The join marker hash reference has field names as keys and numerical markers or array references including numerical markers as values. Fields with identical markers greater or equal than 0 will have identical content, fields with identical markers littler than 0 will have different content.

After the join table array reference, the optional hash reference with conditions and restrictions and an optional callback may follow.

Joins are EXPERIMENTAL and may change without warnings.

Treatments

Sometimes field functions and returned values shall be treated in a special way. By handing over subroutines, select as well as load allow for these treatments.

  my $name = sub {
    return ('name', sub { uc $_[0] });
  };
  $oro->select(Person => ['age', [ $name => 'name'] ]);

This example returns all values in the name column in uppercase. Treatments are array references in the field array, with the first element being a treatment subroutine reference and the second element being the alias of the column.

The treatment subroutine returns a field value (an SQL string), optionally an anonymous subroutine that is executed after each returned value, and optionally an array of values to pass to the inner subroutine. The first parameter the inner subroutine has to handle is the value to treat, following the optional treatment parameters. The treatment returns the treated value (that does not have to be a string).

Outer subroutines are executed as long as the first value is not a string value. The only parameter passed to the outer subroutine is the current table name.

See the SQLite Driver for examples of treatments.

Treatments are HEAVILY EXPERIMENTAL and may change without warnings.

Caching

  use CHI;
  my $hash = {};
  my $cache = CHI->new(
    driver => 'Memory',
    datastore => $hash
  );

  my $users = $oro->select(
    Person => {
      -cache => {
        chi        => $cache,
        key        => 'all_persons',
        expires_in => '10 min'
      }
    }
  );

Selected results can be directly cached by using the -cache keyword. It accepts a hash reference with the parameter chi containing the cache object and key containing the key for caching. If no key is given, the SQL statement is used as the key. All other parameters are transferred to the set method of the cache.

Note: Although the parameter is called chi, all caching objects granting the limited functionalities of set and get methods are valid (e.g., Cache::Cache, Mojo::Cache).

Caching is EXPERIMENTAL and may change without warnings.

load

  my $user  = $oro->load(Person, { id => 4 });
  my $user  = $oro->load(Person, ['name'], { id => 4 });
  my $count = $oro->load(Person, ['count(*):persons']);

Returns a single hash reference of a given table, that meets a given condition.

Expects the table name of selection, an optional array reference of fields to return and a hash reference with conditions, the rows have to fulfill. Normally this will include the primary key. Restrictions as well as the caching system can be applied as with select. In case of scalar values, identity is tested. In case of array references, it is tested, if the field value is an element of the set. Fields can be column names or functions. With a colon you can define aliases for the field names.

list

  my $users = $oro->list(Table => {
    sortBy => 'name',
    startPage => 5,
    count => 20
  });

Returns a response hash based on queries as specified in OpenSearch and PortableContacts. This is useful to be directly called from web applications.

Expects a table name (in case no table or joined table was created using table) and a hash reference supporting the following parameters:

startIndex: The offset index of the result set. Needs to be a positive integer. Defaults to 0.
startPage: The page number of the result set. Defaults to 1.
count: The number of entries per page. Defaults to 25.
sortBy: The field to sort the result by. Needs to be a field name.
sortOrder: The order of sorting. Defaults to ascending. Also accepts descending.
filterBy: A field to filter the result by.
filterOp: The operation to filter the results based on the filterBy field. Supports present, to filter on results that have the field defined, equals, to filter on results that have the field with a value defined by filterValue, contains, to filter on results that have a field containing a string defined by filterValue, and startsWith, to filter on results that have a field starting with a string defined by filterValue.
filterValue: The string to check with filterOp.
fields: An array reference or comma separated string of fields to be returned. Defaults to all fields.

In addition to that, the caching system can be applied as with select.

The created response is a hash reference with the following structure:

  #  {
  #    totalResults => 44,
  #    startIndex   => 0,
  #    itemsPerPage => 20,
  #    startPage    => 5,
  #    entry => [
  #      { name => 'Akron', age => 20 },
  #      { name => 'Peter', age => 30 }
  #    ]
  #  }

All valid parameters are returned, including the totalResults value, giving the number of elements in the non-filtered result set. The count parameter is consumed and the correct itemsPerPage value is returned. The entry array reference contains hash references of all rows.

This method is EXPERIMENTAL and may change without warnings.

count

  my $persons = $oro->count('Person');
  my $pauls   = $oro->count('Person' => { name => 'Paul' });

Returns the number of rows of a table.

Expects the table name and a hash reference with conditions, the rows have to fulfill. Caching can be applied as with select.

delete

  my $rows = $oro->delete(Person => { id => 4 });

Deletes rows of a given table, that meet a given condition.

Expects the table name of selection and optionally a hash reference with conditions and restrictions, the rows have to fulfill. In case of scalar values, identity is tested for the condition. In case of array references, it is tested, if the field value is an element of the set. Restrictions can be applied as with select.

Returns the number of rows that were deleted.

table

  # Table names
  my $person = $oro->table('Person');
  print $person->count;
  my $person = $person->load({ id => 2 });
  my $persons = $person->select({ name => 'Paul' });
  $person->insert({ name => 'Ringo' });
  $person->delete;

  # Joined tables
  my $books = $oro->table(
    [
      Person =>    ['name:author', 'age:age'] => { id => 1 },
      Book =>      ['title'] => { author_id => 1, publisher_id => 2 },
      Publisher => ['name:publisher', 'id:pub_id'] => { id => 2 }
    ]
  );
  $books->select({ author => 'Akron' });
  $books->list({ filterBy => 'author', filterOp => 'present' });
  print $books->count;

Returns a new Oro object with a predefined table or joined tables.

Allows to omit the first table argument for the methods select, load, list, count and - in case of non-joined-tables - for insert, update, merge, and delete.

In conjunction with a joined table this can be seen as an ad hoc view.

This method is EXPERIMENTAL and may change without warnings.

txn

  $oro->txn(
    sub {
      foreach (1..100) {
        $oro->insert(Person => { name => 'Peter'.$_ }) or return -1;
      };
      $oro->delete(Person => { id => 400 });

      $oro->txn(
        sub {
          $_->insert('Person' => { name => 'Fry' }) or return -1;
        }) or return -1;
    });

Wrap transactions.

Expects an anonymous subroutine containing all actions. If the subroutine returns -1, the transactional data will be omitted. Otherwise the actions will be released. Transactions established with this method can be securely nested (although inner transactions may not be true transactions depending on the driver).

do

  $oro->do(
    'CREATE TABLE Person (
        id   INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
     )');

Executes direct SQL code.

This is a wrapper for the do method of DBI (but fork- and thread-safe).

explain

  print $oro->explain(
    'SELECT ? FROM Person', ['name']
  );

Returns the query plan for a given query as a line-breaked string.

This method is EXPERIMENTAL and may change without warnings.

prep_and_exec

  my ($rv, $sth) = $oro->prep_and_exec(
    'SELECT ? FROM Person', ['name'], 'cached'
  );

  if ($rv) {
    my $row;
    while ($row = $sth->fetchrow_hashref) {
      print $row->{name};
      if ($name eq 'Fry') {
        $sth->finish;
        last;
      };
    };
  };

Prepare and execute an SQL statement with all checkings. Returns the return value (on error false, otherwise true, e.g. the number of modified rows) and - in an array context - the statement handle.

Accepts the SQL statement, parameters for binding in an array reference and optionally a boolean value, if the prepared statement should be cached by DBI.

EVENTS

on_connect

  $oro->on_connect(
    sub { $log->debug('New connection established') }
  );

  if ($oro->on_connect(
    my_event => sub {
      shift->insert(Log => { msg => 'reconnect' } )
    })) {
    print 'Event newly established!';
  };

Attaches a callback for execution in case of newly established database connections.

The first argument passed to the anonymous subroutine is the Oro object, the second one is the newly established database connection. Prepending a string with a name will prevent from adding an event multiple times - adding the event again will be ignored.

Returns a true value in case the event is newly established, otherwise false.

Events will be emitted in an unparticular order.

This event is EXPERIMENTAL and may change without warnings.

DEPENDENCIES

DBI, DBD::SQLite.

INSTALL

When not installing via a package manager, CPAN or cpanm, you can install Oro manually, using

  $ perl Makefile.PL
  $ make
  $ make test
  $ sudo make install

By default, make test will test all common and driver specific tests for the SQLite driver. By using make test TEST_DB={Driver} all common and driver specific tests for the given driver are run, e.g. make test TEST_DB=MySQL. The constructor information can be written as a perl data structure in t/test_db.pl, for example:

  {
    MySQL => {
      database => 'test',
      host     => 'localhost',
      username => 'MyTestUser',
      password => 'h3z6z8vvfju'
    }
  }

ACKNOWLEDGEMENT

Partly inspired by ORLite, written by Adam Kennedy. Some code is based on DBIx::Connector, written by David E. Wheeler. Without me knowing (it's a shame!), some of the concepts are quite similar to SQL::Abstract, written by Nathan Wiger et al.

AVAILABILITY

  https://github.com/Akron/DBIx-Oro

COPYRIGHT AND LICENSE

This program is free software, you can redistribute it and/or modify it under the same terms as Perl.

To install DBIx::Oro, copy and paste the appropriate command in to your terminal.

cpanm

cpanm DBIx::Oro

CPAN shell

perl -MCPAN -e shell
install DBIx::Oro

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

ATTRIBUTES

dbh

driver

last_insert_id

last_sql

METHODS

new

insert

update

merge

select

Operators

Restrictions

Joined Tables

Treatments

Caching

load

list

count

delete

table

txn

do

explain

prep_and_exec

EVENTS

on_connect

DEPENDENCIES

INSTALL

ACKNOWLEDGEMENT

AVAILABILITY

COPYRIGHT AND LICENSE

Module Install Instructions