sqliteFundamentals.go

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/mattn/go-sqlite3"
)

// Player schema from the database
type Player struct {
	id       int
	name     string
	club     string
	position string
	age      uint8
}

func createPlayersTable() *sql.DB {
	db, err := sql.Open("sqlite3", "./players.db")

	if err != nil {
		log.Println(err)
	}

	// Create table
	statement, err := db.Prepare("CREATE TABLE IF NOT EXISTS players (id INTEGER PRIMARY KEY, name VARCHAR(64) NOT NULL, club VARCHAR(64) NOT NULL, age INTEGER, position VARCHAR(64) NOT NULL)")
	if err != nil {
		fmt.Println("errrrr ", err)
		log.Println("Error in creating table")
	} else {
		log.Println("Successfully created table players!")
	}
	statement.Exec()

	//Flushing the DB so that we don't add already added players each time we run the programme.
	flushDB, _ := db.Prepare("delete from players")
	flushDB.Exec()

	return db
}

func createPlayer(dbDriver *sql.DB) {
	statement, err := dbDriver.Prepare("INSERT INTO players(name, club, position, age) VALUES (?, ?, ?, ?)")
	if err != nil {
		fmt.Println(err)
		log.Panic("Error in creating player")
	}
	statement.Exec("Kai Havertz", "Chelsea", "CAM", 21)
	statement.Exec("Mason Maount", "Chelsea", "CM", 22)
	statement.Exec("Jorginho", "Chelsea", "CM", 22)

	log.Println("Inserted players in the database!")
}

func readPlayer(dbDriver *sql.DB) {
	rows, _ := dbDriver.Query("SELECT id, name, club, position, age FROM players LIMIT 20")
	var tempPlayer Player

	for rows.Next() {
		rows.Scan(&tempPlayer.id, &tempPlayer.name, &tempPlayer.club, &tempPlayer.position, &tempPlayer.age)
		log.Printf("ID:%d, Name:%s, Club:%s, Position:%s, Age:%d\n", tempPlayer.id,
			tempPlayer.name, tempPlayer.club, tempPlayer.position, tempPlayer.age)
	}

	log.Println("Fetched all players from the database!")
}

func updatePlayer(dbDriver *sql.DB) {
	statement, _ := dbDriver.Prepare("update players set name=? where name=?")
	statement.Exec("Mason Mount", "Mason Maount")
	log.Println("Successfully updated the player in the database!")
}

func deletePlayer(dbDriver *sql.DB) {
	statement, _ := dbDriver.Prepare("delete from players where name=?")
	statement.Exec("Jorginho")
	log.Println("Successfully deleted the plauyer from database!")
}

func main() {

	db := createPlayersTable()
	defer db.Close()
	fmt.Println("===============================================")
	createPlayer(db)
	readPlayer(db)
	fmt.Println("===============================================")
	updatePlayer(db)
	readPlayer(db)
	fmt.Println("===============================================")
	deletePlayer(db)
	readPlayer(db)
}