123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290 |
- using System;
- using System.Collections;
- using System.Collections.Generic;
- using System.Linq;
- using System.Reflection;
- using System.Text;
- namespace InABox.Core
- {
- /*
- *
- * Incomplete class - an attempt to simplify and separate the SQLiteProvider funtionality into a generalised
- * (reflection-driven) model that can be created once, and a streamlined processor that genrerates valid
- * SQL to throw at the database.
- *
- * Currently does not handle aggregates, formulas, conditions, filters or sorts, but it does create a nice
- * simple JOIN model based on Entity Properties, EntityLinks and EnclosedEntities
- *
- * If we can get this working, it will ease the way towards using a non-SQLite database (eg SQL Server, MySQL, etc)
- * but there is still some way to go!
- *
- */
- public class SQLFieldDefinition
- {
-
- public SQLTableDefinition Parent { get; private set; }
-
- public String ColumnName { get; private set; }
-
- public String FullName { get; private set; }
-
- public IFormula Formula { get; set; }
-
- public SQLFieldDefinition(SQLTableDefinition parent, String columnName, IFormula formula)
- {
- Parent = parent;
-
- ColumnName = columnName;
-
- FullName = ColumnName;
- var curparent = parent;
- while ((curparent != null) && (!String.IsNullOrWhiteSpace(curparent.LinkName)))
- {
- FullName = $"{curparent.LinkName}.{FullName}";
- curparent = curparent.Parent;
- }
- Formula = null;
- }
-
- public override string ToString() => FullName;
- }
-
- public class SQLTableDefinition
- {
-
- public Type Type { get; private set; }
-
- public int TableNumber { get; private set; }
-
- public String TableName { get; private set; }
- public String LinkName { get; set; }
-
- public SQLTableDefinition Parent { get; private set; }
-
- public List<SQLFieldDefinition> Fields { get; private set; }
-
- public List<SQLTableDefinition> Joins { get; private set; }
-
- public override string ToString() => $"{TableName}";
- public SQLTableDefinition(Type type, SQLTableDefinition parent)
- {
- Type = type;
- LinkName = "";
- Parent = parent;
- TableName = type.EntityName().Split('.').Last();
- Fields = new List<SQLFieldDefinition>();
- Joins = new List<SQLTableDefinition>();
- }
-
- private IEnumerable<PropertyInfo> PropertyList(Type type, Func<PropertyInfo,bool> predicate)
- {
- return CoreUtils.PropertyList(type,
- x => x.GetCustomAttribute<DoNotPersist>() == null
- && x.GetCustomAttribute<DoNotSerialize>() == null
- && x.PropertyType != typeof(UserProperties)
- && x.GetCustomAttribute<AggregateAttribute>() == null
- && x.GetCustomAttribute<ComplexFormulaAttribute>() == null
- //&& x.GetCustomAttribute<FormulaAttribute>() == null
- && x.GetCustomAttribute<ConditionAttribute>() == null
- ).Where(x => predicate(x));
- }
-
- private void LoadEnclosedEntity(PropertyInfo enclosedentity, String prefix, ref int tableNumber)
- {
- var props = PropertyList(enclosedentity.PropertyType, x => true);
- foreach (var prop in props)
- {
- if (prop.PropertyType.GetInterfaces().Contains(typeof(IEnclosedEntity)))
- LoadEnclosedEntity(
- prop,
- $"{prefix}.{prop.Name}",
- ref tableNumber
- );
- else if (prop.PropertyType.GetInterfaces().Contains(typeof(IEntityLink)))
- LoadEntityLink(
- prop,
- $"{prefix}.{prop.Name}",
- ref tableNumber
- );
- else
- Fields.Add(new SQLFieldDefinition(this, $"{prefix}.{prop.Name}", prop.GetCustomAttribute<FormulaAttribute>()));
- }
- }
-
- private void LoadEntityLink(PropertyInfo entitylink, String prefix, ref int tableNumber)
- {
- var linktype = entitylink.PropertyType.GetInterfaces()
- .FirstOrDefault(i => i.IsGenericType && i.GetGenericTypeDefinition() == typeof(IEntityLink<>))
- ?.GenericTypeArguments[0];
- if (linktype != null)
- {
- Fields.Add(new SQLFieldDefinition(this, $"{entitylink.Name}.ID", null));
- var props = PropertyList(entitylink.PropertyType, x => !String.Equals(x.Name, "ID"));
- if (props.Any())
- {
- SQLTableDefinition join = new SQLTableDefinition(linktype, this) { LinkName = prefix };
- tableNumber++;
- join.Load(entitylink.PropertyType, ref tableNumber);
- Joins.Add(join);
- }
- }
- }
-
- public void Load(Type type, ref int tableNumber)
- {
- TableNumber = tableNumber;
- var props = PropertyList(type, x => true);
- foreach (var prop in props)
- {
- if (prop.PropertyType.GetInterfaces().Contains(typeof(IEnclosedEntity)))
- LoadEnclosedEntity(
- prop,
- prop.Name,
- ref tableNumber
- );
- else if (prop.PropertyType.GetInterfaces().Contains(typeof(IEntityLink)))
- LoadEntityLink(
- prop,
- prop.Name,
- ref tableNumber
- );
- else
- LoadField(prop);
- }
- }
- private void LoadField(PropertyInfo prop)
- {
- IFormula formula = prop.GetCustomAttribute<FormulaAttribute>();
- if (formula != null)
- {
- // Because Formulas can themselves contain properties from EntityLinks
- // or Enclosed Entities, we need to deal with the requisite nesting of tables
- // although I'm not sure how this will work out in practice
- }
- Fields.Add(new SQLFieldDefinition(this, prop.Name, formula));
- }
- }
-
- public abstract class SQLCompiler<T> where T : Entity
- {
- protected static Dictionary<Type, SQLTableDefinition> _cache = new Dictionary<Type, SQLTableDefinition>();
-
- protected static SQLTableDefinition GetDefinition(Type type)
- {
- if (!_cache.TryGetValue(type, out SQLTableDefinition result))
- {
- lock (((ICollection)_cache).SyncRoot)
- {
- result = new SQLTableDefinition(type, null);
- int tablenumber = 0;
- result.Load(type, ref tablenumber);
- _cache[type] = result;
- }
- }
- return result;
- }
- protected IEnumerable<SQLTableDefinition> GetTables(SQLTableDefinition table, String[] columns)
- {
- return GetFields(table, columns).Select(x => x.Parent).Distinct();
- }
-
- protected SQLFieldDefinition[] GetFields(SQLTableDefinition table, String[] columns)
- {
- List<SQLFieldDefinition> result = new List<SQLFieldDefinition>();
- foreach (var field in table.Fields.Where(x =>columns.Contains(x.FullName)))
- result.Add(field);
- foreach (var join in table.Joins)
- {
- var subfields = GetFields(join, columns);
- result.AddRange(subfields);
- }
- return result.ToArray();
- }
-
- protected IEnumerable<SQLFieldDefinition> GetFields(SQLTableDefinition table, SQLFieldDefinition[] columns)
- {
- List<SQLFieldDefinition> result = new List<SQLFieldDefinition>();
- foreach (var field in table.Fields.Where(x =>columns.Contains(x)))
- result.Add(field);
- foreach (var join in table.Joins)
- {
- var subfields = GetFields(join, columns);
- result.AddRange(subfields);
- }
- return result;
- }
- protected abstract String DoCompile(String prefix, SQLTableDefinition table, SQLTableDefinition[] activetables,
- SQLFieldDefinition[] visiblecolumns);
-
- public string Compile(Filter<T>? filter = null, Columns<T>? columns = null, SortOrder<T>? sort = null)
- {
- var root = GetDefinition(typeof(T));
-
-
- var cols = (columns ?? Columns.All<T>()).ColumnNames().ToList();
- var visiblecolumns = GetFields(root, cols.ToArray());
-
- if (filter != null)
- cols.AddRange(filter.ColumnNames());
-
- if (sort != null)
- cols.AddRange(sort.ColumnNames());
-
- var activetables = GetFields(root, cols.ToArray()).Select(x => x.Parent).Distinct().ToArray();
- return DoCompile("", root, activetables, visiblecolumns);
-
- }
-
- }
- public class SQLiteCompiler<T> : SQLCompiler<T> where T : Entity
- {
- private void DoCompileJoins(SQLTableDefinition table, String prefix, SQLTableDefinition[] activetables, StringBuilder builder)
- {
- foreach (var join in table.Joins.Where(x=>activetables.Contains(x)))
- {
- builder.AppendLine($"{prefix}LEFT OUTER JOIN");
- builder.AppendLine($"{prefix}\t{join.TableName} T{join.TableNumber} ON T{table.TableNumber}.[{join.LinkName}.ID] = T{join.TableNumber}.[ID]");
- DoCompileJoins(join, prefix, activetables, builder);
- }
- }
-
- protected override String DoCompile(String prefix, SQLTableDefinition table, SQLTableDefinition[] activetables, SQLFieldDefinition[] visiblecolumns)
- {
- StringBuilder result = new StringBuilder();
-
- result.Append($"{prefix}SELECT\n{prefix}\t");
-
- var fields = visiblecolumns.Select(x=> $@"T{x.Parent.TableNumber}.[{x.ColumnName}] as [{(x.Parent != table ? x.FullName : x.ColumnName)}]").ToList();
- if (!visiblecolumns.Any(x=>String.Equals(x.ColumnName,"ID")))
- fields.Insert(0,$@"T{table.TableNumber}.[ID] as [ID]");
- result.AppendLine(String.Join($",\n{prefix}\t",fields));
-
- result.AppendLine($"{prefix}FROM");
- result.AppendLine($"{prefix}\t{table.TableName} T{table.TableNumber}");
- DoCompileJoins(table, prefix, activetables, result);
-
- return result.ToString();
- }
-
-
- }
-
- }
|