<commit_before>import sqlite3
import datetime
import csv
from datetime import date

def create_schema(conn):
    with conn:
        conn.execute('''CREATE TABLE game
            (id integer primary key autoincrement,
            title text, release_year integer, linux integer,
            play_more integer, couch integer, passes integer,
            via text, eternal integer)''')

        conn.execute('''CREATE TABLE own
            (game_id integer, storefront text,
            foreign key (game_id) references game(id))''') 

        conn.execute('''CREATE TABLE sessions
            (game_id integer, started datetime,
            outcome text,
            foreign key (game_id) references game(id))''')


def import_gdoc_sessions(conn, rows):
    with conn:
        skipped = 0
        for title, start_date, status, keep, notes in rows:
            if title == 'Title' or len(title) == 0:
                continue
            gid = None 
            for game_id in conn.execute("""SELECT id FROM game WHERE title = ?""", (title,)):
                gid = game_id[0]
                break

            if gid is None:
                skipped += 1
                gid = add_game(conn, title, None, None, None, None,
                        0, None, False, [])

            # munge
            status = None if status == '' else status
            if len(start_date) == 0:
                start_date = None
            else:
                day, month, year = start_date.split('/')
                start_date = datetime.datetime(int(year), int(month), int(day))

            conn.execute("""INSERT INTO
                sessions(game_id, started, outcome)
                VALUES (?, ?, ?)""", (gid, start_date, status))

    print("Added %i games due to mismatched titles." % skipped)


def add_game(conn, title, release_year, linux, play_more, couch, passes, via, eternal, storefronts):
    c = conn.cursor()

    c.execute('''insert into
            game(title, release_year, linux, play_more,
                couch, passes, via, eternal)
            values(?, ?, ?, ?, ?, ?, ?, ?)''',
            (title, release_year, linux, play_more,
                couch, passes, via, eternal))
    game_id = c.lastrowid
    assert(game_id != None)

    for sf in storefronts:
        if sf == '':
            continue
        c.execute('''insert into
            own(game_id, storefront) values(?, ?)''',
            (game_id, sf))

    conn.commit()
    return game_id

def import_gdoc_games(conn, rows):
    for title, release_year, linux, play_more, owned_on, couch, passes, via in rows:
        if title == 'title' or title == '':
            continue

        # munge data
        release_year = None if release_year == '' else int(release_year)
        linux = linux == 1 or linux == '1'
        play_more = play_more == 1 or play_more == '1'
        couch = couch == 1 or couch == '1'
        passes = 0 if passes == '' or passes == 'eternal' else int(passes)
        eternal = 1 if passes == 'eternal' else 0

        add_game(conn, title, release_year, linux, play_more, couch, passes, via, eternal, owned_on.split(','))


def dump_csvs(conn, fn_prefix):
    with conn:
        with open("%s_game.csv" % fn_prefix, 'w') as game:
            writer = csv.writer(game)
            writer.writerow(['id', 'title', 'release_year', 'linux',
                'play_more', 'couch', 'passes', 'via',
                'eternal'])
            for row in conn.execute('SELECT * FROM game'):
                writer.writerow([row['id'], row['title'], row['release_year'],
                        row['linux'], row['play_more'], row['couch'],
                        row['passes'], row['via'], row['eternal']])

        with open("%s_own.csv" % fn_prefix, 'w') as own:
            writer = csv.writer(own)
            writer.writerow(['game_id', 'storefront'])
            for row in conn.execute('SELECT * FROM own'):
                writer.writerow([row['game_id'], row['storefront']])

        with open("%s_session.csv" % fn_prefix, 'w') as session:
            writer = csv.writer(session)
            writer.writerow(['game_id', 'started', 'outcome'])
            for row in conn.execute('SELECT * FROM sessions'):
                writer.writerow([row['game_id'], row['started'], row['outcome']])

def load_csvs(conn, fn_prefix):
    with conn:
        with open("%s_game.csv" % fn_prefix, 'r') as game:
            reader = csv.reader(game)
            for i, row in enumerate(reader):
                if i == 0: # skip title line
                    continue
                conn.execute('''insert into game(id, title, release_year,
                    linux, play_more, couch, passes, via, eternal)
                    values (?, ?, ?, ?, ?, ?, ?, ?, ?)''', row)
                    
        with open("%s_own.csv" % fn_prefix, 'r') as own:
            reader = csv.reader(own)
            for i, row in enumerate(reader):
                if i == 0: # skip title line
                    continue
                conn.execute('''insert into own(game_id, storefront)
                    values (?, ?)''', row)

        with open("%s_session.csv" % fn_prefix, 'r') as session:
            reader = csv.reader(session)
            for i, row in enumerate(reader):
                if i == 0: # skip title line
                    continue
                conn.execute('''insert into sessions(game_id, started,
                    outcome)
                    values (?, ?, ?)''', row)

def storefronts(conn, gid):
    with conn:
        rows = conn.execute('SELECT storefront FROM own WHERE game_id=?', (gid,))
        return [row['storefront'] for row in rows]

