示例#1
0
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()
示例#2
0
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;"
示例#3
0
 def execute_and_map(self, ddlstmt):
     "Execute a DDL statement, commit, and return a map of the database"
     self.execute(ddlstmt)
     self.conn.commit()
     db = Database(DbConnection(self.name, self.user, host=self.host,
                                port=self.port))
     return db.to_map()
示例#4
0
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()
示例#5
0
    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()
示例#6
0
    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()
示例#7
0
def main(host='localhost', port=5432, schema=None):
    """Convert database table specifications to YAML."""
    parser = OptionParser("usage: %prog [options] dbname")
    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('-n', '--schema', dest='schema',
                     help="only for named schema (default %default)")
    parser.add_option('-t', '--table', dest='tablist', action='append',
                     help="only for named tables (default all)")

    parser.set_defaults(host=host, port=port, username=os.getenv("USER"),
                        schema=schema)
    (options, args) = parser.parse_args()
    if len(args) > 1:
        parser.error("too many arguments")
    elif len(args) != 1:
        parser.error("database name not specified")
    dbname = args[0]

    db = Database(DbConnection(dbname, options.username, options.host,
                               options.port))
    dbmap = db.to_map()
    # trim the map of schemas/tables not selected
    if options.schema:
        skey = 'schema ' + options.schema
        for sch in dbmap.keys():
            if sch != skey:
                del dbmap[sch]
    if options.tablist:
        ktablist = ['table ' + tbl for tbl in options.tablist]
        for sch in dbmap.keys():
            for tbl in dbmap[sch].keys():
                if tbl not in ktablist:
                    del dbmap[sch][tbl]
            if not dbmap[sch]:
                del dbmap[sch]

    print yaml.dump(dbmap, default_flow_style=False)
示例#8
0
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
示例#9
0
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()
示例#10
0
def main(host='localhost', port=5432, schema=None):
    """Convert database table specifications to YAML."""
    parser = ArgumentParser(parents=[parent_parser()],
                            description="Extract the schema of a PostgreSQL "
                            "database in YAML format")
    parser.add_argument('-n', '--schema',
                        help="only for named schema (default %(default)s)")
    parser.add_argument('-t', '--table', dest='tablist', action='append',
                     help="only for named tables (default all)")

    parser.set_defaults(host=host, port=port, username=os.getenv("USER"),
                        schema=schema)
    args = parser.parse_args()

    db = Database(DbConnection(args.dbname, args.username, args.password,
                               args.host, args.port))
    dbmap = db.to_map()
    # trim the map of schemas/tables not selected
    if args.schema:
        skey = 'schema ' + args.schema
        for sch in dbmap.keys():
            if sch[:7] == 'schema ' and sch != skey:
                del dbmap[sch]
    if args.tablist:
        ktablist = ['table ' + tbl for tbl in args.tablist]
        for sch in dbmap.keys():
            if sch[:7] == 'schema ':
                for tbl in dbmap[sch].keys():
                    if tbl not in ktablist:
                        del dbmap[sch][tbl]
                if not dbmap[sch]:
                    del dbmap[sch]

    if args.output:
        fd = args.output
        sys.stdout = fd
    print(yaml.dump(dbmap, default_flow_style=False))
    if args.output:
        fd.close()
示例#11
0
def main(host='localhost', port=5432, schema=None):
    """Convert database table specifications to YAML."""
    parser = ArgumentParser(parents=[parent_parser()],
                            description="Extract the schema of a PostgreSQL "
                            "database in YAML format")
    parser.add_argument('-n', '--schema',
                        help="only for named schema (default %(default)s)")
    parser.add_argument('-t', '--table', dest='tablist', action='append',
                     help="only for named tables (default all)")

    parser.set_defaults(host=host, port=port, username=os.getenv("USER"),
                        schema=schema)
    args = parser.parse_args()

    pswd = (args.password and getpass.getpass() or '')
    db = Database(args.dbname, args.username, pswd, args.host, args.port)
    dbmap = db.to_map([args.schema], args.tablist)
    if args.output:
        fd = args.output
        sys.stdout = fd
    print(yaml.dump(dbmap, default_flow_style=False))
    if args.output:
        fd.close()
示例#12
0
def main(host='localhost', port=5432, schema=None):
    """Convert database table specifications to YAML."""
    parser = ArgumentParser(parents=[parent_parser()],
                            description="Extract the schema of a PostgreSQL "
                            "database in YAML format")
    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(host=host, port=port, schema=schema,
                        username=os.getenv("PGUSER") or os.getenv("USER"))
    args = parser.parse_args()

    pswd = (args.password and getpass.getpass() or None)
    db = Database(args.dbname, args.username, pswd, args.host, args.port)
    dbmap = db.to_map(schemas=args.schemas, tables=args.tables,
                      exclude_schemas=args.excl_schemas,
                      exclude_tables=args.excl_tables)

    print(yaml.dump(dbmap, default_flow_style=False),
          file=args.output or sys.stdout)

    if args.output:
        args.output.close()
示例#13
0
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()
示例#14
0
 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)
示例#15
0
 def database(self):
     """The Pyrseas Database instance"""
     return Database(self.cfg)
示例#16
0
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()