Exemplo n.º 1
0
        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;
            }
        }
Exemplo n.º 2
0
        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();
            }
        }