public SearchResults SearchCatalog(String searchTerm)
{
SearchResults results = new SearchResults();
//This entire search is case-insensitive
searchTerm = searchTerm.Trim().ToLower();
if (searchTerm.Length == 0) {
//Can't search on empty string
return results;
}
//The search term is applied to the graph of nodes
//once for each permutation of the search term, where
//a permutation consists of the original search term with
//zero or more word separators inserted between the letters.
//This, for the search term 'cpal', one permutation would match
//a single word prefixed with 'cpal', while another permutation
//might a word prefixed with 'c', followed one or more words later
//by a word prefixed with 'pal'.
//
//This is conveniently expressed by a bitmap where each bit
//represents the space between two adjacent letters in the search term
//A 1 bit indicates the presence of a word separator between the letters,
//while a 0 bit denotes no word separator.
//
//Thus, computing the permutations is as simple as counting from
//0 to 2^n - 1, where n is the number of spaces between characters
//in the search term. This is simply the length of the search term minus
//1.
int bitmapLen = searchTerm.Length - 1;
System.Diagnostics.Trace.Assert(searchTerm.Length < 64);
//Keep an array list of the separated search terms,
//where each element in the array list is an array of strings,
//with each string being the prefix of a word in the word graph
ArrayList searchTermPermutations = new ArrayList();
ArrayList searchTermBitmaps = new ArrayList();
if (searchTerm.Length != 1)
{
for (ulong bitmap = 0; bitmap < (1UL << bitmapLen); bitmap++)
{
//Count the 1 bits in this value of bitmap */
ulong w = bitmap;
int numOnes = 0;
while (w != 0)
{
numOnes++;
w &= w - 1;
}
//There are numOnes word separators to be inserted, therefore
//there will be numOnes + 1 substrings as a result
String[] searchString = new String[numOnes + 1];
int[] separatorIdxs = new int[numOnes];
int lastIdx = 0;
//For each non-zero bit in the bitmap, split the string there
for (ulong mask = 1, idx = 0;
mask <= bitmap;
idx++, mask<<=1)
{
if ((bitmap & mask) == mask)
{
//The idx-th bit is set, so split the search term
//after the idx+1st character
separatorIdxs[lastIdx++] = (int)idx + 1;
}
}
//separatorIdxs is a list of the 0-based character positions after
//which a separator should be inserted. Thus, 1 denotes separating
//the first char from the rest, 2 separates the first two chars, etc
lastIdx = 0;
for (int idx = 0; idx < separatorIdxs.Length; idx++)
{
searchString[idx] = searchTerm.Substring(lastIdx, separatorIdxs[idx] - lastIdx);
lastIdx = separatorIdxs[idx];
}
//The last substring is from the last index plus one, to the end
//of the search string
searchString[searchString.Length - 1] = searchTerm.Substring(lastIdx);
searchTermPermutations.Add(searchString);
searchTermBitmaps.Add(bitmap);
}
}
else
{
//Search term is only one char long
searchTermPermutations.Add(new String[] {searchTerm} );
searchTermBitmaps.Add(0UL);
}
SQLiteConnection conn = GetConnection();
//If the temp table hash exists from the last search, drop
//all the temp tables listed therein
if (_tempTableHash != null) {
using (SQLiteCommand cmd = conn.CreateCommand()) {
cmd.CommandType = CommandType.Text;
foreach (String tbl in _tempTableHash.Keys) {
cmd.CommandText = "drop table " + tbl;
cmd.ExecuteNonQuery();
Catalog.DumpCommand(cmd);
}
}
}
_tempTableHash = new Hashtable();
//For each of the permutations, check for matching nodes
using (SQLiteTransaction tx = conn.BeginTransaction()) {
//Retrieve all catalog items whose title includes the matching nodes
//Do this by filling a temp table with the matching node IDs, then
//doing a join with some other tables mapping the node IDs to catalog
//items.
using (SQLiteCommand cmd = conn.CreateCommand()) {
cmd.Transaction = tx;
cmd.CommandType = CommandType.Text;
cmd.CommandText = @"
create temp table matching_node_ids (
node_id integer primary key
);";
cmd.ExecuteNonQuery();
Catalog.DumpCommand(cmd);
}
for (int idx = 0; idx < searchTermPermutations.Count; idx++) {
SearchForPermutation((String[])searchTermPermutations[idx],
(ulong)searchTermBitmaps[idx]);
}
//Temp table of matching node ids is populated
//Create and populate a temp table to store the catalog item IDs corresponding to these node ids
using (SQLiteCommand cmd = conn.CreateCommand()) {
cmd.Transaction = tx;
cmd.CommandType = CommandType.Text;
cmd.CommandText = @"
create temp table matching_cat_ids (
catalog_item_id integer primary key
);";
cmd.ExecuteNonQuery();
Catalog.DumpCommand(cmd);
}
using (SQLiteCommand cmd = conn.CreateCommand()) {
cmd.Transaction = tx;
cmd.CommandType = CommandType.Text;
cmd.CommandText = @"
insert into matching_cat_ids
select distinct ni.catalog_item_id as catalog_item_id
from
matching_node_ids mn
inner join
title_word_graph_node_items ni
on
mn.node_id = ni.node_id";
cmd.ExecuteNonQuery();
Catalog.DumpCommand(cmd);
}
//Temp table of catalog_item_ids is populated. Join with the catalog item table
using (SQLiteCommand cmd = conn.CreateCommand()) {
cmd.Transaction = tx;
cmd.CommandType = CommandType.Text;
cmd.CommandText = @"
select ci.catalog_item_id, ci.title, ci.uri
from catalog_items ci inner join matching_cat_ids
on ci.catalog_item_id = matching_cat_ids.catalog_item_id;";
SQLiteDataReader rdr = cmd.ExecuteReader();
Catalog.DumpCommand(cmd);
while (rdr.Read()) {
long catId = rdr.GetInt64(0);
String title = rdr.GetString(1);
String uri = rdr.GetString(2);
results.Add(new SearchResult(catId, title, uri));
}
rdr.Close();
}
//Results are retrieved. Drop the temp tables and return
using (SQLiteCommand cmd = conn.CreateCommand()) {
cmd.CommandType = CommandType.Text;
cmd.CommandText = @"drop table matching_cat_ids;";
cmd.ExecuteNonQuery();
Catalog.DumpCommand(cmd);
}
using (SQLiteCommand cmd = conn.CreateCommand()) {
cmd.CommandType = CommandType.Text;
cmd.CommandText = @"drop table matching_node_ids;";
cmd.ExecuteNonQuery();
Catalog.DumpCommand(cmd);
}
tx.Commit();
return results;
}
}
public SearchResults SearchCatalog(String searchTerm)
{
//This entire search is case-insensitive
searchTerm = searchTerm.ToLower();
//First, query the catalog for all items that have all of the
//letters from the search term in their titles.
StringBuilder sb = new StringBuilder();
sb.Append(@"
select distinct ci.id, ci.title, ci.uri
from
catalog_items ci,
catalog_item_title_chars citc
where
citc.title_ci_char in ('");
sb.Append(String.Join("','", GetUniqueChars(searchTerm)));
sb.Append("')");
FbConnection conn = GetConnection();
try {
using (FbCommand cmd = new FbCommand()) {
using (cmd.Transaction = conn.BeginTransaction(FbTransactionOptions.Autocommit|FbTransactionOptions.Concurrency|FbTransactionOptions.Read)) {
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sb.ToString();
FbDataReader rdr = cmd.ExecuteReader();
//For each matching id/title combination, perform
//a second pass filter, ensuring that
//the characters in the search term
//are present in the same order as the
//search term. Thus, a search for 'foo'
//would match 'FOrest Orgy' and 'F*****g asshOle mOment', but
//not 'Orgy in a FOrest'.
SearchResults results = new SearchResults();
while (rdr.Read()) {
int catId = rdr.GetInt32(0);
String title = rdr.GetString(1);
String uri = rdr.GetString(2);
if (DoesTitleMatchSearch(title, searchTerm)) {
//This one matches
results.Add(new SearchResult(catId, title, uri));
}
}
rdr.Close();
return results;
}
}
} finally {
conn.Close();
}
}