今日用到了SQLite,发现这个数据库进行了很大的升级,从3.4.X升级到3.5.X了。版本号发生改变,API接口OS层发生重大改变。
SQLite version 3.5.0 introduces a new OS interface layer that is incompatible with all prior versions of SQLite. In addition, a few existing interfaces have been generalized to work across all database connections within a process rather than just all connections within a thread. The purpose of this article is to describe the changes to 3.5.0 in detail so that users of prior versions of SQLite can judge what, if any, effort will be required to upgrade to newer versions.
1.0 Overview Of Changes
A quick enumeration of the changes in SQLite version 3.5.0 is provide here. Subsequent sections will describe these changes in more detail.
- The OS interface layer has been completely reworked:
- The undocumented sqlite3_os_switch() interface has been removed.
- The SQLITE_ENABLE_REDEF_IO compile-time flag no longer functions. I/O procedures are now always redefinable.
- Three new objects are defined for specifying I/O procedures: sqlite3_vfs, sqlite3_file, and sqlite3_io_methods.
- Three new interfaces are used to create alternative OS interfaces: sqlite3_vfs_register(), sqlite3_vfs_unregister(), and sqlite3_vfs_find().
- A new interface has been added to provided additional control over the creation of new database connections: sqlite3_open_v2(). The legacy interfaces of sqlite3_open() and sqlite3_open16() continue to be fully supported.
- The optional shared cache and memory management features that were introduced in version 3.3.0 can now be used across multiple threads within the same process. Formerly, these extensions only applied to database connections operating within a single thread.
- The sqlite3_enable_shared_cache() interface now applies to all threads within a process, not to just the one thread in which it was run.
- The sqlite3_soft_heap_limit() interface now applies to all threads within a process, not to just the one thread in which it was run.
- The sqlite3_release_memory() interface will now attempt to reduce the memory usages across all database connections in all threads, not just connections in the thread where the interface is called.
- The sqlite3_thread_cleanup() interface has become a no-op.
- Restrictions on the use of the same database connection by multiple threads have been dropped. It is now safe for multiple threads to use the same database connection at the same time.
- There is now a compile-time option that allows an application to define alternative malloc()/free() implementations without having to modify any core SQLite code.
- There is now a compile-time option that allows an application to define alternative mutex implementations without having to modify any core SQLite code.
Of these changes, only 1a and 2a through 2c are incompatibilities in any formal sense. But users who have previously made custom modifications to the SQLite source (for example to add a custom OS layer for embedded hardware) might find that these changes have a larger impact. On the other hand, an important goal of these changes is to make it much easier to customize SQLite for use on different operating systems.
由于存储层的改变,SQLite.NET的各个版本也发生了变化:
SQLite.NET 1.0.46是最后一个基于3.4.X版本的提供程序;
SQLite.NET 1.0.48是当前最新的基于3.5.4代码的提供程序;
SQLite.NET的新版本主要是随SQLite3.4->3.5的改进而进行了相应的修改。性能有很大变化,对比如下:
1.0.46 test Beginning Test on System.Data.SQLite.SQLiteConnection Inserting using CommandBuilder and DataAdapter Inserting using CommandBuilder and DataAdapter Fast insert using parameters and prepared statement User Function iteration of 120003 records in 240 ms User (text) command executed 397517 times in 1 second. UserAggregate executed 17 times in 1 second. SUCCESS - UserCollation Tests Finished. | 1.0.48 test Beginning Test on System.Data.SQLite.SQLiteConnection Inserting using CommandBuilder and DataAdapter Inserting using CommandBuilder and DataAdapter Fast insert using parameters and prepared statement User Function iteration of 120003 records in 191 ms User (text) command executed 298951 times in 1 second. UserAggregate executed 15 times in 1 second. SUCCESS - UserCollation Tests Finished. |
由测试可知,3.4版的SQLite在很多方面,性能依然要比3.5版的SQLite强不少。但是SQLite 3.5版在OS适配层的改进(引入VFS对象),提高了数据的写入效率,明显的,其Commits的时间总是较少。
结论:
3.4版的SQLite在数据插入较多的应用情况下,效率仍然较好;
3.5版的SQLite在数据写入磁盘方面有很大改进,但其显然在内存数据组织方面还有待改进,插入操作所耗费的时间明显比3.4版要长;
感觉3.4版的SQLite是非常稳定的一个版本。3.5版,看来等等再使用了。