def update_resume_data(atp: lt.add_torrent_params, conn: apsw.Connection) -> None:
    # Change OR to AND when https://github.com/arvidn/libtorrent/issues/6913 is fixed
    conn.cursor().execute(
        "UPDATE torrent SET resume_data = ?3 "
        "WHERE (info_sha1 IS ?1) OR (info_sha256 IS ?2)",
        (*_ih_bytes(info_hashes(atp)), resume_data(atp)),
    )
Beispiel #2
0
class DatabaseTestCase(unittest.TestCase):

    def setUp(self):
        self.db = Connection(':memory:')
        cur = self.db.cursor()
        cur.execute("""
        CREATE TABLE mytable (
            id INTEGER PRIMARY KEY,
            firstname TEXT,
            lastname TEXT)""")
        cur.execute("""
        INSERT INTO mytable (id, firstname, lastname)
        VALUES
        (1, 'Mir', 'Jakuri')
        """)

    def test_upsert_insert(self):
        utils.upsert(self.db, 'mytable', ['id'], {
            'id': 2,
            'firstname': 'Ion',
            'lastname': 'Preciel',
        })
        cur = self.db.cursor()
        cur.execute('SELECT * FROM mytable')
        self.assertEqual((1, 'Mir', 'Jakuri'), cur.fetchone())
        self.assertEqual((2, 'Ion', 'Preciel'), cur.fetchone())

    def test_upsert_update(self):
        utils.upsert(self.db, 'mytable', ['id'], {
            'id': 1,
            'lastname': 'Teiwaz',
        })
        cur = self.db.cursor()
        cur.execute('SELECT * FROM mytable')
        self.assertEqual((1, 'Mir', 'Teiwaz'), cur.fetchone())
Beispiel #3
0
    def __init__(self, **kwargs):
        self.register_event_type('on_pre_start')
        super(ThreeDoListApp, self).__init__(**kwargs)

        if not self.db:
            connection = Connection('db.db')
            cursor = connection.cursor()
            cursor.execute("""
                            CREATE TABLE [notebook](
                            page_number INTEGER NOT NULL,
                            page TEXT NOT NULL,
                            what TEXT DEFAULT '',
                            when_ TEXT DEFAULT '',
                            why INTEGER DEFAULT 0,
                            how TEXT DEFAULT '',
                            ix INTEGER,
                            bookmark INTEGER DEFAULT 0);

                            CREATE TABLE [archive](
                            page TEXT,
                            what TEXT,
                            when_ TEXT,
                            why INTEGER DEFAULT 0,
                            how TEXT);

                            CREATE TRIGGER [on_complete]
                            INSERT ON archive
                            BEGIN DELETE FROM notebook WHERE page=new.page AND ix=new.ix AND what=new.what;
                            END;

                            INSERT INTO notebook(page_number, page)
                            VALUES(0, 'Main List')
                            """)
            #cursor.execute("commit")
            self.db = connection
Beispiel #4
0
    def connect(self):
        try:
            logger.debug2(u"Connecting to sqlite db '%s'" % self._database)
            if not self._connection:
                self._connection = Connection(filename=self._database,
                                              flags=SQLITE_OPEN_READWRITE
                                              | SQLITE_OPEN_CREATE
                                              | SQLITE_CONFIG_MULTITHREAD,
                                              vfs=None,
                                              statementcachesize=100)
            if not self._cursor:

                def rowtrace(cursor, row):
                    valueSet = {}
                    for rowDescription, current in izip(
                            cursor.getdescription(), row):
                        valueSet[rowDescription[0]] = current

                    return valueSet

                self._cursor = self._connection.cursor()
                if not self._synchronous:
                    self._cursor.execute('PRAGMA synchronous=OFF')
                    self._cursor.execute('PRAGMA temp_store=MEMORY')
                    self._cursor.execute('PRAGMA cache_size=5000')
                if self._databaseCharset.lower() in ('utf8', 'utf-8'):
                    self._cursor.execute('PRAGMA encoding="UTF-8"')
                self._cursor.setrowtrace(rowtrace)
            return (self._connection, self._cursor)
        except Exception as connectionError:
            logger.warning("Problem connecting to SQLite databse: {!r}",
                           connectionError)
            raise connectionError
def update_info(ti: lt.torrent_info, conn: apsw.Connection) -> None:
    # Change OR to AND when https://github.com/arvidn/libtorrent/issues/6913 is fixed
    conn.cursor().execute(
        "UPDATE torrent SET info = ?3 "
        "WHERE (info_sha1 IS ?1) OR (info_sha256 IS ?2) "
        "AND (info IS NULL)",
        (*_ih_bytes(ti.info_hashes()), ti.info_section()),
    )
def test_bad_resume_data(conn: apsw.Connection) -> None:
    conn.cursor().execute(
        "INSERT INTO torrent (info_sha1, info_sha256, resume_data, info) VALUES "
        "(RANDOMBLOB(20), RANDOMBLOB(32), X'00', NULL)")

    atps = list(resume.iter_resume_data_from_db(conn))

    assert atps == []
def insert_or_ignore_resume_data(
    atp: lt.add_torrent_params, conn: apsw.Connection
) -> None:
    conn.cursor().execute(
        "INSERT OR IGNORE INTO torrent (info_sha1, info_sha256, resume_data) "
        "VALUES (?1, ?2, ?3)",
        (*_ih_bytes(info_hashes(atp)), resume_data(atp)),
    )
Beispiel #8
0
    def test_upgrade_dispersy(self):
        self.torrent_upgrader._update_dispersy()

        db_path = os.path.join(self.sqlite_path, u"dispersy.db")
        connection = Connection(db_path)
        cursor = connection.cursor()
        self.assertFalse(list(cursor.execute(u"SELECT * FROM community WHERE classification == 'SearchCommunity'")))
        self.assertFalse(list(cursor.execute(u"SELECT * FROM community WHERE classification == 'MetadataCommunity'")))
        cursor.close()
        connection.close()
    def test_upgrade_dispersy(self):
        self.torrent_upgrader._update_dispersy()

        db_path = os.path.join(self.sqlite_path, u"dispersy.db")
        connection = Connection(db_path)
        cursor = connection.cursor()
        self.assertFalse(list(cursor.execute(u"SELECT * FROM community WHERE classification == 'SearchCommunity'")))
        self.assertFalse(list(cursor.execute(u"SELECT * FROM community WHERE classification == 'MetadataCommunity'")))
        cursor.close()
        connection.close()
