voip:asterisk:mysql-datenbankdefinitionen

MySQL-Datenbankdefinitionen

Unseren Datenbankuser asteriskuser legen wir am einfachsten über die Konsole oder via phpmyadmin an:

--
-- Datenbankuser anlegen
--

CREATE USER 'asteriskuser'@'localhost' IDENTIFIED BY 'Mark_is_a_Geek';

-- --------------------------------------------------------------------

--
-- Datenbank anlegen
--

CREATE DATABASE `asterisk_db`;

-- --------------------------------------------------------------------

--
-- Berechtigungen für den datenbankuser auf die Datenbank festlegen
-- 

GRANT all on asterisk_db.* to 'asteriskuser'@'localhost';

-- --------------------------------------------------------------------

--
-- Berechtigungen freigeben
--

FLUSH PRIVILEGES;

-- --------------------------------------------------------------------

Anschließend legen wir noch die benötigten Datenbanktabellen an. Auch dieses erledigen wir am einfachsten via phpmyadmin oder über die mysql-Konsole:

--
-- Datenbank auswählen
--

USE asterisk_db;

-- --------------------------------------------------------------------

--
-- Tabelle `cdr` anlegen
--

CREATE TABLE IF NOT EXISTS `cdr` (
  `calldate` datetime NOT NULL default '0000-00-00 00:00:00',
  `clid` varchar(80) NOT NULL default '',
  `src` varchar(80) NOT NULL default '',
  `dst` varchar(80) NOT NULL default '',
  `dcontext` varchar(80) NOT NULL default '',
  `channel` varchar(80) NOT NULL default '',
  `dstchannel` varchar(80) NOT NULL default '',
  `lastapp` varchar(80) NOT NULL default '',
  `lastdata` varchar(80) NOT NULL default '',
  `duration` int(11) NOT NULL default '0',
  `billsec` int(11) NOT NULL default '0',
  `disposition` varchar(45) NOT NULL default '',
  `amaflags` int(11) NOT NULL default '0',
  `accountcode` varchar(20) NOT NULL default '',
  `uniqueid` varchar(32) NOT NULL default '',
  `userfield` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`uniqueid`),
  KEY `calldate` (`calldate`),
  KEY `dst` (`dst`),
  KEY `accountcode` (`accountcode`),
  KEY `src` (`src`),
  KEY `disposition` (`disposition`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------------------

--
-- Tabelle `extensions_table` anlegen
--

CREATE TABLE IF NOT EXISTS `extensions_table` (
  `id` int(11) NOT NULL auto_increment,
  `context` varchar(20) NOT NULL default '',
  `exten` varchar(20) NOT NULL default '',
  `priority` tinyint(4) NOT NULL default '0',
  `app` varchar(20) NOT NULL default '',
  `appdata` varchar(128) NOT NULL default '',
  PRIMARY KEY  (`context`,`exten`,`priority`),
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=129 ;

-- --------------------------------------------------------------------

--
-- Tabelle `sip` anlegen
--

CREATE TABLE IF NOT EXISTS `sip` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(80) NOT NULL,
  `host` varchar(31) NOT NULL default 'dynamic',
  `nat` varchar(5) NOT NULL default 'no',
  `type` enum('user','peer','friend') NOT NULL default 'friend',
  `accountcode` varchar(20) default 'ACCOUNT',
  `amaflags` varchar(13) default NULL,
  `callgroup` varchar(10) default NULL,
  `callerid` varchar(80) default NULL,
  `call-limit` varchar(6) NOT NULL default '10',
  `cancallforward` char(3) default 'yes',
  `canreinvite` char(3) default 'no',
  `context` varchar(80) default 'from-sip',
  `defaultip` varchar(15) default NULL,
  `dtmfmode` varchar(7) default 'rfc2833',
  `fromuser` varchar(80) default NULL,
  `fromdomain` varchar(80) default NULL,
  `insecure` varchar(4) default NULL,
  `language` char(2) default 'en',
  `mailbox` varchar(50) default NULL,
  `md5secret` varchar(80) default NULL,
  `deny` varchar(95) default '0.0.0.0/0.0.0.0',
  `permit` varchar(95) default '0.0.0.0/0.0.0.0',
  `mask` varchar(95) default NULL,
  `musiconhold` varchar(100) default 'default',
  `pickupgroup` varchar(10) default NULL,
  `qualify` char(3) default 'yes',
  `regexten` varchar(80) default NULL,
  `restrictcid` char(3) default NULL,
  `rtptimeout` char(3) default NULL,
  `rtpholdtimeout` char(3) default NULL,
  `secret` varchar(80) default 'SECRET',
  `setvar` varchar(100) default NULL,
  `disallow` varchar(100) default 'all',
  `allow` varchar(100) default 'alaw',
  `fullcontact` varchar(80) NOT NULL,
  `ipaddr` varchar(15) NOT NULL,
  `port` smallint(5) unsigned NOT NULL default '0',
  `regserver` varchar(100) default NULL,
  `regseconds` int(11) NOT NULL default '0',
  `username` varchar(80) NOT NULL,
  `MSN` varchar(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `name_2` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=38 ;

-- --------------------------------------------------------------------

--
-- Tabelle `voicemail_messages` anlegen
--

CREATE TABLE IF NOT EXISTS `voicemail_messages` (
  `id` int(11) NOT NULL auto_increment,
  `msgnum` int(11) NOT NULL default '0',
  `dir` varchar(80) default '',
  `context` varchar(80) default '',
  `macrocontext` varchar(80) default '',
  `callerid` varchar(40) default '',
  `origtime` varchar(40) default '',
  `duration` varchar(20) default '',
  `mailboxuser` varchar(80) default '',
  `mailboxcontext` varchar(80) default '',
  `recording` longblob,
  PRIMARY KEY  (`id`),
  KEY `dir` (`dir`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1103 ;

-- --------------------------------------------------------------------

--
-- Tabelle `voicemail_users` anlegen
--

CREATE TABLE IF NOT EXISTS `voicemail_users` (
  `uniqueid` int(11) NOT NULL auto_increment,
  `customer_id` varchar(11) NOT NULL default '0',
  `context` varchar(50) NOT NULL default 'default',
  `mailbox` varchar(11) NOT NULL default '0',
  `password` varchar(5) NOT NULL default '6666',
  `fullname` varchar(150) NOT NULL,
  `email` varchar(50) NOT NULL,
  `pager` varchar(50) NOT NULL,
  `tz` varchar(10) NOT NULL default 'en',
  `attach` varchar(4) NOT NULL default 'yes',
  `saycid` varchar(4) NOT NULL default 'yes',
  `dialout` varchar(10) NOT NULL,
  `callback` varchar(10) NOT NULL,
  `review` varchar(4) NOT NULL default 'no',
  `operator` varchar(4) NOT NULL default 'no',
  `envelope` varchar(4) NOT NULL default 'no',
  `sayduration` varchar(4) NOT NULL default 'no',
  `saydurationm` tinyint(4) NOT NULL default '1',
  `sendvoicemail` varchar(4) NOT NULL default 'no',
  `delete` varchar(4) NOT NULL default 'no',
  `nextaftercmd` varchar(4) NOT NULL default 'yes',
  `forcename` varchar(4) NOT NULL default 'no',
  `forcegreetings` varchar(4) NOT NULL default 'no',
  `hidefromdir` varchar(4) NOT NULL default 'yes',
  `stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`uniqueid`),
  KEY `mailbox_context` (`mailbox`,`context`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=33 ;

-- --------------------------------------------------------------------

Zum Schluss testen wir noch, ob unser User asteriskuser auch auf unsere Datenbank und den efinierten Tabellen zugreifen kann:

[user@host ~]# mysql -D asterisk_db -u asteriskuser -h localhost -p
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 674 to server version: 5.0.22-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> status;
--------------
mysql  Ver 14.12 Distrib 5.0.22, for redhat-linux-gnu (i686) using readline 5.0

Connection id:          677
Current database:       asterisk_db
Current user:           asteriskuser@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.22-log
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 1 day 23 hours 21 min 11 sec

Threads: 4  Questions: 545412  Slow queries: 0  Opens: 0  Flush tables: 1  Open tables: 26  Queries per second avg: 3.199
--------------

mysql> use asterisk;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_asterisk |
+--------------------+
| cdr                | 
| extensions_table   | 
| sip                | 
| voicemail_messages | 
| voicemail_users    | 
+--------------------+
5 rows in set (0.01 sec)

mysql> select * from sip;
+----+------+---------+-----+--------+-------------+----------+-----------+-----------------------+------------+----------------+-------------+---------+-----------+----------+----------+------------+----------+----------+---------+-----------+-----------------+-----------------+------+-------------+-------------+---------+----------+-------------+------------+----------------+----------+--------+----------+-------+-----------------------------------------+---------------+-------+--------------+------------+----------+-------------+
| id | name | host    | nat | type   | accountcode | amaflags | callgroup | callerid              | call-limit | cancallforward | canreinvite | context | defaultip | dtmfmode | fromuser | fromdomain | insecure | language | mailbox | md5secret | deny            | permit          | mask | musiconhold | pickupgroup | qualify | regexten | restrictcid | rtptimeout | rtpholdtimeout | secret   | setvar | disallow | allow | fullcontact                             | ipaddr        | port  | regserver    | regseconds | username | MSN         |
+----+------+---------+-----+--------+-------------+----------+-----------+-----------------------+------------+----------------+-------------+---------+-----------+----------+----------+------------+----------+----------+---------+-----------+-----------------+-----------------+------+-------------+-------------+---------+----------+-------------+------------+----------------+----------+--------+----------+-------+-----------------------------------------+---------------+-------+--------------+------------+----------+-------------+
35 rows in set (0.00 sec)

Zurück zum Inhaltsverzeichnis ⇒ asterisk

Diese Website verwendet Cookies. Durch die Nutzung der Website stimmen Sie dem Speichern von Cookies auf Ihrem Computer zu. Außerdem bestätigen Sie, dass Sie unsere Datenschutzbestimmungen gelesen und verstanden haben. Wenn Sie nicht einverstanden sind, verlassen Sie die Website.Weitere Information
  • voip/asterisk/mysql-datenbankdefinitionen.txt
  • Zuletzt geändert: 20.04.2008 22:00.
  • (Externe Bearbeitung)