Run Test Cases on multiple databases

Wim · 1 July 2021 17:47

Hi
I’m new to Robotframework and building a test to check certain parameters in my databases.

This is just a simple test I did.

*** Settings ***
Library OracleDB
Library JSONLibrary

*** Test Cases ***
Login Database
log to console Login Database s-db4upg-cch
log Login Database s-db4upg-cch
OracleDB.Connect To Oracle s-db4upg-cch user passwword
DB001 - Check DB DOMAIN
@{query} Execute Sql String select name, value from v$parameter where name in (‘db_domain’)
Should Match Regexp ${query[0][1]} ^(d|D|o|O|t|T|a|A|p|P)$
DB002 - Check FORCE LOGGING
@{query} Execute Sql String select force_logging from v$database
Should Be Equal YES ${query[0][0]}
DB003 - Check FLASHBACK-ON
@{query} Execute Sql String select flashback_on from v$database
Should Be Equal YES ${query[0][0]}
Logout Database
OracleDB.Close All Oracle Connections

This runs on just one of my databases.
What would be the best approach for the following?

I want to have just one set of Test Cases like above, but I want to run this on multiple databases.
So the database would be variable.
Because I have hundreds of database to check on, I would in the end divide them into (for example) like 10 parallel and 40 sequential.
For parallel I read I should use pabot.

For now I’m just looking on how to run 1 Test Case for different databases.

Any help, suggestions are welcome.

regards,
Wim

damies13 · 2 July 2021 04:20

Hi Wim

