Пример #1
0
        /// <summary>
        /// Determines if FTS search can be used for the given table, columns, and keyword.
        /// </summary>
        /// <param name="tableName">The name of the table being searched</param>
        /// <param name="columnList">The list of columns being selected</param>
        /// <param name="searchPattern">The search pattern or keyword</param>
        /// <returns>True if FTS will be available for this search, false otherwise.</returns>
        public static bool UseFtsSearch(string tableName, string columnList, string searchPattern)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException("tableName", "The table name must be specified.");
            }
            if (string.IsNullOrEmpty(columnList))
            {
                throw new ArgumentException("At least one column must be specified.", "columns");
            }

            // IF THERE IS NO SEARCH KEYWORD WE DON'T NEED TO WORRY ABOUT FTS
            if (string.IsNullOrEmpty(searchPattern))
            {
                return(false);
            }

            // PARSE THE COLUMN LIST
            List <string> columns = new List <string>(columnList.Split(",".ToCharArray()));

            // DETERMINE IF FTS SEARCH WILL BE AVAILABLE
            return(Store.GetCachedSettings().FullTextSearch &&
                   KeywordSearchHelper.IsSearchPatternSupportedForFts(searchPattern) &&
                   ColumnsIndexed(tableName, columns));
        }
Пример #2
0
 /// <summary>
 /// Ensures that full text searching is enabled and the catalog exists
 /// </summary>
 /// <returns>True if the catalog exists, false otherwise.</returns>
 public static bool EnsureCatalog()
 {
     if (!KeywordSearchHelper.IsFullTextSearchEnabled())
     {
         KeywordSearchHelper.EnableFullTextSearch();
     }
     if (!KeywordSearchHelper.CatalogExists())
     {
         KeywordSearchHelper.CreateCatalog();
     }
     return(KeywordSearchHelper.CatalogExists());
 }
Пример #3
0
        /// <summary>
        /// Ensures that the expected fulltext indexes are present in the catalog
        /// </summary>
        /// <returns>True if the indexes are available; false otherwise.</returns>
        public static bool EnsureIndexes()
        {
            List <string> indexedTables = GetIndexedTables();

            foreach (string expectedIndex in FTSIndexedTables)
            {
                if (!indexedTables.Contains(expectedIndex))
                {
                    CreateIndex(expectedIndex);
                }
            }
            return(KeywordSearchHelper.IndexesExist());
        }
Пример #4
0
        /// <summary>
        /// Gets a SQL filter for the given table, columns, and keyword
        /// </summary>
        /// <param name="tableName">Name of the table being searched</param>
        /// <param name="columnPrefix">Table name or alias that must be applied to the column(s)</param>
        /// <param name="columnList">The column(s) being searched, multiple columns should be comma delimited with no spaces.</param>
        /// <param name="parameterName">The name to use for the SQL parameter.</param>
        /// <param name="defaultToSubstring">If true, the search pattern will be converted to a substring match unless otherwise specified.  When false, the search pattern is used as is.  This applies only to non-FTS searches.</param>
        /// <param name="searchPattern">Pattern to search for - this value may be normalized by the procedure and must be passed by reference</param>
        /// <returns>A clause suitable for use in a SQL where statement</returns>
        /// <remarks>The returned SQL string will use a parameter - specified by parameterName.  This must be populated when executing the query.</remarks>
        internal static string PrepareSqlFilter(string tableName, string columnPrefix, string columnList, string parameterName, bool defaultToSubstring, ref string searchPattern)
        {
            // VALIDATE INPUTS
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException("tableName", "The table name must be specified.");
            }
            if (string.IsNullOrEmpty(columnList))
            {
                throw new ArgumentException("At least one column must be specified.", "columns");
            }
            if (string.IsNullOrEmpty(parameterName))
            {
                throw new ArgumentNullException("parameterName", "The parameter name must be specified.");
            }
            if (string.IsNullOrEmpty(searchPattern))
            {
                throw new ArgumentNullException("searchPattern", "The search pattern must be specified.");
            }

            // MAKE SURE TABLE PREFIX HAS A TERMINAL .
            if (!string.IsNullOrEmpty(columnPrefix) && !columnPrefix.EndsWith("."))
            {
                columnPrefix += ".";
            }

            // MAKE SURE PARAMETER NAME HAS APPROPRIATE PREFIX
            if (!parameterName.StartsWith("@"))
            {
                parameterName = "@" + parameterName;
            }

            // PARSE THE COLUMN LIST
            List <string> columns = new List <string>(columnList.Split(",".ToCharArray()));

            // DETERMINE THE SEARCH TYPE
            if (Store.GetCachedSettings().FullTextSearch &&
                KeywordSearchHelper.IsSearchPatternSupportedForFts(searchPattern) &&
                ColumnsIndexed(tableName, columns))
            {
                // CREATE A SEARCH FILTER USING FTS
                searchPattern = (new FTSQueryParser(searchPattern)).NormalForm;
                return(" AND CONTAINS(" + PrepareFtsColumnList(tableName, columnPrefix, columns) + ", " + parameterName + ")");
            }
            else
            {
                // CREATE A SEARCH FILTER WITHOUT USING FTS
                return(PrepareNonFtsSqlFilter(columnPrefix, columns, parameterName, defaultToSubstring, ref searchPattern));
            }
        }
