sqlite.html

来自「perl教程」· HTML 代码 · 共 414 行 · 第 1/2 页

</dd>
</li>
</dl>
<p>For example, here is how to define a <code>now()</code> function which returns the
current number of seconds since the epoch:</p>
<pre>
    <span class="variable">$dbh</span><span class="operator">-&gt;</span><span class="variable">func</span><span class="operator">(</span> <span class="string">'now'</span><span class="operator">,</span> <span class="number">0</span><span class="operator">,</span> <span class="keyword">sub</span><span class="variable"> </span><span class="operator">{</span> <span class="keyword">return</span> <span class="keyword">time</span> <span class="operator">},</span> <span class="string">'create_function'</span> <span class="operator">);</span>
</pre>
<p>After this, it could be use from SQL as:</p>
<pre>
    <span class="variable">INSERT</span> <span class="variable">INTO</span> <span class="variable">mytable</span> <span class="operator">(</span> <span class="variable">now</span><span class="operator">()</span> <span class="operator">);</span>
</pre>
<p>
</p>
<h2><a name="_dbh_func___name___argc___pkg___create_aggregate___">$dbh-&gt;func( $name, $argc, $pkg, 'create_aggregate' )</a></h2>
<p>This method will register a new aggregate function which can then used
from SQL. The method's parameters are:</p>
<dl>
<dt><strong>$name</strong>

<dd>
<p>The name of the aggregate function, this is the name under which the
function will be available from SQL.</p>
</dd>
</li>
<dt><strong>$argc</strong>

<dd>
<p>This is an integer which tells the SQL parser how many arguments the
function takes. If that number is -1, the function can take any number
of arguments.</p>
</dd>
</li>
<dt><strong><a name="item__pkg">$pkg</a></strong>

<dd>
<p>This is the package which implements the aggregator interface.</p>
</dd>
</li>
</dl>
<p>The aggregator interface consists of defining three methods:</p>
<dl>
<dt><strong><a name="item_new"><code>new()</code></a></strong>

<dd>
<p>This method will be called once to create an object which should
be used to aggregate the rows in a particular group. The <a href="#item_step"><code>step()</code></a> and
<a href="#item_finalize"><code>finalize()</code></a> methods will be called upon the reference return by
the method.</p>
</dd>
</li>
<dt><strong><a name="item_step"><code>step(@_)</code></a></strong>

<dd>
<p>This method will be called once for each rows in the aggregate.</p>
</dd>
</li>
<dt><strong><a name="item_finalize"><code>finalize()</code></a></strong>

<dd>
<p>This method will be called once all rows in the aggregate were
processed and it should return the aggregate function's result. When
there is no rows in the aggregate, <a href="#item_finalize"><code>finalize()</code></a> will be called right
after new().</p>
</dd>
</li>
</dl>
<p>Here is a simple aggregate function which returns the variance
(example adapted from pysqlite):</p>
<pre>
    <span class="keyword">package</span> <span class="variable">variance</span><span class="operator">;</span>
</pre>
<pre>
    <span class="keyword">sub</span><span class="variable"> new </span><span class="operator">{</span> <span class="keyword">bless</span> <span class="operator">[]</span><span class="operator">,</span> <span class="keyword">shift</span><span class="operator">;</span> <span class="operator">}</span>
</pre>
<pre>
    <span class="keyword">sub</span><span class="variable"> step </span><span class="operator">{</span>
        <span class="keyword">my</span> <span class="operator">(</span> <span class="variable">$self</span><span class="operator">,</span> <span class="variable">$value</span> <span class="operator">)</span> <span class="operator">=</span> <span class="variable">@_</span><span class="operator">;</span>
</pre>
<pre>
        <span class="keyword">push</span> <span class="variable">@$self</span><span class="operator">,</span> <span class="variable">$value</span><span class="operator">;</span>
            <span class="operator">}</span>
