top of page

Technical materials

Public·1 member

SafecomLink Sqlite database structure (for developers)

Since SafecomLink V5, SafecomLink has a Sqlite database as its main data engine.

This allows developers to integrate in a super flexible, SQL based way to the SafecomLink traffic for various types of integrations.


Unlike APIs that are limited to a particular use case, accessing directly the SafecomLink database provides unlimited options for integrations.


To allow “track changes” data reading, each table has 2 identifiers for every row that you can use:

  1. A sequential ID – which can be used as a high watermark

  2. A unique UUID based identifier.


The SafecomLink Sqlite DB can be found in the SafecomLink directory under the name: SafecomLink.db


By design, the SafecomLink Sqlite database is not protected by a password or encryption to customers decide on their own protection mechanism.




alert

Description: storing all broadcasts (incoming & outgoing)

Columns:

  • id (INTEGER) - Auto increment ID

  • guid (TEXT) - unique row identifier

  • source_id (INTEGER) - where this alert originated from (see alert_source lookup table)

  • from_callsign (TEXT)

  • to_callsign (TEXT)

  • alert_tag (TEXT) - the alert tag (text) that triggered the alert.

  • source_text (TEXT) - the full source text that includes the tag that triggered the alert.

  • alert_time (DATETIME) - time in which the alert was received.

  • read_status (BOOLEAN) - True if it was read already. False if not-read.

  • folder_id (INTEGER) - Where this alert is stored (Incoming / Archived folder)

  • frequency (INTEGER) – in Hz

  • is_deleted (BOOLEAN) - true if the alert was deleted.

Indexes: id, guid, (folder_id, alert_time), alert_tag, from_callsign, to_callsign




alert_source

Description: Lookup table for the "alert" table

Columns:

  • source_id (INTEGER)

  • source (TEXT) - the source of the alert (1-Beacon/2-CQ/3-Broadcast/4-Datastream)

Indexes: source_id



alert_folder

Description: Lookup table for the "alert" table

Columns:

  • folder_id (INTEGER)

  • folder (TEXT) - the status of the alert (Incoming/Archived)

Indexes: folder_id



broadcast

Description: storing all broadcasts (incoming & outgoing)

Columns:

  • id (INTEGER) - Auto increment ID

  • guid (TEXT) - unique row identifier

  • broadcast_time (DATETIME) – in UTC

  • frequency (INTEGER) – in Hz

  • from_callsign (TEXT)

  • to_callsign (TEXT)

  • via_callsign (TEXT) – digipeater (one or more)

  • broadcast_message (TEXT) – The actual broadcast message

  • snr (INTEGER) – the SNR of received broadcasts.

Indexes: id, guid, broadcast_time, from_callsign, to_callsign




contact

Description: All callsigns you made a QSO with or entered manually

Columns:

  • id (NTEGER) - Auto increment ID

  • guid (TEXT) - unique row identifier

  • callsign (TEXT)

  • name (TEXT)

  • qth (TEXT)

  • comments (TEXT) - additional comments made by you for that contact

  • rig (TEXT)

  • time_added (DATETIME) - When this contact was first added to the contacts list

  • favorite (BOOLEAN) - if this contact is a favorite one

  • is_deleted (BOOLEAN) - deletion status of this contact

Indexes: id, guid, starttime, callsign



cqframe

Description: Storing all received Beacons and CQ

Columns:

  • id (INTEGER) - Auto increment ID

  • guid (TEXT) - unique row identifier

  • cqframe_time (DATETIME) – in UTC

  • cqframe_type_id (INTEGER) – Reference to the table "cqframe_type"

  • frequency (INTEGER) – in Hz

  • bandwidth (TEXT) - 500 or 2300

  • from_callsign (TEXT)

  • snr (INTEGER) – the SNR of received cqframe.

  • slot (INTEGER) – slot id where the cqframe was received

  • data (TEXT) - used for additional data. In CQ for example it can be a special CQ (POTA/SOTA...)

  • locator (TEXT) - in special CQs a locator is also sent

  • is_emcomm (BOOLEAN) - weather the beacon came from an EmComm station.

Indexes: id, guid, cqframe_time, from_callsign



cqframe_type

Description: Lookup table of the "cqframe" table. identifying a cqframe as either CQ (1) or Beacon (2)

Columns:

  • cqframe_type_id (INTEGER)

  • cqframe_type (TEXT)

Indexes: cqframe_type_id



datastream_entry_type

Description: Lookup table of the "datastream" table. identifying a type of an entry.

  • 1 - Incoming message

  • 2 - Outgoing message

  • 3 - System message

Columns:

  • datastream_entry_type_id (INTEGER)

  • datastream_entry_type (TEXT)

Indexes: datastream_entry_type_id



datastream

Description: Contains all the datastream entries including chat message and system messages.

Columns:

  • id (INTEGER) - unique sequential row identifier

  • guid (TEXT) - unique row identifier (guid)

  • datastream_entry_type_id (INTEGER) - type of record as described in datastream_entry_type table

  • qso_guid (TEXT) - the QSO identifier (from the qso table) that this entry belong to

  • callsign (TEXT) - the callsign who wrote this entry

  • entry (TEXT) - the message it self (chat or info message)

  • file_path (TEXT) - if this message contains a file (file transfer) - the local path of this file

  • chat_id (INTEGER) - sequentia number of the message in this QSO

  • reply_on_chat_id (INTEGER) - a chat ID for which this message is a reply to

  • creation_time (DATETIME) - when this message was received

  • is_deleted (BOOLEAN) - deletion status of this record.

