How to start?

Scaffolding

You can use ready pgsql scaffolding and not worry about task configurations. It's already done!

Example task structure:

pgsql
|-- solution
|  |-- schema.js
|-- test
|  |-- expectations
|  |  |-- expected_users.csv
|  |-- statements
|  |  |-- select_all_users.sql
|  |-- generic.spec.js
|  |-- parse.js
|  |-- scenario.sql
|  |-- testHelper.js
|-- .gitignore
|-- .jshintrc
|-- Gruntfile.js
|-- nodemon.json
|-- package.json
|-- README.md
|-- realskill.json

Database connection

You are required to provide valid connection to working PostgreSQL instance. This scaffolding is tested on PostgreSQL 9.4, however it should work on other database versions. If you just installed fresh version of PostgreSQL server don't forget to enable listening, setting listen_address = 'localhost' in PostgreSQL configuration file (on most *nix system it's located at /etc/postgresql/9.4/main/postgresql.conf). You may also have to adjust Host Based Authentication Policy that is described in pg_hba.conf file (recommended authentication method is MD5).

Configuration on *nix systems

You can manually prepare database connection or use command below that will create user, database, and set appropriate ownerships. Command below must be run from postgres system user (switch to root user then switch to postgres by su postgres). When prompted for password, enter password realskill.

createuser realskill -P && createdb realskill -O realskill && psql -d realskill -c 
'ALTER SCHEMA public OWNER TO realskill;'

Configuration on Windows systems

Using GUI tools

Use pgadmin to set following configuration:

user: realskill
password: realskill
database: realskill
schema: public

Database and schema owner must be set to realskill user.

Using command line

Open Windows Command Prompt as administrator (see help).

Create user realskill with realskill password.

createuser -P -U postgres -W realskill

You will be prompted for new user password twice, then postgres superuser password (default is postgres).

Create database realskill and set ownership to user realskill (you will be prompted for postgres password).

createdb -O realskill -U postgres -W realskill

Change schema public (of realskill database) ownership to user realskill (you will be prompted for postgres password).

psql -d realskill -U postgres -W -c "ALTER SCHEMA public OWNER TO realskill;"

##How to write a scenario for the task? ###scenario.sql

Scenarios are composed of 2 types of instructions: statement and expect. Each instruction symbol must be prepended with --. Last executed statement result is stored and compared with subsequent expect. Expect must be a valid CSV data set, with column names in first row.

Statement and expected content can be provided inline (in subsequent lines, before next instruction symbol) as well as extracted into separate file.

####inline instructions

--statement
INSERT INTO users(email) VALUES ('[email protected]'),('[email protected]');

--expect
id,email
1,[email protected]
2,[email protected]

####extracted instructions

--statement="../solution/schema.sql"

--expect="expectations/expected_users.csv"

####comment

You can put some inline comment after --statement or --expect statement if instruction doesn't point to external file. Those comments will be displayed with tests results.

--statement="../solution/schema.sql" Seed schema

--expect="expected_users.csv" 2 users

--statement insert valid row
INSERT INTO users(email) VALUES ('[email protected]'),('[email protected]');

--expect 2 users
id,email
1,[email protected]
2,[email protected]

error

You can expect data set response as well as SQL error. To test error you need to specify 2x2 csv table as follows:

name	code
error	SQL-errorCode

Code value must be valid PostgreSQL error code prefixed with SQL- string. Please place your test scenario in test/scenario.sql (you can find example scenario file there).

####example scenario

Provided runner parses plain text file as below (for syntax highlighting the sql format is recommended).

--statement="../solution/schema.sql" Seed schema
--statement insert valid row
INSERT INTO users(email) VALUES ('[email protected]'),('[email protected]');
--statement
SELECT * FROM users;
--expect 2 users
id,email
1,[email protected]
2,[email protected]
--statement="statements/select_all_users.sql" Select all users
--expect="expectations/expected_users.csv" 2 users
--statement insert incorrect row
INSERT INTO users(email) VALUES (1,2,3,4)
--expect syntax error
name,code
error,SQL-42601
--required expression="users" file="../solution/schema.sql" use "users" table

That would be translated into following scenarios:

 RealSkill SQL runner
    ✓ Parse tests scenarios and execute SQL (862ms)

  Evaluate scenario
    Statement Seed schema
      ✓ should be successfull
    Statement insert valid row
      ✓ should be successfull
    Statement SELECT * FROM users;
      ✓ should return 2 users
    Statement Select all users
      ✓ should return 2 users
    Statement insert incorrect row
      ! error: INSERT posiada więcej wyrażeń niż docelowych kolumn
        code: 42601
        routine: transformInsertRow 
      ✓ should return syntax error
    Statement use "users" table
      ✓ should find "users"