SQLiteProvider.cs 137 KB

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