<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>SQLite Full Text Search and other usefull UDFs</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
body, p, td {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: xx-small;
}
-->
</style>
</head>
<body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<table width="100%" border="0">
<tr>
<td align="center"> <table width="770" border="0">
<tr>
<td> <h2>SQLite Full Text Search and other usefull UDFs</h2>
<?php
require_once (dirname (__FILE__) . '/sqlite_fulltextsearch.class.php');
require_once (dirname (__FILE__) . '/sqlite_fulltextsearchex.class.php');
if ($dbhandle = sqlite_open ('sqlitedb', 0666, $sqliteerror)) {
$sqlite_fts = new sqlite_fulltextsearch ();
/* use sqlite_fulltextsearchex class for exponential word prominence */
// $sqlite_fts = new sqlite_fulltextsearchex ();
/*
register method registers the "fulltextsearch" UDF to the current DB handle.
*/
$sqlite_fts->register ($dbhandle);
/*
use_against_cache property allows to optimize the "fulltextsearch" UDF execution.
*/
$sqlite_fts->use_against_cache = true;
// use the commented code to create the table and insert some sample contents
$sql = 'DROP TABLE tblArticles';
@sqlite_query ($dbhandle, $sql);
$sql = 'CREATE TABLE tblArticles (Article TEXT NULL)';
sqlite_query ($dbhandle, $sql);
$values = array ('SQLite Tutorial', 'DBMS stands for DataBase', 'How To Use SQLite Well', 'After you went through a', 'Optimizing SQLite', 'In this tutorial we will show', '1001 SQLite Tricks', 'Never run SQLited as root.', 'SQLite vs. YourSQL', 'In the following database comparison', 'SQLite Security', 'When configured properly, SQLite');
foreach ($values as $value) {
sqlite_query ($dbhandle, 'INSERT INTO tblArticles (Article) VALUES ("' . sqlite_escape_string ($value) . '")');
}
// */
?>
<br> <strong>fulltextsearch UDF requests three different parameters.</strong>
<br>
The first is the text on wich you want to execute the full text search
(i.e. the field name)<br>
The second is the value against wich you want to execute the full
text search<br>
The third is a boolean value (0, 1) to specify if you want to use
or not the case in the full text search (1 => case-sensitive, 0
=> not case-sensitive)<br>
<?php
$sql = 'SELECT fulltextsearch (Article, "sqlite tutorial", 0) AS Match, Article FROM tblArticles ORDER BY Match DESC';
$rows = sqlite_array_query ($dbhandle, $sql);
print ("<p align='center'><table border='1' width='350' cellspacing='0' cellpadding='0'>");
print ("<tr><td width='120'>Match</td><td>Article</td></tr>");
foreach ($rows as $row) {
print ("<tr><td>" . number_format ($row['Match'], 2, '.', '') . "</td><td>$row[Article]</td></tr>");
}
print ("</table></p>");
?>
<strong>All the word prominence UDFs requests three different parameters.
</strong><br>
The first is the text on wich you want to calculate the word prominence
(i.e. the field name)<br>
The second is the work against wich you want to execute the calculation<br>
The third is a boolean value (0, 1) to specify if you want to use
or not the case (1 => case-sensitive, 0 => not case-sensitive)
<p> <strong>Word prominence is calculated weighting the position of
the word in the text. </strong><br>
The sqlite_fulltextsearch class contains a public method named "prominence"
that is used to calculate the word prominence.<br>
The sqlite_fulltextsearch calculates a linear word prominence. If
you want to use a different scale for the word prominence<br>
weighting calculation, just override the "prominence"
method. An example of exponential prominence is provided by the
<br>
sqlite_fulltextsearchex class.<br>
The public interface of sqlite_fulltextsearch class has also a method
named "wordspreparation". This method is called before
any call of any one of the UDFs and allows the user to personalize
the content before is used for the searches and calculations. An
example of the possible usages of this methos if showed in the sqlite_fulltextsearchex
class where it's implemented a strip_tags behaviour usefull if you
are trying to match against an HTML content.<br>
The sqlite_fulltextsearch class provides three different prominence
UDF for SQLite:<br>
<br>
- <strong>wordprominence</strong><br>
- <strong>reversewordprominence</strong><br>
- <strong>centerwordprominence</strong><br>
<br>
The internal behaviour is the same. The only thing that changes
is the order of the words in the text.<br>
If we have the following text string:
<pre style="font-size: x-small;"> a b c d e f g</pre>
The text will be weighted using the following words order:
<pre style="font-size: x-small;">
- wordprominencea b c d e f g
- reversewordprominence g f e d c b a
- centerwordprominence d e c f b g a
</pre>
<strong>wordprominence gives more importance to the words at the beginning
of the text</strong><br>
<strong>reversewordprominence gives more importance to the words at
the end of the text</strong><br>
<strong>centerwordprominence gives more importance to the words in
the middle of the text</p></strong>
<?php
$sql = 'SELECT wordprominence (Article, "sqlite", 0) AS Prominence, Article FROM tblArticles ORDER BY Prominence DESC';
$rows = sqlite_array_query ($dbhandle, $sql);
print ("<p align='center'><table border='1' width='350' cellspacing='0' cellpadding='0'>");
print ("<tr><td width='120'>Word Prominence</td><td>Article</td></tr>");
foreach ($rows as $row) {
print ("<tr><td>" . number_format ($row['Prominence'], 2, '.', '') . "</td><td>$row[Article]</td></tr>");
}
print ("</table></p>");
$sql = 'SELECT reversewordprominence (Article, "sqlite", 0) AS Prominence, Article FROM tblArticles ORDER BY Prominence DESC';
$rows = sqlite_array_query ($dbhandle, $sql);
print ("<p align='center'><table border='1' width='350' cellspacing='0' cellpadding='0'>");
print ("<tr><td width='120'>Reverse Prominence</td><td>Article</td></tr>");
foreach ($rows as $row) {
print ("<tr><td>" . number_format ($row['Prominence'], 2, '.', '') . "</td><td>$row[Article]</td></tr>");
}
print ("</table></p>");
$sql = 'SELECT centerwordprominence (Article, "sqlite", 0) AS Prominence, Article FROM tblArticles ORDER BY Prominence DESC';
$rows = sqlite_array_query ($dbhandle, $sql);
print ("<p align='center'><table border='1' width='350' cellspacing='0' cellpadding='0'>");
print ("<tr><td width='120'>Center Prominence</td><td>Article</td></tr>");
foreach ($rows as $row) {
print ("<tr><td>" . number_format ($row['Prominence'], 2, '.', '') . "</td><td>$row[Article]</td></tr>");
}
print ("</table></p>");
} else {
die ('Error opening sqlite db: ' . $sqliteerror);
}
?>
</td>
</tr>
</table></td>
</tr>
</table>
</body>
</html>
|
Download
