GoDown/app/handlers/sqldb/sqldb.go

package sqldb

import (
	"database/sql"
	"fmt"

	"github.com/spf13/viper"
)

var DB *sql.DB
var dbUser, dbPass, dbHost, dbPort, dbName string

// check error
func check(err error) {
	if err != nil {
		panic(err.Error())
	}
}

// read db cred
func ReaddbCred() {
	dbCred := viper.New()
	dbCred.SetConfigName("db-cred")
	dbCred.AddConfigPath("../")
	dbCred.SetConfigType("yaml")
	err := dbCred.ReadInConfig()
	check(err)
	dbUser = dbCred.GetString("dbUser")
	dbPass = dbCred.GetString("dbPass")
	dbHost = dbCred.GetString("dbHost")
	dbPort = dbCred.GetString("dbPort")
	dbName = dbCred.GetString("dbName")
}

func Opendb() (db *sql.DB) {
	// open db
	sqlopen := fmt.Sprintf(dbUser + ":" + dbPass + "@tcp(" + dbHost + ":" + dbPort + ")/" + dbName)
	var err error
	DB, err = sql.Open("mysql", sqlopen)
	check(err)
	fmt.Println("Successfully connected to the database")
	return DB
}

func Dbinit() {
	DB = Opendb()
	defer DB.Close()
	dbinit, err := DB.Query("CREATE TABLE IF NOT EXISTS testdb.media (`id` INT NOT NULL AUTO_INCREMENT, `ytid` VARCHAR(100), `size` INT, `sha256sum` VARCHAR(255), `duration` INT, `title` VARCHAR(255), `author` VARCHAR(255), PRIMARY KEY (`id`));")
	if err != nil {
		fmt.Println("Error while creating table")
		panic(err.Error())
	}
	defer dbinit.Close()
	fmt.Println("DB-Init successfull")
}

func Dbentrycount() {
	DB = Opendb()
	defer DB.Close()
	var count int
	dbcount, err := DB.Query("SELECT COUNT(*) FROM testdb.media;")
	if err != nil {
		fmt.Println("Error while counting rows from table")
		panic(err.Error())
	}
	defer dbcount.Close()
	for dbcount.Next() {
		err = dbcount.Scan(&count)
		if err != nil {
			panic(err.Error())
		}
	}
	fmt.Printf("There are %v entries in the database\n", count)
}