Roundup Tracker - Issues

Issue 2551245

classification
Add index creation hint to schema definition
Type: resource usage Severity: normal
Components: Database, API Versions: devel
process
Status: new
:
: : rouilj, schlatterbeck
Priority: normal : Effort-Medium

Created on 2022-11-26 09:47 by schlatterbeck, last changed 2023-11-06 03:39 by rouilj.

Messages
msg7687 Author: [hidden] (schlatterbeck) Date: 2022-11-26 09:47
Currently roundup decides to produce indexes automatically. In some cases it makes sense that the user can specify indexes for some tables.

Proposed is the following parameter to the property definition that requests an index (proposed by John P. Rouillard):

 propname = String(addindex=True)

e.g.
msg = FileClass(db, "msg", ..., messageid=String(addindex=True), ...)

The implementation could check if all indexes exist and create missing ones if needed. Note that we do not want to remove unexpected indexes: The user might have added additional indexes by hand.
msg7688 Author: [hidden] (rouilj) Date: 2022-11-26 20:56
As a workaround a call can be made in initial_data.py:

     if hasattr (db, 'sql'):
        db.sql ('create index _issue_status_idx on _issue (_status);')
        ...

but this only works when the tracker is initialized. If any column is dropped
it will wipe out the index. For a single column this isn't an issue (there is
nothing to index). But it is unknown what happens to a multi-column index that
references a missing column.

Also the proposed addition only handle a single column index.
Multi-column indexes which might be needed for some use-cases (e.g to speed
up searches).
 
Ideally we would hook custom indexing into the schema update/create method.

However I wonder if we can provide a hook to run on schema change to
make this easier.

import roundup.backends.rdbms_common

class Database:

   create_supplimentary_indexes(classname, added_props[], ...):
       admin does his sql here.
       self.sql( <insert sql statement here> )

   drop_supplimentary_indexes(classname, dropped_props[], ...):
       admin does his sql here.
       self.sql( <insert sql statement here> )

and they gets called as part of update_class() and
create_class_table_indicies()/drop_class_table_indicies()?
History
Date User Action Args
2023-11-06 03:39:05rouiljsettitle: Add index creation to schema definition -> Add index creation hint to schema definition
2022-11-26 20:56:24rouiljsetkeywords: + Effort-Medium
messages: + msg7688
2022-11-26 09:47:38schlatterbeckcreate