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