protocol.txt

When the client connects, the authentication phase starts. To start,
there will be only one authentication mechanism: SQLSERVER. The client
sends an AUTH command selecting SQLSERVER, with an initial response of
<username-in-UTF8> '\0' <password-in-UTF8>. This is then encoded as a
hex string (as specified by the D-Bus spec) to be sent. The server
will then respond with either OK or ERROR as appropriate.

Authentication will happen by either creating a new connection to the
SQL server (if no pooled connections exist) or looking up the pool
that corresponds to the username and comparing the password given. If
this fails, it will try opening a new connection to the SQL server in
case the password changed (if so, replacing the existing stored
password in the pool). Existing connections will be left alone in this
case.

Authorization at this point is just that all queries are guaranteed to
pass through a connection that is attached to the username that was
used when authenticating. The SQL server will enforce
table/view/procedure permissions, etc.

The API itself is really simple right now:

Operations on node /db
Interface name is vx.db

ExecNoResult(string query IN)
- Will throw a vx.db.sqlerror error (with the error
text being the complaint by the SQL server) in case of error

ExecScalar(string query IN, variant result OUT)
- Will throw a vx.db.sqlerror error (with the error
text being the complaint by the SQL server) in case of error
- The maximum amount of data that can be received this way is 16MB.
There is a vx.db.toomuchdata error for this

ExecRecordset(string query IN, VxColumnInfo[] colinfo OUT,
        variant data OUT, byte[][] nullity OUT)
- Will throw a vx.db.sqlerror error (with the error
text being the complaint by the SQL server) in case of error
- will throw a vx.db.badschema error if unable to
represent the results
- Null values are specified by the byte being 1; non-null values are
specified by the byte being 0. The data itself is in the variant.
* Note: Using a byte so that less space is wasted -- booleans are
32-bit and require 4-byte alignmen
- Data is in row-major order
- The variant that holds the data is an array of a struct that varies
based on the query: each field in the struct is a column in the result
set. In D-Bus terms, the signature of the variant is "a(...)" where
"..." varies. The array may have length zero.
- The maximum amount of data that can be received this way is 16MB.
There is a vx.db.toomuchdata error for this.
- VxColumnInfo is a struct in the form "a(issnny)"
  int32 Column size
  string Column name
  string Column type
  int16 Numeric precision
  int16 Numeric scale
  uint8 Nullability
    - This is a bitfield with only one bit defined:
      0x1 = is nullable

For this API, there are no guarantees that a series of queries will
run through the same connection (so no transactions, temporary tables,
or the like)... unless this is actually necessary. I have some ideas
for how transactions could be done but that complexity seems to be
unnecessary at this point. It will need to be extended anyway to
support bigger recordsets.

Column types (names are case-insensitive):
"int64" (sent as a dbus int64, format "x"): MSSQL bigint
"int32" (sent as a dbus int32, format "i"): MSSQL int
"int16" (sent as a dbus int16, format "n"): MSSQL smallint
"uint8" (sent as a dbus byte, format "y"): MSSQL tinyint
"bool" (sent as a dbus boolean, format "b"): MSSQL bit
"double" (sent as a dbus double, format "d"): MSSQL float, real
* There is no dbus float, so those are converted to double
"uuid" (sent as a dbus string, format "s"; sent in canonical form [hex
with dashes in particular spots]): MSSQL uniqueidentifier

"binary" (sent as dbus array of bytes, format "ay"): MSSQL binary,
varbinary, image, timestamp

"string" (sent as a dbus string, format "s"; UTF-8 encoding): MSSQL
char, varchar, text, nchar, nvarchar, ntext, xml
* Need to figure out what to do with non-ascii characters in
char/varchar/text (presumably MSSQL will suggest some encoding that
the database was configured as).

"datetime" (sent as dbus struct of int64 & int32, format "(xi)"; same
format as struct timeval: first is seconds since unix epoch, second is
microseconds (always nonnegative); timezone is not specified): MSSQL datetime,
smalldatetime

"decimal" (sent as dbus string, format "s"): MSSQL decimal, numeric,
money, smallmoney
* Acceptable if of the form: ^[+-]?[0-9]*(\.[0-9]*)?$
* If no +/- is provided, assume +
* "-0" == "+0" == "0"
* That regex matches "". I declare that "" is zero.
* Although this allows for an arbitrary (but finite due to string
length limits) number of leading or trailing zeros, they should be
avoided for efficiency reasons.
* What's recommended to be actually sent:
 - Zero is "0"
 - Within (0,1), it's "0.xy" where x is [0-9]*, y is [1-9]
 - Within (-1,0), it's "-0.xy" where x and y are as above
 - ".xy" and "-.xy" should be considered OK as well
 - Otherwise, numbers should be of the form "yx.xy" or "-yx.xy"