How to use MySQL to read xml dataset for integration test instead of H2 database

Development
,
1

Hi there, we are developing a reporting module that uses specific MySQL functions in queries. When we run the queries in H2 database for integration test it complains the syntax.

I tried to replace the functions for equivalent ones so that both H2 and MySQL can understand. But when I do that the results are not the same in MySQL.

There are some functions that I changed to use in both databases:

    • date_add() replaced with timestampadd()
    • datediff() replaced with timestampdiff()

As the module uses MySQL in production I would like to know how to configure project to use MySQL + DbUnit + XML Dataset. May be there are other solutions to do that, If you guys know please let me know

1 Like
2

In your test, override the useInMemoryDatabase() method and return false. Then supply the connection information as system properties. This will give you some context: https://github.com/openmrs/openmrs-core/blob/master/api/src/test/java/org/openmrs/test/BaseContextSensitiveTest.java#L312-L349

3

Thank you for your reply. Does this configuration require a physical instance of MySQL database in the machine?

4

Depends on your databaseUrl value

5

ok I understand. let me try it

6

by this it means mysql can not be used in memory for unit tests?

7

MySQL is not primarily an in memory database. https://en.wikipedia.org/wiki/List_of_in-memory_databases

8

the idea is to run those SQL functions in database server who understands the syntax… or use mysql in memory.

Tell me something, how do I load xml dataset in MySQL physical instance for tests using dbunit ? May be that is the solution

9

Did you try doing what i advised above? It should work for MySQL.

10

Yes sure. But when do that I get the below error:

https://pastebin.com/H7dpG3dY

11

Looks like there is required privilege missing to stick the data in the database.

Bellow my initial dataset:

<!-- user -->
<users user_id="1" system_id="admin" creator="1"
	date_created="2008-10-13 11:32:52" person_id="1" retired="0"
	uuid="1c3db49d-440a-11e6-a65c-00e04c680037" />

<!-- role -->
<role role="System Developer"
	description="Developers of the OpenMRS .. have additional access to change fundamental structure of the database model."
	uuid="8d94f852-c2cc-11de-8d13-0010c6dffd0f" />

<role role="Authenticated CDaccess"
	description="For Concept Dictionary Synchronization only"
	uuid="e2f0a906-1d5f-11e0-b929-000c29ad1d07" />
	
	<role role="Anonymous"
	description="Privileges for non-authenticated users."
	uuid="774b2af3-6437-4e5a-a310-547554c7c65c" />
	
	<role role="Provider"
	description="General privileges held by all providers"
	uuid="8d94f280-c2cc-11de-8d13-0010c6dffd0f" />
	
<!-- role_role -->
<role_role parent_role="System Developer"
	child_role="Authenticated CDaccess" />

<!-- user_role -->
<user_role user_id="1" role="System Developer" />
<user_role user_id="1" role="Provider" />


<!-- privilege -->
<privilege privilege="Get Patients"
	description="Able to get patients"
	uuid="d05118c6-2490-4d78-a41a-390e3596a244" />


<!-- role_privilege -->
 
	<role_privilege role="Provider"
	privilege="Get Patients" />
	
	

<!-- user_property -->
<user_property user_id="1" property="loginAttempts"
	property_value="0" />
12

Can you edit your post and replace the stack trace with a link to pastebin.com where your paste is?

13

Yes sure https://pastebin.com/H7dpG3dY

14

You need to add the required privilege.

15

thank you! that’s was what I was thinking, any privilege for writing? or all of them mentioned in here https://wiki.openmrs.org/pages/viewpage.action?pageId=3346872

16

Doesn’t the stack trace have the missing privilege?

17

yes it does, Let me look for what privilege is and then I will come back

18

Hi @dkayiwa , when I did what you sggested (overrided useinmemorymethod) I got null pointer exception:

public IntegrationModuleDaoTest() throws ManagedProcessException {
		
		super();
		
		Properties props = getRuntimeProperties();
		
		if (!useInMemoryDatabase()) {
			
					DBConfigurationBuilder config = DBConfigurationBuilder.newBuilder();
					config.setPort(0); // 0 => autom. detect free port 
					DB db = DB.newEmbeddedDB(config.build());
					db.start();
					
					String dbName = "openmrs"; // or just "test" 
					if (!dbName.equals("test")) { //mysqld out-of-the-box already has a DB named "test" 
						                          // in case we need another DB, here's how to create it first 
						db.createDB(dbName);
					}
					
					String url = config.getURL(dbName);
					
					props.setProperty(Environment.URL, url);
					props.setProperty(Environment.USER, "root");
					props.setProperty(Environment.PASS, "");
			
		
			
		}
		
		Context.setRuntimeProperties(props);
		
	}
	
	@Override
	public Boolean useInMemoryDatabase() {
			return false;
	}