diff options
Diffstat (limited to 'contrib')
| -rw-r--r-- | contrib/realtime/mssql/mssql_cdr.sql | 44 | ||||
| -rw-r--r-- | contrib/realtime/mssql/mssql_config.sql | 1627 | ||||
| -rw-r--r-- | contrib/realtime/mssql/mssql_voicemail.sql | 54 | ||||
| -rw-r--r-- | contrib/realtime/mysql/mysql_cdr.sql | 32 | ||||
| -rw-r--r-- | contrib/realtime/mysql/mysql_config.sql | 990 | ||||
| -rw-r--r-- | contrib/realtime/mysql/mysql_voicemail.sql | 34 | ||||
| -rw-r--r-- | contrib/realtime/oracle/oracle_cdr.sql | 38 | ||||
| -rw-r--r-- | contrib/realtime/oracle/oracle_config.sql | 1621 | ||||
| -rw-r--r-- | contrib/realtime/oracle/oracle_voicemail.sql | 48 | ||||
| -rw-r--r-- | contrib/realtime/postgresql/postgresql_cdr.sql | 36 | ||||
| -rw-r--r-- | contrib/realtime/postgresql/postgresql_config.sql | 1068 | ||||
| -rw-r--r-- | contrib/realtime/postgresql/postgresql_voicemail.sql | 38 |
12 files changed, 5630 insertions, 0 deletions
diff --git a/contrib/realtime/mssql/mssql_cdr.sql b/contrib/realtime/mssql/mssql_cdr.sql new file mode 100644 index 000000000..d342515cd --- /dev/null +++ b/contrib/realtime/mssql/mssql_cdr.sql @@ -0,0 +1,44 @@ +BEGIN TRANSACTION; + +CREATE TABLE alembic_version ( + version_num VARCHAR(32) NOT NULL +); + +GO + +-- Running upgrade -> 210693f3123d + +CREATE TABLE cdr ( + accountcode VARCHAR(20) NULL, + src VARCHAR(80) NULL, + dst VARCHAR(80) NULL, + dcontext VARCHAR(80) NULL, + clid VARCHAR(80) NULL, + channel VARCHAR(80) NULL, + dstchannel VARCHAR(80) NULL, + lastapp VARCHAR(80) NULL, + lastdata VARCHAR(80) NULL, + start DATETIME NULL, + answer DATETIME NULL, + [end] DATETIME NULL, + duration INTEGER NULL, + billsec INTEGER NULL, + disposition VARCHAR(45) NULL, + amaflags VARCHAR(45) NULL, + userfield VARCHAR(256) NULL, + uniqueid VARCHAR(150) NULL, + linkedid VARCHAR(150) NULL, + peeraccount VARCHAR(20) NULL, + sequence INTEGER NULL +); + +GO + +INSERT INTO alembic_version (version_num) VALUES ('210693f3123d'); + +GO + +COMMIT; + +GO + diff --git a/contrib/realtime/mssql/mssql_config.sql b/contrib/realtime/mssql/mssql_config.sql new file mode 100644 index 000000000..514c2e2f2 --- /dev/null +++ b/contrib/realtime/mssql/mssql_config.sql @@ -0,0 +1,1627 @@ +BEGIN TRANSACTION; + +CREATE TABLE alembic_version ( + version_num VARCHAR(32) NOT NULL +); + +GO + +-- Running upgrade -> 4da0c5f79a9c + +CREATE TABLE sippeers ( + id INTEGER NOT NULL IDENTITY(1,1), + name VARCHAR(40) NOT NULL, + ipaddr VARCHAR(45) NULL, + port INTEGER NULL, + regseconds INTEGER NULL, + defaultuser VARCHAR(40) NULL, + fullcontact VARCHAR(80) NULL, + regserver VARCHAR(20) NULL, + useragent VARCHAR(20) NULL, + lastms INTEGER NULL, + host VARCHAR(40) NULL, + type VARCHAR(6) NULL, + context VARCHAR(40) NULL, + permit VARCHAR(95) NULL, + [deny] VARCHAR(95) NULL, + secret VARCHAR(40) NULL, + md5secret VARCHAR(40) NULL, + remotesecret VARCHAR(40) NULL, + transport VARCHAR(7) NULL, + dtmfmode VARCHAR(9) NULL, + directmedia VARCHAR(6) NULL, + nat VARCHAR(29) NULL, + callgroup VARCHAR(40) NULL, + pickupgroup VARCHAR(40) NULL, + language VARCHAR(40) NULL, + disallow VARCHAR(200) NULL, + allow VARCHAR(200) NULL, + insecure VARCHAR(40) NULL, + trustrpid VARCHAR(3) NULL, + progressinband VARCHAR(5) NULL, + promiscredir VARCHAR(3) NULL, + useclientcode VARCHAR(3) NULL, + accountcode VARCHAR(40) NULL, + setvar VARCHAR(200) NULL, + callerid VARCHAR(40) NULL, + amaflags VARCHAR(40) NULL, + callcounter VARCHAR(3) NULL, + busylevel INTEGER NULL, + allowoverlap VARCHAR(3) NULL, + allowsubscribe VARCHAR(3) NULL, + videosupport VARCHAR(3) NULL, + maxcallbitrate INTEGER NULL, + rfc2833compensate VARCHAR(3) NULL, + mailbox VARCHAR(40) NULL, + [session-timers] VARCHAR(9) NULL, + [session-expires] INTEGER NULL, + [session-minse] INTEGER NULL, + [session-refresher] VARCHAR(3) NULL, + t38pt_usertpsource VARCHAR(40) NULL, + regexten VARCHAR(40) NULL, + fromdomain VARCHAR(40) NULL, + fromuser VARCHAR(40) NULL, + qualify VARCHAR(40) NULL, + defaultip VARCHAR(45) NULL, + rtptimeout INTEGER NULL, + rtpholdtimeout INTEGER NULL, + sendrpid VARCHAR(3) NULL, + outboundproxy VARCHAR(40) NULL, + callbackextension VARCHAR(40) NULL, + timert1 INTEGER NULL, + timerb INTEGER NULL, + qualifyfreq INTEGER NULL, + constantssrc VARCHAR(3) NULL, + contactpermit VARCHAR(95) NULL, + contactdeny VARCHAR(95) NULL, + usereqphone VARCHAR(3) NULL, + textsupport VARCHAR(3) NULL, + faxdetect VARCHAR(3) NULL, + buggymwi VARCHAR(3) NULL, + auth VARCHAR(40) NULL, + fullname VARCHAR(40) NULL, + trunkname VARCHAR(40) NULL, + cid_number VARCHAR(40) NULL, + callingpres VARCHAR(21) NULL, + mohinterpret VARCHAR(40) NULL, + mohsuggest VARCHAR(40) NULL, + parkinglot VARCHAR(40) NULL, + hasvoicemail VARCHAR(3) NULL, + subscribemwi VARCHAR(3) NULL, + vmexten VARCHAR(40) NULL, + autoframing VARCHAR(3) NULL, + rtpkeepalive INTEGER NULL, + [call-limit] INTEGER NULL, + g726nonstandard VARCHAR(3) NULL, + ignoresdpversion VARCHAR(3) NULL, + allowtransfer VARCHAR(3) NULL, + dynamic VARCHAR(3) NULL, + path VARCHAR(256) NULL, + supportpath VARCHAR(3) NULL, + PRIMARY KEY (id), + UNIQUE (name), + CONSTRAINT type_values CHECK (type IN ('friend', 'user', 'peer')), + CONSTRAINT sip_transport_values CHECK (transport IN ('udp', 'tcp', 'tls', 'ws', 'wss', 'udp,tcp', 'tcp,udp')), + CONSTRAINT sip_dtmfmode_values CHECK (dtmfmode IN ('rfc2833', 'info', 'shortinfo', 'inband', 'auto')), + CONSTRAINT sip_directmedia_values CHECK (directmedia IN ('yes', 'no', 'nonat', 'update')), + CONSTRAINT yes_no_values CHECK (trustrpid IN ('yes', 'no')), + CONSTRAINT sip_progressinband_values CHECK (progressinband IN ('yes', 'no', 'never')), + CONSTRAINT yes_no_values CHECK (promiscredir IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (useclientcode IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (callcounter IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (allowoverlap IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (allowsubscribe IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (videosupport IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (rfc2833compensate IN ('yes', 'no')), + CONSTRAINT sip_session_timers_values CHECK ([session-timers] IN ('accept', 'refuse', 'originate')), + CONSTRAINT sip_session_refresher_values CHECK ([session-refresher] IN ('uac', 'uas')), + CONSTRAINT yes_no_values CHECK (sendrpid IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (constantssrc IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (usereqphone IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (textsupport IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (faxdetect IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (buggymwi IN ('yes', 'no')), + CONSTRAINT sip_callingpres_values CHECK (callingpres IN ('allowed_not_screened', 'allowed_passed_screen', 'allowed_failed_screen', 'allowed', 'prohib_not_screened', 'prohib_passed_screen', 'prohib_failed_screen', 'prohib')), + CONSTRAINT yes_no_values CHECK (hasvoicemail IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (subscribemwi IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (autoframing IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (g726nonstandard IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (ignoresdpversion IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (allowtransfer IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (dynamic IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (supportpath IN ('yes', 'no')) +); + +GO + +CREATE INDEX sippeers_name ON sippeers (name); + +GO + +CREATE INDEX sippeers_name_host ON sippeers (name, host); + +GO + +CREATE INDEX sippeers_ipaddr_port ON sippeers (ipaddr, port); + +GO + +CREATE INDEX sippeers_host_port ON sippeers (host, port); + +GO + +CREATE TABLE iaxfriends ( + id INTEGER NOT NULL IDENTITY(1,1), + name VARCHAR(40) NOT NULL, + type VARCHAR(6) NULL, + username VARCHAR(40) NULL, + mailbox VARCHAR(40) NULL, + secret VARCHAR(40) NULL, + dbsecret VARCHAR(40) NULL, + context VARCHAR(40) NULL, + regcontext VARCHAR(40) NULL, + host VARCHAR(40) NULL, + ipaddr VARCHAR(40) NULL, + port INTEGER NULL, + defaultip VARCHAR(20) NULL, + sourceaddress VARCHAR(20) NULL, + mask VARCHAR(20) NULL, + regexten VARCHAR(40) NULL, + regseconds INTEGER NULL, + accountcode VARCHAR(20) NULL, + mohinterpret VARCHAR(20) NULL, + mohsuggest VARCHAR(20) NULL, + inkeys VARCHAR(40) NULL, + outkeys VARCHAR(40) NULL, + language VARCHAR(10) NULL, + callerid VARCHAR(100) NULL, + cid_number VARCHAR(40) NULL, + sendani VARCHAR(3) NULL, + fullname VARCHAR(40) NULL, + trunk VARCHAR(3) NULL, + auth VARCHAR(20) NULL, + maxauthreq INTEGER NULL, + requirecalltoken VARCHAR(4) NULL, + encryption VARCHAR(6) NULL, + transfer VARCHAR(9) NULL, + jitterbuffer VARCHAR(3) NULL, + forcejitterbuffer VARCHAR(3) NULL, + disallow VARCHAR(200) NULL, + allow VARCHAR(200) NULL, + codecpriority VARCHAR(40) NULL, + qualify VARCHAR(10) NULL, + qualifysmoothing VARCHAR(3) NULL, + qualifyfreqok VARCHAR(10) NULL, + qualifyfreqnotok VARCHAR(10) NULL, + timezone VARCHAR(20) NULL, + adsi VARCHAR(3) NULL, + amaflags VARCHAR(20) NULL, + setvar VARCHAR(200) NULL, + PRIMARY KEY (id), + UNIQUE (name), + CONSTRAINT type_values CHECK (type IN ('friend', 'user', 'peer')), + CONSTRAINT yes_no_values CHECK (sendani IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (trunk IN ('yes', 'no')), + CONSTRAINT iax_requirecalltoken_values CHECK (requirecalltoken IN ('yes', 'no', 'auto')), + CONSTRAINT iax_encryption_values CHECK (encryption IN ('yes', 'no', 'aes128')), + CONSTRAINT iax_transfer_values CHECK (transfer IN ('yes', 'no', 'mediaonly')), + CONSTRAINT yes_no_values CHECK (jitterbuffer IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (forcejitterbuffer IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (qualifysmoothing IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (adsi IN ('yes', 'no')) +); + +GO + +CREATE INDEX iaxfriends_name ON iaxfriends (name); + +GO + +CREATE INDEX iaxfriends_name_host ON iaxfriends (name, host); + +GO + +CREATE INDEX iaxfriends_name_ipaddr_port ON iaxfriends (name, ipaddr, port); + +GO + +CREATE INDEX iaxfriends_ipaddr_port ON iaxfriends (ipaddr, port); + +GO + +CREATE INDEX iaxfriends_host_port ON iaxfriends (host, port); + +GO + +CREATE TABLE voicemail ( + uniqueid INTEGER NOT NULL IDENTITY(1,1), + context VARCHAR(80) NOT NULL, + mailbox VARCHAR(80) NOT NULL, + password VARCHAR(80) NOT NULL, + fullname VARCHAR(80) NULL, + alias VARCHAR(80) NULL, + email VARCHAR(80) NULL, + pager VARCHAR(80) NULL, + attach VARCHAR(3) NULL, + attachfmt VARCHAR(10) NULL, + serveremail VARCHAR(80) NULL, + language VARCHAR(20) NULL, + tz VARCHAR(30) NULL, + deletevoicemail VARCHAR(3) NULL, + saycid VARCHAR(3) NULL, + sendvoicemail VARCHAR(3) NULL, + review VARCHAR(3) NULL, + tempgreetwarn VARCHAR(3) NULL, + operator VARCHAR(3) NULL, + envelope VARCHAR(3) NULL, + sayduration INTEGER NULL, + forcename VARCHAR(3) NULL, + forcegreetings VARCHAR(3) NULL, + callback VARCHAR(80) NULL, + dialout VARCHAR(80) NULL, + exitcontext VARCHAR(80) NULL, + maxmsg INTEGER NULL, + volgain NUMERIC(5, 2) NULL, + imapuser VARCHAR(80) NULL, + imappassword VARCHAR(80) NULL, + imapserver VARCHAR(80) NULL, + imapport VARCHAR(8) NULL, + imapflags VARCHAR(80) NULL, + stamp DATETIME NULL, + PRIMARY KEY (uniqueid), + CONSTRAINT yes_no_values CHECK (attach IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (deletevoicemail IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (saycid IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (sendvoicemail IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (review IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (tempgreetwarn IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (operator IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (envelope IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (forcename IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (forcegreetings IN ('yes', 'no')) +); + +GO + +CREATE INDEX voicemail_mailbox ON voicemail (mailbox); + +GO + +CREATE INDEX voicemail_context ON voicemail (context); + +GO + +CREATE INDEX voicemail_mailbox_context ON voicemail (mailbox, context); + +GO + +CREATE INDEX voicemail_imapuser ON voicemail (imapuser); + +GO + +CREATE TABLE meetme ( + bookid INTEGER NOT NULL IDENTITY(1,1), + confno VARCHAR(80) NOT NULL, + starttime DATETIME NULL, + endtime DATETIME NULL, + pin VARCHAR(20) NULL, + adminpin VARCHAR(20) NULL, + opts VARCHAR(20) NULL, + adminopts VARCHAR(20) NULL, + recordingfilename VARCHAR(80) NULL, + recordingformat VARCHAR(10) NULL, + maxusers INTEGER NULL, + members INTEGER NOT NULL, + PRIMARY KEY (bookid) +); + +GO + +CREATE INDEX meetme_confno_start_end ON meetme (confno, starttime, endtime); + +GO + +CREATE TABLE musiconhold ( + name VARCHAR(80) NOT NULL, + mode VARCHAR(10) NULL, + directory VARCHAR(255) NULL, + application VARCHAR(255) NULL, + digit VARCHAR(1) NULL, + sort VARCHAR(10) NULL, + format VARCHAR(10) NULL, + stamp DATETIME NULL, + PRIMARY KEY (name), + CONSTRAINT moh_mode_values CHECK (mode IN ('custom', 'files', 'mp3nb', 'quietmp3nb', 'quietmp3')) +); + +GO + +INSERT INTO alembic_version (version_num) VALUES ('4da0c5f79a9c'); + +GO + +-- Running upgrade 4da0c5f79a9c -> 43956d550a44 + +CREATE TABLE ps_endpoints ( + id VARCHAR(40) NOT NULL, + transport VARCHAR(40) NULL, + aors VARCHAR(200) NULL, + auth VARCHAR(40) NULL, + context VARCHAR(40) NULL, + disallow VARCHAR(200) NULL, + allow VARCHAR(200) NULL, + direct_media VARCHAR(3) NULL, + connected_line_method VARCHAR(8) NULL, + direct_media_method VARCHAR(8) NULL, + direct_media_glare_mitigation VARCHAR(8) NULL, + disable_direct_media_on_nat VARCHAR(3) NULL, + dtmf_mode VARCHAR(7) NULL, + external_media_address VARCHAR(40) NULL, + force_rport VARCHAR(3) NULL, + ice_support VARCHAR(3) NULL, + identify_by VARCHAR(8) NULL, + mailboxes VARCHAR(40) NULL, + moh_suggest VARCHAR(40) NULL, + outbound_auth VARCHAR(40) NULL, + outbound_proxy VARCHAR(40) NULL, + rewrite_contact VARCHAR(3) NULL, + rtp_ipv6 VARCHAR(3) NULL, + rtp_symmetric VARCHAR(3) NULL, + send_diversion VARCHAR(3) NULL, + send_pai VARCHAR(3) NULL, + send_rpid VARCHAR(3) NULL, + timers_min_se INTEGER NULL, + timers VARCHAR(8) NULL, + timers_sess_expires INTEGER NULL, + callerid VARCHAR(40) NULL, + callerid_privacy VARCHAR(23) NULL, + callerid_tag VARCHAR(40) NULL, + [100rel] VARCHAR(8) NULL, + aggregate_mwi VARCHAR(3) NULL, + trust_id_inbound VARCHAR(3) NULL, + trust_id_outbound VARCHAR(3) NULL, + use_ptime VARCHAR(3) NULL, + use_avpf VARCHAR(3) NULL, + media_encryption VARCHAR(4) NULL, + inband_progress VARCHAR(3) NULL, + call_group VARCHAR(40) NULL, + pickup_group VARCHAR(40) NULL, + named_call_group VARCHAR(40) NULL, + named_pickup_group VARCHAR(40) NULL, + device_state_busy_at INTEGER NULL, + fax_detect VARCHAR(3) NULL, + t38_udptl VARCHAR(3) NULL, + t38_udptl_ec VARCHAR(10) NULL, + t38_udptl_maxdatagram INTEGER NULL, + t38_udptl_nat VARCHAR(3) NULL, + t38_udptl_ipv6 VARCHAR(3) NULL, + tone_zone VARCHAR(40) NULL, + language VARCHAR(40) NULL, + one_touch_recording VARCHAR(3) NULL, + record_on_feature VARCHAR(40) NULL, + record_off_feature VARCHAR(40) NULL, + rtp_engine VARCHAR(40) NULL, + allow_transfer VARCHAR(3) NULL, + allow_subscribe VARCHAR(3) NULL, + sdp_owner VARCHAR(40) NULL, + sdp_session VARCHAR(40) NULL, + tos_audio INTEGER NULL, + tos_video INTEGER NULL, + cos_audio INTEGER NULL, + cos_video INTEGER NULL, + sub_min_expiry INTEGER NULL, + from_domain VARCHAR(40) NULL, + from_user VARCHAR(40) NULL, + mwi_fromuser VARCHAR(40) NULL, + dtls_verify VARCHAR(40) NULL, + dtls_rekey VARCHAR(40) NULL, + dtls_cert_file VARCHAR(200) NULL, + dtls_private_key VARCHAR(200) NULL, + dtls_cipher VARCHAR(200) NULL, + dtls_ca_file VARCHAR(200) NULL, + dtls_ca_path VARCHAR(200) NULL, + dtls_setup VARCHAR(7) NULL, + srtp_tag_32 VARCHAR(3) NULL, + UNIQUE (id), + CONSTRAINT yesno_values CHECK (direct_media IN ('yes', 'no')), + CONSTRAINT pjsip_connected_line_method_values CHECK (connected_line_method IN ('invite', 'reinvite', 'update')), + CONSTRAINT pjsip_connected_line_method_values CHECK (direct_media_method IN ('invite', 'reinvite', 'update')), + CONSTRAINT pjsip_direct_media_glare_mitigation_values CHECK (direct_media_glare_mitigation IN ('none', 'outgoing', 'incoming')), + CONSTRAINT yesno_values CHECK (disable_direct_media_on_nat IN ('yes', 'no')), + CONSTRAINT pjsip_dtmf_mode_values CHECK (dtmf_mode IN ('rfc4733', 'inband', 'info')), + CONSTRAINT yesno_values CHECK (force_rport IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (ice_support IN ('yes', 'no')), + CONSTRAINT pjsip_identify_by_values CHECK (identify_by IN ('username')), + CONSTRAINT yesno_values CHECK (rewrite_contact IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (rtp_ipv6 IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (rtp_symmetric IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (send_diversion IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (send_pai IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (send_rpid IN ('yes', 'no')), + CONSTRAINT pjsip_timer_values CHECK (timers IN ('forced', 'no', 'required', 'yes')), + CONSTRAINT pjsip_cid_privacy_values CHECK (callerid_privacy IN ('allowed_not_screened', 'allowed_passed_screened', 'allowed_failed_screened', 'allowed', 'prohib_not_screened', 'prohib_passed_screened', 'prohib_failed_screened', 'prohib', 'unavailable')), + CONSTRAINT pjsip_100rel_values CHECK ([100rel] IN ('no', 'required', 'yes')), + CONSTRAINT yesno_values CHECK (aggregate_mwi IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (trust_id_inbound IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (trust_id_outbound IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (use_ptime IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (use_avpf IN ('yes', 'no')), + CONSTRAINT pjsip_media_encryption_values CHECK (media_encryption IN ('no', 'sdes', 'dtls')), + CONSTRAINT yesno_values CHECK (inband_progress IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (fax_detect IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (t38_udptl IN ('yes', 'no')), + CONSTRAINT pjsip_t38udptl_ec_values CHECK (t38_udptl_ec IN ('none', 'fec', 'redundancy')), + CONSTRAINT yesno_values CHECK (t38_udptl_nat IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (t38_udptl_ipv6 IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (one_touch_recording IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (allow_transfer IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (allow_subscribe IN ('yes', 'no')), + CONSTRAINT pjsip_dtls_setup_values CHECK (dtls_setup IN ('active', 'passive', 'actpass')), + CONSTRAINT yesno_values CHECK (srtp_tag_32 IN ('yes', 'no')) +); + +GO + +CREATE INDEX ps_endpoints_id ON ps_endpoints (id); + +GO + +CREATE TABLE ps_auths ( + id VARCHAR(40) NOT NULL, + auth_type VARCHAR(8) NULL, + nonce_lifetime INTEGER NULL, + md5_cred VARCHAR(40) NULL, + password VARCHAR(80) NULL, + realm VARCHAR(40) NULL, + username VARCHAR(40) NULL, + UNIQUE (id), + CONSTRAINT pjsip_auth_type_values CHECK (auth_type IN ('md5', 'userpass')) +); + +GO + +CREATE INDEX ps_auths_id ON ps_auths (id); + +GO + +CREATE TABLE ps_aors ( + id VARCHAR(40) NOT NULL, + contact VARCHAR(40) NULL, + default_expiration INTEGER NULL, + mailboxes VARCHAR(80) NULL, + max_contacts INTEGER NULL, + minimum_expiration INTEGER NULL, + remove_existing VARCHAR(3) NULL, + qualify_frequency INTEGER NULL, + authenticate_qualify VARCHAR(3) NULL, + UNIQUE (id), + CONSTRAINT yesno_values CHECK (remove_existing IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (authenticate_qualify IN ('yes', 'no')) +); + +GO + +CREATE INDEX ps_aors_id ON ps_aors (id); + +GO + +CREATE TABLE ps_contacts ( + id VARCHAR(40) NOT NULL, + uri VARCHAR(40) NULL, + expiration_time VARCHAR(40) NULL, + qualify_frequency INTEGER NULL, + UNIQUE (id) +); + +GO + +CREATE INDEX ps_contacts_id ON ps_contacts (id); + +GO + +CREATE TABLE ps_domain_aliases ( + id VARCHAR(40) NOT NULL, + domain VARCHAR(80) NULL, + UNIQUE (id) +); + +GO + +CREATE INDEX ps_domain_aliases_id ON ps_domain_aliases (id); + +GO + +CREATE TABLE ps_endpoint_id_ips ( + id VARCHAR(40) NOT NULL, + endpoint VARCHAR(40) NULL, + match VARCHAR(80) NULL, + UNIQUE (id) +); + +GO + +CREATE INDEX ps_endpoint_id_ips_id ON ps_endpoint_id_ips (id); + +GO + +UPDATE alembic_version SET version_num='43956d550a44' WHERE alembic_version.version_num = '4da0c5f79a9c'; + +GO + +-- Running upgrade 43956d550a44 -> 581a4264e537 + +CREATE TABLE extensions ( + id BIGINT NOT NULL IDENTITY(1,1), + context VARCHAR(40) NOT NULL, + exten VARCHAR(40) NOT NULL, + priority INTEGER NOT NULL, + app VARCHAR(40) NOT NULL, + appdata VARCHAR(256) NOT NULL, + PRIMARY KEY (id), + UNIQUE (context, exten, priority), + UNIQUE (id) +); + +GO + +UPDATE alembic_version SET version_num='581a4264e537' WHERE alembic_version.version_num = '43956d550a44'; + +GO + +-- Running upgrade 581a4264e537 -> 2fc7930b41b3 + +CREATE TABLE ps_systems ( + id VARCHAR(40) NOT NULL, + timer_t1 INTEGER NULL, + timer_b INTEGER NULL, + compact_headers VARCHAR(3) NULL, + threadpool_initial_size INTEGER NULL, + threadpool_auto_increment INTEGER NULL, + threadpool_idle_timeout INTEGER NULL, + threadpool_max_size INTEGER NULL, + UNIQUE (id), + CONSTRAINT yesno_values CHECK (compact_headers IN ('yes', 'no')) +); + +GO + +CREATE INDEX ps_systems_id ON ps_systems (id); + +GO + +CREATE TABLE ps_globals ( + id VARCHAR(40) NOT NULL, + max_forwards INTEGER NULL, + user_agent VARCHAR(40) NULL, + default_outbound_endpoint VARCHAR(40) NULL, + UNIQUE (id) +); + +GO + +CREATE INDEX ps_globals_id ON ps_globals (id); + +GO + +CREATE TABLE ps_transports ( + id VARCHAR(40) NOT NULL, + async_operations INTEGER NULL, + bind VARCHAR(40) NULL, + ca_list_file VARCHAR(200) NULL, + cert_file VARCHAR(200) NULL, + cipher VARCHAR(200) NULL, + domain VARCHAR(40) NULL, + external_media_address VARCHAR(40) NULL, + external_signaling_address VARCHAR(40) NULL, + external_signaling_port INTEGER NULL, + method VARCHAR(11) NULL, + local_net VARCHAR(40) NULL, + password VARCHAR(40) NULL, + priv_key_file VARCHAR(200) NULL, + protocol VARCHAR(3) NULL, + require_client_cert VARCHAR(3) NULL, + verify_client VARCHAR(3) NULL, + verifiy_server VARCHAR(3) NULL, + tos VARCHAR(3) NULL, + cos VARCHAR(3) NULL, + UNIQUE (id), + CONSTRAINT pjsip_transport_method_values CHECK (method IN ('default', 'unspecified', 'tlsv1', 'sslv2', 'sslv3', 'sslv23')), + CONSTRAINT pjsip_transport_protocol_values CHECK (protocol IN ('udp', 'tcp', 'tls', 'ws', 'wss')), + CONSTRAINT yesno_values CHECK (require_client_cert IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (verify_client IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (verifiy_server IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (tos IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (cos IN ('yes', 'no')) +); + +GO + +CREATE INDEX ps_transports_id ON ps_transports (id); + +GO + +CREATE TABLE ps_registrations ( + id VARCHAR(40) NOT NULL, + auth_rejection_permanent VARCHAR(3) NULL, + client_uri VARCHAR(40) NULL, + contact_user VARCHAR(40) NULL, + expiration INTEGER NULL, + max_retries INTEGER NULL, + outbound_auth VARCHAR(40) NULL, + outbound_proxy VARCHAR(40) NULL, + retry_interval INTEGER NULL, + forbidden_retry_interval INTEGER NULL, + server_uri VARCHAR(40) NULL, + transport VARCHAR(40) NULL, + support_path VARCHAR(3) NULL, + UNIQUE (id), + CONSTRAINT yesno_values CHECK (auth_rejection_permanent IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (support_path IN ('yes', 'no')) +); + +GO + +CREATE INDEX ps_registrations_id ON ps_registrations (id); + +GO + +ALTER TABLE ps_endpoints ADD media_address VARCHAR(40) NULL; + +GO + +ALTER TABLE ps_endpoints ADD redirect_method VARCHAR(9) NULL; + +GO + +ALTER TABLE ps_endpoints ADD CONSTRAINT pjsip_redirect_method_values CHECK (redirect_method IN ('user', 'uri_core', 'uri_pjsip')); + +GO + +ALTER TABLE ps_endpoints ADD set_var TEXT NULL; + +GO + +EXEC sp_rename 'ps_endpoints.mwi_fromuser', mwi_from_user, 'COLUMN'; + +GO + +ALTER TABLE ps_contacts ADD outbound_proxy VARCHAR(40) NULL; + +GO + +ALTER TABLE ps_contacts ADD path TEXT NULL; + +GO + +ALTER TABLE ps_aors ADD maximum_expiration INTEGER NULL; + +GO + +ALTER TABLE ps_aors ADD outbound_proxy VARCHAR(40) NULL; + +GO + +ALTER TABLE ps_aors ADD support_path VARCHAR(3) NULL; + +GO + +ALTER TABLE ps_aors ADD CONSTRAINT yesno_values CHECK (support_path IN ('yes', 'no')); + +GO + +UPDATE alembic_version SET version_num='2fc7930b41b3' WHERE alembic_version.version_num = '581a4264e537'; + +GO + +-- Running upgrade 2fc7930b41b3 -> 21e526ad3040 + +ALTER TABLE ps_globals ADD debug VARCHAR(40) NULL; + +GO + +UPDATE alembic_version SET version_num='21e526ad3040' WHERE alembic_version.version_num = '2fc7930b41b3'; + +GO + +-- Running upgrade 21e526ad3040 -> 28887f25a46f + +CREATE TABLE queues ( + name VARCHAR(128) NOT NULL, + musiconhold VARCHAR(128) NULL, + announce VARCHAR(128) NULL, + context VARCHAR(128) NULL, + timeout INTEGER NULL, + ringinuse VARCHAR(3) NULL, + setinterfacevar VARCHAR(3) NULL, + setqueuevar VARCHAR(3) NULL, + setqueueentryvar VARCHAR(3) NULL, + monitor_format VARCHAR(8) NULL, + membermacro VARCHAR(512) NULL, + membergosub VARCHAR(512) NULL, + queue_youarenext VARCHAR(128) NULL, + queue_thereare VARCHAR(128) NULL, + queue_callswaiting VARCHAR(128) NULL, + queue_quantity1 VARCHAR(128) NULL, + queue_quantity2 VARCHAR(128) NULL, + queue_holdtime VARCHAR(128) NULL, + queue_minutes VARCHAR(128) NULL, + queue_minute VARCHAR(128) NULL, + queue_seconds VARCHAR(128) NULL, + queue_thankyou VARCHAR(128) NULL, + queue_callerannounce VARCHAR(128) NULL, + queue_reporthold VARCHAR(128) NULL, + announce_frequency INTEGER NULL, + announce_to_first_user VARCHAR(3) NULL, + min_announce_frequency INTEGER NULL, + announce_round_seconds INTEGER NULL, + announce_holdtime VARCHAR(128) NULL, + announce_position VARCHAR(128) NULL, + announce_position_limit INTEGER NULL, + periodic_announce VARCHAR(50) NULL, + periodic_announce_frequency INTEGER NULL, + relative_periodic_announce VARCHAR(3) NULL, + random_periodic_announce VARCHAR(3) NULL, + retry INTEGER NULL, + wrapuptime INTEGER NULL, + penaltymemberslimit INTEGER NULL, + autofill VARCHAR(3) NULL, + monitor_type VARCHAR(128) NULL, + autopause VARCHAR(3) NULL, + autopausedelay INTEGER NULL, + autopausebusy VARCHAR(3) NULL, + autopauseunavail VARCHAR(3) NULL, + maxlen INTEGER NULL, + servicelevel INTEGER NULL, + strategy VARCHAR(11) NULL, + joinempty VARCHAR(128) NULL, + leavewhenempty VARCHAR(128) NULL, + reportholdtime VARCHAR(3) NULL, + memberdelay INTEGER NULL, + weight INTEGER NULL, + timeoutrestart VARCHAR(3) NULL, + defaultrule VARCHAR(128) NULL, + timeoutpriority VARCHAR(128) NULL, + PRIMARY KEY (name), + CONSTRAINT yesno_values CHECK (ringinuse IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (setinterfacevar IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (setqueuevar IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (setqueueentryvar IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (announce_to_first_user IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (relative_periodic_announce IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (random_periodic_announce IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (autofill IN ('yes', 'no')), + CONSTRAINT queue_autopause_values CHECK (autopause IN ('yes', 'no', 'all')), + CONSTRAINT yesno_values CHECK (autopausebusy IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (autopauseunavail IN ('yes', 'no')), + CONSTRAINT queue_strategy_values CHECK (strategy IN ('ringall', 'leastrecent', 'fewestcalls', 'random', 'rrmemory', 'linear', 'wrandom', 'rrordered')), + CONSTRAINT yesno_values CHECK (reportholdtime IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (timeoutrestart IN ('yes', 'no')) +); + +GO + +CREATE TABLE queue_members ( + queue_name VARCHAR(80) NOT NULL, + interface VARCHAR(80) NOT NULL, + uniqueid VARCHAR(80) NOT NULL, + membername VARCHAR(80) NULL, + state_interface VARCHAR(80) NULL, + penalty INTEGER NULL, + paused INTEGER NULL, + PRIMARY KEY (queue_name, interface) +); + +GO + +UPDATE alembic_version SET version_num='28887f25a46f' WHERE alembic_version.version_num = '21e526ad3040'; + +GO + +-- Running upgrade 28887f25a46f -> 4c573e7135bd + +ALTER TABLE ps_endpoints ALTER COLUMN tos_audio VARCHAR(10); + +GO + +ALTER TABLE ps_endpoints ALTER COLUMN tos_video VARCHAR(10); + +GO + +ALTER TABLE ps_endpoints DROP COLUMN cos_audio; + +GO + +ALTER TABLE ps_endpoints DROP COLUMN cos_video; + +GO + +ALTER TABLE ps_endpoints ADD cos_audio INTEGER NULL; + +GO + +ALTER TABLE ps_endpoints ADD cos_video INTEGER NULL; + +GO + +ALTER TABLE ps_transports ALTER COLUMN tos VARCHAR(10); + +GO + +ALTER TABLE ps_transports DROP COLUMN cos; + +GO + +ALTER TABLE ps_transports ADD cos INTEGER NULL; + +GO + +UPDATE alembic_version SET version_num='4c573e7135bd' WHERE alembic_version.version_num = '28887f25a46f'; + +GO + +-- Running upgrade 4c573e7135bd -> 3855ee4e5f85 + +ALTER TABLE ps_endpoints ADD message_context VARCHAR(40) NULL; + +GO + +ALTER TABLE ps_contacts ADD user_agent VARCHAR(40) NULL; + +GO + +UPDATE alembic_version SET version_num='3855ee4e5f85' WHERE alembic_version.version_num = '4c573e7135bd'; + +GO + +-- Running upgrade 3855ee4e5f85 -> e96a0b8071c + +ALTER TABLE ps_globals ALTER COLUMN user_agent VARCHAR(255); + +GO + +ALTER TABLE ps_contacts ALTER COLUMN id VARCHAR(255); + +GO + +ALTER TABLE ps_contacts ALTER COLUMN uri VARCHAR(255); + +GO + +ALTER TABLE ps_contacts ALTER COLUMN user_agent VARCHAR(255); + +GO + +ALTER TABLE ps_registrations ALTER COLUMN client_uri VARCHAR(255); + +GO + +ALTER TABLE ps_registrations ALTER COLUMN server_uri VARCHAR(255); + +GO + +UPDATE alembic_version SET version_num='e96a0b8071c' WHERE alembic_version.version_num = '3855ee4e5f85'; + +GO + +-- Running upgrade e96a0b8071c -> c6d929b23a8 + +CREATE TABLE ps_subscription_persistence ( + id VARCHAR(40) NOT NULL, + packet VARCHAR(2048) NULL, + src_name VARCHAR(128) NULL, + src_port INTEGER NULL, + transport_key VARCHAR(64) NULL, + local_name VARCHAR(128) NULL, + local_port INTEGER NULL, + cseq INTEGER NULL, + tag VARCHAR(128) NULL, + endpoint VARCHAR(40) NULL, + expires INTEGER NULL, + UNIQUE (id) +); + +GO + +CREATE INDEX ps_subscription_persistence_id ON ps_subscription_persistence (id); + +GO + +UPDATE alembic_version SET version_num='c6d929b23a8' WHERE alembic_version.version_num = 'e96a0b8071c'; + +GO + +-- Running upgrade c6d929b23a8 -> 51f8cb66540e + +ALTER TABLE ps_endpoints ADD force_avp VARCHAR(3) NULL; + +GO + +ALTER TABLE ps_endpoints ADD CONSTRAINT yesno_values CHECK (force_avp IN ('yes', 'no')); + +GO + +ALTER TABLE ps_endpoints ADD media_use_received_transport VARCHAR(3) NULL; + +GO + +ALTER TABLE ps_endpoints ADD CONSTRAINT yesno_values CHECK (media_use_received_transport IN ('yes', 'no')); + +GO + +UPDATE alembic_version SET version_num='51f8cb66540e' WHERE alembic_version.version_num = 'c6d929b23a8'; + +GO + +-- Running upgrade 51f8cb66540e -> 1d50859ed02e + +ALTER TABLE ps_endpoints ADD accountcode VARCHAR(20) NULL; + +GO + +UPDATE alembic_version SET version_num='1d50859ed02e' WHERE alembic_version.version_num = '51f8cb66540e'; + +GO + +-- Running upgrade 1d50859ed02e -> 1758e8bbf6b + +ALTER TABLE sippeers ALTER COLUMN useragent VARCHAR(255); + +GO + +UPDATE alembic_version SET version_num='1758e8bbf6b' WHERE alembic_version.version_num = '1d50859ed02e'; + +GO + +-- Running upgrade 1758e8bbf6b -> 5139253c0423 + +ALTER TABLE queue_members DROP COLUMN uniqueid; + +GO + +ALTER TABLE queue_members ADD uniqueid INTEGER NOT NULL; + +GO + +ALTER TABLE queue_members ADD UNIQUE (uniqueid); + +GO + +UPDATE alembic_version SET version_num='5139253c0423' WHERE alembic_version.version_num = '1758e8bbf6b'; + +GO + +-- Running upgrade 5139253c0423 -> d39508cb8d8 + +CREATE TABLE queue_rules ( + rule_name VARCHAR(80) NOT NULL, + time VARCHAR(32) NOT NULL, + min_penalty VARCHAR(32) NOT NULL, + max_penalty VARCHAR(32) NOT NULL +); + +GO + +UPDATE alembic_version SET version_num='d39508cb8d8' WHERE alembic_version.version_num = '5139253c0423'; + +GO + +-- Running upgrade d39508cb8d8 -> 5950038a6ead + +ALTER TABLE ps_transports ALTER COLUMN verifiy_server VARCHAR(3); + +GO + +EXEC sp_rename 'ps_transports.verifiy_server', verify_server, 'COLUMN'; + +GO + +ALTER TABLE ps_transports ADD CONSTRAINT yesno_values CHECK (verifiy_server IN ('yes', 'no')); + +GO + +UPDATE alembic_version SET version_num='5950038a6ead' WHERE alembic_version.version_num = 'd39508cb8d8'; + +GO + +-- Running upgrade 5950038a6ead -> 10aedae86a32 + +ALTER TABLE sippeers DROP CONSTRAINT sip_directmedia_values; + +GO + +ALTER TABLE sippeers ALTER COLUMN directmedia VARCHAR(8); + +GO + +ALTER TABLE sippeers ADD CONSTRAINT sip_directmedia_values_v2 CHECK (directmedia IN ('yes', 'no', 'nonat', 'update', 'outgoing')); + +GO + +UPDATE alembic_version SET version_num='10aedae86a32' WHERE alembic_version.version_num = '5950038a6ead'; + +GO + +-- Running upgrade 10aedae86a32 -> eb88a14f2a + +ALTER TABLE ps_endpoints ADD media_encryption_optimistic VARCHAR(3) NULL; + +GO + +ALTER TABLE ps_endpoints ADD CONSTRAINT yesno_values CHECK (media_encryption_optimistic IN ('yes', 'no')); + +GO + +UPDATE alembic_version SET version_num='eb88a14f2a' WHERE alembic_version.version_num = '10aedae86a32'; + +GO + +-- Running upgrade eb88a14f2a -> 371a3bf4143e + +ALTER TABLE ps_endpoints ADD user_eq_phone VARCHAR(3) NULL; + +GO + +ALTER TABLE ps_endpoints ADD CONSTRAINT yesno_values CHECK (user_eq_phone IN ('yes', 'no')); + +GO + +UPDATE alembic_version SET version_num='371a3bf4143e' WHERE alembic_version.version_num = 'eb88a14f2a'; + +GO + +-- Running upgrade 371a3bf4143e -> 45e3f47c6c44 + +ALTER TABLE ps_globals ADD endpoint_identifier_order VARCHAR(40) NULL; + +GO + +UPDATE alembic_version SET version_num='45e3f47c6c44' WHERE alembic_version.version_num = '371a3bf4143e'; + +GO + +-- Running upgrade 45e3f47c6c44 -> 23530d604b96 + +ALTER TABLE ps_endpoints ADD rpid_immediate VARCHAR(3) NULL; + +GO + +ALTER TABLE ps_endpoints ADD CONSTRAINT yesno_values CHECK (rpid_immediate IN ('yes', 'no')); + +GO + +UPDATE alembic_version SET version_num='23530d604b96' WHERE alembic_version.version_num = '45e3f47c6c44'; + +GO + +-- Running upgrade 23530d604b96 -> 31cd4f4891ec + +ALTER TABLE ps_endpoints DROP CONSTRAINT pjsip_dtmf_mode_values; + +GO + +ALTER TABLE ps_endpoints ALTER COLUMN dtmf_mode VARCHAR(7); + +GO + +ALTER TABLE ps_endpoints ADD CONSTRAINT pjsip_dtmf_mode_values_v2 CHECK (dtmf_mode IN ('rfc4733', 'inband', 'info', 'auto')); + +GO + +UPDATE alembic_version SET version_num='31cd4f4891ec' WHERE alembic_version.version_num = '23530d604b96'; + +GO + +-- Running upgrade 31cd4f4891ec -> 461d7d691209 + +ALTER TABLE ps_aors ADD qualify_timeout INTEGER NULL; + +GO + +ALTER TABLE ps_contacts ADD qualify_timeout INTEGER NULL; + +GO + +UPDATE alembic_version SET version_num='461d7d691209' WHERE alembic_version.version_num = '31cd4f4891ec'; + +GO + +-- Running upgrade 461d7d691209 -> a541e0b5e89 + +ALTER TABLE ps_globals ADD max_initial_qualify_time INTEGER NULL; + +GO + +UPDATE alembic_version SET version_num='a541e0b5e89' WHERE alembic_version.version_num = '461d7d691209'; + +GO + +-- Running upgrade a541e0b5e89 -> 28b8e71e541f + +ALTER TABLE ps_endpoints ADD g726_non_standard VARCHAR(3) NULL; + +GO + +ALTER TABLE ps_endpoints ADD CONSTRAINT yesno_values CHECK (g726_non_standard IN ('yes', 'no')); + +GO + +UPDATE alembic_version SET version_num='28b8e71e541f' WHERE alembic_version.version_num = 'a541e0b5e89'; + +GO + +-- Running upgrade 28b8e71e541f -> 498357a710ae + +ALTER TABLE ps_endpoints ADD rtp_keepalive INTEGER NULL; + +GO + +UPDATE alembic_version SET version_num='498357a710ae' WHERE alembic_version.version_num = '28b8e71e541f'; + +GO + +-- Running upgrade 498357a710ae -> 26f10cadc157 + +ALTER TABLE ps_endpoints ADD rtp_timeout INTEGER NULL; + +GO + +ALTER TABLE ps_endpoints ADD rtp_timeout_hold INTEGER NULL; + +GO + +UPDATE alembic_version SET version_num='26f10cadc157' WHERE alembic_version.version_num = '498357a710ae'; + +GO + +-- Running upgrade 26f10cadc157 -> 154177371065 + +ALTER TABLE ps_globals ADD default_from_user VARCHAR(80) NULL; + +GO + +UPDATE alembic_version SET version_num='154177371065' WHERE alembic_version.version_num = '26f10cadc157'; + +GO + +-- Running upgrade 154177371065 -> 28ce1e718f05 + +ALTER TABLE ps_registrations ADD fatal_retry_interval INTEGER NULL; + +GO + +UPDATE alembic_version SET version_num='28ce1e718f05' WHERE alembic_version.version_num = '154177371065'; + +GO + +-- Running upgrade 28ce1e718f05 -> 189a235b3fd7 + +ALTER TABLE ps_globals ADD keep_alive_interval INTEGER NULL; + +GO + +UPDATE alembic_version SET version_num='189a235b3fd7' WHERE alembic_version.version_num = '28ce1e718f05'; + +GO + +-- Running upgrade 189a235b3fd7 -> 2d078ec071b7 + +ALTER TABLE ps_aors ALTER COLUMN contact VARCHAR(255); + +GO + +UPDATE alembic_version SET version_num='2d078ec071b7' WHERE alembic_version.version_num = '189a235b3fd7'; + +GO + +-- Running upgrade 2d078ec071b7 -> 26d7f3bf0fa5 + +ALTER TABLE ps_endpoints ADD bind_rtp_to_media_address VARCHAR(3) NULL; + +GO + +ALTER TABLE ps_endpoints ADD CONSTRAINT yesno_values CHECK (bind_rtp_to_media_address IN ('yes', 'no')); + +GO + +UPDATE alembic_version SET version_num='26d7f3bf0fa5' WHERE alembic_version.version_num = '2d078ec071b7'; + +GO + +-- Running upgrade 26d7f3bf0fa5 -> 136885b81223 + +ALTER TABLE ps_globals ADD regcontext VARCHAR(80) NULL; + +GO + +UPDATE alembic_version SET version_num='136885b81223' WHERE alembic_version.version_num = '26d7f3bf0fa5'; + +GO + +-- Running upgrade 136885b81223 -> 423f34ad36e2 + +ALTER TABLE ps_aors ALTER COLUMN qualify_timeout FLOAT; + +GO + +ALTER TABLE ps_contacts ALTER COLUMN qualify_timeout FLOAT; + +GO + +UPDATE alembic_version SET version_num='423f34ad36e2' WHERE alembic_version.version_num = '136885b81223'; + +GO + +-- Running upgrade 423f34ad36e2 -> dbc44d5a908 + +ALTER TABLE ps_systems ADD disable_tcp_switch VARCHAR(3) NULL; + +GO + +ALTER TABLE ps_systems ADD CONSTRAINT yesno_values CHECK (disable_tcp_switch IN ('yes', 'no')); + +GO + +ALTER TABLE ps_registrations ADD line VARCHAR(3) NULL; + +GO + +ALTER TABLE ps_registrations ADD CONSTRAINT yesno_values CHECK (line IN ('yes', 'no')); + +GO + +ALTER TABLE ps_registrations ADD endpoint VARCHAR(40) NULL; + +GO + +UPDATE alembic_version SET version_num='dbc44d5a908' WHERE alembic_version.version_num = '423f34ad36e2'; + +GO + +-- Running upgrade dbc44d5a908 -> 3bcc0b5bc2c9 + +ALTER TABLE ps_transports ADD allow_reload VARCHAR(3) NULL; + +GO + +ALTER TABLE ps_transports ADD CONSTRAINT yesno_values CHECK (allow_reload IN ('yes', 'no')); + +GO + +UPDATE alembic_version SET version_num='3bcc0b5bc2c9' WHERE alembic_version.version_num = 'dbc44d5a908'; + +GO + +-- Running upgrade 3bcc0b5bc2c9 -> 5813202e92be + +ALTER TABLE ps_globals ADD contact_expiration_check_interval INTEGER NULL; + +GO + +UPDATE alembic_version SET version_num='5813202e92be' WHERE alembic_version.version_num = '3bcc0b5bc2c9'; + +GO + +-- Running upgrade 5813202e92be -> 1c688d9a003c + +ALTER TABLE ps_globals ADD default_voicemail_extension VARCHAR(40) NULL; + +GO + +ALTER TABLE ps_aors ADD voicemail_extension VARCHAR(40) NULL; + +GO + +ALTER TABLE ps_endpoints ADD voicemail_extension VARCHAR(40) NULL; + +GO + +ALTER TABLE ps_endpoints ADD mwi_subscribe_replaces_unsolicited INTEGER NULL; + +GO + +UPDATE alembic_version SET version_num='1c688d9a003c' WHERE alembic_version.version_num = '5813202e92be'; + +GO + +-- Running upgrade 1c688d9a003c -> 8d478ab86e29 + +ALTER TABLE ps_globals ADD disable_multi_domain VARCHAR(3) NULL; + +GO + +ALTER TABLE ps_globals ADD CONSTRAINT yesno_values CHECK (disable_multi_domain IN ('yes', 'no')); + +GO + +UPDATE alembic_version SET version_num='8d478ab86e29' WHERE alembic_version.version_num = '1c688d9a003c'; + +GO + +-- Running upgrade 8d478ab86e29 -> 65eb22eb195 + +ALTER TABLE ps_globals ADD unidentified_request_count INTEGER NULL; + +GO + +ALTER TABLE ps_globals ADD unidentified_request_period INTEGER NULL; + +GO + +ALTER TABLE ps_globals ADD unidentified_request_prune_interval INTEGER NULL; + +GO + +ALTER TABLE ps_globals ADD default_realm VARCHAR(40) NULL; + +GO + +UPDATE alembic_version SET version_num='65eb22eb195' WHERE alembic_version.version_num = '8d478ab86e29'; + +GO + +-- Running upgrade 65eb22eb195 -> 81b01a191a46 + +ALTER TABLE ps_contacts ADD reg_server VARCHAR(20) NULL; + +GO + +ALTER TABLE ps_contacts ADD CONSTRAINT ps_contacts_uq UNIQUE (id, reg_server); + +GO + +UPDATE alembic_version SET version_num='81b01a191a46' WHERE alembic_version.version_num = '65eb22eb195'; + +GO + +-- Running upgrade 81b01a191a46 -> 6be31516058d + +ALTER TABLE ps_contacts ADD authenticate_qualify VARCHAR(3) NULL; + +GO + +ALTER TABLE ps_contacts ADD CONSTRAINT yesno_values CHECK (authenticate_qualify IN ('yes', 'no')); + +GO + +UPDATE alembic_version SET version_num='6be31516058d' WHERE alembic_version.version_num = '81b01a191a46'; + +GO + +-- Running upgrade 6be31516058d -> bca7113d796f + +ALTER TABLE ps_endpoints ADD [deny] VARCHAR(95) NULL; + +GO + +ALTER TABLE ps_endpoints ADD permit VARCHAR(95) NULL; + +GO + +ALTER TABLE ps_endpoints ADD acl VARCHAR(40) NULL; + +GO + +ALTER TABLE ps_endpoints ADD contact_deny VARCHAR(95) NULL; + +GO + +ALTER TABLE ps_endpoints ADD contact_permit VARCHAR(95) NULL; + +GO + +ALTER TABLE ps_endpoints ADD contact_acl VARCHAR(40) NULL; + +GO + +UPDATE alembic_version SET version_num='bca7113d796f' WHERE alembic_version.version_num = '6be31516058d'; + +GO + +-- Running upgrade bca7113d796f -> a845e4d8ade8 + +ALTER TABLE ps_contacts ADD via_addr VARCHAR(40) NULL; + +GO + +ALTER TABLE ps_contacts ADD via_port INTEGER NULL; + +GO + +ALTER TABLE ps_contacts ADD call_id VARCHAR(255) NULL; + +GO + +UPDATE alembic_version SET version_num='a845e4d8ade8' WHERE alembic_version.version_num = 'bca7113d796f'; + +GO + +-- Running upgrade a845e4d8ade8 -> ef7efc2d3964 + +ALTER TABLE ps_contacts ADD endpoint VARCHAR(40) NULL; + +GO + +ALTER TABLE ps_contacts ALTER COLUMN expiration_time BIGINT; + +GO + +CREATE INDEX ps_contacts_qualifyfreq_exp ON ps_contacts (qualify_frequency, expiration_time); + +GO + +CREATE INDEX ps_aors_qualifyfreq_contact ON ps_aors (qualify_frequency, contact); + +GO + +UPDATE alembic_version SET version_num='ef7efc2d3964' WHERE alembic_version.version_num = 'a845e4d8ade8'; + +GO + +-- Running upgrade ef7efc2d3964 -> 9deac0ae4717 + +ALTER TABLE ps_endpoints ADD subscribe_context VARCHAR(40) NULL; + +GO + +UPDATE alembic_version SET version_num='9deac0ae4717' WHERE alembic_version.version_num = 'ef7efc2d3964'; + +GO + +-- Running upgrade 9deac0ae4717 -> 4a6c67fa9b7a + +ALTER TABLE ps_endpoints ADD fax_detect_timeout INTEGER NULL; + +GO + +UPDATE alembic_version SET version_num='4a6c67fa9b7a' WHERE alembic_version.version_num = '9deac0ae4717'; + +GO + +-- Running upgrade 4a6c67fa9b7a -> c7a44a5a0851 + +ALTER TABLE ps_globals ADD mwi_tps_queue_high INTEGER NULL; + +GO + +ALTER TABLE ps_globals ADD mwi_tps_queue_low INTEGER NULL; + +GO + +ALTER TABLE ps_globals ADD mwi_disable_initial_unsolicited VARCHAR(3) NULL; + +GO + +ALTER TABLE ps_globals ADD CONSTRAINT yesno_values CHECK (mwi_disable_initial_unsolicited IN ('yes', 'no')); + +GO + +UPDATE alembic_version SET version_num='c7a44a5a0851' WHERE alembic_version.version_num = '4a6c67fa9b7a'; + +GO + +-- Running upgrade c7a44a5a0851 -> 3772f8f828da + +ALTER TABLE ps_endpoints ALTER COLUMN identify_by VARCHAR(13); + +GO + +ALTER TABLE ps_endpoints ADD CONSTRAINT pjsip_identify_by_values CHECK (identify_by IN ('username', 'auth_username')); + +GO + +UPDATE alembic_version SET version_num='3772f8f828da' WHERE alembic_version.version_num = 'c7a44a5a0851'; + +GO + +-- Running upgrade 3772f8f828da -> 4e2493ef32e6 + +ALTER TABLE ps_endpoints ADD contact_user VARCHAR(80) NULL; + +GO + +UPDATE alembic_version SET version_num='4e2493ef32e6' WHERE alembic_version.version_num = '3772f8f828da'; + +GO + +-- Running upgrade 4e2493ef32e6 -> a6ef36f1309 + +ALTER TABLE ps_globals ADD ignore_uri_user_options VARCHAR(3) NULL; + +GO + +ALTER TABLE ps_globals ADD CONSTRAINT yesno_values CHECK (ignore_uri_user_options IN ('yes', 'no')); + +GO + +UPDATE alembic_version SET version_num='a6ef36f1309' WHERE alembic_version.version_num = '4e2493ef32e6'; + +GO + +-- Running upgrade a6ef36f1309 -> 4468b4a91372 + +ALTER TABLE ps_endpoints ADD asymmetric_rtp_codec VARCHAR(3) NULL; + +GO + +ALTER TABLE ps_endpoints ADD CONSTRAINT yesno_values CHECK (asymmetric_rtp_codec IN ('yes', 'no')); + +GO + +UPDATE alembic_version SET version_num='4468b4a91372' WHERE alembic_version.version_num = 'a6ef36f1309'; + +GO + +-- Running upgrade 4468b4a91372 -> 28ab27a7826d + +ALTER TABLE ps_endpoint_id_ips ADD srv_lookups VARCHAR(3) NULL; + +GO + +ALTER TABLE ps_endpoint_id_ips ADD CONSTRAINT yesno_values CHECK (srv_lookups IN ('yes', 'no')); + +GO + +UPDATE alembic_version SET version_num='28ab27a7826d' WHERE alembic_version.version_num = '4468b4a91372'; + +GO + +-- Running upgrade 28ab27a7826d -> 465e70e8c337 + +ALTER TABLE ps_endpoint_id_ips ADD match_header VARCHAR(255) NULL; + +GO + +UPDATE alembic_version SET version_num='465e70e8c337' WHERE alembic_version.version_num = '28ab27a7826d'; + +GO + +-- Running upgrade 465e70e8c337 -> 15db7b91a97a + +ALTER TABLE ps_endpoints ADD rtcp_mux VARCHAR(3) NULL; + +GO + +ALTER TABLE ps_endpoints ADD CONSTRAINT yesno_values CHECK (rtcp_mux IN ('yes', 'no')); + +GO + +UPDATE alembic_version SET version_num='15db7b91a97a' WHERE alembic_version.version_num = '465e70e8c337'; + +GO + +-- Running upgrade 15db7b91a97a -> f638dbe2eb23 + +ALTER TABLE ps_transports ADD symmetric_transport VARCHAR(3) NULL; + +GO + +ALTER TABLE ps_transports ADD CONSTRAINT yesno_values CHECK (symmetric_transport IN ('yes', 'no')); + +GO + +ALTER TABLE ps_subscription_persistence ADD contact_uri VARCHAR(256) NULL; + +GO + +UPDATE alembic_version SET version_num='f638dbe2eb23' WHERE alembic_version.version_num = '15db7b91a97a'; + +GO + +-- Running upgrade f638dbe2eb23 -> 8fce4c573e15 + +ALTER TABLE ps_endpoints ADD allow_overlap VARCHAR(3) NULL; + +GO + +ALTER TABLE ps_endpoints ADD CONSTRAINT yesno_values CHECK (allow_overlap IN ('yes', 'no')); + +GO + +UPDATE alembic_version SET version_num='8fce4c573e15' WHERE alembic_version.version_num = 'f638dbe2eb23'; + +GO + +COMMIT; + +GO + diff --git a/contrib/realtime/mssql/mssql_voicemail.sql b/contrib/realtime/mssql/mssql_voicemail.sql new file mode 100644 index 000000000..75cf56ac6 --- /dev/null +++ b/contrib/realtime/mssql/mssql_voicemail.sql @@ -0,0 +1,54 @@ +BEGIN TRANSACTION; + +CREATE TABLE alembic_version ( + version_num VARCHAR(32) NOT NULL +); + +GO + +-- Running upgrade -> a2e9769475e + +CREATE TABLE voicemail_messages ( + dir VARCHAR(255) NOT NULL, + msgnum INTEGER NOT NULL, + context VARCHAR(80) NULL, + macrocontext VARCHAR(80) NULL, + callerid VARCHAR(80) NULL, + origtime INTEGER NULL, + duration INTEGER NULL, + recording IMAGE NULL, + flag VARCHAR(30) NULL, + category VARCHAR(30) NULL, + mailboxuser VARCHAR(30) NULL, + mailboxcontext VARCHAR(30) NULL, + msg_id VARCHAR(40) NULL +); + +GO + +ALTER TABLE voicemail_messages ADD CONSTRAINT voicemail_messages_dir_msgnum PRIMARY KEY (dir, msgnum); + +GO + +CREATE INDEX voicemail_messages_dir ON voicemail_messages (dir); + +GO + +INSERT INTO alembic_version (version_num) VALUES ('a2e9769475e'); + +GO + +-- Running upgrade a2e9769475e -> 39428242f7f5 + +ALTER TABLE voicemail_messages ALTER COLUMN recording IMAGE; + +GO + +UPDATE alembic_version SET version_num='39428242f7f5' WHERE alembic_version.version_num = 'a2e9769475e'; + +GO + +COMMIT; + +GO + diff --git a/contrib/realtime/mysql/mysql_cdr.sql b/contrib/realtime/mysql/mysql_cdr.sql new file mode 100644 index 000000000..972f69a97 --- /dev/null +++ b/contrib/realtime/mysql/mysql_cdr.sql @@ -0,0 +1,32 @@ +CREATE TABLE alembic_version ( + version_num VARCHAR(32) NOT NULL +); + +-- Running upgrade -> 210693f3123d + +CREATE TABLE cdr ( + accountcode VARCHAR(20), + src VARCHAR(80), + dst VARCHAR(80), + dcontext VARCHAR(80), + clid VARCHAR(80), + channel VARCHAR(80), + dstchannel VARCHAR(80), + lastapp VARCHAR(80), + lastdata VARCHAR(80), + start DATETIME, + answer DATETIME, + end DATETIME, + duration INTEGER, + billsec INTEGER, + disposition VARCHAR(45), + amaflags VARCHAR(45), + userfield VARCHAR(256), + uniqueid VARCHAR(150), + linkedid VARCHAR(150), + peeraccount VARCHAR(20), + sequence INTEGER +); + +INSERT INTO alembic_version (version_num) VALUES ('210693f3123d'); + diff --git a/contrib/realtime/mysql/mysql_config.sql b/contrib/realtime/mysql/mysql_config.sql new file mode 100644 index 000000000..767301799 --- /dev/null +++ b/contrib/realtime/mysql/mysql_config.sql @@ -0,0 +1,990 @@ +CREATE TABLE alembic_version ( + version_num VARCHAR(32) NOT NULL +); + +-- Running upgrade -> 4da0c5f79a9c + +CREATE TABLE sippeers ( + id INTEGER NOT NULL AUTO_INCREMENT, + name VARCHAR(40) NOT NULL, + ipaddr VARCHAR(45), + port INTEGER, + regseconds INTEGER, + defaultuser VARCHAR(40), + fullcontact VARCHAR(80), + regserver VARCHAR(20), + useragent VARCHAR(20), + lastms INTEGER, + host VARCHAR(40), + type ENUM('friend','user','peer'), + context VARCHAR(40), + permit VARCHAR(95), + deny VARCHAR(95), + secret VARCHAR(40), + md5secret VARCHAR(40), + remotesecret VARCHAR(40), + transport ENUM('udp','tcp','tls','ws','wss','udp,tcp','tcp,udp'), + dtmfmode ENUM('rfc2833','info','shortinfo','inband','auto'), + directmedia ENUM('yes','no','nonat','update'), + nat VARCHAR(29), + callgroup VARCHAR(40), + pickupgroup VARCHAR(40), + language VARCHAR(40), + disallow VARCHAR(200), + allow VARCHAR(200), + insecure VARCHAR(40), + trustrpid ENUM('yes','no'), + progressinband ENUM('yes','no','never'), + promiscredir ENUM('yes','no'), + useclientcode ENUM('yes','no'), + accountcode VARCHAR(40), + setvar VARCHAR(200), + callerid VARCHAR(40), + amaflags VARCHAR(40), + callcounter ENUM('yes','no'), + busylevel INTEGER, + allowoverlap ENUM('yes','no'), + allowsubscribe ENUM('yes','no'), + videosupport ENUM('yes','no'), + maxcallbitrate INTEGER, + rfc2833compensate ENUM('yes','no'), + mailbox VARCHAR(40), + `session-timers` ENUM('accept','refuse','originate'), + `session-expires` INTEGER, + `session-minse` INTEGER, + `session-refresher` ENUM('uac','uas'), + t38pt_usertpsource VARCHAR(40), + regexten VARCHAR(40), + fromdomain VARCHAR(40), + fromuser VARCHAR(40), + qualify VARCHAR(40), + defaultip VARCHAR(45), + rtptimeout INTEGER, + rtpholdtimeout INTEGER, + sendrpid ENUM('yes','no'), + outboundproxy VARCHAR(40), + callbackextension VARCHAR(40), + timert1 INTEGER, + timerb INTEGER, + qualifyfreq INTEGER, + constantssrc ENUM('yes','no'), + contactpermit VARCHAR(95), + contactdeny VARCHAR(95), + usereqphone ENUM('yes','no'), + textsupport ENUM('yes','no'), + faxdetect ENUM('yes','no'), + buggymwi ENUM('yes','no'), + auth VARCHAR(40), + fullname VARCHAR(40), + trunkname VARCHAR(40), + cid_number VARCHAR(40), + callingpres ENUM('allowed_not_screened','allowed_passed_screen','allowed_failed_screen','allowed','prohib_not_screened','prohib_passed_screen','prohib_failed_screen','prohib'), + mohinterpret VARCHAR(40), + mohsuggest VARCHAR(40), + parkinglot VARCHAR(40), + hasvoicemail ENUM('yes','no'), + subscribemwi ENUM('yes','no'), + vmexten VARCHAR(40), + autoframing ENUM('yes','no'), + rtpkeepalive INTEGER, + `call-limit` INTEGER, + g726nonstandard ENUM('yes','no'), + ignoresdpversion ENUM('yes','no'), + allowtransfer ENUM('yes','no'), + dynamic ENUM('yes','no'), + path VARCHAR(256), + supportpath ENUM('yes','no'), + PRIMARY KEY (id), + UNIQUE (name) +); + +CREATE INDEX sippeers_name ON sippeers (name); + +CREATE INDEX sippeers_name_host ON sippeers (name, host); + +CREATE INDEX sippeers_ipaddr_port ON sippeers (ipaddr, port); + +CREATE INDEX sippeers_host_port ON sippeers (host, port); + +CREATE TABLE iaxfriends ( + id INTEGER NOT NULL AUTO_INCREMENT, + name VARCHAR(40) NOT NULL, + type ENUM('friend','user','peer'), + username VARCHAR(40), + mailbox VARCHAR(40), + secret VARCHAR(40), + dbsecret VARCHAR(40), + context VARCHAR(40), + regcontext VARCHAR(40), + host VARCHAR(40), + ipaddr VARCHAR(40), + port INTEGER, + defaultip VARCHAR(20), + sourceaddress VARCHAR(20), + mask VARCHAR(20), + regexten VARCHAR(40), + regseconds INTEGER, + accountcode VARCHAR(20), + mohinterpret VARCHAR(20), + mohsuggest VARCHAR(20), + inkeys VARCHAR(40), + outkeys VARCHAR(40), + language VARCHAR(10), + callerid VARCHAR(100), + cid_number VARCHAR(40), + sendani ENUM('yes','no'), + fullname VARCHAR(40), + trunk ENUM('yes','no'), + auth VARCHAR(20), + maxauthreq INTEGER, + requirecalltoken ENUM('yes','no','auto'), + encryption ENUM('yes','no','aes128'), + transfer ENUM('yes','no','mediaonly'), + jitterbuffer ENUM('yes','no'), + forcejitterbuffer ENUM('yes','no'), + disallow VARCHAR(200), + allow VARCHAR(200), + codecpriority VARCHAR(40), + qualify VARCHAR(10), + qualifysmoothing ENUM('yes','no'), + qualifyfreqok VARCHAR(10), + qualifyfreqnotok VARCHAR(10), + timezone VARCHAR(20), + adsi ENUM('yes','no'), + amaflags VARCHAR(20), + setvar VARCHAR(200), + PRIMARY KEY (id), + UNIQUE (name) +); + +CREATE INDEX iaxfriends_name ON iaxfriends (name); + +CREATE INDEX iaxfriends_name_host ON iaxfriends (name, host); + +CREATE INDEX iaxfriends_name_ipaddr_port ON iaxfriends (name, ipaddr, port); + +CREATE INDEX iaxfriends_ipaddr_port ON iaxfriends (ipaddr, port); + +CREATE INDEX iaxfriends_host_port ON iaxfriends (host, port); + +CREATE TABLE voicemail ( + uniqueid INTEGER NOT NULL AUTO_INCREMENT, + context VARCHAR(80) NOT NULL, + mailbox VARCHAR(80) NOT NULL, + password VARCHAR(80) NOT NULL, + fullname VARCHAR(80), + alias VARCHAR(80), + email VARCHAR(80), + pager VARCHAR(80), + attach ENUM('yes','no'), + attachfmt VARCHAR(10), + serveremail VARCHAR(80), + language VARCHAR(20), + tz VARCHAR(30), + deletevoicemail ENUM('yes','no'), + saycid ENUM('yes','no'), + sendvoicemail ENUM('yes','no'), + review ENUM('yes','no'), + tempgreetwarn ENUM('yes','no'), + operator ENUM('yes','no'), + envelope ENUM('yes','no'), + sayduration INTEGER, + forcename ENUM('yes','no'), + forcegreetings ENUM('yes','no'), + callback VARCHAR(80), + dialout VARCHAR(80), + exitcontext VARCHAR(80), + maxmsg INTEGER, + volgain NUMERIC(5, 2), + imapuser VARCHAR(80), + imappassword VARCHAR(80), + imapserver VARCHAR(80), + imapport VARCHAR(8), + imapflags VARCHAR(80), + stamp DATETIME, + PRIMARY KEY (uniqueid) +); + +CREATE INDEX voicemail_mailbox ON voicemail (mailbox); + +CREATE INDEX voicemail_context ON voicemail (context); + +CREATE INDEX voicemail_mailbox_context ON voicemail (mailbox, context); + +CREATE INDEX voicemail_imapuser ON voicemail (imapuser); + +CREATE TABLE meetme ( + bookid INTEGER NOT NULL AUTO_INCREMENT, + confno VARCHAR(80) NOT NULL, + starttime DATETIME, + endtime DATETIME, + pin VARCHAR(20), + adminpin VARCHAR(20), + opts VARCHAR(20), + adminopts VARCHAR(20), + recordingfilename VARCHAR(80), + recordingformat VARCHAR(10), + maxusers INTEGER, + members INTEGER NOT NULL, + PRIMARY KEY (bookid) +); + +CREATE INDEX meetme_confno_start_end ON meetme (confno, starttime, endtime); + +CREATE TABLE musiconhold ( + name VARCHAR(80) NOT NULL, + mode ENUM('custom','files','mp3nb','quietmp3nb','quietmp3'), + directory VARCHAR(255), + application VARCHAR(255), + digit VARCHAR(1), + sort VARCHAR(10), + format VARCHAR(10), + stamp DATETIME, + PRIMARY KEY (name) +); + +INSERT INTO alembic_version (version_num) VALUES ('4da0c5f79a9c'); + +-- Running upgrade 4da0c5f79a9c -> 43956d550a44 + +CREATE TABLE ps_endpoints ( + id VARCHAR(40) NOT NULL, + transport VARCHAR(40), + aors VARCHAR(200), + auth VARCHAR(40), + context VARCHAR(40), + disallow VARCHAR(200), + allow VARCHAR(200), + direct_media ENUM('yes','no'), + connected_line_method ENUM('invite','reinvite','update'), + direct_media_method ENUM('invite','reinvite','update'), + direct_media_glare_mitigation ENUM('none','outgoing','incoming'), + disable_direct_media_on_nat ENUM('yes','no'), + dtmf_mode ENUM('rfc4733','inband','info'), + external_media_address VARCHAR(40), + force_rport ENUM('yes','no'), + ice_support ENUM('yes','no'), + identify_by ENUM('username'), + mailboxes VARCHAR(40), + moh_suggest VARCHAR(40), + outbound_auth VARCHAR(40), + outbound_proxy VARCHAR(40), + rewrite_contact ENUM('yes','no'), + rtp_ipv6 ENUM('yes','no'), + rtp_symmetric ENUM('yes','no'), + send_diversion ENUM('yes','no'), + send_pai ENUM('yes','no'), + send_rpid ENUM('yes','no'), + timers_min_se INTEGER, + timers ENUM('forced','no','required','yes'), + timers_sess_expires INTEGER, + callerid VARCHAR(40), + callerid_privacy ENUM('allowed_not_screened','allowed_passed_screened','allowed_failed_screened','allowed','prohib_not_screened','prohib_passed_screened','prohib_failed_screened','prohib','unavailable'), + callerid_tag VARCHAR(40), + `100rel` ENUM('no','required','yes'), + aggregate_mwi ENUM('yes','no'), + trust_id_inbound ENUM('yes','no'), + trust_id_outbound ENUM('yes','no'), + use_ptime ENUM('yes','no'), + use_avpf ENUM('yes','no'), + media_encryption ENUM('no','sdes','dtls'), + inband_progress ENUM('yes','no'), + call_group VARCHAR(40), + pickup_group VARCHAR(40), + named_call_group VARCHAR(40), + named_pickup_group VARCHAR(40), + device_state_busy_at INTEGER, + fax_detect ENUM('yes','no'), + t38_udptl ENUM('yes','no'), + t38_udptl_ec ENUM('none','fec','redundancy'), + t38_udptl_maxdatagram INTEGER, + t38_udptl_nat ENUM('yes','no'), + t38_udptl_ipv6 ENUM('yes','no'), + tone_zone VARCHAR(40), + language VARCHAR(40), + one_touch_recording ENUM('yes','no'), + record_on_feature VARCHAR(40), + record_off_feature VARCHAR(40), + rtp_engine VARCHAR(40), + allow_transfer ENUM('yes','no'), + allow_subscribe ENUM('yes','no'), + sdp_owner VARCHAR(40), + sdp_session VARCHAR(40), + tos_audio INTEGER, + tos_video INTEGER, + cos_audio INTEGER, + cos_video INTEGER, + sub_min_expiry INTEGER, + from_domain VARCHAR(40), + from_user VARCHAR(40), + mwi_fromuser VARCHAR(40), + dtls_verify VARCHAR(40), + dtls_rekey VARCHAR(40), + dtls_cert_file VARCHAR(200), + dtls_private_key VARCHAR(200), + dtls_cipher VARCHAR(200), + dtls_ca_file VARCHAR(200), + dtls_ca_path VARCHAR(200), + dtls_setup ENUM('active','passive','actpass'), + srtp_tag_32 ENUM('yes','no'), + UNIQUE (id) +); + +CREATE INDEX ps_endpoints_id ON ps_endpoints (id); + +CREATE TABLE ps_auths ( + id VARCHAR(40) NOT NULL, + auth_type ENUM('md5','userpass'), + nonce_lifetime INTEGER, + md5_cred VARCHAR(40), + password VARCHAR(80), + realm VARCHAR(40), + username VARCHAR(40), + UNIQUE (id) +); + +CREATE INDEX ps_auths_id ON ps_auths (id); + +CREATE TABLE ps_aors ( + id VARCHAR(40) NOT NULL, + contact VARCHAR(40), + default_expiration INTEGER, + mailboxes VARCHAR(80), + max_contacts INTEGER, + minimum_expiration INTEGER, + remove_existing ENUM('yes','no'), + qualify_frequency INTEGER, + authenticate_qualify ENUM('yes','no'), + UNIQUE (id) +); + +CREATE INDEX ps_aors_id ON ps_aors (id); + +CREATE TABLE ps_contacts ( + id VARCHAR(40) NOT NULL, + uri VARCHAR(40), + expiration_time VARCHAR(40), + qualify_frequency INTEGER, + UNIQUE (id) +); + +CREATE INDEX ps_contacts_id ON ps_contacts (id); + +CREATE TABLE ps_domain_aliases ( + id VARCHAR(40) NOT NULL, + domain VARCHAR(80), + UNIQUE (id) +); + +CREATE INDEX ps_domain_aliases_id ON ps_domain_aliases (id); + +CREATE TABLE ps_endpoint_id_ips ( + id VARCHAR(40) NOT NULL, + endpoint VARCHAR(40), + `match` VARCHAR(80), + UNIQUE (id) +); + +CREATE INDEX ps_endpoint_id_ips_id ON ps_endpoint_id_ips (id); + +UPDATE alembic_version SET version_num='43956d550a44' WHERE alembic_version.version_num = '4da0c5f79a9c'; + +-- Running upgrade 43956d550a44 -> 581a4264e537 + +CREATE TABLE extensions ( + id BIGINT NOT NULL AUTO_INCREMENT, + context VARCHAR(40) NOT NULL, + exten VARCHAR(40) NOT NULL, + priority INTEGER NOT NULL, + app VARCHAR(40) NOT NULL, + appdata VARCHAR(256) NOT NULL, + PRIMARY KEY (id), + UNIQUE (context, exten, priority), + UNIQUE (id) +); + +UPDATE alembic_version SET version_num='581a4264e537' WHERE alembic_version.version_num = '43956d550a44'; + +-- Running upgrade 581a4264e537 -> 2fc7930b41b3 + +CREATE TABLE ps_systems ( + id VARCHAR(40) NOT NULL, + timer_t1 INTEGER, + timer_b INTEGER, + compact_headers ENUM('yes','no'), + threadpool_initial_size INTEGER, + threadpool_auto_increment INTEGER, + threadpool_idle_timeout INTEGER, + threadpool_max_size INTEGER, + UNIQUE (id) +); + +CREATE INDEX ps_systems_id ON ps_systems (id); + +CREATE TABLE ps_globals ( + id VARCHAR(40) NOT NULL, + max_forwards INTEGER, + user_agent VARCHAR(40), + default_outbound_endpoint VARCHAR(40), + UNIQUE (id) +); + +CREATE INDEX ps_globals_id ON ps_globals (id); + +CREATE TABLE ps_transports ( + id VARCHAR(40) NOT NULL, + async_operations INTEGER, + bind VARCHAR(40), + ca_list_file VARCHAR(200), + cert_file VARCHAR(200), + cipher VARCHAR(200), + domain VARCHAR(40), + external_media_address VARCHAR(40), + external_signaling_address VARCHAR(40), + external_signaling_port INTEGER, + method ENUM('default','unspecified','tlsv1','sslv2','sslv3','sslv23'), + local_net VARCHAR(40), + password VARCHAR(40), + priv_key_file VARCHAR(200), + protocol ENUM('udp','tcp','tls','ws','wss'), + require_client_cert ENUM('yes','no'), + verify_client ENUM('yes','no'), + verifiy_server ENUM('yes','no'), + tos ENUM('yes','no'), + cos ENUM('yes','no'), + UNIQUE (id) +); + +CREATE INDEX ps_transports_id ON ps_transports (id); + +CREATE TABLE ps_registrations ( + id VARCHAR(40) NOT NULL, + auth_rejection_permanent ENUM('yes','no'), + client_uri VARCHAR(40), + contact_user VARCHAR(40), + expiration INTEGER, + max_retries INTEGER, + outbound_auth VARCHAR(40), + outbound_proxy VARCHAR(40), + retry_interval INTEGER, + forbidden_retry_interval INTEGER, + server_uri VARCHAR(40), + transport VARCHAR(40), + support_path ENUM('yes','no'), + UNIQUE (id) +); + +CREATE INDEX ps_registrations_id ON ps_registrations (id); + +ALTER TABLE ps_endpoints ADD COLUMN media_address VARCHAR(40); + +ALTER TABLE ps_endpoints ADD COLUMN redirect_method ENUM('user','uri_core','uri_pjsip'); + +ALTER TABLE ps_endpoints ADD COLUMN set_var TEXT; + +ALTER TABLE ps_endpoints CHANGE mwi_fromuser mwi_from_user VARCHAR(40) NULL; + +ALTER TABLE ps_contacts ADD COLUMN outbound_proxy VARCHAR(40); + +ALTER TABLE ps_contacts ADD COLUMN path TEXT; + +ALTER TABLE ps_aors ADD COLUMN maximum_expiration INTEGER; + +ALTER TABLE ps_aors ADD COLUMN outbound_proxy VARCHAR(40); + +ALTER TABLE ps_aors ADD COLUMN support_path ENUM('yes','no'); + +UPDATE alembic_version SET version_num='2fc7930b41b3' WHERE alembic_version.version_num = '581a4264e537'; + +-- Running upgrade 2fc7930b41b3 -> 21e526ad3040 + +ALTER TABLE ps_globals ADD COLUMN debug VARCHAR(40); + +UPDATE alembic_version SET version_num='21e526ad3040' WHERE alembic_version.version_num = '2fc7930b41b3'; + +-- Running upgrade 21e526ad3040 -> 28887f25a46f + +CREATE TABLE queues ( + name VARCHAR(128) NOT NULL, + musiconhold VARCHAR(128), + announce VARCHAR(128), + context VARCHAR(128), + timeout INTEGER, + ringinuse ENUM('yes','no'), + setinterfacevar ENUM('yes','no'), + setqueuevar ENUM('yes','no'), + setqueueentryvar ENUM('yes','no'), + monitor_format VARCHAR(8), + membermacro VARCHAR(512), + membergosub VARCHAR(512), + queue_youarenext VARCHAR(128), + queue_thereare VARCHAR(128), + queue_callswaiting VARCHAR(128), + queue_quantity1 VARCHAR(128), + queue_quantity2 VARCHAR(128), + queue_holdtime VARCHAR(128), + queue_minutes VARCHAR(128), + queue_minute VARCHAR(128), + queue_seconds VARCHAR(128), + queue_thankyou VARCHAR(128), + queue_callerannounce VARCHAR(128), + queue_reporthold VARCHAR(128), + announce_frequency INTEGER, + announce_to_first_user ENUM('yes','no'), + min_announce_frequency INTEGER, + announce_round_seconds INTEGER, + announce_holdtime VARCHAR(128), + announce_position VARCHAR(128), + announce_position_limit INTEGER, + periodic_announce VARCHAR(50), + periodic_announce_frequency INTEGER, + relative_periodic_announce ENUM('yes','no'), + random_periodic_announce ENUM('yes','no'), + retry INTEGER, + wrapuptime INTEGER, + penaltymemberslimit INTEGER, + autofill ENUM('yes','no'), + monitor_type VARCHAR(128), + autopause ENUM('yes','no','all'), + autopausedelay INTEGER, + autopausebusy ENUM('yes','no'), + autopauseunavail ENUM('yes','no'), + maxlen INTEGER, + servicelevel INTEGER, + strategy ENUM('ringall','leastrecent','fewestcalls','random','rrmemory','linear','wrandom','rrordered'), + joinempty VARCHAR(128), + leavewhenempty VARCHAR(128), + reportholdtime ENUM('yes','no'), + memberdelay INTEGER, + weight INTEGER, + timeoutrestart ENUM('yes','no'), + defaultrule VARCHAR(128), + timeoutpriority VARCHAR(128), + PRIMARY KEY (name) +); + +CREATE TABLE queue_members ( + queue_name VARCHAR(80) NOT NULL, + interface VARCHAR(80) NOT NULL, + uniqueid VARCHAR(80) NOT NULL, + membername VARCHAR(80), + state_interface VARCHAR(80), + penalty INTEGER, + paused INTEGER, + PRIMARY KEY (queue_name, interface) +); + +UPDATE alembic_version SET version_num='28887f25a46f' WHERE alembic_version.version_num = '21e526ad3040'; + +-- Running upgrade 28887f25a46f -> 4c573e7135bd + +ALTER TABLE ps_endpoints MODIFY tos_audio VARCHAR(10) NULL; + +ALTER TABLE ps_endpoints MODIFY tos_video VARCHAR(10) NULL; + +ALTER TABLE ps_endpoints DROP COLUMN cos_audio; + +ALTER TABLE ps_endpoints DROP COLUMN cos_video; + +ALTER TABLE ps_endpoints ADD COLUMN cos_audio INTEGER; + +ALTER TABLE ps_endpoints ADD COLUMN cos_video INTEGER; + +ALTER TABLE ps_transports MODIFY tos VARCHAR(10) NULL; + +ALTER TABLE ps_transports DROP COLUMN cos; + +ALTER TABLE ps_transports ADD COLUMN cos INTEGER; + +UPDATE alembic_version SET version_num='4c573e7135bd' WHERE alembic_version.version_num = '28887f25a46f'; + +-- Running upgrade 4c573e7135bd -> 3855ee4e5f85 + +ALTER TABLE ps_endpoints ADD COLUMN message_context VARCHAR(40); + +ALTER TABLE ps_contacts ADD COLUMN user_agent VARCHAR(40); + +UPDATE alembic_version SET version_num='3855ee4e5f85' WHERE alembic_version.version_num = '4c573e7135bd'; + +-- Running upgrade 3855ee4e5f85 -> e96a0b8071c + +ALTER TABLE ps_globals MODIFY user_agent VARCHAR(255) NULL; + +ALTER TABLE ps_contacts MODIFY id VARCHAR(255) NULL; + +ALTER TABLE ps_contacts MODIFY uri VARCHAR(255) NULL; + +ALTER TABLE ps_contacts MODIFY user_agent VARCHAR(255) NULL; + +ALTER TABLE ps_registrations MODIFY client_uri VARCHAR(255) NULL; + +ALTER TABLE ps_registrations MODIFY server_uri VARCHAR(255) NULL; + +UPDATE alembic_version SET version_num='e96a0b8071c' WHERE alembic_version.version_num = '3855ee4e5f85'; + +-- Running upgrade e96a0b8071c -> c6d929b23a8 + +CREATE TABLE ps_subscription_persistence ( + id VARCHAR(40) NOT NULL, + packet VARCHAR(2048), + src_name VARCHAR(128), + src_port INTEGER, + transport_key VARCHAR(64), + local_name VARCHAR(128), + local_port INTEGER, + cseq INTEGER, + tag VARCHAR(128), + endpoint VARCHAR(40), + expires INTEGER, + UNIQUE (id) +); + +CREATE INDEX ps_subscription_persistence_id ON ps_subscription_persistence (id); + +UPDATE alembic_version SET version_num='c6d929b23a8' WHERE alembic_version.version_num = 'e96a0b8071c'; + +-- Running upgrade c6d929b23a8 -> 51f8cb66540e + +ALTER TABLE ps_endpoints ADD COLUMN force_avp ENUM('yes','no'); + +ALTER TABLE ps_endpoints ADD COLUMN media_use_received_transport ENUM('yes','no'); + +UPDATE alembic_version SET version_num='51f8cb66540e' WHERE alembic_version.version_num = 'c6d929b23a8'; + +-- Running upgrade 51f8cb66540e -> 1d50859ed02e + +ALTER TABLE ps_endpoints ADD COLUMN accountcode VARCHAR(20); + +UPDATE alembic_version SET version_num='1d50859ed02e' WHERE alembic_version.version_num = '51f8cb66540e'; + +-- Running upgrade 1d50859ed02e -> 1758e8bbf6b + +ALTER TABLE sippeers MODIFY useragent VARCHAR(255) NULL; + +UPDATE alembic_version SET version_num='1758e8bbf6b' WHERE alembic_version.version_num = '1d50859ed02e'; + +-- Running upgrade 1758e8bbf6b -> 5139253c0423 + +ALTER TABLE queue_members DROP COLUMN uniqueid; + +ALTER TABLE queue_members ADD COLUMN uniqueid INTEGER NOT NULL; + +ALTER TABLE queue_members ADD UNIQUE (uniqueid); + +ALTER TABLE queue_members MODIFY uniqueid INTEGER NOT NULL AUTO_INCREMENT; + +UPDATE alembic_version SET version_num='5139253c0423' WHERE alembic_version.version_num = '1758e8bbf6b'; + +-- Running upgrade 5139253c0423 -> d39508cb8d8 + +CREATE TABLE queue_rules ( + rule_name VARCHAR(80) NOT NULL, + time VARCHAR(32) NOT NULL, + min_penalty VARCHAR(32) NOT NULL, + max_penalty VARCHAR(32) NOT NULL +); + +UPDATE alembic_version SET version_num='d39508cb8d8' WHERE alembic_version.version_num = '5139253c0423'; + +-- Running upgrade d39508cb8d8 -> 5950038a6ead + +ALTER TABLE ps_transports CHANGE verifiy_server verify_server ENUM('yes','no') NULL; + +UPDATE alembic_version SET version_num='5950038a6ead' WHERE alembic_version.version_num = 'd39508cb8d8'; + +-- Running upgrade 5950038a6ead -> 10aedae86a32 + +ALTER TABLE sippeers MODIFY directmedia ENUM('yes','no','nonat','update','outgoing') NULL; + +UPDATE alembic_version SET version_num='10aedae86a32' WHERE alembic_version.version_num = '5950038a6ead'; + +-- Running upgrade 10aedae86a32 -> eb88a14f2a + +ALTER TABLE ps_endpoints ADD COLUMN media_encryption_optimistic ENUM('yes','no'); + +UPDATE alembic_version SET version_num='eb88a14f2a' WHERE alembic_version.version_num = '10aedae86a32'; + +-- Running upgrade eb88a14f2a -> 371a3bf4143e + +ALTER TABLE ps_endpoints ADD COLUMN user_eq_phone ENUM('yes','no'); + +UPDATE alembic_version SET version_num='371a3bf4143e' WHERE alembic_version.version_num = 'eb88a14f2a'; + +-- Running upgrade 371a3bf4143e -> 45e3f47c6c44 + +ALTER TABLE ps_globals ADD COLUMN endpoint_identifier_order VARCHAR(40); + +UPDATE alembic_version SET version_num='45e3f47c6c44' WHERE alembic_version.version_num = '371a3bf4143e'; + +-- Running upgrade 45e3f47c6c44 -> 23530d604b96 + +ALTER TABLE ps_endpoints ADD COLUMN rpid_immediate ENUM('yes','no'); + +UPDATE alembic_version SET version_num='23530d604b96' WHERE alembic_version.version_num = '45e3f47c6c44'; + +-- Running upgrade 23530d604b96 -> 31cd4f4891ec + +ALTER TABLE ps_endpoints MODIFY dtmf_mode ENUM('rfc4733','inband','info','auto') NULL; + +UPDATE alembic_version SET version_num='31cd4f4891ec' WHERE alembic_version.version_num = '23530d604b96'; + +-- Running upgrade 31cd4f4891ec -> 461d7d691209 + +ALTER TABLE ps_aors ADD COLUMN qualify_timeout INTEGER; + +ALTER TABLE ps_contacts ADD COLUMN qualify_timeout INTEGER; + +UPDATE alembic_version SET version_num='461d7d691209' WHERE alembic_version.version_num = '31cd4f4891ec'; + +-- Running upgrade 461d7d691209 -> a541e0b5e89 + +ALTER TABLE ps_globals ADD COLUMN max_initial_qualify_time INTEGER; + +UPDATE alembic_version SET version_num='a541e0b5e89' WHERE alembic_version.version_num = '461d7d691209'; + +-- Running upgrade a541e0b5e89 -> 28b8e71e541f + +ALTER TABLE ps_endpoints ADD COLUMN g726_non_standard ENUM('yes','no'); + +UPDATE alembic_version SET version_num='28b8e71e541f' WHERE alembic_version.version_num = 'a541e0b5e89'; + +-- Running upgrade 28b8e71e541f -> 498357a710ae + +ALTER TABLE ps_endpoints ADD COLUMN rtp_keepalive INTEGER; + +UPDATE alembic_version SET version_num='498357a710ae' WHERE alembic_version.version_num = '28b8e71e541f'; + +-- Running upgrade 498357a710ae -> 26f10cadc157 + +ALTER TABLE ps_endpoints ADD COLUMN rtp_timeout INTEGER; + +ALTER TABLE ps_endpoints ADD COLUMN rtp_timeout_hold INTEGER; + +UPDATE alembic_version SET version_num='26f10cadc157' WHERE alembic_version.version_num = '498357a710ae'; + +-- Running upgrade 26f10cadc157 -> 154177371065 + +ALTER TABLE ps_globals ADD COLUMN default_from_user VARCHAR(80); + +UPDATE alembic_version SET version_num='154177371065' WHERE alembic_version.version_num = '26f10cadc157'; + +-- Running upgrade 154177371065 -> 28ce1e718f05 + +ALTER TABLE ps_registrations ADD COLUMN fatal_retry_interval INTEGER; + +UPDATE alembic_version SET version_num='28ce1e718f05' WHERE alembic_version.version_num = '154177371065'; + +-- Running upgrade 28ce1e718f05 -> 189a235b3fd7 + +ALTER TABLE ps_globals ADD COLUMN keep_alive_interval INTEGER; + +UPDATE alembic_version SET version_num='189a235b3fd7' WHERE alembic_version.version_num = '28ce1e718f05'; + +-- Running upgrade 189a235b3fd7 -> 2d078ec071b7 + +ALTER TABLE ps_aors MODIFY contact VARCHAR(255) NULL; + +UPDATE alembic_version SET version_num='2d078ec071b7' WHERE alembic_version.version_num = '189a235b3fd7'; + +-- Running upgrade 2d078ec071b7 -> 26d7f3bf0fa5 + +ALTER TABLE ps_endpoints ADD COLUMN bind_rtp_to_media_address ENUM('yes','no'); + +UPDATE alembic_version SET version_num='26d7f3bf0fa5' WHERE alembic_version.version_num = '2d078ec071b7'; + +-- Running upgrade 26d7f3bf0fa5 -> 136885b81223 + +ALTER TABLE ps_globals ADD COLUMN regcontext VARCHAR(80); + +UPDATE alembic_version SET version_num='136885b81223' WHERE alembic_version.version_num = '26d7f3bf0fa5'; + +-- Running upgrade 136885b81223 -> 423f34ad36e2 + +ALTER TABLE ps_aors MODIFY qualify_timeout FLOAT NULL; + +ALTER TABLE ps_contacts MODIFY qualify_timeout FLOAT NULL; + +UPDATE alembic_version SET version_num='423f34ad36e2' WHERE alembic_version.version_num = '136885b81223'; + +-- Running upgrade 423f34ad36e2 -> dbc44d5a908 + +ALTER TABLE ps_systems ADD COLUMN disable_tcp_switch ENUM('yes','no'); + +ALTER TABLE ps_registrations ADD COLUMN line ENUM('yes','no'); + +ALTER TABLE ps_registrations ADD COLUMN endpoint VARCHAR(40); + +UPDATE alembic_version SET version_num='dbc44d5a908' WHERE alembic_version.version_num = '423f34ad36e2'; + +-- Running upgrade dbc44d5a908 -> 3bcc0b5bc2c9 + +ALTER TABLE ps_transports ADD COLUMN allow_reload ENUM('yes','no'); + +UPDATE alembic_version SET version_num='3bcc0b5bc2c9' WHERE alembic_version.version_num = 'dbc44d5a908'; + +-- Running upgrade 3bcc0b5bc2c9 -> 5813202e92be + +ALTER TABLE ps_globals ADD COLUMN contact_expiration_check_interval INTEGER; + +UPDATE alembic_version SET version_num='5813202e92be' WHERE alembic_version.version_num = '3bcc0b5bc2c9'; + +-- Running upgrade 5813202e92be -> 1c688d9a003c + +ALTER TABLE ps_globals ADD COLUMN default_voicemail_extension VARCHAR(40); + +ALTER TABLE ps_aors ADD COLUMN voicemail_extension VARCHAR(40); + +ALTER TABLE ps_endpoints ADD COLUMN voicemail_extension VARCHAR(40); + +ALTER TABLE ps_endpoints ADD COLUMN mwi_subscribe_replaces_unsolicited INTEGER; + +UPDATE alembic_version SET version_num='1c688d9a003c' WHERE alembic_version.version_num = '5813202e92be'; + +-- Running upgrade 1c688d9a003c -> 8d478ab86e29 + +ALTER TABLE ps_globals ADD COLUMN disable_multi_domain ENUM('yes','no'); + +UPDATE alembic_version SET version_num='8d478ab86e29' WHERE alembic_version.version_num = '1c688d9a003c'; + +-- Running upgrade 8d478ab86e29 -> 65eb22eb195 + +ALTER TABLE ps_globals ADD COLUMN unidentified_request_count INTEGER; + +ALTER TABLE ps_globals ADD COLUMN unidentified_request_period INTEGER; + +ALTER TABLE ps_globals ADD COLUMN unidentified_request_prune_interval INTEGER; + +ALTER TABLE ps_globals ADD COLUMN default_realm VARCHAR(40); + +UPDATE alembic_version SET version_num='65eb22eb195' WHERE alembic_version.version_num = '8d478ab86e29'; + +-- Running upgrade 65eb22eb195 -> 81b01a191a46 + +ALTER TABLE ps_contacts ADD COLUMN reg_server VARCHAR(20); + +ALTER TABLE ps_contacts ADD CONSTRAINT ps_contacts_uq UNIQUE (id, reg_server); + +UPDATE alembic_version SET version_num='81b01a191a46' WHERE alembic_version.version_num = '65eb22eb195'; + +-- Running upgrade 81b01a191a46 -> 6be31516058d + +ALTER TABLE ps_contacts ADD COLUMN authenticate_qualify ENUM('yes','no'); + +UPDATE alembic_version SET version_num='6be31516058d' WHERE alembic_version.version_num = '81b01a191a46'; + +-- Running upgrade 6be31516058d -> bca7113d796f + +ALTER TABLE ps_endpoints ADD COLUMN deny VARCHAR(95); + +ALTER TABLE ps_endpoints ADD COLUMN permit VARCHAR(95); + +ALTER TABLE ps_endpoints ADD COLUMN acl VARCHAR(40); + +ALTER TABLE ps_endpoints ADD COLUMN contact_deny VARCHAR(95); + +ALTER TABLE ps_endpoints ADD COLUMN contact_permit VARCHAR(95); + +ALTER TABLE ps_endpoints ADD COLUMN contact_acl VARCHAR(40); + +UPDATE alembic_version SET version_num='bca7113d796f' WHERE alembic_version.version_num = '6be31516058d'; + +-- Running upgrade bca7113d796f -> a845e4d8ade8 + +ALTER TABLE ps_contacts ADD COLUMN via_addr VARCHAR(40); + +ALTER TABLE ps_contacts ADD COLUMN via_port INTEGER; + +ALTER TABLE ps_contacts ADD COLUMN call_id VARCHAR(255); + +UPDATE alembic_version SET version_num='a845e4d8ade8' WHERE alembic_version.version_num = 'bca7113d796f'; + +-- Running upgrade a845e4d8ade8 -> ef7efc2d3964 + +ALTER TABLE ps_contacts ADD COLUMN endpoint VARCHAR(40); + +ALTER TABLE ps_contacts MODIFY expiration_time BIGINT NULL; + +CREATE INDEX ps_contacts_qualifyfreq_exp ON ps_contacts (qualify_frequency, expiration_time); + +CREATE INDEX ps_aors_qualifyfreq_contact ON ps_aors (qualify_frequency, contact); + +UPDATE alembic_version SET version_num='ef7efc2d3964' WHERE alembic_version.version_num = 'a845e4d8ade8'; + +-- Running upgrade ef7efc2d3964 -> 9deac0ae4717 + +ALTER TABLE ps_endpoints ADD COLUMN subscribe_context VARCHAR(40); + +UPDATE alembic_version SET version_num='9deac0ae4717' WHERE alembic_version.version_num = 'ef7efc2d3964'; + +-- Running upgrade 9deac0ae4717 -> 4a6c67fa9b7a + +ALTER TABLE ps_endpoints ADD COLUMN fax_detect_timeout INTEGER; + +UPDATE alembic_version SET version_num='4a6c67fa9b7a' WHERE alembic_version.version_num = '9deac0ae4717'; + +-- Running upgrade 4a6c67fa9b7a -> c7a44a5a0851 + +ALTER TABLE ps_globals ADD COLUMN mwi_tps_queue_high INTEGER; + +ALTER TABLE ps_globals ADD COLUMN mwi_tps_queue_low INTEGER; + +ALTER TABLE ps_globals ADD COLUMN mwi_disable_initial_unsolicited ENUM('yes','no'); + +UPDATE alembic_version SET version_num='c7a44a5a0851' WHERE alembic_version.version_num = '4a6c67fa9b7a'; + +-- Running upgrade c7a44a5a0851 -> 3772f8f828da + +ALTER TABLE ps_endpoints MODIFY identify_by ENUM('username','auth_username') NULL; + +UPDATE alembic_version SET version_num='3772f8f828da' WHERE alembic_version.version_num = 'c7a44a5a0851'; + +-- Running upgrade 3772f8f828da -> 4e2493ef32e6 + +ALTER TABLE ps_endpoints ADD COLUMN contact_user VARCHAR(80); + +UPDATE alembic_version SET version_num='4e2493ef32e6' WHERE alembic_version.version_num = '3772f8f828da'; + +-- Running upgrade 4e2493ef32e6 -> a6ef36f1309 + +ALTER TABLE ps_globals ADD COLUMN ignore_uri_user_options ENUM('yes','no'); + +UPDATE alembic_version SET version_num='a6ef36f1309' WHERE alembic_version.version_num = '4e2493ef32e6'; + +-- Running upgrade a6ef36f1309 -> 4468b4a91372 + +ALTER TABLE ps_endpoints ADD COLUMN asymmetric_rtp_codec ENUM('yes','no'); + +UPDATE alembic_version SET version_num='4468b4a91372' WHERE alembic_version.version_num = 'a6ef36f1309'; + +-- Running upgrade 4468b4a91372 -> 28ab27a7826d + +ALTER TABLE ps_endpoint_id_ips ADD COLUMN srv_lookups ENUM('yes','no'); + +UPDATE alembic_version SET version_num='28ab27a7826d' WHERE alembic_version.version_num = '4468b4a91372'; + +-- Running upgrade 28ab27a7826d -> 465e70e8c337 + +ALTER TABLE ps_endpoint_id_ips ADD COLUMN match_header VARCHAR(255); + +UPDATE alembic_version SET version_num='465e70e8c337' WHERE alembic_version.version_num = '28ab27a7826d'; + +-- Running upgrade 465e70e8c337 -> 15db7b91a97a + +ALTER TABLE ps_endpoints ADD COLUMN rtcp_mux ENUM('yes','no'); + +UPDATE alembic_version SET version_num='15db7b91a97a' WHERE alembic_version.version_num = '465e70e8c337'; + +-- Running upgrade 15db7b91a97a -> f638dbe2eb23 + +ALTER TABLE ps_transports ADD COLUMN symmetric_transport ENUM('yes','no'); + +ALTER TABLE ps_subscription_persistence ADD COLUMN contact_uri VARCHAR(256); + +UPDATE alembic_version SET version_num='f638dbe2eb23' WHERE alembic_version.version_num = '15db7b91a97a'; + +-- Running upgrade f638dbe2eb23 -> 8fce4c573e15 + +ALTER TABLE ps_endpoints ADD COLUMN allow_overlap ENUM('yes','no'); + +UPDATE alembic_version SET version_num='8fce4c573e15' WHERE alembic_version.version_num = 'f638dbe2eb23'; + diff --git a/contrib/realtime/mysql/mysql_voicemail.sql b/contrib/realtime/mysql/mysql_voicemail.sql new file mode 100644 index 000000000..1ee25c283 --- /dev/null +++ b/contrib/realtime/mysql/mysql_voicemail.sql @@ -0,0 +1,34 @@ +CREATE TABLE alembic_version ( + version_num VARCHAR(32) NOT NULL +); + +-- Running upgrade -> a2e9769475e + +CREATE TABLE voicemail_messages ( + dir VARCHAR(255) NOT NULL, + msgnum INTEGER NOT NULL, + context VARCHAR(80), + macrocontext VARCHAR(80), + callerid VARCHAR(80), + origtime INTEGER, + duration INTEGER, + recording BLOB, + flag VARCHAR(30), + category VARCHAR(30), + mailboxuser VARCHAR(30), + mailboxcontext VARCHAR(30), + msg_id VARCHAR(40) +); + +ALTER TABLE voicemail_messages ADD CONSTRAINT voicemail_messages_dir_msgnum PRIMARY KEY (dir, msgnum); + +CREATE INDEX voicemail_messages_dir ON voicemail_messages (dir); + +INSERT INTO alembic_version (version_num) VALUES ('a2e9769475e'); + +-- Running upgrade a2e9769475e -> 39428242f7f5 + +ALTER TABLE voicemail_messages MODIFY recording BLOB(4294967295) NULL; + +UPDATE alembic_version SET version_num='39428242f7f5' WHERE alembic_version.version_num = 'a2e9769475e'; + diff --git a/contrib/realtime/oracle/oracle_cdr.sql b/contrib/realtime/oracle/oracle_cdr.sql new file mode 100644 index 000000000..81d599ae6 --- /dev/null +++ b/contrib/realtime/oracle/oracle_cdr.sql @@ -0,0 +1,38 @@ +CREATE TABLE alembic_version ( + version_num VARCHAR2(32 CHAR) NOT NULL +) + +/ + +-- Running upgrade -> 210693f3123d + +CREATE TABLE cdr ( + accountcode VARCHAR2(20 CHAR), + src VARCHAR2(80 CHAR), + dst VARCHAR2(80 CHAR), + dcontext VARCHAR2(80 CHAR), + clid VARCHAR2(80 CHAR), + channel VARCHAR2(80 CHAR), + dstchannel VARCHAR2(80 CHAR), + lastapp VARCHAR2(80 CHAR), + lastdata VARCHAR2(80 CHAR), + "start" DATE, + answer DATE, + end DATE, + duration INTEGER, + billsec INTEGER, + disposition VARCHAR2(45 CHAR), + amaflags VARCHAR2(45 CHAR), + userfield VARCHAR2(256 CHAR), + uniqueid VARCHAR2(150 CHAR), + linkedid VARCHAR2(150 CHAR), + peeraccount VARCHAR2(20 CHAR), + sequence INTEGER +) + +/ + +INSERT INTO alembic_version (version_num) VALUES ('210693f3123d') + +/ + diff --git a/contrib/realtime/oracle/oracle_config.sql b/contrib/realtime/oracle/oracle_config.sql new file mode 100644 index 000000000..f34edd452 --- /dev/null +++ b/contrib/realtime/oracle/oracle_config.sql @@ -0,0 +1,1621 @@ +CREATE TABLE alembic_version ( + version_num VARCHAR2(32 CHAR) NOT NULL +) + +/ + +-- Running upgrade -> 4da0c5f79a9c + +CREATE TABLE sippeers ( + id INTEGER NOT NULL, + name VARCHAR2(40 CHAR) NOT NULL, + ipaddr VARCHAR2(45 CHAR), + port INTEGER, + regseconds INTEGER, + defaultuser VARCHAR2(40 CHAR), + fullcontact VARCHAR2(80 CHAR), + regserver VARCHAR2(20 CHAR), + useragent VARCHAR2(20 CHAR), + lastms INTEGER, + host VARCHAR2(40 CHAR), + type VARCHAR(6 CHAR), + context VARCHAR2(40 CHAR), + permit VARCHAR2(95 CHAR), + deny VARCHAR2(95 CHAR), + secret VARCHAR2(40 CHAR), + md5secret VARCHAR2(40 CHAR), + remotesecret VARCHAR2(40 CHAR), + transport VARCHAR(7 CHAR), + dtmfmode VARCHAR(9 CHAR), + directmedia VARCHAR(6 CHAR), + nat VARCHAR2(29 CHAR), + callgroup VARCHAR2(40 CHAR), + pickupgroup VARCHAR2(40 CHAR), + language VARCHAR2(40 CHAR), + disallow VARCHAR2(200 CHAR), + allow VARCHAR2(200 CHAR), + insecure VARCHAR2(40 CHAR), + trustrpid VARCHAR(3 CHAR), + progressinband VARCHAR(5 CHAR), + promiscredir VARCHAR(3 CHAR), + useclientcode VARCHAR(3 CHAR), + accountcode VARCHAR2(40 CHAR), + setvar VARCHAR2(200 CHAR), + callerid VARCHAR2(40 CHAR), + amaflags VARCHAR2(40 CHAR), + callcounter VARCHAR(3 CHAR), + busylevel INTEGER, + allowoverlap VARCHAR(3 CHAR), + allowsubscribe VARCHAR(3 CHAR), + videosupport VARCHAR(3 CHAR), + maxcallbitrate INTEGER, + rfc2833compensate VARCHAR(3 CHAR), + mailbox VARCHAR2(40 CHAR), + "session-timers" VARCHAR(9 CHAR), + "session-expires" INTEGER, + "session-minse" INTEGER, + "session-refresher" VARCHAR(3 CHAR), + t38pt_usertpsource VARCHAR2(40 CHAR), + regexten VARCHAR2(40 CHAR), + fromdomain VARCHAR2(40 CHAR), + fromuser VARCHAR2(40 CHAR), + qualify VARCHAR2(40 CHAR), + defaultip VARCHAR2(45 CHAR), + rtptimeout INTEGER, + rtpholdtimeout INTEGER, + sendrpid VARCHAR(3 CHAR), + outboundproxy VARCHAR2(40 CHAR), + callbackextension VARCHAR2(40 CHAR), + timert1 INTEGER, + timerb INTEGER, + qualifyfreq INTEGER, + constantssrc VARCHAR(3 CHAR), + contactpermit VARCHAR2(95 CHAR), + contactdeny VARCHAR2(95 CHAR), + usereqphone VARCHAR(3 CHAR), + textsupport VARCHAR(3 CHAR), + faxdetect VARCHAR(3 CHAR), + buggymwi VARCHAR(3 CHAR), + auth VARCHAR2(40 CHAR), + fullname VARCHAR2(40 CHAR), + trunkname VARCHAR2(40 CHAR), + cid_number VARCHAR2(40 CHAR), + callingpres VARCHAR(21 CHAR), + mohinterpret VARCHAR2(40 CHAR), + mohsuggest VARCHAR2(40 CHAR), + parkinglot VARCHAR2(40 CHAR), + hasvoicemail VARCHAR(3 CHAR), + subscribemwi VARCHAR(3 CHAR), + vmexten VARCHAR2(40 CHAR), + autoframing VARCHAR(3 CHAR), + rtpkeepalive INTEGER, + "call-limit" INTEGER, + g726nonstandard VARCHAR(3 CHAR), + ignoresdpversion VARCHAR(3 CHAR), + allowtransfer VARCHAR(3 CHAR), + dynamic VARCHAR(3 CHAR), + path VARCHAR2(256 CHAR), + supportpath VARCHAR(3 CHAR), + PRIMARY KEY (id), + UNIQUE (name), + CONSTRAINT type_values CHECK (type IN ('friend', 'user', 'peer')), + CONSTRAINT sip_transport_values CHECK (transport IN ('udp', 'tcp', 'tls', 'ws', 'wss', 'udp,tcp', 'tcp,udp')), + CONSTRAINT sip_dtmfmode_values CHECK (dtmfmode IN ('rfc2833', 'info', 'shortinfo', 'inband', 'auto')), + CONSTRAINT sip_directmedia_values CHECK (directmedia IN ('yes', 'no', 'nonat', 'update')), + CONSTRAINT yes_no_values CHECK (trustrpid IN ('yes', 'no')), + CONSTRAINT sip_progressinband_values CHECK (progressinband IN ('yes', 'no', 'never')), + CONSTRAINT yes_no_values CHECK (promiscredir IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (useclientcode IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (callcounter IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (allowoverlap IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (allowsubscribe IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (videosupport IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (rfc2833compensate IN ('yes', 'no')), + CONSTRAINT sip_session_timers_values CHECK ("session-timers" IN ('accept', 'refuse', 'originate')), + CONSTRAINT sip_session_refresher_values CHECK ("session-refresher" IN ('uac', 'uas')), + CONSTRAINT yes_no_values CHECK (sendrpid IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (constantssrc IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (usereqphone IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (textsupport IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (faxdetect IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (buggymwi IN ('yes', 'no')), + CONSTRAINT sip_callingpres_values CHECK (callingpres IN ('allowed_not_screened', 'allowed_passed_screen', 'allowed_failed_screen', 'allowed', 'prohib_not_screened', 'prohib_passed_screen', 'prohib_failed_screen', 'prohib')), + CONSTRAINT yes_no_values CHECK (hasvoicemail IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (subscribemwi IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (autoframing IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (g726nonstandard IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (ignoresdpversion IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (allowtransfer IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (dynamic IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (supportpath IN ('yes', 'no')) +) + +/ + +CREATE INDEX sippeers_name ON sippeers (name) + +/ + +CREATE INDEX sippeers_name_host ON sippeers (name, host) + +/ + +CREATE INDEX sippeers_ipaddr_port ON sippeers (ipaddr, port) + +/ + +CREATE INDEX sippeers_host_port ON sippeers (host, port) + +/ + +CREATE TABLE iaxfriends ( + id INTEGER NOT NULL, + name VARCHAR2(40 CHAR) NOT NULL, + type VARCHAR(6 CHAR), + username VARCHAR2(40 CHAR), + mailbox VARCHAR2(40 CHAR), + secret VARCHAR2(40 CHAR), + dbsecret VARCHAR2(40 CHAR), + context VARCHAR2(40 CHAR), + regcontext VARCHAR2(40 CHAR), + host VARCHAR2(40 CHAR), + ipaddr VARCHAR2(40 CHAR), + port INTEGER, + defaultip VARCHAR2(20 CHAR), + sourceaddress VARCHAR2(20 CHAR), + mask VARCHAR2(20 CHAR), + regexten VARCHAR2(40 CHAR), + regseconds INTEGER, + accountcode VARCHAR2(20 CHAR), + mohinterpret VARCHAR2(20 CHAR), + mohsuggest VARCHAR2(20 CHAR), + inkeys VARCHAR2(40 CHAR), + outkeys VARCHAR2(40 CHAR), + language VARCHAR2(10 CHAR), + callerid VARCHAR2(100 CHAR), + cid_number VARCHAR2(40 CHAR), + sendani VARCHAR(3 CHAR), + fullname VARCHAR2(40 CHAR), + trunk VARCHAR(3 CHAR), + auth VARCHAR2(20 CHAR), + maxauthreq INTEGER, + requirecalltoken VARCHAR(4 CHAR), + encryption VARCHAR(6 CHAR), + transfer VARCHAR(9 CHAR), + jitterbuffer VARCHAR(3 CHAR), + forcejitterbuffer VARCHAR(3 CHAR), + disallow VARCHAR2(200 CHAR), + allow VARCHAR2(200 CHAR), + codecpriority VARCHAR2(40 CHAR), + qualify VARCHAR2(10 CHAR), + qualifysmoothing VARCHAR(3 CHAR), + qualifyfreqok VARCHAR2(10 CHAR), + qualifyfreqnotok VARCHAR2(10 CHAR), + timezone VARCHAR2(20 CHAR), + adsi VARCHAR(3 CHAR), + amaflags VARCHAR2(20 CHAR), + setvar VARCHAR2(200 CHAR), + PRIMARY KEY (id), + UNIQUE (name), + CONSTRAINT type_values CHECK (type IN ('friend', 'user', 'peer')), + CONSTRAINT yes_no_values CHECK (sendani IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (trunk IN ('yes', 'no')), + CONSTRAINT iax_requirecalltoken_values CHECK (requirecalltoken IN ('yes', 'no', 'auto')), + CONSTRAINT iax_encryption_values CHECK (encryption IN ('yes', 'no', 'aes128')), + CONSTRAINT iax_transfer_values CHECK (transfer IN ('yes', 'no', 'mediaonly')), + CONSTRAINT yes_no_values CHECK (jitterbuffer IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (forcejitterbuffer IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (qualifysmoothing IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (adsi IN ('yes', 'no')) +) + +/ + +CREATE INDEX iaxfriends_name ON iaxfriends (name) + +/ + +CREATE INDEX iaxfriends_name_host ON iaxfriends (name, host) + +/ + +CREATE INDEX iaxfriends_name_ipaddr_port ON iaxfriends (name, ipaddr, port) + +/ + +CREATE INDEX iaxfriends_ipaddr_port ON iaxfriends (ipaddr, port) + +/ + +CREATE INDEX iaxfriends_host_port ON iaxfriends (host, port) + +/ + +CREATE TABLE voicemail ( + uniqueid INTEGER NOT NULL, + context VARCHAR2(80 CHAR) NOT NULL, + mailbox VARCHAR2(80 CHAR) NOT NULL, + password VARCHAR2(80 CHAR) NOT NULL, + fullname VARCHAR2(80 CHAR), + alias VARCHAR2(80 CHAR), + email VARCHAR2(80 CHAR), + pager VARCHAR2(80 CHAR), + attach VARCHAR(3 CHAR), + attachfmt VARCHAR2(10 CHAR), + serveremail VARCHAR2(80 CHAR), + language VARCHAR2(20 CHAR), + tz VARCHAR2(30 CHAR), + deletevoicemail VARCHAR(3 CHAR), + saycid VARCHAR(3 CHAR), + sendvoicemail VARCHAR(3 CHAR), + review VARCHAR(3 CHAR), + tempgreetwarn VARCHAR(3 CHAR), + operator VARCHAR(3 CHAR), + envelope VARCHAR(3 CHAR), + sayduration INTEGER, + forcename VARCHAR(3 CHAR), + forcegreetings VARCHAR(3 CHAR), + callback VARCHAR2(80 CHAR), + dialout VARCHAR2(80 CHAR), + exitcontext VARCHAR2(80 CHAR), + maxmsg INTEGER, + volgain NUMERIC(5, 2), + imapuser VARCHAR2(80 CHAR), + imappassword VARCHAR2(80 CHAR), + imapserver VARCHAR2(80 CHAR), + imapport VARCHAR2(8 CHAR), + imapflags VARCHAR2(80 CHAR), + stamp DATE, + PRIMARY KEY (uniqueid), + CONSTRAINT yes_no_values CHECK (attach IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (deletevoicemail IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (saycid IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (sendvoicemail IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (review IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (tempgreetwarn IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (operator IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (envelope IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (forcename IN ('yes', 'no')), + CONSTRAINT yes_no_values CHECK (forcegreetings IN ('yes', 'no')) +) + +/ + +CREATE INDEX voicemail_mailbox ON voicemail (mailbox) + +/ + +CREATE INDEX voicemail_context ON voicemail (context) + +/ + +CREATE INDEX voicemail_mailbox_context ON voicemail (mailbox, context) + +/ + +CREATE INDEX voicemail_imapuser ON voicemail (imapuser) + +/ + +CREATE TABLE meetme ( + bookid INTEGER NOT NULL, + confno VARCHAR2(80 CHAR) NOT NULL, + starttime DATE, + endtime DATE, + pin VARCHAR2(20 CHAR), + adminpin VARCHAR2(20 CHAR), + opts VARCHAR2(20 CHAR), + adminopts VARCHAR2(20 CHAR), + recordingfilename VARCHAR2(80 CHAR), + recordingformat VARCHAR2(10 CHAR), + maxusers INTEGER, + members INTEGER NOT NULL, + PRIMARY KEY (bookid) +) + +/ + +CREATE INDEX meetme_confno_start_end ON meetme (confno, starttime, endtime) + +/ + +CREATE TABLE musiconhold ( + name VARCHAR2(80 CHAR) NOT NULL, + "mode" VARCHAR(10 CHAR), + directory VARCHAR2(255 CHAR), + application VARCHAR2(255 CHAR), + digit VARCHAR2(1 CHAR), + sort VARCHAR2(10 CHAR), + format VARCHAR2(10 CHAR), + stamp DATE, + PRIMARY KEY (name), + CONSTRAINT moh_mode_values CHECK ("mode" IN ('custom', 'files', 'mp3nb', 'quietmp3nb', 'quietmp3')) +) + +/ + +INSERT INTO alembic_version (version_num) VALUES ('4da0c5f79a9c') + +/ + +-- Running upgrade 4da0c5f79a9c -> 43956d550a44 + +CREATE TABLE ps_endpoints ( + id VARCHAR2(40 CHAR) NOT NULL, + transport VARCHAR2(40 CHAR), + aors VARCHAR2(200 CHAR), + auth VARCHAR2(40 CHAR), + context VARCHAR2(40 CHAR), + disallow VARCHAR2(200 CHAR), + allow VARCHAR2(200 CHAR), + direct_media VARCHAR(3 CHAR), + connected_line_method VARCHAR(8 CHAR), + direct_media_method VARCHAR(8 CHAR), + direct_media_glare_mitigation VARCHAR(8 CHAR), + disable_direct_media_on_nat VARCHAR(3 CHAR), + dtmf_mode VARCHAR(7 CHAR), + external_media_address VARCHAR2(40 CHAR), + force_rport VARCHAR(3 CHAR), + ice_support VARCHAR(3 CHAR), + identify_by VARCHAR(8 CHAR), + mailboxes VARCHAR2(40 CHAR), + moh_suggest VARCHAR2(40 CHAR), + outbound_auth VARCHAR2(40 CHAR), + outbound_proxy VARCHAR2(40 CHAR), + rewrite_contact VARCHAR(3 CHAR), + rtp_ipv6 VARCHAR(3 CHAR), + rtp_symmetric VARCHAR(3 CHAR), + send_diversion VARCHAR(3 CHAR), + send_pai VARCHAR(3 CHAR), + send_rpid VARCHAR(3 CHAR), + timers_min_se INTEGER, + timers VARCHAR(8 CHAR), + timers_sess_expires INTEGER, + callerid VARCHAR2(40 CHAR), + callerid_privacy VARCHAR(23 CHAR), + callerid_tag VARCHAR2(40 CHAR), + "100rel" VARCHAR(8 CHAR), + aggregate_mwi VARCHAR(3 CHAR), + trust_id_inbound VARCHAR(3 CHAR), + trust_id_outbound VARCHAR(3 CHAR), + use_ptime VARCHAR(3 CHAR), + use_avpf VARCHAR(3 CHAR), + media_encryption VARCHAR(4 CHAR), + inband_progress VARCHAR(3 CHAR), + call_group VARCHAR2(40 CHAR), + pickup_group VARCHAR2(40 CHAR), + named_call_group VARCHAR2(40 CHAR), + named_pickup_group VARCHAR2(40 CHAR), + device_state_busy_at INTEGER, + fax_detect VARCHAR(3 CHAR), + t38_udptl VARCHAR(3 CHAR), + t38_udptl_ec VARCHAR(10 CHAR), + t38_udptl_maxdatagram INTEGER, + t38_udptl_nat VARCHAR(3 CHAR), + t38_udptl_ipv6 VARCHAR(3 CHAR), + tone_zone VARCHAR2(40 CHAR), + language VARCHAR2(40 CHAR), + one_touch_recording VARCHAR(3 CHAR), + record_on_feature VARCHAR2(40 CHAR), + record_off_feature VARCHAR2(40 CHAR), + rtp_engine VARCHAR2(40 CHAR), + allow_transfer VARCHAR(3 CHAR), + allow_subscribe VARCHAR(3 CHAR), + sdp_owner VARCHAR2(40 CHAR), + sdp_session VARCHAR2(40 CHAR), + tos_audio INTEGER, + tos_video INTEGER, + cos_audio INTEGER, + cos_video INTEGER, + sub_min_expiry INTEGER, + from_domain VARCHAR2(40 CHAR), + from_user VARCHAR2(40 CHAR), + mwi_fromuser VARCHAR2(40 CHAR), + dtls_verify VARCHAR2(40 CHAR), + dtls_rekey VARCHAR2(40 CHAR), + dtls_cert_file VARCHAR2(200 CHAR), + dtls_private_key VARCHAR2(200 CHAR), + dtls_cipher VARCHAR2(200 CHAR), + dtls_ca_file VARCHAR2(200 CHAR), + dtls_ca_path VARCHAR2(200 CHAR), + dtls_setup VARCHAR(7 CHAR), + srtp_tag_32 VARCHAR(3 CHAR), + UNIQUE (id), + CONSTRAINT yesno_values CHECK (direct_media IN ('yes', 'no')), + CONSTRAINT pjsip_connected_line_method_values CHECK (connected_line_method IN ('invite', 'reinvite', 'update')), + CONSTRAINT pjsip_connected_line_method_values CHECK (direct_media_method IN ('invite', 'reinvite', 'update')), + CONSTRAINT pjsip_direct_media_glare_mitigation_values CHECK (direct_media_glare_mitigation IN ('none', 'outgoing', 'incoming')), + CONSTRAINT yesno_values CHECK (disable_direct_media_on_nat IN ('yes', 'no')), + CONSTRAINT pjsip_dtmf_mode_values CHECK (dtmf_mode IN ('rfc4733', 'inband', 'info')), + CONSTRAINT yesno_values CHECK (force_rport IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (ice_support IN ('yes', 'no')), + CONSTRAINT pjsip_identify_by_values CHECK (identify_by IN ('username')), + CONSTRAINT yesno_values CHECK (rewrite_contact IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (rtp_ipv6 IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (rtp_symmetric IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (send_diversion IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (send_pai IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (send_rpid IN ('yes', 'no')), + CONSTRAINT pjsip_timer_values CHECK (timers IN ('forced', 'no', 'required', 'yes')), + CONSTRAINT pjsip_cid_privacy_values CHECK (callerid_privacy IN ('allowed_not_screened', 'allowed_passed_screened', 'allowed_failed_screened', 'allowed', 'prohib_not_screened', 'prohib_passed_screened', 'prohib_failed_screened', 'prohib', 'unavailable')), + CONSTRAINT pjsip_100rel_values CHECK ("100rel" IN ('no', 'required', 'yes')), + CONSTRAINT yesno_values CHECK (aggregate_mwi IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (trust_id_inbound IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (trust_id_outbound IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (use_ptime IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (use_avpf IN ('yes', 'no')), + CONSTRAINT pjsip_media_encryption_values CHECK (media_encryption IN ('no', 'sdes', 'dtls')), + CONSTRAINT yesno_values CHECK (inband_progress IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (fax_detect IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (t38_udptl IN ('yes', 'no')), + CONSTRAINT pjsip_t38udptl_ec_values CHECK (t38_udptl_ec IN ('none', 'fec', 'redundancy')), + CONSTRAINT yesno_values CHECK (t38_udptl_nat IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (t38_udptl_ipv6 IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (one_touch_recording IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (allow_transfer IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (allow_subscribe IN ('yes', 'no')), + CONSTRAINT pjsip_dtls_setup_values CHECK (dtls_setup IN ('active', 'passive', 'actpass')), + CONSTRAINT yesno_values CHECK (srtp_tag_32 IN ('yes', 'no')) +) + +/ + +CREATE INDEX ps_endpoints_id ON ps_endpoints (id) + +/ + +CREATE TABLE ps_auths ( + id VARCHAR2(40 CHAR) NOT NULL, + auth_type VARCHAR(8 CHAR), + nonce_lifetime INTEGER, + md5_cred VARCHAR2(40 CHAR), + password VARCHAR2(80 CHAR), + realm VARCHAR2(40 CHAR), + username VARCHAR2(40 CHAR), + UNIQUE (id), + CONSTRAINT pjsip_auth_type_values CHECK (auth_type IN ('md5', 'userpass')) +) + +/ + +CREATE INDEX ps_auths_id ON ps_auths (id) + +/ + +CREATE TABLE ps_aors ( + id VARCHAR2(40 CHAR) NOT NULL, + contact VARCHAR2(40 CHAR), + default_expiration INTEGER, + mailboxes VARCHAR2(80 CHAR), + max_contacts INTEGER, + minimum_expiration INTEGER, + remove_existing VARCHAR(3 CHAR), + qualify_frequency INTEGER, + authenticate_qualify VARCHAR(3 CHAR), + UNIQUE (id), + CONSTRAINT yesno_values CHECK (remove_existing IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (authenticate_qualify IN ('yes', 'no')) +) + +/ + +CREATE INDEX ps_aors_id ON ps_aors (id) + +/ + +CREATE TABLE ps_contacts ( + id VARCHAR2(40 CHAR) NOT NULL, + uri VARCHAR2(40 CHAR), + expiration_time VARCHAR2(40 CHAR), + qualify_frequency INTEGER, + UNIQUE (id) +) + +/ + +CREATE INDEX ps_contacts_id ON ps_contacts (id) + +/ + +CREATE TABLE ps_domain_aliases ( + id VARCHAR2(40 CHAR) NOT NULL, + domain VARCHAR2(80 CHAR), + UNIQUE (id) +) + +/ + +CREATE INDEX ps_domain_aliases_id ON ps_domain_aliases (id) + +/ + +CREATE TABLE ps_endpoint_id_ips ( + id VARCHAR2(40 CHAR) NOT NULL, + endpoint VARCHAR2(40 CHAR), + match VARCHAR2(80 CHAR), + UNIQUE (id) +) + +/ + +CREATE INDEX ps_endpoint_id_ips_id ON ps_endpoint_id_ips (id) + +/ + +UPDATE alembic_version SET version_num='43956d550a44' WHERE alembic_version.version_num = '4da0c5f79a9c' + +/ + +-- Running upgrade 43956d550a44 -> 581a4264e537 + +CREATE TABLE extensions ( + id NUMBER(19) NOT NULL, + context VARCHAR2(40 CHAR) NOT NULL, + exten VARCHAR2(40 CHAR) NOT NULL, + priority INTEGER NOT NULL, + app VARCHAR2(40 CHAR) NOT NULL, + appdata VARCHAR2(256 CHAR) NOT NULL, + PRIMARY KEY (id), + UNIQUE (context, exten, priority), + UNIQUE (id) +) + +/ + +UPDATE alembic_version SET version_num='581a4264e537' WHERE alembic_version.version_num = '43956d550a44' + +/ + +-- Running upgrade 581a4264e537 -> 2fc7930b41b3 + +CREATE TABLE ps_systems ( + id VARCHAR2(40 CHAR) NOT NULL, + timer_t1 INTEGER, + timer_b INTEGER, + compact_headers VARCHAR(3 CHAR), + threadpool_initial_size INTEGER, + threadpool_auto_increment INTEGER, + threadpool_idle_timeout INTEGER, + threadpool_max_size INTEGER, + UNIQUE (id), + CONSTRAINT yesno_values CHECK (compact_headers IN ('yes', 'no')) +) + +/ + +CREATE INDEX ps_systems_id ON ps_systems (id) + +/ + +CREATE TABLE ps_globals ( + id VARCHAR2(40 CHAR) NOT NULL, + max_forwards INTEGER, + user_agent VARCHAR2(40 CHAR), + default_outbound_endpoint VARCHAR2(40 CHAR), + UNIQUE (id) +) + +/ + +CREATE INDEX ps_globals_id ON ps_globals (id) + +/ + +CREATE TABLE ps_transports ( + id VARCHAR2(40 CHAR) NOT NULL, + async_operations INTEGER, + bind VARCHAR2(40 CHAR), + ca_list_file VARCHAR2(200 CHAR), + cert_file VARCHAR2(200 CHAR), + cipher VARCHAR2(200 CHAR), + domain VARCHAR2(40 CHAR), + external_media_address VARCHAR2(40 CHAR), + external_signaling_address VARCHAR2(40 CHAR), + external_signaling_port INTEGER, + method VARCHAR(11 CHAR), + local_net VARCHAR2(40 CHAR), + password VARCHAR2(40 CHAR), + priv_key_file VARCHAR2(200 CHAR), + protocol VARCHAR(3 CHAR), + require_client_cert VARCHAR(3 CHAR), + verify_client VARCHAR(3 CHAR), + verifiy_server VARCHAR(3 CHAR), + tos VARCHAR(3 CHAR), + cos VARCHAR(3 CHAR), + UNIQUE (id), + CONSTRAINT pjsip_transport_method_values CHECK (method IN ('default', 'unspecified', 'tlsv1', 'sslv2', 'sslv3', 'sslv23')), + CONSTRAINT pjsip_transport_protocol_values CHECK (protocol IN ('udp', 'tcp', 'tls', 'ws', 'wss')), + CONSTRAINT yesno_values CHECK (require_client_cert IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (verify_client IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (verifiy_server IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (tos IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (cos IN ('yes', 'no')) +) + +/ + +CREATE INDEX ps_transports_id ON ps_transports (id) + +/ + +CREATE TABLE ps_registrations ( + id VARCHAR2(40 CHAR) NOT NULL, + auth_rejection_permanent VARCHAR(3 CHAR), + client_uri VARCHAR2(40 CHAR), + contact_user VARCHAR2(40 CHAR), + expiration INTEGER, + max_retries INTEGER, + outbound_auth VARCHAR2(40 CHAR), + outbound_proxy VARCHAR2(40 CHAR), + retry_interval INTEGER, + forbidden_retry_interval INTEGER, + server_uri VARCHAR2(40 CHAR), + transport VARCHAR2(40 CHAR), + support_path VARCHAR(3 CHAR), + UNIQUE (id), + CONSTRAINT yesno_values CHECK (auth_rejection_permanent IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (support_path IN ('yes', 'no')) +) + +/ + +CREATE INDEX ps_registrations_id ON ps_registrations (id) + +/ + +ALTER TABLE ps_endpoints ADD media_address VARCHAR2(40 CHAR) + +/ + +ALTER TABLE ps_endpoints ADD redirect_method VARCHAR(9 CHAR) + +/ + +ALTER TABLE ps_endpoints ADD CONSTRAINT pjsip_redirect_method_values CHECK (redirect_method IN ('user', 'uri_core', 'uri_pjsip')) + +/ + +ALTER TABLE ps_endpoints ADD set_var CLOB + +/ + +ALTER TABLE ps_endpoints RENAME COLUMN mwi_fromuser TO mwi_from_user + +/ + +ALTER TABLE ps_contacts ADD outbound_proxy VARCHAR2(40 CHAR) + +/ + +ALTER TABLE ps_contacts ADD path CLOB + +/ + +ALTER TABLE ps_aors ADD maximum_expiration INTEGER + +/ + +ALTER TABLE ps_aors ADD outbound_proxy VARCHAR2(40 CHAR) + +/ + +ALTER TABLE ps_aors ADD support_path VARCHAR(3 CHAR) + +/ + +ALTER TABLE ps_aors ADD CONSTRAINT yesno_values CHECK (support_path IN ('yes', 'no')) + +/ + +UPDATE alembic_version SET version_num='2fc7930b41b3' WHERE alembic_version.version_num = '581a4264e537' + +/ + +-- Running upgrade 2fc7930b41b3 -> 21e526ad3040 + +ALTER TABLE ps_globals ADD debug VARCHAR2(40 CHAR) + +/ + +UPDATE alembic_version SET version_num='21e526ad3040' WHERE alembic_version.version_num = '2fc7930b41b3' + +/ + +-- Running upgrade 21e526ad3040 -> 28887f25a46f + +CREATE TABLE queues ( + name VARCHAR2(128 CHAR) NOT NULL, + musiconhold VARCHAR2(128 CHAR), + announce VARCHAR2(128 CHAR), + context VARCHAR2(128 CHAR), + timeout INTEGER, + ringinuse VARCHAR(3 CHAR), + setinterfacevar VARCHAR(3 CHAR), + setqueuevar VARCHAR(3 CHAR), + setqueueentryvar VARCHAR(3 CHAR), + monitor_format VARCHAR2(8 CHAR), + membermacro VARCHAR2(512 CHAR), + membergosub VARCHAR2(512 CHAR), + queue_youarenext VARCHAR2(128 CHAR), + queue_thereare VARCHAR2(128 CHAR), + queue_callswaiting VARCHAR2(128 CHAR), + queue_quantity1 VARCHAR2(128 CHAR), + queue_quantity2 VARCHAR2(128 CHAR), + queue_holdtime VARCHAR2(128 CHAR), + queue_minutes VARCHAR2(128 CHAR), + queue_minute VARCHAR2(128 CHAR), + queue_seconds VARCHAR2(128 CHAR), + queue_thankyou VARCHAR2(128 CHAR), + queue_callerannounce VARCHAR2(128 CHAR), + queue_reporthold VARCHAR2(128 CHAR), + announce_frequency INTEGER, + announce_to_first_user VARCHAR(3 CHAR), + min_announce_frequency INTEGER, + announce_round_seconds INTEGER, + announce_holdtime VARCHAR2(128 CHAR), + announce_position VARCHAR2(128 CHAR), + announce_position_limit INTEGER, + periodic_announce VARCHAR2(50 CHAR), + periodic_announce_frequency INTEGER, + relative_periodic_announce VARCHAR(3 CHAR), + random_periodic_announce VARCHAR(3 CHAR), + retry INTEGER, + wrapuptime INTEGER, + penaltymemberslimit INTEGER, + autofill VARCHAR(3 CHAR), + monitor_type VARCHAR2(128 CHAR), + autopause VARCHAR(3 CHAR), + autopausedelay INTEGER, + autopausebusy VARCHAR(3 CHAR), + autopauseunavail VARCHAR(3 CHAR), + maxlen INTEGER, + servicelevel INTEGER, + strategy VARCHAR(11 CHAR), + joinempty VARCHAR2(128 CHAR), + leavewhenempty VARCHAR2(128 CHAR), + reportholdtime VARCHAR(3 CHAR), + memberdelay INTEGER, + weight INTEGER, + timeoutrestart VARCHAR(3 CHAR), + defaultrule VARCHAR2(128 CHAR), + timeoutpriority VARCHAR2(128 CHAR), + PRIMARY KEY (name), + CONSTRAINT yesno_values CHECK (ringinuse IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (setinterfacevar IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (setqueuevar IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (setqueueentryvar IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (announce_to_first_user IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (relative_periodic_announce IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (random_periodic_announce IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (autofill IN ('yes', 'no')), + CONSTRAINT queue_autopause_values CHECK (autopause IN ('yes', 'no', 'all')), + CONSTRAINT yesno_values CHECK (autopausebusy IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (autopauseunavail IN ('yes', 'no')), + CONSTRAINT queue_strategy_values CHECK (strategy IN ('ringall', 'leastrecent', 'fewestcalls', 'random', 'rrmemory', 'linear', 'wrandom', 'rrordered')), + CONSTRAINT yesno_values CHECK (reportholdtime IN ('yes', 'no')), + CONSTRAINT yesno_values CHECK (timeoutrestart IN ('yes', 'no')) +) + +/ + +CREATE TABLE queue_members ( + queue_name VARCHAR2(80 CHAR) NOT NULL, + interface VARCHAR2(80 CHAR) NOT NULL, + uniqueid VARCHAR2(80 CHAR) NOT NULL, + membername VARCHAR2(80 CHAR), + state_interface VARCHAR2(80 CHAR), + penalty INTEGER, + paused INTEGER, + PRIMARY KEY (queue_name, interface) +) + +/ + +UPDATE alembic_version SET version_num='28887f25a46f' WHERE alembic_version.version_num = '21e526ad3040' + +/ + +-- Running upgrade 28887f25a46f -> 4c573e7135bd + +ALTER TABLE ps_endpoints MODIFY tos_audio VARCHAR2(10 CHAR) + +/ + +ALTER TABLE ps_endpoints MODIFY tos_video VARCHAR2(10 CHAR) + +/ + +ALTER TABLE ps_endpoints DROP COLUMN cos_audio + +/ + +ALTER TABLE ps_endpoints DROP COLUMN cos_video + +/ + +ALTER TABLE ps_endpoints ADD cos_audio INTEGER + +/ + +ALTER TABLE ps_endpoints ADD cos_video INTEGER + +/ + +ALTER TABLE ps_transports MODIFY tos VARCHAR2(10 CHAR) + +/ + +ALTER TABLE ps_transports DROP COLUMN cos + +/ + +ALTER TABLE ps_transports ADD cos INTEGER + +/ + +UPDATE alembic_version SET version_num='4c573e7135bd' WHERE alembic_version.version_num = '28887f25a46f' + +/ + +-- Running upgrade 4c573e7135bd -> 3855ee4e5f85 + +ALTER TABLE ps_endpoints ADD message_context VARCHAR2(40 CHAR) + +/ + +ALTER TABLE ps_contacts ADD user_agent VARCHAR2(40 CHAR) + +/ + +UPDATE alembic_version SET version_num='3855ee4e5f85' WHERE alembic_version.version_num = '4c573e7135bd' + +/ + +-- Running upgrade 3855ee4e5f85 -> e96a0b8071c + +ALTER TABLE ps_globals MODIFY user_agent VARCHAR2(255 CHAR) + +/ + +ALTER TABLE ps_contacts MODIFY id VARCHAR2(255 CHAR) + +/ + +ALTER TABLE ps_contacts MODIFY uri VARCHAR2(255 CHAR) + +/ + +ALTER TABLE ps_contacts MODIFY user_agent VARCHAR2(255 CHAR) + +/ + +ALTER TABLE ps_registrations MODIFY client_uri VARCHAR2(255 CHAR) + +/ + +ALTER TABLE ps_registrations MODIFY server_uri VARCHAR2(255 CHAR) + +/ + +UPDATE alembic_version SET version_num='e96a0b8071c' WHERE alembic_version.version_num = '3855ee4e5f85' + +/ + +-- Running upgrade e96a0b8071c -> c6d929b23a8 + +CREATE TABLE ps_subscription_persistence ( + id VARCHAR2(40 CHAR) NOT NULL, + packet VARCHAR2(2048 CHAR), + src_name VARCHAR2(128 CHAR), + src_port INTEGER, + transport_key VARCHAR2(64 CHAR), + local_name VARCHAR2(128 CHAR), + local_port INTEGER, + cseq INTEGER, + tag VARCHAR2(128 CHAR), + endpoint VARCHAR2(40 CHAR), + expires INTEGER, + UNIQUE (id) +) + +/ + +CREATE INDEX ps_subscription_persistence_id ON ps_subscription_persistence (id) + +/ + +UPDATE alembic_version SET version_num='c6d929b23a8' WHERE alembic_version.version_num = 'e96a0b8071c' + +/ + +-- Running upgrade c6d929b23a8 -> 51f8cb66540e + +ALTER TABLE ps_endpoints ADD force_avp VARCHAR(3 CHAR) + +/ + +ALTER TABLE ps_endpoints ADD CONSTRAINT yesno_values CHECK (force_avp IN ('yes', 'no')) + +/ + +ALTER TABLE ps_endpoints ADD media_use_received_transport VARCHAR(3 CHAR) + +/ + +ALTER TABLE ps_endpoints ADD CONSTRAINT yesno_values CHECK (media_use_received_transport IN ('yes', 'no')) + +/ + +UPDATE alembic_version SET version_num='51f8cb66540e' WHERE alembic_version.version_num = 'c6d929b23a8' + +/ + +-- Running upgrade 51f8cb66540e -> 1d50859ed02e + +ALTER TABLE ps_endpoints ADD accountcode VARCHAR2(20 CHAR) + +/ + +UPDATE alembic_version SET version_num='1d50859ed02e' WHERE alembic_version.version_num = '51f8cb66540e' + +/ + +-- Running upgrade 1d50859ed02e -> 1758e8bbf6b + +ALTER TABLE sippeers MODIFY useragent VARCHAR2(255 CHAR) + +/ + +UPDATE alembic_version SET version_num='1758e8bbf6b' WHERE alembic_version.version_num = '1d50859ed02e' + +/ + +-- Running upgrade 1758e8bbf6b -> 5139253c0423 + +ALTER TABLE queue_members DROP COLUMN uniqueid + +/ + +ALTER TABLE queue_members ADD uniqueid INTEGER NOT NULL + +/ + +ALTER TABLE queue_members ADD UNIQUE (uniqueid) + +/ + +UPDATE alembic_version SET version_num='5139253c0423' WHERE alembic_version.version_num = '1758e8bbf6b' + +/ + +-- Running upgrade 5139253c0423 -> d39508cb8d8 + +CREATE TABLE queue_rules ( + rule_name VARCHAR2(80 CHAR) NOT NULL, + time VARCHAR2(32 CHAR) NOT NULL, + min_penalty VARCHAR2(32 CHAR) NOT NULL, + max_penalty VARCHAR2(32 CHAR) NOT NULL +) + +/ + +UPDATE alembic_version SET version_num='d39508cb8d8' WHERE alembic_version.version_num = '5139253c0423' + +/ + +-- Running upgrade d39508cb8d8 -> 5950038a6ead + +ALTER TABLE ps_transports MODIFY verifiy_server VARCHAR(3 CHAR) + +/ + +ALTER TABLE ps_transports RENAME COLUMN verifiy_server TO verify_server + +/ + +ALTER TABLE ps_transports ADD CONSTRAINT yesno_values CHECK (verifiy_server IN ('yes', 'no')) + +/ + +UPDATE alembic_version SET version_num='5950038a6ead' WHERE alembic_version.version_num = 'd39508cb8d8' + +/ + +-- Running upgrade 5950038a6ead -> 10aedae86a32 + +ALTER TABLE sippeers DROP CONSTRAINT sip_directmedia_values + +/ + +ALTER TABLE sippeers MODIFY directmedia VARCHAR(8 CHAR) + +/ + +ALTER TABLE sippeers ADD CONSTRAINT sip_directmedia_values_v2 CHECK (directmedia IN ('yes', 'no', 'nonat', 'update', 'outgoing')) + +/ + +UPDATE alembic_version SET version_num='10aedae86a32' WHERE alembic_version.version_num = '5950038a6ead' + +/ + +-- Running upgrade 10aedae86a32 -> eb88a14f2a + +ALTER TABLE ps_endpoints ADD media_encryption_optimistic VARCHAR(3 CHAR) + +/ + +ALTER TABLE ps_endpoints ADD CONSTRAINT yesno_values CHECK (media_encryption_optimistic IN ('yes', 'no')) + +/ + +UPDATE alembic_version SET version_num='eb88a14f2a' WHERE alembic_version.version_num = '10aedae86a32' + +/ + +-- Running upgrade eb88a14f2a -> 371a3bf4143e + +ALTER TABLE ps_endpoints ADD user_eq_phone VARCHAR(3 CHAR) + +/ + +ALTER TABLE ps_endpoints ADD CONSTRAINT yesno_values CHECK (user_eq_phone IN ('yes', 'no')) + +/ + +UPDATE alembic_version SET version_num='371a3bf4143e' WHERE alembic_version.version_num = 'eb88a14f2a' + +/ + +-- Running upgrade 371a3bf4143e -> 45e3f47c6c44 + +ALTER TABLE ps_globals ADD endpoint_identifier_order VARCHAR2(40 CHAR) + +/ + +UPDATE alembic_version SET version_num='45e3f47c6c44' WHERE alembic_version.version_num = '371a3bf4143e' + +/ + +-- Running upgrade 45e3f47c6c44 -> 23530d604b96 + +ALTER TABLE ps_endpoints ADD rpid_immediate VARCHAR(3 CHAR) + +/ + +ALTER TABLE ps_endpoints ADD CONSTRAINT yesno_values CHECK (rpid_immediate IN ('yes', 'no')) + +/ + +UPDATE alembic_version SET version_num='23530d604b96' WHERE alembic_version.version_num = '45e3f47c6c44' + +/ + +-- Running upgrade 23530d604b96 -> 31cd4f4891ec + +ALTER TABLE ps_endpoints DROP CONSTRAINT pjsip_dtmf_mode_values + +/ + +ALTER TABLE ps_endpoints MODIFY dtmf_mode VARCHAR(7 CHAR) + +/ + +ALTER TABLE ps_endpoints ADD CONSTRAINT pjsip_dtmf_mode_values_v2 CHECK (dtmf_mode IN ('rfc4733', 'inband', 'info', 'auto')) + +/ + +UPDATE alembic_version SET version_num='31cd4f4891ec' WHERE alembic_version.version_num = '23530d604b96' + +/ + +-- Running upgrade 31cd4f4891ec -> 461d7d691209 + +ALTER TABLE ps_aors ADD qualify_timeout INTEGER + +/ + +ALTER TABLE ps_contacts ADD qualify_timeout INTEGER + +/ + +UPDATE alembic_version SET version_num='461d7d691209' WHERE alembic_version.version_num = '31cd4f4891ec' + +/ + +-- Running upgrade 461d7d691209 -> a541e0b5e89 + +ALTER TABLE ps_globals ADD max_initial_qualify_time INTEGER + +/ + +UPDATE alembic_version SET version_num='a541e0b5e89' WHERE alembic_version.version_num = '461d7d691209' + +/ + +-- Running upgrade a541e0b5e89 -> 28b8e71e541f + +ALTER TABLE ps_endpoints ADD g726_non_standard VARCHAR(3 CHAR) + +/ + +ALTER TABLE ps_endpoints ADD CONSTRAINT yesno_values CHECK (g726_non_standard IN ('yes', 'no')) + +/ + +UPDATE alembic_version SET version_num='28b8e71e541f' WHERE alembic_version.version_num = 'a541e0b5e89' + +/ + +-- Running upgrade 28b8e71e541f -> 498357a710ae + +ALTER TABLE ps_endpoints ADD rtp_keepalive INTEGER + +/ + +UPDATE alembic_version SET version_num='498357a710ae' WHERE alembic_version.version_num = '28b8e71e541f' + +/ + +-- Running upgrade 498357a710ae -> 26f10cadc157 + +ALTER TABLE ps_endpoints ADD rtp_timeout INTEGER + +/ + +ALTER TABLE ps_endpoints ADD rtp_timeout_hold INTEGER + +/ + +UPDATE alembic_version SET version_num='26f10cadc157' WHERE alembic_version.version_num = '498357a710ae' + +/ + +-- Running upgrade 26f10cadc157 -> 154177371065 + +ALTER TABLE ps_globals ADD default_from_user VARCHAR2(80 CHAR) + +/ + +UPDATE alembic_version SET version_num='154177371065' WHERE alembic_version.version_num = '26f10cadc157' + +/ + +-- Running upgrade 154177371065 -> 28ce1e718f05 + +ALTER TABLE ps_registrations ADD fatal_retry_interval INTEGER + +/ + +UPDATE alembic_version SET version_num='28ce1e718f05' WHERE alembic_version.version_num = '154177371065' + +/ + +-- Running upgrade 28ce1e718f05 -> 189a235b3fd7 + +ALTER TABLE ps_globals ADD keep_alive_interval INTEGER + +/ + +UPDATE alembic_version SET version_num='189a235b3fd7' WHERE alembic_version.version_num = '28ce1e718f05' + +/ + +-- Running upgrade 189a235b3fd7 -> 2d078ec071b7 + +ALTER TABLE ps_aors MODIFY contact VARCHAR2(255 CHAR) + +/ + +UPDATE alembic_version SET version_num='2d078ec071b7' WHERE alembic_version.version_num = '189a235b3fd7' + +/ + +-- Running upgrade 2d078ec071b7 -> 26d7f3bf0fa5 + +ALTER TABLE ps_endpoints ADD bind_rtp_to_media_address VARCHAR(3 CHAR) + +/ + +ALTER TABLE ps_endpoints ADD CONSTRAINT yesno_values CHECK (bind_rtp_to_media_address IN ('yes', 'no')) + +/ + +UPDATE alembic_version SET version_num='26d7f3bf0fa5' WHERE alembic_version.version_num = '2d078ec071b7' + +/ + +-- Running upgrade 26d7f3bf0fa5 -> 136885b81223 + +ALTER TABLE ps_globals ADD regcontext VARCHAR2(80 CHAR) + +/ + +UPDATE alembic_version SET version_num='136885b81223' WHERE alembic_version.version_num = '26d7f3bf0fa5' + +/ + +-- Running upgrade 136885b81223 -> 423f34ad36e2 + +ALTER TABLE ps_aors MODIFY qualify_timeout FLOAT + +/ + +ALTER TABLE ps_contacts MODIFY qualify_timeout FLOAT + +/ + +UPDATE alembic_version SET version_num='423f34ad36e2' WHERE alembic_version.version_num = '136885b81223' + +/ + +-- Running upgrade 423f34ad36e2 -> dbc44d5a908 + +ALTER TABLE ps_systems ADD disable_tcp_switch VARCHAR(3 CHAR) + +/ + +ALTER TABLE ps_systems ADD CONSTRAINT yesno_values CHECK (disable_tcp_switch IN ('yes', 'no')) + +/ + +ALTER TABLE ps_registrations ADD line VARCHAR(3 CHAR) + +/ + +ALTER TABLE ps_registrations ADD CONSTRAINT yesno_values CHECK (line IN ('yes', 'no')) + +/ + +ALTER TABLE ps_registrations ADD endpoint VARCHAR2(40 CHAR) + +/ + +UPDATE alembic_version SET version_num='dbc44d5a908' WHERE alembic_version.version_num = '423f34ad36e2' + +/ + +-- Running upgrade dbc44d5a908 -> 3bcc0b5bc2c9 + +ALTER TABLE ps_transports ADD allow_reload VARCHAR(3 CHAR) + +/ + +ALTER TABLE ps_transports ADD CONSTRAINT yesno_values CHECK (allow_reload IN ('yes', 'no')) + +/ + +UPDATE alembic_version SET version_num='3bcc0b5bc2c9' WHERE alembic_version.version_num = 'dbc44d5a908' + +/ + +-- Running upgrade 3bcc0b5bc2c9 -> 5813202e92be + +ALTER TABLE ps_globals ADD contact_expiration_check_interval INTEGER + +/ + +UPDATE alembic_version SET version_num='5813202e92be' WHERE alembic_version.version_num = '3bcc0b5bc2c9' + +/ + +-- Running upgrade 5813202e92be -> 1c688d9a003c + +ALTER TABLE ps_globals ADD default_voicemail_extension VARCHAR2(40 CHAR) + +/ + +ALTER TABLE ps_aors ADD voicemail_extension VARCHAR2(40 CHAR) + +/ + +ALTER TABLE ps_endpoints ADD voicemail_extension VARCHAR2(40 CHAR) + +/ + +ALTER TABLE ps_endpoints ADD mwi_subscribe_replaces_unsolicited INTEGER + +/ + +UPDATE alembic_version SET version_num='1c688d9a003c' WHERE alembic_version.version_num = '5813202e92be' + +/ + +-- Running upgrade 1c688d9a003c -> 8d478ab86e29 + +ALTER TABLE ps_globals ADD disable_multi_domain VARCHAR(3 CHAR) + +/ + +ALTER TABLE ps_globals ADD CONSTRAINT yesno_values CHECK (disable_multi_domain IN ('yes', 'no')) + +/ + +UPDATE alembic_version SET version_num='8d478ab86e29' WHERE alembic_version.version_num = '1c688d9a003c' + +/ + +-- Running upgrade 8d478ab86e29 -> 65eb22eb195 + +ALTER TABLE ps_globals ADD unidentified_request_count INTEGER + +/ + +ALTER TABLE ps_globals ADD unidentified_request_period INTEGER + +/ + +ALTER TABLE ps_globals ADD unidentified_request_prune_interval INTEGER + +/ + +ALTER TABLE ps_globals ADD default_realm VARCHAR2(40 CHAR) + +/ + +UPDATE alembic_version SET version_num='65eb22eb195' WHERE alembic_version.version_num = '8d478ab86e29' + +/ + +-- Running upgrade 65eb22eb195 -> 81b01a191a46 + +ALTER TABLE ps_contacts ADD reg_server VARCHAR2(20 CHAR) + +/ + +ALTER TABLE ps_contacts ADD CONSTRAINT ps_contacts_uq UNIQUE (id, reg_server) + +/ + +UPDATE alembic_version SET version_num='81b01a191a46' WHERE alembic_version.version_num = '65eb22eb195' + +/ + +-- Running upgrade 81b01a191a46 -> 6be31516058d + +ALTER TABLE ps_contacts ADD authenticate_qualify VARCHAR(3 CHAR) + +/ + +ALTER TABLE ps_contacts ADD CONSTRAINT yesno_values CHECK (authenticate_qualify IN ('yes', 'no')) + +/ + +UPDATE alembic_version SET version_num='6be31516058d' WHERE alembic_version.version_num = '81b01a191a46' + +/ + +-- Running upgrade 6be31516058d -> bca7113d796f + +ALTER TABLE ps_endpoints ADD deny VARCHAR2(95 CHAR) + +/ + +ALTER TABLE ps_endpoints ADD permit VARCHAR2(95 CHAR) + +/ + +ALTER TABLE ps_endpoints ADD acl VARCHAR2(40 CHAR) + +/ + +ALTER TABLE ps_endpoints ADD contact_deny VARCHAR2(95 CHAR) + +/ + +ALTER TABLE ps_endpoints ADD contact_permit VARCHAR2(95 CHAR) + +/ + +ALTER TABLE ps_endpoints ADD contact_acl VARCHAR2(40 CHAR) + +/ + +UPDATE alembic_version SET version_num='bca7113d796f' WHERE alembic_version.version_num = '6be31516058d' + +/ + +-- Running upgrade bca7113d796f -> a845e4d8ade8 + +ALTER TABLE ps_contacts ADD via_addr VARCHAR2(40 CHAR) + +/ + +ALTER TABLE ps_contacts ADD via_port INTEGER + +/ + +ALTER TABLE ps_contacts ADD call_id VARCHAR2(255 CHAR) + +/ + +UPDATE alembic_version SET version_num='a845e4d8ade8' WHERE alembic_version.version_num = 'bca7113d796f' + +/ + +-- Running upgrade a845e4d8ade8 -> ef7efc2d3964 + +ALTER TABLE ps_contacts ADD endpoint VARCHAR2(40 CHAR) + +/ + +ALTER TABLE ps_contacts MODIFY expiration_time NUMBER(19) + +/ + +CREATE INDEX ps_contacts_qualifyfreq_exp ON ps_contacts (qualify_frequency, expiration_time) + +/ + +CREATE INDEX ps_aors_qualifyfreq_contact ON ps_aors (qualify_frequency, contact) + +/ + +UPDATE alembic_version SET version_num='ef7efc2d3964' WHERE alembic_version.version_num = 'a845e4d8ade8' + +/ + +-- Running upgrade ef7efc2d3964 -> 9deac0ae4717 + +ALTER TABLE ps_endpoints ADD subscribe_context VARCHAR2(40 CHAR) + +/ + +UPDATE alembic_version SET version_num='9deac0ae4717' WHERE alembic_version.version_num = 'ef7efc2d3964' + +/ + +-- Running upgrade 9deac0ae4717 -> 4a6c67fa9b7a + +ALTER TABLE ps_endpoints ADD fax_detect_timeout INTEGER + +/ + +UPDATE alembic_version SET version_num='4a6c67fa9b7a' WHERE alembic_version.version_num = '9deac0ae4717' + +/ + +-- Running upgrade 4a6c67fa9b7a -> c7a44a5a0851 + +ALTER TABLE ps_globals ADD mwi_tps_queue_high INTEGER + +/ + +ALTER TABLE ps_globals ADD mwi_tps_queue_low INTEGER + +/ + +ALTER TABLE ps_globals ADD mwi_disable_initial_unsolicited VARCHAR(3 CHAR) + +/ + +ALTER TABLE ps_globals ADD CONSTRAINT yesno_values CHECK (mwi_disable_initial_unsolicited IN ('yes', 'no')) + +/ + +UPDATE alembic_version SET version_num='c7a44a5a0851' WHERE alembic_version.version_num = '4a6c67fa9b7a' + +/ + +-- Running upgrade c7a44a5a0851 -> 3772f8f828da + +ALTER TABLE ps_endpoints MODIFY identify_by VARCHAR(13 CHAR) + +/ + +ALTER TABLE ps_endpoints ADD CONSTRAINT pjsip_identify_by_values CHECK (identify_by IN ('username', 'auth_username')) + +/ + +UPDATE alembic_version SET version_num='3772f8f828da' WHERE alembic_version.version_num = 'c7a44a5a0851' + +/ + +-- Running upgrade 3772f8f828da -> 4e2493ef32e6 + +ALTER TABLE ps_endpoints ADD contact_user VARCHAR2(80 CHAR) + +/ + +UPDATE alembic_version SET version_num='4e2493ef32e6' WHERE alembic_version.version_num = '3772f8f828da' + +/ + +-- Running upgrade 4e2493ef32e6 -> a6ef36f1309 + +ALTER TABLE ps_globals ADD ignore_uri_user_options VARCHAR(3 CHAR) + +/ + +ALTER TABLE ps_globals ADD CONSTRAINT yesno_values CHECK (ignore_uri_user_options IN ('yes', 'no')) + +/ + +UPDATE alembic_version SET version_num='a6ef36f1309' WHERE alembic_version.version_num = '4e2493ef32e6' + +/ + +-- Running upgrade a6ef36f1309 -> 4468b4a91372 + +ALTER TABLE ps_endpoints ADD asymmetric_rtp_codec VARCHAR(3 CHAR) + +/ + +ALTER TABLE ps_endpoints ADD CONSTRAINT yesno_values CHECK (asymmetric_rtp_codec IN ('yes', 'no')) + +/ + +UPDATE alembic_version SET version_num='4468b4a91372' WHERE alembic_version.version_num = 'a6ef36f1309' + +/ + +-- Running upgrade 4468b4a91372 -> 28ab27a7826d + +ALTER TABLE ps_endpoint_id_ips ADD srv_lookups VARCHAR(3 CHAR) + +/ + +ALTER TABLE ps_endpoint_id_ips ADD CONSTRAINT yesno_values CHECK (srv_lookups IN ('yes', 'no')) + +/ + +UPDATE alembic_version SET version_num='28ab27a7826d' WHERE alembic_version.version_num = '4468b4a91372' + +/ + +-- Running upgrade 28ab27a7826d -> 465e70e8c337 + +ALTER TABLE ps_endpoint_id_ips ADD match_header VARCHAR2(255 CHAR) + +/ + +UPDATE alembic_version SET version_num='465e70e8c337' WHERE alembic_version.version_num = '28ab27a7826d' + +/ + +-- Running upgrade 465e70e8c337 -> 15db7b91a97a + +ALTER TABLE ps_endpoints ADD rtcp_mux VARCHAR(3 CHAR) + +/ + +ALTER TABLE ps_endpoints ADD CONSTRAINT yesno_values CHECK (rtcp_mux IN ('yes', 'no')) + +/ + +UPDATE alembic_version SET version_num='15db7b91a97a' WHERE alembic_version.version_num = '465e70e8c337' + +/ + +-- Running upgrade 15db7b91a97a -> f638dbe2eb23 + +ALTER TABLE ps_transports ADD symmetric_transport VARCHAR(3 CHAR) + +/ + +ALTER TABLE ps_transports ADD CONSTRAINT yesno_values CHECK (symmetric_transport IN ('yes', 'no')) + +/ + +ALTER TABLE ps_subscription_persistence ADD contact_uri VARCHAR2(256 CHAR) + +/ + +UPDATE alembic_version SET version_num='f638dbe2eb23' WHERE alembic_version.version_num = '15db7b91a97a' + +/ + +-- Running upgrade f638dbe2eb23 -> 8fce4c573e15 + +ALTER TABLE ps_endpoints ADD allow_overlap VARCHAR(3 CHAR) + +/ + +ALTER TABLE ps_endpoints ADD CONSTRAINT yesno_values CHECK (allow_overlap IN ('yes', 'no')) + +/ + +UPDATE alembic_version SET version_num='8fce4c573e15' WHERE alembic_version.version_num = 'f638dbe2eb23' + +/ + diff --git a/contrib/realtime/oracle/oracle_voicemail.sql b/contrib/realtime/oracle/oracle_voicemail.sql new file mode 100644 index 000000000..12133ea8c --- /dev/null +++ b/contrib/realtime/oracle/oracle_voicemail.sql @@ -0,0 +1,48 @@ +CREATE TABLE alembic_version ( + version_num VARCHAR2(32 CHAR) NOT NULL +) + +/ + +-- Running upgrade -> a2e9769475e + +CREATE TABLE voicemail_messages ( + dir VARCHAR2(255 CHAR) NOT NULL, + msgnum INTEGER NOT NULL, + context VARCHAR2(80 CHAR), + macrocontext VARCHAR2(80 CHAR), + callerid VARCHAR2(80 CHAR), + origtime INTEGER, + duration INTEGER, + recording BLOB, + flag VARCHAR2(30 CHAR), + category VARCHAR2(30 CHAR), + mailboxuser VARCHAR2(30 CHAR), + mailboxcontext VARCHAR2(30 CHAR), + msg_id VARCHAR2(40 CHAR) +) + +/ + +ALTER TABLE voicemail_messages ADD CONSTRAINT voicemail_messages_dir_msgnum PRIMARY KEY (dir, msgnum) + +/ + +CREATE INDEX voicemail_messages_dir ON voicemail_messages (dir) + +/ + +INSERT INTO alembic_version (version_num) VALUES ('a2e9769475e') + +/ + +-- Running upgrade a2e9769475e -> 39428242f7f5 + +ALTER TABLE voicemail_messages MODIFY recording BLOB + +/ + +UPDATE alembic_version SET version_num='39428242f7f5' WHERE alembic_version.version_num = 'a2e9769475e' + +/ + diff --git a/contrib/realtime/postgresql/postgresql_cdr.sql b/contrib/realtime/postgresql/postgresql_cdr.sql new file mode 100644 index 000000000..5f1ffa4b1 --- /dev/null +++ b/contrib/realtime/postgresql/postgresql_cdr.sql @@ -0,0 +1,36 @@ +BEGIN; + +CREATE TABLE alembic_version ( + version_num VARCHAR(32) NOT NULL +); + +-- Running upgrade -> 210693f3123d + +CREATE TABLE cdr ( + accountcode VARCHAR(20), + src VARCHAR(80), + dst VARCHAR(80), + dcontext VARCHAR(80), + clid VARCHAR(80), + channel VARCHAR(80), + dstchannel VARCHAR(80), + lastapp VARCHAR(80), + lastdata VARCHAR(80), + start TIMESTAMP WITHOUT TIME ZONE, + answer TIMESTAMP WITHOUT TIME ZONE, + "end" TIMESTAMP WITHOUT TIME ZONE, + duration INTEGER, + billsec INTEGER, + disposition VARCHAR(45), + amaflags VARCHAR(45), + userfield VARCHAR(256), + uniqueid VARCHAR(150), + linkedid VARCHAR(150), + peeraccount VARCHAR(20), + sequence INTEGER +); + +INSERT INTO alembic_version (version_num) VALUES ('210693f3123d'); + +COMMIT; + diff --git a/contrib/realtime/postgresql/postgresql_config.sql b/contrib/realtime/postgresql/postgresql_config.sql new file mode 100644 index 000000000..3ac125c03 --- /dev/null +++ b/contrib/realtime/postgresql/postgresql_config.sql @@ -0,0 +1,1068 @@ +BEGIN; + +CREATE TABLE alembic_version ( + version_num VARCHAR(32) NOT NULL +); + +-- Running upgrade -> 4da0c5f79a9c + +CREATE TYPE type_values AS ENUM ('friend', 'user', 'peer'); + +CREATE TYPE sip_transport_values AS ENUM ('udp', 'tcp', 'tls', 'ws', 'wss', 'udp,tcp', 'tcp,udp'); + +CREATE TYPE sip_dtmfmode_values AS ENUM ('rfc2833', 'info', 'shortinfo', 'inband', 'auto'); + +CREATE TYPE sip_directmedia_values AS ENUM ('yes', 'no', 'nonat', 'update'); + +CREATE TYPE yes_no_values AS ENUM ('yes', 'no'); + +CREATE TYPE sip_progressinband_values AS ENUM ('yes', 'no', 'never'); + +CREATE TYPE sip_session_timers_values AS ENUM ('accept', 'refuse', 'originate'); + +CREATE TYPE sip_session_refresher_values AS ENUM ('uac', 'uas'); + +CREATE TYPE sip_callingpres_values AS ENUM ('allowed_not_screened', 'allowed_passed_screen', 'allowed_failed_screen', 'allowed', 'prohib_not_screened', 'prohib_passed_screen', 'prohib_failed_screen', 'prohib'); + +CREATE TABLE sippeers ( + id SERIAL NOT NULL, + name VARCHAR(40) NOT NULL, + ipaddr VARCHAR(45), + port INTEGER, + regseconds INTEGER, + defaultuser VARCHAR(40), + fullcontact VARCHAR(80), + regserver VARCHAR(20), + useragent VARCHAR(20), + lastms INTEGER, + host VARCHAR(40), + type type_values, + context VARCHAR(40), + permit VARCHAR(95), + deny VARCHAR(95), + secret VARCHAR(40), + md5secret VARCHAR(40), + remotesecret VARCHAR(40), + transport sip_transport_values, + dtmfmode sip_dtmfmode_values, + directmedia sip_directmedia_values, + nat VARCHAR(29), + callgroup VARCHAR(40), + pickupgroup VARCHAR(40), + language VARCHAR(40), + disallow VARCHAR(200), + allow VARCHAR(200), + insecure VARCHAR(40), + trustrpid yes_no_values, + progressinband sip_progressinband_values, + promiscredir yes_no_values, + useclientcode yes_no_values, + accountcode VARCHAR(40), + setvar VARCHAR(200), + callerid VARCHAR(40), + amaflags VARCHAR(40), + callcounter yes_no_values, + busylevel INTEGER, + allowoverlap yes_no_values, + allowsubscribe yes_no_values, + videosupport yes_no_values, + maxcallbitrate INTEGER, + rfc2833compensate yes_no_values, + mailbox VARCHAR(40), + "session-timers" sip_session_timers_values, + "session-expires" INTEGER, + "session-minse" INTEGER, + "session-refresher" sip_session_refresher_values, + t38pt_usertpsource VARCHAR(40), + regexten VARCHAR(40), + fromdomain VARCHAR(40), + fromuser VARCHAR(40), + qualify VARCHAR(40), + defaultip VARCHAR(45), + rtptimeout INTEGER, + rtpholdtimeout INTEGER, + sendrpid yes_no_values, + outboundproxy VARCHAR(40), + callbackextension VARCHAR(40), + timert1 INTEGER, + timerb INTEGER, + qualifyfreq INTEGER, + constantssrc yes_no_values, + contactpermit VARCHAR(95), + contactdeny VARCHAR(95), + usereqphone yes_no_values, + textsupport yes_no_values, + faxdetect yes_no_values, + buggymwi yes_no_values, + auth VARCHAR(40), + fullname VARCHAR(40), + trunkname VARCHAR(40), + cid_number VARCHAR(40), + callingpres sip_callingpres_values, + mohinterpret VARCHAR(40), + mohsuggest VARCHAR(40), + parkinglot VARCHAR(40), + hasvoicemail yes_no_values, + subscribemwi yes_no_values, + vmexten VARCHAR(40), + autoframing yes_no_values, + rtpkeepalive INTEGER, + "call-limit" INTEGER, + g726nonstandard yes_no_values, + ignoresdpversion yes_no_values, + allowtransfer yes_no_values, + dynamic yes_no_values, + path VARCHAR(256), + supportpath yes_no_values, + PRIMARY KEY (id), + UNIQUE (name) +); + +CREATE INDEX sippeers_name ON sippeers (name); + +CREATE INDEX sippeers_name_host ON sippeers (name, host); + +CREATE INDEX sippeers_ipaddr_port ON sippeers (ipaddr, port); + +CREATE INDEX sippeers_host_port ON sippeers (host, port); + +CREATE TYPE iax_requirecalltoken_values AS ENUM ('yes', 'no', 'auto'); + +CREATE TYPE iax_encryption_values AS ENUM ('yes', 'no', 'aes128'); + +CREATE TYPE iax_transfer_values AS ENUM ('yes', 'no', 'mediaonly'); + +CREATE TABLE iaxfriends ( + id SERIAL NOT NULL, + name VARCHAR(40) NOT NULL, + type type_values, + username VARCHAR(40), + mailbox VARCHAR(40), + secret VARCHAR(40), + dbsecret VARCHAR(40), + context VARCHAR(40), + regcontext VARCHAR(40), + host VARCHAR(40), + ipaddr VARCHAR(40), + port INTEGER, + defaultip VARCHAR(20), + sourceaddress VARCHAR(20), + mask VARCHAR(20), + regexten VARCHAR(40), + regseconds INTEGER, + accountcode VARCHAR(20), + mohinterpret VARCHAR(20), + mohsuggest VARCHAR(20), + inkeys VARCHAR(40), + outkeys VARCHAR(40), + language VARCHAR(10), + callerid VARCHAR(100), + cid_number VARCHAR(40), + sendani yes_no_values, + fullname VARCHAR(40), + trunk yes_no_values, + auth VARCHAR(20), + maxauthreq INTEGER, + requirecalltoken iax_requirecalltoken_values, + encryption iax_encryption_values, + transfer iax_transfer_values, + jitterbuffer yes_no_values, + forcejitterbuffer yes_no_values, + disallow VARCHAR(200), + allow VARCHAR(200), + codecpriority VARCHAR(40), + qualify VARCHAR(10), + qualifysmoothing yes_no_values, + qualifyfreqok VARCHAR(10), + qualifyfreqnotok VARCHAR(10), + timezone VARCHAR(20), + adsi yes_no_values, + amaflags VARCHAR(20), + setvar VARCHAR(200), + PRIMARY KEY (id), + UNIQUE (name) +); + +CREATE INDEX iaxfriends_name ON iaxfriends (name); + +CREATE INDEX iaxfriends_name_host ON iaxfriends (name, host); + +CREATE INDEX iaxfriends_name_ipaddr_port ON iaxfriends (name, ipaddr, port); + +CREATE INDEX iaxfriends_ipaddr_port ON iaxfriends (ipaddr, port); + +CREATE INDEX iaxfriends_host_port ON iaxfriends (host, port); + +CREATE TABLE voicemail ( + uniqueid SERIAL NOT NULL, + context VARCHAR(80) NOT NULL, + mailbox VARCHAR(80) NOT NULL, + password VARCHAR(80) NOT NULL, + fullname VARCHAR(80), + alias VARCHAR(80), + email VARCHAR(80), + pager VARCHAR(80), + attach yes_no_values, + attachfmt VARCHAR(10), + serveremail VARCHAR(80), + language VARCHAR(20), + tz VARCHAR(30), + deletevoicemail yes_no_values, + saycid yes_no_values, + sendvoicemail yes_no_values, + review yes_no_values, + tempgreetwarn yes_no_values, + operator yes_no_values, + envelope yes_no_values, + sayduration INTEGER, + forcename yes_no_values, + forcegreetings yes_no_values, + callback VARCHAR(80), + dialout VARCHAR(80), + exitcontext VARCHAR(80), + maxmsg INTEGER, + volgain NUMERIC(5, 2), + imapuser VARCHAR(80), + imappassword VARCHAR(80), + imapserver VARCHAR(80), + imapport VARCHAR(8), + imapflags VARCHAR(80), + stamp TIMESTAMP WITHOUT TIME ZONE, + PRIMARY KEY (uniqueid) +); + +CREATE INDEX voicemail_mailbox ON voicemail (mailbox); + +CREATE INDEX voicemail_context ON voicemail (context); + +CREATE INDEX voicemail_mailbox_context ON voicemail (mailbox, context); + +CREATE INDEX voicemail_imapuser ON voicemail (imapuser); + +CREATE TABLE meetme ( + bookid SERIAL NOT NULL, + confno VARCHAR(80) NOT NULL, + starttime TIMESTAMP WITHOUT TIME ZONE, + endtime TIMESTAMP WITHOUT TIME ZONE, + pin VARCHAR(20), + adminpin VARCHAR(20), + opts VARCHAR(20), + adminopts VARCHAR(20), + recordingfilename VARCHAR(80), + recordingformat VARCHAR(10), + maxusers INTEGER, + members INTEGER NOT NULL, + PRIMARY KEY (bookid) +); + +CREATE INDEX meetme_confno_start_end ON meetme (confno, starttime, endtime); + +CREATE TYPE moh_mode_values AS ENUM ('custom', 'files', 'mp3nb', 'quietmp3nb', 'quietmp3'); + +CREATE TABLE musiconhold ( + name VARCHAR(80) NOT NULL, + mode moh_mode_values, + directory VARCHAR(255), + application VARCHAR(255), + digit VARCHAR(1), + sort VARCHAR(10), + format VARCHAR(10), + stamp TIMESTAMP WITHOUT TIME ZONE, + PRIMARY KEY (name) +); + +INSERT INTO alembic_version (version_num) VALUES ('4da0c5f79a9c'); + +-- Running upgrade 4da0c5f79a9c -> 43956d550a44 + +CREATE TYPE yesno_values AS ENUM ('yes', 'no'); + +CREATE TYPE pjsip_connected_line_method_values AS ENUM ('invite', 'reinvite', 'update'); + +CREATE TYPE pjsip_direct_media_glare_mitigation_values AS ENUM ('none', 'outgoing', 'incoming'); + +CREATE TYPE pjsip_dtmf_mode_values AS ENUM ('rfc4733', 'inband', 'info'); + +CREATE TYPE pjsip_identify_by_values AS ENUM ('username'); + +CREATE TYPE pjsip_timer_values AS ENUM ('forced', 'no', 'required', 'yes'); + +CREATE TYPE pjsip_cid_privacy_values AS ENUM ('allowed_not_screened', 'allowed_passed_screened', 'allowed_failed_screened', 'allowed', 'prohib_not_screened', 'prohib_passed_screened', 'prohib_failed_screened', 'prohib', 'unavailable'); + +CREATE TYPE pjsip_100rel_values AS ENUM ('no', 'required', 'yes'); + +CREATE TYPE pjsip_media_encryption_values AS ENUM ('no', 'sdes', 'dtls'); + +CREATE TYPE pjsip_t38udptl_ec_values AS ENUM ('none', 'fec', 'redundancy'); + +CREATE TYPE pjsip_dtls_setup_values AS ENUM ('active', 'passive', 'actpass'); + +CREATE TABLE ps_endpoints ( + id VARCHAR(40) NOT NULL, + transport VARCHAR(40), + aors VARCHAR(200), + auth VARCHAR(40), + context VARCHAR(40), + disallow VARCHAR(200), + allow VARCHAR(200), + direct_media yesno_values, + connected_line_method pjsip_connected_line_method_values, + direct_media_method pjsip_connected_line_method_values, + direct_media_glare_mitigation pjsip_direct_media_glare_mitigation_values, + disable_direct_media_on_nat yesno_values, + dtmf_mode pjsip_dtmf_mode_values, + external_media_address VARCHAR(40), + force_rport yesno_values, + ice_support yesno_values, + identify_by pjsip_identify_by_values, + mailboxes VARCHAR(40), + moh_suggest VARCHAR(40), + outbound_auth VARCHAR(40), + outbound_proxy VARCHAR(40), + rewrite_contact yesno_values, + rtp_ipv6 yesno_values, + rtp_symmetric yesno_values, + send_diversion yesno_values, + send_pai yesno_values, + send_rpid yesno_values, + timers_min_se INTEGER, + timers pjsip_timer_values, + timers_sess_expires INTEGER, + callerid VARCHAR(40), + callerid_privacy pjsip_cid_privacy_values, + callerid_tag VARCHAR(40), + "100rel" pjsip_100rel_values, + aggregate_mwi yesno_values, + trust_id_inbound yesno_values, + trust_id_outbound yesno_values, + use_ptime yesno_values, + use_avpf yesno_values, + media_encryption pjsip_media_encryption_values, + inband_progress yesno_values, + call_group VARCHAR(40), + pickup_group VARCHAR(40), + named_call_group VARCHAR(40), + named_pickup_group VARCHAR(40), + device_state_busy_at INTEGER, + fax_detect yesno_values, + t38_udptl yesno_values, + t38_udptl_ec pjsip_t38udptl_ec_values, + t38_udptl_maxdatagram INTEGER, + t38_udptl_nat yesno_values, + t38_udptl_ipv6 yesno_values, + tone_zone VARCHAR(40), + language VARCHAR(40), + one_touch_recording yesno_values, + record_on_feature VARCHAR(40), + record_off_feature VARCHAR(40), + rtp_engine VARCHAR(40), + allow_transfer yesno_values, + allow_subscribe yesno_values, + sdp_owner VARCHAR(40), + sdp_session VARCHAR(40), + tos_audio INTEGER, + tos_video INTEGER, + cos_audio INTEGER, + cos_video INTEGER, + sub_min_expiry INTEGER, + from_domain VARCHAR(40), + from_user VARCHAR(40), + mwi_fromuser VARCHAR(40), + dtls_verify VARCHAR(40), + dtls_rekey VARCHAR(40), + dtls_cert_file VARCHAR(200), + dtls_private_key VARCHAR(200), + dtls_cipher VARCHAR(200), + dtls_ca_file VARCHAR(200), + dtls_ca_path VARCHAR(200), + dtls_setup pjsip_dtls_setup_values, + srtp_tag_32 yesno_values, + UNIQUE (id) +); + +CREATE INDEX ps_endpoints_id ON ps_endpoints (id); + +CREATE TYPE pjsip_auth_type_values AS ENUM ('md5', 'userpass'); + +CREATE TABLE ps_auths ( + id VARCHAR(40) NOT NULL, + auth_type pjsip_auth_type_values, + nonce_lifetime INTEGER, + md5_cred VARCHAR(40), + password VARCHAR(80), + realm VARCHAR(40), + username VARCHAR(40), + UNIQUE (id) +); + +CREATE INDEX ps_auths_id ON ps_auths (id); + +CREATE TABLE ps_aors ( + id VARCHAR(40) NOT NULL, + contact VARCHAR(40), + default_expiration INTEGER, + mailboxes VARCHAR(80), + max_contacts INTEGER, + minimum_expiration INTEGER, + remove_existing yesno_values, + qualify_frequency INTEGER, + authenticate_qualify yesno_values, + UNIQUE (id) +); + +CREATE INDEX ps_aors_id ON ps_aors (id); + +CREATE TABLE ps_contacts ( + id VARCHAR(40) NOT NULL, + uri VARCHAR(40), + expiration_time VARCHAR(40), + qualify_frequency INTEGER, + UNIQUE (id) +); + +CREATE INDEX ps_contacts_id ON ps_contacts (id); + +CREATE TABLE ps_domain_aliases ( + id VARCHAR(40) NOT NULL, + domain VARCHAR(80), + UNIQUE (id) +); + +CREATE INDEX ps_domain_aliases_id ON ps_domain_aliases (id); + +CREATE TABLE ps_endpoint_id_ips ( + id VARCHAR(40) NOT NULL, + endpoint VARCHAR(40), + match VARCHAR(80), + UNIQUE (id) +); + +CREATE INDEX ps_endpoint_id_ips_id ON ps_endpoint_id_ips (id); + +UPDATE alembic_version SET version_num='43956d550a44' WHERE alembic_version.version_num = '4da0c5f79a9c'; + +-- Running upgrade 43956d550a44 -> 581a4264e537 + +CREATE TABLE extensions ( + id BIGSERIAL NOT NULL, + context VARCHAR(40) NOT NULL, + exten VARCHAR(40) NOT NULL, + priority INTEGER NOT NULL, + app VARCHAR(40) NOT NULL, + appdata VARCHAR(256) NOT NULL, + PRIMARY KEY (id), + UNIQUE (context, exten, priority), + UNIQUE (id) +); + +UPDATE alembic_version SET version_num='581a4264e537' WHERE alembic_version.version_num = '43956d550a44'; + +-- Running upgrade 581a4264e537 -> 2fc7930b41b3 + +CREATE TYPE pjsip_redirect_method_values AS ENUM ('user', 'uri_core', 'uri_pjsip'); + +CREATE TABLE ps_systems ( + id VARCHAR(40) NOT NULL, + timer_t1 INTEGER, + timer_b INTEGER, + compact_headers yesno_values, + threadpool_initial_size INTEGER, + threadpool_auto_increment INTEGER, + threadpool_idle_timeout INTEGER, + threadpool_max_size INTEGER, + UNIQUE (id) +); + +CREATE INDEX ps_systems_id ON ps_systems (id); + +CREATE TABLE ps_globals ( + id VARCHAR(40) NOT NULL, + max_forwards INTEGER, + user_agent VARCHAR(40), + default_outbound_endpoint VARCHAR(40), + UNIQUE (id) +); + +CREATE INDEX ps_globals_id ON ps_globals (id); + +CREATE TYPE pjsip_transport_method_values AS ENUM ('default', 'unspecified', 'tlsv1', 'sslv2', 'sslv3', 'sslv23'); + +CREATE TYPE pjsip_transport_protocol_values AS ENUM ('udp', 'tcp', 'tls', 'ws', 'wss'); + +CREATE TABLE ps_transports ( + id VARCHAR(40) NOT NULL, + async_operations INTEGER, + bind VARCHAR(40), + ca_list_file VARCHAR(200), + cert_file VARCHAR(200), + cipher VARCHAR(200), + domain VARCHAR(40), + external_media_address VARCHAR(40), + external_signaling_address VARCHAR(40), + external_signaling_port INTEGER, + method pjsip_transport_method_values, + local_net VARCHAR(40), + password VARCHAR(40), + priv_key_file VARCHAR(200), + protocol pjsip_transport_protocol_values, + require_client_cert yesno_values, + verify_client yesno_values, + verifiy_server yesno_values, + tos yesno_values, + cos yesno_values, + UNIQUE (id) +); + +CREATE INDEX ps_transports_id ON ps_transports (id); + +CREATE TABLE ps_registrations ( + id VARCHAR(40) NOT NULL, + auth_rejection_permanent yesno_values, + client_uri VARCHAR(40), + contact_user VARCHAR(40), + expiration INTEGER, + max_retries INTEGER, + outbound_auth VARCHAR(40), + outbound_proxy VARCHAR(40), + retry_interval INTEGER, + forbidden_retry_interval INTEGER, + server_uri VARCHAR(40), + transport VARCHAR(40), + support_path yesno_values, + UNIQUE (id) +); + +CREATE INDEX ps_registrations_id ON ps_registrations (id); + +ALTER TABLE ps_endpoints ADD COLUMN media_address VARCHAR(40); + +ALTER TABLE ps_endpoints ADD COLUMN redirect_method pjsip_redirect_method_values; + +ALTER TABLE ps_endpoints ADD COLUMN set_var TEXT; + +ALTER TABLE ps_endpoints RENAME mwi_fromuser TO mwi_from_user; + +ALTER TABLE ps_contacts ADD COLUMN outbound_proxy VARCHAR(40); + +ALTER TABLE ps_contacts ADD COLUMN path TEXT; + +ALTER TABLE ps_aors ADD COLUMN maximum_expiration INTEGER; + +ALTER TABLE ps_aors ADD COLUMN outbound_proxy VARCHAR(40); + +ALTER TABLE ps_aors ADD COLUMN support_path yesno_values; + +UPDATE alembic_version SET version_num='2fc7930b41b3' WHERE alembic_version.version_num = '581a4264e537'; + +-- Running upgrade 2fc7930b41b3 -> 21e526ad3040 + +ALTER TABLE ps_globals ADD COLUMN debug VARCHAR(40); + +UPDATE alembic_version SET version_num='21e526ad3040' WHERE alembic_version.version_num = '2fc7930b41b3'; + +-- Running upgrade 21e526ad3040 -> 28887f25a46f + +CREATE TYPE queue_autopause_values AS ENUM ('yes', 'no', 'all'); + +CREATE TYPE queue_strategy_values AS ENUM ('ringall', 'leastrecent', 'fewestcalls', 'random', 'rrmemory', 'linear', 'wrandom', 'rrordered'); + +CREATE TABLE queues ( + name VARCHAR(128) NOT NULL, + musiconhold VARCHAR(128), + announce VARCHAR(128), + context VARCHAR(128), + timeout INTEGER, + ringinuse yesno_values, + setinterfacevar yesno_values, + setqueuevar yesno_values, + setqueueentryvar yesno_values, + monitor_format VARCHAR(8), + membermacro VARCHAR(512), + membergosub VARCHAR(512), + queue_youarenext VARCHAR(128), + queue_thereare VARCHAR(128), + queue_callswaiting VARCHAR(128), + queue_quantity1 VARCHAR(128), + queue_quantity2 VARCHAR(128), + queue_holdtime VARCHAR(128), + queue_minutes VARCHAR(128), + queue_minute VARCHAR(128), + queue_seconds VARCHAR(128), + queue_thankyou VARCHAR(128), + queue_callerannounce VARCHAR(128), + queue_reporthold VARCHAR(128), + announce_frequency INTEGER, + announce_to_first_user yesno_values, + min_announce_frequency INTEGER, + announce_round_seconds INTEGER, + announce_holdtime VARCHAR(128), + announce_position VARCHAR(128), + announce_position_limit INTEGER, + periodic_announce VARCHAR(50), + periodic_announce_frequency INTEGER, + relative_periodic_announce yesno_values, + random_periodic_announce yesno_values, + retry INTEGER, + wrapuptime INTEGER, + penaltymemberslimit INTEGER, + autofill yesno_values, + monitor_type VARCHAR(128), + autopause queue_autopause_values, + autopausedelay INTEGER, + autopausebusy yesno_values, + autopauseunavail yesno_values, + maxlen INTEGER, + servicelevel INTEGER, + strategy queue_strategy_values, + joinempty VARCHAR(128), + leavewhenempty VARCHAR(128), + reportholdtime yesno_values, + memberdelay INTEGER, + weight INTEGER, + timeoutrestart yesno_values, + defaultrule VARCHAR(128), + timeoutpriority VARCHAR(128), + PRIMARY KEY (name) +); + +CREATE TABLE queue_members ( + queue_name VARCHAR(80) NOT NULL, + interface VARCHAR(80) NOT NULL, + uniqueid VARCHAR(80) NOT NULL, + membername VARCHAR(80), + state_interface VARCHAR(80), + penalty INTEGER, + paused INTEGER, + PRIMARY KEY (queue_name, interface) +); + +UPDATE alembic_version SET version_num='28887f25a46f' WHERE alembic_version.version_num = '21e526ad3040'; + +-- Running upgrade 28887f25a46f -> 4c573e7135bd + +ALTER TABLE ps_endpoints ALTER COLUMN tos_audio TYPE VARCHAR(10); + +ALTER TABLE ps_endpoints ALTER COLUMN tos_video TYPE VARCHAR(10); + +ALTER TABLE ps_endpoints DROP COLUMN cos_audio; + +ALTER TABLE ps_endpoints DROP COLUMN cos_video; + +ALTER TABLE ps_endpoints ADD COLUMN cos_audio INTEGER; + +ALTER TABLE ps_endpoints ADD COLUMN cos_video INTEGER; + +ALTER TABLE ps_transports ALTER COLUMN tos TYPE VARCHAR(10); + +ALTER TABLE ps_transports DROP COLUMN cos; + +ALTER TABLE ps_transports ADD COLUMN cos INTEGER; + +UPDATE alembic_version SET version_num='4c573e7135bd' WHERE alembic_version.version_num = '28887f25a46f'; + +-- Running upgrade 4c573e7135bd -> 3855ee4e5f85 + +ALTER TABLE ps_endpoints ADD COLUMN message_context VARCHAR(40); + +ALTER TABLE ps_contacts ADD COLUMN user_agent VARCHAR(40); + +UPDATE alembic_version SET version_num='3855ee4e5f85' WHERE alembic_version.version_num = '4c573e7135bd'; + +-- Running upgrade 3855ee4e5f85 -> e96a0b8071c + +ALTER TABLE ps_globals ALTER COLUMN user_agent TYPE VARCHAR(255); + +ALTER TABLE ps_contacts ALTER COLUMN id TYPE VARCHAR(255); + +ALTER TABLE ps_contacts ALTER COLUMN uri TYPE VARCHAR(255); + +ALTER TABLE ps_contacts ALTER COLUMN user_agent TYPE VARCHAR(255); + +ALTER TABLE ps_registrations ALTER COLUMN client_uri TYPE VARCHAR(255); + +ALTER TABLE ps_registrations ALTER COLUMN server_uri TYPE VARCHAR(255); + +UPDATE alembic_version SET version_num='e96a0b8071c' WHERE alembic_version.version_num = '3855ee4e5f85'; + +-- Running upgrade e96a0b8071c -> c6d929b23a8 + +CREATE TABLE ps_subscription_persistence ( + id VARCHAR(40) NOT NULL, + packet VARCHAR(2048), + src_name VARCHAR(128), + src_port INTEGER, + transport_key VARCHAR(64), + local_name VARCHAR(128), + local_port INTEGER, + cseq INTEGER, + tag VARCHAR(128), + endpoint VARCHAR(40), + expires INTEGER, + UNIQUE (id) +); + +CREATE INDEX ps_subscription_persistence_id ON ps_subscription_persistence (id); + +UPDATE alembic_version SET version_num='c6d929b23a8' WHERE alembic_version.version_num = 'e96a0b8071c'; + +-- Running upgrade c6d929b23a8 -> 51f8cb66540e + +ALTER TABLE ps_endpoints ADD COLUMN force_avp yesno_values; + +ALTER TABLE ps_endpoints ADD COLUMN media_use_received_transport yesno_values; + +UPDATE alembic_version SET version_num='51f8cb66540e' WHERE alembic_version.version_num = 'c6d929b23a8'; + +-- Running upgrade 51f8cb66540e -> 1d50859ed02e + +ALTER TABLE ps_endpoints ADD COLUMN accountcode VARCHAR(20); + +UPDATE alembic_version SET version_num='1d50859ed02e' WHERE alembic_version.version_num = '51f8cb66540e'; + +-- Running upgrade 1d50859ed02e -> 1758e8bbf6b + +ALTER TABLE sippeers ALTER COLUMN useragent TYPE VARCHAR(255); + +UPDATE alembic_version SET version_num='1758e8bbf6b' WHERE alembic_version.version_num = '1d50859ed02e'; + +-- Running upgrade 1758e8bbf6b -> 5139253c0423 + +ALTER TABLE queue_members DROP COLUMN uniqueid; + +ALTER TABLE queue_members ADD COLUMN uniqueid INTEGER NOT NULL; + +ALTER TABLE queue_members ADD UNIQUE (uniqueid); + +UPDATE alembic_version SET version_num='5139253c0423' WHERE alembic_version.version_num = '1758e8bbf6b'; + +-- Running upgrade 5139253c0423 -> d39508cb8d8 + +CREATE TABLE queue_rules ( + rule_name VARCHAR(80) NOT NULL, + time VARCHAR(32) NOT NULL, + min_penalty VARCHAR(32) NOT NULL, + max_penalty VARCHAR(32) NOT NULL +); + +UPDATE alembic_version SET version_num='d39508cb8d8' WHERE alembic_version.version_num = '5139253c0423'; + +-- Running upgrade d39508cb8d8 -> 5950038a6ead + +ALTER TABLE ps_transports ALTER COLUMN verifiy_server TYPE yesno_values; + +ALTER TABLE ps_transports RENAME verifiy_server TO verify_server; + +UPDATE alembic_version SET version_num='5950038a6ead' WHERE alembic_version.version_num = 'd39508cb8d8'; + +-- Running upgrade 5950038a6ead -> 10aedae86a32 + +CREATE TYPE sip_directmedia_values_v2 AS ENUM ('yes', 'no', 'nonat', 'update', 'outgoing'); + +ALTER TABLE sippeers ALTER COLUMN directmedia TYPE sip_directmedia_values_v2 USING directmedia::text::sip_directmedia_values_v2; + +DROP TYPE sip_directmedia_values; + +UPDATE alembic_version SET version_num='10aedae86a32' WHERE alembic_version.version_num = '5950038a6ead'; + +-- Running upgrade 10aedae86a32 -> eb88a14f2a + +ALTER TABLE ps_endpoints ADD COLUMN media_encryption_optimistic yesno_values; + +UPDATE alembic_version SET version_num='eb88a14f2a' WHERE alembic_version.version_num = '10aedae86a32'; + +-- Running upgrade eb88a14f2a -> 371a3bf4143e + +ALTER TABLE ps_endpoints ADD COLUMN user_eq_phone yesno_values; + +UPDATE alembic_version SET version_num='371a3bf4143e' WHERE alembic_version.version_num = 'eb88a14f2a'; + +-- Running upgrade 371a3bf4143e -> 45e3f47c6c44 + +ALTER TABLE ps_globals ADD COLUMN endpoint_identifier_order VARCHAR(40); + +UPDATE alembic_version SET version_num='45e3f47c6c44' WHERE alembic_version.version_num = '371a3bf4143e'; + +-- Running upgrade 45e3f47c6c44 -> 23530d604b96 + +ALTER TABLE ps_endpoints ADD COLUMN rpid_immediate yesno_values; + +UPDATE alembic_version SET version_num='23530d604b96' WHERE alembic_version.version_num = '45e3f47c6c44'; + +-- Running upgrade 23530d604b96 -> 31cd4f4891ec + +CREATE TYPE pjsip_dtmf_mode_values_v2 AS ENUM ('rfc4733', 'inband', 'info', 'auto'); + +ALTER TABLE ps_endpoints ALTER COLUMN dtmf_mode TYPE pjsip_dtmf_mode_values_v2 USING dtmf_mode::text::pjsip_dtmf_mode_values_v2; + +DROP TYPE pjsip_dtmf_mode_values; + +UPDATE alembic_version SET version_num='31cd4f4891ec' WHERE alembic_version.version_num = '23530d604b96'; + +-- Running upgrade 31cd4f4891ec -> 461d7d691209 + +ALTER TABLE ps_aors ADD COLUMN qualify_timeout INTEGER; + +ALTER TABLE ps_contacts ADD COLUMN qualify_timeout INTEGER; + +UPDATE alembic_version SET version_num='461d7d691209' WHERE alembic_version.version_num = '31cd4f4891ec'; + +-- Running upgrade 461d7d691209 -> a541e0b5e89 + +ALTER TABLE ps_globals ADD COLUMN max_initial_qualify_time INTEGER; + +UPDATE alembic_version SET version_num='a541e0b5e89' WHERE alembic_version.version_num = '461d7d691209'; + +-- Running upgrade a541e0b5e89 -> 28b8e71e541f + +ALTER TABLE ps_endpoints ADD COLUMN g726_non_standard yesno_values; + +UPDATE alembic_version SET version_num='28b8e71e541f' WHERE alembic_version.version_num = 'a541e0b5e89'; + +-- Running upgrade 28b8e71e541f -> 498357a710ae + +ALTER TABLE ps_endpoints ADD COLUMN rtp_keepalive INTEGER; + +UPDATE alembic_version SET version_num='498357a710ae' WHERE alembic_version.version_num = '28b8e71e541f'; + +-- Running upgrade 498357a710ae -> 26f10cadc157 + +ALTER TABLE ps_endpoints ADD COLUMN rtp_timeout INTEGER; + +ALTER TABLE ps_endpoints ADD COLUMN rtp_timeout_hold INTEGER; + +UPDATE alembic_version SET version_num='26f10cadc157' WHERE alembic_version.version_num = '498357a710ae'; + +-- Running upgrade 26f10cadc157 -> 154177371065 + +ALTER TABLE ps_globals ADD COLUMN default_from_user VARCHAR(80); + +UPDATE alembic_version SET version_num='154177371065' WHERE alembic_version.version_num = '26f10cadc157'; + +-- Running upgrade 154177371065 -> 28ce1e718f05 + +ALTER TABLE ps_registrations ADD COLUMN fatal_retry_interval INTEGER; + +UPDATE alembic_version SET version_num='28ce1e718f05' WHERE alembic_version.version_num = '154177371065'; + +-- Running upgrade 28ce1e718f05 -> 189a235b3fd7 + +ALTER TABLE ps_globals ADD COLUMN keep_alive_interval INTEGER; + +UPDATE alembic_version SET version_num='189a235b3fd7' WHERE alembic_version.version_num = '28ce1e718f05'; + +-- Running upgrade 189a235b3fd7 -> 2d078ec071b7 + +ALTER TABLE ps_aors ALTER COLUMN contact TYPE VARCHAR(255); + +UPDATE alembic_version SET version_num='2d078ec071b7' WHERE alembic_version.version_num = '189a235b3fd7'; + +-- Running upgrade 2d078ec071b7 -> 26d7f3bf0fa5 + +ALTER TABLE ps_endpoints ADD COLUMN bind_rtp_to_media_address yesno_values; + +UPDATE alembic_version SET version_num='26d7f3bf0fa5' WHERE alembic_version.version_num = '2d078ec071b7'; + +-- Running upgrade 26d7f3bf0fa5 -> 136885b81223 + +ALTER TABLE ps_globals ADD COLUMN regcontext VARCHAR(80); + +UPDATE alembic_version SET version_num='136885b81223' WHERE alembic_version.version_num = '26d7f3bf0fa5'; + +-- Running upgrade 136885b81223 -> 423f34ad36e2 + +ALTER TABLE ps_aors ALTER COLUMN qualify_timeout TYPE FLOAT; + +ALTER TABLE ps_contacts ALTER COLUMN qualify_timeout TYPE FLOAT; + +UPDATE alembic_version SET version_num='423f34ad36e2' WHERE alembic_version.version_num = '136885b81223'; + +-- Running upgrade 423f34ad36e2 -> dbc44d5a908 + +ALTER TABLE ps_systems ADD COLUMN disable_tcp_switch yesno_values; + +ALTER TABLE ps_registrations ADD COLUMN line yesno_values; + +ALTER TABLE ps_registrations ADD COLUMN endpoint VARCHAR(40); + +UPDATE alembic_version SET version_num='dbc44d5a908' WHERE alembic_version.version_num = '423f34ad36e2'; + +-- Running upgrade dbc44d5a908 -> 3bcc0b5bc2c9 + +ALTER TABLE ps_transports ADD COLUMN allow_reload yesno_values; + +UPDATE alembic_version SET version_num='3bcc0b5bc2c9' WHERE alembic_version.version_num = 'dbc44d5a908'; + +-- Running upgrade 3bcc0b5bc2c9 -> 5813202e92be + +ALTER TABLE ps_globals ADD COLUMN contact_expiration_check_interval INTEGER; + +UPDATE alembic_version SET version_num='5813202e92be' WHERE alembic_version.version_num = '3bcc0b5bc2c9'; + +-- Running upgrade 5813202e92be -> 1c688d9a003c + +ALTER TABLE ps_globals ADD COLUMN default_voicemail_extension VARCHAR(40); + +ALTER TABLE ps_aors ADD COLUMN voicemail_extension VARCHAR(40); + +ALTER TABLE ps_endpoints ADD COLUMN voicemail_extension VARCHAR(40); + +ALTER TABLE ps_endpoints ADD COLUMN mwi_subscribe_replaces_unsolicited INTEGER; + +UPDATE alembic_version SET version_num='1c688d9a003c' WHERE alembic_version.version_num = '5813202e92be'; + +-- Running upgrade 1c688d9a003c -> 8d478ab86e29 + +ALTER TABLE ps_globals ADD COLUMN disable_multi_domain yesno_values; + +UPDATE alembic_version SET version_num='8d478ab86e29' WHERE alembic_version.version_num = '1c688d9a003c'; + +-- Running upgrade 8d478ab86e29 -> 65eb22eb195 + +ALTER TABLE ps_globals ADD COLUMN unidentified_request_count INTEGER; + +ALTER TABLE ps_globals ADD COLUMN unidentified_request_period INTEGER; + +ALTER TABLE ps_globals ADD COLUMN unidentified_request_prune_interval INTEGER; + +ALTER TABLE ps_globals ADD COLUMN default_realm VARCHAR(40); + +UPDATE alembic_version SET version_num='65eb22eb195' WHERE alembic_version.version_num = '8d478ab86e29'; + +-- Running upgrade 65eb22eb195 -> 81b01a191a46 + +ALTER TABLE ps_contacts ADD COLUMN reg_server VARCHAR(20); + +ALTER TABLE ps_contacts ADD CONSTRAINT ps_contacts_uq UNIQUE (id, reg_server); + +UPDATE alembic_version SET version_num='81b01a191a46' WHERE alembic_version.version_num = '65eb22eb195'; + +-- Running upgrade 81b01a191a46 -> 6be31516058d + +ALTER TABLE ps_contacts ADD COLUMN authenticate_qualify yesno_values; + +UPDATE alembic_version SET version_num='6be31516058d' WHERE alembic_version.version_num = '81b01a191a46'; + +-- Running upgrade 6be31516058d -> bca7113d796f + +ALTER TABLE ps_endpoints ADD COLUMN deny VARCHAR(95); + +ALTER TABLE ps_endpoints ADD COLUMN permit VARCHAR(95); + +ALTER TABLE ps_endpoints ADD COLUMN acl VARCHAR(40); + +ALTER TABLE ps_endpoints ADD COLUMN contact_deny VARCHAR(95); + +ALTER TABLE ps_endpoints ADD COLUMN contact_permit VARCHAR(95); + +ALTER TABLE ps_endpoints ADD COLUMN contact_acl VARCHAR(40); + +UPDATE alembic_version SET version_num='bca7113d796f' WHERE alembic_version.version_num = '6be31516058d'; + +-- Running upgrade bca7113d796f -> a845e4d8ade8 + +ALTER TABLE ps_contacts ADD COLUMN via_addr VARCHAR(40); + +ALTER TABLE ps_contacts ADD COLUMN via_port INTEGER; + +ALTER TABLE ps_contacts ADD COLUMN call_id VARCHAR(255); + +UPDATE alembic_version SET version_num='a845e4d8ade8' WHERE alembic_version.version_num = 'bca7113d796f'; + +-- Running upgrade a845e4d8ade8 -> ef7efc2d3964 + +ALTER TABLE ps_contacts ADD COLUMN endpoint VARCHAR(40); + +ALTER TABLE ps_contacts ALTER COLUMN expiration_time TYPE BIGINT USING expiration_time::bigint; + +CREATE INDEX ps_contacts_qualifyfreq_exp ON ps_contacts (qualify_frequency, expiration_time); + +CREATE INDEX ps_aors_qualifyfreq_contact ON ps_aors (qualify_frequency, contact); + +UPDATE alembic_version SET version_num='ef7efc2d3964' WHERE alembic_version.version_num = 'a845e4d8ade8'; + +-- Running upgrade ef7efc2d3964 -> 9deac0ae4717 + +ALTER TABLE ps_endpoints ADD COLUMN subscribe_context VARCHAR(40); + +UPDATE alembic_version SET version_num='9deac0ae4717' WHERE alembic_version.version_num = 'ef7efc2d3964'; + +-- Running upgrade 9deac0ae4717 -> 4a6c67fa9b7a + +ALTER TABLE ps_endpoints ADD COLUMN fax_detect_timeout INTEGER; + +UPDATE alembic_version SET version_num='4a6c67fa9b7a' WHERE alembic_version.version_num = '9deac0ae4717'; + +-- Running upgrade 4a6c67fa9b7a -> c7a44a5a0851 + +ALTER TABLE ps_globals ADD COLUMN mwi_tps_queue_high INTEGER; + +ALTER TABLE ps_globals ADD COLUMN mwi_tps_queue_low INTEGER; + +ALTER TABLE ps_globals ADD COLUMN mwi_disable_initial_unsolicited yesno_values; + +UPDATE alembic_version SET version_num='c7a44a5a0851' WHERE alembic_version.version_num = '4a6c67fa9b7a'; + +-- Running upgrade c7a44a5a0851 -> 3772f8f828da + +ALTER TYPE pjsip_identify_by_values RENAME TO pjsip_identify_by_values_tmp; + +CREATE TYPE pjsip_identify_by_values AS ENUM ('username', 'auth_username'); + +ALTER TABLE ps_endpoints ALTER COLUMN identify_by TYPE pjsip_identify_by_values USING identify_by::text::pjsip_identify_by_values; + +DROP TYPE pjsip_identify_by_values_tmp; + +UPDATE alembic_version SET version_num='3772f8f828da' WHERE alembic_version.version_num = 'c7a44a5a0851'; + +-- Running upgrade 3772f8f828da -> 4e2493ef32e6 + +ALTER TABLE ps_endpoints ADD COLUMN contact_user VARCHAR(80); + +UPDATE alembic_version SET version_num='4e2493ef32e6' WHERE alembic_version.version_num = '3772f8f828da'; + +-- Running upgrade 4e2493ef32e6 -> a6ef36f1309 + +ALTER TABLE ps_globals ADD COLUMN ignore_uri_user_options yesno_values; + +UPDATE alembic_version SET version_num='a6ef36f1309' WHERE alembic_version.version_num = '4e2493ef32e6'; + +-- Running upgrade a6ef36f1309 -> 4468b4a91372 + +ALTER TABLE ps_endpoints ADD COLUMN asymmetric_rtp_codec yesno_values; + +UPDATE alembic_version SET version_num='4468b4a91372' WHERE alembic_version.version_num = 'a6ef36f1309'; + +-- Running upgrade 4468b4a91372 -> 28ab27a7826d + +ALTER TABLE ps_endpoint_id_ips ADD COLUMN srv_lookups yesno_values; + +UPDATE alembic_version SET version_num='28ab27a7826d' WHERE alembic_version.version_num = '4468b4a91372'; + +-- Running upgrade 28ab27a7826d -> 465e70e8c337 + +ALTER TABLE ps_endpoint_id_ips ADD COLUMN match_header VARCHAR(255); + +UPDATE alembic_version SET version_num='465e70e8c337' WHERE alembic_version.version_num = '28ab27a7826d'; + +-- Running upgrade 465e70e8c337 -> 15db7b91a97a + +ALTER TABLE ps_endpoints ADD COLUMN rtcp_mux yesno_values; + +UPDATE alembic_version SET version_num='15db7b91a97a' WHERE alembic_version.version_num = '465e70e8c337'; + +-- Running upgrade 15db7b91a97a -> f638dbe2eb23 + +ALTER TABLE ps_transports ADD COLUMN symmetric_transport yesno_values; + +ALTER TABLE ps_subscription_persistence ADD COLUMN contact_uri VARCHAR(256); + +UPDATE alembic_version SET version_num='f638dbe2eb23' WHERE alembic_version.version_num = '15db7b91a97a'; + +-- Running upgrade f638dbe2eb23 -> 8fce4c573e15 + +ALTER TABLE ps_endpoints ADD COLUMN allow_overlap yesno_values; + +UPDATE alembic_version SET version_num='8fce4c573e15' WHERE alembic_version.version_num = 'f638dbe2eb23'; + +COMMIT; + diff --git a/contrib/realtime/postgresql/postgresql_voicemail.sql b/contrib/realtime/postgresql/postgresql_voicemail.sql new file mode 100644 index 000000000..f9257849c --- /dev/null +++ b/contrib/realtime/postgresql/postgresql_voicemail.sql @@ -0,0 +1,38 @@ +BEGIN; + +CREATE TABLE alembic_version ( + version_num VARCHAR(32) NOT NULL +); + +-- Running upgrade -> a2e9769475e + +CREATE TABLE voicemail_messages ( + dir VARCHAR(255) NOT NULL, + msgnum INTEGER NOT NULL, + context VARCHAR(80), + macrocontext VARCHAR(80), + callerid VARCHAR(80), + origtime INTEGER, + duration INTEGER, + recording BYTEA, + flag VARCHAR(30), + category VARCHAR(30), + mailboxuser VARCHAR(30), + mailboxcontext VARCHAR(30), + msg_id VARCHAR(40) +); + +ALTER TABLE voicemail_messages ADD CONSTRAINT voicemail_messages_dir_msgnum PRIMARY KEY (dir, msgnum); + +CREATE INDEX voicemail_messages_dir ON voicemail_messages (dir); + +INSERT INTO alembic_version (version_num) VALUES ('a2e9769475e'); + +-- Running upgrade a2e9769475e -> 39428242f7f5 + +ALTER TABLE voicemail_messages ALTER COLUMN recording TYPE BYTEA; + +UPDATE alembic_version SET version_num='39428242f7f5' WHERE alembic_version.version_num = 'a2e9769475e'; + +COMMIT; + |