# mysqlCreate.sql
# Create the simplest MYSQL database suitable for use by AuthSQL
# In a real system, you will probably want much more than the
# minimum set of columns for each table.
# There is also an example RADONLINE table that will work with SessSQL
# and its default parameters
# 
# $Id: mysqlCreate.sql,v 1.14 2003/03/13 08:36:36 mikem Exp $

# Vape any old versions


# You must have at least a USERNAME and PASSWORD column.
# You can have ENCRYPTEDPASSWORD if you define EncryptedPasswordColumn
# in the config file.
# You can have REPLYATTR if you define ReplyAttrColumn in the config file
# You can have CHECKATTR if you define CheckAttrColumn in the config file
# The exact names of the columns and this table can be changed with the
# config file, but the names given here are the defaults.
create table SUBSCRIBERS (
	USERNAME	char(50) NOT NULL,	# Users login name, including realm
	PASSWORD	char(50),	# Cleartext password
	ENCRYPTEDPASSWORD char(50),	# Optional encrypted password
	CHECKATTR	char(200),	# Optional check radius attributes
	REPLYATTR	char(200),	# Optional reply radius attributes
	TIMELEFT	int,		# Seconds left (see prepaid.cfg)
	UNIQUE 		USERNAME_I (USERNAME)
);

# You must have at least username and timestamp
# You can add more feilds to the database, but you will also
# have to change AcctColumnDef to make sure they get inserted
create table ACCOUNTING (
	USERNAME	char(50) NOT NULL, # From User-Name 
	TIME_STAMP	int,      # Time this was received (since 1970)
	ACCTSTATUSTYPE	char(10),
	ACCTDELAYTIME	int,
	ACCTINPUTOCTETS	int,
	ACCTOUTPUTOCTETS int,
	ACCTSESSIONID	char(30),
	ACCTSESSIONTIME	int,	  # Length of the session in secs
	ACCTTERMINATECAUSE int,
	NASIDENTIFIER	char(50),
	NASPORT		int,
	FRAMEDIPADDRESS	char(22),
	INDEX ACCOUNTING_I (USERNAME)
);

# This is a sample user that is used by test.pl during testing
insert into SUBSCRIBERS (
	USERNAME, 
	PASSWORD, 
	ENCRYPTEDPASSWORD,
	CHECKATTR,
	REPLYATTR,
	TIMELEFT
	) 
	values (
	'mikem', 
	'fred', 
	'1xMKc0GIVUNbE',
	'Service-Type = Framed-User',
	'Framed-Protocol = PPP,Framed-IP-Netmask = 255.255.255.0,cisco-avpair = "testing testing"',
	100
	);
	
# An entry for each user _currently_ on line, for use by
# <SessionDatabase SQL>
# You can add more fields to this database, but you will also
# need to adjust AddQuery to store the additional values.
# You _must_ have at least 
# USERNAME, NASIDENTIFIER, NASPORT and ACCTSESSIONID, which
# is the unique key in this table.
create table RADONLINE (
	USERNAME	char(50) NOT NULL,
	NASIDENTIFIER	char(50) NOT NULL,
	NASPORT		int NOT NULL,
	ACCTSESSIONID	char(30) NOT NULL,
	TIME_STAMP	int,
	FRAMEDIPADDRESS	char(22),
	NASPORTTYPE	char(10),
	SERVICETYPE	char(20),

	UNIQUE RADONLINE_I (NASIDENTIFIER, NASPORT),
	INDEX RADONLINE_I2 (USERNAME)
);

# An entry for each allocatable address for AllocateAddress SQL
# STATE: 0=free, 1=allocated
# TIME_STAMP: last time it changed state
# YIADDR: the IP address to be allocated
create table RADPOOL (
	STATE		int NOT NULL,
	TIME_STAMP	int,
	EXPIRY		int,
	USERNAME	char(50),
	POOL		char(50) NOT NULL,
	YIADDR		char(50) NOT NULL,
	SUBNETMASK	char(50) NOT NULL,
	DNSSERVER	char(50),

	UNIQUE RADPOOL_I (YIADDR),
	INDEX RADPOOL_I2 (POOL)
);

# A table for storing log messages with LogSQL
# These are the minimum requirements
create table RADLOG (
	TIME_STAMP	int,
	PRIORITY	int,
	MESSAGE		char(200)
);

# A table for storing Client definitions
# This table is accessed by ClientListSQL at
# startup to initialise a list of Clients
create table RADCLIENTLIST (
	NASIDENTIFIER			char(50) NOT NULL,
	SECRET				char(50) NOT NULL,
	IGNOREACCTSIGNATURE		int,
	DUPINTERVAL			int,
	DEFAULTREALM			char(50),
	NASTYPE				char(20),
	SNMPCOMMUNITY			char(20),
	LIVINGSTONOFFS			int,
	LIVINGSTONHOLE			int,
	FRAMEDGROUPBASEADDRESS		char(50),
	FRAMEDGROUPMAXPORTSPERCLASSC	int,
	REWRITEUSERNAME			char(50),
	NOIGNOREDUPLICATES		char(50),
	PREHANDLERHOOK			char(50),
	UNIQUE 				NASIDENTIFIER_I (NASIDENTIFIER)
);	

