Long term database support for X# (XDS)

[RobertvanderHulst]

In the last week the X# devteam has discussed the long term plans for data access. The text below is a not very structured summary of these discussions. The work on this long term solution will be bundled in the Ξάνθη (Xanthi) project

Traditionally our community has been using the DBF format for its data storage. This format was popular because it was easy to use and use in a networked environment by multiple users was also relatively fast. In the last years people have found that the use of DBF files in a network has become more and more complicated because of changes in the Windows networking environment.

An alternative way to store and access data has traditionally been in SQL databases. These databases have the advantage that there usually is a central server process that manages the data and the locking and updating from this data and that only the rows from the tables needed by the clients were sent over the network. However working with data in SQL databases requires a different style of working compared with the record oriented data approach of DBFs.

In the past a product such as A**dvantage Database Server (**ADS) has tried to combine the best of both worlds by allowing you to use both the traditional record oriented data access as well as the SQL set oriented database access. Unfortunately the future of ADS is uncertain. It looks like SAP (the current owner of ADS) is planning to stop the development of ADS.

FoxPro has introduced in the past the ability to access data from DBF files with both the traditional record oriented approach as well as by using a set oriented approach with "embedded SQL" commands.

In an attempt to add support for both of these approaches we have looked at ADS, but in the light of the uncertainty of the future of ADS we have decided that we will take an alternative approach. We have also looked at adding SQL support to access data in our DBF layers but we have decided not to add that because that is fairly complicated and there are already many existing SQL solutions on the market. We have therefore decided to start to work on a solution that meets the following conditions:

• You should be able to work both in Record Oriented Set oriented mode
• The traditional Xbase commands such as Go Top, Go To, Seek, Replace, Append and Delete should work
• We should also add support for embedded SQL
• Both ways to select data should result in updatable cursors
• You should be able to create (local) filters and (local) indexes on results of both types
• We want to create a system that works on any .Net SQL database provider

These are the building blocks that we need to support this

• An RDD that allows you to use a "table based" approach against a SQL database. You should be able to open a table by name and the system should translate that to a SQL query. Creating an index "the xbase way" should result in a SQL Index and a order by clause and traditional seeks should work and not create a subset of the data but position the current position in the cursor on the row that matches the seek value. Skipping back and forward should be possible
• An RDD that allows you to query Set based data access return the data in a local cursor. This will not support the "traditional" seeks. When you set a filter then the RDD will try to convert this to a SQL Where clause and will create a server side filter and only bring back the rows to the client that match the filter
• We will create a server side service that uses these 2 RDDs to connect to the database (where you can mix table and set based data access). This service could even use "single user" databases, such as for example SQLite and will allow multiple clients to connect to the same "single user" database. Of course the server can also use data from databases that allow more than one user to access, such as MS Sql Server or MySql
• The SQL RDDs will use special classes that helps to customize SQL differences between different SQL dialects. For example the syntax to select a maximum number of rows differs from database to database. You have "Top " for SQL Server and "Limit " for MySql. Also the characters that are used to delimit column names may differ between servers. These are especially useful when column names are used that are the same as built in keywords. For example SQL Server uses "[name]" where MySql uses "name"
• By default the SQL RDDs will have support built in for ODBC, OLEDB and SQLServer (this support is in the System.Data assembly from .Net) For other Ado.Net providers we will create additional assemblies that can also have a dependency on vendor specific code, such as MySql, SQLite or Postgres SQL.
• We will create a client side RDD that communicates with the server over IP

The Server process

