voip:asterisk:mysql-datenbankdefinitionen

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

voip:asterisk:mysql-datenbankdefinitionen [29.01.2008 20:15. ] – angelegt djangovoip: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 **//phpmyadmin//** an:
 +<code>--
 +-- 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;
 +
 +-- --------------------------------------------------------------------</code> 
 +
 +===== 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//**:
 +<code>--
 +-- 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 ;
 +
 +-- --------------------------------------------------------------------</code>
 +
 +===== Datenbankzugriff testen =====
 +
 +Zum Schluss testen wir noch, ob unser User  **asteriskuser** auch auf unsere Datenbank und den efinierten Tabellen zugreifen kann:
 +
 +<code>[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)</code>
 +
 +Zurück zum Inhaltsverzeichnis => [[voip:asterisk]]