External Components – Database Queries

As I began to think about adding the ability to access a database to the Exploratory Project I was aware that I would need to use a language that had APIs to interface to databases.  

There is a GNAT Ada supported way of accessing databases as described in section 23 of the GNATColl document at http://docs.adacore.com/gnatcoll-docs/sql.html.  However, I don't want to get involved with learning it at the present time.  However, it supports access to PostgreSQL and SQLite which can be interfaced via Windows or Linux so will have to look into the use of those databases some time.

What I'll do next is to provide an interface to a database software system to implement queries.

Instead of using an Ada interface, at least to start with, I will treat a C# application in a manner somewhat similar to how the C# display application is currently treated.  That is, more or less like an external/isolated component – meaning a framework component that is external to the user application. 

This due to the C# processes not having access to user application framework.  Since they lack the access they can't register themselves with the framework not the message topics that they consume or produce. 

This could be remedied via the use of a C or C++ component to access the database.  Microsoft Visual C++, at least, has the use of the Microsoft libraries that support SQL database access.  So I could go that route if I wanted to find out how to reference those libraries in a GNAT build.  Maybe later.  Although, most likely it will be using the GNAT support that GNATColl provides.

But first I will look into providing the use of paired application processes of independent builds where one provides the use of the framework and its interface to other user applications enabling any user application to send topic messages and the second application process of the pair, the external component, actually interfaces to the database.

Proposed Architecture:

The user application architecture that I'm thinking of is to clone that used in the current Exploratory Project for communication with the display app.

That is, like the display app – which can now be referred to as the external display component where "external" means that the component is in its own build rather than part of the application build. 

As with the external display component there will be one or more external database components as required.  These can be like the A661 layers that paint different displays or display areas when multiple databases are concerned.  For lack of any better descriptor to use at the moment I will refer to the database equivalent of an A661 layer as a database box (in the sense of a container).

Likewise, there will be an internal/local database component to manage a particular box.  This will correspond to the GUI_n component for interfacing to A661 layer n.

The A661 layers are defined by the xml display definition file that is loaded into both the user application and the external display component.  In the user application it is parsed to discover the widget ids that can be sent for widget events and the graphical items that can be displayed or hidden.  The result is stored in a table of the Widget package and used in conjunction with widget action components that act upon a particular widget event.  The GUI_n component can then do a lookup to determine what widget action component treats the widget event and forward the received message to it.  The widget action will then create a widget command message and return it to the layer management component for transmit to the display layer.

My current thinking is that something similar can be done in regards to a database.  Except that other user components will be the initiators rather than operator widget clicks and the like.

A local or remote user component will want a piece of information and send a request topic to have it returned to it.  The component that registers to consume the topic could supply the information for computations or such that it performs or, in this case, be the database management component. 

If the latter, it could look up a local query action component that would format the query into SQL and forward the SQL message back to the database management component which would than transmit it to the external database component.  The external database component would just need to interface to the database via the query (after connecting to the database, if currently not connected) and return the result to the database management component.  The database management component could then forward the response to the particular query action component to interpret the response and extract the data that satisfies the original request.  It would then publish the response to the original request just as if it had been locally available.

The query actions most likely do not need to be components – that is, have their own thread.  Instead, just callback procedures running under the database management component thread.

This initial work is just to interface to a database rather than to determine what queries should look like.  Therefore, user components will send Context Filtered or Requested Delivery message topics without knowing how the requested item might be stored and found.  It will be the consumer component that has this knowledge in the Query Action (QA) procedures which are hidden from the requesting component.  Therefore, the requesting component can't form a SQL query; the Query Action will do so.

As with the A661 layer management component, the query management component (DBI_n) it looks up the query action associated with the topic in the SQL package table by the identifier in the request topic message.  The table is built from a database schema as well as application specific data such as which Query Action that treats the request topic message.

Similarly for a database, the xml could be replaced by the database schema and used to build a Schema package table that would also reference SQL Query Action procedures of the Database management application.  Upon receiving a request from another component, the Database management component of the Database management application, could forward the message to the particular SQL Query Action component that would build the SQL query and return it to the management component to transmit to the external component / database application (or, with SQLite, the internal component). 

That's the idea.  Now to determine if it works.