PHP DB::nonQuery方法代码示例

 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;