PHP DB::query方法代码示例

 public function testMySQLCreateTableOptions()
 {
     if (!DB::get_conn() instanceof MySQLDatabase) {
         $this->markTestSkipped('MySQL only');
     }
     $ret = DB::query(sprintf('SHOW TABLE STATUS WHERE "Name" = \'%s\'', 'DatabaseTest_MyObject'))->first();
     $this->assertEquals($ret['Engine'], 'InnoDB', "MySQLDatabase tables can be changed to InnoDB through DataObject::\$create_table_options");
 }

 /**
  * Get the list of classnames, including obsolete classes.
  *
  * If table or name are not set, or if it is not a valid field on the given table,
  * then only known classnames are returned.
  *
  * Values cached in this method can be cleared via `DBClassName::clear_classname_cache();`
  *
  * @return array
  */
 public function getEnumObsolete()
 {
     // Without a table or field specified, we can only retrieve known classes
     $table = $this->getTable();
     $name = $this->getName();
     if (empty($table) || empty($name)) {
         return $this->getEnum();
     }
     // Ensure the table level cache exists
     if (empty(self::$classname_cache[$table])) {
         self::$classname_cache[$table] = array();
     }
     // Check existing cache
     if (!empty(self::$classname_cache[$table][$name])) {
         return self::$classname_cache[$table][$name];
     }
     // Get all class names
     $classNames = $this->getEnum();
     if (DB::get_schema()->hasField($table, $name)) {
         $existing = DB::query("SELECT DISTINCT \"{$name}\" FROM \"{$table}\"")->column();
         $classNames = array_unique(array_merge($classNames, $existing));
     }
     // Cache and return
     self::$classname_cache[$table][$name] = $classNames;
     return $classNames;
 }

 public function testSavingUnchecked()
 {
     /* Create a new test data record */
     $article = new CheckboxFieldTest_Article();
     /* Create a field, with no value */
     $field = new CheckboxField('IsChecked', 'Checked');
     /* Save the field into our Article object */
     $field->saveInto($article);
     /* Write the record to the test database */
     $article->write();
     /* Check that IsChecked column contains a 0 */
     $this->assertEquals(DB::query("SELECT \"IsChecked\" FROM \"CheckboxFieldTest_Article\"")->value(), 0, 'We have a 0 set in the database, because the field saved into as a 0');
     /* Delete the record we tested */
     $article->delete();
 }

 /**
  * Remove invalid records from tables - that is, records that don't have
  * corresponding records in their parent class tables.
  */
 public function cleanup()
 {
     $allClasses = get_declared_classes();
     foreach ($allClasses as $class) {
         if (get_parent_class($class) == 'SilverStripe\\ORM\\DataObject') {
             $baseClasses[] = $class;
         }
     }
     foreach ($baseClasses as $baseClass) {
         // Get data classes
         $subclasses = ClassInfo::subclassesFor($baseClass);
         unset($subclasses[0]);
         foreach ($subclasses as $k => $subclass) {
             if (DataObject::has_own_table($subclass)) {
                 unset($subclasses[$k]);
             }
         }
         if ($subclasses) {
             $records = DB::query("SELECT * FROM \"{$baseClass}\"");
             foreach ($subclasses as $subclass) {
                 $recordExists[$subclass] = DB::query("SELECT \"ID\" FROM \"{$subclass}\"")->keyedColumn();
             }
             foreach ($records as $record) {
                 foreach ($subclasses as $subclass) {
                     $id = $record['ID'];
                     if ($record['ClassName'] != $subclass && !is_subclass_of($record['ClassName'], $subclass) && isset($recordExists[$subclass][$id])) {
                         $sql = "DELETE FROM \"{$subclass}\" WHERE \"ID\" = {$record['ID']}";
                         echo "<li>{$sql}";
                         DB::query($sql);
                     }
                 }
             }
         }
     }
 }

 /**
  * Get a list of all available permission codes, both defined through the
  * {@link PermissionProvider} interface, and all not explicitly defined codes existing
  * as a {@link Permission} database record. By default, the results are
  * grouped as denoted by {@link Permission_Group}.
  *
  * @param bool $grouped Group results into an array of permission groups.
  * @return array Returns an array of all available permission codes. The
  *  array indicies are the permission codes as used in
  *  {@link Permission::check()}. The value is a description
  *  suitable for using in an interface.
  */
 public static function get_codes($grouped = true)
 {
     $classes = ClassInfo::implementorsOf('SilverStripe\\Security\\PermissionProvider');
     $allCodes = array();
     $adminCategory = _t('Permission.AdminGroup', 'Administrator');
     $allCodes[$adminCategory]['ADMIN'] = array('name' => _t('Permission.FULLADMINRIGHTS', 'Full administrative rights'), 'help' => _t('Permission.FULLADMINRIGHTS_HELP', 'Implies and overrules all other assigned permissions.'), 'sort' => 100000);
     if ($classes) {
         foreach ($classes as $class) {
             $SNG = singleton($class);
             if ($SNG instanceof TestOnly) {
                 continue;
             }
             $someCodes = $SNG->providePermissions();
             if ($someCodes) {
                 foreach ($someCodes as $k => $v) {
                     if (is_array($v)) {
                         // There must be a category and name key.
                         if (!isset($v['category'])) {
                             user_error("The permission {$k} must have a category key", E_USER_WARNING);
                         }
                         if (!isset($v['name'])) {
                             user_error("The permission {$k} must have a name key", E_USER_WARNING);
                         }
                         if (!isset($allCodes[$v['category']])) {
                             $allCodes[$v['category']] = array();
                         }
                         $allCodes[$v['category']][$k] = array('name' => $v['name'], 'help' => isset($v['help']) ? $v['help'] : null, 'sort' => isset($v['sort']) ? $v['sort'] : 0);
                     } else {
                         $allCodes['Other'][$k] = array('name' => $v, 'help' => null, 'sort' => 0);
                     }
                 }
             }
         }
     }
     $flatCodeArray = array();
     foreach ($allCodes as $category) {
         foreach ($category as $code => $permission) {
             $flatCodeArray[] = $code;
         }
     }
     $otherPerms = DB::query("SELECT DISTINCT \"Code\" From \"Permission\" WHERE \"Code\" != ''")->column();
     if ($otherPerms) {
         foreach ($otherPerms as $otherPerm) {
             if (!in_array($otherPerm, $flatCodeArray)) {
                 $allCodes['Other'][$otherPerm] = array('name' => $otherPerm, 'help' => null, 'sort' => 0);
             }
         }
     }
     // Don't let people hijack ADMIN rights
     if (!Permission::check("ADMIN")) {
         unset($allCodes['ADMIN']);
     }
     ksort($allCodes);
     $returnCodes = array();
     foreach ($allCodes as $category => $permissions) {
         if ($grouped) {
             uasort($permissions, array(__CLASS__, 'sort_permissions'));
             $returnCodes[$category] = $permissions;
         } else {
             $returnCodes = array_merge($returnCodes, $permissions);
         }
     }
     return $returnCodes;
 }

 /**
  * Remove invalid records from tables - that is, records that don't have
  * corresponding records in their parent class tables.
  */
 public function cleanup()
 {
     $baseClasses = [];
     foreach (ClassInfo::subclassesFor(DataObject::class) as $class) {
         if (get_parent_class($class) == DataObject::class) {
             $baseClasses[] = $class;
         }
     }
     $schema = DataObject::getSchema();
     foreach ($baseClasses as $baseClass) {
         // Get data classes
         $baseTable = $schema->baseDataTable($baseClass);
         $subclasses = ClassInfo::subclassesFor($baseClass);
         unset($subclasses[0]);
         foreach ($subclasses as $k => $subclass) {
             if (!DataObject::getSchema()->classHasTable($subclass)) {
                 unset($subclasses[$k]);
             }
         }
         if ($subclasses) {
             $records = DB::query("SELECT * FROM \"{$baseTable}\"");
             foreach ($subclasses as $subclass) {
                 $subclassTable = $schema->tableName($subclass);
                 $recordExists[$subclass] = DB::query("SELECT \"ID\" FROM \"{$subclassTable}\"")->keyedColumn();
             }
             foreach ($records as $record) {
                 foreach ($subclasses as $subclass) {
                     $subclassTable = $schema->tableName($subclass);
                     $id = $record['ID'];
                     if ($record['ClassName'] != $subclass && !is_subclass_of($record['ClassName'], $subclass) && isset($recordExists[$subclass][$id])) {
                         $sql = "DELETE FROM \"{$subclassTable}\" WHERE \"ID\" = ?";
                         echo "<li>{$sql} [{$id}]</li>";
                         DB::prepared_query($sql, [$id]);
                     }
                 }
             }
         }
     }
 }

 public function testWriteToDataObject()
 {
     $obj = new MoneyTest_DataObject();
     $m = new DBMoney();
     $m->setValue(array('Currency' => 'EUR', 'Amount' => 1.23));
     $obj->MyMoney = $m;
     $obj->write();
     $this->assertEquals('EUR', DB::query(sprintf('SELECT "MyMoneyCurrency" FROM "MoneyTest_DataObject" WHERE "ID" = %d', $obj->ID))->value());
     $this->assertEquals('1.23', DB::query(sprintf('SELECT "MyMoneyAmount" FROM "MoneyTest_DataObject" WHERE "ID" = %d', $obj->ID))->value());
 }

 public function testSavingIntoTextField()
 {
     $field = new CheckboxSetField('Content', 'Content', array('Test' => 'Test', 'Another' => 'Another', 'Something' => 'Something'));
     $article = new CheckboxSetFieldTest_Article();
     $field->setValue(array('Test' => 'Test', 'Another' => 'Another'));
     $field->saveInto($article);
     $article->write();
     $dbValue = DB::query(sprintf('SELECT "Content" FROM "CheckboxSetFieldTest_Article" WHERE "ID" = %s', $article->ID))->value();
     // JSON encoded values
     $this->assertEquals('["Test","Another"]', $dbValue);
 }

 public function testDeleteFromStage()
 {
     $page1 = $this->objFromFixture('VersionedTest_DataObject', 'page1');
     $pageID = $page1->ID;
     $page1->Content = 'orig';
     $page1->write();
     $page1->copyVersionToStage(Versioned::DRAFT, Versioned::LIVE);
     $this->assertEquals(1, DB::query('SELECT COUNT(*) FROM "VersionedTest_DataObject" WHERE "ID" = ' . $pageID)->value());
     $this->assertEquals(1, DB::query('SELECT COUNT(*) FROM "VersionedTest_DataObject_Live" WHERE "ID" = ' . $pageID)->value());
     $page1->deleteFromStage('Live');
     // Confirm that deleteFromStage() doesn't manipulate the original record
     $this->assertEquals($pageID, $page1->ID);
     $this->assertEquals(1, DB::query('SELECT COUNT(*) FROM "VersionedTest_DataObject" WHERE "ID" = ' . $pageID)->value());
     $this->assertEquals(0, DB::query('SELECT COUNT(*) FROM "VersionedTest_DataObject_Live" WHERE "ID" = ' . $pageID)->value());
     $page1->delete();
     $this->assertEquals(0, $page1->ID);
     $this->assertEquals(0, DB::query('SELECT COUNT(*) FROM "VersionedTest_DataObject" WHERE "ID" = ' . $pageID)->value());
     $this->assertEquals(0, DB::query('SELECT COUNT(*) FROM "VersionedTest_DataObject_Live" WHERE "ID" = ' . $pageID)->value());
 }

 /**
  * Test that passwords validate against NZ e-government guidelines
  *  - don't allow the use of the last 6 passwords
  *  - require at least 3 of lowercase, uppercase, digits and punctuation
  *  - at least 7 characters long
  */
 public function testValidatePassword()
 {
     $member = $this->objFromFixture('SilverStripe\\Security\\Member', 'test');
     $this->assertNotNull($member);
     Member::set_password_validator(new MemberTest_PasswordValidator());
     // BAD PASSWORDS
     $valid = $member->changePassword('shorty');
     $this->assertFalse($valid->valid());
     $this->assertContains("TOO_SHORT", $valid->codeList());
     $valid = $member->changePassword('longone');
     $this->assertNotContains("TOO_SHORT", $valid->codeList());
     $this->assertContains("LOW_CHARACTER_STRENGTH", $valid->codeList());
     $this->assertFalse($valid->valid());
     $valid = $member->changePassword('w1thNumb3rs');
     $this->assertNotContains("LOW_CHARACTER_STRENGTH", $valid->codeList());
     $this->assertTrue($valid->valid());
     // Clear out the MemberPassword table to ensure that the system functions properly in that situation
     DB::query("DELETE FROM \"MemberPassword\"");
     // GOOD PASSWORDS
     $valid = $member->changePassword('withSym###Ls');
     $this->assertNotContains("LOW_CHARACTER_STRENGTH", $valid->codeList());
     $this->assertTrue($valid->valid());
     $valid = $member->changePassword('withSym###Ls2');
     $this->assertTrue($valid->valid());
     $valid = $member->changePassword('withSym###Ls3');
     $this->assertTrue($valid->valid());
     $valid = $member->changePassword('withSym###Ls4');
     $this->assertTrue($valid->valid());
     $valid = $member->changePassword('withSym###Ls5');
     $this->assertTrue($valid->valid());
     $valid = $member->changePassword('withSym###Ls6');
     $this->assertTrue($valid->valid());
     $valid = $member->changePassword('withSym###Ls7');
     $this->assertTrue($valid->valid());
     // CAN'T USE PASSWORDS 2-7, but I can use pasword 1
     $valid = $member->changePassword('withSym###Ls2');
     $this->assertFalse($valid->valid());
     $this->assertContains("PREVIOUS_PASSWORD", $valid->codeList());
     $valid = $member->changePassword('withSym###Ls5');
     $this->assertFalse($valid->valid());
     $this->assertContains("PREVIOUS_PASSWORD", $valid->codeList());
     $valid = $member->changePassword('withSym###Ls7');
     $this->assertFalse($valid->valid());
     $this->assertContains("PREVIOUS_PASSWORD", $valid->codeList());
     $valid = $member->changePassword('withSym###Ls');
     $this->assertTrue($valid->valid());
     // HAVING DONE THAT, PASSWORD 2 is now available from the list
     $valid = $member->changePassword('withSym###Ls2');
     $this->assertTrue($valid->valid());
     $valid = $member->changePassword('withSym###Ls3');
     $this->assertTrue($valid->valid());
     $valid = $member->changePassword('withSym###Ls4');
     $this->assertTrue($valid->valid());
     Member::set_password_validator(null);
 }

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

 public function testLazyLoadedFieldsDoNotReferenceVersionsTable()
 {
     // Save another record, sanity check that we're getting the right one
     $obj2 = new VersionedTest_Subclass();
     $obj2->Name = "test2";
     $obj2->ExtraField = "foo2";
     $obj2->write();
     $obj1 = new VersionedLazySub_DataObject();
     $obj1->PageName = "old-value";
     $obj1->ExtraField = "old-value";
     $obj1ID = $obj1->write();
     $obj1->copyVersionToStage(Versioned::DRAFT, Versioned::LIVE);
     $obj1 = VersionedLazySub_DataObject::get()->byID($obj1ID);
     $this->assertEquals('old-value', $obj1->PageName, "Correct value on base table when fetching base class");
     $this->assertEquals('old-value', $obj1->ExtraField, "Correct value on sub table when fetching base class");
     $obj1 = VersionedLazy_DataObject::get()->byID($obj1ID);
     $this->assertEquals('old-value', $obj1->PageName, "Correct value on base table when fetching sub class");
     $this->assertEquals('old-value', $obj1->ExtraField, "Correct value on sub table when fetching sub class");
     // Force inconsistent state to test behaviour (shouldn't select from *_versions)
     DB::query(sprintf("UPDATE \"VersionedLazy_DataObject_versions\" SET \"PageName\" = 'versioned-value' " . "WHERE \"RecordID\" = %d", $obj1ID));
     DB::query(sprintf("UPDATE \"VersionedLazySub_DataObject_versions\" SET \"ExtraField\" = 'versioned-value' " . "WHERE \"RecordID\" = %d", $obj1ID));
     $obj1 = VersionedLazySub_DataObject::get()->byID($obj1ID);
     $this->assertEquals('old-value', $obj1->PageName, "Correct value on base table when fetching base class");
     $this->assertEquals('old-value', $obj1->ExtraField, "Correct value on sub table when fetching base class");
     $obj1 = VersionedLazy_DataObject::get()->byID($obj1ID);
     $this->assertEquals('old-value', $obj1->PageName, "Correct value on base table when fetching sub class");
     $this->assertEquals('old-value', $obj1->ExtraField, "Correct value on sub table when fetching sub class");
     // Update live table only to test behaviour (shouldn't select from *_versions or stage)
     DB::query(sprintf('UPDATE "VersionedLazy_DataObject_Live" SET "PageName" = \'live-value\' WHERE "ID" = %d', $obj1ID));
     DB::query(sprintf('UPDATE "VersionedLazySub_DataObject_Live" SET "ExtraField" = \'live-value\' WHERE "ID" = %d', $obj1ID));
     Versioned::set_stage(Versioned::LIVE);
     $obj1 = VersionedLazy_DataObject::get()->byID($obj1ID);
     $this->assertEquals('live-value', $obj1->PageName, "Correct value from base table when fetching base class on live stage");
     $this->assertEquals('live-value', $obj1->ExtraField, "Correct value from sub table when fetching base class on live stage");
 }

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