<commit_before>import pickle
import pyodbc
import traceback
from datetime import datetime
from CustomModules import PageMechanics
import CustomModules
import Configuration
import logging


class SQLConnector:
    def __init__(self, sql_config: Configuration.SQLConfig):
        self.connection = pyodbc.connect(
            'DRIVER=' + sql_config.Driver + ';PORT=1433;SERVER=' + sql_config.Server + ';PORT=1443;DATABASE='
            + sql_config.Database + ';UID=' + sql_config.Username + ';PWD=' + sql_config.Password)
        self.cursor = self.connection.cursor()

    def select_page_id_from_dbo_knownpages(self, page_title: str, platform: str):
        self.cursor.execute(
            "select [page_id] FROM [dbo].[KnownPages] where [page_title] = ? and [platform]= ?", page_title,
            platform)
        raw = self.cursor.fetchone()
        if raw is not None:
            return raw.page_id
        return None

    def select_id_from_dbo_knownpages(self, page_object: CustomModules.PageMechanics.PageGlobal=None, platform: str=None, page_id: str=None, page_title: str=None):
        if platform is None:
            if isinstance(page_object, CustomModules.PageMechanics.PageXWiki):
                platform = 'xwiki'
            elif isinstance(page_object, CustomModules.PageMechanics.PageConfluence):
                platform = 'confluence'
            elif isinstance(page_object, CustomModules.PageMechanics.PageMediaWiki):
                platform = 'mediawiki'
            else:
                return None
        if page_id is not None and page_title is None:
            if platform.lower() == 'xwiki' and not page_id.startswith('xwiki:'):
                page_id = 'xwiki:' + page_id
            self.cursor.execute("select [id] from [dbo].[KnownPages] where [page_id] = ? and [platform] LIKE LOWER(?)", page_id, platform)
            raw = self.cursor.fetchone()
            if raw:
                return raw.id
            return None
        if page_title is not None and page_id is None:
            self.cursor.execute(
                "select [id] from [dbo].[KnownPages] where [page_title] = ? and [platform] LIKE LOWER(?)", page_title, platform)
            raw = self.cursor.fetchone()
            if raw:
                return raw.id
            return None

    def select_id_characters_total_from_dbo_knownpages(self, platform: str, page_id: str=None, page_title: str=None):
        if page_id is not None:
            if platform.lower() == 'xwiki':
                page_id = 'xwiki:' + page_id
            self.cursor.execute(
                "select [id],[characters_total] FROM [dbo].[KnownPages] where [page_id] = ? and [platform] LIKE LOWER(?)",
                page_id, platform)
            raw = self.cursor.fetchone()
            if raw:
                return raw.id, raw.characters_total
            return None
        if page_title is not None:
            self.cursor.execute(
                "select [id], [characters_total] from [dbo].[KnownPages] where [page_title] = ? and [platform] LIKE LOWER(?)",
                page_title,
                platform)
            raw = self.cursor.fetchone()
            if raw:
                return raw.id, raw.characters_total
            return None

    def select_id_from_dbo_knownpages_users(self, username: str):
        self.cursor.execute(
            "select [id] from [dbo].[KnownPages_Users] where [user_name] = ?", username)
        raw = self.cursor.fetchone()
        if raw:
            return raw.id
        return None

    def select_karma_score_from_userkarma_slice(self, user_id: str, date_start: datetime, date_end: datetime):
        date_end_year = '%02d' % date_end.year
        date_end_month = '%02d' % date_end.month
        date_end_day = '%02d' % date_end.day
        date_start_year = '%02d' % date_start.year
        date_start_month = '%02d' % date_start.month
        date_start_day = '%02d' % date_start.day
        try:
            self.cursor.execute(
                "select [karma_score], CONVERT(varchar(max), DATEDIFF(second,{d '1970-01-01'},[change_time])) as [change_time] FROM [dbo].[UserKarma_slice] where [user_id] = ? and change_time between CONVERT(datetime, ?+?+?) and CONVERT(datetime, ?+?+?)",
                user_id, date_start_year, date_start_month, date_start_day, date_end_year, date_end_month, date_end_day)
            raw = self.cursor.fetchall()
            if raw:
                return raw
            return None
        except:
            print("Sad story, but your query is a shit")
            print(
                "select [karma_score], [change_time] FROM [dbo].[UserKarma_slice] where [user_id] = " + user_id + " and change_time between CONVERT(datetime, " + str(
                    date_start.year) + str(date_start.month) + str(date_start.day) + ") and CONVERT(datetime, " + str(
                    date_end.year) + str(date_end.month) + str(date_end.day) + ")")

    def select_from_known_bugs_by_filter(self, components_filer: list, product_filter: list, tbfi_filter: list, start: str, end: str) -> list:

        query = "WITH OrderedRecords AS" \
                "(" \
                "SELECT case when [dbo].[KnownBugs].[bug_title] is NULL then [dbo].[KnownPages].[page_title] when [dbo].[KnownBugs].[bug_title] is not NULL then [dbo].[KnownBugs].[bug_title] end as page_title, [dbo].[KnownBugs].[bug_id], [dbo].[KnownBugs].[product], [dbo].[KnownBugs].[tbfi], [dbo].[KnownBugs].[components], [dbo].[KnownPages].page_id," \
                "ROW_NUMBER() OVER (ORDER BY [dbo].[KnownPages].id) AS 'RowNumber' " \
                "FROM [dbo].[KnownBugs] " \
                "left join [dbo].[KnownPages] on [dbo].[KnownBugs].KnownPages_id = [dbo].[KnownPages].id " \
                "WHERE "
        if len(product_filter) > 0:
            query += "[product] in ("
            for idx, product in enumerate(product_filter):
                query += "'" + product + "'"
                if idx != len(product_filter) - 1:
                    query += ", "
                else:
                    query += ")"
        if len(tbfi_filter) > 0:
            query += "and [tbfi] in ("
            for idx, tbfi in enumerate(tbfi_filter):
                query += "'" + tbfi + "'"
                if idx != len(tbfi_filter) - 1:
                    query += ", "
                else:
                    query += ")"
        if len(components_filer) > 0:
            for idx, component in enumerate(components_filer):
                query += " and(Charindex('" + component + "',CAST(components AS VARCHAR(MAX)))>0)"
        query += ")"
        query += "SELECT [page_title], [bug_id], [product], [tbfi], [components], [page_id], [RowNumber] FROM OrderedRecords WHERE RowNumber BETWEEN " + start + " and " + end + " order by bug_id"
        # print(query)
        self.cursor.execute(query)
        raw = self.cursor.fetchall()
        if raw is None:
            return []
        return raw

    def select_page_title_from_dbo_knownpages(self, native_sql_id: str):
        self.cursor.execute(
            "SELECT [page_title] FROM [dbo].[KnownPages] where [ID] = ?", native_sql_id)
        raw = self.cursor.fetchone()
        if raw:
            return raw.page_title<commit_msg>print(query)<commit_after>import pickle
