Open-source comments: SQLite database literacy

Today I noticed that SQLite
3.6.11 (released last month) added a long-awaited online backup
Interface. When I'm so excited, I will talk to you about the SQLite database. This post is intended for SQLite literacy. If you are familiar with SQLite, you do not need to read it again. In addition, if you want to know the specific application of SQLite in software projects, you can see "here
".

★Technical advantages and features

SQLite is a lightweight, cross-platform relational database. Since it is known as a relational database, it is a matter of course to support things commonly used in the sql92 standard (such as views, transactions, triggers, and so on). I will not go into detail today. Today, I will talk about some special things.

◇ Lightweight
First, it has its first feature: lightweight. Presumably, the author of SQLite attaches great importance to this feature, and even its logo uses "feathers" to show its lightness.
　
SQLite is different from the C/S database software. It is a database engine in the process, so there is no database client or server. To use SQLite, you only need to bring one of its dynamic
Library, you can enjoy all its functions. In addition, the size of the dynamic library is quite small. Taking version 3.6.11 as an example, the size of the dynamic library is kb in windows and that of Linux.

◇ Green software
Another feature of SQLite is green: its core engine does not rely on third-party software and does not require "installation ". Therefore, it can save a lot of trouble during deployment.

◇ Single file
The so-called "single file" means that all the information in the database (such as tables, views, triggers, and so on) is contained in one file. This file can be copied to other directories or other machines.

◇ Cross-platform/portability
If you only support mainstream operating systems, you will not be able to boast about it. In addition to mainstream operating systems, SQLite also supports many unpopular operating systems. My personal interest is its support for many embedded systems (such as Android, Windows Mobile, symbin, palm, and VxWorks.

◇ Memory Database)
In this year, the memory is getting cheaper and cheaper. Many common PCs start to measure the memory in GB (servers are even more expensive ). At this time, the memory database features of SQLite become more and more useful.
　
The SQLite API does not distinguish whether the database currently operated is in memory or in files (transparent to the storage media ). Therefore, if you think that disk I/O may become a bottleneck, you can consider switching
Memory mode. During the switchover, the code for operating SQLite basically does not need to be changed. As long as the file is loaded to the memory at the beginning, it is OK to dump the memory database back to the file at the end. In this
In this case, the "online backup API" mentioned above comes in handy. Smart students should understand why I look forward to the backup function so much?

★Technical disadvantages and deficiencies

We talked about the features and advantages above. To avoid the suspicion of hand-writing soft texts with a gun, let's talk about some shortcomings of SQLite. If you want to use it in the future, consider these disadvantages.

◇ Concurrent access Lock Mechanism
SQLite's performance in concurrent (including multi-process and multi-thread) read/write has never been ideal. It uses the file lock method, and the entire database may be exclusive to write operations, leading to blocking or error of other read/write operations. This leads to a serious concurrency bottleneck.

◇ Incomplete SQL standard support
On its official website
Which sql92 standards are not supported. What I personally feel is that foreign key constraints are not supported.

◇ Network File System (NFS)
Sometimes you need to access the SQLite database files on other machines and place the database files in the shared network directory. At this time, you should be careful. When SQLite files are stored in NFS, concurrent read/write operations may cause problems (such as data corruption ). It is said that some NFS file locks have bugs.

★Programming Language Interface

SQLite supports programming interfaces in many languages. This is great for people like me who like to mix multiple programming languages. Next I will give a rough introduction.

◇ C/C ++
Since SQLite is written in C, it comes with Apis
It is also a C interface. Therefore, C/C ++ is the most straightforward to use. If you do not like the process-oriented c api style, you can find another c ++ packaging library. If you want to re-invent the wheel, you can package one by yourself.
◇ Java
If you want to use Java to access SQLite, you can use the JDBC driver of SQLite or a specialized SQLite packaging library. I personally suggest using the JDBC Method. If you want to change the database in the future, you don't need to change the code.
◇ Python
Pysqlite
Is the first choice for python to operate SQLite. It has been integrated into the python Standard Library since Python 2.5. It seems that the python community is quite fond of SQLite.
◇ DOTNET
If you like DOTNET, you can use ADO. Net of SQLite.
Driver.
◇ Ruby
Ruby can use SQLite-Ruby
Operate the SQLite database, but I have never used it.
◇ Perl
On CPAN, DBD: SQLite
But I have never used it.

★Some non-technical reference factors

The above are all technical topics. If you want to use SQLite in your company's commercial software projects. You also need to select an open-source project
"To evaluate the several reference factors.
◇ License)
SQLite uses public domain
Protocol, which is the best and can be used with confidence.
◇ Popularity of users
Over the past few years, more and more people have used SQLite.
). Some large companies have begun to integrate it into products (such as Google's gears, Apple's Safari, and Adobe's air ). This shows that its robustness and stability won't be too big a problem.
◇ Development activity
If the change log of SQLite
We can see that there are updates every 1-2 months in the last five years. It indicates that the development activity is still very high.
From the above non-technical factors, SQLite is very reliable for commercial company software projects.

Copyright Notice

All original articles in this blog are copyrighted by the author. This statement must be reprinted to keep this article complete, and the author's programming will be noted in the form of hyperlinks
And the original address of this article:

Http://program-think.blogspot.com/2009/03/opensource-review-sqlite-database.html