From 7fd284c51580790f1a834a9c42401998986d9dbc Mon Sep 17 00:00:00 2001 From: tilghman Date: Tue, 13 Jul 2010 14:49:46 +0000 Subject: Merged revisions 275910 via svnmerge from https://origsvn.digium.com/svn/asterisk/trunk ................ r275910 | tilghman | 2010-07-13 09:48:40 -0500 (Tue, 13 Jul 2010) | 9 lines Merged revisions 275909 via svnmerge from https://origsvn.digium.com/svn/asterisk/branches/1.4 ........ r275909 | tilghman | 2010-07-13 09:47:30 -0500 (Tue, 13 Jul 2010) | 2 lines Move SQL scripts into their own database-specific directories. ........ ................ git-svn-id: http://svn.digium.com/svn/asterisk/branches/1.6.2@275911 f38db490-d61c-443f-a65b-d21fe96a405b --- contrib/realtime/mysql/iaxfriends.sql | 57 ++++++++++++ contrib/realtime/mysql/meetme.sql | 12 +++ contrib/realtime/mysql/sipfriends.sql | 55 ++++++++++++ contrib/realtime/mysql/voicemail.sql | 64 ++++++++++++++ contrib/realtime/postgresql/realtime.sql | 143 +++++++++++++++++++++++++++++++ 5 files changed, 331 insertions(+) create mode 100644 contrib/realtime/mysql/iaxfriends.sql create mode 100644 contrib/realtime/mysql/meetme.sql create mode 100644 contrib/realtime/mysql/sipfriends.sql create mode 100644 contrib/realtime/mysql/voicemail.sql create mode 100644 contrib/realtime/postgresql/realtime.sql (limited to 'contrib/realtime') diff --git a/contrib/realtime/mysql/iaxfriends.sql b/contrib/realtime/mysql/iaxfriends.sql new file mode 100644 index 000000000..a83d8ce46 --- /dev/null +++ b/contrib/realtime/mysql/iaxfriends.sql @@ -0,0 +1,57 @@ +# +# Table structure for table `iaxfriends` +# + +CREATE TABLE `iaxfriends` ( + `name` varchar(40) NOT NULL default '', + `type` varchar(10) NOT NULL default 'friend', -- friend/user/peer + `username` varchar(40) NULL, -- username to send as peer + `mailbox` varchar(40) NULL, -- mailbox@context + `secret` varchar(40) NULL, + `dbsecret` varchar(40) NULL, -- In AstDB, location to store/retrieve secret + `context` varchar(40) NULL, + `regcontext` varchar(40) NULL, + `host` varchar(40) NULL default 'dynamic', + `ipaddr` varchar(20) NULL, -- Must be updateable by Asterisk user + `port` int(5) NULL, -- Must be updateable by Asterisk user + `defaultip` varchar(20) NULL, + `sourceaddress` varchar(20) NULL, + `mask` varchar(20) NULL, + `regexten` varchar(40) NULL, + `regseconds` int(11) NULL, -- Must be updateable by Asterisk user + `accountcode` varchar(20) NULL, + `mohinterpret` varchar(20) NULL, + `mohsuggest` varchar(20) NULL, + `inkeys` varchar(40) NULL, + `outkey` varchar(40) NULL, + `language` varchar(10) NULL, + `callerid` varchar(100) NULL, -- The whole callerid string, or broken down in the next 3 fields + `cid_number` varchar(40) NULL, -- The number portion of the callerid + `sendani` varchar(10) NULL, -- yes/no + `fullname` varchar(40) NULL, -- The name portion of the callerid + `trunk` varchar(3) NULL, -- Yes/no + `auth` varchar(20) NULL, -- RSA/md5/plaintext + `maxauthreq` varchar(5) NULL, -- Maximum outstanding AUTHREQ calls {1-32767} + `requirecalltoken` varchar(4) NULL, -- yes/no/auto + `encryption` varchar(20) NULL, -- aes128/yes/no + `transfer` varchar(10) NULL, -- mediaonly/yes/no + `jitterbuffer` varchar(3) NULL, -- yes/no + `forcejitterbuffer` varchar(3) NULL, -- yes/no + `disallow` varchar(40) NULL, -- all/{list-of-codecs} + `allow` varchar(40) NULL, -- all/{list-of-codecs} + `codecpriority` varchar(40) NULL, + `qualify` varchar(10) NULL, -- yes/no/{number of milliseconds} + `qualifysmoothing` varchar(10) NULL, -- yes/no + `qualifyfreqok` varchar(10) NULL, -- {number of milliseconds}|60000 + `qualifyfreqnotok` varchar(10) NULL, -- {number of milliseconds}|10000 + `timezone` varchar(20) NULL, + `adsi` varchar(10) NULL, -- yes/no + `amaflags` varchar(20) NULL, + `setvar` varchar(200) NULL, + PRIMARY KEY (`name`), + INDEX name (name, host), + INDEX name2 (name, ipaddr, port), + INDEX ipaddr (ipaddr, port), + INDEX host (host, port) +); + diff --git a/contrib/realtime/mysql/meetme.sql b/contrib/realtime/mysql/meetme.sql new file mode 100644 index 000000000..19c4ed745 --- /dev/null +++ b/contrib/realtime/mysql/meetme.sql @@ -0,0 +1,12 @@ +-- +-- Table structure for Realtime meetme +-- + +CREATE TABLE meetme ( + confno char(80) DEFAULT '0' NOT NULL, + pin char(20) NULL, + adminpin char(20) NULL, + members integer DEFAULT 0 NOT NULL, + PRIMARY KEY (confno) +); + diff --git a/contrib/realtime/mysql/sipfriends.sql b/contrib/realtime/mysql/sipfriends.sql new file mode 100644 index 000000000..a9c94e8dc --- /dev/null +++ b/contrib/realtime/mysql/sipfriends.sql @@ -0,0 +1,55 @@ +# +# Table structure for table `sipfriends` +# + +CREATE TABLE `sipfriends` ( + `name` varchar(40) NOT NULL default '', + `type` varchar(10) NOT NULL default '', + `username` varchar(40), + `fromuser` varchar(40), + `fromdomain` varchar(40), + `secret` varchar(40), + `md5secret` varchar(40), + `auth` varchar(10), + `mailbox` varchar(20), + `subscribemwi` varchar(10), -- yes/no + `vmexten` varchar(20), + `callerid` varchar(40), + `cid_number` varchar(40), + `callingpres` varchar(20), + `usereqphone` varchar(10), + `language` varchar(10), + `incominglimit` varchar(10), + `context` varchar(40) NOT NULL default '', + `subscribecontext` varchar(40), + `amaflags` varchar(20), + `accountcode` varchar(20), + `musicclass` varchar(20), + `mohsuggest` varchar(20), + `allowtransfer` varchar(20), + `callgroup` varchar(20), + `pickupgroup` varchar(20), + `autoframing` varchar(10), -- yes/no + `disallow` varchar(20) default 'all', + `allow` varchar(20), + `maxcallbitrate` varchar(15), + `host` varchar(40) default 'dynamic', + `outboundproxy` varchar(40), + `ipaddr` varchar(20) NOT NULL default '', + `defaultip` varchar(20), + `port` int(6) NOT NULL default '0', + `fullcontact` varchar(40), + `insecure` varchar(20), + `qualify` varchar(15), + `regseconds` int(11) NOT NULL default '0', + `regexten` varchar(20), + `regserver` varchar(20), + `rtptimeout` varchar(15), + `rtpholdtimeout` varchar(15), + `rtpkeepalive` varchar(15), + `lastms` int(11) NOT NULL default '-1', + `setvar` varchar(200), + PRIMARY KEY (`name`), + INDEX host (host, port), + INDEX ipaddr (ipaddr, port), +) TYPE=MyISAM; diff --git a/contrib/realtime/mysql/voicemail.sql b/contrib/realtime/mysql/voicemail.sql new file mode 100644 index 000000000..c8148cfcb --- /dev/null +++ b/contrib/realtime/mysql/voicemail.sql @@ -0,0 +1,64 @@ +DROP TABLE IF EXISTS voicemail; +CREATE TABLE voicemail ( + -- All of these column names are very specific, including "uniqueid". Do not change them if you wish voicemail to work. + uniqueid INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, + -- Mailbox context. + context CHAR(80) NOT NULL DEFAULT 'default', + -- Mailbox number. Should be numeric. + mailbox CHAR(80) NOT NULL, + -- Must be numeric. Negative if you don't want it to be changed from VoicemailMain + password CHAR(80) NOT NULL, + -- Used in email and for Directory app + fullname CHAR(80), + -- Email address (will get sound file if attach=yes) + email CHAR(80), + -- Email address (won't get sound file) + pager CHAR(80), + -- Attach sound file to email - YES/no + attach CHAR(3), + -- Which sound format to attach + attachfmt CHAR(10), + -- Send email from this address + serveremail CHAR(80), + -- Prompts in alternative language + language CHAR(20), + -- Alternative timezone, as defined in voicemail.conf + tz CHAR(30), + -- Delete voicemail from server after sending email notification - yes/NO + deletevoicemail CHAR(3), + -- Read back CallerID information during playback - yes/NO + saycid CHAR(3), + -- Allow user to send voicemail from within VoicemailMain - YES/no + sendvoicemail CHAR(3), + -- Listen to voicemail and approve before sending - yes/NO + review CHAR(3), + -- Warn user a temporary greeting exists - yes/NO + tempgreetwarn CHAR(3), + -- Allow '0' to jump out during greeting - yes/NO + operator CHAR(3), + -- Hear date/time of message within VoicemailMain - YES/no + envelope CHAR(3), + -- Hear length of message within VoicemailMain - yes/NO + sayduration CHAR(3), + -- Minimum duration in minutes to say + saydurationm INT(3), + -- Force new user to record name when entering voicemail - yes/NO + forcename CHAR(3), + -- Force new user to record greetings when entering voicemail - yes/NO + forcegreetings CHAR(3), + -- Context in which to dial extension for callback + callback CHAR(80), + -- Context in which to dial extension (from advanced menu) + dialout CHAR(80), + -- Context in which to execute 0 or * escape during greeting + exitcontext CHAR(80), + -- Maximum messages in a folder (100 if not specified) + maxmsg INT(5), + -- Increase DB gain on recorded message by this amount (0.0 means none) + volgain DECIMAL(5,2), + -- IMAP user for authentication (if using IMAP storage) + imapuser VARCHAR(80), + -- IMAP password for authentication (if using IMAP storage) + imappassword VARCHAR(80), + stamp timestamp +); diff --git a/contrib/realtime/postgresql/realtime.sql b/contrib/realtime/postgresql/realtime.sql new file mode 100644 index 000000000..7eab14136 --- /dev/null +++ b/contrib/realtime/postgresql/realtime.sql @@ -0,0 +1,143 @@ +drop table extensions_conf; + +CREATE TABLE extensions_conf ( +id serial NOT NULL, +context character varying(20) DEFAULT '' NOT NULL, +exten character varying(20) DEFAULT '' NOT NULL, +priority smallint DEFAULT 0 NOT NULL, +app character varying(20) DEFAULT '' NOT NULL, +appdata character varying(128) +); + +drop table cdr; +CREATE TABLE cdr ( +calldate timestamp with time zone DEFAULT now() NOT NULL, +clid character varying(80) DEFAULT '' NOT NULL, +src character varying(80) DEFAULT '' NOT NULL, +dst character varying(80) DEFAULT '' NOT NULL, +dcontext character varying(80) DEFAULT '' NOT NULL, +channel character varying(80) DEFAULT '' NOT NULL, +dstchannel character varying(80) DEFAULT '' NOT NULL, +lastapp character varying(80) DEFAULT '' NOT NULL, +lastdata character varying(80) DEFAULT '' NOT NULL, +duration bigint DEFAULT 0::bigint NOT NULL, +billsec bigint DEFAULT 0::bigint NOT NULL, +disposition character varying(45) DEFAULT '' NOT NULL, +amaflags bigint DEFAULT 0::bigint NOT NULL, +accountcode character varying(20) DEFAULT '' NOT NULL, +uniqueid character varying(32) DEFAULT '' NOT NULL, +userfield character varying(255) DEFAULT '' NOT NULL +); + +drop table sip_conf; +CREATE TABLE sip_conf ( +id serial NOT NULL, +name character varying(80) DEFAULT '' NOT NULL, +accountcode character varying(20), +amaflags character varying(7), +callgroup character varying(10), +callerid character varying(80), +canreinvite character varying(3) DEFAULT 'yes', +context character varying(80), +defaultip character varying(15), +dtmfmode character varying(7), +fromuser character varying(80), +fromdomain character varying(80), +host character varying(31) DEFAULT '' NOT NULL, +insecure character varying(4), +"language" character varying(2), +mailbox character varying(50), +md5secret character varying(80), +nat character varying(5) DEFAULT 'no' NOT NULL, +permit character varying(95), +deny character varying(95), +mask character varying(95), +pickupgroup character varying(10), +port character varying(5) DEFAULT '' NOT NULL, +qualify character varying(3), +restrictcid character varying(1), +rtptimeout character varying(3), +rtpholdtimeout character varying(3), +secret character varying(80), +"type" character varying DEFAULT 'friend' NOT NULL, +username character varying(80) DEFAULT '' NOT NULL, +disallow character varying(100) DEFAULT 'all', +allow character varying(100) DEFAULT 'g729;ilbc;gsm;ulaw;alaw', +musiconhold character varying(100), +regseconds bigint DEFAULT 0::bigint NOT NULL, +ipaddr character varying(15) DEFAULT '' NOT NULL, +regexten character varying(80) DEFAULT '' NOT NULL, +cancallforward character varying(3) DEFAULT 'yes', +lastms integer DEFAULT -1 NOT NULL +); + +drop table voicemail_users; +CREATE TABLE voicemail_users ( +id serial NOT NULL, +customer_id bigint DEFAULT (0)::bigint NOT NULL, +context character varying(50) DEFAULT '' NOT NULL, +mailbox bigint DEFAULT (0)::bigint NOT NULL, +"password" character varying(4) DEFAULT '0' NOT NULL, +fullname character varying(50) DEFAULT '' NOT NULL, +email character varying(50) DEFAULT '' NOT NULL, +pager character varying(50) DEFAULT '' NOT NULL, +stamp timestamp(6) without time zone NOT NULL +); + +drop table queue_table; +CREATE TABLE queue_table ( +name varchar(128), +musiconhold varchar(128), +announce varchar(128), +context varchar(128), +timeout int8, +monitor_join bool, +monitor_format varchar(128), +queue_youarenext varchar(128), +queue_thereare varchar(128), +queue_callswaiting varchar(128), +queue_holdtime varchar(128), +queue_minutes varchar(128), +queue_seconds varchar(128), +queue_lessthan varchar(128), +queue_thankyou varchar(128), +queue_reporthold varchar(128), +announce_frequency int8, +announce_round_seconds int8, +announce_holdtime varchar(128), +retry int8, +wrapuptime int8, +maxlen int8, +servicelevel int8, +strategy varchar(128), +joinempty varchar(128), +leavewhenempty varchar(128), +eventmemberstatus bool, +eventwhencalled bool, +reportholdtime bool, +memberdelay int8, +weight int8, +timeoutrestart bool, +setinterfacevar bool, +PRIMARY KEY (name) +) WITHOUT OIDS; +ALTER TABLE queue_table OWNER TO asterisk; + +drop table queue_member_table; +CREATE TABLE queue_member_table +( +queue_name varchar(128), +interface varchar(128), +penalty int8, +PRIMARY KEY (queue_name, interface) +) WITHOUT OIDS; + +GRANT ALL ON TABLE cdr TO asterisk; +GRANT ALL ON TABLE extensions_conf TO asterisk; +GRANT ALL ON TABLE sip_conf TO asterisk; +GRANT ALL ON TABLE voicemail_users TO asterisk; +GRANT ALL ON TABLE queue_member_table TO asterisk; +GRANT ALL ON TABLE queue_table TO asterisk; + + + -- cgit v1.2.3