sqlite

package
v0.0.0-...-f6c473f Latest Latest
Warning

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

 Go to latest Published: Mar 28, 2026 License: MIT, MIT Imports: 34 Imported by: 0

README

Database — SQLite (Client-side schema)

Purpose

  • SQLite is used only on the client side (desktop app). It prioritizes local usability, soft deletes, and storing server linkage material (uid and full API key) alongside logbook metadata.

Key differences vs Postgres (server)

  • Deletes
    • SQLite: soft deletes are supported via deleted_at on both logbook and qso.
    • Postgres: hard deletes with cascading from logbook to QSOs and API keys.
  • API keys
    • SQLite: the full API key (prefix.secretHex) may be stored on the logbook row for local use; there is no separate api_keys table client-side.
    • Postgres: only a hash (or HMAC) of the secretHex is stored in api_keys; full keys are never persisted.
  • Identifiers
    • Both use a sequential internal id per table.
    • uid is a server-issued opaque identifier on the logbook. In SQLite this field is optional (NULL until registration), with a unique index when present.

Schema overview (high level)

  • logbook

    • Columns: id, created_at, modified_at, deleted_at, name, callsign, description, uid (nullable), api_key (nullable)
    • Uniqueness: name is unique; uid and api_key are unique when present (partial unique indexes)
    • Notes: api_key stores the full key client-side; treat it as sensitive and avoid logging

  • qso

    • Columns: id, created_at, modified_at, deleted_at, call, band, mode, freq, qso_date, time_on, time_off, rst_sent, rst_rcvd, country, additional_data (JSON), logbook_id
    • Constraints: JSON field must not duplicate core columns; frequency and date/time fields validated by CHECKs
    • Foreign key: logbook_id REFERENCES logbook(id) ON DELETE RESTRICT (soft-delete friendly)
    • Indexes: call, band, country, (qso_date, time_on), logbook_id; optional partial indexes for active rows (deleted_at IS NULL)

Soft delete guidance

  • Prefer filtering by deleted_at IS NULL for active sets.
  • Partial indexes are provided to keep these queries efficient.

Migrations

  • 0001: creates logbook and qso, adds partial unique indexes on uid and api_key, and soft-delete-friendly indexes and triggers.
  • 0002: no-op for api_keys (client stores full key on logbook; no separate table).

Security notes (client-side)

  • Full API key is stored locally to authenticate against the server; consider encrypting at rest according to your threat model.
  • Never log the full API key; redact in UI and logs.

See also

  • Postgres (server) schema and policies: ../postgres/README.md
  • API key high-level design: ../../apikey/README.md

Documentation

Index

Constants

View Source 
const ServiceName = types.SqliteServiceName
View Source 
const (
	SqliteDriver = "sqlite"
)

Variables

View Source 
var OrderingNames = []struct {
	Value  Ordering
	TSName string
}{
	{Value: Ascending, TSName: "ASC"},
	{Value: Descending, TSName: "DESC"},
}

Functions

func GetMigrationDrivers 

func GetMigrationDrivers(handle *sql.DB) (source.Driver, database.Driver, error)

Types

type Ordering 

type Ordering string
const (
	Ascending  Ordering = "ASC"
	Descending Ordering = "DESC"
)

func (Ordering) String 

func (o Ordering) String() string

type Service 

type Service struct {
	ConfigService  *config.Service  `di.inject:"configservice"`
	LoggerService  *logging.Service `di.inject:"loggingservice"`
	DatabaseConfig *types.DatastoreConfig
	// contains filtered or unexported fields
}

func (*Service) BeginTxContext 

func (s *Service) BeginTxContext(ctx context.Context) (*sql.Tx, context.CancelFunc, error)

BeginTxContext starts a new transaction.

func (*Service) CheckDefaultLogbookExists 

func (s *Service) CheckDefaultLogbookExists() (bool, error)

func (*Service) CheckDefaultLogbookExistsWithContext 

func (s *Service) CheckDefaultLogbookExistsWithContext(ctx context.Context) (bool, error)

func (*Service) Close 

func (s *Service) Close() error

Close closes the database connection.

func (*Service) DeleteLogbookByID 

func (s *Service) DeleteLogbookByID(id int64) error

func (*Service) DeleteLogbookByIDWithContext 