def test_insert_info_hash_sizes(conn: apsw.Connection) -> None:
    with pytest.raises(apsw.ConstraintError):
        conn.cursor().execute(
            "INSERT INTO torrent (info_sha1, resume_data) VALUES (?, X'00')",
            (b"aaa",),
        )
    with pytest.raises(apsw.ConstraintError):
        conn.cursor().execute(
            "INSERT INTO torrent (info_sha256, resume_data) VALUES (?, X'00')",
            (b"bbb",),
        )
def update_info_hashes(ih: lt.info_hash_t, conn: apsw.Connection) -> None:
    params = _ih_bytes(ih)
    info_sha1, info_sha256 = params
    if info_sha1 is None or info_sha256 is None:
        return
    conn.cursor().execute(
        "UPDATE torrent SET info_sha1 = ?1 "
        "WHERE (info_sha1 IS NULL) AND (info_sha256 IS ?2)",
        params,
    )
    conn.cursor().execute(
        "UPDATE torrent SET info_sha256 = ?2 "
        "WHERE (info_sha256 IS NULL) AND (info_sha1 IS ?1)",
        params,
    )
def _init_db(conn: apsw.Connection, schema: str) -> None:
    assert schema == "main"
    # NB: nulls are distinct in unique constraints. See https://sqlite.org/nulls.html
    conn.cursor().execute(
        "CREATE TABLE torrent ("
        "info_sha1 BLOB, "
        "info_sha256 BLOB, "
        "resume_data BLOB NOT NULL, "
        "info BLOB, "
        "CHECK (info_sha1 IS NOT NULL OR info_sha256 IS NOT NULL), "
        "CHECK (info_sha1 IS NULL OR LENGTH(info_sha1) = 20), "
        "CHECK (info_sha256 IS NULL OR LENGTH(info_sha256) = 32), "
        "UNIQUE (info_sha1), "
        "UNIQUE (info_sha256))"
    )
Beispiel #13
0
 def remove_tables_from_connection(conn: apsw.Connection):
     cursor = conn.cursor()
     with conn:
         cursor.execute(
             """DROP TABLE contrib_activitymetadata_message_metadata""")
         cursor.execute(
             """DROP TABLE contrib_activitymetadata_guild_settings""")
Beispiel #14
0
def import_file(file: str, database: sql.Connection):
    with open(file, "r", -1, "utf-8") as kanjidic_file:
        xml = etree.parse(kanjidic_file)
    root = xml.getroot()
    kanji_list = root.findall("character")

    # We would ordinarily have to do 2 passes over the data two parse references between the
    # different kanji. Since this would involve parsing a lot of the tree twice we instead build
    # up the references on the first run. This uses significantly more memory (most of the mapping
    # references will go unused) but is much faster.
    codepoint_map = {}
    variant_map = {}

    cursor = database.cursor()
    with database:
        for kanji in kanji_list:
            onyomi, kunyomi, nanori = extract_readings(kanji)

            if not onyomi and not kunyomi and not nanori:
                # This kanji is most likely not used in the Japanese language
                continue

            kanji_id = import_kanji(cursor, database, kanji)

            # The reading and meaning imports insert the appropriate values into the database
            import_meanings(cursor, kanji_id, kanji)
            import_readings(cursor, kanji_id, onyomi, kunyomi, nanori)

            # Update the maps used for the deferred variant mapping
            update_reference_maps(kanji_id, kanji, codepoint_map, variant_map)

        import_references(cursor, codepoint_map, variant_map)
Beispiel #15
0
 def remove_tables_from_connection(conn: apsw.Connection):
     cursor = conn.cursor()
     with conn:
         cursor.execute("""DROP TABLE contrib_qotw_all_history""")
         cursor.execute("""DROP TABLE contrib_qotw_selected_history""")
         cursor.execute("""DROP TABLE contrib_qotw_members""")
         cursor.execute("""DROP TABLE contrib_qotw_guild_settings""")
 def set_req_all(conn: apsw.Connection, guild_id: int, role_id: int,
                 *req_role_ids: int):
     total_role_ids = {
         role_id,
         *req_role_ids,
     }
     with contextlib.closing(conn.cursor()) as cursor, conn:
         cursor.execute(
             """
             INSERT INTO guild_settings (guild_id) VALUES (?)
             ON CONFLICT (guild_id) DO NOTHING
             """,
             (guild_id, ),
         )
         cursor.executemany(
             """
             INSERT INTO role_settings (role_id, guild_id) VALUES (?,?)
             ON CONFLICT (role_id) DO NOTHING
             """,
             tuple((rid, guild_id) for rid in total_role_ids),
         )
         cursor.execute(
             """
             DELETE FROM role_requires_all WHERE role_id = ?
             """,
             (role_id, ),
         )
         cursor.executemany(
             """
             INSERT INTO role_requires_all (role_id, required_role_id) VALUES (?,?)
             ON CONFLICT (role_id, required_role_id) DO NOTHING
             """,
             tuple((role_id, other_id) for other_id in req_role_ids),
         )
 def remove_all_on_message(conn: apsw.Connection, message_id: int):
     with contextlib.closing(conn.cursor()) as cursor, conn:
         cursor.execute(
             """
             DELETE FROM react_role_entries WHERE message_id = ?
             """,
             (message_id, ),
         )
def test_good_resume_data_bad_info_dict(conn: apsw.Connection,
                                        ti: lt.torrent_info) -> None:
    atp = lt.add_torrent_params()
    atp.ti = ti
    bdecoded = lt.write_resume_data(atp)
    bdecoded.pop(b"info")
    resume_data = lt.bencode(bdecoded)
    conn.cursor().execute(
        "INSERT INTO torrent (info_sha1, info_sha256, resume_data, info) VALUES "
        "(RANDOMBLOB(20), RANDOMBLOB(32), ?, X'00')",
        (resume_data, ),
    )

    atps = list(resume.iter_resume_data_from_db(conn))

    assert len(atps) == 1
    assert atps[0].ti is None
 def remove_entry(conn: apsw.Connection, message_id: int,
                  reaction_str: str):
     with contextlib.closing(conn.cursor()) as cursor, conn:
         cursor.execute(
             """
             DELETE FROM react_role_entries WHERE message_id = ? AND reaction_string = ?
             """,
             (message_id, strip_variation_selectors(reaction_str)),
         )
