wangjia
86b67c557b
- db.Dialect:LockForUpdate(mysql "FOR UPDATE" / sqlite "")、Upsert(中性 EXCLUDED.col → mysql VALUES()/ sqlite excluded.);DialectForDB 从连接驱动推导 - 9 处 ON DUPLICATE KEY、11 处 FOR UPDATE 全走 dialect;sqlite 靠 _txlock= immediate 取得 BEGIN IMMEDIATE 悲观锁等价语义 - directory_version 三处重复合并为 store.BumpDirectoryVersion(dialect 感知) - 这些文件同时含(2/4)的 UTC→Go 改动(与 upsert/锁同语句交错,无法拆分) - 顺带:usage 的 FIELD()、codes 的 DATE_ADD/GREATEST 续期、nodes 的 UNIX_TIMESTAMP、NULLIF 等 MySQL 专属构造一并退回 Go/可移植写法 Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
97 lines
3.2 KiB
Go
97 lines
3.2 KiB
Go
package db
|
|
|
|
import (
|
|
"database/sql"
|
|
"fmt"
|
|
"regexp"
|
|
"strings"
|
|
)
|
|
|
|
// Dialect captures the small set of SQL constructs that differ between MySQL
|
|
// and SQLite. The ~95% of the server's queries are plain CRUD with "?"
|
|
// placeholders and need no dialect handling; only upserts and row locks do.
|
|
type Dialect interface {
|
|
// Name reports "mysql" or "sqlite".
|
|
Name() string
|
|
|
|
// LockForUpdate returns the pessimistic row-lock clause to append to a
|
|
// SELECT inside a transaction:
|
|
// mysql → "FOR UPDATE"
|
|
// sqlite → "" (single-writer engine; the BEGIN IMMEDIATE transaction —
|
|
// enabled via _txlock=immediate in the DSN — already serializes
|
|
// writers, giving equivalent exclusivity).
|
|
LockForUpdate() string
|
|
|
|
// Upsert builds the conflict-resolution tail for an INSERT. conflictCols is
|
|
// the unique/primary key that triggers the conflict. setExprs are assignment
|
|
// expressions; refer to the would-be-inserted value with the sentinel
|
|
// "EXCLUDED.<col>" (rewritten to VALUES(col) on MySQL, kept as excluded.col
|
|
// on SQLite). With no setExprs the conflict is a no-op (insert-or-ignore).
|
|
//
|
|
// Example (atomic accumulate):
|
|
// d.Upsert([]string{"user_id","date"},
|
|
// "bytes_up = bytes_up + EXCLUDED.bytes_up")
|
|
Upsert(conflictCols []string, setExprs ...string) string
|
|
}
|
|
|
|
// DialectForDB derives the Dialect from the driver backing an open *sql.DB, so
|
|
// the dialect always matches the actual connection (no global state, and store
|
|
// constructors keep their (db *sql.DB) signatures).
|
|
func DialectForDB(database *sql.DB) Dialect {
|
|
if database == nil {
|
|
return MySQLDialect{}
|
|
}
|
|
name := strings.ToLower(fmt.Sprintf("%T", database.Driver()))
|
|
if strings.Contains(name, "sqlite") {
|
|
return SQLiteDialect{}
|
|
}
|
|
return MySQLDialect{}
|
|
}
|
|
|
|
// DialectFor returns the Dialect for a driver name ("mysql"|"sqlite").
|
|
func DialectFor(driver string) Dialect {
|
|
if Normalize(driver) == "sqlite" {
|
|
return SQLiteDialect{}
|
|
}
|
|
return MySQLDialect{}
|
|
}
|
|
|
|
var excludedRef = regexp.MustCompile(`EXCLUDED\.(\w+)`)
|
|
|
|
// MySQLDialect implements Dialect for MySQL.
|
|
type MySQLDialect struct{}
|
|
|
|
func (MySQLDialect) Name() string { return "mysql" }
|
|
func (MySQLDialect) LockForUpdate() string { return "FOR UPDATE" }
|
|
|
|
func (MySQLDialect) Upsert(conflictCols []string, setExprs ...string) string {
|
|
if len(setExprs) == 0 {
|
|
// No-op upsert (insert-or-ignore): assign a key column to itself.
|
|
col := "id"
|
|
if len(conflictCols) > 0 {
|
|
col = conflictCols[0]
|
|
}
|
|
return fmt.Sprintf("ON DUPLICATE KEY UPDATE %s = %s", col, col)
|
|
}
|
|
rewritten := make([]string, len(setExprs))
|
|
for i, e := range setExprs {
|
|
rewritten[i] = excludedRef.ReplaceAllString(e, "VALUES($1)")
|
|
}
|
|
return "ON DUPLICATE KEY UPDATE " + strings.Join(rewritten, ", ")
|
|
}
|
|
|
|
// SQLiteDialect implements Dialect for SQLite.
|
|
type SQLiteDialect struct{}
|
|
|
|
func (SQLiteDialect) Name() string { return "sqlite" }
|
|
func (SQLiteDialect) LockForUpdate() string { return "" }
|
|
|
|
func (SQLiteDialect) Upsert(conflictCols []string, setExprs ...string) string {
|
|
target := "ON CONFLICT(" + strings.Join(conflictCols, ", ") + ") "
|
|
if len(setExprs) == 0 {
|
|
return target + "DO NOTHING"
|
|
}
|
|
// SQLite accepts the EXCLUDED.<col> reference verbatim.
|
|
return target + "DO UPDATE SET " + strings.Join(setExprs, ", ")
|
|
}
|