Document Actions
Database Development
Database development standards and recommendations
- 1 Database Standards
- 2 How to Properly Script Database Changes
- 3 Indexes
- 4 Older databases (December 2006 or older)
- 5 Database Upgrade
- 6 Additional Database Resources
Database Standards
Write portable SQL - much of our SQL is already portable between MySQL and Mayfly (see In Memory Database). Although production use of other databases (postgres, for example) is not a high priority, generally portable SQL is also simpler and easier to understand and maintain than database-specific tricks.
Please make sure you are familiar with the end user conditions.
How to Properly Script Database Changes
In summary, here is how a developer should go about developing a change which involves changes to the database schema or preloaded data:
- Edit Database Version Persistence to set the APPLICATION_VERSION to the next higher value
- Edit latest-schema.sql (to make schema changes) and latest-data.sql (to make default data changes)
- if your changes to the database do not include adding any new rows to the LOOKUP_VALUE table then:
- Add upgrade_to_version.sql file. This script should include all the changes that have been made to the database schema in latest-schema.sql and all the changes to data in latest-data.sql. This script should migrate any existing data if appropriate (for example, if you are combining two columns into one and want people's old data to be moved over). Use mifos/sql/upgrade_to_101.sql as an example.
- if your changes to the database do include adding any new rows to the LOOKUP_VALUE table (or involve conditional logic or operations that don't lend themselves to straight SQL) then:
- Add a java based upgrade that will dynamically add rows to the LOOKUP_VALUE table that will not conflict with data that has been added in live database. Please refer to the the wiki page How To Do Java Based Database Upgrades, for detailed instructions.
- Edit mifos/sql/mifosdroptables.sql to include new tables.
- Run LatestTestAfterCheckpoint (this is part of the normal "ant run_test"). This will detect problems such as doing one thing in latest-*.sql and another in upgrade_to_version.sql.
- Stage the database upgrade by downloading a database snapshot, loading it into a local database instance and deploying Mifos against this database.
DO NOT MODIFY mifosdbcreationscript.sql. Ever. This will compromise database integrity by bypassing unit tests used to verify scripted upgrading.
Indexes
As of 18 Dec 2006, the sql/Index.sql file contains indexes but is not part of the upgrade scheme described here. This is likely to change.
Older databases (December 2006 or older)
If the database does not have a DATABASE_VERSION table, it is from before the mechanisms described above existed. You will therefore need to figure out which of the following upgrade scripts to apply to get you to a DATABASE_VERSION of 100 (which is the first version with a DATABASE_VERSION table).
From June 2006 (or so) until about October 2006, the practice was to modify mifosdbcreationscript.sql and mifosmasterdata.sql with each checkin. Apply some of the following upgrade scripts, in order, if your database was created during this time period:
Iteration5-DBScripts02062006.sql Iteration6-DBScripts16062006.sql Iteration7-DBScripts30062006.sql Iteration8-DBScripts14072006.sql Iteration9-DBScripts28072006.sql Iteration10-DBScripts11082006.sql Iteration11-DBScripts28082006.sql Iteration12-DBScripts15092006.sql Iteration12-Intermediate-DBScripts11092006.sql
From October 2006 to December 2006 the practice was to check in the upgrade scripts and not modify mifosdbcreationscript.sql and mifosmasterdata.sql. Apply some of the following upgrade scripts, in order, if your database was created during this time period:
Iteration13-DBScripts25092006.sql Iteration14-DDL-DBScripts10102006.sql Iteration14-DML-DBScripts10102006.sql Iteration15-DBScripts20061012.sql Iteration15-DDL-DBScripts24102006.sql add-version.sql
At some point in 2006 ant build_db started also creating the Reports Mini Portal tables (for example, report_jasper_map). If you lack these tables, run:
rmpdbcreationscript.sql rmpmasterdata.sql
From some time in 2006 until May 2007, the Index.sql file existed in mifos, but was not automatically run by anything. If you lack the indexes listed in Index.sql, run it manually. On 16 May 2007 (database version 116), latest-schema.sql started adding those indexes. An upgrade will not add them for you (the idea is that many sites with older databases will have already manually run Index.sql).
Database Upgrade
To create a Mifos database, the ant build_db (or ant build_test_db for tests) target runs the files latest-schema.sql and latest-data.sql which represent the current version of the database.
If APPLICATION_VERSION is greater than the Database version
To upgrade from one version of the database to the next, there are scripts with names like upgrade_to_version.sql. For example, the first one is upgrade_to_101.sql. These scripts should end by updating the DATABASE_VERSION table.
The application knows what version of the database it expects (this is the constant APPLICATION_VERSION in class DatabaseVersionPersistence).
Upon startup, the application checks the database version from the DATABASE_VERSION table. If the DATABASE_VERSION is less than the APPLICATION_VERSION, then the application will apply the relevant upgrade_to_version.sql scripts, one at a time, until it reaches the correct APPLICATION_VERSION.
- A unit test in LatestTest can verify that the result of applying the upgrades is the same as just loading latest-*.sql.
If the Database version is greater than APPLICATION_VERSION
Deploy a more recent Mifos web application.
Additional Database Resources
Database Schema Diagram
Beginning with database version 154, SchemaSpy database diagrams are generated automatically.
Mifos Version 1.1/MySQL 5.0
http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html
Version 1.0 /MySQL Version 4.1
MySQL 4.1 Tips: http://dev.mysql.com/doc/refman/4.1/en/innodb-tuning.html