The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
DBIx-Oro-0.31_9
River stage zero No dependents
++
/ DBIx::Oro::Driver::SQLite

NAME

DBIx::Oro::Driver::SQLite - SQLite driver for DBIx::Oro

SYNOPSIS

  use DBIx::Oro;

  # Create an SQLite Oro object
  my $oro = DBIx::Oro->new('file.sqlite');

  # Attach new databases
  $oro->attach(blog => ':memory:');

  # Check, if database was newly created
  if ($oro->created) {

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

    # Create Fulltext Search tables
    $oro->do(
      'CREATE VIRTUAL TABLE Blog USING fts4(title, body)'
    );
  };

  # Insert values
  $oro->insert(Blog => {
    title => 'My Birthday',
    body  => 'It was a wonderful party!'
  });

  # Create snippet treatment function
  my $snippet = $oro->snippet(
    start => '<strong>',
    end   => '</strong>',
    token => 10
  );

  my $birthday =
    $oro->load(Blog =>
      [[ $snippet => 'snippet']] => {
        Blog => { match => 'birthday' }
      });

  print $birthday->{snippet};
  # My <strong>Birthday</strong>

DESCRIPTION

DBIx::Oro::Driver::SQLite is an SQLite specific database driver for DBIx::Oro that provides further functionalities.

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

ATTRIBUTES

DBIx::Oro::Driver::SQLite inherits all attributes from DBIx::Oro and implements the following new ones (with possibly overwriting inherited attributes).

created

  if ($oro->created) {
    print "This is brand new!";
  };

If the database was created on construction of the handle, this attribute is true. Otherwise it's false. In most cases, this is useful to create tables, triggers and indices for SQLite databases.

  if ($oro->created) {
    $oro->txn(
      sub {

        # Create table
        $oro->do(
          'CREATE TABLE Person (
              id    INTEGER PRIMARY KEY,
              name  TEXT NOT NULL,
              age   INTEGER
          )'
        ) or return -1;

        # Create index
        $oro->do(
          'CREATE INDEX age_i ON Person (age)'
        ) or return -1;
    });
  };

file

  my $file = $oro->file;
  $oro->file('myfile.sqlite');

The sqlite file of the database. This can be a filename (with a path prefix), :memory: for memory databases or the empty string for temporary files.

foreign_keys

  print $oro->foreign_keys;
  $oro->foreign_keys(0);

DBIx::Oro::Driver::SQLite turns foreign keys on by default. To disable this, set foreign_keys to a false value, e.g. in the constructor.

autocommit

  print $oro->autocommit;
  $oro->autocommit(200);

Run commit after a given number of insert, update, delete or merge operations. Accepts the number of silent operations till the commit is released. Will automatically commit on start. To release unstaged changes at the end, just reset autocommit, e.g. with autocommit(0).

METHODS

DBIx::Oro::Driver::SQLite inherits all methods from DBIx::Oro and implements the following new ones (with possibly overwriting inherited methods).

new

  my $oro = DBIx::Oro->new('test.sqlite');
  $oro = DBIx::Oro->new(':memory:');
  $oro = DBIx::Oro->new('');
  $oro = DBIx::Oro->new(
    file   => 'test.sqlite',
    driver => 'SQLite',
    init   => sub {
      shift->do(
        'CREATE TABLE Person (
            id    INTEGER PRIMARY KEY,
            name  TEXT NOT NULL,
            age   INTEGER
         )'
      );
    }
  );

Creates a new SQLite database accessor object on the given filename or in memory, if the filename is :memory:. If the database file does not already exist, it is created. If the file is the empty string, a temporary database is created. A callback function called init will be triggered, if the database was newly created. This callback is wrapped inside a transaction. The first parameter of the callback function is the Oro object. The attached database handle and file pairs will be attached on every reconnect. The unicode parameter defines the sqlite_unicode property on every connect. Defaults to true.

See new in DBIx::Oro for further information.

delete

  $oro->delete(Person => { id => 4, -secure => 1});

Deletes rows of a given table, that meet a given condition. See delete in DBIx::Oro for further information.

Security

In addition to conditions, the deletion can have further parameters.

-secure

Forces a secure deletion by overwriting all data with 0.

The security parameter is EXPERIMENTAL and may change without warnings.

attach

  $oro->attach( another_db => 'users.sqlite' );
  $oro->attach( another_db => ':memory:' );
  $oro->attach( 'another_db' );

  $oro->load( 'another_db.user' => { id => 4 } );

Attaches another database file to the connector. All tables of this database can then be queried with the same connector.

Accepts the database handle name and a database file name. If the file name is ':memory:' a new database is created in memory. If no file name is given, a temporary database is created. If the database file name does not exist, it returns undef.

The database handle can be used as a prefix for tables in queries. The default prefix for tables of the parent database is main..

This method is EXPERIMENTAL and may change without warnings.

detach

  $oro->detach('another_db');
  $oro->detach(@another_dbs);

Detaches attached databases from the connection.

This method is EXPERIMENTAL and may change without warnings.

TREATMENTS

Treatments can be used for the manipulation of select and load queries.

DBIx::Oro::Driver::SQLite implements the following treatment generators.

matchinfo

  my $result = $oro->select(
    text =>
      [[ $oro->matchinfo('nls') => 'matchinfo']] => {
        text => { match => 'default transaction' }
      });

  # $result = [{
  #   matchinfo => {
  #     l => [3, 3],
  #     n => 3,
  #     c => 2,
  #     p => 2,
  #     s => [2, 0]
  #   }
  # }, {
  #   matchinfo => {
  #     l => [4, 3],
  #     n => 3,
  #     c => 2,
  #     p => 2,
  #     s => [1, 1]
  #   }
  # }];

Creates a treatment for select or load that supports matchinfo information for fts3/fts4 tables. It accepts a format string containing the characters p, c, n, a, l, s, and x. See the SQLite manual for further information on these characters. The characters p and c will always be set. Returns the column value as a hash reference of the associated values.

offsets

  my $result = $oro->load(
    text =>
      [[ $oro->offsets => 'offset' ]] => {
        text => { match => 'world' }
      });

  # $result = {
  #   offset => [
  #     [0, 0, 6, 5],
  #     [1, 0, 24, 5]
  #   ]
  # };

Creates a treatment for select or load that supports offset information for fts3/fts4 tables. It accepts an optional parameter for column association and returns the column value as an array reference containing multiple array references.

See the SQLite manual for further information.

snippet

  my $snippet = $oro->snippet(
    start    => '[',
    end      => ']',
    ellipsis => '',
    token    => 5,
    column   => 1
  );

  my $result = $oro->load(
    text =>
      [[ $snippet => 'excerpt' ]] => {
        text => { match => 'cold' }
      });

  print $result->{excerpt};
  # It was [cold] outside

Creates a treatment for select or load that supports snippets for fts3/fts4 tables. On creation it accepts the parameters start, end, ellipsis, token, and column.

See the SQLite manual for further information.

SEE ALSO

The SQLite manual, especially the information regarding the fulltext search extensions.

DEPENDENCIES

DBI, DBD::SQLite.

AVAILABILITY

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

COPYRIGHT AND LICENSE

Copyright (C) 2011-2016, Nils Diewald.

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