Sqmediumlite

Python - SQLite network connection

version 2.1.1, Edzard Pasma, 21st December 2011

1. Introduction

SQLite is a serverless database engine and applications directly read and write the database files. Files can be placed on a network file system however at a not-great performance and at non-attractive quality of concurrent use.

The network connection provided in this Python package connects to databases on the network at full speed and quality, at the cost of running a permanent process on the file server side. In terms of SQLite it is no longer serverless. Hence the name "mediumlite". Still it is much lighter than a true RDBMS and interactions like connecting and querying are very fast.

Other tools

Besides the network connection this package contains some independent tools: apswdbapi2, rowfactory and apswtrace. A description is found in the sources, the links are below. The remainder of this document is about the network connection only.

Copyright

The package is experimental and may act as example. There is no copyright except on the tools that are copied from APSW "Another Python SQLite Wrapper". This is indicated in the source.

Source files

tools	description	lines	modified
__init__.py	network connection	310	Wed Dec 21 2011
apswdbapi2.py	standard interface based on APSW	331	Wed Dec 21 2011
apswtrace.py	enhanced APSW SQL tracer	591	Fri Nov 25 2011
rowfactory.py	exposes columns as row attributes	46	Wed Dec 21 2011
internal
_backend.py	back-end of network connection	216	Tue Dec 20 2011
_common.py	socket connection	131	Wed Dec 21 2011
_conf.py	configurable settings	29	Fri Dec 09 2011
_service.py	Windows service handler	76	Thu Dec 08 2011
_shell.py	interactive shell	155	Tue Dec 20 2011
_shellbase.py	APSW interactive shell	2942	Mon Dec 19 2011
_test.py	test suite	2133	Wed Dec 21 2011

2. Installation

Installation of the complete package is only required for the network connection. The other tools can well be used stand-alone and copied from the source.

The package is downloaded from pypi.python.org/pypi/sqmediumlite/#downloads, either as windows installer or as source archive.

To install from source, start a command or shell prompt and change directory to the unzipped source archive. Here enter:

python setup.py install

Dependencies

The Python version must be 2.6, 2.7, or 3.1+.

APSW "another Python SQLite wrapper" may be used by the back-end of the network connection. This occurs if it happens to be installed. By default the back-end relies on the standard Python sqlite3 module (Pysqlite).

On Windows, the back-end side of the network connection requires pywin32 "Python for Windows extensions", downloadable from:

sourceforge.net/projects/pywin32/files/pywin32.

Most convenient is the binary (.exe) version, which includes the required Windows SDK elements.

Test

The package has an integrated test suite that covers all components except apswtrace. The test can be started as a setup option:

python setup.py test

Or as a method in the package:

import sqmedium
sqmedium.test ()

3. Connecting

The network connection is used by importing sqmediumlite instead of the standard Python sqlite3 module. The interfaces are identical except for some attributes that are hard to handle over the network. Differences are listed in __init__.py.

The location of the back-end (the file server) must be specified one way or another. By default this is taken to be the local host. One way to change this is to specifiy it as part of the database name:

import sqmedium as sqlite
con = sqlite.connect ("//h1972688.stratoserver.net/test.db")

Another way is in a sqmediumconf.py file (last chapter)

interactive shell

The interactive shell is copied from APSW "Another Python SQLite Wrapper" and this is modelled after the SQLite shell (CLI). I thankfully refer to the APSW document: apidoc.apsw.googlecode.com/hg/shell.html.

The FILENAME argument can now include a network location:

python -c "import sqmedium; sqmedium.main ()" //h1972688.stratoserver.net/test.db

test database

The host in above examples is a VPS that I set up for testing. It can be addressed only if one is directly connected to the internet and not via a proxy server.

4. Back-end process

A process must have been started on the file server side before connecting. This back-end process is controlled by special methods of the main package. One must be logged in on the file server to do this. Basically the start() and stop() are all that is needed. However extra configuration is required to make the process automatically start at system startup. The methods are also available as commands in the interactive shell.

Windows

On Windows a permanent process is started with the service() method. As this is a one-time action it can be interactively done through the interactive shell:

Microsoft Windows XP [versie 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\EdzPas>mkdir dbfiles

C:\Documents and Settings\EdzPas>cd dbfiles

C:\Documents and Settings\EdzPas\dbfiles>python -c "import sqmedium; sqmedium.main()"
SQLite version not available (APSW version not available, sqmediumlite 2.1.1)
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .service --startup auto install
Installing service Sqmedium%20C%3A%5CDocuments%20and%20Settings%5CEdzPas%5Cdbfiles
Service installed
sqlite> .service start
Starting service Sqmedium%20C%3A%5CDocuments%20and%20Settings%5CEdzPas%5Cdbfiles
sqlite> .quit

Linux

Linux and traditional unix require a control script in /etc/init.d. For sqmediumlite this can be a minimal script of just one line, based on the sqmediumlite shell (used non-interactively). The shell supports all the necessary functions (start/stop/restart/status) and the script only needs to pass the parameter in the form expected by the shell:

su -l dbowner -c "(cd /home/dbowner/dbfiles; python -c 'import sqmedium; sqmedium.main ()' '' .$1)"

'dbowner' is a non-root user and '/home/dbowner/dbfiles' is the working directory of the process.

The command is saved in /etc/init.d as sqmedium1.sh (or any name) and must become executable:

chmod +x /etc/init.d/sqmediumlite1.py

The script must still be linked to the various run levels. In Ubuntu linux this is one command:

update-rc.d sqmedium1.sh defaults

Other unix-like systems may have other commands or may expect to manually create the symbolic links in /etc/rc[0-6].d.

OS X

Mac OS X no longer has init.d. I ignore how to automatically launch a process here.

5. Configuration file

The network connection has some configurable settings that can be provided in a configuration file, sqmediumconf.py. Actually the package always reads an internal configuration file, _conf.py. This can be copied and named sqmediumconf.py before changing settings. The network connection tries to import sqmediumconf from outside the package directory, so it can be placed in the working directory of an application or anywhere in the Python path. It does not need to include all settings from _conf.py and may even be empty. Settings can apply both on the application and on the file server side.

host

Sqmediumconf is firstly intended to specify the location of the file server while leaving application code unchanged. This is specified by host:

host = "h1972688.stratoserver.net" # file server name or IP address

This setting is overruled if host is given as part of the database name.

back-end settings

On the file server side, a configuration file can configure cache sharing or WAL mode and it can restrict network access. The comments in _conf.py should clarify this.

port number

The configuration file also defines the port number for the socket connection. This is 41521 or 41529 (Python 3).

deleting it

Sqmediumconf.py becomes compiled like a Python module. Deleting it (to clear all settings) does not have any effect. Also the .pyc file must be deleted. With Python 3 this is resolved.

6. Change history

21 Dec 2011

2.1.1

apswdbapi2:
- Pysqlite aggregate compatibility
- APSW to DB-API2 error mapping at more places
- mention limitations in module doc

network interface:
- test case for dump method, incl. BLOB - '.dir' shell command

This page was created Wed Dec 21 16:02 CET 2011