/**
* Create the database and the tables in it
* - Requires an external file with sql commands
* @param none
*/
function doCreate()
{
global $db;
global $conf;
$announcements = DBEngine::get_table('announcements');
$login = DBEngine::get_table('login');
$reservations = DBEngine::get_table('reservations');
$resources = DBEngine::get_table('resources');
$permission = DBEngine::get_table('permission');
$schedule_permission = DBEngine::get_table('schedule_permission');
$schedules = DBEngine::get_table('schedules');
$reservation_users = DBEngine::get_table('reservation_users');
$anonymous_users = DBEngine::get_table('anonymous_users');
$additional_resources = DBEngine::get_table('additional_resources');
$reservation_resources = DBEngine::get_table('reservation_resources');
$mutex = DBEngine::get_table('mutex');
$groups = DBEngine::get_table('groups');
$user_groups = DBEngine::get_table('user_groups');
$reminders = DBEngine::get_table('reminders');
$sqls = array(array("create database {$conf['db']['dbName']}", "Creating database"), array("use {$conf['db']['dbName']}", "Selecting database"), array("CREATE TABLE {$announcements} (\r\n\t\t\t\t\t\t\t\tannouncementid CHAR(16) NOT NULL PRIMARY KEY,\r\n\t\t\t\t\t\t\t\tannouncement VARCHAR(255) NOT NULL DEFAULT '',\r\n\t\t\t\t\t\t\t\tnumber SMALLINT NOT NULL DEFAULT '0',\r\n\t\t\t\t\t\t\t\tstart_datetime INTEGER,\r\n\t\t\t\t\t\t\t\tend_datetime INTEGER\r\n\t\t\t\t\t\t\t)", "Creating announcement table"), array("CREATE INDEX announcements_startdatetime ON {$announcements}(start_datetime)", 'Creating index'), array("CREATE INDEX announcements_enddatetime ON {$announcements}(end_datetime)", 'Creating index'), array("CREATE TABLE {$login} (\r\n\t\t\t\t\t\t\t memberid CHAR(16) NOT NULL PRIMARY KEY,\r\n\t\t\t\t\t\t\t email VARCHAR(75) NOT NULL,\r\n\t\t\t\t\t\t\t password CHAR(32) NOT NULL,\r\n\t\t\t\t\t\t\t fname VARCHAR(30) NOT NULL,\r\n\t\t\t\t\t\t\t lname VARCHAR(30) NOT NULL,\r\n\t\t\t\t\t\t\t phone VARCHAR(16) NOT NULL,\r\n\t\t\t\t\t\t\t institution VARCHAR(255),\r\n\t\t\t\t\t\t\t position VARCHAR(100),\r\n\t\t\t\t\t\t\t e_add CHAR(1) NOT NULL DEFAULT 'y',\r\n\t\t\t\t\t\t\t e_mod CHAR(1) NOT NULL DEFAULT 'y',\r\n\t\t\t\t\t\t\t e_del CHAR(1) NOT NULL DEFAULT 'y',\r\n\t\t\t\t\t\t\t e_app CHAR(1) NOT NULL DEFAULT 'y',\r\n\t\t\t\t\t\t\t e_html CHAR(1) NOT NULL DEFAULT 'y',\r\n\t\t\t\t\t\t\t logon_name VARCHAR(30),\r\n\t\t\t\t\t\t\t is_admin SMALLINT DEFAULT 0,\r\n\t\t\t\t\t\t\t lang VARCHAR(5),\r\n\t\t\t\t\t\t\t timezone FLOAT NOT NULL DEFAULT 0\r\n\t\t\t\t\t\t\t )", 'Creating login table'), array("CREATE INDEX login_email ON {$login} (email)", 'Creating index'), array("CREATE INDEX login_password ON {$login} (password)", 'Creating index'), array("CREATE INDEX login_logonname ON {$login} (logon_name)", 'Creating index'), array("CREATE TABLE {$reservations} (\r\n\t\t\t\t\t\t\t resid CHAR(16) NOT NULL PRIMARY KEY,\r\n\t\t\t\t\t\t\t machid CHAR(16) NOT NULL,\r\n\t\t\t\t\t\t\t scheduleid CHAR(16) NOT NULL,\r\n\t\t\t\t\t\t\t start_date INT NOT NULL DEFAULT 0,\r\n\t\t\t\t\t\t\t end_date INT NOT NULL DEFAULT 0,\r\n\t\t\t\t\t\t\t starttime INTEGER NOT NULL,\r\n\t\t\t\t\t\t\t endtime INTEGER NOT NULL,\r\n\t\t\t\t\t\t\t created INTEGER NOT NULL,\r\n\t\t\t\t\t\t\t modified INTEGER,\r\n\t\t\t\t\t\t\t parentid CHAR(16),\r\n\t\t\t\t\t\t\t is_blackout SMALLINT NOT NULL DEFAULT 0,\r\n\t\t\t\t\t\t\t is_pending SMALLINT NOT NULL DEFAULT 0,\r\n\t\t\t\t\t\t\t summary TEXT,\r\n\t\t\t\t\t\t\t allow_participation SMALLINT NOT NULL DEFAULT 0,\r\n\t\t\t\t\t\t\t allow_anon_participation SMALLINT NOT NULL DEFAULT 0\r\n\t\t\t\t\t\t\t )", 'Creating reservations table'), array("CREATE INDEX res_machid ON {$reservations} (machid)", 'Creating index'), array("CREATE INDEX res_scheduleid ON {$reservations} (scheduleid)", 'Creating index'), array("CREATE INDEX reservations_startdate ON {$reservations} (start_date)", 'Creating index'), array("CREATE INDEX reservations_enddate ON {$reservations} (end_date)", 'Creating index'), array("CREATE INDEX res_startTime ON {$reservations} (starttime)", 'Creating index'), array("CREATE INDEX res_endTime ON {$reservations} (endtime)", 'Creating index'), array("CREATE INDEX res_created ON {$reservations} (created)", 'Creating index'), array("CREATE INDEX res_modified ON {$reservations} (modified)", 'Creating index'), array("CREATE INDEX res_parentid ON {$reservations} (parentid)", 'Creating index'), array("CREATE INDEX res_isblackout ON {$reservations} (is_blackout)", 'Creating index'), array("CREATE INDEX reservations_pending ON {$reservations} (is_pending)", 'Creating index'), array("CREATE TABLE {$resources} (\r\n\t\t\t\t\t\t\t machid CHAR(16) NOT NULL PRIMARY KEY,\r\n\t\t\t\t\t\t\t scheduleid CHAR(16) NOT NULL,\r\n\t\t\t\t\t\t\t name VARCHAR(75) NOT NULL,\r\n\t\t\t\t\t\t\t location VARCHAR(250),\r\n\t\t\t\t\t\t\t rphone VARCHAR(16),\r\n\t\t\t\t\t\t\t notes TEXT,\r\n\t\t\t\t\t\t\t status CHAR(1) NOT NULL DEFAULT 'a',\r\n\t\t\t\t\t\t\t minres INTEGER NOT NULL,\r\n\t\t\t\t\t\t\t maxres INTEGER NOT NULL,\r\n\t\t\t\t\t\t\t autoassign SMALLINT,\r\n\t\t\t\t\t\t\t approval SMALLINT,\r\n\t\t\t\t\t\t\t allow_multi SMALLINT,\r\n\t\t\t\t\t\t\t max_participants INTEGER,\r\n\t\t\t\t\t\t\t min_notice_time INTEGER,\r\n\t\t\t\t\t\t\t max_notice_time INTEGER\r\n\t\t\t\t\t\t\t )", 'Creating resources table'), array("CREATE INDEX rs_scheduleid ON {$resources} (scheduleid)", 'Creating index'), array("CREATE INDEX rs_name ON {$resources} (name)", 'Creating index'), array("CREATE INDEX rs_status ON {$resources} (status)", 'Creating index'), array("CREATE TABLE {$permission} (\r\n\t\t\t\t\t\t\t memberid CHAR(16) NOT NULL,\r\n\t\t\t\t\t\t\t machid CHAR(16) NOT NULL,\r\n\t\t\t\t\t\t\t PRIMARY KEY(memberid, machid)\r\n\t\t\t\t\t\t\t )", 'Creating permission table'), array("CREATE INDEX per_memberid ON {$permission} (memberid)", 'Creating index'), array("CREATE INDEX per_machid ON {$permission} (machid)", 'Creating index'), array("CREATE TABLE {$schedules} (\r\n\t\t\t\t\t\t\t scheduleid CHAR(16) NOT NULL PRIMARY KEY,\r\n\t\t\t\t\t\t\t scheduletitle CHAR(75),\r\n\t\t\t\t\t\t\t daystart INTEGER NOT NULL,\r\n\t\t\t\t\t\t\t dayend INTEGER NOT NULL,\r\n\t\t\t\t\t\t\t timespan INTEGER NOT NULL,\r\n\t\t\t\t\t\t\t timeformat INTEGER NOT NULL,\r\n\t\t\t\t\t\t\t weekdaystart INTEGER NOT NULL,\r\n\t\t\t\t\t\t\t viewdays INTEGER NOT NULL,\r\n\t\t\t\t\t\t\t usepermissions SMALLINT,\r\n\t\t\t\t\t\t\t ishidden SMALLINT,\r\n\t\t\t\t\t\t\t showsummary SMALLINT,\r\n\t\t\t\t\t\t\t adminemail VARCHAR(75),\r\n\t\t\t\t\t\t\t isdefault SMALLINT\r\n\t\t\t\t\t\t\t )", 'Creating table schedules'), array("CREATE INDEX sh_hidden ON {$schedules} (ishidden)", 'Creating index'), array("CREATE INDEX sh_perms ON {$schedules} (usepermissions)", 'Creating index'), array("CREATE TABLE {$schedule_permission} (\r\n\t\t\t\t\t\t\t scheduleid CHAR(16) NOT NULL,\r\n\t\t\t\t\t\t\t memberid CHAR(16) NOT NULL,\r\n\t\t\t\t\t\t\t PRIMARY KEY(scheduleid, memberid)\r\n\t\t\t\t\t\t\t )", 'Creating table schedule_permission'), array("CREATE INDEX sp_scheduleid ON {$schedule_permission} (scheduleid)", 'Creating index'), array("CREATE INDEX sp_memberid ON {$schedule_permission} (memberid)", 'Creating index'), array("CREATE TABLE {$reservation_users} (\r\n\t\t\t\t\t\t\t resid CHAR(16) NOT NULL,\r\n\t\t\t\t\t\t\t memberid CHAR(16) NOT NULL,\r\n\t\t\t\t\t\t\t owner SMALLINT,\r\n\t\t\t\t\t\t\t invited SMALLINT,\r\n\t\t\t\t\t\t\t perm_modify SMALLINT,\r\n\t\t\t\t\t\t\t perm_delete SMALLINT,\r\n\t\t\t\t\t\t\t accept_code CHAR(16),\r\n\t\t\t\t\t\t\t PRIMARY KEY(resid, memberid)\r\n\t\t\t\t\t\t\t )", 'Creating table reservation_users'), array("CREATE INDEX resusers_resid ON {$reservation_users} (resid)", 'Creating index'), array("CREATE INDEX resusers_memberid ON {$reservation_users} (memberid)", 'Creating index'), array("CREATE INDEX resusers_owner ON {$reservation_users} (owner)", 'Creating index'), array("CREATE TABLE {$anonymous_users} (\r\n\t\t\t\t\t\t\t memberid CHAR(16) NOT NULL PRIMARY KEY,\r\n\t\t\t\t\t\t\t email VARCHAR(75) NOT NULL,\r\n\t\t\t\t\t\t\t fname VARCHAR(30) NOT NULL,\r\n\t\t\t\t\t\t\t lname VARCHAR(30) NOT NULL\r\n\t\t\t\t\t\t\t )", 'Creating table anonymous_users'), array("CREATE TABLE {$additional_resources} (\r\n\t\t\t\t\t\t\t resourceid CHAR(16) NOT NULL PRIMARY KEY,\r\n\t\t\t\t\t\t\t name VARCHAR(75) NOT NULL,\r\n\t\t\t\t\t\t\t status CHAR(1) NOT NULL DEFAULT 'a',\r\n\t\t\t\t\t\t\t number_available INTEGER NOT NULL DEFAULT -1\r\n\t\t\t\t\t\t\t )", 'Creating table additional_resources'), array("CREATE INDEX ar_name ON {$additional_resources} (name)", 'Creating index'), array("CREATE INDEX ar_status ON {$additional_resources} (status)", 'Creating index'), array("CREATE TABLE {$reservation_resources} (\r\n\t\t\t\t\t\t\t resid CHAR(16) NOT NULL,\r\n\t\t\t\t\t\t\t resourceid CHAR(16) NOT NULL,\r\n\t\t\t\t\t\t\t owner SMALLINT,\r\n\t\t\t\t\t\t\t PRIMARY KEY(resid, resourceid)\r\n\t\t\t\t\t\t\t )", 'Creating table reservation_resources'), array("CREATE INDEX resresources_resid ON {$reservation_resources} (resid)", 'Creating index'), array("CREATE INDEX resresources_resourceid ON {$reservation_resources} (resourceid)", 'Creating index'), array("CREATE INDEX resresources_owner ON {$reservation_resources} (owner)", 'Creating index'), array("CREATE TABLE {$mutex} (\r\n\t\t\t\t\t\t\t i INTEGER NOT NULL PRIMARY KEY\r\n\t\t\t\t\t\t\t )", 'Creating table mutex'), array("INSERT INTO {$mutex} VALUES (0)", 'Insert values'), array("INSERT INTO {$mutex} VALUES (1)", 'Insert values'), array("CREATE TABLE {$groups} (\r\n\t\t\t\t\t\t\t groupid CHAR(16) NOT NULL PRIMARY KEY,\r\n\t\t\t\t\t\t\t group_name VARCHAR(50) NOT NULL\r\n\t\t\t\t\t\t\t )", 'Creating table groups'), array("CREATE TABLE {$user_groups} (\r\n\t\t\t\t\t\t\t groupid CHAR(16) NOT NULL,\r\n\t\t\t\t\t\t\t memberid CHAR(50) NOT NULL,\r\n\t\t\t\t\t\t\t is_admin SMALLINT NOT NULL DEFAULT 0,\r\n\t\t\t\t\t\t\t PRIMARY KEY(groupid, memberid)\r\n\t\t\t\t\t\t\t )", 'Creating table user_groups'), array("CREATE INDEX usergroups_groupid ON {$user_groups} (groupid)", 'Creating index'), array("CREATE INDEX usergroups_memberid ON {$user_groups} (memberid)", 'Creating index'), array("CREATE INDEX usergroups_is_admin ON {$user_groups} (is_admin)", 'Creating index'), array("CREATE TABLE {$reminders} (\r\n\t\t\t\t\t\t\t reminderid CHAR(16) NOT NULL PRIMARY KEY,\r\n\t\t\t\t\t\t\t memberid CHAR(16) NOT NULL,\r\n\t\t\t\t\t\t\t resid CHAR(16) NOT NULL,\r\n\t\t\t\t\t\t\t reminder_time BIGINT NOT NULL\r\n\t\t\t\t\t\t\t )", 'Creating table reminders'), array("CREATE INDEX reminders_time ON {$reminders} (reminder_time)", 'Creating index'), array("CREATE INDEX reminders_memberid ON {$reminders} (memberid)", 'Creating index'), array("CREATE INDEX reminders_resid ON {$reminders} (resid)", 'Creating index'), array("grant select, insert, update, delete\r\n\t\t\t\t\t\t\ton {$conf['db']['dbName']}.*\r\n\t\t\t\t\t\t\tto {$conf['db']['dbUser']}@localhost identified by '{$conf['db']['dbPass']}'", 'Creating database user'));
if ($conf['db']['drop_old']) {
// Drop any old database with same name
array_unshift($sqls, array("drop database if exists {$conf['db']['dbName']}", 'Dropping database'));
}
foreach ($sqls as $sql) {
echo $sql[1] . '...';
$result = $db->query($sql[0]);
check_result($result);
}
$dbe = new DBEngine();
// Create default schedule
echo 'Creating default schedule...';
$scheduleid = $dbe->get_new_id();
$result = $dbe->db->query("INSERT INTO {$schedules} VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)", array($scheduleid, 'default', 480, 1200, 30, 12, 0, 7, 0, 0, 1, $conf['app']['adminEmail'], 1));
check_result($result);
}
function determineVersion()
{
$db = new DBEngine();
$version = "0.0.0";
$result = $db->db->query('select * from ' . $db->get_table('reservations'));
$num = $result->numCols();
if ($num < 12) {
$version = "0.0.0";
} else {
if ($num == 12) {
$version = "1.0.0";
} else {
if ($num == 13) {
$version = "1.1.0";
} else {
if ($num == 14) {
$version = "1.2.0";
}
}
}
}
return $version;
}