<commit_before># coding: utf-8
from app import db, redis_c
import os
import redis
import json
from datetime import date
from sqlalchemy.sql import text
from flask import jsonify


class WordAll(db.Model):
    word_id = db.Column(db.Integer, primary_key=True)
    word_string = db.Column(db.String(8), nullable=False, unique=True)
    reported = db.Column(db.SmallInteger)

    def __init__(self, word_string):
        self.word_string = word_string
        self.reported = 0


class WordSearch(db.Model):
    word_id = db.Column(db.Integer, db.ForeignKey('word_all.word_id', ondelete='CASCADE'), primary_key=True)
    word_parsed = db.Column(db.String(24), nullable=False)

    def __init__(self, word_id, word_parsed):
        self.word_id = word_id
        self.word_parsed = word_parsed


class CandidateWord(db.Model):
    word_id = db.Column(db.Integer, db.ForeignKey('word_all.word_id', ondelete='CASCADE'), primary_key=True)
    vote = db.Column(db.Integer)

    def __init__(self, word_id):
        self.word_id = word_id
        self.vote = 0


class ReportLog(db.Model):
    report_id = db.Column(db.Integer, primary_key=True)
    word_id = db.Column(db.Integer, db.ForeignKey('word_all.word_id', ondelete='CASCADE'), nullable=False)
    report_type = db.Column(db.SmallInteger, db.ForeignKey('report_class.report_type'), nullable=False)
    report_detail = db.Column(db.String(80))

    def __init__(self, word_id, report_type, report_detail):
        self.word_id = word_id
        self.report_type = report_type
        self.report_detail = report_detail


class ReportClass(db.Model):
    report_type = db.Column(db.SmallInteger, primary_key=True)
    report_name = db.Column(db.String(24))


class WordRank(db.Model):
    word_id = db.Column(db.Integer, db.ForeignKey('word_search.word_id', ondelete='CASCADE'), primary_key=True)
    rank_good = db.Column(db.Integer)
    rank_bad = db.Column(db.Integer)
    viewed = db.Column(db.Integer)
    fresh_rate = db.Column(db.Integer)

    def __init__(self, word_id):
        self.word_id = word_id
        self.rank_good = 0
        self.rank_bad = 0
        self.viewed = 0
        self.fresh_rate = 0


class RankLog(db.Model):
    word_id = db.Column(db.Integer, db.ForeignKey('word_search.word_id', ondelete='CASCADE'), primary_key=True)
    elapsed_date = db.Column(db.Date, primary_key=True, nullable=False, autoincrement=False)
    rank_good = db.Column(db.Integer)
    rank_bad = db.Column(db.Integer)
    viewed = db.Column(db.Integer)
    point = db.Column(db.Integer)

    def __init__(self, word_id):
        self.word_id = word_id
        self.elapsed_date = date.today().isoformat()
        self.rank_bad = 0
        self.rank_good = 0
        self.viewed = 0
        self.point = 0


