Carol starts by creating the film table in her database, caroldev, using the CREATE TABLE script, version 0.1, which we saw in the original article. She then uses the Pyrseas dbtoyaml tool to ouput the database specification:
$ dbtoyaml caroldev schema public: table film: check_constraints: film_release_year_check: columns: - release_year expression: (release_year >= 1888) columns: - id: not_null: true type: integer - title: not_null: true type: character varying(32) - release_year: not_null: true type: integer primary_key: film_pkey: access_method: btree columns: - id
This should be mostly self-explanatory, but let’s go over it. Indentation indicates structure (nesting) and hyphens are used for each element in a list. Thus, there is one schema (public) and one table (film) defined within it. The table has three columns (id, title and release_year) with the given data types and none of them are nullable. The PRIMARY KEY (film_pkey) is on the id column and there is a CHECK constraint on the release_year column. By the way, the alphabetical order within each level is simply the default output format of PyYAML.
Carol can redirect the output of dbtoyaml to a file, say, film.yaml and store it in a Git repository or some other VCS. Dave will then be able to pull the file from the repository and use it to generate the SQL statements needed to create the table in his database, or the development, test and production databases, using the yamltodb tool as follows:
$ yamltodb -1 davedev film.yaml BEGIN; CREATE TABLE film ( id integer NOT NULL, title character varying(32) NOT NULL, release_year integer NOT NULL); ALTER TABLE film ADD CONSTRAINT film_pkey PRIMARY KEY (id); ALTER TABLE film ADD CONSTRAINT film_release_year_check CHECK (release_year >= 1888); COMMIT;
If he is satisfied with the SQL, he can pipe the output to psql, e.g.,
$ yamltodb -1 davedev film.yaml | psql davedev
For the second version, Dave issues an ALTER TABLE to add the length column, invokes dbtoyaml to create a new film.yaml and commits that to his repository. Carol uses another ALTER TABLE to add the rating column in her database, and stores a new film.yaml in her repository.
Manny, the integration manager, merges the new film.yaml versions into the project-wide repository. He uses yamltodb to output the statements needed to upgrade to version 0.2:
$ git diff 0.1 0.2 diff --git a/film.yaml b/film.yaml index 0633c1b..98f19c4 100644 --- a/film.yaml +++ b/film.yaml @@ -1,6 +1,10 @@ schema public: table film: check_constraints: + film_length_check: + columns: + - length + expression: ((length > 0) AND (length < 10000)) film_release_year_check: columns: - release_year @@ -15,6 +19,10 @@ schema public: - release_year: not_null: true type: integer + - length: + type: smallint + - rating: + type: character(5) primary_key: film_pkey: access_method: btree $ yamltodb moviesdev film.yaml ALTER TABLE film ADD COLUMN length smallint; ALTER TABLE film ADD COLUMN rating character(5); ALTER TABLE film ADD CONSTRAINT film_length_check CHECK ((length > 0) AND (length < 10000));
For the next round, Carol and Dave issue the statements shown under Version 0.3 in the previous article and commit newer versions of film.yaml which Manny integrates into the project repository. The yamltodb output against the project database is as follows:
$ yamltodb -1 film.yaml BEGIN; CREATE SEQUENCE category_category_id_seq START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; CREATE TABLE film_category ( film_id integer NOT NULL, category_id integer NOT NULL, last_update timestamp with time zone NOT NULL); CREATE TABLE category ( category_id integer NOT NULL DEFAULT nextval('category_category_id_seq'::regclass), name character varying(25) NOT NULL, last_update timestamp with time zone NOT NULL); ALTER SEQUENCE category_category_id_seq OWNED BY category.category_id; ALTER TABLE category ADD CONSTRAINT category_pkey PRIMARY KEY (category_id); ALTER TABLE film_category ADD CONSTRAINT film_category_pkey PRIMARY KEY (film_id, category_id); ALTER TABLE film_category ADD CONSTRAINT film_category_category_id_fkey FOREIGN KEY (category_id) REFERENCES category (category_id); ALTER TABLE film_category ADD CONSTRAINT film_category_film_id_fkey FOREIGN KEY (film_id) REFERENCES film (id); COMMIT;
I cannot objectively review the tools I have created, but sincerely hope others will find them useful in managing changes to PostgreSQL databases in conjunction with a VCS.
Pyrseas dbtoyaml and yamltodb require Python, psycopg2 and the PyYAML library. They currently support the basic PostgreSQL features (schemas, tables, PRIMARY KEYs, FOREIGN KEYs, UNIQUE and CHECK constraints, indexes) but the intention is to expand them to cover all features.
The primary audience of the tools are DBAs and developers, but they can be used in conjunction with something like depesz Versioning to implement a stricter system for control over production databases.
I’d like to take this opportunity to thank Adam Cornett, an early adopter/tester, who found an issue with a FOREIGN KEY across schemas, which has been fixed in the master repository.