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