<commit_before>#!/usr/bin/env python
import os, sys
sys.path.append(os.path.join(os.path.dirname(__file__), os.pardir))
sys.path.append(os.path.join(os.path.dirname(__file__), os.pardir, "core"))

import re
import csv
import numpy as np
import sqlparse
from dump import dump_stats, dump_all_stats

os.environ.setdefault("DJANGO_SETTINGS_MODULE", "cmudbac.settings")
import django
django.setup()

from library.models import *

QUERIES_DIRECTORY = 'queries'

def query_stats(directory = '.'):
    stats = {}

    for repo in Repository.objects.filter(latest_attempt__result = 'OK'):
        actions = Action.objects.filter(attempt = repo.latest_attempt)
        if len(actions) == 0:
            continue
        
        for action in actions:
            counters = Counter.objects.filter(action = action)
            for counter in counters:
                stats[counter.description] = stats.get(counter.description, 0) + counter.count

    dump_stats(directory, 'query', stats)

def table_coverage_stats(directory = '.'):
    stats = []

    for repo in Repository.objects.filter(latest_attempt__result = 'OK'):
        actions = Action.objects.filter(attempt = repo.latest_attempt)
        if len(actions) == 0:
            continue

        statistics = Statistic.objects.filter(attempt = repo.latest_attempt).filter(description = 'num_tables')
        if len(statistics) == 0:
            continue
        table_count = statistics[0].count
        if table_count == 0:
            continue
        
        covered_tables = set()
        for action in actions:
            for query in Query.objects.filter(action = action):
                for table in re.findall('FROM\s*\S+', query.content.upper()):
                    table_name = table.replace('FROM', '').replace("'", "").replace(' ', '')
                    covered_tables.add(table_name)
               
        percentage = int(float(len(covered_tables) * 100) / table_count)
        stats.append(percentage)

    dump_stats(directory, 'table_coverage', stats)
        
def column_coverage_stats(directory = '.'):
    stats = []

    for repo in Repository.objects.filter(latest_attempt__result = 'OK'):
        actions = Action.objects.filter(attempt = repo.latest_attempt)
        if len(actions) == 0:
            continue

        informations = Information.objects.filter(attempt = repo.latest_attempt).filter(name = 'columns')
        if len(informations) == 0:
            continue
        information = informations[0]
        column_count = 0
        if repo.latest_attempt.database.name == 'PostgreSQL':
            column_count = len(re.findall('(\(.*?\))[,\]]', information.description))
        elif repo.latest_attempt.database.name == 'MySQL':
            column_count = len(re.findall('(\(.*?\))[,\)]', information.description))
        if column_count == 0:
            continue
        
        covered_columns = set()
        for action in actions:
            for query in Query.objects.filter(action = action):
                parsed = sqlparse.parse(query.content)[0]
                tokens = parsed.tokens
                for token in tokens:
                    if isinstance(token, sqlparse.sql.Identifier):
                        covered_columns.add(token.value)
        if len(covered_columns) > column_count:
            percentage = int(float(len(covered_columns) * 100) / column_count)
        else:
            percentage = 100
        stats.append(percentage)

    dump_stats(directory, 'column_coverage', stats)

def join_stats(directory = '.'):
    stats = {}

    for repo in Repository.objects.filter(latest_attempt__result = 'OK'):
        for action in Action.objects.filter(attempt = repo.latest_attempt):
            queries = Query.objects.filter(action = action)
            for query in queries:
                content = query.content.upper()
                if 'JOIN' in content:
                    parsed = sqlparse.parse(content)[0]
                    tokens = parsed.tokens
                    for index in xrange(0, len(tokens)):
                        if tokens[index].is_keyword and 'JOIN' in tokens[index].value:
                            stats[tokens[index].value] = stats.get(tokens[index].value, 0) + 1

    dump_stats(directory, 'join', stats)

def hash_stats(directory = '.'):
    stats = []

    for repo in Repository.objects.filter(latest_attempt__result = 'OK'):
        hash_count = 0
        for action in Action.objects.filter(attempt = repo.latest_attempt):
            for query in Query.objects.filter(action = action):
                for explain in Explain.objects.filter(query = query):
                    hash_count += len(re.findall('Hash', explain.output))
        stats.append(hash_count)

    dump_stats(directory, 'hash', stats)

def scan_stats(directory = '.'):
    stats = {}

    for repo in Repository.objects.filter(latest_attempt__result = 'OK'):
        for action in Action.objects.filter(attempt = repo.latest_attempt):
            for query in Query.objects.filter(action = action):
                for explain in Explain.objects.filter(query = query):
                    for scan in re.findall('[A-Za-z][\sA-Za-z]*Scan', explain.output):
                        stats[scan] = stats.get(scan, 0) + 1

    dump_stats(directory, 'scan', stats)

