Use Cases of SQLite

I used SQLite Literacy last time, and some people commented on it later.
Q: How can I use it in a project? Let's talk about this topic today.

★How to weigh?

When you weigh whether SQLite should be used in an application, consider at least the following points (at the technical level:
◇ Can we leverage some of SQLite's strengths?
◇ Is there any other alternative?
◇ Is there any potential technical risk?
After thinking about the above issues, make a decision.

★Features of SQLite

About the features of SQLite, In the last post
. Considering that some students are relatively forgetful, let's review:
◇ File-type database, with only a single data file
◇ Lightweight
◇ Green (independent from other software libraries)
◇ Cross-platform (including engines and data files)
◇ Support for memory databases
◇ Support for large data files (TB level)

★Possible alternatives

As I mentioned earlier, we need to consider other alternative solutions to weigh the use of SQLite. So I will briefly introduce several other technical means similar to SQLite. Later, we will compare the application scenarios with these alternative solutions.

◇ Access Database

The Access database is also a file-type database and supports many SQL features similar to SQLite. Since Windows 2000, Windows has built an Access database engine (Microsoft Jet Database Engine)
). Therefore, the ACCESS database can run independently (not dependent on office) in the above system ).
The main disadvantage of Access database is that it cannot be cross-platform. There are also several minor disadvantages: the file size is limited (2 GB) and the memory database is not supported.

◇ Other file-type Databases
In fact, in addition to access, there are other file-type databases. However, these file-type databases are either too famous or do not support multiple programming languages (such as HSQLDB
), Or it is outdated (such as Foxpro and paradox ). These things will not be mentioned later when analyzing application scenarios.

◇ CSV file
CSV (comma separated values. For more information, see "here
") Is a simple plain text format. It is used to represent two-dimensional data information. A csv file can be understood as a table in the database.
The disadvantage of CSV is that it is not easy to store non-text data information (such as blob type information). If you need to store the information of multiple tables at the same time, you need to have multiple CSV files (it is too troublesome to have multiple files ).

◇ XML file
XML files must all be known. I will not talk about them much. XML format has two main disadvantages: one is because XML itself is a tree structure, sometimes it is not easy to represent information of two-dimensional data tables; the other is that when the data volume is large (for example, the file size exceeds 10 MB or the XML node level is deep), the XML parsing overhead is quite large.

★Application scenarios as databases

As I mentioned above, I am now starting to get started with the topic. Let's talk about how SQLite can be used as a lightweight database to facilitate the work?
In such scenarios, because SQLite is used as a database, you do not need to consider CSV and XML alternatives.

◇ Small desktop software for Databases
If you develop a small desktop software and need to use database functions (such as a back-to-word software), SQLite is a good choice. Because SQLite is very green and very short.
However, Windows has a large proportion in the desktop system. For thoseIgnore
SQLite, a cross-platform developer, has little advantage over access.

◇ Mobile phone software requiring Databases
　
Mobile apps are developing rapidly, and there are also a large number of developers. If you are a mobile app developer and your app needs database functions (such as a dictionary tool), SQLite is the best choice. Because of the wide variety of mobile phone operating systems and the small memory size of mobile phones, SQLite's cross-platform and lightweight features are fully utilized. Currently, several well-known mobile phone operating systems (such as Android
Windows Mobile
, Symbin
, Palm
And so on), SQLite supports well.
In this case, access is basically useless.

★Application scenarios as data containers

The so-called data container is to use SQLite as the container for loading data and give full play to the advantages of a single SQLite data file. In addition, you can avoid the trouble of defining a set of data file formats. You know, defineComplete
The data file format is extremely difficult (considering scalability, downward compatibility, cross-CPU architecture, byte order, performance, and ...).

◇ Data backup/recovery, Data Import/Export
Some software systems (especially some enterprise application systems) often encounter functional requirements for data backup/recovery. For example, the customer will ask you to regularly back up some data (often business-related) into oneIndependent
The data file is then stored elsewhere. Once the software system has an accident, the backup data will be restored.
In addition, the import/export function is also frequently encountered. Generally, a software is installed in multiple places. Then, you need to export some data (often business-related) from a and then import it to B.
To address these two requirements: XML or access is not suitable if the involved data is large. If cross-platform access is involvedNo
Use Access. CSV is not recommended if multiple types of data are involved (unless you can tolerate the coexistence of multiple CSV files ). SQLite is suitable for any of the above conditions.

◇ Online upgrade
In the past few years, there have been very few stand-alone machines that are not connected to the Internet, and there will be more software that provides the online upgrade function. Generally, online upgrades can be divided into two types: Upgrade programs (such as Firefox automatically upgrades the new version) and upgrade business data (such as antivirus software upgrades the virus database ). SQLite can be used for both types. Place the content to be upgraded to the SQLite database file. You only need to download the content for future upgrade.Single
.
In this scenario, CSV and XML are not suitable. If you do not consider cross-platform, you can also use access.

★Use Cases as memory databases

In this type of scenario, we need to make full use of the features of the SQLite memory database. Since the API design of SQLite is reasonable, there is almost no difference between the operation of memory database and the operation of file database, so switching from file type to memory type does not need to be changed. In addition, since 3.6.11, SQLite has added online backup
Interface to synchronize data between the memory database and the file database.

◇ Reduce disk I/O overhead
For example, a dictionary tool is developed and its dictionary is stored in SQLite database files. As the dictionary grows, you may find that word search is getting slower and slower. Of course, slow speed may not be a disk
I/O. At this time, You Can slightly modify the program (probably about 10 lines of code) and load the dictionary into the SQLite database in the memory during initialization. Then compare and test the performance. If you find that the performance has been significantly improved, you can continue using this method in the future.
When using this trick, be careful with the memory usage of the memory database. For example, it may take several megabytes or even dozens of megabytes for an ordinary PC. If it is larger, it will be uncomfortable. In addition, for the mobile phone operating system, this trick is not very effective (the memory of the mobile phone itself is not very large, and the storage medium speed is already quite fast ).

◇ As a temporary table
The memory database mode can also be used as a temporary table to store some temporary data. When the process of the program exits, the memory database disappears and no garbage is left.
However, this method is only applicable when a program excludes a temporary table. If the temporary table needs to be shared by multiple processes, this cannot be done.

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/04/how-to-use-sqlite.html