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(); } }