Beispiel #20
0
def get_all_tasks(
        con: apsw.Connection) -> Generator[ScheduledPayloadTask, None, None]:
    with closing(con.cursor()) as cursor:
        for (uuid, payload, schedule, tzi) in cursor.execute("""
            SELECT uuid, payload, schedule, tzinfo
            FROM crontab
            WHERE version=1
            """):
            ce = CronEntry.parse(schedule)
            yield ScheduledPayloadTask(ce, payload, uuid, pytz.timezone(tzi))
 def set_sticky(self, conn: apsw.Connection, val: bool):
     cursor = conn.cursor()
     with contextlib.closing(conn.cursor()) as cursor, conn:
         cursor.execute(
             """
             INSERT INTO guild_settings (guild_id) VALUES (?)
             ON CONFLICT (guild_id) DO NOTHING
             """,
             (self.guild_id, ),
         )
         cursor.execute(
             """
             INSERT INTO role_settings (role_id, guild_id, sticky) VALUES (?,?,?)
             ON CONFLICT (role_id)
             DO UPDATE SET
                 sticky=excluded.sticky
             """,
             (self.role_id, self.guild_id, val),
         )
def test_resume_data_external_info(conn: apsw.Connection,
                                   ti: lt.torrent_info) -> None:
    orig = lt.add_torrent_params()
    orig.ti = ti
    bdecoded = lt.write_resume_data(orig)
    bdecoded.pop(b"info")
    resume_data = lt.bencode(bdecoded)
    info = ti.info_section()
    info_hashes = ti.info_hashes()
    conn.cursor().execute(
        "INSERT INTO torrent (info_sha1, info_sha256, resume_data, info) "
        "VALUES (?, ?, ?, ?)",
        (info_hashes.v1.to_bytes(), info_hashes.v2.to_bytes(), resume_data,
         info),
    )

    atps = list(resume.iter_resume_data_from_db(conn))

    assert len(atps) == 1
    assert normalize(atps[0]) == normalize(orig)
    def bulk_remove_exclusivity(conn: apsw.Connection, guild_id: int,
                                role_ids: Iterable[int]):

        with contextlib.closing(conn.cursor()) as cursor, conn:
            cursor.executemany(
                """
                DELETE FROM role_mutual_exclusivity
                WHERE role_id_1 = ?1 OR role_id_2 = ?1
                """,
                tuple((rid, ) for rid in set(role_ids)),
            )
Beispiel #24
0
def unstore_task(connection: apsw.Connection, s_type: str, uuid: bytes):
    if s_type == CRON:
        with closing(connection.cursor()) as cursor:
            cursor.execute(
                """
                DELETE FROM crontab WHERE uuid=?
                """,
                (uuid, ),
            )
    else:
        raise RuntimeError("Unexpected type: %s" % s_type)
    def all_in_guild(cls, conn: apsw.Connection, guild_id: int):
        cursor = conn.cursor()
        rows = cursor.execute(
            """
            SELECT guild_id, channel_id, message_id, reaction_string, role_id, react_remove_triggers_removal
            FROM react_role_entries
            WHERE guild_id=?
            """,
            (guild_id, ),
        )

        return [cls(*row) for row in rows]
Beispiel #26
0
 def setUp(self):
     self.db = Connection(':memory:')
     cur = self.db.cursor()
     cur.execute("""
     CREATE TABLE mytable (
         id INTEGER PRIMARY KEY,
         firstname TEXT,
         lastname TEXT)""")
     cur.execute("""
     INSERT INTO mytable (id, firstname, lastname)
     VALUES
     (1, 'Mir', 'Jakuri')
     """)
    def self_assignable_ids_in_guild(conn: apsw.Connection,
                                     guild_id: int) -> List[int]:
        cursor = conn.cursor()

        return [
            r[0] for r in cursor.execute(
                """
                SELECT role_id FROM role_settings
                WHERE guild_id = ? AND self_assignable
                """,
                (guild_id, ),
            )
        ]
Beispiel #28
0
 def setUp(self):
     self.db = Connection(':memory:')
     cur = self.db.cursor()
     cur.execute("""
     CREATE TABLE mytable (
         firstname TEXT,
         lastname TEXT,
         age INTEGER
     )""")
     cur.execute("""
     INSERT INTO mytable (firstname, lastname, age)
     VALUES
     ('Mir', 'Jakuri', 10)
     """)
Beispiel #29
0
class MultipleKeyTestCase(unittest.TestCase):

    def setUp(self):
        self.db = Connection(':memory:')
        cur = self.db.cursor()
        cur.execute("""
        CREATE TABLE mytable (
            firstname TEXT,
            lastname TEXT,
            age INTEGER
        )""")
        cur.execute("""
        INSERT INTO mytable (firstname, lastname, age)
        VALUES
        ('Mir', 'Jakuri', 10)
        """)

    def test_upsert_multiple_key_insert(self):
        utils.upsert(self.db, 'mytable', ['firstname', 'lastname'], {
            'firstname': 'Mir',
            'lastname': 'Teiwaz',
            'age': 350,
        })
        cur = self.db.cursor()
        cur.execute('SELECT * FROM mytable')
        self.assertEqual(('Mir', 'Jakuri', 10), cur.fetchone())
        self.assertEqual(('Mir', 'Teiwaz', 350), cur.fetchone())

    def test_upsert_multiple_key_update(self):
        utils.upsert(self.db, 'mytable', ['firstname', 'lastname'], {
            'firstname': 'Mir',
            'lastname': 'Jakuri',
            'age': 350,
        })
        cur = self.db.cursor()
        cur.execute('SELECT * FROM mytable')
        self.assertEqual(('Mir', 'Jakuri', 350), cur.fetchone())
Beispiel #30
0
 def __init__(self, config):
    # TODO: concurrency issue resolved?
    if config['apsw']:
       from apsw import Connection
       self.store = Connection(config['sql'])
       self.store.cursor().execute('PRAGMA foreign_keys = ON')
    else:
       from sqlite3 import connect
       self.store = connect(config['sql'], check_same_thread=False)
       self.store.execute('PRAGMA foreign_keys = ON')
    self._loadAdminConf(config['admin'])
    self.dropbox = config['dropbox']
    # TODO: Awkward, but this'll need to be the hour difference between Pacific
    # and wherever the server is. Shouldn't be an issue if we use GU.
    self.timezone = 1 * 60 * 60
Beispiel #31
0
def store_task(connection: apsw.Connection, task: ScheduledPayloadTask):
    with closing(connection.cursor()) as cursor:
        zone_str = task.zone.zone  # type: ignore
        s_type, s_ver, s_parse = task.schedule.to_store()
        if s_type == CRON:
            cursor.execute(
                """
                INSERT INTO crontab(uuid, payload, schedule, tzinfo, version)
                VALUES(?,?,?,?,?)
                """,
                (task.uuid, task.payload, s_parse, zone_str, s_ver),
            )
        else:
            # If this happens, I forgot to add logic for the others after adding them
            raise RuntimeError("Unexpected type: %s" % s_type)
