Main schema

CREATE TABLE Repeaters
(
  -- *** DMR Generic Attributes
  `ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'Repeater ID',
  -- *** Connectivity
  Network INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Network ID used by repeater',
  Address VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'IP address of repeater (used to authorize if set)',
  -- *** Radio Amateur Attributes
  `Call` VARCHAR(8) NOT NULL COMMENT 'Call sign of repeater',
  `Owner` VARCHAR(8) NOT NULL COMMENT 'Call sign of repeater''s owner',
  -- *** Additional Information
  Created TIMESTAMP NOT NULL DEFAULT 0,
  Modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  -- *** Indexes
  INDEX NetworkIndex (Network),
  INDEX OwnerIndex (Owner)
);
 
CREATE TABLE Users
(
  -- *** DMR generic attributes
  `ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'Station ID',
  Home INTEGER UNSIGNED NOT NULL DEFAULT 250 COMMENT 'ID of Home Network',
  -- *** Station Location information
  Repeater INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Last known Repeater ID',
  Slot TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Last known Repeater Slot',
  -- *** Application information
  Capabilities INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Terminal capabilities',
  Algorithm SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `Key` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  LANGUAGE CHAR(2) NOT NULL DEFAULT 'en' COMMENT 'Language Code',
  -- *** Radio Amateur attributes
  `Call` VARCHAR(8) NOT NULL COMMENT 'Call sign of Radio Amateur',
  SSID TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'SSID in AX.25 network',
  Symbol CHAR(2) NOT NULL DEFAULT '/[' COMMENT 'Symbol table and code in APRS network',
  `Text` VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'Text to use as APRS comment and D-STAR free-text',
  Priority TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Priority when searching by call',
  -- *** Additional Information
  Created TIMESTAMP NOT NULL DEFAULT 0,
  Modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  -- *** Indexes
  INDEX CallIndex (`Call`, SSID)
);
 
CREATE TABLE Routes
(
  `ID` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Destination ID',
  `Type` CHAR(1) NOT NULL DEFAULT 'G' COMMENT 'Call type: Group of Private)',
  `Kind` CHAR(1) NOT NULL DEFAULT 'R' COMMENT 'Kind of destination: Applicarion, Repeater or Link',
  `Link` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Link ID',
  `Slot` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Link Slot',
  Active TINYINT(1) NOT NULL DEFAULT 1,
  -- *** Additional Information
  Created TIMESTAMP NOT NULL DEFAULT 0,
  Modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  -- *** Indexes
  INDEX RouteIndex (`Active`, `Type`, `ID`)
);
 
CREATE TABLE Subscriptions
(
  `User` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Subscribed User ID',
  `Group` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Subscribed Group ID',
  Active TINYINT(1) NOT NULL DEFAULT 1,
  -- *** Additional Information
  Created TIMESTAMP NOT NULL DEFAULT 0,
  Modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  -- *** Indexes
  INDEX SubscriptionIndex (`Active`, `Group`)
);

Pre-CallDirector extensions

CREATE TABLE Nodes
(
  `ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'Network ID',
  Address VARCHAR(256) NOT NULL DEFAULT '' COMMENT 'Domain name or IP address',
  Active TINYINT(1) NOT NULL DEFAULT 1,
  -- *** Additional Information
  Created TIMESTAMP NOT NULL DEFAULT 0,
  Modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

AMPR Router extensions

CREATE TABLE Tunnels
(
  -- *** DMR generic attributes
  `ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'Station ID',
  -- *** IP Bridge attributes
  Network TINYINT UNSIGNED NOT NULL DEFAULT 13 COMMENT 'Common Air Interface network',
  Address VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'IP address allocated from AMPR network',
  Protocol TINYINT UNSIGNED NOT NULL DEFAULT 47 COMMENT 'Tunnel Encapsulation Protocol',
  Firewall TINYINT UNSIGNED NOT NULL DEFAULT 3 COMMENT 'Firewall rule set number',
  -- *** Additional Information
  Created TIMESTAMP NOT NULL DEFAULT 0,
  Modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  -- *** Indexes
  UNIQUE INDEX AddressIndex (Address)
);