Marking Automation Tester

My Real Project
(Marking Automation Tester) by Kurniawan

I am a Database Management System tutor in Swinburne University this semester.
This week I have a lot of job. I have to marking DMS assignment that I am tutoring.
I got 80 programs (ORACLE FORMS) to test and give them a score.

It was a busy day. So I want to find the way to make automation tester so my work will be easier. But thanks God, I found the way.

I was sitting in Software Testing Process Automation and the lecture (Dr George Plackov) introduced very interesting tools (Rational Robot). My Plan is to create Automation tester to mark 80 Oracle Forms using Rational Robot.

This Automation tester will run the program and check according to the marking sheet and put it the score through the Electronic Submission Processor (ESP)
https://esp.it.swin.edu.au/ .

So basically I just doing 1 click for every form that I get and it will do everything for me. Would it be very interesting?

This is the description of the specification requirement.

GENERAL DESCRIPTION
The aim of this assignment is to implement:
A. A stored procedure that performs a bank transaction on the Minibank database.
B. A Form that can be used by the teller to perform a bank transaction over-the-counter bank transaction.

The Minibank Schema MBDDL.SQL with sample data MBDAT.SQL are supplied
MBCUSTOMER(cust_no, surname, name1, name2, address1, address2, state, postcode, birthdate)
MBACCOUNT(account_no, cust_no, account_type, int_rate , last_stmnt_date, balance, overdraft, holdflag)
MBTRAN(trans_no, trans_type, trans_amount, account_no, balance, trans_text, trans_date_time, trans_user)
TRANSNUMB_CONTROL(last_transnumb)
The table TRANSNUMB_CONTROL is used to a generate unique key values for the MBTRANS table. TRANSNUMB_CONTROL should hold the next key value for a row to be inserted into MBTRANS, and then incremented ready for the transaction.



DETAILED SPECIFICATION
A. The stored procedure performs a bank transaction in accordance with the following business rules:
BR1. When a (Bank) transaction is performed on a customer’s account (eg. deposit or withdrawal transaction), a record is entered into the transaction log (MBTRANS) identified by a unique transaction number. The current time, date, teller identity as well as the account number, the new account balance and type of transaction are recorded.
BR2. A deposit (trans_type = ‘DEP’) credits the account balance by the transaction amount.
BR3. A withdrawal (trans_type = ‘WITH’) debits the account balance by the transaction amount.
BR4. If a withdrawal would take the balance below the credit limit (balance + overdraft < 0 ), the withdrawal is rejected.
BR5. No withdrawals are allowed on held accounts.
BR6. For any over-the-counter withdrawal (trans_text = ‘Over-the-counter’ AND trans_type = ‘WITH’), a bank charge of $3 is automatically levied on the account. The charge is debited from the account balance and logged separately as a transaction of type CHGS (trans_type = ‘CHGS’).
BR7. For any deposit or withdrawal (trans_type IN (‘WITH’,‘DEP’)) on a cheque account (account_type = ‘CHQ’), the government levies a tax at the rate of 0.1% of the deposit or withdrawal amount. The tax is debited from the account balance and logged separately as a transaction of type FID (trans_type = ‘FID’).

The stored procedure has the following specification:
CREATE OR REPLACE addTrans(
new_trans_type MBTRANS.trans_type%TYPE,
new_trans_amount MBTRANS.trans_amount%TYPE,
new_account_no MBTRANS.account_no%TYPE,
new_trans_text MBTRANS.trans_text%TYPE,
new_trans_user MBTRANS.trans_user%TYPE)
The procedure should raise the following errors:
raise_application_error(-20001, ‘Account not found’);
raise_application_error(-20002, ‘Insufficient funds’);
raise_application_error(-20003, ‘Account held: No withdrawal allowed’);




Testing stored procedure
The stored procedure should be tested for the following scenarios:
Scenario Expected Outcome
Normal Deposit Deposit transaction logged.
Account balance incremented by transaction amount.
Tax transaction logged.
Over-the-counter Withdrawal Withdrawal transaction logged.
Account balance decremented by transaction amount.
Tax transaction logged.
Charges transaction logged.
Other Withdrawal Withdrawal transaction logged.
Account balance decremented by transaction amount.
Tax transaction logged.
Non-existent account ERROR ‘Account not found’
Insufficient funds ERROR ‘Insufficient funds’
Account held ERROR ‘Account held’