Пример #5
0
        /// <summary>
        /// Removes the catalog
        /// </summary>
        public static void RemoveCatalog()
        {
            if (KeywordSearchHelper.CatalogExists())
            {
                // ATTEMPT TO REMOVE INDEXES IN THE CATALOG
                RemoveIndexes();

                // ATTEMPT TO REMOVE THE CATALOG, FAILURE IS NOT FATAL
                try
                {
                    Database  database = Token.Instance.Database;
                    DbCommand command  = database.GetSqlStringCommand("DROP FULLTEXT CATALOG " + FTSCatalogName);
                    database.ExecuteNonQuery(command);
                }
                catch (SqlException se)
                {
                    Logger.Warn("Could not remove fulltext catalog " + FTSCatalogName, se);
                }
            }
        }
Пример #6
0
        /// <summary>
        /// Builds a select command using this object, the given sort expression and the count value.
        /// </summary>
        /// <param name="sortExpression">The sort expression to use in the select command</param>
        /// <param name="count">The number of rows to retrieve</param>
        /// <returns>The DbCommand object representing the required select command</returns>
        private DbCommand GetFilteredOrderIds(int maximumRows, int startRowIndex, string sortExpression, bool count)
        {
            // ADD THE ORDER TABLE PREFIX TO AVOID AMBIGIOUS COLUMN SQL ERROR
            if (string.IsNullOrEmpty(sortExpression))
            {
                sortExpression = "ac_Orders.OrderDate DESC";
            }
            else if (!sortExpression.StartsWith("ac_Orders."))
            {
                sortExpression = "ac_Orders." + sortExpression;
            }

            Token    token    = Token.Instance;
            Database database = token.Database;
            Store    store    = token.Store;

            // CREATE A BLANK SQL COMMAND TO BE POPULATED
            DbCommand selectCommand = database.GetSqlStringCommand("SELECT");

            // BUILD A LIST OF THE SOURCE TABLE(S)
            bool          distinct   = false;
            StringBuilder fromTables = new StringBuilder();

            fromTables.Append("ac_Orders");
            // DETERMINE FTS PATH
            bool useFtsSearch = false;

            if (!string.IsNullOrEmpty(this.Keyword))
            {
                useFtsSearch = Store.GetCachedSettings().FullTextSearch&& KeywordSearchHelper.IsSearchPatternSupportedForFts(this.Keyword);
                if (!useFtsSearch)
                {
                    // BASIC SEARCH REQUIRES TABLE JOINS
                    if ((this.KeywordField & KeywordFieldType.ShippingInfo) == KeywordFieldType.ShippingInfo)
                    {
                        distinct = true;
                        fromTables.Append(" INNER JOIN ac_OrderShipments ON ac_Orders.OrderId = ac_OrderShipments.OrderId");
                        if ((this.KeywordField & KeywordFieldType.Notes) == KeywordFieldType.Notes)
                        {
                            fromTables.Insert(0, "(");
                            fromTables.Append(") INNER JOIN ac_OrderNotes ON ac_Orders.OrderId = ac_OrderNotes.OrderId");
                        }
                    }
                    else if ((this.KeywordField & KeywordFieldType.Notes) == KeywordFieldType.Notes)
                    {
                        distinct = true;
                        fromTables.Append(" INNER JOIN ac_OrderNotes ON ac_Orders.OrderId = ac_OrderNotes.OrderId");
                    }
                }
            }
            fromTables.Insert(0, " FROM ");

            // BUILD THE WHERE CRITERIA
            List <string> whereCriteria = new List <string>();

            // ALL FILTERS MUST INCLUDE STOREID
            whereCriteria.Add("ac_Orders.StoreId = @storeId");
            database.AddInParameter(selectCommand, "storeId", DbType.Int32, Token.Instance.StoreId);

            // ADD DATE FILTER
            if (OrderDateStart > DateTime.MinValue)
            {
                whereCriteria.Add("ac_Orders.OrderDate >= @orderDateStart");
                database.AddInParameter(selectCommand, "orderDateStart", DbType.DateTime, this.OrderDateStart);
            }
            if (OrderDateEnd > DateTime.MinValue && OrderDateEnd < DateTime.MaxValue)
            {
                whereCriteria.Add("ac_Orders.OrderDate <= @orderDateEnd");
                database.AddInParameter(selectCommand, "orderDateEnd", DbType.DateTime, this.OrderDateEnd);
            }

            // ORDER ID RANGE FILTER
            IdRangeParser orderIdRangeParser = new IdRangeParser("ac_Orders.OrderId", _OrderIdRange, "oi");

            if (orderIdRangeParser.RangeCount > 0)
            {
                whereCriteria.Add(orderIdRangeParser.GetSqlString(string.Empty));
                orderIdRangeParser.AddParameters(database, selectCommand);
            }

            // ORDER NUMBER RANGE FILTER
            IdRangeParser orderNumberRangeFilter = new IdRangeParser("ac_Orders.OrderNumber", _OrderNumberRange, "on");

            if (orderNumberRangeFilter.RangeCount > 0)
            {
                whereCriteria.Add(orderNumberRangeFilter.GetSqlString(string.Empty));
                orderNumberRangeFilter.AddParameters(database, selectCommand);
            }

            // ADD ORDER STATUS FILTER
            if (this.OrderStatus.Count > 0)
            {
                if (OrderStatus.Count > 1)
                {
                    whereCriteria.Add("ac_Orders.OrderStatusId IN ('" + this.OrderStatus.ToList("','") + "')");
                }
                else
                {
                    whereCriteria.Add("ac_Orders.OrderStatusId = @orderStatusId");
                    database.AddInParameter(selectCommand, "orderStatusId", DbType.Int32, this.OrderStatus[0]);
                }
            }

            // ADD PAYMENT STATUS FILTER
            if (this.PaymentStatus != OrderPaymentStatus.Unspecified)
            {
                whereCriteria.Add("PaymentStatusId = @paymentStatus");
                database.AddInParameter(selectCommand, "paymentStatus", System.Data.DbType.Byte, this.PaymentStatus);
            }

            // ADD SHIPMENT STATUS FILTER
            if (this.ShipmentStatus != OrderShipmentStatus.Unspecified)
            {
                whereCriteria.Add("ShipmentStatusId = @shipmentStatus");
                database.AddInParameter(selectCommand, "shipmentStatus", System.Data.DbType.Byte, this.ShipmentStatus);
            }

            if (!string.IsNullOrEmpty(this.Keyword))
            {
                // DETERMINE THE SEARCH TYPE
                if (useFtsSearch)
                {
                    // CREATE A SEARCH FILTER USING FTS
                    string        searchPattern    = (new FTSQueryParser(this.Keyword)).NormalForm;
                    List <string> containsCriteria = new List <string>();
                    if ((this.KeywordField & KeywordFieldType.BillingInfo) == KeywordFieldType.BillingInfo)
                    {
                        containsCriteria.Add("ac_Orders.OrderId IN (SELECT OrderId FROM ac_Orders WHERE CONTAINS(*, @keyword))");
                    }
                    if ((this.KeywordField & KeywordFieldType.ShippingInfo) == KeywordFieldType.ShippingInfo)
                    {
                        containsCriteria.Add("ac_Orders.OrderId IN (SELECT OrderId FROM ac_OrderShipments WHERE CONTAINS(*, @keyword))");
                    }
                    if ((this.KeywordField & KeywordFieldType.Notes) == KeywordFieldType.Notes)
                    {
                        containsCriteria.Add("ac_Orders.OrderId IN (SELECT OrderId FROM ac_OrderNotes WHERE CONTAINS(*, @keyword))");
                    }
                    if (containsCriteria.Count == 1)
                    {
                        whereCriteria.Add(containsCriteria[0]);
                    }
                    else
                    {
                        whereCriteria.Add("(" + string.Join(" OR ", containsCriteria.ToArray()) + ")");
                    }
                    database.AddInParameter(selectCommand, "keyword", DbType.String, searchPattern);
                }
                else
                {
                    // BUILD COLUMNS FOR BASIC (NON-FTS) SEARCH
                    List <string> columns = new List <string>();
                    if ((this.KeywordField & KeywordFieldType.BillingInfo) == KeywordFieldType.BillingInfo)
                    {
                        columns.Add("BillToFirstName");
                        columns.Add("BillToLastName");
                        columns.Add("BillToCompany");
                        columns.Add("BillToAddress1");
                        columns.Add("BillToAddress2");
                        columns.Add("BillToCity");
                        columns.Add("BillToProvince");
                        columns.Add("BillToPostalCode");
                        columns.Add("BillToPhone");
                        columns.Add("BillToFax");
                        columns.Add("BillToEmail");
                    }
                    if ((this.KeywordField & KeywordFieldType.ShippingInfo) == KeywordFieldType.ShippingInfo)
                    {
                        columns.Add("ShipToFirstName");
                        columns.Add("ShipToLastName");
                        columns.Add("ShipToCompany");
                        columns.Add("ShipToAddress1");
                        columns.Add("ShipToAddress2");
                        columns.Add("ShipToCity");
                        columns.Add("ShipToProvince");
                        columns.Add("ShipToPostalCode");
                        columns.Add("ShipToPhone");
                        columns.Add("ShipToFax");
                        columns.Add("ShipToEmail");
                        columns.Add("ShipMessage");
                    }
                    if ((this.KeywordField & KeywordFieldType.Notes) == KeywordFieldType.Notes)
                    {
                        columns.Add("Comment");
                    }

                    // PARSE THE CRITERIA AND ADD TO THE QUERY
                    KeywordCriterion criterion = KeywordSearchHelper.ParseKeywordCriterion(this.Keyword, columns.ToArray());
                    whereCriteria.Add(criterion.WhereClause);
                    foreach (DatabaseParameter param in criterion.Parameters)
                    {
                        database.AddInParameter(selectCommand, param.Name, param.DbType, param.Value);
                    }
                }
            }

            // COMPILE THE FINAL QUERY
            StringBuilder sqlBuilder = new StringBuilder();

            if (!count)
            {
                // BUILD SELECT TO RETRIEVE ALL DATA
                sqlBuilder.Append("SELECT");
                if (distinct)
                {
                    sqlBuilder.Append(" DISTINCT");
                }
                if (maximumRows > 0)
                {
                    sqlBuilder.Append(" TOP " + (startRowIndex + maximumRows).ToString());
                }
                sqlBuilder.Append(" ac_Orders.OrderId");
                string sortColumn = sortExpression.Replace(" ASC", "").Replace(" DESC", "");
                if (sortColumn != "ac_Orders.OrderId")
                {
                    sqlBuilder.Append("," + sortColumn);
                }
            }
            else
            {
                // BUILD COUNT COMMAND
                sqlBuilder.Append("SELECT COUNT(");
                if (distinct)
                {
                    sqlBuilder.Append("DISTINCT ");
                }
                sqlBuilder.Append("ac_Orders.OrderId) AS OrderCount");
            }
            sqlBuilder.Append(fromTables.ToString());
            sqlBuilder.Append(" WHERE " + string.Join(" AND ", whereCriteria.ToArray()) + " ");

            // ADD IN SORT EXPRESSION
            if (!count)
            {
                sqlBuilder.Append("ORDER BY " + sortExpression);
            }

            // SET SQL STRING AND RETURN COMMAND
            selectCommand.CommandText = sqlBuilder.ToString();
            return(selectCommand);
        }