/// <summary>
/// Get where clause for viewon main table
/// </summary>
protected virtual SqlSelectBuilder.Where MainTableWhereClause(TableDef tabledef, ViewonKey key)
{
var w = new SqlSelectBuilder.Where();
foreach (var cri in key.Criteria)
{
var coldef = tabledef.FindCol(cri.Name);
if (coldef != null)
{
var crihelper = new ViewonCriterion(coldef, cri.PackedValue);
crihelper.ExportWhereClause(w, SqlFlavor);
}
}
return(w);
}
/// <summary>
/// Get where clause for persiston main table, or null if this is a whole-table persiston
/// </summary>
protected virtual SqlSelectBuilder.Where MainTableWhereClause(TableDef tabledef, PersistonKey key)
{
if (key.WholeTable)
{
return(null);
}
var pkType = tabledef.FindCol(tabledef.PrimaryKeyColName).CSType;
object pk = key.PrimaryKey; //always string here
if (pkType != typeof(string))
{
pk = Utils.ChangeType(pk, pkType);
}
var w = new SqlSelectBuilder.Where();
w.AddWhere($"{tabledef.PrimaryKeyColName}={w.NextParameterName()}", pk);
return(w);
}
/// <summary>
/// Called from Load after loading the top table to load all other tables.
/// The base implementation is careful to only load a table once regardless of the number of detail levels and the number of parent/grandparent
/// rows. For example for a persiston whose table structure is (Sale, LineItem, LineItemNote), then there will only be one select statement
/// issued for LineItemNote, using an IN clause for the primary keys of LineItem (which were already loaded).
/// (The call syntax would be complex to invoke this for the top table so this is called only once for the children of the top
/// table.)
/// </summary>
/// <param name="parentRows">parent Row objects indexed by primary key value</param>
protected async Task LoadChildTablesRecursive(Dictionary <object, Row> parentRows, IDbConnection db, DataDictionary dbdef, TableDef parentdef)
{
if (!parentRows.Any())
{
return;
}
if (parentdef.Children == null)
{
return;
}
//get parent keys in the form "1,2,3..."
string parentKeyListFormatted = SqlSelectBuilder.FormatInClauseList(parentRows.Keys);
foreach (var childTabledef in parentdef.Children)
{
//get rows - where clause is "parentkey in (...)"
var whereClause = new SqlSelectBuilder.Where();
whereClause.AddWhere($"{childTabledef.ParentKeyColumnName} in({parentKeyListFormatted})");
var loadResult = await LoadTable(db, dbdef, childTabledef, whereClause, childTabledef.DefaulSortColName, 0, 0);
var rowdict = loadResult.RowsByParentKey;
//deal out the rows into the parent objects' Lists of this type
var listField = parentdef.RowType.GetField(childTabledef.Name);
foreach (object parentKey in rowdict.Keys)
{
var rowsForParent = rowdict[parentKey];
var parentRow = parentRows[parentKey];
var list = Utils.CreateOrGetFieldValue <IList>(parentRow, listField);
foreach (var row in rowsForParent)
{
list.Add(row);
}
}
//recur
var rowsByPK = RestructureByPrimaryKey(childTabledef, rowdict);
await LoadChildTablesRecursive(rowsByPK, db, dbdef, childTabledef);
}
}
/// <summary>
/// Load rows given by where clause for a table and return them as dictionary indexed by the parent key value.
/// If there is no parent key for the table, then the dictionary will have one entry with key="".
/// The returned list members are objects of the type indicated by tabledef.
/// The implementation loads one additional row to determine whether the load was complete, if pageSize is nonzero.
/// </summary>
/// <param name="pageSize">if zero, does not do paging</param>
/// <param name="whereClause">can be null</param>
protected virtual Task <SingleLoadResult> LoadTable(IDbConnection db, DataDictionary dbdef, TableDef tabledef, SqlSelectBuilder.Where whereClause,
string sortColName, int pageSize, int pageNo)
{
var colInfos = BuildColumnsToLoadList(dbdef, tabledef);
string parentKeyColName = tabledef.ParentKeyColumnName;
int parentKeyColIndex = -1;
var columnNames = colInfos.Select(c => c.SqlExpression).ToList();
if (parentKeyColName != null)
{
parentKeyColIndex = columnNames.Count;
columnNames.Add(parentKeyColName);
}
int customColIndex = -1;
if (tabledef.HasCustomColumns)
{
customColIndex = columnNames.Count;
columnNames.Add(CUSTOMCOLNAME);
}
var sql = new SqlSelectBuilder(SqlFlavor, tabledef.SqlTableName, sortColName, columnNames)
{
PageSize = pageSize,
PageNo = pageNo
};
if (whereClause != null)
{
sql.WhereClause = whereClause;
}
var rowsByParentKey = new Dictionary <object, List <Row> >();
bool isComplete = true;
using (var cmd = db.CreateCommand())
{
cmd.CommandText = CustomizeSqlStatement(sql.ToString());
if (whereClause != null)
{
whereClause.ExportParameters(cmd);
}
using (var reader = cmd.ExecuteReader())
{
int rowsLoaded = 0;
while (reader.Read())
{
//if this is the throw-away row that was one more than the page size, then note as incomplete but don't look at it
if (++rowsLoaded > pageSize && pageSize > 0)
{
isComplete = false;
break;
}
//read parent key value
object pk = "";
if (parentKeyColIndex >= 0)
{
pk = reader.GetValue(parentKeyColIndex);
}
//read remaining values
var row = Utils.Construct(tabledef.RowType) as Row;
SetRowFromReader(colInfos, reader, row);
//read custom values
if (tabledef.HasCustomColumns)
{
string json = (string)Utils.ChangeType(reader.GetValue(customColIndex), typeof(string));
if (!string.IsNullOrEmpty(json))
{
SetRowFromCustomValues(json, tabledef, row);
}
}
//store in return dict
if (!rowsByParentKey.ContainsKey(pk))
{
rowsByParentKey[pk] = new List <Row>();
}
rowsByParentKey[pk].Add(row);
}
}
}
var ret = new SingleLoadResult
{
RowsByParentKey = rowsByParentKey,
IsComplete = isComplete
};
return(Task.FromResult(ret));
}
public void ExportWhereClause(SqlSelectBuilder.Where w, SqlFlavorizer sqlFlavor)
{
//numeric ranges
if (Utils.IsSupportedNumericType(ColDef.CSType))
{
try
{
(string lo, string hi) = SplitOnTilde(PackedValue);
if (lo != null)
{
decimal dlo = decimal.Parse(lo);
w.AddWhere($"{ColDef.Name}>={w.NextParameterName()}", dlo);
}
if (hi != null)
{
decimal dhi = decimal.Parse(hi);
w.AddWhere($"{ColDef.Name}<={w.NextParameterName()}", dhi);
}
}
catch
{
throw new Exception($"Misformatted numeric parameter: {PackedValue}");
}
}
//dates and times
else if (ColDef.CSType == typeof(DateTime))
{
bool isDateOnly = ColDef.WireType == Constants.TYPE_DATE;
(string lo, string hi) = SplitOnTilde(PackedValue);
if (lo != null)
{
var dlo = Retrovert.ParseRetroDateTime(lo, isDateOnly);
w.AddWhere($"{ColDef.Name}>={w.NextParameterName()}", dlo);
}
if (hi != null)
{
var dhi = Retrovert.ParseRetroDateTime(hi, isDateOnly);
w.AddWhere($"{ColDef.Name}<={w.NextParameterName()}", dhi);
}
}
else if (ColDef.CSType == typeof(bool))
{
bool b;
if (PackedValue == "0")
{
b = false;
}
else if (PackedValue == "1")
{
b = true;
}
else
{
throw new Exception($"Boolean parameter must be 0 or 1: {PackedValue}");
}
w.AddWhere($"{ColDef.Name}={w.NextParameterName()}", b);
}
else if (ColDef.CSType == typeof(string))
{
w.AddWhere($"{ColDef.Name} {sqlFlavor.LikeOperator()} {w.NextParameterName()}", sqlFlavor.LikeParamValue(PackedValue));
}
else
{
throw new Exception($"Type {ColDef.CSType.Name} not supported as a viewon parameter");
}
}
