1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
|
package main
import (
"fmt"
"log"
"regexp"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
type App struct {
Config *Config
DB *sqlx.DB
}
type LinkRow struct {
ID int `db:"id"`
URL string `db:"url"`
}
var imdbTitleRe = regexp.MustCompile(`/title/(tt\d+)($|/)`)
func (a *App) extractImdbIDs() error {
rows, err := a.DB.Query(`
SELECT id, url FROM links
WHERE field = 1 AND url LIKE '%.com/title%' AND host = 'www.imdb.com'
AND (param IS NULL OR param = '')
`)
if err != nil {
return fmt.Errorf("query links: %w", err)
}
defer rows.Close()
var count, updated int
tx := a.DB.MustBegin()
stmt, err := tx.Prepare(`UPDATE links SET param = ? WHERE id = ?`)
if err != nil {
tx.Rollback()
return fmt.Errorf("prepare update: %w", err)
}
defer stmt.Close()
for rows.Next() {
count++
var link LinkRow
if err := rows.Scan(&link.ID, &link.URL); err != nil {
tx.Rollback()
return fmt.Errorf("scan row: %w", err)
}
match := imdbTitleRe.FindStringSubmatch(link.URL)
if len(match) < 2 {
log.Printf("no IMDb ID found in URL: %s", link.URL)
continue
}
imdbID := match[1]
_, err := stmt.Exec(imdbID, link.ID)
if err != nil {
tx.Rollback()
return fmt.Errorf("update link %d: %w", link.ID, err)
}
updated++
}
if err := rows.Err(); err != nil {
tx.Rollback()
return fmt.Errorf("rows iteration: %w", err)
}
if err := tx.Commit(); err != nil {
return fmt.Errorf("commit: %w", err)
}
log.Printf("extractImdbIDs: scanned %d rows, updated %d", count, updated)
return nil
}
func main() {
cfg, err := LoadConfig("config.json")
if err != nil {
log.Fatalf("failed to load config: %v", err)
}
app := App{Config: cfg}
log.Printf(`Connecting to "%s" database "%s" as user "%s" on host "%s:%s" with extra options "%s".`, cfg.DBDriver, cfg.DBDBName, cfg.DBUser, cfg.DBHost, cfg.DBPort, cfg.DBOptions)
app.DB, err = sqlx.Connect(cfg.DBDriver, cfg.DBUser+":"+cfg.DBPassword+"@tcp("+cfg.DBHost+":"+cfg.DBPort+")/"+cfg.DBDBName+"?"+cfg.DBOptions)
if err != nil {
log.Fatal(err, "Cannot connect to database")
}
if err = app.DB.Ping(); err != nil {
log.Fatal(err, "No connection to database")
}
defer app.DB.Close()
if err = app.extractImdbIDs(); err != nil {
log.Fatalf("extractImdbIDs: %v", err)
}
}
|
