<commit_before>from lxml import html
import pymysql.cursors
import sqlite3
import urllib.request
from urllib.error import URLError
import os, sys
import subprocess as sbp
import re
import json
import fileinput
import time
import gzip
from .autosql import AutoSqlDeclaration

SCRIPT_DIRECTORY = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
# Cache the parsed config file after the first access
config_yaml = None

BEDLIKE_FORMATS = {
    # format: (as_location, bed_type)
    'bed': (None, None),
    'genePred': ('autosql/genePredFull.as', 'bed12+8'),
    'psl': ('autosql/bigPsl.as', 'bed12+12'),
    'gvf': ('autosql/bigGvf.as', 'bed8+3'),
    'narrowPeak': ('autosql/bigNarrowPeak.as', 'bed6+4'),
    'rmsk' : ('autosql/bigRmsk.as', 'bed6+6')
}
# For some reason bedToBigBed fails to create an extraIndex for BED files above this
# length, roughly 27GB. Therefore, it's best to disable extraIndex'es for such large BED files.
MAX_INDEXABLE_BED_SIZE = 27000 * 1000000

class TrackInfo(dict):
    def __init__(self, *args):
        self.groups = {}
        dict.__init__(self, args)


def open_ucsc_yaml():
    global config_yaml
    if config_yaml is not None:
        return config_yaml
    try:
        import yaml
        with open(os.path.join(os.path.dirname(__file__), '../../ucsc.yaml'), 'r') as handle:
            config_yaml = yaml.load(handle)
    except (ImportError, FileNotFoundError) as e:
        sys.exit('FATAL: could not read ../ucsc.yaml.')
    return config_yaml
    

def print_time():
    """
    :return: current time string
    """
    return time.strftime('%X %x')


def create_sqllite3_db(xorganism):
    """
    Checks if DB is created and
    if not create it.
    Returns DB location.
    """
    db_loc = './{}/tracks.db'.format(xorganism)

    new_db = True
    if os.path.isfile(db_loc):
        new_db = False

    xconn = sqlite3.connect(db_loc)
    if new_db:
        xconn.execute("CREATE TABLE tracks (name, displayName, type, grp, grpLabel, parentTrack, trackLabel, "
                      "shortLabel, longLabel, priority, location, html longblob, updateDate, "
                      "remoteSettings, localSettings, compositeTrack, srt);")
        xconn.execute("CREATE UNIQUE INDEX ix_tracks_name on tracks(name);")
    return db_loc, xconn

def get_last_local_updates(localconn):
    return dict(localconn.execute('SELECT name, updateDate FROM tracks;').fetchall())


def get_ucsc_base_url():
    return open_ucsc_yaml()['browser_hosts']['authoritative']

def get_remote_table():
    return get_ucsc_base_url() + open_ucsc_yaml()['browser_urls']['tables']

def get_mysql_host():
    return open_ucsc_yaml()['browser_mysql']['authoritative']

def get_downloads_base_url():
    return open_ucsc_yaml()['data_urls']['downloads']

def get_downloads_table_tsv():
    return open_ucsc_yaml()['data_urls']['table_tsv']
    
def get_wig_as_bigwig():
    return open_ucsc_yaml()['data_urls']['wig_as_bigwig']

def get_remote_tracks():
    return get_ucsc_base_url() + open_ucsc_yaml()['browser_urls']['tracks']

def get_remote_item_url():
    return get_ucsc_base_url() + open_ucsc_yaml()['browser_urls']['item_detail']


def cmd_exists(cmd):
    """
    Check if a given executable is on $PATH
    For more on the `type` shell builtin, see https://bash.cyberciti.biz/guide/Type_command
    """
    return sbp.call("type " + cmd, shell=True, stdout=sbp.PIPE, stderr=sbp.PIPE) == 0

def url_exists(url):
    req = urllib.request.Request(url, method="HEAD")
    try:
        urllib.request.urlopen(req)
    except URLError:
        return False
    return True

def qups(in_cmd, ucur):
    """
    Executes MySQL query and returns parsed results
    """
    ucur.connection.ping(reconnect=True)
    ucur.execute(in_cmd)
    return ucur.fetchall()


def connect_to_ucsc_mysql(host, db):
    try:
        conn = pymysql.connect(host=host, user='genome', database=db)
        return conn.cursor()
    except pymysql.err.InternalError:
        print('WARNING ({}): No MySQL tables found for "{}". Omitting.'.format(print_time(), organism))
        return None


