go-site/cmd/web/connector.go

package main

import (
	"fmt"
	"io/ioutil"
	"encoding/json"
	"database/sql"
	_ "github.com/go-sql-driver/mysql"
)

type ConnectionProperties struct {
	User 	string
	Pass 	string
	Host	string
	Db 		string
}

type connection struct {
	dBOpenStr string
	connProperties ConnectionProperties
}

func (c *connection) Init(filepath string){
	if (NOMYSQL) {
		return
	}
	b, err := ioutil.ReadFile(filepath)
    if err != nil {
      fmt.Print(err)
    }
    propJson := string(b)

	json.Unmarshal([]byte(propJson), &c.connProperties)
	
	//c.connProperties.Db = "nosite"
	fmt.Printf("Connection data:\n%s\n%s\n%s\n%s\n", c.connProperties.User, c.connProperties.Pass, c.connProperties.Host, c.connProperties.Db)
	c.dBOpenStr = fmt.Sprintf ("%s:%s@tcp(%s)/%s", c.connProperties.User, c.connProperties.Pass, c.connProperties.Host, c.connProperties.Db)
	fmt.Printf("Connecting with:\n%s\n", c.dBOpenStr)
	db, err := sql.Open("mysql", c.dBOpenStr)
	if err != nil {
		panic(err)
	}
	if err = db.Ping(); err != nil {
        db.Close()
        logger.Print("Fatal : Error with connection to database!")
    } else {
    	fmt.Print("Connection succesfull!")
    	return
    }
    

    logger.Print("Trying to connect to DB-server...")
    c.dBOpenStr = fmt.Sprintf ("%s:%s@tcp(%s)/", c.connProperties.User, c.connProperties.Pass, c.connProperties.Host)
    fmt.Printf("Connecting with:\n%s\n", c.dBOpenStr)
	db, err = sql.Open("mysql", c.dBOpenStr)
	if err != nil {
		panic(err)
	}
	if err = db.Ping(); err != nil {
        db.Close()
        logger.Print("Fatal : Error with connection to database server!")
        return
    } else {
    	
    }
    c.databaseInitialization();
}

/*  создает на сервере необходимую бд и таблицы  */
func (c *connection) databaseInitialization() { 
	db, err := sql.Open("mysql", c.dBOpenStr)

	if err != nil {
		panic(err)
	}

	defer db.Close()

	var counter int
	var act_query string
	logger.Printf("Checking for existence of database '%s' on server...", c.connProperties.Db)
	act_query = "SELECT count(*) FROM information_schema.tables WHERE TABLE_SCHEMA = '" + c.connProperties.Db + "';"
	db.QueryRow(act_query).Scan(&counter)
	fmt.Print(counter)
	if (counter != 0) {
		logger.Print("Server already has the specified database")
	} else {
		logger.Print("The server does not have the specified database")
		logger.Printf("Creating database '%s'...", c.connProperties.Db)
		act_query = "CREATE SCHEMA " + c.connProperties.Db + " DEFAULT CHARACTER SET utf8 ;"
		result, err := db.Exec(act_query)
	    if err != nil{
	        panic(err)
	    }

	    rowsCount, _ := result.RowsAffected()
		fmt.Printf("Lines changed: %d\n", rowsCount)
	    if (rowsCount == 1) {
	    	logger.Print("Succesfull!")
	    }
	}

	logger.Print("Checking for existence of table 'users' on server...")
	act_query = "SELECT count(*) FROM information_schema.tables WHERE TABLE_NAME = 'users' AND TABLE_SCHEMA = '"+ c.connProperties.Db +"';"
	db.QueryRow(act_query).Scan(&counter)
	fmt.Print(counter)
	if (counter != 0) {
		logger.Print("Server already has the specified table!")
	} else {
		logger.Print("The server does not have the specified table")
		logger.Printf("Creating table '%s'.'users'...",c.connProperties.Db)
		act_query = "CREATE TABLE `" + c.connProperties.Db + "`.`users` ( `idusers` INT NOT NULL AUTO_INCREMENT,`username` VARCHAR(45) NOT NULL, `password` VARCHAR(45) NOT NULL, PRIMARY KEY(`idusers`), UNIQUE INDEX `idusers_UNIQUE` (`idusers` ASC), UNIQUE INDEX `username_UNIQUE` (`username` ASC)) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 ;"
		fmt.Print(act_query)
		_, err := db.Exec(act_query)
	    if err != nil{
	        panic(err)
	    }
    	logger.Print("Succesfull!")
	}


}

func (c connection) LogIn(username string, password string) bool{
	fmt.Printf("\n\nLogIn\nConnecting with:\n%s\n", c.dBOpenStr)
	db, err := sql.Open("mysql", c.dBOpenStr)

	if err != nil {
		panic(err)
	}

	defer db.Close()

	var counter int
	//fmt.Printf("%s\n%s\n", username, password)
	act_query := fmt.Sprintf("SELECT count(*) FROM %s.users WHERE username='%s' AND password=SHA('%s');", c.connProperties.Db, username, password)
	db.QueryRow(act_query).Scan(&counter)
	fmt.Println("we have", counter, "rows")
    
    if (counter == 0) {
    	return false
    }
	return true


}

func (c connection) IsNameUsed(username string) bool {

	db, err := sql.Open("mysql", c.dBOpenStr)
	if err != nil {
		panic(err)
	}
	defer db.Close()

	var counter int
	act_query := fmt.Sprintf("SELECT count(*) FROM %s.users WHERE username='%s';", c.connProperties.Db, username)
	db.QueryRow(act_query).Scan(&counter)
    
	if (counter == 0) {
		fmt.Printf("Username unused\n")
    	return false
    }
	fmt.Printf("Username used\n")
	return true
}

func (c connection) SigInUser(username string, password string) bool {
	db, err := sql.Open("mysql", c.dBOpenStr)  
    if err != nil {
        panic(err)
    } 
    defer db.Close()
    act_query := fmt.Sprintf("INSERT INTO %s.users (username, password) VALUES ('%s', SHA('%s'))", c.connProperties.Db, username, password)
    result, err := db.Exec(act_query)
    if err != nil{
        panic(err)
    }

    rowsCount, _ := result.RowsAffected()
    if (rowsCount == 1) {
	    fmt.Printf("Lines changed: %d\n", rowsCount)
	    return true
    } else {
    	return false
    }
}

func (c connection) SubmitScore(score int){
	fmt.Printf("Submiting score %d", score)
}

/*
func main() {

	var dBConnector connection

	dBConnector.Init("config.json")
	if (dBConnector.LogIn("Alex", "09Alex09")) {
		fmt.Printf("Succesfull logIn\n")
	} else {
		fmt.Printf("logIn error\n")
	}

	if (dBConnector.LogIn("Alax", "09Alex09")) {
		fmt.Printf("Succesfull logIn\n")
	} else {
		fmt.Printf("logIn error\n")
	}
}
*/