def dump_all(self):
print 'backup...'
conn_info = db.get_con_info(self.CONNECTION_ID)
if conn_info is None:
print "cannot find connection for " + self.CONNECTION_ID
exit()
tables = [
'annotation',
'annotation_type',
'gid2source_id',
'gid2terms',
'homologene',
'id_type',
'interaction',
'interaction_type',
'taxid2name',
'term',
'term_category',
'term2gids',
'term2term',
'statistics',
]
cmd = [
'mysqldump', '--host ' + conn_info["HOST"],
'-u ' + conn_info["USR"], '-p' + conn_info["PWD"], self.DATABASE,
' '.join(tables), '>' + self.OUTPUT
]
print cmd
util.unix(cmd)
def restore(self):
print 'restoring...'
if len(self.RESTORE_DB) == 0:
print "You need to specify a database name to be restored using -r option."
exit()
if len(self.INPUT) == 0:
print "You need to specify a .sql file name -i option."
exit()
conn_info = db.get_con_info(self.CONNECTION_ID)
if conn_info is None:
print "cannot find connection for " + self.CONNECTION_ID
exit()
cmd = [
'mysql',
'--host ' + conn_info["HOST"],
'-u ' + conn_info["USR"],
'-p' + conn_info["PWD"],
'-e',
'"create database {0}"'.format(self.RESTORE_DB),
]
print ' '.join(cmd)
util.unix(cmd)
cmd = [
'mysql', '--host ' + conn_info["HOST"], '-u ' + conn_info["USR"],
'-p' + conn_info["PWD"], self.RESTORE_DB, '<' + self.INPUT
]
print ' '.join(cmd)
util.unix(cmd)
def dump_schema(self):
tables_with_data = [
'annotation', 'annotation_type', 'gid2source_id', 'gid2terms',
'homologene', 'id_type', 'interaction', 'interaction_type',
'server_status', 'session', 'statistics', 'taxid2name', 'term',
'term2gids', 'term2term', 'term_category'
]
conn_info = db.get_con_info(self.CONNECTION_ID)
if conn_info is None:
print "cannot find connection for " + self.CONNECTION_ID
exit()
metascape_db_in_xxx = 'gp_metascape'
#todo uncomment next line
if len(tables_with_data):
cmd = [
'mysqldump', '--host ' + conn_info["HOST"],
'-u ' + conn_info["USR"], '-p' + conn_info["PWD"],
metascape_db_in_xxx, ' '.join(tables_with_data),
'>' + self.OUTPUT + "_P2"
]
print " ".join(cmd)
util.unix(cmd)
util.unix("cat " + self.OUTPUT + "_P2 >> " + self.OUTPUT)
util.unix("rm " + self.OUTPUT + "_P2")
def sync_category_tables(self, dest_db):
print 'synchronizing category tables of the gp_devel with the production gp.'
con_prod = db.get_con_info("MYSQL01_RW")
cmd = [
'mysqldump', '--host ' + con_prod["HOST"], '-u ' + con_prod["USR"],
'-p' + con_prod["PWD"], 'gp',
' '.join(['annotation_type', 'id_type',
'term_category']), '>category_tables.sql'
]
print ' '.join(cmd)
util.unix(cmd)
def do_update(self):
dir = os.path.join(SyncDB.UPLOAD_DIR(),self.dest)
cmd = "find %s -name '*.csv' -print0 |xargs -0 tail -n +2 -q > %s" % (dir, self.fn_dest)
print cmd
s = util.unix(cmd)
conn_info = db.get_con_info(SyncDB.CONNECTION_ID)
if conn_info is None:
print "cannot find connection for " + SyncDB.CONNECTION_ID
exit()
#Tracer()()
print s
cols = self.cols.split(',')
if 'tax_id' in cols:
df = util.read_csv(self.fn_dest, names=self.cols.split(','))
df['tax_id'] = df['tax_id'].map(str)
f = lambda x: SyncDB.PARENT_SPECIE_MAP[x] if x in SyncDB.PARENT_SPECIE_MAP else x
df['tax_id'] = df['tax_id'].map(f)
df.to_csv(self.fn_dest, index=False, header=False)
#for interaction table.
if 'tax_id_A' in cols:
df = util.read_csv(self.fn_dest, names=self.cols.split(','))
df['tax_id_A'] = df['tax_id_A'].map(str)
df['tax_id_B'] = df['tax_id_B'].map(str)
f = lambda x: SyncDB.PARENT_SPECIE_MAP[x] if x in SyncDB.PARENT_SPECIE_MAP else x
df['tax_id_A'] = df['tax_id_A'].map(f)
df['tax_id_B'] = df['tax_id_B'].map(f)
df.to_csv(self.fn_dest, index=False, header=False)
mysql_cmd = [
'mysqlimport',
'--local',
'--delete',
'--host ' + conn_info["HOST"],
'-u ' + conn_info["USR"],
'--password='******'--fields-terminated-by=,',
'--fields-optionally-enclosed-by="\\""',
'--ignore-lines=0',
'--columns=%s' % self.cols,
SyncDB.DATABASE,
self.fn_dest]
c = " ".join(mysql_cmd)
print c
s = util.unix(mysql_cmd)
def dump_data_tables(self):
conn_info = db.get_con_info(self.CONNECTION_ID)
if conn_info is None:
print "cannot find connection for " + self.CONNECTION_ID
exit()
tables = [
'annotation', 'gid2source_id', 'gid2terms', 'homologene', 'term',
'term2gids', 'term2term'
]
cmd = [
'mysqldump', '--host ' + conn_info["HOST"],
'-u ' + conn_info["USR"], '-p' + conn_info["PWD"], self.DATABASE,
' '.join(tables), '>' + self.OUTPUT
]
util.unix(cmd)
print " ".join(cmd)