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 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()
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 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 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)
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(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()
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()
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()
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()
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)
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()