feat(storage): primary key for all tables and general qol refactoring (#2431)

This is a massive overhaul to the SQL Storage for Authelia. It facilitates a whole heap of utility commands to help manage the database, primary keys, ensures all database requests use a context for cancellations, and paves the way for a few other PR's which improve the database.

Fixes #1337

1 files changed, 53 insertions, 0 deletions

diff --git a/internal/storage/sql_provider_backend_mysql.go b/internal/storage/sql_provider_backend_mysql.go
new file mode 100644
index 000000000..dfb7ec179
--- /dev/null
+++ b/internal/storage/sql_provider_backend_mysql.go
@@ -0,0 +1,53 @@
+package storage
+
+import (
+	"fmt"
+	"time"
+
+	_ "github.com/go-sql-driver/mysql" // Load the MySQL Driver used in the connection string.
+
+	"github.com/authelia/authelia/v4/internal/configuration/schema"
+)
+
+// MySQLProvider is a MySQL provider.
+type MySQLProvider struct {
+	SQLProvider
+}
+
+// NewMySQLProvider a MySQL provider.
+func NewMySQLProvider(config schema.MySQLStorageConfiguration) (provider *MySQLProvider) {
+	provider = &MySQLProvider{
+		SQLProvider: NewSQLProvider(providerMySQL, providerMySQL, dataSourceNameMySQL(config)),
+	}
+
+	// All providers have differing SELECT existing table statements.
+	provider.sqlSelectExistingTables = queryMySQLSelectExistingTables
+
+	// Specific alterations to this provider.
+	provider.sqlFmtRenameTable = queryFmtMySQLRenameTable
+
+	return provider
+}
+
+func dataSourceNameMySQL(config schema.MySQLStorageConfiguration) (dataSourceName string) {
+	dataSourceName = fmt.Sprintf("%s:%s", config.Username, config.Password)
+
+	if dataSourceName != "" {
+		dataSourceName += "@"
+	}
+
+	address := config.Host
+	if config.Port > 0 {
+		address += fmt.Sprintf(":%d", config.Port)
+	}
+
+	dataSourceName += fmt.Sprintf("tcp(%s)", address)
+	if config.Database != "" {
+		dataSourceName += fmt.Sprintf("/%s", config.Database)
+	}
+
+	dataSourceName += "?"
+	dataSourceName += fmt.Sprintf("timeout=%ds&multiStatements=true&parseTime=true", int32(config.Timeout/time.Second))
+
+	return dataSourceName
+}