B. Develop an Oracle form to be used by a teller at Minibank to perform over-the-counter bank transactions. The teller accepts applications from customers at the counter for deposits or withdrawals from any of the customer’s accounts except withdrawals from a cheque account. Cheque clearances are handled separately. The behaviour of the form during the user interaction phase is as follows:
1. When the user teller loads the form, the current date and identity of the teller is displayed.
2. The teller enters an account number either directly or by selecting a value from an LOV (pop up list) showing account_no, account_type and surname in surname sequence.
3. Upon entering a valid account number, the customer’s surname (surname) and first given name (name1), account type and available credit (credit) are displayed. Available credit is calculated as balance+overdraft. The user cannot directly modify the displayed values of surname, name1, account_type or credit on the form.
4. Only a single over-the-counter deposit or withdrawal transaction can be entered by this form; that is, purchase, interest, bank charge or tax transactions cannot be entered directly by this form. The user then enters either ‘DEP’ or ‘WITH’ for trans_type, except if the account type is ‘CHQ’, or the account is held, then trans_type displays ‘DEP’ and can only accept ‘DEP’.
5. The user then enters a positive value for the transaction amount (trans_amount). A deposit (DEP) increments the displayed available_credit by trans_amount. A withdrawal decrements the credit by trans_amount, as long as credit does not go below zero in which case the transaction amount is rejected. (See business rule 4 above.)
6. (OPTIONAL) If the user goes back and modifies trans_type, if trans_amount is not null, credit is recalculated and displayed.
7. If the user does not enter a value for trans_text, it defaults to ‘Over-the-counter’.
8. (OPTIONAL) If the user goes back and modifies account_no, then surname, name1, account_type and credit are re-evaluated and trans_type and trans_amount are set to null.
9. The form should also enforce all fixed-format database constraints that apply to data in the form.
10. The form cannot be used to directly query, update or delete of any row in the database.

The appearance of the form should be

When the user presses SAVE, the form should commit a transaction on the Minibank database according to business rules BR1-7 above. These business rules must be preserved during the Post-and-commit Phase. Even though business rules 4 & 5, would have been validated during the user interaction phase, it is possible that it may have been invalidated by other concurrent transactions. Such violations must be caught by your form and reported appropriately on the message line. This will be tested.
You should produce two different versions of the form, each with a different design for the post-and commit logic.
B1 This version should call the stored procedure from part A from the ON-INSERT trigger. Thus all business logic is implemented on the server. All exceptions raised by the stored procedure will be handled in the EXCEPTION section of the ON-INSERT trigger. (An example of this kind of architecture can be found as VERSION 3 in Lecture 5.) A skeleton for the ON-INSERT trigger is provided below.
B2 This version should implement all business logic in the PRE-INSERT trigger on the client. All exceptions will therefore be handled in the EXCEPTION section of this trigger. (An example of this kind of architecture can be found as VERSION 1 in Lecture 5.)
Only Oracle Form Builder features described in lectures may be used to develop this form.
Your form must behave correctly even when there are several transactions running concurrently. That is, transactions independently accepting valid changes during user interaction may fail after attempting to commit the changes. Hence you will need to lock records to prevent two or more users accessing the same record at the same time. It is therefore important for you to decide what SELECT statements should lock their result.
Your form must be portable. That is, it should run correctly from any Oracle account that has access to the MB database. To ensure this, you should run and test your form in a different account from what it was developed.
Development Steps
1. Analyse Business rules. Determine whether each one is a verifier or an evaluator.
2. For the database transaction, determine what business rules need to be preserved by the transaction.
3. Determined what tables are primarily INSERTed into by the transaction. Make datablocks based on these tables for the form.
4. Based on the requirements of part B, carry out a design of your form for the user interaction phase. Use the notation for form objects and triggers from the lecture.
5. For each INSERT operation, draw a block diagram showing a PL/SQL program block with inner blocks that preserve verifiers and evaluators.
6. Code and test stored procedures.
7. Use the PL/SQL block design to design the behaviour of your form during the Post-and-Commit phase. Use the notation for triggers, procedures and tables from the lecture. Draw separate diagrams for each of Architecture B1 and Architecture B2.
8. Code and test.
Forms Advice
a. Make sure when compiling a form that you are connected to the DBMS. If not, errors will be generated even though the syntax and logic may be correct.
b. The order in which the cursor jumps from field to field is determined by the order in which those blocks and fields are listed in the object navigator. Oracle starts at the top and works its way down. To change the jumping order just change the order in the object navigator by selecting and dragging the fields to the appropriate position relative to the others. Also applies to blocks.
c. When creating a text-item manually in Navigator, the item will not be visible on the form unless it is place on the canvas. Assign it to the canvas in the Property Palette. To change the properties of the item, double click on the item to get the Property Palette.
d. When creating a text item that does not correspond to a column in the current table, don’t forget to change the property Database item to NO in the Property Palette.
e. So that a user cannot change the value of an item, you can set the item properties so that it is a Display Item.
f. To disallow querying, deleting or updating of records, go into the properties of the block in the section Database and set to NO the properties “query allowed” , “update allowed” and “delete allowed”.
g. To run a Form, don’t forget to start OC4J instance.








