You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
975 lines
26 KiB
975 lines
26 KiB
<?php
|
|
use \SparkLib\DB;
|
|
use \SparkLib\Fail;
|
|
|
|
/**
|
|
* This is a modified SparkFinder for use with generated relationships. It
|
|
* is automatically available through __get magic on dinosaurs with a
|
|
* corresponding has_many defined in configuration/records.php. For now it
|
|
* uses a blanket 's' on the end of the class name for plurals, like Orders,
|
|
* Customers, or OrdersStatusHistorys =[
|
|
*
|
|
* See the last section of this file for the available SQL operations.
|
|
*
|
|
* EXAMPLE 1: Find a customer's 5 most recent orders.
|
|
*
|
|
* $customer = new CustomerSaurus(id);
|
|
* $orders = $customer->Orders->orderBy('date_purchased', 'DESC')->limit(5)->find();
|
|
* while($order = $orders->getNext())
|
|
* // whatever
|
|
*
|
|
* It should be noted that while a foreach() on the return of find() will
|
|
* also work, it loads everything into memory first and is thus a lot more
|
|
* costly than the above while() example, as that iterates over one result at
|
|
* a time. So, use while().
|
|
*
|
|
* EXAMPLE 2: Find a single storefront product by SKU:
|
|
*
|
|
* $product = StorefrontProductSaurus::finder()
|
|
* ->where('products_model')->eq('DEV-00666')
|
|
* ->find()->current();
|
|
*
|
|
* This uses the static finder() method available to all generated records.
|
|
* So, find() returns a SparkFriendFinder with the first result (the only
|
|
* one, in this case) preloaded, and current() returns it.
|
|
*/
|
|
class SparkFriendFinder extends \SparkLib\Iterator {
|
|
|
|
const NULLS_LAST = 'LAST';
|
|
const NULLS_FIRST = 'FIRST';
|
|
|
|
protected $_recordClass;
|
|
protected $_relationships;
|
|
|
|
// A class to instantiate with the record instance as its only
|
|
// parameter and return
|
|
protected $_wrapperClass = null;
|
|
|
|
// Table and primary key of the record class we're searching for.
|
|
protected $_table = null;
|
|
protected $_tableKey = null;
|
|
protected $_tableRecord = null;
|
|
|
|
protected $_sparkfinder = null;
|
|
protected $_total = null;
|
|
|
|
// Initial SELECT and WHERE.
|
|
protected $_default_query = '';
|
|
// Full query with constraints.
|
|
protected $_query = '';
|
|
|
|
// Those constraints:
|
|
protected $_distinctOn = '';
|
|
protected $_join = '';
|
|
protected $_with = '';
|
|
protected $_where = '';
|
|
protected $_orderBy = '';
|
|
protected $_groupBy = '';
|
|
protected $_limit = '';
|
|
protected $_forUpdate = '';
|
|
|
|
// For the time being these two will only get set in child classes of this one.
|
|
protected $_left_join = '';
|
|
protected $_extraselects = '';
|
|
|
|
protected $_key;
|
|
protected $_id;
|
|
|
|
protected $_buffered = true;
|
|
|
|
protected $_selectIdOnly = false;
|
|
|
|
/**
|
|
* Get a new finder. Takes a string containing the classname of dinosaur
|
|
* to instantiate, and an optional key and id.
|
|
*
|
|
* If a key and id are passed, we are looking for records in the context of
|
|
* another one, e.g. getting a customer's orders. Otherwise, we are
|
|
* (probably) using the static SomeRecord::finder() method.
|
|
*/
|
|
public function __construct ($record_class, $key = null, $id = null)
|
|
{
|
|
$this->_recordClass = $record_class;
|
|
$this->_key = $key;
|
|
$this->_id = $id;
|
|
|
|
// It's worth noting that class_exists() probably invokes the Autoloader here
|
|
if (! class_exists($this->_recordClass))
|
|
throw new InvalidArgumentException("No such record class: {$this->_recordClass}");
|
|
|
|
// Pull in some metadata from the record class:
|
|
$this->handleClass();
|
|
}
|
|
|
|
/**
|
|
* Re-run the previous query, starting over from the first record.
|
|
*/
|
|
public function rewind ()
|
|
{
|
|
if (! $this->_sparkfinder)
|
|
return false;
|
|
$this->getResults();
|
|
}
|
|
|
|
// Simple getters
|
|
public function key () { return $this->current()->getId(); }
|
|
public function recordClass () { return $this->_recordClass; }
|
|
public function tableKey () { return $this->_tableKey; }
|
|
public function tableRecord () { return $this->_tableRecord; }
|
|
|
|
/**
|
|
* Return current position within the resultset.
|
|
*/
|
|
public function position()
|
|
{
|
|
if (isset($this->_sparkfinder))
|
|
return $this->_sparkfinder->position();
|
|
else
|
|
return 0;
|
|
}
|
|
|
|
/**
|
|
* Check if there's a currently available record.
|
|
*
|
|
* @return bool - yay or nay
|
|
*/
|
|
public function valid ()
|
|
{
|
|
return $this->_sparkfinder->valid();
|
|
}
|
|
|
|
/**
|
|
* How many rows in the current statement results?
|
|
*/
|
|
public function count ()
|
|
{
|
|
if (! $this->_sparkfinder)
|
|
return false;
|
|
return $this->_sparkfinder->count();
|
|
}
|
|
|
|
/**
|
|
* How many rows total with these params?
|
|
*
|
|
* Keeps the joins, as they may affect the total; drops the _with because
|
|
* that's just overhead.
|
|
*/
|
|
public function total ()
|
|
{
|
|
$dbi = SparkDBI::getInstance();
|
|
|
|
if($this->_total)
|
|
return $this->_total;
|
|
|
|
$q = 'SELECT COUNT(' . $this->_table . '.' . $this->_tableKey . ') AS total ' .
|
|
' FROM ' . $this->_table .
|
|
$this->_join .
|
|
$this->_left_join .
|
|
' WHERE true ' .
|
|
$this->_where;
|
|
|
|
$totalArray = $dbi->fetchRow($q);
|
|
$this->_total = (int)$totalArray['total'];
|
|
|
|
return $this->_total;
|
|
}
|
|
|
|
/**
|
|
* How many rows total with these params?
|
|
*
|
|
* use this if you have a group by statement in your query
|
|
*/
|
|
public function group_total ()
|
|
{
|
|
$dbi = SparkDBI::getInstance();
|
|
|
|
if ($this->_total)
|
|
return $this->_total;
|
|
|
|
$q = 'SELECT COUNT(0) AS total FROM (SELECT ' . $this->_table . '.' . $this->_tableKey .
|
|
' FROM "' . $this->_table . '"' .
|
|
$this->_join .
|
|
$this->_left_join .
|
|
' WHERE true ' .
|
|
$this->_where .
|
|
$this->_groupBy .
|
|
') g';
|
|
|
|
$totalArray = $dbi->fetchRow($q);
|
|
$this->_total = $totalArray['total'];
|
|
|
|
return $this->_total;
|
|
}
|
|
|
|
/**
|
|
* Advance to the next record.
|
|
*/
|
|
public function next ()
|
|
{
|
|
if (! $this->_sparkfinder)
|
|
throw new Exception('No current results - find() not called?');
|
|
return $this->_sparkfinder->next();
|
|
}
|
|
|
|
/**
|
|
* Return an object for the current record.
|
|
*
|
|
* @return SomeSaurus - a $this->_recordClass
|
|
*/
|
|
public function current ()
|
|
{
|
|
if (! $this->valid())
|
|
return false;
|
|
|
|
if ($this->_wrapperClass) {
|
|
$wrapper = $this->_wrapperClass;
|
|
// yo dawg, I heard you like classes
|
|
return new $wrapper($this->_sparkfinder->current());
|
|
}
|
|
|
|
return $this->_sparkfinder->current();
|
|
}
|
|
|
|
/**
|
|
* Load the next record and return an object for it.
|
|
*
|
|
* @return SomeSaurus - a $this->_recordClass
|
|
*/
|
|
public function getNext ()
|
|
{
|
|
// Assume we want to go ahead and find() if there aren't
|
|
// already results to get
|
|
if (! isset($this->_sparkfinder))
|
|
$this->find();
|
|
|
|
// Will be a record object or false;
|
|
$result = $this->current();
|
|
|
|
// If we got an object, we'll advance to the next record.
|
|
if (false !== $result)
|
|
$this->next();
|
|
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* @return string current query
|
|
*/
|
|
public function getQuery ()
|
|
{
|
|
return $this->_query;
|
|
}
|
|
|
|
/**
|
|
* @return true if there are more available.
|
|
*/
|
|
public function hasNext ()
|
|
{
|
|
return $this->count() && ($this->_sparkfinder->position() < $this->count());
|
|
}
|
|
|
|
/**
|
|
* Load some class properties with meta data about $this->_recordClass.
|
|
*/
|
|
protected function handleClass ()
|
|
{
|
|
// Hack our way around some PHP fail:
|
|
$rc = $this->_recordClass;
|
|
|
|
// If these methods exist, we're probably dealing with a SparkRecord.
|
|
// Otherwise, it's something else.
|
|
if (method_exists($rc, 'getTableKey')) {
|
|
$this->_tableKey = $rc::getDefaultTableKey();
|
|
$this->_table = $rc::getDefaultTableName();
|
|
$this->_tableRecord = $rc::getDefaults();
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Set a wrapper class for results.
|
|
*/
|
|
public function wrap ($class)
|
|
{
|
|
if (class_exists($class)) {
|
|
$this->_wrapperClass = $class;
|
|
} else {
|
|
throw new Exception($class . ' does not exist');
|
|
}
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* Build the query.
|
|
*
|
|
* @return string sql
|
|
*/
|
|
public function buildQuery ()
|
|
{
|
|
// Build and save the query
|
|
$this->_query = $this->_base_query() .
|
|
$this->_where .
|
|
$this->_groupBy .
|
|
$this->_orderBy .
|
|
$this->_limit .
|
|
$this->_forUpdate . "\n";
|
|
|
|
return $this->_query;
|
|
}
|
|
|
|
/**
|
|
* Build the query, execute it, and load the first object.
|
|
*
|
|
* @return SparkFriendFinder - $this, for method chains.
|
|
*/
|
|
public function find ()
|
|
{
|
|
// Build and save the query
|
|
$this->buildQuery();
|
|
|
|
// clear query parts
|
|
$this->_join = '';
|
|
$this->_where = '';
|
|
$this->_orderBy = '';
|
|
$this->_limit = '';
|
|
$this->_with = '';
|
|
|
|
// Get a SparkFinder and stash it
|
|
$this->getResults();
|
|
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* Get a database handle.
|
|
*/
|
|
protected function getDBH ()
|
|
{
|
|
return DB::getInstance();
|
|
}
|
|
|
|
/**
|
|
* Turn the current query into a result set and stash it.
|
|
*/
|
|
protected function getResults ()
|
|
{
|
|
try {
|
|
$this->_sparkfinder = new SparkFinder(
|
|
$this->_recordClass,
|
|
$this->_query
|
|
);
|
|
} catch (Exception $e) {
|
|
throw new Exception('Query fail: ' . $this->_query . ' -- ' . $e->getMessage());
|
|
}
|
|
|
|
// PDO TODO: Come up with a graceful way to request an unbuffered resultset.
|
|
// (Presumably necessitates a connection per resultset.)
|
|
}
|
|
|
|
/**
|
|
* Make the query a FOR UPDATE query.
|
|
*
|
|
* Essentially what this does is put a row lock on
|
|
* every row that is part of the SELECT query such that
|
|
* any other query (with a FOR UPDATE clause) that matches
|
|
* any of the selected rows for this finder will block
|
|
* until the current transaction has completed, either by
|
|
* a commit or rollback.
|
|
*/
|
|
public function forUpdate () {
|
|
$this->_forUpdate = ' FOR UPDATE';
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* Run find() and get the first result. Just a utility to shorten
|
|
* things up a bit. If find() has already been called, will act
|
|
* as an alias to current(), rather than re-running a bare query.
|
|
*
|
|
* @return SparkRecord
|
|
*/
|
|
public function getOne ()
|
|
{
|
|
if (isset($this->_sparkfinder))
|
|
return $this->current();
|
|
return $this->find()->current();
|
|
}
|
|
|
|
/**
|
|
* JOIN the given dinosaur by the relationship defined in records.ini
|
|
*/
|
|
public function with ($dinosaur)
|
|
{
|
|
$record_class = $this->_recordClass;
|
|
$relationships = $record_class::getRelationships();
|
|
|
|
if (! isset($relationships[$dinosaur]))
|
|
throw new Exception('Invalid relationship: ' . $dinosaur);
|
|
|
|
if(in_array($relationships[$dinosaur]['type'], array('has_one', 'has_many'))) {
|
|
$our_key = $relationships[$dinosaur]['pk'];
|
|
$their_key = $relationships[$dinosaur]['fk'];
|
|
} elseif($relationships[$dinosaur]['type'] == 'belongs_to') {
|
|
$our_key = $relationships[$dinosaur]['fk'];
|
|
$their_key = $relationships[$dinosaur]['pk'];
|
|
}
|
|
|
|
// If this is a one to many relationship, use a left join
|
|
/*
|
|
$has_many = $record_class::getHasManyDefaults();
|
|
if($has_many[$dinosaur])
|
|
$this->_join .= ' LEFT';
|
|
*/
|
|
|
|
$classname = $relationships[$dinosaur]['class'];
|
|
$this->_with .= $this->_generate_with($classname);
|
|
$this->_join .= " JOIN {$classname::getDefaultTableName()} {$classname::getDefaultTableName()} ON {$this->_table}.{$our_key} = {$classname::getDefaultTableName()}.{$their_key} \n";
|
|
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* JOIN another dinosaur by the relationship
|
|
* defined in records.ini
|
|
* TODO: Maybe merge this with with() or something less verbose?
|
|
*/
|
|
public function withOn ($dinosaur, $joinon)
|
|
{
|
|
$record_class = $joinon . 'Saurus';
|
|
$relationships = $record_class::getRelationships();
|
|
|
|
if(! isset($relationships[$dinosaur]))
|
|
throw new Exception('Invalid relationship: ' . $dinosaur . ' on ' . $record_class);
|
|
|
|
if(in_array($relationships[$dinosaur]['type'], array('has_one', 'has_many'))) {
|
|
$our_key = $relationships[$dinosaur]['pk'];
|
|
$their_key = $relationships[$dinosaur]['fk'];
|
|
} elseif($relationships[$dinosaur]['type'] == 'belongs_to') {
|
|
$our_key = $relationships[$dinosaur]['fk'];
|
|
$their_key = $relationships[$dinosaur]['pk'];
|
|
}
|
|
|
|
$classname = $relationships[$dinosaur]['class'];
|
|
$this->_with .= $this->_generate_with($classname);
|
|
$this->_join .= " JOIN {$classname::getDefaultTableName()} {$classname::getDefaultTableName()} ON {$record_class::getDefaultTableName()}.{$our_key} = {$classname::getDefaultTableName()}.{$their_key} \n";
|
|
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* LEFT JOIN a table
|
|
*
|
|
* @param string $sql LEFT JOIN table on pkey = fkey
|
|
*/
|
|
public function leftJoin ($sql)
|
|
{
|
|
$sql = trim($sql);
|
|
|
|
if (! strlen($sql))
|
|
return $this;
|
|
|
|
// make sure it is left join sql
|
|
if (strtoupper(substr($sql, 0, 9)) != 'LEFT JOIN')
|
|
throw new Exception('Invalid LEFT JOIN clause: ' . $sql);
|
|
|
|
$this->_left_join = ' ' . $sql . ' ';
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* put some extra fields in the SELECT
|
|
* for use with leftJoin() or sql functions
|
|
*
|
|
* BEWARE! If you use this in public-facing code with user inputs,
|
|
* Brennen WILL murder you.
|
|
*
|
|
* @param array $fields fields or sql expressions like COUNT(*)
|
|
*/
|
|
public function extraSelects ($fields)
|
|
{
|
|
if (! is_array($fields) || ! count($fields))
|
|
throw new Exception('extraSelects expects a non-empty array');
|
|
|
|
$sql = implode(', ', $fields) . ','; // needs trailing comma
|
|
|
|
$this->_extraselects = $sql . ' ';
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* To select only the primary table id and any extra selects
|
|
* (instead of select * from all tables)
|
|
*
|
|
*/
|
|
public function selectIdOnly ()
|
|
{
|
|
$this->_selectIdOnly = true;
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* FIELD table field name
|
|
* **BEWARE: This function is rendered useless with a join or extraselects.
|
|
*/
|
|
public function isValidField ($field)
|
|
{
|
|
if ($this->_join === '' && $this->_left_join === '' && $this->_extraselects === '') {
|
|
return isset($this->_tableRecord[$field]);
|
|
} else {
|
|
return true;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Add to the WHERE clause for a given field.
|
|
*
|
|
* Be careful - this may fail if you're doing a ->with() _after_ the where().
|
|
* Do with() first.
|
|
*
|
|
* @param $field string
|
|
* @return SparkFriendFinder
|
|
*/
|
|
public function where ($field)
|
|
{
|
|
if (!$this->isValidField($field))
|
|
throw new Exception('Invalid field in where clause: ' . $field);
|
|
|
|
$this->_where .= "\n AND " . $field;
|
|
return $this;
|
|
}
|
|
|
|
public function whereSubstring($field, $from, $for)
|
|
{
|
|
if (!$this->isValidField($field))
|
|
throw new Exception('Invalid field in where clause: ' . $field);
|
|
|
|
if (!is_integer($from) || !is_integer($for))
|
|
throw new Exception('Invalid from/for in whereSubstring clause ' . $field);
|
|
|
|
$this->_where .= "\n AND substring(" . $field . " from " . $from . " for " . $for . ")";
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* Add a WHERE lower($field)
|
|
*
|
|
* @param $field string
|
|
* @return SparkFriendFinder
|
|
*/
|
|
public function whereLower ($field)
|
|
{
|
|
if (!$this->isValidField($field))
|
|
throw new Exception('Invalid field in where clause: ' . $field);
|
|
|
|
$this->_where .= "\n AND lower(" . $field . ")";
|
|
return $this;
|
|
}
|
|
|
|
public function orWhere ($field)
|
|
{
|
|
if (!$this->isValidField($field))
|
|
throw new Exception('Invalid field in where clause: ' . $field);
|
|
|
|
$this->_where .= "\n OR " . $field;
|
|
return $this;
|
|
}
|
|
|
|
public function orWhereSubstring($field, $from, $for)
|
|
{
|
|
if (!$this->isValidField($field))
|
|
throw new Exception('Invalid field in where clause: ' . $field);
|
|
|
|
if (!is_integer($from) || !is_integer($for))
|
|
throw new Exception('Invalid from/for in orWhereSubstring clause ' . $field);
|
|
|
|
$this->_where .= "\n OR substring(" . $field . " from " . $from . " for " . $for . ")";
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* Add to the WHERE clause for a given subquery evaluation.
|
|
*
|
|
* Like with where() be careful - this may fail if you're doing
|
|
* a ->with() _after_ the where(). Do with() first.
|
|
*
|
|
* @param $subquery string
|
|
* @return SparkFriendFinder
|
|
*/
|
|
public function subquery ($subquery)
|
|
{
|
|
$this->_where .= "\n AND (\n"
|
|
. $subquery . "\n"
|
|
. ")";
|
|
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* startSub - begin a sub-where clause in the query.
|
|
*
|
|
* example:
|
|
*
|
|
* $orders = OrderSaurus::finder()
|
|
* $orders->startSub();
|
|
* $orders->orWhere('customers_name')->like('%' . $s_query . '%');
|
|
* $orders->orWhere('customers_email_address')->like('%' . $s_query . '%');
|
|
* $orders->endSub();
|
|
* $orders->find();
|
|
*
|
|
* @param $op string of either AND or OR to prepend to the outside of the subclause
|
|
* @param $subop string of either AND or OR which will affect the subclause
|
|
*/
|
|
public function startSub ($op = 'AND', $subop = 'OR')
|
|
{
|
|
if('AND' == strtoupper($op))
|
|
$this->_where .= "\n AND ( ";
|
|
if('OR' == strtoupper($op))
|
|
$this->_where .= "\n OR ( ";
|
|
|
|
if('AND' == strtoupper($subop))
|
|
$this->_where .= ' true ';
|
|
if('OR' == strtoupper($subop))
|
|
$this->_where .= ' false ';
|
|
|
|
return $this;
|
|
}
|
|
|
|
public function endSub ()
|
|
{
|
|
$this->_where .= "\n ) ";
|
|
return $this;
|
|
}
|
|
|
|
public function orderBy ($field, $direction = 'ASC', $nulls = null)
|
|
{
|
|
if ('ASC' != $direction && 'DESC' != $direction)
|
|
throw new Exception('Invalid direction in "orderBy" clause: ' . $direction);
|
|
|
|
if (isset($nulls) && (('FIRST' !== $nulls) && ('LAST' !== $nulls)))
|
|
throw new Exception('Invalid NULLS in "orderBy" clause: ' . $nulls);
|
|
|
|
if ($field instanceof \SparkLib\DB\Literal) {
|
|
// right now, this just handles Random() - might be other things, tho:
|
|
$field = $field->literal();
|
|
} else {
|
|
// it's a string - check whether it's safe to order by
|
|
// We're unable to validate fields if a FIND_IN_SET or COALESCE is present.
|
|
if (!stristr($field, 'FIND_IN_SET') && !stristr($field, 'COALESCE')) {
|
|
if (!$this->isValidField($field))
|
|
throw new Exception('Invalid field in orderBy clause: ' . $field);
|
|
}
|
|
}
|
|
|
|
if ('' == $this->_orderBy)
|
|
$this->_orderBy = ' ORDER BY ' . $field . ' ' . $direction;
|
|
else
|
|
$this->_orderBy .= ', ' . $field . ' ' . $direction;
|
|
|
|
if (isset($nulls))
|
|
$this->_orderBy .= ' NULLS ' . $nulls;
|
|
|
|
return $this;
|
|
}
|
|
|
|
public function groupBy ($field)
|
|
{
|
|
// We're unable to validate fields if a FIND_IN_SET is present.
|
|
if (!stristr($field, 'FIND_IN_SET')) {
|
|
if (!$this->isValidField($field) && $field !== self::RAND)
|
|
throw new Exception('Invalid field in "groupBy" clause: ' . $field);
|
|
}
|
|
|
|
if ('' == $this->_groupBy)
|
|
$this->_groupBy = ' GROUP BY ' . $field;
|
|
else
|
|
$this->_groupBy .= ', ' . $field;
|
|
|
|
return $this;
|
|
}
|
|
|
|
public function distinctOn ($field)
|
|
{
|
|
// We're unable to validate fields if a FIND_IN_SET is present.
|
|
if (!stristr($field, 'FIND_IN_SET')) {
|
|
if (!$this->isValidField($field) && $field !== self::RAND)
|
|
throw new Exception('Invalid field in "distinct" clause: ' . $field);
|
|
}
|
|
|
|
if ('' == $this->_distinctOn)
|
|
$this->_distinctOn = ' DISTINCT ON (' . $field . ')';
|
|
else
|
|
$this->_distinctOn = str_replace(')', ',', $this->_distinctOn) . $field . ')';
|
|
|
|
return $this;
|
|
|
|
}
|
|
|
|
/**
|
|
* PDO TODO: document
|
|
*/
|
|
public function limit ($limit, $offset = 0)
|
|
{
|
|
$limit = (int) $limit;
|
|
$offset = (int) $offset;
|
|
|
|
if ('' != $this->_limit)
|
|
throw new Exception('You may only impose one "limit" clause.');
|
|
|
|
if($limit > 0)
|
|
$this->_limit = ' LIMIT ' . $limit;
|
|
|
|
$this->_limit .= ' OFFSET ' . $offset;
|
|
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* PDO TODO: document
|
|
*/
|
|
public function eq ($val)
|
|
{
|
|
if (is_array($val)) {
|
|
$this->_where .= ' IN(';
|
|
foreach($val as $i => $v) {
|
|
$this->_where .= $this->render($v);
|
|
if($i+1 < count($val))
|
|
$this->_where .= ',';
|
|
}
|
|
$this->_where .= ')';
|
|
} else {
|
|
$this->_where .= ' = ' . $this->render($val);
|
|
}
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* A hack to look for the lower-case version of a string.
|
|
*
|
|
* We'll let the database handle lower-casing, since there's a good
|
|
* chance it's smarter than PHP.
|
|
*/
|
|
public function eqLower ($val)
|
|
{
|
|
if (! is_string($val)) {
|
|
throw new \Exception('lowerEq() expects a string, for now');
|
|
}
|
|
|
|
$this->_where .= ' = lower(' . $this->render($val) . ')';
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* PDO TODO: document
|
|
*/
|
|
public function ne ($val)
|
|
{
|
|
if (is_array($val)) {
|
|
$this->_where .= ' NOT IN(';
|
|
foreach($val as $i => $v) {
|
|
$this->_where .= $this->render($v);
|
|
if($i+1 < count($val))
|
|
$this->_where .= ',';
|
|
}
|
|
$this->_where .= ')';
|
|
} else {
|
|
$this->_where .= ' <> ' . $this->render($val);
|
|
}
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* IS - use with a DB\Null, for example.
|
|
*/
|
|
public function is ($val)
|
|
{
|
|
$this->_where .= ' IS ' . $this->render($val);
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* IS NOT - use with a DB\Null, for example.
|
|
*/
|
|
public function isnot ($val)
|
|
{
|
|
$this->_where .= ' IS NOT ' . $this->render($val);
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* IN()
|
|
* Added so we can use sub-selects. NOT ESCAPED.
|
|
* eq/ne should be used under normal circumstances
|
|
*/
|
|
public function in ($val)
|
|
{
|
|
// this isn't escaped because we need IN((query)) to be quote-free.
|
|
// be seriously fucking careful.
|
|
$this->_where .= ' IN(' . $val . ')';
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* like in, but not. NOT ESCAPED.
|
|
*/
|
|
public function nin ($val)
|
|
{
|
|
// this isn't escaped because we need NOT IN((query)) to be quote-free.
|
|
// be really goddamned careful.
|
|
$this->_where .= ' NOT IN(' . $val . ')';
|
|
return $this;
|
|
}
|
|
|
|
public function gt ($val) { $this->_where .= ' > ' . $this->render($val); return $this; }
|
|
|
|
public function lt ($val) { $this->_where .= ' < ' . $this->render($val); return $this; }
|
|
|
|
public function between($val1, $val2) { $this->_where .= ' BETWEEN ' . $this->render($val1) . ' AND ' . $this->render($val2); return $this; }
|
|
|
|
public function gte ($val) { $this->_where .= ' >= ' . $this->render($val); return $this; }
|
|
|
|
public function lte ($val) { $this->_where .= ' <= ' . $this->render($val); return $this; }
|
|
|
|
public function like ($val) { $this->_where .= ' LIKE ' . $this->render($val); return $this; }
|
|
|
|
public function plus ($val) { $this->_where .= ' + ' . $this->render($val); return $this; }
|
|
|
|
public function minus ($val) { $this->_where .= ' - ' . $this->render($val); return $this; }
|
|
|
|
/**
|
|
* Case-insensitive like.
|
|
*/
|
|
public function ilike ($val)
|
|
{
|
|
// MySQL doesn't support ILIKE
|
|
if ('mysql' !== constant('\DB_SERVER_TYPE')) {
|
|
$this->_where .= ' ILIKE ' . $this->render($val);
|
|
return $this;
|
|
}
|
|
return $this->like($val);
|
|
}
|
|
|
|
public function has($val) {
|
|
return $this->like('%' . $val . '%');
|
|
}
|
|
|
|
public function ihas($val) {
|
|
return $this->ilike('%' . $val . '%');
|
|
}
|
|
|
|
public function nlike ($val) { $this->_where .= ' NOT LIKE ' . $this->render($val); return $this; }
|
|
|
|
/**
|
|
* Case-insensitive nlike.
|
|
*/
|
|
public function nilike ($val)
|
|
{
|
|
// MySQL doesn't support ILIKE
|
|
if ('mysql' !== constant('\DB_SERVER_TYPE')) {
|
|
$this->_where .= ' NOT ILIKE ' . $this->render($val);
|
|
return $this;
|
|
}
|
|
return $this->nlike($val);
|
|
}
|
|
|
|
/**
|
|
* Use buffered results.
|
|
*/
|
|
public function buffered ()
|
|
{
|
|
$this->_buffered = true;
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* Use unbuffered results.
|
|
*/
|
|
public function unbuffered ()
|
|
{
|
|
// PDO TODO: it would be nice to make unbuffered resultsets available
|
|
throw new \Exception('unbuffered queries are not yet implemented for SparkFriendFinder');
|
|
$this->_buffered = false;
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* Custom escape to quote and otherwise render strings.
|
|
*/
|
|
protected function render ($value)
|
|
{
|
|
$value = $this->typeJuggle($value);
|
|
if ($value instanceof DB\Literal) {
|
|
return $value->literal();
|
|
}
|
|
return $this->getDBH()->quote($value);
|
|
}
|
|
|
|
/**
|
|
* Normalize values to the scheme expected by render()
|
|
*/
|
|
protected function typeJuggle ($val)
|
|
{
|
|
// promote actual PHP nulls to SQL nulls
|
|
if (is_null($val))
|
|
$val = new DB\Null;
|
|
|
|
if ($val instanceof DB\Literal)
|
|
return $val;
|
|
|
|
if (is_string($val))
|
|
return $val;
|
|
|
|
if (is_bool($val)) {
|
|
if ($val)
|
|
$val = new DB\True;
|
|
else
|
|
$val = new DB\False;
|
|
}
|
|
|
|
return $val;
|
|
}
|
|
|
|
|
|
/**
|
|
* Initial SELECT and WHERE.
|
|
*
|
|
* If a JOIN is present, it will be included.
|
|
*/
|
|
protected function _base_query ()
|
|
{
|
|
$this->_default_query = 'SELECT ' .
|
|
$this->_distinctOn . ' ' .
|
|
$this->_extraselects;
|
|
|
|
// if true, only primary table key and extra selects are selected
|
|
if ($this->_selectIdOnly) {
|
|
$this->_default_query .= $this->_table . '.' . $this->_tableKey;
|
|
} else {
|
|
$this->_default_query .= $this->_table . '.* ' .
|
|
$this->_with;
|
|
}
|
|
|
|
$this->_default_query .= ' FROM ' . $this->_table .
|
|
$this->_join .
|
|
$this->_left_join .
|
|
' WHERE ' . (($this->_key && $this->_id) ? $this->_key . ' = ' . $this->_id : 'true ');
|
|
|
|
return $this->_default_query;
|
|
}
|
|
|
|
/**
|
|
* Generates a string which selects and aliases
|
|
* each column for a given dinosaur.
|
|
*/
|
|
protected function _generate_with ($dinosaur)
|
|
{
|
|
$with = '';
|
|
|
|
$table = $dinosaur::getDefaultTableName();
|
|
$columns = array_keys($dinosaur::getDefaults());
|
|
foreach ($columns as $column) {
|
|
$with .= ", " . $table . '.' . $column . ' AS "' . $dinosaur::getBaseName() . '__' . $column . '"';
|
|
}
|
|
|
|
return $with;
|
|
}
|
|
|
|
}
|