• The server will have a "data access" interface through which the clients can connect. When clients open a result set (either record or set oriented) then the server will fetch this and store this in an Ado.Net Datatable.
• The Server will have a http "management interface" in which you can see which clients are connected and which tables these clients are using
• The server will be able to communicate with the clients, to inform them for example that they need to close their connection because the server is being brought down for maintenance. The server will also detect that a client has been inactive for a while and will try to ask the client if they are still "alive" after it has not heard from the client in a while.
• This same connection may be used (in the future) to inform clients about updates by other users to tables that they have opened.
• The server will keep the "cursor state" for each table that the client has opened in a DataSession per client.
• After a period of inactivity the server will close the cursors that the client has opened to save resources
• To be able to work with User Defined Functions (UDFs) we will allow the user to specify one or more "custom assemblies" that contain functions that can be used in queries and index expression. These "custom assemblies" must contain a certain prescribed interface that lists the names and other information about the functions exposed by these assemblies
• The server will work with the concept of "users" and you will be able to indicate a user name for a connection. If the database supports users this will be the real database user and then you can also use the security rules for the backend database to grant and revoke access to certain table, columns or views, depending on the possibilities of the server. If the backend database does not know the concept of users (like SQLite) then we can create our own table of users /password to grant access to the database. However in that case you will not be able to restrict access to tables, columns or views.
• There will be a configuration file for the server to specify which Ado.Net provider to use for the data access and also the connection string to connect to that database.

The Client RDD

• When the client connects it sends a username/password. Depending on the back end this is a real username/password for the database or a username/password in the custom users table
• The client will send messages to the server to open a table and for navigation
• The client will not cache the data and will only locally have the current row of the result set.
• When a table is opened then the client will fetch only the first row(s) in the result set. The server keeps a cache of all the rows.
• In the case of a result set retrieved with a SQL command then the client will have an Ado.Net datatable, so changes for more than one row can be locally cached and sent to the server when needed.
• The client will send "keep alive" messages frequently to let the server know that they are still running
• The client will wait for incoming messages from the server about "management" info and maybe other messages in the future

Database Requirements

• When the server process opens a database it may (optionally) add one or more tables to the database to store additional information to the database, such as the information that can be found in the FoxPro DBC files. You can think of things like captions, descriptions, pictures and input masks for columns, descriptions and properties for tables and views and information about remote views (tables or views in another database which will be opened with the normal ODBC Connection or other Ado.Net connection available on the server.)
• Another table may be created (when needed) to store user names and passwords.
• If the user wants to emulate table based data access then every table must have a unique primary index and an autonumber column to emulate the record number and a column to emulate the "Deleted" state of a row. There will be a default name for these columns, but you will be able to change these defaults with a configuration file.

Table mode data access

• If the user wants to emulate table based data access then every table must have a unique primary index and an autonumber column to emulate the record number and a column to emulate the "Deleted" state of a row. There will be a default name for these columns, but you will be able to change these defaults with a configuration file.
• The RDD by default will not fetch all the rows of a result set. That would be way to much in most scenarios. There will be a default maximum size that you can (globally) override in a configuration file and per table if you implement a special callback function (a Delegate) that the RDD can call to query for more information

Configurable settings

• We will use configuration files to configure as many settings as possible, so our system becomes very flexible.

Embedded SQL

• The embedded SQL in the X# language will capture the complete SQL string and send it to the backend. We will partially parse this string to determine if there are local variables or User Defined Functions used in these strings that will not be recognized by the backend. When found then these variables will be replaced with parameters and the value of the locals (privates) will be passed as parameter value to the backend.
• The result of an Embedded SQL statement will be a local in-memory cursor where the data is stored in an Ado.Net datatable, just like the data for SqlExec() is now stored in an in-memory cursor
• Create Table statements will be sent to the SQL backend and will also result in a (empty) local cursor that is bound to the backend
• Create Cursor statements will be sent as temporary table to the SQL backend and will result in an (empty) local cursor that is bound to the temporary table in the SQL Backend. Some backends do not support temporary tables In that case (which we can detect using the provider for the backend) we will use a special naming scheme for temporary tables and the server process will delete these tables at startup and when a user connection is closed.
• By sending the cursors to the backend too we can also join tables and cursors Update, Delete and Insert statements will both affect the local cursor and will also be sent to the SQL Backend