Blank for the schemas with tests, CI, without preference

Relational database — the beast is relatively unknown, and has a reputation as a generator of problems. Not that problems didn't exist, but as with other tools, most often the difficulties arise from the inability of their (RDBMS) to cook.
To cook with one article not to learn, but one dish sdyuzhil.

I tried out the skeleton, a set of scripts, which you can do your schema in PostgreSQL and test them using pgTAP: github.com/C-Pro/pg_skeleton
And as a nice bonus I screwed the case to Travis, that you even and CI was already at the start :)



For installation we need:
the

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

So, in order:

If PostgreSQL is not already installed — put. If you have Ubuntu or Debian, I recommend to plug in their repository apt.postgresql.org (for instructions on connecting, see here: wiki.postgresql.org/wiki/Apt). Immediately warn — on Ubuntu 13 they have packages there, they are focused on LTS releases.

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

Unpack the archive, continue as usual:
the

make && sudo make install

Now the postgres server should be available to extension pgtap.

Installed pg_prove — perl utility to run the pgTAP tests
the

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

All can be downloaded and put pg_skeleton:
the

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

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

Now run the tests:
the

cd test
./run_tests.sh

If you saw the magic word PASS — everything is great, you can disassemble the skeleton the bones. That is, the files.
the

the
• .gitignore — as You probably guessed — a list of masks of files ignored by git
the
• .travis.yml — the configuration file for travis, which describes how to install the project and run the tests. When I do a git push to this repository, travis runs tests and checks, not broken anything. The travis build looks like this: travis-ci.org/C-Pro/pg_skeleton# As you can see the history of builds and commits in the Gita — had to struggle to deploy postgres on travis you need to install the extension, and pg_prove to run the tests.
the
• create_db.sql — the parameterized script to create user and database
the
• drop_db.sql — the parameterized script to remove the database and user
the
• extensions.sql script to install the required extension with the installation of the schema
the
• install.cfg — settings: server address, postgres, name the created database and user. Is ignored by git so that you could have different settings when it is deployed on their machine and different servers without conflicts in git.
the
• install.cfg.example — the template configuration file
the
• install.sql — sql script to install the base. It includes all the other sql scripts to create the schema.
the
• uninstall.sh — executable script to remove the database and user

Each subfolder has the name of the schema that contains it.

In the test_user folder contains the scripts to create the schema test_user (this is just an example of the diagram) with one table and several examples of functions.
the

the
• create_tables.the sql in each folder schema, there is such a file. It contains the DDL to create schema objects.
the
• create_functions.sql script containing the f-s scheme.
the
• users_crud.sql functions can be many, so they stand out in different files that connect to the file create_functions.sql command \i (of type include).

This partitioning into separate files will save your nerves in the future. Dividing in separate files creating tables, foreign constraints, f-AI, display (views), etc., you can include them in the install.sql in the correct order, without falling into the trap of interdependencies such as these:
create table b (y int references a.x);

The test folder also creates the schema, but this is a special scheme, and she lives only while there are tests.
setup.sql is designed to load f and the test data into a temporary test schema before running tests. (uh, how many words test :)
run_tests.sh using pg_prove one runs the file ./tests/run_<name>.sh
run_<name>.sh created one on the diagram.
First, they connected the setup file.sql which loads defining test functions, auxiliary f-u test.test_scheme_check_func and test data from a file test_data.sh. Then you run your tests, which can be in several files, which are simple to connect to run_<name>.sh. After all the tests in the scheme, runs f-I test.test_scheme_check_func. This f-I is a test of pgTAP, which falls, if the schema are not covered by tests, f-AI. The figure is on the review to the test. The review should begin with the name test f-II. Can certainly be uncovered overloaded f-AI with the same name, but it's better than no control of coverage. After you run tests, rollback occurs — all created objects and loaded the test data is deleted.

Well, that's probably all. bye.
I confess, came out chaotically — ask that is not clear.
Use, understand, forcite!

Article based on information from habrahabr.ru