squizz_db_sync.go

package main

import (
	"encoding/json"
	"flag"
	"fmt"
	"html"
	"io/ioutil"
	"log"
	"net/http"
	"net/url"
	"os"
	"regexp"
	"strings"
	"time"

	"gorm.io/driver/postgres"
	"gorm.io/gorm"
)

var (
	dbHost     = flag.String("dbhost", "localhost", "db host")
	dbPort     = flag.String("dbport", "5432", "db port")
	dbUser     = flag.String("dbuser", "postgres", "db user")
	dbPassword = flag.String("dbpassword", "postgres", "db password")
	dbName     = flag.String("dbname", "postgres", "db name")
)

func main() {
	// database
	dsn := fmt.Sprintf("host=%v user=%v password=%v dbname=%v port=%v sslmode=disable", *dbHost, *dbUser, *dbPassword, *dbName, *dbPort)
	fmt.Printf("[1/3] Connectiong to database %v\n", dsn)
	db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{
		// Logger: logger.Default.LogMode(logger.Silent),
	})
	if err != nil {
		fmt.Println("ERROR: database unreachable: ", err)
		os.Exit(1)
	}
	err = db.AutoMigrate(&Quizz{})
	if err != nil {
		fmt.Println("ERROR: database migration failed :", err)
		os.Exit(1)
	}
	err = db.AutoMigrate(&Theme{})
	if err != nil {
		fmt.Println("ERROR: database migration failed :", err)
		os.Exit(1)
	}

	fmt.Println("[2/3] Looking for quizz on openquizzdb.org")
	quizzByTheme, err := parseQuizzDB()
	if err != nil {
		fmt.Println("ERROR: Parsing opendbquizz:", err)
		os.Exit(1)
	}

	fmt.Println("[3/3] Downnloading and saving quizzs")
	for theme, quizzIDs := range quizzByTheme {
		t := Theme{Name: theme}
		db.Create(&t)
		for _, id := range quizzIDs {
			quizzJSONLink, err := parseQuizzJSONLink(id)
			if err != nil {
				fmt.Printf("Skipped (Error finding quizz json link : %v)\n", err)
				continue
			}

			quizzlist, err := download(quizzJSONLink)
			if err != nil {
				log.Printf("Skipped (Error downloading : %v)\n", err)
				continue
			}

			for _, q := range quizzlist {
				q.ThemeID = t.ID
				db.Create(&q)
			}
		}
	}
}

// Level difficulty
const (
	DifficultyBeginner = iota + 1
	DifficultyNormal
	DifficultyExpert
)

// Quizz represent a question datas for a quizz
type Quizz struct {
	ID         int       `json:"-"`
	CreatedAt  time.Time `json:"-"`
	UpdatedAt  time.Time `json:"-"`
	Question   string    `json:"question" gorm:"unique"`
	Answer     string    `json:"réponse"`
	Difficulty int       `json:"-"`
	ThemeID    int       `json:"-"`
}

// TableName in database
func (q Quizz) TableName() string {
	return "quizz"
}

type Theme struct {
	ID        int
	CreatedAt time.Time
	UpdatedAt time.Time
	Name      string
}

func (t Theme) TableName() string {
	return "themes"
}

var reParseQuizzList = regexp.MustCompile("goq\\((.*?)\\)")

// parseQuizzDB return quizz IDs grouped by theme
func parseQuizzDB() (map[string][]string, error) {
	req, err := http.NewRequest("GET", "https://www.openquizzdb.org/listing.php", nil)
	if err != nil {
		return nil, err
	}

	clt := &http.Client{Timeout: time.Minute}
	res, err := clt.Do(req)
	if err != nil {
		return nil, err
	}
	defer res.Body.Close()

	bodyBytes, err := ioutil.ReadAll(res.Body)
	if err != nil {
		return nil, err
	}

	quizzByTheme := make(map[string][]string)
	for _, v := range strings.Split(string(bodyBytes), `<span style="font-weight:bold;color:#555555">`)[1:] {
		theme := strings.ToLower(html.UnescapeString(strings.SplitN(v, "<", 2)[0]))
		quizzlist := reParseQuizzList.FindAllStringSubmatch(v, -1)
		if _, ok := quizzByTheme[theme]; !ok {
			quizzByTheme[theme] = make([]string, 0)
		}
		for _, quizzID := range quizzlist {
			quizzByTheme[theme] = append(quizzByTheme[theme], quizzID[1])
		}
	}

	return quizzByTheme, nil
}

var reParseJSON = regexp.MustCompile(`https://www.kiwime.com/oqdb/files/(.*?).json`)

func parseQuizzJSONLink(id string) (string, error) {
	form := url.Values{}
	form.Add("id", id)
	req, err := http.NewRequest("POST", "https://www.openquizzdb.org/download.php", strings.NewReader(form.Encode()))
	req.Header.Add("Content-Type", "application/x-www-form-urlencoded")
	if err != nil {
		return "", err
	}

	clt := &http.Client{Timeout: time.Minute}
	res, err := clt.Do(req)
	if err != nil {
		return "", err
	}
	defer res.Body.Close()

	bodyBytes, err := ioutil.ReadAll(res.Body)
	if err != nil {
		return "", err
	}

	match := reParseJSON.FindStringSubmatch(string(bodyBytes))
	if len(match) == 0 {
		return "", fmt.Errorf("no link found")
	}

	return fmt.Sprintf("https://www.kiwime.com/oqdb/files/%v.json", match[1]), nil
}

func download(target string) ([]Quizz, error) {
	req, err := http.NewRequest("GET", target, nil)
	if err != nil {
		return nil, err
	}

	clt := &http.Client{Timeout: time.Minute}
	res, err := clt.Do(req)
	if err != nil {
		return nil, err
	}
	defer res.Body.Close()

	var fullResponse struct {
		Quizz struct {
			Fr struct {
				Beginner []Quizz `json:"débutant"`
				Normal   []Quizz `json:"confirmé"`
				Expert   []Quizz `json:"expert"`
			} `json:"fr"`
		} `json:"quizz"`
	}
	if err = json.NewDecoder(res.Body).Decode(&fullResponse); err != nil {
		return nil, err
	}

	quizzlist := []Quizz{}
	for _, q := range fullResponse.Quizz.Fr.Beginner {
		q.Difficulty = DifficultyBeginner
		quizzlist = append(quizzlist, q)
	}
	for _, q := range fullResponse.Quizz.Fr.Normal {
		q.Difficulty = DifficultyNormal
		quizzlist = append(quizzlist, q)
	}
	for _, q := range fullResponse.Quizz.Fr.Expert {
		q.Difficulty = DifficultyExpert
		quizzlist = append(quizzlist, q)
	}

	return quizzlist, nil
}