go-site/cmd/web/connector.go

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"html/template"
	"io/ioutil"
	"log"

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

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

type connection struct {
	dBOpenStr      string
	connProperties ConnectionProperties
}

type Torrent struct {
	ID      int64
	Hash    string
	Name    string
	OwnerID string
	Time    string
	Deleted bool
}

type Folder struct {
	Uuid 		string
	ParentUuid 	string
	Type		int
	Title		string
	Snake		string
}

type ForumMessage struct {
	Author	string
	Time 	string
	Text 	string
}

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.Println("Fatal : Error with connection to database!")
	} else {
		fmt.Println("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!")
	}
	/*

		CREATE TABLE `gosite`.`torrents` (
		`id` INT NOT NULL AUTO_INCREMENT,
		`hash` VARCHAR(45) NOT NULL,
		`name` VARCHAR(45) NOT NULL,
		`ownerid` VARCHAR(45) NOT NULL,
		`time` TIMESTAMP NOT NULL,
		`deleted` TINYINT NULL,
		UNIQUE INDEX `idtorrents_UNIQUE` (`id` ASC) VISIBLE,
		PRIMARY KEY (`hash`),
		UNIQUE INDEX `hash_UNIQUE` (`hash` ASC) VISIBLE);


	*/

}

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 (c connection) TakeTorrent(hash string) (Torrent, error) {
	db, err := sql.Open("mysql", c.dBOpenStr)
	if err != nil {
		panic(err)
	}
	defer db.Close()
	act_query := fmt.Sprintf("SELECT * FROM `%s`.`torrents` WHERE `hash`='%s'", c.connProperties.Db, hash)
	fmt.Println(act_query)
	rows, err := db.Query(act_query)
	if err != nil {
		panic(err)
	}
	defer rows.Close()

	var torrent Torrent
	for rows.Next() {

		if err := rows.Scan(
			&torrent.ID,
			&torrent.Hash,
			&torrent.Name,
			&torrent.OwnerID,
			&torrent.Time,
			&torrent.Deleted,
		); err != nil {
			log.Fatal(err)
		}

		fmt.Println(torrent)
		return torrent, nil
	}

	return torrent, fmt.Errorf("Torrent not found")
}


// Takes 'amount' latest torrents in order of Time desc 
// (torrents[0] is a newest)
// If amount is 0 returns all torrents in same order as usual 
func (c connection) GetLastTorrents(amount int64) (torrents []Torrent, count int64, err error) {
	db, err := sql.Open("mysql", c.dBOpenStr)
	if err != nil {
		return
	}
	defer db.Close()

	var act_query string

	act_query = fmt.Sprintf("SELECT * FROM `%s`.`torrents`", c.connProperties.Db)
	act_query += " ORDER BY `time` DESC" 
	if amount != 0 {
		act_query += fmt.Sprintf(" LIMIT %d",  amount)
	}

	
	
	fmt.Println(act_query)
	rows, err := db.Query(act_query)
	if err != nil {
		return
	}
	defer rows.Close()

	for rows.Next() {
		var torrent Torrent
		if err := rows.Scan(
			&torrent.ID,
			&torrent.Hash,
			&torrent.Name,
			&torrent.OwnerID,
			&torrent.Time,
			&torrent.Deleted,
		); err != nil {
			log.Fatal(err)
		}

		torrents = append(torrents, torrent)
		count++
	}

	return
}