func (s *Service) DeleteLogbookByIDWithContext(ctx context.Context, id int64) error

func (*Service) ExecContext 

func (s *Service) ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)

func (*Service) FetchAllLogbooks 

func (s *Service) FetchAllLogbooks() ([]types.Logbook, error)

func (*Service) FetchAllLogbooksWithContext 

func (s *Service) FetchAllLogbooksWithContext(ctx context.Context) ([]types.Logbook, error)

func (*Service) FetchContactedStationByCallsign 

func (s *Service) FetchContactedStationByCallsign(callsign string) (types.ContactedStation, error)

func (*Service) FetchContactedStationByCallsignWithContext 

func (s *Service) FetchContactedStationByCallsignWithContext(ctx context.Context, callsign string) (types.ContactedStation, error)

func (*Service) FetchCountryByCallsign 

func (s *Service) FetchCountryByCallsign(callsign string) (types.Country, error)

func (*Service) FetchCountryByCallsignWithContext 

func (s *Service) FetchCountryByCallsignWithContext(ctx context.Context, callsign string) (types.Country, error)

func (*Service) FetchCountryByName 

func (s *Service) FetchCountryByName(name string) (types.Country, error)

func (*Service) FetchCountryByNameWithContext 

func (s *Service) FetchCountryByNameWithContext(ctx context.Context, name string) (types.Country, error)

func (*Service) FetchLogbookByID 

func (s *Service) FetchLogbookByID(id int64) (types.Logbook, error)

func (*Service) FetchLogbookByIDWithContext 

func (s *Service) FetchLogbookByIDWithContext(ctx context.Context, id int64) (types.Logbook, error)

func (*Service) FetchPendingUploads 

func (s *Service) FetchPendingUploads() ([]types.QsoUpload, error)

func (*Service) FetchPendingUploadsWithContext 

func (s *Service) FetchPendingUploadsWithContext(ctx context.Context) ([]types.QsoUpload, error)

func (*Service) FetchQsoById 

func (s *Service) FetchQsoById(id int64) (types.Qso, error)

func (*Service) FetchQsoByIdWithContext 

func (s *Service) FetchQsoByIdWithContext(ctx context.Context, id int64) (types.Qso, error)

func (*Service) FetchQsoCountByLogbookId 

func (s *Service) FetchQsoCountByLogbookId(id int64) (int64, error)

func (*Service) FetchQsoCountByLogbookIdWithContext 

func (s *Service) FetchQsoCountByLogbookIdWithContext(ctx context.Context, id int64) (int64, error)

func (*Service) FetchQsoSliceByCallsign 

func (s *Service) FetchQsoSliceByCallsign(callsign string) ([]types.ContactHistory, error)

func (*Service) FetchQsoSliceByCallsignWithContext 

func (s *Service) FetchQsoSliceByCallsignWithContext(ctx context.Context, callsign string) ([]types.ContactHistory, error)

func (*Service) FetchQsoSliceByLogbookId 

func (s *Service) FetchQsoSliceByLogbookId(id int64) (types.QsoSlice, error)

func (*Service) FetchQsoSliceByLogbookIdWithContext 

func (s *Service) FetchQsoSliceByLogbookIdWithContext(ctx context.Context, id int64) (types.QsoSlice, error)

func (*Service) FetchQsoSliceBySessionID 

func (s *Service) FetchQsoSliceBySessionID(id int64) (types.QsoSlice, error)

func (*Service) FetchQsoSliceBySessionIDWithContext 

func (s *Service) FetchQsoSliceBySessionIDWithContext(ctx context.Context, id int64) (types.QsoSlice, error)

func (*Service) FetchQsoSliceNotForwardedWithContext 

func (s *Service) FetchQsoSliceNotForwardedWithContext(ctx context.Context) (types.QsoSlice, error)

func (*Service) FetchQsoSlicePaging 

func (s *Service) FetchQsoSlicePaging(logbookId, pageNum, pageSize int64, ordering Ordering) (types.QsoSlice, error)

func (*Service) FetchQsoSlicePagingWithContext 

func (s *Service) FetchQsoSlicePagingWithContext(ctx context.Context, logbookId, pageNum, pageSize int64, ordering Ordering) (types.QsoSlice, error)

func (*Service) GenerateSession 

