Database Interface

[ZeroKnight]

More or less a continuation/refactor of the ideas originally started in the Perl incarnation.

Task List

• [x] Implement design
• [x] Implement backup functionality
• [x] Proper restructure of existing zerobot.sqlite, e.g. proper normalization and "global" tables
• [x] Import old data into the new structure
• [x] Import backlogged module data, transforming into proper formatting as necessary, e.g. quotes

Design

ZeroBot shall make use of a monolithic zerobot.sqlite database with some core-related tables (?), global tables usable by all modules, and module-specific tables. I'm currently not sure what the core tables would hold, and may just merge this concept into the global tables.

Each module (and the Core) will have its own connection to the database. A ZeroBot.database method will be responsible for initializing these connections, and the core will deliver these connection objects to modules that request them.

Modules that make use of the database should adhere to a table naming convention such that modules with a single or primary table have a name equal to their module identifier, e.g. chat. Modules may create additional tables, but the names should always prefix these table names with their identifier, e.g. chat_triggers.

Implementation Details

ZeroBot.database.Connection shall extend sqlite3.Connection in order to associate the module that the connection is for, and facilitate synchronization of any state with Core.

Connections are requested by modules via a method like Core.database_connect, typically within their module_register callback. This method shall call ZeroBot.database.create_connection, which handles creating and setting up an sqlite3.Connection object. A dictionary within Core will map module identifiers to these resultant connection objects. Modules may close their connection manually, but the Core will ensure that this is done during shutdown and module reload.

Once a module is connected, it may execute any arbitrary queries it wants. Connection creation and closure should be logged, probably in INFO.

Database transactions should be logged, with the executing module included in the message. DQL statements should go to DEBUG, and DML/DDL should go to INFO. Can achieve this through light extension of sqlite3.Connection.execute* methods. TCL statements should be logged as well, but probably only commit and rollback warrant being sent to INFO; the rest can be sent to DEBUG.

Table Access [Deprecated]

NOTE: I've since decided against this. The enforcement offered is rather superficial, as a module could simply reset the authorizer to a dummy callback or make its own connection directly. With this in mind, writing the authorizer and the config logic around granting modules access becomes a bit of a waste of effort, as modules can realistically do whatever they want anyway; as the saying goes: "we're all consenting adults here". ZeroBot's modules are intended to be able to interact anyway. Modules can of course still use set_authorizer to implement validation, if they wish to do so.

I'm leaving these notes here anyway for reference.

Table access can be controlled via sqlite3.Connection.set_authorizer. A callback will be defined in ZeroBot.database that authorizes transactions in the following ways:

• The affected table name is checked against the executing module and will allow the transaction only if:
  • The module's identifier matches the whole table name or its prefix
  • The module is explicitly granted permission to the table in ZeroBot's config
  • The table is global
  • Otherwise, the transaction is rejected.
• A module may only create extra tables with a prefix matching its identifier, rejecting otherwise.

[ZeroKnight]

Table Restructuring, Normalizing, and Reformatting

ZeroBot's database was created at a time that I knew nothing about database design and was just going by the seat of my pants. Data needs to be properly normalized and reformatted in many tables; especially quote.

General

• [x] Rename tables to conform to new <module>_<name> scheme
• [x] QC pass on old schemas:
  • [x] Ensure proper column order (PK first)
  • [x] Proper PK definition (INTEGER PRIMARY KEY NOT NULL)
  • [x] More descriptive name than just "id" for PKs
  • [x] Use phrases as private keys where applicable and set WITHOUT ROWID
  • [x] Figure out what to do with babelbaba (see "psalms" idea in Joplin)
  • [x] Figure out what to do with magic8ball; maybe add some quirky ZeroBot phrases...

New Users Table, Aliases Table

The users table will hold user information for other tables and features to make use of in a consistent manner. Each entry should represent a single, unique user and store any useful information about them. Currently not sure what protocol-agnostic information to add for now, though. Maybe something like "last seen time", but would need to extend the Context API for this.

UPDATE: Came up with a few columns

• created_at holds the time the user was created
• creation_flags holds an integer "bitstring" representing TBD flags describing why/how the user was created, e.g. by a module, by the Core, etc.
• creation_metadata is an optional, arbitrary string associated with the creation of this user. Can for example specify the module that created the user.
• comment is an optional, arbitrary string that can contain any arbitrary information.

