python 如何通知进程在不同进程中完成的 SQLite 数据库更改？

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

提问by paniq

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

假设我有两个或多个处理 SQLite 数据库的进程——一个“播放器”进程和许多“编辑器”进程。

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.

我知道 SQLite 提供挂钩来跟踪同一进程中的数据库更改，但似乎没有关于如何使用多个进程执行此操作的信息。

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.

我正在考虑使用日志表和触发器，但我想知道是否有更简单的方法。

采纳答案by S.Lott

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 andnotify 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.

如果您的消息流量足够小以至于网络延迟不成问题，则编辑器会将所有数据发送到服务器/播放器。如果消息流量太大，则编辑器写入数据库并将仅带有数据库 FK 的消息发送到服务器/播放器。

---

---

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".

这听起来像是播放器服务的糟糕设计。除非它没有从各种编辑器那里获得状态，否则它不能“永久搞砸”。如果它从编辑器获取状态（但试图反映该状态，例如），那么您应该考虑这样一种设计，即玩家只需从编辑器获取状态，而不能“永久搞砸”。

回答by Anentropic

SQLite has an update_hookfunction which does what you want.

SQLite 有一个update_hook功能可以满足您的需求。

SQLite C Interface

Data Change Notification Callbacks

SQLite C 接口

数据更改通知回调

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.

该sqlite3_update_hook()接口向由第一个参数标识的数据库连接注册一个回调函数，只要在 rowid 表中更新、插入或删除行，就会调用该函数。由先前调用此函数为同一数据库连接设置的任何回调都将被覆盖。

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

不幸的是，它没有被 Python sqlite 模块公开......

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

这是一个稍微有点麻烦的解决方法，它深入研究 C api（来自 Python 代码）以使用它：https: //stackoverflow.com/a/16920926

回答by Martin

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.

每个想要对数据库进行一些修改的编辑器都会调用这个过程，无论是通过 IPC 或网络，还是任何方法。

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)

然后，此过程可以将数据库中的更改通知玩家。玩家，当他想要检索一些数据时，应该向管理数据库的进程请求它想要的数据。（或者 db 进程告诉它它需要什么，当它通知更改时，因此不需要来自玩家的请求）

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

这样做的好处是只有一个进程访问 SQLite 数据库，因此数据库上不会出现锁定或并发问题。

回答by Paul Tomblin

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

只需在两个进程之间打开一个套接字，让编辑器告诉所有玩家有关更新的信息。

回答by Martin C.

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 Signalsin Linux.

• 轮询（如您所提到的），但将其保留为单个值（例如只保留其他表的 LastUpdateTime 的表）

• 使用目标平台上可用的任何进程间通信。这可能是 Windows 中的事件（例如，在 C# 中（我不知道在 Python 中）ManualResetEvent、AutoResetEvent 或互斥体，如果您想在每个进程中牺牲一个服务员线程），或Linux 中的信号。

回答by vartec

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.

如果它在同一台机器上，最简单的方法是命名管道，带有阻塞 read() 的“播放器”和“编辑器”在修改数据库时将令牌放入管道。

回答by snemarch

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 toohappy about multiple concurrent accesses to the database.

有多少编辑器进程（为什么要进程？），以及您期望更新的频率？这听起来不是一个好的设计，尤其是没有考虑到 sqlite 真的对多个并发访问数据库不太满意。

Ifmultiple processes makes sense andyou 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.

如果多个进程有意义并且您想要持久性，那么让编辑器通过套接字、管道、共享内存等通知您的播放器然后让播放器（又名服务器进程）进行持久化可能会更明智。