def get_member_sticky(
    conn: apsw.Connection,
    guild_id: int,
    member_id: int,
) -> Sequence[int]:

    cursor = conn.cursor()

    return tuple(row[0] for row in cursor.execute(
        """
        SELECT role_id FROM roles_stuck_to_members
        WHERE user_id = ? AND guild_id = ?
        """,
        (member_id, guild_id),
    ))
def update_member_sticky(
    conn: apsw.Connection,
    guild_id: int,
    member_id: int,
    added_roles: Iterable[int],
    removed_roles: Iterable[int],
):
    with contextlib.closing(conn.cursor()) as cursor, conn:

        cursor.execute(
            """
            INSERT INTO guild_settings (guild_id) VALUES (?)
            ON CONFLICT (guild_id) DO NOTHING
            """,
            (guild_id, ),
        )
        cursor.execute(
            """
            INSERT INTO user_settings(user_id) VALUES (?)
            ON CONFLICT (user_id) DO NOTHING
            """,
            (member_id, ),
        )
        cursor.execute(
            """
            INSERT INTO member_settings (user_id, guild_id) VALUES (?,?)
            ON CONFLICT (user_id, guild_id) DO NOTHING
            """,
            (member_id, guild_id),
        )
        if removed_roles:
            cursor.executemany(
                """
                DELETE FROM roles_stuck_to_members WHERE
                guild_id = ? AND user_id = ? AND role_id = ?
                """,
                tuple((guild_id, member_id, rid) for rid in removed_roles),
            )
        if added_roles:
            cursor.executemany(
                """
                INSERT INTO roles_stuck_to_members (guild_id, user_id, role_id)
                SELECT ?1, ?2, ?3
                WHERE EXISTS(SELECT 1 FROM role_settings WHERE role_id = ?3 AND sticky)
                ON CONFLICT (guild_id, user_id, role_id) DO NOTHING
                """,
                tuple((guild_id, member_id, rid) for rid in added_roles),
            )
Beispiel #34
0
def match_character_extracts(anime_database, character_extracts):
    """Match and merge characters using an anime database."""
    next_reference_id = 1
    reference_id_to_character = {}
    with Connection(anime_database) as connection:
        connection.createscalarfunction("lv_jaro",
                                        jaro,
                                        numargs=2,
                                        deterministic=True)
        cursor = connection.cursor()
        cursor.execute("delete from unmatched_character")
        for filename in tqdm(character_extracts):
            # pylint: disable=line-too-long
            with open_transcoded(filename, "r",
                                 errors="ignore") as character_fileobj:
                character_json = json.loads(
                    character_fileobj.read().decode("utf8"))
            for character in tqdm(character_json):
                reference_id = next_reference_id
                reference_id_to_character[reference_id] = character
                character_names = \
                    list(chain.from_iterable(character["names"].values()))
                anime_names = [a["name"] for a in character["anime_roles"]]
                for character_name, anime_name in product(
                        character_names, anime_names):
                    cursor.execute(
                        "insert into unmatched_character ("
                        "character_name, normalized_character_name,"
                        "anime_name, normalized_anime_name, "
                        "reference_id) values (?, ?, ?, ?, ?)",
                        (character_name,
                         normalize_character_name(character_name), anime_name,
                         normalize_anime_name(anime_name), reference_id))
                next_reference_id += 1
    cursor.execute("REINDEX")
    for (reference_id_csv, ) in cursor.execute(MATCH_SQL):
        reference_ids = list(map(int, reference_id_csv.split(",")))
        characters = map(reference_id_to_character.get, reference_ids)
        merged_character = reduce(merge_character_metadata, characters)
        if is_sensitive_metadata(merged_character):
            print(
                f"Skipping character {merged_character['names']['en']} "
                f"due to sensitive metadata.",
                file=sys.stderr)
            continue
        yield merged_character
Beispiel #35
0
    def __init__(self,name):
        '''
        Set up C{apsw} connection and cursor for database given filename.

        @param name: database file name
        @type name: C{string}
        '''
        self.filename = name
        try:
            self.db = Connection(self.filename)
        except CantOpenError:
            print "Can't open database %s"%(self.filename)
            raise

        # rowDict function used to manipulate output into dictionary form.
        self.primaryCursor = self.db.cursor()
        self.primaryCursor.setrowtrace(self.__rowDict)
def test_insert_info_hashes_unique(conn: apsw.Connection) -> None:
    cur = conn.cursor()
    info_sha1, info_sha256 = next(cur.execute("SELECT RANDOMBLOB(20), RANDOMBLOB(32)"))
    cur.execute(
        "INSERT INTO torrent (info_sha1, info_sha256, resume_data) "
        "VALUES (?, ?, X'00')",
        (info_sha1, info_sha256),
    )
    with pytest.raises(apsw.ConstraintError):
        cur.execute(
            "INSERT INTO torrent (info_sha1, resume_data) VALUES (?, X'00')",
            (info_sha1,),
        )
    with pytest.raises(apsw.ConstraintError):
        cur.execute(
            "INSERT INTO torrent (info_sha256, resume_data) VALUES (?, X'00')",
            (info_sha256,),
        )
    def from_database(cls, conn: apsw.Connection, message_id: int,
                      reaction_str: str):

        cursor = conn.cursor()

        row = cursor.execute(
            """
            SELECT guild_id, channel_id, message_id, reaction_string, role_id, react_remove_triggers_removal
            FROM react_role_entries
            WHERE message_id = ? AND reaction_string = ?
            """,
            (message_id, reaction_str),
        ).fetchone()

        if not row:
            raise NoSuchRecord()

        return cls(*row)
Beispiel #38
0
class DatabaseController(object):
    def __init__(self,name):
        '''
        Set up C{apsw} connection and cursor for database given filename.

        @param name: database file name
        @type name: C{string}
        '''
        self.filename = name
        try:
            self.db = Connection(self.filename)
        except CantOpenError:
            print "Can't open database %s"%(self.filename)
            raise

        # rowDict function used to manipulate output into dictionary form.
        self.primaryCursor = self.db.cursor()
        self.primaryCursor.setrowtrace(self.__rowDict)

    def __rowDict(self,cursor,row):
        '''
        Transform rows returned into dicts, {colname:value}

        @param cursor: C{apsw} connection cursor, called automatically from C{rowtrace}
        @type cursor: C{apsw.Connection.cursor}
        @param row: a row from the database
        @type row: C{tuple}
        '''
        desc = cursor.getdescription()
        desc = [val[0] for val in desc]  # extract just the names of table columns
        d = dict(zip(desc,row))  # turn into a dictionary, name:value pairs
        return d

    def getTableNames(self):
        '''
        Return a list of table names in the database

        @return: List of strings of table names in database
        @rtype: C{list[string]}
        '''
        self.primaryCursor.execute("select name from sqlite_master;")
        return [a['name'] for a in self.primaryCursor.fetchall()]  # make a flat array of names
