← Index
Performance Profile   « block view • line view • sub view »
For /wise/base/deliv/dev/bin/getfix
  Run on Thu May 20 15:30:03 2010
Reported on Thu May 20 16:25:29 2010

File/wise/base/static/lib/perl5/site_perl/5.10.0/x86_64-linux-thread-multi/DBD/SQLite.pm
Statements Executed68
Total Time0.011269 seconds

Subroutines — ordered by exclusive time
Calls P F Exclusive
Time		 Inclusive
Time		 Subroutine
2120.004310.00431DBD::SQLite::db::_login (xsub)
2120.000160.00016DBD::SQLite::bootstrap (xsub)
1111.1e-59.6e-5DBD::SQLite::driver
00000DBD::SQLite::BEGIN
00000DBD::SQLite::CLONE
00000DBD::SQLite::db::_get_version
00000DBD::SQLite::db::column_info
00000DBD::SQLite::db::get_info
00000DBD::SQLite::db::prepare
00000DBD::SQLite::db::primary_key_info
00000DBD::SQLite::db::table_info
00000DBD::SQLite::db::type_info_all
00000DBD::SQLite::dr::BEGIN
00000DBD::SQLite::dr::__ANON__[:90]
00000DBD::SQLite::dr::__ANON__[:91]
00000DBD::SQLite::dr::connect

