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") parser.add_argument('--quote-reserved', action='store_true', help="quote SQL reserved words") 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: inmap = yaml.safe_load(options.spec) 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 main(host='localhost', port=5432): """Convert YAML specifications to database DDL.""" parser = OptionParser("usage: %prog [options] dbname yamlspec") parser.add_option('-H', '--host', dest='host', help="database server host or socket directory " "(default %default)") parser.add_option('-p', '--port', dest='port', type='int', help="database server port (default %default)") parser.add_option('-U', '--username', dest='username', help="database user name (default %default)") parser.add_option('-1', '--single-transaction', action='store_true', dest='onetrans', help="wrap commands in BEGIN/COMMIT") parser.set_defaults(host=host, port=port, username=os.getenv("USER")) (options, args) = parser.parse_args() if len(args) > 2: parser.error("too many arguments") elif len(args) != 2: parser.error("missing arguments") dbname = args[0] yamlspec = args[1] db = Database(DbConnection(dbname, options.username, options.host, options.port)) stmts = db.diff_map(yaml.load(open(yamlspec))) if stmts: if options.onetrans: print "BEGIN;" print ";\n".join(stmts) + ';' if options.onetrans: print "COMMIT;"
def main(host='localhost', port=5432): """Convert YAML specifications to database DDL.""" parser = ArgumentParser(parents=[parent_parser()], description="Generate SQL statements to update a " "PostgreSQL database to match the schema specified" " in a YAML file") 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('-n', '--schema', dest='schlist', action='append', help="only for named schemas (default all)") parser.set_defaults(host=host, port=port, username=os.getenv("USER")) args = parser.parse_args() dbconn = DbConnection(args.dbname, args.username, args.password, args.host, args.port) db = Database(dbconn) inmap = yaml.load(args.spec) if args.schlist: kschlist = ['schema ' + sch for sch in args.schlist] for sch in inmap.keys(): if sch not in kschlist: del inmap[sch] stmts = db.diff_map(inmap, args.schlist) if args.output: fd = args.output sys.stdout = fd if stmts: if args.onetrans or args.update: print("BEGIN;") print(";\n".join(stmts) + ';') if args.onetrans or args.update: print("COMMIT;") if args.update: dbconn.connect() try: for stmt in stmts: dbconn.conn.cursor().execute(stmt) except: dbconn.conn.rollback() raise else: dbconn.conn.commit() print("Changes applied", file=sys.stderr) if args.output: fd.close()
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 process_map(self, input_map): """Process an input map and return the SQL statements necessary to convert the database to match the map.""" db = Database(DbConnection(self.name, self.user, self.host, self.port)) return db.diff_map(input_map)