[Sqlite] --) data migration and backup -- from earlier version 3.6.2 to later version 3.8.6

Data Migration 1: run the. dump command to help prompt. dump? TABLE ?... DumpthedatabaseinanSQLtextformatIfTABLEspecified, onlydumptablesmatchingLIKEpatternTABLE. Comprehension analysis: by default, dump command output is directed to the screen. For example,. dump

Data Migration 1: run the. dump command to help prompt. dump? TABLE? ... Dump the database in an SQL text format If TABLE specified, only dump tables matching LIKE pattern TABLE. Comprehension analysis: by default, the output of the. dump command is directed to the screen. For example,. dump

Data Migration

1. Use the. dump command

Command help prompt

. Dump? TABLE? ... Dump the database in an SQL text format

If TABLE specified, only dump tables matching

LIKE pattern TABLE.

Understanding and analysis:

By default, the output of the. dump command is directed to the screen. For example,. dump

To redirect the output to a file, run the. dump [filename] command to redirect all the output to the specified file. To restore the output to the screen, run the. output stdout command.

Sqlite>. output file. SQL

Sqlite>. dump

Sqlite>. output stdout

NOTE: If file. SQL does not exist, the file will be created in the current working directory. If the file exists, it will be overwritten.

2. Prepare test data:

Create table company (id int not null, name varchar (20), age int, address varchar (20), salary decimal (7, 2 ));

INSERT INTO COMPANY

SELECT 1, 'Paul ', 32, 'california', 20000.0 UNION ALL

SELECT 2, 'allen ', 25, 'texas', 15000.0 UNION ALL

SELECT 3, 'Teddy ', 23, 'norway', 20000.0 UNION ALL

SELECT 4, 'mark', 25, 'Rich-Mond ', 65000.0 UNION ALL

SELECT 5, 'David ', 27, 'texas', 85000.0 UNION ALL

SELECT 6, 'Kim ', 22, 'South-Hall', 45000.0 UNION ALL

SELECT 7, 'James ', 24, 'houston', 10000.0;

SELECT * from company;

Create table t1 (id INT, name varchar (20 ));

Insert into t1 SELECT 1, 'A' union all select 2, 'B' UNION ALL SELECT 3, 'C ';

Change the name of a table in earlier versions to COMPANY_TMP.

Alter table company rename to COMPANY_TMP;

3. Start the backup operation and back up the backup in earlier version 3.6.2:

[Root @ name01 ~] #/Usr/bin/sqlite3.bak. 3.6.2 tim

SQLite version 3.6.20

Enter ". help" for instructions

Enter SQL statements terminated with ";"

Sqlite>

Sqlite>. headers on

Sqlite>. mode columns

Sqlite>. output alltables. SQL

Sqlite>. dump

Sqlite>. exit

4. view the generated backup file:

[Root @ name01 ~] # More alltables. SQL

PRAGMA foreign_keys = OFF;

Begin transaction;

Create table "COMPANY_TMP" (id int not null, name varchar (20), age int, address varchar (20), salary decimal (7,2 ));

Insert into "COMPANY_TMP" VALUES (1, 'pol', 32, 'california, 20000 );

Insert into "COMPANY_TMP" VALUES (2, 'allen ', 25, 'texas, 15000 );

Insert into "COMPANY_TMP" VALUES (3, 'Teddy ', 23, 'norway', 20000 );

Insert into "COMPANY_TMP" VALUES (4, 'mark', 25, 'Rich-Mond, 65000 );

Insert into "COMPANY_TMP" VALUES (5, 'David', 27, 'texas, 85000 );

Insert into "COMPANY_TMP" VALUES (6, 'Kim ', 22, 'South-Hall', 45000 );

Insert into "COMPANY_TMP" VALUES (7, 'James ', 24, 'houston, 10000 );

Create table t1 (id INT, name varchar (20 ));

Insert into "t1" VALUES (1, 'A ');

Insert into "t1" VALUES (2, 'B ');

Insert into "t1" VALUES (3, 'C ');

COMMIT;

[Root @ name01 ~] #

All the SQL statements backed up into dml and ddl are displayed.

V. Restore data on version 3.8.6

1. Create the same COMPANY table on 3.8.6, but add two more fields IPHONE and LOGIN_DATE, as shown below:

Drop table if exists company;

Create table company (

Id int not null,

Name varchar (20 ),

Age int,

Address varchar (20 ),

Salary decimal (7,2 ),

Iphone varchar (16)

Not null, LOGIN_DATE DATETIME

);

INSERT INTO COMPANY

SELECT 1, 'Paul ', 32, 'california', 20000.0, '123', DATETIME ('now ') UNION ALL

SELECT 2, 'allen', 25, 'texas ', 15000.0, '123', DATETIME ('now ');

SELECT * from company;

2. Start to restore the SQL script for importing the original backup data.

[Root @ name01 ~] # Sqlite3 ti

SQLite VERSION 3.8.6 2014-08-15 11:46:33

Enter ". help" for usage hints.

Sqlite>. headers ON

Sqlite>. MODE COLUMNS

Sqlite>. READ alltables. SQL

Sqlite>. TABLE

COMPANY COMPANY_TMP t1

Sqlite>

3. Check the data in the existing table before migrating data from the old temporary table to the new table.

Sqlite> SELECT * from company;

Id name age address salary iphone LOGIN_DATE

--------------------------------------------------------------------------------

1 Paul 32 California 20000 14782121412 11:14:07

2 Allen 25 Texas 15000 13982121412 11:14:07

Sqlite>

4. Start Using INSERT to migrate data to the table COMPANY with the newly added Field

Sqlite> insert into company (ID, NAME, AGE, ADDRESS, SALARY, IPHONE) select id, NAME, AGE, ADDRESS, SALARY, ''FROM COMPANY_TMP;

Sqlite> SELECT * from company;

Id name age address salary iphone LOGIN_DATE

--------------------------------------------------------------------------------

1 Paul 32 California 20000 14782121412 11:14:07

2 Allen 25 Texas 15000 13982121412 11:14:07

1 Paul 32 California 20000

2 Allen 25, Texas 15000

3 Teddy 23 Norway 20000

4 Mark 25 Rich-Mond 65000

5 David 27, Texas 85000

6. Kim 22 South-Hall 45000

7 James 24 Houston 10000

Sqlite>

After the migrated data is displayed, the old table data has been migrated to the new table.

6. Delete the old temporary table.

Sqlite> drop table if exists COMPANY_TMP;

Sqlite>

So far, the entire data migration has been successfully completed.