SQLiteProvider.cs 119 KB

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