The AI uses a SQLite 3 database with the Python sqlite3 module. The database is saved in ai.db right next to ai.py.

General Design

Over all we design our database lean. Only add tables and columns if we need them. This will keep the structure minimal and easier to maintain.

We use UUIDs saved as varchars as technical primary keys but sometimes also use Discord object IDs or drone IDs, depending on what we represent. Foreign key relations should always be maintained as constraints in the respective tables.

Migration

As the requirements for this project are changing and it can maybe never be considered 'done', we assume that the schema of our database will be changing over the course of time.

To ensure, that we can change the schema and every developer and the server can get up to date as easily as possible, we decided to build a simple versioning system for the schema.

Migration files

Changes to the schema are written in sql files in (res/db/migrate) and have each a prefixed number. These numbers indicate the order in which they are to be executed. Thus you should only create new files with higher version numbers then any other file.

Every schema change has to be written in its own file after it has been merged into the develop branch. Of course you can do whatever you are programmed to do on your own branches.

You can use pretty much everything possible in SQLite 3 in these scripts.