Debugging environment:

ubuntu 16.04

Android sdk 7.1.1 / platform-tools sqlite3

(1) Creating database files

Instead of creating db files manually, execute them directly from the command line:

~/test_sqlite$ sqlite3 test.db

Go to the SQLIte command line and build tables directly using standard SQL:

sqlite> create table test_table (id integer primary key, name text, description text);
sqlite> .exit

See the corresponding directory, DB file "test.db" has been automatically created. Open it again and check the table you just created:

~/test_sqlite$ sqlite3 test.db
SQLite version 3.9.2 2015-11-02 18:31:45
Enter ".help" for usage hints.
sqlite> .tables
test_table

Of course, you can also use the schema command to view more detailed information:

sqlite> .schema test_table
CREATE TABLE test_table (id integer primary key, name text, description text);

Insert some data into test_table:

sqlite> insert into test_table (id, name, description) values (0, 'name0', 'des0');
sqlite> insert into test_table (id, name, description) values (1, 'name1', 'des1');
sqlite> insert into test_table (id, name, description) values (2, 'name2', 'des2');
sqlite> 
sqlite> 
sqlite> select * from test_table;
0|name0|des0
1|name1|des1
2|name2|des2

(2) Using dump command to export database tables to files

First, redirect sqlite output to a file. Similarly, it is not necessary to create a file manually, but to give the file name directly.

sqlite> .output test_table.sql

Using the dump command, the data is exported to the file. If dump has no parameters, the entire database is exported:

sqlite> .dump test_table

Redirected output back to standard command line output:

sqlite> .output stdout

View the contents of the test_table.sql file:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test_table (id integer primary key, name text, description text);
INSERT INTO "test_table" VALUES(0,'name0','des0');
INSERT INTO "test_table" VALUES(1,'name1','des1');
INSERT INTO "test_table" VALUES(2,'name2','des2');
COMMIT;

dump commands export database objects into SQL format: Database Definition Language (DDL) and Data Operating Language (DML) commands. This information can help us reconstruct database objects and the data in them. It can be said that the whole table is completely reproduced.

As you can see from the test_table.sql file content, it contains DDL -- create table, DML -- insert values, and is completed in a database transaction.

(3) Clone Recovery Database Table

Using the dump information of the database exported in (2) to execute the SQL in batches:

sqlite> BEGIN TRANSACTION;
CREATE TABLE test_table (id integer primary key, name text, description text);
INSERT INTO "test_table" VALUES(0,'name0','des0');
INSERT INTO "test_table" VALUES(1,'name1','des1');
INSERT INTO "test_table" VALUES(2,'name2','des2');sqlite> 
COMMIT;

Look at the facial expression:

sqlite> .tables
test_table
sqlite> select * from test_table;
0|name0|des0
1|name1|des1
2|name2|des2

See that the table test_table is cloned and restored.