SQLiteProvider.cs 129 KB

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