inabox/InABox.Core/DatabaseSchema/SQLTableDefinition.cs

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<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 ?? new Columns<T>().Default(ColumnType.All)).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();            
        }
        

        
    }
    
}