SQLiteProvider.cs 124 KB

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