[FEATURE] Support mysql for the main DB (#961)

Signed-off-by: Augustin Husson <husson.augustin@gmail.com>
perses · Feb 23, 2023 · 11c7c90 · 11c7c90
1 parent d4d0fdd
commit 11c7c90
Showing 17 changed files with 774 additions and 22 deletions.
diff --git a/.github/workflows/go.yml b/.github/workflows/go.yml
@@ -52,7 +52,7 @@ jobs:
       - name: check go.mod
         run: make checkunused
   test:
-    name: "unit and integration tests"
+    name: "tests with file DB"
     runs-on: ubuntu-latest
     steps:
       - name: checkout
@@ -62,6 +62,27 @@ jobs:
           enable_go: true
       - name: test
         run: make integration-test
+  test-mysql:
+    name: "tests with mysql"
+    runs-on: ubuntu-latest
+    services:
+      mariadb:
+        image: mariadb
+        ports:
+          - '3306:3306'
+        env:
+          MARIADB_ROOT_PASSWORD: root
+          MARIADB_DATABASE: perses
+          MARIADB_USER: user
+          MARIADB_PASSWORD: password
+    steps:
+      - name: checkout
+        uses: actions/checkout@v3
+      - uses: ./.github/actions/setup_environment
+        with:
+          enable_go: true
+      - name: test
+        run: make mysql-integration-test
   golangci:
     name: lint
     runs-on: ubuntu-latest

diff --git a/Makefile b/Makefile
@@ -108,6 +108,10 @@ test: generate
 integration-test: generate
 	$(GO) test -tags=integration -v -count=1 -cover -coverprofile=$(COVER_PROFILE) -coverpkg=./... ./...
 
+.PHONY: mysql-integration-test
+mysql-integration-test: generate
+	PERSES_TEST_USE_SQL=true $(GO) test -tags=integration -v -count=1 -cover -coverprofile=$(COVER_PROFILE) -coverpkg=./... ./...
+
 .PHONY: coverage-html
 coverage-html: integration-test
 	@echo ">> Print test coverage"
@@ -159,7 +163,7 @@ generate-changelog:
 .PHONY: clean
 clean:
 	rm -rf ./bin
-	rm EXTRACTED_CHANGELOG.md
+	rm -rf EXTRACTED_CHANGELOG.md
 	./scripts/ui_release.sh --clean
 	cd ./ui && npm run clean
 

diff --git a/cmd/perses/main.go b/cmd/perses/main.go
@@ -54,6 +54,15 @@ func main() {
 	if err != nil {
 		logrus.WithError(err).Fatal("unable to instantiate the persistence manager")
 	}
+	persesDAO := persistenceManager.GetPersesDAO()
+	if dbInitError := persesDAO.Init(); dbInitError != nil {
+		logrus.WithError(dbInitError).Fatal("unable to initialize the database")
+	}
+	defer func() {
+		if daoCloseErr := persesDAO.Close(); daoCloseErr != nil {
+			logrus.WithError(daoCloseErr).Error("unable to close the connection to the database")
+		}
+	}()
 	serviceManager, err := dependency.NewServiceManager(persistenceManager, conf)
 	if err != nil {
 		logrus.WithError(err).Fatal("unable to instantiate the service manager")

diff --git a/dev/config-mariadb.yaml b/dev/config-mariadb.yaml
@@ -0,0 +1,17 @@
+database:
+  sql:
+    user: "user"
+    password: "password"
+    db_name: "perses"
+    net: "tcp"
+    addr: "localhost:3306"
+    allow_native_passwords: true
+
+schemas:
+  panels_path: "schemas/panels"
+  queries_path: "schemas/queries"
+  datasources_path: "schemas/datasources"
+  variables_path: "schemas/variables"
+  interval: "5m"
+
+readonly: false
diff --git a/dev/docker-compose.yaml b/dev/docker-compose.yaml
@@ -0,0 +1,12 @@
+version: '3.1'
+
+services:
+  mariadb:
+    image: mariadb
+    ports:
+      - '3306:3306'
+    environment:
+      MARIADB_ROOT_PASSWORD: root
+      MARIADB_DATABASE: perses
+      MARIADB_USER: user
+      MARIADB_PASSWORD: password
diff --git a/go.mod b/go.mod
@@ -6,6 +6,8 @@ require (
 	cuelang.org/go v0.4.2
 	github.com/fsnotify/fsnotify v1.6.0
 	github.com/gavv/httpexpect/v2 v2.12.0
+	github.com/go-sql-driver/mysql v1.7.0
+	github.com/huandu/go-sqlbuilder v1.19.0
 	github.com/json-iterator/go v1.1.12
 	github.com/labstack/echo/v4 v4.10.0
 	github.com/olekukonko/tablewriter v0.0.5
@@ -36,6 +38,7 @@ require (
 	github.com/google/go-querystring v1.1.0 // indirect
 	github.com/google/uuid v1.3.0 // indirect
 	github.com/gorilla/websocket v1.5.0 // indirect
+	github.com/huandu/xstrings v1.3.2 // indirect
 	github.com/imkira/go-interpol v1.1.0 // indirect
 	github.com/inconshreveable/mousetrap v1.0.1 // indirect
 	github.com/klauspost/compress v1.15.2 // indirect

diff --git a/go.sum b/go.sum
@@ -60,6 +60,8 @@ github.com/go-logr/logr v1.2.3 h1:2DntVwHkVopvECVRSlL5PSo9eG+cAkDCuckLubN+rq0=
 github.com/go-logr/logr v1.2.3/go.mod h1:jdQByPbusPIv2/zmleS9BjJVeZ6kBagPoEUsqbVz/1A=
 github.com/go-logr/stdr v1.2.2 h1:hSWxHoqTgW2S2qGc0LTAI563KZ5YKYRhT3MFKZMbjag=
 github.com/go-logr/stdr v1.2.2/go.mod h1:mMo/vtBO5dYbehREoey6XUKy/eSumjCCveDpRre4VKE=
+github.com/go-sql-driver/mysql v1.7.0 h1:ueSltNNllEqE3qcWBTD0iQd3IpL/6U+mJxLkazJ7YPc=
+github.com/go-sql-driver/mysql v1.7.0/go.mod h1:OXbVy3sEdcQ2Doequ6Z5BW6fXNQTmx+9S1MCJN5yJMI=
 github.com/go-stack/stack v1.8.0/go.mod h1:v0f6uXyyMGvRgIKkXu+yp6POWl0qKG85gN/melR3HDY=
 github.com/gogo/protobuf v1.1.1/go.mod h1:r8qH/GZQm5c6nD/R0oafs1akxWv10x8SbQlK7atdtwQ=
 github.com/gogo/protobuf v1.2.1/go.mod h1:hp+jE20tsWTFYpLwKvXlhS1hjn+gTNwPg2I6zVXpSg4=
@@ -101,6 +103,12 @@ github.com/grpc-ecosystem/grpc-gateway v1.9.0/go.mod h1:vNeuVxBJEsws4ogUvrchl83t
 github.com/hashicorp/hcl v1.0.0/go.mod h1:E5yfLk+7swimpb2L/Alb/PJmXilQ/rhwaUYs4T20WEQ=
 github.com/hpcloud/tail v1.0.0 h1:nfCOvKYfkgYP8hkirhJocXT2+zOD8yUNjXaWfTlyFKI=
 github.com/hpcloud/tail v1.0.0/go.mod h1:ab1qPbhIpdTxEkNHXyeSf5vhxWSCs/tWer42PpOxQnU=
+github.com/huandu/go-assert v1.1.5 h1:fjemmA7sSfYHJD7CUqs9qTwwfdNAx7/j2/ZlHXzNB3c=
+github.com/huandu/go-assert v1.1.5/go.mod h1:yOLvuqZwmcHIC5rIzrBhT7D3Q9c3GFnd0JrPVhn/06U=
+github.com/huandu/go-sqlbuilder v1.19.0 h1:X1JyJI9cjfj/jVAxblh2MZbYsGtikbEgAu6sUU1nLJQ=
+github.com/huandu/go-sqlbuilder v1.19.0/go.mod h1:nUVmMitjOmn/zacMLXT0d3Yd3RHoO2K+vy906JzqxMI=
+github.com/huandu/xstrings v1.3.2 h1:L18LIDzqlW6xN2rEkpdV8+oL/IXWJ1APd+vsdYy4Wdw=
+github.com/huandu/xstrings v1.3.2/go.mod h1:y5/lhBue+AyNmUVz9RLU9xbLR0o4KIIExikq4ovT0aE=
 github.com/imkira/go-interpol v1.1.0 h1:KIiKr0VSG2CUW1hl1jpiyuzuJeKUUpC8iM1AIE7N1Vk=
 github.com/imkira/go-interpol v1.1.0/go.mod h1:z0h2/2T3XF8kyEPpRgJ3kmNv+C43p+I/CoI+jC3w2iA=
 github.com/inconshreveable/mousetrap v1.0.0/go.mod h1:PxqpIevigyE2G7u3NXJIT2ANytuPF1OarO4DADm73n8=

diff --git a/internal/api/config/database.go b/internal/api/config/database.go
@@ -15,6 +15,7 @@ package config
 
 import (
 	"fmt"
+	"time"
 )
 
 type FileExtension string
@@ -39,6 +40,75 @@ func (f *File) Verify() error {
 	return nil
 }
 
+type SQL struct {
+	// Username
+	User string `json:"user,omitempty" yaml:"user,omitempty"`
+	// Password (requires User)
+	Password string `json:"password,omitempty" yaml:"password,omitempty"`
+	// Network type
+	Net string `json:"net,omitempty" yaml:"net,omitempty"`
+	// Network address (requires Net)
+	Addr string `json:"addr,omitempty" yaml:"addr,omitempty"`
+	// Database name
+	DBName string `json:"db_name" yaml:"db_name"`
+	// Connection collation
+	Collation string `json:"collation,omitempty" yaml:"collation,omitempty"`
+	// Location for time.Time values
+	Loc *time.Location `json:"loc,omitempty" yaml:"loc,omitempty"`
+	// Max packet size allowed
+	MaxAllowedPacket int `json:"max_allowed_packet" yaml:"maxAllowedPacket"`
+	// Server public key name
+	ServerPubKey string `json:"server_pub_key" yaml:"server_pub_key"`
+	// Dial timeout
+	Timeout time.Duration `json:"timeout" yaml:"timeout"`
+	// I/O read timeout
+	ReadTimeout time.Duration `json:"read_timeout" yaml:"read_timeout"`
+	// I/O write timeout
+	WriteTimeout time.Duration `json:"write_timeout" yaml:"write_timeout"`
+	// Allow all files to be used with LOAD DATA LOCAL INFILE
+	AllowAllFiles bool `json:"allow_all_files" yaml:"allow_all_files"`
+	// Allows the cleartext client side plugin
+	AllowCleartextPasswords bool `json:"allow_cleartext_passwords" yaml:"allow_cleartext_passwords"`
+	// Allows fallback to unencrypted connection if server does not support TLS
+	AllowFallbackToPlaintext bool `json:"allow_fallback_to_plaintext" yaml:"allow_fallback_to_plaintext"`
+	// Allows the native password authentication method
+	AllowNativePasswords bool `json:"allow_native_passwords" yaml:"allow_native_passwords"`
+	// Allows the old insecure password method
+	AllowOldPasswords bool `json:"allow_old_passwords" yaml:"allow_old_passwords"`
+	// Check connections for liveness before using them
+	CheckConnLiveness bool `json:"check_conn_liveness" yaml:"check_conn_liveness"`
+	// Return number of matching rows instead of rows changed
+	ClientFoundRows bool `json:"client_found_rows" yaml:"client_found_rows"`
+	// Prepend table alias to column names
+	ColumnsWithAlias bool `json:"columns_with_alias" yaml:"columns_with_alias"`
+	// Interpolate placeholders into query string
+	InterpolateParams bool `json:"interpolate_params" yaml:"interpolate_params"`
+	// Allow multiple statements in one query
+	MultiStatements bool `json:"multi_statements" yaml:"multi_statements"`
+	// Parse time values to time.Time
+	ParseTime bool `json:"parse_time" yaml:"parse_time"`
+	// Reject read-only connections
+	RejectReadOnly bool `json:"reject_read_only" yaml:"reject_read_only"`
+}
+
+func (s *SQL) Verify() error {
+	if len(s.DBName) == 0 {
+		return fmt.Errorf("db_name must be specified")
+	}
+	return nil
+}
+
 type Database struct {
-	File *File `json:"file" yaml:"file"`
+	File *File `json:"file,omitempty" yaml:"file,omitempty"`
+	SQL  *SQL  `json:"sql,omitempty" yaml:"sql,omitempty"`
+}
+
+func (d *Database) Verify() error {
+	if d.File == nil && d.SQL == nil {
+		return fmt.Errorf("you must specify if Perses has to use SQL or filesystem as a database")
+	}
+	if d.File != nil && d.SQL != nil {
+		return fmt.Errorf("you cannot tel to Perses to use SQL and the filesystem at the same time")
+	}
+	return nil
 }
diff --git a/internal/api/e2e/framework/server.go b/internal/api/e2e/framework/server.go
@@ -17,6 +17,7 @@ package e2eframework
 
 import (
 	"net/http/httptest"
+	"os"
 	"os/exec"
 	"path/filepath"
 	"strings"
@@ -33,6 +34,8 @@ import (
 	modelV1 "github.com/perses/perses/pkg/model/api/v1"
 )
 
+var useSQL = os.Getenv("PERSES_TEST_USE_SQL")
+
 func GetRepositoryPath(t *testing.T) string {
 	projectPathByte, err := exec.Command("git", "rev-parse", "--show-toplevel").Output()
 	if err != nil {
@@ -61,9 +64,6 @@ func CreateServer(t *testing.T) (*httptest.Server, *httpexpect.Expect, dependenc
 	projectPath := GetRepositoryPath(t)
 	handler := echo.New()
 	conf := config.Config{
-		Database: config.Database{
-			File: defaultFileConfig(),
-		},
 		Schemas: config.Schemas{
 			PanelsPath:      filepath.Join(projectPath, config.DefaultPanelsPath),
 			QueriesPath:     filepath.Join(projectPath, config.DefaultQueriesPath),
@@ -72,10 +72,29 @@ func CreateServer(t *testing.T) (*httptest.Server, *httpexpect.Expect, dependenc
 			Interval:        0,
 		},
 	}
+	if useSQL == "true" {
+		conf.Database = config.Database{
+			SQL: &config.SQL{
+				User:                 "user",
+				Password:             "password",
+				Net:                  "tcp",
+				Addr:                 "localhost:3306",
+				DBName:               "perses",
+				AllowNativePasswords: true,
+			},
+		}
+	} else {
+		conf.Database = config.Database{
+			File: defaultFileConfig(),
+		}
+	}
 	persistenceManager, err := dependency.NewPersistenceManager(conf.Database)
 	if err != nil {
 		t.Fatal(err)
 	}
+	if dbInitError := persistenceManager.GetPersesDAO().Init(); dbInitError != nil {
+		t.Fatal(dbInitError)
+	}
 	serviceManager, err := dependency.NewServiceManager(persistenceManager, conf)
 	if err != nil {
 		t.Fatal(err)
@@ -92,6 +111,7 @@ func CreateServer(t *testing.T) (*httptest.Server, *httpexpect.Expect, dependenc
 
 func WithServer(t *testing.T, testFunc func(*httpexpect.Expect, dependency.PersistenceManager) []modelAPI.Entity) {
 	server, expect, persistenceManager := CreateServer(t)
+	defer persistenceManager.GetPersesDAO().Close()
 	defer server.Close()
 	entities := testFunc(expect, persistenceManager)
 	ClearAllKeys(t, persistenceManager.GetPersesDAO(), entities...)

diff --git a/internal/api/shared/database/database.go b/internal/api/shared/database/database.go
@@ -14,11 +14,14 @@
 package database
 
 import (
+	"database/sql"
 	"fmt"
 
+	"github.com/go-sql-driver/mysql"
 	"github.com/perses/perses/internal/api/config"
 	databaseFile "github.com/perses/perses/internal/api/shared/database/file"
 	databaseModel "github.com/perses/perses/internal/api/shared/database/model"
+	databaseSQL "github.com/perses/perses/internal/api/shared/database/sql"
 )
 
 func New(conf config.Database) (databaseModel.DAO, error) {
@@ -27,6 +30,43 @@ func New(conf config.Database) (databaseModel.DAO, error) {
 			Folder:    conf.File.Folder,
 			Extension: conf.File.Extension,
 		}, nil
+	} else if conf.SQL != nil {
+		c := conf.SQL
+		mysqlConfig := mysql.Config{
+			User:                     c.User,
+			Passwd:                   c.Password,
+			Net:                      c.Net,
+			Addr:                     c.Addr,
+			DBName:                   c.DBName,
+			Collation:                c.Collation,
+			Loc:                      c.Loc,
+			MaxAllowedPacket:         c.MaxAllowedPacket,
+			ServerPubKey:             c.ServerPubKey,
+			Timeout:                  c.Timeout,
+			ReadTimeout:              c.ReadTimeout,
+			WriteTimeout:             c.WriteTimeout,
+			AllowAllFiles:            c.AllowAllFiles,
+			AllowCleartextPasswords:  c.AllowCleartextPasswords,
+			AllowFallbackToPlaintext: c.AllowFallbackToPlaintext,
+			AllowNativePasswords:     c.AllowNativePasswords,
+			AllowOldPasswords:        c.AllowOldPasswords,
+			CheckConnLiveness:        c.CheckConnLiveness,
+			ClientFoundRows:          c.ClientFoundRows,
+			ColumnsWithAlias:         c.ColumnsWithAlias,
+			InterpolateParams:        c.InterpolateParams,
+			MultiStatements:          c.MultiStatements,
+			ParseTime:                c.ParseTime,
+			RejectReadOnly:           c.RejectReadOnly,
+		}
+
+		db, err := sql.Open("mysql", mysqlConfig.FormatDSN())
+		if err != nil {
+			return nil, err
+		}
+		return &databaseSQL.DAO{
+			DB:         db,
+			SchemaName: c.DBName,
+		}, nil
 	}
 	return nil, fmt.Errorf("no dao defined")
 }