man DBD::SQLite (3): Self-contained RDBMS in a DBI Driver

(3)

$DBD::SQLite::sqlite_version
"3.x.y"
$DBD::SQLite::sqlite_version_number
"3xxxyyy"
"DBD::SQLite::Constants::SQLITE_VERSION_NUMBER()"
"DBD::SQLite::compile_options()"
"path"
"name"
  my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","");

$dbfile
$dbfile
  my $dbh = DBI->connect("dbi:SQLite:uri=file:$path_to_dbfile?mode=rwc");

  use DBD::SQLite::Constants qw/:file_open/;
  my $dbh = DBI->connect("dbi:SQLite:$dbfile", undef, undef, {
    sqlite_open_flags => SQLITE_OPEN_READONLY,
  });

"ReadOnly"
"PrintWarn"
"tempfile()"
  ($fh, $filename) = tempfile($template, EXLOCK => 0);

"tempdir()"
"newdir()"
"sqlite_busy_timeout"
"sqlite_use_immediate_transaction"
"dbish"
  dbish dbi:SQLite:foo.db

"sqlite3"
  use DBI qw(:sql_types);
  my $dbh = DBI->connect("dbi:SQLite:dbfile","","");

  my $blob = `cat foo.jpg`;
  my $sth = $dbh->prepare("INSERT INTO mytable VALUES (1, ?)");
  $sth->bind_param(1, $blob, SQL_BLOB);
  $sth->execute();

  $sth = $dbh->prepare("SELECT * FROM mytable WHERE id = 1");
  $sth->execute();
  my $row = $sth->fetch;
  my $blobo = $row->[1];

  # now $blobo == $blob

  my $sth = $dbh->prepare(q{
    SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?;
  });
  $sth->execute(5);

  SELECT bar FROM foo GROUP BY bar HAVING count(*) > "5";

"BLOB"
"bind_param()"
  use DBI qw(:sql_types);  # Don't forget this

  my $sth = $dbh->prepare(q{
    SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?;
  });
  $sth->bind_param(1, 5, SQL_INTEGER);
  $sth->execute();

  my $sth = $dbh->prepare(q{
    SELECT bar FROM foo GROUP BY bar HAVING count(*) > (? + 0);
  });
  $sth->execute(5);

"sqlite_see_if_its_a_number"
  $dbh->{sqlite_see_if_its_a_number} = 1;
  my $sth = $dbh->prepare(q{
    SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?;
  });
  $sth->execute(5);

  my $dbh = DBI->connect('dbi:SQLite:foo', undef, undef, {
    AutoCommit => 1,
    RaiseError => 1,
    sqlite_see_if_its_a_number => 1,
  });

"sqlite3"
"sqlite_see_if_its_a_number"
  my $dbh = DBI->connect('dbi:SQLite:foo', undef, undef, {
    AutoCommit => 1,
    RaiseError => 1,
    sqlite_see_if_its_a_number => 1,
  });
  my $sth = $dbh->prepare('INSERT INTO foo VALUES(?)');
  # '1.230' will be inserted as a text, instead of 1.23 as a number,
  # even though sqlite_see_if_its_a_number is set.
  $sth->bind_param(1, '1.230', SQL_VARCHAR);
  $sth->execute;

"?"
":AAAA"
  my $sth = $dbh->prepare(
    'update TABLE set a=?1 where b=?2 and a IS NOT ?1'
  );
  $sth->execute(1, 2);

"DELETE"
"TRUNCATE"
"DELETE"
"WAL"
"WAL"
"sqlite3"
"PRAGMA journal_mode = DELETE"
"TRUNCATE"
"sqlite3"
"foreign_keys"
  $dbh->do("PRAGMA foreign_keys = ON");

  $dbh->do("PRAGMA foreign_keys = OFF");

"AutoCommit"
"begin_work"
"BEGIN"
"commit/rollback"
  $dbh->{AutoCommit} = 1;

  $dbh->begin_work; # or $dbh->do('BEGIN TRANSACTION');

  # $dbh->{AutoCommit} is turned off temporarily during a transaction;

  $dbh->commit; # or $dbh->do('COMMIT');

  # $dbh->{AutoCommit} is turned on again;

"BEGIN"
"begin_work"
"BEGIN"
  $dbh->{AutoCommit} = 0;

  # $dbh->do('BEGIN TRANSACTION') is not necessary, but possible

  ...

  $dbh->commit; # or $dbh->do('COMMIT');

  # $dbh->{AutoCommit} stays intact;

  $dbh->{AutoCommit} = 1;  # ends the transactional mode

