src/billing/cisco_h323_db_schema-postgres.sql

   1 /*
   2  * Id: postgresql.conf,v 1.8.2.11 2003/07/15 11:15:43 pnixon Exp $
   3  *
   4  * --- Peter Nixon [ codemonkey@peternixon.net ]
   5  * This is a custom SQL schema for doing H323 and SIP VoIP accounting with FreeRadius
   6  * and Cisco equipment. It is currently known to work with 3640, 5300 and 5350 series
   7  * as well as CSPS (Cisco SIP Proxy Server).
   8  * It will scale ALOT better than the default radius schema which is designed for
   9  * simple dialup installations of FreeRadius.
  10  *
  11  * For this schema to work properly you MUST replace raddb/postgresql.conf
  12  * with the contents of pgsql-voip.conf
  13  *
  14  * If you wish to do RADIUS Authentication using the same database, you must use
  15  * src/modules/rlm_sql/drivers/rlm_sql_postgresql/db_postgresql.sql as well as
  16  * this schema.
  17  *
  18  */
  19
  20 /*
  21  * Table structure for 'Start' tables
  22  */
  23
  24 CREATE TABLE StartVoIP (
  25         RadAcctId               BIGSERIAL PRIMARY KEY,
  26         h323SetupTime           TIMESTAMP with time zone NOT NULL,
  27         H323ConnectTime         TIMESTAMP with time zone,
  28         UserName                VARCHAR(64),
  29         RadiusServerName        VARCHAR(32),
  30         NASIPAddress            INET NOT NULL,
  31         AcctTime                TIMESTAMP with time zone,
  32         CalledStationId         VARCHAR(80),
  33         CallingStationId        VARCHAR(80),
  34         AcctDelayTime           INTEGER,
  35         H323GWID                VARCHAR(32),
  36         h323CallOrigin          VARCHAR(10),
  37         CallID                  VARCHAR(50) NOT NULL,
  38         processed               BOOLEAN DEFAULT false
  39 );
  40 create index startvoipcombo on startvoip (h323SetupTime, nasipaddress);
  41
  42
  43 CREATE TABLE StartTelephony (
  44         RadAcctId               BIGSERIAL PRIMARY KEY,
  45         h323SetupTime           TIMESTAMP with time zone NOT NULL,
  46         H323ConnectTime         TIMESTAMP with time zone,
  47         UserName                VARCHAR(64),
  48         RadiusServerName        VARCHAR(32),
  49         NASIPAddress            INET NOT NULL,
  50         AcctTime                TIMESTAMP with time zone,
  51         CalledStationId         VARCHAR(80),
  52         CallingStationId        VARCHAR(80),
  53         AcctDelayTime           INTEGER,
  54         H323GWID                VARCHAR(32),
  55         h323CallOrigin          VARCHAR(10),
  56         CallID                  VARCHAR(35) NOT NULL,
  57         processed               BOOLEAN DEFAULT false
  58 );
  59 create index starttelephonycombo on starttelephony (h323SetupTime, nasipaddress);
  60
  61
  62
  63 /*
  64  * Table structure for 'Stop' tables
  65  */
  66 CREATE TABLE StopVoIP (
  67         RadAcctId               BIGSERIAL PRIMARY KEY,
  68         H323SetupTime           TIMESTAMP with time zone,
  69         H323ConnectTime         TIMESTAMP with time zone,
  70         H323DisconnectTime      TIMESTAMP with time zone NOT NULL,
  71         UserName                VARCHAR(32),
  72         RadiusServerName        VARCHAR(32),
  73         NASIPAddress            INET NOT NULL,
  74         AcctTime                TIMESTAMP with time zone,
  75         AcctSessionTime         BIGINT,
  76         AcctInputOctets         BIGINT,
  77         AcctOutputOctets        BIGINT,
  78         CalledStationId         VARCHAR(80),
  79         CallingStationId        VARCHAR(80),
  80         AcctDelayTime           SMALLINT,
  81         CiscoNASPort            VARCHAR(1),
  82         H323GWID                VARCHAR(32),
  83         H323CallOrigin          VARCHAR(10),
  84         H323DisconnectCause     VARCHAR(20),
  85         H323RemoteAddress       INET,
  86         H323VoiceQuality        INTEGER,
  87         CallID                  VARCHAR(50) NOT NULL,
  88         processed               BOOLEAN DEFAULT false
  89 );
  90 create UNIQUE index stopvoipcombo on stopvoip (h323SetupTime, nasipaddress, CallID);
  91 /*
  92  * Some Cisco CSPS do not have complete VSA details. If you have one of these you will want
  93  * to use the following index, as the one above will drop records.
  94  *
  95  *  create UNIQUE index stopvoipcombo on stopvoip (h323DisconnectTime, nasipaddress, CallID);
  96  *
  97  */
  98
  99
 100 CREATE TABLE StopTelephony (
 101         RadAcctId               BIGSERIAL PRIMARY KEY,
 102         H323SetupTime           TIMESTAMP with time zone NOT NULL,
 103         H323ConnectTime         TIMESTAMP with time zone NOT NULL,
 104         H323DisconnectTime      TIMESTAMP with time zone NOT NULL,
 105         UserName                VARCHAR(32) DEFAULT '' NOT NULL,
 106         RadiusServerName        VARCHAR(32),
 107         NASIPAddress            INET NOT NULL,
 108         AcctTime                TIMESTAMP with time zone,
 109         AcctSessionTime         BIGINT,
 110         AcctInputOctets         BIGINT,
 111         AcctOutputOctets        BIGINT,
 112         CalledStationId         VARCHAR(80),
 113         CallingStationId        VARCHAR(80),
 114         AcctDelayTime           SMALLINT,
 115         CiscoNASPort            VARCHAR(16),
 116         H323GWID                VARCHAR(32),
 117         H323CallOrigin          VARCHAR(10),
 118         H323DisconnectCause     VARCHAR(20),
 119         H323RemoteAddress       INET,
 120         H323VoiceQuality        INTEGER,
 121         CallID                  VARCHAR(35) NOT NULL,
 122         processed               BOOLEAN DEFAULT false
 123 );
 124 -- You can have more than one record that is identical except for CiscoNASPort if you have a VoIP dial peer
 125 -- configured for multiple PRIs.
 126 create UNIQUE index stoptelephonycombo on stoptelephony (h323SetupTime, nasipaddress, CallID, CiscoNASPort);
 127
 128 /*
 129  * Table structure for 'gateways'
 130  *
 131  * This table should list the IP addresses, names and locations of all your gateways
 132  * This can be used to make more useful reports.
 133  *
 134  * Note: This table should be removed in favour of using the "nas" table.
 135  */
 136
 137 CREATE TABLE gateways (
 138         gw_ip           INET NOT NULL,
 139         gw_name         VARCHAR(32) NOT NULL,
 140         gw_city         VARCHAR(32)
 141 );
 142
 143
 144 /*
 145  * Table structure for 'customers'
 146  *
 147  * This table should list your Customers names and company
 148  * This can be used to make more useful reports.
 149  */
 150
 151 CREATE TABLE customers (
 152         cust_id         SERIAL NOT NULL,
 153         company         VARCHAR(32),
 154         customer        VARCHAR(32)
 155 );
 156
 157 /*
 158  * Table structure for 'cust_gw'
 159  *
 160  * This table should list the IP addresses and Customer IDs of all your Customers gateways
 161  * This can be used to make more useful reports.
 162  */
 163
 164 CREATE TABLE cust_gw (
 165         cust_gw         INET PRIMARY KEY,
 166         cust_id         INTEGER NOT NULL,
 167         "location"      VARCHAR(32)
 168 );
 169
 170
 171 CREATE VIEW customerip AS
 172     SELECT gw.cust_gw AS ipaddr, cust.company, cust.customer, gw."location" FROM customers cust, cust_gw gw WHERE (cust.cust_id = gw.cust_id);
 173
 174
 175 /*
 176  * Function 'strip_dot'
 177  * removes "." from the start of cisco timestamps
 178  *
 179  * From the cisco website:
 180  * "A timestamp that is preceded by an asterisk (*) or a dot (.) may not be accurate.
 181  *  An asterisk (*) means that after a gateway reboot, the gateway clock was not manually set
 182  *  and the gateway has not synchronized with an NTP server yet. A dot (.) means the gateway
 183  *  NTP has lost synchronization with an NTP server."
 184  *
 185  * We therefore do not bother to strip asterisks (*) from timestamps, as you NEED ntp setup
 186  * unless you don't care about billing at all!
 187  *
 188  *  * Example useage:
 189  *      insert into mytable values (strip_dot('.16:46:02.356 EET Wed Dec 11 2002'));
 190  *
 191  */
 192
 193
 194 CREATE OR REPLACE FUNCTION strip_dot (VARCHAR) RETURNS TIMESTAMPTZ AS '
 195  DECLARE
 196         original_timestamp ALIAS FOR $1;
 197  BEGIN
 198         IF original_timestamp = '''' THEN
 199                 RETURN NULL;
 200         END IF;
 201         IF substring(original_timestamp from 1 for 1) = ''.'' THEN
 202                 RETURN substring(original_timestamp from 2);
 203         ELSE
 204                 RETURN original_timestamp;
 205         END IF;
 206  END;
 207 ' LANGUAGE 'plpgsql';
 208
 209
 210 CREATE OR REPLACE FUNCTION pick_id (VARCHAR, VARCHAR) RETURNS VARCHAR AS '
 211  DECLARE
 212         h323confid ALIAS FOR $1;
 213         callid ALIAS FOR $2;
 214  BEGIN
 215         IF h323confid <> '''' THEN
 216                 RETURN h323confid;
 217         END IF;
 218         IF callid <> '''' THEN
 219                 RETURN callid;
 220         END IF;
 221         RETURN NULL;
 222  END;
 223 ' LANGUAGE 'plpgsql';
 224
 225
 226
 227 /*
 228  * Table structure for 'isdn_error_codes' table
 229  *
 230  * Taken from cisco.com this data can be JOINED against h323DisconnectCause to
 231  * give human readable error reports.
 232  *
 233  */
 234
 235
 236 CREATE TABLE isdn_error_codes (
 237         error_code      VARCHAR(2) PRIMARY KEY,
 238         desc_short      VARCHAR(90),
 239         desc_long       TEXT
 240 );
 241
 242 /*
 243  * Data for 'isdn_error_codes' table
 244  */
 245
 246 INSERT INTO isdn_error_codes VALUES ('1', 'Unallocated (unassigned) number', 'The ISDN number was sent to the switch in the correct format; however, the number is not assigned to any destination equipment.');
 247 INSERT INTO isdn_error_codes VALUES ('10', 'Normal call clearing', 'Normal call clearing has occurred.');
 248 INSERT INTO isdn_error_codes VALUES ('11', 'User busy', 'The called system acknowledges the connection request but is unable to accept the call because all B channels are in use.');
 249 INSERT INTO isdn_error_codes VALUES ('12', 'No user responding', 'The connection cannot be completed because the destination does not respond to the call.');
 250 INSERT INTO isdn_error_codes VALUES ('13', 'No answer from user (user alerted)', 'The destination responds to the connection request but fails to complete the connection within the prescribed time. The problem is at the remote end of the connection.');
 251 INSERT INTO isdn_error_codes VALUES ('15', 'Call rejected', 'The destination is capable of accepting the call but rejected the call for an unknown reason.');
 252 INSERT INTO isdn_error_codes VALUES ('16', 'Number changed', 'The ISDN number used to set up the call is not assigned to any system.');
 253 INSERT INTO isdn_error_codes VALUES ('1A', 'Non-selected user clearing', 'The destination is capable of accepting the call but rejected the call because it was not assigned to the user.');
 254 INSERT INTO isdn_error_codes VALUES ('1B', 'Designation out of order', 'The destination cannot be reached because the interface is not functioning correctly, and a signaling message cannot be delivered. This might be a temporary condition, but it could last for an extended period of time. For example, the remote equipment might be turned off.');
 255 INSERT INTO isdn_error_codes VALUES ('1C', 'Invalid number format', 'The connection could be established because the destination address was presented in an unrecognizable format or because the destination address was incomplete.');
 256 INSERT INTO isdn_error_codes VALUES ('1D', 'Facility rejected', 'The facility requested by the user cannot be provided by the network.');
 257 INSERT INTO isdn_error_codes VALUES ('1E', 'Response to STATUS ENQUIRY', 'The status message was generated in direct response to the prior receipt of a status enquiry message.');
 258 INSERT INTO isdn_error_codes VALUES ('1F', 'Normal, unspecified', 'Reports the occurrence of a normal event when no standard cause applies. No action required.');
 259 INSERT INTO isdn_error_codes VALUES ('2', 'No route to specified transit network', 'The ISDN exchange is asked to route the call through an unrecognized intermediate network.');
 260 INSERT INTO isdn_error_codes VALUES ('22', 'No circuit/channel available', 'The connection cannot be established because no appropriate channel is available to take the call.');
 261 INSERT INTO isdn_error_codes VALUES ('26', 'Network out of order', 'The destination cannot be reached because the network is not functioning correctly, and the condition might last for an extended period of time. An immediate reconnect attempt will probably be unsuccessful.');
 262 INSERT INTO isdn_error_codes VALUES ('29', 'Temporary failure', 'An error occurred because the network is not functioning correctly. The problem will be resolved shortly.');
 263 INSERT INTO isdn_error_codes VALUES ('2A', 'Switching equipment congestion', 'The destination cannot be reached because the network switching equipment is temporarily overloaded.');
 264 INSERT INTO isdn_error_codes VALUES ('2B', 'Access information discarded', 'The network cannot provide the requested access information.');
 265 INSERT INTO isdn_error_codes VALUES ('2C', 'Requested circuit/channel not available', 'The remote equipment cannot provide the requested channel for an unknown reason. This might be a temporary problem.');
 266 INSERT INTO isdn_error_codes VALUES ('2F', 'Resources unavailable, unspecified', 'The requested channel or service is unavailable for an unknown reason. This might be a temporary problem.');
 267 INSERT INTO isdn_error_codes VALUES ('3', 'No route to destination', 'The call was routed through an intermediate network that does not serve the destination address.');
 268 INSERT INTO isdn_error_codes VALUES ('31', 'Quality of service unavailable', 'The requested quality of service cannot be provided by the network. This might be a subscription problem.');
 269 INSERT INTO isdn_error_codes VALUES ('32', 'Requested facility not subscribed', 'The remote equipment supports the requested supplementary service by subscription only.');
 270 INSERT INTO isdn_error_codes VALUES ('39', 'Bearer capability not authorized', 'The user requested a bearer capability that the network provides, but the user is not authorized to use it. This might be a subscription problem.');
 271 INSERT INTO isdn_error_codes VALUES ('3A', 'Bearer capability not presently available', 'The network normally provides the requested bearer capability, but it is unavailable at the present time. This might be due to a temporary network problem or to a subscription problem.');
 272 INSERT INTO isdn_error_codes VALUES ('3F', 'Service or option not available, unspecified', 'The network or remote equipment was unable to provide the requested service option for an unspecified reason. This might be a subscription problem.');
 273 INSERT INTO isdn_error_codes VALUES ('41', 'Bearer capability not implemented', 'The network cannot provide the bearer capability requested by the user.');
 274 INSERT INTO isdn_error_codes VALUES ('42', 'Channel type not implemented', 'The network or the destination equipment does not support the requested channel type.');
 275 INSERT INTO isdn_error_codes VALUES ('45', 'Requested facility not implemented', 'The remote equipment does not support the requested supplementary service.');
 276 INSERT INTO isdn_error_codes VALUES ('46', 'Only restricted digital information bearer capability is available', 'The network is unable to provide unrestricted digital information bearer capability.');
 277 INSERT INTO isdn_error_codes VALUES ('4F', 'Service or option not implemented, unspecified', 'The network or remote equipment is unable to provide the requested service option for an unspecified reason. This might be a subscription problem.');
 278 INSERT INTO isdn_error_codes VALUES ('51', 'Invalid call reference value', 'The remote equipment received a call with a call reference that is not currently in use on the user-network interface.');
 279 INSERT INTO isdn_error_codes VALUES ('52', 'Identified channel does not exist', 'The receiving equipment is requested to use a channel that is not activated on the interface for calls.');
 280 INSERT INTO isdn_error_codes VALUES ('53', 'A suspended call exists, but this call identity does not', 'The network received a call resume request. The call resume request contained a Call Identify information element that indicates that the call identity is being used for a suspended call.');
 281 INSERT INTO isdn_error_codes VALUES ('54', 'Call identity in use', 'The network received a call resume request. The call resume request contained a Call Identify information element that indicates that it is in use for a suspended call.');
 282 INSERT INTO isdn_error_codes VALUES ('55', 'No call suspended', 'The network received a call resume request when there was not a suspended call pending. This might be a transient error that will be resolved by successive call retries.');
 283 INSERT INTO isdn_error_codes VALUES ('56', 'Call having the requested call identity has been cleared', 'The network received a call resume request. The call resume request contained a Call Identity information element, which once indicated a suspended call. However, the suspended call was cleared either by timeout or by the remote user.');
 284 INSERT INTO isdn_error_codes VALUES ('58', 'Incompatible destination', 'Indicates that an attempt was made to connect to non-ISDN equipment. For example, to an analog line.');
 285 INSERT INTO isdn_error_codes VALUES ('5B', 'Invalid transit network selection', 'The ISDN exchange was asked to route the call through an unrecognized intermediate network.');
 286 INSERT INTO isdn_error_codes VALUES ('5F', 'Invalid message, unspecified', 'An invalid message was received, and no standard cause applies. This is usually due to a D-channel error. If this error occurs systematically, report it to your ISDN service provider.');
 287 INSERT INTO isdn_error_codes VALUES ('6', 'Channel unacceptable', 'The service quality of the specified channel is insufficient to accept the connection.');
 288 INSERT INTO isdn_error_codes VALUES ('60', 'Mandatory information element is missing', 'The receiving equipment received a message that did not include one of the mandatory information elements. This is usually due to a D-channel error. If this error occurs systematically, report it to your ISDN service provider.');
 289 INSERT INTO isdn_error_codes VALUES ('61', 'Message type non-existent or not implemented', 'The receiving equipment received an unrecognized message, either because the message type was invalid or because the message type was valid but not supported. The cause is due to either a problem with the remote configuration or a problem with the local D channel.');
 290 INSERT INTO isdn_error_codes VALUES ('62', 'Message not compatible with call state or message type non-existent or not implemented', 'The remote equipment received an invalid message, and no standard cause applies. This cause is due to a D-channel error. If this error occurs systematically, report it to your ISDN service provider.');
 291 INSERT INTO isdn_error_codes VALUES ('63', 'Information element non-existent or not implemented', 'The remote equipment received a message that includes information elements, which were not recognized. This is usually due to a D-channel error. If this error occurs systematically, report it to your ISDN service provider.');
 292 INSERT INTO isdn_error_codes VALUES ('64', 'Invalid information element contents', 'The remote equipment received a message that includes invalid information in the information element. This is usually due to a D-channel error.');
 293 INSERT INTO isdn_error_codes VALUES ('65', 'Message not compatible with call state', 'The remote equipment received an unexpected message that does not correspond to the current state of the connection. This is usually due to a D-channel error.');
 294 INSERT INTO isdn_error_codes VALUES ('66', 'Recovery on timer expires', 'An error-handling (recovery) procedure was initiated by a timer expiry. This is usually a temporary problem.');
 295 INSERT INTO isdn_error_codes VALUES ('6F', 'Protocol error, unspecified', 'An unspecified D-channel error when no other standard cause applies.');
 296 INSERT INTO isdn_error_codes VALUES ('7', 'Call awarded and being delivered in an established channel', 'The user is assigned an incoming call that is being connected to an already-established call channel.');
 297 INSERT INTO isdn_error_codes VALUES ('7F', 'Internetworking, unspecified', 'An event occurred, but the network does not provide causes for the action that it takes. The precise problem is unknown.');
 298