path: root/database.go

package main

import (
	"strconv"

	log "github.com/sirupsen/logrus"

	"database/sql"

	_ "github.com/go-sql-driver/mysql"
)

func (app *App) saveStory(s Story) error {
	query := `
		INSERT IGNORE story (
			id,
			story_id,
			created_at,
			updated_at,
			type,
			title,
			text,
			descendants,
			time,
			poster	
		) VALUES (
			NULL,
			?,
			?,
			?,
			?,
			?,
			?,
			?,
			?,
			?
		);
		`

	stmt, err := app.DB.Prepare(query)
	if err != nil {
		log.Warn("saveStory: Preparing query failed")
		return err
	}
	defer stmt.Close()

	res, err := stmt.Exec(s.Id, app.Now, app.Now, s.Type, s.Title, s.Text, s.Score, s.Time, s.By)
	if err != nil {
		log.Warn("saveStory: Statement execution failed")
		return err
	}
	lid, err := res.LastInsertId()
	if err != nil {
		log.Warn("saveStory: lastInsertId() failed")
		return err
	}

	log.Debugf("saveStory: Successfull insert for item %d\n", s.Id)

	for _, l := range s.Links {
		query = `
			INSERT IGNORE links(
				id,
				created_at,
				updated_at,
				story_id,
				url,
				field,
				host,
				param,
				type
			) VALUES (
				NULL,
				?,
				?,
				?,
				?,
				?,
				?,
				?,
				?
			);
			`
		stmt2, err := app.DB.Prepare(query)
		if err != nil {
			log.Warn("saveStory: InsertLinks: Preparing query failed")
			return err
		}
		defer stmt2.Close()

		_, err = stmt2.Exec(app.Now, app.Now, lid, l.Url, l.Field, l.Host, l.Param, l.Type)
		if err != nil {
			log.Warn("saveStory: InsertLinks: Statement execution failed")
			return err
		}
	}

	return nil

	/*
		query = `
			INSERT IGNORE discussion (
				id,
				created_at,
				updated_at,
				article_id,
				title,
				source,
				item_id,
				source_url,
				posted_on,
				comments,
				upvotes
			) VALUES (
				NULL,
				?,
				?,
				(SELECT id FROM article WHERE url = ?),
				?,
				?,
				?,
				?,
				?,
				?,
				?
			);
			`
		stmt2, err := app.DB.Prepare(query)
		if err != nil {
			log.Warn("saveStory: Preparing second query failed")
			return err
		}
		defer stmt2.Close()

		_, err = stmt2.Exec(app.Now, app.Now, s.Url, s.Title, "HN", s.Id, "https://news.ycombinator.com/item?id="+strconv.Itoa(s.Id), s.Time, s.Descendants, s.Score)
		if err != nil {
			log.Warn("saveStory: Statement execution failed")
			return err
		}
	*/

	return nil
}

