We have built our own SQLite database server application and are considering
making this open source. Since there will be some time involved into making it
an open source project and maintaining it, we would first like to make an
inventory to find out if there is any interest in this server application.
==> Advantages and features of this server application:
* Takes semi-automatically care of concurrent writes (1*).
* Very stable server application (eliminates long startup times in case of
client application crash with large DB's).
* Database connections are cached.
* Multiple parameter sets to prepare a query and execute it multiple times with
a different parameter set.
* The result of a previous select query can be used in the next query.
* Auto-checkpointing is disabled and manual checkpointing is done in a separate
thread, preventing transaction commits (read and write) to be occasionally
slowed down by a checkpoint event.
* Built-in LZMA2 compressed and SHA256 encrypted backup and restore.
* Built-in a-synchronous 'continuous data protection' (real-time backup) to
secondary location (4*).
* Built-in slow query log and email notifications (2* 3*).
* Built-in query error log and email notifications (3*).
1.) A programmer can mark a transaction to be splittable and set splitting
points. If set, the server application will automatically split write queries
that take longer than the time set in milliseconds, thus allowing other write
queries to be executed in between.
2.) The application can automatically log slow queries (that exceed the set
maximum query execution time) and send notifications by email. Maximum query
execution time can be set separately for read and write queries.
3.) All logging is done into separate databases, to prevent eating up costly
writing time from the main database.
4.) When a full backup has been made successfully, the real-time backup is
cleared.
==> Cons:
* It has been built as a Windows service and therefore only runs on Microsoft
platforms
* Transactions that cannot be split will block all writers.
==> How it works:
Clients can communicate with this server over TCP/IP sockets. Queries are
submitted and returned in XML format (BLOB results are returned in binary
format to prevent CPU intensive encoding and decoding). The server application
is written in native Visual C++ (without using MFC). If we would make this
project open source we would also include a client example.
A few examples of the XML the client sends to perform a query:
<query>
<sql databaseid="test">
<![CDATA[delete from session where lastclicktime =
@MinClickTime]]>
</sql>
<paramset>
<param type="1"
name="@MinClickTime"><![CDATA[1395318850]]></param>
</paramset>
<paramset>
<param type="1"
name="@MinClickTime"><![CDATA[1395318900]]></param>
</paramset>
</query>
<query>
<sql databaseid="test">
<![CDATA[select ifnull(max(id),0)+1 as newid from field ]]>
</sql>
<sql databaseid="test">
<![CDATA[insert into field(id, name) values(@ID, @Name) ]]>
</sql>
<paramset>
<param type="0" name="@ID"><![CDATA[newid]]></param>
<param type="1" name="@Name"><![CDATA[Test]]></param>
</paramset>
<transactionbreak></transactionbreak>
<sql databaseid="test">
<![CDATA[insert into closedfield(fieldid) values(@ID) ]]>
</sql>
<paramset>
<param type="0" name="@ID"><![CDATA[newid]]></param>
</paramset>
</query>
Because the result of the last select query is returned to the client, the
client can read the new ID from the result.
Please let us know if you would be interested in this project!
Best regards,
Harmen de Jong
CoachR Group B.V.
www.CoachRDevelopment.com
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
