SQLiteProvider.cs 140 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797179817991800180118021803180418051806180718081809181018111812181318141815181618171818181918201821182218231824182518261827182818291830183118321833183418351836183718381839184018411842184318441845184618471848184918501851185218531854185518561857185818591860186118621863186418651866186718681869187018711872187318741875187618771878187918801881188218831884188518861887188818891890189118921893189418951896189718981899190019011902190319041905190619071908190919101911191219131914191519161917191819191920192119221923192419251926192719281929193019311932193319341935193619371938193919401941194219431944194519461947194819491950195119521953195419551956195719581959196019611962196319641965196619671968196919701971197219731974197519761977197819791980198119821983198419851986198719881989199019911992199319941995199619971998199920002001200220032004200520062007200820092010201120122013201420152016201720182019202020212022202320242025202620272028202920302031203220332034203520362037203820392040204120422043204420452046204720482049205020512052205320542055205620572058205920602061206220632064206520662067206820692070207120722073207420752076207720782079208020812082208320842085208620872088208920902091209220932094209520962097209820992100210121022103210421052106210721082109211021112112211321142115211621172118211921202121212221232124212521262127212821292130213121322133213421352136213721382139214021412142214321442145214621472148214921502151215221532154215521562157215821592160216121622163216421652166216721682169217021712172217321742175217621772178217921802181218221832184218521862187218821892190219121922193219421952196219721982199220022012202220322042205220622072208220922102211221222132214221522162217221822192220222122222223222422252226222722282229223022312232223322342235223622372238223922402241224222432244224522462247224822492250225122522253225422552256225722582259226022612262226322642265226622672268226922702271227222732274227522762277227822792280228122822283228422852286228722882289229022912292229322942295229622972298229923002301230223032304230523062307230823092310231123122313231423152316231723182319232023212322232323242325232623272328232923302331233223332334233523362337233823392340234123422343234423452346234723482349235023512352235323542355235623572358235923602361236223632364236523662367236823692370237123722373237423752376237723782379238023812382238323842385238623872388238923902391239223932394239523962397239823992400240124022403240424052406240724082409241024112412241324142415241624172418241924202421242224232424242524262427242824292430243124322433243424352436243724382439244024412442244324442445244624472448244924502451245224532454245524562457245824592460246124622463246424652466246724682469247024712472247324742475247624772478247924802481248224832484248524862487248824892490249124922493249424952496249724982499250025012502250325042505250625072508250925102511251225132514251525162517251825192520252125222523252425252526252725282529253025312532253325342535253625372538253925402541254225432544254525462547254825492550255125522553255425552556255725582559256025612562256325642565256625672568256925702571257225732574257525762577257825792580258125822583258425852586258725882589259025912592259325942595259625972598259926002601260226032604260526062607260826092610261126122613261426152616261726182619262026212622262326242625262626272628262926302631263226332634263526362637263826392640264126422643264426452646264726482649265026512652265326542655265626572658265926602661266226632664266526662667266826692670267126722673267426752676267726782679268026812682268326842685268626872688268926902691269226932694269526962697269826992700270127022703270427052706270727082709271027112712271327142715271627172718271927202721272227232724272527262727272827292730273127322733273427352736273727382739274027412742274327442745274627472748274927502751275227532754275527562757275827592760276127622763276427652766276727682769277027712772277327742775277627772778277927802781278227832784278527862787278827892790279127922793279427952796279727982799280028012802280328042805280628072808280928102811281228132814281528162817281828192820282128222823282428252826282728282829283028312832283328342835283628372838283928402841284228432844284528462847284828492850285128522853285428552856285728582859286028612862286328642865286628672868286928702871287228732874287528762877287828792880288128822883288428852886288728882889289028912892289328942895289628972898289929002901290229032904290529062907290829092910291129122913291429152916291729182919292029212922292329242925292629272928292929302931293229332934293529362937293829392940294129422943294429452946294729482949295029512952295329542955295629572958295929602961296229632964296529662967296829692970297129722973297429752976297729782979298029812982298329842985298629872988298929902991299229932994299529962997299829993000300130023003300430053006300730083009301030113012301330143015301630173018301930203021302230233024302530263027302830293030303130323033303430353036303730383039304030413042304330443045304630473048304930503051305230533054305530563057305830593060306130623063306430653066306730683069307030713072307330743075307630773078307930803081308230833084308530863087308830893090309130923093309430953096309730983099310031013102310331043105310631073108310931103111311231133114311531163117311831193120312131223123312431253126312731283129313031313132313331343135313631373138313931403141314231433144314531463147314831493150315131523153315431553156315731583159316031613162316331643165316631673168316931703171317231733174317531763177317831793180318131823183318431853186318731883189319031913192319331943195319631973198319932003201320232033204320532063207320832093210321132123213321432153216321732183219322032213222322332243225322632273228322932303231323232333234323532363237323832393240324132423243324432453246324732483249325032513252325332543255325632573258325932603261326232633264326532663267326832693270327132723273327432753276327732783279
  1. using System.Collections;
  2. using System.Data;
  3. using System.Data.SQLite;
  4. using System.Diagnostics.CodeAnalysis;
  5. using System.Linq.Expressions;
  6. using System.Reflection;
  7. using System.Runtime.Serialization.Formatters.Binary;
  8. using System.Text;
  9. using InABox.Core;
  10. using Microsoft.CodeAnalysis;
  11. using NPOI.POIFS.FileSystem;
  12. namespace InABox.Database.SQLite
  13. {
  14. internal abstract class SQLiteAccessor : IDisposable
  15. {
  16. public SQLiteConnection Connection { get; private set; }
  17. protected static int nConnections = 0;
  18. public void Dispose()
  19. {
  20. Connection?.Close();
  21. --nConnections;
  22. }
  23. public SQLiteCommand CreateCommand()
  24. {
  25. return new SQLiteCommand(Connection);
  26. }
  27. protected void Setup(string url)
  28. {
  29. var sb = new SQLiteConnectionStringBuilder();
  30. sb.DataSource = url;
  31. sb.Version = 3;
  32. sb.DateTimeFormat = SQLiteDateFormats.Ticks;
  33. sb.JournalMode = SQLiteJournalModeEnum.Wal;
  34. var conn = sb.ToString();
  35. Connection = new SQLiteConnection(conn);
  36. Connection.BusyTimeout = Convert.ToInt32(TimeSpan.FromMinutes(2).TotalMilliseconds);
  37. Connection.Open();
  38. Connection.SetLimitOption(SQLiteLimitOpsEnum.SQLITE_LIMIT_VARIABLE_NUMBER, 10000);
  39. ++nConnections;
  40. }
  41. }
  42. internal class SQLiteReadAccessor : SQLiteAccessor
  43. {
  44. public SQLiteReadAccessor(string url)
  45. {
  46. Setup(url);
  47. }
  48. }
  49. internal class SQLiteWriteAccessor : SQLiteAccessor
  50. {
  51. private static readonly object writelock = new();
  52. public SQLiteWriteAccessor(string url)
  53. {
  54. if (Monitor.TryEnter(writelock, new TimeSpan(0, 1, 0)))
  55. try
  56. {
  57. Setup(url);
  58. }
  59. catch(Exception e)
  60. {
  61. throw new Exception($"Exception while acquiring write lock! {nConnections} connections open.", e);
  62. }
  63. finally
  64. {
  65. Monitor.Exit(writelock);
  66. }
  67. if (Connection == null)
  68. throw new Exception($"Timeout while aqcuiring write lock! {nConnections} connections open.");
  69. }
  70. public SQLiteTransaction BeginTransaction()
  71. {
  72. return Connection.BeginTransaction();
  73. }
  74. }
  75. public class SQLiteProvider : IProvider
  76. {
  77. private static object writelock = new();
  78. private bool bForceRebuild;
  79. private bool RebuildTriggers = false;
  80. private SQLiteProvider()
  81. {
  82. }
  83. public SQLiteProvider(string filename)
  84. {
  85. var path = Path.GetDirectoryName(filename);
  86. if (!Directory.Exists(path))
  87. Directory.CreateDirectory(path);
  88. URL = filename;
  89. }
  90. public string URL { get; set; }
  91. public event LogEvent? OnLog;
  92. public Type[] Types { get; set; }
  93. public void Start()
  94. {
  95. var chkfile = Path.ChangeExtension(URL, "chk");
  96. bForceRebuild = File.Exists(chkfile);
  97. if (!File.Exists(URL))
  98. {
  99. SQLiteConnection.CreateFile(URL);
  100. }
  101. else if (bForceRebuild)
  102. {
  103. var i = 0;
  104. while (File.Exists(Path.ChangeExtension(URL, string.Format("{0:D3}", i))))
  105. i++;
  106. File.Copy(URL, Path.ChangeExtension(URL, string.Format("{0:D3}", i)));
  107. }
  108. using (var access = GetWriteAccess())
  109. {
  110. ExecuteSQL(access, "PRAGMA journal_mode=WAL;");
  111. }
  112. //using (var access = GetReadAccess())
  113. //{
  114. // using (var command = access.CreateCommand())
  115. // {
  116. // command.CommandText = "PRAGMA compile_options";
  117. // using (var reader = command.ExecuteReader())
  118. // {
  119. // while (reader.Read())
  120. // OnLog?.Invoke(LogType.Information,String.Format("{0}",reader.GetValue(0)));
  121. // }
  122. // }
  123. //}
  124. //ExecuteSQL("PRAGMA foreign_keys = on;");
  125. // Need to arrange the typelist to ensure that foreign keys
  126. // refer to tables that already exist
  127. var ordered = new List<Type>();
  128. foreach (var type in Types)
  129. LoadType(type, ordered);
  130. //Load up the metadata
  131. var metadata = LoadMetaData();
  132. var table = typeof(CustomProperty).EntityName().Split('.').Last();
  133. if (!metadata.ContainsKey(table))
  134. {
  135. OnLog?.Invoke(LogType.Information, "Creating Table: " + typeof(CustomProperty).EntityName().Split('.').Last());
  136. using (var access = GetWriteAccess())
  137. {
  138. CreateTable(access, typeof(CustomProperty), true, new CustomProperty[] { });
  139. }
  140. }
  141. else
  142. {
  143. using (var access = GetWriteAccess())
  144. {
  145. CheckFields(access, typeof(CustomProperty), metadata[table].Item1, new CustomProperty[] { });
  146. }
  147. }
  148. var customproperties = Load<CustomProperty>(); // new Filter<CustomProperty>(x => x.Class).IsEqualTo(type.EntityName()))
  149. metadata = LoadMetaData();
  150. foreach (var type in ordered)
  151. {
  152. if (type.GetCustomAttribute<AutoEntity>() == null)
  153. {
  154. table = type.EntityName().Split('.').Last();
  155. if (!metadata.ContainsKey(table))
  156. {
  157. OnLog?.Invoke(LogType.Information, "Creating Table: " + type.EntityName().Split('.').Last());
  158. using (var access = GetWriteAccess())
  159. {
  160. CreateTable(access, type, true, customproperties);
  161. }
  162. }
  163. }
  164. }
  165. metadata = LoadMetaData();
  166. foreach (var type in ordered)
  167. {
  168. if (type.GetCustomAttribute<AutoEntity>() == null)
  169. {
  170. table = type.EntityName().Split('.').Last();
  171. using (var access = GetWriteAccess())
  172. {
  173. CheckFields(access, type, metadata[table].Item1, customproperties);
  174. }
  175. }
  176. }
  177. metadata = LoadMetaData();
  178. foreach (var type in ordered)
  179. {
  180. if (type.GetCustomAttribute<AutoEntity>() == null)
  181. {
  182. LoadDeletions(type);
  183. table = type.EntityName().Split('.').Last();
  184. using (var access = GetWriteAccess())
  185. {
  186. CheckTriggers(access, type, metadata[table].Item2);
  187. }
  188. }
  189. }
  190. metadata = LoadMetaData();
  191. foreach (var type in ordered)
  192. {
  193. if (type.GetCustomAttribute<AutoEntity>() == null)
  194. {
  195. table = type.EntityName().Split('.').Last();
  196. using (var access = GetWriteAccess())
  197. {
  198. CheckIndexes(access, type, metadata[table].Item3);
  199. }
  200. }
  201. }
  202. metadata = LoadMetaData();
  203. foreach (var type in ordered)
  204. {
  205. if (type.GetCustomAttribute<AutoEntity>() != null)
  206. {
  207. table = type.EntityName().Split('.').Last();
  208. if (!metadata.ContainsKey(table))
  209. {
  210. OnLog?.Invoke(LogType.Information, "Creating Table: " + type.EntityName().Split('.').Last());
  211. using (var access = GetWriteAccess())
  212. {
  213. CreateTable(access, type, true, customproperties);
  214. }
  215. }
  216. else
  217. {
  218. using (var access = GetWriteAccess())
  219. {
  220. CheckFields(access, type, metadata[table].Item1, customproperties);
  221. }
  222. }
  223. }
  224. }
  225. if (bForceRebuild)
  226. {
  227. using (var access = GetWriteAccess())
  228. {
  229. ExecuteSQL(access, "VACUUM;");
  230. }
  231. File.Delete(chkfile);
  232. }
  233. }
  234. public bool IsRelational()
  235. {
  236. return true;
  237. }
  238. private void Connection_Trace(object sender, TraceEventArgs e)
  239. {
  240. var type = LogType.Query;
  241. string[] updates = { "INSERT ", "UPDATE ", "DELETE ", "ALTER ", "DROP ", "CREATE ", "VACUUM" };
  242. foreach (var update in updates)
  243. type = e.Statement.ToUpper().StartsWith(update) ? LogType.Update : type;
  244. OnLog?.Invoke(type, e.Statement + ";");
  245. }
  246. private SQLiteReadAccessor GetReadAccess()
  247. {
  248. var result = new SQLiteReadAccessor(URL);
  249. result.Connection.Trace += Connection_Trace;
  250. return result;
  251. }
  252. private SQLiteWriteAccessor GetWriteAccess()
  253. {
  254. var result = new SQLiteWriteAccessor(URL);
  255. result.Connection.Trace += Connection_Trace;
  256. return result;
  257. }
  258. private int ExecuteSQL(SQLiteWriteAccessor writer, string statement)
  259. {
  260. var result = 0;
  261. try
  262. {
  263. using (var command = writer.CreateCommand())
  264. {
  265. command.CommandText = statement;
  266. result = command.ExecuteNonQuery();
  267. }
  268. }
  269. catch (Exception)
  270. {
  271. throw;
  272. }
  273. return result;
  274. }
  275. #region Database Structure Management
  276. private class MetadataEntry
  277. {
  278. public String Name { get; set; }
  279. public String SQL { get; set; }
  280. }
  281. private class Metadata
  282. {
  283. public List<MetadataEntry> Tables { get; init; }
  284. public List<MetadataEntry> Views { get; init; }
  285. public List<MetadataEntry> Indexes { get; init; }
  286. public List<MetadataEntry> Triggers { get; init; }
  287. public Metadata() : base()
  288. {
  289. Tables = new List<MetadataEntry>();
  290. Views = new List<MetadataEntry>();
  291. Indexes = new List<MetadataEntry>();
  292. Triggers = new List<MetadataEntry>();
  293. }
  294. }
  295. private Dictionary<string, Tuple<Dictionary<string, string>, Dictionary<string, string>, Dictionary<string, string>>> LoadMetaData()
  296. {
  297. // Key is table name
  298. // Item1 = Field Name / Field Type map
  299. // Item2 = Trigger Name / Trigger SQL
  300. // Item3 = Index Name / Index SQL
  301. // Item4 = View Name /View SQL
  302. var metadata = new Dictionary<string, Tuple<Dictionary<string, string>, Dictionary<string, string>, Dictionary<string, string>>>();
  303. using (var access = GetReadAccess())
  304. {
  305. using (var command = access.CreateCommand())
  306. {
  307. command.CommandText = "SELECT tbl_name, sql, type FROM sqlite_master WHERE type='table' or type='view' ORDER BY type;";
  308. using (var reader = command.ExecuteReader())
  309. {
  310. if (reader.HasRows)
  311. while (reader.Read())
  312. {
  313. var tblinfo = new Tuple<Dictionary<string, string>, Dictionary<string, string>, Dictionary<string, string>>(
  314. new Dictionary<string, string>(), new Dictionary<string, string>(), new Dictionary<string, string>());
  315. var table = reader.GetString(0);
  316. var sql = reader.GetString(1);
  317. bool istable = String.Equals(reader.GetString(2),"table");
  318. if (istable)
  319. {
  320. sql = sql.Replace("\"", "")
  321. .Replace(string.Format("CREATE TABLE {0} (", table), "");
  322. sql = sql.Remove(sql.Length - 1).Trim();
  323. var fields = sql.Replace("\n\t", "").Replace("\t", " ").Replace("\"", "").Trim().Split(',');
  324. var primarykey = "";
  325. foreach (var fld in fields)
  326. {
  327. var field = fld.Trim().Replace("\t", " ").Replace("\"", "").Replace("[", "").Replace("]", "");
  328. //if (field.ToUpper().StartsWith("CONSTRAINT"))
  329. // tblinfo.Item2.Add(field);
  330. if (field.ToUpper().StartsWith("PRIMARY KEY"))
  331. {
  332. primarykey = field.Replace("PRIMARY KEY(", "").Replace(")", "");
  333. }
  334. else
  335. {
  336. var comps = field.Split(' ');
  337. tblinfo.Item1[comps[0]] = string.Format("{0}{1}", comps[1],
  338. field.Contains("PRIMARY KEY") ? " PRIMARY KEY" : "");
  339. }
  340. }
  341. if (!string.IsNullOrEmpty(primarykey))
  342. {
  343. var pkfld = tblinfo.Item1[primarykey];
  344. if (!pkfld.ToUpper().Contains("PRIMARY KEY"))
  345. tblinfo.Item1[primarykey] = string.Format("{0} PRIMARY KEY", pkfld.Trim());
  346. }
  347. }
  348. else
  349. {
  350. sql = sql.Replace("\"", "")
  351. .Replace("DISTINCT ", "");
  352. sql = sql.Split(new String[] { " AS SELECT " }, StringSplitOptions.TrimEntries).Last();
  353. sql = sql.Split(new String[] { " FROM " }, StringSplitOptions.TrimEntries).First();
  354. var fields = sql.Replace("\n\t", "").Replace("\t", " ").Replace("\"", "").Trim().Split(',');
  355. foreach (var fld in fields)
  356. {
  357. var field = fld.Trim()
  358. .Replace("\t", " ")
  359. .Replace("\"", "")
  360. .Replace("[", "").Replace("]", "");
  361. var parts = field.Split(" as ");
  362. if(parts.Length == 1)
  363. {
  364. tblinfo.Item1[field] = "";
  365. }
  366. else if(parts.Length == 2)
  367. {
  368. field = parts[1];
  369. if (parts[0] != "NULL")
  370. {
  371. tblinfo.Item1[field] = "";
  372. }
  373. }
  374. }
  375. }
  376. metadata[table] = tblinfo;
  377. }
  378. reader.Close();
  379. }
  380. // Now Load Up all the indexes
  381. command.CommandText = "select name, tbl_name, sql from sqlite_master where type='index' and sql is not null;";
  382. using (var reader = command.ExecuteReader())
  383. {
  384. if (reader.HasRows)
  385. while (reader.Read())
  386. {
  387. var name = reader.GetString(0);
  388. var table = reader.GetString(1);
  389. if (metadata.ContainsKey(table))
  390. {
  391. var tblInfo = metadata[table];
  392. var sql = reader.GetString(2);
  393. tblInfo.Item3[name] = sql;
  394. }
  395. }
  396. }
  397. // Now Load Up all the triggers
  398. command.CommandText = "select name, tbl_name, sql from sqlite_master where type='trigger';";
  399. using (var reader = command.ExecuteReader())
  400. {
  401. if (reader.HasRows)
  402. while (reader.Read())
  403. {
  404. var name = reader.GetString(0);
  405. var table = reader.GetString(1);
  406. if (metadata.ContainsKey(table))
  407. {
  408. var tblInfo = metadata[table];
  409. var sql = reader.GetString(2);
  410. tblInfo.Item2[name] = sql;
  411. }
  412. }
  413. }
  414. }
  415. }
  416. return metadata;
  417. }
  418. private static void LoadType(Type type, List<Type> into)
  419. {
  420. if (into.Contains(type))
  421. return;
  422. var props = type.GetProperties().Where(x => x.PropertyType.GetInterfaces().Contains(typeof(IEntityLink)));
  423. if (!props.Any())
  424. {
  425. into.Insert(0, type);
  426. }
  427. else
  428. {
  429. into.Add(type);
  430. foreach (var prop in props)
  431. {
  432. var subtype = prop.PropertyType.BaseType;
  433. while (subtype != null && !subtype.GetGenericArguments().Any())
  434. subtype = subtype.BaseType;
  435. subtype = subtype?.GetGenericArguments().FirstOrDefault();
  436. if (subtype != null && subtype != type)
  437. LoadType(subtype, into);
  438. }
  439. }
  440. }
  441. private string ColumnName(params PropertyInfo[] properties)
  442. {
  443. var bits = new List<string>();
  444. foreach (var property in properties)
  445. bits.Add(property.Name);
  446. var result = string.Join(".", bits);
  447. return result;
  448. }
  449. private string ColumnType(Type type)
  450. {
  451. if (type.IsOrdinal())
  452. return "INT";
  453. if (type.IsEnum)
  454. return "TEXT";
  455. if (type == typeof(DateTime))
  456. return "TEXT";
  457. if (type == typeof(TimeSpan))
  458. return "NUM";
  459. if (type == typeof(string[]))
  460. return "BLOB";
  461. if (type == typeof(byte[]))
  462. return "BLOB";
  463. if (type.IsFloatingPoint())
  464. return "NUM";
  465. if (type.GetInterfaces().Contains(typeof(IPackable)))
  466. return "BLOB";
  467. return "TEXT";
  468. }
  469. private void LoadFields(Type type, Dictionary<string, string> fields, List<PropertyInfo>? prefixes, CustomProperty[] customproperties)
  470. {
  471. if (prefixes == null)
  472. prefixes = new List<PropertyInfo>();
  473. AutoEntity? view = type.GetCustomAttribute<AutoEntity>();
  474. Type definition = view?.Generator != null
  475. ? view.Generator.Definition
  476. : type;
  477. var properties = CoreUtils.GetInheritedProperties(definition).Where(x =>
  478. x.GetCustomAttribute<DoNotPersist>() == null
  479. // We think this is wrong; but who knows? && x.GetCustomAttribute<DoNotSerialize>() == null
  480. && x.GetCustomAttributes().FirstOrDefault(a => a.GetType().Equals(typeof(AggregateAttribute))) == null
  481. && x.GetCustomAttributes().FirstOrDefault(a => a.GetType().Equals(typeof(FormulaAttribute))) == null
  482. && x.GetCustomAttributes().FirstOrDefault(a => a.GetType().Equals(typeof(ConditionAttribute))) == null
  483. && x.GetCustomAttributes().FirstOrDefault(a => a.GetType().Equals(typeof(ChildEntityAttribute))) == null
  484. && x.CanWrite
  485. && x.PropertyType != typeof(UserProperties)
  486. );
  487. foreach (var property in properties)
  488. if (property.PropertyType.GetInterfaces().Contains(typeof(IEnclosedEntity)))
  489. {
  490. LoadFields(property.PropertyType, fields, prefixes.Concat(new[] { property }).ToList(), customproperties);
  491. }
  492. else if (property.PropertyType.GetInterfaces().Contains(typeof(IEntityLink)))
  493. {
  494. var subprop = property.PropertyType.GetProperty("ID")!; // Not-null because IEntityLink has ID
  495. var subname = ColumnName(prefixes.Concat(new[] { property, subprop }).ToArray());
  496. var subtype = ColumnType(subprop.PropertyType);
  497. fields[subname] = subtype;
  498. }
  499. else
  500. {
  501. var colname = ColumnName(prefixes.Concat(new[] { property }).ToArray());
  502. var coltype = ColumnType(property.PropertyType);
  503. fields[colname] = string.Format("{0}{1}", coltype, colname.Equals("ID") ? " PRIMARY KEY" : "");
  504. }
  505. // Now add in the Custom Properties (if any exist)
  506. //CustomProperty[] customprops = Load<CustomProperty>(new Filter<CustomProperty>(x=>x.Class).IsEqualTo(type.EntityName()));
  507. foreach (var prop in customproperties.Where(x => x.Class.Equals(type.EntityName())))
  508. fields[prop.Name] = ColumnType(prop.PropertyType);
  509. }
  510. private List<string> LoadIndexes(Type type)
  511. {
  512. var result = new List<string>();
  513. var properties = type.GetProperties().Where(x =>
  514. x.GetCustomAttribute<DoNotSerialize>() == null && x.GetCustomAttribute<DoNotPersist>() == null &&
  515. x.GetCustomAttributes().FirstOrDefault(a => a.GetType().IsSubclassOf(typeof(AggregateAttribute))) == null
  516. && x.GetCustomAttributes().FirstOrDefault(a => a.GetType().Equals(typeof(ChildEntityAttribute))) == null
  517. && x.CanWrite);
  518. foreach (var property in properties)
  519. if (property.PropertyType.GetInterfaces().Contains(typeof(IEntityLink)))
  520. {
  521. var subprop = property.PropertyType.GetProperty("ID")!; // Not-null because IEntityLink has ID
  522. var subname = ColumnName(property, subprop);
  523. var tablename = type.EntityName().Split('.').Last();
  524. result.Add(string.Format("CREATE INDEX idx{0}{1} ON {0} ([{2}])", tablename, subname.Replace(".", ""), subname));
  525. }
  526. else
  527. {
  528. var index = property.GetCustomAttributes<SecondaryIndexAttribute>().FirstOrDefault();
  529. if (index != null)
  530. {
  531. var colname = ColumnName(property);
  532. var tablename = type.EntityName().Split('.').Last();
  533. result.Add(string.Format("CREATE INDEX idx{0}{1} ON {0} ([{2}])", tablename, colname.Replace(".", ""), colname));
  534. }
  535. }
  536. return result;
  537. }
  538. private List<string> LoadTriggers(Type type)
  539. {
  540. var result = new List<string>();
  541. // Get the EntityLink that is associated with this class
  542. var linkclass = CoreUtils.TypeList(
  543. new[] { type.Assembly },
  544. x => typeof(IEntityLink).GetTypeInfo().IsAssignableFrom(x) && x.GetInheritedGenericTypeArguments().FirstOrDefault() == type
  545. ).FirstOrDefault();
  546. // if The entitylink does not exist, we don't need to do anything
  547. if (linkclass == null)
  548. return result;
  549. var actions = new List<string>();
  550. var childtypes = Types.Where(x => /* (x != type) && */ x.IsSubclassOf(typeof(Entity)) && x.GetCustomAttribute<AutoEntity>() == null);
  551. foreach (var childtype in childtypes)
  552. {
  553. // Get all registererd types for this entitylink
  554. var fields = new List<string>();
  555. var bDelete = false;
  556. var tablename = childtype.EntityName().Split('.').Last();
  557. // Find any IEntityLink<> properties that refer back to this class
  558. var childprops = CoreUtils.PropertyList(childtype, x => x.PropertyType == linkclass);
  559. foreach (var childprop in childprops)
  560. {
  561. var fieldname = string.Format("[{0}.ID]", childprop.Name);
  562. var attr = childprop.GetCustomAttributes(typeof(EntityRelationshipAttribute), true).FirstOrDefault();
  563. if (attr != null && ((EntityRelationshipAttribute)attr).Action.Equals(DeleteAction.Cascade))
  564. {
  565. fields.Clear();
  566. bDelete = true;
  567. fields.Add(fieldname);
  568. break;
  569. }
  570. fields.Add(fieldname);
  571. //actions[childtype] = String.Format("UPDATE {1} SET [{0}.ID] = NULL WHERE [id] = old.[ID];", tablename)
  572. }
  573. if (fields.Any())
  574. {
  575. if (bDelete)
  576. actions.Add(string.Format("DELETE FROM {0} WHERE {1} = old.ID;", tablename, fields.First()));
  577. else
  578. foreach (var field in fields)
  579. actions.Add(string.Format("UPDATE {0} SET {1} = NULL WHERE {1} = old.ID;", tablename, field));
  580. }
  581. }
  582. if (actions.Any())
  583. result.Add(string.Format("CREATE TRIGGER {0}_BEFOREDELETE BEFORE DELETE ON {0} FOR EACH ROW BEGIN {1} END",
  584. type.EntityName().Split('.').Last(), string.Join(" ", actions)));
  585. return result;
  586. }
  587. public void ForceRecreateViews()
  588. {
  589. var ordered = new List<Type>();
  590. foreach (var type in Types)
  591. LoadType(type, ordered);
  592. var customproperties = Load<CustomProperty>();
  593. var metadata = LoadMetaData();
  594. foreach (var type in ordered)
  595. {
  596. var view = type.GetCustomAttribute<AutoEntity>();
  597. if (view?.Generator != null)
  598. {
  599. var table = type.EntityName().Split('.').Last();
  600. if (!metadata.ContainsKey(table))
  601. {
  602. OnLog?.Invoke(LogType.Information, "Creating Table: " + type.EntityName().Split('.').Last());
  603. using (var access = GetWriteAccess())
  604. {
  605. CreateTable(access, type, true, customproperties);
  606. }
  607. }
  608. else
  609. {
  610. var type_fields = new Dictionary<string, string>();
  611. LoadFields(view.Generator.Definition, type_fields, null, customproperties);
  612. using (var access = GetWriteAccess())
  613. {
  614. RebuildTable(access, type, metadata[table].Item1, type_fields, customproperties);
  615. }
  616. }
  617. }
  618. }
  619. }
  620. private Dictionary<string, object?> CheckDefaultColumns(IAutoEntityGenerator generator)
  621. {
  622. var viewfields = new Dictionary<string, string>();
  623. LoadFields(generator.Definition, viewfields, null, new CustomProperty[] { });
  624. Dictionary<String, object?> result = new Dictionary<string, object?>();
  625. if (!viewfields.ContainsKey("ID"))
  626. result["ID"] = null;
  627. if (!viewfields.ContainsKey("Created"))
  628. result["Created"] = null;
  629. if (!viewfields.ContainsKey("CreatedBy"))
  630. result["CreatedBy"] = null;
  631. if (!viewfields.ContainsKey("LastUpdate"))
  632. result["LastUpdate"] = null;
  633. if (!viewfields.ContainsKey("LastUpdateBy"))
  634. result["LastUpdateBy"] = null;
  635. return result;
  636. }
  637. private void CreateTable(SQLiteWriteAccessor access, Type type, bool includeconstraints, CustomProperty[] customproperties)
  638. {
  639. var tablename = type.EntityName().Split('.').Last();
  640. var ddl = new List<string>();
  641. var view = type.GetCustomAttribute<AutoEntity>();
  642. if (view != null)
  643. {
  644. using (var command = access.CreateCommand())
  645. {
  646. command.CommandText = $"select name from sqlite_master where type='view' and name='{tablename}';";
  647. using (var reader = command.ExecuteReader())
  648. {
  649. if (reader.HasRows)
  650. while (reader.Read())
  651. ExecuteSQL(access,string.Format("DROP VIEW {0}", reader.GetString(0)));
  652. }
  653. }
  654. ddl.Add("CREATE VIEW");
  655. ddl.Add(type.EntityName().Split('.').Last());
  656. ddl.Add("AS");
  657. if (view.Generator is IAutoEntityUnionGenerator union)
  658. {
  659. List<String> queries = new List<String>();
  660. foreach (var table in union.Tables)
  661. {
  662. var columns = Columns.Create(table.Entity);
  663. var constants = CheckDefaultColumns(union);
  664. var interfacefields = new Dictionary<string, string>();
  665. LoadFields(union.Definition, interfacefields, null, new CustomProperty[] { });
  666. var entityfields = new Dictionary<string, string>();
  667. LoadFields(table.Entity, entityfields, null, new CustomProperty[] { });
  668. foreach (var field in interfacefields.Keys)
  669. {
  670. if (entityfields.ContainsKey(field))
  671. columns.Add(field);
  672. else
  673. {
  674. var constant = table.Constants.FirstOrDefault(x => String.Equals(x.Mapping.Property, field));
  675. if (constant != null)
  676. constants[field] = constant.Value;
  677. else
  678. constants[field] = null;
  679. }
  680. }
  681. var query = PrepareSelectNonGeneric(table.Entity, new SQLiteCommand(), 'A',
  682. table.Filter, columns, null,
  683. null, constants, int.MaxValue, union.Distinct, false);
  684. queries.Add(query);
  685. }
  686. // var viewfields = new Dictionary<string, string>();
  687. //LoadFields(union.Definition, viewfields, null, new CustomProperty[] { });
  688. // var fields = viewfields.Keys.Select(x => String.Format("[{0}]", x)).ToList();
  689. // if (!fields.Contains("[ID]"))
  690. // fields.Add(String.Format("NULL as [ID]"));
  691. // if (!fields.Contains("[Created]"))
  692. // fields.Add(String.Format("NULL as [Created]"));
  693. // if (!fields.Contains("[CreatedBy]"))
  694. // fields.Add(String.Format("NULL as [CreatedBy]"));
  695. // if (!fields.Contains("[LastUpdate]"))
  696. // fields.Add(String.Format("NULL as [LastUpdate]"));
  697. // if (!fields.Contains("[LastUpdateBy]"))
  698. // fields.Add(String.Format("NULL as [LastUpdateBy]"));
  699. //
  700. // List<String> queries = new List<String>();
  701. // foreach (var entity in union.Entities)
  702. // queries.Add(String.Format("SELECT {0} {1} FROM {2}",
  703. // union.Distinct ? "DISTINCT" : "",
  704. // String.Join(", ", fields),
  705. // entity.EntityName().Split('.').Last())
  706. // );
  707. ddl.Add(String.Join(" UNION ", queries));
  708. }
  709. else if ( view.Generator is IAutoEntityCrossGenerator cross)
  710. {
  711. List<String> constants = new List<string>();
  712. foreach (var constant in CheckDefaultColumns(cross))
  713. constants.Add($"{EscapeValue(constant.Value)} as [{constant.Key}]");
  714. String query = String.Format(
  715. "SELECT {0} {1}.[{2}] as [{3}], {4}.[{5}] as [{6}], {7} FROM {1}, {4} WHERE {1}.[{8}] = {4}.[{9}]",
  716. cross.Distinct ? "DISTINCT" : "",
  717. cross.LeftEntity(),
  718. cross.LeftProperty(),
  719. cross.LeftMapping(),
  720. cross.RightEntity(),
  721. cross.RightProperty(),
  722. cross.RightMapping(),
  723. String.Join(", ", constants),
  724. cross.LeftLink(),
  725. cross.RightLink()
  726. );
  727. ddl.Add(query);
  728. }
  729. else if ( view.Generator is IAutoEntityCartesianGenerator cartesian)
  730. {
  731. List<String> fields = new List<string>();
  732. List<String> tables = new List<String>();
  733. List<String> filters = new List<String>();
  734. int iTable = 0;
  735. foreach (var table in cartesian.Tables)
  736. {
  737. var subQueryText = PrepareSelectNonGeneric(table.Type, new SQLiteCommand(), 'A',
  738. table.Filter, table.Columns, null,
  739. null, null, int.MaxValue, cartesian.Distinct, false);
  740. tables.Add($"({subQueryText}) T{iTable}");
  741. foreach (var mapping in table.Mappings)
  742. fields.Add($"T{iTable}.[{mapping.Column.Property}] as [{mapping.Mapping.Property}]");
  743. iTable++;
  744. }
  745. foreach (var constant in cartesian.Constants)
  746. fields.Add($"{EscapeValue(constant.Constant)} as [{constant.Mapping.Property}]");
  747. foreach (var constant in CheckDefaultColumns(cartesian))
  748. fields.Add($"{EscapeValue(constant.Value)} as [{constant.Key}]");
  749. StringBuilder sb = new StringBuilder();
  750. sb.Append("SELECT ");
  751. sb.Append(String.Join(", ", fields));
  752. sb.Append(" FROM ");
  753. sb.Append(String.Join(", ", tables));
  754. if (filters.Any())
  755. sb.Append($" WHERE {String.Join(" AND ", filters)}");
  756. ddl.Add(sb.ToString());
  757. }
  758. ddl.Add(";");
  759. var viewstatement = string.Join(" ", ddl);
  760. }
  761. else
  762. {
  763. ddl.Add("CREATE TABLE");
  764. ddl.Add(type.EntityName().Split('.').Last());
  765. ddl.Add("(");
  766. var fields = new Dictionary<string, string>();
  767. var constraints = new List<string>();
  768. var indexes = new List<string>();
  769. LoadFields(type, fields, null, customproperties);
  770. var defs = new List<string>();
  771. foreach (var key in fields.Keys)
  772. defs.Add(string.Format("[{0}] {1}", key, fields[key]));
  773. if (includeconstraints)
  774. defs.AddRange(constraints);
  775. ddl.Add(string.Join(", ", defs));
  776. ddl.Add(");");
  777. }
  778. var statement = string.Join(" ", ddl);
  779. try
  780. {
  781. ExecuteSQL(access, statement);
  782. }
  783. catch (Exception e)
  784. {
  785. OnLog?.Invoke(LogType.Error, "Unable to Create Table: " + e.Message);
  786. throw;
  787. }
  788. }
  789. private void RebuildTable(SQLiteWriteAccessor access, Type type, Dictionary<string, string> table_fields,
  790. Dictionary<string, string> type_fields,
  791. CustomProperty[] customproperties)
  792. {
  793. var table = type.EntityName().Split('.').Last();
  794. if (type.GetCustomAttribute<AutoEntity>() != null)
  795. {
  796. OnLog?.Invoke(LogType.Information, "Recreating View: " + table);
  797. try
  798. {
  799. String drop = "";
  800. using (var command = access.CreateCommand())
  801. {
  802. command.CommandText =
  803. $"select name from sqlite_master where type='trigger' and tbl_name='{table}' and sql is not null;";
  804. using (var reader = command.ExecuteReader())
  805. {
  806. if (reader.HasRows)
  807. while (reader.Read())
  808. ExecuteSQL(access,string.Format("DROP TRIGGER {0}", reader.GetString(0)));
  809. }
  810. command.CommandText = "select type from sqlite_master where name='" + table + "' and sql is not null;";
  811. using (var reader = command.ExecuteReader())
  812. {
  813. if (reader.HasRows)
  814. while (reader.Read())
  815. drop = reader.GetString(0).ToUpper();
  816. }
  817. }
  818. if (!String.IsNullOrWhiteSpace(drop))
  819. ExecuteSQL(access, string.Format("DROP {0} {1};", drop, table));
  820. CreateTable(access, type, true, customproperties);
  821. }
  822. catch (Exception e)
  823. {
  824. OnLog?.Invoke(LogType.Error, string.Format("RebuildTable({0}) [VIEW] failed: {1}\n{2}", table, e.Message, e.StackTrace));
  825. throw;
  826. }
  827. }
  828. else
  829. {
  830. OnLog?.Invoke(LogType.Information, "Rebuilding Table: " + table);
  831. try
  832. {
  833. ExecuteSQL(access, "PRAGMA foreign_keys = off;");
  834. // using (var command = access.CreateCommand())
  835. // {
  836. //
  837. // command.CommandText =
  838. // $"select name from sqlite_master where type='trigger' and tbl_name='{table}' and sql is not null;";
  839. // using (var reader = command.ExecuteReader())
  840. // {
  841. // if (reader.HasRows)
  842. // while (reader.Read())
  843. // ExecuteSQL(access,string.Format("DROP TRIGGER {0}", reader.GetString(0)));
  844. // }
  845. // command.CommandText = $"select name from sqlite_master where type='view' and name='{table}';";
  846. // using (var reader = command.ExecuteReader())
  847. // {
  848. // if (reader.HasRows)
  849. // while (reader.Read())
  850. // ExecuteSQL(access,string.Format("DROP VIEW {0}", reader.GetString(0)));
  851. // }
  852. // }
  853. using (var transaction = access.Connection.BeginTransaction())
  854. {
  855. var drops = new List<string>();
  856. using (var command = access.CreateCommand())
  857. {
  858. //command.CommandText = String.Format("select name from sqlite_master where type='trigger' and tbl_name='{0}' and sql is not null;", table);
  859. command.CommandText = "select name from sqlite_master where type='view' and sql is not null;";
  860. using (var reader = command.ExecuteReader())
  861. {
  862. if (reader.HasRows)
  863. while (reader.Read())
  864. drops.Add(string.Format("DROP VIEW {0}", reader.GetString(0)));
  865. }
  866. //command.CommandText = String.Format("select name from sqlite_master where type='trigger' and tbl_name='{0}' and sql is not null;", table);
  867. command.CommandText = "select name from sqlite_master where type='trigger' and sql is not null;";
  868. using (var reader = command.ExecuteReader())
  869. {
  870. if (reader.HasRows)
  871. while (reader.Read())
  872. drops.Add(string.Format("DROP TRIGGER {0}", reader.GetString(0)));
  873. }
  874. command.CommandText = string.Format(
  875. "select name from sqlite_master where type='index' and tbl_name='{0}' and sql is not null;",
  876. table);
  877. using (var reader = command.ExecuteReader())
  878. {
  879. if (reader.HasRows)
  880. while (reader.Read())
  881. drops.Add(string.Format("DROP INDEX {0}", reader.GetString(0)));
  882. }
  883. }
  884. foreach (var drop in drops)
  885. ExecuteSQL(access, drop);
  886. bool existingtable = false;
  887. using (var command = access.CreateCommand())
  888. {
  889. command.CommandText =
  890. $"select name from sqlite_master where type='table' and tbl_name='{table}' and sql is not null;";
  891. using (var reader = command.ExecuteReader())
  892. {
  893. if (reader.HasRows)
  894. existingtable = true;
  895. }
  896. }
  897. if (existingtable)
  898. ExecuteSQL(access, string.Format("ALTER TABLE {0} RENAME TO _{0}_old;", table));
  899. CreateTable(access, type, true, customproperties);
  900. var fields = new List<string>();
  901. foreach (var field in type_fields.Keys)
  902. if (table_fields.ContainsKey(field))
  903. fields.Add("[" + field + "]");
  904. if (existingtable)
  905. {
  906. ExecuteSQL(access,
  907. string.Format("INSERT INTO {0} ({1}) SELECT {1} FROM _{0}_old;", table,
  908. string.Join(", ", fields)));
  909. ExecuteSQL(access, string.Format("DROP TABLE _{0}_old;", table));
  910. }
  911. transaction.Commit();
  912. }
  913. ExecuteSQL(access, "PRAGMA foreign_keys = on;");
  914. }
  915. catch (Exception e)
  916. {
  917. OnLog?.Invoke(LogType.Error, string.Format("RebuildTable({0}) [TABLE] failed: {1}\n{2}", table, e.Message, e.StackTrace));
  918. throw;
  919. }
  920. }
  921. }
  922. private void CheckFields(SQLiteWriteAccessor access, Type type, Dictionary<string, string> current_fields, CustomProperty[] customproperties)
  923. {
  924. var type_fields = new Dictionary<string, string>();
  925. var view = type.GetCustomAttribute<AutoEntity>();
  926. if ((view != null) && (view.Generator != null))
  927. LoadFields(view.Generator.Definition, type_fields, null, customproperties);
  928. else
  929. LoadFields(type, type_fields, null, customproperties);
  930. var bRebuild = false;
  931. foreach (var field in type_fields.Keys)
  932. {
  933. try
  934. {
  935. Type tType;
  936. var cprop = customproperties.FirstOrDefault(x => string.Equals(x.Name, field));
  937. if (cprop != null)
  938. tType = cprop.PropertyType;
  939. else
  940. tType = CoreUtils.GetProperty(type, field).PropertyType;
  941. if ((view == null) && (tType == typeof(TimeSpan)) && current_fields.ContainsKey(field) && !current_fields[field].Equals(type_fields[field]))
  942. {
  943. var sql = string.Format(
  944. "update {0} set [{1}] = cast(substr([{1}],1,2) as double) + cast(substr([{1}],4,2) as double)/60 + cast(substr([{1}],7,2) as double)/3600 where [{1}] like \"%:%:%\"",
  945. type.Name, field);
  946. ExecuteSQL(access, sql);
  947. }
  948. }
  949. catch (Exception e)
  950. {
  951. Logger.Send(LogType.Error, "", string.Format("*** Unknown Error: {0}\n{1}", e.Message, e.StackTrace));
  952. }
  953. if (!current_fields.ContainsKey(field) || (!String.IsNullOrWhiteSpace(current_fields[field])) && (current_fields[field] != type_fields[field]))
  954. bRebuild = true;
  955. }
  956. foreach (var field in current_fields.Keys)
  957. if (!type_fields.ContainsKey(field))
  958. bRebuild = true;
  959. if (bForceRebuild || bRebuild)
  960. RebuildTable(access, type, current_fields, type_fields, customproperties);
  961. }
  962. private void CheckIndexes(SQLiteWriteAccessor access, Type type, Dictionary<string, string> db_indexes)
  963. {
  964. var type_indexes = LoadIndexes(type);
  965. foreach (var index in db_indexes.Keys)
  966. if (!type_indexes.Contains(db_indexes[index]))
  967. ExecuteSQL(access, string.Format("DROP INDEX {0}", index));
  968. foreach (var index in type_indexes)
  969. if (!db_indexes.ContainsValue(index))
  970. ExecuteSQL(access, index);
  971. }
  972. private void CheckTriggers(SQLiteWriteAccessor access, Type type, Dictionary<string, string> db_triggers)
  973. {
  974. /*
  975. #if PURGE
  976. foreach (var trigger in db_triggers.Keys)
  977. ExecuteSQL(access, string.Format("DROP TRIGGER {0}", trigger));
  978. #else*/
  979. var type_triggers = LoadTriggers(type);
  980. foreach (var trigger in db_triggers.Keys)
  981. if (!type_triggers.Contains(db_triggers[trigger]))
  982. ExecuteSQL(access, string.Format("DROP TRIGGER {0}", trigger));
  983. foreach (var trigger in type_triggers)
  984. if (!db_triggers.ContainsValue(trigger))
  985. ExecuteSQL(access, trigger);
  986. //#endif
  987. }
  988. // private void CheckViews(SQLiteWriteAccessor access, Type type, Dictionary<String, String> db_views)
  989. // {
  990. // var type_view = LoadView(type);
  991. //
  992. //
  993. // if (!type_triggers.Contains(db_triggers[viewname]))
  994. // ExecuteSQL(access, string.Format("DROP TRIGGER {0}", trigger));
  995. //
  996. // if (!db_views.ContainsValue(type_view))
  997. // ExecuteSQL(access, type_view);
  998. // }
  999. #endregion
  1000. #region CRUD Operations
  1001. private static bool IsNull([NotNullWhen(false)] object? o)
  1002. {
  1003. return o == null || o is DBNull;
  1004. }
  1005. public object? Decode(object o, Type type)
  1006. {
  1007. if (IsNull(o))
  1008. return type == typeof(string) ? "" : type.GetDefault();
  1009. if (type == typeof(string[]))
  1010. {
  1011. if (!IsNull(o))
  1012. {
  1013. if (o is byte[] array)
  1014. using (var ms = new MemoryStream(array))
  1015. {
  1016. var deser = new BinaryFormatter().Deserialize(ms);
  1017. return deser as string[];
  1018. }
  1019. return Array.Empty<string>();
  1020. }
  1021. return Array.Empty<string>();
  1022. }
  1023. if (type.GetInterfaces().Contains(typeof(IPackable)))
  1024. {
  1025. var packable = (Activator.CreateInstance(type) as IPackable)!; // Not-null because of above check
  1026. if (!IsNull(o))
  1027. {
  1028. if (o is byte[] array)
  1029. {
  1030. using (var ms = new MemoryStream(array))
  1031. packable.Unpack(new BinaryReader(ms));
  1032. }
  1033. }
  1034. return packable;
  1035. }
  1036. if (type == typeof(DateTime))
  1037. return IsNull(o) || string.IsNullOrEmpty(o.ToString()) ? DateTime.MinValue : DateTime.Parse(o.ToString() ?? "");
  1038. if (type == typeof(TimeSpan))
  1039. {
  1040. if (IsNull(o))
  1041. return TimeSpan.MinValue;
  1042. var oStr = o.ToString() ?? "";
  1043. if (double.TryParse(oStr, out var hrs))
  1044. return TimeSpan.FromHours(hrs);
  1045. if (oStr.Contains(':'))
  1046. return TimeSpan.Parse(oStr);
  1047. }
  1048. if (type == typeof(Guid))
  1049. return IsNull(o) ? Guid.Empty : Guid.Parse(o.ToString() ?? "");
  1050. if (type == typeof(bool))
  1051. {
  1052. var oStr = o.ToString() ?? "";
  1053. return !IsNull(o) && (oStr.Equals("1") || oStr.ToUpper().Equals("TRUE"));
  1054. }
  1055. if (type.IsEnum)
  1056. return Enum.Parse(type, o.ToString() ?? "");
  1057. if (IsNull(o))
  1058. return null;
  1059. if (type == typeof(long))
  1060. return o;
  1061. var result = CoreUtils.ChangeType(o, type);
  1062. return result;
  1063. }
  1064. public static object Encode(object? o, Type? type)
  1065. {
  1066. if (IsNull(o) || type is null)
  1067. return DBNull.Value;
  1068. if (type == typeof(DateTime) && o.GetType() == typeof(string))
  1069. o = DateTime.Parse(o.ToString() ?? "");
  1070. if (type == typeof(double) && o.GetType() == typeof(string))
  1071. o = double.Parse(o.ToString() ?? "");
  1072. if (type == typeof(float) && o.GetType() == typeof(string))
  1073. o = float.Parse(o.ToString() ?? "");
  1074. if (type == typeof(long) && o.GetType() == typeof(string))
  1075. o = long.Parse(o.ToString() ?? "");
  1076. if (type.IsEnum && o.GetType() == typeof(string))
  1077. o = Enum.Parse(type, o.ToString() ?? "");
  1078. if (type == typeof(TimeSpan) && o.GetType() == typeof(string))
  1079. {
  1080. if (o.ToString()?.Contains(':') == true)
  1081. {
  1082. if (TimeSpan.TryParse(o.ToString(), out var time))
  1083. o = time;
  1084. }
  1085. else if (double.TryParse(o.ToString(), out var hrs))
  1086. {
  1087. o = TimeSpan.FromHours(hrs);
  1088. }
  1089. }
  1090. if (type == typeof(bool))
  1091. {
  1092. if (o.GetType() == typeof(string))
  1093. o = bool.Parse(o.ToString() ?? "");
  1094. if (o.Equals(false))
  1095. return DBNull.Value;
  1096. return o;
  1097. }
  1098. if (type == typeof(string) && string.IsNullOrEmpty(o.ToString()))
  1099. return DBNull.Value;
  1100. if (type == typeof(Guid) && o.GetType() == typeof(string))
  1101. o = Guid.Parse(o.ToString() ?? "");
  1102. if (o is string[])
  1103. using (var ms = new MemoryStream())
  1104. {
  1105. new BinaryFormatter().Serialize(ms, o);
  1106. return ms.GetBuffer();
  1107. }
  1108. if (o is IPackable pack)
  1109. {
  1110. using var ms = new MemoryStream();
  1111. using var writer = new BinaryWriter(ms);
  1112. pack.Pack(writer);
  1113. return ms.ToArray();
  1114. }
  1115. if (o.GetType() == typeof(double) && o.Equals(default(double)))
  1116. return DBNull.Value;
  1117. if (o.GetType() == typeof(float) && o.Equals(default(float)))
  1118. return DBNull.Value;
  1119. if (o.GetType() == typeof(int) && o.Equals(default(int)))
  1120. return DBNull.Value;
  1121. if (o.GetType() == typeof(long) && o.Equals(default(long)))
  1122. return DBNull.Value;
  1123. if (o.GetType() == typeof(DateTime))
  1124. {
  1125. if ((DateTime)o == DateTime.MinValue)
  1126. return DBNull.Value;
  1127. return string.Format("{0:yyyy-MM-dd HH:mm:ss.FFFFFFF}", o);
  1128. }
  1129. if (o.GetType() == typeof(TimeSpan))
  1130. {
  1131. if (((TimeSpan)o).Ticks == 0L)
  1132. return DBNull.Value;
  1133. return ((TimeSpan)o).TotalHours;
  1134. }
  1135. if (o.GetType() == typeof(Guid))
  1136. {
  1137. if (o.Equals(Guid.Empty))
  1138. return DBNull.Value;
  1139. return o.ToString() ?? "";
  1140. }
  1141. if (type == typeof(double) && o.GetType() == typeof(string))
  1142. if (double.TryParse((string)o, out var value))
  1143. o = value;
  1144. if (o.GetType().IsEnum)
  1145. return o.ToString() ?? "";
  1146. return o;
  1147. }
  1148. private static readonly Dictionary<Operator, string> operators = new()
  1149. {
  1150. { Operator.IsEqualTo, "{0} = {1}" },
  1151. { Operator.IsNotEqualTo, "{0} != {1}" },
  1152. { Operator.IsGreaterThan, "{0} > {1}" },
  1153. { Operator.IsGreaterThanOrEqualTo, "{0} >= {1}" },
  1154. { Operator.IsLessThan, "{0} < {1}" },
  1155. { Operator.IsLessThanOrEqualTo, "{0} <= {1}" },
  1156. { Operator.BeginsWith, "{0} LIKE {1} || '%'" },
  1157. { Operator.Contains, "{0} LIKE '%' || {1} || '%'" },
  1158. { Operator.EndsWith, "{0} LIKE '%' || {1}" },
  1159. { Operator.InList, "{0} IN ({1})" },
  1160. { Operator.NotInList, "{0} NOT IN ({1})" },
  1161. { Operator.InQuery, "{0} IN ({1})" },
  1162. { Operator.NotInQuery, "{0} NOT IN ({1})" }
  1163. };
  1164. private static string EscapeValue(object? value)
  1165. {
  1166. if (IsNull(value))
  1167. return "NULL";
  1168. if ((value is string) || (value is Enum) || (value is Guid))
  1169. return string.Format("\'" + "{0}" + "\'", value.ToString()?.Replace("\'", "\'\'"));
  1170. if (value is string[])
  1171. return string.Format("hex({0})", BitConverter.ToString(Encoding.ASCII.GetBytes(value.ToString() ?? "")).Replace("-", ""));
  1172. if (value is IColumn col)
  1173. return $"[{col.Property}]";
  1174. return value.ToString() ?? "";
  1175. }
  1176. private static string GetFilterConstant(FilterConstant constant)
  1177. {
  1178. return constant switch
  1179. {
  1180. FilterConstant.Now => "datetime()",
  1181. FilterConstant.Today => "datetime(date())",
  1182. FilterConstant.Zero => "0",
  1183. // These figures are based around today
  1184. FilterConstant.OneWeekAgo => "datetime(date(),'-7 days')",
  1185. FilterConstant.OneWeekAhead => "datetime(date(),'+7 days')",
  1186. FilterConstant.OneMonthAgo => "datetime(date(),'-1 month')",
  1187. FilterConstant.OneMonthAhead => "datetime(date(),'+1 month')",
  1188. FilterConstant.ThreeMonthsAgo => "datetime(date(),'-3 months')",
  1189. FilterConstant.ThreeMonthsAhead => "datetime(date(),'+3 months')",
  1190. FilterConstant.SixMonthsAgo => "datetime(date(),'-6 months')",
  1191. FilterConstant.SixMonthsAhead => "datetime(date(),'+6 months')",
  1192. FilterConstant.OneYearAgo => "datetime(date(),'-1 year')",
  1193. FilterConstant.OneYearAhead => "datetime(date(),'+1 year')",
  1194. // Relative Week values run from Monday thru Sunday
  1195. FilterConstant.StartOfLastWeek => "datetime(date(), 'weekday 0', '-13 days')",
  1196. FilterConstant.EndOfLastWeek => "datetime(date(), 'weekday 0', '-6 days', '-000.0001 seconds')",
  1197. FilterConstant.StartOfThisWeek => "datetime(date(), 'weekday 0', '-6 days')",
  1198. FilterConstant.EndOfThisWeek => "datetime(date(), 'weekday 0', '+1 day', '-000.0001 seconds')",
  1199. FilterConstant.StartOfNextWeek => "datetime(date(), 'weekday 0', '+1 days')",
  1200. FilterConstant.EndOfNextWeek => "datetime(date(), 'weekday 0', '+8 days', '-000.0001 seconds')",
  1201. FilterConstant.StartOfLastMonth => "datetime(date(), 'start of month', '-1 month')",
  1202. FilterConstant.EndOfLastMonth => "datetime(date(), 'start of month', '-000.0001 seconds')",
  1203. FilterConstant.StartOfThisMonth => "datetime(date(), 'start of month')",
  1204. FilterConstant.EndOfThisMonth => "datetime(date(), 'start of month', '+1 month', '-000.0001 seconds')",
  1205. FilterConstant.StartOfNextMonth => "datetime(date(), 'start of month', '+1 month')",
  1206. FilterConstant.EndOfNextMonth => "datetime(date(), 'start of month', '+2 months', '-000.0001 seconds')",
  1207. FilterConstant.StartOfLastCalendarYear => "datetime(date(), 'start of year', '-1 year')",
  1208. FilterConstant.EndOfLastCalendarYear => "datetime(date(), 'start of year', '-000.0001 seconds')",
  1209. FilterConstant.StartOfThisCalendarYear => "datetime(date(), 'start of year')",
  1210. FilterConstant.EndOfThisCalendarYear => "datetime(date(), 'start of year', '+1 year', '-000.0001 seconds')",
  1211. FilterConstant.StartOfNextCalendarYear => "datetime(date(), 'start of year', '+1 year')",
  1212. FilterConstant.EndOfNextCalendarYear => "datetime(date(), 'start of year', '+2 years', '-000.0001 seconds')",
  1213. FilterConstant.StartOfLastFinancialYear => "datetime(date(), '-18 months', 'start of year', '+6 months')",
  1214. FilterConstant.EndOfLastFinancialYear => "datetime(date(), '-18 months', 'start of year', '-18 months', '-000.0001 seconds')",
  1215. FilterConstant.StartOfThisFinancialYear => "datetime(date(), '-6 months', 'start of year', '+6 months')",
  1216. FilterConstant.EndOfThisFinancialYear => "datetime(date(), '-6 months', 'start of year', '+18 months', '-000.0001 seconds')",
  1217. FilterConstant.StartOfNextFinancialYear => "datetime(date(), '+6 months', 'start of year', '+6 months')",
  1218. FilterConstant.EndOfNextFinancialYear => "datetime(date(), '+6 months', 'start of year', '+18 months', '-000.0001 seconds')",
  1219. _ => throw new Exception($"FilterConstant.{constant} is not implemented!"),
  1220. };
  1221. }
  1222. private string GetFilterClauseNonGeneric(Type T, SQLiteCommand command, char prefix, IFilter? filter, List<Tuple<string, string, string, string>> tables,
  1223. Dictionary<string, string> fieldmap, List<string> columns, bool useparams)
  1224. {
  1225. if (filter == null || filter.Expression == null)
  1226. return "";
  1227. var result = "";
  1228. if (filter.Operator == Operator.All)
  1229. {
  1230. result = filter.IsNot ? "1 = 0" : "1 = 1";
  1231. }
  1232. else if (filter.Operator == Operator.None)
  1233. {
  1234. result = filter.IsNot ? "1 = 1" : "1 = 0";
  1235. }
  1236. else
  1237. {
  1238. string prop;
  1239. if (CoreUtils.TryFindMemberExpression(filter.Expression, out var mexp))
  1240. {
  1241. prop = CoreUtils.GetFullPropertyName(mexp, ".");
  1242. }
  1243. else
  1244. {
  1245. prop = filter.Expression.ToString();
  1246. if (prop.Contains("=>"))
  1247. prop = string.Join(".", prop.Split('.').Skip(1));
  1248. mexp = CoreUtils.GetMemberExpression(T, prop);
  1249. }
  1250. LoadFieldsandTables(command, T, prefix, fieldmap, tables, columns, prop, useparams);
  1251. if (fieldmap.ContainsKey(prop))
  1252. prop = fieldmap[prop];
  1253. if(filter.Value is CustomFilterValue)
  1254. {
  1255. throw new Exception("Custom Filter Value not able to be processed server-side!");
  1256. }
  1257. if (filter.Operator == Operator.InList || filter.Operator == Operator.NotInList)
  1258. {
  1259. // if, and only if the list contains Guids, we can safely bypass the
  1260. // 1000-parameter limit by using building the string ourselves
  1261. if (filter.Value is Guid[] list)
  1262. {
  1263. result = string.Format("(" + operators[filter.Operator] + ")", prop, string.Format("\"{0}\"", string.Join("\",\"", list)));
  1264. }
  1265. else if (filter.Value is IEnumerable enumerable)
  1266. {
  1267. var paramlist = new List<object>();
  1268. foreach (var item in enumerable)
  1269. {
  1270. var sParam = string.Format("@p{0}", command.Parameters.Count);
  1271. command.Parameters.AddWithValue(sParam, Encode(item, mexp.Type));
  1272. paramlist.Add(sParam);
  1273. }
  1274. result = string.Format("(" + operators[filter.Operator] + ")", prop, string.Join(",", paramlist));
  1275. }
  1276. }
  1277. else if (filter.Operator == Operator.InQuery || filter.Operator == Operator.NotInQuery)
  1278. {
  1279. if(filter.Value is ISubQuery subQuery)
  1280. {
  1281. var subEntityType = subQuery.GetQueryType();
  1282. var subColumns = Columns.Create(subEntityType);
  1283. var subColumn = subQuery.GetColumn();
  1284. subColumns.Add(subColumn);
  1285. var subQueryText = PrepareSelectNonGeneric(subEntityType, command, 'A',
  1286. subQuery.GetFilter(), subColumns, null,
  1287. null, null, int.MaxValue, false, useparams);
  1288. result = string.Format("(" + operators[filter.Operator] + ")", prop, subQueryText);
  1289. }
  1290. }
  1291. else
  1292. {
  1293. if (filter.Value is FilterConstant constant)
  1294. result = string.Format("(" + operators[filter.Operator] + ")", prop, GetFilterConstant(constant));
  1295. else
  1296. {
  1297. var value = Encode(filter.Value, mexp.Type);
  1298. if (IsNull(value) && ((filter.Operator == Operator.IsEqualTo) || (filter.Operator == Operator.IsNotEqualTo)))
  1299. {
  1300. result = string.Format("({0} {1} NULL)", prop,
  1301. filter.Operator == Operator.IsEqualTo
  1302. ? "IS"
  1303. : "IS NOT");
  1304. }
  1305. else
  1306. {
  1307. if (useparams)
  1308. {
  1309. var sParam = string.Format("@p{0}", command.Parameters.Count);
  1310. if (filter.Expression.Type == typeof(string[]))
  1311. {
  1312. var bytes = Encoding.ASCII.GetBytes(value.ToString() ?? "");
  1313. value = BitConverter.ToString(bytes).Replace("-", "");
  1314. result = string.Format("(" + operators[filter.Operator] + ")", string.Format("hex({0})", prop), sParam);
  1315. }
  1316. else
  1317. {
  1318. result = string.Format("(" + operators[filter.Operator] + ")", prop, sParam);
  1319. }
  1320. command.Parameters.AddWithValue(sParam, value);
  1321. }
  1322. else
  1323. result = string.Format("(" + operators[filter.Operator] + ")", prop, EscapeValue(filter.Value));
  1324. }
  1325. }
  1326. }
  1327. if (filter.IsNot)
  1328. {
  1329. result = $"(NOT {result})";
  1330. }
  1331. }
  1332. var bChanged = false;
  1333. if (filter.Ands != null && filter.Ands.Any())
  1334. {
  1335. foreach (var and in filter.Ands)
  1336. {
  1337. var AndResult = GetFilterClauseNonGeneric(T, command, prefix, and, tables, fieldmap, columns, useparams);
  1338. if (!string.IsNullOrEmpty(AndResult))
  1339. {
  1340. result = string.Format("{0} and {1}", result, AndResult);
  1341. bChanged = true;
  1342. }
  1343. }
  1344. if (bChanged)
  1345. result = string.Format("({0})", result);
  1346. }
  1347. bChanged = false;
  1348. if (filter.Ors != null && filter.Ors.Any())
  1349. {
  1350. foreach (var or in filter.Ors)
  1351. {
  1352. var OrResult = GetFilterClauseNonGeneric(T, command, prefix, or, tables, fieldmap, columns, useparams);
  1353. if (!string.IsNullOrEmpty(OrResult))
  1354. {
  1355. result = string.Format("{0} or {1}", result, OrResult);
  1356. bChanged = true;
  1357. }
  1358. }
  1359. if (bChanged)
  1360. result = string.Format("({0})", result);
  1361. }
  1362. return result;
  1363. }
  1364. private string GetFilterClause<T>(SQLiteCommand command, char prefix, Filter<T>? filter, List<Tuple<string, string, string, string>> tables,
  1365. Dictionary<string, string> fieldmap, List<string> columns, bool useparams) where T : Entity
  1366. => GetFilterClauseNonGeneric(typeof(T), command, prefix, filter, tables, fieldmap, columns, useparams);
  1367. private string GetSortClauseNonGeneric(Type T,
  1368. SQLiteCommand command, ISortOrder? sort, char prefix, List<Tuple<string, string, string, string>> tables,
  1369. Dictionary<string, string> fieldmap, List<string> columns, bool useparams)
  1370. {
  1371. if (sort == null)
  1372. return "";
  1373. var result = "";
  1374. if (sort.Expression != null)
  1375. {
  1376. if (CoreUtils.TryFindMemberExpression(sort.Expression, out var mexp))
  1377. result = CoreUtils.GetFullPropertyName(mexp, ".");
  1378. else
  1379. result = sort.Expression.ToString();
  1380. var prop = CoreUtils.GetProperty(T, result);
  1381. if (prop.GetCustomAttribute<DoNotSerialize>() == null && prop.GetCustomAttribute<DoNotPersist>() == null && prop.CanWrite)
  1382. {
  1383. LoadFieldsandTables(command, T, prefix, fieldmap, tables, columns, result, useparams);
  1384. if (fieldmap.ContainsKey(result))
  1385. result = fieldmap[result];
  1386. if (sort.Expression.Type.Equals(typeof(string)))
  1387. result = string.Format("{0} COLLATE NOCASE", result);
  1388. if (sort.Direction == SortDirection.Ascending)
  1389. result = string.Format("{0} ASC", result);
  1390. else
  1391. result = string.Format("{0} DESC", result);
  1392. }
  1393. else
  1394. {
  1395. result = "";
  1396. }
  1397. }
  1398. foreach (var then in sort.Thens)
  1399. result = result + ", " + GetSortClauseNonGeneric(T, command, then, prefix, tables, fieldmap, columns, useparams);
  1400. return result;
  1401. }
  1402. private string GetSortClause<T>(SQLiteCommand command, SortOrder<T>? sort, char prefix, List<Tuple<string, string, string, string>> tables,
  1403. Dictionary<string, string> fieldmap, List<string> columns, bool useparams)
  1404. => GetSortClauseNonGeneric(typeof(T), command, sort, prefix, tables, fieldmap, columns, useparams);
  1405. private static string GetCalculation(AggregateAttribute attribute, string columnname)
  1406. {
  1407. return attribute.Calculation switch
  1408. {
  1409. AggregateCalculation.Sum => "SUM",
  1410. AggregateCalculation.Count => "COUNT",
  1411. AggregateCalculation.Maximum => "MAX",
  1412. AggregateCalculation.Minimum => "MIN",
  1413. AggregateCalculation.Average => "AVERAGE",
  1414. AggregateCalculation.Concat => "GROUP_CONCAT",
  1415. _ => throw new Exception(string.Format("{0}.{1} is not a valid aggregate", columnname, attribute.Calculator.GetType().Name)),
  1416. };
  1417. }
  1418. private string GetFunction(FormulaAttribute attribute, Dictionary<string, string> fieldmap, string columnname)
  1419. {
  1420. if (attribute.Operator == FormulaOperator.None)
  1421. throw new Exception(string.Format("{0}.{1} is not a valid formula", columnname, attribute.Calculator.GetType().Name));
  1422. if (attribute.Operator == FormulaOperator.Constant)
  1423. return EscapeValue(attribute.Value);
  1424. if (!fieldmap.ContainsKey(attribute.Value))
  1425. throw new Exception(string.Format("{0}.{1} -> {2} does not exist", columnname, attribute.GetType().Name, attribute.Value));
  1426. foreach (var modifier in attribute.Modifiers)
  1427. if (!fieldmap.ContainsKey(modifier))
  1428. throw new Exception(string.Format("{0}.{1} -> {2} does not exist", columnname, attribute.GetType().Name, modifier));
  1429. if (attribute.Operator == FormulaOperator.Add)
  1430. return string.Format("(IFNULL({0},0.00) + {1})", fieldmap[attribute.Value],
  1431. string.Join(" + ", attribute.Modifiers.Select(x => string.Format("IFNULL({0},0.00)", fieldmap[x]))));
  1432. if (attribute.Operator == FormulaOperator.Subtract)
  1433. return string.Format("(IFNULL({0},0.00) - ({1}))", fieldmap[attribute.Value],
  1434. string.Join(" + ", attribute.Modifiers.Select(x => string.Format("IFNULL({0},0.00)", fieldmap[x]))));
  1435. if (attribute.Operator == FormulaOperator.Multiply)
  1436. return string.Format("(IFNULL({0},0.00) * {1})", fieldmap[attribute.Value],
  1437. string.Join(" * ", attribute.Modifiers.Select(x => string.Format("IFNULL({0},0.00)", fieldmap[x]))));
  1438. if (attribute.Operator == FormulaOperator.Divide)
  1439. {
  1440. var result = string.Format("IFNULL({0},0.00)", fieldmap[attribute.Value]);
  1441. foreach (var modifier in attribute.Modifiers)
  1442. result = string.Format("({0} / {1})", result, string.Format("IFNULL({0},1.00)", fieldmap[modifier]));
  1443. return result;
  1444. }
  1445. if (attribute.Operator == FormulaOperator.Maximum)
  1446. {
  1447. var parameters = attribute.Modifiers.Select(m => $"IFNULL({fieldmap[m]},0.00)");
  1448. var result = $"MAX({fieldmap[attribute.Value]}, {String.Join(", ", parameters)})";
  1449. // var result = string.Format(
  1450. // "CASE IFNULL({0},0.00) WHEN IFNULL({0},0.00) < IFNULL({1},0.00) THEN IFNULL({1},0.00) ELSE IFNULL({0},0.00) END",
  1451. // fieldmap[attribute.Value], fieldmap[attribute.Modifiers.First()]);
  1452. return result;
  1453. }
  1454. if (attribute.Operator == FormulaOperator.Minumum)
  1455. {
  1456. var parameters = attribute.Modifiers.Select(m => $"IFNULL({fieldmap[m]},0.00)");
  1457. var result = $"MIN({fieldmap[attribute.Value]}, {String.Join(", ", parameters)})";
  1458. //var result = string.Format(
  1459. // "CASE IFNULL({0},0.00) WHEN IFNULL({0},0.00) > IFNULL({1},0.00) THEN IFNULL({1},0.00) ELSE IFNULL({0},0.00) END",
  1460. // fieldmap[attribute.Value], fieldmap[attribute.Modifiers.First()]);
  1461. return result;
  1462. }
  1463. throw new Exception(string.Format("Calculation Type [{0}.{1}] not found", columnname, attribute.GetType().Name));
  1464. }
  1465. private string GetCondition(ConditionAttribute attribute, Dictionary<string, string> fieldmap, string columnname)
  1466. {
  1467. var intf = attribute.Calculator.GetType().GetInterfaces()
  1468. .FirstOrDefault(x => x.Name.StartsWith("ICondition") && x.GenericTypeArguments.Length.Equals(3));
  1469. if (intf is null)
  1470. throw new Exception($"Attribute calculate {attribute.Calculator} is not an ICondition");
  1471. var valuetype = intf.GenericTypeArguments[1];
  1472. var defvalue = valuetype.GetDefault();
  1473. if (!fieldmap.ContainsKey(attribute.Left))
  1474. throw new Exception(string.Format("{0}.{1} -> {2} does not exist", columnname, attribute.GetType().Name, attribute.Left));
  1475. var condition = "";
  1476. if (attribute.Condition == Condition.Equals)
  1477. condition = "=";
  1478. else if (attribute.Condition == Condition.NotEqual)
  1479. condition = "<>";
  1480. else if (attribute.Condition == Condition.GreaterThan)
  1481. condition = ">";
  1482. else if (attribute.Condition == Condition.GreaterThanOrEqualTo)
  1483. condition = ">=";
  1484. else if (attribute.Condition == Condition.LessThan)
  1485. condition = "<";
  1486. else if (attribute.Condition == Condition.LessThanOrEqualTo)
  1487. condition = "<=";
  1488. else
  1489. throw new Exception(string.Format("{0}.{1} is not a valid condition", columnname, attribute.Calculator.GetType().Name));
  1490. if (!fieldmap.ContainsKey(attribute.Right))
  1491. throw new Exception(string.Format("{0}.{1} -> {2} does not exist", columnname, attribute.GetType().Name, attribute.Right));
  1492. if (!fieldmap.ContainsKey(attribute.True))
  1493. throw new Exception(string.Format("{0}.{1} -> {2} does not exist", columnname, attribute.GetType().Name, attribute.True));
  1494. if (!fieldmap.ContainsKey(attribute.False))
  1495. throw new Exception(string.Format("{0}.{1} -> {2} does not exist", columnname, attribute.GetType().Name, attribute.False));
  1496. return string.Format("CASE WHEN COALESCE({0},{1}) {2} {3} THEN {4} ELSE {5} END", fieldmap[attribute.Left], defvalue, condition,
  1497. fieldmap[attribute.Right], fieldmap[attribute.True], fieldmap[attribute.False]);
  1498. }
  1499. //private static List<string> Breadcrumb = new List<string>();
  1500. //Dictionary<String, Tuple<int, TimeSpan>> stopwatch = new Dictionary<string, Tuple<int, TimeSpan>>();
  1501. //Stopwatch sw = new Stopwatch();
  1502. //private void LogReset()
  1503. //{
  1504. // stopwatch = new Dictionary<string, Tuple<int, TimeSpan>>();
  1505. // sw = new Stopwatch();
  1506. //}
  1507. //void LogStart()
  1508. //{
  1509. // if (sw.IsRunning)
  1510. // sw.Restart();
  1511. // else
  1512. // sw.Start();
  1513. //}
  1514. //void LogStop(String msg)
  1515. //{
  1516. // TimeSpan elapsed = sw.Elapsed;
  1517. // if (Breadcrumb.Any())
  1518. // msg = String.Join(".", Breadcrumb) + "." + msg;
  1519. // if (!stopwatch.ContainsKey(msg))
  1520. // stopwatch[msg] = new Tuple<int, TimeSpan>(1,elapsed);
  1521. // else
  1522. // stopwatch[msg] = new Tuple<int, TimeSpan>(stopwatch[msg].Item1 + 1,stopwatch[msg].Item2 + elapsed);
  1523. // sw.Restart();
  1524. //}
  1525. //private void LogPrint(String header)
  1526. //{
  1527. // foreach (var keyvalue in stopwatch.ToArray())
  1528. // OnLog?.Invoke(LogType.Information, String.Format("- {0}: {1} = ({2}) {3,10:0.0000}", header, keyvalue.Key, keyvalue.Value.Item1, keyvalue.Value.Item2.TotalMilliseconds));
  1529. //}
  1530. private void LoadFieldsandTables(SQLiteCommand command, Type type, char prefix, Dictionary<string, string> fieldmap,
  1531. List<Tuple<string, string, string, string>> tables, List<string> columns, string columnname, bool useparams)
  1532. {
  1533. if (fieldmap.ContainsKey(columnname))
  1534. return;
  1535. var newprefix = (char)(prefix + 1);
  1536. // LogStart();
  1537. var property = DatabaseSchema.Property(type, columnname);
  1538. // LogStop("DatabaseSchema.Property");
  1539. if (property != null && property is CustomProperty)
  1540. {
  1541. fieldmap[columnname] = string.Format("{0}1.[{1}]", prefix, columnname);
  1542. }
  1543. else
  1544. {
  1545. var bits = columnname.Split('.');
  1546. if (bits.Length == 1)
  1547. {
  1548. var prop = type.GetProperty(columnname);
  1549. if (prop != null)
  1550. {
  1551. // LogStart();
  1552. // LogStop("GetCustomAttributes(Aggregate)");
  1553. if (prop.GetCustomAttribute<AggregateAttribute>() is AggregateAttribute agg)
  1554. {
  1555. bool internalaggregate = agg.Calculator.GetType().GetInterfaces()
  1556. .Any(x => x.IsGenericType && x.GetGenericTypeDefinition() == typeof(ICoreAggregate<,>));
  1557. if (internalaggregate)
  1558. {
  1559. }
  1560. else
  1561. {
  1562. var scols = new Dictionary<string, string> { { agg.Aggregate, GetCalculation(agg, columnname) } };
  1563. var siblings = columns.Where(x => !x.Equals(columnname) && x.Split('.').First().Equals(bits.First()))
  1564. .Select(x => string.Join(".", x.Split('.').Skip(1))).ToList();
  1565. var linkedtype = agg.Source;
  1566. foreach (var sibling in siblings)
  1567. {
  1568. // LogStart();
  1569. var sprop = CoreUtils.GetProperty(type, sibling);
  1570. // LogStop("GetProperty(sibling)");
  1571. // LogStart();
  1572. var sattr = prop.GetCustomAttributes().FirstOrDefault(x => x.GetType().Equals(typeof(AggregateAttribute)));
  1573. // LogStop("GetCustomAttributes(Sibling.Aggregate)");
  1574. if (sattr is AggregateAttribute sagg)
  1575. {
  1576. // LogStart();
  1577. scols[sagg.Aggregate] = GetCalculation(sagg, sibling);
  1578. // LogStop("GetCalculation(Sibling)");
  1579. }
  1580. }
  1581. var subcols = Columns.Create(linkedtype);
  1582. foreach (var key in agg.Links.Keys)
  1583. subcols.Add(key);
  1584. //subcols.Add(agg.Link);
  1585. foreach (var scol in scols.Keys)
  1586. subcols.Add(scol);
  1587. // LogStart();
  1588. var aggFilter = agg.Filter;
  1589. if(aggFilter is not null)
  1590. {
  1591. var ffs = new List<string>();
  1592. FilterFields(aggFilter, ffs);
  1593. //foreach (var ff in ffs)
  1594. // subcols.Add(ff);
  1595. }
  1596. var linkedtable = string.Format("({0})",
  1597. PrepareSelectNonGeneric(linkedtype, command, newprefix, aggFilter, subcols, null, scols, null, int.MaxValue, false, useparams));
  1598. var alias = tables.Count + 1;
  1599. var link = string.Join(" , ", agg.Links.Keys.Select(x => string.Format("{0}{1}.{2}", prefix, alias, x)));
  1600. var tuple = tables.FirstOrDefault(x =>
  1601. x.Item1.Equals(linkedtable) && x.Item4.Equals(link) && !x.Item2.Equals(string.Format("{0}1", prefix)));
  1602. if (tuple == null)
  1603. {
  1604. var joins = new List<string>();
  1605. foreach (var key in agg.Links.Keys)
  1606. joins.Add(
  1607. string.Format("IFNULL({0}{1}.[{2}],'') = IFNULL({0}1.[{3}],'')", prefix, alias, key, agg.Links[key]));
  1608. var join = string.Format("LEFT OUTER JOIN {0} {1}{2} ON {3}", linkedtable, prefix, alias,
  1609. string.Join(" AND ", joins));
  1610. tuple = new Tuple<string, string, string, string>(linkedtable, prefix + alias.ToString(), join, link);
  1611. //tuple = new Tuple<String, String, String, String>(linkedtable, prefix + alias.ToString(), String.Format("LEFT OUTER JOIN {0} {1}{2} ON {1}{2}.[{3}] = {1}1.[ID]", linkedtable, prefix, alias, agg.Link), link);
  1612. tables.Add(tuple);
  1613. }
  1614. //if (bits.Last().Equals("ID"))
  1615. // fieldmap[columnname] = String.Format("{0}1.[{1}]", prefix, columnname);
  1616. //else
  1617. fieldmap[columnname] = string.Format("{0}.[{1}]", tuple.Item2, agg.Aggregate);
  1618. //LoadFieldsandTables(command, agg.Type, newprefix, fieldmap, tables, columns, columnname);
  1619. //var linkedtable = String.Format("(SELECT [{3}],{0}([{1}]) as [{1}] from {2} group by [{3}])", GetCalculation(agg, columnname), agg.AggregateProperty, agg.Type.EntityName().Split('.').Last(), agg.LinkedProperty);
  1620. //int alias = tables.Count + 1;
  1621. //var tuple = new Tuple<String, String, String, String>(linkedtable, prefix + alias.ToString(), String.Format("LEFT OUTER JOIN {0} {1}{2} ON {1}{2}.[{3}] = {1}1.[ID]", linkedtable, prefix, alias, agg.LinkedProperty), "");
  1622. //tables.Add(tuple);
  1623. //fieldmap[columnname] = String.Format("{0}.[{1}]", tuple.Item2, agg.AggregateProperty);
  1624. }
  1625. }
  1626. else if (prop.GetCustomAttribute<FormulaAttribute>() is FormulaAttribute fnc)
  1627. {
  1628. // var functionmap = new Dictionary<string, string>();
  1629. // CheckColumn(columns, fnc.Value);
  1630. // LoadFieldsandTables(command, type, prefix, functionmap, tables, columns, fnc.Value, useparams);
  1631. // foreach (var column in fnc.Modifiers)
  1632. // {
  1633. // CheckColumn(columns, column);
  1634. // LoadFieldsandTables(command, type, prefix, functionmap, tables, columns, column, useparams);
  1635. // }
  1636. // fieldmap[columnname] = GetFunction(fnc, functionmap, columnname);
  1637. foreach (var field in fnc.Modifiers.Prepend(fnc.Value))
  1638. {
  1639. CheckColumn(columns, field);
  1640. LoadFieldsandTables(command, type, prefix, fieldmap, tables, columns, field, useparams);
  1641. }
  1642. fieldmap[columnname] = GetFunction(fnc, fieldmap, columnname);
  1643. }
  1644. else if(prop.GetCustomAttribute<ConditionAttribute>() is ConditionAttribute cnd)
  1645. {
  1646. var cndmap = new Dictionary<string, string>();
  1647. // LogStart();
  1648. CheckColumn(columns, cnd.Left);
  1649. // LogStop("CheckColumn(Left)");
  1650. // LogStart();
  1651. CheckColumn(columns, cnd.Right);
  1652. // LogStop("CheckColumn(Right)");
  1653. // LogStart();
  1654. CheckColumn(columns, cnd.True);
  1655. // LogStop("CheckColumn(True)");
  1656. // LogStart();
  1657. CheckColumn(columns, cnd.False);
  1658. // LogStop("CheckColumn(False)");
  1659. //// LogStart();
  1660. LoadFieldsandTables(command, type, prefix, cndmap, tables, columns, cnd.Left, useparams);
  1661. //// LogStop("LoadFieldsAndTables(Left)");
  1662. //// LogStart();
  1663. LoadFieldsandTables(command, type, prefix, cndmap, tables, columns, cnd.Right, useparams);
  1664. //// LogStop("LoadFieldsAndTables(Right)");
  1665. //// LogStart();
  1666. LoadFieldsandTables(command, type, prefix, cndmap, tables, columns, cnd.True, useparams);
  1667. //// LogStop("LoadFieldsAndTables(True)");
  1668. //// LogStart();
  1669. LoadFieldsandTables(command, type, prefix, cndmap, tables, columns, cnd.False, useparams);
  1670. //// LogStop("LoadFieldsAndTables(False)");
  1671. // LogStart();
  1672. fieldmap[columnname] = GetCondition(cnd, cndmap, columnname);
  1673. // LogStop("GetCondition");
  1674. }
  1675. else
  1676. {
  1677. fieldmap[columnname] = string.Format("{0}1.[{1}]", prefix, columnname);
  1678. }
  1679. }
  1680. else
  1681. {
  1682. //IProperty customprop = DataModel.Property(type, columnname);
  1683. //if (customprop != null)
  1684. // fieldmap[columnname] = String.Format("{0}1.[{1}]", prefix, columnname);
  1685. //else
  1686. fieldmap[columnname] = columnname;
  1687. }
  1688. }
  1689. else
  1690. {
  1691. var prop = type.GetProperty(bits.First());
  1692. if (prop != null)
  1693. {
  1694. var combinecount = 1;
  1695. while (prop.PropertyType.GetInterfaces().Contains(typeof(IEnclosedEntity)))
  1696. {
  1697. combinecount++;
  1698. prop = CoreUtils.GetProperty(type, string.Join(".", bits.Take(combinecount)));
  1699. }
  1700. if (prop.PropertyType.GetInterfaces().Contains(typeof(IEntityLink)))
  1701. {
  1702. var linkedType = prop.PropertyType.GetInheritedGenericTypeArguments().First();
  1703. var remote = linkedType.GetProperty(bits.Last());
  1704. // Are there any other properties for this link? These will form the columns for our subquery
  1705. var siblings = columns.Where(x => x.Split('.').First().Equals(bits.First()))
  1706. .Select(x => string.Join(".", x.Split('.').Skip(combinecount))).ToList();
  1707. if (remote != null && !siblings.Contains(remote.Name))
  1708. siblings.Add(remote.Name);
  1709. if(prop.GetCustomAttribute<ChildEntityAttribute>() is ChildEntityAttribute child)
  1710. {
  1711. var parent = child.Calculator.ParentColumn;
  1712. if (!siblings.Contains(nameof(Entity.ID)))
  1713. siblings.Insert(0, nameof(Entity.ID));
  1714. if (!siblings.Contains(parent))
  1715. siblings.Add(parent);
  1716. var subcols = Columns.Create(linkedType, siblings);
  1717. var subPrefix = (char)(newprefix + 1);
  1718. var innerSQL = string.Format("({0})",
  1719. PrepareSelectNonGeneric(
  1720. linkedType,
  1721. command,
  1722. subPrefix,
  1723. child.Calculator.Filter,
  1724. subcols,
  1725. child.Calculator.Sort,
  1726. null,
  1727. null,
  1728. int.MaxValue,
  1729. false,
  1730. useparams));
  1731. var linkedTable = $"(SELECT {string.Join(", ", siblings.Select(x => $"{newprefix}1.[{x}] as [{x}]"))}"
  1732. + $" FROM {innerSQL} {newprefix}1"
  1733. + $" GROUP BY [{parent}])";
  1734. var link = string.Format("{0}.ID", prop.Name);
  1735. var tuple = tables.FirstOrDefault(x =>
  1736. x.Item1.Equals(linkedTable) && x.Item4.Equals(link) && !x.Item2.Equals(string.Format("{0}1", prefix)));
  1737. if (tuple is null)
  1738. {
  1739. var alias = tables.Count + 1;
  1740. tuple = new Tuple<string, string, string, string>(
  1741. linkedTable,
  1742. prefix + alias.ToString(),
  1743. string.Format(
  1744. "LEFT OUTER JOIN {0} {1}{2} ON {1}{2}.[{3}] = {1}1.[ID]",
  1745. linkedTable,
  1746. prefix,
  1747. alias,
  1748. parent
  1749. ),
  1750. link
  1751. );
  1752. tables.Add(tuple);
  1753. }
  1754. //if (bits.Last().Equals("ID"))
  1755. // fieldmap[columnname] = String.Format("{0}1.[{1}]", prefix, columnname);
  1756. //else
  1757. fieldmap[columnname] = string.Format("{0}.[{1}]", tuple.Item2, string.Join(".", bits.Skip(combinecount)));
  1758. foreach (var sibling in siblings)
  1759. {
  1760. var subcol = string.Format("{0}.{1}", string.Join(".", bits.Take(combinecount)), sibling);
  1761. if (!subcol.Equals(columnname))
  1762. fieldmap[subcol] = string.Format("{0}.[{1}]", tuple.Item2, sibling);
  1763. }
  1764. }
  1765. else
  1766. {
  1767. if (siblings.Count.Equals(1) && siblings.First().Equals("ID"))
  1768. {
  1769. fieldmap[columnname] = string.Format("{0}1.[{1}]", prefix, columnname);
  1770. }
  1771. else
  1772. {
  1773. if (!siblings.Contains("ID"))
  1774. siblings.Insert(0, "ID");
  1775. var subcols = Columns.Create(linkedType, siblings);
  1776. var linkedtable = string.Format("({0})",
  1777. PrepareSelectNonGeneric(linkedType, command, newprefix, null, subcols, null, null, null, int.MaxValue, false, useparams));
  1778. var link = string.Format("{0}.ID", prop.Name);
  1779. var tuple = tables.FirstOrDefault(x =>
  1780. x.Item1.Equals(linkedtable) && x.Item4.Equals(link) && !x.Item2.Equals(string.Format("{0}1", prefix)));
  1781. if (tuple == null)
  1782. {
  1783. var alias = tables.Count + 1;
  1784. tuple = new Tuple<string, string, string, string>(
  1785. linkedtable,
  1786. prefix + alias.ToString(),
  1787. string.Format(
  1788. "LEFT OUTER JOIN {0} {1}{2} ON {1}{2}.[ID] = {1}1.[{3}.ID]",
  1789. linkedtable,
  1790. prefix,
  1791. alias,
  1792. string.Join(".", bits.Take(combinecount)) //prop.Name
  1793. ),
  1794. link
  1795. );
  1796. tables.Add(tuple);
  1797. }
  1798. //if (bits.Last().Equals("ID"))
  1799. // fieldmap[columnname] = String.Format("{0}1.[{1}]", prefix, columnname);
  1800. //else
  1801. fieldmap[columnname] = string.Format("{0}.[{1}]", tuple.Item2, string.Join(".", bits.Skip(combinecount)));
  1802. foreach (var sibling in siblings)
  1803. {
  1804. var subcol = string.Format("{0}.{1}", string.Join(".", bits.Take(combinecount)), sibling);
  1805. if (!subcol.Equals(columnname))
  1806. fieldmap[subcol] = string.Format("{0}.[{1}]", tuple.Item2, sibling);
  1807. }
  1808. }
  1809. }
  1810. }
  1811. else if (prop.PropertyType.GetInterfaces().Contains(typeof(IEnclosedEntity)))
  1812. {
  1813. fieldmap[columnname] = string.Format("{0}1.[{1}]", prefix, columnname);
  1814. }
  1815. else
  1816. {
  1817. fieldmap[columnname] = string.Format("{0}1.[{1}]", prefix, columnname);
  1818. }
  1819. }
  1820. else
  1821. {
  1822. fieldmap[columnname] = columnname;
  1823. }
  1824. }
  1825. }
  1826. }
  1827. private static void CheckColumn(List<string> columns, string column)
  1828. {
  1829. if (!columns.Contains(column))
  1830. columns.Add(column);
  1831. }
  1832. public void FilterFields(IFilter? filter, List<string> fields)
  1833. {
  1834. if (filter == null)
  1835. return;
  1836. if (filter.Operator != Operator.None && filter.Operator != Operator.All)
  1837. {
  1838. var exp = CoreUtils.GetFullPropertyName(CoreUtils.ExtractMemberExpression(filter.Expression), ".");
  1839. if (!fields.Contains(exp))
  1840. fields.Add(exp);
  1841. }
  1842. foreach (var and in filter.Ands)
  1843. FilterFields(and, fields);
  1844. foreach (var or in filter.Ors)
  1845. FilterFields(or, fields);
  1846. }
  1847. public void SortFields(ISortOrder? sort, List<string> fields)
  1848. {
  1849. if (sort == null)
  1850. return;
  1851. var exp = CoreUtils.GetFullPropertyName(CoreUtils.ExtractMemberExpression(sort.Expression), ".");
  1852. if (!fields.Contains(exp))
  1853. fields.Add(exp);
  1854. foreach (var then in sort.Thens)
  1855. SortFields(then, fields);
  1856. }
  1857. public enum AggregateType
  1858. {
  1859. None,
  1860. Sum,
  1861. Count
  1862. }
  1863. public string PrepareSelectNonGeneric(Type T, SQLiteCommand command, char prefix,
  1864. IFilter? filter, IColumns? columns, ISortOrder? sort,
  1865. Dictionary<string, string>? aggregates, Dictionary<string, object?>? constants, int top, bool distinct, bool useparams)
  1866. {
  1867. var fieldmap = new Dictionary<string, string>();
  1868. var cols = CoreUtils.GetColumns(T, columns);
  1869. var fields = new List<string>();
  1870. fields.AddRange(cols.ColumnNames());
  1871. FilterFields(filter, fields);
  1872. SortFields(sort, fields);
  1873. var tables = new List<Tuple<string, string, string, string>>();
  1874. var condition = "";
  1875. var sortorder = "";
  1876. var entityName = T.EntityName().Split('.').Last();
  1877. tables.Add(new Tuple<string, string, string, string>(
  1878. entityName,
  1879. $"{prefix}1",
  1880. $"{entityName} {prefix}1",
  1881. "")
  1882. );
  1883. foreach (var column in cols.ColumnNames())
  1884. LoadFieldsandTables(command, T, prefix, fieldmap, tables, fields, column, useparams);
  1885. var parameters = new Dictionary<string, object>();
  1886. condition = GetFilterClauseNonGeneric(T, command, prefix, filter, tables, fieldmap, fields, useparams);
  1887. sortorder = GetSortClauseNonGeneric(T, command, sort, prefix, tables, fieldmap, fields, useparams);
  1888. var combined = new SortedDictionary<string, string>();
  1889. fields.Clear();
  1890. foreach (var column in cols.ColumnNames())
  1891. if (fieldmap.ContainsKey(column))
  1892. {
  1893. if (aggregates != null && aggregates.ContainsKey(column))
  1894. combined[constants != null ? column : String.Format("{0:D8}", combined.Keys.Count)] = string.Format("{0}({1}) as [{2}]", aggregates[column], fieldmap[column], column);
  1895. else
  1896. combined[constants != null ? column : String.Format("{0:D8}", combined.Keys.Count)] = string.Format("{0} as [{1}]", fieldmap[column], column);
  1897. }
  1898. if (constants != null)
  1899. {
  1900. foreach(var (column, value) in constants)
  1901. {
  1902. combined[column] = string.Format("{0} as [{1}]", EscapeValue(value), column);
  1903. }
  1904. }
  1905. var result = new List<string>();
  1906. result.Add("SELECT");
  1907. if (distinct)
  1908. result.Add("DISTINCT");
  1909. result.Add(string.Join(", ", combined.Values));
  1910. result.Add("FROM");
  1911. result.AddRange(tables.Select(x => x.Item3));
  1912. if (!string.IsNullOrWhiteSpace(condition))
  1913. {
  1914. result.Add("WHERE");
  1915. result.Add(condition);
  1916. }
  1917. if (!string.IsNullOrWhiteSpace(sortorder))
  1918. {
  1919. result.Add("ORDER BY");
  1920. result.Add(sortorder);
  1921. }
  1922. if (aggregates != null)
  1923. {
  1924. var str = string.Join(", ",
  1925. fieldmap.Where(x => cols.ColumnNames().Contains(x.Key) && !aggregates.ContainsKey(x.Key)).Select(f => f.Value));
  1926. if (!string.IsNullOrWhiteSpace(str))
  1927. {
  1928. result.Add("GROUP BY");
  1929. result.Add(str);
  1930. }
  1931. }
  1932. if (top != int.MaxValue)
  1933. result.Add(String.Format("LIMIT {0}", top));
  1934. //Breadcrumb.Remove(Breadcrumb.Last());
  1935. return string.Join(" ", result);
  1936. }
  1937. public string PrepareSelect<T>(SQLiteCommand command, char prefix, Filter<T>? filter, Columns<T>? columns, SortOrder<T>? sort,
  1938. Dictionary<string, string>? aggregates, Dictionary<string, object?>? constants, int top, bool distinct, bool useparams) where T : Entity
  1939. => PrepareSelectNonGeneric(typeof(T), command, prefix, filter, columns, sort, aggregates, constants, top, distinct, useparams);
  1940. private static void PrepareUpsertNonGeneric(Type T, SQLiteCommand command, Entity item)
  1941. {
  1942. command.CommandText = "";
  1943. command.Parameters.Clear();
  1944. if (item.ID == Guid.Empty)
  1945. item.ID = Guid.NewGuid();
  1946. Dictionary<string, object> insert = item.GetValues(true);
  1947. Dictionary<string, object> update = item.GetValues(false);
  1948. var insertfields = new List<string>();
  1949. var insertvalues = new List<string>();
  1950. var updatecommands = new List<string>();
  1951. var iParam = 0;
  1952. foreach (var key in insert.Keys)
  1953. {
  1954. if (insert[key] is UserProperties)
  1955. continue;
  1956. var sParam = string.Format("@p{0}", iParam++);
  1957. object? value = null;
  1958. try
  1959. {
  1960. value = Encode(insert[key], insert[key]?.GetType());
  1961. }
  1962. catch (Exception e)
  1963. {
  1964. Logger.Send(LogType.Error, "", string.Format("*** Unknown Error: {0}\n{1}", e.Message, e.StackTrace));
  1965. }
  1966. command.Parameters.AddWithValue(sParam, value);
  1967. insertfields.Add(string.Format("[{0}]", key));
  1968. insertvalues.Add(sParam);
  1969. if (update.ContainsKey(key))
  1970. if (!key.Equals("ID"))
  1971. updatecommands.Add(string.Format("[{0}]={1}", key, sParam));
  1972. }
  1973. var particles = new List<string>();
  1974. particles.Add("INSERT INTO");
  1975. particles.Add(T.EntityName().Split('.').Last());
  1976. particles.Add("(");
  1977. particles.Add(string.Join(",", insertfields));
  1978. particles.Add(")");
  1979. particles.Add("VALUES");
  1980. particles.Add("(");
  1981. particles.Add(string.Join(",", insertvalues));
  1982. particles.Add(")");
  1983. if (updatecommands.Any())
  1984. particles.Add("ON CONFLICT([ID]) DO UPDATE SET " + string.Join(", ", updatecommands));
  1985. else
  1986. particles.Add("ON CONFLICT DO NOTHING");
  1987. command.CommandText = string.Join(" ", particles);
  1988. }
  1989. private void PrepareUpsert<T>(SQLiteCommand command, T item) where T : Entity
  1990. => PrepareUpsertNonGeneric(typeof(T), command, item);
  1991. public void PrepareDelete<T>(SQLiteCommand command, T item) where T : Entity
  1992. {
  1993. command.CommandText = string.Format("DELETE FROM {0} WHERE [ID] = @p0", typeof(T).EntityName().Split('.').Last());
  1994. command.Parameters.Clear();
  1995. command.Parameters.AddWithValue("@p0", Encode(item.ID, typeof(Guid)));
  1996. }
  1997. #endregion
  1998. #region Schema Handling
  1999. public Dictionary<string, Type> GetSchema()
  2000. {
  2001. var result = new Dictionary<string, Type>();
  2002. return result;
  2003. }
  2004. public void CreateSchema(params Type[] types)
  2005. {
  2006. }
  2007. public void SaveSchema(Dictionary<string, Type> schema)
  2008. {
  2009. }
  2010. public void UpgradeSchema(params Type[] types)
  2011. {
  2012. }
  2013. #endregion
  2014. #region CRUD Operations
  2015. public static object[] GetValues(IDataReader reader, int count)
  2016. {
  2017. var result = new object[count];
  2018. reader.GetValues(result);
  2019. return result;
  2020. }
  2021. #region Query
  2022. public IEnumerable<object[]> List<T>(Filter<T>? filter = null, Columns<T>? columns = null, SortOrder<T>? sort = null) where T : Entity, new()
  2023. {
  2024. //Dictionary<String, TimeSpan> stopwatch = new Dictionary<string, TimeSpan>();
  2025. //Stopwatch sw = new Stopwatch();
  2026. //sw.Start();
  2027. var cols = CoreUtils.GetColumns(columns);
  2028. //stopwatch["GetColumns"] = new TimeSpan(sw.ElapsedTicks);
  2029. //sw.Restart();
  2030. var result = new List<object[]>();
  2031. //stopwatch["MakeResult"] = new TimeSpan(sw.ElapsedTicks);
  2032. //sw.Restart();
  2033. using (var access = GetReadAccess())
  2034. {
  2035. using (var command = access.CreateCommand())
  2036. {
  2037. var sortorder = sort ?? LookupFactory.DefineSort<T>();
  2038. command.CommandText = "";
  2039. command.Parameters.Clear();
  2040. command.CommandText = PrepareSelect(command, 'A', filter, cols, sortorder, null, null, int.MaxValue, false, true) + ";";
  2041. using (var reader = command.ExecuteReader())
  2042. {
  2043. foreach (var row in reader)
  2044. {
  2045. var values = GetValues(reader, cols.Items.Length);
  2046. result.Add(values);
  2047. }
  2048. reader.Close();
  2049. }
  2050. }
  2051. }
  2052. return result;
  2053. }
  2054. private CoreTable DoQueryNonGeneric(Type T, IFilter? filter = null, IColumns? columns = null, ISortOrder? sort = null, int top = int.MaxValue, bool log = false, bool distinct = false)
  2055. {
  2056. var start = DateTime.Now;
  2057. //LogReset();
  2058. //LogStart();
  2059. //LogStop("GetColumns");
  2060. var cols = CoreUtils.GetColumns(T, columns);
  2061. var blobColumns = Columns.Create(T);
  2062. foreach(var column in cols.GetColumns())
  2063. {
  2064. var prop = DatabaseSchema.Property(T, column.Property);
  2065. if (prop is not null && prop.HasAttribute<ExternalStorageAttribute>())
  2066. {
  2067. blobColumns.Add(column);
  2068. }
  2069. }
  2070. if(blobColumns.Count > 0)
  2071. {
  2072. cols.Add("ID");
  2073. }
  2074. var result = new CoreTable(T.EntityName());
  2075. foreach (var col in cols.GetColumns())
  2076. result.Columns.Add(new CoreColumn { ColumnName = col.Property, DataType = col.Type });
  2077. //LogStop("MakeTable");
  2078. using (var access = GetReadAccess())
  2079. {
  2080. using (var command = access.CreateCommand())
  2081. {
  2082. var sortorder = sort ?? LookupFactory.DefineSort(T);
  2083. command.CommandText = "";
  2084. command.Parameters.Clear();
  2085. //LogStart();
  2086. var sql = PrepareSelectNonGeneric(T, command, 'A', filter, cols, sortorder, null, null, top, distinct, true) + ";";
  2087. command.CommandText = sql;
  2088. try
  2089. {
  2090. using (var reader = command.ExecuteReader())
  2091. {
  2092. if (reader.HasRows)
  2093. {
  2094. var rows = new List<CoreRow>();
  2095. while (reader.Read())
  2096. {
  2097. var row = result.NewRow();
  2098. for (var i = 0; i < reader.FieldCount; i++)
  2099. try
  2100. {
  2101. var type = result.Columns[i].DataType;
  2102. if (reader.IsDBNull(i))
  2103. {
  2104. row.Values.Add(result.Columns[i].DataType.GetDefault());
  2105. }
  2106. else if (type.IsEnum)
  2107. {
  2108. ReadAndDecodeValue(result, reader, row, i);
  2109. }
  2110. else if (type == typeof(byte))
  2111. {
  2112. row.Values.Add(reader.GetByte(i));
  2113. }
  2114. else if (type == typeof(byte[]))
  2115. {
  2116. ReadAndDecodeValue(result, reader, row, i);
  2117. }
  2118. else if (type == typeof(bool))
  2119. {
  2120. ReadAndDecodeValue(result, reader, row, i);
  2121. }
  2122. else if (type == typeof(short))
  2123. {
  2124. row.Values.Add(reader.GetInt16(i));
  2125. }
  2126. else if (type == typeof(int))
  2127. {
  2128. row.Values.Add(reader.GetInt32(i));
  2129. }
  2130. else if (type == typeof(long))
  2131. {
  2132. row.Values.Add(reader.GetInt64(i));
  2133. }
  2134. else if (type == typeof(float))
  2135. {
  2136. row.Values.Add(reader.GetFloat(i));
  2137. }
  2138. else if (type == typeof(double))
  2139. {
  2140. row.Values.Add(reader.GetDouble(i));
  2141. }
  2142. else if (type == typeof(decimal))
  2143. {
  2144. row.Values.Add(reader.GetDecimal(i));
  2145. }
  2146. else if (type == typeof(string))
  2147. {
  2148. var value = reader.GetString(i);
  2149. row.Values.Add(value != null ? value : "");
  2150. }
  2151. else if (type == typeof(DateTime))
  2152. {
  2153. ReadAndDecodeValue(result, reader, row, i);
  2154. }
  2155. else if (type == typeof(TimeSpan))
  2156. {
  2157. ReadAndDecodeValue(result, reader, row, i);
  2158. }
  2159. else if (type == typeof(Guid))
  2160. {
  2161. row.Values.Add(reader.GetGuid(i));
  2162. }
  2163. else
  2164. {
  2165. ReadAndDecodeValue(result, reader, row, i);
  2166. }
  2167. }
  2168. catch (Exception e)
  2169. {
  2170. row.Values.Add(result.Columns[i].DataType.GetDefault());
  2171. }
  2172. result.Rows.Add(row);
  2173. }
  2174. }
  2175. reader.Close();
  2176. }
  2177. }
  2178. catch (Exception e)
  2179. {
  2180. OnLog?.Invoke(LogType.Error, e.Message);
  2181. }
  2182. //LogStop("ReadData");
  2183. //LogPrint(String.Format("Query{0}", typeof(T).Name));
  2184. }
  2185. }
  2186. foreach(var column in blobColumns.ColumnNames())
  2187. {
  2188. foreach(var row in result.Rows)
  2189. {
  2190. var id = row.Get<Guid>("ID");
  2191. var data = GetExternalData(T, column, id);
  2192. if(data is not null)
  2193. {
  2194. row.Set<byte[]>(column, data);
  2195. }
  2196. }
  2197. }
  2198. if (log)
  2199. {
  2200. var duration = DateTime.Now - start;
  2201. Logger.Send(LogType.Information, "",
  2202. string.Format("SQLite::Query<{0}>({1} cols) returns {2} rows in {3}ms", T.Name, cols.Count, result.Rows.Count,
  2203. duration.TotalMilliseconds));
  2204. }
  2205. return result;
  2206. }
  2207. private CoreTable DoQuery<T>(Filter<T>? filter, Columns<T>? columns, SortOrder<T>? sort, int top, bool log, bool distinct)
  2208. where T : Entity, new() => DoQueryNonGeneric(typeof(T), filter, columns, sort, top, log, distinct);
  2209. public CoreTable Query(Type type, IFilter? filter, IColumns? columns, ISortOrder? sort, int top, bool log, bool distinct)
  2210. => DoQueryNonGeneric(type, filter, columns, sort, top, log, distinct);
  2211. public CoreTable Query<T>(Filter<T>? filter = null, Columns<T>? columns = null, SortOrder<T>? sort = null, int top = int.MaxValue, bool log = false, bool distinct = false)
  2212. where T : Entity, new()
  2213. {
  2214. return DoQuery(filter, columns, sort, top, log, distinct);
  2215. }
  2216. [Obsolete]
  2217. public CoreTable QueryDeleted<T>(Deletion deletion, Filter<T>? filter = null, Columns<T>? columns = null, SortOrder<T>? sort = null, int top = int.MaxValue, bool log = false)
  2218. where T : Entity, new()
  2219. {
  2220. if (filter != null)
  2221. filter.And(x => x.Deleted).IsEqualTo(deletion.ID);
  2222. else
  2223. filter = new Filter<T>(x => x.Deleted).IsEqualTo(deletion.ID);
  2224. return DoQuery(filter, columns, sort, top, false, false);
  2225. }
  2226. private void ReadAndDecodeValue(CoreTable result, SQLiteDataReader reader, CoreRow row, int i)
  2227. {
  2228. var value = reader.GetValue(i);
  2229. var decoded = Decode(value, result.Columns[i].DataType);
  2230. row.Values.Add(decoded);
  2231. }
  2232. public T[] Load<T>(Filter<T>? filter = null, SortOrder<T>? sort = null) where T : Entity, new()
  2233. {
  2234. var result = new List<T>();
  2235. var cols = CoreUtils.GetColumns<T>(null);
  2236. using (var access = GetReadAccess())
  2237. {
  2238. using (var command = access.CreateCommand())
  2239. {
  2240. try
  2241. {
  2242. command.CommandText = "";
  2243. command.Parameters.Clear();
  2244. command.CommandText = PrepareSelect(command, 'A', filter, cols, sort, null, null, int.MaxValue, false, true) + ";";
  2245. using (var reader = command.ExecuteReader())
  2246. {
  2247. if (reader.HasRows)
  2248. while (reader.Read())
  2249. {
  2250. var entity = new T();
  2251. entity.SetObserving(false);
  2252. for (var i = 0; i < reader.FieldCount; i++)
  2253. {
  2254. object value;
  2255. if (cols.Items[i].Expression.Type == typeof(long) && !reader.IsDBNull(i))
  2256. value = reader.GetInt64(i);
  2257. else
  2258. value = reader.GetValue(i);
  2259. try
  2260. {
  2261. var decoded = Decode(value, cols.Items[i].Expression.Type);
  2262. CoreUtils.SetPropertyValue(entity, cols.Items[i].Property, decoded);
  2263. }
  2264. catch (Exception e)
  2265. {
  2266. Logger.Send(LogType.Error, "", string.Format("*** Unknown Error: {0}\n{1}", e.Message, e.StackTrace));
  2267. }
  2268. }
  2269. entity.SetObserving(true);
  2270. result.Add(entity);
  2271. }
  2272. reader.Close();
  2273. }
  2274. }
  2275. catch (Exception e)
  2276. {
  2277. Logger.Send(LogType.Error, "", string.Format("*** Unknown Error: {0}\n{1}", e.Message, e.StackTrace));
  2278. }
  2279. }
  2280. }
  2281. return result.ToArray();
  2282. }
  2283. #endregion
  2284. #region Save
  2285. private static readonly Dictionary<Type, IProperty[]> _externalProperties = new Dictionary<Type, IProperty[]>();
  2286. private static IProperty[] GetExternalProperties(Type T)
  2287. {
  2288. if(!_externalProperties.TryGetValue(T, out var properties))
  2289. {
  2290. properties = DatabaseSchema.Properties(T).Where(x => x.HasAttribute<ExternalStorageAttribute>()).ToArray();
  2291. _externalProperties.Add(T, properties);
  2292. }
  2293. return properties;
  2294. }
  2295. private void OnSaveNonGeneric(Type T, Entity entity)
  2296. {
  2297. var props = GetExternalProperties(T);
  2298. List<(IProperty, byte[])>? data = null;
  2299. if (props.Any())
  2300. {
  2301. data = new List<(IProperty, byte[])>();
  2302. foreach(var prop in props)
  2303. {
  2304. var value = prop.Getter()(entity) as byte[];
  2305. if(value is not null && value.Length > 0)
  2306. {
  2307. data.Add(new(prop, value));
  2308. prop.Setter()(entity, Array.Empty<byte>());
  2309. }
  2310. }
  2311. }
  2312. using var access = GetWriteAccess();
  2313. using var command = access.CreateCommand();
  2314. PrepareUpsertNonGeneric(T, command, entity);
  2315. command.ExecuteNonQuery();
  2316. if(data is not null)
  2317. {
  2318. foreach(var (prop, value) in data)
  2319. {
  2320. SaveExternalData(T, prop.Name, entity.ID, value);
  2321. prop.Setter()(entity, value);
  2322. }
  2323. }
  2324. }
  2325. private void OnSaveNonGeneric(Type T, IEnumerable<Entity> entities)
  2326. {
  2327. // Casting to IList so that we can use it multiple times.
  2328. entities = entities.AsIList();
  2329. if (!entities.Any())
  2330. return;
  2331. var props = GetExternalProperties(T);
  2332. List<(IProperty, List<(Entity, byte[])>)>? data = null;
  2333. if (props.Any())
  2334. {
  2335. data = new List<(IProperty, List<(Entity, byte[])>)>();
  2336. foreach(var prop in props)
  2337. {
  2338. var lst = new List<(Entity, byte[])>();
  2339. foreach(var entity in entities)
  2340. {
  2341. var value = prop.Getter()(entity) as byte[];
  2342. if(value is not null && value.Length > 0)
  2343. {
  2344. lst.Add((entity, value));
  2345. prop.Setter()(entity, Array.Empty<byte>());
  2346. }
  2347. }
  2348. data.Add(new(prop, lst));
  2349. }
  2350. }
  2351. using var access = GetWriteAccess();
  2352. using var transaction = access.BeginTransaction();
  2353. try
  2354. {
  2355. using var command = access.CreateCommand();
  2356. foreach (var entity in entities)
  2357. {
  2358. PrepareUpsertNonGeneric(T, command, entity);
  2359. command.ExecuteNonQuery();
  2360. }
  2361. transaction.Commit();
  2362. if(data is not null)
  2363. {
  2364. foreach(var (property, list) in data)
  2365. {
  2366. foreach(var (entity, value) in list)
  2367. {
  2368. SaveExternalData(T, property.Name, entity.ID, value);
  2369. property.Setter()(entity, value);
  2370. }
  2371. }
  2372. }
  2373. }
  2374. catch (Exception)
  2375. {
  2376. transaction.Rollback();
  2377. throw;
  2378. }
  2379. }
  2380. private void OnSave<T>(T entity) where T : Entity
  2381. => OnSaveNonGeneric(typeof(T), entity);
  2382. public static bool CanSave(Type T)
  2383. {
  2384. if (DbFactory.IsReadOnly)
  2385. {
  2386. if (T.IsAssignableTo(typeof(License)) || T.IsAssignableTo(typeof(UserTracking)))
  2387. return true;
  2388. DbFactory.LogReadOnly();
  2389. throw new DbLockedException();
  2390. }
  2391. return true;
  2392. }
  2393. public static bool CanSave<T>() => CanSave(typeof(T));
  2394. public void Save(Type type, Entity entity)
  2395. {
  2396. if (!CanSave(type))
  2397. {
  2398. return;
  2399. }
  2400. OnSaveNonGeneric(type, entity);
  2401. }
  2402. public void Save(Type type, IEnumerable<Entity> entities)
  2403. {
  2404. if (!CanSave(type))
  2405. {
  2406. return;
  2407. }
  2408. OnSaveNonGeneric(type, entities);
  2409. }
  2410. public void Save<T>(IEnumerable<T> entities) where T : Entity => Save(typeof(T), entities);
  2411. public void Save<T>(T entity) where T : Entity => Save(typeof(T), entity);
  2412. #endregion
  2413. #region Delete
  2414. public void Purge<T>(T entity) where T : Entity
  2415. {
  2416. if (!CanSave<T>()) return;
  2417. using var access = GetWriteAccess();
  2418. using var command = access.CreateCommand();
  2419. PrepareDelete(command, entity);
  2420. var rows = command.ExecuteNonQuery();
  2421. }
  2422. public void Purge<T>(IEnumerable<T> entities) where T : Entity
  2423. {
  2424. if (!CanSave<T>()) return;
  2425. // Casting to IList so that we can use it multiple times.
  2426. entities = entities.AsIList();
  2427. if (!entities.Any())
  2428. return;
  2429. using var access = GetWriteAccess();
  2430. using var transaction = access.BeginTransaction();
  2431. try
  2432. {
  2433. using (var command = access.CreateCommand())
  2434. {
  2435. foreach (var entity in entities)
  2436. {
  2437. PrepareDelete(command, entity);
  2438. var rows = command.ExecuteNonQuery();
  2439. }
  2440. }
  2441. transaction.Commit();
  2442. }
  2443. catch (Exception)
  2444. {
  2445. transaction.Rollback();
  2446. throw;
  2447. }
  2448. }
  2449. private Dictionary<Type, List<Tuple<Type, string>>> _cascades = new();
  2450. private Dictionary<Type, List<Tuple<Type, List<string>>>> _setNulls = new();
  2451. private const int deleteBatchSize = 100;
  2452. private void LoadDeletions(Type type)
  2453. {
  2454. // Get the EntityLink that is associated with this class
  2455. var linkclass = CoreUtils.TypeList(
  2456. new[] { type.Assembly },
  2457. x => typeof(IEntityLink).GetTypeInfo().IsAssignableFrom(x) && x.GetInheritedGenericTypeArguments().FirstOrDefault() == type
  2458. ).FirstOrDefault();
  2459. // if The entitylink does not exist, we don't need to do anything
  2460. if (linkclass == null)
  2461. return;
  2462. var cascades = new List<Tuple<Type, string>>();
  2463. var setNulls = new List<Tuple<Type, List<string>>>();
  2464. var childtypes = Types.Where(x => x.IsSubclassOf(typeof(Entity)) && x.GetCustomAttribute<AutoEntity>() == null);
  2465. foreach (var childtype in childtypes)
  2466. {
  2467. // Get all registered types for this entitylink
  2468. var fields = new List<string>();
  2469. var bDelete = false;
  2470. // Find any IEntityLink<> properties that refer back to this class
  2471. var childprops = CoreUtils.PropertyList(childtype, x => x.PropertyType == linkclass);
  2472. foreach (var childprop in childprops)
  2473. {
  2474. var fieldname = string.Format("{0}.ID", childprop.Name);
  2475. var attr = childprop.GetCustomAttributes(typeof(EntityRelationshipAttribute), true).FirstOrDefault();
  2476. if (attr != null && ((EntityRelationshipAttribute)attr).Action.Equals(DeleteAction.Cascade))
  2477. {
  2478. cascades.Add(new(childtype, fieldname));
  2479. bDelete = true;
  2480. break;
  2481. }
  2482. fields.Add(fieldname);
  2483. }
  2484. if(!bDelete && fields.Any())
  2485. {
  2486. setNulls.Add(new(childtype, fields));
  2487. }
  2488. }
  2489. if(cascades.Count > 0)
  2490. {
  2491. _cascades[type] = cascades;
  2492. }
  2493. if(setNulls.Count > 0)
  2494. {
  2495. _setNulls[type] = setNulls;
  2496. }
  2497. }
  2498. private readonly MethodInfo _deleteEntitiesMethod = typeof(SQLiteProvider).GetMethods(BindingFlags.NonPublic | BindingFlags.Instance)
  2499. .Single(x => x.Name == nameof(DeleteEntity) && x.IsGenericMethod);
  2500. private void DeleteEntity<T>(Guid[] parentIDs, string parentField, DeletionData deletionData) where T : Entity, new()
  2501. {
  2502. var columns = DeletionData.DeletionColumns<T>();
  2503. var entityIDs = new List<Guid>();
  2504. for (int i = 0; i < parentIDs.Length; i += deleteBatchSize)
  2505. {
  2506. var items = new ArraySegment<Guid>(parentIDs, i, Math.Min(deleteBatchSize, parentIDs.Length - i));
  2507. var entities = Query(new Filter<T>(parentField).InList(items.ToArray()), columns);
  2508. foreach (var row in entities.Rows)
  2509. {
  2510. deletionData.DeleteEntity<T>(row);
  2511. entityIDs.Add(row.Get<T, Guid>(x => x.ID));
  2512. }
  2513. }
  2514. CascadeDelete(typeof(T), entityIDs.ToArray(), deletionData);
  2515. }
  2516. private void DeleteEntity(Type T, Guid[] parentIDs, string parentField, DeletionData deletionData)
  2517. {
  2518. _deleteEntitiesMethod.MakeGenericMethod(T).Invoke(this, new object?[] { parentIDs, parentField, deletionData });
  2519. }
  2520. private readonly MethodInfo _setNullEntityMethod = typeof(SQLiteProvider).GetMethods(BindingFlags.NonPublic | BindingFlags.Instance)
  2521. .Single(x => x.Name == nameof(SetNullEntity) && x.IsGenericMethod);
  2522. private void SetNullEntity<T>(List<string> properties, Guid[] parentIDs, DeletionData deletionData) where T : Entity, new()
  2523. {
  2524. foreach(var property in properties)
  2525. {
  2526. var columns = new Columns<T>(x => x.ID);
  2527. columns.Add(property);
  2528. for(int i = 0; i < parentIDs.Length; i += deleteBatchSize)
  2529. {
  2530. var items = new ArraySegment<Guid>(parentIDs, i, Math.Min(deleteBatchSize, parentIDs.Length - i));
  2531. var entities = Query(new Filter<T>(property).InList(items.ToArray()), columns);
  2532. foreach (var row in entities.Rows)
  2533. {
  2534. deletionData.SetNullEntity<T>(row.Get<T, Guid>(x => x.ID), property, row.Get<Guid>(property));
  2535. }
  2536. }
  2537. }
  2538. }
  2539. private void SetNullEntity(Type T, List<string> properties, Guid[] parentIDs, DeletionData deletionData)
  2540. {
  2541. _setNullEntityMethod.MakeGenericMethod(T).Invoke(this, new object?[] { properties, parentIDs, deletionData });
  2542. }
  2543. private void CascadeDelete(Type type, Guid[] parentIDs, DeletionData deletionData)
  2544. {
  2545. if(parentIDs.Length == 0)
  2546. {
  2547. return;
  2548. }
  2549. if (_cascades.TryGetValue(type, out var cascades))
  2550. {
  2551. foreach (var cascade in cascades)
  2552. {
  2553. DeleteEntity(cascade.Item1, parentIDs, cascade.Item2, deletionData);
  2554. }
  2555. }
  2556. if(_setNulls.TryGetValue(type, out var setNulls))
  2557. {
  2558. foreach(var setNull in setNulls)
  2559. {
  2560. SetNullEntity(setNull.Item1, setNull.Item2, parentIDs, deletionData);
  2561. }
  2562. }
  2563. }
  2564. public void Delete<T>(T entity, string userID) where T : Entity, new()
  2565. {
  2566. if (!CanSave<T>())
  2567. {
  2568. return;
  2569. }
  2570. if (typeof(T).HasAttribute<UnrecoverableAttribute>())
  2571. {
  2572. Purge(entity);
  2573. var props = GetExternalProperties(typeof(T));
  2574. foreach(var prop in props)
  2575. {
  2576. DeleteExternalData(typeof(T), prop.Name, entity.ID);
  2577. }
  2578. }
  2579. else
  2580. {
  2581. entity = DoQuery(
  2582. new Filter<T>(x => x.ID).IsEqualTo(entity.ID),
  2583. DeletionData.DeletionColumns<T>(),
  2584. null,
  2585. int.MaxValue,
  2586. false,
  2587. false
  2588. ).Rows.First().ToObject<T>();
  2589. var deletionData = new DeletionData();
  2590. deletionData.DeleteEntity(entity);
  2591. CascadeDelete(typeof(T), new Guid[] { entity.ID }, deletionData);
  2592. var tableName = typeof(T).Name;
  2593. var deletion = new Deletion()
  2594. {
  2595. DeletionDate = DateTime.Now,
  2596. HeadTable = tableName,
  2597. Description = entity.ToString() ?? "",
  2598. DeletedBy = userID,
  2599. Data = Serialization.Serialize(deletionData)
  2600. };
  2601. OnSave(deletion);
  2602. Purge(entity);
  2603. }
  2604. }
  2605. public void Delete<T>(IEnumerable<T> entities, string userID) where T : Entity, new()
  2606. {
  2607. if (!CanSave<T>())
  2608. {
  2609. return;
  2610. }
  2611. entities = entities.AsIList();
  2612. if (!entities.Any())
  2613. return;
  2614. if (typeof(T).HasAttribute<UnrecoverableAttribute>())
  2615. {
  2616. Purge(entities);
  2617. var props = GetExternalProperties(typeof(T));
  2618. foreach(var prop in props)
  2619. {
  2620. foreach(var entity in entities)
  2621. {
  2622. DeleteExternalData(typeof(T), prop.Name, entity.ID);
  2623. }
  2624. }
  2625. }
  2626. else
  2627. {
  2628. var ids = entities.Select(x => x.ID).ToArray();
  2629. var entityList = Query(
  2630. new Filter<T>(x => x.ID).InList(ids),
  2631. DeletionData.DeletionColumns<T>()).Rows.Select(x => x.ToObject<T>()).ToList();
  2632. if (!entityList.Any())
  2633. return;
  2634. var deletionData = new DeletionData();
  2635. foreach (var entity in entityList)
  2636. {
  2637. deletionData.DeleteEntity(entity);
  2638. }
  2639. CascadeDelete(typeof(T), ids, deletionData);
  2640. var tableName = typeof(T).Name;
  2641. var deletion = new Deletion()
  2642. {
  2643. DeletionDate = DateTime.Now,
  2644. HeadTable = tableName,
  2645. Description = $"Deleted {entityList.Count} entries",
  2646. DeletedBy = userID,
  2647. Data = Serialization.Serialize(deletionData)
  2648. };
  2649. OnSave(deletion);
  2650. Purge(entities);
  2651. }
  2652. }
  2653. private void AddDeletionType(Type type, List<Type> deletions)
  2654. {
  2655. deletions.Add(type);
  2656. if (!_cascades.TryGetValue(type, out var cascades))
  2657. return;
  2658. foreach (var cascade in cascades)
  2659. {
  2660. AddDeletionType(cascade.Item1, deletions);
  2661. }
  2662. }
  2663. private Dictionary<Type, List<Type>> _allCascades = new();
  2664. private List<Type> GetDeletionTypes(Type type)
  2665. {
  2666. if(_allCascades.TryGetValue(type, out var cascades))
  2667. {
  2668. return cascades;
  2669. }
  2670. var deletionTypes = new List<Type>();
  2671. AddDeletionType(type, deletionTypes);
  2672. _allCascades[type] = deletionTypes;
  2673. return deletionTypes;
  2674. }
  2675. public void Purge(Deletion deletion)
  2676. {
  2677. if (!CanSave<Deletion>())
  2678. {
  2679. return;
  2680. }
  2681. var data = Serialization.Deserialize<DeletionData>(deletion.Data);
  2682. if(data is not null)
  2683. {
  2684. foreach(var (entityName, cascade) in data.Cascades)
  2685. {
  2686. if (!CoreUtils.TryGetEntity(entityName, out var entityType)) continue;
  2687. var props = GetExternalProperties(entityType);
  2688. foreach(var prop in props)
  2689. {
  2690. foreach(var entity in cascade.ToObjects(entityType).Cast<Entity>())
  2691. {
  2692. DeleteExternalData(entityType, prop.Name, entity.ID);
  2693. }
  2694. }
  2695. }
  2696. }
  2697. Purge<Deletion>(deletion);
  2698. }
  2699. public void Recover(Deletion deletion)
  2700. {
  2701. if (!CanSave<Deletion>())
  2702. {
  2703. return;
  2704. }
  2705. if (deletion.ID == Guid.Empty)
  2706. {
  2707. Logger.Send(LogType.Error, "", "Empty Deletion ID; Recovery cancelled");
  2708. return;
  2709. }
  2710. var data = Serialization.Deserialize<DeletionData>(deletion.Data);
  2711. if (data is null)
  2712. {
  2713. Logger.Send(LogType.Error, "", "Deletion Data deserialisation failed; Recovery cancelled");
  2714. return;
  2715. }
  2716. foreach (var (entityName, setNulls) in data.SetNulls)
  2717. {
  2718. if (!CoreUtils.TryGetEntity(entityName, out var entityType)) continue;
  2719. var saves = new List<Entity>();
  2720. foreach(var setNull in setNulls)
  2721. {
  2722. var row = DoQueryNonGeneric(
  2723. entityType,
  2724. Filter.Create<Entity>(entityType, x => x.ID).IsEqualTo(setNull.EntityID),
  2725. Columns.Create(entityType)
  2726. .Add<Entity>(x => x.ID)
  2727. .Add(setNull.Property),
  2728. null,
  2729. 1,
  2730. false,
  2731. false
  2732. ).Rows.FirstOrDefault();
  2733. if (row is null) continue;
  2734. var entity = (row.ToObject(entityType) as Entity)!;
  2735. CoreUtils.SetPropertyValue(entity, setNull.Property, setNull.ParentID);
  2736. saves.Add(entity);
  2737. }
  2738. OnSaveNonGeneric(entityType, saves);
  2739. }
  2740. foreach(var (entityName, cascade) in data.Cascades)
  2741. {
  2742. if (!CoreUtils.TryGetEntity(entityName, out var entityType)) continue;
  2743. OnSaveNonGeneric(entityType, cascade.ToObjects(entityType).Cast<Entity>());
  2744. }
  2745. Purge<Deletion>(deletion);
  2746. }
  2747. public List<Type> GetDeletionTypes(Deletion deletion)
  2748. {
  2749. var entityType = CoreUtils.Entities.First(x => x.Name == deletion.HeadTable);
  2750. return GetDeletionTypes(entityType);
  2751. }
  2752. #endregion
  2753. #endregion
  2754. #region External Data Storage
  2755. private string ExternalDataFolder(Type T, string columnName, string idString)
  2756. {
  2757. return Path.Combine(
  2758. Path.GetDirectoryName(URL) ?? "",
  2759. $"{Path.GetFileName(URL)}.data",
  2760. T.Name,
  2761. columnName,
  2762. idString.Substring(0, 2));
  2763. }
  2764. private byte[]? GetExternalData(Type T, string columnName, Guid id)
  2765. {
  2766. var idString = id.ToString();
  2767. var filename = Path.Combine(ExternalDataFolder(T, columnName, idString), idString);
  2768. try
  2769. {
  2770. return File.ReadAllBytes(filename);
  2771. }
  2772. catch(Exception e)
  2773. {
  2774. //Logger.Send(LogType.Error, "", $"Could not load external {T.Name}.{columnName}: {e.Message}");
  2775. return null;
  2776. }
  2777. }
  2778. private void SaveExternalData(Type T, string columnName, Guid id, byte[] data)
  2779. {
  2780. var idString = id.ToString();
  2781. var directory = ExternalDataFolder(T, columnName, idString);
  2782. Directory.CreateDirectory(directory);
  2783. var filename = Path.Combine(directory, idString);
  2784. File.WriteAllBytes(filename, data);
  2785. }
  2786. private void DeleteExternalData(Type T, string columnName, Guid id)
  2787. {
  2788. var idString = id.ToString();
  2789. var directory = ExternalDataFolder(T, columnName, idString);
  2790. Directory.CreateDirectory(directory);
  2791. var filename = Path.Combine(directory, idString);
  2792. if (File.Exists(filename))
  2793. {
  2794. File.Delete(filename);
  2795. }
  2796. }
  2797. #endregion
  2798. }
  2799. }