public function writeToTable($tableName, $type = 'MyISAM', $temporary = false) { $fieldDefs = array_map(function ($cn) { return sprintf('`%s` varchar(255) default NULL', $cn); }, $this->_columnNames); // trim blank last column $trimLast = false; if (!end($this->_columnNames)) { $trimLast = true; array_pop($fieldDefs); } // create table DB::nonQuery('CREATE TABLE `%s` (%s) ENGINE=%s DEFAULT CHARSET=utf8;', array($tableName, join(',', $fieldDefs), $type)); // write rows $count = 0; while ($row = $this->getNextRow(false)) { if ($trimLast) { array_pop($row); } DB::nonQuery('INSERT INTO `%s` VALUES ("%s")', array($tableName, implode('","', array_map(array('DB', 'escape'), $row)))); $count++; } return $count; }
<?php $tableName = Gatekeeper\Endpoints\Endpoint::$tableName; // skip conditions $skipped = true; if (!static::tableExists($tableName)) { printf("Skipping migration because table `%s` does not exist yet\n", $tableName); return static::STATUS_SKIPPED; } // migration if (!static::columnExists($tableName, 'GlobalBandwidthCount')) { printf("Adding column `%s`.`%s`\n", $tableName, 'GlobalBandwidthCount'); DB::nonQuery('ALTER TABLE `%s` ADD `GlobalBandwidthCount` int unsigned NULL default NULL', $tableName); $skipped = false; } if (!static::columnExists($tableName, 'GlobalBandwidthPeriod')) { printf("Adding column `%s`.`%s`\n", $tableName, 'GlobalBandwidthPeriod'); DB::nonQuery('ALTER TABLE `%s` ADD `GlobalBandwidthPeriod` int unsigned NULL default NULL', $tableName); $skipped = false; } // done return $skipped ? static::STATUS_SKIPPED : static::STATUS_EXECUTED;
public static function synchronize(Job $Job, $pretend = true) { if ($Job->Status != 'Pending' && $Job->Status != 'Completed') { return static::throwError('Cannot execute job, status is not Pending or Complete'); } // update job status $Job->Status = 'Pending'; if (!$pretend) { $Job->save(); } // init results struct $results = array('events' => array('analyzed' => 0, 'created' => 0, 'updated' => 0, 'deleted' => 0, 'skipped' => 0)); // uncap execution time set_time_limit(0); $now = time(); $nowString = date('Y-m-d H:i:s', $now); // compile course upload data foreach (Feed::getAll() as $Feed) { $ics = new iCal($Feed->Link); foreach ($ics->getEvents() as $icsEvent) { if ($Feed->MinimumDate && $Feed->MinimumDate > $icsEvent->getStart()) { $results['events']['skipped']++; continue; } $results['events']['analyzed']++; $icsId = $icsEvent->getUID(); if ($recurrenceId = $icsEvent->getProperty('recurrence-id')) { $icsId .= '+' . $recurrenceId; } // try to get existing if (!($Event = FeedEvent::getByUID($icsId))) { $Event = FeedEvent::create(array('UID' => $icsId)); } $description = trim($icsEvent->getDescription()); $location = trim($icsEvent->getLocation()); $Event->setFields(array('Title' => $icsEvent->getSummary(), 'Description' => $description ? $description : null, 'Location' => $location ? $location : null, 'StartTime' => $icsEvent->getStart(), 'EndTime' => $icsEvent->getEnd(), 'FeedID' => $Feed->ID, 'Imported' => $now)); $logEntry = $Job->logRecordDelta($Event, array('messageRenderer' => function ($logEntry) { if ($logEntry['action'] == 'create') { return "Created new event: {$logEntry[record]->Title}"; } else { return "Updated event #{$logEntry[record]->ID}: {$logEntry[record]->Title}"; } }, 'ignoreFields' => array('Imported'), 'valueRenderers' => array('StartTime' => function ($value) { return date('Y-m-d H:i:s', $value); }, 'EndTime' => function ($value) { return date('Y-m-d H:i:s', $value); }))); if ($logEntry['action'] == 'create') { $results['events']['created']++; } elseif ($logEntry['action'] == 'update') { $results['events']['updated']++; } if (!$pretend) { $Event->save(); } } if (!$pretend) { // delete events that came from this feed but weren't included this time \DB::nonQuery('DELETE FROM `%s` WHERE FeedID = %u AND Imported != "%s"', array(FeedEvent::$tableName, $Feed->ID, $nowString)); } $results['events']['deleted'] += \DB::affectedRows(); } // save job results $Job->Status = 'Completed'; $Job->Results = $results; if (!$pretend) { $Job->save(); } return true; }
public function destroy() { DB::nonQuery('DELETE FROM `%s` WHERE ContextClass = "%s" AND ContextID = %u AND CategoryID = %u', array(static::$tableName, DB::escape($ContextClass), $ContextID, $CategoryID)); return DB::affectedRows() > 0; }
/** * Clear all the files from a given collection's tree * * Warning: this method is designed to be called from SiteCollection::delete and will leave stale cache entries if called * on its own */ public static function deleteTree(SiteCollection $Collection) { DB::nonQuery('LOCK TABLES ' . static::$tableName . ' WRITE, ' . static::$tableName . ' AS f1 READ, ' . static::$tableName . ' AS f2 READ, ' . SiteCollection::$tableName . ' AS collections READ'); $positions = DB::oneRecord('SELECT PosLeft, PosRight FROM `%s` collections WHERE ID = %u', array(SiteCollection::$tableName, $Collection->ID)); DB::nonQuery('INSERT INTO `%1$s` (CollectionID, Handle, Status, AuthorID, AncestorID) SELECT f2.CollectionID, f2.Handle, "Deleted", %5$s, f2.ID FROM (SELECT MAX(f1.ID) AS ID FROM `%1$s` f1 WHERE CollectionID IN (SELECT collections.ID FROM `%2$s` collections WHERE PosLeft BETWEEN %3$u AND %4$u) AND Status != "Phantom" GROUP BY f1.Handle) AS lastestFiles LEFT JOIN `%1$s` f2 ON (f2.ID = lastestFiles.ID) WHERE f2.Status != "Deleted"', array(static::$tableName, SiteCollection::$tableName, $positions['PosLeft'], $positions['PosRight'], !empty($GLOBALS['Session']) && $GLOBALS['Session']->PersonID ? $GLOBALS['Session']->PersonID : 'NULL')); DB::nonQuery('UNLOCK TABLES'); }
<?php $GLOBALS['Session']->requireAccountLevel('Developer'); if ($_SERVER['REQUEST_METHOD'] == 'POST') { DB::nonQuery('DELETE FROM `%s` WHERE CollectionID IN (SELECT ID FROM `%s` WHERE Site != "Local")', array(SiteFile::$tableName, SiteCollection::$tableName)); apc_clear_cache('user'); die('Cleared ' . DB::affectedRows() . ' cached files'); } ?> <!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <title>Clear Parent Site Cache</title> <style> * { font-size: xx-large; text-align: center; } input { cursor: pointer; margin: 1em; } em strong { color: #c00; } </style> </head> <body>
public static function findFiles($filename, $useRegexp = false, $scope = null, $localOnly = false) { $collections = array(); if ($scope) { if (!is_array($scope)) { $scope = array($scope); } foreach ($scope as $scopeItem) { if (is_string($scopeItem)) { foreach (static::getCollectionLayers($scopeItem, $localOnly) as $collection) { $collections[] = $collection; } } elseif (is_a($scopeItem, 'SiteCollection')) { $collections[] = $scopeItem; } } } DB::nonQuery('LOCK TABLES ' . SiteFile::$tableName . ' f1 READ, ' . SiteFile::$tableName . ' f2 READ, ' . SiteCollection::$tableName . ' collections READ'); $collectionsQuery = sprintf('SELECT collections.ID FROM `%s` collections WHERE Status != "Deleted"', SiteCollection::$tableName); if (count($collections)) { $collectionsQuery .= sprintf(' AND ((%s))', implode(') OR (', array_map(function ($collection) { $positions = DB::oneRecord('SELECT PosLeft, PosRight FROM `%s` collections WHERE ID = %u', array(SiteCollection::$tableName, $collection->ID)); return sprintf('PosLeft BETWEEN %u AND %u', $positions['PosLeft'], $positions['PosRight']); }, $collections))); } $fileResults = DB::query('SELECT f2.* FROM (SELECT MAX(f1.ID) AS ID FROM `%1$s` f1 WHERE CollectionID IN (%2$s) AND Status != "Phantom" GROUP BY f1.Handle) AS lastestFiles LEFT JOIN `%1$s` f2 ON (f2.ID = lastestFiles.ID) WHERE f2.Status != "Deleted" AND f2.Handle %3$s "%4$s"', array(SiteFile::$tableName, $collectionsQuery, $useRegexp ? 'REGEXP' : '=', DB::escape($filename))); DB::nonQuery('UNLOCK TABLES'); $results = array(); while ($record = $fileResults->fetch_assoc()) { $fileNode = new SiteFile($record['Handle'], $record); $results[join('/', $fileNode->getFullPath(null, false))] = $fileNode; } return $results; }
<?php $newGroupRollType = 'enum(\'Member\',\'Administrator\',\'Owner\',\'Founder\')'; // skip conditions if (!static::tableExists('group_members')) { printf("Skipping migration because table `group_members` does not exist yet\n"); return static::STATUS_SKIPPED; } if (static::getColumnType('group_members', 'Role') == $newGroupRollType) { printf("Skipping migration because `Role` column already has correct type\n"); return static::STATUS_SKIPPED; } // migration DB::nonQuery('ALTER TABLE `group_members` CHANGE `Role` `Role` ' . $newGroupRollType . ' NOT NULL'); // done return static::STATUS_EXECUTED;
<?php $GLOBALS['Session']->requireAccountLevel('Developer'); if ($_SERVER['REQUEST_METHOD'] == 'POST') { DB::nonQuery('DELETE FROM `%s` WHERE CollectionID IN (SELECT ID FROM `%s` WHERE Site != "Local")', array(SiteFile::$tableName, SiteCollection::$tableName)); print 'Cleared ' . DB::affectedRows() . ' cached files<br>' . PHP_EOL; DB::nonQuery('DELETE FROM `%s` WHERE Site != "Local"', array(SiteCollection::$tableName)); die('Cleared ' . DB::affectedRows() . ' cached collections'); } ?> <!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <title>Clear Parent Site Cache + Nuke Collections</title> <style> * { font-size: xx-large; text-align: center; } input { cursor: pointer; margin: 1em; } em strong { color: #c00; } </style> </head>
<?php $newClassType = "enum('Media','PhotoMedia','AudioMedia','VideoMedia','PDFMedia')"; // skip conditions $skipped = true; if (!static::tableExists('media')) { printf("Skipping migration because table `media` does not exist yet\n"); return static::STATUS_SKIPPED; } // migration if (static::getColumnType('media', 'Class') != $newClassType) { print "Updating `Class` enum\n"; DB::nonQuery('ALTER TABLE `media` CHANGE `Class` `Class` ' . $newClassType . ' NOT NULL'); $skipped = false; } if (static::getColumnType('media', 'ContextClass') != 'varchar(255)') { print "Changing `ContextClass` to varchar\n"; DB::nonQuery('ALTER TABLE `media` CHANGE `ContextClass` `ContextClass` VARCHAR(255) NOT NULL'); $skipped = false; } if (static::getColumnType('media', 'MIMEType') != 'varchar(255)') { print "Changing `MIMEType` to varchar\n"; DB::nonQuery('ALTER TABLE `media` CHANGE `MIMEType` `MIMEType` VARCHAR(255) NOT NULL'); $skipped = false; } // done return $skipped ? static::STATUS_SKIPPED : static::STATUS_EXECUTED;
public static function handleRequest() { // TODO: try global handle lookup? // resolve URL in root $resolvedNode = false; $rootNode = static::getRootCollection('site-root'); // handle root request - default page if (empty(static::$pathStack[0]) && static::$defaultPage) { static::$pathStack[0] = static::$defaultPage; } // route request if (static::$pathStack[0] == 'emergence') { array_shift(static::$pathStack); return Emergence::handleRequest(); } elseif (static::$pathStack[0] == 'parent-refresh' && $_REQUEST['key'] == static::$controlKey) { DB::nonQuery('DELETE FROM `%s` WHERE CollectionID IN (SELECT ID FROM `%s` WHERE SiteID != %u)', array(SiteFile::$tableName, SiteCollection::$tableName, Site::getSiteID())); die('Cleared ' . DB::affectedRows() . ' cached files'); } else { $resolvedNode = $rootNode; $resolvedPath = array(); while ($handle = array_shift(static::$pathStack)) { $scriptHandle = substr($handle, -4) == '.php' ? $handle : $handle . '.php'; //printf('%s: (%s)/(%s) - %s<br>', $resolvedNode->Handle, $handle, implode('/',static::$pathStack), $scriptHandle); if ($resolvedNode && method_exists($resolvedNode, 'getChild') && (($childNode = $resolvedNode->getChild($handle)) || $scriptHandle && ($childNode = $resolvedNode->getChild($scriptHandle))) || ($childNode = Emergence::resolveFileFromParent('site-root', array_merge($resolvedPath, array($handle)))) || $scriptHandle && ($childNode = Emergence::resolveFileFromParent('site-root', array_merge($resolvedPath, array($scriptHandle))))) { $resolvedNode = $childNode; if (is_a($resolvedNode, 'SiteFile')) { break; } } else { $resolvedNode = false; //break; } $resolvedPath[] = $handle; } } if ($resolvedNode) { // create session if (static::$autoCreateSession && $resolvedNode->MIMEType == 'application/php') { $GLOBALS['Session'] = UserSession::getFromRequest(); } if (is_callable(static::$onRequestMapped)) { call_user_func(static::$onRequestMapped, $resolvedNode); } if ($resolvedNode->MIMEType == 'application/php') { require $resolvedNode->RealPath; exit; } elseif (!is_callable(array($resolvedNode, 'outputAsResponse'))) { //throw new Exception('Node does not support rendering'); static::respondNotFound(); } else { $resolvedNode->outputAsResponse(); } } else { static::respondNotFound(); } }
public static function delete($id) { DB::nonQuery('DELETE FROM `%s` WHERE CategoryID = %u', array(CategoryItem::$tableName, $id)); return parent::delete($id); }
public function delete() { // mark collection and all subcollections as deleted DB::nonQuery('UPDATE `%s` SET Status = "Deleted" WHERE PosLeft BETWEEN %u AND %u', array(static::$tableName, $this->PosLeft, $this->PosRight)); // TODO: mark files and all subfiles as deleted SiteFile::deleteTree($this); }
<?php $tableName = Gatekeeper\Transactions\PingTransaction::$tableName; // skip conditions if (!static::tableExists($tableName)) { printf("Skipping migration because table `%s` does not exist yet\n", $tableName); return static::STATUS_SKIPPED; } if (static::columnExists($tableName, 'TestPassed')) { printf("Skipping migration because column `%s`.`TestPassed` already exists\n", $tableName); return static::STATUS_SKIPPED; } // migration printf("Adding column `%s`.`%s`\n", $tableName, 'TestPassed'); DB::nonQuery('ALTER TABLE `%s` ADD `TestPassed` boolean NULL default NULL', $tableName); // done return static::STATUS_EXECUTED;
<?php use Laddr\MemberCheckin; $columnName = 'MeetupID'; $newType = 'varchar(255)'; // skip conditions if (!static::tableExists(MemberCheckin::$tableName)) { printf("Skipping migration because table `%s` does not exist yet\n", MemberCheckin::$tableName); return static::STATUS_SKIPPED; } if (static::getColumnType(MemberCheckin::$tableName, $columnName) == $newType) { printf("Column `%s`.`%s` is already type %s\n", MemberCheckin::$tableName, $columnName, $newType); return static::STATUS_SKIPPED; } // migration printf("Changing column `%s`.`%s` to type %s\n", MemberCheckin::$tableName, $columnName, $newType); DB::nonQuery('ALTER TABLE `%1$s` CHANGE `%2$s` `%2$s` %3$s NULL default NULL', [MemberCheckin::$tableName, $columnName, $newType]); // done return static::STATUS_EXECUTED;
<?php // skip conditions if (!static::tableExists('groups')) { printf("Skipping migration because table `groups` does not exist yet\n"); return static::STATUS_SKIPPED; } if (static::getColumnKey('groups', 'Left') == 'UNI') { print "Skipping because column `Left` in table `groups` is already unique\n"; return static::STATUS_SKIPPED; } // migration DB::nonQuery('ALTER TABLE groups ADD UNIQUE `Left` (`Left`)'); // done return static::STATUS_EXECUTED;
public static function deleteTree(SiteCollection $Collection) { DB::nonQuery('INSERT INTO `%1$s` (CollectionID, Handle, Status, AuthorID, AncestorID) SELECT f2.CollectionID, f2.Handle, "Deleted", %5$u, f2.ID FROM (SELECT MAX(f1.ID) AS ID FROM `%1$s` f1 WHERE CollectionID IN (SELECT collections.ID FROM `%2$s` collections WHERE PosLeft BETWEEN %3$u AND %4$u) AND Status != "Phantom" GROUP BY f1.Handle) AS lastestFiles LEFT JOIN `%1$s` f2 ON (f2.ID = lastestFiles.ID) WHERE f2.Status != "Deleted"', array(static::$tableName, SiteCollection::$tableName, $Collection->PosLeft, $Collection->PosRight, $GLOBALS['Session']->PersonID)); }
<?php // skip conditions if (!static::tableExists('tags')) { printf("Skipping migration because table `tags` does not exist yet\n"); return static::STATUS_SKIPPED; } if (static::columnExists('tags', 'Class')) { printf("Skipping migration because column `Class` in table `tags` already exists\n"); return static::STATUS_SKIPPED; } // migration print "Adding `Class` column to `tags` table\n"; DB::nonQuery('ALTER TABLE `tags` ADD `Class` ENUM("Tag") NOT NULL AFTER `ID`;'); // done return static::STATUS_EXECUTED;
<?php $tableName = Gatekeeper\Endpoints\Endpoint::$tableName; // skip conditions $skipped = true; if (!static::tableExists($tableName)) { printf("Skipping migration because table `%s` does not exist yet\n", $tableName); return static::STATUS_SKIPPED; } if (static::columnExists($tableName, 'KeySelfRegistration')) { printf("Skipping migration because column `%s`.`KeySelfRegistration` already exists\n", $tableName); return static::STATUS_SKIPPED; } // migration printf("Adding column `%s`.`%s`\n", $tableName, 'KeySelfRegistration'); DB::nonQuery('ALTER TABLE `%s` ADD `KeySelfRegistration` boolean NOT NULL default 0 AFTER KeyRequired', $tableName); // done return static::STATUS_EXECUTED;
<?php $tableName = Gatekeeper\Endpoints\Endpoint::$tableName; // skip conditions $skipped = true; if (!static::tableExists($tableName)) { printf("Skipping migration because table `%s` does not exist yet\n", $tableName); return static::STATUS_SKIPPED; } if (static::columnExists($tableName, 'Public')) { printf("Skipping migration because column `%s`.`Public` already exists\n", $tableName); return static::STATUS_SKIPPED; } // migration printf("Adding column `%s`.`%s`\n", $tableName, 'Public'); DB::nonQuery('ALTER TABLE `%s` ADD `Public` boolean NOT NULL default 0 AFTER AdminEmail', $tableName); // done return static::STATUS_EXECUTED;
<?php // skip conditions if (!static::tableExists(Tag::$tableName)) { printf("Skipping migration because table `%s` does not exist yet\n", Tag::$tableName); return static::STATUS_SKIPPED; } // migration printf("Removing prefixes from tag titles\n"); DB::nonQuery('UPDATE `%s` SET Title = SUBSTRING(Title, LOCATE(".", Title) + 1) WHERE Handle LIKE "%%.%%" AND Title LIKE "%%.%%" AND SUBSTRING_INDEX(Handle, ".", 1) = SUBSTRING_INDEX(Title, ".", 1)', [Tag::$tableName]); // done return static::STATUS_EXECUTED;
<?php $tableName = Gatekeeper\Endpoints\Endpoint::$tableName; // skip conditions $skipped = true; if (!static::tableExists($tableName)) { printf("Skipping migration because table `%s` does not exist yet\n", $tableName); return static::STATUS_SKIPPED; } // migration if (!static::columnExists($tableName, 'PingFrequency')) { printf("Adding column `%s`.`%s`\n", $tableName, 'PingFrequency'); DB::nonQuery('ALTER TABLE `%s` ADD `PingFrequency` int unsigned NULL default NULL', $tableName); $skipped = false; } if (!static::columnExists($tableName, 'PingURI')) { printf("Adding column `%s`.`%s`\n", $tableName, 'PingURI'); DB::nonQuery('ALTER TABLE `%s` ADD `PingURI` varchar(255) NULL default NULL', $tableName); $skipped = false; } if (!static::columnExists($tableName, 'PingTestPattern')) { printf("Adding column `%s`.`%s`\n", $tableName, 'PingTestPattern'); DB::nonQuery('ALTER TABLE `%s` ADD `PingTestPattern` varchar(255) NULL default NULL', $tableName); $skipped = false; } // done return $skipped ? static::STATUS_SKIPPED : static::STATUS_EXECUTED;
<?php $GLOBALS['Session']->requireAccountLevel('Developer'); header('Content-Type: text/plain'); // delete orphan collections DB::nonQuery('DELETE c3 FROM (SELECT c1.* FROM _e_file_collections c1 LEFT JOIN _e_file_collections c2 ON c2.ID = c1.ParentID WHERE c1.ParentID IS NOT NULL AND c2.ID IS NULL) orphan JOIN _e_file_collections c3 ON (c3.PosLeft BETWEEN orphan.PosLeft AND orphan.PosRight)'); printf("Deleted %u orphan collections\n", DB::affectedRows()); // delete orphan files DB::nonQuery('DELETE f FROM _e_files f LEFT JOIN _e_file_collections c ON c.ID = f.CollectionID WHERE c.ID IS NULL'); printf("Deleted %u orphan files\n", DB::affectedRows());
public static function delete($id) { DB::nonQuery('DELETE FROM `%s` WHERE `%s` = %u', array(static::$tableName, static::_cn('ID'), $id)); static::_invalidateRecordCaches($id); return DB::affectedRows() > 0; }
public function destroy() { DB::nonQuery('DELETE FROM `%s` WHERE `%s` = \'%s\' AND `%s` = %u AND `%s` = %u', array(static::$tableName, static::_cn('ContextClass'), $this->ContextClass, static::_cn('ContextID'), $this->ContextID, static::_cn('TagID'), $this->TagID)); return DB::affectedRows() > 0; }
public function delete() { // FIXME: check if status/handle unique combo already exists DB::nonQuery('LOCK TABLES ' . static::$tableName . ' WRITE'); $positions = DB::oneRecord('SELECT PosLeft, PosRight FROM `%s` WHERE ID = %u', array(static::$tableName, $this->ID)); // mark collection and all subcollections as deleted DB::nonQuery('UPDATE `%s` SET Status = "Deleted" WHERE PosLeft BETWEEN %u AND %u', array(static::$tableName, $positions['PosLeft'], $positions['PosRight'])); DB::nonQuery('UNLOCK TABLES'); // delete all files SiteFile::deleteTree($this); // clear caches static::clearCacheTree($this->_record); }
<?php namespace Slate\CBL; $newDemonstratedType = 'TIMESTAMP'; // skip conditions $skipped = true; if (!static::tableExists(Demonstration::$tableName)) { printf("Skipping migration because table `demonstrations` does not exist yet\n"); return static::STATUS_SKIPPED; } // migration if (static::getColumnType(Demonstration::$tableName, 'Demonstrated') != $newDemonstratedType) { print "Updating `Demonstrated` column type\n"; \DB::nonQuery("ALTER TABLE " . Demonstration::$tableName . " CHANGE COLUMN `Demonstrated` `Demonstrated` {$newDemonstratedType} NOT NULL"); $skipped = false; } // done return $skipped ? static::STATUS_SKIPPED : static::STATUS_EXECUTED;
<?php // skip conditions $skipped = true; if (!static::tableExists('groups')) { printf("Skipping migration because table `groups` does not exist yet\n"); return static::STATUS_SKIPPED; } // migration if (static::columnExists('groups', 'Data')) { print "Dropping `Data` column from `groups`\n"; DB::nonQuery('ALTER TABLE `groups` DROP `Data`'); $skipped = false; } if (!static::columnExists('groups', 'About')) { print "Adding `About` column to `groups`\n"; DB::nonQuery('ALTER TABLE `groups` ADD `About` text NULL default NULL'); $skipped = false; } // done return $skipped ? static::STATUS_SKIPPED : static::STATUS_EXECUTED;
public static function repairTable($tableName, $leftCol = 'Left', $rightCol = 'Right', $parentCol = 'ParentID') { // check for orphan collections first $orphanCollections = DB::allValues('ID', 'SELECT c1.ID FROM _e_file_collections c1 LEFT JOIN _e_file_collections c2 ON c2.ID = c1.ParentID WHERE c1.ParentID IS NOT NULL AND c2.ID IS NULL'); if (count($orphanCollections)) { throw new Exception('Cannot renest table, orphan collections found: ' . implode(',', $orphanCollections)); } // compile map $records = array(); $backlog = array(); $cursor = 1; $result = DB::query('SELECT ID, `%2$s` FROM `%1$s` ORDER BY `%2$s`, ID', array($tableName, $parentCol)); while (($record = $result->fetch_assoc()) || ($record = array_shift($backlog))) { if ($record[$parentCol]) { if (!($parent =& $records[$record[$parentCol]])) { // if parent not found yet, save to end of backlog and skip this record $backlog[] = $record; continue; } $record[$leftCol] = $parent[$rightCol]; $record[$rightCol] = $record[$leftCol] + 1; foreach ($records as &$bAccount) { if ($bAccount[$leftCol] > $record[$leftCol]) { $bAccount[$leftCol] += 2; } if ($bAccount[$rightCol] >= $record[$leftCol]) { $bAccount[$rightCol] += 2; } } $cursor += 2; } else { $record[$leftCol] = $cursor++; $record[$rightCol] = $cursor++; } $records[$record['ID']] = $record; } // write results DB::nonQuery('UPDATE `%s` SET `%s` = NULL, `%s` = NULL', array($tableName, $leftCol, $rightCol)); foreach ($records as $record) { DB::nonQuery('UPDATE `%s` SET `%s` = %u, `%s` = %u WHERE ID = %u', array($tableName, $leftCol, $record[$leftCol], $rightCol, $record[$rightCol], $record['ID'])); } return count($records); }
<?php // add columns to all history_ tables $tableNames = DB::allValues('TABLE_NAME', 'SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME LIKE "history_%%"'); print_r($tableNames); foreach ($tableNames as $tableName) { if (!static::columnExists($tableName, 'Modified')) { printf("Adding `Modified` column to `%s` table\n", $tableName); DB::nonQuery('ALTER TABLE `%s` ADD `Modified` timestamp NULL default NULL AFTER `CreatorID`', $tableName); } if (!static::columnExists($tableName, 'ModifierID')) { printf("Adding `ModifierID` column to `%s` table\n", $tableName); DB::nonQuery('ALTER TABLE `%s` ADD `ModifierID` int unsigned NULL default NULL AFTER `Modified`', $tableName); } $tableName = substr($tableName, 8); if (!static::columnExists($tableName, 'Modified')) { printf("Adding `Modified` column to `%s` table\n", $tableName); DB::nonQuery('ALTER TABLE `%s` ADD `Modified` timestamp NULL default NULL AFTER `CreatorID`', $tableName); } if (!static::columnExists($tableName, 'ModifierID')) { printf("Adding `ModifierID` column to `%s` table\n", $tableName); DB::nonQuery('ALTER TABLE `%s` ADD `ModifierID` int unsigned NULL default NULL AFTER `Modified`', $tableName); } } // done return static::STATUS_EXECUTED;