Просмотр Исходного

{code}
CREATE DATABASE asterisk;

USE asterisk;

GRANT ALL ON asterisk.* TO asterisk@localhost IDENTIFIED BY 'servicemode';

DROP TABLE IF EXISTS sipusers;
CREATE TABLE `sipusers` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(10) NOT NULL,
`ipaddr` VARCHAR(15) DEFAULT NULL,
`port` INT(5) DEFAULT NULL,
`regseconds` INT(11) DEFAULT NULL,
`defaultuser` VARCHAR(10) DEFAULT NULL,
`fullcontact` VARCHAR(35) DEFAULT NULL,
`regserver` VARCHAR(20) DEFAULT NULL,
`useragent` VARCHAR(20) DEFAULT NULL,
`lastms` INT(11) DEFAULT NULL,
`host` VARCHAR(40) DEFAULT NULL,
`type` enum('friend','user','peer') DEFAULT NULL,
`context` VARCHAR(40) DEFAULT NULL,
`permit` VARCHAR(40) DEFAULT NULL,
`deny` VARCHAR(40) DEFAULT NULL,
`secret` VARCHAR(40) DEFAULT NULL,
`md5secret` VARCHAR(40) DEFAULT NULL,
`remotesecret` VARCHAR(40) DEFAULT NULL,
`transport` enum('udp','tcp','udp,tcp','tcp,udp') DEFAULT NULL,
`dtmfmode` enum('rfc2833','info','shortinfo','inband','auto') DEFAULT NULL,
`directmedia` enum('yes','no','nonat','update') DEFAULT NULL,
`nat` enum('yes','no','never','route') DEFAULT NULL,
`callgroup` VARCHAR(40) DEFAULT NULL,
`pickupgroup` VARCHAR(40) DEFAULT NULL,
`language` VARCHAR(40) DEFAULT NULL,
`disallow` VARCHAR(40) DEFAULT NULL,
`allow` VARCHAR(40) DEFAULT NULL,
`insecure` VARCHAR(40) DEFAULT NULL,
`trustrpid` enum('yes','no') DEFAULT NULL,
`progressinband` enum('yes','no','never') DEFAULT NULL,
`promiscredir` enum('yes','no') DEFAULT NULL,
`useclientcode` enum('yes','no') DEFAULT NULL,
`accountcode` VARCHAR(40) DEFAULT NULL,
`setvar` VARCHAR(40) DEFAULT NULL,
`callerid` VARCHAR(40) DEFAULT NULL,
`amaflags` VARCHAR(40) DEFAULT NULL,
`callcounter` enum('yes','no') DEFAULT NULL,
`busylevel` INT(11) DEFAULT NULL,
`allowoverlap` enum('yes','no') DEFAULT NULL,
`allowsubscribe` enum('yes','no') DEFAULT NULL,
`videosupport` enum('yes','no') DEFAULT NULL,
`maxcallbitrate` INT(11) DEFAULT NULL,
`rfc2833compensate` enum('yes','no') DEFAULT NULL,
`mailbox` VARCHAR(40) DEFAULT NULL,
`session-timers` enum('accept','refuse','originate') DEFAULT NULL,
`session-expires` INT(11) DEFAULT NULL,
`session-minse` INT(11) DEFAULT NULL,
`session-refresher` enum('uac','uas') DEFAULT NULL,
`t38pt_usertpsource` VARCHAR(40) DEFAULT NULL,
`regexten` VARCHAR(40) DEFAULT NULL,
`fromdomain` VARCHAR(40) DEFAULT NULL,
`fromuser` VARCHAR(40) DEFAULT NULL,
`qualify` VARCHAR(40) DEFAULT NULL,
`defaultip` VARCHAR(40) DEFAULT NULL,
`rtptimeout` INT(11) DEFAULT NULL,
`rtpholdtimeout` INT(11) DEFAULT NULL,
`sendrpid` enum('yes','no') DEFAULT NULL,
`outboundproxy` VARCHAR(40) DEFAULT NULL,
`callbackextension` VARCHAR(40) DEFAULT NULL,
`timert1` INT(11) DEFAULT NULL,
`timerb` INT(11) DEFAULT NULL,
`qualifyfreq` INT(11) DEFAULT NULL,
`constantssrc` enum('yes','no') DEFAULT NULL,
`contactpermit` VARCHAR(40) DEFAULT NULL,
`contactdeny` VARCHAR(40) DEFAULT NULL,
`usereqphone` enum('yes','no') DEFAULT NULL,
`textsupport` enum('yes','no') DEFAULT NULL,
`faxdetect` enum('yes','no') DEFAULT NULL,
`buggymwi` enum('yes','no') DEFAULT NULL,
`auth` VARCHAR(40) DEFAULT NULL,
`fullname` VARCHAR(40) DEFAULT NULL,
`trunkname` VARCHAR(40) DEFAULT NULL,
`cid_number` VARCHAR(40) DEFAULT NULL,
`callingpres` enum('allowed_not_screened','allowed_passed_screen','allowed_failed_screen'
,'allowed','prohib_not_screened','prohib_passed_screen','prohib_failed_screen'
,'prohib') DEFAULT NULL,
`mohinterpret` VARCHAR(40) DEFAULT NULL,
`mohsuggest` VARCHAR(40) DEFAULT NULL,
`parkinglot` VARCHAR(40) DEFAULT NULL,
`hasvoicemail` enum('yes','no') DEFAULT NULL,
`subscribemwi` enum('yes','no') DEFAULT NULL,
`vmexten` VARCHAR(40) DEFAULT NULL,
`autoframing` enum('yes','no') DEFAULT NULL,
`rtpkeepalive` INT(11) DEFAULT NULL,
`call-limit` INT(11) DEFAULT NULL,
`g726nonstandard` enum('yes','no') DEFAULT NULL,
`ignoresdpversion` enum('yes','no') DEFAULT NULL,
`allowtransfer` enum('yes','no') DEFAULT NULL,
`dynamic` enum('yes','no') DEFAULT NULL,
`sippasswd` VARCHAR(80) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `ipaddr` (`ipaddr`,`port`),
KEY `host` (`host`,`port`)
) ENGINE=MyISAM;


