Configuring Database Servers

The new version of Pyrrho has a completely approach to server configuration, based on a peer-to-peer database. In this article, we look at the configuration of distributed databases, addressing the "Pyrrho and the Cloud" scenario from 2009, and return to issues of partitioned databases in a forthcoming article.

Each server has its own folder for holding databases (although full pathnames can be specified for databases). The configuration database (if any) is found in this folder and is called _ ,. It contains tables with names starting with _. The basic schema of _ is hardwired in the server engine so that _ is created the first time the server owner alters a configuration table.

By default the configuration tables are empty: databases that are not mentioned in the configuration tables are local databases, and we know about no other servers. In this way we already have an API for configuration (namely SQL/ADO.NET) and we conform to Codd’s rule that SQL should be the DDL for the DBMS.

Configuration tables holding server data are automatically partitioned so that each _ database only holds information for data managed by that server instance, and is the transaction master for its own data. Thus any change to properties of remote servers is handled as a remote update – the Pyrrho protocol already supports this. It turns out that the provision for verification of distributed transactions is exactly what is required to guarantee consistency of the information held by different servers.

When the server starts up it should load the _ database if present. What will be new is a more elegant handling of the situations where servers are unavailable: obviously this should mean that their databases and partitions are unavailable. Code exists in the server already for deferring distributed transaction verification and this is exactly what is needed for if and when the remote server comes online later. See also “Database dependency” below.

As of v 5.0, we will use the following schema for _:

_Client: (Server, Client, User, Password). We locally store entries for which Server=us. This table is maintained automatically: don’t update it. Passwords use Pyrrho’s password type so are not readable. The user and password information enables access to the configuration database on the client.

_Database: (Name, Server, Password, ServerRole, Remote, RemoteUser, RemotePassword). (Remote, Server) references _Client. We locally store entries for which Server=us. Passwords use Pyrrho’s password type so are not readable, but they must be supplied when this table is updated to enable server-server communication. The remoteuser and remotepassword information enable server-server access to the named database on the remote server. The password information enables access to the configuration database on the server.

_Partition: (Base, BaseServer, BasePassword, Table, PartitionName, PartitionServer PartitionServerConfigUser, PartitionServerConfigPassword, SegmentName, Column, MinValue, MaxValue, IncludeMin, IncludeMax). Table and Column are positions in the base database: we understand that the partition database has appropriate entries in its schema and a mapping from the base positions. We locally store entries where BaseServer=us.  If you create such an entry, Pyrrho automatically defines the table in the partition database, and makes a Partition1 record in the partition database that gives the server details. If the PartitionServer field is null, the partition is marked as dropped. The partition user and password are for server-server access from the base database: the base server password is a security precaution: it must match the base server’s account. The PartName field enables a number of tableparts to be specified as being in the partition: each part can specify a number of column conditions.

Remember that _ is for distributed configuration information, and is not affected by connection-specific things such as creating or accessing non-configured databases. The current server’s serverRole for non-configured databases will be 7 (for local databases: Query+Master+Storage) and 0 (remote), but they are not entered in the configuration tables. No server is obliged to have complete information.

In the _Database table, Remote is null if ServerRole has bit 1 (Master), and otherwise references cause distributed transactions: e.g. a server providing query and/or storage only will usually list the master server as remote. If no master server is configured the database is read-only (this is not an error). However, at most one server can have the master role.

To change configuration information, the owner of the server account simply connects to the local _ database and modifies it. The information about other servers is read-only, although selects, joins etc show the overall information to the extent required for server-server communication (server roles and partitions). When changes are committed, affected databases are unloaded and a background process is started to reload them. Pyrrho does try to notify affected servers, using the same mechanism as for any update to a distributed database: this will be an area for continuing improvements.

Note, however, that the server passwords in the configuration tables are for enabling access from one server to another, using standard operating system security, for the accounts under which the servers have been configured to run. The partition base credentials enable access to the base database of the partition being configured. On a single server or in a single domain, it is quite likely that a single account and password will be used in all or most of the tables.

As a special case, the entry in the _Database table for a partition will have all 3 server roles and the remote information will be for the base server.

A simple configuration example

