diff options
| -rw-r--r-- | src/go.mod | 7 | ||||
| -rw-r--r-- | src/go.sum | 10 | ||||
| -rw-r--r-- | src/main.go | 85 |
3 files changed, 87 insertions, 15 deletions
@@ -1,3 +1,10 @@ module hnimdbbot go 1.26.4 + +require ( + github.com/go-sql-driver/mysql v1.8.1 + github.com/jmoiron/sqlx v1.4.0 +) + +require filippo.io/edwards25519 v1.1.0 // indirect diff --git a/src/go.sum b/src/go.sum new file mode 100644 index 0000000..f4ce337 --- /dev/null +++ b/src/go.sum @@ -0,0 +1,10 @@ +filippo.io/edwards25519 v1.1.0 h1:FNf4tywRC1HmFuKW5xopWpigGjJKiJSV0Cqo0cJWDaA= +filippo.io/edwards25519 v1.1.0/go.mod h1:BxyFTGdWcka3PhytdK4V28tE5sGfRvvvRV7EaN4VDT4= +github.com/go-sql-driver/mysql v1.8.1 h1:LedoTUt/eveggdHS9qUFC1EFSa8bU2+1pZjSRpvNJ1Y= +github.com/go-sql-driver/mysql v1.8.1/go.mod h1:wEBSXgmK//2ZFJyE+qWnIsVGmvmEKlqwuVSjsCm7DZg= +github.com/jmoiron/sqlx v1.4.0 h1:1PLqN7S1UYp5t4SrVVnt4nUVNemrDAtxlulVe+Qgm3o= +github.com/jmoiron/sqlx v1.4.0/go.mod h1:ZrZ7UsYB/weZdl2Bxg6jCRO9c3YHl8r3ahlKmRT4JLY= +github.com/lib/pq v1.10.9 h1:YXG7RB+JIjhP29X+OtkiDnYaXQwpS4JEWq7dtCCRUEw= +github.com/lib/pq v1.10.9/go.mod h1:AlVN5x4E4T544tWzH6hKfbfQvm3HdbOxrmggDNAPY9o= +github.com/mattn/go-sqlite3 v1.14.22 h1:2gZY6PC6kBnID23Tichd1K+Z0oS6nE/XwU+Vz/5o4kU= +github.com/mattn/go-sqlite3 v1.14.22/go.mod h1:Uh1q+B4BYcTPb+yiD3kU8Ct7aC0hY9fxUwlHK0RXw+Y= diff --git a/src/main.go b/src/main.go index ebfbd98..e079760 100644 --- a/src/main.go +++ b/src/main.go @@ -1,27 +1,79 @@ package main import ( - "encoding/json" "fmt" - "html" - "io/ioutil" - "net/http" - "net/url" + "log" "regexp" - "strconv" - "strings" - "time" - "github.com/PuerkitoBio/goquery" - "github.com/anikhasibul/queue" + _ "github.com/go-sql-driver/mysql" "github.com/jmoiron/sqlx" - log "github.com/sirupsen/logrus" ) type App struct { Config *Config DB *sqlx.DB - Now time.Time +} + +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() { @@ -31,11 +83,10 @@ func main() { } app := App{Config: cfg} - app.Now = time.Now() - log.Debug(fmt.Sprintf(`Connecting to "%s" database "%s" as user "%s" on host "%s:%s" with extra options "%s".`, app.Config.DBDriver, app.Config.DBDBName, app.Config.DBUser, app.Config.DBHost, app.Config.DBPort, app.Config.DBOptions)) + 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(app.Config.DBDriver, app.Config.DBUser+":"+app.Config.DBPassword+"@tcp("+app.Config.DBHost+":"+app.Config.DBPort+")/"+app.Config.DBDBName+"?"+app.Config.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") } @@ -44,4 +95,8 @@ func main() { log.Fatal(err, "No connection to database") } defer app.DB.Close() + + if err = app.extractImdbIDs(); err != nil { + log.Fatalf("extractImdbIDs: %v", err) + } } |