[Assignment Spec]  [Database Design]  [Examples]  [Testing]  [Submitting]  [Fixes+Updates]

A set of automated tests (based off of the examples provided) are available for you to run.

To run the automated tests

... login to vxdb2, source env, run your server as usual ...
vxdb2$ source /localstorage/$USER/env
vxdb2$ p1
... cd into the directory containing your ass1.sql file
vxdb2$ cd /my/dir/for/ass1
... Run the autotests
vxdb2$ 3311 autotest ass1

Your ass1.sql file must load into a new database without errors for autotest to run

Once you have completed all Views and Functions you should see something similar to the following

vxdb2$ 3311 autotest ass1
Ran 35 tests | 35 tests passed | 0 tests failed: 100%|████████████████████████████████████████████████████████████████████| 35/35 [00:02<00:00, 16.29 test/s]
Test Q1_A (Ensure View Q1 exists) - passed
Test Q1_B (Ensure Interface of View Q1) - passed
Test Q1_C (Check View Q1 Values (SELECT * FROM Q1 ORDER BY beer, "sold in", alcohol) ) - passed
Test Q2_A (Ensure View Q2 exists) - passed
Test Q2_B (Ensure Interface of View Q2) - passed
Test Q2_C (Check View Q2 Values (SELECT * FROM Q2 ORDER BY beer, abv, reason) ) - passed
Test Q3_A (Ensure View Q3 exists) - passed
Test Q3_B (Ensure Interface of View Q3) - passed
Test Q3_C (Check View Q3 Values (SELECT * FROM Q3 ORDER BY country) ) - passed
Test Q4_A (Ensure View Q4 exists) - passed
Test Q4_B (Ensure Interface of View Q4) - passed
Test Q4_C (Check View Q4 Values (SELECT * FROM Q4 ORDER BY beer) ) - passed
Test Q5_A (Ensure View Q5 exists) - passed
Test Q5_B (Ensure Interface of View Q5) - passed
Test Q5_C (Check View Q5 Values (SELECT * FROM Q5 ORDER BY beer, type) ) - passed
Test Q6_A (Ensure View Q6 exists) - passed
Test Q6_B (Ensure Interface of View Q6) - passed
Test Q6_C (Check View Q6 Values (SELECT * FROM Q6 ORDER BY beer) ) - passed
Test Q7_A (Ensure View Q7 exists) - passed
Test Q7_B (Ensure Interface of View Q7) - passed
Test Q7_C (Check View Q7 Values (SELECT * FROM Q7 ORDER BY brewery) ) - passed
Test Q8_A (Ensure Function Q8 exists) - passed
Test Q8_B (Check Function Q8 Values (SELECT * FROM Q8(9999)) ) - passed
Test Q8_C (Check Function Q8 Values (SELECT * FROM Q8(561)) ) - passed
Test Q8_D (Check Function Q8 Values (SELECT * FROM Q8(130)) ) - passed
Test Q8_E (Check Function Q8 Values (SELECT * FROM Q8(571)) ) - passed
Test Q8_F (Check Function Q8 Values (SELECT * FROM Q8(712)) ) - passed
Test Q8_G (Check Function Q8 Values (SELECT * FROM Q8(664)) ) - passed
Test Q8_H (Check Function Q8 Values (SELECT * FROM Q8(103)) ) - passed
Test Q9_A (Ensure Function Q9 exists) - passed
Test Q9_B (Check Function Q9 Values (SELECT * FROM Q9('oat cream') ORDER BY beer, brewer) ) - passed
Test Q9_C (Check Function Q9 Values (SELECT * FROM Q9('pastry stout') ORDER BY beer, brewer) ) - passed
Test Q9_D (Check Function Q9 Values (SELECT * FROM Q9('aquarius') ORDER BY beer, brewer) ) - passed
Test Q9_E (Check Function Q9 Values (SELECT * FROM Q9('Hazy IPA') ORDER BY beer, brewer) ) - passed
Test Q9_F (Check Function Q9 Values (SELECT * FROM Q9('spills') ORDER BY beer, brewer) ) - passed
35 tests passed 0 tests failed

You can run tests for a specific question by adding the question number to the end of the autotest command

vxdb2$ 3311 autotest ass1 Q8
Ran 8 tests | 8 tests passed | 0 tests failed: 100%|████████████████████████████████████████████████████████████████████████| 8/8 [00:00<00:00, 22.90 test/s]
Test Q8_A (Ensure Function Q8 exists) - passed
Test Q8_B (Check Function Q8 Values (SELECT * FROM Q8(9999)) ) - passed
Test Q8_C (Check Function Q8 Values (SELECT * FROM Q8(561)) ) - passed
Test Q8_D (Check Function Q8 Values (SELECT * FROM Q8(130)) ) - passed
Test Q8_E (Check Function Q8 Values (SELECT * FROM Q8(571)) ) - passed
Test Q8_F (Check Function Q8 Values (SELECT * FROM Q8(712)) ) - passed
Test Q8_G (Check Function Q8 Values (SELECT * FROM Q8(664)) ) - passed
Test Q8_H (Check Function Q8 Values (SELECT * FROM Q8(103)) ) - passed
8 tests passed 0 tests failed

