Blank for data schema with tests, CI, without preference

A relational database is a relatively unknown beast, and has a reputation for causing problems. Not that there were no problems, but like with other tools, most often difficulties arise from their inability (RDBMS) to cook.
Cooking with one article does not learn, but one dish is compressible.

I tried to isolate the skeleton, a set of scripts, on the basis of which you can make your PostgreSQL data schemes and test them with pgTAP: github.com/C-Pro/pg_skeleton
And as a nice bonus, I screwed this thing to Travis, so that you also have CI was already at the start :)



For installation, we need:

• PostgreSQL> = 9.2 with dev headers (you have to compile the extension for postgres)
• pgTAP (extension itself)
• pg_prove to run tests

So, in order:

If PostgreSQL is not installed yet - install. If you have Ubuntu or Debian, I recommend connecting their apt.postgresql.org repository (see the connection instructions here: wiki.postgresql.org/wiki/Apt ). I warn you right away - on Ubuntu 13 they do not have packages, they focus on LTS releases.

Download and install the latest version of pgTAP - a framework for testing everything and everything in PostgreSQL: pgtap.org Unpack the

archive, then as usual:

make && sudo make install

The pgtap extension should now be available to the postgres server.

Install pg_prove - perl utility to run pgTAP tests

sudo cpan TAP::Parser::SourceHandler::pgTAP

Everything, you can download and install pg_skeleton:

git clone https://github.com/C-Pro/pg_skeleton.git
cd pg_skeleton
cp install.cfg.example install.cfg
./install.sh

It will ask you to enter the desired password for the user - the owner of the created database and the postgres user.

Now run the tests:

cdtest
./run_tests.sh

If you saw the magic word PASS - everything is great, you can disassemble the skeleton by bones. That is, by file.

• .gitignore - as you probably already guessed - a list of file masks ignored by git
• .travis.yml - configuration file for travis, which describes how to install the project and run tests. When I do git push to this repository, travis runs the tests and checks if something has broken. The travis build looks like this: travis-ci.org/C-Pro/pg_skeleton# As you can see from the history of builds and commits in the git, I had to torment myself to deploy the necessary postgres to travis, install the extension, pg_prove and run the tests.
• create_db.sql - parameterized user creation script and database
• drop_db.sql - parameterized database and user deletion script
• extensions.sql - script to install the required extensions when installing the scheme
• install.cfg - settings: postgres server address, names of the created database and user. Git is ignored so that you can have different settings when deploying to your machine and different servers without conflicts in git.
• install.cfg.example - configuration file template
• install.sql - sql script to install the database. It includes all other sql scripts to create a data scheme.
• uninstall.sh - executable script to remove the database and user

Each subfolder is named for the schema that it contains.

The test_user folder contains scripts for creating the test_user schema (this is just an example of a schema) with one table and several example functions.

• create_tables.sql - in each scheme folder there is such a file. It contains DDL for creating schema objects.
• create_functions.sql - a script containing the schema features.
• users_crud.sql - there can be many functions, so they are allocated to different files that are connected in the create_functions.sql file with the \ i command (of type include).

This division into separate files will save nerves in the future. Having separated the creation of tables, foreign constraints, functions, views, etc. in different files, you can include them in install.sql in the correct order without falling into the trap of interdependencies like these:

createtable a (x intreferences b.y);
  createtable b (y intreferences a.x);

The test folder also creates a schema, but this is a special schema, and it only lives while tests are running.
setup.sql is designed to load f-th and test data into the test temporary circuit before running the tests. (phew, how many words are the test :)
run_tests.sh with the help of pg_prove executes the files ./tests/run_<name>.sh
run_ <name> .sh one by one per scheme.
First, they include the setup.sql file - which loads the definitions of the test functions, the auxiliary function test.test_scheme_check_func and the test data from the file test_data.sh. Then tests are run that can be in multiple files that just connect to run_ <name> .sh. After all the tests in the circuit, test.test_scheme_check_func is executed. This f-th itself is a pgTAP test, which fails if there are non-covered f-ts in the circuit. The determination is based on the test comment. The comment should begin with the name of the test f-s. Of course, overloaded files with the same names may be uncovered, but this is better than no control over test coverage. After the tests are completed, rollback occurs - all created objects and loaded test data are deleted.

Well, that's probably all for now.
I confess, it turned out to be confusing - ask what is incomprehensible.
Use, understand, fork!