fileparser.py

import csv
from connections import get_db_connection, get_redis_client
from normalization import compute_search_key
con = get_db_connection()
db = con

r = get_redis_client()


def save_words(csvf, word_set_id, orig_set_id=''):
    """Open the CSV file and read its contents into memory

    Args:
        csvf (file): the file to process
        word_set_id (str): the translated word set to add to
        orig_set_id (str, optional): The original language word set to add to. Defaults to ''.

    Returns:
        len (int): the number of words added
    """
    words = []
    headings = []
    additional_set_id = None

    if orig_set_id not in ('', None):
        try:
            additional_set_id = int(orig_set_id)
        except (TypeError, ValueError):
            additional_set_id = None

    with open(csvf, "r", encoding='utf-8-sig') as file:
        reader = csv.reader(file, delimiter=',')

        # Create dictionary keys
        for row in reader:
            i = 0
            while (i < len(row)):
                headings.append(row[i])
                i += 1
            break

        # Save STR values to each person
        for row in reader:
            i = 0
            word = {}

            while (i < len(row)):
                key = str(headings[i])
                value = row[i]
                word[key] = value
                i += 1
            words.append(word)

    # Get heading names
    lang1 = headings[0]  # Original Language
    lang1p = headings[1]  # Original transliteration
    lang2 = headings[2]  # Translation Language
    lang2p = headings[3]  # Translation transliteration
    wtype = headings[4]  # Type of word (noun, verb)

    # Get char code from language
    orig_lang_row = db.execute(
        "SELECT id, charcode FROM languages WHERE name = ?",
        (lang1,),
    ).fetchone()
    trans_lang_row = db.execute(
        "SELECT id, charcode FROM languages WHERE name = ?",
        (lang2,),
    ).fetchone()

    orig_lang_id = int(orig_lang_row['id'])
    trans_lang_id = int(trans_lang_row['id'])
    orig_lang_code = (orig_lang_row['charcode'] or '').lower()
    trans_lang_code = (trans_lang_row['charcode'] or '').lower()

    for w in words:
        # Normalize and validate word_type value from CSV row.
        wt_val = (w.get(wtype) or '').strip()
        if wt_val == '':
            raise ValueError(f"CSV missing word_type value for row: {w}")

        # Lookup existing word_type; if missing, create it so uploads don't fail.
        wt_row = db.execute("SELECT id FROM word_type WHERE type = ?", (wt_val,)).fetchone()
        if wt_row is None:
            new_wt = db.execute("INSERT INTO word_type (type) VALUES (?)", (wt_val,))
            con.commit()
            word_type_id = int(new_wt.lastrowid)
        else:
            word_type_id = int(wt_row['id'])

        new_orig_lemma_id = db.execute(
            "INSERT INTO lemma (language_id, pos_id, pronunciation, audiopath) VALUES (?, ?, ?, ?)",
            (orig_lang_id, word_type_id, w[lang1p], None),
        ).lastrowid
        db.execute(
            "INSERT INTO lemma_form (lemma_id, language_id, form_type, script, value, search_key, is_primary) VALUES (?, ?, ?, ?, ?, ?, ?)",
            (
                new_orig_lemma_id,
                orig_lang_id,
                'surface',
                'Hebr' if orig_lang_code == 'he' else 'Latn',
                w[lang1],
                compute_search_key(w[lang1], orig_lang_code),
                1,
            ),
        )
        new_orig_sense_id = db.execute(
            "INSERT INTO sense (lemma_id, part_of_speech, is_primary) VALUES (?, ?, ?)",
            (new_orig_lemma_id, word_type_id, 1),
        ).lastrowid

        new_trans_lemma_id = db.execute(
            "INSERT INTO lemma (language_id, pos_id, pronunciation, audiopath) VALUES (?, ?, ?, ?)",
            (trans_lang_id, word_type_id, w[lang2p], None),
        ).lastrowid
        db.execute(
            "INSERT INTO lemma_form (lemma_id, language_id, form_type, script, value, search_key, is_primary) VALUES (?, ?, ?, ?, ?, ?, ?)",
            (
                new_trans_lemma_id,
                trans_lang_id,
                'surface',
                'Hebr' if trans_lang_code == 'he' else 'Latn',
                w[lang2],
                compute_search_key(w[lang2], trans_lang_code),
                1,
            ),
        )

        # Create sense translation and set items for the new words
        new_trans_sense_id = db.execute(
            "INSERT INTO sense (lemma_id, part_of_speech, is_primary) VALUES (?, ?, ?)",
            (new_trans_lemma_id, word_type_id, 1),
        ).lastrowid

        db.execute(
            "INSERT INTO set_item (word_set_id, sense_id, prompt_mode) VALUES (?, ?, ?)",
            (int(word_set_id), new_trans_sense_id, 'show_all_forms'),
        )

        # If an additional original set ID was provided and is valid, 
        # add the original sense to that set as well.
        if additional_set_id is not None:
            db.execute(
                "INSERT INTO set_item (word_set_id, sense_id, prompt_mode) VALUES (?, ?, ?)",
                (additional_set_id, new_orig_sense_id, 'show_all_forms'),
            )

        db.execute(
            "INSERT INTO sense_translation (source_sense_id, target_sense_id, relation_type) VALUES (?, ?, ?)",
            (new_orig_sense_id, new_trans_sense_id, 'exact'),
        )
        db.execute(
            "INSERT INTO sense_translation (source_sense_id, target_sense_id, relation_type) VALUES (?, ?, ?)",
            (new_trans_sense_id, new_orig_sense_id, 'exact'),
        )
        con.commit()
    file.close()
    return len(words)