stuarta0 / dbqf Goto Github PK

Scenario: advanced search created referencing a date field in Standalone.WPF (but should affect any standalone version) that has a custom date parser. Saved and loaded in a new instance and the date fails to parse with the custom parser and instead uses the dbqf.core parser.

For some reason a search with a date between AND string equals causes a NullReferenceException when determining parameters.

In the previous version of the library (in SVN, not git; not included in commit history), the tree layout was defined by an XML template, with a callback for formatting when the threaded operation for loading child nodes had completed.

Problems with the old method:

• Most of the logic was hard coded into a UserControl with a private TreeView so you couldn't do anything with the actual tree control. This is pretty much the issue - you have no control.
• Attempting to handle node Font from the formatting callback caused OutOfMemoryException for large data due to GDI objects exceeding 10,000 (a fault of the WinForms TreeView in fact).
• Efficiency issues relating to the way queries were constructed and executed. This has been resolved with the current library.

Aims:

• Still provide the killer feature of having a fully searchable tree based on any field anywhere in the data that is very responsive (whether this is threaded or not).
• Disconnect the UI from the definition and fetching of the tree (obviously). This will allow using other UI toolkits with the hierarchical data without code duplication.
• Provide modular code to swap in a different fetch strategy so the UI is oblivious to whether the data has come from a database via the standard dbqf functionality or whether the strategy is just supplying nodes itself (e.g. some nodes may not be backed by data like a root node labelled 'Invoices').
• Add DTO for serialisation of tree definition if plausible to do so (it'll probably needs it's own basic fetch strategy to accompany it)
• Nice to have: provide a user-editable tree configurator.

Move behaviour of IParameter ToSqlString into dbqf.Sql library (as well as SqlString class itself). Inherit every existing concrete IParameter (e.g. LikeParameter -> SqlLikeParameter), and implement the ToSqlString (possibly with another ISqlParameter interface too, which defines this).

