lucifer/database/sqlite/init.go

package sqlite

import (
	"log"
	"strings"

	"github.com/jmoiron/sqlx"

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

var db *sqlx.DB

// Initialize sets up the sqlite database.
func Initialize(filename string) (err error) {
	if db != nil {
		panic("sqlite.Initialize called twice!")
	}

	db, err = sqlx.Open("sqlite3", filename)
	if err != nil {
		db = nil
		return err
	}

	err = db.Ping()
	if err != nil {
		db.Close()
		db = nil
		return err
	}

	tableDefs := strings.Split(tableDefStr, ";")
	for i, tableDef := range tableDefs {
		query := strings.Trim(tableDef, "\n  ")
		if len(query) < 2 {
			continue
		}

		_, err := db.Exec(query)
		if err != nil {
			log.Printf("Statement %d failed", i)

			db.Close()
			db = nil
			return err
		}
	}

	return nil
}

const tableDefStr = `

CREATE TABLE IF NOT EXISTS "user" (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	name VARCHAR(64) NOT NULL UNIQUE,
	hash VARCHAR(128) NOT NULL
);

CREATE TABLE IF NOT EXISTS "session" (
	id CHAR(64) PRIMARY KEY,
	user_id INTEGER NOT NULL,
	expire_date DATE NOT NULL
);

CREATE TABLE IF NOT EXISTS "bridge" (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	internal_id VARCHAR(255) NOT NULL,
	name VARCHAR(255) NOT NULL,
	driver VARCHAR(255) NOT NULL,
	addr VARCHAR(255) NOT NULL,
	key BLOB NOT NULL
);

CREATE TABLE IF NOT EXISTS "light" (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	bridge_id INTEGER NOT NULL,
	internal_id VARCHAR(255) NOT NULL,
	group_id INTEGER NOT NULL,
	name VARCHAR(255) NOT NULL,
	enabled BOOLEAN NOT NULL,
	color VARCHAR(255) NOT NULL,
	brightness INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS "button" (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	bridge_id INTEGER NOT NULL,
	internal_index INTEGER NOT NULL,
	internal_id VARCHAR(255) NOT NULL,
	target_group_id INTEGER NOT NULL,
	name VARCHAR(255) NOT NULL,
	kind VARCHAR(255) NOT NULL,
	missing BOOLEAN NOT NULL,
	num_buttons INTEGER NOT NULL
);

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

CREATE TABLE IF NOT EXISTS "group_permission" (
	group_id INT NOT NULL,
	user_id INT NOT NULL,
	read BOOLEAN NOT NULL,
	write BOOLEAN NOT NULL,
	'create' BOOLEAN NOT NULL,
	'delete' BOOLEAN NOT NULL,
	manage BOOLEAN NOT NULL,

	PRIMARY KEY (group_id, user_id)
);

CREATE TABLE IF NOT EXISTS "group_light" (
	group_id INT NOT NULL,
	light_id INT NOT NULL,

	PRIMARY KEY (group_id, light_id)
);

REPLACE INTO "group" (id, name) VALUES (0, "Lonely Lights");
`