MARKING GUIDE
MB Teller Form
This marking scheme assumes a datablock based on the MBTRAN table. Designs based on other tables are possible, but complex. Any student presenting with an alternative design must pass the same behavioural tests. The functionality should be the same, but the triggers may be different. You need to look at any like this separately, but be tough.
Populate the database by running MBANKDAT2.sql from the SQL*Plus window.
Carry out inspection only if test is inconclusive.
Feature Test Inspection Mark
1. Form showing correct fields. Load the form MBTellerBa.FMB and observe. 1
2. Current date (non-input) displayed. Observe WHEN-CREATE-RECORD, PRE-FORM, -BLOCK or –RECORD 1
3. Current user (non-input) displayed. Observe 1
4. No query allowed. Attempt Enter Query. Property Palette. 1
5. account_no input, NOT NULL and validated, and LOV for held account. Attempt to leave field NULL.
Enter wrong account_no, manually.
Attempt to enter valid account_no by LOV. LOV 2
6. Customer name, Credit Account type (all non-input) populated. Enter account_no = 410100
Check Customer are populated.
POST-TEXT-ITEM or POST-CHANGE or WHEN-VALIDATE-ITEM on :account_no. 2
7. Customer name, Credit Account type cannot be updated. Attempt to update any of:surname, :name1, :credit, :account_type fields. Propery Palette 1
8. Trans_type input, NOT NULL and valid. Attempt to leave field NULL.
Enter wrong :trans_type.
Enter valid :trans_type = ‘WITH’. POST-TEXT-ITEM or POST-CHANGE or WHEN-VALIDATE-ITEM on :trans_type. 1
9. Withdrawal should give error. Should display message ‘Cannot withdraw from held account.’ OR WHEN-VALIDATE-RECORD 1
NEW FORM
10. Enter Trans_amount invalid for withdrawal. Should show appropriate error message. Enter account_no = 402000
Enter valid :trans_type = ‘WITH’.
Enter invalid :trans_amount = 5000.
Should display message ‘Insufficient funds.’ POST-TEXT-ITEM or POST-CHANGE or WHEN-VALIDATE-ITEM on :trans_amount and :trans_type. 1
11. Enter valid withdrawal Trans_amount. Credit (non-input) updated. Enter valid :trans_amount = 2000.
:credit = 2003 2
12. Comment (input) initialized and. :trans_text = ‘Over-the-counter’
Attempt to change ok. 1
END OF USER INTERACTION testing
13. Enter valid withdrawal, but before SAVE, another transaction reduces credit limit. Should produce appropriate error message. Enter account_no = 402000
Enter valid :trans_type = ‘WITH’
Enter valid :trans_amount = 2000.
DO NOT PRESS SAVE
In SQL*Plus window, reduce the credit available to account 402000, by running 2.sql script. Should succeed, if not, then lock in form taken too soon. Return to the form window and SAVE. Should fail with user-defined error message of insufficient credit. PRE-INSERT on :MBTRAN trigger for credit check. Re-read account balance with lock. Original credit must be reassigned based on new query.
PRE-INSERT on :MBTRAN 4
14. Enter valid withdrawal, but before SAVE, another transaction puts hold on the account. Should produce appropriate error message. Enter valid :trans_amount = 5.
DO NOT PRESS SAVE
In SQL*Plus window, put a hold on account 402000, by running 3.sql script. Should succeed, if not, then lock in form taken too soon. Return to the form window and SAVE. Should fail with user-defined error message of account on hold. 3
15. Successful cheque deposit:Trans number correctly assigned Enter account_no = 402100
Enter valid :trans_type = ‘DEP’
Enter valid :trans_amount = 200.
PRESS SAVE, Run 4.sql in SQL*Plus window:
TRANS_NO TRAN TRANS_AMOUNT ACCOUNT_NO BALANCE TRANS_TEXT
---------- ---- ------------ ---------- ---------- ----------------
5002 FID .2 402100 2806.6 Cheque Deposit
5000 DEP 200 402100 2806.8 Over-the-counter PRE-INSERT on :MBTRAN.
Transaction number generated
Date re-evaluated
Re-read balance & check credit
No hold check

