func (d *Dal) deleteMovie(msg *pubsub.Message) {
movie := msg.Payload.(*model.Movie)
// d.count = 0
lib.Notify(d.bus, "prune:delete", fmt.Sprintf("STARTED DELETING [%d] %s", movie.Id, movie.Title))
tx, err := d.db.Begin()
if err != nil {
mlog.Fatalf("at begin: %s", err)
}
stmt, err := tx.Prepare("delete from movie where rowid = ?")
if err != nil {
tx.Rollback()
mlog.Fatalf("at prepare: %s", err)
}
defer stmt.Close()
_, err = stmt.Exec(movie.Id)
if err != nil {
tx.Rollback()
mlog.Fatalf("at exec: %s", err)
}
tx.Commit()
lib.Notify(d.bus, "prune:delete", fmt.Sprintf("FINISHED DELETING [%d] %s", movie.Id, movie.Title))
}
func (d *Dal) setScore(msg *pubsub.Message) {
movie := msg.Payload.(*model.Movie)
mlog.Info("STARTED UPDATING MOVIE SCORE [%d] %s (%d)", movie.Id, movie.Title, movie.Score)
tx, err := d.db.Begin()
if err != nil {
mlog.Fatalf("at begin: %s", err)
}
stmt, err := tx.Prepare(`update movie set
score = ?,
modified = ?
where rowid = ?`)
if err != nil {
tx.Rollback()
mlog.Fatalf("at prepare: %s", err)
}
defer stmt.Close()
now := time.Now().UTC().Format(time.RFC3339)
_, err = stmt.Exec(movie.Score, now, movie.Id)
if err != nil {
tx.Rollback()
mlog.Fatalf("at exec: %s", err)
}
tx.Commit()
mlog.Info("FINISHED UPDATING MOVIE SCORE [%d] %s", movie.Id, movie.Title)
msg.Reply <- movie
}
func (d *Dal) checkExists(msg *pubsub.Message) {
tx, err := d.db.Begin()
if err != nil {
mlog.Fatalf("at begin: %s", err)
}
stmt, err := tx.Prepare("select rowid from movie where location = ?")
if err != nil {
tx.Rollback()
mlog.Fatalf("at prepare: %s", err)
}
defer stmt.Close()
movie := msg.Payload.(*model.Movie)
var id int
err = stmt.QueryRow(movie.Location).Scan(&id)
// if err == sql.ErrNoRows {
// mlog.Fatalf("id = %d, err = %d", id, err)
// }
// mlog.Fatalf("gone and done")
if err != sql.ErrNoRows && err != nil {
tx.Rollback()
mlog.Fatalf("at queryrow: %s", err)
}
tx.Commit()
// mlog.Info("Check exists: [%d] (%s)", id, movie.Location)
msg.Reply <- (id != 0)
}
func (d *Dal) listMovies(options *lib.Options) (total uint64, movies []*model.Movie) {
// mlog.Info("listMovies.options: %+v", options)
tx, err := d.db.Begin()
if err != nil {
mlog.Fatalf("listMovies:Unable to begin transaction: %s", err)
}
sql := fmt.Sprintf(`select rowid, title, original_title, file_title, year, runtime, tmdb_id, imdb_id,
overview, tagline, resolution, filetype, location, cover, backdrop, genres, vote_average,
vote_count, countries, added, modified, last_watched, all_watched, count_watched, score,
director, writer, actors, awards, imdb_rating, imdb_votes
from movie order by %s %s, rowid desc limit ? offset ?`, options.SortBy, options.SortOrder)
stmt, err := tx.Prepare(sql)
if err != nil {
mlog.Fatalf("Unable to prepare transaction: %s", err)
}
defer stmt.Close()
rows, err := stmt.Query(options.Limit, options.Offset)
if err != nil {
mlog.Fatalf("Unable to prepare transaction: %s", err)
}
items := make([]*model.Movie, 0)
// if options.Offset == 0 {
// if d.count == 0 {
var count uint64
err = d.countRows.QueryRow().Scan(&count)
if err != nil {
mlog.Fatalf("Unable to count rows: %s", err)
}
// }
for rows.Next() {
movie := model.Movie{}
rows.Scan(
&movie.Id, &movie.Title, &movie.Original_Title, &movie.File_Title, &movie.Year,
&movie.Runtime, &movie.Tmdb_Id, &movie.Imdb_Id, &movie.Overview, &movie.Tagline,
&movie.Resolution, &movie.FileType, &movie.Location, &movie.Cover, &movie.Backdrop,
&movie.Genres, &movie.Vote_Average, &movie.Vote_Count, &movie.Production_Countries,
&movie.Added, &movie.Modified, &movie.Last_Watched, &movie.All_Watched,
&movie.Count_Watched, &movie.Score, &movie.Director, &movie.Writer, &movie.Actors,
&movie.Awards, &movie.Imdb_Rating, &movie.Imdb_Votes,
)
items = append(items, &movie)
}
rows.Close()
tx.Commit()
mlog.Info("Listed %d movies (total %d)", len(items), count)
return count, items
}
func (d *Dal) updateMovie(msg *pubsub.Message) {
defer d.bus.Pub(nil, "/event/workunit/done")
movie := msg.Payload.(*model.Movie)
mlog.Info("STARTED UPDATING [%d] %s", movie.Id, movie.Title)
tx, err := d.db.Begin()
if err != nil {
mlog.Fatalf("at begin: %s", err)
}
stmt, err := tx.Prepare(`update movie set title = ?,
original_title = ?,
year = ?,
runtime = ?,
tmdb_id = ?,
imdb_id = ?,
overview = ?,
tagline = ?,
cover = ?,
backdrop = ?,
genres = ?,
vote_average = ?,
vote_count = ?,
countries = ?,
modified = ?,
director = ?,
writer = ?,
actors = ?,
awards = ?,
imdb_rating = ?,
imdb_votes = ?
where rowid = ?`)
if err != nil {
tx.Rollback()
mlog.Fatalf("at prepare: %s", err)
}
defer stmt.Close()
_, err = stmt.Exec(movie.Title, movie.Original_Title, movie.Year, movie.Runtime, movie.Tmdb_Id, movie.Imdb_Id, movie.Overview, movie.Tagline, movie.Cover, movie.Backdrop, movie.Genres, movie.Vote_Average, movie.Vote_Count, movie.Production_Countries, movie.Modified, movie.Director, movie.Writer, movie.Actors, movie.Awards, movie.Imdb_Rating, movie.Imdb_Votes, movie.Id)
if err != nil {
tx.Rollback()
mlog.Fatalf("at exec: %s", err)
}
tx.Commit()
mlog.Info("FINISHED UPDATING [%d] %s", movie.Id, movie.Title)
// updated := &pubsub.Message{}
// d.bus.Pub(updated, "/event/movie/updated")
}
func (d *Dal) prepare(sql string) *sql.Stmt {
stmt, err := d.db.Prepare(sql)
if err != nil {
mlog.Fatalf("prepare sql: %s (%s)", err, sql)
}
return stmt
}
func (d *Dal) Start() {
mlog.Info("Starting service Dal ...")
dbPath := filepath.Join(d.settings.DataDir, "mediagui.db")
var err error
d.db, err = sql.Open("sqlite3", dbPath)
if err != nil {
mlog.Fatalf("Unable to open database (%s): %s", dbPath, err)
}
d.mailbox = d.register(d.bus, "/get/movies/cover", d.getCover)
d.registerAdditional(d.bus, "/get/movies", d.getMovies, d.mailbox)
d.registerAdditional(d.bus, "/get/movies/duplicates", d.getDuplicates, d.mailbox)
d.registerAdditional(d.bus, "/command/movie/exists", d.checkExists, d.mailbox)
d.registerAdditional(d.bus, "/command/movie/store", d.storeMovie, d.mailbox)
d.registerAdditional(d.bus, "/command/movie/partialstore", d.partialStoreMovie, d.mailbox)
d.registerAdditional(d.bus, "/command/movie/update", d.updateMovie, d.mailbox)
d.registerAdditional(d.bus, "/command/movie/delete", d.deleteMovie, d.mailbox)
d.registerAdditional(d.bus, "/put/movies/score", d.setScore, d.mailbox)
d.registerAdditional(d.bus, "/put/movies/watched", d.setWatched, d.mailbox)
d.countRows = d.prepare("select count(*) from movie;")
mlog.Info("Connected to database %s", dbPath)
go d.react()
}
func (d *Dal) partialStoreMovie(msg *pubsub.Message) {
defer d.bus.Pub(nil, "/event/workunit/done")
movie := msg.Payload.(*model.Movie)
// d.count = 0
now := time.Now().UTC().Format(time.RFC3339)
movie.Added = now
movie.Modified = now
mlog.Info("STARTED PARTIAL SAVING %s", movie.Title)
tx, err := d.db.Begin()
if err != nil {
mlog.Fatalf("at begin: %s", err)
}
stmt, err := tx.Prepare(`insert into movie(title, original_title, file_title,
year, runtime, tmdb_id, imdb_id, overview, tagline,
resolution, filetype, location, cover, backdrop, genres,
vote_average, vote_count, countries, added, modified,
last_watched, all_watched, count_watched, score, director,
writer, actors, awards, imdb_rating, imdb_votes)
values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`)
if err != nil {
tx.Rollback()
mlog.Fatalf("at prepare: %s", err)
}
defer stmt.Close()
_, err = stmt.Exec(movie.Title, movie.Original_Title, movie.File_Title, movie.Year,
movie.Runtime, movie.Tmdb_Id, movie.Imdb_Id, movie.Overview, movie.Tagline,
movie.Resolution, movie.FileType, movie.Location, movie.Cover, movie.Backdrop,
movie.Genres, movie.Vote_Average, movie.Vote_Count, movie.Production_Countries,
movie.Added, movie.Modified, movie.Last_Watched, movie.All_Watched, movie.Count_Watched,
movie.Score, movie.Director, movie.Writer, movie.Actors, movie.Awards, movie.Imdb_Rating,
movie.Imdb_Votes)
if err != nil {
tx.Rollback()
mlog.Fatalf("at exec: %s", err)
}
tx.Commit()
mlog.Info("FINISHED PARTIAL SAVING %s [%d]", movie.Title, movie.Id)
}
func (d *Dal) getDuplicates(msg *pubsub.Message) {
mlog.Info("getDuplicates.starting")
tx, err := d.db.Begin()
if err != nil {
mlog.Fatalf("Unable to begin transaction: %s", err)
}
// rows, err := self.listMovies.Query()
// if err != nil {
// mlog.Fatalf("unable to prepare transaction: %s", err)
// }
// rows, err := self.db.Query("select rowid, title, original_title, file_title, year, runtime, tmdb_id, imdb_id, overview, tagline, resolution, filetype, location, cover, backdrop, genres, vote_average, vote_count, countries, added, modified, last_watched, all_watched, count_watched from movie where title in (select title from movie group by title having count(*) > 1);")
rows, err := d.db.Query(`select a.rowid, a.title, a.original_title, a.file_title,
a.year, a.runtime, a.tmdb_id, a.imdb_id, a.overview, a.tagline, a.resolution,
a.filetype, a.location, a.cover, a.backdrop, a.genres, a.vote_average,
a.vote_count, a.countries, a.added, a.modified, a.last_watched, a.all_watched,
a.count_watched, a.score, a.director, a.writer, a.actors, a.awards, a.imdb_rating,
a.imdb_votes
from
movie a
join
(select title, year from movie group by title, year having count(*) > 1) b
on a.title = b.title and a.year = b.year;`)
if err != nil {
mlog.Fatalf("Unable to prepare transaction: %s", err)
}
items := make([]*model.Movie, 0)
for rows.Next() {
movie := model.Movie{}
rows.Scan(&movie.Id, &movie.Title, &movie.Original_Title, &movie.File_Title, &movie.Year, &movie.Runtime, &movie.Tmdb_Id, &movie.Imdb_Id, &movie.Overview, &movie.Tagline, &movie.Resolution, &movie.FileType, &movie.Location, &movie.Cover, &movie.Backdrop, &movie.Genres, &movie.Vote_Average, &movie.Vote_Count, &movie.Production_Countries, &movie.Added, &movie.Modified, &movie.Last_Watched, &movie.All_Watched, &movie.Count_Watched, &movie.Score, &movie.Director, &movie.Writer, &movie.Actors, &movie.Awards, &movie.Imdb_Rating, &movie.Imdb_Votes)
items = append(items, &movie)
}
rows.Close()
tx.Commit()
mlog.Info("Found %d duplicate movies", len(items))
msg.Reply <- &model.MoviesDTO{Total: uint64(len(items)), Items: items}
}
func (s *Scraper) Start() {
mlog.Info("Starting service Scraper ...")
var err error
s.tmdb, err = tmdb.NewClient("e610ded10c3f47d05fe797961d90fea6", false)
if err != nil {
mlog.Fatalf("Unable to create tmdb client: %s", err)
}
s.mailbox = s.register(s.bus, "/command/movie/scrape", s.scrapeMovie)
s.registerAdditional(s.bus, "/command/movie/rescrape", s.reScrapeMovie, s.mailbox)
s.registerAdditional(s.bus, "/event/config/changed", s.configChanged, s.mailbox)
s.pool = lib.NewPool(12, 4000)
go s.react()
}
func (d *Dal) setWatched(msg *pubsub.Message) {
dto := msg.Payload.(*model.Movie)
mlog.Info("STARTED UPDATING MOVIE WATCHED DATE [%d] %s (%s)", dto.Id, dto.Title, dto.Last_Watched)
now := time.Now().UTC().Format(time.RFC3339)
stmt, err := d.db.Prepare("select all_watched from movie where rowid = ?")
if err != nil {
mlog.Fatalf("at prepare: %s", err)
}
// get all watched times from the db
var when string
err = stmt.QueryRow(dto.Id).Scan(&when)
if err != nil {
mlog.Fatalf("at queryrow: %s", err)
}
// create an array with all watched times
var watched_times []string
if when != "" {
watched_times = strings.Split(when, "|")
}
// convert incoming watched time to sane format
watched, err := parseToday(dto.Last_Watched)
if err != nil {
mlog.Fatalf("at parseToday: %s", err)
}
last_watched := watched.UTC().Format(time.RFC3339)
// add last watched to array, only if it doesn't already exist
if !strings.Contains(when, last_watched) {
watched_times = append(watched_times, last_watched)
}
// this sorts the dates in ascending order by default
sort.Strings(watched_times)
// set final variables
last_watched = watched_times[len(watched_times)-1]
count_watched := uint64(len(watched_times))
all_watched := strings.Join(watched_times, "|")
// var all_watched string
// count_watched := dto.Count_Watched
// if !strings.Contains(dto.All_Watched, dto.Last_Watched) {
// count_watched++
// if dto.All_Watched == "" {
// all_watched = dto.Last_Watched
// } else {
// all_watched += "|" + dto.Last_Watched
// }
// }
tx, err := d.db.Begin()
if err != nil {
mlog.Fatalf("at begin: %s", err)
}
stmt, err = tx.Prepare(`update movie set
last_watched = ?,
all_watched = ?,
count_watched = ?,
modified = ?
where rowid = ?`)
if err != nil {
tx.Rollback()
mlog.Fatalf("at prepare: %s", err)
}
defer stmt.Close()
_, err = stmt.Exec(last_watched, all_watched, count_watched, now, dto.Id)
if err != nil {
tx.Rollback()
mlog.Fatalf("at exec: %s", err)
}
tx.Commit()
mlog.Info("FINISHED UPDATING MOVIE WATCHED DATE [%d] %s", dto.Id, dto.Title)
dto.All_Watched = all_watched
dto.Count_Watched = count_watched
dto.Modified = now
msg.Reply <- dto
}
func (d *Dal) regularSearch(options *lib.Options) (total uint64, movies []*model.Movie) {
mlog.Info("searchMovies.options: %+v", options)
tx, err := d.db.Begin()
if err != nil {
mlog.Fatalf("searchMovies:Unable to begin transaction: %s", err)
}
term := options.Query + "*"
args := options.FilterBy
mlog.Info("this is: %s %s", term, args)
// if self.searchArgs != args {
// self.searchArgs = args
countQuery := fmt.Sprintf(`select count(*) from movie dt, %s vt
where vt.%s match ? and dt.rowid = vt.docid;`,
"movie"+options.FilterBy, "movie"+options.FilterBy)
stmt, err := tx.Prepare(countQuery)
if err != nil {
mlog.Fatalf("searchMovies:Unable to prepare transaction: %s", err)
}
defer stmt.Close()
mlog.Info("searchMovies:CountQuery:%s", countQuery)
var count uint64
err = stmt.QueryRow(term).Scan(&count)
if err != nil {
mlog.Fatalf("searchMovies:Unable to count rows: %s", err)
}
listQuery := fmt.Sprintf(`select dt.rowid, dt.title, dt.original_title, dt.year, dt.runtime,
dt.tmdb_id, dt.imdb_id, dt.overview, dt.tagline, dt.resolution,
dt.filetype, dt.location, dt.cover, dt.backdrop, dt.genres, dt.vote_average,
dt.vote_count, dt.countries, dt.added, dt.modified, dt.last_watched,
dt.all_watched, dt.count_watched, dt.score, dt.director, dt.writer, dt.actors,
dt.awards, dt.imdb_rating, dt.imdb_votes
from movie dt, %s vt
where vt.%s match ? and dt.rowid = vt.docid order by dt.%s %s limit ? offset ?`,
"movie"+options.FilterBy, "movie"+options.FilterBy, options.SortBy, options.SortOrder)
// mlog.Info("my main man: %s", sql)
stmt, err = tx.Prepare(listQuery)
if err != nil {
mlog.Fatalf("searchMovies:listQuery:Unable to prepare transaction: %s", err)
}
defer stmt.Close()
rows, err := stmt.Query(term, options.Limit, options.Offset)
if err != nil {
mlog.Fatalf("searchMovies:listQuery:Unable to begin transaction: %s", err)
}
items := make([]*model.Movie, 0)
for rows.Next() {
movie := model.Movie{}
rows.Scan(&movie.Id, &movie.Title, &movie.Original_Title, &movie.Year, &movie.Runtime, &movie.Tmdb_Id, &movie.Imdb_Id, &movie.Overview, &movie.Tagline, &movie.Resolution, &movie.FileType, &movie.Location, &movie.Cover, &movie.Backdrop, &movie.Genres, &movie.Vote_Average, &movie.Vote_Count, &movie.Production_Countries, &movie.Added, &movie.Modified, &movie.Last_Watched, &movie.All_Watched, &movie.Count_Watched, &movie.Score, &movie.Director, &movie.Writer, &movie.Actors, &movie.Awards, &movie.Imdb_Rating, &movie.Imdb_Votes)
// movie := &model.Movie{}
// rows.Scan(movie.Id, movie.Title, movie.Original_Title, movie.Year, movie.Runtime, movie.Tmdb_Id, movie.Imdb_Id, movie.Overview, movie.Tagline, movie.Resolution, movie.FileType, movie.Location, movie.Cover, movie.Backdrop)
// mlog.Info("title: (%s)", movie.Title)
items = append(items, &movie)
}
rows.Close()
tx.Commit()
mlog.Info("searchMovies:Listed %d movies (total %d)", len(items), count)
return count, items
}
func (d *Dal) searchByYear(options *lib.Options) (total uint64, movies []*model.Movie) {
var start, end, year uint64
var decade bool = false
if strings.Contains(options.Query, "-") {
decade = true
parts := strings.Split(options.Query, "-")
start, _ = strconv.ParseUint(parts[0], 0, 64)
end, _ = strconv.ParseUint(parts[1], 0, 64)
} else {
year, _ = strconv.ParseUint(options.Query, 0, 64)
}
var countQuery string
if decade {
countQuery = `select count(*) from movie where year between ? and ?;`
} else {
countQuery = `select count(*) from movie where year = ?;`
}
// mlog.Info("this is: %s %s", term, args)
tx, err := d.db.Begin()
if err != nil {
mlog.Fatalf("searchByYear:Unable to begin transaction: %s", err)
}
stmt, err := tx.Prepare(countQuery)
if err != nil {
mlog.Fatalf("searchByYear:Unable to prepare transaction: %s", err)
}
defer stmt.Close()
var count uint64
var rows *sql.Rows
if decade {
err = stmt.QueryRow(start, end).Scan(&count)
if err != nil {
mlog.Fatalf("searchByYear:Unable to count rows: %s", err)
}
sql := fmt.Sprintf(`select rowid, title, original_title, file_title, year, runtime, tmdb_id, imdb_id,
overview, tagline, resolution, filetype, location, cover, backdrop, genres, vote_average,
vote_count, countries, added, modified, last_watched, all_watched, count_watched, score,
director, writer, actors, awards, imdb_rating, imdb_votes
from movie where year between ? and ? order by %s %s, rowid desc limit ? offset ?`, options.SortBy, options.SortOrder)
stmt, err := tx.Prepare(sql)
if err != nil {
mlog.Fatalf("Unable to prepare transaction: %s", err)
}
defer stmt.Close()
rows, err = stmt.Query(start, end, options.Limit, options.Offset)
if err != nil {
mlog.Fatalf("Unable to prepare transaction: %s", err)
}
} else {
err = stmt.QueryRow(year).Scan(&count)
if err != nil {
mlog.Fatalf("searchByYear:Unable to count rows: %s", err)
}
sql := fmt.Sprintf(`select rowid, title, original_title, file_title, year, runtime, tmdb_id, imdb_id,
overview, tagline, resolution, filetype, location, cover, backdrop, genres, vote_average,
vote_count, countries, added, modified, last_watched, all_watched, count_watched, score,
director, writer, actors, awards, imdb_rating, imdb_votes
from movie where year = ? order by %s %s, rowid desc limit ? offset ?`, options.SortBy, options.SortOrder)
mlog.Info("sql.: %s", sql)
stmt, err := tx.Prepare(sql)
if err != nil {
mlog.Fatalf("Unable to prepare transaction: %s", err)
}
defer stmt.Close()
rows, err = stmt.Query(year, options.Limit, options.Offset)
if err != nil {
mlog.Fatalf("Unable to prepare transaction: %s", err)
}
}
items := make([]*model.Movie, 0)
for rows.Next() {
movie := model.Movie{}
rows.Scan(
&movie.Id, &movie.Title, &movie.Original_Title, &movie.File_Title, &movie.Year,
&movie.Runtime, &movie.Tmdb_Id, &movie.Imdb_Id, &movie.Overview, &movie.Tagline,
&movie.Resolution, &movie.FileType, &movie.Location, &movie.Cover, &movie.Backdrop,
&movie.Genres, &movie.Vote_Average, &movie.Vote_Count, &movie.Production_Countries,
&movie.Added, &movie.Modified, &movie.Last_Watched, &movie.All_Watched,
&movie.Count_Watched, &movie.Score, &movie.Director, &movie.Writer, &movie.Actors,
&movie.Awards, &movie.Imdb_Rating, &movie.Imdb_Votes,
)
// movie := &model.Movie{}
// rows.Scan(movie.Id, movie.Title, movie.Original_Title, movie.Year, movie.Runtime, movie.Tmdb_Id, movie.Imdb_Id, movie.Overview, movie.Tagline, movie.Resolution, movie.FileType, movie.Location, movie.Cover, movie.Backdrop)
// mlog.Info("title: (%s)", movie.Title)
items = append(items, &movie)
// mlog.Info("movie: %+v:", movie)
}
rows.Close()
tx.Commit()
mlog.Info("searchByYear:Listed %d movies (total %d)", len(items), count)
return count, items
}