DROP TABLE IF EXISTS sipregs;
CREATE TABLE `sipregs` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(80) NOT NULL DEFAULT '',
`fullcontact` VARCHAR(80) NOT NULL DEFAULT '',
`ipaddr` VARCHAR(45) DEFAULT NULL,
`port` mediumint(5) UNSIGNED NOT NULL DEFAULT '0',
`username` VARCHAR(80) NOT NULL DEFAULT '',
`regserver` VARCHAR(100) DEFAULT NULL,
`regseconds` INT(11) NOT NULL DEFAULT '0',
`defaultuser` VARCHAR(80) NOT NULL DEFAULT '',
`useragent` VARCHAR(20) DEFAULT NULL,
`lastms` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
);


DROP TABLE IF EXISTS voicemail;
CREATE TABLE voicemail (
-- All of these column names are very specific, including "uniqueid". Do not change them if you wish voicemail to work.
uniqueid INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
-- Mailbox context.
context CHAR(80) NOT NULL DEFAULT 'default',
-- Mailbox number. Should be numeric.
mailbox CHAR(80) NOT NULL,
-- Must be numeric. Negative if you don't want it to be changed from VoicemailMain
password CHAR(80) NOT NULL,
-- Used in email and for Directory app
fullname CHAR(80),
-- Email address (will get sound file if attach=yes)
email CHAR(80),
-- Email address (won't get sound file)
pager CHAR(80),
-- Attach sound file to email - YES/no
attach CHAR(3),
-- Which sound format to attach
attachfmt CHAR(10),
-- Send email from this address
serveremail CHAR(80),
-- Prompts in alternative language
LANGUAGE CHAR(20),
-- Alternative timezone, as defined in voicemail.conf
tz CHAR(30),
-- Delete voicemail from server after sending email notification - yes/NO
deletevoicemail CHAR(3),
-- Read back CallerID information during playback - yes/NO
saycid CHAR(3),
-- Allow user to send voicemail from within VoicemailMain - YES/no
sendvoicemail CHAR(3),
-- Listen to voicemail and approve before sending - yes/NO
review CHAR(3),
-- Warn user a temporary greeting exists - yes/NO
tempgreetwarn CHAR(3),
-- Allow '0' to jump out during greeting - yes/NO
operator CHAR(3),
-- Hear date/time of message within VoicemailMain - YES/no
envelope CHAR(3),
-- Hear length of message within VoicemailMain - yes/NO
sayduration CHAR(3),
-- Minimum duration in minutes to say
saydurationm INT(3),
-- Force new user to record name when entering voicemail - yes/NO
forcename CHAR(3),
-- Force new user to record greetings when entering voicemail - yes/NO
forcegreetings CHAR(3),
-- Context in which to dial extension for callback
callback CHAR(80),
-- Context in which to dial extension (from advanced menu)
dialout CHAR(80),
-- Context in which to execute 0 or * escape during greeting
exitcontext CHAR(80),
-- Maximum messages in a folder (100 if not specified)
maxmsg INT(5),
-- Increase DB gain on recorded message by this amount (0.0 means none)
volgain DECIMAL(5,2),
-- IMAP user for authentication (if using IMAP storage)
imapuser VARCHAR(80),
-- IMAP password for authentication (if using IMAP storage)
imappassword VARCHAR(80),
-- IMAP server location (if using IMAP storage)
imapsever VARCHAR(80),
-- IMAP port (if using IMAP storage)
imapport VARCHAR(8),
-- IMAP flags (if using IMAP storage)
imapflags VARCHAR(80),
stamp TIMESTAMP
);

DROP TABLE IF EXISTS voicemail_data;
CREATE TABLE voicemail_data (
-- Path to the recording
filename CHAR(255) NOT NULL PRIMARY KEY,
-- Mailbox number (without context)
origmailbox CHAR(80),
-- Dialplan context
context CHAR(80),
-- Dialplan context, if voicemail was invoked from a macro
macrocontext CHAR(80),
-- Dialplan extension
exten CHAR(80),
-- Dialplan priority
priority INT(5),
-- Name of the channel, when message was left
callerchan CHAR(80),
-- CallerID on the channel, when message was left
callerid CHAR(80),
-- Contrary to the name, origdate is a full datetime, in localized format
origdate CHAR(30),
-- Same date as origdate, but in Unixtime
origtime INT(11),
-- Value of the channel variable VM_CATEGORY, if set
category CHAR(30),
-- Length of the message, in seconds
duration INT(11)
);
DROP TABLE IF EXISTS voicemail_messages;
CREATE TABLE voicemail_messages (
-- Logical directory
dir CHAR(255),
-- Message number within the logical directory
msgnum INT(4),
-- Dialplan context
context CHAR(80),
-- Dialplan context, if Voicemail was invoked from a macro
macrocontext CHAR(80),
-- CallerID, when the message was left
callerid CHAR(80),
-- Date when the message was left, in Unixtime
origtime INT(11),
-- Length of the message, in seconds
duration INT(11),
-- The recording itself
recording BLOB,
-- Text flags indicating urgency of the message
flag CHAR(30),
-- Value of channel variable VM_CATEGORY, if set
category CHAR(30),
-- Owner of the mailbox
mailboxuser CHAR(30),
-- Context of the owner of the mailbox
mailboxcontext CHAR(30),
-- Unique ID of the message,
msg_id CHAR(40),
PRIMARY KEY (dir, msgnum)
);
{code}