public (TableIndex, StatementEqualsRef[], StatementEqualsRef[]) GetWhereIndexSetRefsAndWhereRefs(IDatabase database, Dict statementParams)
{
if (string.IsNullOrEmpty(this.setClause) && string.IsNullOrEmpty(this.whereClause))
{
if (this.StatementFromRefs.Length > 1)
{
throw new Exception("Cannot auto fill set clause and where clause with more than one table in update statement");
}
var statementEqualsRefs = statementParams.Select(x => new StatementEqualsRef(this.StatementFromRefs[0].table.Name, x.Key, x.Key));
var uniqueIndex = this.StatementFromRefs[0].table.FindUniqueIndex(statementEqualsRefs.ToArray());
if (uniqueIndex == null)
{
throw new Exception($"Could not find unique index building WHERE clause of UPDATE statement");
}
var whereRefs = statementEqualsRefs.Where(x => uniqueIndex.FieldNames.Contains(x.fieldName)).ToArray();
var setRefs = statementEqualsRefs.Where(x => !uniqueIndex.FieldNames.Contains(x.fieldName)).ToArray();
return(uniqueIndex, setRefs, whereRefs);
}
else
{
var setRefs = BaseStatement.DetermineEqualsRefs(database, this.setClause);
var whereRefs = BaseStatement.DetermineEqualsRefs(database, this.whereClause);
var uniqueIndex = this.StatementFromRefs[0].table.FindUniqueIndex(whereRefs);
return(uniqueIndex, setRefs, whereRefs);
}
}
public (TableIndex, StatementEqualsRef[]) GetWhereIndexAndWhereRefs(IDatabase database, Dict statementParams)
{
StatementEqualsRef[] equalsRefs;
if (string.IsNullOrEmpty(this.whereClause))
{
if (this.StatementFromRefs.Length > 1)
{
throw new Exception("Cannot auto fill where clause with more than one table in DELETE statement");
}
equalsRefs = statementParams.Select(x => new StatementEqualsRef(this.StatementFromRefs[0].table.Name, x.Key, x.Key)).ToArray();
}
else
{
equalsRefs = BaseStatement.DetermineEqualsRefs(database, this.whereClause);
}
var uniqueIndex = this.StatementFromRefs[0].table.FindUniqueIndex(equalsRefs);
if (uniqueIndex == null)
{
throw new Exception($"Could not find unique index building WHERE clause of DELETE statement");
}
if (equalsRefs.Length > uniqueIndex.FieldNames.Length)
{
throw new Exception($"Unused fields auto filling WHERE clause of DELETE statement ({string.Join(",", equalsRefs.Select(x => x.fieldName))})");
}
return(uniqueIndex, equalsRefs);
}
public (string, Dict) GetExecutableSqlAndParams(Dict statementParams, Dict defaultValues)
{
Dict executableParams = new Dict();
executableParams.UpdateFrom(defaultValues);
executableParams.UpdateFrom(statementParams);
(List <string> names, List <string> values) = this.GetNamesAndValues(executableParams, defaultValues);
string newNamesClause = string.Join(",", names);
string newValuesClause = string.Join(",", values);
string executableSql = $"INSERT INTO {this.StatementFromRefs[0].table.Name} ({newNamesClause}) VALUES ({newValuesClause})";
BaseStatement.ConfirmAllParamsUsed(executableSql, executableParams);
return(executableSql, executableParams);
}
protected async Task <object> GetKeyValue(TableIndex whereIndex, Dict varsDict, Dict executableParams, StatementEqualsRef[] whereRefs, TableIndex primaryIndex, string tableName)
{
Dict fieldValues;
if (whereIndex.IndexType == TableIndexType.Primary)
{
fieldValues = BaseStatement.RemapStatementParamsToFieldValues(varsDict, whereRefs);
}
else
{
// This extra select to convert from a non-primary key index to a primary key index is unfortunate
var selectValues = whereRefs.ToDictionary(x => x.fieldName, x => executableParams[x.fieldName]);
fieldValues = await this.database.SelectRowAsync($"SELECT {string.Join(",", primaryIndex.FieldNames)} FROM {tableName}", selectValues);
}
if (fieldValues == null)
{
return(null);
}
else
{
return(BaseDatabase.GetKeyValue(primaryIndex.FieldNames, fieldValues));
}
}