From 362cd74e254a5fb8e2665d58188e8a8f635511d9 Mon Sep 17 00:00:00 2001 From: Peter Kozak Date: Thu, 14 Mar 2013 06:49:48 -0400 Subject: calls_active view --- db/migrate/20130314104000_change_calls_active.rb | 84 ++++++++++++++++++++++++ 1 file changed, 84 insertions(+) create mode 100644 db/migrate/20130314104000_change_calls_active.rb (limited to 'db') diff --git a/db/migrate/20130314104000_change_calls_active.rb b/db/migrate/20130314104000_change_calls_active.rb new file mode 100644 index 0000000..4054157 --- /dev/null +++ b/db/migrate/20130314104000_change_calls_active.rb @@ -0,0 +1,84 @@ +class ChangeCallsActive < ActiveRecord::Migration + def self.up + execute "DROP VIEW IF EXISTS calls_active" + if ActiveRecord::Base.connection_config[:adapter] != 'sqlite3' + execute <<-SQL + CREATE VIEW calls_active AS SELECT + a.uuid AS uuid, + a.direction AS direction, + a.created_epoch AS start_stamp, + a.cid_name AS caller_id_name, + a.cid_num AS caller_id_number, + a.dest AS destination, + d.id AS sip_account_id, + d.caller_name AS sip_caller_name, + a.callee_name AS callee_name, + a.callee_num AS callee_number, + a.callstate AS callstate, + a.read_codec AS read_codec, + a.read_rate AS read_rate, + a.read_bit_rate AS read_bit_rate, + a.write_codec AS write_codec, + a.write_rate AS write_rate, + a.write_bit_rate AS write_bit_rate, + a.secure AS secure, + b.uuid AS b_uuid, + b.cid_name AS b_caller_id_name, + b.cid_num AS b_caller_id_number, + b.callstate AS b_callstate, + e.id AS b_sip_account_id, + e.caller_name AS b_sip_caller_name, + b.callee_name AS b_callee_name, + b.callee_num AS b_callee_number, + b.secure AS b_secure + FROM channels a + LEFT JOIN calls c ON a.uuid = c.caller_uuid AND a.hostname = c.hostname + LEFT JOIN channels b ON b.uuid = c.callee_uuid AND b.hostname = c.hostname + LEFT JOIN sip_accounts d ON a.presence_id LIKE CONCAT(d.auth_name, "@%") + LEFT JOIN sip_accounts e ON b.presence_id LIKE CONCAT(e.auth_name, "@%") + WHERE a.uuid = c.caller_uuid OR a.uuid NOT IN (select callee_uuid from calls) + SQL + else + execute <<-SQL + CREATE VIEW calls_active AS SELECT + a.uuid AS uuid, + a.direction AS direction, + a.created_epoch AS start_stamp, + a.cid_name AS caller_id_name, + a.cid_num AS caller_id_number, + a.dest AS destination, + d.id AS sip_account_id, + d.caller_name AS sip_caller_name, + a.callee_name AS callee_name, + a.callee_num AS callee_number, + a.callstate AS callstate, + a.read_codec AS read_codec, + a.read_rate AS read_rate, + a.read_bit_rate AS read_bit_rate, + a.write_codec AS write_codec, + a.write_rate AS write_rate, + a.write_bit_rate AS write_bit_rate, + a.secure AS secure, + b.uuid AS b_uuid, + b.cid_name AS b_caller_id_name, + b.cid_num AS b_caller_id_number, + b.callstate AS b_callstate, + e.id AS b_sip_account_id, + e.caller_name AS b_sip_caller_name, + b.callee_name AS b_callee_name, + b.callee_num AS b_callee_number, + b.secure AS b_secure + FROM channels a + LEFT JOIN calls c ON a.uuid = c.caller_uuid AND a.hostname = c.hostname + LEFT JOIN channels b ON b.uuid = c.callee_uuid AND b.hostname = c.hostname + LEFT JOIN sip_accounts d ON a.presence_id LIKE (d.auth_name || "@%") + LEFT JOIN sip_accounts e ON b.presence_id LIKE (e.auth_name || "@%") + WHERE a.uuid = c.caller_uuid OR a.uuid NOT IN (select callee_uuid from calls) + SQL + end + end + + def self.down + + end +end \ No newline at end of file -- cgit v1.2.3