SQLTableDefinition.cs 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290
  1. using System;
  2. using System.Collections;
  3. using System.Collections.Generic;
  4. using System.Linq;
  5. using System.Reflection;
  6. using System.Text;
  7. namespace InABox.Core
  8. {
  9. /*
  10. *
  11. * Incomplete class - an attempt to simplify and separate the SQLiteProvider funtionality into a generalised
  12. * (reflection-driven) model that can be created once, and a streamlined processor that genrerates valid
  13. * SQL to throw at the database.
  14. *
  15. * Currently does not handle aggregates, formulas, conditions, filters or sorts, but it does create a nice
  16. * simple JOIN model based on Entity Properties, EntityLinks and EnclosedEntities
  17. *
  18. * If we can get this working, it will ease the way towards using a non-SQLite database (eg SQL Server, MySQL, etc)
  19. * but there is still some way to go!
  20. *
  21. */
  22. public class SQLFieldDefinition
  23. {
  24. public SQLTableDefinition Parent { get; private set; }
  25. public String ColumnName { get; private set; }
  26. public String FullName { get; private set; }
  27. public IFormula Formula { get; set; }
  28. public SQLFieldDefinition(SQLTableDefinition parent, String columnName, IFormula formula)
  29. {
  30. Parent = parent;
  31. ColumnName = columnName;
  32. FullName = ColumnName;
  33. var curparent = parent;
  34. while ((curparent != null) && (!String.IsNullOrWhiteSpace(curparent.LinkName)))
  35. {
  36. FullName = $"{curparent.LinkName}.{FullName}";
  37. curparent = curparent.Parent;
  38. }
  39. Formula = null;
  40. }
  41. public override string ToString() => FullName;
  42. }
  43. public class SQLTableDefinition
  44. {
  45. public Type Type { get; private set; }
  46. public int TableNumber { get; private set; }
  47. public String TableName { get; private set; }
  48. public String LinkName { get; set; }
  49. public SQLTableDefinition Parent { get; private set; }
  50. public List<SQLFieldDefinition> Fields { get; private set; }
  51. public List<SQLTableDefinition> Joins { get; private set; }
  52. public override string ToString() => $"{TableName}";
  53. public SQLTableDefinition(Type type, SQLTableDefinition parent)
  54. {
  55. Type = type;
  56. LinkName = "";
  57. Parent = parent;
  58. TableName = type.EntityName().Split('.').Last();
  59. Fields = new List<SQLFieldDefinition>();
  60. Joins = new List<SQLTableDefinition>();
  61. }
  62. private IEnumerable<PropertyInfo> PropertyList(Type type, Func<PropertyInfo,bool> predicate)
  63. {
  64. return CoreUtils.PropertyList(type,
  65. x => x.GetCustomAttribute<DoNotPersist>() == null
  66. && x.GetCustomAttribute<DoNotSerialize>() == null
  67. && x.PropertyType != typeof(UserProperties)
  68. && x.GetCustomAttribute<AggregateAttribute>() == null
  69. && x.GetCustomAttribute<ComplexFormulaAttribute>() == null
  70. //&& x.GetCustomAttribute<FormulaAttribute>() == null
  71. && x.GetCustomAttribute<ConditionAttribute>() == null
  72. ).Where(x => predicate(x));
  73. }
  74. private void LoadEnclosedEntity(PropertyInfo enclosedentity, String prefix, ref int tableNumber)
  75. {
  76. var props = PropertyList(enclosedentity.PropertyType, x => true);
  77. foreach (var prop in props)
  78. {
  79. if (prop.PropertyType.GetInterfaces().Contains(typeof(IEnclosedEntity)))
  80. LoadEnclosedEntity(
  81. prop,
  82. $"{prefix}.{prop.Name}",
  83. ref tableNumber
  84. );
  85. else if (prop.PropertyType.GetInterfaces().Contains(typeof(IEntityLink)))
  86. LoadEntityLink(
  87. prop,
  88. $"{prefix}.{prop.Name}",
  89. ref tableNumber
  90. );
  91. else
  92. Fields.Add(new SQLFieldDefinition(this, $"{prefix}.{prop.Name}", prop.GetCustomAttribute<FormulaAttribute>()));
  93. }
  94. }
  95. private void LoadEntityLink(PropertyInfo entitylink, String prefix, ref int tableNumber)
  96. {
  97. var linktype = entitylink.PropertyType.GetInterfaces()
  98. .FirstOrDefault(i => i.IsGenericType && i.GetGenericTypeDefinition() == typeof(IEntityLink<>))
  99. ?.GenericTypeArguments[0];
  100. if (linktype != null)
  101. {
  102. Fields.Add(new SQLFieldDefinition(this, $"{entitylink.Name}.ID", null));
  103. var props = PropertyList(entitylink.PropertyType, x => !String.Equals(x.Name, "ID"));
  104. if (props.Any())
  105. {
  106. SQLTableDefinition join = new SQLTableDefinition(linktype, this) { LinkName = prefix };
  107. tableNumber++;
  108. join.Load(entitylink.PropertyType, ref tableNumber);
  109. Joins.Add(join);
  110. }
  111. }
  112. }
  113. public void Load(Type type, ref int tableNumber)
  114. {
  115. TableNumber = tableNumber;
  116. var props = PropertyList(type, x => true);
  117. foreach (var prop in props)
  118. {
  119. if (prop.PropertyType.GetInterfaces().Contains(typeof(IEnclosedEntity)))
  120. LoadEnclosedEntity(
  121. prop,
  122. prop.Name,
  123. ref tableNumber
  124. );
  125. else if (prop.PropertyType.GetInterfaces().Contains(typeof(IEntityLink)))
  126. LoadEntityLink(
  127. prop,
  128. prop.Name,
  129. ref tableNumber
  130. );
  131. else
  132. LoadField(prop);
  133. }
  134. }
  135. private void LoadField(PropertyInfo prop)
  136. {
  137. IFormula formula = prop.GetCustomAttribute<FormulaAttribute>();
  138. if (formula != null)
  139. {
  140. // Because Formulas can themselves contain properties from EntityLinks
  141. // or Enclosed Entities, we need to deal with the requisite nesting of tables
  142. // although I'm not sure how this will work out in practice
  143. }
  144. Fields.Add(new SQLFieldDefinition(this, prop.Name, formula));
  145. }
  146. }
  147. public abstract class SQLCompiler<T> where T : Entity
  148. {
  149. protected static Dictionary<Type, SQLTableDefinition> _cache = new Dictionary<Type, SQLTableDefinition>();
  150. protected static SQLTableDefinition GetDefinition(Type type)
  151. {
  152. if (!_cache.TryGetValue(type, out SQLTableDefinition result))
  153. {
  154. lock (((ICollection)_cache).SyncRoot)
  155. {
  156. result = new SQLTableDefinition(type, null);
  157. int tablenumber = 0;
  158. result.Load(type, ref tablenumber);
  159. _cache[type] = result;
  160. }
  161. }
  162. return result;
  163. }
  164. protected IEnumerable<SQLTableDefinition> GetTables(SQLTableDefinition table, String[] columns)
  165. {
  166. return GetFields(table, columns).Select(x => x.Parent).Distinct();
  167. }
  168. protected SQLFieldDefinition[] GetFields(SQLTableDefinition table, String[] columns)
  169. {
  170. List<SQLFieldDefinition> result = new List<SQLFieldDefinition>();
  171. foreach (var field in table.Fields.Where(x =>columns.Contains(x.FullName)))
  172. result.Add(field);
  173. foreach (var join in table.Joins)
  174. {
  175. var subfields = GetFields(join, columns);
  176. result.AddRange(subfields);
  177. }
  178. return result.ToArray();
  179. }
  180. protected IEnumerable<SQLFieldDefinition> GetFields(SQLTableDefinition table, SQLFieldDefinition[] columns)
  181. {
  182. List<SQLFieldDefinition> result = new List<SQLFieldDefinition>();
  183. foreach (var field in table.Fields.Where(x =>columns.Contains(x)))
  184. result.Add(field);
  185. foreach (var join in table.Joins)
  186. {
  187. var subfields = GetFields(join, columns);
  188. result.AddRange(subfields);
  189. }
  190. return result;
  191. }
  192. protected abstract String DoCompile(String prefix, SQLTableDefinition table, SQLTableDefinition[] activetables,
  193. SQLFieldDefinition[] visiblecolumns);
  194. public string Compile(Filter<T>? filter = null, Columns<T>? columns = null, SortOrder<T>? sort = null)
  195. {
  196. var root = GetDefinition(typeof(T));
  197. var cols = (columns ?? Columns.All<T>()).ColumnNames().ToList();
  198. var visiblecolumns = GetFields(root, cols.ToArray());
  199. if (filter != null)
  200. cols.AddRange(filter.ColumnNames());
  201. if (sort != null)
  202. cols.AddRange(sort.ColumnNames());
  203. var activetables = GetFields(root, cols.ToArray()).Select(x => x.Parent).Distinct().ToArray();
  204. return DoCompile("", root, activetables, visiblecolumns);
  205. }
  206. }
  207. public class SQLiteCompiler<T> : SQLCompiler<T> where T : Entity
  208. {
  209. private void DoCompileJoins(SQLTableDefinition table, String prefix, SQLTableDefinition[] activetables, StringBuilder builder)
  210. {
  211. foreach (var join in table.Joins.Where(x=>activetables.Contains(x)))
  212. {
  213. builder.AppendLine($"{prefix}LEFT OUTER JOIN");
  214. builder.AppendLine($"{prefix}\t{join.TableName} T{join.TableNumber} ON T{table.TableNumber}.[{join.LinkName}.ID] = T{join.TableNumber}.[ID]");
  215. DoCompileJoins(join, prefix, activetables, builder);
  216. }
  217. }
  218. protected override String DoCompile(String prefix, SQLTableDefinition table, SQLTableDefinition[] activetables, SQLFieldDefinition[] visiblecolumns)
  219. {
  220. StringBuilder result = new StringBuilder();
  221. result.Append($"{prefix}SELECT\n{prefix}\t");
  222. var fields = visiblecolumns.Select(x=> $@"T{x.Parent.TableNumber}.[{x.ColumnName}] as [{(x.Parent != table ? x.FullName : x.ColumnName)}]").ToList();
  223. if (!visiblecolumns.Any(x=>String.Equals(x.ColumnName,"ID")))
  224. fields.Insert(0,$@"T{table.TableNumber}.[ID] as [ID]");
  225. result.AppendLine(String.Join($",\n{prefix}\t",fields));
  226. result.AppendLine($"{prefix}FROM");
  227. result.AppendLine($"{prefix}\t{table.TableName} T{table.TableNumber}");
  228. DoCompileJoins(table, prefix, activetables, result);
  229. return result.ToString();
  230. }
  231. }
  232. }