Reference: SQLite Applicability

Reference: http://www.sqlite.com.cn/MySqlite/5/100.Html

SQLite is different from most other SQL database engines, because its primary design goal is to simplify:

• Easy to manage
• Easy to use
• Easy to embed other large programs
• Easy to maintain and configure

 

Many people like SQLite because it is small and fast. however, these features are only some of their advantages, and users will find that SQLite is very stable. excellent stability comes from its simplicity. The simpler it is, the less prone to errors. in addition to the simplicity, simplicity, and stability, SQLite strives to simplify the process.

Simplification can be said to be an advantage in a database engine, but it may also be a disadvantage, mainly determined by what you want to do. to simplify, SQLite omitted some features that people think are useful, for example, high concurrency, strict access control, rich built-in functions, stored procedures, complex SQL language features, XML and Java extensions, and ultra-large trillion-level data measurement. if you need to use these features and don't mind their complexity, SQLite may not be suitable for you. SQLite is not intended to serve as an enterprise-level database engine, nor to compete with Oracle or PostgreSQL.

SQLite is applicable only to the following scenarios: when you are more interested in simple management, use, and maintenance of databases than the countless complex functions provided by enterprise-level databases, using SQLite is a wise choice. facts also prove that in many cases people have clearly realized that simplicity is the best choice.

Best SQLite trial

•  

  Website

  As a database engine, SQLite is applicable to websites with medium and small traffic (that is, 99.9% of websites ). how much website traffic SQLite can handle is the pressure on the website database. generally, SQLite can run normally if a website has a click rate of less than 100000 times/day. 100000 times/day is a conservative estimate, not an accurate limit. it turns out that SQLite can still run normally even if it is 10 times the above traffic.

•  

  Embedded devices and Application Software

  Because SQLite databases almost do not need to be managed, SQLite is a good choice for devices or services that run unattended or have no manual technical support. SQLite is suitable for mobile phones, PDAs, set-top boxes, and other instruments. as an embedded database, it can also be well applied to client programs.

•  

  Application file format

  SQLite has achieved great success as a local disk file format for desktop applications. for example, financial analysis tools, CAD packages, file management programs, etc. generally, you need to call the sqlite3_open () function to open a database and mark the start point of an explicit local transaction (begin transaction) to obtain the file content in an exclusive manner. the file storage will execute a commit and mark the starting point of another explicit local transaction. the role of such transactions is to ensure that updates to application data files are atomic, persistent, independent, and consistent.

  Some temporary triggers can be added to the database to record all changes in a temporary cancel/redo log table. these changes can be rolled back when you press the cancel/Redo button. with this technology, you only need to write a small amount of code to implement an infinitely Undo/Redo function.

•  

  Replace some special file formats

  Many programs use the fopen (), fread (), or fwrite () function to create and manage custom files to save data. replacing these custom file formats with SQLite is a good choice.

•  

  Internal or temporary database

  For programs with large amounts of data that need to be filtered and classified in different ways, if you read data into an SQLite database in memory, then, use the join query and order by clause to extract the required data in a certain order and arrangement, which is usually simpler and faster. using the embedded SQLite database in accordance with the above method will make the program more flexible, because adding new columns or indexes does not require rewriting of any query statements.

•  

  Command Line dataset analysis tool

  Experienced SQL users can use the SQLite command line program to analyze a variety of mixed datasets. originally, data can be imported from CSV (comma-separated value files) files, and then split into countless comprehensive data reports. possible usage includes website log analysis, motion statistics analysis, editing planning standards, and analyzing test results.

  Of course, you can also use enterprise-level Client/Server databases to do the same thing. in this case, the advantage of using SQLite is that the deployment of SQLite is simpler and the result database is a separate file. You can store it on a floppy disk or USB disk or directly send it to your colleagues by email.

•  

  As a substitute for enterprise-level databases in demo or test versions

  If you are writing a client program using the enterprise-level database engine, it will be meaningful to use a general-purpose database background that allows you to connect to different SQL database engines. the larger significance is to statically connect the SQLite database engine to the client program, so as to embed SQLite as a hybrid database support. in this way, the client program can use the SQLite database file for independent testing or verification.

•  

  Database Teaching

  The installation and use of SQLite is very simple (the installation process is almost negligible, only the beibeibeisqlitesource code or sqlite.exe executable file can be directly run on the target host), so it is very suitable for explaining SQL statements. students can easily create their favorite databases and then e-mail them to their teachers for comments or scores. for those who are interested in how to implement a high-level Relational Database Management System (RDBMS), The SQLite source code is based on the modular design and has good comments and documents, it will lay a good foundation for them. this does not mean that SQLite is how to implement the precise model of other database engines, but it is suitable for students to understand how SQLite works quickly and to master the design and implementation principles of other database systems.

•  

  Test SQL Language extension

  The simple and modular design of SQLite makes it an excellent prototype platform for testing database language features or new ideas.

 

Where can I use other relational database management systems (RDBMS)

•  

  Client/Server programs

  If you have many client programs that want to access a shared database through the network, you should consider replacing SQLite with a client/server database. SQLite can work through the Network File System, but because of the delay with most network file systems, the execution efficiency is not very high. in addition, most network file systems have bugs (including UNIX and Windows) in implementing file logical locks ). if the file lock does not work properly, two or more client programs may change the same part of the same database at the same time, leading to database errors. these problems are essentially bugs in file system execution, so SQLite cannot avoid them.

  Good experience tells us that we should avoid using SQLite when many computers need to access the same database through a network file system at the same time.

•  

  High-traffic websites

  SQLite can usually be used as the background database of a website. however, if your website has a high access volume and you begin to consider deploying distributed databases, you should not hesitate to consider replacing SQLite with an enterprise-level Client/Server database.

•  

  Ultra-large Dataset

  When you start a transaction processing in SQLite (transactions are generated before any write operation occurs, rather than calling begin that must be displayed... commit), the database engine will have to allocate a small dirty page (File Buffer page) to help it manage rollback operations. each 1 MB of database file SQLite needs 256 bytes. for small databases, this space is nothing, but when the database grows to billions of bytes, the size of the buffer page will be quite large. if you need to store or modify dozens of GB Data, you should consider using other database engines.

•  

  High-concurrency access

  SQLite locks the entire database file for reading/writing. this means that if any process reads a part of the database, no other process can write any part of the database. similarly, if any process writes data to the database, no other process can read any part of the database. this is not a problem in most cases. in these cases, each program uses the database for a short period of time and is not exclusive, so that the lock may take up to dozens of milliseconds. however, if some programs require high concurrency, they need to find other solutions.