def setUpClass(cls): import yaml from pyrseas.database import Database cls.pgdb = PostgresDb(TEST_DBNAME, TEST_USER, TEST_HOST, TEST_PORT) cls.pgdb.connect() db = Database(TEST_DBNAME, TEST_USER, None, TEST_HOST, TEST_PORT) class Opts: pass opts = Opts() opts.schemas = [] opts.quote_reserved = False stmts = db.diff_map(yaml.load(open(YAML_SPEC)), opts) for stmt in stmts: cls.pgdb.execute(stmt) cls.pgdb.conn.commit()
def generate_init_sql(schema, config): class Opts: pass opts = Opts() opts.schemas = ["public"] opts.revert = False cfg = { "database": { "dbname": config["dbname"], "host": config["host"], "port": config["port"], "username": config["user"], "password": config["password"] }, "options": opts } db = Database(cfg) stmts = db.diff_map(schema) return stmts
def main(): """Convert YAML specifications to database DDL.""" parser = cmd_parser( "Generate SQL statements to update a PostgreSQL " "database to match the schema specified in a " "YAML-formatted file(s)", __version__) parser.add_argument('-m', '--multiple-files', action='store_true', help='input from multiple files (metadata directory)') parser.add_argument('spec', nargs='?', type=FileType('r'), default=sys.stdin, help='YAML specification') parser.add_argument('-1', '--single-transaction', action='store_true', dest='onetrans', help="wrap commands in BEGIN/COMMIT") parser.add_argument('-u', '--update', action='store_true', help="apply changes to database (implies -1)") parser.add_argument('--revert', action='store_true', help="generate SQL to revert changes (experimental)") parser.add_argument('-n', '--schema', metavar='SCHEMA', dest='schemas', action='append', default=[], help="process only named schema(s) (default all)") cfg = parse_args(parser) output = cfg['files']['output'] options = cfg['options'] db = Database(cfg) if options.multiple_files: inmap = db.map_from_dir() else: try: inmap = yaml.safe_load(options.spec) except Exception as exc: print("Unable to process the input YAML file") print("Error is '%s'" % exc) return 1 stmts = db.diff_map(inmap) if stmts: fd = output or sys.stdout if options.onetrans or options.update: print("BEGIN;", file=fd) for stmt in stmts: if isinstance(stmt, tuple): outstmt = "".join(stmt) + '\n' else: outstmt = "%s;\n" % stmt if PY2: outstmt = outstmt.encode('utf-8') print(outstmt, file=fd) if options.onetrans or options.update: print("COMMIT;", file=fd) if options.update: try: for stmt in stmts: if isinstance(stmt, tuple): # expected format: (\copy, table, from, path, csv) db.dbconn.copy_from(stmt[3], stmt[1]) else: db.dbconn.execute(stmt) except: db.dbconn.rollback() raise else: db.dbconn.commit() print("Changes applied", file=sys.stderr) if output: output.close()
def database(self): """The Pyrseas Database instance""" return Database(self.cfg)
def main(schema=None): """Convert database table specifications to YAML.""" parser = cmd_parser( "Extract the schema of a PostgreSQL database in " "YAML format", __version__) parser.add_argument('-m', '--multiple-files', action='store_true', help='output to multiple files (metadata directory)') parser.add_argument('-O', '--no-owner', action='store_true', help='exclude object ownership information') parser.add_argument('-x', '--no-privileges', action='store_true', dest='no_privs', help='exclude privilege (GRANT/REVOKE) information') group = parser.add_argument_group("Object inclusion/exclusion options", "(each can be given multiple times)") group.add_argument('-n', '--schema', metavar='SCHEMA', dest='schemas', action='append', default=[], help="extract the named schema(s) (default all)") group.add_argument('-N', '--exclude-schema', metavar='SCHEMA', dest='excl_schemas', action='append', default=[], help="do NOT extract the named schema(s) " "(default none)") group.add_argument('-t', '--table', metavar='TABLE', dest='tables', action='append', default=[], help="extract the named table(s) (default all)") group.add_argument('-T', '--exclude-table', metavar='TABLE', dest='excl_tables', action='append', default=[], help="do NOT extract the named table(s) " "(default none)") parser.set_defaults(schema=schema) cfg = parse_args(parser) output = cfg['files']['output'] options = cfg['options'] if options.multiple_files and output: parser.error("Cannot specify both --multiple-files and --output") db = Database(cfg) dbmap = db.to_map() if not options.multiple_files: print(yamldump(dbmap), file=output or sys.stdout) if output: output.close()