$stH = dbQuery("DELETE FROM `settings` WHERE `name` NOT LIKE 'calendar%'");
//empty table, except calendar values
}
$eveCnt = $catCnt = $useCnt = $groCnt = $setCnt = 0;
//init counters
dbTransaction('begin');
foreach ($sqlInsArr as $query) {
//insert records
$stH = dbQuery($query);
if (!$stH) {
break;
}
${substr($query, -3) . 'Cnt'}++;
//increment INSERT counter
}
$result = dbTransaction('commit');
if (!$result) {
$errMsg[] = "Error inserting data in table {$matches[1]} of calendar {$calID}";
break;
}
foreach ($selTabs as $table) {
//show result
$resMsg[] = "- Inserted {$table}: " . ${substr($table, 0, 3) . 'Cnt'};
}
}
break;
case 'backup':
//process Backup calendars
$buCals = array();
foreach ($selCals as $calID) {
//process calendars
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;
}