Sqmediumlite

Python - SQLite network connection

version 2.1.5, Edzard Pasma, 5th September 2013

1. Introduction

Sqmediumlite is a Python package from which the main purpose is to access SQLite databases over a network.

SQLite by itself is a serverless database engine which means that application programmes directly read and write the database files. Access to databases on a network file system is possible however at a not-great performance and at non-attractive quality of concurrent use.

The network connection provided through Sqmediumlite 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 the configuration is no longer serverless. Hence the name "mediumlite". It is not so light as SQLite but still much lighter then a true RDBMS.

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.

Status

The package is experimental and may act as example. Still I consider it stable for production as the main tool is covered by a vast test suite.

There is no copyright except on parts that are copied from APSW "Another Python SQLite Wrapper". This is clearly indicated in the source.

Contact

Email: pasma10@concepts.nl

Mailing list: Python-SQLite discussion group

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.

Source files

tools	description	lines	modified
__init__.py	network connection	312	Tue Aug 14 2012
__main__.py	interactive shell	166	Sun Aug 12 2012
apswdbapi2.py	standard interface based on APSW	286	Thu Sep 05 2013
apswtrace.py	enhanced APSW SQL tracer	628	Sat Sep 07 2013
rowfactory.py	exposes columns as row attributes	52	Sat Sep 07 2013
internal
_backend.py	back-end of network connection	216	Sun Aug 12 2012
_common.py	socket connection	123	Sat Sep 07 2013
_conf.py	configurable settings	29	Fri Dec 09 2011
_service.py	Windows service handler	76	Thu Dec 08 2011
_shellbase.py	APSW interactive shell	2988	Sat Sep 07 2013
_test.py	test suite	2131	Thu Sep 05 2013

Download

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 automatically if APSW 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 in the network 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 shell is started with the -m option of the Python interpretor (as opposed to APSW issue 100). The FILENAME argument can now include a network location:

python -m sqmedium //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. One must be logged in on the file server to do this. The back-end process is controlled by special methods of the main package, also available as commands in the interactive shell. Basically the start() and stop() are all that is needed. However extra configuration is required to make the process automatically start at system startup.

Windows

On Windows a permanent process is started with the service() method. This requires "Python for Windows extensions" as described under Dependencies. As installing a service is a one-time action it can well be done interactively through the sqmediumlite 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 -m sqmedium
SQLite version not available (APSW version not available, sqmediumlite 2.1.5)
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 sqmediumlite 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 sqmediumlite shell:

su -l dbowner -c "(cd /home/dbowner/dbfiles; python -m sqmedium '' .$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/sqmedium1.sh

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).

Note for Python 2

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

6. Change history

21 Dec 2011	2.1.1	apswdbapi2: br> - Pysqlite aggregate compatibility - APSW to DB-API2 error mapping at more places network interface: - test case for dump method, incl. BLOB - '.dir' shell command
5 May 2012	2.1.3	use Pythons -m option to start shell
12th August 2012	2.1.4	apswtrace.py is not covered in the test suite but running the test suite through apswtrace.py appears a useful test. Several situations where it crashed are now dealt with.
5th September 2013	2.1.5	Incomplete version
7th September 2013	2.1.6	- apswtrace now deals with sql statements pragma and vacuum. - apswtrace option 'exeonly' can be used to measure the time of the first SQLite step only. This may sometimes be more realistic than the complete time including fetch, as that also includes idle time between fetching multiple rows. - apswdbapi2.py simply maps all APSW/SQLite exceptions to DBAPI ProgrammingError. - can use hasattr () on rowfactory instances.

This page was created Sat Sep 07 19:22 CEST 2013