如何通知进程在不同进程中完成的 SQLite 数据库更改? [英] How do I notify a process of an SQLite database change done in a different process?

问题描述

假设我有两个或更多进程处理 SQLite 数据库 - 一个播放器"进程和许多编辑器"进程.

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.

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

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.

推荐答案

关系数据库不是您的最佳首选.

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

为什么?

您希望所有编辑都将更改传递给您的播放器.

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.

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

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

这篇关于如何通知进程在不同进程中完成的 SQLite 数据库更改?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！