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\")"; } }