top of page
Search

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

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

  • 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, (from_callsign,band,id)



 


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

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

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

  • instance_id (INTEGER) - the VarAC instance cluster that generated this record.


Indexes: id, guid, cqframe_time, from_callsign, (from_callsign,band,id)


 


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

bottom of page