static void UpdateFromFactory(LoaderFacetFactory factory) { var ffwi = factory.Factory as IFacetFactoryWithIndex; List <FacetMoniker> index; try { index = ffwi.EnumerateIndex().ToList(); } catch (RequiresConfigurationException rce) { factory.AddIndexingException(rce); return; } catch (Exception ex) { // TODO: failure log? Debug.WriteLine("Could not enumerate indexed factory {0}: {1}", factory.Name, ex.Message); return; } using (var connection = DatabaseUtil.GetConnection()) { const string cmdText = @"SELECT FacetTypeId, FactoryDataHash, DisplayName FROM FacetMonikers WHERE FactoryTypeId = @FactoryTypeId"; // load the existing index using (var cmd = new SqlCeCommand(cmdText, connection)) { cmd.Parameters.AddWithValue("@FactoryTypeId", factory.DatabaseId); using (var reader = cmd.ExecuteResultSet(ResultSetOptions.Updatable)) { while (reader.Read()) { var facetType = Loader.GetFacetInfo((int)reader["FacetTypeId"]); if (facetType == null) { // this would be unusual, since the factory is loaded continue; } var factoryDataHash = (string)reader["FactoryDataHash"]; var displayName = (string)reader["DisplayName"]; var indexIndex = index.FindIndex(x => x.FacetType == facetType.Type && x.HashString == factoryDataHash && x.DisplayName == displayName); if (indexIndex == -1) { reader.Delete(); } else { index.RemoveAt(indexIndex); } } } } foreach (var moniker in index) { SaveFacetMoniker(moniker, connection: connection); } } }
public static IEnumerable <ParseResult> ParseFromHistory(IEnumerable <TypeDescriptor> facetTypes, ParseInput input) { var facetTypeIds = String.Join(",", from ft in facetTypes select Loader.GetFacetInfo(ft).DatabaseId.ToString()); if (facetTypeIds.Length == 0) { yield break; } using (var connection = DatabaseUtil.GetConnection()) { const string cmdText = @"SELECT FacetMonikers.Id, DisplayName, DateTime, FactoryData, FacetTypeId, FactoryTypeId, Source, ExtraData, Alias, cua.MatchedText, cua.Relevance FROM FacetMonikers LEFT OUTER JOIN CommandUsageArguments AS cua ON cua.FacetMonikerId = FacetMonikers.Id WHERE FacetTypeID IN ({0}) AND ({1}) ORDER BY FactoryTypeId"; using (var cmd = new SqlCeCommand()) { var clausesBuilder = new StringBuilder(); for (var index = 0; index < input.Terms.Count; index++) { if (clausesBuilder.Length > 0) { clausesBuilder.Append(" OR "); } var term = input.Terms[index]; var paramName = "@t" + index; clausesBuilder.AppendFormat("cua.MatchedText = {0} OR Alias = {0}", paramName); cmd.Parameters.AddWithValue(paramName, term.Text); } cmd.Connection = connection; cmd.CommandText = String.Format(cmdText, facetTypeIds, clausesBuilder); Func <string, string, bool> compareTerm = (term, text) => string.Compare(term, text, StringComparison.CurrentCultureIgnoreCase) == 0; var returned = new List <ParseResult>(); var query = from row in cmd.ExecuteReader().AsEnumerable() let moniker = MaterializeMoniker(row) where moniker != null let rowMatchedText = row["MatchedText"] as string let rowAlias = row["Alias"] as string let match = // determine the (first) input term that selected this row: (from t in input.Terms let mtMatch = compareTerm(t.Text, rowMatchedText) let aliasMatch = compareTerm(t.Text, rowAlias) where mtMatch || aliasMatch select new { Term = t, mtMatch, aliasMatch }).First() where match.Term.IsFactorySourceValid(moniker.GetFactory()) select new ParseResult(match.Term, moniker, match.aliasMatch ? 1.0 : row.ValOrDefault("Relevance", 1.0)); // above: if "Relevance" is null, we matched via alias, which is relevance 1.0. foreach (var result in query) { if (!returned.Contains(result)) { returned.Add(result); yield return(result); } } } } }
public static IEnumerable <ParseResult> Parse(IEnumerable <IFacetFactoryWithIndex> factories, IEnumerable <TypeDescriptor> facetTypes, ParseInput input, ParseMode all, List <RequiresConfigurationException> outConfigurationExceptions) { var factoryTypesAndIds = factories.ToDictionary(f => Loader.GetFactoryInfo(f).DatabaseId); if (factoryTypesAndIds.Count == 0) { yield break; } var facetTypeIds = String.Join(",", from ft in facetTypes select Loader.GetFacetInfo(ft).DatabaseId.ToString()); using (var connection = DatabaseUtil.GetConnection()) { const string cmdText = @"SELECT Id, DisplayName, DateTime, FactoryData, FacetTypeId, FactoryTypeId, Source, ExtraData FROM FacetMonikers WHERE FactoryTypeId = @FactoryTypeId AND FacetTypeID IN ({0}) ORDER BY FactoryTypeId"; var cmd = new SqlCeCommand(String.Format(cmdText, facetTypeIds), connection); var param = cmd.Parameters.Add("@FactoryTypeId", SqlDbType.Int); foreach (var factory in factoryTypesAndIds) { param.Value = factory.Key; if (factory.Key == 18) { int x = 10; } var query = from row in cmd.ExecuteReader().AsEnumerable() select MaterializeMoniker(row); IEnumerable <ParseResult> results; try { // TODO: remove ToArray when you figure out what causes "the row is deleted" bullll sheeet results = factory.Value.Parse(input.RewriteInput(Loader.GetFactoryInfo(factory.Value), false), query.ToArray()); } catch (OutOfMemoryException) { throw; } catch (RequiresConfigurationException ex) { outConfigurationExceptions.Add(ex); continue; } catch { continue; } foreach (var result in results) { yield return(result); } } } }
public static int?SaveFacetMoniker(FacetMoniker moniker, string alias = DontSetAlias, SqlCeConnection connection = null, SqlCeTransaction trans = null) { var localConnection = connection ?? DatabaseUtil.GetConnection(); try { var facetTypeId = Loader.GetFacetInfo(moniker.FacetType).DatabaseId; int? factoryTypeId = null; string factoryData; if (!moniker.IsAmbient) { factoryTypeId = Loader.GetFactoryInfo(moniker.FactoryType).DatabaseId; factoryData = moniker.FactoryData; } else { // we misuse the factorydata column for ambient facets because it simplifies // storage/retrieval factoryData = moniker.AmbientToken.ToString(); } using (var cmd = new SqlCeCommand("FacetMonikers", localConnection, trans)) { cmd.IndexName = "UQ_FacetMonikers"; cmd.CommandType = CommandType.TableDirect; using (var rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable)) { if (rs.Seek(DbSeekOptions.FirstEqual, factoryTypeId, facetTypeId, moniker.HashString) && rs.Read()) { if (alias != DontSetAlias) { var aliasOrdinal = rs.GetOrdinal("Alias"); if (rs.IsDBNull(aliasOrdinal) != (alias == null) || rs[aliasOrdinal] as string != alias) { rs.SetString(aliasOrdinal, alias); rs.Update(); } } return((int)rs["Id"]); } var record = rs.CreateRecord(); record["FacetTypeId"] = facetTypeId; record["FactoryTypeId"] = factoryTypeId; record["DisplayName"] = moniker.DisplayName; record["FactoryDataHash"] = moniker.HashString; record["FactoryData"] = factoryData; record["Source"] = moniker.Source; record["Alias"] = alias; if (moniker.DateTime != null) { record["DateTime"] = moniker.DateTime.Value; } if (moniker.ExtraData != null) { record["ExtraData"] = SerializeExtraData(moniker); } rs.Insert(record, DbInsertOptions.PositionOnInsertedRow); return((int)rs["Id"]); } } } finally { if (connection == null) { localConnection.Dispose(); } } }
public static IEnumerable <CommandExecutor> LoadPartialCommands(string alias = null) { using (var connection = DatabaseUtil.GetConnection()) { const string selectCmdText = @"SELECT Id, CommandId, Alias FROM PartialCommands"; const string selectArgText = @"SELECT fm.FactoryTypeId, fm.FacetTypeId, fm.DisplayName, fm.DateTime, fm.FactoryData, fm.Source, fm.ExtraData, pca.Ordinal FROM PartialCommandArguments AS pca INNER JOIN FacetMonikers AS fm ON fm.Id = pca.FacetMonikerId WHERE pca.PartialCommandId = @PartialCommandId ORDER BY pca.Ordinal"; var selectCmdTextFinal = selectCmdText + (alias == null ? "" : " WHERE Alias = @Alias"); using (var selectCmdCommand = new SqlCeCommand(selectCmdTextFinal, connection)) using (var selectArgCommand = new SqlCeCommand(selectArgText, connection)) { if (alias != null) { selectCmdCommand.Parameters.AddWithValue("@Alias", alias); } var partialCommandIdParam = selectArgCommand.Parameters.Add("@PartialCommandId", SqlDbType.Int); foreach (var cmd in selectCmdCommand.ExecuteReader().AsEnumerable()) { partialCommandIdParam.Value = (int)cmd["Id"]; var command = Loader.GetCommandInfo((int)cmd["CommandId"]); if (command == null) { continue; } var args = (from arg in selectArgCommand.ExecuteReader().AsEnumerable() let moniker = FacetIndex.MaterializeMoniker(arg) select new { Ordinal = (int)arg["Ordinal"], Argument = new CommandArgument(moniker) }).ToArray(); var allArgs = new List <CommandArgument>(); for (var ordinal = 0; ordinal < command.Command.Parameters.Count; ordinal++) { var loadedArg = args.Where(x => x.Ordinal == ordinal).Select(x => x.Argument).FirstOrDefault(); allArgs.Add(loadedArg ?? CommandArgument.Unspecified); } var cmdAlias = (string)cmd["Alias"]; yield return(new CommandExecutor(null, command.Command, allArgs, cmdAlias, new[] { cmdAlias })); } } } }
public static void SaveExecutedCommand(CommandExecutor executor) { using (var connection = DatabaseUtil.GetConnection()) { using (var trans = connection.BeginTransaction()) { const string insertCmdText = @"INSERT INTO CommandUsages (Text, At, CommandId) VALUES (@Text, @At, @CommandId)"; const string insertArgText = @"INSERT INTO CommandUsageArguments (CommandUsageId, FacetMonikerId, Ordinal, RangeStartIndex, RangeLength, Relevance, MatchedText) VALUES (@CommandUsageId, @FacetMonikerId, @Ordinal, @RangeStartIndex, @RangeLength, 0.0, @MatchedText)"; int commandUsageId; using (var cmd = new SqlCeCommand(insertCmdText, connection, trans)) { cmd.Parameters.AddWithValue("@CommandId", Loader.GetCommandInfo(executor.Command).DatabaseId); cmd.Parameters.AddWithValue("@Text", executor.Input == null ? "" : executor.Input.Text); cmd.Parameters.AddWithValue("@At", DateTime.Now); cmd.ExecuteNonQuery(); commandUsageId = connection.GetLastInsertedId(trans); } using (var cmd = new SqlCeCommand(insertArgText, connection, trans)) { cmd.Parameters.AddWithValue("@CommandUsageId", commandUsageId); cmd.Parameters.AddWithValue("@FacetMonikerId", 0); cmd.Parameters.AddWithValue("@Ordinal", 0); cmd.Parameters.AddWithValue("@RangeStartIndex", 0); cmd.Parameters.AddWithValue("@RangeLength", 0); cmd.Parameters.AddWithValue("@MatchedText", ""); for (var ordinal = 0; ordinal < executor.Arguments.Count; ordinal++) { var arg = executor.Arguments[ordinal]; if (!arg.IsSpecified) { continue; } cmd.Parameters["@Ordinal"].Value = ordinal; cmd.Parameters["@FacetMonikerId"].Value = FacetIndex.SaveFacetMoniker(arg.FacetMoniker, connection: connection, trans: trans); if (arg.ParseRange != null) { cmd.Parameters["@RangeStartIndex"].Value = arg.ParseRange.StartIndex; cmd.Parameters["@RangeLength"].Value = arg.ParseRange.Length; cmd.Parameters["@MatchedText"].Value = executor.Input == null ? "" : executor.Input.GetTextForRange(arg.ParseRange); } else { cmd.Parameters["@RangeStartIndex"].Value = -1; cmd.Parameters["@RangeLength"].Value = -1; cmd.Parameters["@MatchedText"].Value = ""; } cmd.ExecuteNonQuery(); } } trans.Commit(); } } }
static FacetMoniker[] GetMostUsedFacets(TypeMoniker commandParameterType, CommandParameter parameter, int maxResults) { var rvl = new List <FacetMoniker>(); commandParameterType = parameter == null ? commandParameterType : parameter.Type; var facetTypeIds = Loader.GetFacetTypesImplementing(commandParameterType) .Select(x => x.DatabaseId.ToString()) .JoinStrings(","); if (facetTypeIds == "") { // no facet types support this command parameter type return(rvl.ToArray()); } using (var connection = DatabaseUtil.GetConnection()) { const string innerSelect = @"SELECT cua.FacetMonikerId, COUNT(*) AS _count FROM CommandUsageArguments AS cua INNER JOIN FacetMonikers AS fm ON fm.Id = cua.FacetMonikerId {0} WHERE fm.FacetTypeId IN ({1}) GROUP BY cua.FacetMonikerId"; const string forCommandFragment = @"INNER JOIN CommandUsages AS cu ON cu.Id = cua.CommandUsageId AND cu.CommandId = @CommandId"; var innerX = String.Format("INNER JOIN ({0}) AS x ON x.FacetMonikerId = fm.Id", String.Format(innerSelect, "", facetTypeIds)); var innerY = parameter == null ? null : String.Format("LEFT OUTER JOIN ({0}) AS y ON y.FacetMonikerId = fm.Id", String.Format(innerSelect, forCommandFragment, facetTypeIds)); var selectYCount = parameter == null ? "" : ", y._count"; var order = parameter == null ? "x._count DESC" : "COALESCE(y._count, 0) DESC, x._count DESC"; const string cmdText = @"SELECT fm.FactoryTypeId, fm.FacetTypeId, fm.DisplayName, fm.DateTime, fm.FactoryData, fm.Source, fm.ExtraData, x._count {0} FROM FacetMonikers AS fm {1} {2} WHERE fm.Id = x.FacetMonikerId ORDER BY {3}"; var finalCmdText = String.Format(cmdText, selectYCount, innerX, innerY, order); using (var cmd = new SqlCeCommand(finalCmdText, connection)) { if (parameter != null) { cmd.Parameters.AddWithValue("@CommandId", Loader.GetCommandInfo(parameter.Command).DatabaseId); } foreach (var row in cmd.ExecuteReader().AsEnumerable()) { var moniker = FacetIndex.MaterializeMoniker(row); if (moniker == null || (parameter != null && !parameter.IsUsableAsArgument(moniker))) { continue; } rvl.Add(moniker); if (rvl.Count == maxResults) { break; } } } } return(rvl.ToArray()); }
public static CommandUsage[] GetCommandUsages(IEnumerable <Command> commands, bool commandsOnly = false) { var rvl = new List <CommandUsage>(); using (var selectCommandConnection = DatabaseUtil.GetConnection()) using (var selectArgConnection = DatabaseUtil.GetConnection()) { const string selectCommandText = @"SELECT cu.Id, cu.At, cu.Text AS ParseInput FROM CommandUsages AS cu WHERE cu.CommandId = @CommandId"; const string selectArgText = @"SELECT fm.FactoryTypeId, fm.FacetTypeId, fm.DisplayName, fm.DateTime, fm.FactoryData, fm.Source, fm.ExtraData, cua.Ordinal, cua.RangeStartIndex, cua.RangeLength, cua.Relevance, cua.MatchedText FROM CommandUsageArguments AS cua INNER JOIN FacetMonikers AS fm ON fm.Id = cua.FacetMonikerId WHERE cua.CommandUsageId = @CommandUsageId ORDER BY cua.Ordinal"; using (var selectCommandUsageCmd = new SqlCeCommand(selectCommandText, selectCommandConnection)) using (var selectUsageArgumentsCmd = new SqlCeCommand(selectArgText, selectArgConnection)) { selectCommandUsageCmd.Parameters.AddWithValue("@CommandId", SqlDbType.Int); selectUsageArgumentsCmd.Parameters.AddWithValue("@CommandUsageId", SqlDbType.Int); foreach (var command in commands.Distinct()) { selectCommandUsageCmd.Parameters["@CommandId"].Value = Loader.GetCommandInfo(command).DatabaseId; foreach (var cmdRow in selectCommandUsageCmd.ExecuteReader().AsEnumerable()) { if (commandsOnly) { rvl.Add(new CommandUsage(command, (DateTime)cmdRow["At"])); continue; } var parseInput = new ParseInput((string)cmdRow["ParseInput"]); var arguments = new List <CommandArgument>(); var lastOrdinal = -1; selectUsageArgumentsCmd.Parameters["@CommandUsageId"].Value = (int)cmdRow["Id"]; foreach (var argRow in selectUsageArgumentsCmd.ExecuteReader().AsEnumerable()) { var ordinal = (int)argRow["Ordinal"]; while (++lastOrdinal < ordinal) { arguments.Add(CommandArgument.Unspecified); } // reconstruct facet moniker var moniker = FacetIndex.MaterializeMoniker(argRow); if (moniker == null) { arguments = null; break; } if ((int)argRow["RangeStartIndex"] != -1) { // reconstruct parse range var range = new ParseRange( (int)argRow["RangeStartIndex"], (int)argRow["RangeLength"]); // reconstruct parseresult var parseResult = new ParseResult( parseInput, range, moniker, (double)argRow["Relevance"]); arguments.Add(new CommandArgument(parseResult)); } else { // moniker only arguments.Add(new CommandArgument(moniker)); } } if (arguments != null) { while (++lastOrdinal < command.Parameters.Count) { arguments.Add(CommandArgument.Unspecified); } var cei = new CommandExecutor(parseInput, command, arguments); rvl.Add(new CommandUsage(cei, (DateTime)cmdRow["At"])); } } } } } return(rvl.ToArray()); }