SQLiteProvider.cs 128 KB

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