Arke: New storage system for Red, relational databases and ORM!

Hey, I'm writing this issue after a conversation in #advanced-coding where I initially suggested the following change.

Config is great, allows anyone to store stuff easily within Red, but it has a lot of limitations too. After playing a lot with django recently, I kept thinking about integrating an ORM in Red, for cog developers, as an additional tool, which I will refer to as "Arke"

## What will Arke be?

Arke will be like Config : providing tools to cog creators for storing data without worrying about creating files or setting up a database. However, the stored data can be relational, which means no more dicts and hierarchy, only models and relations!

The models will be defined using an ORM, and Red will handle the rest (registering the models, preventing conflicts, handling the database based on the user's choice).

### What will Arke not be?

It is NOT a replacement for Config, it is an **optional addition**. Anyone should be free of using Config or Arke.

## Relational models? What is that?

Unlike Config, the storing schema will not be dictionaries, but relational models. You have to describe what a model should look like, and what it should be linked to.

Dictionaries are limited to two relations: parent and children, while relational models have no such limit, and do not need to worry about hierarchy. Take the following example :

```py
class ModLog:
id: int # this will be the primary key
# basic fields
reason: str
time: datetime.datetime
type: Literal["warn", "mute", "kick", "ban"]

# relational fields
guild: discord.Guild
member: discord.User
moderator: discord.User
```

With a model like that, you can easily query and filter all modlogs for a guild, a member, or a moderator. From that modlog object, you can access the other linked attributes back.

```py
>>> all_modlogs = ModLog.objects.all()
>>> guild_modlogs = ModLog.objects.filter(guild=red)
>>> all_reasons = ModLog.objects.column(reason)

>>> my_modlogs = ModLog.objects.filter(member__id=348415857728159745)
>>> first_modlog = my_modlogs[0]
>>> first_modlog.moderator

```
*(This is absolutely not valid Python code, just showing examples)*

All of the fields described above are columns, which means you can for example query all `reason` objects of modlogs, without querying the other stuff. That kind of operations make everything not only more optimised for both time and storage space, it also makes programming a lot more convenient.

## What is an ORM?

ORM means **Object relational mapping**.

Databases like that need SQL operations, and it's annoying to deal with, but thanks to ORMs, you can simply build an object in a language, and it will be translated to SQL queries.

There are multiple ORMs for Python, the two most popular ones are [SQLAlchemy](https://www.sqlalchemy.org/) and [Django ORM](https://www.djangoproject.com/).

I personally find the latter easier to use, but there's no standalone version, plus SQLAlchemy has some great features (support for more dbs, async operations, advanced SQL support...), so I guess we'll use that.

Let me show some examples from [their docs](https://docs.sqlalchemy.org/en/14/intro.html):

### Creating models

```py
class User(Base):
__tablename__ = 'user_account'

id = Column(Integer, primary_key=True)
name = Column(String(30))
fullname = Column(String)

addresses = relationship("Address", back_populates="user")

def __repr__(self):
return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"

class Address(Base):
__tablename__ = 'address'

id = Column(Integer, primary_key=True)
email_address = Column(String, nullable=False)
user_id = Column(Integer, ForeignKey('user_account.id'))

user = relationship("User", back_populates="addresses")

def __repr__(self):
return f"Address(id={self.id!r}, email_address={self.email_address!r})"
```

### Manipulating data

```py
# Taking the first user
>>> session.scalars(select(User)).first()
User(id=1, name='spongebob', fullname='Spongebob Squarepants')

# Taking only some keys from the first user
>>> session.execute(select(User.name, User.fullname)).first()
('spongebob', 'Spongebob Squarepants')

# Picking a specific object
>>> session.execute(select(User).where(User.name == 'spongebob'))
User(id=1, name='spongebob', fullname='Spongebob Squarepants')

# Progressively fetch users ordered by ID
>>> result = session.execute(select(User).order_by(User.id))
>>> result.fetchone()
(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)
>>> result.scalars().all()
[User(id=2, name='sandy', fullname='Sandy Cheeks'),
User(id=3, name='patrick', fullname='Patrick Star'),
User(id=4, name='squidward', fullname='Squidward Tentacles'),
User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')]
```

Of course this needs more explanations, this is just a quick overview of what you can do. I will write detailed and comprehensive docs for this module.

## What will this look like in Red?

Just showing some draft ideas, nothing is done yet, I'm giving stuff I have in mind and I'm very open to suggestions.

### Developer side

I need to play more with SQLAlchemy before being sure of how this is going to work, but ideally, I want something that will be very similar to config :

- When loading the cog, you initialise an Arke object, which will create a unique database for your cog, so you're free to do all the tables you want.
- The Arke object will be used for running the queries. That, or do it the django way and only rely on the object type (which means typevars for us)
- The cog's metadata should tell if it is using Arke or not, and eventually some recommendations on the database to use (for example, postgresql supports fuzzy search, and SQLAlchemy supports db-specific operations to allow this, or fall back to the default).

### User side

There are multiple ways of doing this, and I want it to be as seamless as possible. Still unsure on what to do so please comment on this 👀

Here's something I had in mind:

- When setting up a new instance, the user can add the `--with-arke` flag, which will add another step to the process: asking what db to use for Arke-compatible cogs. The default option should be SQLite3.
- If an instance is already setup, the Arke setup applied on top of the existing instance with `--setup-arke`?
- When the user is installing a cog that uses Arke while it is not setup, there should be a message explaining that Arke needs to be initialised on this instance, using the step above.

That, or simply setup Arke on all instances by default, but for something optional, not sure if it's a great thing.

## A quick FAQ

### Why "Arke"

Had to come up with a name other than BetterConfig :kappa:

Searched for a nice name, and found Arke from the Greek mythology. During the Titanomachy, she was a messenger from the Titans, sending messages to the Olympian gods. You can read more about her [here](https://en.wikipedia.org/wiki/Arke).

Since the goal of the application is to provide an interface to cog developers, and then handle saving the data on our side, I think that's a cool and fitting name :D

### Why should this be part of core Red?

Cogs have already been using databases other than Config like leveler, without any tool like that from Red. However, learning how to configure a database, connect to it and send queries is hard. Even harder for the users who have a hard time setting up that database themselves (see Fixator's channel 👀).

On the other hand, Config is great: support for different drivers, easy interface for all cog devs, and easy to use for the end user. I want to bring that to relational databases too.

If this module is independent from Red, it's not going to change a thing: each cog will have its own database system, without any coordination. In addition, new devs will simply use the existing system, and not bother adding a whole layer to use a specific database.

I never wanted myself to learn SQL, JSON is way easier to use. However, the way django works with its ORM made everything so much easier, made me love relational database schemas, and now I'm having a hard time using JSON. I think if the tool is there, ready to use, more developers will want to try it.

### Wouldn't that make the setup process harder for end users?

Sure, setting up a database can be hard: installing additional programs, setting up hosts and passwords, making sure the connection stays alive... except for SQLite!

Config has complex driver options which requires that kind of setup too, but there's always one easy and straightforward option: JSON. Well in our case, that easy and straightforward option is SQLite3, it's just a text file, doesn't require any server! Sure you will be losing performance, but for small sized bots, that will be more than enough. Large bots have already switched to Redis or other drivers than JSON.

### What are the other advantages of using a db

Omitting the ORM, tables and relational stuff here.

- It's blazing fast, and storage efficient
- Migrations! No need to hand write your JSON anymore, they can be automatically builded for everyone!

----

I think I explained everything I had in mind. As you can see, it's just ideas in my head, but they're pretty clear. I will start working on some prototypes very soon, and want to hear your feedback!

----

Update (less than 30 min after yes): Of course we're not limited to SQLAlchemy as an ORM! Just talked to Preda which mentioned piccolo ORM, and that looks a lot better! This issue is for sorting the main points, including this one