def _parse_results(file):
db = lib.db.db()
db.params['dbname'] = 'databases/d2bfs.db'
db.params['table'] = 'results'
db.connect()
lines = open(file, 'r').readlines()
for line in lines:
if '[ssh]' in line and 'host:' in line:
line = line.split()
host = line[2]
user = line[4]
passwd = line[6]
db.select(
'SELECT * FROM ' + db.params['table'] +
' WHERE host=? AND user=? AND pass=?', (
host,
user,
passwd,
))
if db.params['selects']:
continue
else:
db.params['cursor'].execute(
'INSERT INTO ' + db.params['table'] +
' VALUES (NULL, ?, ?, ?)', (
host,
user,
passwd,
))
db.params['connex'].commit()
db.close()
def test_connect(self):
db = self.db(connect='host:server | user:username | password:secret')
db.connect()
args = db._dbapi.connect_kwargs
self.assertTrue('host' in args)
self.assertEqual('server', args['host'])
self.assertTrue('user' in args)
self.assertEqual('username', args['user'])
self.assertTrue('password' in args)
self.assertEqual('secret', args['password'])
def test_connect(self):
db = self.db(connect='host:server | user:username | password:secret')
db.connect()
args = db._dbapi.connect_kwargs
self.assertTrue('host' in args)
self.assertEqual('server', args['host'])
self.assertTrue('user' in args)
self.assertEqual('username', args['user'])
self.assertTrue('password' in args)
self.assertEqual('secret', args['password'])
def insert_parasites(raw_data, to_taxon_id):
"""Insert parasites."""
log(f'Inserting {DATASET_ID} parasites')
parasite_records = []
for _, row in raw_data.iterrows():
parasite_records.append(parasite_record(
row, to_taxon_id['lice'], 'lice_total',
['Ecomorph Notes', 'lice ages']))
parasite_records.append(parasite_record(
row, to_taxon_id['feather_mites'], 'feather_mites_total'))
parasite_records.append(parasite_record(
row, to_taxon_id['ticks'], 'ticks_total'))
parasite_records.append(parasite_record(
row, to_taxon_id['flies'], 'flies_total'))
parasite_records.append(
parasite_record(row, to_taxon_id['fleas'], 'fleas_total'))
parasite_records.append(
parasite_record(row, to_taxon_id['others'], 'others'))
parasites = pd.DataFrame(parasite_records)
parasites['parasite_id'] = db.get_ids(parasites, 'parasites')
parasites.loc[:, db.PARASITE_COLUMNS].to_sql(
'parasites', db.connect(), if_exists='append', index=False)
def insert_counts(counts, to_event_id, to_taxon_id):
"""Insert counts."""
in_species = counts['SCIENTIFIC_NAME'].isin(to_taxon_id)
has_event = counts['SAMPLING_EVENT_IDENTIFIER'].isin(to_event_id)
counts = counts.loc[in_species & has_event, :].copy()
if counts.shape[0] == 0:
return
counts['count_id'] = db.create_ids(counts, 'counts')
counts['event_id'] = counts.SAMPLING_EVENT_IDENTIFIER.map(to_event_id)
counts['taxon_id'] = counts.SCIENTIFIC_NAME.map(to_taxon_id)
counts['dataset_id'] = DATASET_ID
counts = counts.rename(columns={'OBSERVATION_COUNT': 'count'})
counts.loc[counts['count'] == 'X', 'count'] = '-1'
counts['count'] = pd.to_numeric(counts['count'], errors='coerce')
fields = """SCIENTIFIC_NAME GLOBAL_UNIQUE_IDENTIFIER LAST_EDITED_DATE
TAXONOMIC_ORDER CATEGORY SUBSPECIES_SCIENTIFIC_NAME
BREEDING_BIRD_ATLAS_CODE BREEDING_BIRD_ATLAS_CATEGORY AGE_SEX
OBSERVER_ID HAS_MEDIA SPECIES_COMMENTS""".split()
counts['count_json'] = util.json_object(counts, fields)
print(counts.shape)
counts.loc[:, db.COUNT_FIELDS].to_sql('counts',
db.connect(),
if_exists='append',
index=False)
def ingest_werner_data():
"""Ingest the Werner Excel sheet stored on Google drive."""
cxn = db.connect()
werner = read_werner_data()
taxonomy = read_taxon_data(cxn)
sci_names = taxonomy.sci_name.tolist()
is_sci_name = werner.sci_name.isin(sci_names)
synonyms = get_synonyms(taxonomy)
is_synonym = werner.sci_name.isin(synonyms)
update_it = ~is_sci_name & is_synonym
werner.loc[update_it, 'sci_name'] = \
werner.loc[update_it, 'sci_name'].apply(lambda x: synonyms[x])
dups = werner.sci_name.duplicated()
werner = werner.loc[~dups, :]
werner['genus'] = werner.sci_name.str.split().str[0]
werner['genus'] = werner['genus'].map(GENUS_REPLACE)
create_werner_data_table(cxn, werner)
def find_requests_and_send_mail(config):
os.chdir(os.path.dirname(os.path.realpath(__file__)))
db = DB.connect(db_file='../db/main.db')
now = time.time()
what_we_be_done_with = db['c'].execute(
'select * from reminders where end_time < %d' % now).fetchall()
for row in DB.map(what_we_be_done_with, 'reminders', db=db):
row['link'] = "http://indycast.net/%s/slices/%s_%d" % (
row['station'],
time.strftime("%Y%m%d%H%M",
time.gmtime(row['start_time'] - row['offset'] * 60)),
(row['end_time'] - row['start_time']) / 60)
if len(row['notes']):
row['link'] += '/%s_on_%s' % (re.sub(
'[^\w]', '_', row['notes']).strip('_'), row['station'])
row['link'] += '.mp3'
email = do_template(template_file='email_reminder_template.txt',
settings=row)
res = misc.send_email(config=config,
who=row['email'],
subject=email['subject'],
body=email['body'])
db['c'].execute('delete from reminders where id = %d' % row['id'])
db['conn'].commit()
return None
def main(cfg):
global api_path
api_path = cfg.api_base + "/executes"
logger.log("Testing Executes API, api path: %s", api_path)
conn = db.connect(cfg)
test_asset_name = "script-test-sp-echo"
_delete(conn, test_asset_name)
try:
asset_id = create_asset(cfg, test_asset_name)
add_role(cfg, asset_id, "script", json.dumps(script_role_value))
add_role(cfg, asset_id, "script-executor-amc", {"os": "linux"})
rsp_msg = execute(cfg, asset_id)
rsp_msg_json = json.loads(rsp_msg)
if rsp_msg_json["code"] != 200:
return logger.failed("unexpected error: " + rsp_msg)
actual_output = str(rsp_msg_json["message"]).rstrip()
if actual_output != expected_output:
return logger.failed("expected: '" + expected_output +
"', actual: '" + actual_output + "'")
except Exception, x:
#traceback.print_exc()
return logger.failed(x.message)
def stats():
"""
Reports various statistical metrics on a particular server.
Use this with the graph.py tool to see station coverage.
"""
misc.am_i_official()
db = DB.connect()
stats = misc.base_stats()
stats.update({
'intents':
DB.all('intents'),
'hits':
db['c'].execute('select sum(read_count) from intents').fetchone()
[0],
'kv':
DB.all('kv'),
'uptime':
TS.uptime(),
'pwd':
os.getcwd(),
'free':
os.popen("df -h / | tail -1").read().strip(),
# Reporting the list as fractional GB is more useful.
'streams':
DB.all('streams', sort_by='start_unix'),
'config':
misc.public_config()
})
return jsonify(stats), 200
def blast_query_against_one_shard(args, state, shard):
"""
Blast the query against one blast DB shard.
Then write the results to the database.
"""
temp_dir = util.iter_dir(args['temp_dir'], state['blast_db'],
state['query_target'], state['iteration'])
output_file = blast.output_file_name(temp_dir, shard)
blast.against_sra(args, state, output_file, shard)
with db.connect(state['blast_db']) as db_conn:
db.aux_db(db_conn, args['temp_dir'], state['blast_db'],
state['query_target'])
shard = basename(shard)
batch = []
hits = blast.hits(output_file)
for hit in hits:
match = blast.PARSE_RESULTS.match(hit['title'])
if match:
seq_name = match.group(1)
seq_end = match.group(2)
else:
seq_name = hit['title']
seq_end = ''
batch.append((state['iteration'], seq_end, seq_name, shard))
db.insert_blast_hit_batch(db_conn, batch)
def ingest_sprent_data():
"""Read the Sprent nodulataion."""
csv_path = util.SPRENT_DATA_CSV
cxn = db.connect()
data = pd.read_csv(csv_path,
header=0,
names=[
'genus', 'species_count', 'nodulation_status',
'nodulating_species', 'clade', 'legume_subfamily'
])
# Split this record into two
idx = data['genus'] == 'Fillaeopsis_and_Lemurodendron'
data.loc[idx, 'genus'] = 'Fillaeopsis'
data.loc[idx, 'species_count'] = 1
row = data.loc[idx, :].copy()
row['genus'] = 'Lemurodendron'
data = data.append(row, ignore_index=True)
# Remove stray records
data = data[data.genus != 'Piptadenia viridiflora']
data = data[data.genus != 'Otion']
# Cleanup genus
data['genus'] = data.genus.str.split().str[0]
create_sprent_table(cxn, data)
def save(df, sheet):
"""Convert the dataframe/Excel sheet into a CSV file."""
name = util.NOD_CSV + sheet + '.csv'
df.to_csv(name, index=False)
table = 'nodulation_' + sheet
df.to_sql(table, db.connect(), if_exists='replace', index=False)
def assemble(args):
"""Loop thru every blast/query pair and run an assembly for each one."""
with util.make_temp_dir(
where=args['temp_dir'],
prefix='atram_',
keep=args['keep_temp_dir']) as temp_dir:
util.update_temp_dir(temp_dir, args)
queries = split_queries(args)
for blast_db in args['blast_db']:
with db.connect(blast_db, check_version=True) as cxn:
for query in queries:
db.aux_db(cxn, args['temp_dir'], blast_db, query)
clean_database(cxn)
log.setup(args['log_file'], blast_db, query)
assembler = assembly.factory(args, cxn)
try:
assembly_loop(args, assembler, blast_db, query)
except (TimeoutError, RuntimeError):
pass
except Exception as err:
log.error('Exception: {}'.format(err))
finally:
assembler.write_final_output(blast_db, query)
db.aux_detach(cxn)
def get_albums():
"""Get all images from the database."""
step = 2000 # Basic set size
photos = []
with db.connect() as cxn:
cursor = cxn.execute('select * from images')
for image in cursor:
path = util.PHOTOS / image[0]
if path.exists():
photos.append({'image_file': image[0], 'sample_id': image[1]})
shuffle(photos)
*albums, residual = [
photos[i:i + step] for i in range(0, len(photos), step)
]
albums[-1] += residual
for i, album in enumerate(albums, 1):
out_dir = f'album_{i}'
for photo in album:
photo['album'] = out_dir
photo['photo'] = photo['image_file'].replace('/', '_')
return albums
def assemble(args):
"""Loop thru every blast/query pair and run an assembly for each one."""
with util.make_temp_dir(where=args['temp_dir'],
prefix='atram_',
keep=args['keep_temp_dir']) as temp_dir:
util.update_temp_dir(temp_dir, args)
queries = split_queries(args)
for blast_db in args['blast_db']:
with db.connect(blast_db, check_version=True) as cxn:
for query in queries:
db.aux_db(cxn, args['temp_dir'], blast_db, query)
clean_database(cxn)
log.setup(args['log_file'], blast_db, query)
assembler = assembly.factory(args, cxn)
try:
assembly_loop(args, assembler, blast_db, query)
except (TimeoutError, RuntimeError):
pass
except Exception as err: # pylint: disable=broad-except
log.error('Exception: {}'.format(err))
finally:
assembler.write_final_output(blast_db, query)
db.aux_detach(cxn)
def blast_query_against_one_shard(args, state, shard):
"""
Blast the query against one blast DB shard.
Then write the results to the database.
"""
output_file = blast.output_file_name(state['iter_dir'], shard)
blast.against_sra(args, state, output_file, shard)
with db.connect(state['blast_db']) as cxn:
db.aux_db(
cxn,
args['temp_dir'],
state['blast_db'],
state['query_target'])
shard = basename(shard)
batch = []
hits = blast.hits(output_file)
for hit in hits:
seq_name, seq_end = blast.parse_blast_title(hit['title'])
batch.append((state['iteration'], seq_end, seq_name, shard))
db.insert_blast_hit_batch(cxn, batch)
def test_setup(self):
db = self.db()
db.connect()
db.setup({
1 : ['ROLLOUT 1', 'ROLLBACK 1'],
2 : ['ROLLOUT 2', 'ROLLBACK 2']
})
# Statement 0: SELECT version - ignore
# Statement 1: Rollout statment 1 - check:
self.assertEqual("ROLLOUT 1", db._conn.cursor_return.execute_kwargs[1][0])
# Statement 2: INSERT version - ignore
# Statement 3: Rollout statment 2 - check:
self.assertEqual("ROLLOUT 2", db._conn.cursor_return.execute_kwargs[3][0])
# Statement 4: INSERT version - check
self.assertEqual("INSERT INTO test_version", db._conn.cursor_return.execute_kwargs[4][0][0:24])
self.assertEqual(2, db._conn.cursor_return.execute_kwargs[4][1][0])
def clean_taxonomy():
"""Audit problem taxonomy records in the database."""
cxn = db.connect()
taxonomy_ids = pd.read_sql('SELECT * FROM taxonomy_ids;', cxn)
errors = get_duplicate_sample_ids(taxonomy_ids)
create_taxonomy_errors_table(cxn, errors)
drop_bad_genera(cxn)
drop_errors(cxn)
def ingest_taxonomy(google_sheet):
"""Ingest data related to the taxonomy."""
cxn = db.connect()
taxonomy = get_taxonomy(google_sheet)
taxonomy = split_sample_ids(taxonomy)
taxonomy.to_sql(google_sheet, cxn, if_exists='replace', index=False)
create_taxon_ids_table(cxn, google_sheet, taxonomy)
def get_taxa():
"""Build a dictionary of scientific names and taxon_ids."""
log(f'Getting {DATASET_ID} taxa')
sql = """SELECT taxon_id, sci_name
FROM taxa
WHERE sci_name IN ({})"""
sql = sql.format(','.join([f"'{x}'" for x in TARGETS]))
taxa = pd.read_sql(sql, db.connect())
return taxa.set_index('sci_name').taxon_id.to_dict()
def ingest_nfn_data():
"""Ingest data related to the taxonomy."""
cxn = db.connect()
exps = [get_expedition(e) for e in util.EXPEDITIONS]
nfn = pd.concat(exps, ignore_index=True).fillna('')
nfn = fixup_data(nfn)
nfn = update_collector_data(nfn)
create_nfn_table(cxn, nfn)
def write_to_db(sample_wells):
"""Output the sample wells to the database."""
with db.connect() as cxn:
sample_wells.to_sql(
'sample_wells', cxn, if_exists='replace', index=False)
cxn.execute("""
CREATE UNIQUE INDEX IF NOT EXISTS
sample_wells_plate_id_well ON sample_wells (plate_id, well);
""")
def test_setup(self):
db = self.db()
db.connect()
db.setup({
1: ['ROLLOUT 1', 'ROLLBACK 1'],
2: ['ROLLOUT 2', 'ROLLBACK 2']
})
# Statement 0: SELECT version - ignore
# Statement 1: Rollout statment 1 - check:
self.assertEqual("ROLLOUT 1",
db._conn.cursor_return.execute_kwargs[1][0])
# Statement 2: INSERT version - ignore
# Statement 3: Rollout statment 2 - check:
self.assertEqual("ROLLOUT 2",
db._conn.cursor_return.execute_kwargs[3][0])
# Statement 4: INSERT version - check
self.assertEqual("INSERT INTO test_version",
db._conn.cursor_return.execute_kwargs[4][0][0:24])
self.assertEqual(2, db._conn.cursor_return.execute_kwargs[4][1][0])
def generate_reports():
"""Generate all of the reports."""
cxn = db.connect()
now = datetime.now()
sample_wells = get_wells(cxn)
plates = get_plates(sample_wells)
genera = get_genus_coverage(cxn)
# generate_html_report(now, sample_wells, plates, genera)
generate_excel_report(cxn, sample_wells, plates, genera)
def ingest_non_fabales_data():
"""Read the non-Fabales nodulataion."""
csv_path = util.NON_FABALES_CSV
cxn = db.connect()
data = pd.read_csv(csv_path,
header=0,
names=['genus', 'nodulation_status', 'nodulation_type'])
data.genus = data.genus.str.strip()
create_genbank_loci_table(cxn, data)
def main(cfg):
global api_path
api_path = cfg.api_base + "/catalog/roles"
logger.log("Testing API on roles, api path: %s", api_path)
conn = db.connect(cfg)
# cleanup test data
for x in xrange(test_recs_count):
_delete(conn, _role_name(x + 1))
#db.sql(conn, "delete from roles where class = 'test'")
db.sql(conn, "commit")
# ---------------------------------------- Create
id_recs = _testCreateNew(conn, test_recs_count)
if not id_recs:
return False
if not _testCreateDupName(conn):
return False
# ---------------------------------------- Read
#print "++", id_recs
db_roles = [Role(_read(conn, id_rec["name"])[0]) for id_rec in id_recs]
logger.log("running Read")
for db_role in db_roles:
if not _testRead(db_role):
return False
# ---------------------------------------- Update
time.sleep(1) # ensure modified_ts has been promoted
logger.log("running Update")
if not _testUpdate(conn, 2, db_roles[1]):
return False
if not _testUpdateNonExisted(conn, db_roles[0]):
return False
# ---------------------------------------- Delete
logger.log("running Delete")
for db_role in db_roles:
if not _testDelete(conn, db_role):
return False
if not _testDeleteNonExisted(conn, db_roles[0]):
return False
#rows = db.sql(conn, "select 1+%s", 44)
#logger.log("rows: %s", rows)
#db.sql(conn, "commit")
db.disconnect(conn)
return True
def fill_blast_fasta(blast_db, fasta_path, shard_params):
"""
Fill the fasta file used as input into blast.
Use sequences from the sqlite3 DB. We use the shard partitions passed in to
determine which sequences to get for this shard.
"""
with db.connect(blast_db) as cxn:
limit, offset = shard_params
with open(fasta_path, 'w') as fasta_file:
for row in db.get_sequences_in_shard(cxn, limit, offset):
util.write_fasta_record(fasta_file, row[0], row[2], row[1])
def ingest_corrales_data():
"""Process the Corrales data."""
csv_path = util.TEMP_DATA / 'corrales.csv'
cxn = db.connect()
google.sheet_to_csv(util.CORRALES_SHEET, csv_path)
corrales = pd.read_csv(csv_path)
corrales.corrales_id = corrales.corrales_id.str.lower()
corrales.image_file = corrales.image_file.apply(util.normalize_file_name)
create_corrales_data_table(cxn, corrales)
merge_into_images(cxn)
def attach_families():
"""Get the family from the sci_name."""
df = pd.read_csv(GREENNESS_IN_CSV)
sql = """ select sci_name, family from taxonomy;"""
with db.connect() as cxn:
map_df = pd.read_sql(sql, cxn)
name_map = map_df.set_index('sci_name')['family'].to_dict()
df['family'] = df['sci_name'].map(name_map)
df.to_csv(GREENNESS_OUT_CSV, index=False)
def merge_sequencing_sheets():
"""Create rapid sample sheet data table table."""
cxn = db.connect()
merged = None
for sheet in SEQ_METADATA_SHEETS:
sheet = pd.read_sql(f'SELECT * from {sheet};', cxn)
if merged is None:
merged = sheet
else:
merged = merged.append(sheet, ignore_index=True)
merged.to_sql('sequencing_metadata', cxn, if_exists='replace', index=False)
def fill_blast_fasta(blast_db, fasta_path, shard_params):
"""
Fill the fasta file used as input into blast.
Use sequences from the sqlite3 DB. We use the shard partitions passed in to
determine which sequences to get for this shard.
"""
with db.connect(blast_db) as cxn:
limit, offset = shard_params
with open(fasta_path, 'w') as fasta_file:
for row in db.get_sequences_in_shard(cxn, limit, offset):
util.write_fasta_record(fasta_file, row[0], row[2], row[1])
def main(cfg):
global api_path
api_path = cfg.api_base + "/admin/data/script_stores"
logger.log("Testing CRUD operations on script_stores, API path: %s", api_path)
conn = db.connect(cfg)
# cleanup test data
for x in xrange(test_recs_count):
_delete(conn, "store "+str(x+1))
db.sql(conn, "commit")
# ---------------------------------------- Create
apiStoreIds = _testCreateNew(conn, test_recs_count)
if not apiStoreIds:
return False
if not _testCreateDupName(conn):
return False
# ---------------------------------------- Read
dbStores = [ ScriptStore(_read(conn, apiStoreId["uuid_pk"])[0]) for apiStoreId in apiStoreIds]
logger.log("running Read")
for dbStore in dbStores:
if not _testRead(conn, dbStore):
return False
# ---------------------------------------- Update
time.sleep(1) # ensure modified_ts is greater
logger.log("running Update")
for dbStore in dbStores:
if not _testUpdate(conn, dbStore):
return False
if not _testUpdateNonExisted(conn, dbStores[0]):
return False
# ---------------------------------------- Delete
logger.log("running Delete")
for dbStore in dbStores:
if not _testDelete(conn, dbStore):
return False
if not _testDeleteNonExisted(conn, dbStores[0]):
return False
#rows = db.sql(conn, "select 1+%s", 44)
#logger.log("rows: %s", rows)
#db.sql(conn, "commit")
db.disconnect(conn)
return True
def main(cfg):
global api_path
api_path = cfg.api_base + "/catalog/assets"
logger.log("Testing API on assets, api path: %s", api_path)
conn = db.connect(cfg)
# cleanup test data
for x in xrange(test_rec_count):
_delete(conn, _asset_name(x + 1))
db.sql(conn, "commit")
# ---------------------------------------- Create Asset
asset_recs = _testCreateAsset(conn, test_rec_count)
if not asset_recs:
return False
if not _testCreateDupAsset(conn):
return False
# ---------------------------------------- Read
#print "++", id_recs
#asset_recs = [ Role(_read(conn, id_rec["name"])[0]) for id_rec in id_recs ]
logger.log("running Read")
for asset_rec in asset_recs:
if not _testRead(asset_rec):
return False
# ---------------------------------------- Update
time.sleep(1) # ensure modified_ts has been promoted
logger.log("running Update")
if not _testUpdate(conn, 2, asset_recs[1]):
return False
if not _testUpdateNonExisted(conn, asset_recs[0]):
return False
# ---------------------------------------- Delete
logger.log("running Delete")
for asset_rec in asset_recs:
if not _testDelete(conn, asset_recs):
return False
if not _testDeleteNonExisted(conn, asset_recs[0]):
return False
#rows = db.sql(conn, "select 1+%s", 44)
#logger.log("rows: %s", rows)
#db.sql(conn, "commit")
db.disconnect(conn)
return True
def ingest_priority_taxa_list():
"""Read the priority taxa list Google sheet."""
csv_path = util.TEMP_DATA / 'priority_taxa.csv'
cxn = db.connect()
google.sheet_to_csv(util.PRIORITY_TAXA_SHEET, csv_path)
taxa = pd.read_csv(
csv_path,
header=0,
na_filter=False,
names=['family', 'subclade', 'genus', 'position', 'priority'])
create_priority_taxa_table(cxn, taxa)
def create_fasta_files(args):
"""Convert data in an atram sqlite database into fasta files."""
if not exists(db.get_db_name(args['blast_db'])):
sys.exit('Could not find the database.')
with db.connect(args['blast_db'], check_version=True) as cxn:
try:
files = open_fasta_files(args, cxn)
for rec in db.get_all_sequences(cxn):
util.write_fasta_record(files[rec[1]], rec[0], rec[2], rec[1])
finally:
close_fasta_files(files)
def __init__(self):
settings = dict(
template_path=os.path.join(os.path.dirname(__file__), "templates"),
static_path=os.path.join(os.path.dirname(__file__), "static"),
xsrf_cookies=True,
cookie_secret=config.secret,
autoescape=None,
jinja2=Environment(loader=FileSystemLoader(
os.path.join(os.path.dirname(__file__), "templates")), trim_blocks=True),
login_url="/signin"
)
handlers = [
(r"/", handler.index.IndexHandler),
(r"/index/(\d+)", handler.index.IndexHandler),
(r"/weibo/signin", handler.weibo.SigninHandler),
(r"/weibo/bind", handler.weibo.BindHandler),
(r"/weibo/callback", handler.weibo.CallbackHandler),
(r"/weibo/sync", handler.weibo.SyncHandler),
(r"/twitter/signin", handler.twitter.SigninHandler),
(r"/twitter/bind", handler.twitter.BindHandler),
(r"/twitter/callback", handler.twitter.CallbackHandler),
(r"/twitter/sync", handler.twitter.SyncHandler),
(r"/feed", handler.rss.FeedHandler),
(r"/rss/sync", handler.rss.SyncHandler),
(r"/past", handler.post.PastHandler),
(r"/past/([0-9]{4}-[0-9]{2}-[0-9]{2})", handler.post.PastHandler),
(r"/chart", handler.chart.ChartHandler),
(r"/chart/([1,2][0-9]{3})", handler.chart.ChartHandler),
(r"/ping", handler.index.PingHandler),
(r"/post/delete/([1-9]\d+)", handler.admin.DeleteHandler),
(r"/admin", handler.admin.AdminHandler),
(r"/signin", handler.admin.SigninHandler),
(r"/signout", handler.admin.SignoutHandler),
(r"/admin/user", handler.admin.UserHandler),
(r"/admin/compose", handler.admin.ComposeHandler),
(r"/admin/visible", handler.admin.VisibleHandler),
(r"/admin/account", handler.admin.AccountHandler),
(r"/([^/]+)", handler.index.SourceHandler),
(r"/([^/]+)/(\d+)", handler.index.SourceHandler),
(r".*", handler.index.NotFoundHandler)
]
tornado.web.Application.__init__(self, handlers, **settings)
self.db = connect(config.mysql)
self.cmd = Commander(self.db)
self.data = {}
def find_requests_and_send_mail(config):
os.chdir(os.path.dirname(os.path.realpath(__file__)))
db = DB.connect(db_file='../db/main.db')
now = time.time()
what_we_be_done_with = db['c'].execute('select * from reminders where end_time < %d' % now).fetchall()
for row in DB.map(what_we_be_done_with, 'reminders', db=db):
row['link'] = "http://indycast.net/%s/slices/%s_%d" % ( row['station'], time.strftime("%Y%m%d%H%M", time.gmtime(row['start_time'] - row['offset'] * 60)), (row['end_time'] - row['start_time']) / 60)
if len(row['notes']):
row['link'] += '/%s_on_%s' % (re.sub('[^\w]', '_', row['notes']).strip('_'), row['station'])
row['link'] += '.mp3'
email = do_template(template_file='email_reminder_template.txt', settings=row)
res = misc.send_email(config=config, who=row['email'], subject=email['subject'], body=email['body'])
db['c'].execute('delete from reminders where id = %d' % row['id'])
db['conn'].commit()
return None
def preprocess(args):
"""Build the databases required by atram."""
log.setup(args['log_file'], args['blast_db'])
with util.make_temp_dir(
where=args['temp_dir'],
prefix='atram_preprocessor_',
keep=args['keep_temp_dir']) as temp_dir:
util.update_temp_dir(temp_dir, args)
with db.connect(args['blast_db'], clean=True) as cxn:
db.create_metadata_table(cxn)
db.create_sequences_table(cxn)
load_seqs(args, cxn)
log.info('Creating an index for the sequence table')
db.create_sequences_index(cxn)
shard_list = assign_seqs_to_shards(cxn, args['shard_count'])
create_all_blast_shards(args, shard_list)
def test_execute_custom_cursor(self):
db = self.db()
db.connect()
cur = db.cursor()
db.execute("select 1", cur=cur)
self.assertEqual("select 1", cur.execute_kwargs[0][0])
def test_verify(self):
db = self.db()
db.connect()
db.verify()
self.assertEqual("SELECT 1", db._conn.cursor_return.execute_kwargs[0][0])
def test_fetchall(self):
db = self.db()
db.connect()
db.fetchall("SELECT 1")
def test_close(self):
db = self.db()
db.connect()
conn = db._conn
db.close()
self.assertTrue(conn.close_kwargs is not None)
def test_close_set_connected(self):
db = self.db()
db.connect()
db.close()
self.assertFalse(db.connected())
def prune_process(lockMap, reindex=False, force=False):
# This is internal, call prune() directly. This is a normally blocking
# process that is prepared by prune(), making it easily callable asynchronously
# If another prune is running then we just bail
if not lockMap['prune'].acquire(False) and not force:
logging.warn("Tried to run another prune whilst one is running. Aborting")
return True
# If we are the first process then we need to make sure that the webserver is up before
# we do this to check to see if we are official
time.sleep(2)
pid = misc.change_proc_name("%s-cleanup" % misc.config['callsign'])
# We want to run the am_i_official here since it could block on a DNS lookup
misc.am_i_official()
try:
register_stream_list(reindex)
except:
lockMap['prune'].release()
return None
db = DB.connect()
archive_duration = misc.config['archivedays'] * TS.ONE_DAY_SECOND
cutoff = TS.unixtime('prune') - archive_duration
# Remove all slices older than 4 hours.
slice_cutoff = TS.unixtime('prune') - 0.1667 * TS.ONE_DAY_SECOND
cloud_cutoff = None
if misc.config['cloud']:
cloud_cutoff = TS.unixtime('prune') - misc.config['cloudarchive'] * TS.ONE_DAY_SECOND
# Put thingies into the cloud.
count = 0
for file_name in glob('*/*.mp3'):
#
# Depending on many factors this could be running for hours
# or even days. We want to make sure this isn't a blarrrghhh
# zombie process or worse yet, still running and competing with
# other instances of itself.
#
if not misc.manager_is_running():
lockMap['prune'].release()
return None
ctime = os.path.getctime(file_name)
# print "Looking at ", file_name, ctime, cutoff, archive_duration, misc.config['archivedays'], misc.am_i_official()
# We observe the rules set up in the config.
if file_name.startswith('slices') and ctime < slice_cutoff or ctime < cutoff:
logging.debug("Prune[remove]: %s (ctime)" % file_name)
os.unlink(file_name)
count += 1
# We want to make sure we aren't archiving the slices
elif cloud_cutoff and ctime < cloud_cutoff and not file_name.startswith('slice') and misc.am_i_official():
logging.debug("Prune[cloud]: %s" % file_name)
# Only unlink the file if I can successfully put it into the cloud.
if put(file_name):
try:
os.unlink(file_name)
except:
logging.debug("Prune[cloud]: Couldn't remove %s" % file_name)
for file_name in glob('%s/*.gz' % misc.DIR_BACKUPS):
ctime = os.path.getctime(file_name)
# We observe the rules set up in the config.
if ctime < cutoff:
logging.debug("Prune: %s" % file_name)
os.unlink(file_name)
count += 1
# The map names are different since there may or may not be a corresponding
# cloud thingie associated with it.
db = DB.connect()
# Don't do this f*****g shit at all because f**k this so hard.
#logging.info('select name, id from streams where end_unix < date("now", "-%d seconds") or (end_minute - start_minute < 0.05 and start_unix < date("now", "%d seconds"))' % (archive_duration, TS.get_offset() * 60 - 1200))
unlink_list = db['c'].execute('select name, id from streams where end_unix < date("now", "-%d seconds")' % (archive_duration)).fetchall()
for file_name_tuple in unlink_list:
file_name = str(file_name_tuple[0])
id = file_name_tuple[1]
logging.debug("Prune[remove]: %s (unlink list)" % file_name)
# If there's a cloud account at all then we need to unlink the
# equivalent mp3 file
if cloud_cutoff and misc.am_i_official():
"cloud.";unlink(file_name)
# After we remove these streams then we delete them from the db.
db['c'].execute('delete from streams where id = %d' % id)
db['conn'].commit()
# now only after we've deleted from the cloud can we delete the local file
if os.path.exists(file_name):
os.unlink(file_name)
count += 1
logging.info("Found %d files older than %s days." % (count, misc.config['archivedays']))
lockMap['prune'].release()
def find_streams(start_list, duration_min):
# Given a start week minute this looks for streams in the storage
# directory that match it - regardless of duration ... so it may return
# partial shows results.
stream_list = []
if type(start_list) is int:
start_list = [start_list]
# Sort nominally - since we have unix time in the name, this should come out
# as sorted by time for us for free.
stitch_list = []
episode_list = []
db = DB.connect()
# So we have a start list, we are about to query our database using the start_minute
# and end_minute field ... to get end_minue we need to make use of our duration.
#
# timeline ->
#
# ################### << Region we want
# start_sea#ch end_search # << Search
# V V
# | | | | | | | << Minute
# a b b c
#
# so we want
# (a) start_minute < start_search and end_minute >= start_search ||
# (b) start_minute > start_search and end_minute <= end_search ||
# (c) start_minute < end_search and end_minute >= end_search
#
condition_list = []
for start in start_list:
end_search = (start + duration_min) % TS.MINUTES_PER_WEEK
# print start, duration_min, end_search
condition_list.append('start_minute < %d and end_minute >= %d' % (start, start))
condition_list.append('start_minute > %d and end_minute >= %d and end_minute <= %d' % (start, start, end_search))
condition_list.append('start_minute < %d and end_minute >= %d' % (end_search, end_search))
condition_query = "((%s))" % ') or ('.join(condition_list)
# see https://github.com/kristopolous/DRR/issues/50 - nah this shit is buggy
condition_query += " and start_unix < datetime(%d, 'unixepoch', 'localtime')" % (TS.sec_now() - misc.config['cascadetime'] + 3)
full_query = "select * from streams where %s order by week_number * 10080 + start_minute asc" % condition_query
entry_list = DB.map(db['c'].execute(full_query).fetchall(), 'streams')
#logging.info(full_query)
#logging.info(entry_list)
# print full_query, len(entry_list)
# We want to make sure that we break down the stream_list into days. We can't JUST look at the week
# number since we permit feed requests for shows which may have multiple days. Since this is leaky
# data that we don't keep via our separation of concerns, we use a little hack to figure this out.
by_episode = []
episode = []
cutoff_minute = 0
current_week = 0
for entry in entry_list:
# look at start minute, if it's > 12 * cascade time (by default 3 hours), then we presume this is a new episode.
if entry['start_minute'] > cutoff_minute or entry['week_number'] != current_week:
if len(episode):
by_episode.append(episode)
episode = []
cutoff_minute = entry['start_minute'] + (12 * misc.config['cascadetime']) % TS.MINUTES_PER_WEEK
current_week = entry['week_number']
# We know by definition that every entry in our stream_list is a valid thing we need
# to look at. We just need to make sure we break them down by episode
episode.append(entry)
if len(episode):
by_episode.append(episode)
#print len(by_episode), condition_query
# Start the creation of the audio files.
for episode in by_episode:
# We blur the test start to a bigger window
test_start = (episode[0]['start_minute'] / (60 * 4))
for week_start in start_list:
# Blur the query start to the same window
query_start = week_start / (60 * 4)
# This shouldn't be necessary but let's do it anyway
if abs(query_start - test_start) <= 1:
# Under these conditions we can say that this episode
# can be associated with this particular start time
# The start_minute is based on the week
offset_start = week_start - episode[0]['start_minute']
fname = audio.stream_name(episode, week_start, duration_min)
print '--name',episode[0]['name'], fname
# We get the name that it will be and then append that
stream_list.append(stream_info(fname))
# print offset_start, duration_min, episode
episode_list.append((episode, offset_start, duration_min))
break
# print stream_list, "\nbreak\n", episode_list, "\nasfdasdf\n"
return stream_list, episode_list
def test_commit(self):
db = self.db()
db.connect()
db.commit()
self.assertTrue(db._conn.commit_kwargs is not None)
def test_cursor(self):
db = self.db()
db.connect()
self.assertTrue(db.cursor() is not None)
self.assertTrue(db._conn.cursor_kwargs is not None)
def test_execute_internal_cursor(self):
db = self.db()
db.connect()
db.execute("select 1")
self.assertEqual("select 1", db._conn.cursor_return.execute_kwargs[0][0])
else:
# Don't want any scripts to read this and harvest my email.
email_to_use = 'kri%s@%soo.com' % ("stopolous", "yah")
res = misc.send_email(config=mail_config, who=email_to_use, subject=subject, body='<br>'.join(report), sender='Indycast Admin <*****@*****.**>')
print("Issues found. Sending email to %s." % email_to_use)
#print('\n'.join(report))
"""
CALLSIGN = 'callsign'
os.chdir(os.path.dirname(os.path.realpath(__file__)))
stderr(OFF)
try:
db = DB.connect(db_file='../db/main.db')
except:
db = None
parser = argparse.ArgumentParser()
parser.add_argument("-q", "--query", default=None, help="query to send to the servers (site-map gives all end points)")
parser.add_argument("-s", "--station", default="all", help="station to query (default all)")
parser.add_argument('-l', '--list', action='store_true', help='show stations')
parser.add_argument('-k', '--key', default=None, help='Get a specific key in a json formatted result')
parser.add_argument('-n', '--notrandom', action='store_true', help='do not reandomize order')
mail_config = misc.mail_config(parser)
stderr(ON)
args = parser.parse_args()
def test_connect_set_connected(self):
db = self.db()
self.assertFalse(db.connected())
db.connect()
self.assertTrue(db.connected())
def test_rollback(self):
db = self.db()
db.connect()
db.rollback()
self.assertTrue(db._conn.rollback_kwargs is not None)
def execute(self, sql, args, format_input='qmark', paramstyle='pyformat'):
db = self.db(paramstyle=paramstyle, format_input=format_input)
db.connect()
db.execute(sql, args)
return db._conn.cursor_return.execute_kwargs[0]
