How to add/edit/remove a table/column

This page  demonstrate how to add or edit a column/table and developing the necessary objects for facilitate the change. The page use a example to demonstrate how create new column date_edited in the table core.client and show how to wire everything up.

Using MyBatis Migrations to migrate the database

1. Install and configure MyBatis Migrations. Its the tool used create the migrations. Refer to the page Creating Postgres Database on instructions on how to install MyBatis Migrations.
2.  Open terminal and navigate to opensrp directory
3. Run the below command to create a new script that will be used to migrate the database 
     migrate new "client table add date_edited" --path=assets/migration
4. Confirm this a new sql file has been created in the directory opensrp/assets/migrations/scripts. In my case it created the file 20180416141726_client_table_add_date_edited.sql. The first part is timestamp and rest depending on the arguments passed
5.    Write the DDL to add the column in the sql file below the section labelled

     -- // client table add date_edited

        -- Migration SQL that makes the change goes here.
6. Write the DDL to undo the change in the section below


   -- //@UNDO
-- SQL to undo the change goes here.

   


7.   Save the sql file. The sql file should  look like the below

   20180416141726_client_table_add_date_edited.sql

   -- // client table add date_edited
   -- Migration SQL that makes the change goes here.
   
   	ALTER TABLE core.client ADD COLUMN date_edited timestamp DEFAULT CURRENT_TIMESTAMP;
   
   -- //@UNDO
   -- SQL to undo the change goes here.
   	ALTER TABLE core.client DROP COLUMN date_edited;

8. Execute the migration up command this will migrate the database and apply the latest script. Migrate uses a changelog table to track the version of the database and will only apply scripts later than the version the database is at. Below is the migrate command to migrate the test opensrp_database
   

   migrate up  --path=assets/migrations --env=test 

9. Confirm on the actual database table that the column has been added.
10. You can test the undo by migrating down. This will rollback the last database migration .e.g
    migrate down  --path=assets/migrations --env=test 

Using MyBatis Generator to generate the models, mappers and examples

1. Install MyBatis Generator to generate the models, mapper and examples for the domain object.  Use the link http://www.mybatis.org/generator/running/runningWithEclipse.html for installation on Eclipse. You can run mybatis using ther tools e.g maven, ant, command etc but that is not covered in this documentation, refer to http://www.mybatis.org/generator/running/running.html on how to use these other tools. 
   
   
2. In the file opensrp/assets/migrations/generator/generatorConfig.xml make sure the table that you changed is active(uncommented)
3. Right click opensrp/assets/migrations/generator/generatorConfig.xml and select Run As >> Run MyBatis Generator
4. This will generate the amended postgres models, mappers (interface and xml) and examples.
5. You can now use classes to work with the new fields using the generated artifacts
6. You can extended custom funtionality that is usually not overwritten when running the MyBatis generator. The custom mappers interface are in the package org.opensrp.repository.postgres.mapper.custom and mappers xml are in the package org.opensrp.repository.postgres.mapper.custom.xml

7. Use the mapper interfaces to execute queries on the database