LineStmts.Exclusive
Time		Avg.Code
1package DBD::SQLite;
2
336.5e-52.2e-5use 5.006;
433.4e-51.1e-5use strict;
# spent 13µs making 1 call to strict::import
536.4e-52.1e-5use DBI 1.57 ();
# spent 37µs making 1 call to UNIVERSAL::VERSION
632.4e-58.0e-6use DynaLoader ();
7
833.4e-51.1e-5use vars qw($VERSION @ISA);
# spent 45µs making 1 call to vars::import
936.9e-52.3e-5use vars qw{$err $errstr $drh $sqlite_version};
# spent 53µs making 1 call to vars::import
10BEGIN {
1161.0e-51.7e-6 $VERSION = '1.23';
12 @ISA = ('DynaLoader');
13
14 # Initialize errors
15 $err = undef;
16 $errstr = undef;
17
18 # Driver singleton
19 $drh = undef;
20
21 # sqlite_version cache
22 $sqlite_version = undef;
2310.000500.00050}
24
2511.5e-51.5e-5__PACKAGE__->bootstrap($VERSION);
# spent 2.60ms making 1 call to DynaLoader::bootstrap
26
27
# spent 96µs (11+85) within DBD::SQLite::driver which was called # once (11µs+85µs) by DBI::install_driver at line 786 of /wise/base/static/lib/perl5/site_perl/5.10.0/x86_64-linux-thread-multi/DBI.pm
sub driver {
2811.0e-51.0e-5 $drh or
# spent 85µs making 1 call to DBI::_new_drh
29 $drh = DBI::_new_drh( "$_[0]::dr", {
30 Name => 'SQLite',
31 Version => $VERSION,
32 Attribution => 'DBD::SQLite by Matt Sergeant et al',
33 } );
34}
35
36sub CLONE {
37 undef $drh;
38}
39
40package DBD::SQLite::dr;
41
42sub connect {
43130.004460.00034 my ($drh, $dbname, $user, $auth, $attr) = @_;
44
45 # Default PrintWarn to the value of $^W
46 unless ( defined $attr->{PrintWarn} ) {
47 $attr->{PrintWarn} = $^W ? 1 : 0;
48 }
49
50 my $dbh = DBI::_new_dbh( $drh, {
# spent 60µs making 1 call to DBI::_new_dbh
51 Name => $dbname,
52 } );
53
54 my $real = $dbname;
55 if ( $dbname =~ /=/ ) {
56 foreach my $attrib ( split(/;/, $dbname) ) {
57 my ($key, $value) = split(/=/, $attrib, 2);
58 if ( $key eq 'dbname' ) {
59 $real = $value;
60 } else {
61 $attr->{$key} = $value;
62 }
63 }
64 }
65
66 # To avoid unicode and long file name problems on Windows,
67 # convert to the shortname if the file (or parent directory) exists.
68 if ( $^O eq 'MSWin32' and $real ne ':memory:' ) {
69 require Win32;
70 require File::Basename;
71 my ($file, $dir, $suffix) = File::Basename::fileparse($real);
72 my $short = Win32::GetShortPathName($real);
73 if ( $short && -f $short ) {
74 # Existing files will work directly.
75 $real = $short;
76 } elsif ( -d $dir ) {
77 # We are creating a new file.
78 # Does the directory it's in at least exist?
79 $real = join '', grep { defined } Win32::GetShortPathName($dir), $file, $suffix;
80 } else {
81 # SQLite can't do mkpath anyway.
82 # So let it go through as it and fail.
83 }
84 }
85
86 # Hand off to the actual login function
87 DBD::SQLite::db::_login($dbh, $real, $user, $auth) or return undef;
# spent 4.31ms making 1 call to DBD::SQLite::db::_login
88
89 # Install perl collations
9034.0e-61.3e-6 my $perl_collation = sub { $_[0] cmp $_[1] };
9160.001510.00025 my $perl_locale_collation = sub { use locale; $_[0] cmp $_[1] };
# spent 11µs making 1 call to locale::import
9214.8e-54.8e-5 $dbh->func( "perl", $perl_collation, "create_collation" );
# spent 101µs making 1 call to DBI::common::func
9311.4e-51.4e-5 $dbh->func( "perllocale", $perl_locale_collation, "create_collation" );
# spent 30µs making 1 call to DBI::common::func
94
95 # HACK: Since PrintWarn = 0 doesn't seem to actually prevent warnings
96 # in DBD::SQLite we set Warn to false if PrintWarn is false.
97 unless ( $attr->{PrintWarn} ) {
98 $attr->{Warn} = 0;
99 }
100
101 return $dbh;
102}
103
104package DBD::SQLite::db;
105
106sub prepare {
107150.004400.00029 my $dbh = shift;
108 my $sql = shift;
109
110 my $sth = DBI::_new_sth( $dbh, {
# spent 183µs making 3 calls to DBI::_new_sth, avg 61µs/call
111 Statement => $sql,
112 } );
113
114 DBD::SQLite::st::_prepare($sth, $sql, @_) or return undef;
# spent 4.35ms making 3 calls to DBD::SQLite::st::_prepare, avg 1.45ms/call
115
116 return $sth;
117}
118
119sub _get_version {
120 return ( DBD::SQLite::db::FETCH($_[0], 'sqlite_version') );
121}
122
12317.0e-67.0e-6my %info = (
124 17 => 'SQLite', # SQL_DBMS_NAME
125 18 => \&_get_version, # SQL_DBMS_VER
126 29 => '"', # SQL_IDENTIFIER_QUOTE_CHAR
127);
128
129sub get_info {
130 my($dbh, $info_type) = @_;
131 my $v = $info{int($info_type)};
132 $v = $v->($dbh) if ref $v eq 'CODE';
133 return $v;
134}
135
136# SQL/CLI (ISO/IEC JTC 1/SC 32 N 0595), 6.63 Tables
137# Based on DBD::Oracle's
138# See also http://www.ch-werner.de/sqliteodbc/html/sqliteodbc_8c.html#a117
139sub table_info {
140 my ($dbh, $cat_val, $sch_val, $tbl_val, $typ_val) = @_;
141
142 my @where = ();
143 my $sql;
144 if ( defined($cat_val) && $cat_val eq '%'
145 && defined($sch_val) && $sch_val eq ''
146 && defined($tbl_val) && $tbl_val eq '') { # Rule 19a
147 $sql = <<'END_SQL';
148SELECT NULL TABLE_CAT
149 , NULL TABLE_SCHEM
150 , NULL TABLE_NAME
151 , NULL TABLE_TYPE
152 , NULL REMARKS
153END_SQL
154 }
155 elsif ( defined($sch_val) && $sch_val eq '%'
156 && defined($cat_val) && $cat_val eq ''
157 && defined($tbl_val) && $tbl_val eq '') { # Rule 19b
158 $sql = <<'END_SQL';
159SELECT NULL TABLE_CAT
160 , NULL TABLE_SCHEM
161 , NULL TABLE_NAME
162 , NULL TABLE_TYPE
163 , NULL REMARKS
164END_SQL
165 }
166 elsif ( defined($typ_val) && $typ_val eq '%'
167 && defined($cat_val) && $cat_val eq ''
168 && defined($sch_val) && $sch_val eq ''
169 && defined($tbl_val) && $tbl_val eq '') { # Rule 19c
170 $sql = <<'END_SQL';
171SELECT NULL TABLE_CAT
172 , NULL TABLE_SCHEM
173 , NULL TABLE_NAME
174 , t.tt TABLE_TYPE
175 , NULL REMARKS
176FROM (
177 SELECT 'TABLE' tt UNION
178 SELECT 'VIEW' tt UNION
179 SELECT 'LOCAL TEMPORARY' tt
180) t
181ORDER BY TABLE_TYPE
182END_SQL
183 }
184 else {
185 $sql = <<'END_SQL';
186SELECT *
187FROM
188(
189SELECT NULL TABLE_CAT
190 , NULL TABLE_SCHEM
191 , tbl_name TABLE_NAME
192 , TABLE_TYPE
193 , NULL REMARKS
194 , sql sqlite_sql
195FROM (
196 SELECT tbl_name, upper(type) TABLE_TYPE, sql
197 FROM sqlite_master
198 WHERE type IN ( 'table','view')
199UNION ALL
200 SELECT tbl_name, 'LOCAL TEMPORARY' TABLE_TYPE, sql
201 FROM sqlite_temp_master
202 WHERE type IN ( 'table','view')
203UNION ALL
204 SELECT 'sqlite_master' tbl_name, 'SYSTEM TABLE' TABLE_TYPE, NULL sql
205UNION ALL
206 SELECT 'sqlite_temp_master' tbl_name, 'SYSTEM TABLE' TABLE_TYPE, NULL sql
207)
208)
209END_SQL
210 if ( defined $tbl_val ) {
211 push @where, "TABLE_NAME LIKE '$tbl_val'";
212 }
213 if ( defined $typ_val ) {
214 my $table_type_list;
215 $typ_val =~ s/^\s+//;
216 $typ_val =~ s/\s+$//;
217 my @ttype_list = split (/\s*,\s*/, $typ_val);
218 foreach my $table_type (@ttype_list) {
219 if ($table_type !~ /^'.*'$/) {
220 $table_type = "'" . $table_type . "'";
221 }
222 $table_type_list = join(", ", @ttype_list);
223 }
224 push @where, "TABLE_TYPE IN (\U$table_type_list)" if $table_type_list;
225 }
226 $sql .= ' WHERE ' . join("\n AND ", @where ) . "\n" if @where;
227 $sql .= " ORDER BY TABLE_TYPE, TABLE_SCHEM, TABLE_NAME\n";
228 }
229 my $sth = $dbh->prepare($sql) or return undef;
230 $sth->execute or return undef;
231 $sth;
232}
233
234sub primary_key_info {
235 my($dbh, $catalog, $schema, $table) = @_;
236
237 # This is a hack but much simpler than using pragma index_list etc
238 # also the pragma doesn't list 'INTEGER PRIMARK KEY' autoinc PKs!
239 my @pk_info;
240 my $sth_tables = $dbh->table_info($catalog, $schema, $table, '');
241 while ( my $row = $sth_tables->fetchrow_hashref ) {
242 my $sql = $row->{sqlite_sql} or next;
243 next unless $sql =~ /(.*?)\s*PRIMARY\s+KEY\s*(?:\(\s*(.*?)\s*\))?/si;
244 my @pk = split /\s*,\s*/, $2 || '';
245 unless ( @pk ) {
246 my $prefix = $1;
247 $prefix =~ s/.*create\s+table\s+.*?\(\s*//si;
248 $prefix = (split /\s*,\s*/, $prefix)[-1];
249 @pk = (split /\s+/, $prefix)[0]; # take first word as name
250 }
251 my $key_seq = 0;
252 foreach my $pk_field (@pk) {
253 push @pk_info, {
254 TABLE_SCHEM => $row->{TABLE_SCHEM},
255 TABLE_NAME => $row->{TABLE_NAME},
256 COLUMN_NAME => $pk_field,
257 KEY_SEQ => ++$key_seq,
258 PK_NAME => 'PRIMARY KEY',
259 };
260 }
261 }
262
263 my $sponge = DBI->connect("DBI:Sponge:", '','')
264 or return $dbh->DBI::set_err($DBI::err, "DBI::Sponge: $DBI::errstr");
265 my @names = qw(TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME KEY_SEQ PK_NAME);
266 my $sth = $sponge->prepare( "column_info $table", {
267 rows => [ map { [ @{$_}{@names} ] } @pk_info ],
268 NUM_OF_FIELDS => scalar @names,
269 NAME => \@names,
270 }) or return $dbh->DBI::set_err(
271 $sponge->err(),
272 $sponge->errstr()
273 );
274 return $sth;
275}
276
277sub type_info_all {
278 return; # XXX code just copied from DBD::Oracle, not yet thought about
279# return [
280# {
281# TYPE_NAME => 0,
282# DATA_TYPE => 1,
283# COLUMN_SIZE => 2,
284# LITERAL_PREFIX => 3,
285# LITERAL_SUFFIX => 4,
286# CREATE_PARAMS => 5,
287# NULLABLE => 6,
288# CASE_SENSITIVE => 7,
289# SEARCHABLE => 8,
290# UNSIGNED_ATTRIBUTE => 9,
291# FIXED_PREC_SCALE => 10,
292# AUTO_UNIQUE_VALUE => 11,
293# LOCAL_TYPE_NAME => 12,
294# MINIMUM_SCALE => 13,
295# MAXIMUM_SCALE => 14,
296# SQL_DATA_TYPE => 15,
297# SQL_DATETIME_SUB => 16,
298# NUM_PREC_RADIX => 17,
299# },
300# [ 'CHAR', 1, 255, '\'', '\'', 'max length', 1, 1, 3,
301# undef, '0', '0', undef, undef, undef, 1, undef, undef
302# ],
303# [ 'NUMBER', 3, 38, undef, undef, 'precision,scale', 1, '0', 3,
304# '0', '0', '0', undef, '0', 38, 3, undef, 10
305# ],
306# [ 'DOUBLE', 8, 15, undef, undef, undef, 1, '0', 3,
307# '0', '0', '0', undef, undef, undef, 8, undef, 10
308# ],
309# [ 'DATE', 9, 19, '\'', '\'', undef, 1, '0', 3,
310# undef, '0', '0', undef, '0', '0', 11, undef, undef
311# ],
312# [ 'VARCHAR', 12, 1024*1024, '\'', '\'', 'max length', 1, 1, 3,
313# undef, '0', '0', undef, undef, undef, 12, undef, undef
314# ]
315# ];
316}
317
318# Taken from Fey::Loader::SQLite
319sub column_info {
320 my($dbh, $catalog, $schema, $table, $column) = @_;
321
322 if ( defined $column and $column eq '%' ) {
323 $column = undef;
324 }
325
326 my $sth_columns = $dbh->prepare( "PRAGMA table_info('$table')" );
327 $sth_columns->execute;
328
329 my @names = qw(
330 TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME
331 DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH
332 DECIMAL_DIGITS NUM_PREC_RADIX NULLABLE
333 REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB
334 CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE
335 );
336
337 my @cols;
338 while ( my $col_info = $sth_columns->fetchrow_hashref ) {
339 next if defined $column && $column ne $col_info->{name};
340
341 my %col = (
342 TABLE_NAME => $table,
343 COLUMN_NAME => $col_info->{name},
344 );
345
346 my $type = $col_info->{type};
347 if ( $type =~ s/(\w+)\((\d+)(?:,(\d+))?\)/$1/ ) {
348 $col{COLUMN_SIZE} = $2;
349 $col{DECIMAL_DIGITS} = $3;
350 }
351
352 $col{TYPE_NAME} = $type;
353
354 if ( defined $col_info->{dflt_value} ) {
355 $col{COLUMN_DEF} = $col_info->{dflt_value}
356 }
357
358 if ( $col_info->{notnull} ) {
359 $col{NULLABLE} = 0;
360 $col{IS_NULLABLE} = 'NO';
361 } else {
362 $col{NULLABLE} = 1;
363 $col{IS_NULLABLE} = 'YES';
364 }
365
366 foreach my $key ( @names ) {
367 next if exists $col{$key};
368 $col{$key} = undef;
369 }
370
371 push @cols, \%col;
372 }
373
374 my $sponge = DBI->connect("DBI:Sponge:", '','')
375 or return $dbh->DBI::set_err($DBI::err, "DBI::Sponge: $DBI::errstr");
376 my $sth = $sponge->prepare( "column_info $table", {
377 rows => [ map { [ @{$_}{@names} ] } @cols ],
378 NUM_OF_FIELDS => scalar @names,
379 NAME => \@names,
380 } ) or return $dbh->DBI::set_err(
381 $sponge->err,
382 $sponge->errstr,
383 );
384 return $sth;
385}
386
38717.0e-67.0e-61;
388
389__END__
390
391=pod
392
393=head1 NAME
394
395DBD::SQLite - Self-contained RDBMS in a DBI Driver
396
397=head1 SYNOPSIS
398
399 use DBI;
400 my $dbh = DBI->connect("dbi:SQLite:dbname=dbfile","","");
401
402=head1 DESCRIPTION
403
404SQLite is a public domain RDBMS database engine that you can find
405at L<http://www.sqlite.org/>.
406
407Rather than ask you to install SQLite first, because SQLite is public
408domain, B<DBD::SQLite> includes the entire thing in the distribution.
409So in order to get a fast transaction capable RDBMS working for your
410perl project you simply have to install this module, and B<nothing>
411else.
412
413SQLite supports the following features:
414
415=over 4
416
417=item Implements a large subset of SQL92
418
419See L<http://www.sqlite.org/lang.html> for details.
420
421=item A complete DB in a single disk file
422
423Everything for your database is stored in a single disk file, making it
424easier to move things around than with L<DBD::CSV>.
425
426=item Atomic commit and rollback
427
428Yes, B<DBD::SQLite> is small and light, but it supports full transactions!
429
430=item Extensible
431
432User-defined aggregate or regular functions can be registered with the
433SQL parser.
434
435=back
436
437There's lots more to it, so please refer to the docs on the SQLite web
438page, listed above, for SQL details. Also refer to L<DBI> for details
439on how to use DBI itself.
440
441=head1 CONFORMANCE WITH DBI SPECIFICATION
442
443The API works like every DBI module does. Please see L<DBI> for more
444details about core features.
445
446Currently many statement attributes are not implemented or are
447limited by the typeless nature of the SQLite database.
448
449=head1 DRIVER PRIVATE ATTRIBUTES
450
451=head2 Database Handle Attributes
452
453=over 4
454
455=item sqlite_version
456
457Returns the version of the SQLite library which B<DBD::SQLite> is using,
458e.g., "2.8.0". Can only be read.
459
460=item unicode
461
462If set to a true value, B<DBD::SQLite> will turn the UTF-8 flag on for all text
463strings coming out of the database (this feature is currently disabled for perl < 5.8.5). For more details on the UTF-8 flag see
464L<perlunicode>. The default is for the UTF-8 flag to be turned off.
465
466Also note that due to some bizarreness in SQLite's type system (see
467L<http://www.sqlite.org/datatype3.html>), if you want to retain
468blob-style behavior for B<some> columns under C<< $dbh->{unicode} = 1
469>> (say, to store images in the database), you have to state so
470explicitly using the 3-argument form of L<DBI/bind_param> when doing
471updates:
472
473 use DBI qw(:sql_types);
474 $dbh->{unicode} = 1;
475 my $sth = $dbh->prepare("INSERT INTO mytable (blobcolumn) VALUES (?)");
476
477 # Binary_data will be stored as is.
478 $sth->bind_param(1, $binary_data, SQL_BLOB);
479
480Defining the column type as C<BLOB> in the DDL is B<not> sufficient.
481
482=back
483
484=head1 DRIVER PRIVATE METHODS
485
486=head2 $dbh->func('last_insert_rowid')
487
488This method returns the last inserted rowid. If you specify an INTEGER PRIMARY
489KEY as the first column in your table, that is the column that is returned.
490Otherwise, it is the hidden ROWID column. See the sqlite docs for details.
491
492Note: You can now use $dbh-E<gt>last_insert_id() if you have a recent version of
493DBI.
494
495=head2 $dbh->func('busy_timeout')
496
497Retrieve the current busy timeout.
498
499=head2 $dbh->func( $ms, 'busy_timeout' )
500
501Set the current busy timeout. The timeout is in milliseconds.
502
503=head2 $dbh->func( $name, $argc, $code_ref, "create_function" )
504
505This method will register a new function which will be useable in an SQL
506query. The method's parameters are:
507
508=over
509
510=item $name
511
512The name of the function. This is the name of the function as it will
513be used from SQL.
514
515=item $argc
516
517The number of arguments taken by the function. If this number is -1,
518the function can take any number of arguments.
519
520=item $code_ref
521
522This should be a reference to the function's implementation.
523
524=back
525
526For example, here is how to define a now() function which returns the
527current number of seconds since the epoch:
528
529 $dbh->func( 'now', 0, sub { return time }, 'create_function' );
530
531After this, it could be use from SQL as:
532
533 INSERT INTO mytable ( now() );
534
535=head2 $dbh->func( $name, $code_ref, "create_collation" )
536
537This method will register a new function which will be useable in an SQL
538query as a COLLATE option for sorting. The method's parameters are:
539
540=over
541
542=item $name
543
544The name of the function. This is the name of the function as it will
545be used from SQL.
546
547=item $code_ref
548
549This should be a reference to the function's implementation.
550The driver will check that this is a proper sorting function.
551
552=back
553
554Collations C<binary> and C<nocase> are builtin within SQLite.
555Collations C<perl> and C<perllocale> are builtin within
556the B<DBD::SQLite> driver, and correspond to the
557Perl C<cmp> operator with or without the L<locale> pragma;
558so you can write for example
559
560 CREATE TABLE foo(
561 txt1 COLLATE perl,
562 txt2 COLLATE perllocale,
563 txt3 COLLATE nocase
564 )
565
566or
567
568 SELECT * FROM foo ORDER BY name COLLATE perllocale
569
570If the attribute C<< $dbh->{unicode} >> is set, strings coming from
571the database and passed to the collation function will be properly
572tagged with the utf8 flag; but this only works if the
573C<unicode> attribute is set B<before> the call to
574C<create_collation>. The recommended way to activate unicode
575is to set the parameter at connection time :
576
577 my $dbh = DBI->connect(
578 "dbi:SQLite:dbname=foo", "", "",
579 {
580 RaiseError => 1,
581 unicode => 1,
582 }
583 );
584
585=head2 $dbh->func( $name, $argc, $pkg, 'create_aggregate' )
586
587This method will register a new aggregate function which can then be used
588from SQL. The method's parameters are:
589
590=over
591
592=item $name
593
594The name of the aggregate function, this is the name under which the
595function will be available from SQL.
596
597=item $argc
598
599This is an integer which tells the SQL parser how many arguments the
600function takes. If that number is -1, the function can take any number
601of arguments.
602
603=item $pkg
604
605This is the package which implements the aggregator interface.
606
607=back
608
609The aggregator interface consists of defining three methods:
610
611=over
612
613=item new()
614
615This method will be called once to create an object which should
616be used to aggregate the rows in a particular group. The step() and
617finalize() methods will be called upon the reference return by
618the method.
619
620=item step(@_)
621
622This method will be called once for each row in the aggregate.
623
624=item finalize()
625
626This method will be called once all rows in the aggregate were
627processed and it should return the aggregate function's result. When
628there is no rows in the aggregate, finalize() will be called right
629after new().
630
631=back
632
633Here is a simple aggregate function which returns the variance
634(example adapted from pysqlite):
635
636 package variance;
637
638 sub new { bless [], shift; }
639
640 sub step {
641 my ( $self, $value ) = @_;
642
643 push @$self, $value;
644 }
645
646 sub finalize {
647 my $self = $_[0];
648
649 my $n = @$self;
650
651 # Variance is NULL unless there is more than one row
652 return undef unless $n || $n == 1;
653
654 my $mu = 0;
655 foreach my $v ( @$self ) {
656 $mu += $v;
657 }
658 $mu /= $n;
659
660 my $sigma = 0;
661 foreach my $v ( @$self ) {
662 $sigma += ($x - $mu)**2;
663 }
664 $sigma = $sigma / ($n - 1);
665
666 return $sigma;
667 }
668
669 $dbh->func( "variance", 1, 'variance', "create_aggregate" );
670
671The aggregate function can then be used as:
672
673 SELECT group_name, variance(score)
674 FROM results
675 GROUP BY group_name;
676
677=head2 $dbh->func( $n_opcodes, $code_ref, 'progress_handler' )
678
679This method registers a handler to be invoked
680periodically during long running calls to SQLite.
681An example use for this interface is to keep a GUI
682updated during a large query.
683The parameters are:
684
685=over
686
687=item $n_opcodes
688
689The progress handler is invoked once for every C<$n_opcodes>
690virtual machine opcodes in SQLite.
691
692=item $handler
693
694Reference to the handler subroutine. If the progress handler returns
695non-zero, the SQLite operation is interrupted. This feature can be used to
696implement a "Cancel" button on a GUI dialog box.
697
698Set this argument to C<undef> if you want to unregister a previous
699progress handler.
700
701=back
702
703=head1 BLOBS
704
705As of version 1.11, blobs should "just work" in SQLite as text columns. However
706this will cause the data to be treated as a string, so SQL statements such
707as length(x) will return the length of the column as a NUL terminated string,
708rather than the size of the blob in bytes. In order to store natively as a
709BLOB use the following code:
710
711 use DBI qw(:sql_types);
712 my $dbh = DBI->connect("dbi:SQLite:dbfile","","");
713
714 my $blob = `cat foo.jpg`;
715 my $sth = $dbh->prepare("INSERT INTO mytable VALUES (1, ?)");
716 $sth->bind_param(1, $blob, SQL_BLOB);
717 $sth->execute();
718
719And then retrieval just works:
720
721 $sth = $dbh->prepare("SELECT * FROM mytable WHERE id = 1");
722 $sth->execute();
723 my $row = $sth->fetch;
724 my $blobo = $row->[1];
725
726 # now $blobo == $blob
727
728=head1 NOTES
729
730Although the database is stored in a single file, the directory containing the
731database file must be writable by SQLite because the library will create
732several temporary files there.
733
734To access the database from the command line, try using dbish which comes with
735the DBI module. Just type:
736
737 dbish dbi:SQLite:foo.db
738
739On the command line to access the file F<foo.db>.
740
741Alternatively you can install SQLite from the link above without conflicting
742with B<DBD::SQLite> and use the supplied C<sqlite> command line tool.
743
744=head1 PERFORMANCE
745
746SQLite is fast, very fast. I recently processed my 72MB log file with it,
747inserting the data (400,000+ rows) by using transactions and only committing
748every 1000 rows (otherwise the insertion is quite slow), and then performing
749queries on the data.
750
751Queries like count(*) and avg(bytes) took fractions of a second to return,
752but what surprised me most of all was:
753
754 SELECT url, count(*) as count
755 FROM access_log
756 GROUP BY url
757 ORDER BY count desc
758 LIMIT 20
759
760To discover the top 20 hit URLs on the site (L<http://axkit.org>), and it
761returned within 2 seconds. I'm seriously considering switching my log
762analysis code to use this little speed demon!
763
764Oh yeah, and that was with no indexes on the table, on a 400MHz PIII.
765
766For best performance be sure to tune your hdparm settings if you are
767using linux. Also you might want to set:
768
769 PRAGMA default_synchronous = OFF
770
771Which will prevent sqlite from doing fsync's when writing (which
772slows down non-transactional writes significantly) at the expense of some
773peace of mind. Also try playing with the cache_size pragma.
774
775=head1 SUPPORT
776
777Bugs should be reported via the CPAN bug tracker at
778
779L<http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite>
780
781=head1 TO DO
782
783There're several pended RT bugs/patches at the moment
784(mainly due to the lack of tests/patches or segfaults on tests).
785
786Here's the list.
787
788L<http://rt.cpan.org/Public/Bug/Display.html?id=35449>
789(breaks tests)
790
791L<http://rt.cpan.org/Public/Bug/Display.html?id=29629>
792(requires a patch)
793
794L<http://rt.cpan.org/Public/Bug/Display.html?id=29058>
795(requires a patch)
796
797Switch tests to L<Test::More> to support more advanced testing behaviours
798
799=head1 AUTHOR
800
801Matt Sergeant E<lt>matt@sergeant.orgE<gt>
802
803Francis J. Lacoste E<lt>flacoste@logreport.orgE<gt>
804
805Wolfgang Sourdeau E<lt>wolfgang@logreport.orgE<gt>
806
807Adam Kennedy E<lt>adamk@cpan.orgE<gt>
808
809Max Maischein E<lt>corion@cpan.orgE<gt>
810
811=head1 COPYRIGHT
812
813The bundled SQLite code in this distribution is Public Domain.
814
815DBD::SQLite is copyright 2002 - 2007 Matt Sergeant.
816
817Some parts copyright 2008 Francis J. Lacoste and Wolfgang Sourdeau.
818
819Some parts copyright 2008 - 2009 Adam Kennedy.
820
821Some parts derived from L<DBD::SQLite::Amalgamation>
822copyright 2008 Audrey Tang.
823
824This program is free software; you can redistribute
825it and/or modify it under the same terms as Perl itself.
826
827The full text of the license can be found in the
828LICENSE file included with this module.
829
830=cut
# spent 160µs within DBD::SQLite::bootstrap which was called # once (160µs+0) by DynaLoader::bootstrap at line 226 of /opt/wise/lib/perl5/5.10.0/x86_64-linux-thread-multi/DynaLoader.pm
sub DBD::SQLite::bootstrap; # xsub
# spent 4.31ms within DBD::SQLite::db::_login which was called # once (4.31ms+0) by DBD::SQLite::dr::connect at line 87 of /wise/base/static/lib/perl5/site_perl/5.10.0/x86_64-linux-thread-multi/DBD/SQLite.pm
sub DBD::SQLite::db::_login; # xsub

Report produced by the NYTProf 2.07 Perl profiler, developed by Tim Bunce and Adam Kaplan.