Small. Fast. Reliable.
Choose any three.
About Sitemap Documentation Download License News Developers Support
Ideas for Client/Server enhancements.

My idea was a network protocol for sqlite database. This would be a client and a server libraries communicating over network(probably sockets). The client library wold have the same interface as current version of sqlite.so (sqlite.dll). The server library would be a wrapper around exsisting sqlite library.

Problems:

  1. Since the callback and VM API's would be to slow over network, it is neccessary to reimplement them on the client side to provide compatibility to the standard sqlite library.

  2. Passing pointers over network is not a good idea. (e.g. pointer to database, or to VM). They should be replaced by handles.

  3. Encoding of strings and integers. Distinguish between empty string and NULL string. BIG_ENDIAN/LITTLE_ENDIAN and so on.

  4. Security

  5. Performance

Implementation architecture: RPC or Sockets?

I tried out the rpcgen (generator of RPC). It generates a lot of useful code, but it can't handle NULL strings returned by sqlite.

Perhaps using sockets is a better idea.

Any suggestions and ideas are welcome.

Alex K.

I have some input on a network protocol for sqlite too (and may actually implement it).

I think that the best transport mechanism is ASCII text (i.e. human readable) over TCP. RPC interfaces are harder to use, limit portability and are much harder to debug.

Since all fields in sqlite are already strings, there is no additional overhead in converting data to ASCII, and there is no need to add transparency (i.e. for '\0') because the database designer had to do that anyway when the data was inserted.

I envisage the protocol that looks a little like the sqlite command line. An example session might look like this (lines prefixed by '>' are sent by the client:

>.cursor select * from foo
ok
>.desc
ok\0
bar\0integer\0
baz\0text\0
>.next 4
ok\0
3\0Hello\0
1\0there\0
3\0how\0
8\0are\0
>.next 4
err 1001 truncated result\0
8\0you?\0

The basic idea is that the first element of any reply indicates success or failure. The '.cursor' statement corresponds to the sqlite_compile() function. The '.next' statemet corresponds to a sqlite_step() call, with the optimization that multiple rows can be requested at once.

This type of interface would give a quasi client-server capabilty. The server is the only process that opens the database file. Clients connect over the network (even local clients). Each connected client would spawn a new server thread and sqlite's multi-threading features would take care of the concurrency problem.

With respect to the issues that you raise, here are my thoughts:

  1. The network will definitely reduce performance, however since the query is executed on the server, and in many cases the query execution time dominates, for a well-designed client-server application there is no reason that sqlite would be any worse that any other DB (for a single client).

  2. There should not be any pointers passed. I think that the protocol definition should loosely map to the C API, but only loosely.

  3. There is no need to distinguish between strings an integers since sqlite itself doesn't. Because all data is in strings there are no endianness issues. Representing NULL however is a problem. Perhaps '\1' can be used to represent NULL, though this would force the protocol to add transparency for embedded '\1' characters.

  4. If the protocol goes over TCP then security can be achieved by using TLS/SSL.

  5. See 1.

4/29/04 CJW.

<hr color=red> How about using xmlrpc protocol http://xmlrpc.org/

The human readable interface would have its advantage if you use it like sqlite console app. But this will be another API more... If some one just want to use sqlite console on a remote machine, it can be done over ssh.

The advantage of C API would be:

  1. No changes to existing applications are needed (just replace sqlite.so)
  2. Since sqlite console app is linked against sqlite.so, we automatically get network capable sqlite console app. (which itself provides some kind of human readable interface) But as i mentioned above, running sqlite console app on a remote machine doesn't require any new modifications - it can be done with ssh.

Performance issue: The performance in LANs would not suffer if you retrive results row by row - that's true. But over internet it's a little bit different. A simple ping takes about 70-100 ms(in a LAN 0.2-0.6 ms). If you look at speed comparisons between Postgres, MySQL and SQLite you can see, that response times for a query lies between 0.3 ms (with index) and 30 ms (without index). In my opinion depending on the situation both the transfer or the execution time may dominate.

NULL issue: sqlite provides an option to set custom representation for NULL strings. Perhaps you can use it some how.

Thanks for link to xmlrpc. Unfortunatelly the xml format produces to mush overhead. (e.g each array element must be surrounded by <value><string>..</string></value>)

How do you plan to use your protocol?

30.04.2004 / Alex K.

We can pass compressed data 01.05.2004
Please, don't use RPC or any protocol which uses unpredictble TCP ports. It will make firewalling them impossible. So please stay with static TCP port assigment.

30.04.2004 / Dan

Please consider Tabular Data Stream (TDS) protocol. This is the protocol used by Sybase and MS SQL Server. If you make the wire protocol TDS, then you can use many of the client tools available for SQL Server, etc., including FreeTDS and JDBC.

From the FreeTDS web site:

Tabular Data Stream (TDS) is defined here

See FreeTDS Project

The protocol is described on this page

30.04.2004 / e