=pod

=head1 NAME

DBIx::Oro - Simple Relational Database Accessor


=head1 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 = $oro->table([
    User => ['name'] => { id => 1 },
    Post => ['msg']  => { user_id => 1 }
  ]);

  # Select on joined tables and send data to a callback
  my $msg = $join->select({
      name   => 'Akron',
      msg    => { not_glob => 'And*' },
      -limit => 2
  });
  foreach (@$msg) {
    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 ?'


=head1 DESCRIPTION

L 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 L
and L
for database specific drivers.

B.>


=head1 ATTRIBUTES

=head2 dbh

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

The DBI database handle.


=head2 driver

  print $oro->driver;

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


=head2 last_insert_id

  my $id = $oro->last_insert_id;

The globally last inserted id regarding the database connection.


=head2 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.

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

B


=head1 METHODS

=head2 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 C attribute (supported are currently
C and C) all attributes
accepted by this specific driver.

If only a string value is given, this will be treated as
a filename of a L object.
If the filename is C<: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 C attribute of the SQLite
Driver's L.

B


=head2 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 C relation tables.

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

=head2 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 L, the rows have to fulfill.

Returns the number of rows affected.


=head2 merge

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

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

Expects the table name to update or insert, a hash reference of
values to update or insert, and optionally a hash reference with L,
the rows have to fulfill.

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


=head2 select

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


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
(or a L)
and optionally an array reference
of fields, optionally a hash reference with L,
L, and SQL specific L
all rows have to fulfill.

Fields can be column names or SQL functions.
With a colon you can define aliases of field names,
like with C.
For preprocessing field values special L can be applied.


=head2 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 (or a L),
an optional array reference of fields
to return and a hash reference with L, the rows have to fulfill.
Normally this will include the primary key.
L, L,
L as well as the L can be applied
as with L.
Fields can be column names or functions. With a colon you can define
aliases for the field names.


=head2 list

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

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

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

=over 4

=item startIndex

The offset index of the result set.
Needs to be a positive integer. Defaults to C<0>.

=item startPage

The page number of the result set. Defaults to C<1>.

=item count

The number of entries per page. Defaults to C<25>.

=item sortBy

The field to sort the result by.
Needs to be a field name.

=item sortOrder

The order of sorting. Defaults to C.
Also accepts C.

=item filterBy

A field to filter the result by.

=item filterOp

The operation to filter the results based on the C field.
Supports C, to filter on results that have the field defined
(opposite to C), C, to filter on results that have
the field with a value defined by C (opposite to C),
C, to filter on results that have a field
containing a string defined by C, and C, to filter
on results that have a field starting with a string defined by C.

B: The C and C filter operations are EXPERIMENTAL
and may change without warnings.

=item filterValue

The string to check with C.


=item fields

An array reference or comma separated string of
fields to be returned. Defaults to all fields.

=back

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

A final callback function can be used to modify each entry.

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 }
  #    ]
  #  }

The objects in the entry array can be different, depending on
the optionally passed callback function.

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

B


=head2 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 L.


=head2 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, L and L,
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.
L and L can be applied as with L.

Returns the number of rows that were deleted.


=head2 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
L, L, L,
L and - in case of non-joined-tables -
for L, L, L, and L.

In conjunction with a L this can be seen as an I.

B


=head2 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).


=head2 do

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

Executes direct SQL code.

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


=head2 explain

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

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

B


=head2 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 C, otherwise C,
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 L.


=head1 RETRIEVAL OPTIONS

When retrieving data using L or L,
the behaviour can further be defined using the following mechanisms.


=head2 Conditions

Several conditions can by applied to the set of results of the methods
L, L, L, L,
L and L.

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

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. There are several operators supported.

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

Supported operators are C (lt)>, C (gt)>, C<= (eq)>,
C= (le)>, C= (ge)>, C.
String comparison operators like C and similar are supported.
To negate the latter operators you can prepend C.
The C and C operators are special as they expect
a two value array reference as their operand. The single C 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 C { not =E undef }>.

Multiple operators for checking with the same column are supported.

B


=head2 Junctions

By using a hash reference for conditions, the ordering of the condition is random.
In case of C conjunctions, ordering is semantically irrelevant, however
sometimes database queries benefit from certain orders. Junctions help to
force the ordering and grouping of conditions and enables to create C disjunctions.

  my $users = $oro->select(
    Person => {
      -or => [
        { name => { like => '%e%' }},
        { age => 40 },
        {
          -and => [
            place => 'Springfield',
            gender => 'undecided'
          ]
        }
      ]
    }
  );

The junctions C and C are prepended with a minus and need an array
reference with either condition pairs or hash references containing conditions.
The order of the conditions will stay intact. Junctions can be nested.

B


=head2 Restrictions

In addition to conditions, the selection can be restricted by using
special restriction parameters, all prepended by a C<-> symbol
in the top hash reference:

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

=over 2

=item

C<-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.

=item

C<-group>

Groups the result set by field names.
Especially useful with aggregation operators like C.
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 C condition.

=item

C<-limit>

Limits the number of rows in the result set.

=item

C<-offset>

Sets the offset of the result set.

=item

C<-distinct>

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

=back

Restrictions can not be nested in L.


=head2 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 C<0> will have
identical content, fields with identical markers littler than C<0>
will have different content.

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

B


=head2 Treatments

Sometimes field functions and returned values shall be treated
in a special way.
By handing over subroutines, L as well as L 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 C 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 L for examples of treatments.

B


=head2 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 C<-cache>
keyword. It accepts a hash reference with the parameter C
containing the cache object and C 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 C
method of the cache.

B Although the parameter is called C, all caching
objects granting the limited functionalities of C and C
methods are valid (e.g., L, L).

B


=head1 EVENTS

=head2 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.

B


=head1 DEPENDENCIES

Perl 5.10.1 (or higher),
L,
L.


=head1 INSTALL

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

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

By default, C will test all common and driver specific
tests for the SQLite driver.
By using C all common and driver specific
tests for the given driver are run, e.g. C.
The constructor information can be written as a perl data structure
in C, for example:

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


=head1 ACKNOWLEDGEMENT

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


=head1 AVAILABILITY

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


=head1 COPYRIGHT AND LICENSE

Copyright (C) 2011-2016, L.

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

=cut
  

		
		
		
		

		

			
				



				
			
		


		
		

			

				

					
部分文件列表（点击文件名可查看文件内容）
			  
			
			
			

					

									本源码包内暂不包含可直接显示的源代码文件，请下载源码包。
							
		
		

		

			
	
	



	


  

    
联系我们：verysource_com
    
CopyRight © 2008-2022 verySource.Com All Rights reserved.　京ICP备17048824号-1　京公网安备：11010502034788