Database Synchronization of Options
- Deniz Oğuz
For Single Select++, Multi Select++ and Radio Buttons++ custom fields you can configure database synchronization of options. If you configure database synchronization option value and descriptions will be periodically imported from a selected database table or SQL select result.
It has been tested with following databases, it may also work for other databases if you add correct drivers;
- PostgreSQL (driver is bundled)
- MySQL (MariaDB driver is bundled and should work for most MySQL versions)
- SQL Server (driver is bundled)
- Oracle (you need to add driver manually)
Database configuration can be done by clicking on "Edit Synchronization Options from Database" link on the Custom Field Configuration Page. You can access this page by selecting "Configure" link from the menu corresponding to custom field. Note that there may be multiple field configurations applicable for different contexts, so be sure to edit the correct one.
Also on the field configuration screen, you will be able to check what is the last time synchronization is performed and how custom fields options are modified.
You will need to configure database connection information, a db table or sql select and finally map result columns to option field's value and description columns. Since description column is optional, you don't need to bind a result column to it.
Connection Information
You can enter connection information using either a JDBC URL or by explicitly specifying necessary fields. If you check below screenshots, on the left you see an oracle database connection specified using URL, and on the right you see a PostgreSQL database specified using fields. In either modes you need to know hostname, port and database name of your database.
Datasource Support
Since version 1.3.0 of the add-on, you can define datasources from add-on settings. Datasource is used as a reusable connection pool inside the add-on. For example 'Database Lookup++" field uses datasource for database connection, it doesn't require you to configure connection information. Obviously Database Synchronization of Options can also use datasource, but they are not updated yet. In the next version you won't need to specify connection information for synchronization, selecting an already defined datasource will be enough.
When you click on next button the connection information will be verified and if there is any error it will be shown for you to fix before configuring table or SQL select. If database connection is successful you will proceed to second step of configuration. In this step you can either directly enter an SQL select statement or you can select a table name. Although it requires you to manually create a select query you can create much more complex queries, you can join tables, filter for criteria none of which is available in table selection mode. In table selection mode all rows of the table are automatically fetched.
When you press the 'Next' button, entered SQL or table selection will be verified and if there is any errors, you will need to fix it before proceeding to next step. If everything is correct, you will proceed to next step to configure mapping between result columns and option field's value and description and additionally synchronization interval and merging strategy.
'Value' field corresponds to value field of option. It is the string shown to user when they are performing a selection on single/multi select or radio button custom fields. 'Description' field maps to description of the corresponding option. It is the help text shown to user when they hover on the option. You can leave description mapping empty, if you want. Depending on how often your database is updated you can select a synchronization interval between 1 minute and 1 day. Merge strategy can be one of the followings:
- Add/Update: The options which does not exist in the custom field will be imported from the database will be added. Options that are already exist will import corresponding description value from the database.
- Add/Update/Disable: In addition to Add/Update strategy any option which doesn't exist in the database will be disabled. A disabled option will not be available for new issues or while editing issues. Old issues will still continue to have this option, if any. Note that even there is no issue which uses the value, option will not be disabled.
- Add/Update/Disable/Delete: In addition to Add/Update/Disable any option which doesn't exist in the database and not used by any issue will be deleted. If an option is used by issues it will not be deleted, it will be only disabled.
When you press the 'Save & Synchronize' button, configuration will be saved and an initial database synchronization will be performed. If there is any change performed you will be notified about it. Depending on the number of rows exist in your database, this operation may take a while although it should not exceed a few seconds in most situations.