Three servers A  , B,  C  initially with no configuration databases. Database D on server A has a table E(F int). We will configure these servers so that both A and C serve queries for D, but C will access storage for D on server B, while A will retain its roles of Query+Master+Storage. 

This will show in the _ database as follows:

_Client table

Server	Client	User	Password
A	B	M\me	*******
B	C	M\me	*******

 _Database table

Name	Server	ServerRole	Password	Remote	RemoteUser	RemotePassword
D	A	7
D	B	4	*******	A	M\me	*******
D	C	2	*******	B	M\me	*******

As we get there we can keep track of developments using Pyrrho's system and log tables. Log tables show the contents of physical database files (so are available when the server has Query+Storage role).

Step 1: Create _ on B and configure storage for database D on A.

 pyrrhocmd –h:B _

SQL> [insert into "_Database" values('D','B',4,passwd,'A',user,passwd)]

The right user name (in Windows Domain\User form) and password should be supplied, but the password won’t be displayed. These two steps have the side effect of getting A to create a _ database and fill in the  _Client entry for B and _Database entry for D, so if A is offline this step should fail. This also creates a D.pfl file in B’s folder.

At this stage the Log$ files for _ on A and _ on B show different Records, but table “_Database” and table “_Client” will agree on the two servers, showing that the configuration database is shared.  

Step 2: Create _ on C and configure the Query ServerRole for database D on B.

pyrrhocmd –h:C _

SQL> [insert into "_Database" values('D','C',2,passwd,'B',user,passwd)]

Pyrrho discovers A's master role. The _Database table shows as above on server B at this stage, (A and C show 2 rows each) although “Sys$Table” and table “Log$” on each server shows just one record in _DatabaseTable E shows in D on A and C,  and E should be listed in Sys$Table on D but the Log$ table on D should not be visible since there is no D.pfl file in C’s folder. div>

This completes the configuration. On C delete an entry in E, and check for expected behaviour.  On C, alter table E to make F a primary key. Check that the Sys$Index tables on both A and C show the change.  We can read information from database D on C even if A is offline. We can’t change anything though.

See a full version of this tutorial, with full explanations of the protocols used,  here .

Optimising enumerations

Like most database engines, at the heart of Pyrrho is an implementation of B-Trees (not binary trees). In Pyrrho this is implemented by a generic abstract class ATree <K,V> for a tree of key-value pairs. There are lots of subclasses of ATree, some of which implement weakly-ordered or multilevel indexes.

The ATree method GetRowEnumerator returns a SlotEnumerator <K,V> that traverses the pairs of the tree in key order. There are two versions of this method, one of which supplies a Key for matching. This will enumerate all pairs where the key matches the given one. Now for a strongly-ordered tree (no key duplicates) the resulting enumeration will have 1 or zero entries (a TrivalEnumerator or an EmptyEnumerator) provided the key supplied will be a constant. By constant is meant "will not change during result-enumeration of any current query".

This is a very subtle and important point: Pyrrho uses partial evaluation so that a Column for values such as integers, shows just the current value, but this can change when an enumerator moves to the next row. Such values are obviously not constant, and so if the Key value supplied to GetRowEnumerator was such a value, while it would still be true that in each case there is either one or zero matching pairs in the tree, we need to check to find out which.

On the other hand, it is such an important optimisation to be able to replace an enumerator with a trivial or empty enumerator that it seems worth adding some machinery to the database engine to keep track of which expressions are constant. The illustration shows a code fragment from the database engine.

As a result of these considerations many structures (e.g. Column, TypedValue and all their subclasses) have an extra field or property with a name such as isConstant to speed up this determination.

Since the key K might be something very simple such as long or string, the IsConstant() method used in the illustration needs to be defined as an extension method. To my relief I find that Debian Squeeze supports the use of C# extension methods so henceforth Open-source Pyrrho OSP has moved back up to .NET 3.5. For Windows of course we currently use .NET 4.

Needless to say the above changes resulted in about 600 changes to the Pyrrho sources, and it is possible that some mistakes will need fixing. I have been doing quite a lot of testing and will continue to do so. For the next while there will be updates of Pyrrho roughly weekly.

Multisets and Arrays