</pre>
<pre>
    <span class="keyword">sub</span><span class="variable"> finalize </span><span class="operator">{</span>
        <span class="keyword">my</span> <span class="variable">$self</span> <span class="operator">=</span> <span class="variable">$_</span><span class="operator">[</span><span class="number">0</span><span class="operator">]</span><span class="operator">;</span>
</pre>
<pre>
        <span class="keyword">my</span> <span class="variable">$n</span> <span class="operator">=</span> <span class="variable">@$self</span><span class="operator">;</span>
</pre>
<pre>
        <span class="comment"># Variance is NULL unless there is more than one row</span>
        <span class="keyword">return</span> <span class="keyword">undef</span> <span class="keyword">unless</span> <span class="variable">$n</span> <span class="operator">||</span> <span class="variable">$n</span> <span class="operator">==</span> <span class="number">1</span><span class="operator">;</span>
</pre>
<pre>
        <span class="keyword">my</span> <span class="variable">$mu</span> <span class="operator">=</span> <span class="number">0</span><span class="operator">;</span>
        <span class="keyword">foreach</span> <span class="keyword">my</span> <span class="variable">$v</span> <span class="operator">(</span> <span class="variable">@$self</span> <span class="operator">)</span> <span class="operator">{</span>
            <span class="variable">$mu</span> <span class="operator">+=</span> <span class="variable">$v</span><span class="operator">;</span>
        <span class="operator">}</span>
        <span class="variable">$mu</span> <span class="operator">/=</span> <span class="variable">$n</span><span class="operator">;</span>
</pre>
<pre>
        <span class="keyword">my</span> <span class="variable">$sigma</span> <span class="operator">=</span> <span class="number">0</span><span class="operator">;</span>
        <span class="keyword">foreach</span> <span class="keyword">my</span> <span class="variable">$v</span> <span class="operator">(</span> <span class="variable">@$self</span> <span class="operator">)</span> <span class="operator">{</span>
            <span class="variable">$sigma</span> <span class="operator">+=</span> <span class="operator">(</span><span class="variable">$x</span> <span class="operator">-</span> <span class="variable">$mu</span><span class="operator">)**</span><span class="number">2</span><span class="operator">;</span>
        <span class="operator">}</span>
        <span class="variable">$sigma</span> <span class="operator">=</span> <span class="variable">$sigma</span> <span class="operator">/</span> <span class="operator">(</span><span class="variable">$n</span> <span class="operator">-</span> <span class="number">1</span><span class="operator">);</span>
</pre>
<pre>
        <span class="keyword">return</span> <span class="variable">$sigma</span><span class="operator">;</span>
            <span class="operator">}</span>
</pre>
<pre>
    <span class="variable">$dbh</span><span class="operator">-&gt;</span><span class="variable">func</span><span class="operator">(</span> <span class="string">"variance"</span><span class="operator">,</span> <span class="number">1</span><span class="operator">,</span> <span class="string">'variance'</span><span class="operator">,</span> <span class="string">"create_aggregate"</span> <span class="operator">);</span>
</pre>
<p>The aggregate function can then be used as:</p>
<pre>
    <span class="variable">SELECT</span> <span class="variable">group_name</span><span class="operator">,</span> <span class="variable">variance</span><span class="operator">(</span><span class="variable">score</span><span class="operator">)</span> <span class="variable">FROM</span> <span class="variable">results</span>
    <span class="variable">GROUP</span> <span class="variable">BY</span> <span class="variable">group_name</span><span class="operator">;</span>
</pre>
<p>
</p>
<hr />
<h1><a name="blobs">BLOBS</a></h1>
<p>As of version 1.11, blobs should &quot;just work&quot; in SQLite as text columns. However
this will cause the data to be treated as a string, so SQL statements such
as <a href="../../lib/Pod/perlfunc.html#item_length"><code>length(x)</code></a> will return the length of the column as a NUL terminated string,
rather than the size of the blob in bytes. In order to store natively as a
BLOB use the following code:</p>
<pre>
  <span class="keyword">use</span> <span class="variable">DBI</span> <span class="string">qw(:sql_types)</span><span class="operator">;</span>
  <span class="keyword">my</span> <span class="variable">$dbh</span> <span class="operator">=</span> <span class="variable">DBI</span><span class="operator">-&gt;</span><span class="keyword">connect</span><span class="operator">(</span><span class="string">"dbi:sqlite:/path/to/db"</span><span class="operator">);</span>
  
  <span class="keyword">my</span> <span class="variable">$blob</span> <span class="operator">=</span> <span class="string">`cat foo.jpg`</span><span class="operator">;</span>
  <span class="keyword">my</span> <span class="variable">$sth</span> <span class="operator">=</span> <span class="variable">$dbh</span><span class="operator">-&gt;</span><span class="variable">prepare</span><span class="operator">(</span><span class="string">"INSERT INTO mytable VALUES (1, ?)"</span><span class="operator">);</span>
  <span class="variable">$sth</span><span class="operator">-&gt;</span><span class="variable">bind_param</span><span class="operator">(</span><span class="number">1</span><span class="operator">,</span> <span class="variable">$blob</span><span class="operator">,</span> <span class="variable">SQL_BLOB</span><span class="operator">);</span>
  <span class="variable">$sth</span><span class="operator">-&gt;</span><span class="variable">execute</span><span class="operator">();</span>
