This class is HEAVILY inspired by and, in places, flat out copied from
CodeIgniter (http://www.codeigniter.com). My hat is off to them.
/** Add the sql to perform a search.
*
* @param Gdn_SQLDriver $Sql
* @param string $Columns a comma seperated list of columns to search on.
*/
public function addMatchSql($Sql, $Columns, $LikeRelavenceColumn = '')
{
if ($this->_SearchMode == 'like') {
if ($LikeRelavenceColumn) {
$Sql->select($LikeRelavenceColumn, '', 'Relavence');
} else {
$Sql->select(1, '', 'Relavence');
}
$Sql->beginWhereGroup();
$ColumnsArray = explode(',', $Columns);
$First = true;
foreach ($ColumnsArray as $Column) {
$Column = trim($Column);
$Param = $this->Parameter();
if ($First) {
$Sql->where("{$Column} like {$Param}", null, false, false);
$First = false;
} else {
$Sql->orWhere("{$Column} like {$Param}", null, false, false);
}
}
$Sql->endWhereGroup();
} else {
$Boolean = $this->_SearchMode == 'boolean' ? ' in boolean mode' : '';
$Param = $this->Parameter();
$Sql->select($Columns, "match(%s) against({$Param}{$Boolean})", 'Relavence');
$Param = $this->Parameter();
$Sql->where("match({$Columns}) against ({$Param}{$Boolean})", null, false, false);
}
}
/** Add the sql to perform a search.
*
* @param Gdn_SQLDriver $Sql
* @param string $Columns a comma seperated list of columns to search on.
*/
public function AddMatchSql($Sql, $Columns, $LikeRelavenceColumn = '')
{
if ($this->_SearchMode == 'like') {
if ($LikeRelavenceColumn) {
$Sql->Select($LikeRelavenceColumn, '', 'Relavence');
} else {
$Sql->Select(1, '', 'Relavence');
}
$Sql->BeginWhereGroup();
$ColumnsArray = explode(',', $Columns);
$First = TRUE;
foreach ($ColumnsArray as $Column) {
$Column = trim($Column);
$Param = $this->Parameter();
if ($First) {
$Sql->Where("{$Column} like {$Param}", NULL, FALSE, FALSE);
$First = FALSE;
} else {
$Sql->OrWhere("{$Column} like {$Param}", NULL, FALSE, FALSE);
}
}
$Sql->EndWhereGroup();
} else {
$Boolean = $this->_SearchMode == 'boolean' ? ' in boolean mode' : '';
$Param = $this->Parameter();
$Sql->Select($Columns, "match(%s) against({$Param}{$Boolean})", 'Relavence');
$Param = $this->Parameter();
$Sql->Where("match({$Columns}) against ({$Param}{$Boolean})", NULL, FALSE, FALSE);
}
}
public function SetProperty($RowID, $Property, $ForceValue = FALSE)
{
if (!isset($this->Schema)) {
$this->DefineSchema();
}
$PrimaryKey = $this->PrimaryKey;
if ($ForceValue !== FALSE) {
$Value = $ForceValue;
} else {
$Row = $this->GetID($RowID);
$Value = $Row->{$Property} == '1' ? '0' : '1';
}
$this->SQL->Update($this->Name)->Set($Property, $Value)->Where($PrimaryKey, $RowID)->Put();
return $Value;
}
/**
*
* @param Gdn_SQLDriver $Sql
*/
protected function _SetTagSql($Sql, $Tag, &$Limit, &$Offset = 0, $Op = 'or')
{
$SortField = 'd.DateLastComment';
$SortDirection = 'desc';
$TagSql = clone Gdn::Sql();
if ($DateFrom = Gdn::Request()->Get('DateFrom')) {
// Find the discussion ID of the first discussion created on or after the date from.
$DiscussionIDFrom = $TagSql->GetWhere('Discussion', array('DateInserted >= ' => $DateFrom), 'DiscussionID', 'asc', 1)->Value('DiscussionID');
$SortField = 'd.DiscussionID';
}
$Tags = array_map('trim', explode(',', $Tag));
$TagIDs = $TagSql->Select('TagID')->From('Tag')->WhereIn('Name', $Tags)->Get()->ResultArray();
$TagIDs = ConsolidateArrayValuesByKey($TagIDs, 'TagID');
if ($Op == 'and' && count($Tags) > 1) {
$DiscussionIDs = $TagSql->Select('DiscussionID')->Select('TagID', 'count', 'CountTags')->From('TagDiscussion')->WhereIn('TagID', $TagIDs)->GroupBy('DiscussionID')->Having('CountTags >=', count($Tags))->Limit($Limit, $Offset)->OrderBy('DiscussionID', 'desc')->Get()->ResultArray();
$Limit = '';
$Offset = 0;
$DiscussionIDs = ConsolidateArrayValuesByKey($DiscussionIDs, 'DiscussionID');
$Sql->WhereIn('d.DiscussionID', $DiscussionIDs);
$SortField = 'd.DiscussionID';
} else {
$Sql->Join('TagDiscussion td', 'd.DiscussionID = td.DiscussionID')->Limit($Limit, $Offset)->WhereIn('td.TagID', $TagIDs);
if ($Op == 'and') {
$SortField = 'd.DiscussionID';
}
}
// Set up the sort field and direction.
SaveToConfig(array('Vanilla.Discussions.SortField' => $SortField, 'Vanilla.Discussions.SortDirection' => $SortDirection), '', FALSE);
}
/**
* Joins the query to a permission junction table and limits the results accordingly.
*
* @param Gdn_SQLDriver $SQL The SQL driver to add the permission to.
* @param mixed $Permissions The permission name (or array of names) to use when limiting the query.
* @param string $ForeignAlias The alias of the table to join to (ie. Category).
* @param string $ForeignColumn The primary key column name of $JunctionTable (ie. CategoryID).
* @param string $JunctionTable
* @param string $JunctionColumn
*/
public function SQLPermission($SQL, $Permissions, $ForeignAlias, $ForeignColumn, $JunctionTable = '', $JunctionColumn = '')
{
$Session = Gdn::Session();
// Figure out the junction table if necessary.
if (!$JunctionTable && StringEndsWith($ForeignColumn, 'ID')) {
$JunctionTable = substr($ForeignColumn, 0, -2);
}
// Check to see if the permission is disabled.
if (C('Garden.Permission.Disabled.' . $JunctionTable)) {
if (!$Session->CheckPermission($Permissions)) {
$SQL->Where('1', '0', FALSE, FALSE);
}
} elseif ($Session->UserID <= 0 || is_object($Session->User) && $Session->User->Admin != '1') {
$SQL->Distinct()->Join('Permission _p', '_p.JunctionID = ' . $ForeignAlias . '.' . $ForeignColumn, 'inner')->Join('UserRole _ur', '_p.RoleID = _ur.RoleID', 'inner')->BeginWhereGroup()->Where('_ur.UserID', $Session->UserID);
if (!is_array($Permissions)) {
$Permissions = array($Permissions);
}
$SQL->BeginWhereGroup();
foreach ($Permissions as $Permission) {
$SQL->Where('_p.`' . $Permission . '`', 1);
}
$SQL->EndWhereGroup();
} else {
// Force this method to play nice in case it is used in an or clause
// (ie. it returns true in a sql sense by doing 1 = 1)
$SQL->Where('1', '1', FALSE, FALSE);
}
return $SQL;
}
public function SaveToSerializedColumn($Column, $RowID, $Name, $Value = '')
{
if (!isset($this->Schema)) {
$this->DefineSchema();
}
// TODO: need to be sure that $this->PrimaryKey is only one primary key
$FieldName = $this->PrimaryKey;
// Load the existing values
$Row = $this->SQL->Select($Column)->From($this->Name)->Where($FieldName, $RowID)->Get()->FirstRow();
if (!$Row) {
throw new Exception(T('ErrorRecordNotFound'));
}
$Values = Gdn_Format::Unserialize($Row->{$Column});
if (is_string($Values) && $Values != '') {
throw new Exception(T('Serialized column failed to be unserialized.'));
}
if (!is_array($Values)) {
$Values = array();
}
if (!is_array($Name)) {
$Name = array($Name => $Value);
}
// Assign the new value(s)
$Values = Gdn_Format::Serialize(array_merge($Values, $Name));
// Save the values back to the db
return $this->SQL->From($this->Name)->Where($FieldName, $RowID)->Set($Column, $Values)->Put();
}
/**
* Joins the query to a permission junction table and limits the results
* accordingly.
*
* @param Gdn_SQLDriver $SQL The SQL driver to add the permission to
* @param string $JunctionTable The table to join to (ie. Category)
* @param string $JunctionColumn The primary key column name of $JunctionTable (ie. CategoryID).
* @param mixed $Permission The permission name (or array of names) to use when limiting the query.
*/
public function SQLPermission($SQL, $JunctionTableAlias, $JunctionColumn, $Permissions)
{
$Session = Gdn::Session();
if ($Session->UserID <= 0 || is_object($Session->User) && $Session->User->Admin != '1') {
$SQL->Distinct()->Join('Permission _p', '_p.JunctionID = ' . $JunctionTableAlias . '.' . $JunctionColumn, 'inner')->Join('UserRole _ur', '_p.RoleID = _ur.RoleID', 'inner')->BeginWhereGroup()->Where('_ur.UserID', $Session->UserID);
if (!is_array($Permissions)) {
$Permissions = array($Permissions);
}
$SQL->BeginWhereGroup();
foreach ($Permissions as $Permission) {
$SQL->Where('_p.`' . $Permission . '`', 1);
}
$SQL->EndWhereGroup();
} else {
// Force this method to play nice in case it is used in an or clause
// (ie. it returns true in a sql sense by doing 1 = 1)
$SQL->Where('1', '1', FALSE, FALSE);
}
return $SQL;
}
/**
* @param unknown_type $Where
* @param unknown_type $OrderFields
* @param unknown_type $OrderDirection
* @param unknown_type $Limit
* @param unknown_type $Offset
* @return unknown
* @todo add doc
*/
public function GetWhere($Where = FALSE, $OrderFields = '', $OrderDirection = 'asc', $Limit = FALSE, $Offset = FALSE)
{
return $this->SQL->GetWhere($this->Name, $Where, $OrderFields, $OrderDirection, $Limit, $Offset);
}
/**
* Used by $this->stash() to create & manage sessions for users & guests.
*
* This is a stop-gap solution until full session management for users &
* guests can be implemented.
*
* @param Gdn_SQLDriver $sql Local clone of the sql driver.
* @param string $valueToStash The value of the stash to set.
*
* @return bool|Gdn_DataSet Current session.
*/
private function getStashSession($sql, $valueToStash)
{
$cookieName = c('Garden.Cookie.Name', 'Vanilla');
$name = $cookieName . '-sid';
// Grab the entire session record.
$sessionID = val($name, $_COOKIE, '');
// If there is no session, and no value for saving, return.
if ($sessionID == '' && $valueToStash == '') {
return false;
}
$session = $sql->select()->from('Session')->where('SessionID', $sessionID)->get()->firstRow();
if (!$session) {
$sessionID = betterRandomString(32);
$transientKey = substr(md5(mt_rand()), 0, 11) . '!';
// Save the session information to the database.
$sql->insert('Session', ['SessionID' => $sessionID, 'UserID' => Gdn::session()->UserID, 'TransientKey' => $transientKey, 'DateInserted' => Gdn_Format::toDateTime(), 'DateUpdated' => Gdn_Format::toDateTime()]);
trace("Inserting session stash {$sessionID}");
$session = $sql->select()->from('Session')->where('SessionID', $sessionID)->get()->firstRow();
// Save a session cookie.
$path = c('Garden.Cookie.Path', '/');
$domain = c('Garden.Cookie.Domain', '');
$expire = 0;
// If the domain being set is completely incompatible with the
// current domain then make the domain work.
$currentHost = Gdn::request()->host();
if (!stringEndsWith($currentHost, trim($domain, '.'))) {
$domain = '';
}
safeCookie($name, $sessionID, $expire, $path, $domain);
$_COOKIE[$name] = $sessionID;
}
$session->Attributes = dbdecode($session->Attributes);
if (!$session->Attributes) {
$session->Attributes = [];
}
return $session;
}
/**
*
*
* @param Gdn_SQLDriver $Sql
*/
public function setTagSql($Sql, $Tag, &$Limit, &$Offset = 0, $Op = 'or')
{
$SortField = 'd.DateLastComment';
$SortDirection = 'desc';
$TagSql = clone Gdn::sql();
if ($DateFrom = Gdn::request()->get('DateFrom')) {
// Find the discussion ID of the first discussion created on or after the date from.
$DiscussionIDFrom = $TagSql->getWhere('Discussion', array('DateInserted >= ' => $DateFrom), 'DiscussionID', 'asc', 1)->value('DiscussionID');
$SortField = 'd.DiscussionID';
}
if (!is_array($Tag)) {
$Tags = array_map('trim', explode(',', $Tag));
}
$TagIDs = $TagSql->select('TagID')->from('Tag')->whereIn('Name', $Tags)->get()->resultArray();
$TagIDs = array_column($TagIDs, 'TagID');
if ($Op == 'and' && count($Tags) > 1) {
$DiscussionIDs = $TagSql->select('DiscussionID')->select('TagID', 'count', 'CountTags')->from('TagDiscussion')->whereIn('TagID', $TagIDs)->groupBy('DiscussionID')->having('CountTags >=', count($Tags))->limit($Limit, $Offset)->orderBy('DiscussionID', 'desc')->get()->resultArray();
$Limit = '';
$Offset = 0;
$DiscussionIDs = array_column($DiscussionIDs, 'DiscussionID');
$Sql->whereIn('d.DiscussionID', $DiscussionIDs);
$SortField = 'd.DiscussionID';
} else {
$Sql->join('TagDiscussion td', 'd.DiscussionID = td.DiscussionID')->limit($Limit, $Offset)->whereIn('td.TagID', $TagIDs);
if ($Op == 'and') {
$SortField = 'd.DiscussionID';
}
}
// Set up the sort field and direction.
saveToConfig(array('Vanilla.Discussions.SortField' => $SortField, 'Vanilla.Discussions.SortDirection' => $SortDirection), '', false);
}