PRE-INSERT or POST-INSERT on :MBTRAN.
Govt tax INSERT
Bank charge INSERT
If PRE-INSERT, database items should not be modified. 1
16. Transaction logged 1
17. Govt tax. 1
18. Account balance updated 1
19. Successful savings withdrawal:Trans number correctly assigned. New form
Enter account_no = 410011
Enter valid :trans_type = ‘WITH’
Enter valid :trans_amount = 100.
PRESS SAVE, Run 5.sql in SQL*Plus window:
TRANS_NO TRAN TRANS_AMOUNT ACCOUNT_NO BALANCE TRANS_TEXT
-------- ---- ------------ ---------- ---------- ----------------
5003 WITH 100 410011 12496.9 Over-the-counter
5004 CHGS 3 410011 12493.9 OtC Fee 1
20. Transaction logged 1
21. Bank charge 1
22. Account balance updated 1
23. No queries in user-interaction triggers should be locked Deduct marks if SQL*Plus transactions hang. user-interaction triggers 4
CODE INSPECTION
24. All queries in transactional triggers must be locked. PRE-INSERT trigger 2
25. Separate user-defined error messages. Separate error message texts for insufficient funds, held account, account not found. Look in POST-CHANGE etc on :trans_type & :trans_amount, PRE-INSERT & ON-ERROR. Should not combine error messages.. 2
26. Trigger coding style Naming, indentation, program blocks, data declarations as local as possible. 2
27. Error message text Appropriate error message text. 3
28. Validation Trigger level (during user interaction) A constraint should be tested as early as possible, ie. the trigger timing should not be too late or level too high. A constraint should not be tested any more frequently than is necessary, ie. the trigger level should not be too low. Triggers should be used consistently, viz POST-TEXT-ITEM or POST-CHANGE or WHEN-VALIDATE-ITEM. 2
29. No redundant, nonfunctioning objects Such as blocks, items or triggers 3
30. No duplicate querying of the same row in user interaction phase. 3
31. Logic for separate business rules in separate subblocks. Except where functionality is common 2
32. No duplicate functionality in post-and commit phase. Repeating code to generate trans number 2
33. All error messages confined to EXCEPTION section of PRE-INSERRT trigger Use EXCEPTION variables 2
34. No programmed INSERT of primary bank transaction in PRE-INSERT trigger 3
35. Quality of Comments Comments should use business language unless programming is unusually complex. All program blocks shoud be traceable to business rules by comments. 2
36. Quality of names Names in form should be consisyent with the DB. Other names should be meaningful in terms of the business. 2
RESTORE the DB (Run MBANKDAT2.sql) - CALLING Stored procedure 0
37. Form commits db transaction correctly with stored procedure. Run the form MBTellerBb.FMB with any successful transaction. No errors. 5
38. Calling stored procedure from ON-INSERT trigger Parameters should be correct 3
39. EXCEPTION section of ON-INSERT trigger 3
TOTAL 75


