# Run this as the MySQL owner of the OpenRADIUS database, eg.
# openradius@localhost in modules/radsql/schema.mysql

use openradius;

create table accounts (
	space		varchar(16) not null,	# namespace cq. realm
	name		varchar(16) not null,	# username
	password	varchar(16),		# password
	acctexp		datetime,		# account expiry date
	slotexp		int unsigned,		# timeslot ending date
	slotlength	int,			# used to set slotexp at acct
	secondsleft	int,			# running balance if not null
	callerlock	varchar(64),		# set to '' to enable
	profilename	varchar(64),		# references data.name

	primary key (space, name)
);

# An explanation of the non-self explanatory fields:
#
# acctexp: if not null, this date must be in the future for authentication to
# succeed. Not used to set Session-Timeout; users logging in before the account
# expires will be fully serviced according to the other values.
#
# slotexp: if not null, the date must be in the future at authentication time,
# otherwise the user is rejected. Session-Timeout is set accordingly, i.e. it's
# the smallest of secondsleft (see below) and slotexp - now().
# 
# slotlength: if not null, and slotexp is null, then slotexp will be
# initialised to now() + slotlength at accounting start time. This creates a
# timeslot that starts at the first successful login.
#
# secondsleft: if null, no accounting based on actual time used is performed,
# otherwise the user is only allowed in if this value is above zero.
# Session-Timeout is set accordingly. When a non-duplicate accounting stop
# record is received, Acct-Session-Time is subtracted from this value. It must
# be initialised here; nothing is copied from the profile at any time. That
# normally contains actual RADIUS attributes only.
#
# callerlock: if null or emtpy, the user is allowed in at authentication time,
# otherwise Called-Station-Id must match this value. If empty, the value is set
# to Called-Station-Id at accounting start time, effectively locking the
# account to the MAC address/telephone nr. that the first successful login 
# happened from. If null, no locking takes place.

grant all on accounts to openradius@localhost;
grant select, update on accounts to radiusd@localhost;

insert into accounts (space, name, secondsleft)
	values ('test', '001', 30);
insert into accounts (space, name, secondsleft)
	values ('test', '002', 30);
insert into accounts (space, name, secondsleft, slotlength)
	values ('test', '003', 60, 45);
insert into accounts (space, name, secondsleft, slotlength, callerlock)
	values ('test', '004', 15, 30, '');
insert into accounts (space, name, slotlength)
	values ('test', '005', 10);