preface
This article was written about this time last year, and I forgot to send it out all the time.
SQLiteLint. Lint, despite the name “Lint”, is not a static check of code, but rather an analysis of SQL statements, execution sequences, table information, etc., while the APP is running. A bit like “Lint” : Step in at the development stage and apply some rules of best practice to detect potential and suspicious SQLite usage problems.
The usefulness of SQLiteLint
SQLiteLint: SQLiteLint: SQLiteLint: SQLiteLint: SQLiteLint: SQLiteLint: SQLiteLint
- Detect index usage problems
- Full table scan caused by no index
- Full table scan caused by invalid index
- Unnecessary temporary tree sorting
- Insufficient index combination
- Detect redundant index problems
- Detect select * issues
- Detect an Autoincrement problem
- Prepared statement is recommended
- Detection is recommended using the Without Rowid feature
Quick access to the
1. Add dependencies
- ingralde.propetiesSpecify Matrix version
MATRIX_VERSION = 0.5.2(Latest version at the time of writing) - Add the dependencies
dependencies {
debugCompile "com.tencent.matrix:matrix-sqlite-lint-android-sdk:${MATRIX_VERSION}"
releaseCompile "com.tencent.matrix:matrix-sqlite-lint-android-sdk-no-op:${MATRIX_VERSION}"
}
Copy the code2.SQLiteLint initializes and adds the concerned database
Class in the Application#onCreate method
SQLiteLintConfig config =new SQLiteLintConfig(SQLiteLint.SqlExecutionCallbackMode.HOOK);
SQLiteLintPlugin sqLiteLintPlugin = new SQLiteLintPlugin(config);
builder.plugin(sqLiteLintPlugin);
Matrix.init(builder.build());
sqLiteLintPlugin.start();
SQLiteLintPlugin plugin = (SQLiteLintPlugin) Matrix.with().getPluginByClass(SQLiteLintPlugin.class);
if (plugin == null) {
return;
}
if(! plugin.isPluginStarted()) { plugin.start(); } plugin.addConcernedDB(new SQLiteLintConfig.ConcernDb(TestDBHelper.get().getWritableDatabase())
//.setWhiteListXml(R.xml.sqlite_lint_whitelist)//disable white list by default
.enableAllCheckers());
Copy the codeActual test use
Here is the official example of Matrix.
public class TestDBHelper extends SQLiteOpenHelper {
private static final int DB_VERSION = 1;
private static final String DB_NAME = "sqliteLintTest.db";
public static final String TABLE_NAME = "testTable";
public static final String TABLE_NAME_AUTO_INCREMENT = "testTableAutoIncrement";
public static final String TABLE_NAME_WITHOUT_ROWID_BETTER = "testTableWithoutRowid";
public static final String TABLE_NAME_Redundant_index = "testTableRedundantIndex";
public static final String TABLE_NAME_CONTACT = "contact";
private static TestDBHelper mHelper = null; .@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
String sql = "create table if not exists " + TABLE_NAME + " (Id integer primary key, name text, age integer)";
sqLiteDatabase.execSQL(sql);
String sqlAutoIncrement = "create table if not exists " + TABLE_NAME_AUTO_INCREMENT + " (Id integer primary key AUTOINCREMENT, name text, age integer)";
sqLiteDatabase.execSQL(sqlAutoIncrement);
String sqlWithoutRowId = "create table if not exists " + TABLE_NAME_WITHOUT_ROWID_BETTER + " (Id text primary key, name integer, age integer)";
sqLiteDatabase.execSQL(sqlWithoutRowId);
String sqlRedundantIndex = "create table if not exists " + TABLE_NAME_Redundant_index + " (Id text, name text, age integer, gender integer)";
sqLiteDatabase.execSQL(sqlRedundantIndex);
String indexSql = "create index if not exists index_age on " + TABLE_NAME_Redundant_index + "(age);";
String indexSql2 = "create index if not exists index_age_name on " + TABLE_NAME_Redundant_index + "(age, name);";
String indexSql3 = "create index if not exists index_name_age on " + TABLE_NAME_Redundant_index + "(name,age);";
String indexSql4 = "create index if not exists index_id on " + TABLE_NAME_Redundant_index + "(Id);";
sqLiteDatabase.execSQL(indexSql);
sqLiteDatabase.execSQL(indexSql2);
sqLiteDatabase.execSQL(indexSql3);
sqLiteDatabase.execSQL(indexSql4);
String contact = "create table if not exists " + TABLE_NAME_CONTACT + " (Id integer primary key, name text, age integer, gender integer, status integer)";
sqLiteDatabase.execSQL(contact);
String contactIndex = "create index if not exists index_age_name_status on " + TABLE_NAME_CONTACT + "(age, name, status);";
String contactIndex2 = "create index if not exists index_name_age_status on " + TABLE_NAME_CONTACT + "(name, age, status);";
String contactStatusIndex = "create index if not exists index_status on " + TABLE_NAME_CONTACT + "(status);"; sqLiteDatabase.execSQL(contactIndex); sqLiteDatabase.execSQL(contactIndex2); sqLiteDatabase.execSQL(contactStatusIndex); }... }Copy the codeHere are a few new database tables:
TestTable: a normal table with an INTEGER ID as the primary key
TestTableAutoIncrement: id is used as the primary key and is an autoincrement attribute
TestTableWithoutRowid: The withoutRowid property is set
TestTableRedundantIndex: Sets multiple indexes
Contact: used for multi-condition query
The following is the SQL statement for the test:
public static String[] getTestSqlList() {
String[] list = new String[]{
"select * from testTable".//select *
"select name from testTable where age>10".//no index
"select name from testTableRedundantIndex where age&2 ! = 0".//not use index
"select name from testTableRedundantIndex where name like 'j%'".//not use index
"select name from testTableRedundantIndex where name = 'jack' and age > 20"."select testTable.name from testTable, testTableAutoIncrement where testTableAutoIncrement.age=testTable.age"."select Id from testTable where age = 10 union select Id from testTableRedundantIndex where age > 10".//union
"select name from testTable order by age".//use tmp tree
"select name from testTableRedundantIndex where gender=1 and age=5".//bigger index
"select name, case when age>=18 then 'Adult' else 'child' end LifeStage from testTableRedundantIndex where age > 20 order by age,name,gender"."select name,age,gender from testTableRedundantIndex where age > 10 and age < 20 or id between 30 and 40 or id = 1000 ORDER BY name,age,gender desc limit 10 offset 2;"."select * from (select * from testTable where age = 18 order by age limit 10) as tb where age = 18 " +
"UNION select m.* from testTable AS m, testTableRedundantIndex AS c where m.age = c.age;"."SELECT name FROM testTable WHERE name not LIKE 'rt%' OR name LIKE 'rc%' AND age > 20 GROUP BY name ORDER BY age;"."SELECT id AS id_alias FROM testTable AS test_alias WHERE id_alias = 1 or id = 2"."SELECT name FROM testTable WHERE id = (SELECT id FROM testTableRedundantIndex WHERE name = 'hello world')"."SELECT * FROM testTable where name = 'rc' UNION SELECT * FROM testTableWithoutRowid UNION SELECT * FROM testTableAutoIncrement"."SELECT name FROM testTable WHERE AGE GLOB '2*';"."SELECT DISTINCT name FROM testTable GROUP BY name HAVING count(name) < 2;"."SELECT name FROM contact WHERE status = 2;"."select rowid from contact where name = 'rr' or age > 12"."select t1.name ,t2.age from testTable as t1,testTableRedundantIndex as t2 where t1.id = t2.id and (t1.age=23 or t2.age=12);"."select t1.name ,t2.age from testTable as t1,testTableRedundantIndex as t2 where t1.id = t2.id and (t1.age=23 and t2.age=12);"."select name,age from contact where name like 'w%' and age > 12"."select name,age from contact where name >= 'rc' and age&2=1"."select name,age from contact where name = 'r' or age > 12 or status = 1"};return list;
}
Copy the codeThe final detection results are shown below:
A total of 31 suggestions or prompts are displayed. Click each suggestion or prompt to view detailed results. The detailed results page displays checked SQL statements, EXPLAIN Qurey plan results, optimization suggestions, and stack information on some details pages. The diagram below:
Based on optimization recommendations, we know that SQLiteLint recommends that we create a compound index of name and age.
SQLiteLint has a problem
-
TargetSdkVersion > = 28 hook failure
After targetsDK >=28, SqliteDebug is of Hide type and reflection cannot access it. It is recommended that you temporarily change targetSdkVersion to analyze the database, if possible, and then change it back.
-
Using JNI After Critical Get crashes after some apps are accessed. After investigation, it is found that there are virtual tables in the project. After initializing and starting SQLiteLint in the onCreate method of Application, it will be solved after adding the concerned database. Note that the database with virtual tables is not added here.
SQLiteLint source code analysis
From the start of the SQLiteLintPlugin,
SQLiteLintConfig config = initSQLiteLintConfig();
SQLiteLintPlugin sqLiteLintPlugin = new SQLiteLintPlugin(config);
builder.plugin(sqLiteLintPlugin);
Matrix.init(builder.build());
sqLiteLintPlugin.start();
private static SQLiteLintConfig initSQLiteLintConfig(a) {
try {
/** ** HOOK mode SQLiteLint will fetch all executed SQL statements and their elapsed time (by hooking sqlite3_profile) *@seeBut another model: SQLiteLint. SqlExecutionCallbackMode. CUSTOM_NOTIFY, you may need to call {@linkSQLiteLint#notifySqlExecution(String, String, int)} to notify * SQLiteLint of the executed SQL statement that needs to be analyzed and the time it takes *@see TestSQLiteLintActivity#doTest()
*/
return new SQLiteLintConfig(SQLiteLint.SqlExecutionCallbackMode.HOOK);
} catch (Throwable t) {
return newSQLiteLintConfig(SQLiteLint.SqlExecutionCallbackMode.HOOK); }}Copy the codeHere we initialize a SQLiteLintConfig and set the analysis callback mode for SQL execution statements to Hook mode.
public SQLiteLintConfig(SQLiteLint.SqlExecutionCallbackMode sqlExecutionCallbackMode) {
SQLiteLint.setSqlExecutionCallbackMode(sqlExecutionCallbackMode);
sConcernDbList = new ArrayList<>();
}
//com.tencent.sqlitelint.SQLiteLint#setSqlExecutionCallbackMode
public static void setSqlExecutionCallbackMode(SqlExecutionCallbackMode sqlExecutionCallbackMode){
if(sSqlExecutionCallbackMode ! =null) {
return;
}
sSqlExecutionCallbackMode = sqlExecutionCallbackMode;
if (sSqlExecutionCallbackMode == SqlExecutionCallbackMode.HOOK) {
// hook must called before open the databaseSQLite3ProfileHooker.hook(); }}Copy the codeSQLiteLintConfig constructor is mainly set up SqlExecutionCallbackMode, continue to be able to see, if it is a way of HOOK, call the SQLite3ProfileHooker. HOOK HOOK operation on (). Inside the hook method will call to the com. Tencent. Sqlitelint. Util. SQLite3ProfileHooker # doHook:
private static boolean doHook(a) {
The hookOpenSQLite3Profile method sets the DEBUG_SQL_TIME variable in SQLiteDebug to true
if(! hookOpenSQLite3Profile()) { SLog.i(TAG,"doHook hookOpenSQLite3Profile failed");
return false;
}
//nativeDoHook is a native method that hooks the native layer sqlite3_profile method to register the SQL execution result callback
return nativeDoHook();
}
Copy the codeNativeDoHook is a native method, so let’s see what the SQLiteLintPlugin does after calling the start method. com.tencent.sqlitelint.SQLiteLintPlugin#start
@Override
public void start(a) {
super.start();
if(! isSupported()) {return;
}
SQLiteLint.setReportDelegate(new IssueReportBehaviour.IReportDelegate() {
@Override
public void report(SQLiteLintIssue issue) {
if (issue == null) {
return;
}
/ / issue to reportreportMatrixIssue(issue); }}); List<SQLiteLintConfig.ConcernDb> concernDbList = mConfig.getConcernDbList();for (int i = 0; i < concernDbList.size(); i++) {
SQLiteLintConfig.ConcernDb concernDb = concernDbList.get(i);
String concernedDbPath = concernDb.getInstallEnv().getConcernedDbPath();
SQLiteLint.install(mContext, concernDb.getInstallEnv(), concernDb.getOptions());
// Set a whitelist for SQL statements not to be checked
SQLiteLint.setWhiteList(concernedDbPath, concernDb.getWhiteListXmlResId());
// Set check itemsSQLiteLint.enableCheckers(concernedDbPath, concernDb.getEnableCheckerList()); }}Copy the codeSQLiteLint Check items
- ExplainQueryPlanChecker: In the SQLite command line
Explain Query plan SQL statementsGet a high-level description of the policies or plans for SQLite features SQL queries, most importantlyReportable queries use database indexes. - AvoidSelectAllChecker: Used by SQL statements
select *check - WithoutRowIdBetterChecker: table creation in SQL statements
without rowidcheck - PreparedStatementBetterChecker: SQL PreparedStatement inspection
- RedundantIndexChecker: Redundant index check
Check the Matrix Android SQLiteLint for details
SQLiteLint install
//com.tencent.sqlitelint.SQLiteLint#install
public static void install(Context context, InstallEnv installEnv, Options options) {
assertinstallEnv ! =null;
assertsSqlExecutionCallbackMode ! =null
: "SqlExecutionCallbackMode is UNKNOWN! setSqlExecutionCallbackMode must be called before install";
options = (options == null)? Options.LAX : options; SQLiteLintAndroidCoreManager.INSTANCE.install(context, installEnv, options); }//com.tencent.sqlitelint.SQLiteLintAndroidCoreManager#install
public void install(Context context, SQLiteLint.InstallEnv installEnv, SQLiteLint.Options options) {
String concernedDbPath = installEnv.getConcernedDbPath();
if (mCoresMap.containsKey(concernedDbPath)) {
SLog.w(TAG, "install twice!! ignore");
return;
}
SQLiteLintAndroidCore core = new SQLiteLintAndroidCore(context, installEnv, options);
mCoresMap.put(concernedDbPath, core);
}
//com.tencent.sqlitelint.SQLiteLintAndroidCore#SQLiteLintAndroidCore
SQLiteLintAndroidCore(Context context, SQLiteLint.InstallEnv installEnv, SQLiteLint.Options options) {
mContext = context;
// initialize sqlitelintInternal. db to store the issues found by the check
SQLiteLintDbHelper.INSTANCE.initialize(context);
mConcernedDbPath = installEnv.getConcernedDbPath();
mSQLiteExecutionDelegate = installEnv.getSQLiteExecutionDelegate();
if (SQLiteLint.getSqlExecutionCallbackMode() == SQLiteLint.SqlExecutionCallbackMode.HOOK) {
//hook sqlite3_profile api
SQLite3ProfileHooker.hook();
}
// Open the check, we will continue to analyze
SQLiteLintNativeBridge.nativeInstall(mConcernedDbPath);
// Sets the behavior after an issue is found
mBehaviors = new ArrayList<>();
/*PersistenceBehaviour is a default pre-behaviour */
mBehaviors.add(new PersistenceBehaviour());
if (options.isAlertBehaviourEnable()) {
mBehaviors.add(new IssueAlertBehaviour(context, mConcernedDbPath));
}
if (options.isReportBehaviourEnable()) {
mBehaviors.add(newIssueReportBehaviour(SQLiteLint.sReportDelegate)); }}Copy the codeThe nativeDoHook mentioned above is as follows:
JNIEXPORT jboolean JNICALL Java_com_tencent_sqlitelint_util_SQLite3ProfileHooker_nativeDoHook(JNIEnv *env, jobject /* this */) {
LOGI("SQLiteLintHooker_nativeDoHook");
if(! kInitSuc) { LOGW("SQLiteLintHooker_nativeDoHook kInitSuc failed");
return false;
}
loaded_soinfo* soinfo = elfhook_open("libandroid_runtime.so");
if(! soinfo) { LOGW("Failure to open libandroid_runtime.so");
return false;
}
if(! elfhook_replace(soinfo,"c", (void*)hooked_sqlite3_profile, (void**)&original_sqlite3_profile)) {
LOGW("Failure to hook sqlite3_profile");
elfhook_close(soinfo);
soinfo = nullptr;
return false;
}
elfhook_close(soinfo);
soinfo = nullptr;
kStop = false;
return true;
}
Copy the codeAndroid_runte. so sqlite3_profile (GOT) Hook (PLT). Elfhook_replace (GOT) Hook (sqlite3_profile)
Why hook the sqlite3_profile method to meet our requirements? After each SQL statement is complete, the callback function registered by SQlite3_profile is called. The configuration file callback contains the original statement text and an estimate of the wall clock time for how long the statement has been running.
Look again at the logic in hooked_SQlite3_profile:
void* hooked_sqlite3_profile(sqlite3* db, void(*xProfile)(void*, const char*, sqlite_uint64), void* p) {
LOGI("hooked_sqlite3_profile call");
return original_sqlite3_profile(db, SQLiteLintSqlite3ProfileCallback, p);
}
Copy the codeHooked_sqlite3_profile do is very simple, use the original sqlite3_profile method and set up a SQLiteLintSqlite3ProfileCallback callback, Because the SQLiteLintSqlite3ProfileCallback we can get the result of the sqlite profile.
void Java_com_tencent_sqlitelint_SQLiteLintNativeBridge_nativeNotifySqlExecute(JNIEnv *env, jobject, jstring dbPath , jstring sql, jlong executeTime, jstring extInfo) {
char *filename = jstringToChars(env, dbPath);
char *ext_info = jstringToChars(env, extInfo);
char *jsql = jstringToChars(env, sql);
NotifySqlExecution(filename, jsql, executeTime, ext_info);
free(jsql);
free(ext_info);
free(filename);
}
Copy the codeSqlExecutionCallbackMode is a Hook and a NotifySqlExecution. After the SQL statement is executed, the Hook method will actively call NotifySqlExecution. Pass in the executed SQL statement that needs to be analyzed and its time parameter, so we don’t need to manually call SQLiteLint#notifySqlExecution(String, String, int) to do so.
SQLiteLintNativeBridge. NativeInstall nativeInstall (mConcernedDbPath) calls a native method
void Java_com_tencent_sqlitelint_SQLiteLintNativeBridge_nativeInstall(JNIEnv *env, jobject, jstring name) {
char *filename = jstringToChars(env,name);
InstallSQLiteLint(filename, OnIssuePublish);
free(filename);
SetSqlExecutionDelegate(SqliteLintExecSql);
}
//sqlitelint::InstallSQLiteLint
void InstallSQLiteLint(const char* db_path, OnPublishIssueCallback issue_callback) {
LintManager::Get()->Install(db_path, issue_callback);
}
//LintManager::Install
void LintManager::Install(const char* db_path, OnPublishIssueCallback issued_callback) {
sInfo("LintManager::Install dbPath:%s", db_path);
std::unique_lock<std::mutex> lock(lints_mutex_);
std: :map<const std: :string, Lint*>::iterator it = lints_.find(db_path);
if(it ! = lints_.end()) { lock.unlock(); sWarn("Install already installed; dbPath: %s", db_path);
return;
}
Lint* lint = new Lint(db_path, issued_callback);
lints_.insert(std::pair<const std: :string, Lint*>(db_path, lint));
lock.unlock();
}
Copy the codeLintManager::Install creates a new Lint that starts a thread in its constructor to execute the check task in an infinite loop. TakeSqlInfo continuously obtains the execution result information from the executed SQL statement. When no SQL statement is executed, TakeSqlInfo enters the wait state. The system calls sqlite3_profile, and in the sqlite3_profile callback we execute the Lint::NotifySqlExecution method, which executes the notify method to re-enter the running state and start the SQL statement examination using the available checker.
void Lint::Check() {
init_check_thread_ = new std::thread(&Lint::InitCheck, this);
std: :vector<Issue>* published_issues = new std: :vector<Issue>;
std: :unique_ptr<SqlInfo> sql_info;
SqlInfo simple_sql_info;
while (true) {
int ret = TakeSqlInfo(sql_info);
if(ret ! =0) {
sError("check exit");
break;
}
// Count SQL statements
env_.IncSqlCnt();
// SQL statement preprocessing, remove excess whitespace, SQL statement converted to lowercase
PreProcessSqlString(sql_info->sql_);
sDebug("Lint::Check checked cnt=%d", env_.GetSqlCnt());
/ / support to check, only support the select, update, delete, insert, replace type of SQL statement
if(! IsSqlSupportCheck(sql_info->sql_)) { sDebug("Lint::Check Sql not support");
env_.AddToSqlHistory(*sql_info);
sql_info = nullptr;
continue;
}
// Preprocessing, according to the type of SQL statement to do different processing
if(! PreProcessSqlInfo(sql_info.get())) { sWarn("Lint::Check PreProcessSqlInfo failed");
env_.AddToSqlHistory(*sql_info);
sql_info = nullptr;
continue;
}
sql_info->CopyWithoutParse(simple_sql_info);
env_.AddToSqlHistory(simple_sql_info);
published_issues->clear();
// Various inspectors check SQL statements
ScheduleCheckers(CheckScene::kSample, *sql_info, published_issues);
const std: :string& wildcard_sql = sql_info->wildcard_sql_.empty() ? sql_info->sql_ : sql_info->wildcard_sql_;
bool checked = false;
if(! checked_sql_cache_.Get(wildcard_sql, checked)) { ScheduleCheckers(CheckScene::kUncheckedSql, *sql_info, published_issues); checked_sql_cache_.Put(wildcard_sql,true);
} else {
sVerbose("Lint::Check() already checked recently");
}
if(! published_issues->empty()) { sInfo("New check some diagnosis out! , sql=%s", sql_info->sql_.c_str());
if (issued_callback_) {
/ / issue to report
issued_callback_(env_.GetDbPath().c_str(), *published_issues);
}
}
sql_info = nullptr;
env_.CheckReleaseHistory();
}
sError("check break");
delete published_issues;
}
Copy the codeAt this point, the SQLiteLint process is basically colluded. Use two diagrams to illustrate the process:
conclusion
Through Matrix SQLiteLint, we can check SQL statements in the development, test or gray stage, you can write more efficient SQL statements with better performance, especially for the database use and optimization of developers who do not have too much experience is very useful. Of course, SQLiteLint is not perfect, and SQLiteLint detection is only a recommendation. In particular, index detection may have false positives, and in some cases, index failure cannot be avoided. Therefore, whether to change it depends on the actual situation.
Under what circumstances does an optimized database index fail