From fc62d3456ad9d06bbf445e50941e2a6847e13406 Mon Sep 17 00:00:00 2001
From: qydysky <qydysky@foxmail.com>
Date: Wed, 26 Apr 2023 03:33:58 +0800
Subject: [PATCH] Add mysql

---
 README.md                   | 43 +++++++++++++++++++++--
 Reply/F.go                  | 68 +++++++++++++++++++++++--------------
 Reply/Reply.go              |  4 +--
 demo/config/config_K_v.json |  8 ++++-
 go.mod                      |  3 +-
 go.sum                      |  6 ++++
 6 files changed, 100 insertions(+), 32 deletions(-)

diff --git a/README.md b/README.md
index 86e3fc5..656aaf5 100644
--- a/README.md
+++ b/README.md
@@ -73,8 +73,47 @@
 ### 说明
 本项目使用github action自动构建，构建过程详见[yml](https://github.com/qydysky/bili_danmu/blob/master/.github/workflows/go.yml)
 
-#### 保存弹幕至sqlite
-配置文件中`保存弹幕至sqlite`，如果设置了文件路径，如`a.sqlite3`，将会在工作目录生成`a.sqlite3`，并记录弹幕。
+#### 保存弹幕至db
+配置文件中添加配置项`保存弹幕至db`。参考以下实例：
+
+mysql:
+```json
+{
+  "保存弹幕至db": {
+      "dbname": "mysql",
+      "url":"root:root@(192.168.31.103:10836)/test",
+      "字段help":"time.Now().Format(time.DateTime), time.Now().Unix(), item.msg, item.color, item.auth, item.uid, item.roomid",
+      "create":"create table danmu (created varchar(20), createdunix varchar(20), msg varchar(100), color varchar(20), auth varchar(50), uid varchar(30), roomid varchar(30))",
+      "insert":"insert into danmu (created, createdunix, msg, color, auth, uid, roomid) values (?,?,?,?,?,?,?)"
+  }
+}
+```
+
+sqlite3:
+
+注意：对不要并发连接，可能会导致失败
+```json
+{
+  "保存弹幕至db": {
+      "dbname": "sqlite",
+      "url":"danmu.sqlite3",
+      "字段help":"time.Now().Format(time.DateTime), time.Now().Unix(), item.msg, item.color, item.auth, item.uid, item.roomid",
+      "create":"create table danmu (created text, createdunix text, msg text, color text, auth text, uid text, roomid text)",
+      "insert":"insert into danmu  values (?,?,?,?,?,?,?)"
+  }
+}
+```
+
+实例：
+
+|created|createdunix|msg|color|auth|uid|roomid|
+|-------|-----------|---|-----|----|---|------|
+|2023-04-26 03:20:33|1682450433|可能走位配合了他的压枪|#e33fff|畏未|96767379|92613|
+|2023-04-26 03:20:45|1682450445|=。=|#54eed8|青江知暖|282800070|92613|
+|2023-04-26 03:20:52|1682450452|这不是铁挂吗|#ffffff|瑶少溪|43048863|92613|
+|2023-04-26 03:20:55|1682450455|开个箱？|#58c1de|修阡宇|433678545|92613|
+|2023-04-26 03:20:59|1682450459|来了来了|#00fffc|我肚子是吃饱了撑大的|830140|92613|
+
 
 #### 指定房间录制回调
 配置文件添加了如下配置
diff --git a/Reply/F.go b/Reply/F.go
index eb2696e..d75f3c0 100644
--- a/Reply/F.go
+++ b/Reply/F.go
@@ -20,6 +20,7 @@ import (
 
 	"database/sql"
 
+	_ "github.com/go-sql-driver/mysql"
 	psql "github.com/qydysky/part/sqlite"
 	"golang.org/x/text/encoding/simplifiedchinese"
 	_ "modernc.org/sqlite"
@@ -1548,45 +1549,60 @@ func (t *DanmuReLiveTriger) Check(uid, msg string) {
 	}
 }
 
-// 保存弹幕至sqlite
-var saveDanmuToSqlite3 SaveDanmuToSqlite3
+// 保存弹幕至mysql
+var saveDanmuToMysql SaveDanmuToMysql
 
-type SaveDanmuToSqlite3 struct {
-	db *sql.DB
+type SaveDanmuToMysql struct {
+	db     *sql.DB
+	insert string
 	sync.Once
 }
 
-func (t *SaveDanmuToSqlite3) init(c *c.Common) {
+func (t *SaveDanmuToMysql) init(c *c.Common) {
 	t.Do(func() {
-		if v, ok := c.K_v.LoadV(`保存弹幕至sqlite`).(string); ok && v != "" {
-			if db, e := sql.Open("sqlite", v); e != nil {
-				panic(e)
-			} else {
-				db.SetMaxOpenConns(1)
-				t.db = db
+		if v, ok := c.K_v.LoadV(`保存弹幕至db`).(map[string]any); ok && len(v) != 0 {
+			var (
+				dbname, url, create                 string
+				dbnameok, urlok, createok, insertok bool
+			)
+
+			dbname, dbnameok = v["dbname"].(string)
+			url, urlok = v["url"].(string)
+			create, createok = v["create"].(string)
+			t.insert, insertok = v["insert"].(string)
+
+			if !dbnameok || !urlok || !createok || !insertok {
+				return
 			}
 
-			ctx := context.Background()
-			tx := psql.BeginTx[any](t.db, ctx, &sql.TxOptions{})
-			tx = tx.Do(psql.SqlFunc[any]{
-				Ty:         psql.Execf,
-				Ctx:        ctx,
-				Query:      "create table danmu (created text, createdunix text, msg text, color text, auth text, uid text, roomid text)",
-				SkipSqlErr: true,
-			})
-			if e := tx.Fin(); e != nil {
-				panic(e)
+			if db, e := sql.Open(dbname, url); e != nil {
+				c.Log.Base_add("保存弹幕至db").L(`E: `, e)
+			} else {
+				db.SetConnMaxLifetime(time.Minute * 3)
+				db.SetMaxOpenConns(10)
+				db.SetMaxIdleConns(10)
+				if e := psql.BeginTx[any](db, context.Background(), &sql.TxOptions{}).Do(psql.SqlFunc[any]{
+					Ty:         psql.Execf,
+					Query:      create,
+					SkipSqlErr: true,
+				}).Fin(); e != nil {
+					c.Log.Base_add("保存弹幕至db").L(`E: `, e)
+				} else {
+					t.db = db
+				}
 			}
 		}
 	})
 }
 
-func (t *SaveDanmuToSqlite3) danmu(item Danmu_item) {
-	if t.db != nil {
+func (t *SaveDanmuToMysql) danmu(item Danmu_item) {
+	if t.db == nil {
+		return
+	}
+	if e := t.db.Ping(); e == nil {
 		if e := psql.BeginTx[any](t.db, context.Background(), &sql.TxOptions{}).Do(psql.SqlFunc[any]{
 			Ty:    psql.Execf,
-			Ctx:   context.Background(),
-			Query: "insert into danmu values (?, ?, ?, ?, ?, ?, ?)",
+			Query: t.insert,
 			Args:  []any{time.Now().Format(time.DateTime), time.Now().Unix(), item.msg, item.color, item.auth, item.uid, item.roomid},
 			AfterEF: func(_ *any, result sql.Result, txE error) (_ *any, stopErr error) {
 				if v, e := result.RowsAffected(); e != nil {
@@ -1597,7 +1613,7 @@ func (t *SaveDanmuToSqlite3) danmu(item Danmu_item) {
 				return nil, nil
 			},
 		}).Fin(); e != nil {
-			c.C.Log.Base_add("保存弹幕至sqlite").L(`E: `, e)
+			c.C.Log.Base_add("保存弹幕至db").L(`E: `, e)
 		}
 	}
 }
diff --git a/Reply/Reply.go b/Reply/Reply.go
index 514665d..3bfc39d 100644
--- a/Reply/Reply.go
+++ b/Reply/Reply.go
@@ -1093,8 +1093,8 @@ func (replyF) danmu(s string) {
 
 	{ // 附加功能 弹幕机 封禁 弹幕合并
 		// 保存弹幕至sqlite
-		saveDanmuToSqlite3.init(c.C)
-		saveDanmuToSqlite3.danmu(item)
+		saveDanmuToMysql.init(c.C)
+		saveDanmuToMysql.danmu(item)
 		// 对指定弹幕重新录制
 		danmuReLiveTriger.Init(c.C)
 		danmuReLiveTriger.Check(item.uid, item.msg)
diff --git a/demo/config/config_K_v.json b/demo/config/config_K_v.json
index 205f09d..ebef581 100644
--- a/demo/config/config_K_v.json
+++ b/demo/config/config_K_v.json
@@ -40,7 +40,13 @@
     "弹幕_识别表情代码": true,
     "发送还有几天过期的礼物": 3,
     "保持牌子亮着": true,
-    "保存弹幕至sqlite": "",
+    "保存弹幕至db": {
+        "dbname": "",
+        "url":"",
+        "字段help":"time.Now().Format(time.DateTime), time.Now().Unix(), item.msg, item.color, item.auth, item.uid, item.roomid",
+        "create":"",
+        "insert":""
+    },
     "日志文件输出-help": "为空时不保存日志",
     "日志文件输出": "danmu.log",
     "日志显示": [
diff --git a/go.mod b/go.mod
index a931c11..3b8ffe0 100644
--- a/go.mod
+++ b/go.mod
@@ -5,7 +5,7 @@ go 1.20
 require (
 	github.com/gotk3/gotk3 v0.6.1
 	github.com/mdp/qrterminal/v3 v3.0.0
-	github.com/qydysky/part v0.24.19
+	github.com/qydysky/part v0.24.21
 	github.com/skip2/go-qrcode v0.0.0-20200617195104-da1b6568686e
 	github.com/skratchdot/open-golang v0.0.0-20200116055534-eef842397966
 	golang.org/x/text v0.9.0
@@ -36,6 +36,7 @@ require (
 	github.com/davecgh/go-spew v1.1.1 // indirect
 	github.com/dustin/go-humanize v1.0.1
 	github.com/go-ole/go-ole v1.2.6 // indirect
+	github.com/go-sql-driver/mysql v1.7.1
 	github.com/gorilla/websocket v1.5.0 // indirect
 	github.com/klauspost/compress v1.16.5 // indirect
 	github.com/miekg/dns v1.1.53 // indirect
diff --git a/go.sum b/go.sum
index d9b3095..8fca728 100644
--- a/go.sum
+++ b/go.sum
@@ -6,6 +6,8 @@ github.com/dustin/go-humanize v1.0.1 h1:GzkhY7T5VNhEkwH0PVJgjz+fX1rhBrR7pRT3mDkp
 github.com/dustin/go-humanize v1.0.1/go.mod h1:Mu1zIs6XwVuF/gI1OepvI0qD18qycQx+mFykh5fBlto=
 github.com/go-ole/go-ole v1.2.6 h1:/Fpf6oFPoeFik9ty7siob0G6Ke8QvQEuVcuChpwXzpY=
 github.com/go-ole/go-ole v1.2.6/go.mod h1:pprOEPIfldk/42T2oK7lQ4v4JSDwmV0As9GaiUsvbm0=
+github.com/go-sql-driver/mysql v1.7.1 h1:lUIinVbN1DY0xBg0eMOzmmtGoHwWBbvnWubQUrtU8EI=
+github.com/go-sql-driver/mysql v1.7.1/go.mod h1:OXbVy3sEdcQ2Doequ6Z5BW6fXNQTmx+9S1MCJN5yJMI=
 github.com/google/go-cmp v0.5.9 h1:O2Tfq5qg4qc4AmwVlvv0oLiVAGB7enBSJ2x2DqQFi38=
 github.com/google/pprof v0.0.0-20221118152302-e6195bd50e26 h1:Xim43kblpZXfIBQsbuBVKCudVG457BR2GZFIz3uw3hQ=
 github.com/google/uuid v1.3.0 h1:t6JiXgmwXMjEs8VusXIJk2BXHsn+wx8BZdTaoZ5fu7I=
@@ -33,6 +35,10 @@ github.com/qydysky/part v0.24.18 h1:TDW9rknY7Y8CtsAcS6A7eWXbojQQWYjZxMnCkmQSoyo=
 github.com/qydysky/part v0.24.18/go.mod h1:vZgRwVF/3s1R07lJO3lbnyfFaY/3xeLkkaeFCPpzjlI=
 github.com/qydysky/part v0.24.19 h1:wsXqz4M5f5P5wtZlfUzgUWq4Qv49am7sgEhFPNolVuI=
 github.com/qydysky/part v0.24.19/go.mod h1:88d8rYyw4cHZSs8u5Q2MkqOnIdKDXpDSwfiSlwo3Tx4=
+github.com/qydysky/part v0.24.20 h1:nvxJQO9S+WB1CBz+LZ+T6NXDQLxs8/YIK7e7SIewyMg=
+github.com/qydysky/part v0.24.20/go.mod h1:8Rn6RC+170YRrtgssAumk8/r68+YH3F45J4l6F6+Fl4=
+github.com/qydysky/part v0.24.21 h1:BNkZzF7Kqd7kZm9BXuc60QiEm5ETUv5D91ZAW5/Orno=
+github.com/qydysky/part v0.24.21/go.mod h1:8Rn6RC+170YRrtgssAumk8/r68+YH3F45J4l6F6+Fl4=
 github.com/remyoudompheng/bigfft v0.0.0-20200410134404-eec4a21b6bb0/go.mod h1:qqbHyh8v60DhA7CoWK5oRCqLrMHRGoxYCSS9EjAz6Eo=
 github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec h1:W09IVJc94icq4NjY3clb7Lk8O1qJ8BdBEF8z0ibU0rE=
 github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec/go.mod h1:qqbHyh8v60DhA7CoWK5oRCqLrMHRGoxYCSS9EjAz6Eo=
-- 
2.39.2