This update focuses on the database installation and upgrade path for Mediabot v3.
The goal is simple: make fresh installs and existing database upgrades cleaner, safer, and easier to reproduce.
This is not a new IRC command. It is infrastructure work, but it matters a lot for anyone who wants to install, test, migrate, or maintain Mediabot without guessing what SQL file must be imported manually.
This milestone improves the database tooling around Mediabot:
install/db_install.sh
install/db_migrate.sh
install/migrations/20260502_user_seen.sql
The main improvements are:
explicit utf8mb4 handling
SQL imports through mysql SOURCE
no shell redirection for schema imports
no cat file.sql | mysql pattern
less password exposure in install scripts
a dedicated migration helper
a USER_SEEN migration for existing databases
Mediabot now has features that rely on database tables added over time, such as:
USER_SEEN
CHANNEL_BAN
A fresh install can use:
install/mediabot.sql
but an existing database needs incremental migrations.
Without a clear migration workflow, an administrator may update the code, start the bot, and then discover that a table is missing only when a command fails.
That is exactly the kind of installation friction this update tries to remove.
A dedicated migration was added for the !seen feature:
install/migrations/20260502_user_seen.sql
It creates the USER_SEEN table if it does not already exist.
This table stores the last known activity for a nick:
message
join
part
quit
nick change
The table uses utf8mb4_unicode_ci and stores one row per normalized nick.
This is useful for existing Mediabot installations that were created before the USER_SEEN table was added to the main schema.
The install and migration workflow now favors the MySQL/MariaDB client with:
--default-character-set=utf8mb4
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci
SET CHARACTER SET utf8mb4
SOURCE /path/to/file.sql
This avoids ambiguous shell redirection such as:
mysql database < file.sql
or:
cat file.sql | mysql database
Using SOURCE inside the mysql client makes charset handling explicit and easier to document.
The installer was improved so the main schema is loaded from the script directory using an absolute path:
install/mediabot.sql
The schema import now uses SOURCE with explicit utf8mb4 settings.
The installer also creates the database with:
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci
This makes the initial database setup more consistent with the rest of the project.
The installer was also hardened to avoid exposing MySQL passwords directly in process arguments.
Instead of building command lines like:
mysql -u user -ppassword
the script now uses temporary mysql option files with restricted permissions.
This reduces the risk of credentials appearing in process listings during installation.
The final output also avoids printing the generated database password in clear text.
A new helper script was added:
install/db_migrate.sh
It applies a single migration file using the same policy:
mysql client
SOURCE
utf8mb4
explicit migration path
Example usage:
./install/db_migrate.sh mediabotv3 install/migrations/20260502_user_seen.sql root
The helper validates that the migration file lives under:
install/migrations/
and refuses suspicious paths.
For a fresh install:
install/mediabot.sql
is the main schema source.
For an existing install:
install/migrations/*.sql
contains incremental migrations.
Current migrations include:
install/migrations/20260502_channel_ban.sql
install/migrations/20260502_user_seen.sql
This gives Mediabot a clearer upgrade story.
The scripts were syntax-checked with:
bash -n install/db_install.sh
bash -n install/db_migrate.sh
The migration file was written to be idempotent through:
CREATE TABLE IF NOT EXISTS
The existing test suite was also used around the related !seen and hotfix work.
Main files touched by this milestone:
install/db_install.sh
install/db_migrate.sh
install/migrations/20260502_user_seen.sql
Related existing files:
install/mediabot.sql
install/migrations/20260502_channel_ban.sql
🧱 Foundation Charm: use utf8mb4 SOURCE imports and add USER_SEEN migration
This is a clean installation and migration improvement.
It makes Mediabot easier to deploy, easier to upgrade, and less likely to surprise administrators with missing tables or charset-related SQL import issues.
It is one more step toward making Mediabot v3 feel like a serious long-running IRC bot project, not just a collection of scripts.
You must be logged in to reply.