public function testMultipleRowInsert()
{
$query = SQLInsert::create('"SQLInsertTestBase"');
$query->addRow(array('"Title"' => 'First Object', '"Age"' => 10, '"Description"' => 'First the worst'));
$query->addRow(array('"Title"' => 'Second object', '"Age"' => 12));
$sql = $query->sql($parameters);
// Only test this case if using the default query builder
if (get_class(DB::get_conn()->getQueryBuilder()) === 'DBQueryBuilder') {
$this->assertSQLEquals('INSERT INTO "SQLInsertTestBase" ("Title", "Age", "Description") VALUES (?, ?, ?), (?, ?, ?)', $sql);
}
$this->assertEquals(array('First Object', 10, 'First the worst', 'Second object', 12, null), $parameters);
$query->execute();
$this->assertEquals(2, DB::affected_rows());
// Check inserted objects are correct
$firstObject = DataObject::get_one('SQLInsertTestBase', array('"Title"' => 'First Object'), false);
$this->assertNotEmpty($firstObject);
$this->assertEquals($firstObject->Title, 'First Object');
$this->assertEquals($firstObject->Age, 10);
$this->assertEquals($firstObject->Description, 'First the worst');
$secondObject = DataObject::get_one('SQLInsertTestBase', array('"Title"' => 'Second object'), false);
$this->assertNotEmpty($secondObject);
$this->assertEquals($secondObject->Title, 'Second object');
$this->assertEquals($secondObject->Age, 12);
$this->assertEmpty($secondObject->Description);
}
/**
* @return array
*/
private function artefacts()
{
$oldschema = [];
$newschema = [];
$current = DB::get_conn()->getSelectedDatabase();
foreach (DB::table_list() as $lowercase => $dbtablename) {
$oldschema[$dbtablename] = DB::field_list($dbtablename);
}
$test = new SapphireTest();
$test->create_temp_db();
foreach (DB::table_list() as $lowercase => $dbtablename) {
$newschema[$lowercase] = DB::field_list($dbtablename);
}
$test->kill_temp_db();
DB::get_conn()->selectDatabase($current);
$artefacts = [];
foreach ($oldschema as $table => $fields) {
if (!isset($newschema[strtolower($table)])) {
$artefacts[$table] = $table;
continue;
}
foreach ($fields as $field => $spec) {
if (!isset($newschema[strtolower($table)][$field])) {
$artefacts[$table][$field] = $field;
}
}
}
return $artefacts;
}
/**
* Get the DB connection in a SS 3.1 and 3.2+ compatible way
* @param string $name
* @return SS_Database
*/
public static function DBConn($name = 'default')
{
if (method_exists('DB', 'get_conn')) {
return DB::get_conn($name);
}
return DB::getConn($name);
}
public function testFilter()
{
if (DB::get_conn() instanceof MySQLDatabase) {
$baseQuery = FulltextFilterTest_DataObject::get();
$this->assertEquals(3, $baseQuery->count(), "FulltextFilterTest_DataObject count does not match.");
// First we'll text the 'SearchFields' which has been set using an array
$search = $baseQuery->filter("SearchFields:fulltext", 'SilverStripe');
$this->assertEquals(1, $search->count());
$search = $baseQuery->exclude("SearchFields:fulltext", "SilverStripe");
$this->assertEquals(2, $search->count());
// Now we'll run the same tests on 'OtherSearchFields' which should yield the same resutls
// but has been set using a string.
$search = $baseQuery->filter("OtherSearchFields:fulltext", 'SilverStripe');
$this->assertEquals(1, $search->count());
$search = $baseQuery->exclude("OtherSearchFields:fulltext", "SilverStripe");
$this->assertEquals(2, $search->count());
// Search on a single field
$search = $baseQuery->filter("ColumnE:fulltext", 'Dragons');
$this->assertEquals(1, $search->count());
$search = $baseQuery->exclude("ColumnE:fulltext", "Dragons");
$this->assertEquals(2, $search->count());
} else {
$this->markTestSkipped("FulltextFilter only supports MySQL syntax.");
}
}
protected function logVisit()
{
if (!Security::database_is_ready()) {
return;
}
DB::query(sprintf('UPDATE "Member" SET "LastVisited" = %s, "NumVisit" = "NumVisit" + 1 WHERE "ID" = %d', DB::get_conn()->now(), $this->owner->ID));
}
function testReadOnlyTransaction()
{
if (DB::get_conn()->supportsTransactions() == true && DB::get_conn() instanceof PostgreSQLDatabase) {
$page = new Page();
$page->Title = 'Read only success';
$page->write();
DB::get_conn()->transactionStart('READ ONLY');
try {
$page = new Page();
$page->Title = 'Read only page failed';
$page->write();
} catch (Exception $e) {
//could not write this record
//We need to do a rollback or a commit otherwise we'll get error messages
DB::get_conn()->transactionRollback();
}
DB::get_conn()->transactionEnd();
DataObject::flush_and_destroy_cache();
$success = DataObject::get('Page', "\"Title\"='Read only success'");
$fail = DataObject::get('Page', "\"Title\"='Read only page failed'");
//This page should be in the system
$this->assertTrue(is_object($success) && $success->exists());
//This page should NOT exist, we had 'read only' permissions
$this->assertFalse(is_object($fail) && $fail->exists());
} else {
$this->markTestSkipped('Current database is not PostgreSQL');
}
}
public function testCreateWithTransaction()
{
if (DB::get_conn()->supportsTransactions() == true) {
DB::get_conn()->transactionStart();
$obj = new TransactionTest_Object();
$obj->Title = 'First page';
$obj->write();
$obj = new TransactionTest_Object();
$obj->Title = 'Second page';
$obj->write();
//Create a savepoint here:
DB::get_conn()->transactionSavepoint('rollback');
$obj = new TransactionTest_Object();
$obj->Title = 'Third page';
$obj->write();
$obj = new TransactionTest_Object();
$obj->Title = 'Fourth page';
$obj->write();
//Revert to a savepoint:
DB::get_conn()->transactionRollback('rollback');
DB::get_conn()->transactionEnd();
$first = DataObject::get('TransactionTest_Object', "\"Title\"='First page'");
$second = DataObject::get('TransactionTest_Object', "\"Title\"='Second page'");
$third = DataObject::get('TransactionTest_Object', "\"Title\"='Third page'");
$fourth = DataObject::get('TransactionTest_Object', "\"Title\"='Fourth page'");
//These pages should be in the system
$this->assertTrue(is_object($first) && $first->exists());
$this->assertTrue(is_object($second) && $second->exists());
//These pages should NOT exist, we reverted to a savepoint:
$this->assertFalse(is_object($third) && $third->exists());
$this->assertFalse(is_object($fourth) && $fourth->exists());
} else {
$this->markTestSkipped('Current database does not support transactions');
}
}
public function testQueriedColumnsFromSubTable()
{
$db = DB::get_conn();
$playerList = new DataList('DataObjectTest_SubTeam');
$playerList = $playerList->setQueriedColumns(array('SubclassDatabaseField'));
$expected = 'SELECT DISTINCT "DataObjectTest_Team"."ClassName", "DataObjectTest_Team"."LastEdited", ' . '"DataObjectTest_Team"."Created", "DataObjectTest_SubTeam"."SubclassDatabaseField", ' . '"DataObjectTest_Team"."ID", CASE WHEN "DataObjectTest_Team"."ClassName" IS NOT NULL THEN ' . '"DataObjectTest_Team"."ClassName" ELSE ' . $db->quoteString('DataObjectTest_Team') . ' END ' . 'AS "RecordClassName", "DataObjectTest_Team"."Title" ' . 'FROM "DataObjectTest_Team" LEFT JOIN "DataObjectTest_SubTeam" ON "DataObjectTest_SubTeam"."ID" = ' . '"DataObjectTest_Team"."ID" WHERE ("DataObjectTest_Team"."ClassName" IN (?)) ' . 'ORDER BY "DataObjectTest_Team"."Title" ASC';
$this->assertSQLEquals($expected, $playerList->sql($parameters));
}
public function requireField()
{
// HACK: MSSQL does not support double so we're using float instead
// @todo This should go into MSSQLDatabase ideally somehow
if (DB::get_conn() instanceof MySQLDatabase) {
DB::require_field($this->tableName, $this->name, "double");
} else {
DB::require_field($this->tableName, $this->name, "float");
}
}
public function getLocation($ip, $ipNumber)
{
$conn = DB::get_conn();
$addressType = IpToLocation::addr_type($ip);
$sql = "SELECT\n\t\t\t\t\t\t`ip_start` AS IPFrom,\n\t\t\t\t\t\t`ip_end` AS IPTo,\n\t\t\t\t\t\t`country` AS Country,\n\t\t\t\t\t\t`stateprov` AS Region,\n\t\t\t\t\t\t`city` AS City\n\t\t\t\t \tFROM\n\t\t\t\t\t\t`dbip_lookup`\n\t\t\t\t\tWHERE\n\t\t\t\t\t\taddr_type = '{$addressType}'\n\t\t\t\t\t\tAND ip_start <= '" . $conn->escapeString($ipNumber) . "'\n\t\t\t\t\tORDER BY\n\t\t\t\t\t\tip_start DESC\n\t\t\t\t\tLIMIT 1";
$res = DB::query($sql);
while ($row = $res->nextRecord()) {
$location = new IpToLocation($row);
$this->debugLocation($location);
return $location;
}
}
protected function excludeMany(DataQuery $query)
{
$this->model = $query->applyRelation($this->relation);
$values = $this->getValue();
$comparisonClause = DB::get_conn()->comparisonClause($this->getDbName(), null, false, true, $this->getCaseSensitive(), true);
$parameters = array();
foreach ($values as $value) {
$parameters[] = $this->getMatchPattern($value);
}
// Since query connective is ambiguous, use AND explicitly here
$count = count($values);
$predicate = implode(' AND ', array_fill(0, $count, $comparisonClause));
return $query->where(array($predicate => $parameters));
}
/**
* Initialisation function that is run before any action on the controller is called.
*
* @uses BasicAuth::requireLogin()
*/
public function init()
{
if ($this->basicAuthEnabled) {
BasicAuth::protect_site_if_necessary();
}
// Directly access the session variable just in case the Group or Member tables don't yet exist
if (Member::config()->log_last_visited) {
Deprecation::notice('4.0', 'Member::$LastVisited is deprecated. From 4.0 onwards you should implement this as a custom extension');
if (Session::get('loggedInAs') && Security::database_is_ready() && ($member = Member::currentUser())) {
DB::prepared_query(sprintf('UPDATE "Member" SET "LastVisited" = %s WHERE "ID" = ?', DB::get_conn()->now()), array($member->ID));
}
}
// This is used to test that subordinate controllers are actually calling parent::init() - a common bug
$this->baseInitCalled = true;
}
public function run($request)
{
$strCSVPath = CONTINENTAL_CONTENT_PATH . '/code/ThirdParty/dbip-city.csv';
if (!file_exists($strCSVPath)) {
echo "<p>I cant find the dbip-city.csv file to import any data.<br>\n\t\t\t\tPlease download any database from <a href='https://db-ip.com/db/'>https://db-ip.com/db/</a>.<br>\n\t\t\t\tNOTE: It's adviced to edit the DB to only include the countries you want to handle, it contains 2 million records!!!<br>\n\t\t\t\tOr make a CSV contain these columns<br>\n\t\t\t\t`IPFrom`,`IPTo`,`Country`,`Region`,`City`\n\t\t\t\t</p>";
//"0.0.0.0","0.255.255.255","US","California","Los Angeles"
} else {
if (!isset($_REQUEST['confirm'])) {
$strLink = Director::baseURL() . 'dev/tasks/ImportDBIPcom?confirm=1';
echo "<p>CAUTION!!!<br>\n\t\t\t\t\tPlease confirm your action<br>\n\t\t\t\t\t<a href='{$strLink}'>I confirm the action</a><br>\n\t\t\t\t\t<a href='{$strLink}&emptydb=1'>I confirm the action, please empty the DB before you import</a>\n\t\t\t\t\t</p>";
} else {
increase_time_limit_to();
//
// this needs varbinary fields so create the table here
//
$arr = DB::table_list();
if (!in_array('dbip_lookup', $arr)) {
$strSQL = "CREATE TABLE `dbip_lookup` (\n\t\t\t\t\t `addr_type` enum('ipv4','ipv6') NOT NULL,\n\t\t\t\t\t `ip_start` varbinary(16) NOT NULL,\n\t\t\t\t\t `ip_end` varbinary(16) NOT NULL,\n\t\t\t\t\t `country` char(2) NOT NULL,\n\t\t\t\t\t `stateprov` varchar(80) NOT NULL,\n\t\t\t\t\t `city` varchar(80) NOT NULL,\n\t\t\t\t\t PRIMARY KEY (`ip_start`)\n\t\t\t\t\t);";
DB::query($strSQL);
}
if (isset($_REQUEST['emptydb'])) {
DB::query('TRUNCATE `dbip_lookup`;');
}
$conn = DB::get_conn();
if ($conn->supportsTransactions()) {
$conn->transactionStart();
}
$handle = fopen($strCSVPath, "r");
if ($handle) {
while (($line = fgets($handle)) !== false) {
$line = str_replace('","', '___', $line);
$line = str_replace('"', '', $line);
$arrParts = Convert::raw2sql(explode("___", $line));
$vals = array('addr_type' => "'" . IpToLocation::addr_type($arrParts[0]) . "'", 'ip_start' => "'" . $conn->escapeString(ContinentalContentUtils::IPAddressToIPNumber($arrParts[0])) . "'", 'ip_end' => "'" . $conn->escapeString(ContinentalContentUtils::IPAddressToIPNumber($arrParts[1])) . "'", 'country' => "'" . $arrParts[2] . "'", 'stateprov' => "'" . $arrParts[3] . "'", 'city' => "'" . $arrParts[4] . "'");
$fields = array_keys($vals);
DB::query('INSERT INTO `dbip_lookup` (`' . implode('`,`', $fields) . '`) VALUES (' . implode(',', $vals) . ')');
}
fclose($handle);
} else {
echo 'Error opening file';
}
if ($conn->supportsTransactions()) {
$conn->transactionEnd();
}
}
}
}
/**
* Check that once a schema has been generated, then it doesn't need any more updating
*/
public function testFieldsDontRerequestChanges()
{
// These are MySQL specific :-S
if (DB::get_conn() instanceof MySQLDatabase) {
$schema = DB::get_schema();
$test = $this;
DB::quiet();
// Verify that it doesn't need to be recreated
$schema->schemaUpdate(function () use($test, $schema) {
$obj = new MySQLDatabaseTest_DO();
$obj->requireTable();
$needsUpdating = $schema->doesSchemaNeedUpdating();
$schema->cancelSchemaUpdate();
$test->assertFalse($needsUpdating);
});
}
}
public function testLeftJoinParameterised()
{
$db = DB::get_conn();
$list = DataObjectTest_TeamComment::get();
$list = $list->leftJoin('DataObjectTest_Team', '"DataObjectTest_Team"."ID" = "DataObjectTest_TeamComment"."TeamID" ' . 'AND "DataObjectTest_Team"."Title" LIKE ?', 'Team', 20, array('Team%'));
$expected = 'SELECT DISTINCT "DataObjectTest_TeamComment"."ClassName", ' . '"DataObjectTest_TeamComment"."LastEdited", "DataObjectTest_TeamComment"."Created", ' . '"DataObjectTest_TeamComment"."Name", "DataObjectTest_TeamComment"."Comment", ' . '"DataObjectTest_TeamComment"."TeamID", "DataObjectTest_TeamComment"."ID", ' . 'CASE WHEN "DataObjectTest_TeamComment"."ClassName" IS NOT NULL' . ' THEN "DataObjectTest_TeamComment"."ClassName" ELSE ' . $db->quoteString('DataObjectTest_TeamComment') . ' END AS "RecordClassName" FROM "DataObjectTest_TeamComment" LEFT JOIN ' . '"DataObjectTest_Team" AS "Team" ON "DataObjectTest_Team"."ID" = ' . '"DataObjectTest_TeamComment"."TeamID" ' . 'AND "DataObjectTest_Team"."Title" LIKE ?' . ' ORDER BY "DataObjectTest_TeamComment"."Name" ASC';
$this->assertSQLEquals($expected, $list->sql($parameters));
$this->assertEquals(array('Team%'), $parameters);
}
public function conditionSQL(&$parameters)
{
$parameters = array();
// Ignore empty conditions
$where = $this->whereQuery->getWhere();
if (empty($where)) {
return null;
}
// Allow database to manage joining of conditions
$sql = DB::get_conn()->getQueryBuilder()->buildWhereFragment($this->whereQuery, $parameters);
return preg_replace('/^\\s*WHERE\\s*/i', '', $sql);
}
public function testSearchTitleAndContentWithSpecialCharacters()
{
if (!$this->checkFulltextSupport()) {
return;
}
if (class_exists('PostgreSQLDatabase') && DB::get_conn() instanceof PostgreSQLDatabase) {
$this->markTestSkipped("PostgreSQLDatabase doesn't support entity-encoded searches");
}
$sf = new SearchForm($this->mockController, 'SearchForm');
$pageWithSpecialChars = $this->objFromFixture('SiteTree', 'pageWithSpecialChars');
$pageWithSpecialChars->publish('Stage', 'Live');
$results = $sf->getResults(null, array('Search' => 'Brötchen'));
$this->assertContains($pageWithSpecialChars->ID, $results->column('ID'), 'Published pages with umlauts in title are found');
$results = $sf->getResults(null, array('Search' => 'Bäcker'));
$this->assertContains($pageWithSpecialChars->ID, $results->column('ID'), 'Published pages with htmlencoded umlauts in content are found');
}
/**
* Reset the database connection to use the original database. Called by {@link self::endTestSession()}.
*/
public function resetDatabaseName()
{
if ($this->oldDatabaseName) {
global $databaseConfig;
$databaseConfig['database'] = $this->oldDatabaseName;
$conn = DB::get_conn();
if ($conn) {
$conn->selectDatabase($this->oldDatabaseName, false, false);
}
}
}
public function testComparisonClauseTextCaseSensitive()
{
DB::query("INSERT INTO \"DataQueryTest_F\" (\"MyString\") VALUES ('HelloWorld')");
$query = new DataQuery('DataQueryTest_F');
$query->where(DB::get_conn()->comparisonClause('"MyString"', 'HelloWorld', false, false, true));
$this->assertGreaterThan(0, $query->count(), "Couldn't find MyString");
$query2 = new DataQuery('DataQueryTest_F');
$query2->where(DB::get_conn()->comparisonClause('"MyString"', 'helloworld', false, false, true));
$this->assertEquals(0, $query2->count(), "Found mystring. Shouldn't be able too.");
$this->resetDBSchema(true);
}
public function setUp()
{
parent::setUp();
$this->adapter = DB::get_conn();
}
/**
* Test that multiple order elements are maintained in the given order
*/
public function testOrderByMultiple()
{
if (DB::get_conn() instanceof MySQLDatabase) {
$query = new SQLSelect();
$query->setSelect(array('"Name"', '"Meta"'));
$query->setFrom('"SQLSelectTest_DO"');
$query->setOrderBy(array('MID("Name", 8, 1) DESC', '"Name" ASC'));
$records = array();
foreach ($query->execute() as $record) {
$records[] = $record;
}
$this->assertCount(2, $records);
$this->assertEquals('Object 2', $records[0]['Name']);
$this->assertEquals('2', $records[0]['_SortColumn0']);
$this->assertEquals('Object 1', $records[1]['Name']);
$this->assertEquals('1', $records[1]['_SortColumn0']);
}
}
/**
* @param String $identifier Unique identifier for this fixture type
* @param Array $data Map of property names to their values.
* @param Array $fixtures Map of fixture names to an associative array of their in-memory
* identifiers mapped to their database IDs. Used to look up
* existing fixtures which might be referenced in the $data attribute
* via the => notation.
* @return DataObject
*/
public function createObject($identifier, $data = null, $fixtures = null)
{
// We have to disable validation while we import the fixtures, as the order in
// which they are imported doesnt guarantee valid relations until after the import is complete.
// Also disable filesystem manipulations
Config::nest();
Config::inst()->update('DataObject', 'validation_enabled', false);
Config::inst()->update('File', 'update_filesystem', false);
$this->invokeCallbacks('beforeCreate', array($identifier, &$data, &$fixtures));
try {
$class = $this->class;
$obj = DataModel::inst()->{$class}->newObject();
// If an ID is explicitly passed, then we'll sort out the initial write straight away
// This is just in case field setters triggered by the population code in the next block
// Call $this->write(). (For example, in FileTest)
if (isset($data['ID'])) {
$obj->ID = $data['ID'];
// The database needs to allow inserting values into the foreign key column (ID in our case)
$conn = DB::get_conn();
if (method_exists($conn, 'allowPrimaryKeyEditing')) {
$conn->allowPrimaryKeyEditing(ClassInfo::baseDataClass($class), true);
}
$obj->write(false, true);
if (method_exists($conn, 'allowPrimaryKeyEditing')) {
$conn->allowPrimaryKeyEditing(ClassInfo::baseDataClass($class), false);
}
}
// Populate defaults
if ($this->defaults) {
foreach ($this->defaults as $fieldName => $fieldVal) {
if (isset($data[$fieldName]) && $data[$fieldName] !== false) {
continue;
}
if (is_callable($fieldVal)) {
$obj->{$fieldName} = $fieldVal($obj, $data, $fixtures);
} else {
$obj->{$fieldName} = $fieldVal;
}
}
}
// Populate overrides
if ($data) {
foreach ($data as $fieldName => $fieldVal) {
// Defer relationship processing
if ($obj->manyManyComponent($fieldName) || $obj->hasManyComponent($fieldName) || $obj->hasOneComponent($fieldName)) {
continue;
}
$this->setValue($obj, $fieldName, $fieldVal, $fixtures);
}
}
$obj->write();
// Save to fixture before relationship processing in case of reflexive relationships
if (!isset($fixtures[$class])) {
$fixtures[$class] = array();
}
$fixtures[$class][$identifier] = $obj->ID;
// Populate all relations
if ($data) {
foreach ($data as $fieldName => $fieldVal) {
if ($obj->manyManyComponent($fieldName) || $obj->hasManyComponent($fieldName)) {
$obj->write();
$parsedItems = array();
if (is_array($fieldVal)) {
// handle lists of many_many relations. Each item can
// specify the many_many_extraFields against each
// related item.
foreach ($fieldVal as $relVal) {
$item = key($relVal);
$id = $this->parseValue($item, $fixtures);
$parsedItems[] = $id;
array_shift($relVal);
$obj->getManyManyComponents($fieldName)->add($id, $relVal);
}
} else {
$items = preg_split('/ *, */', trim($fieldVal));
foreach ($items as $item) {
// Check for correct format: =><relationname>.<identifier>.
// Ignore if the item has already been replaced with a numeric DB identifier
if (!is_numeric($item) && !preg_match('/^=>[^\\.]+\\.[^\\.]+/', $item)) {
throw new InvalidArgumentException(sprintf('Invalid format for relation "%s" on class "%s" ("%s")', $fieldName, $class, $item));
}
$parsedItems[] = $this->parseValue($item, $fixtures);
}
if ($obj->hasManyComponent($fieldName)) {
$obj->getComponents($fieldName)->setByIDList($parsedItems);
} elseif ($obj->manyManyComponent($fieldName)) {
$obj->getManyManyComponents($fieldName)->setByIDList($parsedItems);
}
}
} else {
$hasOneField = preg_replace('/ID$/', '', $fieldName);
if ($className = $obj->hasOneComponent($hasOneField)) {
$obj->{$hasOneField . 'ID'} = $this->parseValue($fieldVal, $fixtures, $fieldClass);
// Inject class for polymorphic relation
if ($className === 'DataObject') {
$obj->{$hasOneField . 'Class'} = $fieldClass;
}
}
}
}
}
$obj->write();
// If LastEdited was set in the fixture, set it here
if ($data && array_key_exists('LastEdited', $data)) {
$this->overrideField($obj, 'LastEdited', $data['LastEdited'], $fixtures);
}
} catch (Exception $e) {
Config::unnest();
throw $e;
}
Config::unnest();
$this->invokeCallbacks('afterCreate', array($obj, $identifier, &$data, &$fixtures));
return $obj;
}
/**
* Create a database table to replay the site tree creation, based on the chronological order of the site tree version table.
*/
protected function setupStructure()
{
if (!DB::get_conn() instanceof MySQLDatabase) {
exit('This task currently only supports <strong>MySQL</strong>...');
}
$replaceArray = self::$db_columns;
unset($replaceArray['FullURL']);
$this->replaceColumnString = implode(',', array_keys($replaceArray));
$tableList = DB::table_list();
if (self::$use_temporary_table || !in_array(self::$default_table, $tableList)) {
$options = self::$use_temporary_table ? array('temporary' => true) : null;
$this->replayTable = DB::create_table(self::$default_table, self::$db_columns, null, $options);
} else {
// Delete all records from the table.
$query = new SQLDelete(self::$default_table);
$query->execute();
}
}
/**
* Applies matches for several values, either as inclusive or exclusive
*
* @param DataQuery $query
* @param bool $inclusive True if this is inclusive, or false if exclusive
* @return DataQuery
*/
protected function manyFilter(DataQuery $query, $inclusive)
{
$this->model = $query->applyRelation($this->relation);
$caseSensitive = $this->getCaseSensitive();
// Check values for null
$field = $this->getDbName();
$values = $this->getValue();
$hasNull = in_array(null, $values, true);
if ($hasNull) {
$values = array_filter($values, function ($value) {
return $value !== null;
});
}
$connective = '';
if (empty($values)) {
$predicate = '';
} elseif ($caseSensitive === null) {
// For queries using the default collation (no explicit case) we can use the WHERE .. NOT IN .. syntax,
// providing simpler SQL than many WHERE .. AND .. fragments.
$column = $this->getDbName();
$placeholders = DB::placeholders($values);
if ($inclusive) {
$predicate = "{$column} IN ({$placeholders})";
} else {
$predicate = "{$column} NOT IN ({$placeholders})";
}
} else {
// Generate reusable comparison clause
$comparisonClause = DB::get_conn()->comparisonClause($this->getDbName(), null, true, !$inclusive, $this->getCaseSensitive(), true);
$count = count($values);
if ($count > 1) {
$connective = $inclusive ? ' OR ' : ' AND ';
$conditions = array_fill(0, $count, $comparisonClause);
$predicate = implode($connective, $conditions);
} else {
$predicate = $comparisonClause;
}
}
// Always check for null when doing exclusive checks (either AND IS NOT NULL / OR IS NULL)
// or when including the null value explicitly (OR IS NULL)
if ($hasNull || !$inclusive) {
// If excluding values which don't include null, or including
// values which include null, we should do an `OR IS NULL`.
// Otherwise we are excluding values that do include null, so `AND IS NOT NULL`.
// Simplified from (!$inclusive && !$hasNull) || ($inclusive && $hasNull);
$isNull = !$hasNull || $inclusive;
$nullCondition = DB::get_conn()->nullCheckClause($field, $isNull);
// Determine merge strategy
if (empty($predicate)) {
$predicate = $nullCondition;
} else {
// Merge null condition with predicate
if ($isNull) {
$nullCondition = " OR {$nullCondition}";
} else {
$nullCondition = " AND {$nullCondition}";
}
// If current predicate connective doesn't match the same as the null connective
// make sure to group the prior condition
if ($connective && ($connective === ' OR ') !== $isNull) {
$predicate = "({$predicate})";
}
$predicate .= $nullCondition;
}
}
return $query->where(array($predicate => $values));
}
/**
* Move a database record from one stage to the other.
*
* @param fromStage Place to copy from. Can be either a stage name or a version number.
* @param toStage Place to copy to. Must be a stage name.
* @param createNewVersion Set this to true to create a new version number. By default, the existing version
* number will be copied over.
*/
public function publish($fromStage, $toStage, $createNewVersion = false)
{
$this->owner->extend('onBeforeVersionedPublish', $fromStage, $toStage, $createNewVersion);
$baseClass = $this->owner->class;
while (($p = get_parent_class($baseClass)) != "DataObject") {
$baseClass = $p;
}
$extTable = $this->extendWithSuffix($baseClass);
if (is_numeric($fromStage)) {
$from = Versioned::get_version($baseClass, $this->owner->ID, $fromStage);
} else {
$this->owner->flushCache();
$from = Versioned::get_one_by_stage($baseClass, $fromStage, "\"{$baseClass}\".\"ID\"={$this->owner->ID}");
}
$publisherID = isset(Member::currentUser()->ID) ? Member::currentUser()->ID : 0;
if ($from) {
$from->forceChange();
if ($createNewVersion) {
$latest = self::get_latest_version($baseClass, $this->owner->ID);
$this->owner->Version = $latest->Version + 1;
} else {
$from->migrateVersion($from->Version);
}
// Mark this version as having been published at some stage
DB::prepared_query("UPDATE \"{$extTable}_versions\"\n\t\t\t\tSET \"WasPublished\" = ?, \"PublisherID\" = ?\n\t\t\t\tWHERE \"RecordID\" = ? AND \"Version\" = ?", array(1, $publisherID, $from->ID, $from->Version));
$oldMode = Versioned::get_reading_mode();
Versioned::reading_stage($toStage);
$conn = DB::get_conn();
if (method_exists($conn, 'allowPrimaryKeyEditing')) {
$conn->allowPrimaryKeyEditing($baseClass, true);
}
$from->write();
if (method_exists($conn, 'allowPrimaryKeyEditing')) {
$conn->allowPrimaryKeyEditing($baseClass, false);
}
$from->destroy();
Versioned::set_reading_mode($oldMode);
} else {
user_error("Can't find {$this->owner->URLSegment}/{$this->owner->ID} in stage {$fromStage}", E_USER_WARNING);
}
}
public function testForceInsert()
{
/* If you set an ID on an object and pass forceInsert = true, then the object should be correctly created */
$conn = DB::get_conn();
if (method_exists($conn, 'allowPrimaryKeyEditing')) {
$conn->allowPrimaryKeyEditing('DataObjectTest_Team', true);
}
$obj = new DataObjectTest_SubTeam();
$obj->ID = 1001;
$obj->Title = 'asdfasdf';
$obj->SubclassDatabaseField = 'asdfasdf';
$obj->write(false, true);
if (method_exists($conn, 'allowPrimaryKeyEditing')) {
$conn->allowPrimaryKeyEditing('DataObjectTest_Team', false);
}
$this->assertEquals("DataObjectTest_SubTeam", DB::query("SELECT \"ClassName\" FROM \"DataObjectTest_Team\" WHERE \"ID\" = {$obj->ID}")->value());
/* Check that it actually saves to the database with the correct ID */
$this->assertEquals("1001", DB::query("SELECT \"ID\" FROM \"DataObjectTest_SubTeam\" WHERE \"SubclassDatabaseField\" = 'asdfasdf'")->value());
$this->assertEquals("1001", DB::query("SELECT \"ID\" FROM \"DataObjectTest_Team\" WHERE \"Title\" = 'asdfasdf'")->value());
}
/**
* Excludes an exact match (equals) on a field value against multiple
* possible values.
*
* @return DataQuery
*/
protected function excludeMany(DataQuery $query)
{
$this->model = $query->applyRelation($this->relation);
$caseSensitive = $this->getCaseSensitive();
$values = $this->getValue();
if ($caseSensitive === null) {
// For queries using the default collation (no explicit case) we can use the WHERE .. NOT IN .. syntax,
// providing simpler SQL than many WHERE .. AND .. fragments.
$column = $this->getDbName();
// If values is an empty array, fall back to 3.1 behaviour and use empty string comparison
if (empty($values)) {
$values = array('');
}
$placeholders = DB::placeholders($values);
return $query->where(array("{$column} NOT IN ({$placeholders})" => $values));
} else {
// Generate reusable comparison clause
$comparisonClause = DB::get_conn()->comparisonClause($this->getDbName(), null, true, true, $this->getCaseSensitive(), true);
// Since query connective is ambiguous, use AND explicitly here
$count = count($values);
$predicate = implode(' AND ', array_fill(0, $count, $comparisonClause));
return $query->where(array($predicate => $values));
}
}
public static function create_temp_db()
{
// Disable PHPUnit error handling
restore_error_handler();
// Create a temporary database, and force the connection to use UTC for time
global $databaseConfig;
$databaseConfig['timezone'] = '+0:00';
DB::connect($databaseConfig);
$dbConn = DB::get_conn();
$prefix = defined('SS_DATABASE_PREFIX') ? SS_DATABASE_PREFIX : 'ss_';
$dbname = strtolower(sprintf('%stmpdb', $prefix)) . rand(1000000, 9999999);
while (!$dbname || $dbConn->databaseExists($dbname)) {
$dbname = strtolower(sprintf('%stmpdb', $prefix)) . rand(1000000, 9999999);
}
$dbConn->selectDatabase($dbname, true);
$st = Injector::inst()->create('SapphireTest');
$st->resetDBSchema();
// Reinstate PHPUnit error handling
set_error_handler(array('PHPUnit_Util_ErrorHandler', 'handleError'));
return $dbname;
}
public function testAppendExtraFieldsToQuery()
{
$list = new ManyManyList('ManyManyListTest_ExtraFields', 'ManyManyListTest_ExtraFields_Clients', 'ManyManyListTest_ExtraFieldsID', 'ChildID', array('Worth' => 'Money', 'Reference' => 'Varchar'));
// ensure that ManyManyListTest_ExtraFields_Clients.ValueCurrency is
// selected.
$db = DB::get_conn();
$expected = 'SELECT DISTINCT "ManyManyListTest_ExtraFields_Clients"."WorthCurrency",' . ' "ManyManyListTest_ExtraFields_Clients"."WorthAmount", "ManyManyListTest_ExtraFields_Clients"."Reference",' . ' "ManyManyListTest_ExtraFields"."ClassName", "ManyManyListTest_ExtraFields"."LastEdited",' . ' "ManyManyListTest_ExtraFields"."Created", "ManyManyListTest_ExtraFields"."ID",' . ' CASE WHEN "ManyManyListTest_ExtraFields"."ClassName" IS NOT NULL THEN' . ' "ManyManyListTest_ExtraFields"."ClassName" ELSE ' . Convert::raw2sql('ManyManyListTest_ExtraFields', true) . ' END AS "RecordClassName" FROM "ManyManyListTest_ExtraFields" INNER JOIN' . ' "ManyManyListTest_ExtraFields_Clients" ON' . ' "ManyManyListTest_ExtraFields_Clients"."ManyManyListTest_ExtraFieldsID" =' . ' "ManyManyListTest_ExtraFields"."ID"';
$this->assertSQLEquals($expected, $list->sql($parameters));
}
/**
* Return a SQL CONCAT() fragment suitable for a SELECT statement.
* Useful for custom queries which assume a certain member title format.
*
* @param String $tableName
* @return String SQL
*/
public static function get_title_sql($tableName = 'Member')
{
// This should be abstracted to SSDatabase concatOperator or similar.
$op = DB::get_conn() instanceof MSSQLDatabase ? " + " : " || ";
$format = self::config()->title_format;
if ($format) {
$columnsWithTablename = array();
foreach ($format['columns'] as $column) {
$columnsWithTablename[] = "\"{$tableName}\".\"{$column}\"";
}
return "(" . join(" {$op} '" . $format['sep'] . "' {$op} ", $columnsWithTablename) . ")";
} else {
return "(\"{$tableName}\".\"Surname\" {$op} ' ' {$op} \"{$tableName}\".\"FirstName\")";
}
}
