Summarization of SQLite3 key related knowledge in _sqlite

SQLite empty the table and return the self-added column to zero

The SQL standard has a TRUNCATE TABLE statement that clears all the contents of the table.
But SQLite does not support this statement. It is OK to use the DELETE from TableName directly in the SQLite.
For most DBMS, using delete is not as fast as using truncate because TRUNCATE does not have to access the entire table without recording data changes.

Although SQLite does not support truncate, it optimizes delete:
Usually, when you clear the table, you need to return the self-added column to zero. The method for defining the SQLite in the list is as follows:

Copy Code code as follows:

CREATE TABLE tablename (ID integer PRIMARY KEY autoincrement, name varchar, type INTEGER);

A table named Sqlite_sequence is automatically created when the SQLite database contains a self-added column.
This table contains two columns: Name and seq. Name records the table in which the SEQ records the current ordinal number (the next record is the current ordinal plus 1).
If you want to return the number of a self-added column to zero, you just need to modify the Sqlite_sequence table.

Copy Code code as follows:

UPDATE sqlite_sequence SET seq = 0 WHERE name= ' tablename ';

You can also delete the record directly:

Copy Code code as follows:

DELETE from Sqlite_sequence WHERE name= ' tablename ';

To return all of the table's own columns to zero, clear the Sqlite_sequence table directly:

Copy Code code as follows:

DELETE from Sqlite_sequence;

The use of integer PRIMARY KEY autoincrement and rowID in SQLite

When I design a table with SQLite, I suddenly think of a problem, in the table I designed, each table has its own reshaping ID value as the primary key,
In fact, you can not specify such an ID value, SQLite inside would have a rowid for each table, this rowid can be used as an implied field,

Why not use this internal ROWID directly as the ID primary key for each table?
There are a number of problems with the primary key from the growth field, such as maintaining or resolving primary key conflicts in large distribution applications.
In some large-scale distribution applications, the primary key is generally selected GUID, this can effectively avoid the primary key conflict, reduce the maintenance of the primary key engineering.
Of course, for small and medium applications, the benefits from growing fields are more simple and fast.

In SQLite, a self-growing field is defined as an INTEGER PRIMARY KEY autoincrement
So when inserting a new data, you simply specify the value of the field as null, and the engine automatically sets its value.
The engine will be set to the maximum rowid+1 of course, you can also set a number that is not NULL to specify this value, but you must be careful not to cause conflict.

The new value of ROWID will randomly find a value that has not been used before the maximum number. Therefore, the value of ROWID is strictly monotonically increased before the ROWID reaches its maximum value.
The algorithm for the INTEGER PRIMARY KEY autoincrement from the growth field is slightly different from the ROWID.
First, after the maximum is reached, ROWID will find the corresponding rowid for the deleted field as the new value, and the self-growing field will throw a sqlite_full error.
Second, since the growth field adds a new value, it is looking for a rowid that has never been used as a new value, while rowID is looking for the largest existing rowid+1.

The impact on the application will be large, especially with some meta records that depend on the ID value, and are only suitable for using the ROWID field instead of the.
For example, we design a meta record table:

Copy Code code as follows:

Create table meta_struct (ID integer PRIMARY KEY autoincrement, name varchar, type INTEGER);

Then, define a level table that describes the structure of the other tables:

Copy Code code as follows:

Create table meta_table (TableID integer, Table_field integer)

Finally, our application can produce a two level table that is actually used according to this level of table.
This ensures that the IDs in the compatibility meta_struct must be unique and that if a field is deleted, the ID value of the field cannot be reused, otherwise, when the database is merged,
First-level tables and level two tables can be confusing. Therefore, the primary key in the Meta_struct table can only use the self-growing field, not the ROWID.

Third, using the Self-growth field, the engine automatically generates a Sqlite_sequence table that records the maximum used value of the self-growing field for each table, and the user can see
It can be used with update, delete, and insert operations, but not recommended, which can make the engine confusing. If you use ROWID, there will also be an internal table where the user can maintain the ROWID value but not see it.