<commit_before># magneticod - Autonomous BitTorrent DHT crawler and metadata fetcher.
# Copyright (C) 2017  <NAME> <<EMAIL>>
# Dedicated to Cemile Binay, in whose hands I thrived.
#
# This program is free software: you can redistribute it and/or modify it under the terms of the GNU Affero General
# Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any
# later version.
#
# This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied
# warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Affero General Public License for more
# details.
#
# You should have received a copy of the GNU Affero General Public License along with this program.  If not, see
# <http://www.gnu.org/licenses/>.
import logging
import sqlite3
import time
import typing
import os

from magneticod import bencode

from .constants import PENDING_INFO_HASHES


class Database:
    def __init__(self, database) -> None:
        self.__db_conn = self.__connect(database)

        # We buffer metadata to flush many entries at once, for performance reasons.
        # list of tuple (info_hash, name, total_size, discovered_on)
        self.__pending_metadata = []  # type: typing.List[typing.Tuple[bytes, str, int, int]]
        # list of tuple (info_hash, size, path)
        self.__pending_files = []  # type: typing.List[typing.Tuple[bytes, int, bytes]]

    @staticmethod
    def __connect(database) -> sqlite3.Connection:
        os.makedirs(os.path.split(database)[0], exist_ok=True)
        db_conn = sqlite3.connect(database, isolation_level=None)

        db_conn.execute("PRAGMA journal_mode=WAL;")
        db_conn.execute("PRAGMA temp_store=1;")
        db_conn.execute("PRAGMA foreign_keys=ON;")

        with db_conn:
            db_conn.execute("CREATE TABLE IF NOT EXISTS torrents ("
                            "id             INTEGER PRIMARY KEY AUTOINCREMENT,"
                            "info_hash      BLOB NOT NULL UNIQUE,"
                            "name           TEXT NOT NULL,"
                            "total_size     INTEGER NOT NULL CHECK(total_size > 0),"
                            "discovered_on  INTEGER NOT NULL CHECK(discovered_on > 0)"
                            ");")
            db_conn.execute("CREATE TABLE IF NOT EXISTS files ("
                            "id          INTEGER PRIMARY KEY,"
                            "torrent_id  INTEGER REFERENCES torrents ON DELETE CASCADE ON UPDATE RESTRICT,"
                            "size        INTEGER NOT NULL,"
                            "path        TEXT NOT NULL"
                            ");")

        return db_conn

    def add_metadata(self, info_hash: bytes, metadata: bytes) -> bool:
        files = []
        discovered_on = int(time.time())
        try:
            info = bencode.loads(metadata)

            assert b"/" not in info[b"name"]
            name = info[b"name"].decode("utf-8")

            if b"files" in info:  # Multiple File torrent:
                for file in info[b"files"]:
                    assert type(file[b"length"]) is int
                    # Refuse trailing slash in any of the path items
                    assert not any(b"/" in item for item in file[b"path"])
                    path = "/".join(i.decode("utf-8") for i in file[b"path"])
                    files.append((info_hash, file[b"length"], path))
            else:  # Single File torrent:
                assert type(info[b"length"]) is int
                files.append((info_hash, info[b"length"], name))
        # TODO: Make sure this catches ALL, AND ONLY operational errors
        except (bencode.BencodeDecodingError, AssertionError, KeyError, AttributeError, UnicodeDecodeError, TypeError):<commit_msg>re-add info_hash_index creation in magneticod:persistence<commit_after># magneticod - Autonomous BitTorrent DHT crawler and metadata fetcher.
# Copyright (C) 2017  <NAME> <<EMAIL>>
# Dedicated to Cemile Binay, in whose hands I thrived.
#
# This program is free software: you can redistribute it and/or modify it under the terms of the GNU Affero General
# Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any
# later version.
#
# This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied
# warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Affero General Public License for more
# details.
#
# You should have received a copy of the GNU Affero General Public License along with this program.  If not, see
# <http://www.gnu.org/licenses/>.
import logging
import sqlite3
import time
import typing
import os

from magneticod import bencode

from .constants import PENDING_INFO_HASHES


class Database:
    def __init__(self, database) -> None:
        self.__db_conn = self.__connect(database)

        # We buffer metadata to flush many entries at once, for performance reasons.
        # list of tuple (info_hash, name, total_size, discovered_on)
        self.__pending_metadata = []  # type: typing.List[typing.Tuple[bytes, str, int, int]]
        # list of tuple (info_hash, size, path)
        self.__pending_files = []  # type: typing.List[typing.Tuple[bytes, int, bytes]]

    @staticmethod
    def __connect(database) -> sqlite3.Connection:
        os.makedirs(os.path.split(database)[0], exist_ok=True)
        db_conn = sqlite3.connect(database, isolation_level=None)

        db_conn.execute("PRAGMA journal_mode=WAL;")
        db_conn.execute("PRAGMA temp_store=1;")
        db_conn.execute("PRAGMA foreign_keys=ON;")

        with db_conn:
            db_conn.execute("CREATE TABLE IF NOT EXISTS torrents ("
                            "id             INTEGER PRIMARY KEY AUTOINCREMENT,"
                            "info_hash      BLOB NOT NULL UNIQUE,"
                            "name           TEXT NOT NULL,"
                            "total_size     INTEGER NOT NULL CHECK(total_size > 0),"
                            "discovered_on  INTEGER NOT NULL CHECK(discovered_on > 0)"
                            ");")
            db_conn.execute("CREATE INDEX IF NOT EXISTS info_hash_index ON torrents (info_hash);")
            db_conn.execute("CREATE TABLE IF NOT EXISTS files ("
                            "id          INTEGER PRIMARY KEY,"
                            "torrent_id  INTEGER REFERENCES torrents ON DELETE CASCADE ON UPDATE RESTRICT,"
                            "size        INTEGER NOT NULL,"
                            "path        TEXT NOT NULL"
                            ");")

        return db_conn

    def add_metadata(self, info_hash: bytes, metadata: bytes) -> bool:
        files = []
        discovered_on = int(time.time())
        try:
            info = bencode.loads(metadata)

            assert b"/" not in info[b"name"]
            name = info[b"name"].decode("utf-8")

            if b"files" in info:  # Multiple File torrent:
                for file in info[b"files"]:
                    assert type(file[b"length"]) is int
                    # Refuse trailing slash in any of the path items
                    assert not any(b"/" in item for item in file[b"path"])
                    path = "/".join(i.decode("utf-8") for i in file[b"path"])
                    files.append((info_hash, file[b"length"], path))
            else:  # Single File torrent:
                assert type(info[b"length"]) is int
                files.append((info_hash, info[b"length"], name))
        # TODO: Make sure this catches ALL, AND ONLY operational errors
        except (bencode.BencodeDecodingError, AssertionError, KeyError, AttributeError, UnicodeDecodeError, TypeError):
{'fraction_non_alphanumeric': 0.0863549007046765, 'fraction_numerical': 0.003587443946188341, 'mean_word_length': 2.1501210653753025, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 21, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7642459', 'n_tokens_mistral': 2153, 'n_tokens_neox': 2051, 'n_words': 724}