def nest_stats(directory = '.'):
    stats = []

    for repo in Repository.objects.filter(latest_attempt__result = 'OK'):
        nest_count = 0
        for action in Action.objects.filter(attempt = repo.latest_attempt):
            for query in Query.objects.filter(action = action):
                for explain in Explain.objects.filter(query = query):
                    nest_count += len(re.findall('Nested', explain.output))
        stats.append(nest_count)

    dump_stats(directory, 'nest', stats)

def index_coverage_stats(directory = '.'):
    stats = []

    for repo in Repository.objects.filter(latest_attempt__result = 'OK'):
        actions = Action.objects.filter(attempt = repo.latest_attempt)
        if len(actions) == 0:
            continue

        statistics = Statistic.objects.filter(attempt = repo.latest_attempt).filter(description = 'num_indexes')
        if len(statistics) == 0:
            continue
        index_count = statistics[0].count
        if index_count == 0:
            continue
        
        covered_indexes = set()
        for action in actions:
            for query in Query.objects.filter(action = action):
                for explain in Explain.objects.filter(query = query):
                    for raw_index in re.findall('Index.*?Scan.*?on \S+', explain.output):
                        index = raw_index.split()[-1]
                        covered_indexes.add(index)
               
        percentage = int(float(len(covered_indexes) * 100) / index_count)
        stats.append(percentage)

    dump_stats(directory, 'index_coverage', stats)

def aggregate_stats(directory = '.'):
    stats = []

    for repo in Repository.objects.filter(latest_attempt__result = 'OK'):
        aggregate_count = 0
        for action in Action.objects.filter(attempt = repo.latest_attempt):
            for query in Query.objects.filter(action = action):
                for explain in Explain.objects.filter(query = query):
                    aggregate_count += len(re.findall('Aggregate', explain.output))
        stats.append(aggregate_count)

    dump_stats(directory, 'aggregate', stats)

def logical_stats(directory = '.'):
    stats = {}

    for repo in Repository.objects.filter(latest_attempt__result = 'OK'):
        for action in Action.objects.filter(attempt = repo.latest_attempt):
            for query in Query.objects.filter(action = action):
                for explain in Explain.objects.filter(query = query):
                    for logical_word in ['AND', 'OR', 'ALL', 'NOT']:
                        stats[logical_word] = stats.get(logical_word, 0) + len(re.findall(logical_word, explain.output))<commit_msg>update scripts<commit_after>#!/usr/bin/env python
import os, sys
sys.path.append(os.path.join(os.path.dirname(__file__), os.pardir))
sys.path.append(os.path.join(os.path.dirname(__file__), os.pardir, "core"))

import re
import csv
import numpy as np
import sqlparse
from dump import dump_stats, dump_all_stats

os.environ.setdefault("DJANGO_SETTINGS_MODULE", "cmudbac.settings")
import django
django.setup()

from library.models import *

QUERIES_DIRECTORY = 'queries'

def query_stats(directory = '.'):
    stats = {}

    for repo in Repository.objects.filter(latest_attempt__result = 'OK'):
        actions = Action.objects.filter(attempt = repo.latest_attempt)
        if len(actions) == 0:
            continue
        
        for action in actions:
            counters = Counter.objects.filter(action = action)
            for counter in counters:
                stats[counter.description] = stats.get(counter.description, 0) + counter.count

    dump_stats(directory, 'query', stats)

def table_coverage_stats(directory = '.'):
    stats = []

    for repo in Repository.objects.filter(latest_attempt__result = 'OK'):
        actions = Action.objects.filter(attempt = repo.latest_attempt)
        if len(actions) == 0:
            continue

        statistics = Statistic.objects.filter(attempt = repo.latest_attempt).filter(description = 'num_tables')
        if len(statistics) == 0:
            continue
        table_count = statistics[0].count
        if table_count == 0:
            continue
        
        covered_tables = set()
        for action in actions:
            for query in Query.objects.filter(action = action):
                for table in re.findall('FROM\s*\S+', query.content.upper()):
                    table_name = table.replace('FROM', '').replace("'", "").replace(' ', '')
                    covered_tables.add(table_name)

        if len(covered_tables) > table_count:
            percentage = int(float(len(covered_tables) * 100) / table_count)
        else:
            percentage = 100
        stats.append(percentage)

    dump_stats(directory, 'table_coverage', stats)
        
def column_coverage_stats(directory = '.'):
    stats = []

    for repo in Repository.objects.filter(latest_attempt__result = 'OK'):
        actions = Action.objects.filter(attempt = repo.latest_attempt)
        if len(actions) == 0:
            continue

        informations = Information.objects.filter(attempt = repo.latest_attempt).filter(name = 'columns')
        if len(informations) == 0:
            continue
        information = informations[0]
        column_count = 0
        if repo.latest_attempt.database.name == 'PostgreSQL':
            column_count = len(re.findall('(\(.*?\))[,\]]', information.description))
        elif repo.latest_attempt.database.name == 'MySQL':
            column_count = len(re.findall('(\(.*?\))[,\)]', information.description))
        if column_count == 0:
            continue
        
        covered_columns = set()
        for action in actions:
            for query in Query.objects.filter(action = action):
                parsed = sqlparse.parse(query.content)[0]
                tokens = parsed.tokens
                for token in tokens:
                    if isinstance(token, sqlparse.sql.Identifier):
                        covered_columns.add(token.value)
        percentage = int(float(len(covered_columns) * 100) / column_count)
        percentage = min(percentage, 100)
        stats.append(percentage)

    dump_stats(directory, 'column_coverage', stats)