def parse_trackdb_settings(settings):
    """
    Parses a `settings` field from a UCSC trackDb table into a dictionary of keys -> values
    See also: https://genome.ucsc.edu/goldenPath/help/trackDb/trackDbHub.html
    """
    parsed_settings = dict()
    for line in (settings).decode("latin1").replace("\\\n", " ").split("\n"):
        try: key, value = re.split(r'\s+', line, 1)
        except ValueError: continue
        parsed_settings[key] = value
    return parsed_settings


def get_last_remote_updates(xcur, db, table_names):
    """
    Fetches table update times
    """
    in_clause = ','.join(['"' + table_name + '"' for table_name in table_names])
    return dict(qups(("SELECT TABLE_NAME, UPDATE_TIME FROM information_schema.tables "
                      "WHERE TABLE_SCHEMA='{}' AND TABLE_NAME IN ({})").format(db, in_clause), xcur))


def get_last_location_and_bed_plus_fields(localconn, track_name):
    """
    Fetches these metadata for a track entry that was saved to the local database during the last update
    """
    prev_row = localconn.execute(
            'SELECT location, localSettings FROM tracks WHERE name="{}";'.format(track_name)).fetchone()
    if prev_row is None:
        raise RuntimeError("Previous entry for track {} could not be retrieved".format(track_name))
    settings = json.loads(prev_row[1])
    return prev_row[0], settings['bedPlusFields'].split(',') if settings.get('bedPlusFields') else None


def get_numrows(xcur, table_name):
    return qups("SELECT COUNT(*) FROM {}".format(table_name), xcur)[0][0]


def get_organisms_list(host='', prefix=None):
    """
    Get list of organism databases that we may want to scrape from UCSC
    """
    prefix_query = ""
    if prefix is not None:
        prefix_query = " AND name LIKE '{}%'".format(prefix)
    try:<commit_msg>coerce datetimes to strings<commit_after>from lxml import html
import pymysql.cursors
import sqlite3
import urllib.request
from urllib.error import URLError
import os, sys
import subprocess as sbp
import re
import json
import fileinput
import time
import gzip
from .autosql import AutoSqlDeclaration

SCRIPT_DIRECTORY = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
# Cache the parsed config file after the first access
config_yaml = None

BEDLIKE_FORMATS = {
    # format: (as_location, bed_type)
    'bed': (None, None),
    'genePred': ('autosql/genePredFull.as', 'bed12+8'),
    'psl': ('autosql/bigPsl.as', 'bed12+12'),
    'gvf': ('autosql/bigGvf.as', 'bed8+3'),
    'narrowPeak': ('autosql/bigNarrowPeak.as', 'bed6+4'),
    'rmsk' : ('autosql/bigRmsk.as', 'bed6+6')
}
# For some reason bedToBigBed fails to create an extraIndex for BED files above this
# length, roughly 27GB. Therefore, it's best to disable extraIndex'es for such large BED files.
MAX_INDEXABLE_BED_SIZE = 27000 * 1000000

class TrackInfo(dict):
    def __init__(self, *args):
        self.groups = {}
        dict.__init__(self, args)


def open_ucsc_yaml():
    global config_yaml
    if config_yaml is not None:
        return config_yaml
    try:
        import yaml
        with open(os.path.join(os.path.dirname(__file__), '../../ucsc.yaml'), 'r') as handle:
            config_yaml = yaml.load(handle)
    except (ImportError, FileNotFoundError) as e:
        sys.exit('FATAL: could not read ../ucsc.yaml.')
    return config_yaml
    

def print_time():
    """
    :return: current time string
    """
    return time.strftime('%X %x')


def create_sqllite3_db(xorganism):
    """
    Checks if DB is created and
    if not create it.
    Returns DB location.
    """
    db_loc = './{}/tracks.db'.format(xorganism)

    new_db = True
    if os.path.isfile(db_loc):
        new_db = False

    xconn = sqlite3.connect(db_loc)
    if new_db:
        xconn.execute("CREATE TABLE tracks (name, displayName, type, grp, grpLabel, parentTrack, trackLabel, "
                      "shortLabel, longLabel, priority, location, html longblob, updateDate, "
                      "remoteSettings, localSettings, compositeTrack, srt);")
        xconn.execute("CREATE UNIQUE INDEX ix_tracks_name on tracks(name);")
    return db_loc, xconn