def select_random_games(conn, n = 1, before_this_year = None, linux = None,
        play_more = True, couch = None, max_passes = 2, owned=True,
        exclude_ids = []):
    with conn:
        # Construct query
        conditions = []

        if before_this_year is True:
            conditions.append('release_year < ' + str(date.today().year))
        elif before_this_year is False:
            conditions.append('release_year == ' + str(date.today().year))

        if linux == True:
            conditions.append('linux == 1')
        elif linux == False:
            conditions.append('linux == 0')

        if play_more == True:
            conditions.append('play_more == 1')
        elif play_more == False:
            conditions.append('play_more == 0')

        if couch == True:
            conditions.append('couch == 1')
        elif couch == False:
            conditions.append('couch == 0')

        conditions.append('passes <= ' + str(max_passes))

        if owned:
            select = '''SELECT id, title, release_year,
                linux, play_more, couch, passes, via, eternal
                FROM own JOIN game ON own.game_id=game.id'''
        else:
            select = '''SELECT id, title, release_year,
                linux, play_more, couch, passes, via, eternal
                FROM game'''

        query = select + ' WHERE ' + ' AND '.join(conditions) 

        # get active session game_ids
        active_ids = [row['game_id'] for row in show_sessions(conn)]

        # exclude games with ids in exclude list
        # and games with active sessions
        # this is slower than doing this with joins, but for our
        # purposes it is sufficient
        if len(exclude_ids + active_ids) > 0:
            query += ' AND id NOT IN (' + ','.join([str(i) for i in exclude_ids + active_ids]) + ')'

        query += ' ORDER BY RANDOM() LIMIT ' + str(n)
        return list(conn.execute(query))

def show_sessions(conn, active = True, status = None,
        session_year = date.today().year):
    with conn:
        conditions = []

        if active is True:
            conditions.append('outcome is null OR outcome == ""')
        elif active is False:
            conditions.append('outcome is not null AND outcome != ""')

        if status is not None:
            conditions.append('outcome == "' + status + '"')
<commit_msg>games will not be selected if they have postponement dates<commit_after>import sqlite3
import datetime
import csv
from datetime import date

def create_schema(conn):
    with conn:
        conn.execute('''CREATE TABLE game
            (id integer primary key autoincrement,
            title text, release_year integer, linux integer,
            play_more integer, couch integer, passes integer,
            via text, eternal integer, next_valid_date datetime)''')

        conn.execute('''CREATE TABLE own
            (game_id integer, storefront text,
            foreign key (game_id) references game(id))''') 

        conn.execute('''CREATE TABLE sessions
            (game_id integer, started datetime,
            outcome text,
            foreign key (game_id) references game(id))''')


def import_gdoc_sessions(conn, rows):
    with conn:
        skipped = 0
        for title, start_date, status, keep, notes in rows:
            if title == 'Title' or len(title) == 0:
                continue
            gid = None 
            for game_id in conn.execute("""SELECT id FROM game WHERE title = ?""", (title,)):
                gid = game_id[0]
                break

            if gid is None:
                skipped += 1
                gid = add_game(conn, title, None, None, None, None,
                        0, None, False, [])

            # munge
            status = None if status == '' else status
            if len(start_date) == 0:
                start_date = None
            else:
                day, month, year = start_date.split('/')
                start_date = datetime.datetime(int(year), int(month), int(day))

            conn.execute("""INSERT INTO
                sessions(game_id, started, outcome)
                VALUES (?, ?, ?)""", (gid, start_date, status))

    print("Added %i games due to mismatched titles." % skipped)


def add_game(conn, title, release_year, linux, play_more, couch, passes, via, eternal, storefronts):
    c = conn.cursor()

    c.execute('''insert into
            game(title, release_year, linux, play_more,
                couch, passes, via, eternal)
            values(?, ?, ?, ?, ?, ?, ?, ?)''',
            (title, release_year, linux, play_more,
                couch, passes, via, eternal))
    game_id = c.lastrowid
    assert(game_id != None)

    for sf in storefronts:
        if sf == '':
            continue
        c.execute('''insert into
            own(game_id, storefront) values(?, ?)''',
            (game_id, sf))

    conn.commit()
    return game_id

def import_gdoc_games(conn, rows):
    for title, release_year, linux, play_more, owned_on, couch, passes, via in rows:
        if title == 'title' or title == '':
            continue

        # munge data
        release_year = None if release_year == '' else int(release_year)
        linux = linux == 1 or linux == '1'
        play_more = play_more == 1 or play_more == '1'
        couch = couch == 1 or couch == '1'
        passes = 0 if passes == '' or passes == 'eternal' else int(passes)
        eternal = 1 if passes == 'eternal' else 0

        add_game(conn, title, release_year, linux, play_more, couch, passes, via, eternal, owned_on.split(','))


