Source

Portfolio / gwam / utilities / setupdb.sql

#!/bin/bash

######################
## DATABASE OPTIONS ##
######################
ADMIN=gwam_admin
ROOT_PASS='p1$$w30rd'
DB_PASS=mind3m0rk
DB=gwam

## Testing Settings
CRT_TEST_ADM_USR=1
CRT_TEST_USR_USR=1

## ROLE TABLE
##
CREATE_ROLE_TABLE="create table $DB.role (id int unsigned not null auto_increment, type varchar(20) default 'USER' not null, created timestamp, primary key (id)) engine=innodb;" 
CREATE_ADMIN_ROLE="insert into $DB.role (type) values ('ADMIN');" 
CREATE_USER_ROLE="insert into $DB.role values ();"

## ACCOUNTS TABLE
## GOOD NOTE: http://gusiev.com/2009/04/update-and-create-timestamps-with-mysql/
##
CREATE_ACCOUNTS_TABLE="create table $DB.acct (id int unsigned not null auto_increment, role_id int unsigned not null default '2', login varchar(255) not null default 'gwam@YOURDOMAIN.com', password varchar(255) not null default 'n0ts3t', active tinyint(1) default '0', created timestamp default '0000-00-00 00:00:00', modified timestamp not null default now() on update now(), primary key (id), constraint fk_role foreign key (role_id) references $DB.role(id) on update cascade, index(login)) engine=innodb;"
CREATE_TEST_ADMIN_ACCOUNT="insert into $DB.acct (role_id,login,password,active,created) values ('1','gwam_admin@YOURDOMAIN.com',password('gwam_admin_test'),'1',NULL);" 
CREATE_TEST_USER_ACCOUNT="insert into $DB.acct (role_id,login,password,active,created) values ('2','gwam_user@YOURDOMAIN.com',password('gwam_user_test'),'1',NULL);" 

## GUESTS TABLE
##
CREATE_GUESTS_TABLE="create table $DB.guest (id int unsigned not null auto_increment, admin varchar(255) not null default 'gwam_admin@YOURDOMAIN.com', gwamname varchar(255) not null default 'lccguest', fname varchar(255) not null default 'YOURPASSWORD', lname varchar(255) not null default 'Guest', email varchar(255) not null default 'lguest@YOURDOMAIN.com', active tinyint(1) default '1', password varchar(255) not null default 'lguest', days_valid int(9) unsigned not null default '5', created timestamp default '0000-00-00 00:00:00', modified timestamp not null default now() on update now(), primary key (id), constraint fk_admin foreign key (admin) references $DB.acct(login) on update cascade) engine=innodb;" 

## SETTINGS TABLE
##
CREATE_SETTINGS_TABLE="create table $DB.settings (id int unsigned not null auto_increment, option_key varchar(255) not null default 'site', option_value varchar(255) not null default 'gwam', modified_by varchar(255) not null default 'gwam_admin@YOURDOMAIN.com', created timestamp default '0000-00-00 00:00:00', modified timestamp not null default now() on update now(), primary key (id)) engine=innodb;"
CREATE_SETTINGS_SITE_TITLE="insert into $DB.settings (option_key,option_value,created) values ('stitle','YOURORGANIZATION - Guest Wireless Access Manager (GWAM)',null);"
CREATE_SETTINGS_SITE_COPY="insert into $DB.settings (option_key,option_value,created) values ('scopy','<p id="footer">YOURORGANIZATION<br />Network Operations<br />&#169; 2010 - AMD<br /><br /></p>',null);"
CREATE_SETTINGS_GUEST_PREFIX="insert into $DB.settings (option_key,option_value,created) values ('gprefix','lccguest',null);"
CREATE_SETTINGS_GUEST_MAX="insert into $DB.settings (option_key,option_value,created) values ('gmax','20',null);"
CREATE_SETTINGS_PASS_LENGTH="insert into $DB.settings (option_key,option_value,created) values ('plength','8',null);"
CREATE_SETTINGS_PASS_NUMS="insert into $DB.settings (option_key,option_value,created) values ('pnums','1',null);"
CREATE_SETTINGS_PASS_CHARS="insert into $DB.settings (option_key,option_value,created) values ('pchars','1',null);"
CREATE_SETTINGS_PASS_UNIQ="insert into $DB.settings (option_key,option_value,created) values ('puniq','6',null);"