Any test that doesn't pass will show the difference between the expected output and your output

For example:

vxdb2$ 3311 autotest ass1 Q3
Ran 3 tests | 2 tests passed | 1 tests failed: 100%|████████████████████████████████████████████████████████████████████████| 3/3 [00:00<00:00, 21.78 test/s]
Test Q3_A (Ensure View Q3 exists) - passed
Test Q3_B (Ensure Interface of View Q3) - passed
Test Q3_C (Check View Q3 Values (SELECT * FROM Q3 ORDER BY country) ) - failed (Incorrect output)
Your program produced these 271 lines of output:
-[ RECORD 1 ]---------------
country | Afghanistan
#beers  | 0
-[ RECORD 2 ]---------------
country | Albania
#beers  | 0
-[ RECORD 3 ]---------------
country | Argentina
#beers  | 0
-[ RECORD 4 ]---------------
country | Australia
#beers  | 519
-[ RECORD 5 ]---------------
country | Austria
#beers  | 1
-[ RECORD 6 ]---------------
country | Belgium
#beers  | 19
-[ RECORD 7 ]---------------
country | Bolivia
#beers  | 0
-[ RECORD 8 ]---------------
country | Brazil
#beers  | 0
-[ RECORD 9 ]---------------
country | Cameroon
#beers  | 0
-[ RECORD 10 ]--------------
country | Canada
#beers  | 12
-[ RECORD 11 ]--------------
country | Chile
...


The correct 270 lines of output for this test were:
-[ RECORD 1 ]---------------
country | Afghanistan
#beers  | 0
-[ RECORD 2 ]---------------
country | Albania
#beers  | 0
-[ RECORD 3 ]---------------
country | Argentina
#beers  | 0
-[ RECORD 4 ]---------------
country | Australia
#beers  | 530
-[ RECORD 5 ]---------------
country | Austria
#beers  | 1
-[ RECORD 6 ]---------------
country | Belgium
#beers  | 19
-[ RECORD 7 ]---------------
country | Bolivia
#beers  | 0
-[ RECORD 8 ]---------------
country | Brazil
#beers  | 0
-[ RECORD 9 ]---------------
country | Cameroon
#beers  | 0
-[ RECORD 10 ]--------------
country | Canada
#beers  | 12
-[ RECORD 11 ]--------------
country | Chile
...


The difference between your output(-) and the correct output(+) is:
...
  country | Australia
- #beers  | 519
?            ^^

+ #beers  | 530
?            ^^

...

2 tests passed 1 tests failed

In this example we expected to recive the number 530 but actully recived the number 519

The query used in each test is provided and can be used to reproduce the results of the test

In this example the query used was SELECT * FROM Q3 ORDER BY country

To reproduce this test you would run the following commands

... if you already have an ass1 database ...
vxdb2$ dropdb ass1
... create a new empty atabase ...
vxdb2$ createdb ass1
... load the data into the database ...
vxdb2$ psql ass1 -f /home/cs3311/web/23T1/assignments/ass1/ass1.dump
... load your views and functions into the database ...
vxdb2$ psql ass1 -f ass1.sql
... run the failing query ...
vxdb2$ psql ass1 -c "SELECT * FROM Q3 ORDER BY country"
... examine the output ...
... autotest uses the psql extended output format that can be viewd by using `-x` ...
vxdb2$ psql ass1 -x -c "SELECT * FROM Q3 ORDER BY country"

If your ass1.sql fails to load (this is: produces an ERROR) you will see the following

Error encountered while running autotests:

---- Loading Database Dump File ----
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
CREATE DOMAIN
CREATE TYPE
CREATE DOMAIN
CREATE TYPE
CREATE DOMAIN
CREATE TYPE
CREATE DOMAIN
CREATE DOMAIN
CREATE FUNCTION
SET
SET
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
COPY 833
COPY 866
COPY 262
COPY 868
COPY 90
COPY 308
COPY 226
COPY 158
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
GRANT
REVOKE

---- Loading Student Submission File ----
CREATE VIEW
psql:ass1.sql:57: ERROR:  syntax error at or near "CREATE"
LINE 12: CREATE OR REPLACE VIEW Q2(beer, style, abv, reason) AS
         ^
ERROR loading file: `ass1.sql`

The first set of output is produced from loading the ass1.dump file, and can be ignored

Everything below the line

---- Loading Student Submission File ----

was produced while loading your ass1.sql file

any line except for ERROR lines are allowed

If there are any ERROR lines, they must be fixed before autotesting can continue