import pyodbc
import traceback
from datetime import datetime
from CustomModules import PageMechanics
import CustomModules
import Configuration
import logging


class SQLConnector:
    def __init__(self, sql_config: Configuration.SQLConfig):
        self.connection = pyodbc.connect(
            'DRIVER=' + sql_config.Driver + ';PORT=1433;SERVER=' + sql_config.Server + ';PORT=1443;DATABASE='
            + sql_config.Database + ';UID=' + sql_config.Username + ';PWD=' + sql_config.Password)
        self.cursor = self.connection.cursor()

    def select_page_id_from_dbo_knownpages(self, page_title: str, platform: str):
        self.cursor.execute(
            "select [page_id] FROM [dbo].[KnownPages] where [page_title] = ? and [platform]= ?", page_title,
            platform)
        raw = self.cursor.fetchone()
        if raw is not None:
            return raw.page_id
        return None

    def select_id_from_dbo_knownpages(self, page_object: CustomModules.PageMechanics.PageGlobal=None, platform: str=None, page_id: str=None, page_title: str=None):
        if platform is None:
            if isinstance(page_object, CustomModules.PageMechanics.PageXWiki):
                platform = 'xwiki'
            elif isinstance(page_object, CustomModules.PageMechanics.PageConfluence):
                platform = 'confluence'
            elif isinstance(page_object, CustomModules.PageMechanics.PageMediaWiki):
                platform = 'mediawiki'
            else:
                return None
        if page_id is not None and page_title is None:
            if platform.lower() == 'xwiki' and not page_id.startswith('xwiki:'):
                page_id = 'xwiki:' + page_id
            self.cursor.execute("select [id] from [dbo].[KnownPages] where [page_id] = ? and [platform] LIKE LOWER(?)", page_id, platform)
            raw = self.cursor.fetchone()
            if raw:
                return raw.id
            return None
        if page_title is not None and page_id is None:
            self.cursor.execute(
                "select [id] from [dbo].[KnownPages] where [page_title] = ? and [platform] LIKE LOWER(?)", page_title, platform)
            raw = self.cursor.fetchone()
            if raw:
                return raw.id
            return None

    def select_id_characters_total_from_dbo_knownpages(self, platform: str, page_id: str=None, page_title: str=None):
        if page_id is not None:
            if platform.lower() == 'xwiki':
                page_id = 'xwiki:' + page_id
            self.cursor.execute(
                "select [id],[characters_total] FROM [dbo].[KnownPages] where [page_id] = ? and [platform] LIKE LOWER(?)",
                page_id, platform)
            raw = self.cursor.fetchone()
            if raw:
                return raw.id, raw.characters_total
            return None
        if page_title is not None:
            self.cursor.execute(
                "select [id], [characters_total] from [dbo].[KnownPages] where [page_title] = ? and [platform] LIKE LOWER(?)",
                page_title,
                platform)
            raw = self.cursor.fetchone()
            if raw:
                return raw.id, raw.characters_total
            return None

    def select_id_from_dbo_knownpages_users(self, username: str):
        self.cursor.execute(
            "select [id] from [dbo].[KnownPages_Users] where [user_name] = ?", username)
        raw = self.cursor.fetchone()
        if raw:
            return raw.id
        return None

    def select_karma_score_from_userkarma_slice(self, user_id: str, date_start: datetime, date_end: datetime):
        date_end_year = '%02d' % date_end.year
        date_end_month = '%02d' % date_end.month
        date_end_day = '%02d' % date_end.day
        date_start_year = '%02d' % date_start.year
        date_start_month = '%02d' % date_start.month
        date_start_day = '%02d' % date_start.day
        try:
            self.cursor.execute(
                "select [karma_score], CONVERT(varchar(max), DATEDIFF(second,{d '1970-01-01'},[change_time])) as [change_time] FROM [dbo].[UserKarma_slice] where [user_id] = ? and change_time between CONVERT(datetime, ?+?+?) and CONVERT(datetime, ?+?+?)",
                user_id, date_start_year, date_start_month, date_start_day, date_end_year, date_end_month, date_end_day)
            raw = self.cursor.fetchall()
            if raw:
                return raw
            return None
        except:
            print("Sad story, but your query is a shit")
            print(
                "select [karma_score], [change_time] FROM [dbo].[UserKarma_slice] where [user_id] = " + user_id + " and change_time between CONVERT(datetime, " + str(
                    date_start.year) + str(date_start.month) + str(date_start.day) + ") and CONVERT(datetime, " + str(
                    date_end.year) + str(date_end.month) + str(date_end.day) + ")")

    def select_from_known_bugs_by_filter(self, components_filer: list, product_filter: list, tbfi_filter: list, start: str, end: str) -> list:

        query = "WITH OrderedRecords AS" \
                "(" \
                "SELECT case when [dbo].[KnownBugs].[bug_title] is NULL then [dbo].[KnownPages].[page_title] when [dbo].[KnownBugs].[bug_title] is not NULL then [dbo].[KnownBugs].[bug_title] end as page_title, [dbo].[KnownBugs].[bug_id], [dbo].[KnownBugs].[product], [dbo].[KnownBugs].[tbfi], [dbo].[KnownBugs].[components], [dbo].[KnownPages].page_id," \
                "ROW_NUMBER() OVER (ORDER BY [dbo].[KnownPages].id) AS 'RowNumber' " \
                "FROM [dbo].[KnownBugs] " \
                "left join [dbo].[KnownPages] on [dbo].[KnownBugs].KnownPages_id = [dbo].[KnownPages].id " \
                "WHERE "
        if len(product_filter) > 0:
            query += "[product] in ("
            for idx, product in enumerate(product_filter):
                query += "'" + product + "'"
                if idx != len(product_filter) - 1:
                    query += ", "
                else:
                    query += ")"
        if len(tbfi_filter) > 0:
            query += "and [tbfi] in ("
            for idx, tbfi in enumerate(tbfi_filter):
                query += "'" + tbfi + "'"
                if idx != len(tbfi_filter) - 1:
                    query += ", "
                else:
                    query += ")"
        if len(components_filer) > 0:
            for idx, component in enumerate(components_filer):
                query += " and(Charindex('" + component + "',CAST(components AS VARCHAR(MAX)))>0)"
        query += ")"
        query += "SELECT [page_title], [bug_id], [product], [tbfi], [components], [page_id], [RowNumber] FROM OrderedRecords WHERE RowNumber BETWEEN " + start + " and " + end + " order by bug_id"
        print(query)
        self.cursor.execute(query)
        raw = self.cursor.fetchall()
        if raw is None:
            return []
        return raw

    def select_page_title_from_dbo_knownpages(self, native_sql_id: str):
        self.cursor.execute(
            "SELECT [page_title] FROM [dbo].[KnownPages] where [ID] = ?", native_sql_id)
        raw = self.cursor.fetchone()
        if raw:
            return raw.page_title
{'fraction_non_alphanumeric': 0.11980709610747503, 'fraction_numerical': 0.004684808818463658, 'mean_word_length': 2.19032967032967, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 13, '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': '98943', 'n_tokens_mistral': 4561, 'n_tokens_neox': 4299, 'n_words': 1019}