SQLite file database, memory database establishment and import and export

First, the initial knowledge of SQLite

The occasional opportunity to contact SQLite, can not help but marvel at the size of SQLite and powerful (it seems that the software is not profiling OH), SQLite is open-source memory database (also known as embedded database), A large number of selfless programmers have contributed their power to the development of SQLite. SQLite is widely used, mobile phones, MP3, set-top boxes may exist in SQLite, Apple's Mac Os,linux, or Windows can apply SQLite when installing third-party software.

The advantages of SQLite technology:

1. SQLite lightweight, cross-platform relational open-source memory database, using SQLite just bring the dynamic library, you can use the full functionality of SQLite (dynamic library windows under 487kb,linux 347KB);

2. The core engine does not rely on third-party software and does not require installation;

3. All information in the database (such as tables, views, triggers, etc.) is contained within a single file. This file can be copied to other directories or other machines, but also replicable. If you use memory mode, you can not have the file;

4. In addition to the mainstream operating system, SQLite also supports a lot of unpopular operating systems. It is also supported for many embedded systems (such as Android, Windows Mobile, Symbin, Palm, VxWorks, etc.);

5. The SQLite API does not differentiate whether the current operating database is in memory or in a file (for storage media is transparent);

And so on

Disadvantages:

1. Locking mechanism for concurrent access
SQLite's performance in concurrent (including multi-process and multi-threaded) reading and writing is not ideal. The database may be exclusive to write operations, which can cause other read and write operations to block or error;

2. SQL standard Support not complete

If foreign KEY constraints are not supported;

It seems that there are more advantages than shortcomings! Oh!

Second, SQLite system organization

The SQLite module divides the query process into several discontinuous tasks, compiles the query statement at the top of the structure stack, does not execute, and handles the storage and interface of the operating system at the bottom.

Architecture of Figure 1-2sqlite

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

Third, SQLite file database, memory database establishment and import and export

The SQLite website (www.sqlite.org) also provides both the compiled version and the source program. Also available for Windows and Linux.

After the previous SQLite warm-up, hurriedly turn to the point, do something! (*^__^*)

3.1 Establishment of the file database and the memory databaseEstablishment of 3.1.1 File database

Windows:

1) Download the latest version of SQLite Sqlite3.exe;

2) Dos enters into the execution program directory;

3) Enter Sqlite3 d:\test.db (open the database if the following execution path exists test.db, or create a new test.db if there is no test.db under the execution path);

Linux:

1) Download the latest version of SQLite sqlite3;

2) The shell enters the directory of the Knowable program;

3) Enter Sqlite3/home/test.db (if the following execution path exists test.db open the database, if the execution path does not exist test.db the new test.db);

Then open or create a new file database library;

Establishment of 3.1.2 Memory database

[CPP]View Plain copy print

C code example:

 

Sqlite3 *db;

 

rc = Sqlite3_open (": Memory:", &db); //Create a database in memory

 

3.2 Import and export of file database and memory database

 

C code example:

 

////////////////////////////////////////////////////////////////////////////////////////////  

 

//Parameter description:

 

//pinmemory: Pointing to the Memory database pointer

 

//zfilename: A string pointer to the file database directory

 

//issave 0: Loading from a file database into memory Database 1: Backing up from a memory database to a 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;

}  

Invoke instance:

[CPP]View Plain copy print?

int ret = 0;

 

char *filename = "d:\\test.db";

Sqlite3 *memorydb;

 

ret = Sqlite3_open ("Memory:", &memorydb);

ret = loadorsavedb (memorydb, filename, 0) //File database import into the in-memory database

3.3 Import and export of file Database command format

3.3.1 File Database command format data export, backup

Method One: (SQLite database internal)

Sqlite>.output D:\test.sql

Sqlite>.dump

Sqlite>.output stdout

Method Two: (DOS command line)

Sqlite3 525.db. Dump>haha.sql

3.3.2 File Database command format data import

Sqlite>.read File.sql

SQLite file database, memory database establishment and import and export