130 lines
3.8 KiB
ReStructuredText
130 lines
3.8 KiB
ReStructuredText
yamltodb - YAML to Database
|
|
===========================
|
|
|
|
Name
|
|
----
|
|
|
|
yamltodb -- generate SQL statements to update a Postgres database to
|
|
match the schema specified in a YAML file
|
|
|
|
Synopsis
|
|
--------
|
|
|
|
::
|
|
|
|
yamltodb [option...] dbname [spec]
|
|
|
|
Description
|
|
-----------
|
|
|
|
:program:`yamltodb` is a utility for generating SQL statements to
|
|
update a Postgres database so that it will match the schema
|
|
specified in an input `YAML <http://yaml.org>`_ formatted
|
|
specification file.
|
|
|
|
For example, given the input file shown under :doc:`dbtoyaml`,
|
|
:program:`yamltodb`, when run against a newly-created database,
|
|
outputs the following SQL statements::
|
|
|
|
CREATE SCHEMA s1;
|
|
ALTER SCHEMA s1 OWNER TO bob;
|
|
GRANT ALL ON SCHEMA s1 TO bob;
|
|
GRANT ALL ON SCHEMA s1 TO alice;
|
|
CREATE TABLE t1 (
|
|
c1 integer NOT NULL,
|
|
c2 smallint,
|
|
c3 boolean DEFAULT false,
|
|
c4 text);
|
|
ALTER TABLE t1 OWNER TO alice;
|
|
CREATE TABLE s1.t2 (
|
|
c21 integer NOT NULL,
|
|
c22 character varying(16));
|
|
ALTER TABLE s1.t2 OWNER TO bob;
|
|
GRANT ALL ON TABLE s1.t2 TO bob;
|
|
GRANT SELECT ON TABLE s1.t2 TO PUBLIC;
|
|
GRANT INSERT, DELETE, UPDATE ON TABLE s1.t2 TO alice WITH GRANT OPTION;
|
|
GRANT INSERT ON TABLE s1.t2 TO carol;
|
|
ALTER TABLE t1 ADD CONSTRAINT t1_c2_check CHECK (c2 > 123);
|
|
ALTER TABLE t1 ADD CONSTRAINT t1_pkey PRIMARY KEY (c1);
|
|
ALTER TABLE s1.t2 ADD CONSTRAINT t2_pkey PRIMARY KEY (c21);
|
|
ALTER TABLE t1 ADD CONSTRAINT t1_c2_fkey FOREIGN KEY (c2) REFERENCES s1.t2 (c21);
|
|
|
|
Options
|
|
-------
|
|
|
|
:program:`yamltodb` accepts the following command-line arguments (in
|
|
addition to the :doc:`cmdargs`):
|
|
|
|
.. program:: yamltodb
|
|
|
|
**dbname**
|
|
|
|
Specifies the name of the database whose schema is to analyzed.
|
|
|
|
**spec**
|
|
|
|
Specifies the location of the YAML specification. If this is
|
|
omitted or specified as a single or double dash, the specification
|
|
is read from the program's standard input. However, if the
|
|
:option:`--multiple-files` option is used, that takes precedence.
|
|
|
|
.. cmdoption:: -m, --multiple-files
|
|
|
|
Specifies that input should be taken from YAML specification files
|
|
present in a two-level (metadata) directory tree. See `Multiple
|
|
File Output` under :doc:`dbtoyaml` for further details.
|
|
|
|
.. cmdoption:: -n <schema>
|
|
--schema <schema>
|
|
|
|
Compare only a schema matching `schema`. By default, all schemas
|
|
are compared. Multiple schemas can be compared by using multiple
|
|
:option:`-n` switches.
|
|
|
|
.. cmdoption:: -1
|
|
--single-transaction
|
|
|
|
Wrap the generated statements in BEGIN/COMMIT. This ensures that
|
|
either all the statements complete successfully, or no changes are
|
|
applied.
|
|
|
|
.. cmdoption:: -u, --update
|
|
|
|
Execute the generated statements against the database mentioned in
|
|
**dbname**. This implies the :option:`--single-transaction`
|
|
option.
|
|
|
|
.. cmdoption:: --revert
|
|
|
|
Generate SQL in reversion mode, that is, to undo the changes that
|
|
would normally be generated. For example, if without this option,
|
|
the SQL would be a ``DROP TABLE``, the :option:`--revert` option
|
|
generates a ``CREATE TABLE`` with all the columns, constraints and
|
|
other objects associated with the table being dropped.
|
|
|
|
This option is experimental and currently has only been
|
|
implemented for schemas and sequences.
|
|
|
|
Examples
|
|
--------
|
|
|
|
Given a YAML file named ``moviesdb.yaml``, to generate SQL statements
|
|
to update a database called `mymovies`::
|
|
|
|
yamltodb mymovies moviesdb.yaml
|
|
|
|
To generate the statements as above and immediately update `mymovies`::
|
|
|
|
yamltodb mymovies moviesdb.yaml | psql mymovies
|
|
|
|
or::
|
|
|
|
yamltodb --update mymovies moviesdb.yaml
|
|
|
|
To generate the statements directly from the output of
|
|
:program:`dbtoyaml` (against a different database), with statements
|
|
enclosed in a single transaction, and save the statements in a file
|
|
named ``mymovies.sql``::
|
|
|
|
dbtoyaml devmovies | yamltodb -1 mymovies -o mymovies.sql
|