SQLite file database, memory database creation, Import and Export

Document directory

• 3.1 create a file database and Memory Database
• 3.3 Import and Export of file database command format

I. First recognized SQLite

By chance, when I came into contact with SQLite, I couldn't help but wonder how small and powerful SQLite is (it seems that the software cannot look like it). SQLite is an open-source memory database (also known as an embedded database ), A large number of selfless programmers have contributed to the development of SQLite. SQLite is widely used. SQLite may exist in mobile phones, MP3, and set-top boxes. SQLite can also be used when third-party software is installed in Apple Mac OS, Linux, or windows.

Advantages of SQLite technology:

1. SQLite is a lightweight, cross-platform relational open-source memory database. With SQLite, you only need to bring a dynamic library to use all the functions of SQLite (dynamic library in Windows and Linux );

2. The core engine does not rely on third-party software and does not need to be installed;

3. All information in the database (such as tables, views, triggers, and so on) is contained in a single file. This file can be copied to other directories or other machines. If the memory is used, the file does not exist;

4. In addition to mainstream operating systems, SQLite also supports many unpopular operating systems. It also supports many embedded systems (such as Android, Windows Mobile, symbin, palm, and VxWorks;

5. The SQLite API does not distinguish whether the database currently operated is in memory or in files (transparent to the storage medium );

And so on;

Disadvantages:

1. concurrent access Lock Mechanism
SQLite has poor performance in concurrent (including multi-process and multi-thread) read/write operations. The database may be exclusive to write operations, leading to blocking or error of other read/write operations;

2. Unsupported SQL standards

If foreign key constraints are not supported;

It seems that there are more advantages than disadvantages! Haha!

Ii. SQLite System

The SQLite module divides the Query Process into several discontinuous tasks. It compiles the query statements at the top of the structure stack without executing them. It processes the storage and interfaces of the operating system at the bottom.

 

Figure 1-2 SQLite Architecture

(Note: The structure chart is reproduced in the SQLite authoritative guide)

Iii. Create and Import and Export SQLite file database and Memory Database

The SQLite website (www.sqlite.org) also provides compiled versions and source programs. Applicable to both Windows and Linux.

After the above SQLite warm-up, hurry into the subject, do something! (* ^__ ^ *)

3.1 create a file database and a memory database 3.1.1 create a file database

Windows:

1) download sqlite's latest sqlite3.exe;

2) DoS enters the execution program directory;

3) Input sqlite3 D: \ test. dB (open the database if test. DB exists in the following execution path; create test. DB if test. DB does not exist in the execution path );

Linux:

1) download sqlite3, the latest SQLite version;

2) shell enters the intellectual program directory;

3) Input sqlite3/home/test. dB (open the database if test. DB exists in the following execution path; create test. DB if test. DB does not exist in the execution path );

At this point, a file database is opened or created;

3.1.2 create a memory database

C sample code: sqlite3 * dB; rc = sqlite3_open (": Memory:", & dB ); // create a database 3.2 file database in the memory, and import and export the memory database C code example: //////////////////////////////////////// //////////////////////////////////////// ///////////// parameter description: // pinmemory: pointer to the memory database // zfilename: String pointer to the directory of the file database // issave 0: load from the file database to the memory database 1: back up the database from the memory to the file database ///////////////////////////////// //////////////////////////////////////// //// // int loadorsavedb (sqlite3 * Pinmemeory, const char * zfilename, int issave) {int RC; sqlite3 * pfile; sqlite3_backup * pbackup; sqlite3 * PTO; sqlite3 * pfrom; rc = sqlite3_open (zfilename, & pfile); If (rc = sqlite_ OK) {pfrom = (issave? Pinmemeory: pfile); PTO = (issave? Pfile: pinmemeory); pbackup = sqlite3_backup_init (PTO, "Main", pfrom, "Main"); If (pbackup) {(void) sqlite3_backup_step (pbackup,-1 ); (void) sqlite3_backup_finish (pbackup);} rc = sqlite3_errcode (PTO) ;}( void) sqlite3_close (pfile); Return RC ;}

Call instance:

Int ret = 0; char * filename = "D: \ test. DB "; sqlite3 * memorydb; ret = sqlite3_open (" Memory: ", & memorydb); ret = loadorsavedb (memorydb, filename, 0) // import the file database to the memory database

3.3 Import and Export of file database command format

3.3.1 export and backup of file database command format data

Method 1: (internal SQLite database)

SQLite>. Output D: \ test. SQL

SQLite>. Dump

SQLite>. Output stdout

Method 2: (doscommand line)

Sqlite3 525.db. Dump> Haha. SQL

3.3.2 file database command format data import

SQLite>. Read File. SQL

This is the end of today. To be continue ......