def dump_csvs(conn, fn_prefix):
    with conn:
        with open("%s_game.csv" % fn_prefix, 'w') as game:
            writer = csv.writer(game)
            writer.writerow(['id', 'title', 'release_year', 'linux',
                'play_more', 'couch', 'passes', 'via',
                'eternal', 'next_valid_date'])
            for row in conn.execute('SELECT * FROM game'):
                writer.writerow([row['id'], row['title'], row['release_year'],
                        row['linux'], row['play_more'], row['couch'],
                        row['passes'], row['via'], row['eternal'],
                        row['next_valid_date']])

        with open("%s_own.csv" % fn_prefix, 'w') as own:
            writer = csv.writer(own)
            writer.writerow(['game_id', 'storefront'])
            for row in conn.execute('SELECT * FROM own'):
                writer.writerow([row['game_id'], row['storefront']])

        with open("%s_session.csv" % fn_prefix, 'w') as session:
            writer = csv.writer(session)
            writer.writerow(['game_id', 'started', 'outcome'])
            for row in conn.execute('SELECT * FROM sessions'):
                writer.writerow([row['game_id'], row['started'], row['outcome']])

def load_csvs(conn, fn_prefix):
    with conn:
        with open("%s_game.csv" % fn_prefix, 'r') as game:
            reader = csv.reader(game)
            for i, row in enumerate(reader):
                if i == 0: # skip title line
                    continue
                # handle addition of rows
                if len(row) == 9:
                    row.append(None)
                row = [(None if len(r) == 0 else r) for r in row]
                conn.execute('''insert into game(id, title, release_year,
                    linux, play_more, couch, passes, via, eternal,
                    next_valid_date)
                    values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''', row)
                    
        with open("%s_own.csv" % fn_prefix, 'r') as own:
            reader = csv.reader(own)
            for i, row in enumerate(reader):
                if i == 0: # skip title line
                    continue
                conn.execute('''insert into own(game_id, storefront)
                    values (?, ?)''', row)

        with open("%s_session.csv" % fn_prefix, 'r') as session:
            reader = csv.reader(session)
            for i, row in enumerate(reader):
                if i == 0: # skip title line
                    continue
                conn.execute('''insert into sessions(game_id, started,
                    outcome)
                    values (?, ?, ?)''', row)

def storefronts(conn, gid):
    with conn:
        rows = conn.execute('SELECT storefront FROM own WHERE game_id=?', (gid,))
        return [row['storefront'] for row in rows]

def select_random_games(conn, n = 1, before_this_year = None, linux = None,
        play_more = True, couch = None, max_passes = 2, owned=True,
        exclude_ids = []):
    with conn:
        # Construct query
        conditions = []

        if before_this_year is True:
            conditions.append('release_year < ' + str(date.today().year))
        elif before_this_year is False:
            conditions.append('release_year == ' + str(date.today().year))

        if linux == True:
            conditions.append('linux == 1')
        elif linux == False:
            conditions.append('linux == 0')

        if play_more == True:
            conditions.append('play_more == 1')
        elif play_more == False:
            conditions.append('play_more == 0')

        if couch == True:
            conditions.append('couch == 1')
        elif couch == False:
            conditions.append('couch == 0')

        conditions.append('passes <= ' + str(max_passes))

        if owned:
            select = '''SELECT id, title, release_year,
                linux, play_more, couch, passes, via, eternal,
                next_valid_date
                FROM own JOIN game ON own.game_id=game.id'''
        else:
            select = '''SELECT id, title, release_year,
                linux, play_more, couch, passes, via, eternal,
                next_valid_date
                FROM game'''

        query = select + ' WHERE ' + ' AND '.join(conditions) 

        day, month, year = date.today().day, date.today().month, date.today().year
        # exclude games that have been delayed for another proposal
        query += ''' AND (next_valid_date IS NULL OR
            next_valid_date <= "%i-%i-%i")''' % (year, month, day)

        # get active session game_ids
        active_ids = [row['game_id'] for row in show_sessions(conn)]

        # exclude games with ids in exclude list
        # and games with active sessions
        # this is slower than doing this with joins, but for our
        # purposes it is sufficient
        if len(exclude_ids + active_ids) > 0:
            query += ' AND id NOT IN (' + ','.join([str(i) for i in exclude_ids + active_ids]) + ')'

        query += ' ORDER BY RANDOM() LIMIT ' + str(n)
        return list(conn.execute(query))

def show_sessions(conn, active = True, status = None,
        session_year = date.today().year):
    with conn:
        conditions = []

        if active is True:
            conditions.append('outcome is null OR outcome == ""')
        elif active is False:
            conditions.append('outcome is not null AND outcome != ""')

        if status is not None:
            conditions.append('outcome == "' + status + '"')

{'fraction_non_alphanumeric': 0.12639493942829863, 'fraction_numerical': 0.00346124007877305, 'mean_word_length': 1.8938007252633398, 'pattern_counts': {'":': 0, '<': 8, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6895684', 'n_tokens_mistral': 4884, 'n_tokens_neox': 4601, 'n_words': 1391}