def get_last_local_updates(localconn):
    return dict(localconn.execute('SELECT name, updateDate FROM tracks;').fetchall())


def get_ucsc_base_url():
    return open_ucsc_yaml()['browser_hosts']['authoritative']

def get_remote_table():
    return get_ucsc_base_url() + open_ucsc_yaml()['browser_urls']['tables']

def get_mysql_host():
    return open_ucsc_yaml()['browser_mysql']['authoritative']

def get_downloads_base_url():
    return open_ucsc_yaml()['data_urls']['downloads']

def get_downloads_table_tsv():
    return open_ucsc_yaml()['data_urls']['table_tsv']
    
def get_wig_as_bigwig():
    return open_ucsc_yaml()['data_urls']['wig_as_bigwig']

def get_remote_tracks():
    return get_ucsc_base_url() + open_ucsc_yaml()['browser_urls']['tracks']

def get_remote_item_url():
    return get_ucsc_base_url() + open_ucsc_yaml()['browser_urls']['item_detail']


def cmd_exists(cmd):
    """
    Check if a given executable is on $PATH
    For more on the `type` shell builtin, see https://bash.cyberciti.biz/guide/Type_command
    """
    return sbp.call("type " + cmd, shell=True, stdout=sbp.PIPE, stderr=sbp.PIPE) == 0

def url_exists(url):
    req = urllib.request.Request(url, method="HEAD")
    try:
        urllib.request.urlopen(req)
    except URLError:
        return False
    return True

def qups(in_cmd, ucur):
    """
    Executes MySQL query and returns parsed results
    """
    ucur.connection.ping(reconnect=True)
    ucur.execute(in_cmd)
    return ucur.fetchall()


def connect_to_ucsc_mysql(host, db):
    try:
        conn = pymysql.connect(host=host, user='genome', database=db)
        return conn.cursor()
    except pymysql.err.InternalError:
        print('WARNING ({}): No MySQL tables found for "{}". Omitting.'.format(print_time(), organism))
        return None


def parse_trackdb_settings(settings):
    """
    Parses a `settings` field from a UCSC trackDb table into a dictionary of keys -> values
    See also: https://genome.ucsc.edu/goldenPath/help/trackDb/trackDbHub.html
    """
    parsed_settings = dict()
    for line in (settings).decode("latin1").replace("\\\n", " ").split("\n"):
        try: key, value = re.split(r'\s+', line, 1)
        except ValueError: continue
        parsed_settings[key] = value
    return parsed_settings


def get_last_remote_updates(xcur, db, table_names):
    """
    Fetches table update times
    """
    in_clause = ','.join(['"' + table_name + '"' for table_name in table_names])
    result = qups(("SELECT TABLE_NAME, UPDATE_TIME FROM information_schema.tables "
                   "WHERE TABLE_SCHEMA='{}' AND TABLE_NAME IN ({})").format(db, in_clause), xcur)
    return dict([(row[0], str(row[1])) for row in result])


def get_last_location_and_bed_plus_fields(localconn, track_name):
    """
    Fetches these metadata for a track entry that was saved to the local database during the last update
    """
    prev_row = localconn.execute(
            'SELECT location, localSettings FROM tracks WHERE name="{}";'.format(track_name)).fetchone()
    if prev_row is None:
        raise RuntimeError("Previous entry for track {} could not be retrieved".format(track_name))
    settings = json.loads(prev_row[1])
    return prev_row[0], settings['bedPlusFields'].split(',') if settings.get('bedPlusFields') else None


def get_numrows(xcur, table_name):
    return qups("SELECT COUNT(*) FROM {}".format(table_name), xcur)[0][0]


def get_organisms_list(host='', prefix=None):
    """
    Get list of organism databases that we may want to scrape from UCSC
    """
    prefix_query = ""
    if prefix is not None:
        prefix_query = " AND name LIKE '{}%'".format(prefix)
    try:
{'fraction_non_alphanumeric': 0.1272209185383842, 'fraction_numerical': 0.006369426751592357, 'mean_word_length': 3.6558720249707375, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 5, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 2}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2736934', 'n_tokens_mistral': 4026, 'n_tokens_neox': 3891, 'n_words': 979}