WSQLite3 `(v0.7 under development)`

versions <0.5 are dysfunctional

versions <0.7 contains unclean thread management, which can cause zombie processes

WSQLite3 (WebSocket SQLite3) is a simple and lightweight solution to manage access from multiple processes (or threads) to SQLite3 databases.

What it is and what it is not:

The focus of WSQLite3 is on simple local session management of multiple instances of a program or compatibilization of different programs. WSQLite3 is not intended as a database in a wide-ranging system for multiple end users. For this, other advanced database systems with client servers should be used.

WSQLite3 in the basic version is designed for friendly connections, an SSL is NOT implemented and there is NO permission handling (Caution: WSQLite3 also allows remote code execution).

How it works and properties:

WSQLite3 manages connections to SQLite3 databases for multiple clients per server. The communication via the WebSocket protocol with JSON data provides simple and high compatibility between different programs and programming languages. The focus of the project is on the WSQLite3 service, which is intended as an independent process. For complex projects, a separate implementation of the client side should be embedded.

install:

python -m pip install wsqlite3 --upgrade

The service can be started and managed via the command line. For more information, run:

wsqlite3 help

since 0.5, extensive upgrades since version 0.7

Project Wordings

Connection	refers to the object of the
Cursor	refers to the object, a derivative of sqlite3.Cursor (interface for database operations)
side/main (with direct reference to Cursor)	the main cursor refers to the cursor managed by the , whereas a side cursor is only managed by its own connection and does not guarantee consistent results
Order	refers to incoming order JSONs that are processed on the side and outgoing order JSONs

Identifications

wsqlite3uid	for the identification of Connection's and database operations. Follows the scheme: `<hex(seconds since the Epoch)>-<hex(pid\|tid)>#<hex(instance counter)>` (`tid` is used in almost all systems for the middle segment)
wsqlite3threadName	for the identification of ConnectionsThread's. Follows the scheme: `t<n>`

The service module

The objects in the service module of WSQLite3 offer several interfaces and preliminary work for inheritance in order to add application-related functionalities.

The main object is the , which in its simplest form can be defined for example by

server = wsqlite3.Server(("localhost", 9998))
server.add_database(None, ":memory:")

and started via

server.run()

or as a daemon thread via

server.daemon = True
server.start()

By default, the accepts an unlimited number of connections (connections_per_thread), each of which is processed asynchronously as a connection object in the same thread. Depending on the use case/expected number of connections, it may be useful to split the connections across multiple threads, even if they are asynchronous.

Orders

Orders to the are placed as JSON as described below, single or in a list [<order> , ...]. The sequence in which the order (sub)sections and their instructions are listed here corresponds to the sequence of -side execution (in the basic version).

Order (sub)sections are basically optional. Depending on the section, a specific instruction pattern may be expected. As a general rule, an instruction that is not set but is expected leads to a KeyError; if a more specific instruction pattern is expected but not fulfilled, this leads to an OrderError (see also Error Handling).

In the basic version, the entire order JSON is otherwise NOT checked for plausibility, unknown (or incorrectly written or set in the wrong place) sections or instructions are simply ignored (however, values in the methods are obtained via dict.pop).

The return value is usually:

{
  "orders": [
    {
      <Orders>...,
      "errors": (0)|(code),
      "error": null | <last occurred Exception Message>,
      "flag": null | <"flag">
    },
    ...
  ],
  "errors": (0)|(code),
  "error": null | <last occurred Exception Message>,
  "flags": null | <"flag">, ...
}

(see also Error Handling)

The outgoing order is structurally the same as the incoming order and is made up of valid sections that were in the order and the return fields according to the instructions.

The value under "errors" indicates whether errors have occurred in one of the sections (see also Error Handling).

How to deal with binary data from/to the database

As a pure text-based data format, bytes cannot be transferred directly via JSON. Therefore, binary data is always transmitted in base64 format. If a binary value is retrieved from the database on the server side, it is transmitted to the client as a base64 string. The client must therefore be able to determine when and where base64 strings appear that need to be decoded, as the coding cannot be distinguished.

On the other hand, in order to be able to transmit binary data to the server, the "tb:params" statement (which replaces the "params" statement) is available for "sql" operations. 's within the object passed to it must be prefixed with either "t:" or "b:":

"t:"	following characters are plain text and are not decoded on the server side
"b:"	following characters are a base64 string that is decoded into bytes on the server side

server << {
  "sql": {
    "exec": "INSERT INTO main VALUES (?, ?)",
    "tb:params": [ "t:Hello", "b:V29ybGQ=" ]
  }
}
server << {
  "sql": {
    "exec": "SELECT * FROM main",
    "fetchone": true
  }
}
server >> {
  "sql": {
    "fetch": [ "Hello", "V29ybGQ=" ]
  }
}["sql"]["fetch"][1].b64decode().decode("utf8") == "World"

