Difference between revisions of "Database Schema"

Latest revision as of 15:43, 23 November 2015

Main schema

<syntaxhighlight lang="sql"> 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 repeaters 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`)

); </syntaxhighlight>

Pre-CallDirector extensions

<syntaxhighlight lang="sql"> 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

); </syntaxhighlight>

AMPR Router extensions

<syntaxhighlight lang="sql"> 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)

); </syntaxhighlight>