db

README

* Overview

This is the data layer of Armaria. This document briefly covers some design decisions that were made about it.

* ERD

Here is the ERD for the Armaria database:

#+begin_src mermaid :file "bookmarks-db.svg" :pupeteer-config-file "~/.emacs.d/pupeteer-config.json" :mermaid-config-file "~/.emacs.d/mermaid-config.json" :background-color "transparent"
erDiagram
  bookmarks ||--|{ bookmarks_tags: ""
  tags ||--|{ bookmarks_tags: ""
  bookmarks o|--o{ bookmarks: ""
  
  bookmarks {
    text id
    text parent_id
    integer is_folder
    text name
    text url
    text description
    text modified
    text order
  }

  tags {
    text tag
    text modified
  }

  bookmarks_tags {
    text bookmark_id
    text tag
    text modified
  }
#+end_src

#+RESULTS:
[[file:bookmarks-db.svg]]

* Hierarchical Data

Armaria supports folders which is to say the bookmarks can be placed in a hierarchical layout (or even more simply a tree). I spent some time reading about the different ways you can implement this in SQL. The best references I found were these:

 - [[https://vadimtropashko.wordpress.com/2008/08/09/one-more-nested-intervals-vs-adjacency-list-comparison/?utm_source=pocket_reader][One more Nested Intervals vs. Adjacency List comparison]]
 - [[https://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database][What are the options for storing hierarchical data in a relational database?]]

I ended up going with the one that is the most widely used: *Adjacency List*.

For those unfamiliar this approach just means you use a parent ID column to create the hierarchy.

While there are more efficient approaches in some cases everything about adjacency lists is very easy to reason about, and reasonably efficient assuming your database supports recursive CTEs (which SQLite does).

* Search

Search was always a requirement for Armaria.

In the past I've used the full text search offerings of many different databases. Unfortunately I've always found them very finicky. For small amounts of data (say less than a million) what is often most intuitive is a simple double wildcard: ~WHERE "col" LIKE %query%~. This is of course wildly non-performant by default. Thankfully SQLite supports something called [[https://en.wikipedia.org/wiki/Trigram_search][trigram search]].

With trigram search that double wildcard filter above becomes performant. One upstream caveat to be aware of is that the minimum size of the query is 3 chars.

* Pagination

Anytime you are designing something that returns a list of things from the database you need to consider if you need pagination. In the case of Armaria I figured it would be a good idea to support it from the start so it can maintain performance for larger Armarias in more use cases.

There are multiple approaches you can take with pagination, but these days it's mostly done with cursors. With the cursor approach you paginate by specifying the number of results you want, how you want those results ordered, and possibly the ID of a row to return results before or after. By making pagination relative like this you can maintain excellent performance even for larger amounts of data.

A good reference on implementing cursor bases pagination can be found here:
https://brunoscheufler.com/blog/2022-01-01-paginating-large-ordered-datasets-with-cursor-based-pagination

Documentation

Overview

db contains the low level database logic access for Armaria

Index

• func AddBook(tx Transaction, url string, name string, description null.NullString, ...) (string, error)
• func AddFolder(tx Transaction, name string, parentID null.NullString, order string) (string, error)
• func AddTags(tx Transaction, tags []string) error
• func BookFolderExists(tx Transaction, ID string, isFolder bool) (bool, error)
• func CleanOrphanedTags(tx Transaction, tags []string) error
• func ExecWithTransaction(inputPath null.NullString, configPath string, execFn ExecTxFn) (err error)
• func GetBookFolderParents(tx Transaction, ID string) ([]string, error)
• func GetBooks(tx Transaction, args GetBooksArgs) ([]armaria.Book, error)
• func GetParentAndChildren(tx Transaction, ID string) ([]armaria.Book, error)
• func GetTags(tx Transaction, args GetTagsArgs) ([]string, error)
• func LinkTags(tx Transaction, bookmarkID string, tags []string) error
• func MaxOrder(tx Transaction, parentID null.NullString) (string, error)
• func QueryWithDB[T any](inputPath null.NullString, configPath string, queryFn QueryTxFn[T]) (T, error)
• func QueryWithTransaction[T any](inputPath null.NullString, configPath string, queryFn QueryTxFn[T]) (val T, err error)
• func RemoveBook(tx Transaction, ID string) error
• func RemoveFolder(tx Transaction, ID string) error
• func UnlinkTags(tx Transaction, ID string, tags []string) error
• func UpdateBook(tx Transaction, ID string, args UpdateBookArgs) error
• func UpdateFolder(tx Transaction, ID string, args UpdateFolderArgs) error
• type ExecTxFn
• type GetBooksArgs
• type GetTagsArgs
• type QueryTxFn
• type Transaction
• type UpdateBookArgs
• type UpdateFolderArgs

Functions

func AddBook

func AddBook(tx Transaction, url string, name string, description null.NullString, parentID null.NullString, order string) (string, error)

AddBook inserts a book into the bookmarks database.

func AddFolder

func AddFolder(tx Transaction, name string, parentID null.NullString, order string) (string, error)

AddFolder inserts a folder into the bookmarks database.

func AddTags

func AddTags(tx Transaction, tags []string) error

AddTags inserts tags into the bookmarks database.

func BookFolderExists

func BookFolderExists(tx Transaction, ID string, isFolder bool) (bool, error)

BookFolderExists returns true if the target book or folder exists.

func CleanOrphanedTags

func CleanOrphanedTags(tx Transaction, tags []string) error

CleanOrphanedTags removes any tags that aren't applied to a bookmark.

func ExecWithTransaction

func ExecWithTransaction(inputPath null.NullString, configPath string, execFn ExecTxFn) (err error)

execWithTransaction creates a scope for functions that operate on a transaction which doesn't return results. Handles connecting to the DB, creating the transaction, committing/rolling back, and closing the connection. Will also handle creating the DB if it doesn't exist and applying missing migrations to it.

func GetBookFolderParents

func GetBookFolderParents(tx Transaction, ID string) ([]string, error)

GetBookFolderParents returns the parent names of a bookmark or folder.

func GetBooks

func GetBooks(tx Transaction, args GetBooksArgs) ([]armaria.Book, error)

GetBooks lists bookmarks/folders in the bookmarks DB.

func GetParentAndChildren

func GetParentAndChildren(tx Transaction, ID string) ([]armaria.Book, error)

GetParentAndChildren gets a parent and all of its children.

func GetTags

func GetTags(tx Transaction, args GetTagsArgs) ([]string, error)

GetTags lists tags in the bookmarks DB.

func LinkTags

func LinkTags(tx Transaction, bookmarkID string, tags []string) error

LinkTags adds tags to bookmark.

func MaxOrder

func MaxOrder(tx Transaction, parentID null.NullString) (string, error)

MaxOrder returns the max order for a given parentID.

func QueryWithDB

func QueryWithDB[T any](inputPath null.NullString, configPath string, queryFn QueryTxFn[T]) (T, error)

QueryWithDB creates a scope for for functions that operate on a database connection which return results. Handles connecting to the DB and closing the connection. Will also handle creating the DB if it doesn't exist and applying missing migrations to it.

func QueryWithTransaction

func QueryWithTransaction[T any](inputPath null.NullString, configPath string, queryFn QueryTxFn[T]) (val T, err error)

queryWithTransaction creates a scope for functions that operate on a transaction which returns results. Handles connecting to the DB, creating the transaction, committing/rolling back, and closing the connection. Will also handle creating the DB if it doesn't exist and applying missing migrations to it.

func RemoveBook

func RemoveBook(tx Transaction, ID string) error

RemoveBook deletes a bookmark from the bookmarks DB.

func RemoveFolder

func RemoveFolder(tx Transaction, ID string) error

RemoveFolder deletes a folder from the bookmarks DB.

func UnlinkTags

func UnlinkTags(tx Transaction, ID string, tags []string) error

UnlinkTags removes tags from a bookmark.

func UpdateBook

func UpdateBook(tx Transaction, ID string, args UpdateBookArgs) error

UpdateBook updates a book in the bookmarks database.

func UpdateFolder

func UpdateFolder(tx Transaction, ID string, args UpdateFolderArgs) error

UpdateFolder updates a folder in the bookmarks database.

Types

type ExecTxFn

type ExecTxFn func(Transaction) error

ExecTxFn is a function that operates on a transaction which doesn't return results.

type GetBooksArgs

type GetBooksArgs struct {
	IDFilter       string
	IncludeBooks   bool
	IncludeFolders bool
	ParentID       null.NullString
	Query          null.NullString
	Tags           []string
	After          null.NullString
	Order          armaria.Order
	Direction      armaria.Direction
	First          null.NullInt64
}

GetBooksArgs are the args for getBooksDB.

type GetTagsArgs

type GetTagsArgs struct {
	IDFilter   null.NullString
	TagsFilter []string
	Query      null.NullString
	After      null.NullString
	Direction  armaria.Direction
	First      null.NullInt64
}

GetTagsArgs are the args for getTagsDB.

type QueryTxFn

type QueryTxFn[T any] func(Transaction) (T, error)

QueryTxFn is a function that operates on a transaction which returns results.

type Transaction

type Transaction interface {
	Exec(query string, args ...interface{}) (sql.Result, error)
	Query(query string, args ...interface{}) (*sql.Rows, error)
	QueryRow(query string, args ...interface{}) *sql.Row
}

Transaction represents a database/sql connection.

type UpdateBookArgs

type UpdateBookArgs struct {
	Name        null.NullString
	URL         null.NullString
	Description null.NullString
	ParentID    null.NullString
	Order       string
}

UpdateBookArgs are the args for updateBookDB.

type UpdateFolderArgs

type UpdateFolderArgs struct {
	Name     null.NullString
	ParentID null.NullString
	Order    string
}

UpdateFolderArgs are the args for updateFolderDB.