This week's update makes a number of small alterations to Pyrrho's syntax, mostly to bring it better in line with the SQL standard. Specifically, array and multiset initialisation (if not using a subquery) uses [] instead of (). Subqueries are always introduced with a leading parenthesis as in (SELECT .., and are used in predicates such as IN, EXISTS, UNIQUE. The Open Source distribution has been moved back to .NET v2.0 so that it works with a larger set of Linux distributions.

Charting enhancements

This week's version of Pyrrho 4.8 has modified slightly the output flags that can be added to tables and columns for automatic charting. Table metadata can include Pie, Line, Points, Histogram. Column metadata can include X, Y and Caption. Legend can be usefully added to Pie and Histogram: if legend is not specified Pyrrho writes the captions directly into the chart. The colours are selected automatically. An example is shown here.

In this example the values in the table are added explicitly: in a real application this would be a presentation database and the table would be filled by some business analytics process. Feedback is always welcome on features of this sort.

Version 4.8 released

This brings standard SQL diagnostics management. Standard SQL exception handling includes three kinds of handlers for errors: CONTINUE, EXIT and UNDO. Pyrrho honours not_found if it is handled but otherwise does not report an error condition if DML operations do not affect any rows. Pyrrho's strict interpretation of correct transaction behaviour is supported by better prompts in the command line interface and a prohibition on using COMMIT inside a SQL routine (ROLLBACK is allowed but aborts the routine). The meaning of UNDO is very interesting: if this handler is activated, all of the database changes performed so far in the scope of the handler declaration are undone. This is a great improvement on ROLLBACK or EXIT as it allows for subtle and sophisticated error handling. Exceptions arising from most of Pyrrho's SQLSTATEs can be handled: you cannot handle such errors as damaged files, bad syntax, or attempts to defeat the transaction mechanism. In addition the keywords not_found and sqlexception etc are supported, and it is permissible to use your own signals (the usual rules for identifiers apply). The diagnostics area in the SQL standard is interesting. There is a set of keywords, including message_text, some of which have prescribed default values for each exception. You can override these using SET assignments in a SIGNAL statement, and collect their values by using GET DIAGNOSTICS, which allows assignment of such data identified by keywords to variables that you specify. In addition, a roundup of improvements results in better behaviour of such things as structured types. The philosophy nowadays is that client software should not need to know about how data types are implemented in the server, but that (as in XML or JSON) should simply give a full and readable description of structured data. The Pyrrho engine uses strong data types, so the server describes each part of the data by giving the nominal data type and the actual data subtype if it is different. For example, the actual type might include semantic information or metadata. Such actual types are visible to SQL and the user when they are explicitly assigned to the value using the RDF/OWL ^^ suffix or the SQL TREAT function. Unfortunately some previous cross-platform support for other APIs is now lost, although many of the interfaces in ADO.NET and JPA are still supported. For example, there seems to be no way for .NET's DataTable to support (nested) structured types, and so DataAdapters and some of the more complex JPA interfaces have been dropped in this edition. The venerable PyrrhoMgr tool is a casualty of this change. I still rather like the PyrrhoSQL tool but it needs someone other than me to improve its usability.

Version 4.7 published

This version improves transaction handling by allowing an explicit ROLLBACK to terminate an SQL routine such as a store procedure. (COMMIT is not allowed inside a routine since it would cause premature termination of the routine.) New error messages for this behaviour means that the client libraries OSPLink and PyrrhoLink have been updated. Transaction behaviour is more transparent in the command prompt tool PyrrhoCmd, for example by the use of a special prompt SQL-T> inside transactions. Other fixes help with tables constraints, parsing of error handlers and default values for strings.

CQRS: Command Query Responsibility Segregation

There is currently a lot of interest in the above concept, e.g. at CQRS info, which basically looks at separating database reads (queries) from writes (commands) when considering scaability and replication. A June 2012 Channel 9 video in MSDn by Grigori Melnik and Julian Dominguez included the first picture here:

In 2010, in a presentation on "A cloud database with transactions" at DB Tech, Helsinki, I included the second picture here on how Pyrrho's cloud version could do the same thing:

The really important aspect here is the convergence between the ideas involved in long-running transactions and eventual consistency with the optimistic transaction model. It offers some hope in the current nightmare world of no-transactions/no-SQL nonsense. For Pyrrho's approach see my 2010 blog post.