123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797179817991800180118021803180418051806180718081809181018111812181318141815181618171818181918201821182218231824182518261827182818291830183118321833183418351836183718381839184018411842184318441845184618471848184918501851185218531854185518561857185818591860186118621863186418651866186718681869187018711872187318741875187618771878187918801881188218831884188518861887188818891890189118921893189418951896189718981899190019011902190319041905190619071908190919101911191219131914191519161917191819191920192119221923192419251926192719281929193019311932193319341935193619371938193919401941194219431944194519461947194819491950195119521953195419551956195719581959196019611962196319641965196619671968196919701971197219731974197519761977197819791980198119821983198419851986198719881989199019911992199319941995199619971998199920002001200220032004200520062007200820092010201120122013201420152016201720182019202020212022202320242025202620272028202920302031203220332034203520362037203820392040204120422043204420452046204720482049205020512052205320542055205620572058205920602061206220632064206520662067206820692070207120722073207420752076207720782079208020812082208320842085208620872088208920902091209220932094209520962097209820992100210121022103210421052106210721082109211021112112211321142115211621172118211921202121212221232124212521262127212821292130213121322133213421352136213721382139214021412142214321442145214621472148214921502151215221532154215521562157215821592160216121622163216421652166216721682169217021712172217321742175217621772178217921802181218221832184218521862187218821892190219121922193219421952196219721982199220022012202220322042205220622072208220922102211221222132214221522162217221822192220222122222223222422252226222722282229223022312232223322342235223622372238223922402241224222432244224522462247224822492250225122522253225422552256225722582259226022612262226322642265226622672268226922702271227222732274227522762277227822792280228122822283228422852286228722882289229022912292229322942295229622972298229923002301230223032304230523062307230823092310231123122313231423152316231723182319232023212322232323242325232623272328232923302331233223332334233523362337233823392340234123422343234423452346234723482349235023512352235323542355235623572358235923602361236223632364236523662367236823692370237123722373237423752376237723782379238023812382238323842385238623872388238923902391239223932394239523962397239823992400240124022403240424052406240724082409241024112412241324142415241624172418241924202421242224232424242524262427242824292430243124322433243424352436243724382439244024412442244324442445244624472448244924502451245224532454245524562457245824592460246124622463246424652466246724682469247024712472247324742475247624772478247924802481248224832484248524862487248824892490249124922493249424952496249724982499250025012502250325042505250625072508250925102511251225132514251525162517251825192520252125222523252425252526252725282529253025312532253325342535253625372538253925402541254225432544254525462547254825492550255125522553255425552556255725582559256025612562256325642565256625672568256925702571257225732574257525762577257825792580258125822583258425852586258725882589259025912592259325942595259625972598259926002601260226032604260526062607260826092610261126122613261426152616261726182619262026212622262326242625262626272628262926302631263226332634263526362637263826392640264126422643264426452646264726482649265026512652265326542655265626572658265926602661266226632664266526662667266826692670267126722673267426752676267726782679268026812682268326842685268626872688268926902691269226932694269526962697269826992700270127022703270427052706270727082709271027112712271327142715271627172718271927202721272227232724272527262727272827292730273127322733273427352736273727382739274027412742274327442745274627472748274927502751275227532754275527562757275827592760276127622763276427652766276727682769277027712772277327742775277627772778277927802781278227832784278527862787278827892790279127922793279427952796279727982799280028012802280328042805280628072808280928102811281228132814281528162817281828192820282128222823282428252826282728282829283028312832283328342835283628372838283928402841284228432844284528462847284828492850285128522853285428552856285728582859286028612862286328642865286628672868286928702871287228732874287528762877287828792880288128822883288428852886288728882889289028912892289328942895289628972898289929002901290229032904290529062907290829092910291129122913291429152916291729182919292029212922292329242925292629272928292929302931293229332934293529362937293829392940294129422943294429452946294729482949295029512952295329542955295629572958295929602961296229632964296529662967296829692970297129722973297429752976297729782979298029812982298329842985298629872988298929902991299229932994299529962997299829993000300130023003300430053006300730083009301030113012301330143015301630173018301930203021302230233024302530263027302830293030303130323033303430353036303730383039304030413042304330443045304630473048304930503051305230533054305530563057305830593060306130623063306430653066306730683069307030713072307330743075307630773078307930803081308230833084308530863087308830893090309130923093309430953096309730983099310031013102310331043105310631073108310931103111311231133114311531163117311831193120312131223123312431253126312731283129313031313132313331343135313631373138313931403141314231433144314531463147314831493150315131523153315431553156315731583159316031613162316331643165316631673168316931703171317231733174317531763177317831793180318131823183318431853186318731883189319031913192319331943195319631973198319932003201320232033204320532063207320832093210321132123213321432153216321732183219322032213222322332243225322632273228322932303231323232333234323532363237323832393240324132423243324432453246324732483249325032513252325332543255325632573258325932603261326232633264326532663267326832693270327132723273327432753276327732783279 |
- using System.Collections;
- using System.Data;
- using System.Data.SQLite;
- using System.Diagnostics.CodeAnalysis;
- using System.Linq.Expressions;
- using System.Reflection;
- using System.Runtime.Serialization.Formatters.Binary;
- using System.Text;
- using InABox.Core;
- using Microsoft.CodeAnalysis;
- using NPOI.POIFS.FileSystem;
- namespace InABox.Database.SQLite
- {
- internal abstract class SQLiteAccessor : IDisposable
- {
- public SQLiteConnection Connection { get; private set; }
- protected static int nConnections = 0;
- public void Dispose()
- {
- Connection?.Close();
- --nConnections;
- }
- public SQLiteCommand CreateCommand()
- {
- return new SQLiteCommand(Connection);
- }
- protected void Setup(string url)
- {
- var sb = new SQLiteConnectionStringBuilder();
- sb.DataSource = url;
- sb.Version = 3;
- sb.DateTimeFormat = SQLiteDateFormats.Ticks;
- sb.JournalMode = SQLiteJournalModeEnum.Wal;
-
- var conn = sb.ToString();
- Connection = new SQLiteConnection(conn);
- Connection.BusyTimeout = Convert.ToInt32(TimeSpan.FromMinutes(2).TotalMilliseconds);
- Connection.Open();
- Connection.SetLimitOption(SQLiteLimitOpsEnum.SQLITE_LIMIT_VARIABLE_NUMBER, 10000);
- ++nConnections;
- }
- }
- internal class SQLiteReadAccessor : SQLiteAccessor
- {
- public SQLiteReadAccessor(string url)
- {
- Setup(url);
- }
- }
- internal class SQLiteWriteAccessor : SQLiteAccessor
- {
- private static readonly object writelock = new();
- public SQLiteWriteAccessor(string url)
- {
- if (Monitor.TryEnter(writelock, new TimeSpan(0, 1, 0)))
- try
- {
- Setup(url);
- }
- catch(Exception e)
- {
- throw new Exception($"Exception while acquiring write lock! {nConnections} connections open.", e);
- }
- finally
- {
- Monitor.Exit(writelock);
- }
- if (Connection == null)
- throw new Exception($"Timeout while aqcuiring write lock! {nConnections} connections open.");
- }
- public SQLiteTransaction BeginTransaction()
- {
- return Connection.BeginTransaction();
- }
- }
- public class SQLiteProvider : IProvider
- {
- private static object writelock = new();
- private bool bForceRebuild;
- private bool RebuildTriggers = false;
- private SQLiteProvider()
- {
- }
- public SQLiteProvider(string filename)
- {
- var path = Path.GetDirectoryName(filename);
- if (!Directory.Exists(path))
- Directory.CreateDirectory(path);
- URL = filename;
- }
- public string URL { get; set; }
- public event LogEvent? OnLog;
- public Type[] Types { get; set; }
- public void Start()
- {
- var chkfile = Path.ChangeExtension(URL, "chk");
- bForceRebuild = File.Exists(chkfile);
- if (!File.Exists(URL))
- {
- SQLiteConnection.CreateFile(URL);
- }
- else if (bForceRebuild)
- {
- var i = 0;
- while (File.Exists(Path.ChangeExtension(URL, string.Format("{0:D3}", i))))
- i++;
- File.Copy(URL, Path.ChangeExtension(URL, string.Format("{0:D3}", i)));
- }
- using (var access = GetWriteAccess())
- {
- ExecuteSQL(access, "PRAGMA journal_mode=WAL;");
- }
- //using (var access = GetReadAccess())
- //{
- // using (var command = access.CreateCommand())
- // {
- // command.CommandText = "PRAGMA compile_options";
- // using (var reader = command.ExecuteReader())
- // {
- // while (reader.Read())
- // OnLog?.Invoke(LogType.Information,String.Format("{0}",reader.GetValue(0)));
- // }
- // }
- //}
- //ExecuteSQL("PRAGMA foreign_keys = on;");
- // Need to arrange the typelist to ensure that foreign keys
- // refer to tables that already exist
- var ordered = new List<Type>();
- foreach (var type in Types)
- LoadType(type, ordered);
- //Load up the metadata
- var metadata = LoadMetaData();
-
- var table = typeof(CustomProperty).EntityName().Split('.').Last();
- if (!metadata.ContainsKey(table))
- {
- OnLog?.Invoke(LogType.Information, "Creating Table: " + typeof(CustomProperty).EntityName().Split('.').Last());
- using (var access = GetWriteAccess())
- {
- CreateTable(access, typeof(CustomProperty), true, new CustomProperty[] { });
- }
- }
- else
- {
- using (var access = GetWriteAccess())
- {
- CheckFields(access, typeof(CustomProperty), metadata[table].Item1, new CustomProperty[] { });
- }
- }
- var customproperties = Load<CustomProperty>(); // new Filter<CustomProperty>(x => x.Class).IsEqualTo(type.EntityName()))
- metadata = LoadMetaData();
- foreach (var type in ordered)
- {
- if (type.GetCustomAttribute<AutoEntity>() == null)
- {
- table = type.EntityName().Split('.').Last();
- if (!metadata.ContainsKey(table))
- {
- OnLog?.Invoke(LogType.Information, "Creating Table: " + type.EntityName().Split('.').Last());
- using (var access = GetWriteAccess())
- {
- CreateTable(access, type, true, customproperties);
- }
- }
- }
- }
- metadata = LoadMetaData();
- foreach (var type in ordered)
- {
- if (type.GetCustomAttribute<AutoEntity>() == null)
- {
- table = type.EntityName().Split('.').Last();
- using (var access = GetWriteAccess())
- {
- CheckFields(access, type, metadata[table].Item1, customproperties);
- }
- }
- }
- metadata = LoadMetaData();
- foreach (var type in ordered)
- {
- if (type.GetCustomAttribute<AutoEntity>() == null)
- {
- LoadDeletions(type);
- table = type.EntityName().Split('.').Last();
- using (var access = GetWriteAccess())
- {
- CheckTriggers(access, type, metadata[table].Item2);
- }
- }
- }
- metadata = LoadMetaData();
- foreach (var type in ordered)
- {
- if (type.GetCustomAttribute<AutoEntity>() == null)
- {
- table = type.EntityName().Split('.').Last();
- using (var access = GetWriteAccess())
- {
- CheckIndexes(access, type, metadata[table].Item3);
- }
- }
- }
- metadata = LoadMetaData();
-
- foreach (var type in ordered)
- {
- if (type.GetCustomAttribute<AutoEntity>() != null)
- {
- table = type.EntityName().Split('.').Last();
- if (!metadata.ContainsKey(table))
- {
- OnLog?.Invoke(LogType.Information, "Creating Table: " + type.EntityName().Split('.').Last());
- using (var access = GetWriteAccess())
- {
- CreateTable(access, type, true, customproperties);
- }
- }
- else
- {
- using (var access = GetWriteAccess())
- {
- CheckFields(access, type, metadata[table].Item1, customproperties);
- }
- }
- }
- }
- if (bForceRebuild)
- {
- using (var access = GetWriteAccess())
- {
- ExecuteSQL(access, "VACUUM;");
- }
- File.Delete(chkfile);
- }
- }
- public bool IsRelational()
- {
- return true;
- }
- private void Connection_Trace(object sender, TraceEventArgs e)
- {
- var type = LogType.Query;
- string[] updates = { "INSERT ", "UPDATE ", "DELETE ", "ALTER ", "DROP ", "CREATE ", "VACUUM" };
- foreach (var update in updates)
- type = e.Statement.ToUpper().StartsWith(update) ? LogType.Update : type;
- OnLog?.Invoke(type, e.Statement + ";");
- }
- private SQLiteReadAccessor GetReadAccess()
- {
- var result = new SQLiteReadAccessor(URL);
- result.Connection.Trace += Connection_Trace;
- return result;
- }
- private SQLiteWriteAccessor GetWriteAccess()
- {
- var result = new SQLiteWriteAccessor(URL);
- result.Connection.Trace += Connection_Trace;
- return result;
- }
- private int ExecuteSQL(SQLiteWriteAccessor writer, string statement)
- {
- var result = 0;
- try
- {
- using (var command = writer.CreateCommand())
- {
- command.CommandText = statement;
- result = command.ExecuteNonQuery();
- }
- }
- catch (Exception)
- {
- throw;
- }
- return result;
- }
- #region Database Structure Management
- private class MetadataEntry
- {
- public String Name { get; set; }
- public String SQL { get; set; }
- }
- private class Metadata
- {
- public List<MetadataEntry> Tables { get; init; }
- public List<MetadataEntry> Views { get; init; }
- public List<MetadataEntry> Indexes { get; init; }
- public List<MetadataEntry> Triggers { get; init; }
- public Metadata() : base()
- {
- Tables = new List<MetadataEntry>();
- Views = new List<MetadataEntry>();
- Indexes = new List<MetadataEntry>();
- Triggers = new List<MetadataEntry>();
- }
- }
-
- private Dictionary<string, Tuple<Dictionary<string, string>, Dictionary<string, string>, Dictionary<string, string>>> LoadMetaData()
- {
- // Key is table name
- // Item1 = Field Name / Field Type map
- // Item2 = Trigger Name / Trigger SQL
- // Item3 = Index Name / Index SQL
- // Item4 = View Name /View SQL
- var metadata = new Dictionary<string, Tuple<Dictionary<string, string>, Dictionary<string, string>, Dictionary<string, string>>>();
- using (var access = GetReadAccess())
- {
- using (var command = access.CreateCommand())
- {
- command.CommandText = "SELECT tbl_name, sql, type FROM sqlite_master WHERE type='table' or type='view' ORDER BY type;";
- using (var reader = command.ExecuteReader())
- {
- if (reader.HasRows)
- while (reader.Read())
- {
- var tblinfo = new Tuple<Dictionary<string, string>, Dictionary<string, string>, Dictionary<string, string>>(
- new Dictionary<string, string>(), new Dictionary<string, string>(), new Dictionary<string, string>());
- var table = reader.GetString(0);
- var sql = reader.GetString(1);
- bool istable = String.Equals(reader.GetString(2),"table");
- if (istable)
- {
- sql = sql.Replace("\"", "")
- .Replace(string.Format("CREATE TABLE {0} (", table), "");
- sql = sql.Remove(sql.Length - 1).Trim();
- var fields = sql.Replace("\n\t", "").Replace("\t", " ").Replace("\"", "").Trim().Split(',');
- var primarykey = "";
- foreach (var fld in fields)
- {
- var field = fld.Trim().Replace("\t", " ").Replace("\"", "").Replace("[", "").Replace("]", "");
- //if (field.ToUpper().StartsWith("CONSTRAINT"))
- // tblinfo.Item2.Add(field);
- if (field.ToUpper().StartsWith("PRIMARY KEY"))
- {
- primarykey = field.Replace("PRIMARY KEY(", "").Replace(")", "");
- }
- else
- {
- var comps = field.Split(' ');
- tblinfo.Item1[comps[0]] = string.Format("{0}{1}", comps[1],
- field.Contains("PRIMARY KEY") ? " PRIMARY KEY" : "");
- }
- }
- if (!string.IsNullOrEmpty(primarykey))
- {
- var pkfld = tblinfo.Item1[primarykey];
- if (!pkfld.ToUpper().Contains("PRIMARY KEY"))
- tblinfo.Item1[primarykey] = string.Format("{0} PRIMARY KEY", pkfld.Trim());
- }
- }
- else
- {
- sql = sql.Replace("\"", "")
- .Replace("DISTINCT ", "");
- sql = sql.Split(new String[] { " AS SELECT " }, StringSplitOptions.TrimEntries).Last();
- sql = sql.Split(new String[] { " FROM " }, StringSplitOptions.TrimEntries).First();
- var fields = sql.Replace("\n\t", "").Replace("\t", " ").Replace("\"", "").Trim().Split(',');
- foreach (var fld in fields)
- {
- var field = fld.Trim()
- .Replace("\t", " ")
- .Replace("\"", "")
- .Replace("[", "").Replace("]", "");
- var parts = field.Split(" as ");
- if(parts.Length == 1)
- {
- tblinfo.Item1[field] = "";
- }
- else if(parts.Length == 2)
- {
- field = parts[1];
- if (parts[0] != "NULL")
- {
- tblinfo.Item1[field] = "";
- }
- }
- }
- }
- metadata[table] = tblinfo;
- }
- reader.Close();
- }
- // Now Load Up all the indexes
- command.CommandText = "select name, tbl_name, sql from sqlite_master where type='index' and sql is not null;";
- using (var reader = command.ExecuteReader())
- {
- if (reader.HasRows)
- while (reader.Read())
- {
- var name = reader.GetString(0);
- var table = reader.GetString(1);
- if (metadata.ContainsKey(table))
- {
- var tblInfo = metadata[table];
- var sql = reader.GetString(2);
- tblInfo.Item3[name] = sql;
- }
- }
- }
- // Now Load Up all the triggers
- command.CommandText = "select name, tbl_name, sql from sqlite_master where type='trigger';";
- using (var reader = command.ExecuteReader())
- {
- if (reader.HasRows)
- while (reader.Read())
- {
- var name = reader.GetString(0);
- var table = reader.GetString(1);
- if (metadata.ContainsKey(table))
- {
- var tblInfo = metadata[table];
- var sql = reader.GetString(2);
- tblInfo.Item2[name] = sql;
- }
- }
- }
- }
- }
- return metadata;
- }
- private static void LoadType(Type type, List<Type> into)
- {
- if (into.Contains(type))
- return;
- var props = type.GetProperties().Where(x => x.PropertyType.GetInterfaces().Contains(typeof(IEntityLink)));
- if (!props.Any())
- {
- into.Insert(0, type);
- }
- else
- {
- into.Add(type);
- foreach (var prop in props)
- {
- var subtype = prop.PropertyType.BaseType;
- while (subtype != null && !subtype.GetGenericArguments().Any())
- subtype = subtype.BaseType;
- subtype = subtype?.GetGenericArguments().FirstOrDefault();
- if (subtype != null && subtype != type)
- LoadType(subtype, into);
- }
- }
- }
- private string ColumnName(params PropertyInfo[] properties)
- {
- var bits = new List<string>();
- foreach (var property in properties)
- bits.Add(property.Name);
- var result = string.Join(".", bits);
- return result;
- }
- private string ColumnType(Type type)
- {
- if (type.IsOrdinal())
- return "INT";
- if (type.IsEnum)
- return "TEXT";
- if (type == typeof(DateTime))
- return "TEXT";
- if (type == typeof(TimeSpan))
- return "NUM";
- if (type == typeof(string[]))
- return "BLOB";
- if (type == typeof(byte[]))
- return "BLOB";
- if (type.IsFloatingPoint())
- return "NUM";
- if (type.GetInterfaces().Contains(typeof(IPackable)))
- return "BLOB";
- return "TEXT";
- }
- private void LoadFields(Type type, Dictionary<string, string> fields, List<PropertyInfo>? prefixes, CustomProperty[] customproperties)
- {
- if (prefixes == null)
- prefixes = new List<PropertyInfo>();
-
- AutoEntity? view = type.GetCustomAttribute<AutoEntity>();
- Type definition = view?.Generator != null
- ? view.Generator.Definition
- : type;
-
- var properties = CoreUtils.GetInheritedProperties(definition).Where(x =>
- x.GetCustomAttribute<DoNotPersist>() == null
- // We think this is wrong; but who knows? && x.GetCustomAttribute<DoNotSerialize>() == null
- && x.GetCustomAttributes().FirstOrDefault(a => a.GetType().Equals(typeof(AggregateAttribute))) == null
- && x.GetCustomAttributes().FirstOrDefault(a => a.GetType().Equals(typeof(FormulaAttribute))) == null
- && x.GetCustomAttributes().FirstOrDefault(a => a.GetType().Equals(typeof(ConditionAttribute))) == null
- && x.GetCustomAttributes().FirstOrDefault(a => a.GetType().Equals(typeof(ChildEntityAttribute))) == null
- && x.CanWrite
- && x.PropertyType != typeof(UserProperties)
- );
- foreach (var property in properties)
- if (property.PropertyType.GetInterfaces().Contains(typeof(IEnclosedEntity)))
- {
- LoadFields(property.PropertyType, fields, prefixes.Concat(new[] { property }).ToList(), customproperties);
- }
- else if (property.PropertyType.GetInterfaces().Contains(typeof(IEntityLink)))
- {
- var subprop = property.PropertyType.GetProperty("ID")!; // Not-null because IEntityLink has ID
- var subname = ColumnName(prefixes.Concat(new[] { property, subprop }).ToArray());
- var subtype = ColumnType(subprop.PropertyType);
- fields[subname] = subtype;
- }
- else
- {
- var colname = ColumnName(prefixes.Concat(new[] { property }).ToArray());
- var coltype = ColumnType(property.PropertyType);
- fields[colname] = string.Format("{0}{1}", coltype, colname.Equals("ID") ? " PRIMARY KEY" : "");
- }
- // Now add in the Custom Properties (if any exist)
- //CustomProperty[] customprops = Load<CustomProperty>(new Filter<CustomProperty>(x=>x.Class).IsEqualTo(type.EntityName()));
- foreach (var prop in customproperties.Where(x => x.Class.Equals(type.EntityName())))
- fields[prop.Name] = ColumnType(prop.PropertyType);
- }
- private List<string> LoadIndexes(Type type)
- {
- var result = new List<string>();
- var properties = type.GetProperties().Where(x =>
- x.GetCustomAttribute<DoNotSerialize>() == null && x.GetCustomAttribute<DoNotPersist>() == null &&
- x.GetCustomAttributes().FirstOrDefault(a => a.GetType().IsSubclassOf(typeof(AggregateAttribute))) == null
- && x.GetCustomAttributes().FirstOrDefault(a => a.GetType().Equals(typeof(ChildEntityAttribute))) == null
- && x.CanWrite);
- foreach (var property in properties)
- if (property.PropertyType.GetInterfaces().Contains(typeof(IEntityLink)))
- {
- var subprop = property.PropertyType.GetProperty("ID")!; // Not-null because IEntityLink has ID
- var subname = ColumnName(property, subprop);
- var tablename = type.EntityName().Split('.').Last();
- result.Add(string.Format("CREATE INDEX idx{0}{1} ON {0} ([{2}])", tablename, subname.Replace(".", ""), subname));
- }
- else
- {
- var index = property.GetCustomAttributes<SecondaryIndexAttribute>().FirstOrDefault();
- if (index != null)
- {
- var colname = ColumnName(property);
- var tablename = type.EntityName().Split('.').Last();
- result.Add(string.Format("CREATE INDEX idx{0}{1} ON {0} ([{2}])", tablename, colname.Replace(".", ""), colname));
- }
- }
- return result;
- }
- private List<string> LoadTriggers(Type type)
- {
- var result = new List<string>();
- // Get the EntityLink that is associated with this class
- var linkclass = CoreUtils.TypeList(
- new[] { type.Assembly },
- x => typeof(IEntityLink).GetTypeInfo().IsAssignableFrom(x) && x.GetInheritedGenericTypeArguments().FirstOrDefault() == type
- ).FirstOrDefault();
- // if The entitylink does not exist, we don't need to do anything
- if (linkclass == null)
- return result;
- var actions = new List<string>();
- var childtypes = Types.Where(x => /* (x != type) && */ x.IsSubclassOf(typeof(Entity)) && x.GetCustomAttribute<AutoEntity>() == null);
- foreach (var childtype in childtypes)
- {
- // Get all registererd types for this entitylink
- var fields = new List<string>();
- var bDelete = false;
- var tablename = childtype.EntityName().Split('.').Last();
- // Find any IEntityLink<> properties that refer back to this class
- var childprops = CoreUtils.PropertyList(childtype, x => x.PropertyType == linkclass);
- foreach (var childprop in childprops)
- {
- var fieldname = string.Format("[{0}.ID]", childprop.Name);
- var attr = childprop.GetCustomAttributes(typeof(EntityRelationshipAttribute), true).FirstOrDefault();
- if (attr != null && ((EntityRelationshipAttribute)attr).Action.Equals(DeleteAction.Cascade))
- {
- fields.Clear();
- bDelete = true;
- fields.Add(fieldname);
- break;
- }
- fields.Add(fieldname);
- //actions[childtype] = String.Format("UPDATE {1} SET [{0}.ID] = NULL WHERE [id] = old.[ID];", tablename)
- }
- if (fields.Any())
- {
- if (bDelete)
- actions.Add(string.Format("DELETE FROM {0} WHERE {1} = old.ID;", tablename, fields.First()));
- else
- foreach (var field in fields)
- actions.Add(string.Format("UPDATE {0} SET {1} = NULL WHERE {1} = old.ID;", tablename, field));
- }
- }
- if (actions.Any())
- result.Add(string.Format("CREATE TRIGGER {0}_BEFOREDELETE BEFORE DELETE ON {0} FOR EACH ROW BEGIN {1} END",
- type.EntityName().Split('.').Last(), string.Join(" ", actions)));
- return result;
- }
- public void ForceRecreateViews()
- {
- var ordered = new List<Type>();
- foreach (var type in Types)
- LoadType(type, ordered);
- var customproperties = Load<CustomProperty>();
- var metadata = LoadMetaData();
- foreach (var type in ordered)
- {
- var view = type.GetCustomAttribute<AutoEntity>();
- if (view?.Generator != null)
- {
- var table = type.EntityName().Split('.').Last();
- if (!metadata.ContainsKey(table))
- {
- OnLog?.Invoke(LogType.Information, "Creating Table: " + type.EntityName().Split('.').Last());
- using (var access = GetWriteAccess())
- {
- CreateTable(access, type, true, customproperties);
- }
- }
- else
- {
- var type_fields = new Dictionary<string, string>();
- LoadFields(view.Generator.Definition, type_fields, null, customproperties);
- using (var access = GetWriteAccess())
- {
- RebuildTable(access, type, metadata[table].Item1, type_fields, customproperties);
- }
- }
- }
- }
- }
- private Dictionary<string, object?> CheckDefaultColumns(IAutoEntityGenerator generator)
- {
- var viewfields = new Dictionary<string, string>();
- LoadFields(generator.Definition, viewfields, null, new CustomProperty[] { });
- Dictionary<String, object?> result = new Dictionary<string, object?>();
- if (!viewfields.ContainsKey("ID"))
- result["ID"] = null;
- if (!viewfields.ContainsKey("Created"))
- result["Created"] = null;
- if (!viewfields.ContainsKey("CreatedBy"))
- result["CreatedBy"] = null;
- if (!viewfields.ContainsKey("LastUpdate"))
- result["LastUpdate"] = null;
- if (!viewfields.ContainsKey("LastUpdateBy"))
- result["LastUpdateBy"] = null;
- return result;
- }
-
- private void CreateTable(SQLiteWriteAccessor access, Type type, bool includeconstraints, CustomProperty[] customproperties)
- {
- var tablename = type.EntityName().Split('.').Last();
- var ddl = new List<string>();
- var view = type.GetCustomAttribute<AutoEntity>();
- if (view != null)
- {
- using (var command = access.CreateCommand())
- {
- command.CommandText = $"select name from sqlite_master where type='view' and name='{tablename}';";
- using (var reader = command.ExecuteReader())
- {
- if (reader.HasRows)
- while (reader.Read())
- ExecuteSQL(access,string.Format("DROP VIEW {0}", reader.GetString(0)));
- }
- }
- ddl.Add("CREATE VIEW");
- ddl.Add(type.EntityName().Split('.').Last());
- ddl.Add("AS");
-
- if (view.Generator is IAutoEntityUnionGenerator union)
- {
- List<String> queries = new List<String>();
- foreach (var table in union.Tables)
- {
- var columns = Columns.Create(table.Entity);
- var constants = CheckDefaultColumns(union);
-
- var interfacefields = new Dictionary<string, string>();
- LoadFields(union.Definition, interfacefields, null, new CustomProperty[] { });
-
- var entityfields = new Dictionary<string, string>();
- LoadFields(table.Entity, entityfields, null, new CustomProperty[] { });
- foreach (var field in interfacefields.Keys)
- {
- if (entityfields.ContainsKey(field))
- columns.Add(field);
- else
- {
- var constant = table.Constants.FirstOrDefault(x => String.Equals(x.Mapping.Property, field));
- if (constant != null)
- constants[field] = constant.Value;
- else
- constants[field] = null;
- }
- }
- var query = PrepareSelectNonGeneric(table.Entity, new SQLiteCommand(), 'A',
- table.Filter, columns, null,
- null, constants, int.MaxValue, union.Distinct, false);
-
- queries.Add(query);
- }
- // var viewfields = new Dictionary<string, string>();
- //LoadFields(union.Definition, viewfields, null, new CustomProperty[] { });
- // var fields = viewfields.Keys.Select(x => String.Format("[{0}]", x)).ToList();
- // if (!fields.Contains("[ID]"))
- // fields.Add(String.Format("NULL as [ID]"));
- // if (!fields.Contains("[Created]"))
- // fields.Add(String.Format("NULL as [Created]"));
- // if (!fields.Contains("[CreatedBy]"))
- // fields.Add(String.Format("NULL as [CreatedBy]"));
- // if (!fields.Contains("[LastUpdate]"))
- // fields.Add(String.Format("NULL as [LastUpdate]"));
- // if (!fields.Contains("[LastUpdateBy]"))
- // fields.Add(String.Format("NULL as [LastUpdateBy]"));
- //
- // List<String> queries = new List<String>();
- // foreach (var entity in union.Entities)
- // queries.Add(String.Format("SELECT {0} {1} FROM {2}",
- // union.Distinct ? "DISTINCT" : "",
- // String.Join(", ", fields),
- // entity.EntityName().Split('.').Last())
- // );
- ddl.Add(String.Join(" UNION ", queries));
- }
- else if ( view.Generator is IAutoEntityCrossGenerator cross)
- {
- List<String> constants = new List<string>();
- foreach (var constant in CheckDefaultColumns(cross))
- constants.Add($"{EscapeValue(constant.Value)} as [{constant.Key}]");
-
- String query = String.Format(
- "SELECT {0} {1}.[{2}] as [{3}], {4}.[{5}] as [{6}], {7} FROM {1}, {4} WHERE {1}.[{8}] = {4}.[{9}]",
- cross.Distinct ? "DISTINCT" : "",
- cross.LeftEntity(),
- cross.LeftProperty(),
- cross.LeftMapping(),
- cross.RightEntity(),
- cross.RightProperty(),
- cross.RightMapping(),
- String.Join(", ", constants),
- cross.LeftLink(),
- cross.RightLink()
- );
-
- ddl.Add(query);
- }
- else if ( view.Generator is IAutoEntityCartesianGenerator cartesian)
- {
- List<String> fields = new List<string>();
- List<String> tables = new List<String>();
- List<String> filters = new List<String>();
- int iTable = 0;
- foreach (var table in cartesian.Tables)
- {
- var subQueryText = PrepareSelectNonGeneric(table.Type, new SQLiteCommand(), 'A',
- table.Filter, table.Columns, null,
- null, null, int.MaxValue, cartesian.Distinct, false);
-
- tables.Add($"({subQueryText}) T{iTable}");
-
- foreach (var mapping in table.Mappings)
- fields.Add($"T{iTable}.[{mapping.Column.Property}] as [{mapping.Mapping.Property}]");
-
- iTable++;
- }
- foreach (var constant in cartesian.Constants)
- fields.Add($"{EscapeValue(constant.Constant)} as [{constant.Mapping.Property}]");
-
- foreach (var constant in CheckDefaultColumns(cartesian))
- fields.Add($"{EscapeValue(constant.Value)} as [{constant.Key}]");
- StringBuilder sb = new StringBuilder();
- sb.Append("SELECT ");
- sb.Append(String.Join(", ", fields));
- sb.Append(" FROM ");
- sb.Append(String.Join(", ", tables));
- if (filters.Any())
- sb.Append($" WHERE {String.Join(" AND ", filters)}");
- ddl.Add(sb.ToString());
- }
- ddl.Add(";");
- var viewstatement = string.Join(" ", ddl);
- }
- else
- {
- ddl.Add("CREATE TABLE");
- ddl.Add(type.EntityName().Split('.').Last());
- ddl.Add("(");
- var fields = new Dictionary<string, string>();
- var constraints = new List<string>();
- var indexes = new List<string>();
- LoadFields(type, fields, null, customproperties);
- var defs = new List<string>();
- foreach (var key in fields.Keys)
- defs.Add(string.Format("[{0}] {1}", key, fields[key]));
- if (includeconstraints)
- defs.AddRange(constraints);
- ddl.Add(string.Join(", ", defs));
- ddl.Add(");");
- }
- var statement = string.Join(" ", ddl);
- try
- {
- ExecuteSQL(access, statement);
- }
- catch (Exception e)
- {
- OnLog?.Invoke(LogType.Error, "Unable to Create Table: " + e.Message);
- throw;
- }
- }
- private void RebuildTable(SQLiteWriteAccessor access, Type type, Dictionary<string, string> table_fields,
- Dictionary<string, string> type_fields,
- CustomProperty[] customproperties)
- {
- var table = type.EntityName().Split('.').Last();
- if (type.GetCustomAttribute<AutoEntity>() != null)
- {
- OnLog?.Invoke(LogType.Information, "Recreating View: " + table);
- try
- {
- String drop = "";
- using (var command = access.CreateCommand())
- {
-
- command.CommandText =
- $"select name from sqlite_master where type='trigger' and tbl_name='{table}' and sql is not null;";
- using (var reader = command.ExecuteReader())
- {
- if (reader.HasRows)
- while (reader.Read())
- ExecuteSQL(access,string.Format("DROP TRIGGER {0}", reader.GetString(0)));
- }
-
- command.CommandText = "select type from sqlite_master where name='" + table + "' and sql is not null;";
- using (var reader = command.ExecuteReader())
- {
- if (reader.HasRows)
- while (reader.Read())
- drop = reader.GetString(0).ToUpper();
- }
- }
- if (!String.IsNullOrWhiteSpace(drop))
- ExecuteSQL(access, string.Format("DROP {0} {1};", drop, table));
- CreateTable(access, type, true, customproperties);
- }
- catch (Exception e)
- {
- OnLog?.Invoke(LogType.Error, string.Format("RebuildTable({0}) [VIEW] failed: {1}\n{2}", table, e.Message, e.StackTrace));
- throw;
- }
- }
- else
- {
-
- OnLog?.Invoke(LogType.Information, "Rebuilding Table: " + table);
- try
- {
-
- ExecuteSQL(access, "PRAGMA foreign_keys = off;");
-
- // using (var command = access.CreateCommand())
- // {
- //
- // command.CommandText =
- // $"select name from sqlite_master where type='trigger' and tbl_name='{table}' and sql is not null;";
- // using (var reader = command.ExecuteReader())
- // {
- // if (reader.HasRows)
- // while (reader.Read())
- // ExecuteSQL(access,string.Format("DROP TRIGGER {0}", reader.GetString(0)));
- // }
- // command.CommandText = $"select name from sqlite_master where type='view' and name='{table}';";
- // using (var reader = command.ExecuteReader())
- // {
- // if (reader.HasRows)
- // while (reader.Read())
- // ExecuteSQL(access,string.Format("DROP VIEW {0}", reader.GetString(0)));
- // }
- // }
-
- using (var transaction = access.Connection.BeginTransaction())
- {
- var drops = new List<string>();
- using (var command = access.CreateCommand())
- {
-
- //command.CommandText = String.Format("select name from sqlite_master where type='trigger' and tbl_name='{0}' and sql is not null;", table);
- command.CommandText = "select name from sqlite_master where type='view' and sql is not null;";
- using (var reader = command.ExecuteReader())
- {
- if (reader.HasRows)
- while (reader.Read())
- drops.Add(string.Format("DROP VIEW {0}", reader.GetString(0)));
- }
-
- //command.CommandText = String.Format("select name from sqlite_master where type='trigger' and tbl_name='{0}' and sql is not null;", table);
- command.CommandText = "select name from sqlite_master where type='trigger' and sql is not null;";
- using (var reader = command.ExecuteReader())
- {
- if (reader.HasRows)
- while (reader.Read())
- drops.Add(string.Format("DROP TRIGGER {0}", reader.GetString(0)));
- }
- command.CommandText = string.Format(
- "select name from sqlite_master where type='index' and tbl_name='{0}' and sql is not null;",
- table);
- using (var reader = command.ExecuteReader())
- {
- if (reader.HasRows)
- while (reader.Read())
- drops.Add(string.Format("DROP INDEX {0}", reader.GetString(0)));
- }
- }
- foreach (var drop in drops)
- ExecuteSQL(access, drop);
- bool existingtable = false;
- using (var command = access.CreateCommand())
- {
- command.CommandText =
- $"select name from sqlite_master where type='table' and tbl_name='{table}' and sql is not null;";
- using (var reader = command.ExecuteReader())
- {
- if (reader.HasRows)
- existingtable = true;
- }
- }
-
- if (existingtable)
- ExecuteSQL(access, string.Format("ALTER TABLE {0} RENAME TO _{0}_old;", table));
- CreateTable(access, type, true, customproperties);
- var fields = new List<string>();
- foreach (var field in type_fields.Keys)
- if (table_fields.ContainsKey(field))
- fields.Add("[" + field + "]");
- if (existingtable)
- {
- ExecuteSQL(access,
- string.Format("INSERT INTO {0} ({1}) SELECT {1} FROM _{0}_old;", table,
- string.Join(", ", fields)));
- ExecuteSQL(access, string.Format("DROP TABLE _{0}_old;", table));
- }
- transaction.Commit();
- }
- ExecuteSQL(access, "PRAGMA foreign_keys = on;");
- }
- catch (Exception e)
- {
- OnLog?.Invoke(LogType.Error, string.Format("RebuildTable({0}) [TABLE] failed: {1}\n{2}", table, e.Message, e.StackTrace));
- throw;
- }
- }
-
- }
- private void CheckFields(SQLiteWriteAccessor access, Type type, Dictionary<string, string> current_fields, CustomProperty[] customproperties)
- {
- var type_fields = new Dictionary<string, string>();
- var view = type.GetCustomAttribute<AutoEntity>();
- if ((view != null) && (view.Generator != null))
- LoadFields(view.Generator.Definition, type_fields, null, customproperties);
- else
- LoadFields(type, type_fields, null, customproperties);
- var bRebuild = false;
- foreach (var field in type_fields.Keys)
- {
- try
- {
- Type tType;
- var cprop = customproperties.FirstOrDefault(x => string.Equals(x.Name, field));
- if (cprop != null)
- tType = cprop.PropertyType;
- else
- tType = CoreUtils.GetProperty(type, field).PropertyType;
- if ((view == null) && (tType == typeof(TimeSpan)) && current_fields.ContainsKey(field) && !current_fields[field].Equals(type_fields[field]))
- {
- var sql = string.Format(
- "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 \"%:%:%\"",
- type.Name, field);
- ExecuteSQL(access, sql);
- }
- }
- catch (Exception e)
- {
- Logger.Send(LogType.Error, "", string.Format("*** Unknown Error: {0}\n{1}", e.Message, e.StackTrace));
- }
- if (!current_fields.ContainsKey(field) || (!String.IsNullOrWhiteSpace(current_fields[field])) && (current_fields[field] != type_fields[field]))
- bRebuild = true;
- }
- foreach (var field in current_fields.Keys)
- if (!type_fields.ContainsKey(field))
- bRebuild = true;
- if (bForceRebuild || bRebuild)
- RebuildTable(access, type, current_fields, type_fields, customproperties);
- }
- private void CheckIndexes(SQLiteWriteAccessor access, Type type, Dictionary<string, string> db_indexes)
- {
- var type_indexes = LoadIndexes(type);
- foreach (var index in db_indexes.Keys)
- if (!type_indexes.Contains(db_indexes[index]))
- ExecuteSQL(access, string.Format("DROP INDEX {0}", index));
- foreach (var index in type_indexes)
- if (!db_indexes.ContainsValue(index))
- ExecuteSQL(access, index);
- }
- private void CheckTriggers(SQLiteWriteAccessor access, Type type, Dictionary<string, string> db_triggers)
- {
- /*
- #if PURGE
- foreach (var trigger in db_triggers.Keys)
- ExecuteSQL(access, string.Format("DROP TRIGGER {0}", trigger));
- #else*/
- var type_triggers = LoadTriggers(type);
- foreach (var trigger in db_triggers.Keys)
- if (!type_triggers.Contains(db_triggers[trigger]))
- ExecuteSQL(access, string.Format("DROP TRIGGER {0}", trigger));
- foreach (var trigger in type_triggers)
- if (!db_triggers.ContainsValue(trigger))
- ExecuteSQL(access, trigger);
- //#endif
- }
- // private void CheckViews(SQLiteWriteAccessor access, Type type, Dictionary<String, String> db_views)
- // {
- // var type_view = LoadView(type);
- //
- //
- // if (!type_triggers.Contains(db_triggers[viewname]))
- // ExecuteSQL(access, string.Format("DROP TRIGGER {0}", trigger));
- //
- // if (!db_views.ContainsValue(type_view))
- // ExecuteSQL(access, type_view);
- // }
- #endregion
- #region CRUD Operations
- private static bool IsNull([NotNullWhen(false)] object? o)
- {
- return o == null || o is DBNull;
- }
- public object? Decode(object o, Type type)
- {
- if (IsNull(o))
- return type == typeof(string) ? "" : type.GetDefault();
- if (type == typeof(string[]))
- {
- if (!IsNull(o))
- {
- if (o is byte[] array)
- using (var ms = new MemoryStream(array))
- {
- var deser = new BinaryFormatter().Deserialize(ms);
- return deser as string[];
- }
- return Array.Empty<string>();
- }
- return Array.Empty<string>();
- }
- if (type.GetInterfaces().Contains(typeof(IPackable)))
- {
- var packable = (Activator.CreateInstance(type) as IPackable)!; // Not-null because of above check
- if (!IsNull(o))
- {
- if (o is byte[] array)
- {
- using (var ms = new MemoryStream(array))
- packable.Unpack(new BinaryReader(ms));
- }
- }
- return packable;
- }
- if (type == typeof(DateTime))
- return IsNull(o) || string.IsNullOrEmpty(o.ToString()) ? DateTime.MinValue : DateTime.Parse(o.ToString() ?? "");
- if (type == typeof(TimeSpan))
- {
- if (IsNull(o))
- return TimeSpan.MinValue;
- var oStr = o.ToString() ?? "";
- if (double.TryParse(oStr, out var hrs))
- return TimeSpan.FromHours(hrs);
- if (oStr.Contains(':'))
- return TimeSpan.Parse(oStr);
- }
- if (type == typeof(Guid))
- return IsNull(o) ? Guid.Empty : Guid.Parse(o.ToString() ?? "");
- if (type == typeof(bool))
- {
- var oStr = o.ToString() ?? "";
- return !IsNull(o) && (oStr.Equals("1") || oStr.ToUpper().Equals("TRUE"));
- }
- if (type.IsEnum)
- return Enum.Parse(type, o.ToString() ?? "");
- if (IsNull(o))
- return null;
- if (type == typeof(long))
- return o;
- var result = CoreUtils.ChangeType(o, type);
- return result;
- }
- public static object Encode(object? o, Type? type)
- {
- if (IsNull(o) || type is null)
- return DBNull.Value;
- if (type == typeof(DateTime) && o.GetType() == typeof(string))
- o = DateTime.Parse(o.ToString() ?? "");
- if (type == typeof(double) && o.GetType() == typeof(string))
- o = double.Parse(o.ToString() ?? "");
- if (type == typeof(float) && o.GetType() == typeof(string))
- o = float.Parse(o.ToString() ?? "");
- if (type == typeof(long) && o.GetType() == typeof(string))
- o = long.Parse(o.ToString() ?? "");
- if (type.IsEnum && o.GetType() == typeof(string))
- o = Enum.Parse(type, o.ToString() ?? "");
- if (type == typeof(TimeSpan) && o.GetType() == typeof(string))
- {
- if (o.ToString()?.Contains(':') == true)
- {
- if (TimeSpan.TryParse(o.ToString(), out var time))
- o = time;
- }
- else if (double.TryParse(o.ToString(), out var hrs))
- {
- o = TimeSpan.FromHours(hrs);
- }
- }
- if (type == typeof(bool))
- {
- if (o.GetType() == typeof(string))
- o = bool.Parse(o.ToString() ?? "");
- if (o.Equals(false))
- return DBNull.Value;
- return o;
- }
- if (type == typeof(string) && string.IsNullOrEmpty(o.ToString()))
- return DBNull.Value;
- if (type == typeof(Guid) && o.GetType() == typeof(string))
- o = Guid.Parse(o.ToString() ?? "");
- if (o is string[])
- using (var ms = new MemoryStream())
- {
- new BinaryFormatter().Serialize(ms, o);
- return ms.GetBuffer();
- }
- if (o is IPackable pack)
- {
- using var ms = new MemoryStream();
- using var writer = new BinaryWriter(ms);
- pack.Pack(writer);
- return ms.ToArray();
- }
- if (o.GetType() == typeof(double) && o.Equals(default(double)))
- return DBNull.Value;
- if (o.GetType() == typeof(float) && o.Equals(default(float)))
- return DBNull.Value;
- if (o.GetType() == typeof(int) && o.Equals(default(int)))
- return DBNull.Value;
- if (o.GetType() == typeof(long) && o.Equals(default(long)))
- return DBNull.Value;
- if (o.GetType() == typeof(DateTime))
- {
- if ((DateTime)o == DateTime.MinValue)
- return DBNull.Value;
- return string.Format("{0:yyyy-MM-dd HH:mm:ss.FFFFFFF}", o);
- }
- if (o.GetType() == typeof(TimeSpan))
- {
- if (((TimeSpan)o).Ticks == 0L)
- return DBNull.Value;
- return ((TimeSpan)o).TotalHours;
- }
- if (o.GetType() == typeof(Guid))
- {
- if (o.Equals(Guid.Empty))
- return DBNull.Value;
- return o.ToString() ?? "";
- }
- if (type == typeof(double) && o.GetType() == typeof(string))
- if (double.TryParse((string)o, out var value))
- o = value;
- if (o.GetType().IsEnum)
- return o.ToString() ?? "";
- return o;
- }
- private static readonly Dictionary<Operator, string> operators = new()
- {
- { Operator.IsEqualTo, "{0} = {1}" },
- { Operator.IsNotEqualTo, "{0} != {1}" },
- { Operator.IsGreaterThan, "{0} > {1}" },
- { Operator.IsGreaterThanOrEqualTo, "{0} >= {1}" },
- { Operator.IsLessThan, "{0} < {1}" },
- { Operator.IsLessThanOrEqualTo, "{0} <= {1}" },
- { Operator.BeginsWith, "{0} LIKE {1} || '%'" },
- { Operator.Contains, "{0} LIKE '%' || {1} || '%'" },
- { Operator.EndsWith, "{0} LIKE '%' || {1}" },
- { Operator.InList, "{0} IN ({1})" },
- { Operator.NotInList, "{0} NOT IN ({1})" },
- { Operator.InQuery, "{0} IN ({1})" },
- { Operator.NotInQuery, "{0} NOT IN ({1})" }
- };
- private static string EscapeValue(object? value)
- {
- if (IsNull(value))
- return "NULL";
- if ((value is string) || (value is Enum) || (value is Guid))
- return string.Format("\'" + "{0}" + "\'", value.ToString()?.Replace("\'", "\'\'"));
- if (value is string[])
- return string.Format("hex({0})", BitConverter.ToString(Encoding.ASCII.GetBytes(value.ToString() ?? "")).Replace("-", ""));
- if (value is IColumn col)
- return $"[{col.Property}]";
- return value.ToString() ?? "";
- }
- private static string GetFilterConstant(FilterConstant constant)
- {
- return constant switch
- {
- FilterConstant.Now => "datetime()",
- FilterConstant.Today => "datetime(date())",
- FilterConstant.Zero => "0",
-
- // These figures are based around today
- FilterConstant.OneWeekAgo => "datetime(date(),'-7 days')",
- FilterConstant.OneWeekAhead => "datetime(date(),'+7 days')",
- FilterConstant.OneMonthAgo => "datetime(date(),'-1 month')",
- FilterConstant.OneMonthAhead => "datetime(date(),'+1 month')",
- FilterConstant.ThreeMonthsAgo => "datetime(date(),'-3 months')",
- FilterConstant.ThreeMonthsAhead => "datetime(date(),'+3 months')",
- FilterConstant.SixMonthsAgo => "datetime(date(),'-6 months')",
- FilterConstant.SixMonthsAhead => "datetime(date(),'+6 months')",
- FilterConstant.OneYearAgo => "datetime(date(),'-1 year')",
- FilterConstant.OneYearAhead => "datetime(date(),'+1 year')",
-
- // Relative Week values run from Monday thru Sunday
- FilterConstant.StartOfLastWeek => "datetime(date(), 'weekday 0', '-13 days')",
- FilterConstant.EndOfLastWeek => "datetime(date(), 'weekday 0', '-6 days', '-000.0001 seconds')",
- FilterConstant.StartOfThisWeek => "datetime(date(), 'weekday 0', '-6 days')",
- FilterConstant.EndOfThisWeek => "datetime(date(), 'weekday 0', '+1 day', '-000.0001 seconds')",
- FilterConstant.StartOfNextWeek => "datetime(date(), 'weekday 0', '+1 days')",
- FilterConstant.EndOfNextWeek => "datetime(date(), 'weekday 0', '+8 days', '-000.0001 seconds')",
-
- FilterConstant.StartOfLastMonth => "datetime(date(), 'start of month', '-1 month')",
- FilterConstant.EndOfLastMonth => "datetime(date(), 'start of month', '-000.0001 seconds')",
- FilterConstant.StartOfThisMonth => "datetime(date(), 'start of month')",
- FilterConstant.EndOfThisMonth => "datetime(date(), 'start of month', '+1 month', '-000.0001 seconds')",
- FilterConstant.StartOfNextMonth => "datetime(date(), 'start of month', '+1 month')",
- FilterConstant.EndOfNextMonth => "datetime(date(), 'start of month', '+2 months', '-000.0001 seconds')",
- FilterConstant.StartOfLastCalendarYear => "datetime(date(), 'start of year', '-1 year')",
- FilterConstant.EndOfLastCalendarYear => "datetime(date(), 'start of year', '-000.0001 seconds')",
- FilterConstant.StartOfThisCalendarYear => "datetime(date(), 'start of year')",
- FilterConstant.EndOfThisCalendarYear => "datetime(date(), 'start of year', '+1 year', '-000.0001 seconds')",
- FilterConstant.StartOfNextCalendarYear => "datetime(date(), 'start of year', '+1 year')",
- FilterConstant.EndOfNextCalendarYear => "datetime(date(), 'start of year', '+2 years', '-000.0001 seconds')",
-
- FilterConstant.StartOfLastFinancialYear => "datetime(date(), '-18 months', 'start of year', '+6 months')",
- FilterConstant.EndOfLastFinancialYear => "datetime(date(), '-18 months', 'start of year', '-18 months', '-000.0001 seconds')",
- FilterConstant.StartOfThisFinancialYear => "datetime(date(), '-6 months', 'start of year', '+6 months')",
- FilterConstant.EndOfThisFinancialYear => "datetime(date(), '-6 months', 'start of year', '+18 months', '-000.0001 seconds')",
- FilterConstant.StartOfNextFinancialYear => "datetime(date(), '+6 months', 'start of year', '+6 months')",
- FilterConstant.EndOfNextFinancialYear => "datetime(date(), '+6 months', 'start of year', '+18 months', '-000.0001 seconds')",
-
- _ => throw new Exception($"FilterConstant.{constant} is not implemented!"),
- };
- }
- private string GetFilterClauseNonGeneric(Type T, SQLiteCommand command, char prefix, IFilter? filter, List<Tuple<string, string, string, string>> tables,
- Dictionary<string, string> fieldmap, List<string> columns, bool useparams)
- {
- if (filter == null || filter.Expression == null)
- return "";
- var result = "";
- if (filter.Operator == Operator.All)
- {
- result = filter.IsNot ? "1 = 0" : "1 = 1";
- }
- else if (filter.Operator == Operator.None)
- {
- result = filter.IsNot ? "1 = 1" : "1 = 0";
- }
- else
- {
- string prop;
- if (CoreUtils.TryFindMemberExpression(filter.Expression, out var mexp))
- {
- prop = CoreUtils.GetFullPropertyName(mexp, ".");
- }
- else
- {
- prop = filter.Expression.ToString();
- if (prop.Contains("=>"))
- prop = string.Join(".", prop.Split('.').Skip(1));
- mexp = CoreUtils.GetMemberExpression(T, prop);
- }
- LoadFieldsandTables(command, T, prefix, fieldmap, tables, columns, prop, useparams);
- if (fieldmap.ContainsKey(prop))
- prop = fieldmap[prop];
- if(filter.Value is CustomFilterValue)
- {
- throw new Exception("Custom Filter Value not able to be processed server-side!");
- }
- if (filter.Operator == Operator.InList || filter.Operator == Operator.NotInList)
- {
- // if, and only if the list contains Guids, we can safely bypass the
- // 1000-parameter limit by using building the string ourselves
- if (filter.Value is Guid[] list)
- {
- result = string.Format("(" + operators[filter.Operator] + ")", prop, string.Format("\"{0}\"", string.Join("\",\"", list)));
- }
- else if (filter.Value is IEnumerable enumerable)
- {
- var paramlist = new List<object>();
- foreach (var item in enumerable)
- {
- var sParam = string.Format("@p{0}", command.Parameters.Count);
- command.Parameters.AddWithValue(sParam, Encode(item, mexp.Type));
- paramlist.Add(sParam);
- }
- result = string.Format("(" + operators[filter.Operator] + ")", prop, string.Join(",", paramlist));
- }
- }
- else if (filter.Operator == Operator.InQuery || filter.Operator == Operator.NotInQuery)
- {
- if(filter.Value is ISubQuery subQuery)
- {
- var subEntityType = subQuery.GetQueryType();
- var subColumns = Columns.Create(subEntityType);
- var subColumn = subQuery.GetColumn();
- subColumns.Add(subColumn);
- var subQueryText = PrepareSelectNonGeneric(subEntityType, command, 'A',
- subQuery.GetFilter(), subColumns, null,
- null, null, int.MaxValue, false, useparams);
- result = string.Format("(" + operators[filter.Operator] + ")", prop, subQueryText);
- }
- }
- else
- {
- if (filter.Value is FilterConstant constant)
- result = string.Format("(" + operators[filter.Operator] + ")", prop, GetFilterConstant(constant));
- else
- {
- var value = Encode(filter.Value, mexp.Type);
- if (IsNull(value) && ((filter.Operator == Operator.IsEqualTo) || (filter.Operator == Operator.IsNotEqualTo)))
- {
- result = string.Format("({0} {1} NULL)", prop,
- filter.Operator == Operator.IsEqualTo
- ? "IS"
- : "IS NOT");
- }
- else
- {
- if (useparams)
- {
- var sParam = string.Format("@p{0}", command.Parameters.Count);
- if (filter.Expression.Type == typeof(string[]))
- {
- var bytes = Encoding.ASCII.GetBytes(value.ToString() ?? "");
- value = BitConverter.ToString(bytes).Replace("-", "");
- result = string.Format("(" + operators[filter.Operator] + ")", string.Format("hex({0})", prop), sParam);
- }
- else
- {
- result = string.Format("(" + operators[filter.Operator] + ")", prop, sParam);
- }
- command.Parameters.AddWithValue(sParam, value);
- }
- else
- result = string.Format("(" + operators[filter.Operator] + ")", prop, EscapeValue(filter.Value));
- }
- }
- }
- if (filter.IsNot)
- {
- result = $"(NOT {result})";
- }
- }
- var bChanged = false;
- if (filter.Ands != null && filter.Ands.Any())
- {
- foreach (var and in filter.Ands)
- {
- var AndResult = GetFilterClauseNonGeneric(T, command, prefix, and, tables, fieldmap, columns, useparams);
- if (!string.IsNullOrEmpty(AndResult))
- {
- result = string.Format("{0} and {1}", result, AndResult);
- bChanged = true;
- }
- }
- if (bChanged)
- result = string.Format("({0})", result);
- }
- bChanged = false;
- if (filter.Ors != null && filter.Ors.Any())
- {
- foreach (var or in filter.Ors)
- {
- var OrResult = GetFilterClauseNonGeneric(T, command, prefix, or, tables, fieldmap, columns, useparams);
- if (!string.IsNullOrEmpty(OrResult))
- {
- result = string.Format("{0} or {1}", result, OrResult);
- bChanged = true;
- }
- }
- if (bChanged)
- result = string.Format("({0})", result);
- }
- return result;
- }
- private string GetFilterClause<T>(SQLiteCommand command, char prefix, Filter<T>? filter, List<Tuple<string, string, string, string>> tables,
- Dictionary<string, string> fieldmap, List<string> columns, bool useparams) where T : Entity
- => GetFilterClauseNonGeneric(typeof(T), command, prefix, filter, tables, fieldmap, columns, useparams);
- private string GetSortClauseNonGeneric(Type T,
- SQLiteCommand command, ISortOrder? sort, char prefix, List<Tuple<string, string, string, string>> tables,
- Dictionary<string, string> fieldmap, List<string> columns, bool useparams)
- {
- if (sort == null)
- return "";
- var result = "";
- if (sort.Expression != null)
- {
- if (CoreUtils.TryFindMemberExpression(sort.Expression, out var mexp))
- result = CoreUtils.GetFullPropertyName(mexp, ".");
- else
- result = sort.Expression.ToString();
- var prop = CoreUtils.GetProperty(T, result);
- if (prop.GetCustomAttribute<DoNotSerialize>() == null && prop.GetCustomAttribute<DoNotPersist>() == null && prop.CanWrite)
- {
- LoadFieldsandTables(command, T, prefix, fieldmap, tables, columns, result, useparams);
- if (fieldmap.ContainsKey(result))
- result = fieldmap[result];
- if (sort.Expression.Type.Equals(typeof(string)))
- result = string.Format("{0} COLLATE NOCASE", result);
- if (sort.Direction == SortDirection.Ascending)
- result = string.Format("{0} ASC", result);
- else
- result = string.Format("{0} DESC", result);
- }
- else
- {
- result = "";
- }
- }
- foreach (var then in sort.Thens)
- result = result + ", " + GetSortClauseNonGeneric(T, command, then, prefix, tables, fieldmap, columns, useparams);
- return result;
- }
- private string GetSortClause<T>(SQLiteCommand command, SortOrder<T>? sort, char prefix, List<Tuple<string, string, string, string>> tables,
- Dictionary<string, string> fieldmap, List<string> columns, bool useparams)
- => GetSortClauseNonGeneric(typeof(T), command, sort, prefix, tables, fieldmap, columns, useparams);
- private static string GetCalculation(AggregateAttribute attribute, string columnname)
- {
- return attribute.Calculation switch
- {
- AggregateCalculation.Sum => "SUM",
- AggregateCalculation.Count => "COUNT",
- AggregateCalculation.Maximum => "MAX",
- AggregateCalculation.Minimum => "MIN",
- AggregateCalculation.Average => "AVERAGE",
- AggregateCalculation.Concat => "GROUP_CONCAT",
- _ => throw new Exception(string.Format("{0}.{1} is not a valid aggregate", columnname, attribute.Calculator.GetType().Name)),
- };
- }
- private string GetFunction(FormulaAttribute attribute, Dictionary<string, string> fieldmap, string columnname)
- {
- if (attribute.Operator == FormulaOperator.None)
- throw new Exception(string.Format("{0}.{1} is not a valid formula", columnname, attribute.Calculator.GetType().Name));
-
- if (attribute.Operator == FormulaOperator.Constant)
- return EscapeValue(attribute.Value);
- if (!fieldmap.ContainsKey(attribute.Value))
- throw new Exception(string.Format("{0}.{1} -> {2} does not exist", columnname, attribute.GetType().Name, attribute.Value));
-
- foreach (var modifier in attribute.Modifiers)
- if (!fieldmap.ContainsKey(modifier))
- throw new Exception(string.Format("{0}.{1} -> {2} does not exist", columnname, attribute.GetType().Name, modifier));
- if (attribute.Operator == FormulaOperator.Add)
- return string.Format("(IFNULL({0},0.00) + {1})", fieldmap[attribute.Value],
- string.Join(" + ", attribute.Modifiers.Select(x => string.Format("IFNULL({0},0.00)", fieldmap[x]))));
- if (attribute.Operator == FormulaOperator.Subtract)
- return string.Format("(IFNULL({0},0.00) - ({1}))", fieldmap[attribute.Value],
- string.Join(" + ", attribute.Modifiers.Select(x => string.Format("IFNULL({0},0.00)", fieldmap[x]))));
- if (attribute.Operator == FormulaOperator.Multiply)
- return string.Format("(IFNULL({0},0.00) * {1})", fieldmap[attribute.Value],
- string.Join(" * ", attribute.Modifiers.Select(x => string.Format("IFNULL({0},0.00)", fieldmap[x]))));
- if (attribute.Operator == FormulaOperator.Divide)
- {
- var result = string.Format("IFNULL({0},0.00)", fieldmap[attribute.Value]);
- foreach (var modifier in attribute.Modifiers)
- result = string.Format("({0} / {1})", result, string.Format("IFNULL({0},1.00)", fieldmap[modifier]));
- return result;
- }
- if (attribute.Operator == FormulaOperator.Maximum)
- {
- var parameters = attribute.Modifiers.Select(m => $"IFNULL({fieldmap[m]},0.00)");
- var result = $"MAX({fieldmap[attribute.Value]}, {String.Join(", ", parameters)})";
- // var result = string.Format(
- // "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",
- // fieldmap[attribute.Value], fieldmap[attribute.Modifiers.First()]);
- return result;
- }
- if (attribute.Operator == FormulaOperator.Minumum)
- {
- var parameters = attribute.Modifiers.Select(m => $"IFNULL({fieldmap[m]},0.00)");
- var result = $"MIN({fieldmap[attribute.Value]}, {String.Join(", ", parameters)})";
- //var result = string.Format(
- // "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",
- // fieldmap[attribute.Value], fieldmap[attribute.Modifiers.First()]);
- return result;
- }
- throw new Exception(string.Format("Calculation Type [{0}.{1}] not found", columnname, attribute.GetType().Name));
- }
- private string GetCondition(ConditionAttribute attribute, Dictionary<string, string> fieldmap, string columnname)
- {
- var intf = attribute.Calculator.GetType().GetInterfaces()
- .FirstOrDefault(x => x.Name.StartsWith("ICondition") && x.GenericTypeArguments.Length.Equals(3));
- if (intf is null)
- throw new Exception($"Attribute calculate {attribute.Calculator} is not an ICondition");
- var valuetype = intf.GenericTypeArguments[1];
- var defvalue = valuetype.GetDefault();
- if (!fieldmap.ContainsKey(attribute.Left))
- throw new Exception(string.Format("{0}.{1} -> {2} does not exist", columnname, attribute.GetType().Name, attribute.Left));
- var condition = "";
- if (attribute.Condition == Condition.Equals)
- condition = "=";
- else if (attribute.Condition == Condition.NotEqual)
- condition = "<>";
- else if (attribute.Condition == Condition.GreaterThan)
- condition = ">";
- else if (attribute.Condition == Condition.GreaterThanOrEqualTo)
- condition = ">=";
- else if (attribute.Condition == Condition.LessThan)
- condition = "<";
- else if (attribute.Condition == Condition.LessThanOrEqualTo)
- condition = "<=";
- else
- throw new Exception(string.Format("{0}.{1} is not a valid condition", columnname, attribute.Calculator.GetType().Name));
- if (!fieldmap.ContainsKey(attribute.Right))
- throw new Exception(string.Format("{0}.{1} -> {2} does not exist", columnname, attribute.GetType().Name, attribute.Right));
- if (!fieldmap.ContainsKey(attribute.True))
- throw new Exception(string.Format("{0}.{1} -> {2} does not exist", columnname, attribute.GetType().Name, attribute.True));
- if (!fieldmap.ContainsKey(attribute.False))
- throw new Exception(string.Format("{0}.{1} -> {2} does not exist", columnname, attribute.GetType().Name, attribute.False));
- return string.Format("CASE WHEN COALESCE({0},{1}) {2} {3} THEN {4} ELSE {5} END", fieldmap[attribute.Left], defvalue, condition,
- fieldmap[attribute.Right], fieldmap[attribute.True], fieldmap[attribute.False]);
- }
-
- //private static List<string> Breadcrumb = new List<string>();
- //Dictionary<String, Tuple<int, TimeSpan>> stopwatch = new Dictionary<string, Tuple<int, TimeSpan>>();
- //Stopwatch sw = new Stopwatch();
- //private void LogReset()
- //{
- // stopwatch = new Dictionary<string, Tuple<int, TimeSpan>>();
- // sw = new Stopwatch();
- //}
- //void LogStart()
- //{
- // if (sw.IsRunning)
- // sw.Restart();
- // else
- // sw.Start();
- //}
- //void LogStop(String msg)
- //{
- // TimeSpan elapsed = sw.Elapsed;
- // if (Breadcrumb.Any())
- // msg = String.Join(".", Breadcrumb) + "." + msg;
- // if (!stopwatch.ContainsKey(msg))
- // stopwatch[msg] = new Tuple<int, TimeSpan>(1,elapsed);
- // else
- // stopwatch[msg] = new Tuple<int, TimeSpan>(stopwatch[msg].Item1 + 1,stopwatch[msg].Item2 + elapsed);
- // sw.Restart();
- //}
- //private void LogPrint(String header)
- //{
- // foreach (var keyvalue in stopwatch.ToArray())
- // OnLog?.Invoke(LogType.Information, String.Format("- {0}: {1} = ({2}) {3,10:0.0000}", header, keyvalue.Key, keyvalue.Value.Item1, keyvalue.Value.Item2.TotalMilliseconds));
- //}
- private void LoadFieldsandTables(SQLiteCommand command, Type type, char prefix, Dictionary<string, string> fieldmap,
- List<Tuple<string, string, string, string>> tables, List<string> columns, string columnname, bool useparams)
- {
- if (fieldmap.ContainsKey(columnname))
- return;
- var newprefix = (char)(prefix + 1);
- // LogStart();
- var property = DatabaseSchema.Property(type, columnname);
- // LogStop("DatabaseSchema.Property");
- if (property != null && property is CustomProperty)
- {
- fieldmap[columnname] = string.Format("{0}1.[{1}]", prefix, columnname);
- }
- else
- {
- var bits = columnname.Split('.');
- if (bits.Length == 1)
- {
- var prop = type.GetProperty(columnname);
- if (prop != null)
- {
- // LogStart();
- // LogStop("GetCustomAttributes(Aggregate)");
- if (prop.GetCustomAttribute<AggregateAttribute>() is AggregateAttribute agg)
- {
- bool internalaggregate = agg.Calculator.GetType().GetInterfaces()
- .Any(x => x.IsGenericType && x.GetGenericTypeDefinition() == typeof(ICoreAggregate<,>));
- if (internalaggregate)
- {
-
- }
- else
- {
- var scols = new Dictionary<string, string> { { agg.Aggregate, GetCalculation(agg, columnname) } };
- var siblings = columns.Where(x => !x.Equals(columnname) && x.Split('.').First().Equals(bits.First()))
- .Select(x => string.Join(".", x.Split('.').Skip(1))).ToList();
- var linkedtype = agg.Source;
- foreach (var sibling in siblings)
- {
- // LogStart();
- var sprop = CoreUtils.GetProperty(type, sibling);
- // LogStop("GetProperty(sibling)");
- // LogStart();
- var sattr = prop.GetCustomAttributes().FirstOrDefault(x => x.GetType().Equals(typeof(AggregateAttribute)));
- // LogStop("GetCustomAttributes(Sibling.Aggregate)");
- if (sattr is AggregateAttribute sagg)
- {
- // LogStart();
- scols[sagg.Aggregate] = GetCalculation(sagg, sibling);
- // LogStop("GetCalculation(Sibling)");
- }
- }
- var subcols = Columns.Create(linkedtype);
- foreach (var key in agg.Links.Keys)
- subcols.Add(key);
- //subcols.Add(agg.Link);
- foreach (var scol in scols.Keys)
- subcols.Add(scol);
- // LogStart();
- var aggFilter = agg.Filter;
- if(aggFilter is not null)
- {
- var ffs = new List<string>();
- FilterFields(aggFilter, ffs);
- //foreach (var ff in ffs)
- // subcols.Add(ff);
- }
- var linkedtable = string.Format("({0})",
- PrepareSelectNonGeneric(linkedtype, command, newprefix, aggFilter, subcols, null, scols, null, int.MaxValue, false, useparams));
-
- var alias = tables.Count + 1;
- var link = string.Join(" , ", agg.Links.Keys.Select(x => string.Format("{0}{1}.{2}", prefix, alias, x)));
- var tuple = tables.FirstOrDefault(x =>
- x.Item1.Equals(linkedtable) && x.Item4.Equals(link) && !x.Item2.Equals(string.Format("{0}1", prefix)));
- if (tuple == null)
- {
- var joins = new List<string>();
- foreach (var key in agg.Links.Keys)
- joins.Add(
- string.Format("IFNULL({0}{1}.[{2}],'') = IFNULL({0}1.[{3}],'')", prefix, alias, key, agg.Links[key]));
- var join = string.Format("LEFT OUTER JOIN {0} {1}{2} ON {3}", linkedtable, prefix, alias,
- string.Join(" AND ", joins));
- tuple = new Tuple<string, string, string, string>(linkedtable, prefix + alias.ToString(), join, link);
- //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);
- tables.Add(tuple);
- }
- //if (bits.Last().Equals("ID"))
- // fieldmap[columnname] = String.Format("{0}1.[{1}]", prefix, columnname);
- //else
- fieldmap[columnname] = string.Format("{0}.[{1}]", tuple.Item2, agg.Aggregate);
- //LoadFieldsandTables(command, agg.Type, newprefix, fieldmap, tables, columns, columnname);
- //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);
- //int alias = tables.Count + 1;
- //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), "");
- //tables.Add(tuple);
- //fieldmap[columnname] = String.Format("{0}.[{1}]", tuple.Item2, agg.AggregateProperty);
- }
- }
- else if (prop.GetCustomAttribute<FormulaAttribute>() is FormulaAttribute fnc)
- {
- // var functionmap = new Dictionary<string, string>();
- // CheckColumn(columns, fnc.Value);
- // LoadFieldsandTables(command, type, prefix, functionmap, tables, columns, fnc.Value, useparams);
- // foreach (var column in fnc.Modifiers)
- // {
- // CheckColumn(columns, column);
- // LoadFieldsandTables(command, type, prefix, functionmap, tables, columns, column, useparams);
- // }
- // fieldmap[columnname] = GetFunction(fnc, functionmap, columnname);
- foreach (var field in fnc.Modifiers.Prepend(fnc.Value))
- {
- CheckColumn(columns, field);
- LoadFieldsandTables(command, type, prefix, fieldmap, tables, columns, field, useparams);
- }
- fieldmap[columnname] = GetFunction(fnc, fieldmap, columnname);
- }
- else if(prop.GetCustomAttribute<ConditionAttribute>() is ConditionAttribute cnd)
- {
- var cndmap = new Dictionary<string, string>();
- // LogStart();
- CheckColumn(columns, cnd.Left);
- // LogStop("CheckColumn(Left)");
- // LogStart();
- CheckColumn(columns, cnd.Right);
- // LogStop("CheckColumn(Right)");
- // LogStart();
- CheckColumn(columns, cnd.True);
- // LogStop("CheckColumn(True)");
- // LogStart();
- CheckColumn(columns, cnd.False);
- // LogStop("CheckColumn(False)");
- //// LogStart();
- LoadFieldsandTables(command, type, prefix, cndmap, tables, columns, cnd.Left, useparams);
- //// LogStop("LoadFieldsAndTables(Left)");
- //// LogStart();
- LoadFieldsandTables(command, type, prefix, cndmap, tables, columns, cnd.Right, useparams);
- //// LogStop("LoadFieldsAndTables(Right)");
- //// LogStart();
- LoadFieldsandTables(command, type, prefix, cndmap, tables, columns, cnd.True, useparams);
- //// LogStop("LoadFieldsAndTables(True)");
- //// LogStart();
- LoadFieldsandTables(command, type, prefix, cndmap, tables, columns, cnd.False, useparams);
- //// LogStop("LoadFieldsAndTables(False)");
- // LogStart();
- fieldmap[columnname] = GetCondition(cnd, cndmap, columnname);
- // LogStop("GetCondition");
- }
- else
- {
- fieldmap[columnname] = string.Format("{0}1.[{1}]", prefix, columnname);
- }
- }
- else
- {
- //IProperty customprop = DataModel.Property(type, columnname);
- //if (customprop != null)
- // fieldmap[columnname] = String.Format("{0}1.[{1}]", prefix, columnname);
- //else
- fieldmap[columnname] = columnname;
- }
- }
- else
- {
- var prop = type.GetProperty(bits.First());
- if (prop != null)
- {
- var combinecount = 1;
- while (prop.PropertyType.GetInterfaces().Contains(typeof(IEnclosedEntity)))
- {
- combinecount++;
- prop = CoreUtils.GetProperty(type, string.Join(".", bits.Take(combinecount)));
- }
- if (prop.PropertyType.GetInterfaces().Contains(typeof(IEntityLink)))
- {
- var linkedType = prop.PropertyType.GetInheritedGenericTypeArguments().First();
- var remote = linkedType.GetProperty(bits.Last());
- // Are there any other properties for this link? These will form the columns for our subquery
- var siblings = columns.Where(x => x.Split('.').First().Equals(bits.First()))
- .Select(x => string.Join(".", x.Split('.').Skip(combinecount))).ToList();
- if (remote != null && !siblings.Contains(remote.Name))
- siblings.Add(remote.Name);
- if(prop.GetCustomAttribute<ChildEntityAttribute>() is ChildEntityAttribute child)
- {
- var parent = child.Calculator.ParentColumn;
- if (!siblings.Contains(nameof(Entity.ID)))
- siblings.Insert(0, nameof(Entity.ID));
- if (!siblings.Contains(parent))
- siblings.Add(parent);
- var subcols = Columns.Create(linkedType, siblings);
- var subPrefix = (char)(newprefix + 1);
- var innerSQL = string.Format("({0})",
- PrepareSelectNonGeneric(
- linkedType,
- command,
- subPrefix,
- child.Calculator.Filter,
- subcols,
- child.Calculator.Sort,
- null,
- null,
- int.MaxValue,
- false,
- useparams));
- var linkedTable = $"(SELECT {string.Join(", ", siblings.Select(x => $"{newprefix}1.[{x}] as [{x}]"))}"
- + $" FROM {innerSQL} {newprefix}1"
- + $" GROUP BY [{parent}])";
- var link = string.Format("{0}.ID", prop.Name);
- var tuple = tables.FirstOrDefault(x =>
- x.Item1.Equals(linkedTable) && x.Item4.Equals(link) && !x.Item2.Equals(string.Format("{0}1", prefix)));
- if (tuple is null)
- {
- var alias = tables.Count + 1;
- 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,
- parent
- ),
- link
- );
- tables.Add(tuple);
- }
- //if (bits.Last().Equals("ID"))
- // fieldmap[columnname] = String.Format("{0}1.[{1}]", prefix, columnname);
- //else
- fieldmap[columnname] = string.Format("{0}.[{1}]", tuple.Item2, string.Join(".", bits.Skip(combinecount)));
- foreach (var sibling in siblings)
- {
- var subcol = string.Format("{0}.{1}", string.Join(".", bits.Take(combinecount)), sibling);
- if (!subcol.Equals(columnname))
- fieldmap[subcol] = string.Format("{0}.[{1}]", tuple.Item2, sibling);
- }
- }
- else
- {
- if (siblings.Count.Equals(1) && siblings.First().Equals("ID"))
- {
- fieldmap[columnname] = string.Format("{0}1.[{1}]", prefix, columnname);
- }
- else
- {
- if (!siblings.Contains("ID"))
- siblings.Insert(0, "ID");
- var subcols = Columns.Create(linkedType, siblings);
- var linkedtable = string.Format("({0})",
- PrepareSelectNonGeneric(linkedType, command, newprefix, null, subcols, null, null, null, int.MaxValue, false, useparams));
- var link = string.Format("{0}.ID", prop.Name);
- var tuple = tables.FirstOrDefault(x =>
- x.Item1.Equals(linkedtable) && x.Item4.Equals(link) && !x.Item2.Equals(string.Format("{0}1", prefix)));
- if (tuple == null)
- {
- var alias = tables.Count + 1;
- tuple = new Tuple<string, string, string, string>(
- linkedtable,
- prefix + alias.ToString(),
- string.Format(
- "LEFT OUTER JOIN {0} {1}{2} ON {1}{2}.[ID] = {1}1.[{3}.ID]",
- linkedtable,
- prefix,
- alias,
- string.Join(".", bits.Take(combinecount)) //prop.Name
- ),
- link
- );
- tables.Add(tuple);
- }
- //if (bits.Last().Equals("ID"))
- // fieldmap[columnname] = String.Format("{0}1.[{1}]", prefix, columnname);
- //else
- fieldmap[columnname] = string.Format("{0}.[{1}]", tuple.Item2, string.Join(".", bits.Skip(combinecount)));
- foreach (var sibling in siblings)
- {
- var subcol = string.Format("{0}.{1}", string.Join(".", bits.Take(combinecount)), sibling);
- if (!subcol.Equals(columnname))
- fieldmap[subcol] = string.Format("{0}.[{1}]", tuple.Item2, sibling);
- }
- }
- }
- }
- else if (prop.PropertyType.GetInterfaces().Contains(typeof(IEnclosedEntity)))
- {
- fieldmap[columnname] = string.Format("{0}1.[{1}]", prefix, columnname);
- }
- else
- {
- fieldmap[columnname] = string.Format("{0}1.[{1}]", prefix, columnname);
- }
- }
- else
- {
- fieldmap[columnname] = columnname;
- }
- }
- }
- }
- private static void CheckColumn(List<string> columns, string column)
- {
- if (!columns.Contains(column))
- columns.Add(column);
- }
- public void FilterFields(IFilter? filter, List<string> fields)
- {
- if (filter == null)
- return;
- if (filter.Operator != Operator.None && filter.Operator != Operator.All)
- {
- var exp = CoreUtils.GetFullPropertyName(CoreUtils.ExtractMemberExpression(filter.Expression), ".");
- if (!fields.Contains(exp))
- fields.Add(exp);
- }
- foreach (var and in filter.Ands)
- FilterFields(and, fields);
- foreach (var or in filter.Ors)
- FilterFields(or, fields);
- }
- public void SortFields(ISortOrder? sort, List<string> fields)
- {
- if (sort == null)
- return;
- var exp = CoreUtils.GetFullPropertyName(CoreUtils.ExtractMemberExpression(sort.Expression), ".");
- if (!fields.Contains(exp))
- fields.Add(exp);
- foreach (var then in sort.Thens)
- SortFields(then, fields);
- }
- public enum AggregateType
- {
- None,
- Sum,
- Count
- }
- public string PrepareSelectNonGeneric(Type T, SQLiteCommand command, char prefix,
- IFilter? filter, IColumns? columns, ISortOrder? sort,
- Dictionary<string, string>? aggregates, Dictionary<string, object?>? constants, int top, bool distinct, bool useparams)
- {
- var fieldmap = new Dictionary<string, string>();
- var cols = CoreUtils.GetColumns(T, columns);
- var fields = new List<string>();
- fields.AddRange(cols.ColumnNames());
- FilterFields(filter, fields);
- SortFields(sort, fields);
- var tables = new List<Tuple<string, string, string, string>>();
- var condition = "";
- var sortorder = "";
- var entityName = T.EntityName().Split('.').Last();
- tables.Add(new Tuple<string, string, string, string>(
- entityName,
- $"{prefix}1",
- $"{entityName} {prefix}1",
- "")
- );
- foreach (var column in cols.ColumnNames())
- LoadFieldsandTables(command, T, prefix, fieldmap, tables, fields, column, useparams);
- var parameters = new Dictionary<string, object>();
- condition = GetFilterClauseNonGeneric(T, command, prefix, filter, tables, fieldmap, fields, useparams);
- sortorder = GetSortClauseNonGeneric(T, command, sort, prefix, tables, fieldmap, fields, useparams);
- var combined = new SortedDictionary<string, string>();
- fields.Clear();
- foreach (var column in cols.ColumnNames())
- if (fieldmap.ContainsKey(column))
- {
- if (aggregates != null && aggregates.ContainsKey(column))
- combined[constants != null ? column : String.Format("{0:D8}", combined.Keys.Count)] = string.Format("{0}({1}) as [{2}]", aggregates[column], fieldmap[column], column);
- else
- combined[constants != null ? column : String.Format("{0:D8}", combined.Keys.Count)] = string.Format("{0} as [{1}]", fieldmap[column], column);
- }
- if (constants != null)
- {
- foreach(var (column, value) in constants)
- {
- combined[column] = string.Format("{0} as [{1}]", EscapeValue(value), column);
- }
- }
- var result = new List<string>();
- result.Add("SELECT");
- if (distinct)
- result.Add("DISTINCT");
- result.Add(string.Join(", ", combined.Values));
- result.Add("FROM");
- result.AddRange(tables.Select(x => x.Item3));
- if (!string.IsNullOrWhiteSpace(condition))
- {
- result.Add("WHERE");
- result.Add(condition);
- }
- if (!string.IsNullOrWhiteSpace(sortorder))
- {
- result.Add("ORDER BY");
- result.Add(sortorder);
- }
- if (aggregates != null)
- {
- var str = string.Join(", ",
- fieldmap.Where(x => cols.ColumnNames().Contains(x.Key) && !aggregates.ContainsKey(x.Key)).Select(f => f.Value));
- if (!string.IsNullOrWhiteSpace(str))
- {
- result.Add("GROUP BY");
- result.Add(str);
- }
- }
- if (top != int.MaxValue)
- result.Add(String.Format("LIMIT {0}", top));
- //Breadcrumb.Remove(Breadcrumb.Last());
- return string.Join(" ", result);
- }
- public string PrepareSelect<T>(SQLiteCommand command, char prefix, Filter<T>? filter, Columns<T>? columns, SortOrder<T>? sort,
- Dictionary<string, string>? aggregates, Dictionary<string, object?>? constants, int top, bool distinct, bool useparams) where T : Entity
- => PrepareSelectNonGeneric(typeof(T), command, prefix, filter, columns, sort, aggregates, constants, top, distinct, useparams);
- private static void PrepareUpsertNonGeneric(Type T, SQLiteCommand command, Entity item)
- {
- command.CommandText = "";
- command.Parameters.Clear();
- if (item.ID == Guid.Empty)
- item.ID = Guid.NewGuid();
- Dictionary<string, object> insert = item.GetValues(true);
- Dictionary<string, object> update = item.GetValues(false);
- var insertfields = new List<string>();
- var insertvalues = new List<string>();
- var updatecommands = new List<string>();
- var iParam = 0;
- foreach (var key in insert.Keys)
- {
- if (insert[key] is UserProperties)
- continue;
- var sParam = string.Format("@p{0}", iParam++);
- object? value = null;
- try
- {
- value = Encode(insert[key], insert[key]?.GetType());
- }
- catch (Exception e)
- {
- Logger.Send(LogType.Error, "", string.Format("*** Unknown Error: {0}\n{1}", e.Message, e.StackTrace));
- }
- command.Parameters.AddWithValue(sParam, value);
- insertfields.Add(string.Format("[{0}]", key));
- insertvalues.Add(sParam);
- if (update.ContainsKey(key))
- if (!key.Equals("ID"))
- updatecommands.Add(string.Format("[{0}]={1}", key, sParam));
- }
- var particles = new List<string>();
- particles.Add("INSERT INTO");
- particles.Add(T.EntityName().Split('.').Last());
- particles.Add("(");
- particles.Add(string.Join(",", insertfields));
- particles.Add(")");
- particles.Add("VALUES");
- particles.Add("(");
- particles.Add(string.Join(",", insertvalues));
- particles.Add(")");
- if (updatecommands.Any())
- particles.Add("ON CONFLICT([ID]) DO UPDATE SET " + string.Join(", ", updatecommands));
- else
- particles.Add("ON CONFLICT DO NOTHING");
- command.CommandText = string.Join(" ", particles);
- }
- private void PrepareUpsert<T>(SQLiteCommand command, T item) where T : Entity
- => PrepareUpsertNonGeneric(typeof(T), command, item);
- public void PrepareDelete<T>(SQLiteCommand command, T item) where T : Entity
- {
- command.CommandText = string.Format("DELETE FROM {0} WHERE [ID] = @p0", typeof(T).EntityName().Split('.').Last());
- command.Parameters.Clear();
- command.Parameters.AddWithValue("@p0", Encode(item.ID, typeof(Guid)));
- }
- #endregion
- #region Schema Handling
- public Dictionary<string, Type> GetSchema()
- {
- var result = new Dictionary<string, Type>();
- return result;
- }
- public void CreateSchema(params Type[] types)
- {
- }
- public void SaveSchema(Dictionary<string, Type> schema)
- {
- }
- public void UpgradeSchema(params Type[] types)
- {
- }
- #endregion
- #region CRUD Operations
-
- public static object[] GetValues(IDataReader reader, int count)
- {
- var result = new object[count];
- reader.GetValues(result);
- return result;
- }
- #region Query
- public IEnumerable<object[]> List<T>(Filter<T>? filter = null, Columns<T>? columns = null, SortOrder<T>? sort = null) where T : Entity, new()
- {
- //Dictionary<String, TimeSpan> stopwatch = new Dictionary<string, TimeSpan>();
- //Stopwatch sw = new Stopwatch();
- //sw.Start();
- var cols = CoreUtils.GetColumns(columns);
- //stopwatch["GetColumns"] = new TimeSpan(sw.ElapsedTicks);
- //sw.Restart();
- var result = new List<object[]>();
- //stopwatch["MakeResult"] = new TimeSpan(sw.ElapsedTicks);
- //sw.Restart();
- using (var access = GetReadAccess())
- {
- using (var command = access.CreateCommand())
- {
- var sortorder = sort ?? LookupFactory.DefineSort<T>();
- command.CommandText = "";
- command.Parameters.Clear();
- command.CommandText = PrepareSelect(command, 'A', filter, cols, sortorder, null, null, int.MaxValue, false, true) + ";";
- using (var reader = command.ExecuteReader())
- {
- foreach (var row in reader)
- {
- var values = GetValues(reader, cols.Items.Length);
- result.Add(values);
- }
- reader.Close();
- }
- }
- }
- return result;
- }
- private CoreTable DoQueryNonGeneric(Type T, IFilter? filter = null, IColumns? columns = null, ISortOrder? sort = null, int top = int.MaxValue, bool log = false, bool distinct = false)
- {
- var start = DateTime.Now;
- //LogReset();
- //LogStart();
- //LogStop("GetColumns");
- var cols = CoreUtils.GetColumns(T, columns);
- var blobColumns = Columns.Create(T);
- foreach(var column in cols.GetColumns())
- {
- var prop = DatabaseSchema.Property(T, column.Property);
- if (prop is not null && prop.HasAttribute<ExternalStorageAttribute>())
- {
- blobColumns.Add(column);
- }
- }
- if(blobColumns.Count > 0)
- {
- cols.Add("ID");
- }
- var result = new CoreTable(T.EntityName());
- foreach (var col in cols.GetColumns())
- result.Columns.Add(new CoreColumn { ColumnName = col.Property, DataType = col.Type });
- //LogStop("MakeTable");
- using (var access = GetReadAccess())
- {
- using (var command = access.CreateCommand())
- {
- var sortorder = sort ?? LookupFactory.DefineSort(T);
- command.CommandText = "";
- command.Parameters.Clear();
- //LogStart();
- var sql = PrepareSelectNonGeneric(T, command, 'A', filter, cols, sortorder, null, null, top, distinct, true) + ";";
- command.CommandText = sql;
- try
- {
- using (var reader = command.ExecuteReader())
- {
- if (reader.HasRows)
- {
- var rows = new List<CoreRow>();
- while (reader.Read())
- {
- var row = result.NewRow();
- for (var i = 0; i < reader.FieldCount; i++)
- try
- {
- var type = result.Columns[i].DataType;
- if (reader.IsDBNull(i))
- {
- row.Values.Add(result.Columns[i].DataType.GetDefault());
- }
- else if (type.IsEnum)
- {
- ReadAndDecodeValue(result, reader, row, i);
- }
- else if (type == typeof(byte))
- {
- row.Values.Add(reader.GetByte(i));
- }
- else if (type == typeof(byte[]))
- {
- ReadAndDecodeValue(result, reader, row, i);
- }
- else if (type == typeof(bool))
- {
- ReadAndDecodeValue(result, reader, row, i);
- }
- else if (type == typeof(short))
- {
- row.Values.Add(reader.GetInt16(i));
- }
- else if (type == typeof(int))
- {
- row.Values.Add(reader.GetInt32(i));
- }
- else if (type == typeof(long))
- {
- row.Values.Add(reader.GetInt64(i));
- }
- else if (type == typeof(float))
- {
- row.Values.Add(reader.GetFloat(i));
- }
- else if (type == typeof(double))
- {
- row.Values.Add(reader.GetDouble(i));
- }
- else if (type == typeof(decimal))
- {
- row.Values.Add(reader.GetDecimal(i));
- }
- else if (type == typeof(string))
- {
- var value = reader.GetString(i);
- row.Values.Add(value != null ? value : "");
- }
- else if (type == typeof(DateTime))
- {
- ReadAndDecodeValue(result, reader, row, i);
- }
- else if (type == typeof(TimeSpan))
- {
- ReadAndDecodeValue(result, reader, row, i);
- }
- else if (type == typeof(Guid))
- {
- row.Values.Add(reader.GetGuid(i));
- }
- else
- {
- ReadAndDecodeValue(result, reader, row, i);
- }
- }
- catch (Exception e)
- {
- row.Values.Add(result.Columns[i].DataType.GetDefault());
- }
- result.Rows.Add(row);
- }
- }
- reader.Close();
- }
- }
- catch (Exception e)
- {
- OnLog?.Invoke(LogType.Error, e.Message);
- }
- //LogStop("ReadData");
- //LogPrint(String.Format("Query{0}", typeof(T).Name));
- }
- }
- foreach(var column in blobColumns.ColumnNames())
- {
- foreach(var row in result.Rows)
- {
- var id = row.Get<Guid>("ID");
- var data = GetExternalData(T, column, id);
- if(data is not null)
- {
- row.Set<byte[]>(column, data);
- }
- }
- }
- if (log)
- {
- var duration = DateTime.Now - start;
- Logger.Send(LogType.Information, "",
- string.Format("SQLite::Query<{0}>({1} cols) returns {2} rows in {3}ms", T.Name, cols.Count, result.Rows.Count,
- duration.TotalMilliseconds));
- }
- return result;
- }
- private CoreTable DoQuery<T>(Filter<T>? filter, Columns<T>? columns, SortOrder<T>? sort, int top, bool log, bool distinct)
- where T : Entity, new() => DoQueryNonGeneric(typeof(T), filter, columns, sort, top, log, distinct);
- public CoreTable Query(Type type, IFilter? filter, IColumns? columns, ISortOrder? sort, int top, bool log, bool distinct)
- => DoQueryNonGeneric(type, filter, columns, sort, top, log, distinct);
-
- public CoreTable Query<T>(Filter<T>? filter = null, Columns<T>? columns = null, SortOrder<T>? sort = null, int top = int.MaxValue, bool log = false, bool distinct = false)
- where T : Entity, new()
- {
- return DoQuery(filter, columns, sort, top, log, distinct);
- }
- [Obsolete]
- public CoreTable QueryDeleted<T>(Deletion deletion, Filter<T>? filter = null, Columns<T>? columns = null, SortOrder<T>? sort = null, int top = int.MaxValue, bool log = false)
- where T : Entity, new()
- {
- if (filter != null)
- filter.And(x => x.Deleted).IsEqualTo(deletion.ID);
- else
- filter = new Filter<T>(x => x.Deleted).IsEqualTo(deletion.ID);
- return DoQuery(filter, columns, sort, top, false, false);
- }
- private void ReadAndDecodeValue(CoreTable result, SQLiteDataReader reader, CoreRow row, int i)
- {
- var value = reader.GetValue(i);
- var decoded = Decode(value, result.Columns[i].DataType);
- row.Values.Add(decoded);
- }
- public T[] Load<T>(Filter<T>? filter = null, SortOrder<T>? sort = null) where T : Entity, new()
- {
- var result = new List<T>();
- var cols = CoreUtils.GetColumns<T>(null);
- using (var access = GetReadAccess())
- {
- using (var command = access.CreateCommand())
- {
- try
- {
- command.CommandText = "";
- command.Parameters.Clear();
- command.CommandText = PrepareSelect(command, 'A', filter, cols, sort, null, null, int.MaxValue, false, true) + ";";
- using (var reader = command.ExecuteReader())
- {
- if (reader.HasRows)
- while (reader.Read())
- {
- var entity = new T();
- entity.SetObserving(false);
- for (var i = 0; i < reader.FieldCount; i++)
- {
- object value;
- if (cols.Items[i].Expression.Type == typeof(long) && !reader.IsDBNull(i))
- value = reader.GetInt64(i);
- else
- value = reader.GetValue(i);
- try
- {
- var decoded = Decode(value, cols.Items[i].Expression.Type);
- CoreUtils.SetPropertyValue(entity, cols.Items[i].Property, decoded);
- }
- catch (Exception e)
- {
- Logger.Send(LogType.Error, "", string.Format("*** Unknown Error: {0}\n{1}", e.Message, e.StackTrace));
- }
- }
- entity.SetObserving(true);
- result.Add(entity);
- }
- reader.Close();
- }
- }
- catch (Exception e)
- {
- Logger.Send(LogType.Error, "", string.Format("*** Unknown Error: {0}\n{1}", e.Message, e.StackTrace));
- }
- }
- }
- return result.ToArray();
- }
- #endregion
- #region Save
- private static readonly Dictionary<Type, IProperty[]> _externalProperties = new Dictionary<Type, IProperty[]>();
- private static IProperty[] GetExternalProperties(Type T)
- {
- if(!_externalProperties.TryGetValue(T, out var properties))
- {
- properties = DatabaseSchema.Properties(T).Where(x => x.HasAttribute<ExternalStorageAttribute>()).ToArray();
- _externalProperties.Add(T, properties);
- }
- return properties;
- }
- private void OnSaveNonGeneric(Type T, Entity entity)
- {
- var props = GetExternalProperties(T);
- List<(IProperty, byte[])>? data = null;
- if (props.Any())
- {
- data = new List<(IProperty, byte[])>();
- foreach(var prop in props)
- {
- var value = prop.Getter()(entity) as byte[];
- if(value is not null && value.Length > 0)
- {
- data.Add(new(prop, value));
- prop.Setter()(entity, Array.Empty<byte>());
- }
- }
- }
- using var access = GetWriteAccess();
- using var command = access.CreateCommand();
- PrepareUpsertNonGeneric(T, command, entity);
- command.ExecuteNonQuery();
- if(data is not null)
- {
- foreach(var (prop, value) in data)
- {
- SaveExternalData(T, prop.Name, entity.ID, value);
- prop.Setter()(entity, value);
- }
- }
- }
- private void OnSaveNonGeneric(Type T, IEnumerable<Entity> entities)
- {
- // Casting to IList so that we can use it multiple times.
- entities = entities.AsIList();
- if (!entities.Any())
- return;
- var props = GetExternalProperties(T);
- List<(IProperty, List<(Entity, byte[])>)>? data = null;
- if (props.Any())
- {
- data = new List<(IProperty, List<(Entity, byte[])>)>();
- foreach(var prop in props)
- {
- var lst = new List<(Entity, byte[])>();
- foreach(var entity in entities)
- {
- var value = prop.Getter()(entity) as byte[];
- if(value is not null && value.Length > 0)
- {
- lst.Add((entity, value));
- prop.Setter()(entity, Array.Empty<byte>());
- }
- }
- data.Add(new(prop, lst));
- }
- }
- using var access = GetWriteAccess();
- using var transaction = access.BeginTransaction();
- try
- {
- using var command = access.CreateCommand();
- foreach (var entity in entities)
- {
- PrepareUpsertNonGeneric(T, command, entity);
- command.ExecuteNonQuery();
- }
- transaction.Commit();
- if(data is not null)
- {
- foreach(var (property, list) in data)
- {
- foreach(var (entity, value) in list)
- {
- SaveExternalData(T, property.Name, entity.ID, value);
- property.Setter()(entity, value);
- }
- }
- }
- }
- catch (Exception)
- {
- transaction.Rollback();
- throw;
- }
- }
- private void OnSave<T>(T entity) where T : Entity
- => OnSaveNonGeneric(typeof(T), entity);
- public static bool CanSave(Type T)
- {
- if (DbFactory.IsReadOnly)
- {
- if (T.IsAssignableTo(typeof(License)) || T.IsAssignableTo(typeof(UserTracking)))
- return true;
- DbFactory.LogReadOnly();
- throw new DbLockedException();
- }
- return true;
- }
- public static bool CanSave<T>() => CanSave(typeof(T));
- public void Save(Type type, Entity entity)
- {
- if (!CanSave(type))
- {
- return;
- }
- OnSaveNonGeneric(type, entity);
- }
- public void Save(Type type, IEnumerable<Entity> entities)
- {
- if (!CanSave(type))
- {
- return;
- }
- OnSaveNonGeneric(type, entities);
- }
- public void Save<T>(IEnumerable<T> entities) where T : Entity => Save(typeof(T), entities);
- public void Save<T>(T entity) where T : Entity => Save(typeof(T), entity);
- #endregion
- #region Delete
- public void Purge<T>(T entity) where T : Entity
- {
- if (!CanSave<T>()) return;
- using var access = GetWriteAccess();
- using var command = access.CreateCommand();
- PrepareDelete(command, entity);
- var rows = command.ExecuteNonQuery();
- }
- public void Purge<T>(IEnumerable<T> entities) where T : Entity
- {
- if (!CanSave<T>()) return;
- // Casting to IList so that we can use it multiple times.
- entities = entities.AsIList();
- if (!entities.Any())
- return;
- using var access = GetWriteAccess();
- using var transaction = access.BeginTransaction();
- try
- {
- using (var command = access.CreateCommand())
- {
- foreach (var entity in entities)
- {
- PrepareDelete(command, entity);
- var rows = command.ExecuteNonQuery();
- }
- }
- transaction.Commit();
- }
- catch (Exception)
- {
- transaction.Rollback();
- throw;
- }
- }
- private Dictionary<Type, List<Tuple<Type, string>>> _cascades = new();
- private Dictionary<Type, List<Tuple<Type, List<string>>>> _setNulls = new();
- private const int deleteBatchSize = 100;
- private void LoadDeletions(Type type)
- {
- // Get the EntityLink that is associated with this class
- var linkclass = CoreUtils.TypeList(
- new[] { type.Assembly },
- x => typeof(IEntityLink).GetTypeInfo().IsAssignableFrom(x) && x.GetInheritedGenericTypeArguments().FirstOrDefault() == type
- ).FirstOrDefault();
- // if The entitylink does not exist, we don't need to do anything
- if (linkclass == null)
- return;
- var cascades = new List<Tuple<Type, string>>();
- var setNulls = new List<Tuple<Type, List<string>>>();
- var childtypes = Types.Where(x => x.IsSubclassOf(typeof(Entity)) && x.GetCustomAttribute<AutoEntity>() == null);
- foreach (var childtype in childtypes)
- {
- // Get all registered types for this entitylink
- var fields = new List<string>();
- var bDelete = false;
- // Find any IEntityLink<> properties that refer back to this class
- var childprops = CoreUtils.PropertyList(childtype, x => x.PropertyType == linkclass);
- foreach (var childprop in childprops)
- {
- var fieldname = string.Format("{0}.ID", childprop.Name);
- var attr = childprop.GetCustomAttributes(typeof(EntityRelationshipAttribute), true).FirstOrDefault();
- if (attr != null && ((EntityRelationshipAttribute)attr).Action.Equals(DeleteAction.Cascade))
- {
- cascades.Add(new(childtype, fieldname));
- bDelete = true;
- break;
- }
- fields.Add(fieldname);
- }
- if(!bDelete && fields.Any())
- {
- setNulls.Add(new(childtype, fields));
- }
- }
- if(cascades.Count > 0)
- {
- _cascades[type] = cascades;
- }
- if(setNulls.Count > 0)
- {
- _setNulls[type] = setNulls;
- }
- }
- private readonly MethodInfo _deleteEntitiesMethod = typeof(SQLiteProvider).GetMethods(BindingFlags.NonPublic | BindingFlags.Instance)
- .Single(x => x.Name == nameof(DeleteEntity) && x.IsGenericMethod);
- private void DeleteEntity<T>(Guid[] parentIDs, string parentField, DeletionData deletionData) where T : Entity, new()
- {
- var columns = DeletionData.DeletionColumns<T>();
- var entityIDs = new List<Guid>();
- for (int i = 0; i < parentIDs.Length; i += deleteBatchSize)
- {
- var items = new ArraySegment<Guid>(parentIDs, i, Math.Min(deleteBatchSize, parentIDs.Length - i));
- var entities = Query(new Filter<T>(parentField).InList(items.ToArray()), columns);
- foreach (var row in entities.Rows)
- {
- deletionData.DeleteEntity<T>(row);
- entityIDs.Add(row.Get<T, Guid>(x => x.ID));
- }
- }
- CascadeDelete(typeof(T), entityIDs.ToArray(), deletionData);
- }
-
- private void DeleteEntity(Type T, Guid[] parentIDs, string parentField, DeletionData deletionData)
- {
- _deleteEntitiesMethod.MakeGenericMethod(T).Invoke(this, new object?[] { parentIDs, parentField, deletionData });
- }
- private readonly MethodInfo _setNullEntityMethod = typeof(SQLiteProvider).GetMethods(BindingFlags.NonPublic | BindingFlags.Instance)
- .Single(x => x.Name == nameof(SetNullEntity) && x.IsGenericMethod);
- private void SetNullEntity<T>(List<string> properties, Guid[] parentIDs, DeletionData deletionData) where T : Entity, new()
- {
- foreach(var property in properties)
- {
- var columns = new Columns<T>(x => x.ID);
- columns.Add(property);
- for(int i = 0; i < parentIDs.Length; i += deleteBatchSize)
- {
- var items = new ArraySegment<Guid>(parentIDs, i, Math.Min(deleteBatchSize, parentIDs.Length - i));
- var entities = Query(new Filter<T>(property).InList(items.ToArray()), columns);
- foreach (var row in entities.Rows)
- {
- deletionData.SetNullEntity<T>(row.Get<T, Guid>(x => x.ID), property, row.Get<Guid>(property));
- }
- }
- }
- }
- private void SetNullEntity(Type T, List<string> properties, Guid[] parentIDs, DeletionData deletionData)
- {
- _setNullEntityMethod.MakeGenericMethod(T).Invoke(this, new object?[] { properties, parentIDs, deletionData });
- }
- private void CascadeDelete(Type type, Guid[] parentIDs, DeletionData deletionData)
- {
- if(parentIDs.Length == 0)
- {
- return;
- }
- if (_cascades.TryGetValue(type, out var cascades))
- {
- foreach (var cascade in cascades)
- {
- DeleteEntity(cascade.Item1, parentIDs, cascade.Item2, deletionData);
- }
- }
- if(_setNulls.TryGetValue(type, out var setNulls))
- {
- foreach(var setNull in setNulls)
- {
- SetNullEntity(setNull.Item1, setNull.Item2, parentIDs, deletionData);
- }
- }
- }
- public void Delete<T>(T entity, string userID) where T : Entity, new()
- {
- if (!CanSave<T>())
- {
- return;
- }
- if (typeof(T).HasAttribute<UnrecoverableAttribute>())
- {
- Purge(entity);
- var props = GetExternalProperties(typeof(T));
- foreach(var prop in props)
- {
- DeleteExternalData(typeof(T), prop.Name, entity.ID);
- }
- }
- else
- {
- entity = DoQuery(
- new Filter<T>(x => x.ID).IsEqualTo(entity.ID),
- DeletionData.DeletionColumns<T>(),
- null,
- int.MaxValue,
- false,
- false
- ).Rows.First().ToObject<T>();
- var deletionData = new DeletionData();
- deletionData.DeleteEntity(entity);
- CascadeDelete(typeof(T), new Guid[] { entity.ID }, deletionData);
- var tableName = typeof(T).Name;
- var deletion = new Deletion()
- {
- DeletionDate = DateTime.Now,
- HeadTable = tableName,
- Description = entity.ToString() ?? "",
- DeletedBy = userID,
- Data = Serialization.Serialize(deletionData)
- };
- OnSave(deletion);
- Purge(entity);
- }
- }
- public void Delete<T>(IEnumerable<T> entities, string userID) where T : Entity, new()
- {
- if (!CanSave<T>())
- {
- return;
- }
- entities = entities.AsIList();
- if (!entities.Any())
- return;
- if (typeof(T).HasAttribute<UnrecoverableAttribute>())
- {
- Purge(entities);
- var props = GetExternalProperties(typeof(T));
- foreach(var prop in props)
- {
- foreach(var entity in entities)
- {
- DeleteExternalData(typeof(T), prop.Name, entity.ID);
- }
- }
- }
- else
- {
- var ids = entities.Select(x => x.ID).ToArray();
- var entityList = Query(
- new Filter<T>(x => x.ID).InList(ids),
- DeletionData.DeletionColumns<T>()).Rows.Select(x => x.ToObject<T>()).ToList();
- if (!entityList.Any())
- return;
- var deletionData = new DeletionData();
- foreach (var entity in entityList)
- {
- deletionData.DeleteEntity(entity);
- }
- CascadeDelete(typeof(T), ids, deletionData);
- var tableName = typeof(T).Name;
- var deletion = new Deletion()
- {
- DeletionDate = DateTime.Now,
- HeadTable = tableName,
- Description = $"Deleted {entityList.Count} entries",
- DeletedBy = userID,
- Data = Serialization.Serialize(deletionData)
- };
- OnSave(deletion);
- Purge(entities);
- }
- }
- private void AddDeletionType(Type type, List<Type> deletions)
- {
- deletions.Add(type);
- if (!_cascades.TryGetValue(type, out var cascades))
- return;
- foreach (var cascade in cascades)
- {
- AddDeletionType(cascade.Item1, deletions);
- }
- }
- private Dictionary<Type, List<Type>> _allCascades = new();
- private List<Type> GetDeletionTypes(Type type)
- {
- if(_allCascades.TryGetValue(type, out var cascades))
- {
- return cascades;
- }
- var deletionTypes = new List<Type>();
- AddDeletionType(type, deletionTypes);
- _allCascades[type] = deletionTypes;
- return deletionTypes;
- }
-
- public void Purge(Deletion deletion)
- {
- if (!CanSave<Deletion>())
- {
- return;
- }
- var data = Serialization.Deserialize<DeletionData>(deletion.Data);
- if(data is not null)
- {
- foreach(var (entityName, cascade) in data.Cascades)
- {
- if (!CoreUtils.TryGetEntity(entityName, out var entityType)) continue;
- var props = GetExternalProperties(entityType);
- foreach(var prop in props)
- {
- foreach(var entity in cascade.ToObjects(entityType).Cast<Entity>())
- {
- DeleteExternalData(entityType, prop.Name, entity.ID);
- }
- }
- }
- }
- Purge<Deletion>(deletion);
- }
- public void Recover(Deletion deletion)
- {
- if (!CanSave<Deletion>())
- {
- return;
- }
- if (deletion.ID == Guid.Empty)
- {
- Logger.Send(LogType.Error, "", "Empty Deletion ID; Recovery cancelled");
- return;
- }
- var data = Serialization.Deserialize<DeletionData>(deletion.Data);
- if (data is null)
- {
- Logger.Send(LogType.Error, "", "Deletion Data deserialisation failed; Recovery cancelled");
- return;
- }
- foreach (var (entityName, setNulls) in data.SetNulls)
- {
- if (!CoreUtils.TryGetEntity(entityName, out var entityType)) continue;
- var saves = new List<Entity>();
- foreach(var setNull in setNulls)
- {
- var row = DoQueryNonGeneric(
- entityType,
- Filter.Create<Entity>(entityType, x => x.ID).IsEqualTo(setNull.EntityID),
- Columns.Create(entityType)
- .Add<Entity>(x => x.ID)
- .Add(setNull.Property),
- null,
- 1,
- false,
- false
- ).Rows.FirstOrDefault();
- if (row is null) continue;
- var entity = (row.ToObject(entityType) as Entity)!;
- CoreUtils.SetPropertyValue(entity, setNull.Property, setNull.ParentID);
- saves.Add(entity);
- }
- OnSaveNonGeneric(entityType, saves);
- }
- foreach(var (entityName, cascade) in data.Cascades)
- {
- if (!CoreUtils.TryGetEntity(entityName, out var entityType)) continue;
- OnSaveNonGeneric(entityType, cascade.ToObjects(entityType).Cast<Entity>());
- }
- Purge<Deletion>(deletion);
- }
- public List<Type> GetDeletionTypes(Deletion deletion)
- {
- var entityType = CoreUtils.Entities.First(x => x.Name == deletion.HeadTable);
- return GetDeletionTypes(entityType);
- }
- #endregion
- #endregion
- #region External Data Storage
- private string ExternalDataFolder(Type T, string columnName, string idString)
- {
- return Path.Combine(
- Path.GetDirectoryName(URL) ?? "",
- $"{Path.GetFileName(URL)}.data",
- T.Name,
- columnName,
- idString.Substring(0, 2));
- }
- private byte[]? GetExternalData(Type T, string columnName, Guid id)
- {
- var idString = id.ToString();
- var filename = Path.Combine(ExternalDataFolder(T, columnName, idString), idString);
- try
- {
- return File.ReadAllBytes(filename);
- }
- catch(Exception e)
- {
- //Logger.Send(LogType.Error, "", $"Could not load external {T.Name}.{columnName}: {e.Message}");
- return null;
- }
- }
- private void SaveExternalData(Type T, string columnName, Guid id, byte[] data)
- {
- var idString = id.ToString();
- var directory = ExternalDataFolder(T, columnName, idString);
- Directory.CreateDirectory(directory);
- var filename = Path.Combine(directory, idString);
- File.WriteAllBytes(filename, data);
- }
- private void DeleteExternalData(Type T, string columnName, Guid id)
- {
- var idString = id.ToString();
- var directory = ExternalDataFolder(T, columnName, idString);
- Directory.CreateDirectory(directory);
- var filename = Path.Combine(directory, idString);
- if (File.Exists(filename))
- {
- File.Delete(filename);
- }
- }
- #endregion
- }
- }
|