Features of SQLite anatomy

SQLite is an acid-compliant, lightweight database engine that is contained in a relatively small C library. It is a public domain project created by D.richardhipp. Unlike the common client/server architecture paradigm, the SQLite engine is not a separate process for the program to communicate with, but rather a major part of connecting to a program. So the main communication protocol is the direct API call within the programming language. This has a positive effect on total consumption, delay time, and overall simplicity. Throughout, the database (definition, table, index, and data itself) is stored in a single file on the host host. Its simple design is done by locking the entire data file at the start of a transaction.

first, the characteristics
(1) Supports atomic, consistent, independent, and durable (ACID) transactions that maintain data integrity even after a system crash or power outage.
(2) 0 configuration – No need to install and manage configurations.
(3) Achieve most SQL92 standards (but not all). Support for the SQL92 standard includes acid transactions, indexing, constraints, triggering, and viewing. FOREIGN key constraints, authorization and recall permissions, etc. are not supported.
(4) A complete database is stored in a single, cross-platform file.
(5) Supports 2TB size database and GB-size string and binary objects.
(6) Small enough, to 3.7. Version 14 ignores comments about 65,000 lines of code.
(7) Most common database operations are faster than in many popular client/server mode databases.
(8) Simple, easy-to-use API.
(9) write using Ansi-c. Supports most language bindings such as C + +, PHP, Python, Perl, Java, C #, TCL, Rails, Delphi, Com/vb DLLs, Objective-c, and more, as well as ODBC interfaces.
(10) Good annotated source code, and has 100% test coverage.
(11) All source code is contained in a single ansi-c source file and is easily embedded into your project.
(12) Self-contained: no external dependencies.
(13) Cross-platform: Support for UNIX (Linux, Mac os-x, Android, IOS), Windows (Win32, WinCE, WinRT), also easily ported to other systems.
(14) The source code belongs to the public domain and can be used for any purpose, including commercial applications.
(15) A command-line tool to manage the SQLite database is included.
SQLite's database permissions depend only on the file system, without the concept of a user account. SQLite has database-level locking, no network servers, and can implement most SQL92 standards (but not all). Some of the other major features of the SQL92 standard are foreign keys and check limits. SQLite implements independent transactions with exclusive and shared locks at the database level. This means that when multiple processes and threads can read data from the same database at the same time, only one can write data. An exclusive lock must be obtained before a process or thread performs a write operation to the database. After an exclusive lock is issued, other read or write operations will no longer occur.
SQLite is designed to be embedded, and has been used in many embedded products, it occupies a very low resource, in the embedded device, may only need hundreds of K of memory is enough. Currently SQLite is widely used in a number of products, including Adobe Photoshop, Airbus, Dropbox, Firefox, Thunderbird, Flame, Google's many projects, Mcafree, Microsoft's game projects, PHP language, Python language, many projects of Skype,toshiba, and so on.
SQLite is also very robust, and its creators conservatively estimate that SQLite can handle Web sites that are burdened with up to 10,000 hits per day, and SQLite can sometimes handle a load of 10 times times that number.

Ii. Types of data
SQLite is untyped (typelessness), which means you can save any type of data to any column of any table you want to save, regardless of the data type declared by this column, and for SQLite it is completely valid to not specify a type for a field (only in one case, That is, when the field type is "Integer Primary Key"). Such as:
Create Table Ex1 (A, B, c);
In fact, SQLite does not support static data types, but instead uses column relationships. This means that its data type does not have a table column property, but a property of the data itself. When a value is inserted into the database, SQLite checks its type. If the type does not match the associated column, SQLite attempts to convert the value to a column type. If it cannot be converted, the value is stored as the type it has.
It is true that SQLite allows data types to be ignored, but it is still recommended to specify the data type in your CREATE TABLE statement. Because the data type can be a hint or a help for you to communicate with other programmers, or when you are ready to replace your database engine. SQLite supports common data types, including null, INTEGER, REAL, text, and BLOB data types. Such as:

 
CREATE TABLE ex2(  
  a VARCHAR(10),  
  b NVARCHAR(15),  
  c TEXT,  
  d INTEGER,  
  e FLOAT,  
  f BOOLEAN,  
  g CLOB,  
  h BLOB,  
  i TIMESTAMP,  
  j NUMERIC(10,5)  
  k VARYING CHARACTER (24),  
  l NATIONAL VARYING CHARACTER(16)  
); 