def join_stats(directory = '.'):
    stats = {}

    for repo in Repository.objects.filter(latest_attempt__result = 'OK'):
        for action in Action.objects.filter(attempt = repo.latest_attempt):
            queries = Query.objects.filter(action = action)
            for query in queries:
                content = query.content.upper()
                if 'JOIN' in content:
                    parsed = sqlparse.parse(content)[0]
                    tokens = parsed.tokens
                    for index in xrange(0, len(tokens)):
                        if tokens[index].is_keyword and 'JOIN' in tokens[index].value:
                            stats[tokens[index].value] = stats.get(tokens[index].value, 0) + 1

    dump_stats(directory, 'join', stats)

def hash_stats(directory = '.'):
    stats = []

    for repo in Repository.objects.filter(latest_attempt__result = 'OK'):
        hash_count = 0
        for action in Action.objects.filter(attempt = repo.latest_attempt):
            for query in Query.objects.filter(action = action):
                for explain in Explain.objects.filter(query = query):
                    hash_count += len(re.findall('Hash', explain.output))
        stats.append(hash_count)

    dump_stats(directory, 'hash', stats)

def scan_stats(directory = '.'):
    stats = {}

    for repo in Repository.objects.filter(latest_attempt__result = 'OK'):
        for action in Action.objects.filter(attempt = repo.latest_attempt):
            for query in Query.objects.filter(action = action):
                for explain in Explain.objects.filter(query = query):
                    for scan in re.findall('[A-Za-z][\sA-Za-z]*Scan', explain.output):
                        stats[scan] = stats.get(scan, 0) + 1

    dump_stats(directory, 'scan', stats)

def nest_stats(directory = '.'):
    stats = []

    for repo in Repository.objects.filter(latest_attempt__result = 'OK'):
        nest_count = 0
        for action in Action.objects.filter(attempt = repo.latest_attempt):
            for query in Query.objects.filter(action = action):
                for explain in Explain.objects.filter(query = query):
                    nest_count += len(re.findall('Nested', explain.output))
        stats.append(nest_count)

    dump_stats(directory, 'nest', stats)

def index_coverage_stats(directory = '.'):
    stats = []

    for repo in Repository.objects.filter(latest_attempt__result = 'OK'):
        actions = Action.objects.filter(attempt = repo.latest_attempt)
        if len(actions) == 0:
            continue

        statistics = Statistic.objects.filter(attempt = repo.latest_attempt).filter(description = 'num_indexes')
        if len(statistics) == 0:
            continue
        index_count = statistics[0].count
        if index_count == 0:
            continue
        
        covered_indexes = set()
        for action in actions:
            for query in Query.objects.filter(action = action):
                for explain in Explain.objects.filter(query = query):
                    for raw_index in re.findall('Index.*?Scan.*?on \S+', explain.output):
                        index = raw_index.split()[-1]
                        covered_indexes.add(index)
               
        percentage = int(float(len(covered_indexes) * 100) / index_count)
        percentage = min(percentage, 100)
        stats.append(percentage)

    dump_stats(directory, 'index_coverage', stats)

def aggregate_stats(directory = '.'):
    stats = []

    for repo in Repository.objects.filter(latest_attempt__result = 'OK'):
        aggregate_count = 0
        for action in Action.objects.filter(attempt = repo.latest_attempt):
            for query in Query.objects.filter(action = action):
                for explain in Explain.objects.filter(query = query):
                    aggregate_count += len(re.findall('Aggregate', explain.output))
        stats.append(aggregate_count)

    dump_stats(directory, 'aggregate', stats)

def logical_stats(directory = '.'):
    stats = {}

    for repo in Repository.objects.filter(latest_attempt__result = 'OK'):
        for action in Action.objects.filter(attempt = repo.latest_attempt):
            for query in Query.objects.filter(action = action):
                for explain in Explain.objects.filter(query = query):
                    for logical_word in ['AND', 'OR', 'ALL', 'NOT']:
                        stats[logical_word] = stats.get(logical_word, 0) + len(re.findall(logical_word, explain.output))
{'fraction_non_alphanumeric': 0.11548064918851436, 'fraction_numerical': 0.0052434456928838954, 'mean_word_length': 2.3439782926320185, 'pattern_counts': {'":': 0, '<': 3, '<?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': '466638', 'n_tokens_mistral': 4612, 'n_tokens_neox': 4359, 'n_words': 970}