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