$tabExist++;
$newTabSet = '';
}
//insert initial data in cat, user and settings tables
$catSaved = initCats();
if (!$catSaved) {
$errMsg[] = "Error: Problem writing to database table 'categories' in calendar {$calID}.";
break;
}
$adPwMd5 = md5($adPwrd);
$usrSaved = initUsers($adName, $adMail, $adPwMd5);
if (!$usrSaved) {
$errMsg[] = "Error: Problem writing to database table 'users' in calendar {$calID}.";
break;
}
$grpSaved = initGroups();
if (!$grpSaved) {
$errMsg[] = "Error: Problem writing to database table 'groups' in calendar {$calID}.";
break;
}
$setSaved = true;
//init
if ($newTabSet) {
//insert default settings
$dbSet = array();
$dbSet['calendarTitle'] = $calTitle;
$dbSet['calendarUrl'] = $calUrlDefault . '?cal=' . $calID;
$dbSet['calendarEmail'] = !empty($calEml) ? $calEml : $adMail;
checkSettings($dbSet);
$setSaved = saveSettings($dbSet, true);
if (!$setSaved) {
function upgradeDb()
{
//upgrade $calID database from 2.7.2+
/* === tables pre-processing === */
//if users.sedit: set privs to 9 for admin accounts (sedit == 1)
$stH = dbQuery("SELECT `sedit` FROM `users` LIMIT 1", 0);
if ($stH) {
//column 'sedit' present (V2.7.2)
$stH = null;
dbQuery("UPDATE `users` SET `privs` = 9 WHERE `sedit` = 1");
}
//if users.privs: table groups not existing - create and populate
$stH = dbQuery("SELECT `privs` FROM `users` LIMIT 1", 0);
if ($stH !== false) {
//column 'privs' present
$stH = null;
createDbTable("groups");
initGroups();
}
//create tables with new schema
createDbTable("usersNew");
createDbTable("groupsNew");
createDbTable("categoriesNew");
createDbTable("eventsNew");
createDbTable("settingsNew");
/* === tables version-processing === */
dbTransaction('begin');
do {
//test if version < 3.1
$stH = dbQuery("SELECT `approve` FROM `categories` LIMIT 1", 0);
if (!$stH) {
//column 'approve' not present - version 2.7
$lcVUpg = '2.7';
dbQuery("INSERT INTO `eventsNew` (`ID`, `private`, `title`, `venue`, `text1`, `catID`, `userID`, `editor`, `checked`, `notify`, `notMail`, `sDate`, `eDate`, `xDates`, `sTime`, `eTime`, `rType`, `rInterval`, `rPeriod`, `rMonth`, `rUntil`, `aDateTime`, `mDateTime`, `status`) \n\t\t\t\tSELECT `event_id`, `private`, `title`, `venue`, `description`, `category_id`, `user_id`, `editor`, `checked`, `notify`, `not_mail`, `s_date`, `e_date`, `x_dates`, `s_time`, `e_time`, `r_type`, `r_interval`, `r_period`, `r_month`, `r_until`, `a_date`, `m_date`, `status` FROM `events`");
//copy content events table
dbQuery("INSERT INTO `usersNew` (`ID`, `name`, `password`, `tPassword`, `email`, `groupID`, `language`, `login0`, `login1`, `loginCnt`, `status`) \n\t\t\t\tSELECT `user_id`, `user_name`, `password`, `temp_password`, `email`, `privs`, `language`, `login_0`, `login_1`, `login_cnt`, `status` FROM `users`");
//copy content users table
dbQuery("INSERT INTO `groupsNew` SELECT * FROM `groups`");
//new table, no change
dbQuery("INSERT INTO `categoriesNew` (`ID`, `name`, `sequence`, `repeat`, `color`, `bgColor`, `checkBx`, `checkLb`, `checkMk`, `status`) \n\t\t\t\tSELECT `category_id`, `name`, `sequence`, `rpeat`, `color`, `background`, `check2`, `label2`, `mark2`, `status` FROM `categories`");
//copy content categories table
dbQuery("INSERT INTO `settingsNew` (`name`, `value`, `outline`) \n\t\t\t\tSELECT `name`, `value`, `description` FROM `settings`");
//copy content settings table
break;
//done
}
//test if version = 3.1
$stH = dbQuery("SELECT `a_date` FROM `events` LIMIT 1", 0);
if ($stH !== false) {
//column 'a_date' present - version 3.1
$lcVUpg = '3.1';
dbQuery("INSERT INTO `eventsNew` (`ID`, `private`, `title`, `venue`, `text1`, `catID`, `userID`, `editor`, `approved`, `checked`, `notify`, `notMail`, `sDate`, `eDate`, `xDates`, `sTime`, `eTime`, `rType`, `rInterval`, `rPeriod`, `rMonth`, `rUntil`, `aDateTime`, `mDateTime`, `status`) \n\t\t\t\tSELECT `event_id`, `private`, `title`, `venue`, `description`, `category_id`, `user_id`, `editor`, `approved`, `checked`, `notify`, `not_mail`, `s_date`, `e_date`, `x_dates`, `s_time`, `e_time`, `r_type`, `r_interval`, `r_period`, `r_month`, `r_until`, `a_date`, `m_date`, `status` FROM `events`");
//copy content events table
dbQuery("INSERT INTO `usersNew` (`ID`, `name`, `password`, `tPassword`, `email`, `groupID`, `language`, `login0`, `login1`, `loginCnt`, `status`) \n\t\t\t\tSELECT `user_id`, `user_name`, `password`, `temp_password`, `email`, `privs`, `language`, `login_0`, `login_1`, `login_cnt`, `status` FROM `users`");
//copy content users table
dbQuery("INSERT INTO `groupsNew` SELECT * FROM `groups`");
//new table, no change
dbQuery("INSERT INTO `categoriesNew` (`ID`, `name`, `sequence`, `repeat`, `approve`, `color`, `bgColor`, `checkBx`, `checkLb`, `checkMk`, `status`) \n\t\t\t\tSELECT `category_id`, `name`, `sequence`, `rpeat`, `approve`, `color`, `background`, `chbox`, `chlabel`, `chmark`, `status` FROM `categories`");
//copy content categories table
dbQuery("INSERT INTO `settingsNew` (`name`, `value`, `outline`) \n\t\t\t\tSELECT `name`, `value`, `description` FROM `settings`");
//copy content settings table
break;
//done
}
//test if version = 3.2
$stH = dbQuery("SELECT `ID` FROM `events` LIMIT 1", 0);
if (!$stH) {
//column 'ID' not present - version 3.2
$lcVUpg = '3.2';
dbQuery("INSERT INTO `eventsNew` (`ID`, `private`, `title`, `venue`, `text1`, `text2`, `text3`, `catID`, `userID`, `editor`, `approved`, `checked`, `notify`, `notMail`, `sDate`, `eDate`, `xDates`, `sTime`, `eTime`, `rType`, `rInterval`, `rPeriod`, `rMonth`, `rUntil`, `aDateTime`, `mDateTime`, `status`) \n\t\t\t\tSELECT `event_id`, `private`, `title`, `venue`, `description`, `xfield1`, `xfield2`, `category_id`, `user_id`, `editor`, `approved`, `checked`, `notify`, `not_mail`, `s_date`, `e_date`, `x_dates`, `s_time`, `e_time`, `r_type`, `r_interval`, `r_period`, `r_month`, `r_until`, `a_datetime`, `m_datetime`, `status` FROM `events`");
//copy content events table
dbQuery("INSERT INTO `usersNew` (`ID`, `name`, `password`, `tPassword`, `email`, `groupID`, `language`, `login0`, `login1`, `loginCnt`, `status`) \n\t\t\t\tSELECT `user_id`, `user_name`, `password`, `temp_password`, `email`, `privs`, `language`, `login_0`, `login_1`, `login_cnt`, `status` FROM `users`");
//copy content users table
dbQuery("INSERT INTO `groupsNew` SELECT * FROM `groups`");
//new table, no change
dbQuery("INSERT INTO `categoriesNew` (`ID`, `name`, `sequence`, `repeat`, `approve`, `color`, `bgColor`, `checkBx`, `checkLb`, `checkMk`, `status`) \n\t\t\t\tSELECT `category_id`, `name`, `sequence`, `rpeat`, `approve`, `color`, `background`, `chbox`, `chlabel`, `chmark`, `status` FROM `categories`");
//copy content categories table
dbQuery("INSERT INTO `settingsNew` (`name`, `value`, `outline`) \n\t\t\t\tSELECT `name`, `value`, `description` FROM `settings`");
//copy content settings table
break;
//done
}
//test if version = 4.1
$stH = dbQuery("SELECT `dayColor` FROM `categories` LIMIT 1", 0);
if (!$stH) {
//column 'dayColor' not present - version 4.1
$lcVUpg = '4.1';
dbQuery("INSERT INTO `eventsNew` (`ID`, `private`, `title`, `venue`, `text1`, `text2`, `text3`, `catID`, `userID`, `editor`, `approved`, `checked`, `notify`, `notMail`, `sDate`, `eDate`, `xDates`, `sTime`, `eTime`, `rType`, `rInterval`, `rPeriod`, `rMonth`, `rUntil`, `aDateTime`, `mDateTime`, `status`) \n\t\t\t\tSELECT `ID`, `private`, `title`, `venue`, `text1`, `text2`, `text3`, `catID`, `userID`, `editor`, `approved`, `checked`, `notify`, `notMail`, `sDate`, `eDate`, `xDates`, `sTime`, `eTime`, `rType`, `rInterval`, `rPeriod`, `rMonth`, `rUntil`, `aDateTime`, `mDateTime`, `status` FROM `events`");
//copy content events table
dbQuery("INSERT INTO `usersNew` (`ID`, `name`, `password`, `tPassword`, `email`, `groupID`, `language`, `login0`, `login1`, `loginCnt`, `status`) \n\t\t\t\tSELECT `ID`, `name`, `password`, `tPassword`, `email`, `groupID`, `language`, `login0`, `login1`, `loginCnt`, `status` FROM `users`");
//copy content users table
dbQuery("INSERT INTO `groupsNew` (`ID`, `name`, `privs`, `catIDs`, `color`, `status`) \n\t\t\t\tSELECT `ID`, `name`, `privs`, `catIDs`, `color`, `status` FROM `groups`");
//copy content groups table
dbQuery("INSERT INTO `categoriesNew` (`ID`, `name`, `sequence`, `repeat`, `approve`, `color`, `bgColor`, `checkBx`, `checkLb`, `checkMk`, `status`) \n\t\t\t\tSELECT `ID`, `name`, `sequence`, `repeat`, `approve`, `color`, `bgColor`, `checkBx`, `checkLb`, `checkMk`, `status` FROM `categories`");
//copy content categories table
dbQuery("INSERT INTO `settingsNew` SELECT * FROM `settings`");
//copy content settings table
break;
//done
}
//test if version = 4.2
$stH = dbQuery("SELECT `noverlap` FROM `categories` LIMIT 1", 0);
if (!$stH) {
//column 'overlay' not present - version 4.2
$lcVUpg = '4.2';
dbQuery("INSERT INTO `eventsNew` SELECT * FROM `events`");
//copy content events table
dbQuery("INSERT INTO `usersNew` SELECT * FROM `users`");
//copy content users table
dbQuery("INSERT INTO `groupsNew` (`ID`, `name`, `privs`, `catIDs`, `color`, `status`) \n\t\t\t\tSELECT `ID`, `name`, `privs`, `catIDs`, `color`, `status` FROM `groups`");
//copy content groups table
dbQuery("INSERT INTO `categoriesNew` (`ID`, `name`, `sequence`, `repeat`, `approve`, `dayColor`, `color`, `bgColor`, `checkBx`, `checkLb`, `checkMk`, `status`) \n\t\t\t\tSELECT `ID`, `name`, `sequence`, `repeat`, `approve`, `dayColor`, `color`, `bgColor`, `checkBx`, `checkLb`, `checkMk`, `status` FROM `categories`");
//copy content categories table
dbQuery("INSERT INTO `settingsNew` SELECT * FROM `settings`");
//copy content settings table
break;
//done
}
//version = current version (no changes)
$lcVUpg = '4.3';
dbQuery("INSERT INTO `eventsNew` SELECT * FROM `events`");
dbQuery("INSERT INTO `usersNew` SELECT * FROM `users`");
dbQuery("INSERT INTO `groupsNew` SELECT * FROM `groups`");
dbQuery("INSERT INTO `categoriesNew` SELECT * FROM `categories`");
dbQuery("INSERT INTO `settingsNew` SELECT * FROM `settings`");
} while (0);
//end of: process calendar $calID
/* === tables post-processing === */
$stH = dbQuery("SELECT `privs` FROM `users` LIMIT 1", 0);
if ($stH !== false) {
//privs found, table groups is new
$stH = null;
//convert users.groupID (old privs) to new groupID
$groups = array(0 => 1, 9 => 2, 1 => 3, 2 => 4, 3 => 5, 4 => 6);
$stH = dbQuery("SELECT `ID`, `groupID` FROM `usersNew`");
//get groupID (old privs)
while ($row = $stH->fetch(PDO::FETCH_ASSOC)) {
dbQuery("UPDATE `usersNew` SET `groupID` = {$groups[$row['groupID']]} WHERE `ID` = {$row['ID']}");
}
}
//groups.ID and users.groupID: renumber ID starting from 1
$stH = dbQuery("SELECT * FROM `groupsNew` WHERE `ID` = 0");
if ($row = $stH->fetch(PDO::FETCH_NUM)) {
//column 'ID' = 0 exists - renumber
$stH = dbQuery("SELECT `ID` FROM `groupsNew` ORDER BY `ID` DESC");
while ($row = $stH->fetch(PDO::FETCH_ASSOC)) {
dbQuery("UPDATE `groupsNew` SET `ID` = `ID` + 1 WHERE `ID` = {$row['ID']}");
//must be done in reverse order
}
dbQuery("UPDATE `usersNew` SET `groupID` = `groupID` + 1");
}
//correct groupIDs admin (3 -> 2) and read-access (2 -> 3)
$stH = dbQuery("SELECT `ID`, `groupID` FROM `usersNew` WHERE `ID` = 2 and `groupID` = 3", 0);
//admin in group 3
if ($row = $stH->fetch(PDO::FETCH_NUM)) {
dbQuery("UPDATE `groupsNew` SET `ID` = 100 WHERE `ID` = 2");
//park read-only
dbQuery("UPDATE `usersNew` SET `groupID` = 100 WHERE `groupID` = 2");
//idem
dbQuery("UPDATE `groupsNew` SET `ID` = 2 WHERE `ID` = 3");
//set admin to 2
dbQuery("UPDATE `usersNew` SET `groupID` = 2 WHERE `groupID` = 3");
//idem
dbQuery("UPDATE `groupsNew` SET `ID` = 3 WHERE `ID` = 100");
//set parked to 3
dbQuery("UPDATE `usersNew` SET `groupID` = 3 WHERE `groupID` = 100");
//idem
}
//events.checked: ;dd-mm-yyyya -> ;dd-mm-yyyy and drop ;dd-mm-yyyyb
$stH = dbQuery("SELECT `ID`,`checked` FROM `eventsNew` WHERE `checked` LIKE '%a%'");
while ($row = $stH->fetch(PDO::FETCH_ASSOC)) {
$chBoxed = preg_replace(array('~;\\d\\d\\d\\d-\\d\\d-\\d\\db~', '~(;\\d\\d\\d\\d-\\d\\d-\\d\\d)a~'), array('', '$1'), $row['checked']);
//drop ;dd-mm-yyyyb and trim a in ;dd-mm-yyyya
dbQuery("UPDATE `eventsNew` SET `checked`='{$chBoxed}' WHERE `ID` = {$row['ID']}");
}
//events.sTime/eTime: truncate time to 00:00
$stH = dbQuery("SELECT `sTime` FROM `eventsNew` LIMIT 1");
if ($row = $stH->fetch(PDO::FETCH_NUM)) {
//column 'sTime' exists
if (strlen($row[0]) > 5) {
//truncate times
dbQuery("UPDATE `eventsNew` SET `sTime` = substr(`sTime`,1,5),`eTime` = substr(`eTime`,1,5)");
}
}
//events.aDateTime/mDateTime: truncate time to 00:00
$stH = dbQuery("SELECT `aDateTime` FROM `eventsNew` LIMIT 1");
if ($row = $stH->fetch(PDO::FETCH_NUM)) {
//column 'aDateTime' exists
if (strlen($row[0]) > 16) {
//truncate time
dbQuery("UPDATE `eventsNew` SET `aDateTime` = substr(`aDateTime`,1,16),`mDateTime` = substr(`mDateTime`,1,16)");
}
if (strlen($row[0]) < 16) {
//pad to yyyy-mm-dd 00:00
dbQuery("UPDATE `eventsNew` SET `aDateTime` = substr(`aDateTime`,1,10)||' 00:00',`mDateTime` = substr(`mDateTime`,1,10)||' 00:00'");
}
}
$stH = null;
//release statement handle
//drop original tables and rename new upgraded tables
dbQuery("DROP TABLE `users`");
dbQuery("ALTER TABLE `usersNew` RENAME TO `users`");
dbQuery("DROP TABLE `groups`");
dbQuery("ALTER TABLE `groupsNew` RENAME TO `groups`");
dbQuery("DROP TABLE `categories`");
dbQuery("ALTER TABLE `categoriesNew` RENAME TO `categories`");
dbQuery("DROP TABLE `events`");
dbQuery("ALTER TABLE `eventsNew` RENAME TO `events`");
dbQuery("DROP TABLE `settings`");
dbQuery("ALTER TABLE `settingsNew` RENAME TO `settings`");
dbTransaction('commit');
return $lcVUpg;
}