"AutoCommit"
"BEGIN"
"COMMIT"
"deferred"
"BEGIN"
"BEGIN IMMEDIATE"
"begin_work"
"AutoCommit"
"sqlite_use_immediate_transaction"
"deferred"
  my $dbh = DBI->connect("dbi:SQLite::memory:", "", "", {
    sqlite_use_immediate_transaction => 0,
  });

"BEGIN"
"SELECT"
"ROLLBACK"
"SELECT"
"finish"
  $sth = $dbh->prepare("SELECT * FROM t");
  $dbh->begin_work;
  eval {
      $sth->execute;
      $row = $sth->fetch;
      ...
      die "For some reason";
      ...
  };
  if($@) {
     $sth->finish;  # You need this for SQLite
     $dbh->rollback;
  } else {
     $dbh->commit;
  }

"dump"
"prepare"
"do"
"sqlite_allow_multiple_statements"
"do"
"prepare"
"prepare_cached"
$sth
  SELECT url, count(*) as count
  FROM access_log
  GROUP BY url
  ORDER BY count desc
  LIMIT 20

  PRAGMA synchronous = OFF

  $dbh->do("PRAGMA cache_size = 800000");

"$dbh->{sqlite_unicode} = 1"
  use DBI qw(:sql_types);
  $dbh->{sqlite_unicode} = 1;
  my $sth = $dbh->prepare("INSERT INTO mytable (blobcolumn) VALUES (?)");

  # Binary_data will be stored as is.
  $sth->bind_param(1, $binary_data, SQL_BLOB);

"BLOB"
"unicode"
"sqlite_unicode"
"unicode"
"do"
"begin
immediate transaction"
"begin transaction"
"deferred"
"prepare"
  $sth = $dbh->table_info(undef, $schema, $table, $type, \%attr);

"LIKE"
$type
  @names = $dbh->primary_key(undef, $schema, $table);
  $sth   = $dbh->primary_key_info(undef, $schema, $table, \%attr);

"undef"
"undef"
  $sth = $dbh->foreign_key_info(undef, $pk_schema, $pk_table,
                                undef, $fk_schema, $fk_table);

$sth
"undef"
  CASCADE              0
  RESTRICT             1
  SET NULL             2
  NO ACTION            3
  SET DEFAULT          4

"undef"
"PRAGMA foreign_key_list"
"PRAGMA"
  $sth = $dbh->statistics_info(undef, $schema, $table,
                                $unique_only, $quick);

$sth
"undef"
  my $bool = $dbh->ping;

"sqlite_"
  $dbh->func( ..., "(method name without sqlite_ prefix)" );

"sqlite_trace"
"func()"
  $dbh->func( ..., "sqlite_trace");

  $h->last_insert_id($catalog, $schema, $table_name, $field_name [, \%attr ])

"$h->last_insert_id("","","","")"
"$dbh->sqlite_last_insert_rowid()"
"undef"
  $dbh->sqlite_create_function( 'now', 0, sub { return time } );

  INSERT INTO mytable ( now() );

"DBD::SQLite"
  SELECT * from table WHERE column REGEXP '\bA\w+'

  SELECT * from table WHERE column REGEXP '(?i:\bA\w+)'

"create_function"
  $code_ref->($dbh, $collation_name)

"DBD::SQLite"
%DBD::SQLite::COLLATION
  package variance;

  sub new { bless [], shift; }

  sub step {
      my ( $self, $value ) = @_;

      push @$self, $value;
  }

  sub finalize {
      my $self = $_[0];

      my $n = @$self;

      # Variance is NULL unless there is more than one row
      return undef unless $n || $n == 1;

      my $mu = 0;
      foreach my $v ( @$self ) {
          $mu += $v;
      }
      $mu /= $n;

      my $sigma = 0;
      foreach my $v ( @$self ) {
          $sigma += ($v - $mu)**2;
      }
      $sigma = $sigma / ($n - 1);

      return $sigma;
  }

  $dbh->sqlite_create_aggregate( "variance", 1, 'variance' );

  SELECT group_name, variance(score)
  FROM results
  GROUP BY group_name;

$n_opcodes
"undef"
"sqlite_commit_hook"
"undef"
"$dbh->rollback()"
"sqlite_rollback_hook"
"undef"
"sqlite_update_hook"
"undef"
  $code_ref->($action_code, $database, $table, $rowid)

