如何在 Perl 中将内存中的 SQLite 数据库保存到文件中？

Question

我使用以下代码在内存中创建了一个SQLite 数据库：

my $dsn      = "dbi:SQLite:dbname=:memory:"; # better in read/write operations performance than disk-file saved database.
my $user     = "";
my $password = "";
my $dbh = DBI->connect($dsn, $user, $password,{});

#… Doing some processing on the database (Creating tables/Inserting rows/Updating fields)

#… After finishing, I need to save the database to a local disk file.

我需要做的是，在使用内存数据库完成操作后，将其保存到磁盘文件file.db中。

---

更新（回答摘要）：
• 有用的命令：感谢Schwern的回答和评论。

• $dbh->sqlite_backup_to_file( $file_path )：将内存中的数据库复制到文件中。
• $dbh->sqlite_backup_from_file( $file_path )：将文件中的数据库复制到内存中。
• my $dbh = DBI->connect($dsn, $user, $password,{AutoCommit => 0})：禁用 AutoCommit 看起来是优化性能的更好和更简单的选择，就像使用前两个命令一样。我只需要确保在关闭 AutoCommit 时，SQLite SELECT 操作不会执行任何磁盘活动（其他问题）。
  • 更新：Schwern（在这里提到）进行的性能测试表明，在内存或磁盘数据库上操作和查询会得到相同的性能。

- Omar

---

你能否从一个磁盘数据库开始，使用事务处理，并仅在程序结束时提交吗？你想要实现什么目标？ - Schwern

不，我想在内存中启动它，然后将其保存到磁盘文件中。 如果我从已保存到磁盘的数据库方案开始，我将加载已存在于磁盘中的数据库，然后创建一个副本到一个新的空的内存数据库中（我不知道如何简单地完成此操作）。然后在内存数据库上执行所有的SQL处理（以避免直接在磁盘数据库上进行处理时造成的延迟读/写）。最后，将更新后的版本（即内存数据库）复制回磁盘数据库。 - Omar

如果仅仅是为了优化目的，尝试使用普通的磁盘数据库，并关闭AutoCommit，在结束时（或每隔几千次插入）只提交您的事务。我进行了一些测试，只要AutoCommit关闭，则速度与内存数据库相当，这通常是应该关闭的。并且您可以使用sqlite_backup_from_file将数据库加载到现有的内存数据库中。 - Schwern

Answer 1

是的，您可以使用$dbh->sqlite_backup_to_file( $filename )，然后像普通的SQLite数据库一样连接到该文件。有关更多信息，请参见SQLite备份API文档。

但是，您可以通过关闭AutoCommit并在完成批量插入时仅提交事务来基本实现相同的功能并保持相同的性能。SQLite可能会将所有插入操作保存在内存中，直到它们被提交。

my $dbh = DBI->connect(
    "dbi:SQLite:dbname=test.sqlite", undef, undef, { RaiseError => 1, AutoCommit => 0 }
);

...do your inserts...

$dbh->commit;

一个简单的基准测试显示这样做同样快速且更加灵活。关闭自动提交将在任一选项中大幅提高性能。

- Schwern

---

@ Omar 我不能百分百确定，但 SQLite 已经非常成熟并且应该有自己的内存缓存。这些问题可以通过性能测试来回答。我自己做了，再一次，似乎没有什么区别。 - Schwern

@Omar 临时数据库，例如...“尽管为每个临时数据库分配了一个磁盘文件，在实践中，临时数据库通常驻留在内存页缓存中，因此通过":memory:"创建的纯内存数据库与通过空文件名创建的临时数据库之间几乎没有区别。唯一的区别是，":memory:"数据库必须始终保留在内存中，而如果数据库变得庞大或SQLite承受内存压力，则可能会将临时数据库的部分刷新到磁盘上。” - Schwern

非常感谢您的努力。您的测试给出了很好的指示。它们在性能上似乎是相同的。 - Omar

@Schwern 我有一个类似的问题，但是用例不同。我的情况是我有一个GUI，用户可以通过GUI对数据库进行大量更改。只有当用户按下保存按钮时，更改才会生效。如果我禁用自动提交，这意味着事务将在几分钟/小时内处于打开状态。在这种情况下禁用自动提交仍然是个好主意吗？ - TonySalimi

@Gupta 事务并不是性能问题，而是数据完整性问题。它们确保更改一次性完成（提交）或根本不进行（回滚）；您不希望部分更改。如果没有事务，当用户不想要更改时，如何撤销用户的更改？性能影响似乎很容易测试，但假设只有一个用户在等待保存，那么会影响谁呢？最后，您需要在用户决定保存更改之前将更改存储在数据库中吗？ - Schwern