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