func (s *Service) GenerateSession() (int64, error)

func (*Service) GenerateSessionWithContext 

func (s *Service) GenerateSessionWithContext(ctx context.Context) (int64, error)

func (*Service) Initialize 

func (s *Service) Initialize() error

Initialize initializes the database service. No constructor is provided as this service is to be initialized within an IOC/DI container.

func (*Service) InsertContactedStation 

func (s *Service) InsertContactedStation(station types.ContactedStation) (int64, error)

func (*Service) InsertContactedStationWithContext 

func (s *Service) InsertContactedStationWithContext(ctx context.Context, station types.ContactedStation) (int64, error)

func (*Service) InsertCountry 

func (s *Service) InsertCountry(country types.Country) (int64, error)

func (*Service) InsertCountryWithContext 

func (s *Service) InsertCountryWithContext(ctx context.Context, country types.Country) (int64, error)

func (*Service) InsertLogbook 

func (s *Service) InsertLogbook(logbook types.Logbook) (int64, error)

func (*Service) InsertLogbookWithContext 

func (s *Service) InsertLogbookWithContext(ctx context.Context, logbook types.Logbook) (int64, error)

func (*Service) InsertQso 

func (s *Service) InsertQso(qso types.Qso) (int64, error)

func (*Service) InsertQsoUpload 

func (s *Service) InsertQsoUpload(id int64, action action.Action, service upload.OnlineService) error

func (*Service) InsertQsoUploadWithContext 

func (s *Service) InsertQsoUploadWithContext(ctx context.Context, qsoId int64, action action.Action, service upload.OnlineService) error

func (*Service) InsertQsoWithContext 

func (s *Service) InsertQsoWithContext(ctx context.Context, qso types.Qso) (int64, error)

func (*Service) IsContestDuplicateByLogbookID 

func (s *Service) IsContestDuplicateByLogbookID(id int64, callsign, band string) (bool, error)

func (*Service) IsContestDuplicateByLogbookIDWithContext 

func (s *Service) IsContestDuplicateByLogbookIDWithContext(ctx context.Context, id int64, callsign, band string) (bool, error)

func (*Service) LogStats 

func (s *Service) LogStats(prefix string)

func (*Service) Migrate 

func (s *Service) Migrate() error

Migrate runs the database migrations.

func (*Service) Open 

func (s *Service) Open() error

Open opens the database connection.

func (*Service) Ping 

func (s *Service) Ping() error

Ping pings the database connection.

func (*Service) QueryContext 

func (s *Service) QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)

func (*Service) SoftDeleteSessionByID 

func (s *Service) SoftDeleteSessionByID(id int64) error

func (*Service) SoftDeleteSessionByIDWithContext 

func (s *Service) SoftDeleteSessionByIDWithContext(ctx context.Context, id int64) error

func (*Service) UpdateContactedStation 

func (s *Service) UpdateContactedStation(station types.ContactedStation) error

func (*Service) UpdateContactedStationWithContext 

func (s *Service) UpdateContactedStationWithContext(ctx context.Context, station types.ContactedStation) error

func (*Service) UpdateCountry 

func (s *Service) UpdateCountry(country types.Country) error

func (*Service) UpdateCountryWithContext 

func (s *Service) UpdateCountryWithContext(ctx context.Context, country types.Country) error

func (*Service) UpdateQso 

func (s *Service) UpdateQso(qso types.Qso) error

func (*Service) UpdateQsoUploadStatus 

func (s *Service) UpdateQsoUploadStatus(id int64, status status.Status, action action.Action, attempts int64, lastError string) error

func (*Service) UpdateQsoUploadStatusWithContext 

func (s *Service) UpdateQsoUploadStatusWithContext(ctx context.Context, id int64, status status.Status, action action.Action, attempts int64, lastError string) error

func (*Service) UpdateQsoWithContext 

func (s *Service) UpdateQsoWithContext(ctx context.Context, qso types.Qso) error

func (*Service) UpsertLogbook 

func (s *Service) UpsertLogbook(logbook types.Logbook) error

func (*Service) UpsertLogbookWithContext 

func (s *Service) UpsertLogbookWithContext(ctx context.Context, logbook types.Logbook) error

Source Files

View all Source files

Directories

Path Synopsis

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.