如何将内存中的sqlite数据库保存到perl中的文件中? [英] How to save in-memory sqlite database to a file in perl?

问题描述

我使用以下方法在内存中创建了一个 SQLite 数据库:

I create an SQLite database in memory using:

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.

What I need to do is after finishing playing with the in-memory database, I need to save it to disk file file.db.

更新(答案摘要):
• 有用的命令: 感谢 Schwern 的回答和 评论.

Updated (Answer Summarised):
• Helpful Commands: Thanks to Schwern for his answer and comment.

• $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(提及此处) 显示操作和查询内存数据库还是磁盘数据库会产生相同的性能.
  • $dbh->sqlite_backup_to_file( $file_path ) Copies database from memory to a file.
  • $dbh->sqlite_backup_from_file( $file_path ) Copies database to memory from a file.
  • my $dbh = DBI->connect($dsn, $user, $password,{AutoCommit => 0}) Disabling AutoCommit seems to be a better and simpler option to optimize performance like using the previous two commands. I just need to make sure that when turning off AutoCommit, SQLite SELECT operations doesn't do any disk activity (other question).
    • Update: Performance testing by Schwern (mentioned here) shows that operating and querying on whether an in-memory or in-disk database results the same performance.

    推荐答案

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

    但是您可以通过关闭 AutoCommit 和仅在完成批量插入后才提交事务.SQLite 可能会将所有插入内容保存在内存中，直到它们被提交.

    But you can accomplish basically the same thing with the same performance by turning off AutoCommit and only committing your transaction when you're done with your bulk inserts. SQLite will probably hold all your inserts in memory until they're committed.

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

    一个简单的基准测试表明这同样快，而且更灵活.

    A simple benchmark shows this is just as fast and it's more flexible.

    关闭 AutoCommit 将使您对任一选项都有很大的提升.

    Turning off AutoCommit will give you a big boost with either option.

    这篇关于如何将内存中的sqlite数据库保存到perl中的文件中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！