database

package
v1.1.1 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Jul 29, 2025 License: AGPL-3.0 Imports: 30 Imported by: 0

Documentation

Overview

sqlite database management

Copyright (c) 2025 Chakib Ben Ziane <contact@blob42.xyz> and [`gosuki` contributors](https://github.com/blob42/gosuki/graphs/contributors). All rights reserved.

SPDX-License-Identifier: AGPL-3.0-or-later

This file is part of GoSuki.

GoSuki is free software: you can redistribute it and/or modify it under the terms of the GNU Affero General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

GoSuki is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more details.

You should have received a copy of the GNU Affero General Public License along with gosuki. If not, see <http://www.gnu.org/licenses/>.

Index

Constants

View Source 
const (
	CacheName   = "memcache"
	L2CacheName = "memcache_l2"
	//MemcacheFmt = "file:%s?mode=memory&cache=shared"
	//BufferFmt   = "file:%s?mode=memory&cache=shared"
	DBTypeInMemoryDSN = "file:%s?mode=memory&cache=shared&_journal=MEMORY"
	DBTypeCacheDSN    = DBTypeInMemoryDSN
)
View Source 
const (
	DBFileName = "gosuki.db"

	DBTypeFileDSN = "file:%s"

	// Opening DBs with this driver allows to track connections
	// This is used to perform sqlite backup
	DriverBackupMode = "sqlite_hook_backup"

	GosukiMainTable = "bookmarks"
)
View Source 
const (
	WhereQueryBookmarks = `
	URL like '%%%s%%' OR metadata like '%%%s%%' OR tags like '%%%s%%'
	`

	WhereQueryBookmarksFuzzy = `
	fuzzy('%s', URL) OR fuzzy('%s', metadata) OR fuzzy('%s', tags)
	`

	WhereQueryBookmarksByTag = `
		(URL LIKE '%%%s%%' OR metadata LIKE '%%%s%%') AND tags LIKE '%%%s%%'
	`
	WhereQueryBookmarksByTagFuzzy = `
		(fuzzy('%s', URL) OR fuzzy('%s', metadata)) AND tags LIKE '%%%s%%'
	`

	QQueryPaginate = ` LIMIT %d OFFSET %d`
)
View Source 
const (

	// metadata: name or title of resource
	// modified: time.Now().Unix()
	// desc:
	// flags: designed to be extended in future using bitwise masks
	// Masks:
	//     0b00000001: set title immutable ((do not change title when updating the bookmarks from the web ))
	QCreateSchema = `` /* 302-byte string literal not displayed */

	// The following view and and triggers provide buku compatibility
	QCreateView = `CREATE VIEW bookmarks AS
	SELECT id, URL, metadata, tags, desc, flags
	FROM gskbookmarks`

	QCreateInsertTrigger = `` /* 326-byte string literal not displayed */

	QCreateUpdateTrigger = `` /* 574-byte string literal not displayed */

	QCreateSchemaVersion = `
		CREATE TABLE IF NOT EXISTS schema_version (
			version INTEGER PRIMARY KEY
		)
	`
)
View Source 
const CurrentSchemaVersion = 2
View Source 
const TagSep = ","

Default separator used to join tags in the DB

Variables

View Source 
var (
	Cache   = &CacheDB{}
	L2Cache = &CacheDB{}
)

Global in-memory cache hierarchy for the gosuki database, structured in two levels to optimize performance and reduce unnecessary I/O operations.

Cache (level 1 cache) serves as a working buffer that aggregates and merges data from all scanned bookmarks. It acts as the primary cache for real-time operations and is periodically synchronized with L2Cache.

L2Cache (level 2 cache) functions as a persistent memory mirror of the on-disk database (gosuki.db). It is updated as a final step after level 1 cache synchronizations from module buffers, enabling checksum-based comparison between levels to detect changes and avoid redundant updates. This ensures efficient data consistency checks and minimizes write operations to the underlying storage.

The two-level architecture balances speed (level 1) with data integrity (level 2), while L2Cache maintains a faithful in-memory replica of the on-disk database state.

View Source 
var (

	// Default sqlite3 driver
	DriverDefault = "sqlite3_gosuki"
)
View Source 
var (
	ErrVfsLocked = errors.New("vfs locked")
)

Functions

func CountTotalBookmarks 

func CountTotalBookmarks(ctx context.Context) (uint, error)

func DebugPrintRow 

func DebugPrintRow(rows *sql.Rows)

Print debug a single row (does not run rows.next())

func DebugPrintRows 

func DebugPrintRows(rows *sql.Rows)

Print debug Rows results

func DotxQuery 

func DotxQuery(file string) (*dotsqlx.DotSqlx, error)

Loads a dotsql <file> and, wraps it with dotsqlx

func DotxQueryEmbedFS 

func DotxQueryEmbedFS(fs embed.FS, filename string) (*dotsqlx.DotSqlx, error)

Loads a dotsql from an embedded FS

func GetDBDir 

func GetDBDir() string

Get database directory path

func GetDBFullPath 

func GetDBFullPath() string

func GetDBPath 

func GetDBPath() string

func Init 

func Init()

func InitDiskConn 

func InitDiskConn(dbPath string) error

Initialize the connection to ondisk gosuki db

func LoadBookmarks 

func LoadBookmarks(load loadFunc, modName string) error

internal loading function called by modules

func RegisterSqliteHooks 

func RegisterSqliteHooks()

RegisterSqliteHooks registers a SQLite backup hook with additional connection tracking.

func SQLFuncFoo 

func SQLFuncFoo(in string) string

Testing custom func

func SQLFuzzy 

func SQLFuzzy(test, in string) bool

func SQLxxHash added in v1.1.0 

func SQLxxHash(in string) string

func ScheduleBackupToDisk added in v1.1.0 

func ScheduleBackupToDisk()

func SyncTreeToBuffer 

func SyncTreeToBuffer(node *Node, buffer *DB)

func SyncURLIndexToBuffer 

func SyncURLIndexToBuffer(urls []string, index Index, buffer *DB)

Types

type Bookmark 

type Bookmark = gosuki.Bookmark

type CacheDB 

type CacheDB struct {
	*DB
}

func GetCacheDB 

func GetCacheDB() *CacheDB

func (*CacheDB) IsInitialized 

func (c *CacheDB) IsInitialized() bool

type DB 

type DB struct {
	Name       string
	Path       string
	Handle     *sqlx.DB
	EngineMode string
	AttachedTo []string
	Type       DBType

	SQLXOpener
	LockChecker
	// contains filtered or unexported fields
}

DB encapsulates an sql.DB struct. All interactions with memory/buffer and disk databases are done through the DB instance. 

var (

	//TEST: expanded in init()
	DefaultDBPath = "~/.local/share/gosuki/"

	// Handle to on-disk gosuki database
	DiskDB *DB
)

func NewBuffer 

func NewBuffer(name string) (*DB, error)

A Buffer is an in-memory sqlite database holding the current state of parsed bookmarks within a specific module. Buffers act as temporary, per-module storage that aggregates data before synchronizing with the Level 1 Cache (Cache). This decouples module processing from the global cache hierarchy, enabling efficient batching of updates and reducing contention. Buffers are ephemeral and exist only for the duration of module operations, with their contents periodically flushed and mereged into the Level 1 Cache to propagate changes upward in the two-level architecture. This design ensures minimal I/O overhead while maintaining consistency through checksum-based comparisons between cache levels.

func NewDB 

func NewDB(name string, dbPath string, dbFormat string, opts ...DsnOptions) *DB

dbPath is empty string ("") when using in memory sqlite db Call to Init() required before using

func (*DB) Attach added in v1.1.0 

func (db *DB) Attach(attached *DB) error

func (*DB) Close 

func (db *DB) Close() error

func (*DB) CopyTo 

func (src *DB) CopyTo(dst *DB, dstName, srcName string)

Copy from src DB to dst DB using sqlite3 backup mode `dst` is overwritten

func (*DB) CountRows 

func (db *DB) CountRows(table string) int

func (*DB) Init 

func (db *DB) Init() (*DB, error)

We should export Open() in its own method and wrap with interface so we can mock it and test the lock status in Init() Initialize a sqlite database with Gosuki Schema if not already done

func (*DB) InitSchema 

func (db *DB) InitSchema() error

func (*DB) InsertBookmark 

func (db *DB) InsertBookmark(bk *Bookmark)

Inserts a bookmarks to DB. In case of conflict follow the default rules which for sqlite is a fail with the error `sqlite3.ErrConstraint` DEAD:

func (*DB) IsEmpty 

func (db *DB) IsEmpty() (bool, error)

func (*DB) Locked 

func (db *DB) Locked() (bool, error)

func (*DB) PrintBookmarks 

func (db *DB) PrintBookmarks() error

func (*DB) SyncFromDisk 

func (dst *DB) SyncFromDisk(dbpath string) error

func (*DB) SyncTo 

func (src *DB) SyncTo(dst *DB)
SyncTo synchronizes bookmarks from the source DB (src) to the destination DB (dst).

It performs the following steps:

  1. Reads all entries from src's gskbookmarks table
  2. Attempts to insert each entry into dst's gskbookmarks table
  3. For existing entries (due to URL constraints), captures their hashes and processes them in a second transaction for potential updates
  4. Updates existing entries only if there are changes in metadata, tags, or description
  5. Commits transactions for both insert and update phases
  6. If dst is a memcache, schedules a disk backup after completion

The synchronization uses SQLite transactions for consistency and handles duplicate URL constraints by comparing hash values. Tags are merged and normalized during updates.

func (*DB) SyncToCache 

func (src *DB) SyncToCache() error

func (*DB) UpsertBookmark 

func (db *DB) UpsertBookmark(bk *Bookmark) error

Inserts or updates a bookmark in the target database. If a bookmark with the same URL already exists due to a constraint, the existing entry is updated with the new data. NOTE: We don't use sql UPSERT as we need to do a manual merge of some columns such as `tags`.

type DBConfig 

type DBConfig struct {
	SyncInterval time.Duration `toml:"sync-interval" mapstructure:"sync-interval"`
	DBPath       string        `toml:"db-path" mapstructure:"db-path"`
}

type DBError 

type DBError struct {
	// Database object where error occured
	DBName string

	// Error that occured
	Err error
}

func DBErr 

func DBErr(dbName string, err error) DBError

func (DBError) Error 

func (e DBError) Error() string

type DBType 

type DBType int
const (
	DBTypeInMemory DBType = iota
	DBTypeRegularFile
)
const (
	DBGosuki DBType = iota
	DBForeign
)

Differentiate between gosukidb.sqlite and other sqlite DBs

type DsnOptions 

type DsnOptions map[string]string

type Index 

type Index = *hashmap.RBTree

This is a RedBlack Tree Hashmap that holds in memory the last state of the bookmark tree.It is used as fast db queries. Each URL holds a pointer to a node in [nodeTree]

type LockChecker 

type LockChecker interface {
	Locked() (bool, error)
}

type Node 

type Node = tree.Node

URLTree Node

type Opener 

type Opener interface {
	Open(driver string, dsn string) error
}

type PaginationParams 

type PaginationParams struct {
	Page int
	Size int
}

func DefaultPagination 

func DefaultPagination() *PaginationParams

type QueryResult 

type QueryResult struct {
	Bookmarks []*gosuki.Bookmark
	Total     uint
}

func BookmarksByTag 

func BookmarksByTag(
	ctx context.Context,
	tag string,
	pagination *PaginationParams,
) (*QueryResult, error)

func ListBookmarks 

func ListBookmarks(
	ctx context.Context,
	pagination *PaginationParams,
) (*QueryResult, error)

func QueryBookmarks 

func QueryBookmarks(
	ctx context.Context,
	query string,
	fuzzy bool,
	pagination *PaginationParams,
) (*QueryResult, error)

func QueryBookmarksByTag 

func QueryBookmarksByTag(
	ctx context.Context,
	query,
	tag string,
	fuzzy bool,
	pagination *PaginationParams,
) (*QueryResult, error)

type RawBookmark 

type RawBookmark struct {
	ID  uint64
	URL string `db:"URL"`

	// Usually used for the bookmark title
	Metadata string

	Tags string
	Desc string

	// Last modified
	Modified uint64

	// kept for buku compat, not used for now
	Flags int

	Module string

	// currently not used
	XHSum string
}

type RawBookmarks 

type RawBookmarks []*RawBookmark

func (RawBookmarks) AsBookmarks 

func (raws RawBookmarks) AsBookmarks() []*gosuki.Bookmark

type SQLXDBOpener 

type SQLXDBOpener struct {
	// contains filtered or unexported fields
}

func (*SQLXDBOpener) Get 

func (o *SQLXDBOpener) Get() *sqlx.DB

func (*SQLXDBOpener) Open 

func (o *SQLXDBOpener) Open(driver string, dataSourceName string) error

type SQLXOpener 

type SQLXOpener interface {
	Opener
	Get() *sqlx.DB
}

type Tags 

type Tags struct {
	// contains filtered or unexported fields
}

func NewTags 

func NewTags(tags []string, delim string) *Tags

Reads tags from a slice of strings

func TagsFromString 

func TagsFromString(s, delim string) *Tags

Builds a list of tags from a string as a Tags struct. It also removes empty tags

func (*Tags) Add 

func (t *Tags) Add(tag string)

func (*Tags) Extend 

func (t *Tags) Extend(tags []string) *Tags

func (*Tags) Get 

func (t *Tags) Get() []string

Returns the list of tags as slice

func (*Tags) PreSanitize 

func (t *Tags) PreSanitize() *Tags

Sanitize the list of tags before saving them to the DB

func (*Tags) Sort added in v1.1.0 

func (t *Tags) Sort() *Tags

Sorts the tags in the same order, order does not matter

func (Tags) String 

func (t Tags) String(wrap bool) string

String representation of the tags. It can wrap the tags with the delim if wrap is true. This is done for compatibility with Buku DB format.

func (Tags) StringWrap 

func (t Tags) StringWrap() string

String representation of the tags. It wraps the tags with the delim.

type VFSLockChecker 

type VFSLockChecker struct {
	// contains filtered or unexported fields
}

func (*VFSLockChecker) Locked 

func (checker *VFSLockChecker) Locked() (bool, error)

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.