craban/server/services/store/store.go

package store

import (
	"database/sql"
	"fmt"
	"strings"

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

type Store struct {
	Path string
	Conn *sql.DB

	userStore *UserStore
	gameStore *GameStore
	postStore *PostStore
}

func addPathOptions(path string) string {
	if !strings.HasPrefix(path, ":memory:") {
		path = "file:" + path
	}
	return fmt.Sprintf("%s?_foreign_keys=true", path)
}

func NewStore(path string) (*Store, error) {
	s := &Store{Path: path}

	// ToDo: should do this at start time instead of create time?
	conn, err := sql.Open("sqlite3", addPathOptions(path))
	if err != nil {
		return nil, err
	}
	s.Conn = conn

	if err := s.EnsureSchema(); err != nil {
		return nil, err
	}

	// init stores
	s.userStore = NewUserStore(s)
	s.gameStore = NewGameStore(s)
	s.postStore = NewPostStore(s)

	return s, nil
}

func (s *Store) Close() error {
	return s.Conn.Close()
}

func (s *Store) User() *UserStore {
	return s.userStore
}

func (s *Store) Game() *GameStore {
	return s.gameStore
}

func (s *Store) Post() *PostStore {
	return s.postStore
}

func (s *Store) EnsureSchema() error {
	schema := `
CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(255) NOT NULL,
  mail VARCHAR(255) UNIQUE NOT NULL,
  username VARCHAR(255) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  admin BOOLEAN NOT NULL
);

CREATE TABLE IF NOT EXISTS games (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS gamemembers (
  game_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  role VARCHAR(255) NOT NULL,
  lastviewedat INTEGER NOT NULL,

  PRIMARY KEY (game_id, user_id)
);

CREATE TABLE IF NOT EXISTS posts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  game_id INTEGER NOT NULL,
  createdat INTEGER NOT NULL,
  body TEXT NOT NULL
);
`

	_, err := s.Conn.Exec(schema)
	return err
}