hctree: doc/hctree/index.html (sqlite.org)
SQLite is sometimes used as the core of a client/server database system. While it works reliably well in such cases, the database backend module that it uses to store b-tree structures in its database file was not designed with this case in mind and can be improved upon in several ways. The HC-tree (hctree) project is an attempt to develop a new database backend that improves upon regular SQLite as follows:
- Improved concurrency: Stock SQLite is limited to a single concurrent writer.Using the begin-concurrent extension changes this so that multiple writers may run concurrently using optimistic page-level locking. This improves concurrency somewhat, but page-level locking can detect conflicts between logically independant transactions, and COMMIT operations must still be serialized.Hctree uses optimistic row-level locking and is designed to support dozens of concurrent writers running at full-speed. Test results obtained from the prototype show that this is possible.
- Support for replication: Stock SQLite supports the sessions extension, which allows the contents of a committed transaction to be serialized for tranmission and application to a second database.Hctree builds this into the database backend, and adds support for application of such transactions to follower databases in leader-follower configurations. In this case, transactions received from a leader database can be applied more quickly and with greater concurrency than with which they were originally applied to the leader database, because no transaction validation is required.
- Removal of database size limitations: Stock SQLite uses 32-bit page numbers. Using the default 4KiB page-size, this leads to a maximum database size of 2^44 bytes, or 16TiB.Hctree uses 48-bit page numbers, allowing 2^60 byte databases, or 1EiB. Roughly one million TiB.
An implicit goal is that hctree must be as fast or faster than stock SQLite for all single-threaded cases. There is no point in running dozens of concurrent writers if each of them is an order of magnitude slower than a single writer writing to a legacy database.
Hctree clients (those that use a version of SQLite compiled from this repository) may read hctree databases and stock SQLite databases.
Their forum post announcement can be seen here, along with their caveats.
SQLite Forum: Hctree is an experimental high-concurrency database backend for SQLite.
2 Likes
Thanks Richard,
Very interesting. I left a question there, regarding simoultaneous access to a database file stored in a shared filesystem and was replied it was not thought for that. A pity, if it could be used in that way it would be a good candidate as a replacement for ISAM files shared on a network without going to a full Client Server SQL database.
Though there is still a problem remaining with SQLite on Clarion classes and RTL regarding >= queries and not consuming the entire resultset to finalize or a way to reset the statement, releasing the locks. Discussed on SV Clarion11 newsgroups thread started 2023-01-05 by Jon and PTSS 43363 reported by him.
Hello Federico…
I read your question (and the answer they wrote you) to SQLite forum and I instantly thought about FirebirdSQL. It comes from Interbase, it has an embedded mode which means that you can use it for standalone use (instead of using .tps and/or .dat files) and when more users need access to database, ODBC client has to be installed in those clients and a small change to connection string…
I’d love to hear thoughts, arguments for, arguments against, etc…
Theodore
Hi Theodore
I agree with you that it provides both use cases and by definition it is easy on do the transition from standalone to server.
Personally I 'm not using it but have tested it on the past. I also followed comments about it on SV Newsgroups.
I don’t have many arguments against, probably if comparing the embbeded version to SQLite, the last has a native file driver in Clarion, but I feel comfortable with ODBC, sometimes it is even preferable, for multi-engine selectable on run-time for example. And SQLite Android deployment is unbeatable. I haven’t done a characteristics comparison.
I think what motivated my post on that SQLite forum is just being alert if there is an embbeded version of a db that works fine on network environment, but it seems by definition that it is unlikely. I think that i could include in that category one embbeded db that uses a small server component instead of a full blown db engine, but that is vaguely described anyway. I think this was an example (I have not tested): https://docs.oracle.com/database/bdb181/html/programmer_reference/bdb_server.html
until they retired support for client-server and SQLite api:
https://download.oracle.com/otndocs/products/berkeleydb/html/changelog_18_1_40.html
quote
“…Changes between version 18.1.32 and version 18.1.40
…
2. The Berkeley DB client-server architecture is no longer supported. If you require the Berkeley DB client-server architecture you must use Berkeley DB 18.1.32 or earlier.
3. The SQL API is no longer supported. If you require SQL support you must use Berkeley DB 18.1.32 or earlier.”
Hello Federico
Thank you for your thoughts…
FirebirdSQL also says that embedded version can’t be used in multi-user environment. For that, ODBC connection has to be done from clients.
MSSQL also has (had?) Compact Edition made primarily for mobile devices but could also be installed on pc’s via sql server express installation. But could also be used in a single user environment.
It must be a kind of “a rule” or something everyone is following?
Theodore
Theodore,
The MSSQL Compact Edition was deprecated in 2013 and support ended 2021 SQL Server Compact - Wikipedia
Some links
Differences Between SQL Server Compact and SQL Server | Microsoft Learn
Everything SQL Server Compact: Comparison of SQL Server Compact, SQLite, SQL Server Express and LocalDB
This Word document “Choosing Between SQL Server 2005 Compact Edition and SQL Server 2005 Express Edition” describes both approaches and also describes previous offerings:
http://download.microsoft.com/download/A/4/7/A47B7B0E-976D-4F49-B15D-F02ADE638EBE/Compact_Express_Comparison.doc
CE doens’t allowed storing the file on network shares. Others allow storing but exclude concurrent access:
Quote:
“Berkeley DB works great with a SAN (and with any other filesystem type as far as we know), but if you attempt to access any filesystem from multiple machines, you are treating the filesystem as a shared, remote filesystem and this can cause problems for Berkeley DB. …”
EndQuote (more info follows about mutexes and cache)
Another explanation is on the response from Donal Fellows from Richards link.
Richard,
Coding specifically to overcome the problem was not part of the goal, it wouldn’t worth the effort, it would be easier to install a full db server. And it may not be feasible on code generated automatically.
Christian Werner SQLite ODBC uses SQLite3.dll locally. It is stated on your link also:
“Note: Using ODBC does not imply client/server or networking. The ODBC driver for SQLite accesses the SQLite database file directly just like SQLite does.”
The other links on your link (sockets and tcp/ip) seems broken or relating to closed project like SQLiteDBMS: “This project has been closed”.
There is Clarion IP Driver. But again the first sentence, it would need specific adaptations on some places.
On most projects I just use db servers. I also use SQLite. This is just to keep an eye open on products or trends if they could worth using on future projects or for other programmers here. This 2 threads on databasedrivers SV newsgroup “SQLite performance” from Arnor on 2021-11-04 and “Berkeley db replacement for SQLite” from Jon on 2022-01-02 and this from here Best portable database for clarion may explain the motivations. SQLite is a great database. At least in the first two threads mentioned, the problems realated to concurrent usage from a network avoided using it on those projects.
1 Like
Hello again…
I was trying to remember a product based on sqlite and finally did it:
From their site:
cubeSQL is a fully featured and high performance relational database management system built on top of the sqlite database engine. We developed the first commercial grade DBMS based on sqlite back in 2005 and over the years we continued to improve our server to better suit all our customer’s needs. cubeSQL is the final result of all our efforts.
It is the ideal database server for both developers who want to convert a single user database solution to a multi-user project and for companies looking for an affordable, easy to use and easy to maintain database management system.
cubeSQL is incredibly fast, has a small footprint, is highly reliable and runs on Windows, Mac and Linux (32 bit and 64 bit for all platforms). cubeSQL can be accessed with PHP, JSON, Xojo and with the native C SDK. cubeSQL is written in low level ANSI C so it can be easily ported to any operating systems.
They used to (still do it) give a two seats key for developers, just to have the opportunity to try their product.
- Up to 2 concurrent connections: FREE
I found out about FirebirdSQL and never actually did anything with that product.
Maybe worth a try. If someone does it, it’d be nice to report back here his/her opinion and results.
HTH
Theodore
1 Like
Yes… They also have “Redistributable License” for developers with very large base:
Pay one price to deploy an unlimited number of servers and server updates during a 12-month period. The Redistributable license is an excellent choice for the developer who already has a large installed base that is migrating from another database server or for the developers who want to lower the price per server for their custom solutions.