Indexes: id, guid, (callsign, creation_time), creation_time



instance

Description: List of SafecomLink instances in a SafecomLink cluster.

Columns:

  • id (INTEGER) - unique instance ID

  • guid (TEXT) - unique row identifier

  • name (TEXT) - instance name

  • comments (TEXT) - additional info

  • busy (BOOLEAN) - set to True if the instance is currently_busy

  • busy_last_time(DATETIME) - last time this instance was busy

  • last_keepalive_time (DATETIME) - last time the instance was seen alive in the cluster

  • creation_time (DATETIME) - when this row was created for the first time

  • is_deleted (BOOLEAN) - if the instance was deleted or not

Indexes: id, guid, busy_last_time, last_keepalive_time



parameter

Description: Global SafecomLink parameters. currently hold only one parameter which is the sqlite structure version.

Columns:

  • parameter_id (INTEGER)

  • parameter_name (TEXT)

  • parameter_value (TEXT)

Indexes: parameter_id




qso

Description: All Valid QSOs are stored here. It's a mirror of ADIF data plus additional information.

Columns:

  • id (INTEGER) - Auto increment ID

  • guid (TEXT) - unique row identifier

  • vara_modem_type (TEXT) - What modem was used during the QSO (VARA HF/FM/SAT)

  • mode (TEXT) - the ADIF mode that was reported (usually DYNAMIC)

  • submode (TEXT) - the ADIF submode that was reported (VARA HF/FM/SAT)

  • starttime (DATETIME) - QSO start time (date and time)

  • endtime (DATETIME) - QSO end time (date and time)

  • frequency (INTEGER) - in Hz

  • band (TEXT) - band based on the frequency (ex. 20m, 40m...)

  • bandwidth (TEXT) - 500 or 2300

  • callsign (TEXT) - the callsign of the station you connected with

  • my_callsign (TEXT) - your callsign at the time of the QSO

  • digipeater (TEXT) - if a digipeater was used - it will be logged here

  • snr_received (INTEGER) - received RST

  • snr_sent (INTEGER) - sent RST

  • name (TEXT) - operator name

  • qth (TEXT) - operator QTH

  • my_power (INTEGER) - based on the value at your profile at the time of the QSO

  • my_rig (INTEGER) - based on the value at your profile at the time of the QSO

  • my_antenna (INTEGER) - based on the value at your profile at the time of the QSO

  • comments (TEXT) - additional comments

  • varac_version (TEXT) - the SafecomLink version at the time of the QSO

  • is_ping (BOOLEAN) - if this QSO was a ping (obsolete since V7 Pings are not listed as valid QSOs)

  • is_deleted (BOOLEAN) - deletion status of this record.

  • slot (TEXT) - slot number on which this QSO took place

Indexes: id, guid, starttime, (callsign,starttime)



vmail

Description: Store all Vmails (incoming / outgoing / sent / parking)

Columns:

  • id (INTEGER) - Auto increment ID

  • guid (TEXT) - unique row identifier

  • creation_time (DATETIME) – in UTC

  • sent_time (DATETIME) – in UTC

  • received_time (DATETIME) – in UTC

  • folder_id (INTEGER) - reference to the "vmail_folder" table. Says if it is an inbox/outbox/parked/sent vmail.

  • vmail_to (TEXT) - the callsign of the vmail destination

  • vmail_from (TEXT)- the callsign of the vmail source

  • vmail_via (TEXT) - the callsign of the vmail intermediate station

  • delivery_band (TEXT) - Band in which the Vmail was received

  • delivery_snr (TEXT) - SNR in which the Vmail was received

  • subject (TEXT) - Vmail subject

  • msg (TEXT) - Vmail body

  • read_status (BOOLEAN) - True if it was read already. False if not-read.

  • is_deleted (BOOLEAN) - if the vmail was deleted or not

  • frequency (INTEGER) – in Hz - on which frequency the VMail was received

Indexes: id, guid, (vmail_to,folder_id), (folder_id,read_status,is_deleted)




vmail_folder

Description: Lookup table for the "vmail" table

Columns:

  • folder_id (INTEGER)

  • folder (TEXT) - the folder name (Inbox/Sent/Outbox/Parking)

Indexes: folder_id



vmail_relay_notification

Description: Storing all relay notifications - which are indications received from other stations regarding parked Vmails that awaits for your retrieval.

Columns:

  • id (INTEGER) - Auto increment ID

  • guid (TEXT) - unique row identifier

  • relay_notification_time (DATETIME) – in UTC

  • frequency (INTEGER) – in Hz

  • from_callsign (TEXT)

  • is_deleted (INTEGER) – A boolean (1/0) field - saying if you already deleted the notification or not.

Indexes: id, guid, from_callsign, is_deleted

15 Views

About

Welcome to the group! Connect with other members, get updates and share media.

bottom of page