First a little tip for putting your test cases in a forum post, if you put three back ticks (```) on the line before and the line after then your formatting should be preserved.

So just to confirm this is your current test case?

*** Settings ***
Library OracleDB
Library JSONLibrary

*** Test Cases ***
Login Database
    log to console     Login Database s-db4upg-cch
    log     Login Database s-db4upg-cch
    OracleDB.Connect     To Oracle     s-db4upg-cch     user     passwword
DB001 - Check DB DOMAIN
    @{query}     Execute Sql String     select name, value from v$parameter where name in (‘db_domain’)
    Should Match Regexp     ${query[0][1]}     ^(d|D|o|O|t|T|a|A|p|P)$
DB002 - Check FORCE LOGGING
    @{query}     Execute Sql String     select force_logging from v$database
    Should Be Equal     YES     ${query[0][0]}
DB003 - Check FLASHBACK-ON
    @{query}     Execute Sql String     select flashback_on from v$database
    Should Be Equal     YES     ${query[0][0]}
Logout Database
    OracleDB.Close     All Oracle Connections

Assuming I’ve read your test case correctly, i’ll make a couple of suggestions of approaches you could take, there is no right or wrong way, you should use what ever approach works best for you.

Suggestion 1

This is very similar to what you already have, just using a few variables. for this example I’ll assume this test is saved in a file called dbchecks.robot

*** Settings ***
Library OracleDB
Library JSONLibrary

Suite Setup    Login Database
Suite Teardown    Logout Database

*** Variables ***
${dbserver}    s-db4upg-cch
${dbuser}     user
${dbpass}     passwword


*** Test Cases ***
DB001 - Check DB DOMAIN
    @{query}     Execute Sql String     select name, value from v$parameter where name in (‘db_domain’)
    Should Match Regexp     ${query[0][1]}     ^(d|D|o|O|t|T|a|A|p|P)$

DB002 - Check FORCE LOGGING
    @{query}     Execute Sql String     select force_logging from v$database
    Should Be Equal     YES     ${query[0][0]}

DB003 - Check FLASHBACK-ON
    @{query}     Execute Sql String     select flashback_on from v$database
    Should Be Equal     YES     ${query[0][0]}

*** Test Cases ***

Login Database
    log to console     Login Database ${dbserver}
    log     Login Database ${dbserver}
    OracleDB.Connect     To Oracle     ${dbserver}    ${dbuser}     ${dbpass}

Logout Database
    OracleDB.Close     All Oracle Connections

First I move the login and log out tests to keywords and then made them suite setup and teardown this is because the best practice is for robot test cases to not be dependant on other test cases, and DB001 - DB003 were dependant on your Login Database test, but if that failed the other tests would have run and failed. Doing it like this, if Login Database fails none of the other test cases will be run because their prerequisite was not met.

Next I moved the login details for the db to global variables so we can easily change them.

Then to execute the test with different databases, we just override the global variables from the command line:

$ robot -v dbserver:s-db2upg-cch -v dbuser:user2 -v dbpass:password2 dbchecks.robot
$ robot -v dbserver:s-db4upg-cch -v dbuser:user -v dbpass:passwword dbchecks.robot
$ robot -v dbserver:s-db6upg-cch -v dbuser:user6 -v dbpass:paswword6 dbchecks.robot

As you can see from the three robot command lines above, we can use the -v switch to pass in different values for the variables and override the values that were in the file.

This approach works well when you have CI/CD pipeline and the database details change every build but the test cases really remain the same, as you can generate this command line with your CI/CD tool, and pass in the database details that were generated during the build.

Suggestion 2

This approach is an example of using the test template data driven style

*** Settings ***
Library OracleDB
Library JSONLibrary

Test Template    Login execute And Logout

*** Test Cases ***         Database           Username      Password      TestCase
s-db4upg-cch DB001         s-db4upg-cch       user          passwword     DB001 - Check DB DOMAIN
s-db4upg-cch DB002         s-db4upg-cch       user          passwword     DB002 - Check FORCE LOGGING
s-db4upg-cch DB003         s-db4upg-cch       user          passwword     DB003 - Check FLASHBACK-ON

s-db2upg-cch DB001         s-db2upg-cch       user2         pass2word     DB001 - Check DB DOMAIN
s-db2upg-cch DB002         s-db2upg-cch       user2         pass2word     DB002 - Check FORCE LOGGING
s-db2upg-cch DB003         s-db2upg-cch       user2         pass2word     DB003 - Check FLASHBACK-ON

s-db6upg-cch DB001         s-db6upg-cch       user6         pass6word     DB001 - Check DB DOMAIN
s-db6upg-cch DB002         s-db6upg-cch       user6         pass6word     DB002 - Check FORCE LOGGING
s-db6upg-cch DB003         s-db6upg-cch       user6         pass6word     DB003 - Check FLASHBACK-ON



*** Test Cases ***
Login execute And Logout
    [Arguments]    ${dbserver}    ${dbuser}    ${dbpass}    ${TestCase}
    Login Database    ${dbserver}    ${dbuser}    ${dbpass}
    Run Keyword    ${TestCase}
    Logout Database

Login Database
    [Arguments]    ${dbserver}    ${dbuser}    ${dbpass} 
    log to console     Login Database ${dbserver}
    log     Login Database ${dbserver}
    OracleDB.Connect     To Oracle     ${dbserver}    ${dbuser}     ${dbpass}

Logout Database
    OracleDB.Close     All Oracle Connections

DB001 - Check DB DOMAIN
    @{query}     Execute Sql String     select name, value from v$parameter where name in (‘db_domain’)
    Should Match Regexp     ${query[0][1]}     ^(d|D|o|O|t|T|a|A|p|P)$

DB002 - Check FORCE LOGGING
    @{query}     Execute Sql String     select force_logging from v$database
    Should Be Equal     YES     ${query[0][0]}

DB003 - Check FLASHBACK-ON
    @{query}     Execute Sql String     select flashback_on from v$database
    Should Be Equal     YES     ${query[0][0]}

Suggestion 3

Is another variation on template test cases, in this example I’ve given examples:

where each database is the test case and all 3 checks are performed in each database test, this will result in 3 test results
where each query is a seperate test and then the databases are tested for each database, this will result in 9 test results for the 3 databases

*** Settings ***
Library OracleDB
Library JSONLibrary

*** Test Cases ***
Check Databases
    [Template]    DB Checks
    s-db4upg-cch       user          passwword
    s-db2upg-cch       user2         pass2word
    s-db6upg-cch       user6         pass6word

DB001
    [Template]    DB001 Check
    s-db4upg-cch       user          passwword
    s-db2upg-cch       user2         pass2word
    s-db6upg-cch       user6         pass6word

DB002
    [Template]    DB002 Check
    s-db4upg-cch       user          passwword
    s-db2upg-cch       user2         pass2word
    s-db6upg-cch       user6         pass6word

DB003
    [Template]    DB003 Check
    s-db4upg-cch       user          passwword
    s-db2upg-cch       user2         pass2word
    s-db6upg-cch       user6         pass6word



*** Test Cases ***
DB Checks
    [Arguments]    ${dbserver}    ${dbuser}    ${dbpass}
    Login Database    ${dbserver}    ${dbuser}    ${dbpass}
    DB001 - Check DB DOMAIN
    DB002 - Check FORCE LOGGING
    DB003 - Check FLASHBACK-ON
    Logout Database

DB001 Check
    [Arguments]    ${dbserver}    ${dbuser}    ${dbpass}
    Login Database    ${dbserver}    ${dbuser}    ${dbpass}
    DB001 - Check DB DOMAIN
    Logout Database

DB002 Check
    [Arguments]    ${dbserver}    ${dbuser}    ${dbpass}
    Login Database    ${dbserver}    ${dbuser}    ${dbpass}
    DB002 - Check FORCE LOGGING
    Logout Database

DB003 Check
    [Arguments]    ${dbserver}    ${dbuser}    ${dbpass}
    Login Database    ${dbserver}    ${dbuser}    ${dbpass}
    DB003 - Check FLASHBACK-ON
    Logout Database


Login Database
    [Arguments]    ${dbserver}    ${dbuser}    ${dbpass} 
    log to console     Login Database ${dbserver}
    log     Login Database ${dbserver}
    OracleDB.Connect     To Oracle     ${dbserver}    ${dbuser}     ${dbpass}

Logout Database
    OracleDB.Close     All Oracle Connections

DB001 - Check DB DOMAIN
    @{query}     Execute Sql String     select name, value from v$parameter where name in (‘db_domain’)
    Should Match Regexp     ${query[0][1]}     ^(d|D|o|O|t|T|a|A|p|P)$

DB002 - Check FORCE LOGGING
    @{query}     Execute Sql String     select force_logging from v$database
    Should Be Equal     YES     ${query[0][0]}

DB003 - Check FLASHBACK-ON
    @{query}     Execute Sql String     select flashback_on from v$database
    Should Be Equal     YES     ${query[0][0]}

There are probably other approaches but this should give you plenty of food for thought.

Hope this helps,

Dave.

Wim · 2 July 2021 09:25

H Dave,

thx for your time spend and the quick response.

I’m looking for something like suggestion 2.
These are just three tests I do, but there will be many more.
Suggestion 2 however logs into each and every database per check.

What I’m looking for (but don’t know if this is possible) is something like this, with a for loop.

I will put these in resource files ofcourse, and use keywords for logon/logoff.
I tried something like above, but all one one test fails, the rest are ignored.

I would like to have one set of test, log on to a database, run all tests, then next database, run all tests etc.
Even when one test fails, I it should do the other tests.
The result should be in one report where I can look per database if tests where successful or not.

Next I would check out pabot to run this in parallel, because I have a lot of databases to test.

thx once more for help and if you have any more suggestions …

regards,
Wim

damies13 · 2 July 2021 14:47

Hi Wim,

do you mean you want something like this, basically a hybrid of suggestion 2 and 3:

*** Settings ***
Library OracleDB
Library JSONLibrary

Test Template    Login execute And Logout

*** Test Cases ***         Database           Username      Password
s-db4upg-cch               s-db4upg-cch       user          passwword
s-db2upg-cch               s-db2upg-cch       user2         pass2word
s-db6upg-cch               s-db6upg-cch       user6         pass6word


*** Test Cases ***
Login execute And Logout
    [Arguments]    ${dbserver}    ${dbuser}    ${dbpass}
    Login Database    ${dbserver}    ${dbuser}    ${dbpass}
    DB001 - Check DB DOMAIN
    DB002 - Check FORCE LOGGING
    DB003 - Check FLASHBACK-ON
    Logout Database

Login Database
    [Arguments]    ${dbserver}    ${dbuser}    ${dbpass} 
    log to console     Login Database ${dbserver}
    log     Login Database ${dbserver}
    OracleDB.Connect     To Oracle     ${dbserver}    ${dbuser}     ${dbpass}

Logout Database
    OracleDB.Close     All Oracle Connections

DB001 - Check DB DOMAIN
    @{query}     Execute Sql String     select name, value from v$parameter where name in (‘db_domain’)
    Should Match Regexp     ${query[0][1]}     ^(d|D|o|O|t|T|a|A|p|P)$

DB002 - Check FORCE LOGGING
    @{query}     Execute Sql String     select force_logging from v$database
    Should Be Equal     YES     ${query[0][0]}

DB003 - Check FLASHBACK-ON
    @{query}     Execute Sql String     select flashback_on from v$database
    Should Be Equal     YES     ${query[0][0]}

The problem with doing it in a for loop like you showed is that you only get a single pass or Fail and then you have to drill down to find out which database and which query failed.

The examples I have in suggestion 2 and the second set in suggestion 3 give you 9 test results 3 databases x 3 queries, this way when one fails you can quickly see at the summary level which one(s) failed. It all depends on how you need to report when there are failed tests.

Dave.

Topic		Replies	Views
How can I run a test case multiple times and store the results in the same log file Robot Framework	7	5249	26 May 2023
How do I run the same Robot Framework test against different URLs in one test run? Robot Framework	2	359	16 December 2022
Parallel execution of dynamic created test cases Robot Framework	2	877	23 November 2022
Perf test : Several login at the same time Robot Framework	6	1495	9 February 2021
Run Test case multiple times Robot Framework	0	1342	20 April 2022

Run Test Cases on multiple databases

Suggestion 1

Suggestion 2

Suggestion 3

Related Topics