All the ParameterBuilder logic should probably move into the dbqf.Sql library, leaving interfaces behind in the core (maybe?). Or copies, depends on whether having ParameterBuilders that make vanilla IParameters is even worth it? (i.e. we'll always need a backend of some kind, so the implementetions will usually end up there).

Even the behaviour of Subject.SourceSQL and .Sql(string) should probably move into dbqf.Sql as they have no bearing on an ORM implementation. Check references on these properties to see how often they are actually referenced (if only in the SQL and Standalone projects then we're all good).

Make an ISqlGenerator interface based on the dbqf.Sql SqlGenerator, and another for the ISqlListGenerator.

Make a dbqf.MsAccess version, which references core and dbqf.Sql as we'll be wanting to use the IParameter types defined in dbqf.Sql (maybe). Create concrete implementations of SqlGenerator with MsAccess-specific SQL structure.

For EF, make a dbqf.EF project, with Configuration, Subject and Field types that bind to property info (or something) that defines mapping within EF. Either use the vanilla IParameter implementation in core (if FieldPath is enough), or inherit and provide further functionality for building LINQ expression trees for each IParameter (more likely). Rather than have an SqlGenerator class, we'll need to determine what kind of class is required to take the LINQ expression trees from the parameters and combine them together and execute via EF. Remembering it is possible to run direct SQL through EF so this is an option.

Standalone should have a factory for generating all required objects for querying and retrieving results. Instead of ISqlResults, it should probably be more like BackendFactory which has methods for getting an ISqlGenerator, ISqlListGenerator, and the concrete DbConnection/DbCommand objects. Or, we just encapsulate all the logic and don't even expose generators or connections, just have an IDbService which only has:

DataTable GetResults(FieldPath[], IParameter)
List<string> GetList(IField)

Then we can have a factory class that returns an IDbService based on string key for the list of connections in a configuration; "SqlClient", "SQLite", and "MsAccess". In fact the connection object in the configuration should really just serialise the concrete type (e.g. rather than object Connection, have an SqlClientConnection which has ConnectionString, an SQLiteConnection which has Filename, etc.).

After master checkout on a clean OS there are a few remaining code maintenance tasks to complete:

• Remove the SQLite.Interop.dll files. These should be handled by NuGet.
• Replace Standalone field output with PathFactory.GetFields(SelectedSubject) until #5 is complete.
• Update README to outline getting started process:
  1. Restoring NuGet packages
  2. Make the Standalone project the startup project
  3. Add an example XML configuration file, SQLite database and use that as the Standalone command line argument
• Update README to indicate no external libraries are needed (except for Standalone).

It would be nice to have a DateTimePicker/MonthCalendar dropdown for date fields, maybe with another embedded UIElement next to the dropdown button so it can piggyback the existing date parser logic.

Out of curiosity it would be interesting to see how hard it would be to make a Gtk# frontend version. Either that or a Xamarin app version.

Trying to add a parameter that has no UIElement causes a NullReferenceException when accessing UIElement.GetValues() (since UIElement = null).

If a user doesn't have read access to a database the standalone application will crash with a Windows error dialog before it loads the list data for the fields.

Add project to public NuGet repository

No small task this one. Might be worth creating a utilities library to move all current serialisation (DTOs and assemblers), export services and data querying here, then reuse all these objects between the WinForms and WPF versions of the application. Castle Windsor will be fine with configuring the container via the external library. In fact, some of the IWindsorInstaller's can be moved to that library too, with the container initialisation occurring in both applications.

Take existing logic from dbqf.WinForms.Advanced.AdvancedPartView and create a control that stacks combos (and adds/removes them) as the user chooses fields that have relationships to other fields.

Allow the control to take a FieldPathFactory so at any depth of the path the next set of fields can be retrieved.

This control can be used for the user-defined output field selection and for the advanced search control. Later this control can be developed in WPF and whatever else.

Allow users to select their own fields for output when executing a search. Currently the behaviour is to get the default set of fields through the built-in FieldPathFactory (which gets everything but the ID field for a subject). This will allow users to drill-down through relationships and get information embedded in other areas of the data. For example; rather than just Invoice.Authoriser, they could traverse the relationships to get the field Invoice.Authorisor.Location.Postcode.

This development has been started in Standalone.Forms.RetrieveFieldsView.

See #7 to aid development of this feature.

All the tests for SqlGenerator were commented out when the change from ToCommand() to UpdateCommand() was made.

Update the test cases to follow the current logic and restore the tests.

Before we hit project explosion and it becomes too hard to navigate the solution, break down the projects into solution folders: https://docs.google.com/document/d/11fvFK2N0aCvDrAsxLZq8gDcNiTOkSfkrsookGU-mpzc/edit?usp=sharing

Provide base code for serialisation of an IParameter graph in the Standalone project based on XML. Protocol Buffers is nice to have for using over RESTful APIs, but for now it just needs to work locally.

Current implementation being developed under Standalone.Serialization.DTO.Criterion. The existing DTOs are heavily tied to protobuf so move these to their own namespace and redesign the classes for use with standard XML serialization.

TBH the whole 'matrix' concept in the configuration is smelly. It's long-winded and difficult to maintain and yet it's defined in the very heart of the project. However it's usage is only ever really exploited when an SQL query is generated. Would not a better way to manage determining relationships across the schema be to follow those structures already in place: i.e. IRelationFields?

So if 99% of the code ignores this concept, why not leave it out of IConfiguration and ConfigurationImpl? What if we just leave it up to the consumer to provide their own method for figuring this out? We could still provide these two methods (matrix and IRelationField traversal) as concrete implementations if consumers want to use them.

So the concept behind traversing IRelationField would be something like:

• On Subject, define a Dictionary<Subject, FieldPath[]> that indicates how to get from A to B
• Lack of destination Subject key in dictionary indicates no relationship
• FieldPath[] holds one or more paths through the data structure
• Since a subject may only be accessible via the traversal from the destination, we'd need a way of defining a FieldPath and saying it runs in reverse (or checking other Subjects' dictionaries for a FieldPath that ends at this Subject).

Make sense? Good. Simpler is better.

Add the ability to double click an existing parameter in the advanced search view and for it to set the field/operator/value to this parameter ready to add a new one (or replace the existing one)

If we setup the installers from the configuration file it allows users of the standalone application to swap out behaviour without needing to change or recompile the standalone application.

Having Standalone.exe is really unhelpful. Even dbqf.standalone.exe would be infinitely better.

Use "dbqf.winforms.exe" and "dbqf.wpf.exe"

I had an issue attempting to use the .NET's default System.Func definition. Even when using System;, and having no references to dbqf namespaces, writing Func<MyType, bool> predicate; always refers to the dbqf type because this delegate is defined in the global namespace. Not a huge issue, but it does result in unnecessarily fully qualified variable types when the System.Func definition was desired, but it probably wasn't the intention in more recent .NET iterations which include their own similar definition.

I propose this line be changed to include the delegate within the dbqf namespace.

Add another option for REPLACE in advanced search when a parameter is selected in addition to the existing AND or OR options. This will remove the selected parameter and add the new parameter in it's place, maintaining existing AND/OR structure. Equality of the field path is irrelevant.

Add control that searches multiple fields simultaneously, optionally with slug behaviour. e.g. "price>50 name=Bicycle"

In the first iteration of the project (in SVN, not git; code not included) a user was able to select fields from anywhere in the data, whether it was via a traversal through a relationship or pulled arbitrarily from somewhere else in the data. The user would then choose the criterion and value(s) and add it to a hierarchy of parameters. Previously these parameters were represented in a binary tree with and/or behaviour.

The replacement core functionality simplifies the parameter structure into conjunction (and)/disjunction (or) with lists of parameters each. This will aid in simplifying the UI too.

The development has been started in dbqf.WinForms.Advanced.

See #7 to aid development of this feature.