regexp.go 6.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270
  1. package dao
  2. import (
  3. "context"
  4. "fmt"
  5. "strings"
  6. "time"
  7. "go-common/app/service/main/antispam/util"
  8. "go-common/library/database/sql"
  9. "go-common/library/log"
  10. )
  11. const (
  12. columnsRegexp = `id, admin_id, area, name, operation, content, state, ctime, mtime`
  13. selectRegexpCountsSQL = `SELECT COUNT(1) FROM regexps %s`
  14. selectRegexpsByCondSQL = `SELECT ` + columnsRegexp + ` FROM regexps %s`
  15. selectRegexpByIDsSQL = `SELECT ` + columnsRegexp + ` FROM regexps WHERE id IN(%s)`
  16. selectRegexpByContentsSQL = `SELECT ` + columnsRegexp + ` FROM regexps WHERE content IN(%s)`
  17. selectRegexpByAreaAndContentSQL = `SELECT ` + columnsRegexp + ` FROM regexps WHERE area = %s AND content IN(%s)`
  18. insertRegexpSQL = `INSERT INTO regexps(id, admin_id, area, name, operation, content, state) VALUES(?, ?, ?, ?, ?, ?, ?)`
  19. updateRegexpSQL = `UPDATE regexps SET admin_id = ?, name = ?, content = ?, operation = ?, state = ?, mtime = ? WHERE id = ?`
  20. )
  21. const (
  22. // OperationLimit .
  23. OperationLimit int = iota
  24. // OperationPutToWhiteList .
  25. OperationPutToWhiteList
  26. // OperationRestrictLimit .
  27. OperationRestrictLimit
  28. // OperationIgnore .
  29. OperationIgnore
  30. )
  31. // RegexpDaoImpl .
  32. type RegexpDaoImpl struct{}
  33. // Regexp .
  34. type Regexp struct {
  35. ID int64 `db:"id"`
  36. Area int `db:"area"`
  37. Name string `db:"name"`
  38. AdminID int64 `db:"admin_id"`
  39. Operation int `db:"operation"`
  40. Content string `db:"content"`
  41. State int `db:"state"`
  42. CTime time.Time `db:"ctime"`
  43. MTime time.Time `db:"mtime"`
  44. }
  45. // NewRegexpDao .
  46. func NewRegexpDao() *RegexpDaoImpl {
  47. return &RegexpDaoImpl{}
  48. }
  49. // GetByCond .
  50. func (*RegexpDaoImpl) GetByCond(ctx context.Context,
  51. cond *Condition) (regexps []*Regexp, totalCounts int64, err error) {
  52. sqlConds := make([]string, 0)
  53. if cond.Area != "" {
  54. sqlConds = append(sqlConds, fmt.Sprintf("area = %s", cond.Area))
  55. }
  56. if cond.State != "" {
  57. sqlConds = append(sqlConds, fmt.Sprintf("state = %s", cond.State))
  58. }
  59. var optionSQL string
  60. if len(sqlConds) > 0 {
  61. optionSQL = fmt.Sprintf("WHERE %s", strings.Join(sqlConds, " AND "))
  62. }
  63. var limitSQL string
  64. if cond.Pagination != nil {
  65. queryCountsSQL := fmt.Sprintf(selectRegexpCountsSQL, optionSQL)
  66. totalCounts, err = GetTotalCounts(ctx, db, queryCountsSQL)
  67. if err != nil {
  68. return nil, 0, err
  69. }
  70. offset, limit := cond.OffsetLimit(totalCounts)
  71. if limit == 0 {
  72. return nil, 0, ErrResourceNotExist
  73. }
  74. limitSQL = fmt.Sprintf("LIMIT %d, %d", offset, limit)
  75. }
  76. if cond.OrderBy != "" {
  77. optionSQL = fmt.Sprintf("%s ORDER BY %s %s", optionSQL, cond.OrderBy, cond.Order)
  78. }
  79. if limitSQL != "" {
  80. optionSQL = fmt.Sprintf("%s %s", optionSQL, limitSQL)
  81. }
  82. querySQL := fmt.Sprintf(selectRegexpsByCondSQL, optionSQL)
  83. log.Info("OptionSQL(%s), GetByCondSQL(%s)", optionSQL, querySQL)
  84. regexps, err = queryRegexps(ctx, db, querySQL)
  85. if err != nil {
  86. return nil, totalCounts, err
  87. }
  88. return regexps, totalCounts, nil
  89. }
  90. // Update .
  91. func (rdi *RegexpDaoImpl) Update(ctx context.Context, r *Regexp) (*Regexp, error) {
  92. err := updateRegexp(ctx, db, r)
  93. if err != nil {
  94. return nil, err
  95. }
  96. return rdi.GetByID(ctx, r.ID)
  97. }
  98. // Insert .
  99. func (rdi *RegexpDaoImpl) Insert(ctx context.Context, r *Regexp) (*Regexp, error) {
  100. err := insertRegexp(ctx, db, r)
  101. if err != nil {
  102. return nil, err
  103. }
  104. return rdi.GetByID(ctx, r.ID)
  105. }
  106. // GetByID .
  107. func (rdi *RegexpDaoImpl) GetByID(ctx context.Context, id int64) (*Regexp, error) {
  108. rs, err := rdi.GetByIDs(ctx, []int64{id})
  109. if err != nil {
  110. return nil, err
  111. }
  112. if rs[0] == nil {
  113. return nil, ErrResourceNotExist
  114. }
  115. return rs[0], nil
  116. }
  117. // GetByIDs .
  118. func (*RegexpDaoImpl) GetByIDs(ctx context.Context, ids []int64) ([]*Regexp, error) {
  119. rs, err := queryRegexps(ctx, db, fmt.Sprintf(selectRegexpByIDsSQL, util.IntSliToSQLVarchars(ids)))
  120. if err != nil {
  121. return nil, err
  122. }
  123. res := make([]*Regexp, len(ids))
  124. for i, id := range ids {
  125. for _, r := range rs {
  126. if r.ID == id {
  127. res[i] = r
  128. }
  129. }
  130. }
  131. return res, nil
  132. }
  133. // GetByContents .
  134. func (*RegexpDaoImpl) GetByContents(ctx context.Context, contents []string) ([]*Regexp, error) {
  135. if len(contents) == 0 {
  136. log.Error("%v", ErrParams)
  137. return nil, ErrParams
  138. }
  139. rs, err := queryRegexps(ctx, db, fmt.Sprintf(selectRegexpByContentsSQL, util.StrSliToSQLVarchars(contents)))
  140. if err != nil {
  141. return nil, err
  142. }
  143. res := make([]*Regexp, len(contents))
  144. for i, c := range contents {
  145. for _, r := range rs {
  146. if strings.EqualFold(r.Content, c) {
  147. res[i] = r
  148. }
  149. }
  150. }
  151. return res, nil
  152. }
  153. // GetByAreaAndContent .
  154. func (*RegexpDaoImpl) GetByAreaAndContent(ctx context.Context, cond *Condition) (*Regexp, error) {
  155. rs, err := queryRegexps(ctx, db, fmt.Sprintf(selectRegexpByAreaAndContentSQL,
  156. cond.Area, util.StrSliToSQLVarchars(cond.Contents)))
  157. if err != nil {
  158. return nil, err
  159. }
  160. return rs[0], nil
  161. }
  162. func insertRegexp(ctx context.Context, executer Executer, r *Regexp) error {
  163. res, err := executer.Exec(ctx, insertRegexpSQL,
  164. r.ID,
  165. r.AdminID,
  166. r.Area,
  167. r.Name,
  168. r.Operation,
  169. r.Content,
  170. r.State,
  171. )
  172. if err != nil {
  173. log.Error("%v", err)
  174. return err
  175. }
  176. lastID, err := res.LastInsertId()
  177. if err != nil {
  178. log.Error("%v", err)
  179. return err
  180. }
  181. r.ID = lastID
  182. return nil
  183. }
  184. func updateRegexp(ctx context.Context, executer Executer, r *Regexp) error {
  185. _, err := executer.Exec(ctx, updateRegexpSQL,
  186. r.AdminID,
  187. r.Name,
  188. r.Content,
  189. r.Operation,
  190. r.State,
  191. time.Now(),
  192. r.ID,
  193. )
  194. if err != nil {
  195. log.Error("%v", err)
  196. return err
  197. }
  198. return nil
  199. }
  200. func queryRegexps(ctx context.Context, q Querier, rawSQL string) ([]*Regexp, error) {
  201. rows, err := q.Query(ctx, rawSQL)
  202. if err == sql.ErrNoRows {
  203. err = ErrResourceNotExist
  204. }
  205. if err != nil {
  206. log.Error("Error: %v, sql: %s", err, rawSQL)
  207. return nil, err
  208. }
  209. defer rows.Close()
  210. rs, err := mapRowToRegexps(rows)
  211. if err != nil {
  212. return nil, err
  213. }
  214. if len(rs) == 0 {
  215. log.Error("Error: %v, sql: %s", ErrResourceNotExist, rawSQL)
  216. return nil, ErrResourceNotExist
  217. }
  218. return rs, nil
  219. }
  220. func mapRowToRegexps(rows *sql.Rows) (rs []*Regexp, err error) {
  221. rs = make([]*Regexp, 0)
  222. for rows.Next() {
  223. r := Regexp{}
  224. err = rows.Scan(
  225. &r.ID,
  226. &r.AdminID,
  227. &r.Area,
  228. &r.Name,
  229. &r.Operation,
  230. &r.Content,
  231. &r.State,
  232. &r.CTime,
  233. &r.MTime,
  234. )
  235. if err != nil {
  236. log.Error("%v", err)
  237. return nil, err
  238. }
  239. rs = append(rs, &r)
  240. }
  241. if err = rows.Err(); err != nil {
  242. log.Error("%v", err)
  243. return nil, err
  244. }
  245. return rs, nil
  246. }