ESP MARKING SHEET should be automatically filled with Rational Robots




After it finished fill up my marking sheet, I can check up manually before I submitted the score by comparing the result with the reports from rational robots.
STEPS THAT I HAVE DONE


STEPS 1. Preparing all of the stuff that needs to be test and run automatically
There are 3 applications that should be run automatically together:

1. Form Application that I want to test






2. SQL PLUS, To test Locking and deadlock.


3. and ESP system to input the marking



STEPS 2. Open the rational Robot and Start Doing Capturing GUI

Select from ESP system which assignment need to be checked.

Download the assignment


Save the file to the C:/temp


Open The form and look at the code


Start Testing and Capture the Result


Update the Marking Sheet based on the testing



STEPS 3. Changing the Scripts so it can be looping automatically through all buttons

By using some basic iteration algorithm, I can generate very powerful tool
This is the code that has been generated



I can generate test data by using Data pools
Open Test Manager (from a Rational Robot script follow Tools > Rational
Test > Test Manager)


Open a datapool (Tools > Manage > Datapools)


I can generate 2000 Test Data to test the performance of the oracle


After that I change my Scripts so It can generate The SQL (Script Query Language that can be inputted directly to iSQL Plus


'$Include "SQAUTIL.SBH"

Sub Main
Dim Result As Integer
dim i as integer
dim givenName as string
dim surname as string
dim address as string
dim phone as string
dim mobile as string

dp= SQAdatapoolOpen("Customer")


'Initially Recorded: 29/04/2006 9:56:01 PM
'Script Name: test sql

Window SetContext, "Caption=iSQL*Plus Release 9.2.0.6.0 Production: Work Screen - Mozilla Firefox", ""


for i=1 to 200

call SQADatapoolFetch(dp)
call SQADatapoolValue(dp,1,givenName)
call SQADatapoolValue(dp,2,surname)
call SQADatapoolValue(dp,3,address)
call SQADatapoolValue(dp,4,phone)
call SQADatapoolValue(dp,5,mobile)

InputKeys "insert into customer values ('" & givenName & “…….. "{ENTER}"

GenericObject Click, "Class=MozillaWindowClass;ClassIndex=7", "Coords=37,392"
next i
call SQADAtapoolClose(DP)
End Sub



AND it will generate




After that I just compare the result that generated by Rational Robot which I done manually.

After I confident if my Automation Tester will work properly, I leave it alone until 4 hours testing.

And finally this subject really helps me a lot, to finish my job and busy day.
I can image how to work without this tool.
Thanks to my beloved teacher (Dr George Plackov)








THE LACKNESS OF RATIONAL ROBOT

• Rational Robot consumes a lot of Memory and CPU process!
I test my application using Rational Robot at Swinburne Burwood LAB ( SB103 ) That have 256 DDR 400 RAM, Pentium 4 1.8 GHZ. And It takes around 6 hours to capture my testing process. It will be easy to capture with small application in Lab material (using Access) but when I try using ORACLE and FORM, SQL PLUS, FIREFOX as browser, it takes very long to capture. I have to wait 5-10 minutes to capture 1 functional test. It is very slow!!!

• Rational Robot not release unused memory
It holds the memory every time it finished. As a result I have to restart my computer for each capturing functional test. Otherwise my computer will be hangs

• Rational Robot contains some bugs
I found some bugs. Some functional can not be capture especially when I want to capture LOV (List of Value) in Oracle form. It always hangs; even I try to restart many times.

• Rational Robot don’t have recovery state
It doesn’t have recovery state. If my computer hangs when I am doing capture, and I have to restart my windows, my capture test is GONE!

• Rational Robot consumes a lot of Hard disk capacity
For 1 capture functional test, it consumes 20-30 MB. Image if you have 1000 functional test, how much HD capacity, you need to supply for this Rational Robot.

Finally because of those lack nesses, it takes more than 10 hours just for generating Automation Marker. Hopefully it will be shorter time, if I have more memory and CPU.