database

package
v1.2.1 Latest Latest
Warning

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

 Go to latest Published: Aug 9, 2025 License: AGPL-3.0 Imports: 35 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 = `` /* 485-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 = 3
View Source 
const TagSep = ","

TagSep is the default separator used to separate and wrap 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 (

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

	Config *dbConfig
)
View Source 
var (

	// Default sqlite3 driver
	DriverDefault = "sqlite3_gosuki"
)
View Source 
var (
	ErrVfsLocked = errors.New("vfs locked")
)
View Source 
var (
	SyncTrigger = atomic.Bool{}
)

Functions

func CountTotalBookmarks 

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

CountTotalBookmarks counts total bookmarks from disk

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 GetDBPath 

func GetDBPath() string

func Init 

func Init(ctx context.Context, cmd *cli.Command)

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.2.0 

func SQLxxHash(in string) string

func ScheduleBackupToDisk added in v1.2.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

func (*CacheDB) TotalBookmarks added in v1.2.0 

func (c *CacheDB) TotalBookmarks(ctx context.Context) (uint, error)

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.

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.2.0 

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

func (*DB) BackupToDisk added in v1.2.0 

func (src *DB) BackupToDisk(dbpath string) error

BackupToDisk copies the `src` database contents to a file on disk. It creates a backup of the source database (src) to the specified dbpath. The function is safe for concurrent use as it acquires a mutex. Returns an error if any step fails, including database connection issues, backup execution errors, or invalid configuration. Uses SQLite's backup API via the sqlx package, requiring the driver to support it.

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) DebugPrintBookmarks added in v1.2.0 

func (db *DB) DebugPrintBookmarks() error

func (*DB) GetDBClock added in v1.2.0 

func (db *DB) GetDBClock(ctx context.Context) (*LamportClock, error)

GetDBClock returns lamport clock for this node's db (version column)

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(ctx context.Context) error

func (*DB) IsEmpty 

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

func (*DB) Locked 

func (db *DB) Locked() (bool, 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 database to the destination database using the current Lamport clock value.

This function performs local-only synchronization by: 1. Reading all entries from the source database's gskbookmarks table 2. Attempting to insert each entry into the destination database's gskbookmarks table 3. Handling duplicate URL constraints by comparing hash values and updating existing entries 4. Maintaining versioning through Lamport clock synchronization 5. Scheduling disk backup when syncing to memcache

It is designed for local synchronization scenarios where causal ordering needs to be maintained using Lamport timestamps that can be used for multi-device synchronization.

Example usage: 

srcDB := NewDB("source.db")
dstDB := NewDB("destination.db")
srcDB.SyncTo(dstDB)

func (*DB) SyncToCache 

func (src *DB) SyncToCache() error

func (*DB) SyncToClock added in v1.2.0 

func (src *DB) SyncToClock(dst *DB, remoteClock uint64)

SyncToClock synchronizes bookmarks from the source DB (src) to the destination DB (dst) using Lamport clock synchronization for peer-to-peer consistency.

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. Lamport clock is used to maintain versioning and ensure proper ordering of operations in distributed systems.

Parameters:

  • dst: The destination database to sync bookmarks to
  • clock: The Lamport clock value to use for versioning

Behavior: - When syncing to L2 cache, increments the version field on successful inserts - Merges tags from both source and destination when updating existing entries - Normalizes merged tags by sorting them alphabetically - Only updates entries when there are actual changes in metadata, tags, or description - Schedules disk backup when syncing to memcache (CacheName) - Uses Lamport clock for p2p synchronization to maintain causal ordering

func (*DB) TotalBookmarks added in v1.2.0 

func (db *DB) TotalBookmarks(ctx context.Context) (uint, 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`. NOTE: This function is always called against a buffer db

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
)

type DsnOptions 

type DsnOptions map[string]string

type Index 

type Index = *hashmap.RBTree

Index 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 LamportClock added in v1.2.0 

type LamportClock struct {
	Value uint64
	// contains filtered or unexported fields
}
var (

	// lamport clock for this node
	Clock *LamportClock
)

func (*LamportClock) LocalTick added in v1.2.0 

func (c *LamportClock) LocalTick() uint64

func (*LamportClock) Tick added in v1.2.0 

func (c *LamportClock) Tick(peerClock uint64) uint64

type LockChecker 

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

type Node 

type Node = tree.Node

Node is a URLTree

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

	// lamport clock
	Version uint64

	// Node that made the change
	NodeID UUID `db:"node_id"`
}

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 (*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.2.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 UUID added in v1.2.0 

type UUID uuid.UUID

func (*UUID) Scan added in v1.2.0 

func (nodeID *UUID) Scan(value any) error

func (UUID) Value added in v1.2.0 

func (nodeID UUID) Value() (driver.Value, error)

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.