wine-server/utils/tables/advertise.go

package tables

import (
	"Wine-Server/utils"
	"fmt"
)

type AdvertiseTable struct {
	Id       uint32         `json:"id"`
	Order    uint8          `json:"order"`
	Time     utils.TimeType `json:"time"`
	Src      string         `json:"src"`
	Type     bool           `json:"type"`     // true: pic, false: video
	Duration uint16         `json:"duration"` // ms
}

func CreateAdvertiseTable() error {
	sql := "CREATE TABLE IF NOT EXISTS `advertise`(" +
		"`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY," +
		"`order` TINYINT UNSIGNED NOT NULL," +
		"`time` DATETIME DEFAULT CURRENT_TIMESTAMP," +
		"`type` BOOL DEFAULT TRUE," +
		"`src` VARCHAR(128) NOT NULL," +
		"`duration` SMALLINT UNSIGNED DEFAULT 3000);"
	_, err := utils.Mysql.Exec(sql)
	if err != nil {
		return err
	}
	return nil
}

func (row *AdvertiseTable) Insert() error {
	sql := "INSERT INTO `advertise` (`id`, `order`, `type`, `src`, `duration`) VALUES (?, ?, ?, ?, ?);"
	pre, err := utils.Mysql.Prepare(sql)
	if err != nil {
		return err
	}
	_, err = pre.Exec(row.Id, row.Order, row.Type, row.Src, row.Duration)
	if err != nil {
		return err
	}
	return nil
}

func (row *AdvertiseTable) Delete() error {
	pre, err := utils.Mysql.Prepare("DELETE FROM `advertise` WHERE `id`=?;")
	if err != nil {
		return err
	}
	_, err = pre.Exec(row.Id)
	if err != nil {
		return err
	}
	return nil
}

func (row *AdvertiseTable) Update(args utils.JsonType) error {
	keys, values := utils.UnZip(args)
	sql := fmt.Sprintf("UPDATE `advertise` SET %s WHERE `id`=%d;", utils.SqlFields(keys), row.Id)
	pre, err := utils.Mysql.Prepare(sql)
	if err != nil {
		return err
	}
	_, err = pre.Exec(values...)
	if err != nil {
		return err
	}
	return nil
}

func (row *AdvertiseTable) Get() error {
	sql := "SELECT `order`, `time`, `src`, `type`, `duration` FROM `advertise` WHERE `id`=?;"
	pre, err := utils.Mysql.Prepare(sql)
	if err != nil {
		return err
	}
	err = pre.QueryRow(row.Id).Scan(&row.Order, &row.Time, &row.Src, &row.Type, &row.Duration)
	if err != nil {
		return err
	}
	return nil
}

func AdvListAll() ([]AdvertiseTable, error) {
	var res []AdvertiseTable
	query, err := utils.Mysql.Query("SELECT `id`, `order`, `time`, `src`, `type`, `duration` FROM `advertise` ORDER BY `order`;")
	if err != nil {
		return nil, err
	}
	for query.Next() {
		var one AdvertiseTable
		err = query.Scan(&one.Id, &one.Order, &one.Time, &one.Src, &one.Type, &one.Duration)
		if err != nil {
			return nil, err
		}
		res = append(res, one)
	}
	return res, nil
}