Introduction to SQLite Database

SQLite is an open source embedded database engine written by D.richard Hipp in C language. It supports most of the SQL92 standards and can be run on all major operating systems.

SQLite consists of the following sections: SQL compiler, Kernel, backend, and attachments. SQLite makes it easier to debug, modify, and extend SQLite's kernel by leveraging virtual machines and virtual database engines (VDBE). All SQL statements are compiled into an easy-to-read assembly that can be executed in an SQLite virtual machine. The overall structure of SQLite is shown below:

It is worth mentioning that the pocket-sized SQLite can support up to 2TB of database size, each database is in the form of a single file, which is stored in the form of B-TREE data structure on disk.

In terms of transactional processing, SQLite implements independent transactions with exclusive and shared locks at the database level. This means that multiple processes can read data from the same database at the same time, but only one can write data. An exclusive lock must be obtained before a process or thread wants the database to perform a write operation. After an exclusive lock is obtained, other read or write operations will no longer occur.

SQLite takes a Dynamic data type, and when a value is inserted into the database, SQLite checks its type, and if the type does not match the associated column, SQLite attempts to convert the value to the type of the column, and if it cannot, the value is stored as its own type, which SQLite calls " Weak type ". However, there is a special case, if the integer PRIMARY KEY, then the other type will not be converted, will be reported a "datatype Missmatch" error.

In summary, SQLite supports NULL, Integer, REAL, text, and BLOB data types, representing null values, integer values, floating-point values, string literals, and binary objects, respectively.

Now, let's do the SQLite database in person.

Before the operation, friends to download the SQLite database, the official download page is http://sqlite.org/download.html, I was under Windows test, so I chose precompiled Binaries for Windows Sqlite-shell-win32-x86 and sqlite-analyzer-win32-x86 zip packages, the former is the SQLite database engine, the latter is the SQLite Database Analyzer, mainly used to analyze the status of the database and other information , you can also according to their own situation to download. After the download is completed, respectively, to get two executable files,

These two files can be placed according to their preferences in the specified location, I put it in the D packing directory. Now let's take a step-by-stage operation of SQLite:

To create a database:

[SQL]View Plaincopy

2. D:\>sqlite3 test.db
4. SQLite version 3.7.7.1 2011-06-28 17:39:05
6. Enter '. Help ' for instructions
8. Enter SQL statements terminated with a ";"
10. sqlite>. Databases
12. Seq name File
16. ---  ---------------  ----------------------------------------------------------
20. 0 Main D:\test.db
24. Sqlite>

We executed the sqlite3 command, the parameter is the name of the database, if the database already exists, then use, if not present, create a new, here we simply created the test.db in the current location, you can also create your own database in any existing and writable directory. (If you are not familiar with SQLite commands, you can perform the ". Help" command to list all the command listings for viewing).

To create a table:

[SQL]View Plaincopy

2. sqlite> CREATE TABLE person (id INTEGER PRIMARY KEY autoincrement, name VARCHAR (a), age smallint);
4. Sqlite>. Tables
6. Person
8. Sqlite>. Schema Person
10. CREATE TABLE person (id INTEGER PRIMARY KEY autoincrement, name VARCHAR (a), age SMALLINT);
12. Sqlite>

After we have created the table, we can use the ". Tables" command to see the existing tables, use the ". Schema" command to see the structure of the table, and if there are no table names for the parameters, the table statement will be output for all tables.

Insert data:

[SQL]View Plaincopy

1. sqlite> INSERT into person VALUES (NULL, ' John ', 30);
2. Sqlite> SELECT * from person ;
3. 1|john|30

Import data from a. sql file:

[SQL]View Plaincopy

1. Sqlite>. Read Test.sql
2. Sqlite> SELECT * from person ;
3. 1|john|30
4. 2|david|35
5. 3|henry|40
6. Sqlite>

Analyze Database Usage Status:

[SQL]View Plaincopy