Structure

▶ toggle collapse all

{

"flag": –> "flag" tagging

since 0.5

Assign a flag to the order to identify it in the response.

"ping": { ping the

"ping": –> "pong": (ns timestamp), "error", "error"

"ping": –> "pong": (ns timestamp)

}

"_exec": { remote code execution

"code": (pythoncode) –> "result": , "error", "error"

"code": (pythoncode) –> "result":

"result" is obtained by globals().get("result") or locals().get("result") after exec("code", globals(), locals())

}

"_getattr": { get attribute

"of": ("connection"|"server"|"thread") "path": (attribute path) [ "call": args, kwargs ] –> "result": , "error", "error"

"of": ("connection"|"server"|"thread") "path": (attribute path) [ "call": args, kwargs ] –> "result":

"of" describes the object from which the attribute is to be obtained. "path" represents a direct attribute name or a python attribute path ("." separated). If "call" is defined, the obtained object is called with (*args, **kwargs). "result" is the attribute respectively the result from the call.

}

"connection": { operations

"get": (wsqlite3-uid) | description record, ... [ "set": ("session"|"response") | ], "id": –> "id": (wsqlite3uid), "send": , "description": –> "description": description record, ..., "description.pop": (key) –> "description.pop": (value), "description.set": description record, ..., "description.update": description record, ..., "properties": –> "properties": properties, "destroy": , "error", "error"

"get": (wsqlite3uid) | description record, ... "set": | ("session"|"response")

Via "get" a foreign connection object can be obtained. If description record, ... are transferred, the first connection object is used whose description records are the same as the parameterization. In the following, all instructions in the section refer to the connection received. If no connection is found via the id or entries, an IdError is raised. With the "get" instruction, the "set" instruction is processed, this can extend the scope of the effect of get:

	also references the connection to all subsequent sections of the order
("session")	references the connection to the entire session
("response")	references the connection as the connection to be sent back to

"id": –> "id": (wsqlite3uid)

Query the id of the current connection.

"send":

Send to the client of the current connection.

"description": –> "description": description record, ..., "description.pop": (key) –> "description.pop": (value), "description.set": description record, ..., "description.update": description record, ...,

Query the description or edit it. description is a dict of the object that can be freely defined by the user and can be used for identification, for example.

"properties": –> "properties": properties

Query the properties of the current connection:

{
  "id": <wsqlite3uid>,
  "description": <description>,
  "timestamp": <creation time>,
  "address": <client socket (host, port)>,
  "thread": <thread.properties>,
}

"destroy":

Close the current connection.

}

"server": { operations

"connections": –> "connections": connection.id: connection.properties, ..., "threads": –> "threads": thread.id: thread.properties, ..., "shutdown": | ("force") | ("commit") | ("force,commit"), "error", "error"

"connections": –> "connections": connection.id: connection.properties, ...

Query a register:

{
  <connection.id>: {<connection.properties>},
  ...
}

"threads": –> "threads": thread.id: thread.properties, ...

Query a thread register:

{
  <thread.id>: {<thread.properties>},
  ...
}

"shutdown": | ("force") | ("commit") | ("force,commit")

Shutdown the . If ("force") is passed, the system does not wait for the session lock of a connection or database to be released. If ("commit") is passed, all databases are committed before closing.

}

"thread": { operations

"get": (wsqlite3threadName), "id": –> "id": (wsqlite3threadName), "broadcast": "broadcast.self": , "connections": –> "connections": connection.id: connection.properties, ..., "properties": –> "properties": properties, "error", "error"

"get": (wsqlite3threadName)

Via "get" a specific object can be obtained. In the following, all instructions in the section refer to the thread received. If no thread is found via the id, an IdError is raised.

"id": –> "id": (wsqlite3threadName)

Query the id of the current .

"broadcast": "broadcast.self":

Send the as a broadcast to all 's of the thread (if "broadcast.self" is set, also to the that orders the broadcast):

{
  "broadcast": <>,
  "from": <wsqlite3threadName>
}

"connections": –> "connections": connection.id: connection.properties, ...

Query a register of the current :

{
  <connection.id>: {<connection.properties>},
  ...
}

"properties": –> "properties": properties,

Query the properties of the current :

{
  "id": <wsqlite3threadName>,
  "timestamp": <start time>,
  "thread.ident": <thread.ident>,
  "TID": <thread.native_id>,
}

}

"sql": { database access

"keys": (key) | , ...,

Query the database key list.

"open": sqlite3-args, sqlite3-kwargs [ "get": (key) | ] –> "open": (key) |

Open and register a database. sqlite3-args, sqlite3-kwargs is passed to sqlite3.connect. An individual session key can be transferred via the "get" field, otherwise an wsqlite3uid is generated and returned in "open". If the field is explicitly set to , the database is set as the default database, which does not have to be retrieved via the "get" instruction in the future. Throws an ConfigurationError if the session key is already in use. The following instructions of this order refer to this database.

"get": (key)

Define a previously registered database to which the instructions refer. If the field is not set, an attempt is made to obtain the default database. Raises a ConfigurationError if the database is not registered.

*"side": *"force": *"sudo":

Parameterizes which/how a is obtained for operations/closed. By default, all values are False. If a is successfully obtained and no OrderError is raised in the section, it is locked to the current connection.

Evaluation of parameters for execution instructions ("exec" "scrip" "many")

"side"	get the side cursor instead of the main cursor (wording)
"force"	get the cursor even if it is locked by the current connection
"sudo"	get the cursor even if it is locked by another connection (has no effect with "side")

Evaluation of parameters for fetch instructions ("fetchone" "fetchmany" "fetchall")

"side"	get the side cursor instead of the main cursor (wording)
"force"	get the cursor even if it is NOT locked (is not implied in "sudo")
"sudo"	get the cursor even if it is locked by another connection (has no effect with "side")

Evaluation of parameters for the "release" instruction

"side"	get the side cursor instead of the main cursor (wording)
"force"	not evaluated
"sudo"	get the cursor even if it is locked by another connection (has no effect with "side")

Evaluation of parameters for the "close" instruction

"side"	get the side cursor instead of the main cursor (wording)
"force"	do not wait for the lock to be released and close the side-cursor/database forcibly
"sudo"	get the cursor even if it is locked by another connection (has no effect with "side")

"lock": –> "lock": (wsqlite3uid) |

Query the id of the connection that locks the cursor.

"arraysize": (True) | (size) –> "arraysize": (size)

Query or set the attribute arraysize of (does not lock the cursor).

"description": –> "description": (column name), , , , , , , ... |

Query the attribute description of (does not lock the cursor).

"lastrowid": –> "lastrowid": |

Query the attribute lastrowid of (does not lock the cursor).

"rowcount": –> "rowcount":

Query the attribute rowcount of (does not lock the cursor).

Execute (SQL). Exactly one instruction is expected within the section, otherwise an OrderError is raised. If the cursor is locked, CursorLockedError is raised.

"script": (SQL)	sqlite3.Cursor.executescript
"exec": (SQL) [ "params"\|"tb:params": placeholder bindings \| placeholder bindings ]	sqlite3.Cursor.execute
"many": (SQL) "params"\|"tb:params": placeholder bindings \| placeholder bindings	sqlite3.Cursor.executemany

( ("fetchone"|"fetchall": ) | ("fetchmany": (True) | (size)) ) –> "fetch": database record, ... |

Execute the corresponding fetch method. Only one instruction is allowed within the section, otherwise an OrderError is raised. If no cursor is locked or cannot be obtained successfully, CursorNotLockedError respectively CursorLockedError is raised.

"fetchone"	sqlite3.Cursor.fetchone
"fetchall"	sqlite3.Cursor.fetchall
"fetchmany"	sqlite3.Cursor.fetchmany

"release": | ("finally") –> "release":

Release the lock on the cursor. If ("finally") is set, the cursor is automatically released if a fetch method has returned or the "fetchall" method has been used. If cursor cannot be obtained successfully, CursorLockedError is raised. Does nothing if the cursor is not locked. Returns whether a release has been performed.

"rollback":

sqlite3.Connection.rollback

"commit":

sqlite3.Connection.commit

"close": –> "close":

In combination with "side", only the side cursor is closed, otherwise the database is closed and removed from the register. Returns whether the closing was performed.

}

"broadcast": { broadcasting

"message": , "self": , "error", "error"

"message": "self":

Send the as a broadcast to all 's (if "self" is set, also to the that orders the broadcast):

{
  "broadcast": <>,
  "from": <wsqlite3uid>
}

}

"autoclose": { autoclose configuration

"cancel": (true|explicit false) | (2) | , "value": ("block") | ("request") | (0) | ("skip!"), "config": –> "config": autoclose config, "config.block": , "config.request": , "config.wait_response": (seconds), "config.wait_close": (seconds), "config.force_shutdown": , "config.sql_commit": , "trigger": , "error", "error"

since 0.5, fixes with 0.6

"cancel": (true|explicit false) | (2) |

Response to a request (see autoclose).

(true)	cancel the automatic closing
(2)	cancel the automatic closing even if there is no connection after the timeout
(false)	execute the automatic closing explicitly

"value": ("block") | ("request") | (0) | ("skip!"),

Set the value of the current connection (see autoclose).

("block")	the connection blocks the automatic closing (default)
("request")	if no connection is blocking, this connection triggers the request whether to close
(0)	neutral
("skip!")	special value: skips the complete feature if the triggering connection has the value

"config": –> "config": autoclose config, "config.block": , "config.request": , "config.wait_response": (seconds), "config.wait_close": (seconds), "config.force_shutdown": , "config.sql_commit": ,

Query or edit the autoclose configuration (see autoclose).

"config": –> "config": autoclose config	Query the current configuration
"config.block":	Block the automatic closing if a connection contains the value ("block"). (false) disables the feature and the automatic closing is never executed. Default is (true).
"config.request":	Request from all clients (except the causing client) whether the automatic closing should be executed if no connection blocks and if a connection contains the value ("request"). (false) disables the feature. Default is (true).
"config.wait_response": (seconds)	Wait for the reactions of the requests. The server will remain open for this time in any case. The previous conditions are then checked again; if the request condition is met again, the execution of the automatic closing depends on the reactions of the requested connections ("cancel"). Default is (8.0).
"config.wait_close": (seconds)	Wait after neither the condition for prevention nor request has occurred. Then check the conditions again. The server remains open for this time in any case. (Subsequently, the feature of requests could be triggered ("config.request_at")). Default is (8.0).
"config.force_shutdown":	Is passed to "server": "shutdown" during the automatic closing. Default is (false).
"config.sql_commit":	Is passed to "server": "shutdown" during the automatic closing. Default is (false).

"trigger": ,

Trigger an autoclose check (see autoclose).

}

"errors":

(0) | (code)

This field is set according to error codes for each order.

"error":

| Exception Message

This field is set to the last Exception Message that occurred within this order.

}

