// generate SQL code for dyadic antijoin operations
DataTableSql DyadicAntijoin(DataTableSql other, DataHeading joinhdg, DataHeading newheading)
{
var sql = _gen.SelectAntijoin(GetFrom(), other.GetFrom(), newheading, joinhdg);
var newtable = CreateFromSql(newheading, sql);
return(newtable);
}
// generate SQL code for dyadic set operations
DataTableSql DyadicSet(DataTableSql other, DataHeading newheading, JoinOps joinop)
{
var sql = _gen.SelectSet(GetFrom(), other.GetFrom(), newheading, joinop);
var newtable = DataTableSql.CreateFromSql(newheading, sql);
return(newtable);
}
// Get a catalog value by entry from database or catalog
public TypedValue GetValue(CatalogEntry entry)
{
if (entry.IsDatabase)
{
if (_catalog.SqlFlag)
{
// Database sql comes from external table
var table = DataTableSql.Create(entry.Name, entry.DataType.Heading);
return(RelationValue.Create(table));
}
else if (!entry.IsLoaded) // lazy load
// Database non-sql lazy loaded from store on path, then in catalog
{
var value = Persist.Create(_catalog.DatabasePath, true).Load(entry.Name);
if (entry.DataType != value.DataType)
{
throw ProgramError.Fatal("Catalog", "Type mismatch for variable {0}", entry.Name);
}
entry.IsLoaded = true;
return(Persist.Create(_catalog.DatabasePath, true).Load(entry.Name));
}
}
// Non-database exists only in the catalog
return(entry.Value);
}
// Generate SQL for COUNT
DataTableSql GenCount()
{
var sql = _gen.SelectCount(GetFrom());
var newtable = DataTableSql.CreateFromSql(DataHeading.Empty, sql);
return(newtable);
}
// Sql to delete multiple rows of data from named table
void DeleteValuesQuery(DataTableSql other)
{
_database.OpenStatement();
// SQL does positional matching, so column order must come from other
var sql = _gen.DeleteNamed(TableName, other.Heading, other.GetQuery()); //WRONG?
_database.ExecuteCommand(sql);
_database.CloseStatement();
}
// Create new table with previous as a subquery (ran out of slots)
static DataTableSql CreateFromSubquery(DataTableSql table)
{
var newtable = new DataTableSql {
DataType = DataTypeRelation.Get(table.Heading),
SqlSelectText = table._gen.SelectAll(table.GetFrom(), table),
};
return(newtable);
}
// Create new table from a partial query, allow more clauses to be added
static DataTableSql CreateFromSql(DataHeading heading, string sql)
{
var newtable = new DataTableSql {
DataType = DataTypeRelation.Get(heading),
SqlSelectText = sql,
};
return(newtable);
}
//--- functions to manipulate queries
// generate SQL code for restrict
DataTableSql AddWhere(ExpressionEval expr, bool hasgroupby)
{
if (SqlWhereText != null)
{
return(DataTableSql.CreateFromSubquery(this).AddWhere(expr, hasgroupby));
}
SqlWhereText = (hasgroupby) ? _gen.Having(expr) : _gen.Where(expr);
return(this);
}
// generate SQL code for order by
DataTableSql AddOrderBy(ExpressionEval[] expr)
{
if (SqlOrderByText != null)
{
return(DataTableSql.CreateFromSubquery(this).AddOrderBy(expr));
}
SqlOrderByText = _gen.OrderBy(expr);
return(this);
}
public string SelectAll(string tableorquery, DataTableSql other)
{
var dict = new Dictionary <string, SubstituteDelegate> {
{ "select", (x) => tableorquery },
{ "namelist", (x) => NameList(other.Heading) },
};
return(_templater.Process("SelectAll", dict));
}
public override DataTable Transform(DataHeading heading, ExpressionEval[] exprs)
{
_database.RegisterExpressions(exprs);
var sql = _gen.SelectAs(GetFrom(), exprs);
var newtable = DataTableSql.CreateFromSql(heading, sql);
Logger.WriteLine(4, "[Trn '{0}']", newtable);
return(newtable);
}
public override DataTable Project(ExpressionEval[] exprs)
{
var heading = DataHeading.Create(exprs);
var sql = _gen.SelectAs(GetFrom(), exprs);
var newtable = DataTableSql.CreateFromSql(heading, sql);
Logger.WriteLine(4, "[Pro '{0}']", newtable);
return(newtable);
}
public override DataTable Rename(ExpressionEval[] exprs)
{
// note: heading order must match exprs (cf Local)
var heading = DataHeading.Create(exprs);
var sql = _gen.SelectAs(GetFrom(), exprs);
var newtable = DataTableSql.CreateFromSql(heading, sql);
Logger.WriteLine(4, "[Ren '{0}']", newtable);
return(newtable);
}
// Compare tables: joinop is INTERSECT, MINUS or NUL (for equals)
bool SetCompare(DataTableSql other, JoinOps joinop, bool both)
{
Logger.Assert(Heading.Equals(other.Heading));
var sql = _gen.SelectOneWhere(GetQuery(), other.GetQuery(), joinop, both);
var ret = GetBoolValue(sql);
Release();
other.Release();
return(ret);
}
// Create new base table, assumed to exist
public static DataTableSql Create(string name, DataHeading heading)
{
var ret = new DataTableSql {
DataType = DataTypeRelation.Get(heading),
TableName = name,
};
ret.DataType.ProposeCleanName(name);
return(ret);
}
public override DataTable TransformAggregate(DataHeading heading, ExpressionEval[] exprs)
{
_database.RegisterExpressions(exprs);
// groups must include all cols that are input to anything other than Aggregate
var groups = GroupColumns(exprs);
var sql = _gen.SelectAsGroup(GetFrom(), exprs, groups);
var newtable = DataTableSql.CreateFromSql(heading, sql);
newtable.HasGroupBy = true;
Logger.WriteLine(4, "[TrnA '{0}']", newtable);
return(newtable);
}
// Store new entry to catalog, perhaps removing old value first
// Currently only for Sql -- Local does total dump
internal void StoreEntry(CatalogEntry entry, CatalogEntry oldentry = null)
{
var ctm = CatalogTableMaker.Create(_catalogtableheading);
if (SqlFlag)
{
var table = DataTableSql.Create(CatalogTableName, _catalogtableheading);
if (oldentry != null)
{
var ctmx = CatalogTableMaker.Create(_catalogtableheading);
ctmx.Table.AddRow(ctmx.MakeEntry(oldentry));
table.UpdateJoin(ctmx.Table, JoinOps.MINUS);
}
ctm.Table.AddRow(ctm.MakeEntry(entry));
table.UpdateJoin(ctm.Table, JoinOps.UNION);
}
}
// set entry to value, update as needed
internal void SetValue(CatalogEntry entry, TypedValue value)
{
// Choose where to store and whether to convert
TypedValue finalvalue;
if (entry.IsDatabase && _catalog.SqlFlag)
{
// Database + sql => hand it to sql, to create table if needed
// set a default value to carry the type
DataTableSql.Create(entry.Name, value.AsTable());
finalvalue = value.DataType.DefaultValue();
}
else
{
// everything else stored in catalog
if (value.DataType is DataTypeRelation)
{
finalvalue = RelationValue.Create(DataTableLocal.Convert(value.AsTable()));
}
else
{
finalvalue = value;
}
// set flags for persisting
if (entry.IsPersistent)
{
entry.IsUnsaved = true;
entry.IsLoaded = true;
}
}
// store value if changed
if (finalvalue != entry.Value)
{
var oldvalue = (entry.Value == null) ? null : entry;
entry.Set(finalvalue);
if (entry.IsPersistent && _catalog.SaveFlag)
{
_catalog.StoreEntry(entry, oldvalue);
}
}
}
//--- persistence Mk II
// Store the persistent catalog and modified tables, local only
// note: for Sql, only used to create new empty catalog
public void StoreToTable()
{
Logger.WriteLine(2, "Save catalog for '{0}'", DatabaseName);
var ctm = CatalogTableMaker.Create(_catalogtableheading);
var table = ctm.AddEntries(PersistentVars.GetEntries()).Table;
if (SqlFlag)
{
DataTableSql.Create(CatalogTableName, table);
}
else
{
Persist.Create(DatabasePath, true).Store(CatalogTableName, RelationValue.Create(table));
var savers = PersistentVars.GetEntries().Where(e => e.IsUnsaved);
Logger.WriteLine(2, $"Persist {savers.Count()} entries");
foreach (var entry in savers)
{
Persist.Create(DatabasePath, true).Store(entry.Name, entry.Value);
}
}
}
// Get the value of a relation from a database
// Entry previously created by peeking
public bool LinkRelvar(string name)
{
var entry = GlobalVars.FindEntry(name);
Logger.Assert(entry != null && entry.IsDatabase);
var heading = entry.DataType.Heading;
if (SqlFlag)
{
var sqlheading = SqlTarget.Current.GetTableHeading(name);
if (sqlheading == null)
{
throw ProgramError.Fatal("Catalog", "sql table not found: '{0}'", name);
}
// TODO: smarter test, but still may not match exactly
//if (!heading.Equals(sqlheading))
if (heading.Degree != sqlheading.Degree)
{
throw ProgramError.Fatal("Catalog", "sql table schema mismatch: '{0}'", name);
}
var table = DataTableSql.Create(name, heading);
GlobalVars.SetValue(entry, RelationValue.Create(table));
}
else
{
var tablev = Persist.Create(DatabasePath, false).Load(name);
if (tablev == null)
{
throw ProgramError.Fatal("Catalog", "local table not found: '{0}'", name);
}
if (!heading.Equals(tablev.Heading))
{
throw ProgramError.Fatal("Catalog", "local table schema mismatch: '{0}'", name);
}
GlobalVars.SetValue(entry, RelationValue.Create(tablev.AsTable()));
}
return(true);
}
public override DataTable TransformOrdered(DataHeading heading, ExpressionEval[] exprs, ExpressionEval[] orderexps)
{
_database.RegisterExpressions(exprs);
var names = exprs.Where(e => e.IsProject).Select(e => e.Name);
var isagg = exprs.Any(e => e.HasFold);
var allin = orderexps.All(o => names.Contains(o.Name));
// if all ordering fields are in exprs then add order to newtable, else to this table
if (!allin)
{
AddOrderBy(orderexps);
}
var sql = (isagg) ? _gen.SelectAsGroup(GetFrom(), exprs, GroupColumns(exprs))
: _gen.SelectAs(GetFrom(), exprs);
var newtable = DataTableSql.CreateFromSql(heading, sql);
if (allin)
{
newtable.AddOrderBy(orderexps);
}
newtable.HasGroupBy = isagg;
Logger.WriteLine(4, "[TrnO '{0}']", newtable);
return(newtable);
}