"DBD::SQLite::INSERT"
"DBD::SQLite::DELETE"
"DBD::SQLite::UPDATE"
"DBD::SQLite::OK"
"DBD::SQLite::IGNORE"
"DBD::SQLite::DENY"
"prepare"
  $code_ref->($action_code, $string1, $string2, $database, $trigger_or_view)

"main"
"temp"
"undef"
  $dbh->sqlite_enable_load_extension(1);
  $sth = $dbh->prepare("select load_extension('libsqlitefunctions.so')")
  or die "Cannot prepare: " . $dbh->errstr();

$file
$proc
"sqlite_enable_load_extension"
"sqlite_load_extension"
  $code_ref->($statement)

  $code_ref->($statement, $elapsed_time)

  my $status = $sth->sqlite_st_status();
  my $cur = $status->{fullscan_step};

  my $status = DBD::SQLite::sqlite_status();
  my $cur  = $status->{memory_used}{current};
  my $high = $status->{memory_used}{highwater};

"DBD::SQLite"
"use"
  use DBD::SQLite;
  $dbh->sqlite_set_authorizer(sub {
    my $action_code = shift;
    return $action_code == DBD::SQLite::DELETE ? DBD::SQLite::DENY
                                               : DBD::SQLite::OK;
  });

"DBD::SQLite"
  OK
  DENY
  IGNORE

  # constant              string1         string2
  # ========              =======         =======
  CREATE_INDEX            Index Name      Table Name
  CREATE_TABLE            Table Name      undef
  CREATE_TEMP_INDEX       Index Name      Table Name
  CREATE_TEMP_TABLE       Table Name      undef
  CREATE_TEMP_TRIGGER     Trigger Name    Table Name
  CREATE_TEMP_VIEW        View Name       undef
  CREATE_TRIGGER          Trigger Name    Table Name
  CREATE_VIEW             View Name       undef
  DELETE                  Table Name      undef
  DROP_INDEX              Index Name      Table Name
  DROP_TABLE              Table Name      undef
  DROP_TEMP_INDEX         Index Name      Table Name
  DROP_TEMP_TABLE         Table Name      undef
  DROP_TEMP_TRIGGER       Trigger Name    Table Name
  DROP_TEMP_VIEW          View Name       undef
  DROP_TRIGGER            Trigger Name    Table Name
  DROP_VIEW               View Name       undef
  INSERT                  Table Name      undef
  PRAGMA                  Pragma Name     1st arg or undef
  READ                    Table Name      Column Name
  SELECT                  undef           undef
  TRANSACTION             Operation       undef
  UPDATE                  Table Name      Column Name
  ATTACH                  Filename        undef
  DETACH                  Database Name   undef
  ALTER_TABLE             Database Name   Table Name
  REINDEX                 Index Name      undef
  ANALYZE                 Table Name      undef
  CREATE_VTABLE           Table Name      Module Name
  DROP_VTABLE             Table Name      Module Name
  FUNCTION                undef           Function Name
  SAVEPOINT               Operation       Savepoint Name

"DBD::SQLite"
"cmp"
"cmp"
"use locale"
  CREATE TABLE foo(
      txt1 COLLATE perl,
      txt2 COLLATE perllocale,
      txt3 COLLATE nocase
  )

  SELECT * FROM foo ORDER BY name COLLATE perllocale

"$dbh->{sqlite_unicode}"
"sqlite_unicode"
  my $dbh = DBI->connect(
      "dbi:SQLite:dbname=foo", "", "",
      {
          RaiseError     => 1,
          sqlite_unicode => 1,
      }
  );

$dbh
"DBD::SQLite"
%DBD::SQLite::COLLATION
  use DBD::SQLite;
  $DBD::SQLite::COLLATION{no_accents} = sub {
    my ( $a, $b ) = map lc, @_;
    tr[����������������������������]
      [aaaaaacdeeeeiiiinoooooouuuuy] for $a, $b;
    $a cmp $b;
  };
  my $dbh  = DBI->connect("dbi:SQLite:dbname=dbfile");
  my $sql  = "SELECT ... FROM ... ORDER BY ... COLLATE no_accents");
  my $rows = $dbh->selectall_arrayref($sql);

