如何在不删除数据库文件的情况下完全清除 SQLite3 数据库? [英] How do I completely clear a SQLite3 database without deleting the database file?
问题描述
出于单元测试目的，我需要完全重置/清除 SQLite3 数据库.运行测试套件时，所有数据库都是在内存中创建的，而不是在文件系统上创建的，因此我无法删除任何文件.此外，一个类的多个实例将同时引用数据库，所以我不能只是在内存中创建一个新数据库并将其分配给一个变量.
For unit testing purposes I need to completely reset/clear SQLite3 databases. All databases are created in memory rather than on the file system when running the test suite so I can't delete any files. Additionally, several instances of a class will be referencing the database simultaneously, so I can't just create a new database in memory and assign it to a variable.
目前我清除数据库的解决方法是从 sqlite_master 读取所有表名并删除它们.但这与完全清除数据库不同，因为元数据和其他我不明白的东西可能会保留.
Currently my workaround for clearing a database is to read all the table names from sqlite_master and drop them. This is not the same as completely clearing the database though, since meta data and other things I don't understand will probably remain.
是否有一种干净简单的方法，比如单个查询，来清除 SQLite3 数据库?如果不是，必须对现有数据库做些什么才能使其与全新数据库相同?
Is there a clean and simple way, like a single query, to clear a SQLite3 database? If not, what would have to be done to an existing database to make it identical to a completely new database?
如果相关，我使用 Ruby 2.0.0 和 sqlite3-ruby 版本1.3.7 和 SQLite3 版本 3.8.2.
In case it's relevant, I'm using Ruby 2.0.0 with sqlite3-ruby version 1.3.7 and SQLite3 version 3.8.2.
推荐答案
简单快捷的方法
如果使用内存数据库，最快最可靠的方法是关闭并重新建立sqlite连接.它会刷新所有数据库数据以及每个连接的设置.
If you use in-memory database, the fastest and most reliable way is to close and re-establish sqlite connection. It flushes any database data and also per-connection settings.
如果您想要某种重置"功能，您必须假设没有其他线程可以中断该功能 - 否则任何方法都会失败.因此，即使您有多个线程在该数据库上工作，也需要一个停止世界"互斥锁(或类似的东西)，以便可以执行重置.虽然您拥有对数据库连接的独占访问权限 - 为什么不关闭并重新打开它?
If you want to have some kind of "reset" function, you must assume that no other threads can interrupt that function - otherwise any method will fail. Therefore even you have multiple threads working on that database, there need to be a "stop the world" mutex (or something like that), so the reset can be performed. While you have exclusive access to the database connection - why not closing and re-opening it?
艰难的道路
如果还有其他一些限制，而您无法按照上述方式进行操作，那么您已经非常接近拥有完整的解决方案了.如果您的线程没有明确接触编译指示，那么只能静默更改schema_version"编译指示，但是如果您的线程可以更改编译指示，那么您必须查看 http://sqlite.org/pragma.html#toc 并编写重置"函数，将每个编译指示值设置为其初始值(您需要在开始时阅读默认值).
If there are some other limitations and you cannot do it the way above, then you were already pretty close to have a complete solution. If your threads don't touch pragmas explicitly, then only "schema_version" pragma can be changed silently, but if your threads can change pragmas, well, then you have to go through the list on http://sqlite.org/pragma.html#toc and write "reset" function which will set each and every pragma value to it's initial value (you need to read default values at the begining).
注意，SQLite 中的编译指示可以分为 3 组:
Note, that pragmas in SQLite can be divided to 3 groups:
- 最初定义的、不可变的或非常有限的可变性
- 动态定义，每个连接，可变
- 动态定义，每个数据库，可变
Group 1 是例如 page_size、page_count、encoding 等.这些都是在数据库创建时定义的，以后通常无法修改，但有一些例外.例如，可以在VACUUM"之前更改 page_size，因此将设置新的页面大小.page_count 不能由用户更改，但它会在添加数据时自动更改(显然).编码在创建时定义，以后无法修改.您应该不需要从组 1 重置编译指示.
Group 1 are for example page_size, page_count, encoding, etc. Those are definied at database creation moment and usualle cannot be modified later, with some exceptions. For example page_size can be changed prior to "VACUUM", so the new page size will be set then. The page_count cannot be changed by user, but it changes automatically when adding data (obviously). The encoding is defined at creation time and cannot be modified later. You should not need to reset pragmas from group 1.
例如，第 2 组是 cache_size、recursive_triggers、jurnal_mode、foreign_keys、busy_timeout 等.这些 pragma 在打开与数据库的新连接时始终设置为默认值.如果您不断开连接，则需要手动将其重置为默认值.
Group 2 are for example cache_size, recursive_triggers, jurnal_mode, foreign_keys, busy_timeout, etc. These pragmas are always set to defaults when opening new connection to the database. If you don't disconnect, you will need to reset those to defaults manually.
第 3 组是例如 schema_version、user_version，也许还有其他一些，您需要查找它.那些也需要手动重置.如果与内存数据库断开连接，数据库将被破坏，因此您无需重置它们.
Group 3 are for example schema_version, user_version, maybe some others, you need to look it up. Those will also need manual reset. If you disconnect from in-memory database, the database gets destroyed, so then you don't need to reset those.
这篇关于如何在不删除数据库文件的情况下完全清除 SQLite3 数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
