VarAC Sqlite database structure (for developers)

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

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

Unlike APIs that are limited to a particular use case, accessing directly the VarAC 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 VarAC Sqlite DB can be found in the VarAC directory under the name: VarAC.db

By design, the VarAC Sqlite database is not protected by a password or encryption to allow anyone to integrate with its content.

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

band_data

Description: Store band related data such as the specific VARA modem volume level set per band.

Columns:

• id (INTEGER) - Auto increment ID

• guid (TEXT) - unique row identifier

• band (TEXT) - the band (20m/40m...)

• modem_audio_level (INTEGER) - the modem volume level set by the operator.

• comments (TEXT) - additional info.

• creation_time (DATETIME) - when this row was created

• is_deleted (BOOLEAN) - if the row was marked as deleted or not

  
  

Indexes: id, guid, band

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 VarAC instances in a VarAC 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 VarAC 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 VarAC 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,

• clean_callsign (TEXT) - the callsign without any prefixes/suffixes. The actual callsign the modem connected to.

  
  

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

qso_snr_report

Description: Stores all SNR reports captured in a QSO. Used to re-create the SNR graphs of a QSO retroactively. This is not necessarily the reports that were sent ad the VARA modem provides a report for every received packet.

Columns:

• id (INTEGER) - Auto increment ID

• guid (TEXT) - unique row identifier

• qso_guid (TEXT) - The QSO UUID for which this SNR report ogirinated form.

• snr_direction (TEXT) - Wether this was a report of myself (1) or the other side (2)

• snr (INTEGER) - the snr level (measured/received/sent)

• creation_time (DATETIME) - when this row was created

  
  

Indexes: id, (guid, creation_time)

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

• has_attachment (BOOLEAN) - if the VMail has attachments or not

• urgent (BOOLEAN) - if this VMail is urgent or not

  
  

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

vmail_attachment (WIP)

Description: Contains references to all attachment files of VMails.

Columns:

• id (INTEGER) - Auto increment ID

• guid (TEXT) - unique row identifier

• vmail_guid (TEXT) - the VMail unique identified for which this attachment relate to.

• file_name (TEXT) - the full path of the file on the local disk

• file_size_bytes (INTEGER) - file size in bytes

• creation_time (DATETIME) - when this row was created

• is_deleted (BOOLEAN) - if the attachment was marked as deleted or not

  
  

Indexes: id, file_name

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.

• urgent (BOOLEAN) - if this relay notification is urgent or not

  
  

Indexes: id, guid, from_callsign, is_deleted