public function importPass()
{
$query = $this->getRemoteObjectsQuery();
$items = $this->task->query($query);
$itemsCount = $items->numRecords();
$this->task->message(" * Found {$itemsCount} items to import");
$total = 0;
$updated = 0;
$inserted = 0;
foreach ($items as $item) {
$this->task->progress(++$total, $itemsCount);
// Build select query for existing object
$values = array();
$select = new SQLQuery("ID", "\"{$this->tableName}\"");
foreach ($this->fields as $field => $class) {
$value = intval($item[$field]);
$values[] = $value;
$select->addWhere(sprintf("\"{$field}\" = %d", $value));
}
$values[] = $item['ID'];
// Check for existing record
if ($localID = DB::query($select->sql())->value()) {
// Update local many_many record instead of making new row
DB::query(sprintf('UPDATE "%s" SET "LegacyID" = %d WHERE "ID" = %d', $this->tableName, $item['ID'], $localID));
$updated++;
} else {
// Insert mapping into the many_many table
$insert = "INSERT INTO \"{$this->tableName}\" (";
$insert .= '"' . implode('", "', array_keys($this->fields)) . '"';
$insert .= ', LegacyID';
$insert .= ') VALUES (';
$insert .= implode(', ', $values);
$insert .= ')';
DB::query($insert);
$localID = DB::getGeneratedID($this->tableName);
$inserted++;
}
// Mark this relation as imported in the remote table
$conn = $this->task->getRemoteConnection();
$conn->query(sprintf('UPDATE "%s" SET "_ImportedID" = %d, "_ImportedDate" = NOW() WHERE "ID" = %d', $this->tableName, $localID, $item['ID']));
}
// Done!
$this->task->message(" * Result: {$inserted} added, {$updated} updated");
}
private function checkBlogEntryPermissions()
{
$authorsId = array();
$sqlQuery = new SQLQuery();
$sqlQuery->setFrom('SiteTree_versions');
$sqlQuery->selectField('AuthorID');
$sqlQuery->addWhere('RecordID = ' . $this->ID);
$sqlQuery->setOrderBy('ID DESC');
$rawSQL = $sqlQuery->sql();
$result = $sqlQuery->execute();
foreach ($result as $row) {
$authorsId[] = $row['AuthorID'];
}
$sqlQuery->setDelete(true);
if (in_array(Member::currentUser()->ID, $authorsId) || $this->parent->OwnerID == Member::currentUser()->ID || Permission::check('ADMIN')) {
return true;
} else {
return false;
}
}
public function testSelect()
{
$query = new SQLQuery('"Title"', '"MyTable"');
$query->addSelect('"TestField"');
$this->assertEquals('SELECT "Title", "TestField" FROM "MyTable"', $query->sql());
// Test replacement of select
$query->setSelect(array('Field' => '"Field"', 'AnotherAlias' => '"AnotherField"'));
$this->assertEquals('SELECT "Field", "AnotherField" AS "AnotherAlias" FROM "MyTable"', $query->sql());
// Check that ' as ' selects don't get mistaken as aliases
$query->addSelect(array('Relevance' => "MATCH (Title, MenuTitle) AGAINST ('Two as One')"));
$this->assertEquals('SELECT "Field", "AnotherField" AS "AnotherAlias", MATCH (Title, MenuTitle) AGAINST (' . '\'Two as One\') AS "Relevance" FROM "MyTable"', $query->sql());
}
public function testJoinSubSelect()
{
$query = new SQLQuery();
$query->setFrom('MyTable');
$query->addInnerJoin('(SELECT * FROM MyOtherTable)', 'Mot.MyTableID = MyTable.ID', 'Mot');
$query->addLeftJoin('(SELECT MyLastTable.MyOtherTableID, COUNT(1) as MyLastTableCount FROM MyLastTable ' . 'GROUP BY MyOtherTableID)', 'Mlt.MyOtherTableID = Mot.ID', 'Mlt');
$query->setOrderBy('COALESCE(Mlt.MyLastTableCount, 0) DESC');
$this->assertSQLEquals('SELECT *, COALESCE(Mlt.MyLastTableCount, 0) AS "_SortColumn0" FROM MyTable ' . 'INNER JOIN (SELECT * FROM MyOtherTable) AS "Mot" ON Mot.MyTableID = MyTable.ID ' . 'LEFT JOIN (SELECT MyLastTable.MyOtherTableID, COUNT(1) as MyLastTableCount FROM MyLastTable ' . 'GROUP BY MyOtherTableID) AS "Mlt" ON Mlt.MyOtherTableID = Mot.ID ' . 'ORDER BY "_SortColumn0" DESC', $query->sql($parameters));
}
public function testWhereAny()
{
$query = new SQLQuery();
$query->setFrom('MyTable');
$query->whereAny(array("Monkey = 'Chimp'", "Color = 'Brown'"));
$this->assertEquals("SELECT * FROM MyTable WHERE (Monkey = 'Chimp' OR Color = 'Brown')", $query->sql());
}
function augmentSQL(SQLQuery &$query)
{
if (!$this->stat('enabled')) {
return false;
}
if (($lang = self::current_lang()) && !self::is_default_lang() || self::$bypass) {
foreach ($query->from as $table => $dummy) {
if (!isset($baseTable)) {
$baseTable = $table;
}
if (self::table_exists("{$table}_lang")) {
$query->renameTable($table, $table . '_lang');
if (stripos($query->sql(), '.ID')) {
// Every reference to ID is now OriginalLangID
$query->replaceText(".ID", ".OriginalLangID");
$query->where = str_replace("`ID`", "`OriginalLangID`", $query->where);
$query->select[] = "`{$baseTable}_lang`.OriginalLangID AS ID";
}
if ($query->where) {
foreach ($query->where as $i => $wherecl) {
if (substr($wherecl, 0, 4) == 'ID =') {
// Another reference to ID to be changed
$query->where[$i] = str_replace('ID =', 'OriginalLangID =', $wherecl);
} else {
$parts = explode(' AND ', $wherecl);
foreach ($parts as $j => $part) {
// Divide this clause between the left ($innerparts[1]) and right($innerparts[2]) part of the condition
ereg('(`?[[:alnum:]_-]*`?\\.?`?[[:alnum:]_-]*`?)(.*)', $part, $innerparts);
if (strpos($innerparts[1], '.') === false) {
//it may be ambiguous, so sometimes we will need to add the table
$parts[$j] = ($this->isInAugmentedTable($innerparts[1], $table) ? "`{$table}_lang`." : "") . "{$part}";
} else {
/* if the table has been specified we have to determine if the original (without _lang) name has to be used
* because we don't have the queried field in the augmented table (which usually means
* that is not a translatable field)
*/
$clauseparts = explode('.', $innerparts[1]);
$originalTable = str_replace('`', '', str_replace('_lang', '', $clauseparts[0]));
$parts[$j] = ($this->isInAugmentedTable($clauseparts[1], $originalTable) ? "`{$originalTable}_lang`" : "`{$originalTable}`") . ".{$clauseparts[1]}{$innerparts[2]}";
}
}
$query->where[$i] = implode(' AND ', $parts);
}
}
}
if ($table != $baseTable) {
$query->from["{$table}_lang"] = $query->from[$table];
} else {
// _lang is now the base table (the first one)
$query->from = array("{$table}_lang" => $query->from[$table]) + $query->from;
}
// unless we are bypassing this query, add the language filter
if (!self::$bypass) {
$query->where[] = "`{$table}_lang`.Lang = '{$lang}'";
}
// unless this is a deletion, the query is applied to the joined table
if (!$query->delete) {
$query->from[$table] = "INNER JOIN `{$table}`" . " ON `{$table}_lang`.OriginalLangID = `{$table}`.ID";
/* if we are selecting fields (not doing counts for example) we need to select everything from
* the original table (was renamed to _lang) since some fields that we require may be there
*/
if ($query->select[0][0] == '`') {
$query->select = array_merge(array("`{$table}`.*"), $query->select);
}
} else {
unset($query->from[$table]);
}
} else {
$query->from[$table] = str_replace("`{$table}`.OriginalLangID", "`{$table}`.ID", $query->from[$table]);
}
}
}
}
function testSelectWithOrderbyClause() {
// numeric limit
$query = new SQLQuery();
$query->from[] = "MyTable";
$query->orderby('MyName ASC');
// can't escape as we don't know if ASC or DESC is appended
$this->assertEquals("SELECT * FROM MyTable ORDER BY MyName ASC", $query->sql());
// array limit
$query = new SQLQuery();
$query->from[] = "MyTable";
$query->orderby(array('sort'=>'MyName'));
$this->assertEquals("SELECT * FROM MyTable ORDER BY `MyName`", $query->sql());
// array limit with start (MySQL specific)
$query = new SQLQuery();
$query->from[] = "MyTable";
$query->orderby(array('sort'=>'MyName','dir'=>'desc'));
$this->assertEquals("SELECT * FROM MyTable ORDER BY `MyName` DESC", $query->sql());
}
public function testInnerJoin()
{
$query = new SQLQuery();
$query->from('MyTable');
$query->innerJoin('MyOtherTable', 'MyOtherTable.ID = 2');
$query->leftJoin('MyLastTable', 'MyOtherTable.ID = MyLastTable.ID');
$this->assertEquals('SELECT * FROM MyTable ' . 'INNER JOIN "MyOtherTable" AS "MyOtherTable" ON MyOtherTable.ID = 2 ' . 'LEFT JOIN "MyLastTable" AS "MyLastTable" ON MyOtherTable.ID = MyLastTable.ID', $query->sql());
$query = new SQLQuery();
$query->from('MyTable');
$query->innerJoin('MyOtherTable', 'MyOtherTable.ID = 2', 'table1');
$query->leftJoin('MyLastTable', 'MyOtherTable.ID = MyLastTable.ID', 'table2');
$this->assertEquals('SELECT * FROM MyTable ' . 'INNER JOIN "MyOtherTable" AS "table1" ON MyOtherTable.ID = 2 ' . 'LEFT JOIN "MyLastTable" AS "table2" ON MyOtherTable.ID = MyLastTable.ID', $query->sql());
}
/**
* Run a remote query against the remote DB
*
* @param SQLQuery $query
* @return SS_Query
*/
public function query(SQLQuery $query)
{
return $this->getRemoteConnection()->query($query->sql());
}
/**
* Test deprecation of SQLQuery::setDelete/getDelete
*/
public function testDeprecatedSetDelete()
{
// Temporarily disable deprecation
Deprecation::notification_version(null);
$query = new SQLQuery();
$query->setSelect(array('"SQLQueryTest_DO"."Name"'));
$query->setFrom('"SQLQueryTest_DO"');
$query->setWhere(array('"SQLQueryTest_DO"."Name"' => 'Andrew'));
// Check SQL for select
$this->assertSQLEquals(<<<EOS
SELECT "SQLQueryTest_DO"."Name" FROM "SQLQueryTest_DO"
WHERE ("SQLQueryTest_DO"."Name" = ?)
EOS
, $query->sql($parameters));
$this->assertEquals(array('Andrew'), $parameters);
// Check setDelete works
$query->setDelete(true);
$this->assertSQLEquals(<<<EOS
DELETE FROM "SQLQueryTest_DO"
WHERE ("SQLQueryTest_DO"."Name" = ?)
EOS
, $query->sql($parameters));
$this->assertEquals(array('Andrew'), $parameters);
// Check that setDelete back to false restores the state
$query->setDelete(false);
$this->assertSQLEquals(<<<EOS
SELECT "SQLQueryTest_DO"."Name" FROM "SQLQueryTest_DO"
WHERE ("SQLQueryTest_DO"."Name" = ?)
EOS
, $query->sql($parameters));
$this->assertEquals(array('Andrew'), $parameters);
}
/**
* Polls the message queue. Will return a map of senders with the lastest message ID
* @see http://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results
* @param SS_HTTPRequest $request
*/
public function get_openchats(SS_HTTPRequest $request)
{
if (!Permission::checkMember(Member::currentUser(), "CMS_ACCESS_LiveChatAdmin")) {
header("HTTP/1.0 403 Forbidden");
die('You do not have permission to use the live chat module');
}
// find the messages that have been sent to you
$query1 = new SQLQuery("*", "LiveChatMessage", "ToID = " . (int) Member::currentUserID());
$query1->addOrderBy("ID DESC");
$query1->addWhere("ClassName = 'LiveChatMessage'");
$query2 = new SQLQuery("ID, FromID, FromName", '(' . $query1->sql() . ') x');
$query2->addGroupBy("FromID, FromName");
// only select the largest ID, unique for each sender
$result = $query2->execute();
$returnar = array();
// add the names to the array
foreach ($result as $id) {
$member = Member::get()->byID($id['FromID']);
$returnar[$id['ID']] = array("Name" => $member ? $member->getName() : $id['FromName'], "FromID" => $id['FromID'] ? $id['FromID'] : md5($id['FromName']));
}
header('Content-Type: application/json');
die(json_encode($returnar));
}