autoclose

since 0.5, fixes with 0.6

The service closes automatically by default. The conditions for this are checked each time a client disconnects. As long as a connection exists and has the "autoclose" "value" ("block"), automatic closing is prevented (default). The "autoclose" "value" ("request") can be assigned to a connection in a graduated manner, which activates - if no connection is blocking - the request whether closing should be performed. For this the JSON below is sent to all clients (except the causing one). As a reaction to this, the order "autoclose" "cancel" is available.

{
  "autoclose": {
    "trigger": <connection.id>,
    "reason":
      ("handshake timeout")|
      ("loop canceled")|
      ("erract")|
      ("fatal error")|
      ("trigger ordered")|
      ("destroy ordered"),
    "connection_total": <current connection total>
  }
}

Error Handling

Own Exception Types

CursorLockedError
CursorNotLockedError
OrderError
ConfigurationError
IdError

Exception catching

All exceptions that are directly related to the processing of an order are caught and handled according to the next subchapter.

The handling of exceptions that are raised outside of this is described in the subsection after next.

Error handling when processing orders

The "error" instruction can be assigned to each section of the incoming order that contains instructions. Possible parameters:

("cancel order")	cancels the entire order
("cancel session")	cancels the entire session
("destroy connection")	cancels the entire session and destroys the current connection
("shutdown server")	cancels the entire session and shuts down the server
("force shutdown server")	cancels the entire session and shuts down the server forcefully
("ignore")	continues to execute the following sections of the order

