How do I notify a process of an SQLite database change done in a different process?

Let's say I have two or more processes dealing with an SQLite database - a "player" process and many "editor" processes.

The "player" process reads the database and updates a view - in my case it would be a waveform being mixed to the soundcard depending on events stored in the database.

An "editor" process is any editor for that database: it changes the database constantly.

Now I want the player to reflect the editing changes quickly.

I know that SQLite supplies hooks to trace database changes within the same process, but there seems to be little info on how to do this with multiple processes.

I could poll the database constantly, compare records and trigger events, but that seems to be quite inefficient, especially when the database grows to a large size.

I am thinking about using a log table and triggers, but I wonder if there is a simpler method.


Asked by: Dainton637 | Posted: 30-11-2021






Answer 1

A relational database is not your best first choice for this.

Why?

You want all of your editors to pass changes to your player.

Your player is -- effectively -- a server for all those editors. Your player needs multiple open connections. It must listen to all those connections for changes. It must display those changes.

If the changes are really large, you can move to a hybrid solution where the editors persist the changes and notify the player.

Either way, the editors must notify they player that they have a change. It's much, much simpler than the player trying to discover changes in a database.


A better design is a server which accepts messages from the editors, persists them, and notifies the player. This server is neither editor nor player, but merely a broker that assures that all the messages are handled. It accepts connections from editors and players. It manages the database.

There are two implementations. Server IS the player. Server is separate from the player. The design of server doesn't change -- only the protocol. When server is the player, then server calls the player objects directly. When server is separate from the player, then the server writes to the player's socket.

When the player is part of the server, player objects are invoked directly when a message is received from an editor. When the player is separate, a small reader collects the messages from a socket and calls the player objects.

The player connects to the server and then waits for a stream of information. This can either be input from the editors or references to data that the server persisted in the database.

If your message traffic is small enough so that network latency is not a problem, editor sends all the data to the server/player. If message traffic is too large, then the editor writes to a database and sends a message with just a database FK to the server/player.


Please clarify "If the editor crashes while notifying, the player is permanently messed up" in your question.

This sounds like a poor design for the player service. It can't be "permanently messed up" unless it's not getting state from the various editors. If it's getting state from the editors (but attempting to mirror that state, for example) then you should consider a design where the player simply gets state from the editor and cannot get "permanently messed up".

Answered by: Dainton524 | Posted: 01-01-2022



Answer 2

SQLite has an update_hook function which does what you want.

SQLite C Interface

Data Change Notification Callbacks

void *sqlite3_update_hook(
    sqlite3*,
    void(*)(void *,int ,char const *,char const *,sqlite3_int64),
    void*
);

The sqlite3_update_hook() interface registers a callback function with the database connection identified by the first argument to be invoked whenever a row is updated, inserted or deleted in a rowid table. Any callback set by a previous call to this function for the same database connection is overridden.

Unfortunately it isn't exposed by the Python sqlite module...

Here is a slightly hacky workaround that digs into the C api (from Python code) to make use of it: https://stackoverflow.com/a/16920926

Answered by: Roland681 | Posted: 01-01-2022



Answer 3

Just open a socket between the two processes and have the editor tell all the players about the update.

Answered by: Kelvin139 | Posted: 01-01-2022



Answer 4

If it's on the same machine, the simplest way would be to have named pipe, "player" with blocking read() and "editors" putting a token in pipe whenever they modify DB.

Answered by: Catherine264 | Posted: 01-01-2022



Answer 5

I think in that case, I would make a process to manage the database read/writes.

Each editor that want to make some modifications to the database makes a call to this proccess, be it through IPC or network, or whatever method.

This process can then notify the player of a change in the database. The player, when he wants to retrieve some data should make a request of the data it wants to the process managing the database. (Or the db process tells it what it needs, when it notifies of a change, so no request from the player needed)

Doing this will have the advantage of having only one process accessing the SQLite DB, so no locking or concurrency issues on the database.

Answered by: Kevin995 | Posted: 01-01-2022



Answer 6

Edit: I am asuming processes on the same machine.

