Issues faced with system_role table in the database

Community Development
,
1

Hello Folks
i have encounter unusual behaviors while testing the role package in the database and these are as follows;

  1. Padded Values in Database for system_role
    When inserting strings like clinlims.system_role.name, they were being stored as <results[ ]>(with spaces) instead of < results > . Now this comes about When you insert a value shorter than n, it pads the remaining space with spaces due to the fact the name field is stored as char(30) and you can see below:

CREATE TABLE clinlims.system_role ( id numeric(10,0) NOT NULL, name character (30) NOT NULL, description character varying(80), is_grouping_role boolean DEFAULT false, grouping_parent numeric(10,0), display_key character varying(60), active boolean DEFAULT true, editable boolean DEFAULT false
);

now to fix this we can add varying to the name character varying(30) NOT NULL but that won’t be consistant with the liqubase file which causes liquibase exceptions causing Liquibase Migration Failure so it this means the type changed in the OpenELIS-Global.sql has to be reflected in the liquibase file concerning this database table which i can’t quite locate at the moment.

  1. DBUnit Boolean Mapping Error
    DBUnit tests failed while processing the system_role table, especially the active column. the DBUnit passes integers active="1" and active="0" in the test dataset, but the column is of type BOOLEAN as shown in the code block above. Cc. add Role Integration test by josephbate · Pull Request #1925 · DIGI-UW/OpenELIS-Global-2 · GitHub
    as you can see in the role.xmli have opted for intergers for this column though i still think in it might be a configuration issue, haven’t quite known where exactly the problem is.

  2. according to the code block above the column grouping_parent is created but the dbunit doesn’t seem to find the column, and throws a org.dbunit.DatabaseUnitException: Exception processing table name='system_role' this makes it hard to test RoleService.getReferencingRoles here also ive failed to find where exactly this occurscoz according to the OpenELIS-Global.sql it checks out fine.

still snooping around trying to find a cause and also possible solutions but any dea on this or possible solutions or insights are welcome

Cc. @Moses_Mutesasira @Herbert @mherman22 @caseyi

2

1.we should change the column type from character(30) to varchar(30) because it is the syntax in database
2. Here the column active type is Boolean so for Boolean there is only true or false but not integers like 0 or 1
3. Here maybe the table column is missing or even null it shows error here
Thank you