> Hi, > I have two processes connecting to a database. Both can modify database. When > some process modify database, I want the other to be notified (so, it can > reload data). > Currently, I use unix sockets, so a process can notify all listener process > that something has changed. But I wonder if there's a better way to achieve > that. > I've tried to a create a custom function (with sqlite3_create_function), and > use triggers. But unfortunately, my trigger was executed for the modifying > process. > So, is there a sqlitish way to have process notifications, or should I stick > to using unix sockets to tell other processes something has changed in the > database. > I'm using C++ language (C api for sqlite) > > regards > arno Howdy, Arno.
I'm a long-time lurker on this list, but until now I haven't had much to contribute. However... on this topic, I might finally have something to offer. About a year ago, I was working on this very problem, and have a working prototype of an SQLite library with a cross-process asynchronous update notification mechanism. When we originally encountered this problem, we tried to characterize it as best we could and then considered a few alternative solutions (which we numbered Option #1 through Option #5 in our documents). One of our (formerly) internal engineering documents includes discussion of this subject and might provide some useful context; see http://www.innovision.com/techdocs/sqlite/PersistentDataSharingAndConcurrenc yControl.pdf Ultimately, we developed prototypes (i.e., enhanced SQLite libraries) for a couple of the options (first, Option #5, and later, Option #3). Another (formerly) internal document further expands on the requirements we wanted to satisfy and how we went about solving (at least sufficient for a prototype) the problem; see http://www.innovision.com/techdocs/sqlite/SQLiteAsyncDatabaseChange.pdf I'll warn you that the document I just referenced probably needs to be updated to be a more accurate reflection of our latest solution to the problem (which is now based on Option #3 rather than Option #5). >From the perspective of a programmer, what they're seeing with the enhanced SQLite library is a new SQLite API function: /* ** The sqlite3_async_update_hook() interface registers a callback function ** with the [database connection] identified by the first argument ** to be invoked whenever the database has been modified in any way. ** ** The monitoring of the database occurs in a separate thread (one thread ** per monitored connection) from the thread that invoked ** sqlite3_async_update_hook(). When a database modification is detected, ** the callback function is called from the monitoring thread. Thus, ** some thread-level synchronization mechanism (e.g., appropriate thread ** safety mode and database locking) should be used as appropriate. ** ** If the async update hook callback function pointer is NULL then the no ** update callbacks are made. ** ** If an async update hook callback function has been registered with a ** database connection at the time it is closed, the callback function ** will automatically be unregistered as part of the connection close ** operation. ** ** The second argument is a pointer to the function to invoke when the ** database is modified. ** The first argument to the callback is a copy of the third argument ** to sqlite3_async_update_hook(). ** The second callback argument is a pointer to the database name. ** ** If another function was previously registered, its pArg value ** is returned. Otherwise NULL is returned. */ void *sqlite3_async_update_hook( sqlite3*, void(*)(void *, const char *), void* ); When I was working on the Option #5 prototype; I created a video demo. If I did it again for Option #3, it probably wouldn't be much different. The demo shows the mechanism working both within a single process as well as across two processes (a Lua-based GUI prototype app, and the sqlite3.exe command line utility, each linked against the enhanced SQLite library). (The Lua aspect is irrelevant here; LuaSQL just happened to be another thing we wanted to evaluate in this prototype). The video demo is available in two formats an AVI (codec available at http://www.techsmith.com/download/codecs.asp): http://www.innovision.com/techdocs/sqlite/SQLiteAsyncDatabaseChangeExample3. avi and a lower-quality MPEG: http://www.innovision.com/techdocs/sqlite/SQLiteAsyncDatabaseChangeExample3. mp4 Now... I've been away from this for at least half a year, but here are some of my thoughts at this point: 1. It appears that our desire to have cross-process update notification is shared by others, so maybe it's time to have the SQLite community dig into this issue more and see if we can arrive at a mutually beneficial solution. 2. Any solution involving the database writer/changer to take any explicit action (e.g. signaling, etc.) to notify other processes possibly using that database file that it has changed is likely to have undesirable vulnerabilities (change can occur that will go undetected, etc.) Consider: (a) file-level copy operations, (b) changes by other tools that don't "follow the rules," etc. 3. As I recall, there were a number of advantages to going with a more "built into SQLite" approach. Among them (a few that I remember right now): - By building it into SQLite, it is more likely to work consistently and robustly across all SQLite-based apps and tools and on all platforms, even if the apps and tools were developed by different people/organizations. - There are certain control points in the lower levels of SQLite (e.g., the b-tree and pager layers) that make excellent places to wedge in change notification mechanisms. In the absence of using such control points, notification mechanisms are harder to implement and tend to suffer from inefficiencies (e.g., doing certain notification-related things when a particular database file is known to be locked and just changed can be much more optimal than alternatives). - Simplicity from the perspective of the SQLite library user. - Who wants to keep re-inventing this wheel? 4. An interesting point to ponder as you're evaluating alternative approaches to this problem: What layer of software really knows what files are involved in a database connection over time? I'd claim that it's SQLite (not the app layer above it). Take, for example, an application that opens an SQLite database connection and then executes arbitrary (e.g., from a console or an input file) SQL commands against it. Some of those commands might be ATTACH DATABASE or DETACH DATABASE commands. So... the set of files that need to be monitored for change is itself changing. Where is that information most readily available? Yup... at the SQLite level. 5. It would be possible to implement a good, high quality cross-process notification mechanism without building it directly into SQLite if SQLite were first enhanced with some new callback hooks at just the right places (I won't elaborate on these further without first reviewing our code to see where those hooks would need to be). Some SQLite VFS extensions could also help put platform-dependent code where it belongs and minimize the surface area of code for anyone wanting to port SQLite (along with the cross-process notification mechanism) to a new platform. 6. The change granularity is a significant issue. It's not too hard or expensive to provide change notification on a database file level. Getting more fine grained, though, becomes much more difficult. While it would be great to get table (or even row-level) cross-process change notification, it is difficult to see how this could be done efficiently without significant implementation done inside the bowels of SQLite. Database-level granularity is enough to at least get the ball rolling; additional mechanisms (e.g., trigger-based change logging to a journal table or some such) can always be layered on top of this if finer grain is needed. Of course, such finer-grained features *could* be built into SQLite at a later date. 7. From the perspective of a user of the SQLite library, it's hard to beat the simplicity of a function like what we did: void * sqlite3_async_update_hook(sqlite3 *db, void(*)(void *context, char const *dbname), void *context); 8. I'd really like to either get this functionality built into SQLite *or* go the opposite direction and get more-or-less the same API-level functionality, but with the notification mechanism source code as decoupled as much as possible from the SQLite source code. It's just not desirable for us to maintain the customized-SQLite-source-code solution we're using right now. -- Ed Scherer Innovision Corporation _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
