Skip to content
Snippets Groups Projects
Backend.go 4.3 KiB
Newer Older
Markus Lutteropp's avatar
...
Markus Lutteropp committed
package sqlite

import (
	"database/sql"
	"fmt"
Markus Lutteropp's avatar
...
Markus Lutteropp committed

	_ "github.com/mattn/go-sqlite3"
	"git.lutteropp.dev/lutteropp.dev/commons/golang/log"
Markus Lutteropp's avatar
...
Markus Lutteropp committed
)

type SQLiteBackend struct {
	log.CommonBackend
	Tables          SQLiteTables
	connection      *sql.DB
	preparedInserts prepared
	preparedQueries prepared
	senderKeys      map[string]int
	severityKeys    map[string]int
}

type prepared struct {
	log        *sql.Stmt
	senders    *sql.Stmt
	severities *sql.Stmt
}

type sqlCache struct {
	id   int
	name string
Markus Lutteropp's avatar
...
Markus Lutteropp committed
}

func (back *SQLiteBackend) Initialize(dbpath string) error {
	back.senderKeys, back.severityKeys = make(map[string]int), make(map[string]int)
Markus Lutteropp's avatar
...
Markus Lutteropp committed
	dbconn, err := sql.Open("sqlite3", dbpath)
	if err != nil {
		return err
	}
	defer func() {
		back.connection = dbconn
	}()
	transaction, err := dbconn.Begin()
	if err != nil {
		return err
	}
	_, err = transaction.Exec(
		fmt.Sprintf(
Markus Lutteropp's avatar
...
Markus Lutteropp committed
			`
			CREATE TABLE IF NOT EXISTS "%[1]s" (
				"id"	INTEGER NOT NULL,
				"name"	TEXT NOT NULL,
				PRIMARY KEY("id" AUTOINCREMENT)
			);
			CREATE TABLE IF NOT EXISTS "%[2]s" (
				"id"	INTEGER NOT NULL,
				"name"	TEXT NOT NULL,
				PRIMARY KEY("id" AUTOINCREMENT)
			);
			CREATE TABLE IF NOT EXISTS "%[3]s" (
Markus Lutteropp's avatar
...
Markus Lutteropp committed
				"serial"	INTEGER NOT NULL,
				"timestamp"	TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
				"severity"	INTEGER NOT NULL,
				"sender"	INTEGER NOT NULL,
Markus Lutteropp's avatar
...
Markus Lutteropp committed
				"message"	TEXT NOT NULL,
				FOREIGN KEY("sender") REFERENCES "sender"("id"),
				PRIMARY KEY("serial" AUTOINCREMENT),
				FOREIGN KEY("severity") REFERENCES "severity"("id")
			);
			CREATE UNIQUE INDEX IF NOT EXISTS "%[1]s_name_uidx" ON "%[1]s" (
				"name"	ASC
			);
			CREATE UNIQUE INDEX IF NOT EXISTS "%[2]s_name_uidx" ON "%[2]s" (
				"name"	ASC
Markus Lutteropp's avatar
...
Markus Lutteropp committed
			);
			CREATE INDEX IF NOT EXISTS "%[3]s_timestamp_idx" ON "%[3]s" (
Markus Lutteropp's avatar
...
Markus Lutteropp committed
				"timestamp"	ASC
			);
			CREATE INDEX IF NOT EXISTS "%[3]s_severity_idx" ON "%[3]s" (
Markus Lutteropp's avatar
...
Markus Lutteropp committed
				"severity"	ASC
			);
			CREATE INDEX IF NOT EXISTS "%[3]s_sender_idx" ON "%[3]s" (
Markus Lutteropp's avatar
...
Markus Lutteropp committed
				"sender"	ASC
			);
			CREATE VIEW IF NOT EXISTS "%[3]s_readable" AS
				SELECT
					"%[3]s"."serial",
					"%[3]s"."timestamp",
					"%[2]s"."name" AS "severity",
					"%[1]s"."name" AS "sender",
					"%[3]s"."message"
				FROM "%[3]s"
					LEFT JOIN "%[2]s" ON "%[3]s"."severity" == "%[2]s"."id"
					LEFT JOIN "%[1]s" ON "%[3]s"."sender" == "%[1]s"."id";
Markus Lutteropp's avatar
...
Markus Lutteropp committed
			`,
			back.Tables.Senders,
			back.Tables.Severities,
Markus Lutteropp's avatar
...
Markus Lutteropp committed
			back.Tables.Log,
		),
	)
	if err != nil {
		return err
	}
	err = transaction.Commit()
	if err != nil {
		return err
	}

	// Prepare statements for inserts
		fmt.Sprintf(
			"INSERT INTO \"%s\" (timestamp, sender, severity, message) VALUES (?, ?, ?, ?)",
			back.Tables.Log,
		),
	); err == nil {
		back.preparedInserts.log = statement
	} else {
		return err
	}
		fmt.Sprintf(
			"INSERT OR IGNORE INTO \"%s\" (name) VALUES (?)",
			back.Tables.Severities,
		),
	); err == nil {
		back.preparedInserts.severities = statement
	} else {
		return err
	}
		fmt.Sprintf(
			"INSERT OR IGNORE INTO \"%s\" (name) VALUES (?)",
			back.Tables.Senders,
		),
	); err == nil {
		back.preparedInserts.senders = statement
	} else {
		return err
	}

	// Prepare statements for queries
		fmt.Sprintf(
			"SELECT * FROM \"%s\" WHERE \"name\" = ?",
			back.Tables.Senders,
		),
	); err == nil {
		back.preparedQueries.senders = statement
	} else {
		return err
	}
		fmt.Sprintf(
			"SELECT * FROM \"%s\" WHERE \"name\" = ?",
			back.Tables.Severities,
		),
	); err == nil {
		back.preparedQueries.severities = statement
	} else {
		return err
	}

	if severityQuery, err := dbconn.Query(fmt.Sprintf("SELECT * FROM \"%s\"", back.Tables.Severities)); err == nil {
		for severityQuery.Next() {
			cache := sqlCache{}
			severityQuery.Scan(&cache.id, &cache.name)
			back.severityKeys[cache.name] = cache.id
		}
	} else {
		return err
	if senderQuery, err := dbconn.Query(fmt.Sprintf("SELECT * FROM \"%s\"", back.Tables.Senders)); err == nil {
		for senderQuery.Next() {
			cache := sqlCache{}
			senderQuery.Scan(&cache.id, &cache.name)
			back.senderKeys[cache.name] = cache.id
		}
	} else {
		return err
	return nil
Markus Lutteropp's avatar
...
Markus Lutteropp committed
}

// It is recommended to vacuum on program termination
func (back *SQLiteBackend) Vacuum() {
	back.connection.Exec("VACUUM;")
}