PySOGS SQL Manager

The PySOGS SQL Manager is a command-line tool for managing SQL filters applied on top of PySOGS. The SQL Manager provides an interactive interface to toggle active filters.

Contents:

• Installation
• Downloading updates
• Usage
• Adding filters
• Filter syntax

Installation

First, download the PySOGS SQL Manager to your SOGS server:

git clone https://codeberg.org/gravel/pysogs-sql-cli
cd pysogs-sql-cli

If your SOGS server comes from the sogs-standalone / sogs-proxied system package, make sure that the current user has access to the SOGS database at /var/lib/session-open-group-server/sogs.db file. The easiest way is to add the current user to the _loki group and enable group write access to the file:

sudo usermod -aG _loki $USER
sudo chmod g+w /var/lib/session-open-group-server/sogs.db

After downloading the PySOGS SQL Manager, you can optionally run the test script to verify access to the database:

./test.sh

Note: If you're running SOGS directly from a git repository, you need to specify the database location as the first argument (e.g. ./test.sh /home/myuser/pysogs).

To have the PySOGS SQL Manager available from anywhere using the sogsfilters command, add the bin directory your $PATH variable:

# in pysogs-sql-cli directory
echo "export PATH=\"$PWD/bin:\$PATH\"" >> ~/.bashrc
source ~/.bashrc

Downloading updates

To download the latest update of the PySOGS SQL Manager, open a terminal in the pysogs-sql-cli directory and execute the following command:

git pull

Usage

Execute the setup.sh script from the pysogs-sql-cli directory to run the PySOGS SQL Manager:

./setup.sh

Alternatively, use the aforementioned sogsfilters shorthand if available.

Note: If you're running SOGS directly from a git repository, you need to specify the database location as the first argument (e.g. ./setup.sh /home/myuser/pysogs if the database is in the current directory).

Use arrow keys to choose an action and Enter to confirm your selection:

Choose action: All filters on (1/4)

Pressing q aborts the selection.

Adding filters

Filters are SQL scripts modifying the behaviour of your SOGS database.

No filters are included with the application. You can pre-made filters at codeberg.org/gravel/pysogs-sql-triggers. You can also extract filters from scripts at github.com/slrslr/session-sogs or create your own. See Filter syntax.

To add filters to the PySOGS SQL Manager, place files with the .sqlite extension into the sql folder in pysogs-sql-cli.

Note: Only the default SQLite3 database engine is supported by default.

Filter syntax

Filters require no special syntax to be used with PySOGS SQL Manager. However, extra syntax is required for some functionality, such as disabling filters, presets, or descriptive prompts.

Disabling filters

To allow the user to disable a filter, SQL triggers enabling the filter behaviour must include the following syntax:

--- @BEGINSECTION DISABLE
DROPTRIGGERIFEXISTSmy_trigger_that_does_stuff;DROPTRIGGERIFEXISTSanother_trigger_created_here;--- @ENDSECTION DISABLE
CREATETRIGGERmy_trigger_that_does_stuffBEFOREINSERTonmessagesFOREACHROW-- etc.

Filter strictness

To apply presets based on filter strictness, specify strictness in the SQL file like so:

--- @STRICTNESS 2

The strictness value indicates how much the filter limits activity on your server. The higher the impact on users, the higher the strictness value.

The strictness value must be between 0 and 4 (inclusive). A value of 0 is usually reserved for utility filters (e.g. chat commands), a strictness of 1 denotes a minimal filter, while a strictness 4 filter may be described as "draconian".

Descriptive prompts

By default, the filename of the SQL file is used when configuring a filter:

Choose action: Configure all filters (3/4)
Enable my-cool-filter.sqlite? [Y/n]: Y

To use a more descriptive command-line prompt, the @PROMPT property can be specified in the SQL file of the filter:

--- @PROMPT Enable my cool filter that does stuff?
-- etc.

The prompt is then used when configuring the filter on the command-line:

Choose action: Configure all filters (3/4)
Enable my cool filter that does stuff? [Y/n]: Y

License

Copyright (c) 2024 gravel

This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, version 3.

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 General Public License for more details.

You should have received a copy of the GNU General Public License along with this program. If not, see https://www.gnu.org/licenses/.

Where verifying the copyright holder's identity is required, a timestamped message signed by any valid PGP keys located at https://codeberg.org/gravel/gravel#pgp-keys or https://git.disroot.org/gravel/gravel#pgp-keys may be considered a proof of identity; in case these resources are unavailable, the copyright holder may identify themself via the PGP key with the following fingerprint: a4e8 f0c0 2b6f 5589 c0bb 95a0 118f df6c 43c6 9326.