Thoughts on SQLite Problems

This article mainly introducesSQLiteThe following describes some database problems and solutions.

Rowid and integer primary keys and auto-increment attributes

In most cases, sqlite3 tables have a rowid (also called OID, _ rowid _), which is a 64-bit integer and serves as the primary key of the B-tree storage structure of SQLite. Therefore, rowid queries are faster than other primary key queries.

During the insert operation, do not specify the value of rowid, so that the system determines the value. Because SQLite uses sqlite_sequence to track the value of the table's rowid. SQLite defines the value of rowid.Algorithm: Within the range of rowid, The rowid of the record to be inserted is always the maximum value + 1 that exists in the table. For example, if you insert five records in sequence, then the rowid of the last record is 5. If you delete this record and insert a new record, the rowid of the new record is 6. When rowid reaches the maximum value that can be expressed, if a new record is to be inserted, the system will randomly take a rowid (Deleted previously) from a positive integer that has not been used ). If there is no unused positive integer and you do not specify rowid as a negative number during insertion, the system will throw the sqlite_full error.

If the primary key is set during table creation and the column of the primary key is INTEGER (not int, short integer, etc.), and the primary key is not set in descending order, in this case, the primary key is the alias of rowid. In other words, the primary key and rowid are no different. If we set the primary key autoincrement attribute again. What is the difference between rowid and rowid? The difference is that the primary key value is an unused rowid value, and the rowid value system will ensure that it increases monotonically, generally, the maximum value of rowid exists in the table is + 1. This is usually because of the following situation: When the insert operation fails due to table constraints, the rowid that was originally assigned a value may not be used in the next insert operation, in this case, the primary key value is the maximum value of rowid + 2 that exists in the table.

Therefore, for tables whose user ID is an integer, whether to independently set the primary key for maintenance or directly use rowid as the primary key depends on their respective business logic relationships. In this case, the primary key feature of rowid is usually not used.

Union all

Today, I encountered a problem. Boss has new requirements for the app. Specifically, the business is:

Assume there are two tables:

 
Create Table A (username varchar (50), created datetime); Create Table B (nickname varchar (50), userid integer, added datetime );
 

There is no relationship between table A and Table B. The only link is that one column of attributes is datetime. Boss needs to extract all values of A and B and sort them by time.

At first glance, I don't know how to solve the problem, because the two tables are just irrelevant. Later I realized that union can be used, for example:

 
Select null as col1, username as col2, created as col3 from a union all select nickname as col1, userid as col2, added as col3 from B order by col3;
 

Then, different objects are generated based on whether a column value is null.

Thoughts:

1. When this problem occurs, the first thing that comes to mind is join, but the table is irrelevant, so it is stuck in a dead end. Jumping out of this thinking, starting from the SQL itself, the problem is solved.

2. From the perspective of software development, if there is a new demand for half of the development, the database structure will be a challenge for the existing software architecture. However, such changes are often inevitable, especially in small companies that use scrum for development. In this case, the architect can only rely on his own tests to design a scalable architecture.

Retrieve a piece of data

SQLite uses limit to retrieve a piece of data. For example, to retrieve the 6-20 records, you only need:

 
Select * From tablename limit 5, 15.
 

We will introduce the issue of SQLite in the future.ArticleNext, we will introduce that interested friends can continue to follow.