/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLSelect $query, DataQuery $dataQuery = NULL)
{
if (Subsite::$disable_subsite_filter) {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly...
if ($query->filtersOnID()) {
return;
}
$regexp = '/^(.*\\.)?("|`)?SubsiteID("|`)?\\s?=/';
foreach ($query->getWhereParameterised($parameters) as $predicate) {
if (preg_match($regexp, $predicate)) {
return;
}
}
try {
$subsiteID = (int) Subsite::currentSubsiteID();
$froms = $query->getFrom();
$froms = array_keys($froms);
$tableName = array_shift($froms);
if ($tableName != 'SiteConfig') {
return;
}
$query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})");
} catch (UnexpectedValueException $e) {
// No subsites exist yet
}
}
/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLSelect $query, DataQuery $dataQuery = NULL)
{
if (Subsite::$disable_subsite_filter) {
return;
}
if ($dataQuery->getQueryParam('Subsite.filter') === false) {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly...
// if(!$query->where || (strpos($query->where[0], ".\"ID\" = ") === false && strpos($query->where[0], ".`ID` = ") === false && strpos($query->where[0], ".ID = ") === false && strpos($query->where[0], "ID = ") !== 0)) {
if ($query->filtersOnID()) {
return;
}
if (Subsite::$force_subsite) {
$subsiteID = Subsite::$force_subsite;
} else {
/*if($context = DataObject::context_obj()) $subsiteID = (int)$context->SubsiteID;
else */
$subsiteID = (int) Subsite::currentSubsiteID();
}
// The foreach is an ugly way of getting the first key :-)
foreach ($query->getFrom() as $tableName => $info) {
// The tableName should be SiteTree or SiteTree_Live...
if (strpos($tableName, 'SiteTree') === false) {
break;
}
$query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})");
break;
}
}
/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLSelect $query)
{
if (Subsite::$disable_subsite_filter) {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly...
if ($query->filtersOnID()) {
return;
}
$regexp = '/^(.*\\.)?("|`)?SubsiteID("|`)?\\s?=/';
foreach ($query->getWhereParameterised($parameters) as $predicate) {
if (preg_match($regexp, $predicate)) {
return;
}
}
/*if($context = DataObject::context_obj()) $subsiteID = (int)$context->SubsiteID;
else */
$subsiteID = (int) Subsite::currentSubsiteID();
$froms = $query->getFrom();
$froms = array_keys($froms);
$tableName = array_shift($froms);
if ($tableName != 'SiteConfig') {
return;
}
$query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})");
}
/**
* Return the LIMIT clause ready for inserting into a query.
*
* @param SQLSelect $query The expression object to build from
* @param array $parameters Out parameter for the resulting query parameters
* @return string The finalised limit SQL fragment
*/
public function buildLimitFragment(SQLSelect $query, array &$parameters)
{
$nl = $this->getSeparator();
// Ensure limit is given
$limit = $query->getLimit();
if (empty($limit)) {
return '';
}
// For literal values return this as the limit SQL
if (!is_array($limit)) {
return "{$nl}LIMIT {$limit}";
}
// Assert that the array version provides the 'limit' key
if (!array_key_exists('limit', $limit) || $limit['limit'] !== null && !is_numeric($limit['limit'])) {
throw new InvalidArgumentException('SQLite3QueryBuilder::buildLimitSQL(): Wrong format for $limit: ' . var_export($limit, true));
}
$clause = "{$nl}";
if ($limit['limit'] !== null) {
$clause .= "LIMIT {$limit['limit']} ";
} else {
$clause .= "LIMIT -1 ";
}
if (isset($limit['start']) && is_numeric($limit['start']) && $limit['start'] !== 0) {
$clause .= "OFFSET {$limit['start']}";
}
return $clause;
}
function LoadFromDB() {
global $POSTS_PER_PAGE;
$s = new SQLSelect();
$s->AddTable("bbs_topics");
$s->AddWhere("bbs_topics.id=".$this->id);
$this->topic = SQLLib::SelectRow($s->GetQuery());
if(!$this->topic) return false;
$s = new SQLSelect();
$s->AddField("count(*) as c");
$s->AddTable("bbs_posts");
$s->AddWhere("bbs_posts.topic=".$this->id);
$this->postcount = SQLLib::SelectRow($s->GetQuery())->c;
$s = new BM_Query();
$s->AddTable("bbs_posts");
$s->AddField("bbs_posts.id as id");
$s->AddField("bbs_posts.post as post");
$s->AddField("bbs_posts.added as added");
$s->attach(array("bbs_posts"=>"author"),array("users as user"=>"id"));
$s->AddWhere("bbs_posts.topic=".$this->id);
//$s->SetLimit( $POSTS_PER_PAGE, (int)(($this->page - 1)*$POSTS_PER_PAGE) );
$this->paginator = new PouetPaginator();
$this->paginator->SetData( "topic.php?which=".$this->id, $this->postcount, $POSTS_PER_PAGE, $_GET["page"] );
$this->paginator->SetLimitOnQuery( $s );
$this->posts = $s->perform();
$this->title = _html($this->topic->topic);
}
public function getMap()
{
$this->getDimensions();
if (empty($this->map)) {
$sel = new SQLSelect($this->table);
$res = $sel->execute();
$this->map = parallelToAssoc($res[strtoupper($this->idfields[0])], $res[strtoupper($this->idfields[1])]);
}
return $this->map;
}
public function getPossLanguages()
{
if (empty($this->posslanguages)) {
$getl = new SQLSelect("translation_language");
$getl->fetchas = "row";
$res = $getl->execute();
$this->posslanguages = $res;
}
return $this->posslanguages;
}
public function processFields()
{
if (!isset($this->fields["language_keycode"]["options"])) {
$opsel = new SQLSelect("translation_language");
$opsel->selectfields = array("id" => "keycode", "label" => "name_def");
$opsel->fetchas = "row";
$res = $opsel->execute();
$options = $res;
$this->fields["language_keycode"]["options"] = $options;
}
}
/**
* Extracts the limit and offset from the limit clause
*
* @param SQLSelect $query
* @return array Two item array with $limit and $offset as values
* @throws InvalidArgumentException
*/
protected function parseLimit(SQLSelect $query)
{
$limit = '';
$offset = '0';
if (is_array($query->getLimit())) {
$limitArr = $query->getLimit();
if (isset($limitArr['limit'])) {
$limit = $limitArr['limit'];
}
if (isset($limitArr['start'])) {
$offset = $limitArr['start'];
}
} else {
if (preg_match('/^([0-9]+) offset ([0-9]+)$/i', trim($query->getLimit()), $matches)) {
$limit = $matches[1];
$offset = $matches[2];
} else {
//could be a comma delimited string
$bits = explode(',', $query->getLimit());
if (sizeof($bits) > 1) {
list($offset, $limit) = $bits;
} else {
$limit = $bits[0];
}
}
}
return array($limit, $offset);
}
/**
* Attempt to find an old/renamed page from some given the URL as an array
*
* @param array $params The array of URL, e.g. /foo/bar as array('foo', 'bar')
* @param SiteTree $parent The current parent in the recursive flow
* @param boolean $redirect Whether we've found an old page worthy of a redirect
*
* @return string|boolean False, or the new URL
*/
public static function find_old_page($params, $parent = null, $redirect = false)
{
$parent = is_numeric($parent) ? SiteTree::get()->byId($parent) : $parent;
$params = (array) $params;
$URL = rawurlencode(array_shift($params));
if (empty($URL)) {
return false;
}
if ($parent) {
$page = SiteTree::get()->filter(array('ParentID' => $parent->ID, 'URLSegment' => $URL))->First();
} else {
$page = SiteTree::get()->filter(array('URLSegment' => $URL))->First();
}
if (!$page) {
// If we haven't found a candidate, lets resort to finding an old page with this URL segment
$oldFilter = array('"SiteTree_versions"."URLSegment"' => $URL, '"SiteTree_versions"."WasPublished"' => true);
if ($parent) {
$oldFilter[] = array('"SiteTree_versions"."ParentID"' => $parent->ID);
}
$query = new SQLSelect('"RecordID"', '"SiteTree_versions"', $oldFilter, '"LastEdited" DESC', null, null, 1);
$record = $query->execute()->first();
if ($record) {
$page = SiteTree::get()->byID($record['RecordID']);
$redirect = true;
}
}
if ($page && $page->canView()) {
if (count($params)) {
// We have to go deeper!
$ret = self::find_old_page($params, $page, $redirect);
if ($ret) {
// A valid child page was found! We can return it
return $ret;
} else {
// No valid page found.
if ($redirect) {
// If we had some redirect to be done, lets do it. imagine /foo/action -> /bar/action, we still want this redirect to happen if action isn't a page
return $page->Link() . implode('/', $params);
}
}
} else {
// We've found the final, end all, page.
return $page->Link();
}
}
return false;
}
/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLSelect $query)
{
if (Subsite::$disable_subsite_filter) {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly... (but it was WAYYYYYYYYY worse)
//@TODO I don't think excluding if SiteTree_ImageTracking is a good idea however because of the SS 3.0 api and ManyManyList::removeAll() changing the from table after this function is called there isn't much of a choice
$from = $query->getFrom();
if (isset($from['SiteTree_ImageTracking']) || $query->filtersOnID()) {
return;
}
$subsiteID = (int) Subsite::currentSubsiteID();
// The foreach is an ugly way of getting the first key :-)
foreach ($query->getFrom() as $tableName => $info) {
$where = "\"{$tableName}\".\"SubsiteID\" IN (0, {$subsiteID})";
$query->addWhere($where);
break;
}
$sect = array_values($query->getSelect());
$isCounting = strpos($sect[0], 'COUNT') !== false;
// Ordering when deleting or counting doesn't apply
if (!$isCounting) {
$query->addOrderBy("\"SubsiteID\"");
}
}
public function build_query()
{
$setstring = "";
// If insertIfAbsent is set, check for the row
if ($this->insertIfAbsent) {
$sel = new SQLSelect($this->table, $this->schema);
$sel->selectfields[] = "1";
$sel->wherearray = $this->selectors;
$val = $sel->execute();
if ($val == NULL || empty($val) || $val[1] == array()) {
$ins = new SQLInsert($this->table, $this->values);
$q = $ins->build_query();
$this->query = $q;
return $this->query;
}
}
// Perform the UPDATE
foreach ($this->values as $key => $value) {
// Skip selectors
if (isset($this->selectors[$key])) {
continue;
}
// Build 'SET' string
if ($setstring != "") {
$setstring .= ",";
}
$setstring .= $key . "=" . dbize($value, $this->get_column_datatype($key));
}
if ($setstring != "") {
$setstring = " SET " . $setstring;
}
// Add selectors to WHERE clause
foreach ($this->selectors as $key => $value) {
$this->wherefields[] = "{$key} = " . dbize($value, $this->get_column_datatype($key));
}
$wherestring = $this->wherefields_to_string();
if ($setstring != "" && $wherestring != "") {
$query = "UPDATE " . $this->table_with_schema() . $setstring . $wherestring;
$this->query = $query;
}
return $this->query;
}
function LoadFromDB() {
$s = new SQLSelect();
$this->group = PouetGroup::Spawn($this->id);
$this->addeduser = PouetUser::Spawn($this->group->addedUser);
// not to boast or anything, but this is f*****g beautiful.
$sub = new SQLSelect();
$sub->AddField("max(comments.addedDate) as maxDate");
$sub->AddField("comments.which");
$sub->AddTable("comments");
$sub->AddJoin("left","prods","prods.id = comments.which");
//$sub->AddOrder("comments.addedDate desc");
$sub->AddGroup("comments.which");
$sub->AddWhere(sprintf_esc("(prods.group1 = %d) or (prods.group2 = %d) or (prods.group3 = %d)",$this->id,$this->id,$this->id));
$s = new BM_Query("prods");
$s->AddField("cmts.addedDate as lastcomment");
$s->AddField("cmts.rating as lastcommentrating");
$s->AddJoin("left","(select comments.addedDate,comments.who,comments.which,comments.rating from (".$sub->GetQuery().") as dummy left join comments on dummy.maxDate = comments.addedDate and dummy.which = comments.which) as cmts","cmts.which=prods.id");
$s->attach(array("cmts"=>"who"),array("users as user"=>"id"));
$s->AddWhere(sprintf_esc("(prods.group1 = %d) or (prods.group2 = %d) or (prods.group3 = %d)",$this->id,$this->id,$this->id));
$r = !!$_GET["reverse"];
switch($_GET["order"])
{
case "type": $s->AddOrder("prods.type ".($r?"DESC":"ASC")); break;
case "party": $s->AddOrder("prods_party.name ".($r?"DESC":"ASC")); $s->AddOrder("prods.party_year ".($r?"DESC":"ASC")); $s->AddOrder("prods.party_place ".($r?"DESC":"ASC")); break;
case "release": $s->AddOrder("prods.releaseDate ".($r?"ASC":"DESC")); break;
case "thumbup": $s->AddOrder("prods.voteup ".($r?"ASC":"DESC")); break;
case "thumbpig": $s->AddOrder("prods.votepig ".($r?"ASC":"DESC")); break;
case "thumbdown": $s->AddOrder("prods.votedown ".($r?"ASC":"DESC")); break;
case "avg": $s->AddOrder("prods.voteavg ".($r?"ASC":"DESC")); break;
case "views": $s->AddOrder("prods.views ".($r?"ASC":"DESC")); break;
case "latestcomment": $s->AddOrder("lastcomment ".($r?"ASC":"DESC")); break;
default: $s->AddOrder("prods.name ".($r?"DESC":"ASC")); break;
}
$this->prods = $s->perform();
PouetCollectPlatforms($this->prods);
PouetCollectAwards($this->prods);
$s = new BM_Query("affiliatedboards");
$s->attach(array("affiliatedboards"=>"board"),array("boards as board"=>"id"));
$s->AddWhere(sprintf_esc("affiliatedboards.group=%d",$this->id));
$this->affil = $s->perform();
}
function Load( $cached = false ) {
$s = new SQLSelect();
$s->AddTable("buttons");
$s->AddOrder("rand()");
$s->AddWhere("dead = 0");
$s->SetLimit("1");
$this->data = SQLLib::SelectRow($s->GetQuery());
$this->title = $this->data->type;
}
/**
* For each author, add an AuthorHelper
*/
private function migrateAuthors()
{
/** @var SQLSelect $query */
$query = SQLSelect::create();
$query->setSelect('Author')->setFrom('News')->setDistinct(true);
$authors = $query->execute();
foreach ($authors as $author) {
/** Create a new author if it doesn't exist */
if (!($authorHelper = AuthorHelper::get()->filter(array('OriginalName' => trim($author['Author'])))->first())) {
/** @var AuthorHelper $authorHelper */
$authorHelper = AuthorHelper::create();
$authorHelper->OriginalName = $author['Author'];
$authorHelper->write();
}
$sql = "UPDATE `News` SET `AuthorHelperID` = '" . $authorHelper->ID . "' WHERE Author = '" . $author['Author'] . "'";
DB::query($sql);
}
}
/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLSelect $query, DataQuery $dataQuery = null)
{
if (Subsite::$disable_subsite_filter) {
return;
}
if (Cookie::get('noSubsiteFilter') == 'true') {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly...
if (!$query->filtersOnID()) {
/*if($context = DataObject::context_obj()) $subsiteID = (int)$context->SubsiteID;
else */
$subsiteID = (int) Subsite::currentSubsiteID();
// Don't filter by Group_Subsites if we've already done that
$hasGroupSubsites = false;
foreach ($query->getFrom() as $item) {
if (is_array($item) && strpos($item['table'], 'Group_Subsites') !== false || !is_array($item) && strpos($item, 'Group_Subsites') !== false) {
$hasGroupSubsites = true;
break;
}
}
if (!$hasGroupSubsites) {
if ($subsiteID) {
$query->addLeftJoin("Group_Subsites", "\"Group_Subsites\".\"GroupID\" \n\t\t\t\t\t\t= \"Group\".\"ID\" AND \"Group_Subsites\".\"SubsiteID\" = {$subsiteID}");
$query->addWhere("(\"Group_Subsites\".\"SubsiteID\" IS NOT NULL OR\n\t\t\t\t\t\t\"Group\".\"AccessAllSubsites\" = 1)");
} else {
$query->addWhere("\"Group\".\"AccessAllSubsites\" = 1");
}
}
// WORKAROUND for databases that complain about an ORDER BY when the column wasn't selected (e.g. SQL Server)
$select = $query->getSelect();
if (isset($select[0]) && !$select[0] == 'COUNT(*)') {
$query->orderby = "\"AccessAllSubsites\" DESC" . ($query->orderby ? ', ' : '') . $query->orderby;
}
}
}
<?
require_once("bootstrap.inc.php");
header("Content-type: application/json; charset=utf-8");
$sql = new SQLSelect();
$sql->AddField("id");
$sql->AddField("nickname as name");
$sql->AddField("avatar");
$sql->AddField("glops");
$sql->AddTable("users");
$r = array();
if ($_POST["search"])
{
$sql->AddWhere(sprintf_esc("nickname like '%%%s%%'",_like($_POST["search"])));
$sql->AddOrder(sprintf_esc("if(nickname='%s',1,2), nickname, lastLogin DESC",$_POST["search"]));
$sql->SetLimit(10);
$r = SQLLib::selectRows( $sql->GetQuery() );
}
else if ($_POST["id"])
{
$sql->AddWhere(sprintf_esc("id = %d",$_POST["id"]));
$sql->SetLimit(1);
$r = SQLLib::selectRows( $sql->GetQuery() );
}
echo json_encode($r);
?>
public function toDelete()
{
if (!$this->isDelete) {
user_error('SQLQuery::toDelete called when $isDelete is false. Use ' . 'toAppropriateExpression() instead', E_USER_WARNING);
}
parent::toDelete();
}
public function html()
{
global $db;
$html = "";
// Pull value from another table, if needed
if (isset($this->value_sqltable) && $this->value_sqltable != "") {
if (isset($this->value_sqlselectfield) && $this->value_sqlselectfield != "") {
$selectfield = $this->value_sqlselectfield;
} else {
$selectfield = $this->name;
}
$sel = new SQLSelect($this->value_sqltable);
$sel->selectfields[] = $selectfield;
if (!empty($this->value_sqlwherefields)) {
$sel->wherefields = $this->value_sqlwherefields;
}
$res = $sel->execute();
if (isset($res[strtoupper($selectfield)][0])) {
$this->value = $res[strtoupper($selectfield)][0];
}
}
// Unencode value
if (isset($this->value) && $this->value != "") {
$this->value = htmlspecialchars_decode($this->value);
}
if ($this->type == "header") {
if ($this->is_html_table) {
$html .= "<tr><td";
if ($this->colspan > 0) {
$html .= " colspan='" . $this->colspan . "'";
} else {
if ($this->maximum_fields_per_row) {
$html .= " colspan='" . $this->maximum_fields_per_row . "'";
}
}
$html .= ">";
}
$html .= "<h3>" . $this->label . "</h3>";
if ($this->helptext != "") {
$html .= "<span id='" . $this->id . "_helpblock' class='help-block " . $this->helptextclasses . "'>" . $this->helptext . "</span>";
}
if ($this->is_html_table) {
$html .= "</td></tr>";
}
return $html;
}
// Line separator, if applicable
if ($this->inline == true && $this->newline == true && $this->is_html_table == false) {
$html .= "<p>";
}
// New TR, for table form
if ($this->is_html_table && $this->type != "hidden") {
if ($this->newline) {
$html .= "<tr>";
}
$html .= "<td";
if ($this->colspan > 0) {
$html .= " colspan='" . $this->colspan . "'";
} else {
if ($this->maximum_fields_per_row) {
$html .= " colspan='" . $this->maximum_fields_per_row . "'";
}
}
$html .= ">";
}
// Open container div
if ($this->type != "hidden") {
$html .= "<div class='form-group";
if (isset($this->containerclasses)) {
$html .= " " . $this->containerclasses;
}
$html .= "'>";
}
// Label
$labelhtml = "";
if ($this->type != "hidden" && $this->type != "html" && $this->label != "") {
$labelhtml .= "<label";
if ($this->id != "") {
$labelhtml .= " for='" . $this->id . "'";
}
// Label classes
$labelhtml .= " class='form-control-label control-label";
if ($this->required == true) {
$labelhtml .= "requiredLabel";
}
if ($this->horizontal == true) {
$labelhtml .= " col-sm-2";
}
if (isset($this->labelclasses) && $this->labelclasses != "") {
$labelhtml .= " " . $this->labelclasses;
}
$labelhtml .= "'";
if (isset($this->labelattrs) && $this->labelattrs != "") {
$labelhtml .= " " . $this->labelattrs;
}
$labelhtml .= ">";
$labelhtml .= $this->label;
$labelhtml .= "</label>";
}
// Initialize input tag string
$inputhtml = "";
// Surrounding grid for horizontal
if ($this->horizontal == true && $this->type != "hidden") {
$inputhtml .= "<div class='col-sm-10'>";
}
// Prefix, if any
if (isset($this->prefix) && $this->prefix != "") {
$inputhtml .= "<div class='prefixandinput'><div class='prefix'>" . $this->prefix . "</div>";
}
// Open input tag
if ($this->type != "html") {
if ($this->type == 'textarea') {
$inputhtml .= "<textarea";
} else {
if ($this->type == 'select') {
$inputhtml .= "<select";
} else {
$inputhtml .= "<input type='" . $this->type . "'";
}
}
// Id and name
if ($this->id != "") {
$inputhtml .= " id='" . $this->id . "'";
}
if ($this->name != "") {
$inputhtml .= " name='" . $this->name . "'";
}
// Required
if ($this->type != "hidden" && $this->required == true) {
$inputhtml .= " required='" . $this->required . "'";
}
// Size & step
if ($this->type == 'number') {
$size = 3;
if ($this->size > 0) {
$size = $this->size;
$this->inputattrs .= "style='width:" . ($size * 2 + 1) . "em'";
}
$inputhtml .= " size='{$size}'";
$inputhtml .= " step='" . $this->step . "'";
}
// Minimum & maximum
if ($this->minimum !== "") {
$inputhtml .= " min='" . $this->minimum . "'";
}
if ($this->maximum !== "") {
$inputhtml .= " min='" . $this->maximum . "'";
}
// Classes
if ($this->type != 'hidden') {
$inputhtml .= " class='form-control";
if ($this->required == true) {
$inputhtml .= " required";
}
if ($this->alignright && $this->type != "textarea" && $this->type != "checkbox") {
$this->inputclasses = "alignright";
}
// Decide if align right
if ($this->type == 'textarea') {
if ($this->inputclasses != "") {
$this->inputclasses .= " ";
}
$this->inputclasses .= "block width100";
// Special for align right text area (to NOT align right and show the full one)
}
if (isset($this->inputclasses)) {
$inputhtml .= " " . $this->inputclasses;
}
$inputhtml .= "'";
}
// I'm putting the default value as a dummy attribute, which can be used for javascript resets, etc.
if (isset($this->defaultvalue) && $this->defaultvalue != "" && $this->value != "") {
$inputhtml .= ' data-defaultvalue="' . str_replace('"', '"', htmlentities($this->defaultvalue)) . '"';
}
// Calculation attributes
if (isset($this->calculate_addfields) && !empty($this->calculate_addfields)) {
$inputhtml .= ' data-calculate_addfields="' . implode(",", $this->calculate_addfields) . '"';
}
if (isset($this->calculate_subtractfields) && !empty($this->calculate_subtractfields)) {
$inputhtml .= ' data-calculate_subtractfields="' . implode(",", $this->calculate_subtractfields) . '"';
}
// Helptext association
if ($this->helptext != "") {
$inputhtml .= ' aria-described-by="' . $this->id . '_helpblock"';
}
// Value
if ($this->type == "checkbox") {
$inputhtml .= ' value="1"';
if ($this->value == 1) {
$inputhtml .= ' checked';
}
} else {
if ($this->type != 'textarea' && $this->type != 'select') {
if ($this->value != "") {
$inputhtml .= ' value="' . $db->undbize($this->value) . '"';
} else {
if ($this->defaultvalue !== "") {
$inputhtml .= ' value="' . $db->undbize($this->defaultvalue) . '"';
}
}
}
}
// Readonly
if ($this->readonly) {
$inputhtml .= ' readonly';
}
// Input attributes
if ($this->inputattrs != "") {
$inputhtml .= " " . $this->inputattrs;
}
// End of opening tag
$inputhtml .= ">";
// Textarea value
if ($this->type == 'textarea' && $this->value != "") {
$inputhtml .= $db->undbize($this->value);
}
// Select options
if ($this->type == 'select' && !empty($this->options)) {
$idx = 0;
$optionvalues = array_values($this->options);
if (isset($this->nulloptiontext)) {
$inputhtml .= "<option value=''>" . $this->nulloptiontext . "</option>";
}
foreach ($this->options as $key => $option) {
if (isset($optionvalues[$idx - 1])) {
$lastoption = $optionvalues[$idx - 1];
}
if (isset($optionvalues[$idx + 1])) {
$nextoption = $optionvalues[$idx + 1];
}
$idx++;
if (is_array($option)) {
$option = array_change_key_case($option);
if (isset($lastoption)) {
$lastoption = array_change_key_case($lastoption);
}
if (isset($nextoption)) {
$nextoption = array_change_key_case($nextoption);
}
if (isset($this->optionidkey)) {
$optionidkey = strtolower($this->optionidkey);
} else {
if (isset($option["key"])) {
$optionidkey = "key";
} else {
if (isset($option["value"])) {
$optionidkey = "value";
} else {
$optionidkey = "id";
}
}
}
if (isset($this->optiontextkey)) {
$optiontextkey = strtolower($this->optiontextkey);
} else {
if (isset($option["label"])) {
$optiontextkey = "label";
} else {
if (isset($option["name"])) {
$optiontextkey = "name";
} else {
$optiontextkey = "text";
}
}
}
if (isset($this->optiongroupkey)) {
$optiongroupkey = strtolower($this->optiongroupkey);
}
$value = $option[$optionidkey];
$text = $option[$optiontextkey];
} else {
$value = $key;
$text = $option;
}
if (isset($optiongroupkey) && isset($option[$optiongroupkey]) && (!isset($lastoption) || $option[$optiongroupkey] != $lastoption[$optiongroupkey])) {
$inputhtml .= "<optgroup label='" . $option[$optiongroupkey] . "'>";
}
$inputhtml .= "<option value='{$value}'";
if ($this->value == $value) {
$inputhtml .= " selected";
}
$inputhtml .= ">{$text}</option>";
if (isset($optiongroupkey) && isset($option[$optiongroupkey]) && (!isset($nextoption) || $option[$optiongroupkey] != $nextoption[$optiongroupkey])) {
$inputhtml .= "</optgroup>";
}
}
}
// Close tag
if ($this->type == 'textarea') {
$inputhtml .= "</textarea>";
} else {
if ($this->type == 'select') {
$inputhtml .= "</select>";
} else {
$inputhtml .= "</input>";
}
}
if ($this->postfix != "") {
$inputhtml .= " " . $this->postfix;
}
// Surrounding grid for horizontal
if ($this->horizontal == true && $this->type != "hidden") {
$inputhtml .= "</div>";
}
}
// End prefixandinput div
if (isset($this->prefix) && $this->prefix != "") {
$inputhtml .= "</div>";
}
// Put label & input in order
if ($this->type == "checkbox" && $this->horizontal == false) {
$html .= $inputhtml . " " . $labelhtml;
} else {
$html .= $labelhtml . " " . $inputhtml;
}
// HTML element content
if ($this->type == "html" && $this->content != "") {
$html .= $this->content;
}
// Tooltip
if ($this->tooltip != "") {
$html .= "<span class='badge' style='margin-left:0.5em;' data-toggle='tooltip' data-placement='auto right' title='" . $this->tooltip . "'>\r\n\t\t\t ?\r\n\t\t\t</span>";
}
// Helptext
if ($this->helptext != "") {
$html .= " <span id='" . $this->id . "_helpblock' class='help-block " . $this->helptextclasses . "'>" . $this->helptext . "</span>";
}
// End div
if ($this->type != "hidden") {
$html .= "</div>";
}
// New TR, for table form
if ($this->is_html_table && $this->type != "hidden") {
$html .= "</td>";
if ($this->newline_next) {
$html .= "</tr>";
}
}
$this->html = $html;
return $html;
}
/**
* Test passing in a LIMIT with OFFSET clause string.
*/
public function testLimitSetFromClauseString()
{
$query = new SQLSelect();
$query->setSelect('*');
$query->setFrom('"SQLQueryTest_DO"');
$query->setLimit('20 OFFSET 10');
$limit = $query->getLimit();
$this->assertEquals(20, $limit['limit']);
$this->assertEquals(10, $limit['start']);
}
<?
require_once("bootstrap.inc.php");
header("Content-type: application/json; charset=utf-8");
$sql = new SQLSelect();
$sql->AddField("prods.id");
$sql->AddField("prods.name");
$sql->AddField("groups.name as groupName");
$sql->AddJoin("left","groups","groups.id = prods.group1");
$sql->AddTable("prods");
$r = array();
if ($_POST["search"])
{
$sql->AddWhere(sprintf_esc("prods.name like '%%%s%%'",_like($_POST["search"])));
$sql->AddOrder(sprintf_esc("if(prods.name='%s',1,2), prods.views desc, prods.name",$_POST["search"]));
$sql->SetLimit(10);
$r = SQLLib::selectRows( $sql->GetQuery() );
}
else if ($_POST["id"])
{
$sql->AddWhere(sprintf_esc("prods.id = %d",$_POST["id"]));
$sql->SetLimit(1);
$r = SQLLib::selectRows( $sql->GetQuery() );
}
echo json_encode($r);
?>
/**
* Add an item to this many_many relationship
* Does so by adding an entry to the joinTable.
*
* @param mixed $item
* @param array $extraFields A map of additional columns to insert into the joinTable.
* Column names should be ANSI quoted.
*/
public function add($item, $extraFields = array())
{
// Ensure nulls or empty strings are correctly treated as empty arrays
if (empty($extraFields)) {
$extraFields = array();
}
// Determine ID of new record
if (is_numeric($item)) {
$itemID = $item;
} elseif ($item instanceof $this->dataClass) {
$itemID = $item->ID;
} else {
throw new InvalidArgumentException("ManyManyList::add() expecting a {$this->dataClass} object, or ID value", E_USER_ERROR);
}
// Validate foreignID
$foreignIDs = $this->getForeignID();
if (empty($foreignIDs)) {
throw new Exception("ManyManyList::add() can't be called until a foreign ID is set", E_USER_WARNING);
}
// Apply this item to each given foreign ID record
if (!is_array($foreignIDs)) {
$foreignIDs = array($foreignIDs);
}
foreach ($foreignIDs as $foreignID) {
// Check for existing records for this item
if ($foreignFilter = $this->foreignIDWriteFilter($foreignID)) {
// With the current query, simply add the foreign and local conditions
// The query can be a bit odd, especially if custom relation classes
// don't join expected tables (@see Member_GroupSet for example).
$query = new SQLSelect("*", "\"{$this->joinTable}\"");
$query->addWhere($foreignFilter);
$query->addWhere(array("\"{$this->joinTable}\".\"{$this->localKey}\"" => $itemID));
$hasExisting = $query->count() > 0;
} else {
$hasExisting = false;
}
// Blank manipulation
$manipulation = array($this->joinTable => array('command' => $hasExisting ? 'update' : 'insert', 'fields' => array()));
if ($hasExisting) {
$manipulation[$this->joinTable]['where'] = array("\"{$this->joinTable}\".\"{$this->foreignKey}\"" => $foreignID, "\"{$this->joinTable}\".\"{$this->localKey}\"" => $itemID);
}
if ($extraFields && $this->extraFields) {
// Write extra field to manipluation in the same way
// that DataObject::prepareManipulationTable writes fields
foreach ($this->extraFields as $fieldName => $fieldSpec) {
// Skip fields without an assignment
if (array_key_exists($fieldName, $extraFields)) {
$fieldObject = Object::create_from_string($fieldSpec, $fieldName);
$fieldObject->setValue($extraFields[$fieldName]);
$fieldObject->writeToManipulation($manipulation[$this->joinTable]);
}
}
}
$manipulation[$this->joinTable]['fields'][$this->localKey] = $itemID;
$manipulation[$this->joinTable]['fields'][$this->foreignKey] = $foreignID;
DB::manipulate($manipulation);
}
}
/**
* Return the number of rows in this query if the limit were removed. Useful in paged data sets.
*
* @param string $column
* @return int
*/
public function unlimitedRowCount($column = null)
{
// we can't clear the select if we're relying on its output by a HAVING clause
if (count($this->having)) {
$records = $this->execute();
return $records->numRecords();
}
$clone = clone $this;
$clone->limit = null;
$clone->orderby = null;
// Choose a default column
if ($column == null) {
if ($this->groupby) {
// @todo Test case required here
$countQuery = new SQLSelect();
$countQuery->setSelect("count(*)");
$countQuery->setFrom(array('(' . $clone->sql($innerParameters) . ') all_distinct'));
$sql = $countQuery->sql($parameters);
// $parameters should be empty
$result = DB::prepared_query($sql, $innerParameters);
return $result->value();
} else {
$clone->setSelect(array("count(*)"));
}
} else {
$clone->setSelect(array("count({$column})"));
}
$clone->setGroupBy(array());
return $clone->execute()->value();
}
/**
* Find the extra field data for a single row of the relationship join
* table, given the known child ID.
*
* @param string $componentName The name of the component
* @param int $itemID The ID of the child for the relationship
*
* @return array Map of fieldName => fieldValue
*/
public function getExtraData($componentName, $itemID)
{
$result = array();
// Skip if no extrafields or unsaved record
if (empty($this->extraFields) || empty($itemID)) {
return $result;
}
if (!is_numeric($itemID)) {
user_error('ComponentSet::getExtraData() passed a non-numeric child ID', E_USER_ERROR);
}
$cleanExtraFields = array();
foreach ($this->extraFields as $fieldName => $dbFieldSpec) {
$cleanExtraFields[] = "\"{$fieldName}\"";
}
$query = new SQLSelect($cleanExtraFields, "\"{$this->joinTable}\"");
$filter = $this->foreignIDWriteFilter($this->getForeignID());
if ($filter) {
$query->setWhere($filter);
} else {
user_error("Can't call ManyManyList::getExtraData() until a foreign ID is set", E_USER_WARNING);
}
$query->addWhere(array("\"{$this->localKey}\"" => $itemID));
$queryResult = $query->execute()->current();
if ($queryResult) {
foreach ($queryResult as $fieldName => $value) {
$result[$fieldName] = $value;
}
}
return $result;
}
/**
* Select the given field expressions.
*
* @param $fieldExpression String The field to select (escaped SQL statement)
* @param $alias String The alias of that field (escaped SQL statement)
*/
protected function selectField($fieldExpression, $alias = null)
{
$this->query->selectField($fieldExpression, $alias);
}
/**
* Link this group set to a specific member.
*
* Recursively selects all groups applied to this member, as well as any
* parent groups of any applied groups
*
* @param array|integer $id (optional) An ID or an array of IDs - if not provided, will use the current
* ids as per getForeignID
* @return array Condition In array(SQL => parameters format)
*/
public function foreignIDFilter($id = null)
{
if ($id === null) {
$id = $this->getForeignID();
}
// Find directly applied groups
$manyManyFilter = parent::foreignIDFilter($id);
$query = new SQLSelect('"Group_Members"."GroupID"', '"Group_Members"', $manyManyFilter);
$groupIDs = $query->execute()->column();
// Get all ancestors, iteratively merging these into the master set
$allGroupIDs = array();
while ($groupIDs) {
$allGroupIDs = array_merge($allGroupIDs, $groupIDs);
$groupIDs = DataObject::get("Group")->byIDs($groupIDs)->column("ParentID");
$groupIDs = array_filter($groupIDs);
}
// Add a filter to this DataList
if (!empty($allGroupIDs)) {
$allGroupIDsPlaceholders = DB::placeholders($allGroupIDs);
return array("\"Group\".\"ID\" IN ({$allGroupIDsPlaceholders})" => $allGroupIDs);
} else {
return array('"Group"."ID"' => 0);
}
}
/**
* Returns true if the member is allowed to do the given action.
* See {@link extendedCan()} for a more versatile tri-state permission control.
*
* @param string $perm The permission to be checked, such as 'View'.
* @param Member $member The member whose permissions need checking. Defaults to the currently logged
* in user.
*
* @return boolean True if the the member is allowed to do the given action
*/
public function can($perm, $member = null)
{
if (!isset($member)) {
$member = Member::currentUser();
}
if (Permission::checkMember($member, "ADMIN")) {
return true;
}
if ($this->many_many('Can' . $perm)) {
if ($this->ParentID && $this->SecurityType == 'Inherit') {
if (!($p = $this->Parent)) {
return false;
}
return $this->Parent->can($perm, $member);
} else {
$permissionCache = $this->uninherited('permissionCache');
$memberID = $member ? $member->ID : 'none';
if (!isset($permissionCache[$memberID][$perm])) {
if ($member->ID) {
$groups = $member->Groups();
}
$groupList = implode(', ', $groups->column("ID"));
// TODO Fix relation table hardcoding
$query = new SQLSelect("\"Page_Can{$perm}\".PageID", array("\"Page_Can{$perm}\""), "GroupID IN ({$groupList})");
$permissionCache[$memberID][$perm] = $query->execute()->column();
if ($perm == "View") {
// TODO Fix relation table hardcoding
$query = new SQLSelect("\"SiteTree\".\"ID\"", array("\"SiteTree\"", "LEFT JOIN \"Page_CanView\" ON \"Page_CanView\".\"PageID\" = \"SiteTree\".\"ID\""), "\"Page_CanView\".\"PageID\" IS NULL");
$unsecuredPages = $query->execute()->column();
if ($permissionCache[$memberID][$perm]) {
$permissionCache[$memberID][$perm] = array_merge($permissionCache[$memberID][$perm], $unsecuredPages);
} else {
$permissionCache[$memberID][$perm] = $unsecuredPages;
}
}
Config::inst()->update($this->class, 'permissionCache', $permissionCache);
}
if ($permissionCache[$memberID][$perm]) {
return in_array($this->ID, $permissionCache[$memberID][$perm]);
}
}
} else {
return parent::can($perm, $member);
}
}
public function testDbDatetimeDifference()
{
$offset = $this->checkPreconditions();
$clause = $this->adapter->datetimeDifferenceClause('1974-10-14 10:30:00', '1973-10-14 10:30:00');
$result = DB::query('SELECT ' . $clause)->value();
$this->matchesRoughly($result / 86400, 365, '1974 - 1973 = 365 * 86400 sec', $offset);
$clause = $this->adapter->datetimeDifferenceClause(date('Y-m-d H:i:s', strtotime('-15 seconds')), 'now');
$result = DB::query('SELECT ' . $clause)->value();
$this->matchesRoughly($result, -15, '15 seconds ago - now', $offset);
$clause = $this->adapter->datetimeDifferenceClause('now', $this->adapter->datetimeIntervalClause('now', '+45 Minutes'));
$result = DB::query('SELECT ' . $clause)->value();
$this->matchesRoughly($result, -45 * 60, 'now - 45 minutes ahead', $offset);
$query = new SQLSelect();
$query->setSelect(array());
$query->selectField($this->adapter->datetimeDifferenceClause('"LastEdited"', '"Created"'), 'test')->setFrom('"DbDateTimeTest_Team"')->setLimit(1);
$result = $query->execute()->value();
$lastedited = Dataobject::get_one('DbDateTimeTest_Team')->LastEdited;
$created = Dataobject::get_one('DbDateTimeTest_Team')->Created;
$this->matchesRoughly($result, strtotime($lastedited) - strtotime($created), 'age of HomePage record in seconds since unix epoc', $offset);
}
public function testParameterisedLeftJoins()
{
$query = new SQLSelect();
$query->setSelect(array('"SQLSelectTest_DO"."Name"', '"SubSelect"."Count"'));
$query->setFrom('"SQLSelectTest_DO"');
$query->addLeftJoin('(SELECT "Title", COUNT(*) AS "Count" FROM "SQLSelectTestBase" GROUP BY "Title" HAVING "Title" NOT LIKE ?)', '"SQLSelectTest_DO"."Name" = "SubSelect"."Title"', 'SubSelect', 20, array('%MyName%'));
$query->addWhere(array('"SQLSelectTest_DO"."Date" > ?' => '2012-08-08 12:00'));
$this->assertSQLEquals('SELECT "SQLSelectTest_DO"."Name", "SubSelect"."Count"
FROM "SQLSelectTest_DO" LEFT JOIN (SELECT "Title", COUNT(*) AS "Count" FROM "SQLSelectTestBase"
GROUP BY "Title" HAVING "Title" NOT LIKE ?) AS "SubSelect" ON "SQLSelectTest_DO"."Name" =
"SubSelect"."Title"
WHERE ("SQLSelectTest_DO"."Date" > ?)', $query->sql($parameters));
$this->assertEquals(array('%MyName%', '2012-08-08 12:00'), $parameters);
$query->execute();
}
/**
* Augment the the SQLSelect that is created by the DataQuery
* @todo Should this all go into VersionedDataQuery?
*/
public function augmentSQL(SQLSelect $query, DataQuery $dataQuery = null)
{
if (!$dataQuery || !$dataQuery->getQueryParam('Versioned.mode')) {
return;
}
$baseTable = ClassInfo::baseDataClass($dataQuery->dataClass());
switch ($dataQuery->getQueryParam('Versioned.mode')) {
// Reading a specific data from the archive
case 'archive':
$date = $dataQuery->getQueryParam('Versioned.date');
foreach ($query->getFrom() as $table => $dummy) {
if (!DB::get_schema()->hasTable($table . '_versions')) {
continue;
}
$query->renameTable($table, $table . '_versions');
$query->replaceText("\"{$table}_versions\".\"ID\"", "\"{$table}_versions\".\"RecordID\"");
$query->replaceText("`{$table}_versions`.`ID`", "`{$table}_versions`.`RecordID`");
// Add all <basetable>_versions columns
foreach (Config::inst()->get('Versioned', 'db_for_versions_table') as $name => $type) {
$query->selectField(sprintf('"%s_versions"."%s"', $baseTable, $name), $name);
}
$query->selectField(sprintf('"%s_versions"."%s"', $baseTable, 'RecordID'), "ID");
if ($table != $baseTable) {
$query->addWhere("\"{$table}_versions\".\"Version\" = \"{$baseTable}_versions\".\"Version\"");
}
}
// Link to the version archived on that date
$query->addWhere(array("\"{$baseTable}_versions\".\"Version\" IN\n\t\t\t\t(SELECT LatestVersion FROM\n\t\t\t\t\t(SELECT\n\t\t\t\t\t\t\"{$baseTable}_versions\".\"RecordID\",\n\t\t\t\t\t\tMAX(\"{$baseTable}_versions\".\"Version\") AS LatestVersion\n\t\t\t\t\t\tFROM \"{$baseTable}_versions\"\n\t\t\t\t\t\tWHERE \"{$baseTable}_versions\".\"LastEdited\" <= ?\n\t\t\t\t\t\tGROUP BY \"{$baseTable}_versions\".\"RecordID\"\n\t\t\t\t\t) AS \"{$baseTable}_versions_latest\"\n\t\t\t\t\tWHERE \"{$baseTable}_versions_latest\".\"RecordID\" = \"{$baseTable}_versions\".\"RecordID\"\n\t\t\t\t)" => $date));
break;
// Reading a specific stage (Stage or Live)
// Reading a specific stage (Stage or Live)
case 'stage':
$stage = $dataQuery->getQueryParam('Versioned.stage');
if ($stage && $stage != $this->defaultStage) {
foreach ($query->getFrom() as $table => $dummy) {
// Only rewrite table names that are actually part of the subclass tree
// This helps prevent rewriting of other tables that get joined in, in
// particular, many_many tables
if (class_exists($table) && ($table == $this->owner->class || is_subclass_of($table, $this->owner->class) || is_subclass_of($this->owner->class, $table))) {
$query->renameTable($table, $table . '_' . $stage);
}
}
}
break;
// Reading a specific stage, but only return items that aren't in any other stage
// Reading a specific stage, but only return items that aren't in any other stage
case 'stage_unique':
$stage = $dataQuery->getQueryParam('Versioned.stage');
// Recurse to do the default stage behavior (must be first, we rely on stage renaming happening before
// below)
$dataQuery->setQueryParam('Versioned.mode', 'stage');
$this->augmentSQL($query, $dataQuery);
$dataQuery->setQueryParam('Versioned.mode', 'stage_unique');
// Now exclude any ID from any other stage. Note that we double rename to avoid the regular stage rename
// renaming all subquery references to be Versioned.stage
foreach ($this->stages as $excluding) {
if ($excluding == $stage) {
continue;
}
$tempName = 'ExclusionarySource_' . $excluding;
$excludingTable = $baseTable . ($excluding && $excluding != $this->defaultStage ? "_{$excluding}" : '');
$query->addWhere('"' . $baseTable . '"."ID" NOT IN (SELECT "ID" FROM "' . $tempName . '")');
$query->renameTable($tempName, $excludingTable);
}
break;
// Return all version instances
// Return all version instances
case 'all_versions':
case 'latest_versions':
foreach ($query->getFrom() as $alias => $join) {
if ($alias != $baseTable) {
$query->setJoinFilter($alias, "\"{$alias}\".\"RecordID\" = \"{$baseTable}_versions\".\"RecordID\"" . " AND \"{$alias}\".\"Version\" = \"{$baseTable}_versions\".\"Version\"");
}
$query->renameTable($alias, $alias . '_versions');
}
// Add all <basetable>_versions columns
foreach (Config::inst()->get('Versioned', 'db_for_versions_table') as $name => $type) {
$query->selectField(sprintf('"%s_versions"."%s"', $baseTable, $name), $name);
}
// Alias the record ID as the row ID
$query->selectField(sprintf('"%s_versions"."%s"', $baseTable, 'RecordID'), "ID");
// Ensure that any sort order referring to this ID is correctly aliased
$orders = $query->getOrderBy();
foreach ($orders as $order => $dir) {
if ($order === "\"{$baseTable}\".\"ID\"") {
unset($orders[$order]);
$orders["\"{$baseTable}_versions\".\"RecordID\""] = $dir;
}
}
$query->setOrderBy($orders);
// latest_version has one more step
// Return latest version instances, regardless of whether they are on a particular stage
// This provides "show all, including deleted" functonality
if ($dataQuery->getQueryParam('Versioned.mode') == 'latest_versions') {
$query->addWhere("\"{$alias}_versions\".\"Version\" IN\n\t\t\t\t\t(SELECT LatestVersion FROM\n\t\t\t\t\t\t(SELECT\n\t\t\t\t\t\t\t\"{$alias}_versions\".\"RecordID\",\n\t\t\t\t\t\t\tMAX(\"{$alias}_versions\".\"Version\") AS LatestVersion\n\t\t\t\t\t\t\tFROM \"{$alias}_versions\"\n\t\t\t\t\t\t\tGROUP BY \"{$alias}_versions\".\"RecordID\"\n\t\t\t\t\t\t) AS \"{$alias}_versions_latest\"\n\t\t\t\t\t\tWHERE \"{$alias}_versions_latest\".\"RecordID\" = \"{$alias}_versions\".\"RecordID\"\n\t\t\t\t\t)");
} else {
// If all versions are requested, ensure that records are sorted by this field
$query->addOrderBy(sprintf('"%s_versions"."%s"', $baseTable, 'Version'));
}
break;
default:
throw new InvalidArgumentException("Bad value for query parameter Versioned.mode: " . $dataQuery->getQueryParam('Versioned.mode'));
}
}
