Progetto_2023-OVI/flask_be/data_interface_sqlite3/query_handlers.py

import sqlite3

# Refactor out? NO, for now!
import pandas as pd


# Just for historical memory!
def simpleQueryHandlerV0(data, path):

    theSimpleQuery = "SELECT norm FROM form WHERE norm LIKE '" + data + "'"
    
    con = sqlite3.connect(path + "/db/test_x.db")
    cur = con.cursor()

    queryReponse = cur.execute(theSimpleQuery)
    results = queryReponse.fetchall()

    return results


# Actual thing, first version
class queryHandlerBasicSqlite:

    def __init__(self, dbpath):
        self.dbpath = dbpath
    
    def query(self, queryData, pandas=False):

        # Formerly the query string was pre-generated outside and
        # sent here in lieu of the query data
        # Now the method processes a query data OBJECT and creates the query
        # accordingly
        queryString = prepareQueryString(queryData)

        dbpath = self.dbpath
        
        con = sqlite3.connect(f"file:{dbpath}?mode=ro", uri=True)
        # PANDAS?
        if pandas:
            answer_table = pd.read_sql(queryString, con)
            return answer_table
        
        else:
            con.row_factory = dict_factory
            cur = con.cursor()
            queryReponse = cur.execute(queryString)
            results = queryReponse.fetchall()
            return results



# Utilities

def prepareQueryString(queryData):

    queryType = queryData['queryType']

    if queryType=='forma':
        joinedQueryData = " OR spec LIKE ".join(queryData['data'])
        if len(queryData['dataNorm'])==0:
            return f"SELECT spec AS forma, nocc AS occ, cod FROM form WHERE spec LIKE {joinedQueryData} ORDER BY idfor"
        else:
            joinedQueryDataNorm = " OR norm LIKE ".join(queryData['dataNorm'])
            return f"SELECT DISTINCT spec AS forma, nocc AS occ, cod FROM form WHERE (spec LIKE {joinedQueryData}) OR (norm LIKE {joinedQueryDataNorm}) ORDER BY idfor"

    elif queryType=='lemma':
        joinedQueryData = " OR spec LIKE ".join(queryData['data'])
        if len(queryData['dataNorm'])==0:
            return f"SELECT spec AS lemma, cat AS cat_gr, omo AS disambiguatore, nocc AS occ, cod FROM lem WHERE spec LIKE {joinedQueryData} ORDER BY idlem"
        else:
            joinedQueryDataNorm = " OR norm LIKE ".join(queryData['dataNorm'])
            return f"SELECT DISTINCT spec AS lemma, cat AS cat_gr, omo AS disambiguatore, nocc AS occ, cod FROM lem WHERE (spec LIKE {joinedQueryData}) OR (norm LIKE {joinedQueryDataNorm}) ORDER BY idlem"

    elif queryType=='lemmaForma':
        joinedQueryData = " OR form.spec LIKE ".join(queryData['data'])
        if len(queryData['dataNorm'])==0:
            return f"SELECT lem.spec AS lemma, lem.cat AS cat_gr, form.spec AS forma, lem.omo AS disambiguatore, pfl.nocc AS occ, lem.cod FROM pfl INNER JOIN form ON form.cod = pfl.forma INNER JOIN lem ON lem.cod != 0 AND lem.cod = pfl.lemma WHERE lem.spec LIKE {joinedQueryData} ORDER BY lem.idlem"
        else:
            joinedQueryData = " OR lem.spec LIKE ".join(queryData['data'])
            joinedQueryDataNorm = " OR lem.norm LIKE ".join(queryData['dataNorm'])
            return f"SELECT DISTINCT lem.spec AS lemma, lem.cat AS cat_gr, form.spec AS forma, lem.omo AS disambiguatore, pfl.nocc AS occ, lem.cod FROM pfl INNER JOIN form ON form.cod = pfl.forma INNER JOIN lem ON lem.cod = pfl.lemma WHERE (lem.spec LIKE {joinedQueryData}) OR (lem.norm LIKE {joinedQueryDataNorm}) ORDER BY lem.idlem"

    elif queryType=='formaLemma':
        joinedQueryData = " OR form.spec LIKE ".join(queryData['data'])
        if len(queryData['dataNorm'])==0:
            return f"SELECT form.spec AS forma, lem.spec AS lemma, lem.cat AS cat_gr, lem.omo AS disambiguatore, pfl.nocc AS occ, form.cod FROM pfl INNER JOIN form ON form.cod = pfl.forma INNER JOIN lem ON lem.cod = pfl.lemma WHERE form.spec LIKE {joinedQueryData} ORDER BY form.idfor"
        else:
            joinedQueryDataNorm = " OR form.norm LIKE ".join(queryData['dataNorm'])
            return f"SELECT DISTINCT form.spec AS forma, lem.spec AS lemma, lem.cat AS cat_gr, lem.omo AS disambiguatore, pfl.nocc AS occ, form.cod FROM pfl INNER JOIN form ON form.cod = pfl.forma INNER JOIN lem ON lem.cod = pfl.lemma WHERE (form.spec LIKE {joinedQueryData}) OR (form.norm LIKE {joinedQueryDataNorm}) ORDER BY form.idfor"

    else:
        raise ValueError('Unrecognized query type')


def dict_factory(cursor, row):
    fields = [column[0] for column in cursor.description]
    return {key: value for key, value in zip(fields, row)}