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()) === 'SilverStripe\\ORM\\Connect\\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);
}
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 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.");
}
}
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 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);
}
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));
}
public function testTransactions()
{
$conn = DB::get_conn();
if (!$conn->supportsTransactions()) {
$this->markTestSkipped("DB Doesn't support transactions");
return;
}
// Test that successful transactions are comitted
$obj = new DatabaseTest_MyObject();
$failed = false;
$conn->withTransaction(function () use(&$obj) {
$obj->MyField = 'Save 1';
$obj->write();
}, function () use(&$failed) {
$failed = true;
});
$this->assertEquals('Save 1', DatabaseTest_MyObject::get()->first()->MyField);
$this->assertFalse($failed);
// Test failed transactions are rolled back
$ex = null;
$failed = false;
try {
$conn->withTransaction(function () use(&$obj) {
$obj->MyField = 'Save 2';
$obj->write();
throw new Exception("error");
}, function () use(&$failed) {
$failed = true;
});
} catch (Exception $ex) {
}
$this->assertTrue($failed);
$this->assertEquals('Save 1', DatabaseTest_MyObject::get()->first()->MyField);
$this->assertInstanceOf('Exception', $ex);
$this->assertEquals('error', $ex->getMessage());
}
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);
}
/**
* 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();
if (empty($values)) {
throw new \InvalidArgumentException("Cannot filter {$field} against an empty set");
}
$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));
}
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;
}
/**
* Clear all data out of the database
*
* @deprecated since version 4.0
*/
public function clearAllData()
{
Deprecation::notice('4.0', 'Use DB::get_conn()->clearAllData() instead');
DB::get_conn()->clearAllData();
}
/**
* Add implicit changes that should be included in this changeset
*
* When an item is created or changed, all it's owned items which have
* changes are implicitly added
*
* When an item is deleted, it's owner (even if that owner does not have changes)
* is implicitly added
*/
public function sync()
{
// Start a transaction (if we can)
DB::get_conn()->withTransaction(function () {
// Get the implicitly included items for this ChangeSet
$implicit = $this->calculateImplicit();
// Adjust the existing implicit ChangeSetItems for this ChangeSet
/** @var ChangeSetItem $item */
foreach ($this->Changes()->filter(['Added' => ChangeSetItem::IMPLICITLY]) as $item) {
$objectKey = $this->implicitKey($item);
// If a ChangeSetItem exists, but isn't in $implicit, it's no longer required, so delete it
if (!array_key_exists($objectKey, $implicit)) {
$item->delete();
} else {
$item->ReferencedBy()->setByIDList($implicit[$objectKey]['ReferencedBy']);
unset($implicit[$objectKey]);
}
}
// Now $implicit is all those items that are implicitly included, but don't currently have a ChangeSetItem.
// So create new ChangeSetItems to match
foreach ($implicit as $key => $props) {
$item = new ChangeSetItem($props);
$item->Added = ChangeSetItem::IMPLICITLY;
$item->ChangeSetID = $this->ID;
$item->ReferencedBy()->setByIDList($props['ReferencedBy']);
$item->write();
}
});
}
/**
* Test the prepValueForDB() method on DBField.
*/
public function testPrepValueForDB()
{
$db = DB::get_conn();
/* Float behaviour, asserting we have 0 */
$this->assertEquals(0, singleton('Float')->prepValueForDB(0));
$this->assertEquals(0, singleton('Float')->prepValueForDB(null));
$this->assertEquals(0, singleton('Float')->prepValueForDB(false));
$this->assertEquals(0, singleton('Float')->prepValueForDB(''));
$this->assertEquals('0', singleton('Float')->prepValueForDB('0'));
/* Double behaviour, asserting we have 0 */
$this->assertEquals(0, singleton('Double')->prepValueForDB(0));
$this->assertEquals(0, singleton('Double')->prepValueForDB(null));
$this->assertEquals(0, singleton('Double')->prepValueForDB(false));
$this->assertEquals(0, singleton('Double')->prepValueForDB(''));
$this->assertEquals('0', singleton('Double')->prepValueForDB('0'));
/* Integer behaviour, asserting we have 0 */
$this->assertEquals(0, singleton('Int')->prepValueForDB(0));
$this->assertEquals(0, singleton('Int')->prepValueForDB(null));
$this->assertEquals(0, singleton('Int')->prepValueForDB(false));
$this->assertEquals(0, singleton('Int')->prepValueForDB(''));
$this->assertEquals('0', singleton('Int')->prepValueForDB('0'));
/* Integer behaviour, asserting we have 1 */
$this->assertEquals(1, singleton('Int')->prepValueForDB(true));
$this->assertEquals(1, singleton('Int')->prepValueForDB(1));
$this->assertEquals('1', singleton('Int')->prepValueForDB('1'));
/* Decimal behaviour, asserting we have 0 */
$this->assertEquals(0, singleton('Decimal')->prepValueForDB(0));
$this->assertEquals(0, singleton('Decimal')->prepValueForDB(null));
$this->assertEquals(0, singleton('Decimal')->prepValueForDB(false));
$this->assertEquals(0, singleton('Decimal')->prepValueForDB(''));
$this->assertEquals('0', singleton('Decimal')->prepValueForDB('0'));
/* Decimal behaviour, asserting we have 1 */
$this->assertEquals(1, singleton('Decimal')->prepValueForDB(true));
$this->assertEquals(1, singleton('Decimal')->prepValueForDB(1));
$this->assertEquals('1', singleton('Decimal')->prepValueForDB('1'));
/* Boolean behaviour, asserting we have 0 */
$this->assertEquals(false, singleton('Boolean')->prepValueForDB(0));
$this->assertEquals(false, singleton('Boolean')->prepValueForDB(null));
$this->assertEquals(false, singleton('Boolean')->prepValueForDB(false));
$this->assertEquals(false, singleton('Boolean')->prepValueForDB('false'));
$this->assertEquals(false, singleton('Boolean')->prepValueForDB('f'));
$this->assertEquals(false, singleton('Boolean')->prepValueForDB(''));
$this->assertEquals(false, singleton('Boolean')->prepValueForDB('0'));
/* Boolean behaviour, asserting we have 1 */
$this->assertEquals(true, singleton('Boolean')->prepValueForDB(true));
$this->assertEquals(true, singleton('Boolean')->prepValueForDB('true'));
$this->assertEquals(true, singleton('Boolean')->prepValueForDB('t'));
$this->assertEquals(true, singleton('Boolean')->prepValueForDB(1));
$this->assertEquals(true, singleton('Boolean')->prepValueForDB('1'));
// @todo - Revisit Varchar to evaluate correct behaviour of nullifyEmpty
/* Varchar behaviour */
$this->assertEquals(0, singleton('Varchar')->prepValueForDB(0));
$this->assertEquals(null, singleton('Varchar')->prepValueForDB(null));
$this->assertEquals(null, singleton('Varchar')->prepValueForDB(false));
$this->assertEquals(null, singleton('Varchar')->prepValueForDB(''));
$this->assertEquals('0', singleton('Varchar')->prepValueForDB('0'));
$this->assertEquals(1, singleton('Varchar')->prepValueForDB(1));
$this->assertEquals(true, singleton('Varchar')->prepValueForDB(true));
$this->assertEquals('1', singleton('Varchar')->prepValueForDB('1'));
$this->assertEquals('00000', singleton('Varchar')->prepValueForDB('00000'));
$this->assertEquals(0, singleton('Varchar')->prepValueForDB(00));
$this->assertEquals('test', singleton('Varchar')->prepValueForDB('test'));
$this->assertEquals(123, singleton('Varchar')->prepValueForDB(123));
/* AllowEmpty Varchar behaviour */
$varcharField = new DBVarchar("testfield", 50, array("nullifyEmpty" => false));
$this->assertSame(0, $varcharField->prepValueForDB(0));
$this->assertSame(null, $varcharField->prepValueForDB(null));
$this->assertSame(null, $varcharField->prepValueForDB(false));
$this->assertSame('', $varcharField->prepValueForDB(''));
$this->assertSame('0', $varcharField->prepValueForDB('0'));
$this->assertSame(1, $varcharField->prepValueForDB(1));
$this->assertSame(true, $varcharField->prepValueForDB(true));
$this->assertSame('1', $varcharField->prepValueForDB('1'));
$this->assertSame('00000', $varcharField->prepValueForDB('00000'));
$this->assertSame(0, $varcharField->prepValueForDB(00));
$this->assertSame('test', $varcharField->prepValueForDB('test'));
$this->assertSame(123, $varcharField->prepValueForDB(123));
unset($varcharField);
/* Text behaviour */
$this->assertEquals(0, singleton('Text')->prepValueForDB(0));
$this->assertEquals(null, singleton('Text')->prepValueForDB(null));
$this->assertEquals(null, singleton('Text')->prepValueForDB(false));
$this->assertEquals(null, singleton('Text')->prepValueForDB(''));
$this->assertEquals('0', singleton('Text')->prepValueForDB('0'));
$this->assertEquals(1, singleton('Text')->prepValueForDB(1));
$this->assertEquals(true, singleton('Text')->prepValueForDB(true));
$this->assertEquals('1', singleton('Text')->prepValueForDB('1'));
$this->assertEquals('00000', singleton('Text')->prepValueForDB('00000'));
$this->assertEquals(0, singleton('Text')->prepValueForDB(00));
$this->assertEquals('test', singleton('Text')->prepValueForDB('test'));
$this->assertEquals(123, singleton('Text')->prepValueForDB(123));
/* AllowEmpty Text behaviour */
$textField = new DBText("testfield", array("nullifyEmpty" => false));
$this->assertSame(0, $textField->prepValueForDB(0));
$this->assertSame(null, $textField->prepValueForDB(null));
$this->assertSame(null, $textField->prepValueForDB(false));
$this->assertSame('', $textField->prepValueForDB(''));
$this->assertSame('0', $textField->prepValueForDB('0'));
$this->assertSame(1, $textField->prepValueForDB(1));
$this->assertSame(true, $textField->prepValueForDB(true));
$this->assertSame('1', $textField->prepValueForDB('1'));
$this->assertSame('00000', $textField->prepValueForDB('00000'));
$this->assertSame(0, $textField->prepValueForDB(00));
$this->assertSame('test', $textField->prepValueForDB('test'));
$this->assertSame(123, $textField->prepValueForDB(123));
unset($textField);
/* Time behaviour */
$time = singleton('Time');
$time->setValue('00:01am');
$this->assertEquals("00:01:00", $time->getValue());
$time->setValue('00:59am');
$this->assertEquals("00:59:00", $time->getValue());
$time->setValue('11:59am');
$this->assertEquals("11:59:00", $time->getValue());
$time->setValue('12:00pm');
$this->assertEquals("12:00:00", $time->getValue());
$time->setValue('12:59am');
$this->assertEquals("12:59:00", $time->getValue());
$time->setValue('1:00pm');
$this->assertEquals("13:00:00", $time->getValue());
$time->setValue('11:59pm');
$this->assertEquals("23:59:00", $time->getValue());
$time->setValue('00:00am');
$this->assertEquals("00:00:00", $time->getValue());
$time->setValue('00:00:00');
$this->assertEquals("00:00:00", $time->getValue());
}
/**
* Move a database record from one stage to the other.
*
* @param int|string $fromStage Place to copy from. Can be either a stage name or a version number.
* @param string $toStage Place to copy to. Must be a stage name.
* @param bool $createNewVersion Set this to true to create a new version number.
* By default, the existing version number will be copied over.
*/
public function copyVersionToStage($fromStage, $toStage, $createNewVersion = false)
{
$owner = $this->owner;
$owner->invokeWithExtensions('onBeforeVersionedPublish', $fromStage, $toStage, $createNewVersion);
$baseClass = $owner->baseClass();
$baseTable = $owner->baseTable();
/** @var Versioned|DataObject $from */
if (is_numeric($fromStage)) {
$from = Versioned::get_version($baseClass, $owner->ID, $fromStage);
} else {
$owner->flushCache();
$from = Versioned::get_one_by_stage($baseClass, $fromStage, array("\"{$baseTable}\".\"ID\" = ?" => $owner->ID));
}
if (!$from) {
throw new InvalidArgumentException("Can't find {$baseClass}#{$owner->ID} in stage {$fromStage}");
}
$from->forceChange();
if ($createNewVersion) {
// Clear version to be automatically created on write
$from->Version = null;
} else {
$from->migrateVersion($from->Version);
// Mark this version as having been published at some stage
$publisherID = isset(Member::currentUser()->ID) ? Member::currentUser()->ID : 0;
$extTable = $this->extendWithSuffix($baseTable);
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));
}
// Change to new stage, write, and revert state
$oldMode = Versioned::get_reading_mode();
Versioned::set_stage($toStage);
// Migrate stage prior to write
$from->setSourceQueryParam('Versioned.mode', 'stage');
$from->setSourceQueryParam('Versioned.stage', $toStage);
$conn = DB::get_conn();
if (method_exists($conn, 'allowPrimaryKeyEditing')) {
$conn->allowPrimaryKeyEditing($baseTable, true);
$from->write();
$conn->allowPrimaryKeyEditing($baseTable, false);
} else {
$from->write();
}
$from->destroy();
Versioned::set_reading_mode($oldMode);
$owner->invokeWithExtensions('onAfterVersionedPublish', $fromStage, $toStage, $createNewVersion);
}
/**
* Return a SQL CONCAT() fragment suitable for a SELECT statement.
* Useful for custom queries which assume a certain member title format.
*
* @return String SQL
*/
public static function get_title_sql()
{
// This should be abstracted to SSDatabase concatOperator or similar.
$op = DB::get_conn() instanceof MSSQLDatabase ? " + " : " || ";
// Get title_format with fallback to default
$format = static::config()->title_format;
if (!$format) {
$format = ['columns' => ['Surname', 'FirstName'], 'sep' => ' '];
}
$columnsWithTablename = array();
foreach ($format['columns'] as $column) {
$columnsWithTablename[] = static::getSchema()->sqlColumnForField(__CLASS__, $column);
}
$sepSQL = \Convert::raw2sql($format['sep'], true);
return "(" . join(" {$op} {$sepSQL} {$op} ", $columnsWithTablename) . ")";
}
/**
* 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']);
}
}
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));
}
/**
* @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
* @throws Exception
*/
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('SilverStripe\\ORM\\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();
$baseTable = DataObject::getSchema()->baseDataTable($class);
if (method_exists($conn, 'allowPrimaryKeyEditing')) {
$conn->allowPrimaryKeyEditing($baseTable, true);
}
$obj->write(false, true);
if (method_exists($conn, 'allowPrimaryKeyEditing')) {
$conn->allowPrimaryKeyEditing($baseTable, 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 === 'SilverStripe\\ORM\\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;
}
/**
* Safely encodes a SQL symbolic identifier (or list of identifiers), such as a database,
* table, or column name. Supports encoding of multi identfiers separated by
* a delimiter (e.g. ".")
*
* @param string|array $identifier The identifier to escape. E.g. 'SiteTree.Title' or list of identifiers
* to be joined via the separator.
* @param string $separator The string that delimits subsequent identifiers
* @return string The escaped identifier. E.g. '"SiteTree"."Title"'
*/
public static function symbol2sql($identifier, $separator = '.')
{
return DB::get_conn()->escapeIdentifier($identifier, $separator);
}
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 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());
}
