sqlite

package
v0.0.56 Latest Latest
Warning

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

 Go to latest Published: Jan 15, 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

This section is empty.

Functions

func GetMigrationDrivers 

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

Types

type Service added in v0.0.32 

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 added in v0.0.32 

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

BeginTxContext starts a new transaction.

func (*Service) Close added in v0.0.32 

func (s *Service) Close() error

Close closes the database connection.

func (*Service) DeleteLogbookByID added in v0.0.53 

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

func (*Service) DeleteLogbookByIDWithContext added in v0.0.53 

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

func (*Service) ExecContext added in v0.0.32 

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

func (*Service) FetchAllLogbooks added in v0.0.51 

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

func (*Service) FetchAllLogbooksWithContext added in v0.0.51 

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

func (*Service) FetchContactedStationByCallsign added in v0.0.32 

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

func (*Service) FetchContactedStationByCallsignWithContext added in v0.0.32 

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

func (*Service) FetchCountryByCallsign added in v0.0.32 

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

func (*Service) FetchCountryByCallsignWithContext added in v0.0.32 

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

func (*Service) FetchCountryByName added in v0.0.32 

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

func (*Service) FetchCountryByNameWithContext added in v0.0.32 

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

func (*Service) FetchLogbookByID added in v0.0.32 

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

func (*Service) FetchLogbookByIDWithContext added in v0.0.32 

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

func (*Service) FetchPendingUploads added in v0.0.38 

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

func (*Service) FetchPendingUploadsWithContext added in v0.0.38 

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

func (*Service) FetchQsoById added in v0.0.40 

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

func (*Service) FetchQsoByIdWithContext added in v0.0.40 

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

func (*Service) FetchQsoCountByLogbookId added in v0.0.32 

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

func (*Service) FetchQsoCountByLogbookIdWithContext added in v0.0.32 

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

func (*Service) FetchQsoSliceByCallsign added in v0.0.32 

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

func (*Service) FetchQsoSliceByCallsignWithContext added in v0.0.32 

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

func (*Service) FetchQsoSliceByLogbookId added in v0.0.52 

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

func (*Service) FetchQsoSliceByLogbookIdWithContext added in v0.0.52 

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

func (*Service) FetchQsoSliceBySessionID added in v0.0.32 

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

func (*Service) FetchQsoSliceBySessionIDWithContext added in v0.0.32 

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

func (*Service) FetchQsoSliceNotForwardedWithContext added in v0.0.37 

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

func (*Service) FetchQsoSlicePaging added in v0.0.56 

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

func (*Service) FetchQsoSlicePagingWithContext added in v0.0.56 

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

func (*Service) GenerateSession added in v0.0.47 

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

func (*Service) GenerateSessionWithContext added in v0.0.47 

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

func (*Service) Initialize added in v0.0.32 

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 added in v0.0.32 

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

func (*Service) InsertContactedStationWithContext added in v0.0.32 

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

func (*Service) InsertCountry added in v0.0.32 

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

func (*Service) InsertCountryWithContext added in v0.0.32 

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

func (*Service) InsertLogbook added in v0.0.51 

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

func (*Service) InsertLogbookWithContext added in v0.0.51 

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

func (*Service) InsertQso added in v0.0.32 

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

func (*Service) InsertQsoUpload added in v0.0.38 

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

func (*Service) InsertQsoUploadWithContext added in v0.0.38 

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

func (*Service) InsertQsoWithContext added in v0.0.32 

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

func (*Service) IsContestDuplicateByLogbookID added in v0.0.36 

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

func (*Service) IsContestDuplicateByLogbookIDWithContext added in v0.0.54 

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

func (*Service) LogStats added in v0.0.32 

func (s *Service) LogStats(prefix string)

func (*Service) Migrate added in v0.0.32 

func (s *Service) Migrate() error

Migrate runs the database migrations.

func (*Service) Open added in v0.0.32 

func (s *Service) Open() error

Open opens the database connection.

func (*Service) Ping added in v0.0.32 

func (s *Service) Ping() error

Ping pings the database connection.

func (*Service) QueryContext added in v0.0.32 

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

func (*Service) SoftDeleteSessionByID added in v0.0.32 

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

func (*Service) SoftDeleteSessionByIDWithContext added in v0.0.32 

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

func (*Service) UpdateContactedStation added in v0.0.32 

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

func (*Service) UpdateContactedStationWithContext added in v0.0.32 

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

func (*Service) UpdateCountry added in v0.0.32 

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

func (*Service) UpdateCountryWithContext added in v0.0.32 

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

func (*Service) UpdateQso added in v0.0.33 

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

func (*Service) UpdateQsoUploadStatus added in v0.0.39 

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

func (*Service) UpdateQsoUploadStatusWithContext added in v0.0.39 

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

func (*Service) UpdateQsoWithContext added in v0.0.33 

func (s *Service) UpdateQsoWithContext(ctx context.Context, qso types.Qso) 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.