Bug report #15082

SpatiaLite index issue

Added by R. R. about 4 years ago. Updated over 1 year ago.

Status:Closed
Priority:Normal
Assignee:-
Category:DB Manager
Affected QGIS version:master Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:end of life
Crashes QGIS or corrupts data:No Copied to github as #:23024

Description

Spatial indexes are not listed in 'Indexes' tab and the 'DB Manager' crashes when adding a index (see screencast).

15082.mp4 - screencast (1.34 MB) R. R., 2016-06-19 11:21 AM

History

#1 Updated by R. R. about 4 years ago

#2 Updated by Jukka Rahkonen about 4 years ago

The issue is real but how to improve the current situation needs some thinking because spatial index in SpatiaLite/GeoPackage is not an index but a table with some well known triggers.

The list of true indexes from SQLite can be obtained with a PRAGMA command

PRAGMA index_list('table_name')

I suppose that the Indexes tab in Layer properties makes exactly that request.

From SpatiaLite databases created with recent versions the existence of the spatial index is supposed to be checked from the geometry columns table which has a column "spatial_index_enabled".

SELECT ROWID, "f_table_name", "f_geometry_column", "type", "coord_dimension", "srid", "spatial_index_enabled"
FROM "geometry_columns"
ORDER BY ROWID

If user is pressing "Create spatial index" button and spatial index already exists then the application should perhaps make a question:
"Table xxx has already a spatial index. Do you want to recover it?" If user says "Yes" then RecoverSpatialIndex function would be performed.

For the GeoPackage case I suppose that if spatial index exists or not should be checked with the test that is included in the standard, on page 75:

@A.3.1.3 Spatial Indexes
A.3.1.3.1 Data
A.3.1.3.1.1 Spatial Indexes Implementation

...snip...

3) For each row table_name, column_name from step 1
a. SELECT sql FROM sqlite_master WHERE tbl_name = ‘rtree_’ || result_set_table_name || ‘_’ || result_set_column_name
b. Not testable if result set is empty
c. Fail if returned sql != ‘CREATE VIRTUAL TABLE rtree_’ ’ || result_set_table_name || ‘_’ || result_set_column_name || USING rtree(id, minx, maxx, miny, maxy)
d. SELECT sql FROM sqlite_master WHERE type = ‘trigger’ AND tname = ‘rtree_’ || result_set_table_name || ‘_’ || result_set_column_name || ‘_insert’
e. Fail if returned sql != result of populating insert triggers template in Annex L using result_set_table_name for <t> and result_set_column_name for <c>
f. SELECT sql FROM sqlite_master WHERE type = ‘trigger’ AND name LIKE ‘rtree_’ || result_set_table_name || ‘_’ || result_set_column_name || ‘_update%’
g. Fail if returned sql != result of populating 4 update triggers templates in Annex L using result_set_table_name for <t> and result_set_column_name for <c>
h. SELECT sql FROM sqlite_master WHERE type=’trigger’ AND name = ‘rtree_’ || result_set_table_name || ‘_’ || result_set_column_name || ‘_delete’
i. Fail if returned sql != result of populating delete trigger template in Annex L using result_set_table_name for <t> and result_set_column_name for <c>
j. Log pass otherwise
4) Pass if logged pass and no [email protected]

#3 Updated by Giovanni Manghi over 3 years ago

  • Easy fix? set to No
  • Regression? set to No

#4 Updated by Giovanni Manghi over 1 year ago

  • Resolution set to end of life
  • Status changed from Open to Closed

Also available in: Atom PDF