def iter_resume_data_from_db(conn: apsw.Connection) -> Iterator[lt.add_torrent_params]:
    version = get_version(conn)
    if version == 0:
        return
    dbver.semver_check_breaking(LATEST, version)
    cur = conn.cursor().execute(
        "SELECT info_sha1, info_sha256, resume_data, info FROM torrent"
    )
    for row in cur:
        info_sha1, info_sha256, resume_data, info = cast(
            tuple[Optional[bytes], Optional[bytes], bytes, Optional[bytes]], row
        )
        # NB: certain fields (creation date, creator, comment) live in the torrent_info
        # object at runtime, but are serialized with the resume data. If the b"info"
        # field is empty, the torrent_info won't be created, and these fields will be
        # dropped. We want to deserialize the resume data all at once, rather than
        # deserialize the torrent_info separately.
        info_dict: Optional[Any] = None
        if info is not None:
            try:
                with ltpy.translate_exceptions():
                    info_dict = lt.bdecode(info)
            except ltpy.Error:
                _LOG.exception(
                    "%s parsing info dict", _log_ih_bytes(info_sha1, info_sha256)
                )
        try:
            with ltpy.translate_exceptions():
                bdecoded = lt.bdecode(resume_data)
                if not isinstance(bdecoded, dict):
                    _LOG.error(
                        "%s resume data not a dict",
                        _log_ih_bytes(info_sha1, info_sha256),
                    )
                    continue
                if bdecoded.get(b"info") is None and info_dict is not None:
                    bdecoded[b"info"] = info_dict
                yield lt.read_resume_data(lt.bencode(bdecoded))
        except ltpy.Error:
            _LOG.exception(
                "%s parsing resume data", _log_ih_bytes(info_sha1, info_sha256)
            )
Beispiel #40
0
def create_anime_db(database, anime_extract):
    """Create an anime database from an anime extract."""
    with open_transcoded(anime_extract, "r", errors="ignore") as anime_fileobj:
        anime_json = json.loads(anime_fileobj.read().decode("utf8"))
    with Connection(database) as connection:
        connection.createscalarfunction("lv_jaro",
                                        jaro,
                                        numargs=2,
                                        deterministic=True)
        cursor = connection.cursor()
        cursor.execute(SCHEMA_SQL)
        for anime_id, anime in enumerate(tqdm(anime_json), start=1):
            cursor.execute("insert into anime values (:anime_id)",
                           {"anime_id": anime_id})
            for name in anime["names"]:
                cursor.execute(
                    "insert into anime_name (is_primary, anime_id, "
                    "anime_name, normalized_anime_name) values (:is_primary, "
                    ":anime_id, :anime_name, :normalized_anime_name)", {
                        "is_primary":
                        name["is_primary"],
                        "anime_id":
                        anime_id,
                        "anime_name":
                        name["name"],
                        "normalized_anime_name":
                        (normalize_anime_name(name["name"]))
                    })
            for character in anime["characters"]:
                cursor.execute(
                    "insert into character_name (anime_id, character_name, "
                    "normalized_character_name) values (:anime_id, "
                    ":character_name, :normalized_character_name)", {
                        "anime_id":
                        anime_id,
                        "character_name":
                        character["name"],
                        "normalized_character_name":
                        (normalize_character_name(character["name"]))
                    })
        cursor.execute("REINDEX")
Beispiel #41
0
from json import load
from apsw import Connection
from os.path import join
from datetime import datetime

config = load(open('/home/main/manager/data/config'))
src = Connection(config['sql'])
dst_file = str(int(datetime.now().strftime("%s")))
dst = Connection(join(config['backups'], dst_file))

with dst.backup('main', src, 'main') as backup:
   while not backup.done:
      backup.step(100)