"perl"
"perllocale"
"perl"
"perllocale"
%DBD::SQLite::COLLATION
$dbh
  CREATE VIRTUAL TABLE city_buildings USING rtree(
     id,               -- Integer primary key
     minLong, maxLong, -- Minimum and maximum longitude
     minLat, maxLat    -- Minimum and maximum latitude
  );

  # IDs that are contained within query coordinates
  my $contained_sql = <<"";
  SELECT id FROM try_rtree
     WHERE  minLong >= ? AND maxLong <= ?
     AND    minLat  >= ? AND maxLat  <= ?

  # ... and those that overlap query coordinates
  my $overlap_sql = <<"";
  SELECT id FROM try_rtree
     WHERE    maxLong >= ? AND minLong <= ?
     AND      maxLat  >= ? AND minLat  <= ?

  my $contained = $dbh->selectcol_arrayref($contained_sql,undef,
                        $minLong, $maxLong, $minLat, $maxLat);

  my $overlapping = $dbh->selectcol_arrayref($overlap_sql,undef,
                        $minLong, $maxLong, $minLat, $maxLat);

"DBD::SQLite"
  use File::ShareDir 'dist_dir';
  use File::Spec::Functions 'catfile';

  # the whole sqlite3.h header
  my $sqlite3_h = catfile(dist_dir('DBD-SQLite'), 'sqlite3.h');

  # or only a particular header, amalgamated in sqlite3.c
  my $what_i_want = 'parse.h';
  my $sqlite3_c = catfile(dist_dir('DBD-SQLite'), 'sqlite3.c');
  open my $fh, '<', $sqlite3_c or die $!;
  my $code = do { local $/; <$fh> };
  my ($parse_h) = $code =~ m{(
    /\*+[ ]Begin[ ]file[ ]$what_i_want[ ]\*+
    .+?
    /\*+[ ]End[ ]of[ ]$what_i_want[ ]\*+/
  )}sx;
  open my $out, '>', $what_i_want or die $!;
  print $out $parse_h;
  close $out;

"Makefile.PL"
"CONFIGURE_REQUIRES"
"sqlite2_blob_open"
"sqlite2_blob_close"
"sqlite3.[ch]"

SYNOPSIS

DESCRIPTION

SQLITE VERSION

NOTABLE DIFFERENCES FROM OTHER DRIVERS

Database Name Is A File Name

Read-Only Database

DBD::SQLite And File::Temp

DBD::SQLite and fork()

Accessing A Database With Other Tools

Blobs

Functions And Bind Parameters

Placeholders

Pragma

Foreign Keys

Transactions

Transaction and Database Locking

$sth->finish and Transaction Rollback

Processing Multiple Statements At A Time

Performance

DRIVER PRIVATE ATTRIBUTES

Database Handle Attributes

Statement Handle Attributes

METHODS

table_info

primary_key, primary_key_info

foreign_key_info

statistics_info

ping

DRIVER PRIVATE METHODS

$dbh->sqlite_last_insert_rowid()

$dbh->sqlite_db_filename()

$dbh->sqlite_busy_timeout()

$dbh->sqlite_busy_timeout( $ms )

$dbh->sqlite_create_function( $name, $argc, $code_ref, $flags )

$dbh->sqlite_create_collation( $name, $code_ref )

$dbh->sqlite_collation_needed( $code_ref )

$dbh->sqlite_create_aggregate( $name, $argc, $pkg, $flags )

$dbh->sqlite_progress_handler( $n_opcodes, $code_ref )

$dbh->sqlite_commit_hook( $code_ref )

$dbh->sqlite_rollback_hook( $code_ref )

$dbh->sqlite_update_hook( $code_ref )

$dbh->sqlite_set_authorizer( $code_ref )

$dbh->sqlite_backup_from_file( $filename )

$dbh->sqlite_backup_to_file( $filename )

$dbh->sqlite_enable_load_extension( $bool )

$dbh->sqlite_load_extension( $file, $proc )

$dbh->sqlite_trace( $code_ref )

$dbh->sqlite_profile( $code_ref )

$dbh->sqlite_table_column_metadata( $dbname, $tablename, $columnname )

$dbh->sqlite_db_status()

$sth->sqlite_st_status()

$dbh->sqlite_create_module()

DRIVER FUNCTIONS

DBD::SQLite::compile_options()

DBD::SQLite::sqlite_status()

DBD::SQLite::strlike($pattern, $string, $escape_char), DBD::SQLite::strglob($pattern, $string)

DRIVER CONSTANTS

Authorizer Return Codes

Action Codes

COLLATION FUNCTIONS

Definition

Builtin collation sequences

Usage

Unicode handling

Adding user-defined collations

FULLTEXT SEARCH

R* TREE SUPPORT

VIRTUAL TABLES IMPLEMENTED IN PERL

FOR DBD::SQLITE EXTENSION AUTHORS

TO DO

Leak Detection

Stream API for Blobs

Support for custom callbacks for R-Tree queries

SUPPORT

AUTHORS

COPYRIGHT

LAST SEARCHED