2. D:\>sqlite3_analyzer test.db
4. /** disk-Space Utilization Report for test.db
8. Page size in bytes ......... ..... 1024x768
10. Pages in the whole file (measured) .... 4
12. Pages in the whole file (calculated): 4
14. Pages that store data ....... ..... 4 100%
16. Pages on the freelist (per header) .... 0 0.0%
18. Pages on the freelist (calculated) .... 0 0.0%
20. Pages of auto-vacuum overhead ..... 0 0.0%
22. Number of tables in the database ... 4
24. Number of indices ........ ....... 0
26. Number of named indices ...... ..... 0
28. Automatically generated indices ... 0
30. Size of the file in bytes ....... 4096
32. Bytes of user payload stored ..... 0.95%
34. ...

To back up the database:

There are two ways to back up the SQLite database. If the database is in use, you should use the. dump command from the command-line interface. This creates a file that contains the necessary commands and data to recreate the database. The dump command can also be used to back up database tables.

[SQL]View Plaincopy

2. sqlite>. Dump
4. PRAGMA foreign_keys=OFF;
6. BEGIN TRANSACTION;
8. CREATE TABLE person (id INTEGER PRIMARY KEY autoincrement, name VARCHAR (a), age SMALLINT);
10. INSERT into "person" VALUES (1,' John ', 30);
12. INSERT into "person" VALUES (2,' David ', 35);
14. INSERT into "person" VALUES (3,' Henry ', 40);
16. DELETE from sqlite_sequence;
18. INSERT into "Sqlite_sequence" VALUES (' person ', 3);
20. COMMIT;
22. Sqlite>. Output Dump.sql
24. sqlite>. Dump
26. Sqlite>

We can specify the output target as a file, and then use the command, the output information will be written to the specified file, if you want to revert to the standard output, you can set:

[SQL]View Plaincopy

2. Sqlite>. Output stdout
4. sqlite>. Dump
6. PRAGMA foreign_keys=OFF;
8. BEGIN TRANSACTION;
10. CREATE TABLE person (id INTEGER PRIMARY KEY autoincrement, name VARCHAR (a), age SMALLINT);
12. INSERT into "person" VALUES (1,' John ', 30);
14. INSERT into "person" VALUES (2,' David ', 35);
16. INSERT into "person" VALUES (3,' Henry ', 40);
18. DELETE from sqlite_sequence;
20. INSERT into "Sqlite_sequence" VALUES (' person ', 3);
22. COMMIT;
24. Sqlite>

If the database is not in use, you can copy the database files directly to a secure location.

Finally, we can use ". Quit" or ". Exit" to exit SQLite.

Management tools:

Now there are many SQLite management tools on the network, I recommend a useful tool for you: SQLite Expert.

Using SQLite in Java:

We want to use SQLite in Java, we need to download the SQLite related drivers, we recommend you go to the Http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC page to download the latest driver package, Now the latest version is Sqlite-jdbc-3.7.2.jar, which is a bit larger because it contains the native class library for Linux, Mac, and Windows.

After downloading the driver, we created a new project called SQLite:

In, we introduced the SQLite driver package to the CLASSPATH, and then set up a DB folder for the database file to be placed. Finally, let's take a look at the Test.java code:

[Java]View Plaincopy

2. Package com.scott.sqlite;
4. Import java.sql.Connection;
6. Import Java.sql.DriverManager;
8. Import Java.sql.ResultSet;
10. Import java.sql.Statement;
14. Public class Test {
16. public static void Main (string[] args) throws Exception {
18. Class.forName ("Org.sqlite.JDBC");
20. Connection conn = drivermanager.getconnection ("jdbc:sqlite:db/test.db");
22. Statement stmt = Conn.createstatement ();
26. Stmt.executeupdate ("DROP TABLE IF EXISTS person");
28. Stmt.executeupdate ("CREATE TABLE person (id INTEGER, name STRING)");
30. Stmt.executeupdate ("INSERT into person VALUES (1, ' John ')");
32. Stmt.executeupdate ("INSERT into Person VALUES (2, ' David ')");
34. Stmt.executeupdate ("INSERT into Person VALUES (3, ' Henry ')");
36. ResultSet rs = stmt.executequery ("select * from person");
38. While (Rs.next ()) {
40. System.out.println ("id=>" + rs.getint ("id") + ", name=>" + rs.getstring ("name"));
42. }
44. Stmt.close ();
46. Conn.close ();
48. }
50. }

Execute the Test.java file with the following results:

This time, in our DB directory, a test.db file is generated:

Instructions for using SQLite:

There are currently no Web servers available for SQLite. The only way to run SQLite from an application that resides on another computer is to run from a network share. This can cause problems such as file locking issues with UNIX® and Windows® network shares. There are also performance degradation issues due to latency associated with accessing network shares.

SQLite only provides database-level locking.

SQLite does not have a user account concept, but instead determines the permissions of all databases based on the file system.

Conclusion:

Because of its low resource footprint, good performance, and 0 management costs, embedded databases have its niche, with built-in SQLite databases for developers, such as Android and iphone, whose ease of use accelerates application development and makes complex data storage much easier.