1DBD::SQLite::Cookbook(3U)ser Contributed Perl DocumentatiDoBnD::SQLite::Cookbook(3)
2
6 DBD::SQLite::Cookbook - The DBD::SQLite Cookbook
7
9 This is the DBD::SQLite cookbook.
10 It is intended to provide a place to keep a variety of functions and
12 formals for use in callback APIs in DBD::SQLite.
13
15 Variance
16 This is a simple aggregate function which returns a variance. It is
17 adapted from an example implementation in pysqlite.
18 package variance;
20 sub new { bless [], shift; }
22 sub step {
24 my ( $self, $value ) = @_;
25 push @$self, $value;
27 }
28 sub finalize {
30 my $self = $_[0];
31 my $n = @$self;
33 # Variance is NULL unless there is more than one row
35 return undef unless $n || $n == 1;
36 my $mu = 0;
38 foreach my $v ( @$self ) {
39 $mu += $v;
40 }
41 $mu /= $n;
42 my $sigma = 0;
44 foreach my $v ( @$self ) {
45 $sigma += ($v - $mu)**2;
46 }
47 $sigma = $sigma / ($n - 1);
48 return $sigma;
50 }
51 # NOTE: If you use an older DBI (< 1.608),
53 # use $dbh->func(..., "create_aggregate") instead.
54 $dbh->sqlite_create_aggregate( "variance", 1, 'variance' );
55 The function can then be used as:
57 SELECT group_name, variance(score)
59 FROM results
60 GROUP BY group_name;
61 Variance (Memory Efficient)
63 A more efficient variance function, optimized for memory usage at the
64 expense of precision:
65 package variance2;
67 sub new { bless {sum => 0, count=>0, hash=> {} }, shift; }
69 sub step {
71 my ( $self, $value ) = @_;
72 my $hash = $self->{hash};
73 # by truncating and hashing, we can comsume many more data points
75 $value = int($value); # change depending on need for precision
76 # use sprintf for arbitrary fp precision
77 if (exists $hash->{$value}) {
78 $hash->{$value}++;
79 } else {
80 $hash->{$value} = 1;
81 }
82 $self->{sum} += $value;
83 $self->{count}++;
84 }
85 sub finalize {
87 my $self = $_[0];
88 # Variance is NULL unless there is more than one row
90 return undef unless $self->{count} > 1;
91 # calculate avg
93 my $mu = $self->{sum} / $self->{count};
94 my $sigma = 0;
96 while (my ($h, $v) = each %{$self->{hash}}) {
97 $sigma += (($h - $mu)**2) * $v;
98 }
99 $sigma = $sigma / ($self->{count} - 1);
100 return $sigma;
102 }
103 The function can then be used as:
105 SELECT group_name, variance2(score)
107 FROM results
108 GROUP BY group_name;
109 Variance (Highly Scalable)
111 A third variable implementation, designed for arbitrarily large data
112 sets:
113 package variance3;
115 sub new { bless {mu=>0, count=>0, S=>0}, shift; }
117 sub step {
119 my ( $self, $value ) = @_;
120 $self->{count}++;
121 my $delta = $value - $self->{mu};
122 $self->{mu} += $delta/$self->{count};
123 $self->{S} += $delta*($value - $self->{mu});
124 }
125 sub finalize {
127 my $self = $_[0];
128 return $self->{S} / ($self->{count} - 1);
129 }
130 The function can then be used as:
132 SELECT group_name, variance3(score)
134 FROM results
135 GROUP BY group_name;
136
138 Sparing database disk space
139 As explained in <http://www.sqlite.org/fts3.html#fts4_options>, several
140 options are available to specify how SQLite should store indexed
141 documents.
142 One strategy is to use SQLite only for the fulltext index and metadata,
144 and keep the full documents outside of SQLite; to do so, use the
145 "content=""" option. For example, the following SQL creates an FTS4
146 table with three columns - "a", "b", and "c":
147 CREATE VIRTUAL TABLE t1 USING fts4(content="", a, b, c);
149 Data can be inserted into such an FTS4 table using an INSERT
151 statements. However, unlike ordinary FTS4 tables, the user must supply
152 an explicit integer docid value. For example:
153 -- This statement is Ok:
155 INSERT INTO t1(docid, a, b, c) VALUES(1, 'a b c', 'd e f', 'g h i');
156 -- This statement causes an error, as no docid value has been provided:
158 INSERT INTO t1(a, b, c) VALUES('j k l', 'm n o', 'p q r');
159 Of course your application will need an algorithm for finding the
161 external resource corresponding to any docid stored within SQLite.
162 Furthermore, SQLite "offsets()" and "snippet()" functions cannot be
163 used, so if such functionality is needed, it has to be directly
164 programmed within the Perl application.
165
167 Bugs should be reported via the CPAN bug tracker at
168 <http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite>
170
172 · Add more and varied cookbook recipes, until we have enough to turn
173 them into a separate CPAN distribution.
174 · Create a series of tests scripts that validate the cookbook
176 recipes.
177
179 Adam Kennedy <adamk@cpan.org>
180 Laurent Dami <dami@cpan.org>
182
184 Copyright 2009 - 2012 Adam Kennedy.
185 This program is free software; you can redistribute it and/or modify it
187 under the same terms as Perl itself.
188 The full text of the license can be found in the LICENSE file included
190 with this module.
191perl v5.16.3 2013-05-29 DBD::SQLite::Cookbook(3)