Newer
Older
"git.lutteropp.dev/lutteropp.dev/commons/golang/log"
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
}
func (back *SQLiteBackend) Initialize(dbpath string) error {
back.senderKeys, back.severityKeys = make(map[string]int), make(map[string]int)
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(
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" (
"serial" INTEGER NOT NULL,
"timestamp" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
"severity" INTEGER NOT NULL,
"sender" INTEGER 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
CREATE INDEX IF NOT EXISTS "%[3]s_timestamp_idx" ON "%[3]s" (
CREATE INDEX IF NOT EXISTS "%[3]s_severity_idx" ON "%[3]s" (
CREATE INDEX IF NOT EXISTS "%[3]s_sender_idx" ON "%[3]s" (
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";
back.Tables.Senders,
back.Tables.Severities,
back.Tables.Log,
),
)
if err != nil {
return err
}
err = transaction.Commit()
if err != nil {
return err
}
// Prepare statements for inserts
if statement, err := dbconn.Prepare(
fmt.Sprintf(
"INSERT INTO \"%s\" (timestamp, sender, severity, message) VALUES (?, ?, ?, ?)",
back.Tables.Log,
),
); err == nil {
back.preparedInserts.log = statement
} else {
return err
}
if statement, err := dbconn.Prepare(
"INSERT OR IGNORE INTO \"%s\" (name) VALUES (?)",
back.Tables.Severities,
),
); err == nil {
back.preparedInserts.severities = statement
} else {
return err
}
if statement, err := dbconn.Prepare(
"INSERT OR IGNORE INTO \"%s\" (name) VALUES (?)",
back.Tables.Senders,
),
); err == nil {
back.preparedInserts.senders = statement
} else {
return err
}
// Prepare statements for queries
if statement, err := dbconn.Prepare(
fmt.Sprintf(
"SELECT * FROM \"%s\" WHERE \"name\" = ?",
back.Tables.Senders,
),
); err == nil {
back.preparedQueries.senders = statement
} else {
return err
}
if statement, err := dbconn.Prepare(
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
}
// It is recommended to vacuum on program termination
func (back *SQLiteBackend) Vacuum() {
back.connection.Exec("VACUUM;")
}