Beispiel #42
0
class SQLStore:
   def __init__(self, config):
      # TODO: concurrency issue resolved?
      if config['apsw']:
         from apsw import Connection
         self.store = Connection(config['sql'])
         self.store.cursor().execute('PRAGMA foreign_keys = ON')
      else:
         from sqlite3 import connect
         self.store = connect(config['sql'], check_same_thread=False)
         self.store.execute('PRAGMA foreign_keys = ON')
      self._loadAdminConf(config['admin'])
      self.dropbox = config['dropbox']
      # TODO: Awkward, but this'll need to be the hour difference between Pacific
      # and wherever the server is. Shouldn't be an issue if we use GU.
      self.timezone = 1 * 60 * 60

   """
   Returns true iff the argument 'username' is equal to a known admin username.
   """
   def isInstructor(self, username):
      return username == self.admin_name

   """
   Returns true iff the argument 'username' is associcated with the given student
   table primary key.
   """
   def isStudent(self, username, id):
      return self.usernameID(username) == id;

   """
   Returns the student primary key for a user with the given username.
   """
   def usernameID(self, username):
      matches = self._select('students', [('username', username)])
      return None if len(matches) == 0 else matches[0]['id']

   """
   Returns true iff the given username matches the given password.
   """
   def passwordMatches(self, username, passwordCandidate):
      actual = self.password(username)
      if actual == None:
         return False
      return pwd_context.verify(passwordCandidate, actual)

   """
   Returns all offerings of the course with code 'courseCode', or None if no such
   course exists.
   """
   def offerings(self, courseCode):
      return self._select('courses', [('code', courseCode)])

   """
   Returns the most recent course offering with code 'courseCode', or None if no
   such course exists.
   """
   def lastOffering(self, courseCode):
      offerings = self.offerings(courseCode)
      if len(offerings) == 0:
         return None
      return sorted(offerings, compare_courses)[-1]

   """
   Returns the hashed password for the user with the given login 'username'.
   """
   def password(self, username):
      if self.isInstructor(username):
         return self.admin_pass
      else:
         info = self._select('students', [('username', username)])
         if len(info) != 1:
            return None
         return info[0]['password']

   """
   Gives the given 'student' the given 'password', overwriting the prior password.
   """
   def editPassword(self, student, password):
      self._update('students', [('password', sha256_crypt.encrypt(password))],
                   [('id', student)])

   """
   Adds a new course with the given attributes.
   If course is just an integer (like: 101), adds the prefix 'PHIL '.
   The new course will inherit all assignments from the last offering of a course
   with the same code.
   Returns the primary key identifier for the new course.
   """
   def addCourse(self, name, code, year, semester, active=False):
      try:
         code = 'PHIL {0}'.format(int(code))
      except:
         pass

      lastOffering = self.lastOffering(code)
      vals = (name, code, year, semester, int(active))
      id = self._insert('courses', external('courses'), vals)

      # Inherit all assignments from the last offering with this code.
      if lastOffering != None:
         for asst in self.assignments(lastOffering['id']):
            del asst['id']
            asst['course'] = id
            self.addAssignment(**asst)

         for link in self.links(lastOffering['id']):
            del link['id']
            link['course'] = id
            self.addLink(**link)
      return id

   """
   Returns a list containing a course dictionary for each registered course.
   """
   def courses(self):
      return self._select('courses')

   """
   Adds a new student with the given attributes. If the username is already
   chosen, assigns the same username with an integer suffix that will make it
   unique.
   Returns the primary key identifier for the new student.
   """
   def addStudent(self, course, section, name, email, notes, username, pseudonym, password):
      # Ensure unique username.
      prior = self._select('students', [('username', username)])
      while username == self.admin_name or len(prior) != 0:
         username += str('_')
         prior = self._select('students', [('username', username)]) 

      vals = (course, section, name, email, notes,
              username, pseudonym, sha256_crypt.encrypt(password))
      return self._insert('students', external('students'), vals)

   """
   Returns all the students registered for the given 'course'.
   If 'bySection' is true, returns a map from section name to list containing a
   student dictionary for each registered student in that section of 'course'.
   """
   def students(self, course=None, bySection=True):
      students = self._select('students', [] if course == None else [('course', course)])
      return _groupBy(students, 'section') if bySection else students

   """
   Returns the course in which the student with the given ID is enrolled.
   """
   def courseFor(self, student):
      matches = self._select('students', [('id', student)])
      return None if len(matches) == 0 else matches[0]['course']

   """
   Returns the full stored name of the student.
   """
   def studentName(self, student):
      matching = self._select('students', [('id', student)])
      return None if len(matching) == 0 else matching[0]['name']

   """
   Adds a new link with the given attributes.
   Returns the primary key identifier for the new link.
   """
   def addLink(self, course, link, description):
      vals = (course, link, description)
      return self._insert('links', external('links'), vals)

   """
   Returns a list of all the link dictionaries for the given 'course'.
   """
   def links(self, course):
      return self._select('links', [('course', course)])

   """
   Adds a new assignment with the given attributes.
   Returns the primary key identifier for the new assignment.
   """
   def addAssignment(self, course, name, description, formal, required,
                     dropbox, min_weight, max_weight, default_weight):

      vals = (course, name, description, int(formal), int(required),
              int(dropbox), min_weight, max_weight, default_weight)
      return self._insert('assignments', external('assignments'), vals)

   """
   Returns the info dict for the assignment with the given 'id'.
   """
   def assignmentInfo(self, id):
      matching = self._select('assignments', [('id', id)])
      return None if len(matching) == 0 else matching[0]

   """
   Returns a list of all the assignment dictionaries for the given 'course'.
   """
   def assignments(self, course=None):
      return self._select('assignments', [] if course == None else [('course', course)])

   """
   Returns the full stored name of the assignment.
   """
   def assignmentName(self, assignment):
      matching = self._select('assignments', [('id', assignment)])
      return None if len(matching) == 0 else matching[0]['name']

   """
   Returns the row entry in 'table' with primary key 'id', or None if no such entry
   exists.
   """
   def row(self, table, id):
      matching = self._select(table, [('id', id)])
      if len(matching) == 0:
         return None
      return matching[0]

   """
   Returns true if 'id' corresponds is the primary key to a row in 'table'.
   """
   def rowExists(self, table, id):
      return self.row(table, id) != None

   """
   Updates the row in 'table' with given 'primary_key' according to each (column,
   new value) mapping in 'changes'.
   """
   def editRowEntry(self, table, primary_key, changes):
      set = []
      for column in changes:
         set.append((column, changes[column]))
      self._update(table, set, [ ('id', primary_key) ])

   """
   Deletes the row in 'table' with 'id'.
   """
   def deleteRowEntry(self, table, id):
      self._delete(table, [('id', id)])

   """
   Removes 'student's score for 'assignment'.
   """
   def removeScore(self, student, assignment):
      self._delete('scores', [('student', student), ('assignment', assignment)])

   """
   Removes the weight that 'student' has given to 'assignment'.
   """
   def removeWeight(self, student, assignment):
      self._delete('weights', [('student', student), ('assignment', assignment)])

   """
   Deletes all rows from 'table' that match 'where' clause (list of k=v pairs).
   """
   def _delete(self, table, where):
      if table == '*':
         raise ValueError('Give a specific table.')
      if len(where) == 0:
         raise ValueError('Narrow the WHERE down a bit, yeah?')
      placeholders, values = _escape(where, 'AND ')
      fmt = 'DELETE FROM {0} WHERE {1}'.format(table, placeholders)
      with self.store:
         cursor = self.store.cursor()
         cursor.execute(fmt, values)
         self.store.commit()

   """
   Returns a map of maps, keyed first on student and then on assignment id. An
   entry for every student, assignment pair in the 'given' course is included.
   Each value is either that student's score on that assignment, or None if no
   score is recorded.
   """
   def scores(self, course):
      return self._fullGradesheet('scores', 'score', course)

   """
   Returns a map of maps (see scores() for format), but instead of score values,
   includes weight values. Any weight that value that would be None (has no entry
   in the weights table) is given the default value for that assignment.
   """
   def weights(self, course):
      assigned = self._fullGradesheet('weights', 'weight', course)
      # If the 'weights' table doesn't have an entry for a given (student,
      # assignment) pair, assume the default.
      assignments = self._select('assignments', [('course', course)])
      defaults = {}
      for a in assignments:
         defaults[a['id']] = a['default_weight']
      for student in assigned:
         for assignment in assigned[student]:
            if assigned[student][assignment] == None:
               assigned[student][assignment] = float(defaults[assignment])
      return assigned

   """
   Returns a map with an assignment id key for each assignment in the given
   'course'. Each value is a dict keyed on ('min', 'max', 'allowZero')
   describing basic validation constraints for that assignment. An assignment
   is allowed to have a zero weight when it is not required.
   """
   def weightConstraints(self, course):
      res = {}
      for a in self.assignments(course):
         res[a['id']] = {
            'min': a['min_weight'],
            'max': a['max_weight'],
            'allowZero': not a['required']
         }
      return res

   """
   Returns a map of maps (see scores() for format), with values from either the
   "scores" or "weights" table. 'keyColumn' is either "score" or "weight".
   Raises ValueError if some other table is passed.
   """
   def _fullGradesheet(self, table, keyColumn, course):
      if table != 'scores' and table != 'weights':
         raise ValueError('Invalid table.')
      if keyColumn != 'score' and keyColumn != 'weight':
         raise ValueError('Invalid keyColumn.')
      with self.store:
         cursor = self.store.cursor()
         cursor.execute('SELECT students.id, {0}.assignment, {0}.{1} \
                         FROM students \
                         JOIN {0} ON students.id={0}.student \
                         WHERE students.course=?'.format(table, keyColumn), [course])
         res = self._blankGradesheet(course)
         rows = cursor.fetchall()
      for (student, assignment, grade) in rows:
         res[student][assignment] = grade
      return res

   """
   Returns a map of maps, keyed first on student and then on assignment id. An
   entry for every student, assignment pair in the 'given' course is included,
   with every value 'None'.
   """
   def _blankGradesheet(self, course):
      students = self.students(course, bySection=False)
      assignments = self.assignments(course)
      res = {}
      for s in students:
         if s['id'] not in res:
            res[s['id']] = {}
         for a in assignments:
            res[s['id']][a['id']] = None
      return res

   """
   Sets the given student's score on the given assignment to that provided,
   overwriting any prior score for that (student, assignment) pair.
   """
   def setScore(self, student, assignment, score):
      # TODO: Should be implemented with INSERT OR REPLACE INTO for efficiency.
      self._setGradeAttribute(student, assignment, 'scores', 'score', score)

   """
   Sets the given student's weight for the given assignment to that provided,
   overwriting any prior weight for that (student, assignment) pair. If the
   assignment is optional, the weight can be either 0 or in the required
   weight range for the assignment. If the assignment is required, the weight
   must be within the required weight range for the assignment.
   """
   def setWeight(self, student, assignment, weight):
      # Set the weight to the minimum or maximum allowed for that assignment.
      a = self._select('assignments', [('id', assignment)])
      if len(a) == 0:
         raise ValueError('Unknown assignment.')
      else:
         a = a[0]
      if a['required'] or weight != 0:
         weight = max(a['min_weight'], weight)
         weight = min(a['max_weight'], weight)

      self._setGradeAttribute(student, assignment, 'weights', 'weight', weight)

   """
   Returns a list of file entries in the given 'student's dropbox for the given
   'assignment'. If two file submissions have the same name, only the most
   recent (based on submit timestamp) is included.
   """
   def studentDropbox(self, student, assignment):
      where = [('student', student), ('assignment', assignment)]
      revisions = _groupBy(self._select('dropbox', where), 'original')
      
      return self.fileRevisionHeads_(self._select('dropbox', where))

   """
   Returns a map from 'student' id to list of files is that student's dropbox
   for the given 'assignment'.
   """
   def studentDropboxes(self, assignment):
      files = self._select('dropbox', [('assignment', assignment)])
      files = sorted(files, key=lambda f : f['timestamp'])
      byStudent = _groupBy(files, 'student')
      for id in byStudent.keys():
         byStudent[id] = self.fileRevisionHeads_(byStudent[id])
      return byStudent;

   """
   Returns files filtered to exclude a file f if any other file has the same
   original name but a later timestamp.
   """
   def fileRevisionHeads_(self, files):
      revisionGroups = _groupBy(files, 'original').values()
      revisionGroups = filter(lambda gp : len(gp) > 0, revisionGroups)
      def latestSubmission(files):
         return sorted(files, key=lambda f : f['timestamp'])[-1]
      return map(latestSubmission, revisionGroups)
      

   """
   Saves the file to the dropbox directory according to a safe file naming scheme,
   recording the file's new dropbox path in the store. The file's original name
   is recorded unless 'newName' is provided, in which case this filename is used.
   Returns the file ID of the new file.
   """
   def addDropboxFile(self, student, assignment, file, newName=None):
      course = self.assignmentInfo(assignment)['course']

      timestamp = int((datetime.now() - datetime(1970, 1, 1)).total_seconds())
      timestamp += self.timezone
      original_name = newName or secure_filename(file.filename)

      # Add database entry to retrieve new ID.
      id = self._insert('dropbox', external('dropbox'),
            (assignment, student, '', original_name, timestamp))

      path = self._dropboxPath(course)
      path = join(path, '_'.join(map(str, (id, student, assignment, original_name))))

      # Save file contents, ensuring no overwrites.
      if exists(path):
         raise ValueError('Attempted to overwrite previous file.')
      file.save(path)

      # Record path in database.
      self._update('dropbox', [('path', path)], [('id', id)])
      return id

   """
   Retrives the stored dropbox file info dict with the given primary key 'id'.
   """
   def getDropboxFile(self, id):
      res = self._select('dropbox', [('id', id)])
      return res[0] if len(res) > 0 else None

   """
   Returns the directory for this 'course's file dropbox, creating one if it does
   not already exist.
   """
   def _dropboxPath(self, course):
      path = '{0}/course_{1}'.format(self.dropbox, course)
      if not exists(path):
         makedirs(path)
      return path

   """
   Sets the grade attribute (either score or weight) for the student, assignment
   pair. 'table' should be either 'scores' or 'weights'. 'key' should be either
   'score' or 'weight', respectively.
   """
   def _setGradeAttribute(self, student, assignment, table, key, value):
      keys = ('student', 'assignment', key)
      values = (student, assignment, value)
      priorWhere = [('student', student), ('assignment', assignment)]
      prior = self._select(table, priorWhere)
      if len(prior) == 0:
         self._insert(table, keys, values)
      else:
         self._update(table, [(key, value)], [('id', prior[0]['id'])])

   """
   Updates all rows in 'table' matching all filters in 'where' to the values
   provided in 'set':
      UPDATE <table> SET <set> WHERE <where>
   Both 'set' and 'where' are lists of (column name, value) pairs.
   """
   def _update(self, table, set, where):
      if table not in TABLES:
         raise ValueError('Unknown table "{0}".'.format(table))
      fmt = 'UPDATE {0} SET {1} WHERE {2}'
      sets, setValues = _escape(set)
      wheres, whereValues = _escape(where)
      with self.store:
         cursor = self.store.cursor()         
         cursor.execute(fmt.format(table, sets, wheres), (setValues + whereValues))
         self.store.commit()

   """
   Returns the fetched rows resulting from executing an SQL query of the form
      SELECT * FROM <table> WHERE <where>
   as a list of row dictionaries keyed on column names and valued on entry values.
   """
   def _select(self, table, where=[]):
      if table not in TABLES:
         raise ValueError('Unknown table "{0}".'.format(table))
      with self.store:
         cursor = self.store.cursor()
         fmt = 'SELECT * FROM {0}'.format(table)
         if len(where) != 0:
            placeholders, values = _escape(where, 'AND ')
            fmt += ' WHERE ' + placeholders
            cursor.execute(fmt, values)
         else:
            cursor.execute(fmt)
         rows = cursor.fetchall()
      keys = map(lambda k : k[0], TABLES[table])
      return map(partial(toDict, keys), rows)

   """
   Returns the new primary key of a newly inserted row resulting from a query of
   the form:
      INSERT INTO <table>(<keys>) VALUES(<values>)
   """
   def _insert(self, table, keys, values):
      if table not in TABLES:
         raise ValueError('Unknown table "{0}".'.format(table))
      fmt = 'INSERT INTO {0}({1}) VALUES({2})'
      fmt_vals = table, ','.join(keys), ','.join(['?'] * len(values))
      with self.store:
         cursor = self.store.cursor()
         cursor.execute(fmt.format(*fmt_vals), values)
         self.store.commit()
         return cursor.lastrowid

   """
   Adds a new table for each described in db_scheme.TABLES. Assumes no such TABLES
   have already been created in this store.
   """
   def createTables(self):
      fmt = 'CREATE TABLE {0}({1})'
      with self.store:
         cursor = self.store.cursor()
         for name in TABLES:
            columns = map(lambda c : ' '.join(c), TABLES[name])
            cursor.execute(fmt.format(name, ', '.join(columns)))
         self.store.commit()

   """
   Loads a simple admin (user, password) configuration based on a configuration file
   with the two-line format:
      <username>
      <sha256 hash>
   Raises IOError if the file with 'filename' is improperly formatted.
   """
   def _loadAdminConf(self, filename):
      try:
         lines = map(lambda s : s.strip(), list(open(filename)))
         self.admin_name, self.admin_pass = lines
      except:
         raise IOError('Malformed admin configuration file.')
