SQL

Work Plan

• Write @query to do parsing and call a dummy function that does nothing but log the request.

Completed in 2.4 source tree.

• Write two sides of sqlmaster/sqlslave connection to do nothing more than bring the channel up and handshake once.

Completed in 2.4 source tree.

• Change @query so that it calls sqlmaster instead.
• Have sqlmaster pass the query across the pipe.
• Have sqlslave return it and log the response.
• Add ability to send configuration options from sqlmaster to sqlslave.
• Change configuration reader to send parsed configuration options to sqlmaster and ultimately to sqlslave.
• Write result set classes and methods.
• Add ability to transfer result sets from sqlslave to sqlmaster. Log response.
• Write reference counting result set context.
• Execute softcode.
• Add functions to navigate result set.

SQL Connector Design

• Avoid blocking main netmux process.
• Navigate large result sets using modestly-sized softcode buffers.
• Recover gracefully from missing or inaccessible SQL server.
• Support multiple simultaneous SQL connections.

Since the main netmux process may be servicing players, one of the design goals of this connector is to avoid any blocking calls to an external database. We intend to do this using a separate sqlslave across a Unix pipe.

Because the queries and results are copied across the socketized pipe between the sqlmaster and sqlslave, latency for the initial query may be longer, but once the pipeline is filled with queries, this approach should make better use of all the resources (keeping the sqlslave, netmux, and the external SQL server busy). Furthermore, any problems with the SQL database or the interface to it are isolated to a separate process and do not put the main game at risk.

Asyncronous Access

The @query command allows softcode to express asyncronous queries much like the @wait command allows commands to execute at a later time. Instead of waiting for time to pass, the @query would wait on a reponse. In cases where a query takes too long, the SQL server becomes inaccessible, or the query is somehow misplaced, there needs to be some timeout semantics so that code can be told that the query failed. It probably makes sense for this timeout to come from a configuration option instead of softcode.

The details of the query are given to the sqlmaster and softcode continues unblocked. Using a socketized pipe, sqlmaster communicates with a separate sqlslave process. sqlmaster is not allowed to make blocking calls. Any unsuccessful writes or reads are tried later.

sqlslave sometimes blocks waiting for requests from sqlmaster and sometimes blocks during calls into the specific SQL interface. Once a response to a particular query has been retrived, it is passed back to sqlmaster.

sqlmaster matches the response from sqlslave to the original query, and causes a code attribute given with the @query command to execute.

Syncronous Access (discouraged)

Syncronous Access is also supportable. A corresponding sql() or query() function can be used instead. This approach necessarily blocks further command processing until the response is received.

The sqlmaster provides the same service as above, however, in this case, it does block waiting for a response from the sqlslave. Other queries may be in process, and it must be able to accept and queue these while waiting for syncronous response.

Another limitation of the syncronous/functional approach is that the response is limited to the size of a single buffer.

Results Set

Once the asyncronous query returns its result, navigating this result is done through a set of rs*() functions: rsend(), rsfirstrow(), rslastrow(), rsnextrow(), rsprevrow(), and rsfields(). Softcode can navigate very large results set as long as no single field is larger than 8KB. Output delimiters are given to these rs*() functions instead of in the original @query.

Several queries can be made at the same time. Each of these would return its own results set. Each result set could potentially trigger the same or a different dbref/attribute. This attribute could in turn trigger other attributes. All of these contexts are in play at the same time. That is, the result set and the positional context continue across @trigger'ed commands.

In fact, the entire cascade of commands requires the use of reference counting. Being able to navigate the result with a mix of commands and functions should soften the mismatch between the size of a SQL results set and the modest bounds (8KB) of a softcode buffer.

Once the last command related to the original @query finishes, the last reference count on the response's memory is released, and the memory is freed.