Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
voip:asterisk:mysql-datenbankdefinitionen [29.01.2008 20:15. ] – angelegt django | voip:asterisk:mysql-datenbankdefinitionen [20.04.2008 22:00. ] (aktuell) – Externe Bearbeitung 127.0.0.1 | ||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
+ | ====== MySQL-Datenbankdefinitionen ====== | ||
+ | ===== User anlegen und berechtigen ===== | ||
+ | |||
+ | Unseren Datenbankuser **asteriskuser** legen wir am einfachsten über die Konsole oder via **// | ||
+ | < | ||
+ | -- Datenbankuser anlegen | ||
+ | -- | ||
+ | |||
+ | CREATE USER ' | ||
+ | |||
+ | -- -------------------------------------------------------------------- | ||
+ | |||
+ | -- | ||
+ | -- Datenbank anlegen | ||
+ | -- | ||
+ | |||
+ | CREATE DATABASE `asterisk_db`; | ||
+ | |||
+ | -- -------------------------------------------------------------------- | ||
+ | |||
+ | -- | ||
+ | -- Berechtigungen für den datenbankuser auf die Datenbank festlegen | ||
+ | -- | ||
+ | |||
+ | GRANT all on asterisk_db.* to ' | ||
+ | |||
+ | -- -------------------------------------------------------------------- | ||
+ | |||
+ | -- | ||
+ | -- Berechtigungen freigeben | ||
+ | -- | ||
+ | |||
+ | FLUSH PRIVILEGES; | ||
+ | |||
+ | -- --------------------------------------------------------------------</ | ||
+ | |||
+ | ===== Datenbanktabellen anlegen ===== | ||
+ | |||
+ | Anschließend legen wir noch die benötigten Datenbanktabellen an. Auch dieses erledigen wir am einfachsten via **// | ||
+ | < | ||
+ | -- Datenbank auswählen | ||
+ | -- | ||
+ | |||
+ | USE asterisk_db; | ||
+ | |||
+ | -- -------------------------------------------------------------------- | ||
+ | |||
+ | -- | ||
+ | -- Tabelle `cdr` anlegen | ||
+ | -- | ||
+ | |||
+ | CREATE TABLE IF NOT EXISTS `cdr` ( | ||
+ | `calldate` datetime NOT NULL default ' | ||
+ | `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 ' | ||
+ | `billsec` int(11) NOT NULL default ' | ||
+ | `disposition` varchar(45) NOT NULL default '', | ||
+ | `amaflags` int(11) NOT NULL default ' | ||
+ | `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 ' | ||
+ | `app` varchar(20) NOT NULL default '', | ||
+ | `appdata` varchar(128) NOT NULL default '', | ||
+ | PRIMARY KEY (`context`, | ||
+ | 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 ' | ||
+ | `nat` varchar(5) NOT NULL default ' | ||
+ | `type` enum(' | ||
+ | `accountcode` varchar(20) default ' | ||
+ | `amaflags` varchar(13) default NULL, | ||
+ | `callgroup` varchar(10) default NULL, | ||
+ | `callerid` varchar(80) default NULL, | ||
+ | `call-limit` varchar(6) NOT NULL default ' | ||
+ | `cancallforward` char(3) default ' | ||
+ | `canreinvite` char(3) default ' | ||
+ | `context` varchar(80) default ' | ||
+ | `defaultip` varchar(15) default NULL, | ||
+ | `dtmfmode` varchar(7) default ' | ||
+ | `fromuser` varchar(80) default NULL, | ||
+ | `fromdomain` varchar(80) default NULL, | ||
+ | `insecure` varchar(4) default NULL, | ||
+ | `language` char(2) default ' | ||
+ | `mailbox` varchar(50) default NULL, | ||
+ | `md5secret` varchar(80) default NULL, | ||
+ | `deny` varchar(95) default ' | ||
+ | `permit` varchar(95) default ' | ||
+ | `mask` varchar(95) default NULL, | ||
+ | `musiconhold` varchar(100) default ' | ||
+ | `pickupgroup` varchar(10) default NULL, | ||
+ | `qualify` char(3) default ' | ||
+ | `regexten` varchar(80) default NULL, | ||
+ | `restrictcid` char(3) default NULL, | ||
+ | `rtptimeout` char(3) default NULL, | ||
+ | `rtpholdtimeout` char(3) default NULL, | ||
+ | `secret` varchar(80) default ' | ||
+ | `setvar` varchar(100) default NULL, | ||
+ | `disallow` varchar(100) default ' | ||
+ | `allow` varchar(100) default ' | ||
+ | `fullcontact` varchar(80) NOT NULL, | ||
+ | `ipaddr` varchar(15) NOT NULL, | ||
+ | `port` smallint(5) unsigned NOT NULL default ' | ||
+ | `regserver` varchar(100) default NULL, | ||
+ | `regseconds` int(11) NOT NULL default ' | ||
+ | `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 ' | ||
+ | `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 ' | ||
+ | `context` varchar(50) NOT NULL default ' | ||
+ | `mailbox` varchar(11) NOT NULL default ' | ||
+ | `password` varchar(5) NOT NULL default ' | ||
+ | `fullname` varchar(150) NOT NULL, | ||
+ | `email` varchar(50) NOT NULL, | ||
+ | `pager` varchar(50) NOT NULL, | ||
+ | `tz` varchar(10) NOT NULL default ' | ||
+ | `attach` varchar(4) NOT NULL default ' | ||
+ | `saycid` varchar(4) NOT NULL default ' | ||
+ | `dialout` varchar(10) NOT NULL, | ||
+ | `callback` varchar(10) NOT NULL, | ||
+ | `review` varchar(4) NOT NULL default ' | ||
+ | `operator` varchar(4) NOT NULL default ' | ||
+ | `envelope` varchar(4) NOT NULL default ' | ||
+ | `sayduration` varchar(4) NOT NULL default ' | ||
+ | `saydurationm` tinyint(4) NOT NULL default ' | ||
+ | `sendvoicemail` varchar(4) NOT NULL default ' | ||
+ | `delete` varchar(4) NOT NULL default ' | ||
+ | `nextaftercmd` varchar(4) NOT NULL default ' | ||
+ | `forcename` varchar(4) NOT NULL default ' | ||
+ | `forcegreetings` varchar(4) NOT NULL default ' | ||
+ | `hidefromdir` varchar(4) NOT NULL default ' | ||
+ | `stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, | ||
+ | PRIMARY KEY (`uniqueid`), | ||
+ | KEY `mailbox_context` (`mailbox`, | ||
+ | ) 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: | ||
+ | |||
+ | < | ||
+ | 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. | ||
+ | Your MySQL connection id is 674 to server version: 5.0.22-log | ||
+ | |||
+ | Type ' | ||
+ | |||
+ | mysql> status; | ||
+ | -------------- | ||
+ | mysql Ver 14.12 Distrib 5.0.22, for redhat-linux-gnu (i686) using readline 5.0 | ||
+ | |||
+ | Connection id: 677 | ||
+ | Current database: | ||
+ | Current user: | ||
+ | SSL: Not in use | ||
+ | Current pager: | ||
+ | Using outfile: | ||
+ | Using delimiter: | ||
+ | Server version: | ||
+ | Protocol version: | ||
+ | Connection: | ||
+ | Server characterset: | ||
+ | Db | ||
+ | Client characterset: | ||
+ | Conn. characterset: | ||
+ | UNIX socket: | ||
+ | Uptime: | ||
+ | |||
+ | Threads: 4 Questions: 545412 | ||
+ | -------------- | ||
+ | |||
+ | 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 | ||
+ | +----+------+---------+-----+--------+-------------+----------+-----------+-----------------------+------------+----------------+-------------+---------+-----------+----------+----------+------------+----------+----------+---------+-----------+-----------------+-----------------+------+-------------+-------------+---------+----------+-------------+------------+----------------+----------+--------+----------+-------+-----------------------------------------+---------------+-------+--------------+------------+----------+-------------+ | ||
+ | 35 rows in set (0.00 sec)</ | ||
+ | |||
+ | Zurück zum Inhaltsverzeichnis => [[voip: |