third, the function
Although SQLite is small, the supported SQL statements are not inferior to other open source databases, and the SQL it supports includes:
Aggregate Functions:avg (x), count (x), COUNT (*), Group_concat (x), Group_concat (x, y), Max (x), Min (x.), sum (x), total (x).
ALTER TABLE
ANALYZE
ATTACH DATABASE
BEGIN TRANSACTION
Comment
COMMIT TRANSACTION
Core Functions:abs (x), changes (), coalesce (x, y,...), glob (x, y), ifnull (y), Hex (×), Last_insert_rowid (), length (×), Like (x, y), like (x, Y, z), load_extension (×), Load_extension (x, y), lower (x), LTrim (×), LTrim (× x, Y), Max (x, y,...), min (x, Y, ...), Nullif (x, y), quote (x.), random (), Randomblob (N), replace (x, Y, z), round (×), round (x, y), rtrim (×), RTrim (y), Soundex (x), Sqlite_compileoption_get (N), sqlite_compileoption_used (x), sqlite_source_id (), Sqlite_version (), substr (x, y, x, y), substr (× x), total_changes (), Trim (x.), Trim (y), typeof (×), Upper (X), Zeroblob (N).
CREATE INDEX
CREATE TABLE
CREATE TRIGGER
CREATE VIEW
CREATE VIRTUAL TABLE
Date and Time Functions:date (), Time (), DateTime (), Julianday (), strftime ().
DELETE
DETACH DATABASE
DROP INDEX
DROP TABLE
DROP TRIGGER
DROP VIEW
END TRANSACTION
EXPLAIN
Expression
INDEXED by
INSERT
Keywords
On CONFLICT clause
PRAGMA
REINDEX
RELEASE savepoint
REPLACE
ROLLBACK TRANSACTION
SavePoint
SELECT
UPDATE
VACUUM
It also supports transactional processing functions and so on. In a way, SQLite is a bit like Microsoft Access, which is a file-based database, a database is a file, in this file can be built more than one table, you can build indexes, triggers, and so on. Backing up this file backs up the entire database. However, SQLite supports cross-platform, simple operation and the ability to create databases directly in many languages, rather than requiring office support like access.

Iv. Management of SQLite

SQLite comes with a command-line administration tool. This command-line program can be called through the database name, and a new database and table can be created as follows:

C:\Users\Kim>sqlite3 alf.db
SQLite version <% version number%> 2016-08-28 20:11:36
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
Sqlite> .tables
Sqlite> create table mytable(name varchar(40), age smallint);
Sqlite> insert into mytable values(‘Jack-Zhou‘, 23);
Sqlite> select * from mytable;
Jack-Zhou|23
Sqlite> .tables
Mytable
Sqlite> .schema
CREATE TABLE mytable(name varchar(40), age smallint);
Sqlite>

The above creates an ALF database in which you create a table mytable, insert a piece of data, and then list all the tables and schemas in the database.
SQLite also comes with a command-line database parser that allows you to display Detailed information about the current state of any SQLite database:

