286 lines
9.3 KiB
SQL
286 lines
9.3 KiB
SQL
USE asterisk_db;
|
||
|
||
show tables;
|
||
CREATE TABLE ps_endpoints (
|
||
id VARCHAR(40) NOT NULL PRIMARY KEY,
|
||
transport VARCHAR(40) NULL,
|
||
aors VARCHAR(200) NULL UNIQUE,
|
||
auth VARCHAR(40) NULL UNIQUE,
|
||
context VARCHAR(40) NULL,
|
||
disallow VARCHAR(200) NULL,
|
||
allow VARCHAR(200) NULL,
|
||
direct_media VARCHAR(40) NULL,
|
||
connected_line_method VARCHAR(40) NULL,
|
||
callerid VARCHAR(40) NULL,
|
||
dtmf_mode VARCHAR(40) NULL
|
||
);
|
||
|
||
CREATE TABLE ps_auths (
|
||
id VARCHAR(40) NOT NULL PRIMARY KEY,
|
||
auth_type VARCHAR(40) NOT NULL,
|
||
username VARCHAR(40) NULL UNIQUE,
|
||
password VARCHAR(40) NULL,
|
||
md5_cred VARCHAR(40) NULL,
|
||
realm VARCHAR(40) NULL
|
||
);
|
||
|
||
CREATE TABLE ps_aors (
|
||
id VARCHAR(40) NOT NULL PRIMARY KEY,
|
||
max_contacts INTEGER USE asterisk_db;
|
||
NULL
|
||
);
|
||
|
||
|
||
CREATE TABLE extensions_table (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
context VARCHAR(50) NOT NULL,
|
||
exten VARCHAR(50) NOT NULL UNIQUE,
|
||
priority INT NOT NULL,
|
||
app VARCHAR(50) NOT NULL,
|
||
appdata VARCHAR(100)
|
||
);
|
||
|
||
|
||
|
||
INSERT INTO ps_endpoints (id, transport, aors, auth, context, disallow, allow)
|
||
VALUES ('1001', 'transport-udp', '1001', '1001', 'default', 'all', 'ulaw,alaw');
|
||
|
||
INSERT INTO ps_auths (id, auth_type, username, password)
|
||
VALUES ('1001', 'userpass', '1001', '12345');
|
||
|
||
INSERT INTO ps_aors (id, max_contacts)
|
||
VALUES ('1001', 1);
|
||
|
||
|
||
-- Add Endpoint for 1002
|
||
INSERT INTO ps_endpoints (id, transport, aors, auth, context, disallow, allow, direct_media)
|
||
VALUES ('1002', 'transport-udp', '1002', '1002', 'default', 'all', 'ulaw,alaw', 'no');
|
||
|
||
-- Add Authentication for 1002
|
||
INSERT INTO ps_auths (id, auth_type, username, password)
|
||
VALUES ('1002', 'userpass', '1002', '12345');
|
||
|
||
-- Add AOR (Address of Record) for 1002
|
||
INSERT INTO ps_aors (id, max_contacts)
|
||
VALUES ('1002', 2);
|
||
|
||
INSERT INTO ps_endpoints (id, transport, aors, auth, context, disallow, allow, direct_media)
|
||
VALUES ('1003', 'transport-udp', '1003', '1003', 'default', 'all', 'ulaw,alaw', 'no');
|
||
|
||
-- Add Authentication for 1002
|
||
INSERT INTO ps_auths (id, auth_type, username, password)
|
||
VALUES ('1003', 'userpass', '1003', '12345');
|
||
|
||
-- Add AOR (Address of Record) for 1002
|
||
INSERT INTO ps_aors (id, max_contacts)
|
||
VALUES ('1003', 2);
|
||
|
||
|
||
INSERT INTO extensions_table (context, exten, priority, app, appdata)
|
||
VALUES
|
||
('default', '1001', 1, 'Dial', 'PJSIP/1001,20'),
|
||
('default', '1002', 1, 'Dial', 'PJSIP/1002,20'),
|
||
('default', '1003', 1, 'Dial', 'PJSIP/1003,20');
|
||
|
||
SELECT * FROM extensions_table;
|
||
|
||
-- DROP TABLE `extensions_table`;
|
||
|
||
SELECT * FROM extensions_table WHERE context='default';
|
||
|
||
|
||
SHOW TABLES;
|
||
select * from ps_endpoints;
|
||
select * from ps_auths;
|
||
select * from ps_aors;
|
||
select * from extensions_table;
|
||
|
||
|
||
ALTER TABLE ps_endpoints DROP COLUMN mailboxes;
|
||
ALTER TABLE ps_endpoints DROP COLUMN mohsuggest;
|
||
|
||
ALTER TABLE ps_endpoints ADD mohsuggest VARCHAR(40);
|
||
ALTER TABLE ps_endpoints ADD mailboxes VARCHAR(255);
|
||
UPDATE ps_endpoints SET mohsuggest = 'default' WHERE id IN ('1001', '1002', '1003');
|
||
|
||
UPDATE ps_aors SET max_contacts = 1 WHERE id = '1001';
|
||
|
||
show tables;
|
||
|
||
|
||
|
||
-- voice mail usertable ---
|
||
|
||
CREATE TABLE voicemail_users (
|
||
context VARCHAR(20), -- Which group or environment (usually 'default')
|
||
mailbox VARCHAR(20), -- The voicemail box number (usually same as extension)
|
||
password VARCHAR(20), -- PIN to access the voicemail
|
||
fullname VARCHAR(50), -- User’s name
|
||
email VARCHAR(100) -- Where to send the voicemail recording
|
||
);
|
||
|
||
INSERT INTO voicemail_users (context, mailbox, password, fullname, email)
|
||
VALUES
|
||
('default', '1001', '1234', 'User One', 'djangod56@gmail.com'),
|
||
('default', '1002', '1234', 'User Two', 'djangod56@gmail.com'),
|
||
('default', '1003', '1234', 'User Three','djangod56@gmail.com');
|
||
|
||
select * from voicemail_users;
|
||
|
||
-- table end --
|
||
|
||
|
||
|
||
INSERT INTO extensions_table (context, exten, priority, app, appdata) VALUES
|
||
('default', '1001', 30, 'VoiceMail', '1001@default,u'),
|
||
('default', '1001', 40, 'Hangup', ''),
|
||
('default', '1002', 30, 'VoiceMail', '1002@default,u'),
|
||
('default', '1002', 40, 'Hangup', ''),
|
||
('default', '1003', 30, 'VoiceMail', '1003@default,u'),
|
||
('default', '1003', 40, 'Hangup', '');
|
||
|
||
select * from extensions_table;
|
||
|
||
SHOW INDEXES FROM extensions_table;
|
||
|
||
ALTER TABLE extensions_table DROP INDEX exten;
|
||
|
||
|
||
ALTER TABLE extensions_table DROP PRIMARY KEY;
|
||
|
||
ALTER TABLE extensions_table ADD PRIMARY KEY (context, exten, priority);
|
||
|
||
ALTER TABLE extensions_table ADD UNIQUE INDEX unique_exten (context, exten, priority);
|
||
|
||
SELECT * FROM extensions_table WHERE exten IN ('1001', '1002', '1003') ORDER BY exten, priority;
|
||
|
||
|
||
INSERT INTO extensions_table (context, exten, priority, app, appdata)
|
||
VALUES ('default', '555', 2, 'Playback', 'vm-theperson');
|
||
|
||
delete from extensions_table where appdata = 'vm-theperson';
|
||
select * from voicemail_users;
|
||
select * from extensions_table;
|
||
|
||
SHOW TABLES;
|
||
SELECT * FROM extensions_table WHERE context = 'default' ORDER BY exten, priority;
|
||
|
||
DESCRIBE extensions_table;
|
||
SELECT * FROM extensions_table WHERE context = 'default' AND exten = '1003';
|
||
|
||
|
||
SHOW INDEXES FROM extensions_table;
|
||
|
||
|
||
|
||
|
||
|
||
UPDATE extensions_table
|
||
SET appdata = CONCAT(SUBSTRING_INDEX(appdata, ',', 1), ',su')
|
||
WHERE app = 'VoiceMail' AND priority = 2;
|
||
|
||
|
||
select * from extensions_table;
|
||
|
||
delete from extensions_table where exten ='9999';
|
||
|
||
UPDATE extensions_table
|
||
SET appdata = CONCAT(SUBSTRING_INDEX(appdata, '@', 1), '@default,s')
|
||
WHERE app = 'VoiceMail';
|
||
SELECT * FROM voicemail_users WHERE mailbox = '1002';
|
||
show tables;
|
||
select * from voicemail_users;
|
||
|
||
|
||
|
||
-- changes in dialplan ---
|
||
|
||
INSERT INTO extensions_table (context, exten, priority, app, appdata)
|
||
VALUES
|
||
('default', '1002', 6, 'GotoIf', '${DIALSTATUS}=BUSY?default,1002,30'),
|
||
('default', '1002', 7, 'GotoIf', '${DIALSTATUS}=CHANUNAVAIL?default,1002,30'),
|
||
('default', '1002', 8, 'GotoIf', '${DIALSTATUS}=NOANSWER?default,1002,30');
|
||
|
||
INSERT INTO extensions_table (context, exten, priority, app, appdata)
|
||
VALUES
|
||
('default', '1003', 6, 'GotoIf', '${DIALSTATUS}=BUSY?default,1003,30'),
|
||
('default', '1003', 7, 'GotoIf', '${DIALSTATUS}=CHANUNAVAIL?default,1003,30'),
|
||
('default', '1003', 8, 'GotoIf', '${DIALSTATUS}=NOANSWER?default,1003,30');
|
||
|
||
select * from extensions_table;
|
||
|
||
|
||
UPDATE extensions_table
|
||
SET appdata = '${DIALSTATUS}=BUSY?default,1002,30'
|
||
WHERE exten = '1002' AND priority = 6;
|
||
|
||
|
||
UPDATE extensions_table
|
||
SET appdata = '${DIALSTATUS}=CHANUNAVAIL?default,1002,30'
|
||
WHERE exten = '1002' AND priority = 7;
|
||
|
||
UPDATE extensions_table
|
||
SET appdata = '${DIALSTATUS}=NOANSWER?default,1002,30'
|
||
WHERE exten = '1002' AND priority = 8;
|
||
|
||
UPDATE extensions_table
|
||
SET appdata = '${DIALSTATUS}=BUSY?default,1003,30'
|
||
WHERE exten = '1003' AND priority = 6;
|
||
|
||
UPDATE extensions_table
|
||
SET appdata = '${DIALSTATUS}=CHANUNAVAIL?default,1003,30'
|
||
WHERE exten = '1003' AND priority = 7;
|
||
|
||
UPDATE extensions_table
|
||
SET appdata = '${DIALSTATUS}=NOANSWER?default,1003,30'
|
||
WHERE exten = '1003' AND priority = 8;
|
||
|
||
|
||
INSERT INTO extensions_table (context, exten, priority, app, appdata)
|
||
VALUES ('default', '1002', 3, 'Goto', 'default,1002,6');
|
||
|
||
INSERT INTO extensions_table (context, exten, priority, app, appdata)
|
||
VALUES ('default', '1003', 3, 'Goto', 'default,1003,6');
|
||
|
||
|
||
-- change end ---------------
|
||
|
||
|
||
desc extensions_table;
|
||
select * from extensions_table;
|
||
|
||
|
||
-- id|context|exten|priority|app |appdata |
|
||
-- --+-------+-----+--------+-----------+-----------------------------------------+
|
||
-- 1|default|1001 | 2|Dial |PJSIP/1001,20,m(default) |
|
||
-- 2|default|1002 | 2|Dial |PJSIP/1002,20,m(default) |
|
||
-- 3|default|1003 | 2|Dial |PJSIP/1003,20,m(default) |
|
||
-- 4|default|1001 | 1|MixMonitor |1001-${UNIQUEID}.wav,b |
|
||
-- 5|default|1002 | 1|MixMonitor |1002-${UNIQUEID}.wav,b |
|
||
-- 6|default|1003 | 1|MixMonitor |1003-${UNIQUEID}.wav,b |
|
||
-- 8|default|1001 | 3|GotoIf |${DIALSTATUS}=BUSY?default,1002,1 |
|
||
-- 9|default|1001 | 4|GotoIf |${DIALSTATUS}=CHANUNAVAIL?default,1002,1 |
|
||
-- 10|default|1001 | 5|GotoIf |${DIALSTATUS}=NOANSWER?default,1002,1 |
|
||
-- 11|default|1001 | 20|MusicOnHold|default |
|
||
-- 12|default|1002 | 20|MusicOnHold|default |
|
||
-- 13|default|1003 | 20|MusicOnHold|default |
|
||
-- 14|default|1001 | 30|VoiceMail |1001@default,u |
|
||
-- 15|default|1001 | 40|Hangup | |
|
||
-- 16|default|1002 | 30|VoiceMail |1002@default,u |
|
||
-- 17|default|1002 | 40|Hangup | |
|
||
-- 18|default|1003 | 30|VoiceMail |1003@default,u |
|
||
-- 19|default|1003 | 40|Hangup | |
|
||
-- 20|default|1002 | 6|GotoIf |${DIALSTATUS}=BUSY?default,1002,30 |
|
||
-- 21|default|1002 | 7|GotoIf |${DIALSTATUS}=CHANUNAVAIL?default,1002,30|
|
||
-- 22|default|1002 | 8|GotoIf |${DIALSTATUS}=NOANSWER?default,1002,30 |
|
||
-- 23|default|1003 | 6|GotoIf |${DIALSTATUS}=BUSY?default,1003,30 |
|
||
-- 24|default|1003 | 7|GotoIf |${DIALSTATUS}=CHANUNAVAIL?default,1003,30|
|
||
-- 25|default|1003 | 8|GotoIf |${DIALSTATUS}=NOANSWER?default,1003,30 |
|
||
-- 26|default|1002 | 3|Goto |default,1002,6 |
|
||
-- 27|default|1003 | 3|Goto |default,1003,6 |
|
||
|
||
|
||
|
||
|
||
|