cisco_h323_db_schema-postgres.sql

radius server在linux下的源码

/*
 * Id: postgresql.conf,v 1.8.2.11 2003/07/15 11:15:43 pnixon Exp $
 *
 * --- Peter Nixon [ [e-mail] ]
 * This is a custom SQL schema for doing H323 VoIP accounting with FreeRadius and
 * Cisco gateways (I am using 5300 and 5350 series).
 * It will scale ALOT better than the default radius schema which is designed for
 * simple dialup installations of FreeRadius.
 *
 * For this schema to work properly you MUST replace raddb/postgresql.conf
 * with the contents of pgsql-voip.conf
 *
 * If you wish to do RADIUS Authentication using the same database, you must use
 * src/modules/rlm_sql/drivers/rlm_sql_postgresql/db_postgresql.sql as well as
 * this schema.
 *
 */

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

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


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



/*
 * Table structure for 'Stop' tables
 */
CREATE TABLE StopVoIP (
	RadAcctId		BIGSERIAL PRIMARY KEY,
	H323SetupTime		TIMESTAMP with time zone,
	H323ConnectTime		TIMESTAMP with time zone,
	H323DisconnectTime	TIMESTAMP with time zone NOT NULL,
	UserName		VARCHAR(32),
	RadiusServerName	VARCHAR(32),
	NASIPAddress		INET NOT NULL,
	AcctTime		TIMESTAMP with time zone,
	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(50) NOT NULL,
	processed		BOOLEAN DEFAULT false
);
create UNIQUE index stopvoipcombo on stopvoip (h323SetupTime, nasipaddress, CallID);
/*
 * Some Cisco CSPS do not have complete VSA details. If you have one of these you will want
 * to use the following index, as the one above will drop records.
 * 
 *  create UNIQUE index stopvoipcombo on stopvoip (h323DisconnectTime, nasipaddress, CallID);
 *
 */


CREATE TABLE StopTelephony (
	RadAcctId		BIGSERIAL PRIMARY KEY,
	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,
	AcctTime		TIMESTAMP with time zone,
	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(35) NOT NULL,
	processed		BOOLEAN DEFAULT false
);
-- You can have more than one record that is identical except for CiscoNASPort if you have a VoIP dial peer
-- configured for multiple PRIs.
create UNIQUE index stoptelephonycombo on stoptelephony (h323SetupTime, 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.
 */