Lookup Value Issues

Owned by Former user (Deleted)
Last updated: Sept 07, 2010Version comment

= Lookup Value Issues = (See also Lookup Value Overwriting which describes the same issue)

== Proposed solution: == Implementing java based upgrades scripts (instead of SQL based upgrade scripts) will allow new lookup values to be created without specifying absolute ids. This will allow new lookup values to be safely created dynamically from the UI without conflicting with values added in upgrade scripts.

The Custom Attribute List (aka Lookup Value) UI will allow new attributes to be added to existing lists.

Each item on the list is represented by a row in the LOOKUP_VALUE table and a row in the LOOKUP_VALUE_LOCALE table which references a LOOKUP_ID in the LOOKUP_VALUE table. Adding a value to these tables means creating a new row in each table with a new id. If this were done in the simplest way, we could imagine that the next available ID would be used.

Unfortunately, all the LOOKUP_VALUE and LOOKUP_VALUE_LOCALE rows in the latest-data.sql file are created using absolute id numbers. This means that using the current scheme, if a Mifos installation added a value to a customized attribute list, and then some other value were added in latest-data.sql and an update_to_xxx.sql (using an absolute id number), then the value added in the Mifos installation would be overwritten. This is what has happened in the Grameen Koota case.

So something needs to change in order to make this work.

Some possible solutions:

  • hack around with auto_increment such as setting the auto_increment initial value to a larger number like 10000 so that MFI additions will not collide with changes to the default configuration
  • encapsulate lookup value creation in a stored procedure
  • try rearranging the inserts so that IDs can be found using select statements rather than using absolute ID values
* move all lookup value definitions to an external file with no id reference that is loaded once with support for later changes and additions

One way or anther, it would be worthwhile to move away from absolute id references-- though doing so would be a big job.

– Main.vanmh - 01 Jun 2007