Change quartz and reporting backend to mysql

[maimoonak]

OpenSRP team frequently questions about the complexity of app w.r.t. usage of multiple databases. Postgres DBMS is used by reporting module and motechquartz (scheduler).

Changing motechquartz and reporting db to be able to run on mysql would remove this one level of complexity.

[maimoonak]

Postgres and MySQL are two completely different DBMSs and both not only have different data types but also different way of handling databases.

1. Postgres have VARCHAR which doesnot need to have any max length but mysql can not work without any length defined with VARCHAR. MySql TEXT could be used as an alternative to postgres VARCHAR but that decreases performance. Rightnow all VARCHARS of postgres are made equivalent to VARCHAR(255). Although this has been double checked that all VARCHARs in scripts are not likely to exceed 255 characters

2. Postgres has binary datatype BYTEA which is made equivalent to BLOB of mysql. Googling shows that BYTEA and BLOB are not completely equivalent but this is used in quartz and the script shipped with quartz for mysql has BLOB in all places where postgres uses BYTEA

3. All postgres SERIAL and INTEGER has been replaced with INT(11) WITH AUTO INCREMENT where need

4. There was a Function in /opensrp-reporting/src/main/resources/migrations/anmReport/V1_0_11__Migrating_date_in_ANM_Report.sql and also in serviceProvide scripts. This function is not needed at all for setting up and the required functionality was to remove foreign key relationship of date_ column in table which is done in scripts for mysql. Please review the changes I have done if have expertise in plsql. It would helps us in identifying any missing piece.

5. Mysql has no way of combining multiple schemas under a database, hence for MySQL anm_report and report schemas are independent databases and there is no database drishti/opensrp as umbrella. For Postgres it would be exactly like before (I have renamed drisht to opensrp).

6. MySQL connector is added along with postgres connector to reduce steps changing b/w DBs in reporting and web but in documentation one of the connector is recommended to remove so that even accidentally code doesnot use unwanted backend.

7. scripts for both are named exactly similar except the name of the DBMS. This helps getting the scripts dynamically and automatically with user changing anything in code or applicationContext files but just by changing a property in maven.properties file at build time.

[maimoonak]

Documentation on switching between DBMSs is here
https://smartregister.atlassian.net/wiki/display/Documentation/Migrating+from+MySQL+to+Postgres+and+vice+versa

[julkarnain]

We should have both databases system so that client can use as their choice. Because Bangladesh client are expecting PostgreSQL database in their system. Even their existing systems those are in mysql have migrated to postgresql. They are expecting common database system. So, we are also trying to run OpenMRS on postgresql

[maimoonak]

@julkarnain please read the documentation, and email I sent on Saturday. It lists down all the details.

[sohelsarder]

Good works, it will add more flexibility in terms of usage database system with opensrp. Now you have two options to choice your DB (postgres & couch) or (mysql & couch). But I am not sure which combination will remain default with opensrp. If it is (mysql & couch) then I think in setup and installation guideline these need to mention and need to modify them as well.

[maimoonak]

@sohelsarder rightnow the pull request has mysql by default. We can modify it to be posgres by default.

[maimoonak]

The code merged successfully.

Since @sohelsarder and @julkarnain wanted postgres as default and no one else provided any feedback on code or functionality, the default DB for opensrp is now changed to postgres which could be easily changed to mysql following the documentation https://smartregister.atlassian.net/wiki/display/Documentation/Migrating+from+MySQL+to+Postgres+and+vice+versa