cisco_h323_db_schema.sql

freeradius-server-2.1.3.tar.gz安装源文件

/*
 * $Id$
 *
 * --- Peter Nixon [ [e-mail] ]
 *
 * This is a custom SQL schema for doing H323 and SIP VoIP accounting
 * with FreeRadius and Cisco equipment. It is currently known to work
 * with 3640, 5300 and 5350 series as well as CSPS (Cisco SIP Proxy
 * Server).  It will scale A LOT better than the default radius schema
 * which is designed for simple dialup installations of FreeRadius.
 *
 * For this schema to work properly you MUST use
 * raddb/sql/postgresql/voip-postpaid.conf rather than
 * raddb/sql/postgresql/dialup.conf
 *
 * If you wish to do RADIUS Authentication using the same database,
 * you MUST use use raddb/sql/postgresql/schema.sql as well as this schema.
 */

/*
 * Table structure for 'Start' tables
 */

CREATE TABLE StartVoIP (
	RadAcctId		BIGSERIAL PRIMARY KEY,
	AcctTime		TIMESTAMP with time zone NOT NULL,
	h323SetupTime		TIMESTAMP with time zone,
	H323ConnectTime		TIMESTAMP with time zone,
	UserName		VARCHAR(64),
	RadiusServerName	VARCHAR(32),
	NASIPAddress		INET NOT NULL,
	CalledStationId		VARCHAR(80),
	CallingStationId	VARCHAR(80),
	AcctDelayTime		INTEGER,
	H323GWID		VARCHAR(32),
	h323CallOrigin		VARCHAR(10),
	CallID			VARCHAR(80) NOT NULL,
	processed		BOOLEAN DEFAULT false
);
create index startvoipcombo on startvoip (AcctTime, nasipaddress);


CREATE TABLE StartTelephony (
	RadAcctId		BIGSERIAL PRIMARY KEY,
	AcctTime		TIMESTAMP with time zone NOT NULL,
	h323SetupTime		TIMESTAMP with time zone,
	H323ConnectTime		TIMESTAMP with time zone,
	UserName		VARCHAR(64),
	RadiusServerName	VARCHAR(32),
	NASIPAddress		INET NOT NULL,
	CalledStationId		VARCHAR(80),
	CallingStationId	VARCHAR(80),
	AcctDelayTime		INTEGER,
	H323GWID		VARCHAR(32),
	h323CallOrigin		VARCHAR(10),
	CallID			VARCHAR(80) NOT NULL,
	processed		BOOLEAN DEFAULT false
);
create index starttelephonycombo on starttelephony (AcctTime, nasipaddress);



/*
 * Table structure for 'Stop' tables
 */
CREATE TABLE StopVoIP (
	RadAcctId		BIGSERIAL PRIMARY KEY,
	AcctTime		TIMESTAMP with time zone NOT NULL,
	H323SetupTime		TIMESTAMP with time zone,
	H323ConnectTime		TIMESTAMP with time zone,
	H323DisconnectTime	TIMESTAMP with time zone,
	UserName		VARCHAR(32),
	RadiusServerName	VARCHAR(32),
	NASIPAddress		INET NOT NULL,
	AcctSessionTime		BIGINT,
	AcctInputOctets		BIGINT,
	AcctOutputOctets	BIGINT,
	CalledStationId		VARCHAR(80),
	CallingStationId	VARCHAR(80),
	AcctDelayTime		SMALLINT,
	CiscoNASPort		VARCHAR(1),
	H323GWID		VARCHAR(32),
	H323CallOrigin		VARCHAR(10),
	H323DisconnectCause	VARCHAR(20),
	H323RemoteAddress	INET,
	H323VoiceQuality	INTEGER,
	CallID			VARCHAR(80) NOT NULL,
	processed		BOOLEAN DEFAULT false
);
create UNIQUE index stopvoipcombo on stopvoip (AcctTime, nasipaddress, CallID);


CREATE TABLE StopTelephony (
	RadAcctId		BIGSERIAL PRIMARY KEY,
	AcctTime		TIMESTAMP with time zone NOT NULL,
	H323SetupTime		TIMESTAMP with time zone NOT NULL,
	H323ConnectTime		TIMESTAMP with time zone NOT NULL,
	H323DisconnectTime	TIMESTAMP with time zone NOT NULL,
	UserName		VARCHAR(32) DEFAULT '' NOT NULL,
	RadiusServerName	VARCHAR(32),
	NASIPAddress		INET NOT NULL,
	AcctSessionTime		BIGINT,
	AcctInputOctets		BIGINT,
	AcctOutputOctets	BIGINT,
	CalledStationId		VARCHAR(80),
	CallingStationId	VARCHAR(80),
	AcctDelayTime		SMALLINT,
	CiscoNASPort		VARCHAR(16),
	H323GWID		VARCHAR(32),
	H323CallOrigin		VARCHAR(10),
	H323DisconnectCause	VARCHAR(20),
	H323RemoteAddress	INET,
	H323VoiceQuality	INTEGER,
	CallID			VARCHAR(80) NOT NULL,
	processed		BOOLEAN DEFAULT false
);
-- You can have more than one record that is identical except for CiscoNASPort if you have a dial peer hungroup
-- configured for multiple PRIs.
create UNIQUE index stoptelephonycombo on stoptelephony (AcctTime, nasipaddress, CallID, CiscoNASPort);

/*
 * Table structure for 'gateways'
 *
 * This table should list the IP addresses, names and locations of all your gateways
 * This can be used to make more useful reports.
 *
 * Note: This table should be removed in favour of using the "nas" table.
 */

CREATE TABLE gateways (
	gw_ip		INET NOT NULL,
	gw_name		VARCHAR(32) NOT NULL,
	gw_city		VARCHAR(32)
);


/*
 * Table structure for 'customers'
 *
 * This table should list your Customers names and company
 * This can be used to make more useful reports.
 */

CREATE TABLE customers (
	cust_id		SERIAL NOT NULL,
	company		VARCHAR(32),
	customer	VARCHAR(32)
);