In addition, the "sql" section evaluates whether the parameter ("+rollback") is appended (e.g. ("cancel order +rollback")).

If the instruction is not set or is set differently, the entire session is canceled.

Regardless of the handling, the current section is canceled and the "error" field in the section of the outgoing order is set according to the error formatting. In the basic version this is:

{
  "type": <exception class name>,
  "args": <exception args>,
  "repr": <representation of the exception>,
  "message": <string of the exception>,
  "pypickle": <the representative string of bytes of the picked object representation>,
  "params": null | <the params attribute of the exception ( set in some Own Exception Types )>
}

"pypickle": Only useful if the client is in the python system. Generated by repr(pickle.dumps(exc)), which is faster in encoding but slower with decoding ( raise pickle.loads(eval(error["pypickle"])) ) than base64 encoding.

If an error occurs at any time, the "errors" field of the return order is set to the highest error code that has occurred:

{
  "orders": [
    {
      <Orders>...,
      "errors": (0)|(code),
      "error": null | <last occurred Exception Message>,
      "flag": null | <"flag">
    },
    ...
  ],
  "errors": (0)|(code),
  "error": null | <last occurred Exception Message>,
  "flags": null | <"flag">, ...
}

(0)	no errors occurred
(2)	errors occurred but no action performed
(3)	errors occurred and order canceled
(4)	errors occurred and session canceled

Error handling after fatal errors during order processing

For extreme exceptional cases, e.g. errors are not intercepted in a modified basic version or if a connection is interrupted exactly during the processing of an order, there is a graduated handling:

First an attempt is made to send the JSON {"errors": -1, "error": Exception Message} if this fails, an attempt is made to send a null byte, if this leads to an error, an attempt is made to terminate and remove the connection properly, if this also leads to an error, .shutdown(force=True) is executed finally.

Up ↑

Thanks to MarkText for md sections

WSQLite3 (v0.7 under development)