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()); }