...

Source file src/code.rocketnine.space/tslocum/zenith/database.go

Documentation: code.rocketnine.space/tslocum/zenith

     1  package zenith
     2  
     3  import (
     4  	"database/sql"
     5  	"fmt"
     6  	"strconv"
     7  	"strings"
     8  	"sync"
     9  )
    10  
    11  // TODO: Add indexes
    12  var DatabaseTables = map[string][]string{
    13  	"match": {
    14  		"`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT",
    15  		"`timestamp` INTEGER NOT NULL DEFAULT 0",
    16  		"`game` INTEGER NOT NULL DEFAULT 0",
    17  		"`match` INTEGER NOT NULL DEFAULT 0",
    18  		"`player` INTEGER NOT NULL DEFAULT 0",
    19  		"`outcome` INTEGER NOT NULL DEFAULT 0",
    20  	},
    21  	"matchmeta": {
    22  		"`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT",
    23  		"`game` INTEGER NOT NULL DEFAULT 0",
    24  		"`match` INTEGER NOT NULL DEFAULT 0",
    25  		"`player` INTEGER NOT NULL DEFAULT 0",
    26  		"`key` VARCHAR(255) NOT NULL DEFAULT ''",
    27  		"`value` TEXT NOT NULL DEFAULT ''",
    28  	},
    29  	"meta": {
    30  		"`key` VARCHAR(255) NOT NULL PRIMARY KEY",
    31  		"`value` TEXT NOT NULL DEFAULT ''",
    32  	}}
    33  
    34  const DatabaseVersion = 1
    35  
    36  var DatabasePrefix string
    37  
    38  type Database struct {
    39  	db           *sql.DB
    40  	funcGreatest string
    41  	driver       string
    42  	sync.RWMutex
    43  }
    44  
    45  func Connect(driver string, dataSource string) (*Database, error) {
    46  	var err error
    47  	d := new(Database)
    48  
    49  	d.db, err = sql.Open(driver, dataSource)
    50  	if err != nil {
    51  		return nil, fmt.Errorf("failed to connect to database: %s", err)
    52  	}
    53  
    54  	d.funcGreatest = "GREATEST"
    55  	if driver == "sqlite3" {
    56  		d.funcGreatest = "MAX"
    57  
    58  		_, err = d.db.Exec(`PRAGMA encoding="UTF-8"`)
    59  		if err != nil {
    60  			return nil, fmt.Errorf("failed to send PRAGMA: %s", err)
    61  		}
    62  	}
    63  
    64  	err = d.CreateTables()
    65  	if err != nil {
    66  		_ = d.db.Close()
    67  		return nil, fmt.Errorf("failed to create tables: %s", err)
    68  	}
    69  
    70  	err = d.Migrate()
    71  	if err != nil {
    72  		_ = d.db.Close()
    73  		return nil, fmt.Errorf("failed to migrate database: %s", err)
    74  	}
    75  
    76  	return d, nil
    77  }
    78  
    79  func (d *Database) CreateTables() error {
    80  	var (
    81  		tcolumns string
    82  		err      error
    83  	)
    84  
    85  	createQueryExtra := ""
    86  	if d.driver == "mysql" {
    87  		createQueryExtra = " ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci"
    88  	}
    89  
    90  	for tname, tcols := range DatabaseTables {
    91  		tname = DatabasePrefix + tname
    92  
    93  		tcolumns = strings.Join(tcols, ",")
    94  		if d.driver == "mysql" {
    95  			tcolumns = strings.Replace(tcolumns, "AUTOINCREMENT", "AUTO_INCREMENT", -1)
    96  		}
    97  
    98  		_, err = d.db.Exec(fmt.Sprintf("CREATE TABLE IF NOT EXISTS `%s` (%s)", tname, tcolumns) + createQueryExtra)
    99  		if err != nil {
   100  			return fmt.Errorf("failed to create table %s: %s", tname, err)
   101  		}
   102  	}
   103  
   104  	return nil
   105  }
   106  
   107  func (d *Database) Migrate() error {
   108  	rows, err := d.db.Query("SELECT `value` FROM "+DatabasePrefix+"meta WHERE `key`=?", "version")
   109  	if err != nil {
   110  		return fmt.Errorf("failed to fetch database version: %s", err)
   111  	}
   112  
   113  	version := 0
   114  	for rows.Next() {
   115  		v := ""
   116  		err = rows.Scan(&v)
   117  		if err != nil {
   118  			return fmt.Errorf("failed to scan database meta: %s", err)
   119  		}
   120  
   121  		version, err = strconv.Atoi(v)
   122  		if err != nil {
   123  			version = -1
   124  		}
   125  	}
   126  
   127  	if version == -1 {
   128  		panic("Unable to migrate database: database version unknown")
   129  	} else if version == 0 {
   130  		_, err := d.db.Exec("UPDATE "+DatabasePrefix+"meta SET `value`=? WHERE `key`=?", strconv.Itoa(DatabaseVersion), "version")
   131  		if err != nil {
   132  			return fmt.Errorf("failed to save database version: %s", err)
   133  		}
   134  	}
   135  
   136  	migrated := false
   137  	for version < DatabaseVersion {
   138  		switch version {
   139  		case 1:
   140  			// DatabaseVersion 2 migration queries will go here
   141  		}
   142  
   143  		version++
   144  		migrated = true
   145  	}
   146  
   147  	if migrated {
   148  		_, err := d.db.Exec("UPDATE "+DatabasePrefix+"meta SET `value`=? WHERE `key`=?", strconv.Itoa(DatabaseVersion), "version")
   149  		if err != nil {
   150  			return fmt.Errorf("failed to save updated database version: %s", err)
   151  		}
   152  	}
   153  
   154  	return nil
   155  }
   156  
   157  func (d *Database) Track(results ...*MatchResult) error {
   158  	d.Lock()
   159  	defer d.Unlock()
   160  
   161  	for i := range results {
   162  		_, err := d.db.Exec("INSERT INTO "+DatabasePrefix+"match (`timestamp`, `game`, `match`, `player`, `outcome`) VALUES (?, ?, ?, ?, ?)", results[i].Timestamp, results[i].Game, results[i].Match, results[i].Player, results[i].Outcome)
   163  		if err != nil {
   164  			return fmt.Errorf("failed to track match %+v: %s", results[i], err)
   165  		}
   166  	}
   167  
   168  	return nil
   169  }
   170  
   171  // TODO Allow -1 player to select all
   172  func (d *Database) Matches(game, player int) ([]*MatchResult, error) {
   173  	d.Lock()
   174  	defer d.Unlock()
   175  
   176  	rows, err := d.db.Query("SELECT * FROM "+DatabasePrefix+"match WHERE `game`=? AND `player`=?", game, player)
   177  	if err != nil {
   178  		return nil, fmt.Errorf("failed to fetch matches: %s", err)
   179  	}
   180  	defer rows.Close()
   181  
   182  	var matches []*MatchResult
   183  	for rows.Next() {
   184  		match, err := d.scanMatch(rows)
   185  		if err != nil {
   186  			return nil, fmt.Errorf("failed to scan matches: %s", err)
   187  		}
   188  
   189  		matches = append(matches, match)
   190  	}
   191  
   192  	return matches, nil
   193  }
   194  
   195  func (d *Database) Player(game, player int) (*Player, error) {
   196  	d.Lock()
   197  	defer d.Unlock()
   198  
   199  	p := &Player{}
   200  
   201  	rows, err := d.db.Query("SELECT COUNT(*) as c FROM "+DatabasePrefix+"match WHERE `game`=? AND `player`=? AND `outcome`=?", game, player, Won)
   202  	if err != nil {
   203  		return nil, fmt.Errorf("failed to fetch player: %s", err)
   204  	}
   205  	for rows.Next() {
   206  		err := rows.Scan(&p.Wins)
   207  		if err != nil {
   208  			rows.Close()
   209  			return nil, fmt.Errorf("failed to fetch player: %s", err)
   210  		}
   211  	}
   212  	rows.Close()
   213  
   214  	rows, err = d.db.Query("SELECT COUNT(*) as c FROM "+DatabasePrefix+"match WHERE `game`=? AND `player`=? AND `outcome`=?", game, player, Drew)
   215  	if err != nil {
   216  		return nil, fmt.Errorf("failed to fetch player: %s", err)
   217  	}
   218  	for rows.Next() {
   219  		err := rows.Scan(&p.Draws)
   220  		if err != nil {
   221  			rows.Close()
   222  			return nil, fmt.Errorf("failed to fetch player: %s", err)
   223  		}
   224  	}
   225  	rows.Close()
   226  
   227  	rows, err = d.db.Query("SELECT COUNT(*) as c FROM "+DatabasePrefix+"match WHERE `game`=? AND `player`=? AND `outcome`=?", game, player, Lost)
   228  	if err != nil {
   229  		return nil, fmt.Errorf("failed to fetch player: %s", err)
   230  	}
   231  	for rows.Next() {
   232  		err := rows.Scan(&p.Losses)
   233  		if err != nil {
   234  			rows.Close()
   235  			return nil, fmt.Errorf("failed to fetch player: %s", err)
   236  		}
   237  	}
   238  	rows.Close()
   239  
   240  	return p, nil
   241  }
   242  
   243  func (d *Database) Versus(game, player, versus int) (int, int, int, error) {
   244  	d.Lock()
   245  	defer d.Unlock()
   246  
   247  	playerMatches := make(map[int]bool)
   248  	versusMatches := make(map[int]bool)
   249  
   250  	var matchID int
   251  	var matchPlayer int
   252  
   253  	rows, err := d.db.Query("SELECT id, player FROM "+DatabasePrefix+"match WHERE `game`=? AND (`player`=? OR `player`=?)", game, player, versus)
   254  	if err != nil {
   255  		return 0, 0, 0, fmt.Errorf("failed to fetch player: %s", err)
   256  	}
   257  	for rows.Next() {
   258  		err := rows.Scan(&matchID, &matchPlayer)
   259  		if err != nil {
   260  			rows.Close()
   261  			return 0, 0, 0, fmt.Errorf("failed to fetch player: %s", err)
   262  		}
   263  
   264  		if matchPlayer == player {
   265  			playerMatches[matchID] = true
   266  		} else {
   267  			versusMatches[matchID] = true
   268  		}
   269  	}
   270  	rows.Close()
   271  
   272  	var matches []int
   273  	for matchID := range playerMatches {
   274  		if versusMatches[matchID] {
   275  			matches = append(matches, matchID)
   276  		}
   277  	}
   278  
   279  	matches = uniqueInts(matches)
   280  
   281  	var wins, draws, losses int
   282  	var addWins, addDraws, addLosses int
   283  
   284  	for match := range matches {
   285  		rows, err := d.db.Query("SELECT COUNT(*) as c FROM "+DatabasePrefix+"match WHERE `game`=? AND `match`=? AND `player`=? AND `outcome`=?", game, match, player, Won)
   286  		if err != nil {
   287  			return 0, 0, 0, fmt.Errorf("failed to fetch player: %s", err)
   288  		}
   289  		for rows.Next() {
   290  			err := rows.Scan(&addWins)
   291  			if err != nil {
   292  				rows.Close()
   293  				return 0, 0, 0, fmt.Errorf("failed to fetch player: %s", err)
   294  			}
   295  			wins += addWins
   296  		}
   297  		rows.Close()
   298  
   299  		rows, err = d.db.Query("SELECT COUNT(*) as c FROM "+DatabasePrefix+"match WHERE `game`=? AND `player`=? AND `outcome`=?", game, player, Drew)
   300  		if err != nil {
   301  			return 0, 0, 0, fmt.Errorf("failed to fetch player: %s", err)
   302  		}
   303  		for rows.Next() {
   304  			err := rows.Scan(&addDraws)
   305  			if err != nil {
   306  				rows.Close()
   307  				return 0, 0, 0, fmt.Errorf("failed to fetch player: %s", err)
   308  			}
   309  			draws += addDraws
   310  		}
   311  		rows.Close()
   312  
   313  		rows, err = d.db.Query("SELECT COUNT(*) as c FROM "+DatabasePrefix+"match WHERE `game`=? AND `player`=? AND `outcome`=?", game, player, Lost)
   314  		if err != nil {
   315  			return 0, 0, 0, fmt.Errorf("failed to fetch player: %s", err)
   316  		}
   317  		for rows.Next() {
   318  			err := rows.Scan(&addLosses)
   319  			if err != nil {
   320  				rows.Close()
   321  				return 0, 0, 0, fmt.Errorf("failed to fetch player: %s", err)
   322  			}
   323  			losses += addLosses
   324  		}
   325  		rows.Close()
   326  	}
   327  
   328  	return wins, draws, losses, nil
   329  }
   330  
   331  func (d *Database) scanMatch(rows *sql.Rows) (*MatchResult, error) {
   332  	m := &MatchResult{}
   333  	err := rows.Scan(&m.ID, &m.Timestamp, &m.Game, &m.Match, &m.Player, &m.Outcome)
   334  	if err != nil {
   335  		return nil, fmt.Errorf("failed to scan match: %s", err)
   336  	}
   337  
   338  	return m, nil
   339  }
   340  

View as plain text