Beispiel #43
0
    def __init__(self, **kwargs):
        self.register_event_type('on_pre_start')
        super(ThreeDoListApp, self).__init__(**kwargs)

        if not self.db:
            connection = Connection('db.db')
            #connection = Connection(self.user_data_dir + '/db.db')
            cursor = connection.cursor()
            cursor.execute("""
                           PRAGMA user_version=1;

                           CREATE TABLE [table of contents](
                           page_number UNSIGNED INTEGER,
                           page TEXT PRIMARY KEY,
                           bookmark UNSIGNED SHORT INTEGER DEFAULT 0);

                           CREATE TABLE [notebook](
                           ix UNSIGNED INTEGER,
                           what TEXT DEFAULT '',
                           when_ TEXT DEFAULT '',
                           where_ TEXT DEFAULT '',
                           why UNSIGNED SHORT INTEGER DEFAULT 0,
                           how TEXT DEFAULT '',
                           page TEXT,
                           UNIQUE(page, ix),
                           FOREIGN KEY(page) REFERENCES [table of contents](page) ON DELETE CASCADE ON UPDATE CASCADE);

                           CREATE TABLE [archive](
                           ix UNSIGNED INTEGER,
                           what TEXT DEFAULT '',
                           when_ TEXT DEFAULT '',
                           where_ TEXT DEFAULT '',
                           why UNSIGNED SHORT INTEGER DEFAULT 0,
                           how TEXT DEFAULT '',
                           page TEXT,
                           FOREIGN KEY(page) REFERENCES [table of contents](page) ON DELETE CASCADE ON UPDATE CASCADE);

                           CREATE TRIGGER [on_new_page]
                           AFTER INSERT ON [table of contents]
                           BEGIN
                               INSERT INTO [notebook](page, ix) VALUES(NEW.page, 1);
                               INSERT INTO [notebook](page, ix) VALUES(NEW.page, 2);
                               INSERT INTO [notebook](page, ix) VALUES(NEW.page, 3);
                           END;

                           CREATE TRIGGER [soft_delete]
                           BEFORE DELETE ON [notebook]
                           WHEN OLD.ix<4 AND OLD.page=(SELECT page FROM [table of contents] WHERE bookmark=1)
                           BEGIN
                               UPDATE [notebook] SET what='', when_='', where_='', why=0, how='' WHERE ix=OLD.ix AND page=OLD.page;
                               SELECT RAISE(IGNORE);
                           END;

                           CREATE TRIGGER [new_action_item]
                           AFTER UPDATE ON [notebook]
                           WHEN NEW.what=''
                           BEGIN
                               DELETE FROM [notebook] WHERE ix>3 AND WHAT='' AND page=(SELECT page FROM [table of contents] WHERE bookmark=1);
                           END;

                           CREATE TRIGGER [on_complete]
                           AFTER INSERT ON archive
                           BEGIN
                               DELETE FROM [notebook] WHERE page=NEW.page AND ix=NEW.ix AND what=NEW.what;
                           END;

                           INSERT INTO [table of contents](page_number, page)
                           VALUES(1, 'Main List');

                           INSERT INTO [table of contents](page_number, page) VALUES(2, 'Sample List');
                           UPDATE [notebook] SET what='Click Me', when_='', where_='', why=0, how='Double-tap any one of us' WHERE page='Sample List' AND ix=1;
                           INSERT INTO [notebook](page, ix, what, how) VALUES('Sample List', 4, 'Swipe right to complete me', 'You can find me later in the "Archive Screen"');
                           INSERT INTO [notebook](page, ix, what, how) VALUES('Sample List', 5, 'Swipe left to delete me', 'You can also delete a list itself in the "List Screen" this way.');
                           INSERT INTO [notebook](page, ix, what, how) VALUES('Sample List', 6, 'Double-tap to rename me', 'You can also rename a list itself in the "List Screen" this way.');
                           INSERT INTO [notebook](page, ix, what, how) VALUES('Sample List', 7, 'Press and hold to Drag-N''-Drop', 'You can re-order your tasks this way.');
                           INSERT INTO [notebook](page, ix, what, how) VALUES('Sample List', 8, 'Drag me to an Action Item.', 'The Action Items are your top 3 tasks to focus on at a time.');
                           """)
            #cursor.execute("commit")
            self.db = connection
            
        self.db.cursor().execute("PRAGMA foreign_keys=ON;")