feat: add support for PgSQL

SychO9 commented 6 months ago

Part of the roadmap

Changes proposed in this pull request: Postgres was trickier than SQLite, but that was mostly due to having some code that dates back years ago when eloquent/ the laravel schema builder was less flexible.

In most cases there should be no need to add any custum handling for the database driver when building queries. But that does become a necessity when writing more complex / larger queries. Example: fulltext search and statistics.

The easiest instance extension devs will run into when wanting to support PgSQL is the loose group by.

// Allowed in mysql
->select('col', 'col2', ..)
->groupBy('col')
->orderBy(..)

// must be done this way in pgsql
->select('col', 'col2', ..)
->distinct('col')
->orderBy('col')
->orderBy(..)

// can be simplified to
->select('col', 'col2', ..)
->whenMySql(fn (Builder $query) => $query->groupBy('col'))
->whenPgSql(fn (Builder $query) => $query->distinct('col')->orderBy('col'))
->orderBy(..)

Screenshot

Necessity

[ ] Has the problem that is being solved here been clearly explained?
[ ] If applicable, have various options for solving this problem been considered?
[ ] For core PRs, does this need to be in core, or could it be in an extension?
[ ] Are we willing to maintain this for years / potentially forever?

Confirmed

[ ] Frontend changes: tested on a local Flarum installation.
[ ] Backend changes: tests are green (run composer test).
[ ] Core developer confirmed locally this works as intended.
[ ] Tests have been added, or are not appropriate here.

Required changes:

[ ] Related documentation PR: (Remove if irrelevant)
[ ] Related core extension PRs: (Remove if irrelevant)

SychO9 commented 5 months ago

Added support for defining supported databases

SychO9 commented 5 months ago

Awesome work! Should we show a warning in the extension UI and/or when enabling extensions if they don't specify compatibility with the current db driver?

SychO9 commented 5 months ago

@askvortsov1

https://github.com/flarum/framework/pull/3985/commits/ff96395941ea36e40672ff484bd79f8b8bf12a06

flarum / framework

feat: add support for PgSQL #3985