SQLite：创建现有连接以重新加载数据库 [英] SQLite : Make an existing connection to reload the database

问题描述

我维护一个旧代码，其中我们有 TempDB 和 FullDB 的概念，TempDB只是FullDB的一个小实例，因此用户可以浏览FullDB

I am maintaining a legacy code wherein we have a cocept of TempDB and FullDB, TempDB is just a small instance of FullDB, so that user can browse while FullDB is prepared.

由于FullDB中涉及到大量的写入操作，在同一数据库文件上的读写操作正在为其他线程上的读者创建锁定。所以我想下面的策略，这最适合我们的情况，万一可能。

Since lots of writes were involved in FullDB, reading and writing on same database file was creating a lock for readers on other thread. SO I am thinking of the following strategy, which best fits in our situation, in case its possible.

这里是我想做的：

1. 开始准备数据库，当达到tempDB的阈值时，提交事务并关闭连接。复制这个文件，让它们调用 orig （这是临时数据库）和 copy （这是临时数据库的副本，并将对此文件进行进一步写入） / li>
   
2. 接下来，读者可以在收到事件后立即在 orig 上打开连接。 Writer将在 copy 上打开一个连接并执行剩余的写操作相当长一段时间，期间读者使用 orig temp db。


3. 当写者准备完整的DB copy 时，我需要替换 code>


4. 这是抓住，读取器不会关闭并重新打开连接。所以我需要在我更换数据库时阻止读取器。这可以通过在 orig DB上获取 EXCLUSIVE 锁来实现，然后我可以替换 orig db copy db（重命名）。

1. Start preparing the DB, when threshold for tempDB is reached, commit the transaction and close the connection.Make a copy of this file, lets call them orig(which is the temp db) and copy(which is copy of temp DB and further writes will be done to this file).
2. Next, readers can open a connection on orig as soon as they receive an event. Writer will open a connection on copy and perform remaining writes for quite a long time during which readers are using the orig temp db.
3. When the writer has prepared the full DB copy , I need to replace the orig file with the updated full db copy.
4. Here's the catch, readers will not close and reopen the connection. So I need to block the readers while I am replacing the DB. This I can achieve by acquiring an EXCLUSIVE lock on the orig DB, and then I can replace the orig db with copy db (renaming).

$ b b

问题：
读者不接受新的DB文件。如何让他们这样做？
我的意思是当我试图通过终端：制作一个数据库，复制它，并进行一些条目的副本，然后替换原件与副本，我仍然得到的条目，存在于原始数据库。令人惊讶的是，即使我删除了（原始和复制）DB文件，我仍然得到条目。似乎SQLite从一些内存中选择数据，而不是从磁盘文件。

The Problem : The readers are not accepting the new DB file.How can I make them to do that? I mean when I tried through terminal : make a DB, copy it and make some entries into the copy and then replace the original with the copy, I was still getting entries that were present in the original DB. To the surprise, even when I deleted both (orig and copy) the DB files, I was still getting entries. It seems SQLite was picking data from some in-memory and not from the disk files.

任何帮助？

PS：在搜索时发现了 .open 命令，但不确定如何工作，或者它是否有用。

PS : On searching I found something called .open command but not sure how it works or whether its really helpful.

EDIT
< =http://stackoverflow.com/questions/1347090/what-should-i-do-to-refresh-data-with-concurrent-sqlite-3-access>这我想要什么？

推荐答案

在有一些打开的连接时，不能重命名或删除数据库文件;在基于Unix的系统上，任何打开的句柄都将仍然访问旧文件。

You must not rename or delete database file while there is some open connection; on Unix-based systems, any open handles will still access the old file.

使用数据库上的（独占）锁，你可以保留文件，所有内容，并将新数据复制到其中。

With an (exclusive) lock on the DB, you can just keep the file, but delete all its contents, and copy the new data into it.

这篇关于SQLite：创建现有连接以重新加载数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！