RAWQUERY = {
    'word_log_search': text('SELECT * FROM rank_log WHERE word_id = :word_id AND elapsed_date = :date'),
    'word_upvote': [text('UPDATE word_rank SET rank_good = rank_good + 1 WHERE word_id = :word_id'),
                    text('''UPDATE rank_log SET rank_good = rank_good + 1
                    WHERE word_id = :word_id and elapsed_date = :date''')],
    'word_downvote': [text('UPDATE word_rank SET rank_bad = rank_bad + 1 WHERE word_id = :word_id'),
                      text('''UPDATE rank_log SET rank_bad = rank_bad + 1
                      WHERE word_id = :word_id and elapsed_date = :date''')],
    'word_view': [text('UPDATE word_rank SET viewed = viewed + 1 WHERE word_id = :word_id'),
                  text('''UPDATE rank_log SET viewed = viewed + 1
                  WHERE word_id = :word_id and elapsed_date = :date''')],
    'word_search_DESC': text('''
    SELECT word_id, word_string, rank_good, rank_bad, viewed, fresh_rate
    FROM (word_search NATURAL JOIN word_all) NATURAL JOIN word_rank
    WHERE word_parsed REGEXP :word ORDER BY :column_name DESC
    '''),
    'word_search_ASC': text('''
    SELECT word_id, word_string, rank_good, rank_bad, viewed, fresh_rate
    FROM (word_search NATURAL JOIN word_all) NATURAL JOIN word_rank
    WHERE word_parsed REGEXP :word ORDER BY :column_name ASC
    '''),
    'get_word_id': text('''
    SELECT word_id
    FROM word_all
    WHERE word_string = :word_str
    '''),
    'get_word_data': text('''
    SELECT * FROM word_rank WHERE word_id = :word_id
    '''),
    'get_candidate_DESC': text('''
    SELECT word_id, word_string, vote
    FROM candidate_word NATURAL JOIN word_all
    ORDER BY :column_name DESC
    LIMIT :page_num, :fetch_num
    '''),
    'get_candidate_ASC': text('''
    SELECT word_id, word_string, vote
    FROM candidate_word NATURAL JOIN word_all
    ORDER BY :column_name ASC
    LIMIT :page_num, :fetch_num
    '''),
    'get_candidate_count': text('''
    SELECT count(*)
    FROM candidate_word
    '''),
    'get_cand_word_json':text('SELECT vote FROM candidate_word WHERE word_id = :word_id'),
    'get_report': text('''
    SELECT report_name, word_string, report_detail, word_id, reported
    FROM (report_log NATURAL JOIN report_class) NATURAL JOIN word_all
    ORDER BY :column_name DESC
    LIMIT :page_num, :fetch_num
    '''),
    'get_report_count': text('''
    SELECT count(*)
    FROM report_log
    '''),
    'word_candidate_upvote': text('UPDATE candidate_word SET vote = vote + 1 WHERE word_id = :word_id'),
    'word_candidate_downvote': text('UPDATE candidate_word SET vote = vote - 1 WHERE word_id = :word_id'),
    'word_candidate_move': text('DELETE FROM candidate_word WHERE word_id = :word_id'),
    'get_candidate_vote': text('SELECT vote FROM candidate_word WHERE word_id = :word_id'),
    'report': text('UPDATE word_all SET reported = reported + 1 WHERE word_id = :word_id'),
    'word_delete': text('DELETE FROM word_all WHERE word_id = :word_id'),
    'fresh_rate': [text('''
    UPDATE word_rank
    SET fresh_rate = IF((SELECT count(*) FROM rank_log
    WHERE word_rank.word_id = rank_log.word_id) > 0,
    (SELECT SUM(point) FROM rank_log WHERE word_rank.word_id = rank_log.word_id), 0)'''),
                   text('''SELECT AVG(fresh_rate) FROM
                   (SELECT fresh_rate FROM word_rank ORDER BY fresh_rate DESC LIMIT (:top_n_count)) as top_fresh'''),
                   text('UPDATE word_rank SET fresh_rate = (100 * fresh_rate / (:top_rate))')],
    'elapse_time': [text('DELETE FROM rank_log WHERE DATEDIFF(CURRENT_DATE(), elapsed_date) >= 30'),
                    text('''
                    UPDATE rank_log
                    SET point = (DATEDIFF(CURRENT_DATE(), elapsed_date)) * (viewed + 10 * (rank_good + rank_bad))''')],
    'get_search_json': text('''
        SELECT word_id, word_string, rank_good, rank_bad, viewed, fresh_rate
        FROM (word_all NATURAL JOIN word_search) NATURAL JOIN word_rank
        WHERE (word_all.word_id IN (SELECT word_id FROM word_search AS search WHERE (search.word_parsed REGEXP :regex)))
        ORDER BY :column_name :desc
        LIMIT :start_index, :counts_per_page
        '''),
    'get_search_length': text('''
    SELECT count(*) FROM word_search WHERE word_parsed REGEXP :regex
    ''')
}
JAMOTABLE = {
    'ㄱ': '0', 'ㄴ': '1', 'ㄷ': '2', 'ㄹ': '3', 'ㅁ': '4', 'ㅂ': '5', 'ㅅ': '6', 'ㅇ': '7', 'ㅈ': '8', 'ㅊ': '9',
    'ㅋ': 'a', 'ㅌ': 'b', 'ㅍ': 'c', 'ㅎ': 'd', 'ㄲ': 'e', 'ㅆ': 'f', 'ㅃ': 'g', 'ㄸ': 'h', 'ㅉ': 'i', 'ㄳ': 'j',
    'ㄵ': 'k', 'ㄶ': 'l', 'ㄺ': 'm', 'ㄻ': 'n', 'ㄼ': 'o', 'ㄽ': 'p', 'ㄾ': 'q', 'ㄿ': 'r', 'ㅀ': 's', 'ㅄ': 't',
    'ㅏ': 'A', 'ㅑ': 'B', 'ㅓ': 'C', 'ㅕ': 'D', 'ㅗ': 'E', 'ㅛ': 'F', 'ㅜ': 'G', 'ㅠ': 'H', 'ㅡ': 'I', 'ㅣ': 'J',
    'ㅐ': 'K', 'ㅒ': 'L', 'ㅔ': 'M', 'ㅖ': 'N', 'ㅘ': 'O', 'ㅙ': 'P', 'ㅚ': 'Q', 'ㅝ': 'R', 'ㅞ': 'S', 'ㅟ': 'T',
    'ㅢ': 'U', 'X': 'V'
}
JAMOPARSE = [
    [JAMOTABLE[x] for x in ["ㄱ", "ㄲ", "ㄴ", "ㄷ", "ㄸ", "ㄹ", "ㅁ", "ㅂ", "ㅃ", "ㅅ", "ㅆ",
                            "ㅇ", "ㅈ", "ㅉ", "ㅊ", "ㅋ", "ㅌ", "ㅍ", "ㅎ", 'X']],
    [JAMOTABLE[x] for x in ["ㅏ", "ㅐ", "ㅑ", "ㅒ", "ㅓ", "ㅔ", "ㅕ", "ㅖ", "ㅗ", "ㅘ", "ㅙ",<commit_msg>fix error<commit_after># coding: utf-8
from app import db, redis_c
import os
import redis
import json
from datetime import date
from sqlalchemy.sql import text
from flask import jsonify


class WordAll(db.Model):
    word_id = db.Column(db.Integer, primary_key=True)
    word_string = db.Column(db.String(8), nullable=False, unique=True)
    reported = db.Column(db.SmallInteger)

    def __init__(self, word_string):
        self.word_string = word_string
        self.reported = 0


class WordSearch(db.Model):
    word_id = db.Column(db.Integer, db.ForeignKey('word_all.word_id', ondelete='CASCADE'), primary_key=True)
    word_parsed = db.Column(db.String(24), nullable=False)

    def __init__(self, word_id, word_parsed):
        self.word_id = word_id
        self.word_parsed = word_parsed


class CandidateWord(db.Model):
    word_id = db.Column(db.Integer, db.ForeignKey('word_all.word_id', ondelete='CASCADE'), primary_key=True)
    vote = db.Column(db.Integer)

    def __init__(self, word_id):
        self.word_id = word_id
        self.vote = 0


class ReportLog(db.Model):
    report_id = db.Column(db.Integer, primary_key=True)
    word_id = db.Column(db.Integer, db.ForeignKey('word_all.word_id', ondelete='CASCADE'), nullable=False)
    report_type = db.Column(db.SmallInteger, db.ForeignKey('report_class.report_type'), nullable=False)
    report_detail = db.Column(db.String(80))

    def __init__(self, word_id, report_type, report_detail):
        self.word_id = word_id
        self.report_type = report_type
        self.report_detail = report_detail


class ReportClass(db.Model):
    report_type = db.Column(db.SmallInteger, primary_key=True)
    report_name = db.Column(db.String(24))


class WordRank(db.Model):
    word_id = db.Column(db.Integer, db.ForeignKey('word_search.word_id', ondelete='CASCADE'), primary_key=True)
    rank_good = db.Column(db.Integer)
    rank_bad = db.Column(db.Integer)
    viewed = db.Column(db.Integer)
    fresh_rate = db.Column(db.Integer)

    def __init__(self, word_id):
        self.word_id = word_id
        self.rank_good = 0
        self.rank_bad = 0
        self.viewed = 0
        self.fresh_rate = 0


class RankLog(db.Model):
    word_id = db.Column(db.Integer, db.ForeignKey('word_search.word_id', ondelete='CASCADE'), primary_key=True)
    elapsed_date = db.Column(db.Date, primary_key=True, nullable=False, autoincrement=False)
    rank_good = db.Column(db.Integer)
    rank_bad = db.Column(db.Integer)
    viewed = db.Column(db.Integer)
    point = db.Column(db.Integer)

    def __init__(self, word_id):
        self.word_id = word_id
        self.elapsed_date = date.today().isoformat()
        self.rank_bad = 0
        self.rank_good = 0
        self.viewed = 0
        self.point = 0


RAWQUERY = {
    'word_log_search': text('SELECT * FROM rank_log WHERE word_id = :word_id AND elapsed_date = :date'),
    'word_upvote': [text('UPDATE word_rank SET rank_good = rank_good + 1 WHERE word_id = :word_id'),
                    text('''UPDATE rank_log SET rank_good = rank_good + 1
                    WHERE word_id = :word_id and elapsed_date = :date''')],
    'word_downvote': [text('UPDATE word_rank SET rank_bad = rank_bad + 1 WHERE word_id = :word_id'),
                      text('''UPDATE rank_log SET rank_bad = rank_bad + 1
                      WHERE word_id = :word_id and elapsed_date = :date''')],
    'word_view': [text('UPDATE word_rank SET viewed = viewed + 1 WHERE word_id = :word_id'),
                  text('''UPDATE rank_log SET viewed = viewed + 1
                  WHERE word_id = :word_id and elapsed_date = :date''')],
    'word_search_DESC': text('''
    SELECT word_id, word_string, rank_good, rank_bad, viewed, fresh_rate
    FROM (word_search NATURAL JOIN word_all) NATURAL JOIN word_rank
    WHERE word_parsed REGEXP :word ORDER BY :column_name DESC
    '''),
    'word_search_ASC': text('''
    SELECT word_id, word_string, rank_good, rank_bad, viewed, fresh_rate
    FROM (word_search NATURAL JOIN word_all) NATURAL JOIN word_rank
    WHERE word_parsed REGEXP :word ORDER BY :column_name ASC
    '''),
    'get_word_id': text('''
    SELECT word_id
    FROM word_all
    WHERE word_string = :word_str
    '''),
    'get_word_data': text('''
    SELECT * FROM word_rank WHERE word_id = :word_id
    '''),
    'get_candidate_DESC': text('''
    SELECT word_id, word_string, vote
    FROM candidate_word NATURAL JOIN word_all
    ORDER BY :column_name DESC
    LIMIT :page_num, :fetch_num
    '''),
    'get_candidate_ASC': text('''
    SELECT word_id, word_string, vote
    FROM candidate_word NATURAL JOIN word_all
    ORDER BY :column_name ASC
    LIMIT :page_num, :fetch_num
    '''),
    'get_candidate_count': text('''
    SELECT count(*)
    FROM candidate_word
    '''),
    'get_cand_word_json':text('SELECT vote FROM candidate_word WHERE word_id = :word_id'),
    'get_report': text('''
    SELECT report_name, word_string, report_detail, word_id, reported
    FROM (report_log NATURAL JOIN report_class) NATURAL JOIN word_all
    ORDER BY :column_name DESC
    LIMIT :page_num, :fetch_num
    '''),
    'get_report_count': text('''
    SELECT count(*)
    FROM report_log
    '''),
    'word_candidate_upvote': text('UPDATE candidate_word SET vote = vote + 1 WHERE word_id = :word_id'),
    'word_candidate_downvote': text('UPDATE candidate_word SET vote = vote - 1 WHERE word_id = :word_id'),
    'word_candidate_move': text('DELETE FROM candidate_word WHERE word_id = :word_id'),
    'get_candidate_vote': text('SELECT vote FROM candidate_word WHERE word_id = :word_id'),
    'report': text('UPDATE word_all SET reported = reported + 1 WHERE word_id = :word_id'),
    'word_delete': text('DELETE FROM word_all WHERE word_id = :word_id'),
    'fresh_rate': [text('''
    UPDATE word_rank
    SET fresh_rate = IF((SELECT count(*) FROM rank_log
    WHERE word_rank.word_id = rank_log.word_id) > 0,
    (SELECT SUM(point) FROM rank_log WHERE word_rank.word_id = rank_log.word_id), 0)'''),
                   text('''SELECT AVG(fresh_rate) FROM
                   (SELECT fresh_rate FROM word_rank ORDER BY fresh_rate DESC LIMIT :top_n_count) as top_fresh'''),
                   text('UPDATE word_rank SET fresh_rate = (100 * fresh_rate / (:top_rate))')],
    'elapse_time': [text('DELETE FROM rank_log WHERE DATEDIFF(CURRENT_DATE(), elapsed_date) >= 30'),
                    text('''
                    UPDATE rank_log
                    SET point = (30 - (DATEDIFF(CURRENT_DATE(), elapsed_date))) * (viewed + 10 * (rank_good + rank_bad))''')],
    'get_search_json': text('''
        SELECT word_id, word_string, rank_good, rank_bad, viewed, fresh_rate
        FROM (word_all NATURAL JOIN word_search) NATURAL JOIN word_rank
        WHERE (word_all.word_id IN (SELECT word_id FROM word_search AS search WHERE (search.word_parsed REGEXP :regex)))
        ORDER BY :column_name :desc
        LIMIT :start_index, :counts_per_page
        '''),
    'get_search_length': text('''
    SELECT count(*) FROM word_search WHERE word_parsed REGEXP :regex
    ''')
}
JAMOTABLE = {
    'ㄱ': '0', 'ㄴ': '1', 'ㄷ': '2', 'ㄹ': '3', 'ㅁ': '4', 'ㅂ': '5', 'ㅅ': '6', 'ㅇ': '7', 'ㅈ': '8', 'ㅊ': '9',
    'ㅋ': 'a', 'ㅌ': 'b', 'ㅍ': 'c', 'ㅎ': 'd', 'ㄲ': 'e', 'ㅆ': 'f', 'ㅃ': 'g', 'ㄸ': 'h', 'ㅉ': 'i', 'ㄳ': 'j',
    'ㄵ': 'k', 'ㄶ': 'l', 'ㄺ': 'm', 'ㄻ': 'n', 'ㄼ': 'o', 'ㄽ': 'p', 'ㄾ': 'q', 'ㄿ': 'r', 'ㅀ': 's', 'ㅄ': 't',
    'ㅏ': 'A', 'ㅑ': 'B', 'ㅓ': 'C', 'ㅕ': 'D', 'ㅗ': 'E', 'ㅛ': 'F', 'ㅜ': 'G', 'ㅠ': 'H', 'ㅡ': 'I', 'ㅣ': 'J',
    'ㅐ': 'K', 'ㅒ': 'L', 'ㅔ': 'M', 'ㅖ': 'N', 'ㅘ': 'O', 'ㅙ': 'P', 'ㅚ': 'Q', 'ㅝ': 'R', 'ㅞ': 'S', 'ㅟ': 'T',
    'ㅢ': 'U', 'X': 'V'
}
JAMOPARSE = [
    [JAMOTABLE[x] for x in ["ㄱ", "ㄲ", "ㄴ", "ㄷ", "ㄸ", "ㄹ", "ㅁ", "ㅂ", "ㅃ", "ㅅ", "ㅆ",
                            "ㅇ", "ㅈ", "ㅉ", "ㅊ", "ㅋ", "ㅌ", "ㅍ", "ㅎ", 'X']],
    [JAMOTABLE[x] for x in ["ㅏ", "ㅐ", "ㅑ", "ㅒ", "ㅓ", "ㅔ", "ㅕ", "ㅖ", "ㅗ", "ㅘ", "ㅙ",
{'fraction_non_alphanumeric': 0.15723429875150516, 'fraction_numerical': 0.0059572849990493695, 'mean_word_length': 3.317373461012312, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 7, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 26, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4373023', 'n_tokens_mistral': 6069, 'n_tokens_neox': 5850, 'n_words': 1409}