def backup(): table = get_table() count = 0 for t in table: obj_t = obj_backup(t) obj_t.setColumn() obj_t.setRetention() #remove existing backup for the day sql = 'DELETE FROM ' + obj_t.db_table_m + ' WHERE bk_date = "' + obj_t.bk_date + '";' try: sqlexecute(sql) except ValueError, e: print sqlerror(e) #backup for the day sql = 'INSERT INTO ' + obj_t.db_table_m + '(bk_date,' + obj_t.column + ''') SELECT "''' + obj_t.bk_date + '",' + obj_t.column + ' FROM ' + obj_t.db_table + ';' try: sqlexecute(sql) count = count + 1 print obj_t.db_table + ' has been saved into ' + obj_t.db_table_m + ' for ' + obj_t.bk_date + '.' except ValueError, e: print obj_t.db_table + ' backup failed for ' + obj_t.bk_date + '.' print sql
def load_model_table(tbl, filter, cols):
ids = ''
ids_1062 = '' #duplicate row
add_count = 0
delete_count = 0
modify_count = 0
day = str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
#get staging table
print 'read from %s...' % red('stg_' + tbl)
a = listsplit2(cols)
sql = 'SELECT id, action, status, %s from %s WHERE (status = "" or status IS NULL) AND action <> "";' % (
a, 'stg_' + tbl)
if filter <> 'all' and filter.find('%') == -1:
sql = sql[:-1] + ' AND table_name in (%s);' % listsplit(filter)
elif filter <> 'all' and filter.find('%') <> -1:
sql = sql[:-1] + ' AND table_name like (%s);' % listsplit(filter)
sql = sql[:-1] + ' ORDER BY action DESC;'
stg = sqlexecute(sql)
#insert
if len(stg) == 0:
print 'no new rows to be updated into %s' % red(tbl)
else:
print 'insert into %s...' % red(tbl)
for row in stg:
if row[1] == 'add':
sql = "INSERT INTO %s (%s, last_update_date) VALUES (%s,'%s');" % (
'ledger_' + tbl, a, listsplit3(row[3:], cols), day)
try:
sqlexecute(sql)
ids = ids + str(row[0]) + ','
add_count = add_count + 1
except ValueError, e:
if sqlerror(e) == '1062':
ids_1062 = ids_1062 + str(row[0]) + ','
elif row[1] == 'delete':
sql = 'DELETE FROM %s WHERE project="%s" AND table_name="%s";' % (
'ledger_' + tbl, row[cols.index('project') + 3],
row[cols.index('table name') + 3])
if 'column name' in cols:
sql = sql[:-1] + ' AND column_name="%s";' % row[
cols.index('column name') + 3]
try:
sqlexecute(sql)
ids = ids + str(row[0]) + ','
delete_count = delete_count + 1
except ValueError, e:
sqlerror(e)
def applyDetails(tentative=False, overwrite=False):
"""To-do: Time doing overwrite vs checking and overwriting if new """
global jobtable #make global so that functions like fromJobObject() can access correct one
jobtable = 'tentativejob' if tentative else 'job'
querytable = tentativetable if tentative else defaulttable
newjobs = [
i[0] for i in plotQuery(['jobid'], [], jobtable)
if i[0] > countDB('details')
]
for i in newjobs:
sqlexecute('INSERT INTO details (jobtableid) VALUES (%d)' % i)
for i, d in enumerate(activeDetails):
print d.name, " %d/%d" % (i + 1, len(activeDetails))
try:
addCol(d.name, d.kind, 'details')
except OperationalError:
pass
vals = d.apply(querytable)
ids = allJobIDs()
for ID, val in zip(ids, vals):
if overwrite or (val is not None and query1(
d.name, 'jobtableid', ID, 'details') is None):
updateDB(d.name, 'jobtableid', ID, val, None, 'details')
def load_stg_table(tbl, loc, cols):
try:
with open('%s/%s.csv' % (loc, tbl), 'rb') as csvfile:
day = str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
count = 0
#read csv file
print 'read from ' + red(tbl + '.csv') + '...'
line = csv.DictReader(csvfile, delimiter=',')
#truncate staging table
print 'remove existing records in %s...' % red(tbl)
sql = 'TRUNCATE TABLE %s;' % tbl
sqlexecute(sql)
#insert into staging table
print 'insert new records into %s...' % red(tbl)
for row in line:
a = ""
b = ""
for col in cols:
b = b + '"' + row[col] + '",'
a = a + col.replace(' ', '_') + ","
a = a + "last_update_date"
b = b + '"' + day + '"'
sql = "INSERT INTO %s (%s) VALUES(%s);" "" % (tbl, a, b)
try:
sqlexecute(sql)
count = count + 1
except ValueError, e:
sqlerror(e)
print 'inserted %s rows into %s' % (bold(count), red(tbl))
#rename loaded csv file
os.rename('%s/%s.csv' % (loc, tbl),
'%s/%s_%s.csv' % (loc, tbl, day))
except IOError:
print '%s not exists' % red(tbl + '.csv')
print '\n'
return
def get_table(): sql = '''SELECT project,table_name,retention_d FROM all_tables WHERE backup = "Y" AND project IS NOT NULL AND table_name IS NOT NULL;''' result = sqlexecute(sql) return result
def load_stg_table_online(tbl, cols):
try:
if tbl == 'stg_all_tables':
url = 'https://docs.google.com/spreadsheets/d/1gOiYEl6bsx40Ht2hzul40YeSdy1TKW1EVxnotirvcbc/pub?gid=983104586&single=true&output=csv'
elif tbl == 'stg_all_table_columns':
url = 'https://docs.google.com/spreadsheets/d/1gOiYEl6bsx40Ht2hzul40YeSdy1TKW1EVxnotirvcbc/pub?gid=0&single=true&output=csv'
csvfile = urllib2.urlopen(url)
day = str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
count = 0
#read csv file
print 'read from ' + red(tbl + '.csv') + '...'
line = csv.DictReader(csvfile, delimiter=',')
#truncate staging table
print 'remove existing records in %s...' % red(tbl)
sql = 'TRUNCATE TABLE %s;' % tbl
sqlexecute(sql)
#insert into staging table
print 'insert new records into %s...' % red(tbl)
for row in line:
a, b = ['', '']
for col in cols:
b = b + '"' + row[col] + '",'
a = a + col.replace(' ', '_') + ","
a = a + "last_update_date"
b = b + '"' + day + '"'
sql = "INSERT INTO %s (%s) VALUES(%s);" "" % (tbl, a, b)
try:
sqlexecute(sql)
count = count + 1
except ValueError, e:
sqlerror(e)
print 'inserted %s rows into %s' % (bold(count), red(tbl))
def main():
try: dropTable('tentativejob') # uncomment if script fails in the middle
except: pass
sqlexecute(jobTable('tentativejob'))
copyTable('job','tentativejob')
combos = product(*domains)
nTot = reduce(mul,[len(x) for x in [trajDomain,xcDomain,pspDomain,pwDomain,kptDomain,preXcDomain,dftDomain]])
ncombo = reduce(mul,[len(x) for x in domains])
for i,c in enumerate(combos):
print '%f %%'%(i/float(ncombo)*100)
# jobid jobkind aseid vib neb xc pw kpt psp xtol strain convergence ID precalc dft comm err stat
tentativeInput = [None,'bulkrelax',c[0],None,None,c[1],c[2],c[3],c[4],0.005,0.03, 2 if c[1]=='mBEEF' else 1,c[5],c[6],None,None,'initialized']
tentativejob = Job(*tentativeInput)
insertObject(tentativejob,tentative=True)
nJobsInit,nJobsTent = countDB('job'),countDB('tentativejob')
print "%d tentative jobs after filtering %d jobs"%(ncombo,nTot)
applyDetails(tentative=True) #populate details table so that plotQuery works
valid = [db2object(x[0],'tentativejob') for x in plotQuery(['jobid'],CONSTRAINTS,tentativetable)]
new = 0
question = "Do you want to insert %d valid bulk jobs?\n(y/n)--> "%len(valid)
if raw_input(question) in ['y','yes']:
for z in range(len(valid)):
ID,status = insertObject(valid[z])
if status == 'inserted': new +=1
print "%d jobs are new"%new
applyDetails()
dropTable('tentativejob')
def get_column(project,table): sql = '''SELECT column_name FROM all_table_columns WHERE project = "''' + project + '''" AND table_name = "''' + table + '";' tmp = sqlexecute(sql) result = '' for t in tmp: result = result + t[0] + ',' return result[:-1]
def write_model_file(p, f, t, tbl, col):
#get tables
sql = 'SELECT %s FROM ledger_all_tables WHERE project IS NOT NULL AND model_file_name IS NOT NULL AND table_name is NOT NULL;' % listsplit2(
tbl)
if p <> 'all': sql = sql[:-1] + " AND project in (%s);" % listsplit(p)
if f <> 'all':
sql = sql[:-1] + " AND model_file_name in (%s);" % listsplit(f)
if t <> 'all': sql = sql[:-1] + " AND table_name in (%s);" % listsplit(t)
sql = sql[:-1] + ' ORDER BY project, model_file_name, table_name;'
try:
models = sqlexecute(sql)
except ValueError, e:
print sqlerror(e)
def setColumn(self): sql = '''SELECT column_name,foreign_key_table FROM all_table_columns WHERE project = "''' + self.project + '''" AND table_name = "''' + self.table + '";' tmp = sqlexecute(sql) result = '' for t in tmp: if t[1] == '': result = result + t[0] + ',' else: result = result + t[0] + '_id,' self.column = result[:-1]
def main():
dropTable('tentativejob')
dropTable('tentativedetails')
sqlexecute(jobTable('tentativejob'))
sqlexecute('INSERT INTO tentativejob SELECT * FROM job')
sqlexecute(tentativedetailstable)
for i,sID in enumerate(surfs):
p = asedb.get(sID).get('parent')
cols = ['xc','pw','kptden','psp','convid','precalc','dftcode']
allInfo = queryManyFromOne(cols,'aseid',p)
xc,pw,kptden,psp,convid,precalc,dftcode = allInfo
tentativeInput = [None,'surfrelax',sID,None,None,xc,pw,kptden,psp,None,None,convid,precalc,dftcode,None,None,'initialized']
tentativeSurfjob = Job(*tentativeInput)
insertObject(tentativeSurfjob,tentative=True)
applyDetails(tentative=True) #populate details table so that plotQuery works
valid = [db2object(x[0],'tentativejob','job') for x in plotQuery(['jobid'],constraints,tentativetable)]
new = 0
print "%d valid jobs, %d are new"%(len(valid),new)
question = "Do you want to insert %d new surface jobs?"%new
if raw_input(question) in ['y','yes']:
for z in range(len(valid)):
ID,status = insertObject(valid[z])
if status == 'inserted': new +=1
print "%d jobs are new"%new
applyDetails()
dropTable('tentativejob')
dropTable('tentativedetails')
def validate_model_table(file):
ind = False
#validate if all tables have columns
sql = 'SELECT distinct project,model_file_name,table_name FROM ledger_all_tables;'
stg = sqlexecute(sql)
if file <> 'all' and file.find('%') == -1:
sql = sql[:-1] + ' AND model_file_name in (%s);' % listsplit(file)
elif file <> 'all' and file.find('%') <> -1:
sql = sql[:-1] + ' AND model_file_name like (%s);' % listsplit(file)
for row in stg:
sql = "SELECT count(*) FROM ledger_all_table_columns WHERE project='%s' AND model_file_name='%s' AND table_name='%s';" % (
row[0], row[1], row[2])
if int(sqlexecute(sql)[0][0]) == 0:
ind = True
print 'table ' + red(row[2]) + ' in ' + red(
'%s/%s' % (row[0], row[1])) + ' found no columns'
#validate unique key
sql = "SELECT project,model_file_name,table_name,column_name,max_length FROM ledger_all_table_columns WHERE unique_key='Y' AND data_type='varchar';"
if file <> 'all' and file.find('%') == -1:
sql = sql[:-1] + ' AND model_file_name in (%s);' % listsplit(file)
elif file <> 'all' and file.find('%') <> -1:
sql = sql[:-1] + ' AND model_file_name like (%s);' % listsplit(file)
stg = sqlexecute(sql)
for row in stg:
if int(row[4]) > 255:
ind = True
print 'table ' + red(row[2]) + ' in ' + red(
'%s/%s' % (row[0], row[1])
) + ' has length=%s' % row[
4] + ', the maximun length allowed for a unique key is 255 for a varchar'
#validate decimal data type
sql = "SELECT project,model_file_name,table_name,column_name,max_length,decimal_place FROM ledger_all_table_columns WHERE data_type='float';"
if file <> 'all' and file.find('%') == -1:
sql = sql[:-1] + ' AND model_file_name in (%s);' % listsplit(file)
elif file <> 'all' and file.find('%') <> -1:
sql = sql[:-1] + ' AND model_file_name like (%s);' % listsplit(file)
stg = sqlexecute(sql)
for row in stg:
try:
int(row[4])
except ValueError:
print 'table ' + red(row[2]) + ' in ' + red(
'%s/%s' %
(row[0], row[1])) + ' has invalid max_length=%s' % row[4]
ind = True
try:
int(row[5])
except ValueError:
print 'table ' + red(row[2]) + ' in ' + red(
'%s/%s' %
(row[0], row[1])) + ' has invalid decimal_place=%s' % row[5]
ind = True
if ind == False: print 'no errors'
print '\n'
return
delete_count = delete_count + 1
except ValueError, e:
sqlerror(e)
elif row[1] == 'modify':
sql = 'UPDATE %s SET %s, last_update_date = "%s" WHERE project="%s" AND table_name="%s";' % (
'ledger_' + tbl, listsplit4(
row, cols), day, row[cols.index('project') + 3],
row[cols.index('table name') + 3])
if 'column name' in cols:
sql = sql[:-1] + ' AND column_name="%s";' % row[
cols.index('column name') + 3]
try:
sqlexecute(sql)
ids = ids + str(row[0]) + ','
modify_count = modify_count + 1
except ValueError, e:
sqlerror(e)
#update staging table status
sql = 'UPDATE stg_%s SET status = "DONE" WHERE id in (%s);' % (
tbl, listsplit(ids))
sqlexecute(sql)
sql = 'UPDATE stg_%s SET status = "DUPLICATE" WHERE id in (%s);' % (
tbl, listsplit(ids_1062))
sqlexecute(sql)
print 'added %s rows' % bold(str(add_count))
,status varchar
,bulk varchar
,calcid integer
,precalcxc varchar
,dft varchar
,timelim numeric
,foreign key(calcid) references calc(id)
,foreign key(bulk) references bulk(pth))""")
commands.append("""CREATE TABLE bulkresult
(id integer primary key
,jobid integer
,a numeric
,b numeric
,c numeric
,alpha numeric
,beta numeric
,gamma numeric
,pos varchar
,magmom varchar
,energy numeric
,bulkmodulus numeric
,bfit numeric
,xccoeffs varchar
,time numeric
,niter integer
,foreign key(jobid) references bulkjob(id))""")
for i, command in enumerate(commands):
sqlexecute(command)
def copyToTentative():
command = ("insert into tentativejob "
+ " (jobid, jobkind,aseidinitial,vibids,nebids,xc,pw,kptden,psp,xtol,strain,convid,precalc,dftcode,comments,error,status) "
+ " select jobid, jobkind,aseidinitial,vibids,nebids,xc,pw,kptden,psp,xtol,strain,convid,precalc,dftcode,comments,error,status "
+ " from job")
sqlexecute(command)
def resetDetails():
dropTable('details')
sqlexecute(detailstable)
def main():
for command in commands:
sqlexecute(command)
def main():
for i, command in enumerate(commands):
sqlexecute(command)
try: sqlexecute(sql) except ValueError, e: print sqlerror(e) #backup for the day sql = 'INSERT INTO ' + obj_t.db_table_m + '(bk_date,' + obj_t.column + ''') SELECT "''' + obj_t.bk_date + '",' + obj_t.column + ' FROM ' + obj_t.db_table + ';' try: sqlexecute(sql) count = count + 1 print obj_t.db_table + ' has been saved into ' + obj_t.db_table_m + ' for ' + obj_t.bk_date + '.' except ValueError, e: print obj_t.db_table + ' backup failed for ' + obj_t.bk_date + '.' print sql #print obj_t.db_table + ' backup failed. Error message: ' + sqlerror(e) #remove expired backups sql = 'DELETE FROM ' + obj_t.db_table_m + ' WHERE bk_date NOT IN ' + obj_t.retention + ';' try: sqlexecute(sql) print obj_t.db_table_m + ' cleanup done.' except ValueError, e: print obj_t.db_table + ' cleanup failed. Error message: ' + sqlerror(e) print '\n' backup()
