Feelings about using SQLite

I recently used the SQLite database. To be honest, this is the third database I used. The reason for using it should be that it is lightweight and has good performance. However, some problems have also been found after a long time of use. Occasionally, there will also be complaints, but it will not scold SQLite for being a broken thing or something. There are several reasons: a thorough understanding of SQLite makes

I recently used the SQLite database. To be honest, this is the third database I used. The reason for using it should be that it is lightweight and has good performance. However, some problems have also been found after a long time of use. Occasionally, there will also be complaints, but it will not scold SQLite for being a broken thing or something. There are several reasons: a thorough understanding of SQLite makes

Feelings about using SQLite

I have been using the SQLite database recently. To be honest, this is the third database I have used. The reason for using it should be that it is lightweight and has good performance. However, some problems have also been found after a long time of use. Occasionally, there will also be complaints, but I will not scold SQLite for being a broken thing or something. There are several reasons: I have a thorough understanding of SQLite, and I am not familiar with the use of SQLite, the ability to solve the problem is limited; I am not capable of writing a database better than SQLite.

I checked the applicable scenarios of SQLite on the Internet. Websites with 100000 visits/day, embedded devices and applications, and application file formats. However, when the data can be read, the lock table exception will also be thrown. SQLite locks are coarse-grained, so we do not know how to handle the exception. What else is the database corruption. Database disk image is malformed. There are three reasons for database damage on the Internet:

Sqlite databases are interrupted due to power outages during write operations.

The disk space in the sqlite database is insufficient.

Disk has bad channels, etc.

The damage to the database was estimated to be the first cause. At that time, no data was backed up. Only the. dump command can be manually used to export data from the damaged database. Let's talk about this.

In the command mode of SQLite, open the database and enter the following commands in sequence.

Sqlite>. mode insert

Sqlite>. output test. SQL

Sqlite>. dump

Sqlite>. exit

Of course, you do not have to run the ". exit" command. Press "Ctrl + C" or directly turn it off. The exported file name "test. SQL" is your favorite.

Or it is better to use this command.

Sqlite3.exe dbname. db. dump> fileName. SQL

The SQL statement exported by this command is more neat than the one above.

No matter which type of exported SQL statement, if there is a problem with the database, there will be this line in the exported SQL statement

/***** ERROR: (11) database disk image is malformed *****/

Finally, you can only roll back the transaction RollBack.

If you want to use the data back, you can change the rollback transaction and delete the ERROR.

Then, import the data.

Sqlite3.exe newDBName. db

Sqlite>. read test. SQL

Sqlite>. exit

In the future, we plan to regularly back up SQLite. The backup method is simply file I/O operations-Copy a Copy. If a Database disk image is malformed exception is thrown during Database reading and writing, the Database is deemed to have been damaged and immediately changed to the backup Database.

When writing data to a database and operating multiple records in the database, you can use a transaction package to perform data operations on multiple tables. When writing data to the database, A file named in the format of database file name + "-journal" is generated under the same directory as the database file. This file will be deleted immediately after the transaction is submitted or rolled back. However, I heard that this file is not deleted in Android, but it only exists in a 0-B size file. When a program is started, if the current host is down due to a power failure and the rollback log file is saved on the disk, SQLite will find the temporary file in the process of opening the database file. SQLite will restore the database based on the file before it opens the database successfully, to ensure that the database data is returned to the status before the previous transaction starts. In this way, database corruption cannot be avoided, but data status consistency can be ensured.