PHP Gdn::Database方法代码示例

 protected function _CachePostCounts(&$Sender)
 {
     $Discussion = $Sender->Data('Discussion');
     $Comments = $Sender->Data('CommentData');
     $UserIDList = array();
     if ($Discussion) {
         $UserIDList[$Discussion->InsertUserID] = 1;
     }
     if ($Comments && $Comments->NumRows()) {
         $Comments->DataSeek(-1);
         while ($Comment = $Comments->NextRow()) {
             $UserIDList[$Comment->InsertUserID] = 1;
         }
     }
     $UserPostCounts = array();
     if (sizeof($UserIDList)) {
         /*
                  $PostCounts = Gdn::SQL()
                     ->Select('u.UserID')
                     ->Select('u.CountComments + u.CountDiscussions', FALSE, 'PostCount')
                     ->From('User u')
                     ->WhereIn('UserID', array_keys($UserIDList))
                     ->Get();
         */
         $PostCounts = Gdn::Database()->Query(sprintf("\n            SELECT \n               u.UserID,\n               COALESCE(u.CountComments,0) + COALESCE(u.CountDiscussions,0) AS PostCount \n            FROM GDN_User u \n            WHERE UserID IN (%s)", implode(",", array_keys($UserIDList))));
         $PostCounts->DataSeek(-1);
         while ($UserPostCount = $PostCounts->NextRow()) {
             $UserPostCounts[$UserPostCount->UserID] = $UserPostCount->PostCount;
         }
     }
     $Sender->SetData('Plugin-PostCount-Counts', $UserPostCounts);
 }

   public function Export() {
      $this->Permission('Garden.Export'); // This permission doesn't exist, so only users with Admin == '1' will succeed.

      set_time_limit(60*2);
      $Ex = new ExportModel();
      $Ex->PDO(Gdn::Database()->Connection());
      $Ex->Prefix = Gdn::Database()->DatabasePrefix;

      /// 2. Do the export. ///
      $Ex->UseCompression = TRUE;
      $Ex->BeginExport(PATH_ROOT.DS.'uploads'.DS.'export '.date('Y-m-d His').'.txt.gz', 'Vanilla 2.0');

      $Ex->ExportTable('User', 'select * from :_User'); // ":_" will be replace by database prefix
      $Ex->ExportTable('Role', 'select * from :_Role');
      $Ex->ExportTable('UserRole', 'select * from :_UserRole');

      $Ex->ExportTable('Category', 'select * from :_Category');
      $Ex->ExportTable('Discussion', 'select * from :_Discussion');
      $Ex->ExportTable('Comment', 'select * from :_Comment');

      $Ex->ExportTable('Conversation', 'select * from :_Conversation');
      $Ex->ExportTable('UserConversation', 'select * from :_UserConversation');
      $Ex->ExportTable('ConversationMessage', 'select * from :_ConversationMessage');

      $Ex->EndExport();
   }

 public function Authenticate()
 {
     $ForeignIdentityUrl = C('Garden.Authenticator.AuthenticateURL');
     if (!$ForeignIdentityUrl) {
         return FALSE;
     }
     try {
         $Response = $this->_GetForeignCredentials($ForeignIdentityUrl);
         if (!$Response) {
             throw new Exception();
         }
         $SQL = Gdn::Database()->SQL();
         $Provider = $SQL->Select('uap.AuthenticationKey, uap.AssociationSecret')->From('UserAuthenticationProvider uap')->Get()->FirstRow(DATASET_TYPE_ARRAY);
         if (!$Provider) {
             throw new Exception();
         }
         // Got a response from the remote identity provider
         $UserEmail = ArrayValue('Email', $Response);
         $UserName = ArrayValue('Name', $Response);
         $UserName = trim(preg_replace('/[^a-z0-9-]+/i', '', $UserName));
         $AuthResponse = $this->ProcessAuthorizedRequest($Provider['AuthenticationKey'], $UserEmail, $UserName);
         if ($AuthResponse == Gdn_Authenticator::AUTH_SUCCESS) {
             Gdn::Request()->WithRoute('DefaultController');
         } elseif ($AuthResponse == Gdn_Authenticator::AUTH_PARTIAL) {
             Redirect(Url('/entry/handshake/proxy', TRUE), 302);
         } else {
             Gdn::Request()->WithRoute('DefaultController');
             throw new Exception('authentication failed');
         }
     } catch (Exception $e) {
         // Fallback to defer checking until the next session
         $this->SetIdentity(-1, FALSE);
     }
 }

 public static function GetCountSQL($Aggregate, $ParentTable, $ChildTable, $ParentColumnName = '', $ChildColumnName = '', $ParentJoinColumn = '', $ChildJoinColumn = '')
 {
     if (!$ParentColumnName) {
         switch (strtolower($Aggregate)) {
             case 'count':
                 $ParentColumnName = "Count{$ChildTable}s";
                 break;
             case 'max':
                 $ParentColumnName = "Last{$ChildTable}ID";
                 break;
             case 'min':
                 $ParentColumnName = "First{$ChildTable}ID";
                 break;
             case 'sum':
                 $ParentColumnName = "Sum{$ChildTable}s";
                 break;
         }
     }
     if (!$ChildColumnName) {
         $ChildColumnName = $ChildTable . 'ID';
     }
     if (!$ParentJoinColumn) {
         $ParentJoinColumn = $ParentTable . 'ID';
     }
     if (!$ChildJoinColumn) {
         $ChildJoinColumn = $ParentJoinColumn;
     }
     $Result = "update :_{$ParentTable} p\n                  set p.{$ParentColumnName} = (\n                     select {$Aggregate}(c.{$ChildColumnName})\n                     from :_{$ChildTable} c\n                     where p.{$ParentJoinColumn} = c.{$ChildJoinColumn})";
     $Result = str_replace(':_', Gdn::Database()->DatabasePrefix, $Result);
     return $Result;
 }

 protected function _GetData()
 {
     $Px = Gdn::Database()->DatabasePrefix;
     $Sql = "show table status where Name in ('{$Px}User', '{$Px}Discussion', '{$Px}Comment')";
     $Result = array('User' => 0, 'Discussion' => 0, 'Comment' => 0);
     foreach ($Result as $Name => $Value) {
         $Result[$Name] = $this->GetCount($Name);
     }
     $this->SetData('Totals', $Result);
 }

 /**
  * The constructor for this class. Automatically fills $this->ClassName.
  *
  * @param string $Database
  * @todo $Database needs a description.
  */
 public function __construct($Database = NULL)
 {
     parent::__construct();
     if (is_null($Database)) {
         $this->Database = Gdn::Database();
     } else {
         $this->Database = $Database;
     }
     $this->DatabasePrefix($this->Database->DatabasePrefix);
     $this->Reset();
 }

 /**
  * The constructor for this class. Automatically fills $this->ClassName.
  *
  * @param string $Database
  * @todo $Database needs a description.
  */
 public function __construct($Database = NULL)
 {
     $this->ClassName = get_class($this);
     if (is_null($Database)) {
         $this->Database = Gdn::Database();
     } else {
         $this->Database = $Database;
     }
     $this->DatabasePrefix($this->Database->DatabasePrefix);
     $this->_Reset();
 }

 public function LoadTable($Tablename, $Path)
 {
     if (!array_key_exists($Tablename, $this->Structures)) {
         throw new Exception("The table \"{$Tablename}\" is not a valid import table.");
     }
     $Path = Gdn::Database()->Connection()->quote($Path);
     $Tablename = Gdn::Database()->DatabasePrefix . self::TABLE_PREFIX . $Tablename;
     Gdn::Database()->Query("truncate table {$Tablename};");
     $Sql = "load data infile {$Path} into table {$Tablename}\r\ncharacter set utf8\r\ncolumns terminated by ','\r\noptionally enclosed by '\"'\r\nescaped by '\\\\'\r\nlines terminated by '\\n'\r\nignore 1 lines;";
     Gdn::Database()->Query($Sql);
 }

 /**
  * The constructor for this class. Automatically fills $this->ClassName.
  *
  * @param string $Database
  * @todo $Database needs a description.
  */
 public function __construct($Database = NULL)
 {
     $this->ClassName = get_class($this);
     if (is_null($Database)) {
         $this->Database = Gdn::Database();
     } else {
         $this->Database = $Database;
     }
     $this->DatabasePrefix($this->Database->DatabasePrefix);
     $this->_TableName = '';
     $this->_Columns = array();
 }

 public function Base_AfterBody_Handler($Sender)
 {
     $Session = Gdn::Session();
     if (!Debug() && !$Session->CheckPermission('Plugins.Debugger.View')) {
         return;
     }
     if (!$Sender->Head) {
         $Sender->Head = new HeadModule($Sender);
     }
     $Sender->Head->AddCss('/plugins/Debugger/style.css');
     //$Session = Gdn::Session();
     //if ($Session->CheckPermission('Plugins.Debugger.View')) {
     $String = '<div id="Sql" class="DebugInfo">';
     $String .= '<h2>' . T('Debug Information') . '</h2>';
     // Add the canonical Url.
     if (method_exists($Sender, 'CanonicalUrl')) {
         $CanonicalUrl = htmlspecialchars($Sender->CanonicalUrl(), ENT_COMPAT, 'UTF-8');
         $String .= '<div class="CanonicalUrl"><b>' . T('Canonical Url') . "</b>: <a href=\"{$CanonicalUrl}\">{$CanonicalUrl}</a></div>";
     }
     $Database = Gdn::Database();
     $SQL = $Database->SQL();
     if (!is_null($Database)) {
         $Queries = $Database->Queries();
         $QueryTimes = $Database->QueryTimes();
         $String .= '<h3>' . count($Queries) . ' queries in ' . $Database->ExecutionTime() . 's</h3>';
         foreach ($Queries as $Key => $QueryInfo) {
             $Query = $QueryInfo['Sql'];
             // this is a bit of a kludge. I found that the regex below would mess up when there were incremented named parameters. Ie. it would replace :Param before :Param0, which ended up with some values like "'4'0".
             if (isset($QueryInfo['Parameters']) && is_array($QueryInfo['Parameters'])) {
                 $tmp = $QueryInfo['Parameters'];
                 $Query = $SQL->ApplyParameters($Query, $tmp);
             }
             $String .= $QueryInfo['Method'] . '<small>' . @number_format($QueryTimes[$Key], 6) . 's</small>' . (isset($QueryInfo['Cache']) ? '<div><b>Cache:</b> ' . var_export($QueryInfo['Cache'], TRUE) . '</div>' : '') . '<pre>' . htmlspecialchars($Query) . ';</pre>';
         }
     }
     $String .= '<h3>Controller Data</h3><pre>';
     $String .= self::FormatData($Sender->Data);
     $String .= '</pre>';
     global $Start;
     $String .= '<h3>Page completed in ' . round(Now() - $_SERVER['REQUEST_TIME'], 4) . 's</h3>';
     /*
              <div>
      <strong>Application:</strong> ' . $Sender->ApplicationFolder . ';
      <strong>Controller:</strong> ' . $Sender->ClassName . ';
      <strong>Method:</strong> ' . $Sender->RequestMethod . ';
              </div>
           </div>';
     */
     $String .= '</div>';
     echo $String;
     //}
 }

 /**
  * Set or setore database prefix (default empty).
  * Allow use SqlDriver class for building queries for other databases.
  * https://github.com/vanillaforums/Garden/pull/1266/files
  * 
  * @param mixed $Px		New prefix
  * @return NULL.
  */
 function DatabasePrefix($Px = '')
 {
     static $ConfigDatabasePrefix;
     static $Count = 0;
     $Count++;
     $Database = Gdn::Database();
     if ($ConfigDatabasePrefix === Null) {
         $ConfigDatabasePrefix = $Database->DatabasePrefix;
     }
     if ($Count & 1) {
         $Database->DatabasePrefix = $Px;
     } else {
         $Database->DatabasePrefix = $ConfigDatabasePrefix;
     }
 }

 public function Setup()
 {
     // Got Setup?
     $Database = Gdn::Database();
     $Config = Gdn::Factory(Gdn::AliasConfig);
     $Drop = C('Skeleton.Version') === FALSE ? TRUE : FALSE;
     $Explicit = TRUE;
     $Validation = new Gdn_Validation();
     // This is going to be needed by structure.php to validate permission names
     include PATH_APPLICATIONS . DS . 'skeleton' . DS . 'settings' . DS . 'structure.php';
     $ApplicationInfo = array();
     include CombinePaths(array(PATH_APPLICATIONS . DS . 'skeleton' . DS . 'settings' . DS . 'about.php'));
     $Version = ArrayValue('Version', ArrayValue('Skeleton', $ApplicationInfo, array()), 'Undefined');
     SaveToConfig('Skeleton.Version', $Version);
 }

 public function Base_Render_Before($Sender)
 {
     $Session = Gdn::Session();
     if (!$Session->CheckPermission('Garden.Settings.Manage')) {
         return;
     }
     if (!$Sender->Head) {
         $Sender->Head = new HeadModule($Sender);
     }
     $Sender->Head->AddCss('/plugins/Debugger/style.css');
     //$Session = Gdn::Session();
     //if ($Session->CheckPermission('Plugins.Debugger.View')) {
     $String = '<div id="Sql">';
     $Database = Gdn::Database();
     if (!is_null($Database)) {
         $Queries = $Database->Queries();
         $QueryTimes = $Database->QueryTimes();
         $String .= '<h3>' . count($Queries) . ' queries in ' . $Database->ExecutionTime() . 's</h3>';
         foreach ($Queries as $Key => $QueryInfo) {
             $Query = $QueryInfo['Sql'];
             // this is a bit of a kludge. I found that the regex below would mess up when there were incremented named parameters. Ie. it would replace :Param before :Param0, which ended up with some values like "'4'0".
             if (isset($QueryInfo['Parameters']) && is_array($QueryInfo['Parameters'])) {
                 $tmp = $QueryInfo['Parameters'];
                 arsort($tmp);
                 foreach ($tmp as $Name => $Parameter) {
                     $Pattern = '/(.+)(' . $Name . ')([\\W\\s]*)(.*)/';
                     $Replacement = "\$1'" . htmlentities($Parameter, ENT_COMPAT, 'UTF-8') . "'\$3\$4";
                     $Query = preg_replace($Pattern, $Replacement, $Query);
                 }
             }
             $String .= '<div>' . $QueryInfo['Method'] . '</div><pre>' . $Query . ';</pre><small>' . number_format($QueryTimes[$Key], 6) . 's</small>';
         }
     }
     global $Start;
     $String .= '<h3>Page completed in ' . round(Now() - $_SERVER['REQUEST_TIME'], 4) . 's</h3>';
     /*
                 <div>
      <strong>Application:</strong> ' . $Sender->ApplicationFolder . ';
      <strong>Controller:</strong> ' . $Sender->ClassName . ';
      <strong>Method:</strong> ' . $Sender->RequestMethod . ';
                 </div>
              </div>';
     */
     $String .= '</div>';
     $Sender->AddAsset('Foot', $String);
     //}
 }

 public function Export()
 {
     $Ex = $this->ExportModel;
     $Ex->PDO(Gdn::Database()->Connection());
     $Ex->Prefix = Gdn::Database()->DatabasePrefix;
     /// 2. Do the export. ///
     $Ex->UseCompression = TRUE;
     $Ex->BeginExport(PATH_ROOT . DS . 'uploads' . DS . 'export ' . date('Y-m-d His') . '.txt.gz', 'Vanilla 2.0');
     $Ex->ExportTable('User', 'select * from :_User');
     // ":_" will be replace by database prefix
     $Ex->ExportTable('Role', 'select * from :_Role');
     $Ex->ExportTable('UserRole', 'select * from :_UserRole');
     $Ex->ExportTable('Category', 'select * from :_Category');
     $Ex->ExportTable('Discussion', 'select * from :_Discussion');
     $Ex->ExportTable('Comment', 'select * from :_Comment');
     $Ex->EndExport();
 }

 /**
  * Get the number of comments inserted since the given timestamp.
  *
  * @since 1.0
  * @access public
  */
 public function GetCommentCountSince($DiscussionID, $DateAllViewed)
 {
     // Only for members
     $Session = Gdn::Session();
     if (!$Session->IsValid()) {
         return;
     }
     // Validate DiscussionID
     $DiscussionID = (int) $DiscussionID;
     if (!$DiscussionID) {
         throw new Exception('A valid DiscussionID is required in GetCommentCountSince.');
     }
     // Prep DB
     $Database = Gdn::Database();
     $SQL = $Database->SQL();
     // Get new comment count
     return $SQL->From('Comment c')->Where('DiscussionID', $DiscussionID)->Where('DateInserted >', Gdn_Format::ToDateTime($DateAllViewed))->GetCount();
 }