asgard/db.go

package main

import (
	"database/sql"
	"strings"
	"time"

	"github.com/emersion/go-imap"

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

func migrate() (*sql.DB, error) {
	db, err := open()
	_, err = db.Exec(`create table tyr_knownAddresses(address_from text, address_to text, ban boolean, unique(address, direction))`)
	if err != nil && err.Error() != "table tyr_knownAddresses already exists" {
		return nil, err
	}
	_, err = db.Exec(`create table tyr_locks(address text unique, token text, date date)`)
	if err != nil && err.Error() != "table tyr_locks already exists" {
		return nil, err
	}
	_, err = db.Exec(`create table mimir_archive(message_id text primary key, subject text, body text, date datetime, in_reply_to text, dkim_status bool, sender text, recipients text, root_id text, foreign key(in_reply_to) references mimir_archive(message_id))`)
	if err != nil && err.Error() != "table mimir_archive already exists" {
		return nil, err
	}

	return db, nil
}

func open() (*sql.DB, error) {
	db, err := sql.Open("sqlite3", "asgard.db")
	if err != nil {
		return nil, err
	}
	return db, nil
}

func getAddressLock(db *sql.DB, address string) (Lock, error) {
	address = strings.ToLower(address)
	lock := Lock{
		address: address,
	}
	row := db.QueryRow(`select token, date from tyr_locks where address = ?`, address)
	err := row.Scan(&lock.token, &lock.date)
	if err == sql.ErrNoRows {
		return Lock{}, nil
	} else {
		return lock, err
	}
}

func getLock(db *sql.DB, token string) (Lock, error) {
	lock := Lock{
		token: token,
	}
	row := db.QueryRow(`select address, date from tyr_locks where token = ?`, token)
	err := row.Scan(&lock.address, &lock.date)
	if err == sql.ErrNoRows {
		return Lock{}, nil
	} else {
		return lock, err
	}
}

func listLocks(db *sql.DB) ([]Lock, error) {
	locks := []Lock{}
	rows, err := db.Query(`select address, token from tyr_locks`)
	if err != nil {
		return locks, err
	}
	for rows.Next() {
		lock := Lock{}
		err := rows.Scan(&lock.address, &lock.token)
		if err != nil {
			return locks, err
		}
		locks = append(locks, lock)
	}
	return locks, nil
}

func insertLock(db *sql.DB, lock Lock) error {
	_, err := db.Exec(`insert into tyr_locks values(?, ?, ?) on
	                  conflict(address) do nothing`,
		lock.address, lock.token, lock.date)
	return err
}

func deleteLock(db *sql.DB, lock Lock) error {
	_, err := db.Exec(`delete from tyr_locks where address = ?`, lock.address)
	return err
}

func updateLock(db *sql.DB, lock Lock) error {
	_, err := db.Exec(`update tyr_locks set date = ? where address = ?`, lock.date, lock.address)
	return err
}

func getKnownAddress(db *sql.DB, address string) ([]KnownAddress, error) {
	knownAddresses := []KnownAddress{}

	rows, err := db.Query(`select address_from, address_to, ban from tyr_knownAddresses where address_from = ?`, address)
	if err != nil {
		return []KnownAddress{}, err
	}
	for rows.Next() {
		knownAddress := KnownAddress{}
		err := rows.Scan(&knownAddress.addressFrom, &knownAddress.addressTo, &knownAddress.ban)
		if err != nil {
			return []KnownAddress{}, err
		}
		knownAddresses = append(knownAddresses, knownAddress)
	}
	return knownAddresses, nil
}

func insertKnownAddress(db *sql.DB, address KnownAddress) error {
	_, err := db.Exec(`insert into tyr_knownAddresses values(?, ?, ?) on
	                  conflict(address_to, address_from) do nothing`,
		address.addressFrom, address.addressTo, address.ban)
	return err
}

func addArchiveEntry(db *sql.DB, messageID, category, subject string, body []byte, date time.Time, inReplyTo string, dkim bool, sender *imap.Address, recipients []*imap.Address) error {
	recipientsAddresses := []string{}
	for _, recipient := range recipients {
		recipientsAddresses = append(recipientsAddresses, recipient.Address())
	}
	recipientsJoined := strings.Join(recipientsAddresses, ", ")

	var rootID string
	row := db.QueryRow(`select root_id from mimir_archive where message_id = ?`, inReplyTo)
	err := row.Scan(&rootID)
	if err != nil {
		if err == (sql.ErrNoRows) {
			rootID = messageID
		} else {
			return err
		}
	}
	db.Exec(`insert inti mimir_archive values(?, ?, ?, ?, ?, ?, ?, ?, ?)`, messageID, subject, body, date, inReplyTo, dkim, sender, recipientsJoined, rootID)

	return nil
}