#####################################
## NO NEED TO EDIT BELOW THIS LINE ##
#####################################

## Setup user
echo "ROOT: Create user $ADMIN"
mysql -u root --password="$ROOT_PASS" -h localhost -e "create user 'gwam_admin'@'localhost' identified by 'mind3m0rk';"

## Setup database
echo "ROOT: Create Database $DB"
mysql -u root --password="$ROOT_PASS" -h localhost -e "create database $DB"
echo "ROOT: Grant Database Permissions To $ADMIN"
mysql -u root --password="$ROOT_PASS" -h localhost -e "grant all on $DB.* to 'gwam_admin'@'localhost'" 

## Setup role table
echo "$ADMIN: Create Role Table"
mysql -u $ADMIN --password="$DB_PASS" -h localhost -e "$CREATE_ROLE_TABLE"
echo "$ADMIN: Create Admin Role"
mysql -u $ADMIN --password="$DB_PASS" -h localhost -e "$CREATE_ADMIN_ROLE"
echo "$ADMIN: Create User Role"
mysql -u $ADMIN --password="$DB_PASS" -h localhost -e "$CREATE_USER_ROLE"

## Setup account table
echo "$ADMIN: Create Accounts Table"
mysql -u $ADMIN --password="$DB_PASS" -h localhost -e "$CREATE_ACCOUNTS_TABLE"
if [ "$CRT_TEST_ADM_USR" -eq "1" ]; then
	echo "$ADMIN: Create Test Admin User"
	mysql -u $ADMIN --password="$DB_PASS" -h localhost -e "$CREATE_TEST_ADMIN_ACCOUNT"
fi
if [ "$CRT_TEST_USR_USR" -eq "1" ]; then
	echo "$ADMIN: Create Test User User"
	mysql -u $ADMIN --password="$DB_PASS" -h localhost -e "$CREATE_TEST_USER_ACCOUNT"
fi

## Setup guest table
echo "$ADMIN: Create Guests Table"
mysql -u $ADMIN --password="$DB_PASS" -h localhost -e "$CREATE_GUESTS_TABLE"

## Setup settings table
echo "$ADMIN: Create Settings Table"
mysql -u $ADMIN --password="$DB_PASS" -h localhost -e "$CREATE_SETTINGS_TABLE"
echo "$ADMIN: Add Default Site Settings"
mysql -u $ADMIN --password="$DB_PASS" -h localhost -e "$CREATE_SETTINGS_SITE_TITLE"
mysql -u $ADMIN --password="$DB_PASS" -h localhost -e "$CREATE_SETTINGS_SITE_COPY"
echo "$ADMIN: Add Default Guest Settings"
mysql -u $ADMIN --password="$DB_PASS" -h localhost -e "$CREATE_SETTINGS_GUEST_PREFIX"
mysql -u $ADMIN --password="$DB_PASS" -h localhost -e "$CREATE_SETTINGS_GUEST_MAX"
echo "$ADMIN: Add Default Pass Settings"
mysql -u $ADMIN --password="$DB_PASS" -h localhost -e "$CREATE_SETTINGS_PASS_LENGTH"
mysql -u $ADMIN --password="$DB_PASS" -h localhost -e "$CREATE_SETTINGS_PASS_NUMS"
mysql -u $ADMIN --password="$DB_PASS" -h localhost -e "$CREATE_SETTINGS_PASS_CHARS"
mysql -u $ADMIN --password="$DB_PASS" -h localhost -e "$CREATE_SETTINGS_PASS_UNIQ"