func (app *App) saveCode(s Story) error {
	query := `
		INSERT IGNORE code(
			id,
			created_at,
			updated_at,
			url,
			title,
			source,
			item_id,
			source_url,
			posted_on,
			comments,
			upvotes
		) VALUES (
			NULL,
			?,
			?,
			?,
			?,
			?,
			?,
			?,
			?,
			?,
			?
		);
		`
	stmt, err := app.DB.Prepare(query)
	if err != nil {
		log.Warn("saveCode: Preparing query failed")
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(app.Now, app.Now, s.Url, s.Title, "HN", s.Id, "https://news.ycombinator.com/item?id="+strconv.Itoa(s.Id), s.Time, s.Descendants, s.Score)
	if err != nil {
		log.Warn("saveCode: Statement execution failed")
		return err
	}

	return nil
}

func (app *App) updateDiscussion(story Story) error {

	query := `
		UPDATE discussion 
			set updated_at = ?,
			comments = ?,
			upvotes = ?
		WHERE item_id = ?;
		`
	stmt, err := app.DB.Prepare(query)
	if err != nil {
		log.Warn("updateDiscussion: Preparing query failed")
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(app.Now, story.Descendants, story.Score, story.Id)
	if err != nil {
		log.Warnf("updateDiscussion: Statement execution failed")
		return err
	}
	log.Debugf("updateDiscussion: Successful update of %d with new Score: %d, Comments: %d\n", story.Id, story.Score, story.Descendants)

	return nil
}

func (app *App) updateArticleUrl(id int, url string) error {
	query := `
		UPDATE article
			set updated_at = ?,
			url = ?
		WHERE id = ?
		`
	stmt, err := app.DB.Prepare(query)
	if err != nil {
		log.Warn("updateArticleUrl: Preparing query failed")
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(app.Now, url, id)
	if err != nil {
		log.Warnf("updateArticleUrl: Statement execution failed")
		return err
	}
	log.Debugf("updateArticleUrl: Successful update new url: %s\n", url)

	return nil

}

func (app *App) mergeArticles(id_to_delete int, correct_url string) error {
	query := "SELECT id FROM discussion WHERE article_id = ?"
	row := app.DB.QueryRow(query, id_to_delete)
	var disc_id int
	err := row.Scan(&disc_id)
	if err != nil {
		log.Warnf("mergeArticles: Query first row failed. id: %d url: %s", id_to_delete, correct_url)
		return err
	}
	query = "SELECT id FROM article WHERE url = ?"
	row = app.DB.QueryRow(query, correct_url)
	var article_id int
	err = row.Scan(&article_id)
	if err != nil {
		log.Warn("mergeArticles: Query second row failed")
		return err
	}

	query = "UPDATE discussion SET article_id = ?, updated_at = ? WHERE id = ?;"
	stmt, err := app.DB.Prepare(query)
	if err != nil {
		log.Warn("mergeArticles: Preparing query failed")
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(article_id, app.Now, disc_id)
	if err != nil {
		log.Warn("mergeArticles: Update discussion failed")
		return err
	}

	query = "UPDATE article_category SET article_id = ? WHERE id = ?;"
	stmt2, err := app.DB.Prepare(query)
	if err != nil {
		log.Warn("mergeArticles: Preparing article_category query failed")
		return err
	}
	defer stmt2.Close()

	_, err = stmt2.Exec(article_id, id_to_delete)
	if err != nil {
		log.Warn("mergeArticles: Update article_category failed")
		return err
	}

	return nil
}

func (app *App) deleteOrphanedArticles() error {
	query := `
		SELECT a.id FROM
			article AS a
		LEFT JOIN 
			discussion AS d ON a.id = d.article_id
		WHERE d.id IS NULL;`
	row := app.DB.QueryRow(query)

	var article_id int
	err := row.Scan(&article_id)

	if err != nil {
		if err == sql.ErrNoRows {
			return nil
		} else {
			log.Warnf("deleteOrphanedArticles: Executing query failed: %s", err.Error())
			return err
		}
	}

	query = `
		DELETE FROM article_category WHERE article_id = ?
	`
	_, err = app.DB.Exec(query, article_id)
	if err != nil {
		log.Warnf("deleteOrphanedArticles: Delete from article_category query failed: %s", err.Error())
		return err
	}

	query = `
		DELETE FROM article WHERE id = ?

	`
	_, err = app.DB.Exec(query, article_id)
	if err != nil {
		log.Warnf("deleteOrphanedArticles: Delete from article query failed: %s", err.Error())
		return err
	}

	return nil
}

func (app *App) saveCategory(article_id int, categories []string) {

	for _, category := range categories {
		if "" == category {
			log.Warnf("saveCategory: category is empty for article_id: %d", article_id)
			continue
		}

		query := "SELECT id FROM category WHERE name = ?"
		row := app.DB.QueryRow(query, category)
		var category_id int
		err := row.Scan(&category_id)

		if err != nil {
			if err != sql.ErrNoRows {
				log.Warn("saveCategory: Selecting category id failed")
				log.Fatal(err)
			}
		}

		if err == sql.ErrNoRows {
			query = `
		INSERT INTO category (
			id,
			created_at,
			updated_at,
			name
		) VALUES (
			null,
			?,
			?,
			?
		)`

			stmt, err := app.DB.Prepare(query)
			if err != nil {
				log.Fatal(err)
			}
			defer stmt.Close()

			result, err := stmt.Exec(app.Now, app.Now, category)
			if err != nil {
				log.Fatal(err)
			}

			category_id64, err := result.LastInsertId()
			category_id = int(category_id64)
			if err != nil {
				log.Fatal(err)
			}
		}

		query = `
	INSERT IGNORE article_category (
		id,
		article_id,
		category_id
	) VALUES (
		null,
		?,
		?
	)
	`

		stmt2, err := app.DB.Prepare(query)
		if err != nil {
			log.Fatal(err)
		}
		_, err = stmt2.Exec(article_id, category_id)
		if err != nil {
			log.Fatal(err)
		}
	}
}

func (app *App) getArticleIdFromUrl(wiki_url string) int {
	row := app.DB.QueryRow("SELECT id FROM article WHERE url = ?", wiki_url)
	var article_id int
	err := row.Scan(&article_id)
	if err != nil {
		log.Warnf("getArticleIdFromUrl: Query or scanning failed for: %s", wiki_url)
		log.Fatal(err)
	}
	return article_id
}

func (app *App) getAllArticles() {
	rows, err := app.DB.Query("SELECT DISTINCT article_id FROM discussion;")
	if err != nil {
		log.Fatal(err)
	}

	for rows.Next() {
		var article_id int

		err = rows.Scan(&article_id)
		if err != nil {
			log.Fatal(err)
		}

		log.Println(article_id)
	}
}

func (app *App) getMaxStoredItem() int {
	row := app.DB.QueryRow("SELECT max_story_id FROM max_item ORDER BY id ASC LIMIT 1;")
	var max_id int
	err := row.Scan(&max_id)
	if err == sql.ErrNoRows {
		return 0
	} else if err != nil {
		log.Warnf("getMaxStoredId failed\n")
		log.Fatal(err)
	}
	return max_id
}

func (app *App) updateNewMaxStoredItem(new_max_item int) error {
	query := "UPDATE max_item SET max_story_id = ?, updated_at = ?;"
	stmt, err := app.DB.Prepare(query)
	if err != nil {
		log.Warn("updateNewMaxStoredItem: Preparing query failed")
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(new_max_item, app.Now)
	if err != nil {
		log.Warnf("updateNewMaxStoredItem: Statement execution failed: %d\n", new_max_item)
		return err
	}
	log.Infof("updateNewMaxStoredItem: updated max_story_id to: %d\n", new_max_item)

	return nil
}

/**
 * Creates new max_story_id
 */
func (app *App) createMaxStoredItem(new_max_item int) error {
	stmt, err := app.DB.Prepare("INSERT INTO max_item VALUES (null, ?, ?, ?);")
	if err != nil {
		log.Warn("getMaxStoredItem: Preparing query failed")
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(new_max_item, app.Now, app.Now)
	if err != nil {
		log.Warnf("getMaxStoredItem: Executing query failed with new_max_item: %d\n", new_max_item)
		return err
	}

	log.Info("getMaxStoredItem: creatd MaxStoredItem for the first time")

	return nil
}

func (app *App) FixURLs() {
	rows, err := app.DB.Query("SELECT url,field FROM links;")
	if err != nil {
		log.Fatal(err)
	}

	for rows.Next() {
		var url string
		var field int

		err = rows.Scan(&url, &field)
		if err != nil {
			log.Fatal(err)
		}

		link := getURL(url, field)
		log.Printf("%+v\n", link)
		//log.Warnf("%+v\n", link)
	}
}