SQLiteProvider.cs 125 KB

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