MySQL-Datenbankdefinitionen
User anlegen und berechtigen
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; -- --------------------------------------------------------------------
Datenbanktabellen anlegen
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 ; -- --------------------------------------------------------------------
Datenbankzugriff testen
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