C:\Users\Kim>sqlite3_analyzer alf.db /** Disk-Space Utilization Report For alf.db  
  
Page size in bytes.................... 1024  
Pages in the whole file (measured).... 2  
Pages in the whole file (calculated).. 2  
Pages that store data................. 2          100.0%  
Pages on the freelist (per header).... 0            0.0%  
Pages on the freelist (calculated).... 0            0.0%  
Pages of auto-vacuum overhead......... 0            0.0%  
Number of tables in the database...... 2  
Number of indices..................... 0  
Number of named indices............... 0  
Automatically generated indices....... 0  
Size of the file in bytes............. 2048  
Bytes of user payload stored.......... 13           0.63%  
  
*** Page counts for all tables with their indices ********************  
  
MYTABLE............................... 1           50.0%  
SQLITE_MASTER......................... 1           50.0%  
  
*** All tables *******************************************************  
  
Percentage of total database.......... 100.0%  
Number of entries..................... 2  
Bytes of storage consumed............. 2048  
Bytes of payload...................... 91           4.4%  
Average payload per entry............. 45.50  
Average unused bytes per entry........ 916.50  
Fragmentation.........................   0.0%  
Maximum payload per entry............. 78  
Entries that use overflow............. 0            0.0%  
Primary pages used.................... 2  
Overflow pages used................... 0  
Total pages used...................... 2  
Unused bytes on primary pages......... 1833        89.5%  
Unused bytes on overflow pages........ 0  
Unused bytes on all pages............. 1833        89.5%  
  
*** Table MYTABLE ****************************************************  
  
Percentage of total database..........  50.0%  
Number of entries..................... 1  
Bytes of storage consumed............. 1024  
Bytes of payload...................... 13           1.3%  
Average payload per entry............. 13.00  
Average unused bytes per entry........ 999.00  
Maximum payload per entry............. 13  
Entries that use overflow............. 0            0.0%  
Primary pages used.................... 1  
Overflow pages used................... 0  
Total pages used...................... 1  
Unused bytes on primary pages......... 999         97.6%  
Unused bytes on overflow pages........ 0  
Unused bytes on all pages............. 999         97.6% 

Because the database is fully managed using the command line interface, it can be a great convenience for database administrators. Of course there are many excellent third-party sqlite management tools, such as the QT-based Sqliteman,firefox plugin SQLite manager,sqlitespy and so on.

Also, there are two ways to back up your SQLite database . If the database is not in use, you can copy the database files directly to a secure location, and if the database is in use, you should use the. dump command (. Dump command, which can also be used to back up the database table), and create a file that contains the necessary commands and data to recreate the database:

C:\Users\Kim>sqlite3 alf.db  
SQLite version 3.7.14 2012-09-03 15:42:36  
Enter ".help" for instructions  
Enter SQL statements terminated with a ";"  
sqlite> .dump  
PRAGMA foreign_keys=OFF;  
BEGIN TRANSACTION;  
CREATE TABLE mytable(name varchar(40), age smallint);  
INSERT INTO "mytable" VALUES(‘Jack-Zhou‘,23);  
COMMIT;  
sqlite>

V. Unsupported SQL features
Rather than trying to list all the SQL92 features supported by SQLite, it is much easier to list only unsupported parts. Listed below are the SQL92 features that SQLite does not support (in the order of the list, the attributes near the top of the list are more likely to be added in the near future):
(1) Right OUTER join and full OUTER join: The left OUTER join has been implemented, but the right OUTER join and full OUTER join have not yet been implemented.
(2) Full ALTER TABLE support: Only the Rename table and add column operations of the ALTER TABLE command are supported. Other ALTER table operations such as Drop Column,alter column,add constraint and so on are ignored.
(3) Full trigger support: Supports the For each row trigger, but does not support the for each statement trigger.
(4) Writable view: The view in SQLite is read-only. You cannot perform a delete, insert, or update on the view. But you can create a trigger that fires when you try to delete,insert,update on the view, and then do the work you need in the trigger.
(5) Grant and REVOKE: since SQLite reads and writes an ordinary disk file, the only permissions that can be obtained are the standard file access rights of the operating system. The grant and REVOKE commands, typically found on a relational database system on a client/server architecture, are meaningless and therefore not implemented for an embedded database engine.

Vi. Application Situations
SQLite differs 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's small and fast. But these features are just some of the advantages, and users will find that SQLite is very stable. Excellent stability stems from its simplicity, and the simpler it is, the less prone it is to make mistakes. In addition to the simplicity, compactness and stability above, the most important thing is that SQLite strives for simplicity.
Simplification in a database engine can be said to be an advantage, but it can also be a disadvantage, mainly depends on what you want to do. To simplify, SQLite omits features that people consider useful, such as high concurrency, strict access control, rich built-in functionality, stored procedures, complex SQL language features, XML, and Java extensions, huge trillions of levels of data measurement, and more. If you need to use these features and don't mind their complexity, then sqlite might not be right for you. SQLite is not intended to be an enterprise-class database engine, nor is it intended to compete with Oracle or PostgreSQL.
With experience, SQLite is suitable for the following occasions: When you prefer simple management, use, and maintenance of databases rather than the myriad of complex features that enterprise databases provide, using SQLite is a wise choice. It is also proven that in many cases it is clear that simplicity is the best choice.
best trial scenarios for SQLite:
(1) application file format
SQLite has been a great success as a local disk file format for desktop applications. such as financial analysis tools, CAD packages, file management procedures and so on. A generic database open operation calls the Sqlite3_open () function and marks the starting point of an explicit local transaction (BEGIN TRANSACTION) to guarantee exclusive access to the contents of the file. The file save executes one commit (commit) while marking another explicit local transaction starting point. The purpose of this transaction is to ensure that updates to the 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 the user presses the Cancel/Redo button. This technology enables an infinite level of undo/redo functionality that requires only a small amount of code to be written.
(2) embedded device and application software
Because SQLite databases require little management, SQLite is a good choice for devices or services that are unattended or unattended. SQLite is well suited for mobile phones, PDAs, set-top boxes, and other instruments. As an embedded database it can also be applied to the client program well.
(3) website
As a database engine SQLite applies to small and medium-sized traffic sites (that is, 99.9% of sites). SQLite can handle how much web traffic is under the pressure of the site's database. In general, if a website has a click-through rate of less than 100,000 times per day, SQLite will work. 100,000 times/day is a conservative estimate, not an accurate upper limit. It turns out that SQLite still works even with 10 times times the above flow.
(4) Replace some special file formats
Many programs use the fopen (), fread (), or fwrite () functions to create and manage a number of custom files to hold data. Using SQLite instead of these custom file formats will be a good choice.
(5) Internal or temporary database
For programs that have a large amount of data that need to be filtered in different ways, relative to code that writes the same function, if you read the data into an in-memory SQLite database and then use the join query and the ORDER BY clause to extract the required data in a certain order and arrangement, it is often simpler and faster. Using the inline SQLite database as described above will make the program more flexible because adding a new column or index does not have to rewrite any query statements.
(6) command-line DataSet analysis tool
Experienced SQL users can use the SQLite command-line program to analyze a variety of mixed data sets. Data can be imported from a CSV (comma-separated value file) file and then sliced to produce countless comprehensive data reports. Possible uses include site log analysis, motion statistics analysis, editorial planning standards, and analysis of test results.
Of course you can do the same thing with an enterprise-class client/server database. The advantage of using SQLite in this case is that SQLite is easier to deploy and the result database is a separate file that you can store on a floppy disk or a USB flash drive or send it directly to a colleague via email.
(7) as a substitute for an enterprise database at the time of demo or Beta
If you are writing a client program that uses an enterprise database engine, it would make sense to use a generic database background that allows you to connect to different SQL database engines. Its greater significance is to connect the SQLite database engine statically to the client program, thus embedding SQLite as a hybrid database support. This allows the client program to use the SQLite database file for independent testing or validation.
(8) database teaching
Because the installation and use of SQLite is very simple (the installation process is almost negligible, only need to copy the SQLite source code or Sqlite.exe executable to the target host, and then directly run it), so it is very suitable for explaining SQL statements. Students can create their favorite databases very simply, and then send them by email to the teacher for comments or scoring. For those who are interested in how to implement a relational database management system (RDBMS) high-level students, according to the modular design and have good comments and documents of the SQLite source code, will lay a good foundation for them. This is not to say that SQLite is how to achieve the exact model of other database engines, but it is very suitable for students to understand how SQLite works quickly, so as to master other database system design implementation principles.
(9) test the expansion of the SQL language
The simple and modular design of SQLite makes it an excellent prototype platform for testing database language features or new ideas.

Vii. which occasions are suitable for use with other relational database management systems (RDBMS)
(1) client/server program
If you have many client programs to access a shared database over the network, you should consider using a client/server database instead of SQLite. SQLite works over a network file system, but because of delays with most network file systems, execution is not very efficient. In addition, most network file systems have bugs (including UNIX and Windows) in the implementation of file logical locks. If the file lock does not work properly, it may occur at the same time that two or more client programs change the same part of the same database, causing a database error. Because these problems are inherently bugs when the file system executes, SQLite has no way of avoiding them.
Good experience tells us that you should avoid using SQLite when many computers need to access the same database simultaneously through a network file system.
(2) high-traffic website
SQLite is typically used as a Web site for a backend database to work well. But if your site is so large that you start thinking about deploying a distributed database, you should not hesitate to consider an enterprise-class client/server database instead of SQLite.
(3) Very large data sets
When you start a transaction in SQLite (the transaction is generated before any writes occur, rather than the call begin...commit that must be displayed), the database engine will have to allocate a small piece of dirty pages (a file buffer page) to help it manage the rollback operation itself. SQLite requires 256 bytes per 1MB database file. For small databases, these spaces are nothing, but when the database grows to billions of bytes, the size of the buffer page is quite large. If you need to store or modify dozens of GB of data, you should consider using a different database engine.
(4) High concurrent access
SQLite Reads/writes locks the entire database file. This means that if any process reads a portion of the database, all other processes can no longer write to any part of the database. Similarly, if any one process is writing to the database, no other process can read any part of the database. In most cases this is not a problem, in which case each program uses the database for a short period of time and is not exclusive, so that the lock can be at most more than 10 milliseconds. But if some programs require high concurrency, then these programs need to look for other solutions.

Features of SQLite anatomy