// Takes 'amount' latest news for headline in order of Time desc 
// (news[0] is a newest)
// If amount is 0 returns all news in same order as usual 
func (c connection) GetLastNews(amount int64) (news []NewsItem, count int64, err error) {
	newsTopicUuid := "557adf6b-6988-4dfe-89d1-85e56947e067"
	
	db, err := sql.Open("mysql", c.dBOpenStr)
	if err != nil {
		return
	}
	defer db.Close()

	var act_query string

	act_query = fmt.Sprintf("SELECT text, users.username, msg.time FROM %s.messages as msg INNER JOIN %s.folders as folders ON msg.parent_uuid = folders.uuid INNER JOIN %s.users as users ON msg.author = users.idusers WHERE folders.parent_uuid = '%s' ORDER BY msg.time DESC", c.connProperties.Db, c.connProperties.Db, c.connProperties.Db, newsTopicUuid)
	if amount != 0 {
		act_query += fmt.Sprintf(" LIMIT %d",  amount)
	}

	fmt.Println(act_query)
	rows, err := db.Query(act_query)
	if err != nil {
		return
	}
	defer rows.Close()

	for rows.Next() {
		var templateStr string
		var item NewsItem
		if err := rows.Scan(
			&templateStr,
			&item.Author,
			&item.Time,
		); err != nil {
			log.Fatal(err)
		}

		item.NewsText = template.HTML(templateStr)

		news = append(news, item)
		count++
	}

	return

}

func (c connection) TakeChildFolders(parentUuid string, amount int64) (folders []Folder, err error){
	db, err := sql.Open("mysql", c.dBOpenStr)
	if err != nil {
		return
	}
	defer db.Close()

	var act_query string

	act_query = fmt.Sprintf("SELECT * FROM `%s`.`folders` as `folders`", c.connProperties.Db)
	act_query += fmt.Sprintf(" WHERE `folders`.`parent_uuid` = '%s'", parentUuid)
	act_query += " ORDER BY `id` DESC"
	if amount != 0 {
		act_query += fmt.Sprintf(" LIMIT %d",  amount)
	}

	fmt.Println(act_query)
	rows, err := db.Query(act_query)
	if err != nil {
		return
	}
	defer rows.Close()

	for rows.Next() {
		var folder Folder
		var id interface{}
		if err := rows.Scan(
			&id,
			&folder.Uuid,
			&folder.ParentUuid,
			&folder.Type,
			&folder.Title,
			&folder.Snake,
		); err != nil {
			log.Fatal(err)
		}

		folders = append(folders, folder)
	}

	return
}

func (c connection) TakeChildFolderBySnake(parentUuid string, snake string) (folder Folder, err error){
	db, err := sql.Open("mysql", c.dBOpenStr)
	if err != nil {
		return
	}
	defer db.Close()

	var act_query string

	act_query = fmt.Sprintf("SELECT * FROM `%s`.`folders` as `folders`", c.connProperties.Db)
	act_query += fmt.Sprintf(" WHERE `folders`.`parent_uuid` = '%s'", parentUuid)
	act_query += fmt.Sprintf(" AND `folders`.`snake` = '%s'", snake)
	fmt.Println(act_query)
	rows, err := db.Query(act_query)
	if err != nil {
		return
	}
	defer rows.Close()

	for rows.Next() {
		var id interface{}
		if err := rows.Scan(
			&id,
			&folder.Uuid,
			&folder.ParentUuid,
			&folder.Type,
			&folder.Title,
			&folder.Snake,
		); err != nil {
			log.Fatal(err)
		}
	}
	return
}

func (c connection) TakeTopicMessages(parentUuid string, amount int64) (messages []ForumMessage, err error) {
	db, err := sql.Open("mysql", c.dBOpenStr)
	if err != nil {
		return
	}
	defer db.Close()

	var act_query string

	act_query = fmt.Sprintf("SELECT `users`.`username`, `messages`.`time`, `messages`.`text`  FROM `%s`.`messages` as `messages`", c.connProperties.Db)
	act_query += fmt.Sprintf(" INNER JOIN `%s`.`users` as `users` ON `users`.`idusers` = `messages`.`author`", c.connProperties.Db)
	act_query += fmt.Sprintf(" WHERE `messages`.`parent_uuid` = '%s'", parentUuid)
	if amount != 0 {
		act_query += fmt.Sprintf(" LIMIT %d",  amount)
	}
	fmt.Println(act_query)
	rows, err := db.Query(act_query)
	if err != nil {
		return
	}
	defer rows.Close()

	for rows.Next() {
		var message ForumMessage
		if err := rows.Scan(
			&message.Author,
			&message.Time,
			&message.Text,
		); err != nil {
			log.Fatal(err)
		}
		messages = append(messages, message)
	}
	return
}

/*
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")
	}
}
*/