The aliases table is an associative table that stores alternate names for a given user in the users table. This is useful if someone changes their username (often or otherwise), or in cases where someone has a modified name for other purposes, like foobar_work. The table shall consist of only a compound PK of the form (user_id, alias), where user_id is a FK to the users table and alias is an alias for that user. A boolean case_sensitive column determines whether the alias is case-sensitive or not; defaults to false.

This table also includes the same columns as the users table.

Counter Table

• [x] Move trigger patterns, announcement, and description to config
• [x] New column: timestamp for when the counter was first created
• [x] New column: timestamp for the last time the counter was triggered
• [x] New column: last user to trigger the counter
• [x] New column: what channel the last trigger happened in

Quote Table

Preliminary Cleanup

Some light cleanup and consistency fixing to make it easier to restructure things programmatically later.

• [x] Include all authors in a multi-quote in the author field, separated by a newline
• [x] Ensure that all multi-quotes are set to style 0 and each line is prefixed by the proper author in <author> format
• [x] Separate individual lines in phrase with actual newlines instead of | or //, etc.

Quote Storage Format

• [x] Come up with a format specifier that will expand to a given author in a multi-author quote, e.g. %1 for the first, %2 for the second, etc. (or use \1 style)
  • This can be useful for automatically structuring multi-line quotes

Standard, single-author quotes will remain unchanged from the existing formatting: only the quote body is stored and the author is implicitly added when fetching a quote.

Multi-line quotes shall be stored more or less as-is in the body: newlines are preserved but collapsed and authors are inserted either literally or with ordinal placeholders (\1, \2, etc.). A new switch, --multi will be added to specifically designate a multi-author quote:

• Each distinct author appearing in the quote will be specified, either as a "list" or with repeated switches, e.g. -a/--author
• For protocols that support messages with multiple lines, newlines may be directly inserted. Otherwise, a \ or \n sequence is interpreted as a new line with leading/trailing space stripped (helpful for IRC).

Restructuring, Fixes, and Additions

• [x] Move existing unique authors and submitters into a new quote_participants table; set user_id as appropriate.
• [x] Create the quote_authors associative table and create an entry for each quote, with properly assigned multi-author quotes.
• [x] Change quote.submitter values to point to the appropriate quote_participants.participant_id; establish FK. Drop quote.author column.
• [x] If needed, add CHECK constraints to ensure that quote and quote_authors stay in sync. I think this is covered by the FK constraint in quote_authors, though.
• [x] Perform further normalization below

Further normalization of Quote data

Rename quote_authors to quote_lines or similar and add a string column (line) for a quote body; this is either the whole quote for a single-author quote or just the one line in a multi-author quote. A line_num column will be added to create a new compound key of (quote_id, line_num). This would consequently result in removing quote.body, moving this data into line.

Quote Authors & Submitters

NOTE: This idea succeeds a previous, unwritten one where I tried to use a similarly named quote_authors associative table and make all authors use a user_id; this obviously ran into glaring problems with "nonce" authors and was scrapped in favor of this.

A new quote_participants table will contain unique author/submitter names indexed by a unique participant_id and will contain an optional column pointing to a user_id if the participant represents the same person as an entry in the users table. The quote table will then relate to this table by pointing to a participant_id instead of keeping a name in the submitter column directly.

This approach should adequately solve the issue of storing authors that are also users vs. "nonce" authors, e.g. an author that is a website or outside of a chat network; it is not fully normalized, but it is a good enough compromise. Authors that are retroactively made into users only require that quote_participants.user_id be updated to point to the new user; this can be facilitated with a user_created module event that the Quote module can catch. Likewise, for aliases, an alias_created event necessitates that any existing quote author names matching the new alias require quote_participants.user_id to be updated to point to the user ID of the new alias. Of course, nonce authors simply always have a user_id of NULL; this allows us to identify actual users and nonce authors from the perspective of the quote_participants table and by extension check author aliases.

In addition, a quote_authors associative table indexed by quote_id and participant_id will be added. This table allows for associating multiple authors to one quote, and are designated an ordinal number in the author_num column. For example, if a quote with quote_id 123 contained a line by "Bob" followed by a line from "Alice", then there would be a tuple of {123, <Bob's id>, 1} and a tuple of {123, <Alice's id>, 2} added to the quote_authors table, where the last element is author_num.