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;
$pageData['currentMeetup'] = $currentMeetup;
$pageData['nextMeetup'] = $nextMeetup;
$pageData['futureMeetups'] = $meetups;
// projects
$pageData['projectsTotal'] = Laddr\Project::getCount();
$pageData['projectsTags']['byTech'] = TagItem::getTagsSummary(array('tagConditions' => array('Handle LIKE "tech.%"'), 'itemConditions' => array('ContextClass' => Laddr\Project::getStaticRootClass())));
$pageData['projectsTags']['byTopic'] = TagItem::getTagsSummary(array('tagConditions' => array('Handle LIKE "topic.%"'), 'itemConditions' => array('ContextClass' => Laddr\Project::getStaticRootClass())));
$pageData['projectsTags']['byEvent'] = TagItem::getTagsSummary(array('tagConditions' => array('Handle LIKE "event.%"'), 'itemConditions' => array('ContextClass' => Laddr\Project::getStaticRootClass())));
$pageData['projectsStages'] = Laddr\Project::getStagesSummary();
// members
$pageData['membersTotal'] = Emergence\People\Person::getCount();
$pageData['membersTags']['byTech'] = TagItem::getTagsSummary(array('tagConditions' => array('Handle LIKE "tech.%"'), 'itemConditions' => array('ContextClass' => Emergence\People\Person::getStaticRootClass())));
$pageData['membersTags']['byTopic'] = TagItem::getTagsSummary(array('tagConditions' => array('Handle LIKE "topic.%"'), 'itemConditions' => array('ContextClass' => Emergence\People\Person::getStaticRootClass())));
// build activity stream
if (!($pageData['activity'] = Cache::fetch('home-activity'))) {
$existingTables = \DB::allValues('table_name', 'SELECT table_name FROM information_schema.TABLES WHERE TABLE_SCHEMA = SCHEMA()');
$activityQueries = [];
if (in_array(Emergence\CMS\AbstractContent::$tableName, $existingTables)) {
$activityQueries[] = sprintf('SELECT' . ' ID, Class, Published AS Timestamp' . ' FROM `%s`' . ' WHERE' . ' Class = "%s" AND' . ' Visibility = "Public" AND' . ' Status = "Published" AND' . ' (Published IS NULL OR Published <= CURRENT_TIMESTAMP)', Emergence\CMS\AbstractContent::$tableName, DB::escape(Emergence\CMS\BlogPost::class));
}
if (in_array(Laddr\ProjectUpdate::$tableName, $existingTables)) {
$activityQueries[] = sprintf('SELECT ID, Class, Created AS Timestamp FROM `%s`', Laddr\ProjectUpdate::$tableName);
}
if (in_array(Laddr\ProjectBuzz::$tableName, $existingTables)) {
$activityQueries[] = sprintf('SELECT ID, Class, Published AS Timestamp FROM `%s`', Laddr\ProjectBuzz::$tableName);
}
if (count($activityQueries)) {
$pageData['activity'] = array_map(function ($result) {
return $result['Class']::getByID($result['ID']);
}, DB::allRecords(implode(' UNION ', $activityQueries) . ' ORDER BY Timestamp DESC LIMIT 10'));
} else {
public static function getAllPrefixes()
{
return DB::allValues('Handle', 'SELECT DISTINCT(SUBSTRING_INDEX(tags.Handle, ".", 1 )) AS Handle FROM `%s` tags WHERE Handle LIKE "%%.%%"', array(static::$tableName));
}