cstrittmatter@0: """Builds SQLite3 databases"""
cstrittmatter@0: 
cstrittmatter@0: import logging, os, pickle, re, requests, sqlite3, sys, time
cstrittmatter@0: import lexmapr.ontology_reasoner as ontr
cstrittmatter@0: from nltk.tokenize import word_tokenize
cstrittmatter@0: from lexmapr.pipeline_helpers import punctuation_treatment
cstrittmatter@0: from lexmapr.definitions import embl_ontologies, synonym_db, ontol_db
cstrittmatter@0: from lexmapr.definitions import owl_dir, purl_link, missing_ontol_labels
cstrittmatter@0: from lexmapr.pipeline_resources import get_resource_label_permutations
cstrittmatter@0: 
cstrittmatter@0: logging.getLogger('requests').setLevel(logging.WARNING)
cstrittmatter@0: logging.getLogger('urllib3').setLevel(logging.WARNING)
cstrittmatter@0: 
cstrittmatter@0: 
cstrittmatter@0: # TODO: might replace pickle with ujson
cstrittmatter@0: def _pickle_save(data_to_save, file_path):
cstrittmatter@0:     '''Write a pickle file'''
cstrittmatter@0:     with open(file_path,'wb') as SAVE_file:
cstrittmatter@0:         pickle.dump(data_to_save, SAVE_file)
cstrittmatter@0: 
cstrittmatter@0: 
cstrittmatter@0: def _pickle_load(file_path):
cstrittmatter@0:     '''Read a pickle file'''
cstrittmatter@0:     with open(file_path,'rb') as LOAD_file:
cstrittmatter@0:         return(pickle.load(LOAD_file))
cstrittmatter@0: 
cstrittmatter@0: 
cstrittmatter@0: def _get_ontols(ontol_interest):
cstrittmatter@0:     '''Obtain URLs for ontologies of interest'''
cstrittmatter@0:     ontol_dic = {}
cstrittmatter@0:     embl_resp = requests.get(embl_ontologies)
cstrittmatter@0:     resp_blocks = re.findall('<tr>([\s\S]+?)</tr>',embl_resp.content.decode('utf-8'))
cstrittmatter@0:     for resp_block in resp_blocks:
cstrittmatter@0:         try:
cstrittmatter@0:             embl_abbr = re.search('class=\"ontology-source\">([\s\S]+?)<', resp_block).group(1)
cstrittmatter@0:             embl_name = re.search('<a href=\"/ols/ontologies/' + embl_abbr.lower() +\
cstrittmatter@0:                                   '\">([\s\S]+?)</a>', resp_block).group(1)
cstrittmatter@0:             embl_link = re.search('href=\"(\S+)\">Download', resp_block).group(1)
cstrittmatter@0:             if embl_link.startswith('ontologies'):
cstrittmatter@0:                 embl_link = embl_link[len('ontologies'):]
cstrittmatter@0:         # TODO: with Python 3.9- embl_link.removeprefix('ontologies')
cstrittmatter@0:         except(AttributeError):
cstrittmatter@0:             continue
cstrittmatter@0:         if embl_abbr in ontol_interest:
cstrittmatter@0:             ontol_dic[embl_abbr] = (embl_name, embl_link)
cstrittmatter@0:     # Continue if not find all ontologies of interest specified in definitions.py
cstrittmatter@0:     not_found = set(ontol_interest).difference(set(ontol_dic.keys()))
cstrittmatter@0:     if not_found:
cstrittmatter@0:         if len(not_found) == 1:
cstrittmatter@0:             logging.warning(f'Did not find ontology: ' + ', '.join(not_found))
cstrittmatter@0:         else:
cstrittmatter@0:             logging.warning(f'Did not find ontologies: ' + ', '.join(not_found))
cstrittmatter@0:     if ontol_dic == {}:
cstrittmatter@0:         sys.exit('Zero ontologies found from user-given list')
cstrittmatter@0:     return(ontol_dic)
cstrittmatter@0: 
cstrittmatter@0: 
cstrittmatter@0: def _check_make(db_file, remake_cache, ontol_interest):
cstrittmatter@0:     '''Check if database file should be remade'''
cstrittmatter@0:     if os.path.exists(db_file) and remake_cache == False:
cstrittmatter@0:         if os.path.exists(os.path.join(owl_dir, 'cached_ontologies.pickle')):
cstrittmatter@0:             if ontol_interest == _pickle_load(os.path.join(owl_dir, 'cached_ontologies.pickle')):
cstrittmatter@0:                 return(False)
cstrittmatter@0:     try:
cstrittmatter@0:         os.remove(db_file)
cstrittmatter@0:     except(FileNotFoundError):
cstrittmatter@0:         pass
cstrittmatter@0:     return(True)
cstrittmatter@0: 
cstrittmatter@0: 
cstrittmatter@0: def _db_insert(db_cursor, table_name, key_term, val_term):
cstrittmatter@0:     '''Insert new data into a database table'''
cstrittmatter@0:     if key_term.strip()==val_term.strip() or key_term.strip()=='' or val_term.strip()=='':
cstrittmatter@0:         return
cstrittmatter@0:     db_cursor.execute(f"INSERT OR IGNORE INTO {table_name} VALUES (:key,:value)",
cstrittmatter@0:                       {'key':key_term.strip(), 'value':val_term.strip()})
cstrittmatter@0:     
cstrittmatter@0: 
cstrittmatter@0: def _get_imports(file_handle):
cstrittmatter@0:     '''Check for required imports; append any new patterns to pattern_strs'''
cstrittmatter@0:     pattern_strs = []
cstrittmatter@0:     pattern_strs.append('<owl:imports rdf:resource=\"&obo;(.*)"/>')
cstrittmatter@0:     pattern_strs.append('<owl:imports rdf:resource=\"(http://.*)\"/>')
cstrittmatter@0:     whole_file = str(file_handle.read())
cstrittmatter@0:     for patt_str in pattern_strs:
cstrittmatter@0:         import_match = re.findall(patt_str, whole_file)
cstrittmatter@0:         if import_match != []:
cstrittmatter@0:             import_match = [x if re.search('^http:',x) else purl_link+x for x in import_match]
cstrittmatter@0:             break
cstrittmatter@0:     return(import_match)
cstrittmatter@0: 
cstrittmatter@0: 
cstrittmatter@0: def _section_file(file_handle, break_pattern,
cstrittmatter@0:                  stt_at='    // Classes', end_at='    // Annotations'):
cstrittmatter@0:     '''Break OWL files into readable sections for each ontology accession'''
cstrittmatter@0:     whole_file = str(file_handle.read())
cstrittmatter@0:     if stt_at != '':
cstrittmatter@0:         if re.search(stt_at, whole_file):
cstrittmatter@0:             whole_file = ''.join(whole_file.split(stt_at)[1:])
cstrittmatter@0:     if end_at != '':
cstrittmatter@0:         if re.search(end_at, whole_file):
cstrittmatter@0:             whole_file = ''.join(whole_file.split(end_at)[:-1])
cstrittmatter@0:     file_sections = whole_file.split(break_pattern)
cstrittmatter@0:     return(file_sections[1:-1])
cstrittmatter@0: 
cstrittmatter@0: 
cstrittmatter@0: def _labels_synonyms(obo_list, have_label=False):
cstrittmatter@0:     '''Identify labels, ids and exact ontology synonyms'''
cstrittmatter@0:     obo_ids = []
cstrittmatter@0:     for obo_string in obo_list:
cstrittmatter@0:         id_pattern = '(\w+) -->'
cstrittmatter@0:         lab_pattern = '\<rdfs:label[\s\S]*?\>([\s\S]+?)\<\/rdfs:label\>'
cstrittmatter@0:         syn_pattern = '\<oboInOwl:hasExactSynonym[\s\S]*?\>(.*?)\</oboInOwl:hasExactSynonym'
cstrittmatter@0:         obo_id = re.search(id_pattern, obo_string).group(1)
cstrittmatter@0:         # Do not save ids that are not formatted as expected, or placeholders/obsolete/Apollo:SV
cstrittmatter@0:         if re.search('APOLLO:SV', obo_id) or re.search('APOLLO_SV', obo_id):
cstrittmatter@0:             continue
cstrittmatter@0:         elif obo_id in missing_ontol_labels:
cstrittmatter@0:             continue
cstrittmatter@0:         elif re.search(':', obo_id) or re.search('_', obo_id):
cstrittmatter@0:             try:
cstrittmatter@0:                 obo_label = re.findall(lab_pattern, obo_string)[-1] 
cstrittmatter@0:                 if re.search('^obsolete:', obo_label):
cstrittmatter@0:                     continue
cstrittmatter@0:             except(IndexError):
cstrittmatter@0:                 obo_label = ''
cstrittmatter@0:             obo_synonyms = re.findall(syn_pattern, obo_string)
cstrittmatter@0:             obo_ids.append([str(obo_id.lstrip('_')), str(obo_label), obo_synonyms])
cstrittmatter@0:     if have_label:
cstrittmatter@0:         obo_ids = [x for x in obo_ids if x[1] != '']
cstrittmatter@0:     return(obo_ids)
cstrittmatter@0: 
cstrittmatter@0: 
cstrittmatter@0: def get_synonyms(remake_cache, ontol_interest):
cstrittmatter@0:     '''Create database of predefined synonyms'''
cstrittmatter@0:     os.makedirs(owl_dir, exist_ok=True)
cstrittmatter@0:     if _check_make(synonym_db, remake_cache, ontol_interest) == False:
cstrittmatter@0:         return
cstrittmatter@0: 
cstrittmatter@0:     conn = sqlite3.connect(synonym_db)
cstrittmatter@0:     c = conn.cursor()
cstrittmatter@0:     c.execute("CREATE TABLE label_synonyms (key TEXT, value TEXT, UNIQUE(key, value))")
cstrittmatter@0:     print('{:<40}'.format('\tRetrieving predefined synonyms'),end='\r')
cstrittmatter@0:     with open('lexmapr/predefined_resources/resource_synonyms.csv', 'r') as IN_file:
cstrittmatter@0:         for read_line in IN_file.readlines():
cstrittmatter@0:             split_line = read_line.split(',')
cstrittmatter@0:             try:
cstrittmatter@0:                 ontol_name = split_line[2].split('_')[0].upper()
cstrittmatter@0:             except(IndexError):
cstrittmatter@0:                 ontol_name = ontol_interest[0]
cstrittmatter@0:             if ontol_name in ontol_interest:
cstrittmatter@0:                 _db_insert(c, 'label_synonyms', punctuation_treatment(split_line[0]),
cstrittmatter@0:                            punctuation_treatment(split_line[1]))
cstrittmatter@0:     conn.commit()
cstrittmatter@0:     with open('lexmapr/predefined_resources/SynLex.csv', 'r') as IN_file:
cstrittmatter@0:         IN_file.readline()
cstrittmatter@0:         for read_line in IN_file.readlines():
cstrittmatter@0:             split_line = read_line.split(',')
cstrittmatter@0:             try:
cstrittmatter@0:                 ontol_name = split_line[2].split('_')[0].upper().strip()
cstrittmatter@0:             except(IndexError):
cstrittmatter@0:                 ontol_name = ontol_interest[0]
cstrittmatter@0:             if ontol_name in ontol_interest:
cstrittmatter@0:                 _db_insert(c, 'label_synonyms', punctuation_treatment(split_line[0]),
cstrittmatter@0:                            punctuation_treatment(split_line[1]))
cstrittmatter@0:     conn.commit()
cstrittmatter@0:     conn.close()
cstrittmatter@0:     return
cstrittmatter@0: 
cstrittmatter@0: 
cstrittmatter@0: def get_resource_ids(remake_cache, ontol_interest):
cstrittmatter@0:     '''Create database of online resources and update synonym database'''
cstrittmatter@0:     if _check_make(ontol_db, remake_cache, ontol_interest) == False:
cstrittmatter@0:         return
cstrittmatter@0: 
cstrittmatter@0:     conn = sqlite3.connect(ontol_db)
cstrittmatter@0:     c = conn.cursor()
cstrittmatter@0:     c.execute("CREATE TABLE non_standard_resource_ids (key TEXT, value TEXT, UNIQUE(key))")
cstrittmatter@0:     c.execute("CREATE TABLE standard_resource_labels (key TEXT, value TEXT, UNIQUE(key, value))")
cstrittmatter@0:     c.execute("""CREATE TABLE standard_resource_permutations (key TEXT, value TEXT,
cstrittmatter@0:                                                               UNIQUE(key, value))""")
cstrittmatter@0:     sonn = sqlite3.connect(synonym_db)
cstrittmatter@0:     s = sonn.cursor()
cstrittmatter@0: 
cstrittmatter@0:     ontol_dic = _get_ontols(ontol_interest)
cstrittmatter@0:     ontol_urls = [purl_link+x[1] for x in ontol_dic.values()]
cstrittmatter@0:     for ontol_name in ontol_dic:
cstrittmatter@0:         print('{:<40}'.format('\tRetrieving '+ontol_name[:15]+' terms'), end='\r')
cstrittmatter@0:         no_label = set()
cstrittmatter@0:         found_label = set()
cstrittmatter@0: 
cstrittmatter@0:         if not os.path.isfile(os.path.join(owl_dir,ontol_name+'.owl')):
cstrittmatter@0:             owl_download = requests.get(embl_ontologies+ontol_dic[ontol_name][1])
cstrittmatter@0:             with open(os.path.join(owl_dir,ontol_name+'.owl'), 'w', encoding='utf-8') as O_file:
cstrittmatter@0:                 O_file.write(owl_download.content.decode('utf-8'))
cstrittmatter@0: 
cstrittmatter@0:         with open(os.path.join(owl_dir,ontol_name+'.owl'),'r', encoding='utf-8') as OWL_file:
cstrittmatter@0:             owl_sections = _section_file(OWL_file, '<!-- '+purl_link)
cstrittmatter@0:             obo_ids = _labels_synonyms(owl_sections)
cstrittmatter@0:             OWL_file.seek(0)
cstrittmatter@0:             import_urls = [x for x in _get_imports(OWL_file) if x not in ontol_urls]            
cstrittmatter@0: 
cstrittmatter@0:         for imp_url in import_urls:
cstrittmatter@0:             imp_download = requests.get(imp_url)
cstrittmatter@0:             imp_file = os.path.join(owl_dir,imp_url.split('/')[-1])
cstrittmatter@0:             with open(imp_file, 'w', encoding='utf-8') as T_file:
cstrittmatter@0:                 T_file.write(imp_download.content.decode('utf-8'))
cstrittmatter@0:             # Assume possible to get duplicate names for different imports, so rewrite file
cstrittmatter@0:             with open(imp_file, 'r', encoding='utf-8') as T_file:
cstrittmatter@0:                 owl_sections = _section_file(T_file, '<!-- '+purl_link, stt_at='', end_at='')
cstrittmatter@0:                 obo_ids.extend(_labels_synonyms(owl_sections, have_label=True))
cstrittmatter@0:             os.remove(imp_file)
cstrittmatter@0:         # TODO: more elegant way to find which ids don't have labels and to remove duplicates?
cstrittmatter@0:         [no_label.add(x[0]) if x[1]=='' else found_label.add(x[0]) for x in obo_ids]
cstrittmatter@0:         no_label_found = list(no_label.difference(found_label))
cstrittmatter@0:         for ontol_term in obo_ids:
cstrittmatter@0:             if ontol_term[0] in no_label_found:
cstrittmatter@0:                 ontol_acc = ontr.Ontology_accession(':'+ontol_term[0])
cstrittmatter@0:                 if ontol_acc.label != 'unk':
cstrittmatter@0:                     if not re.search('^obsolete:', ontol_acc.label):
cstrittmatter@0:                         ontol_term[1] = ontol_acc.label
cstrittmatter@0:                 time.sleep(0.05)
cstrittmatter@0:             if ontol_term[1] == '':
cstrittmatter@0:                 continue
cstrittmatter@0:             ontol_label = punctuation_treatment(ontol_term[1])
cstrittmatter@0:             _db_insert(c, 'non_standard_resource_ids', ontol_term[0], ontol_term[1])
cstrittmatter@0:             _db_insert(c, 'standard_resource_labels', ontol_label, ontol_term[0])
cstrittmatter@0:             # Note from v 0.7: To limit performance overhead, we ignore resource labels with
cstrittmatter@0:             # more than 7 tokens, as permutating too many tokens can be costly.
cstrittmatter@0:             # Ignore NCBI taxon terms
cstrittmatter@0:             if len(word_tokenize(ontol_label)) < 7 and len(word_tokenize(ontol_label)) > 1:
cstrittmatter@0:                 if not re.search('NCBITaxon', ontol_term[0]):
cstrittmatter@0:                     for permutation in get_resource_label_permutations(ontol_label):
cstrittmatter@0:                         _db_insert(c,'standard_resource_permutations',permutation,ontol_term[0])
cstrittmatter@0:                 # Add abbreviated binomials from NCBITaxons; TODO: may get wrong combinations?
cstrittmatter@0:                 elif len(word_tokenize(ontol_label)) == 2:
cstrittmatter@0:                     bi_name = ontol_label.split()
cstrittmatter@0:                     _db_insert(c, 'standard_resource_permutations',
cstrittmatter@0:                                bi_name[0][0]+' '+bi_name[1], ontol_term[0])
cstrittmatter@0:             for syn_term in ontol_term[2]:
cstrittmatter@0:                 _db_insert(s,'label_synonyms',punctuation_treatment(str(syn_term)),ontol_label)
cstrittmatter@0:         conn.commit()
cstrittmatter@0:         sonn.commit()
cstrittmatter@0: 
cstrittmatter@0:     conn.close()
cstrittmatter@0:     sonn.close()
cstrittmatter@0:     _pickle_save(ontol_interest, os.path.join(owl_dir,'cached_ontologies.pickle'))
cstrittmatter@0:     return