In my opinion there are two ways:

  • Polling (as you mentioned), but keep it to a single value (like a table that just keeps the LastUpdateTime of other tables)

  • Use whichever interprocess-communication there is available on the target platform. This could be events in Windows (e.g. in C# (I don't know in Python) the ManualResetEvent, AutoResetEvent, or a Mutex if you want to sacrifice a waiter-thread in each process), or Signals in Linux.

Answered by: Oliver605 | Posted: 01-01-2022



Answer 7

How many editor processes (why processes?), and how often do you expect updates? This doesn't sound like a good design, especially not considering sqlite really isn't too happy about multiple concurrent accesses to the database.

If multiple processes makes sense and you want persistence, it would probably be smarter to have the editors notify your player via sockets, pipes, shared memory or the like and then have the player (aka server process) do the persisting.

Answered by: Aida378 | Posted: 01-01-2022



Answer 8

Sqlite itself has no mechanism to achieve this. The closest feature I see to this problem is monitoring the file system. There's a multitude of options. Stackoverflow has discussions about this in: How do I watch a file for changes? .

Other programming languages have the same discussions: How do I make my program watch for file modification in C++?

Answered by: Kellan846 | Posted: 01-01-2022



Similar questions

python - best way to process local file and update Titan graph database

I am newbie to Titan graph database. I am trying to process local data, and insert them into titan db. I am looking for the program language or script language that can do fast way to process local data and update/insert titan db. bulbs, is python interface, using REST API to update titan db. but I see sometimes the program hang over there. Can I use shell script to process the file, and...


python - Import csv data to web2py database and process uploads

I've made a really simple single-user database application with web2py to be deployed to a desktop machine. The reason I choose web2py is due to its simplicity and its not intrusive web server. My problem is that I need to migrate an existing database from another application that I've just preprocessed and prepared into a csv file that can be now perfectly imported into web2py's sqlite database. Now, I hav...


api - How to process data before storing to database in python eve

I'm currently learning python eve framework and mongoDB database for restful API development. In eve, basic CRUD operations are done only by defining the schema in settings.py file. Client can send GET/POST methods and data is automatically stored into mongoDB according to the predefined schema. What if I want to preprocess my data before inserting it into mongoDB (e.g.: Client only sends product quant...


python - Django access to database from another process

I create a new Process from django app. Can i create new record in database from this process? My code throws exception: django.core.exceptions.AppRegistryNotReady: Apps aren't loaded yet. UPD_1 def post(self, request): v = Value('b', True) proc = Process(target=start, args=(v, request.user, request.data['stock'], request.data['pai...


python - Saving process data on another table in the database

I'm working on a process for my company. Doing this i have found it necesary to store data that is being inserted in the proccess. My process is working fine but instead of saving in my other model, it keeps on saving the data on the table that corresponds to the process and not my other model. models.py: from __future__ import unicode_literals from datetime import datetime from django.db import mod...


python - Process the data in the database and write to a new table

The problem I'm having is processing a table in the database and then merging all to write to another table. The table structure is like this: Owns 3 database tables. I want to merge into the table by the Entryid My idea is to ...


database - Problem opening berkeley db in python

I have problems opening a berkeley db in python using bdtables. As bdtables is used by the library I am using to access the database, I need it to work. The problem seems to be that the db environment I am trying to open (I got a copy of the database to open), is version 4.4 while libdb is version 4.6. I get the following error using bsddb.dbtables.bsdTableDB([dbname],[folder]): (-30972, "DB_VERSION...


database - Store simple user settings in Python

I am programming a website in which users will have a number of settings, such as their choice of colour scheme, etc. I'm happy to store these as plain text files, and security is not an issue. The way I currently see it is: there is a dictionary, where all the keys are users and the values are dictionaries with the users' settings in them. For example, userdb["bob"]["colour_scheme"] would have the value "b...


python - Sometimes can't delete an Oracle database row using Django

I have a unit test which contains the following line of code Site.objects.get(name="UnitTest").delete() and this has worked just fine until now. However, that statement is currently hanging. It'll sit there forever trying to execute the delete. If I just say print Site.objects.get(name="UnitTest") then it works, so I know that it can retrieve the site. ...


python - How do I test a django database schema?

I want to write tests that can show whether or not the database is in sync with my models.py file. Actually I have already written them, only to find out that django creates a new database each time the tests are run based on the models.py file. Is there any way I can make the models.py test use the existing database schema? The one that's in mysql/postgresql, and not the one that's in /myapp/models.py ?


python - Using user input to find information in a Mysql database

I need to design a program using python that will ask the user for a barcode. Then, using this barcode, it will search a mysql to find its corresponding product. I am a bit stuck on how to get started. Does anyone have any tips for me?


python - Given an rpm package name, query the yum database for updates

I was imagining a 3-line Python script to do this but the yum Python API is impenetrable. Is this even possible? Is writing a wrapper for 'yum list package-name' the only way to do this?


python - Import XML into SQL database

I'm working with a 20 gig XML file that I would like to import into a SQL database (preferably MySQL, since that is what I am familiar with). This seems like it would be a common task, but after Googling around a bit I haven't been able to figure out how to do it. What is the best way to do this? I know this ability is built into MySQL 6.0, but that is not an option right now because it is an alpha development rel...


python - Using 'old' database with django

I'm using a hand built (Postgres) database with Django. With "inspectdb" I was able to automatically create a model for it. The problem is that some tables have multiple primary keys (for many-to-many relations) and they are not accessible via Django. What's the best way to access these tables?


database - python orm

This is a newbie theory question - I'm just starting to use Python and looking into Django and orm. Question: If I develop my objects and through additional development modify the base object structures, inheritance, etc. - would Django's ORM solution modify the database automatically OR do I need to perform a conversion (if the app is live)? So, I start with a basic Phone app Object person: name, address, city, s...


python - Large Sqlite database search

How is it possible to implement an efficient large Sqlite db search (more than 90000 entries)? I'm using Python and SQLObject ORM: import re ... def search1(): cr = re.compile(ur'foo') for item in Item.select(): if cr.search(item.name) or cr.search(item.skim): print item.name This function runs in more than 30 seconds. How sh...






Still can't find your answer? Check out these communities...



PySlackers | Full Stack Python | NHS Python | Pythonist Cafe | Hacker Earth | Discord Python



top