SQLite database literacy

Today, I noticed that SQLite 3.6.11 (released last month) has added an online backup interface that I have been waiting for a long time. I am so excited that 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.

★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 include a dynamic library to 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 switch to the memory mode. During the switchoverCodeYou don't need to make a big change. You just need to load the file to the memory at the beginning and then dump the memory database back to the file at the end. In this case, the "online backup API" mentioned above comes in handy. Smart students should understand why I expect 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. The database may be exclusive to write operations, leading to blocking or error of other read/write operations.

◇ Incomplete SQL standard support
On its official website, it lists the sql92 standards that 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 LanguageInterface
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, its built-in APIs are also C interfaces. 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. PythonCommunityI still like SQLite.
◇ DOTNET
If you like DOTNET, you can access it through the ADO. Net driver of SQLite.
◇ Ruby
Ruby can operate the SQLite database through SQLite-Ruby, but I have never used it.
◇ Perl
There is DBD: SQLite on CPAN, 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 evaluate it based on several reference factors mentioned in "how to select an open-source project.
◇ License)
SQLite uses the 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 (which can be reflected by Google Trends ). 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 you get a rough idea about the change log of SQLite, we can see that there are basically 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.

The reprinted statement must contain this statement to ensure the completeness of this article. The author's programming preferences and original addresses are shown in hyperlink form:
Http://program-think.blogspot.com/2009/03/opensource-review-sqlite-database.html