</pre>
<p>And then retreival just works:</p>
<pre>
  <span class="variable">$sth</span> <span class="operator">=</span> <span class="variable">$dbh</span><span class="operator">-&gt;</span><span class="variable">prepare</span><span class="operator">(</span><span class="string">"SELECT * FROM mytable WHERE id = 1"</span><span class="operator">);</span>
  <span class="variable">$sth</span><span class="operator">-&gt;</span><span class="variable">execute</span><span class="operator">();</span>
  <span class="keyword">my</span> <span class="variable">$row</span> <span class="operator">=</span> <span class="variable">$sth</span><span class="operator">-&gt;</span><span class="variable">fetch</span><span class="operator">;</span>
  <span class="keyword">my</span> <span class="variable">$blobo</span> <span class="operator">=</span> <span class="variable">$row</span><span class="operator">-&gt;</span><span class="operator">[</span><span class="number">1</span><span class="operator">]</span><span class="operator">;</span>
  
  <span class="comment"># now $blobo == $blob</span>
</pre>
<p>
</p>
<hr />
<h1><a name="notes">NOTES</a></h1>
<p>To access the database from the command line, try using dbish which comes with
the DBI module. Just type:</p>
<pre>
  dbish dbi:SQLite:foo.db</pre>
<p>On the command line to access the file <em>foo.db</em>.</p>
<p>Alternatively you can install SQLite from the link above without conflicting
with DBD::SQLite and use the supplied <code>sqlite</code> command line tool.</p>
<p>
</p>
<hr />
<h1><a name="performance">PERFORMANCE</a></h1>
<p>SQLite is fast, very fast. I recently processed my 72MB log file with it,
inserting the data (400,000+ rows) by using transactions and only committing
every 1000 rows (otherwise the insertion is quite slow), and then performing
queries on the data.</p>
<p>Queries like <code>count(*)</code> and <code>avg(bytes)</code> took fractions of a second to return,
but what surprised me most of all was:</p>
<pre>
  SELECT url, count(*) as count FROM access_log
    GROUP BY url
    ORDER BY count desc
    LIMIT 20</pre>
<p>To discover the top 20 hit URLs on the site (http://axkit.org), and it
returned within 2 seconds. I'm seriously considering switching my log
analysis code to use this little speed demon!</p>
<p>Oh yeah, and that was with no indexes on the table, on a 400MHz PIII.</p>
<p>For best performance be sure to tune your hdparm settings if you are
using linux. Also you might want to set:</p>
<pre>
  PRAGMA default_synchronous = OFF</pre>
<p>Which will prevent sqlite from doing fsync's when writing (which
slows down non-transactional writes significantly) at the expense of some
peace of mind. Also try playing with the cache_size pragma.</p>
<p>
</p>
<hr />
<h1><a name="bugs">BUGS</a></h1>
<p>Likely to be many, please use <a href="http://rt.cpan.org/">http://rt.cpan.org/</a> for reporting bugs.</p>
<p>
</p>
<hr />
<h1><a name="author">AUTHOR</a></h1>
<p>Matt Sergeant, <a href="mailto:[e-mail]">[e-mail]</a></p>
<p>Perl extension functions contributed by Francis J. Lacoste
&lt;<a href="mailto:[e-mail]">[e-mail]</a>&gt; and Wolfgang Sourdeau
&lt;<a href="mailto:[e-mail]">[e-mail]</a>&gt;</p>
<p>
</p>
<hr />
<h1><a name="see_also">SEE ALSO</a></h1>
<p><a href="../../lib/DBI.html">the DBI manpage</a>.</p>

</body>

</html>