# This is a sample Client that is used for testing
insert into RADCLIENTLIST (
	NASIDENTIFIER, 
	SECRET, 
	DUPINTERVAL
	) 
	values (
	'203.63.154.1', 
	'mysecret', 
	0
	);
	
# An example table for AuthBy SQLRADIUS
# Contains an entry for each realm to be proxied, along with
# target server information. For a simple system, set the TARGETNAME
# to be the Relam to be proxied, and use the defualt HostSelect
# in AuthBy SQLRADIUS. For more complicated systems, see below.
# FAILUREPOLICY determines what to do if the request cant be forwarded
# can be one of the return codes documented in AuthGeneric.pm, ie
# 0 -> ACCEPT, 1 -> REJECT, 2 -> IGNORE. NULL will default to IGNORE
create table RADSQLRADIUS (
       TARGETNAME			char(50),
       HOST1				char(50),
       HOST2				char(50),
       SECRET				char(50),
       AUTHPORT				char(20),
       ACCTPORT				char(20),
       RETRIES				int,
       RETRYTIMEOUT			int,
       USEOLDASCENDPASSWORDS		int,
       SERVERHASBROKENPORTNUMBERS	int,
       SERVERHASBROKENADDRESSES		int,
       IGNOREREPLYSIGNATURE		int,
       FAILUREPOLICY			int,
       UNIQUE 				RADSQLRADIUS_I (TARGETNAME)
       );


# If you have many called-station-ids or realms mapping to and single
# target radius server, you can have an indirect table like this one
# and set your HostSelect to be a join between tables
create table RADSQLRADIUSINDIRECT (
       SOURCENAME			char(50),
       TARGETNAME			char(50),
       UNIQUE 				RADSQLRADIUSINDIRECT_I (SOURCENAME)
       );


# This table works with default StatsLogSQL
create table RADSTATSLOG (
       TIME_STAMP			int,
       TYPE				char(20),
       IDENTIFIER			char(30),
       ACCESSACCEPTS			int,
       ACCESSCHALLENGES			int,
       ACCESSREJECTS			int,
       ACCESSREQUESTS			int,
       ACCOUNTINGREQUESTS		int,
       ACCOUNTINGRESPONSES		int,
       BADAUTHACCESSREQUESTS		int,
       BADAUTHACCOUNTINGREQUESTS	int,
       BADAUTHREQUESTS			int,
       DROPPEDACCESSREQUESTS		int,
       DROPPEDACCOUNTINGREQUESTS	int,
       DROPPEDREQUESTS			int,
       DUPACCESSREQUESTS		int,
       DUPACCOUNTINGREQUESTS		int,
       DUPLICATEREQUESTS		int,
       MALFORMEDACCESSREQUESTS		int,
       MALFORMEDACCOUNTINGREQUESTS	int,
       PROXIEDNOREPLY			int,
       PROXIEDREQUESTS			int,
       REQUESTS				int,
       RESPONSETIME			decimal(12,6)
);

# Table for recording successful and unsuccessful login attempts
# This table could be used to work with a Radiator AuthLog like this:
#<AuthLog SQL>
#	DBSource	dbi:mysql:radmin:localhost
#	DBUsername	radmin
#	DBAuth		radminpw
#	LogSuccess
#	SuccessQuery insert into RADAUTHLOG (TIME_STAMP, USERNAME, TYPE) values (%t, '%n', 1)
#	LogFailure
#	FailureQuery insert into RADAUTHLOG (TIME_STAMP, USERNAME, TYPE, REASON) values (%t, '%n', 0, %1)
#</AuthLog>
create table RADAUTHLOG (
	TIME_STAMP	int,
	USERNAME	char(50),
	TYPE		int,
	REASON		char(50),
	INDEX USERNAME_I (USERNAME)
);

# This table can be used in the example EAP-TTLS hook goodies/eap_anon_hook.pl
# It is a lookaside table used to work out who the real user is for TTLS accounting
# requests that would otherwise look like they are from 'anonymous'
create table RADLASTAUTH (
	USERNAME	char(50) NOT NULL,
	NASIDENTIFIER	char(50) NOT NULL,
	NASPORT		int NOT NULL,
	ACCTSESSIONID	char(30),
	TIME_STAMP	int,

	UNIQUE RADLASTAUTH_I (NASIDENTIFIER, NASPORT, ACCTSESSIONID)
);
