Occ db:add-missing-indices alternative with sqlite3 command for SQLite DB

ℹ️ Support
,
1

This enquiry has less of a practical purpose. I use MariaDB in addition to SQLite with other Nextclouds and I realise that this approach may not make sense for 99% of all users of this forum.

I would like to fix the following warnings from my SQLite database in my test Nextcloud instance. Using occ is not an option and importantly, it should not be an option at all.

I open my SQLite database with sqlite3. I would like to insert the missing indices. But can someone tell me how to get the values in the brackets for the commands? Or are these already indirectly contained in the error messages? E.g. at dav_shares_resourceid_type then (resourceid, type). If not where can i get the parameters or where does occ get them.

Another example:

sqlite owncloud.db
CREATE INDEX systag_objecttype ON oc_systemtag_object_mapping (objecttype);
.exit

Detected some missing optional indices. Occasionally new indices are added (by Nextcloud or installed applications) to improve database performance. Adding indices can sometimes take awhile and temporarily hurt performance so this is not done automatically during upgrades. Once the indices are added, queries to those tables should be faster. Use the command occ db:add-missing-indices to add them. Missing indices: “dav_shares_resourceid_type” in table “dav_shares”, “dav_shares_resourceid_access” in table “dav_shares”, “oc_npushhash_di” in table “notifications_pushhash”, “tables_columns_t_id” in table “tables_columns”, “tables_row_sleeves_t_id” in table “tables_row_sleeves”, “tables_tables_ownership” in table “tables_tables”, “fs_name_hash” in table “filecache”, “schedulobj_lastmodified_idx” in table “schedulingobjects”, “systag_by_objectid” in table “systemtag_object_mapping”, “systag_objecttype” in table “systemtag_object_mapping”.`

2

I did a quick search from the Nextcloud web root directory and found these matches. I hope it will you finding what you are looking for.

# grep -R dav_shares_resourceid_type
apps/dav/lib/Migration/Version1004Date20170825134824.php:        $table->addIndex(['resourceid', 'type'], 'dav_shares_resourceid_type');
apps/dav/lib/Listener/AddMissingIndicesListener.php:             'dav_shares_resourceid_type',