~ubuntu-branches/ubuntu/utopic/moodle/utopic

$expected = "SELECT * FROM {$prefix}user, {$prefix}funny_table_name, {$prefix}mdl_stupid_table WHERE {$prefix}user.id = {$prefix}funny_table_name.userid";

295

$this->assertSame($expected, $DB->public_fix_table_names($sql));

296

}

297

298

function test_fix_sql_params() {

298

public function test_fix_sql_params() {

299

$DB = $this->tdb;

300

$prefix = $DB->get_prefix();

301

302

$table = $this->get_test_table();

303

$tablename = $table->getName();

304

305

// Correct table placeholder substitution

305

// Correct table placeholder substitution.

306

$sql = "SELECT * FROM {{$tablename}}";

307

$sqlarray = $DB->fix_sql_params($sql);

308

$this->assertEquals("SELECT * FROM {$prefix}".$tablename, $sqlarray[0]);

309

310

// Conversions of all param types

310

// Conversions of all param types.

311

$sql = array();

312

$sql[SQL_PARAMS_NAMED] = "SELECT * FROM {$prefix}testtable WHERE name = :param1, course = :param2";

313

$sql[SQL_PARAMS_QM] = "SELECT * FROM {$prefix}testtable WHERE name = ?, course = ?";

330

$this->assertSame($rsql, $sql[$rtype]);

331

$this->assertSame($rparams, $params[$rtype]);

332

333

334

// Malformed table placeholder

333

// Malformed table placeholder.

335

334

$sql = "SELECT * FROM [testtable]";

336

335

$sqlarray = $DB->fix_sql_params($sql);

337

336

$this->assertSame($sql, $sqlarray[0]);

338

337

339

340

// Mixed param types (colon and dollar)

338

// Mixed param types (colon and dollar).

341

339

$sql = "SELECT * FROM {{$tablename}} WHERE name = :param1, course = \$1";

342

340

$params = array('param1' => 'record1', 'param2' => 3);

343

341

try {

344

342

$DB->fix_sql_params($sql, $params);

345

343

$this->fail("Expecting an exception, none occurred");

346

} catch (Exception $e) {

347

$this->assertTrue($e instanceof dml_exception);

344

} catch (moodle_exception $e) {

345

$this->assertInstanceOf('dml_exception', $e);

348

346

}

349

347

350

// Mixed param types (question and dollar)

348

// Mixed param types (question and dollar).

351

349

$sql = "SELECT * FROM {{$tablename}} WHERE name = ?, course = \$1";

352

350

$params = array('param1' => 'record2', 'param2' => 5);

353

351

try {

354

352

$DB->fix_sql_params($sql, $params);

355

353

$this->fail("Expecting an exception, none occurred");

356

} catch (Exception $e) {

357

$this->assertTrue($e instanceof dml_exception);

354

} catch (moodle_exception $e) {

355

$this->assertInstanceOf('dml_exception', $e);

358

356

}

359

357

360

// Too few params in sql

358

// Too few params in sql.

361

359

$sql = "SELECT * FROM {{$tablename}} WHERE name = ?, course = ?, id = ?";

362

360

$params = array('record2', 3);

363

361

try {

364

362

$DB->fix_sql_params($sql, $params);

365

363

$this->fail("Expecting an exception, none occurred");

366

} catch (Exception $e) {

367

$this->assertTrue($e instanceof dml_exception);

364

} catch (moodle_exception $e) {

365

$this->assertInstanceOf('dml_exception', $e);

368

366

}

369

367

370

// Too many params in array: no error, just use what is necessary

368

// Too many params in array: no error, just use what is necessary.

371

369

$params[] = 1;

372

370

$params[] = time();

373

try {

374

$sqlarray = $DB->fix_sql_params($sql, $params);

375

$this->assertTrue(is_array($sqlarray));

376

$this->assertEquals(count($sqlarray[1]), 3);

377

} catch (Exception $e) {

378

$this->fail("Unexpected ".get_class($e)." exception");

379

}

371

$sqlarray = $DB->fix_sql_params($sql, $params);

372

$this->assertInternalType('array', $sqlarray);

373

$this->assertCount(3, $sqlarray[1]);

380

374

381

// Named params missing from array

375

// Named params missing from array.

382

376

$sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :course";

383

377

$params = array('wrongname' => 'record1', 'course' => 1);

384

378

try {

385

379

$DB->fix_sql_params($sql, $params);

386

380

$this->fail("Expecting an exception, none occurred");

387

} catch (Exception $e) {

388

$this->assertTrue($e instanceof dml_exception);

381

} catch (moodle_exception $e) {

382

$this->assertInstanceOf('dml_exception', $e);

389

383

}

390

384

391

385

// Duplicate named param in query - this is a very important feature!!

392

// it helps with debugging of sloppy code

386

// it helps with debugging of sloppy code.

393

387

$sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :name";

394

388

$params = array('name' => 'record2', 'course' => 3);

395

389

try {

396

390

$DB->fix_sql_params($sql, $params);

397

391

$this->fail("Expecting an exception, none occurred");

398

} catch (Exception $e) {

399

$this->assertTrue($e instanceof dml_exception);

392

} catch (moodle_exception $e) {

393

$this->assertInstanceOf('dml_exception', $e);

400

394

}

401

395

402

// Extra named param is ignored

396

// Extra named param is ignored.

403

397

$sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :course";

404

398

$params = array('name' => 'record1', 'course' => 1, 'extrastuff'=>'haha');

405

try {

406

$sqlarray = $DB->fix_sql_params($sql, $params);

407

$this->assertTrue(is_array($sqlarray));

408

$this->assertEquals(count($sqlarray[1]), 2);

409

} catch (Exception $e) {

410

$this->fail("Unexpected ".get_class($e)." exception");

411

}

399

$sqlarray = $DB->fix_sql_params($sql, $params);

400

$this->assertInternalType('array', $sqlarray);

401

$this->assertCount(2, $sqlarray[1]);

412

402

413

// Params exceeding 30 chars length

403

// Params exceeding 30 chars length.

414

404

$sql = "SELECT * FROM {{$tablename}} WHERE name = :long_placeholder_with_more_than_30";

415

405

$params = array('long_placeholder_with_more_than_30' => 'record1');

416

406

try {

417

407

$DB->fix_sql_params($sql, $params);

418

408

$this->fail("Expecting an exception, none occurred");

419

} catch (Exception $e) {

420

$this->assertTrue($e instanceof coding_exception);

409

} catch (moodle_exception $e) {

410

$this->assertInstanceOf('coding_exception', $e);

421

411

}

422

412

423

// Booleans in NAMED params are casting to 1/0 int

413

// Booleans in NAMED params are casting to 1/0 int.

424

414

$sql = "SELECT * FROM {{$tablename}} WHERE course = ? OR course = ?";

425

415

$params = array(true, false);

426

416

list($sql, $params) = $DB->fix_sql_params($sql, $params);

427

417

$this->assertTrue(reset($params) === 1);

428

418

$this->assertTrue(next($params) === 0);

429

419

430

// Booleans in QM params are casting to 1/0 int

420

// Booleans in QM params are casting to 1/0 int.

431

421

$sql = "SELECT * FROM {{$tablename}} WHERE course = :course1 OR course = :course2";

432

422

$params = array('course1' => true, 'course2' => false);

433

423

list($sql, $params) = $DB->fix_sql_params($sql, $params);

434

424

$this->assertTrue(reset($params) === 1);

435

425

$this->assertTrue(next($params) === 0);

436

426

437

// Booleans in DOLLAR params are casting to 1/0 int

427

// Booleans in DOLLAR params are casting to 1/0 int.

438

428

$sql = "SELECT * FROM {{$tablename}} WHERE course = \$1 OR course = \$2";

439

429

$params = array(true, false);

440

430

list($sql, $params) = $DB->fix_sql_params($sql, $params);

441

431

$this->assertTrue(reset($params) === 1);

442

432

$this->assertTrue(next($params) === 0);

443

433

444

// No data types are touched except bool

434

// No data types are touched except bool.

445

435

$sql = "SELECT * FROM {{$tablename}} WHERE name IN (?,?,?,?,?,?)";

446

$inparams = array('abc', 'ABC', NULL, '1', 1, 1.4);

436

$inparams = array('abc', 'ABC', null, '1', 1, 1.4);

447

437

list($sql, $params) = $DB->fix_sql_params($sql, $inparams);

448

438

$this->assertSame(array_values($params), array_values($inparams));

449

439

}

450

440

451

441

public function test_strtok() {

452

// strtok was previously used by bound emulation, make sure it is not used any more

442

// Strtok was previously used by bound emulation, make sure it is not used any more.

453

443

$DB = $this->tdb;

454

444

$dbman = $this->tdb->get_manager();

455

445

456

446

$table = $this->get_test_table();

457

447

$tablename = $table->getName();

458

448

459

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

460

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

449

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

450

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

461

451

$table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, 'lala');

462

452

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

463

453

$dbman->create_table($table);

472

462

473

463

public function test_tweak_param_names() {

474

464

// Note the tweak_param_names() method is only available in the oracle driver,

475

// hence we look for expected results indirectly, by testing various DML methods

465

// hence we look for expected results indirectly, by testing various DML methods.

476

466

// with some "extreme" conditions causing the tweak to happen.

477

467

$DB = $this->tdb;

478

468

$dbman = $this->tdb->get_manager();

480

470

$table = $this->get_test_table();

481

471

$tablename = $table->getName();

482

472

483

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

484

// Add some columns with 28 chars in the name

473

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

474

// Add some columns with 28 chars in the name.

485

475

$table->add_field('long_int_columnname_with_28c', XMLDB_TYPE_INTEGER, '10');

486

476

$table->add_field('long_dec_columnname_with_28c', XMLDB_TYPE_NUMBER, '10,2');

487

477

$table->add_field('long_str_columnname_with_28c', XMLDB_TYPE_CHAR, '100');

488

// Add some columns with 30 chars in the name

478

// Add some columns with 30 chars in the name.

489

479

$table->add_field('long_int_columnname_with_30cxx', XMLDB_TYPE_INTEGER, '10');

490

480

$table->add_field('long_dec_columnname_with_30cxx', XMLDB_TYPE_NUMBER, '10,2');

491

481

$table->add_field('long_str_columnname_with_30cxx', XMLDB_TYPE_CHAR, '100');

496

486

497

487

$this->assertTrue($dbman->table_exists($tablename));

498

488

499

// Test insert record

489

// Test insert record.

500

490

$rec1 = new stdClass();

501

491

$rec1->long_int_columnname_with_28c = 28;

502

492

$rec1->long_dec_columnname_with_28c = 28.28;

505

495

$rec1->long_dec_columnname_with_30cxx = 30.30;

506

496

$rec1->long_str_columnname_with_30cxx = '30';

507

497

508

// insert_record()

498

// Insert_record().

509

499

$rec1->id = $DB->insert_record($tablename, $rec1);

510

500

$this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id)));

511

501

512

// update_record()

502

// Update_record().

513

503

$DB->update_record($tablename, $rec1);

514

504

$this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id)));

515

505

516

// set_field()

506

// Set_field().

517

507

$rec1->long_int_columnname_with_28c = 280;

518

508

$DB->set_field($tablename, 'long_int_columnname_with_28c', $rec1->long_int_columnname_with_28c,

519

509

array('id' => $rec1->id, 'long_int_columnname_with_28c' => 28));

534

524

array('id' => $rec1->id, 'long_str_columnname_with_30cxx' => '30'));

535

525

$this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id)));

536

526

537

// delete_records()

527

// Delete_records().

538

528

$rec2 = $DB->get_record($tablename, array('id' => $rec1->id));

539

529

$rec2->id = $DB->insert_record($tablename, $rec2);

540

530

$this->assertEquals(2, $DB->count_records($tablename));

541

531

$DB->delete_records($tablename, (array) $rec2);

542

532

$this->assertEquals(1, $DB->count_records($tablename));

543

533

544

// get_recordset()

534

// Get_recordset().

545

535

$rs = $DB->get_recordset($tablename, (array) $rec1);

546

536

$iterations = 0;

547

537

foreach ($rs as $rec2) {

551

541

$this->assertEquals(1, $iterations);

552

542

$this->assertEquals($rec1, $rec2);

553

543

554

// get_records()

544

// Get_records().

555

545

$recs = $DB->get_records($tablename, (array) $rec1);

556

$this->assertEquals(1, count($recs));

546

$this->assertCount(1, $recs);

557

547

$this->assertEquals($rec1, reset($recs));

558

548

559

// get_fieldset_select()

549

// Get_fieldset_select().

560

550

$select = 'id = :id AND

561

551

long_int_columnname_with_28c = :long_int_columnname_with_28c AND

562

552

long_dec_columnname_with_28c = :long_dec_columnname_with_28c AND

565

555

long_dec_columnname_with_30cxx = :long_dec_columnname_with_30cxx AND

566

556

long_str_columnname_with_30cxx = :long_str_columnname_with_30cxx';

567

557

$fields = $DB->get_fieldset_select($tablename, 'long_int_columnname_with_28c', $select, (array)$rec1);

568

$this->assertEquals(1, count($fields));

558

$this->assertCount(1, $fields);

569

559

$this->assertEquals($rec1->long_int_columnname_with_28c, reset($fields));

570

560

$fields = $DB->get_fieldset_select($tablename, 'long_dec_columnname_with_28c', $select, (array)$rec1);

571

561

$this->assertEquals($rec1->long_dec_columnname_with_28c, reset($fields));

578

568

$fields = $DB->get_fieldset_select($tablename, 'long_str_columnname_with_30cxx', $select, (array)$rec1);

579

569

$this->assertEquals($rec1->long_str_columnname_with_30cxx, reset($fields));

580

570

581

// overlapping placeholders (progressive str_replace)

571

// Overlapping placeholders (progressive str_replace).

582

572

$overlapselect = 'id = :p AND

583

573

long_int_columnname_with_28c = :param1 AND

584

574

long_dec_columnname_with_28c = :param2 AND

595

585

'param_' => $rec1->long_dec_columnname_with_30cxx,

596

586

'param__' => $rec1->long_str_columnname_with_30cxx);

597

587

$recs = $DB->get_records_select($tablename, $overlapselect, $overlapparams);

598

$this->assertEquals(1, count($recs));

588

$this->assertCount(1, $recs);

599

589

$this->assertEquals($rec1, reset($recs));

600

590

601

// execute()

591

// Execute().

602

592

$DB->execute("DELETE FROM {{$tablename}} WHERE $select", (array)$rec1);

603

593

$this->assertEquals(0, $DB->count_records($tablename));

604

594

}

607

597

$DB = $this->tdb;

608

598

$dbman = $this->tdb->get_manager();

609

599

610

// Need to test with multiple DBs

600

// Need to test with multiple DBs.

611

601

$table = $this->get_test_table();

612

602

$tablename = $table->getName();

613

603

614

604

$original_count = count($DB->get_tables());

615

605

616

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

606

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

617

607

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

618

608

619

609

$dbman->create_table($table);

630

620

$table = $this->get_test_table();

631

621

$tablename = $table->getName();

632

622

633

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

634

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

623

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

624

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

635

625

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

636

626

$table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));

637

627

$table->add_index('course-id', XMLDB_INDEX_UNIQUE, array('course', 'id'));

638

628

$dbman->create_table($table);

639

629

640

630

$indices = $DB->get_indexes($tablename);

641

$this->assertTrue(is_array($indices));

642

$this->assertEquals(count($indices), 2);

643

// we do not care about index names for now

631

$this->assertInternalType('array', $indices);

632

$this->assertCount(2, $indices);

633

// We do not care about index names for now.

644

634

$first = array_shift($indices);

645

635

$second = array_shift($indices);

646

636

if (count($first['columns']) == 2) {

652

642

}

653

643

$this->assertFalse($single['unique']);

654

644

$this->assertTrue($composed['unique']);

655

$this->assertEquals(1, count($single['columns']));

656

$this->assertEquals(2, count($composed['columns']));

657

$this->assertEquals('course', $single['columns'][0]);

658

$this->assertEquals('course', $composed['columns'][0]);

659

$this->assertEquals('id', $composed['columns'][1]);

645

$this->assertCount(1, $single['columns']);

646

$this->assertCount(2, $composed['columns']);

647

$this->assertSame('course', $single['columns'][0]);

648

$this->assertSame('course', $composed['columns'][0]);

649

$this->assertSame('id', $composed['columns'][1]);

660

650

}

661

651

662

652

public function test_get_columns() {

666

656

$table = $this->get_test_table();

667

657

$tablename = $table->getName();

668

658

669

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

670

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

659

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

660

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

671

661

$table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, 'lala');

672

662

$table->add_field('description', XMLDB_TYPE_TEXT, 'small', null, null, null, null);

673

663

$table->add_field('enumfield', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'test2');

692

682

$dbman->create_table($table);

693

683

694

684

$columns = $DB->get_columns($tablename);

695

$this->assertTrue(is_array($columns));

685

$this->assertInternalType('array', $columns);

696

686

697

687

$fields = $table->getFields();

698

$this->assertEquals(count($columns), count($fields));

688

$this->assertCount(count($columns), $fields);

699

689

700

690

$field = $columns['id'];

701

$this->assertEquals('R', $field->meta_type);

691

$this->assertSame('R', $field->meta_type);

702

692

$this->assertTrue($field->auto_increment);

703

693

$this->assertTrue($field->unique);

704

694

705

695

$field = $columns['course'];

706

$this->assertEquals('I', $field->meta_type);

696

$this->assertSame('I', $field->meta_type);

707

697

$this->assertFalse($field->auto_increment);

708

698

$this->assertTrue($field->has_default);

709

699

$this->assertEquals(0, $field->default_value);

710

700

$this->assertTrue($field->not_null);

711

701

712

for($i=1;$i<=10;$i++) {

702

for ($i=1; $i<=10; $i++) {

713

703

$field = $columns['someint'.$i];

714

$this->assertEquals('I', $field->meta_type);

704

$this->assertSame('I', $field->meta_type);

715

705

$this->assertGreaterThanOrEqual($i, $field->max_length);

716

706

}

717

707

$field = $columns['someint18'];

718

$this->assertEquals('I', $field->meta_type);

708

$this->assertSame('I', $field->meta_type);

719

709

$this->assertGreaterThanOrEqual(18, $field->max_length);

720

710

721

711

$field = $columns['name'];

722

$this->assertEquals('C', $field->meta_type);

712

$this->assertSame('C', $field->meta_type);

723

713

$this->assertFalse($field->auto_increment);

724

714

$this->assertEquals(255, $field->max_length);

725

715

$this->assertTrue($field->has_default);

727

717

$this->assertFalse($field->not_null);

728

718

729

719

$field = $columns['description'];

730

$this->assertEquals('X', $field->meta_type);

720

$this->assertSame('X', $field->meta_type);

731

721

$this->assertFalse($field->auto_increment);

732

722

$this->assertFalse($field->has_default);

733

$this->assertSame(null, $field->default_value);

723

$this->assertNull($field->default_value);

734

724

$this->assertFalse($field->not_null);

735

725

736

726

$field = $columns['enumfield'];

737

$this->assertEquals('C', $field->meta_type);

727

$this->assertSame('C', $field->meta_type);

738

728

$this->assertFalse($field->auto_increment);

739

729

$this->assertSame('test2', $field->default_value);

740

730

$this->assertTrue($field->not_null);

741

731

742

732

$field = $columns['onenum'];

743

$this->assertEquals('N', $field->meta_type);

733

$this->assertSame('N', $field->meta_type);

744

734

$this->assertFalse($field->auto_increment);

745

735

$this->assertEquals(10, $field->max_length);

746

736

$this->assertEquals(2, $field->scale);

749

739

$this->assertFalse($field->not_null);

750

740

751

741

$field = $columns['onefloat'];

752

$this->assertEquals('N', $field->meta_type);

742

$this->assertSame('N', $field->meta_type);

753

743

$this->assertFalse($field->auto_increment);

754

744

$this->assertTrue($field->has_default);

755

745

$this->assertEquals(300.0, $field->default_value);

756

746

$this->assertFalse($field->not_null);

757

747

758

748

$field = $columns['anotherfloat'];

759

$this->assertEquals('N', $field->meta_type);

749

$this->assertSame('N', $field->meta_type);

760

750

$this->assertFalse($field->auto_increment);

761

751

$this->assertTrue($field->has_default);

762

752

$this->assertEquals(400.0, $field->default_value);

763

753

$this->assertFalse($field->not_null);

764

754

765

// Test negative defaults in numerical columns

755

// Test negative defaults in numerical columns.

766

756

$field = $columns['negativedfltint'];

767

757

$this->assertTrue($field->has_default);

768

758

$this->assertEquals(-1, $field->default_value);

787

777

$this->assertEquals($next_column->name, $next_field->getName());

788

778

}

789

779

790

// Test get_columns for non-existing table returns empty array. MDL-30147

780

// Test get_columns for non-existing table returns empty array. MDL-30147.

791

781

$columns = $DB->get_columns('xxxx');

792

782

$this->assertEquals(array(), $columns);

793

783

794

// create something similar to "context_temp" with id column without sequence

784

// Create something similar to "context_temp" with id column without sequence.

795

785

$dbman->drop_table($table);

796

786

$table = $this->get_test_table();

797

787

$tablename = $table->getName();

798

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null);

799

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

788

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);

789

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

800

790

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

801

791

$dbman->create_table($table);

802

792

808

798

$DB = $this->tdb;

809

799

$dbman = $this->tdb->get_manager();

810

800

811

$this->assertTrue($dbman instanceof database_manager);

801

$this->assertInstanceOf('database_manager', $dbman);

812

802

}

813

803

814

804

public function test_setup_is_unicodedb() {

816

806

$this->assertTrue($DB->setup_is_unicodedb());

817

807

}

818

808

819

public function test_set_debug() { //tests get_debug() too

809

public function test_set_debug() { // Tests get_debug() too.

820

810

$DB = $this->tdb;

821

811

$dbman = $this->tdb->get_manager();

822

812

823

813

$table = $this->get_test_table();

824

814

$tablename = $table->getName();

825

815

826

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

827

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

816

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

817

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

828

818

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

829

819

$dbman->create_table($table);

830

820

857

847

858

848

$table1 = $this->get_test_table('1');

859

849

$tablename1 = $table1->getName();

860

$table1->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

861

$table1->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

850

$table1->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

851

$table1->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

862

852

$table1->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');

863

853

$table1->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));

864

854

$table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

866

856

867

857

$table2 = $this->get_test_table('2');

868

858

$tablename2 = $table2->getName();

869

$table2->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

870

$table2->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

859

$table2->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

860

$table2->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

871

861

$table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

872

862

$dbman->create_table($table2);

873

863

876

866

$DB->insert_record($tablename1, array('course' => 7, 'name' => 'ccc'));

877

867

$DB->insert_record($tablename1, array('course' => 3, 'name' => 'ddd'));

878

868

879

// select results are ignored

869

// Select results are ignored.

880

870

$sql = "SELECT * FROM {{$tablename1}} WHERE course = :course";

881

871

$this->assertTrue($DB->execute($sql, array('course'=>3)));

882

872

883

// throw exception on error

873

// Throw exception on error.

884

874

$sql = "XXUPDATE SET XSSD";

885

875

try {

886

876

$DB->execute($sql);

887

877

$this->fail("Expecting an exception, none occurred");

888

} catch (Exception $e) {

889

$this->assertTrue($e instanceof dml_exception);

878

} catch (moodle_exception $e) {

879

$this->assertInstanceOf('dml_exception', $e);

890

880

}

891

881

892

// update records

882

// Update records.

893

883

$sql = "UPDATE {{$tablename1}}

894

884

SET course = 6

895

885

WHERE course = ?";

896

886

$this->assertTrue($DB->execute($sql, array('3')));

897

$this->assertEquals($DB->count_records($tablename1, array('course' => 6)), 2);

887

$this->assertEquals(2, $DB->count_records($tablename1, array('course' => 6)));

898

888

899

// update records with subquery condition

900

// confirm that the option not using table aliases is cross-db

889

// Update records with subquery condition.

890

// Confirm that the option not using table aliases is cross-db.

901

891

$sql = "UPDATE {{$tablename1}}

902

892

SET course = 0

903

893

WHERE NOT EXISTS (

904

894

SELECT course

905

895

FROM {{$tablename2}} tbl2

906

896

WHERE tbl2.course = {{$tablename1}}.course

907

AND 1 = 0)"; // Really we don't update anything, but verify the syntax is allowed

897

AND 1 = 0)"; // Really we don't update anything, but verify the syntax is allowed.

908

898

$this->assertTrue($DB->execute($sql));

909

899

910

// insert from one into second table

900

// Insert from one into second table.

911

901

$sql = "INSERT INTO {{$tablename2}} (course)

912

902

913

903

SELECT course

914

904

FROM {{$tablename1}}";

915

905

$this->assertTrue($DB->execute($sql));

916

$this->assertEquals($DB->count_records($tablename2), 4);

906

$this->assertEquals(4, $DB->count_records($tablename2));

917

907

}

918

908

919

909

public function test_get_recordset() {

923

913

$table = $this->get_test_table();

924

914

$tablename = $table->getName();

925

915

926

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

927

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

916

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

917

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

928

918

$table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');

929

919

$table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);

930

920

$table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));

935

925

array('course' => 3, 'name' => 'record2', 'onetext'=>'abcd'),

936

926

array('course' => 5, 'name' => 'record3', 'onetext'=>'abcde'));

937

927

938

foreach ($data as $key=>$record) {

928

foreach ($data as $key => $record) {

939

929

$data[$key]['id'] = $DB->insert_record($tablename, $record);

940

930

}

941

931

942

// standard recordset iteration

932

// Standard recordset iteration.

943

933

$rs = $DB->get_recordset($tablename);

944

$this->assertTrue($rs instanceof moodle_recordset);

934

$this->assertInstanceOf('moodle_recordset', $rs);

945

935

reset($data);

946

foreach($rs as $record) {

936

foreach ($rs as $record) {

947

937

$data_record = current($data);

948

938

foreach ($record as $k => $v) {

949

939

$this->assertEquals($data_record[$k], $v);

952

942

}

953

943

$rs->close();

954

944

955

// iterator style usage

945

// Iterator style usage.

956

946

$rs = $DB->get_recordset($tablename);

957

$this->assertTrue($rs instanceof moodle_recordset);

947

$this->assertInstanceOf('moodle_recordset', $rs);

958

948

reset($data);

959

949

while ($rs->valid()) {

960

950

$record = $rs->current();

967

957

}

968

958

$rs->close();

969

959

970

// make sure rewind is ignored

960

// Make sure rewind is ignored.

971

961

$rs = $DB->get_recordset($tablename);

972

$this->assertTrue($rs instanceof moodle_recordset);

962

$this->assertInstanceOf('moodle_recordset', $rs);

973

963

reset($data);

974

964

$i = 0;

975

foreach($rs as $record) {

965

foreach ($rs as $record) {

976

966

$i++;

977

967

$rs->rewind();

978

968

if ($i > 10) {

987

977

}

988

978

$rs->close();

989

979

990

// test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)

980

// Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).

991

981

$conditions = array('onetext' => '1');

992

982

try {

993

983

$rs = $DB->get_recordset($tablename, $conditions);

994

984

$this->fail('An Exception is missing, expected due to equating of text fields');

995

} catch (exception $e) {

996

$this->assertTrue($e instanceof dml_exception);

997

$this->assertEquals($e->errorcode, 'textconditionsnotallowed');

985

} catch (moodle_exception $e) {

986

$this->assertInstanceOf('dml_exception', $e);

987

$this->assertSame('textconditionsnotallowed', $e->errorcode);

998

988

}

999

989

1000

990

// Test nested iteration.

1001

991

$rs1 = $DB->get_recordset($tablename);

1002

992

$i = 0;

1003

foreach($rs1 as $record1) {

993

foreach ($rs1 as $record1) {

1004

994

$rs2 = $DB->get_recordset($tablename);

1005

995

$i++;

1006

996

$j = 0;

1007

foreach($rs2 as $record2) {

997

foreach ($rs2 as $record2) {

1008

998

$j++;

1009

999

}

1010

1000

$rs2->close();

1011

$this->assertEquals($j, count($data));

1001

$this->assertCount($j, $data);

1012

1002

}

1013

1003

$rs1->close();

1014

$this->assertEquals($i, count($data));

1004

$this->assertCount($i, $data);

1015

1005

1016

// notes:

1006

// Notes:

1017

1007

// * limits are tested in test_get_recordset_sql()

1018

1008

// * where_clause() is used internally and is tested in test_get_records()

1019

1009

}

1025

1015

$table = $this->get_test_table();

1026

1016

$tablename = $table->getName();

1027

1017

1028

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1029

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

1018

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1019

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

1030

1020

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

1031

1021

$dbman->create_table($table);

1032

1022

1041

1031

$DB->delete_records($tablename, array('course'=>2));

1042

1032

1043

1033

$i = 0;

1044

foreach($rs as $record) {

1034

foreach ($rs as $record) {

1045

1035

$i++;

1046

1036

$this->assertEquals($i, $record->course);

1047

1037

}

1062

1052

$DB->delete_records($tablename, array('course'=>2));

1063

1053

1064

1054

$i = 0;

1065

foreach($rs as $record) {

1055

foreach ($rs as $record) {

1066

1056

$i++;

1067

1057

$this->assertEquals($i, $record->course);

1068

1058

}

1077

1067

$table = $this->get_test_table();

1078

1068

$tablename = $table->getName();

1079

1069

1080

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1081

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

1070

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1071

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

1082

1072

$table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');

1083

1073

$table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));

1084

1074

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

1087

1077

$data = array(array('course' => 3, 'name' => 'record1'),

1088

1078

array('course' => 3, 'name' => 'record2'),

1089

1079

array('course' => 5, 'name' => 'record3'));

1090

foreach ($data as $key=>$record) {

1080

foreach ($data as $key => $record) {

1091

1081

$data[$key]['id'] = $DB->insert_record($tablename, $record);

1092

1082

}

1093

1083

1094

// Test repeated numeric keys are returned ok

1095

$rs = $DB->get_recordset($tablename, NULL, NULL, 'course, name, id');

1084

// Test repeated numeric keys are returned ok.

1085

$rs = $DB->get_recordset($tablename, null, null, 'course, name, id');

1096

1086

1097

1087

reset($data);

1098

1088

$count = 0;

1099

foreach($rs as $key => $record) {

1089

foreach ($rs as $key => $record) {

1100

1090

$data_record = current($data);

1101

1091

$this->assertEquals($data_record['course'], $key);

1102

1092

next($data);

1103

1093

$count++;

1104

1094

}

1105

1095

$rs->close();

1106

$this->assertEquals($count, 3);

1096

$this->assertEquals(3, $count);

1107

1097

1108

// Test string keys are returned ok

1109

$rs = $DB->get_recordset($tablename, NULL, NULL, 'name, course, id');

1098

// Test string keys are returned ok.

1099

$rs = $DB->get_recordset($tablename, null, null, 'name, course, id');

1110

1100

1111

1101

reset($data);

1112

1102

$count = 0;

1113

foreach($rs as $key => $record) {

1103

foreach ($rs as $key => $record) {

1114

1104

$data_record = current($data);

1115

1105

$this->assertEquals($data_record['name'], $key);

1116

1106

next($data);

1117

1107

$count++;

1118

1108

}

1119

1109

$rs->close();

1120

$this->assertEquals($count, 3);

1110

$this->assertEquals(3, $count);

1121

1111

1122

// Test numeric not starting in 1 keys are returned ok

1123

$rs = $DB->get_recordset($tablename, NULL, 'id DESC', 'id, course, name');

1112

// Test numeric not starting in 1 keys are returned ok.

1113

$rs = $DB->get_recordset($tablename, null, 'id DESC', 'id, course, name');

1124

1114

1125

1115

$data = array_reverse($data);

1126

1116

reset($data);

1127

1117

$count = 0;

1128

foreach($rs as $key => $record) {

1118

foreach ($rs as $key => $record) {

1129

1119

$data_record = current($data);

1130

1120

$this->assertEquals($data_record['id'], $key);

1131

1121

next($data);

1132

1122

$count++;

1133

1123

}

1134

1124

$rs->close();

1135

$this->assertEquals($count, 3);

1125

$this->assertEquals(3, $count);

1136

1126

}

1137

1127

1138

1128

public function test_get_recordset_list() {

1142

1132

$table = $this->get_test_table();

1143

1133

$tablename = $table->getName();

1144

1134

1145

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1146

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null, '0');

1135

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1136

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, null, null, '0');

1147

1137

$table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));

1148

1138

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

1149

1139

$dbman->create_table($table);

1212

1202

$this->assertEquals(1, $counter);

1213

1203

$rs->close();

1214

1204

1215

$rs = $DB->get_recordset_list($tablename, 'course',array()); // Must return 0 rows without conditions. MDL-17645

1205

$rs = $DB->get_recordset_list($tablename, 'course', array()); // Must return 0 rows without conditions. MDL-17645.

1216

1206

1217

1207

$counter = 0;

1218

1208

foreach ($rs as $record) {

1221

1211

$rs->close();

1222

1212

$this->assertEquals(0, $counter);

1223

1213

1224

// notes:

1214

// Notes:

1225

1215

// * limits are tested in test_get_recordset_sql()

1226

1216

// * where_clause() is used internally and is tested in test_get_records()

1227

1217

}

1233

1223

$table = $this->get_test_table();

1234

1224

$tablename = $table->getName();

1235

1225

1236

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1237

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

1226

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1227

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

1238

1228

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

1239

1229

$dbman->create_table($table);

1240

1230

1259

1249

$rs->close();

1260

1250

$this->assertEquals(2, $counter);

1261

1251

1262

// notes:

1252

// Notes:

1263

1253

// * limits are tested in test_get_recordset_sql()

1264

1254

}

1265

1255

1270

1260

$table = $this->get_test_table();

1271

1261

$tablename = $table->getName();

1272

1262

1273

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1274

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

1263

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1264

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

1275

1265

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

1276

1266

$dbman->create_table($table);

1277

1267

1291

1281

$rs->close();

1292

1282

$this->assertEquals(2, $counter);

1293

1283

1294

// limits - only need to test this case, the rest have been tested by test_get_records_sql()

1295

// only limitfrom = skips that number of records

1284

// Limits - only need to test this case, the rest have been tested by test_get_records_sql()

1285

// only limitfrom = skips that number of records.

1296

1286

$rs = $DB->get_recordset_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 2, 0);

1297

1287

$records = array();

1298

foreach($rs as $key => $record) {

1288

foreach ($rs as $key => $record) {

1299

1289

$records[$key] = $record;

1300

1290

}

1301

1291

$rs->close();

1302

$this->assertEquals(5, count($records));

1292

$this->assertCount(5, $records);

1303

1293

$this->assertEquals($inskey3, reset($records)->id);

1304

1294

$this->assertEquals($inskey7, end($records)->id);

1305

1295

1306

// note: fetching nulls, empties, LOBs already tested by test_insert_record() no needed here

1296

// Note: fetching nulls, empties, LOBs already tested by test_insert_record() no needed here.

1307

1297

}

1308

1298

1309

1299

public function test_export_table_recordset() {

1343

1333

$table = $this->get_test_table();

1344

1334

$tablename = $table->getName();

1345

1335

1346

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1347

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

1336

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1337

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

1348

1338

$table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);

1349

1339

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

1350

1340

$dbman->create_table($table);

1354

1344

$DB->insert_record($tablename, array('course' => 5));

1355

1345

$DB->insert_record($tablename, array('course' => 2));

1356

1346

1357

// All records

1347

// All records.

1358

1348

$records = $DB->get_records($tablename);

1359

$this->assertEquals(4, count($records));

1349

$this->assertCount(4, $records);

1360

1350

$this->assertEquals(3, $records[1]->course);

1361

1351

$this->assertEquals(3, $records[2]->course);

1362

1352

$this->assertEquals(5, $records[3]->course);

1363

1353

$this->assertEquals(2, $records[4]->course);

1364

1354

1365

// Records matching certain conditions

1355

// Records matching certain conditions.

1366

1356

$records = $DB->get_records($tablename, array('course' => 3));

1367

$this->assertEquals(2, count($records));

1357

$this->assertCount(2, $records);

1368

1358

$this->assertEquals(3, $records[1]->course);

1369

1359

$this->assertEquals(3, $records[2]->course);

1370

1360

1371

// All records sorted by course

1361

// All records sorted by course.

1372

1362

$records = $DB->get_records($tablename, null, 'course');

1373

$this->assertEquals(4, count($records));

1363

$this->assertCount(4, $records);

1374

1364

$current_record = reset($records);

1375

1365

$this->assertEquals(4, $current_record->id);

1376

1366

$current_record = next($records);

1380

1370

$current_record = next($records);

1381

1371

$this->assertEquals(3, $current_record->id);

1382

1372

1383

// All records, but get only one field

1373

// All records, but get only one field.

1384

1374

$records = $DB->get_records($tablename, null, '', 'id');

1385

1375

$this->assertFalse(isset($records[1]->course));

1386

1376

$this->assertTrue(isset($records[1]->id));

1387

$this->assertEquals(4, count($records));

1377

$this->assertCount(4, $records);

1388

1378

1389

// Booleans into params

1379

// Booleans into params.

1390

1380

$records = $DB->get_records($tablename, array('course' => true));

1391

$this->assertEquals(0, count($records));

1381

$this->assertCount(0, $records);

1392

1382

$records = $DB->get_records($tablename, array('course' => false));

1393

$this->assertEquals(0, count($records));

1383

$this->assertCount(0, $records);

1394

1384

1395

// test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)

1385

// Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).

1396

1386

$conditions = array('onetext' => '1');

1397

1387

try {

1398

1388

$records = $DB->get_records($tablename, $conditions);

1399

1389

if (debugging()) {

1400

// only in debug mode - hopefully all devs test code in debug mode...

1390

// Only in debug mode - hopefully all devs test code in debug mode...

1401

1391

$this->fail('An Exception is missing, expected due to equating of text fields');

1402

1392

}

1403

} catch (exception $e) {

1404

$this->assertTrue($e instanceof dml_exception);

1405

$this->assertEquals($e->errorcode, 'textconditionsnotallowed');

1393

} catch (moodle_exception $e) {

1394

$this->assertInstanceOf('dml_exception', $e);

1395

$this->assertSame('textconditionsnotallowed', $e->errorcode);

1406

1396

}

1407

1397

1408

// test get_records passing non-existing table

1409

// with params

1398

// Test get_records passing non-existing table.

1399

// with params.

1410

1400

try {

1411

1401

$records = $DB->get_records('xxxx', array('id' => 0));

1412

1402

$this->fail('An Exception is missing, expected due to query against non-existing table');

1413

} catch (exception $e) {

1414

$this->assertTrue($e instanceof dml_exception);

1403

} catch (moodle_exception $e) {

1404

$this->assertInstanceOf('dml_exception', $e);

1415

1405

if (debugging()) {

1416

// information for developers only, normal users get general error message

1417

$this->assertEquals($e->errorcode, 'ddltablenotexist');

1406

// Information for developers only, normal users get general error message.

1407

$this->assertSame('ddltablenotexist', $e->errorcode);

1418

1408

}

1419

1409

}

1420

// and without params

1410

// And without params.

1421

1411

try {

1422

1412

$records = $DB->get_records('xxxx', array());

1423

1413

$this->fail('An Exception is missing, expected due to query against non-existing table');

1424

} catch (exception $e) {

1425

$this->assertTrue($e instanceof dml_exception);

1414

} catch (moodle_exception $e) {

1415

$this->assertInstanceOf('dml_exception', $e);

1426

1416

if (debugging()) {

1427

// information for developers only, normal users get general error message

1428

$this->assertEquals($e->errorcode, 'ddltablenotexist');

1417

// Information for developers only, normal users get general error message.

1418

$this->assertSame('ddltablenotexist', $e->errorcode);

1429

1419

}

1430

1420

}

1431

1421

1432

// test get_records passing non-existing column

1422

// Test get_records passing non-existing column.

1433

1423

try {

1434

1424

$records = $DB->get_records($tablename, array('xxxx' => 0));

1435

1425

$this->fail('An Exception is missing, expected due to query against non-existing column');

1436

} catch (exception $e) {

1437

$this->assertTrue($e instanceof dml_exception);

1426

} catch (moodle_exception $e) {

1427

$this->assertInstanceOf('dml_exception', $e);

1438

1428

if (debugging()) {

1439

// information for developers only, normal users get general error message

1440

$this->assertEquals($e->errorcode, 'ddlfieldnotexist');

1429

// Information for developers only, normal users get general error message.

1430

$this->assertSame('ddlfieldnotexist', $e->errorcode);

1441

1431

}

1442

1432

}

1443

1433

1444

// note: delegate limits testing to test_get_records_sql()

1434

// Note: delegate limits testing to test_get_records_sql().

1445

1435

}

1446

1436

1447

1437

public function test_get_records_list() {

1451

1441

$table = $this->get_test_table();

1452

1442

$tablename = $table->getName();

1453

1443

1454

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1455

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

1444

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1445

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

1456

1446

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

1457

1447

$dbman->create_table($table);

1458

1448

1462

1452

$DB->insert_record($tablename, array('course' => 2));

1463

1453

1464

1454

$records = $DB->get_records_list($tablename, 'course', array(3, 2));

1465

$this->assertTrue(is_array($records));

1466

$this->assertEquals(3, count($records));

1455

$this->assertInternalType('array', $records);

1456

$this->assertCount(3, $records);

1467

1457

$this->assertEquals(1, reset($records)->id);

1468

1458

$this->assertEquals(2, next($records)->id);

1469

1459

$this->assertEquals(4, next($records)->id);

1470

1460

1471

$this->assertSame(array(), $records = $DB->get_records_list($tablename, 'course', array())); // Must return 0 rows without conditions. MDL-17645

1472

$this->assertEquals(0, count($records));

1461

$this->assertSame(array(), $records = $DB->get_records_list($tablename, 'course', array())); // Must return 0 rows without conditions. MDL-17645.

1462

$this->assertCount(0, $records);

1473

1463

1474

// note: delegate limits testing to test_get_records_sql()

1464

// Note: delegate limits testing to test_get_records_sql().

1475

1465

}

1476

1466

1477

1467

public function test_get_records_sql() {

1478

global $CFG;

1479

1480

1468

$DB = $this->tdb;

1481

1469

$dbman = $DB->get_manager();

1482

1470

1483

1471

$table = $this->get_test_table();

1484

1472

$tablename = $table->getName();

1485

1473

1486

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1487

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

1474

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1475

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

1488

1476

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

1489

1477

$dbman->create_table($table);

1490

1478

1498

1486

1499

1487

$table2 = $this->get_test_table("2");

1500

1488

$tablename2 = $table2->getName();

1501

$table2->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1502

$table2->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

1489

$table2->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1490

$table2->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

1503

1491

$table2->add_field('nametext', XMLDB_TYPE_TEXT, 'small', null, null, null, null);

1504

1492

$table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

1505

1493

$dbman->create_table($table2);

1510

1498

$DB->insert_record($tablename2, array('course'=>6, 'nametext'=>'badabong'));

1511

1499

1512

1500

$records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3));

1513

$this->assertEquals(2, count($records));

1501

$this->assertCount(2, $records);

1514

1502

$this->assertEquals($inskey1, reset($records)->id);

1515

1503

$this->assertEquals($inskey4, next($records)->id);

1516

1504

1517

// Awful test, requires debug enabled and sent to browser. Let's do that and restore after test

1505

// Awful test, requires debug enabled and sent to browser. Let's do that and restore after test.

1518

1506

$records = $DB->get_records_sql("SELECT course AS id, course AS course FROM {{$tablename}}", null);

1519

1507

$this->assertDebuggingCalled();

1520

$this->assertEquals(6, count($records));

1521

$CFG->debug = DEBUG_MINIMAL;

1508

$this->assertCount(6, $records);

1509

set_debugging(DEBUG_MINIMAL);

1522

1510

$records = $DB->get_records_sql("SELECT course AS id, course AS course FROM {{$tablename}}", null);

1523

1511

$this->assertDebuggingNotCalled();

1524

$this->assertEquals(6, count($records));

1525

$CFG->debug = DEBUG_DEVELOPER;

1512

$this->assertCount(6, $records);

1513

set_debugging(DEBUG_DEVELOPER);

1526

1514

1527

// negative limits = no limits

1515

// Negative limits = no limits.

1528

1516

$records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, -1, -1);

1529

$this->assertEquals(7, count($records));

1517

$this->assertCount(7, $records);

1530

1518

1531

// zero limits = no limits

1519

// Zero limits = no limits.

1532

1520

$records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 0, 0);

1533

$this->assertEquals(7, count($records));

1521

$this->assertCount(7, $records);

1534

1522

1535

// only limitfrom = skips that number of records

1523

// Only limitfrom = skips that number of records.

1536

1524

$records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 2, 0);

1537

$this->assertEquals(5, count($records));

1525

$this->assertCount(5, $records);

1538

1526

$this->assertEquals($inskey3, reset($records)->id);

1539

1527

$this->assertEquals($inskey7, end($records)->id);

1540

1528

1541

// only limitnum = fetches that number of records

1529

// Only limitnum = fetches that number of records.

1542

1530

$records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 0, 3);

1543

$this->assertEquals(3, count($records));

1531

$this->assertCount(3, $records);

1544

1532

$this->assertEquals($inskey1, reset($records)->id);

1545

1533

$this->assertEquals($inskey3, end($records)->id);

1546

1534

1547

// both limitfrom and limitnum = skips limitfrom records and fetches limitnum ones

1535

// Both limitfrom and limitnum = skips limitfrom records and fetches limitnum ones.

1548

1536

$records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 3, 2);

1549

$this->assertEquals(2, count($records));

1537

$this->assertCount(2, $records);

1550

1538

$this->assertEquals($inskey4, reset($records)->id);

1551

1539

$this->assertEquals($inskey5, end($records)->id);

1552

1540

1553

// both limitfrom and limitnum in query having subqueris

1554

// note the subquery skips records with course = 0 and 3

1541

// Both limitfrom and limitnum in query having subqueris.

1542

// Note the subquery skips records with course = 0 and 3.

1555

1543

$sql = "SELECT * FROM {{$tablename}}

1556

1544

WHERE course NOT IN (

1557

1545

SELECT course FROM {{$tablename}}

1558

1546

WHERE course IN (0, 3))

1559

1547

ORDER BY course";

1560

$records = $DB->get_records_sql($sql, null, 0, 2); // Skip 0, get 2

1561

$this->assertEquals(2, count($records));

1548

$records = $DB->get_records_sql($sql, null, 0, 2); // Skip 0, get 2.

1549

$this->assertCount(2, $records);

1562

1550

$this->assertEquals($inskey6, reset($records)->id);

1563

1551

$this->assertEquals($inskey5, end($records)->id);

1564

$records = $DB->get_records_sql($sql, null, 2, 2); // Skip 2, get 2

1565

$this->assertEquals(2, count($records));

1552

$records = $DB->get_records_sql($sql, null, 2, 2); // Skip 2, get 2.

1553

$this->assertCount(2, $records);

1566

1554

$this->assertEquals($inskey3, reset($records)->id);

1567

1555

$this->assertEquals($inskey2, end($records)->id);

1568

1556

1569

// test 2 tables with aliases and limits with order bys

1557

// Test 2 tables with aliases and limits with order bys.

1570

1558

$sql = "SELECT t1.id, t1.course AS cid, t2.nametext

1571

1559

FROM {{$tablename}} t1, {{$tablename2}} t2

1572

1560

WHERE t2.course=t1.course

1573

1561

ORDER BY t1.course, ". $DB->sql_compare_text('t2.nametext');

1574

$records = $DB->get_records_sql($sql, null, 2, 2); // Skip courses 3 and 6, get 4 and 5

1575

$this->assertEquals(2, count($records));

1576

$this->assertEquals('5', end($records)->cid);

1577

$this->assertEquals('4', reset($records)->cid);

1578

1579

// test 2 tables with aliases and limits with the highest INT limit works

1580

$records = $DB->get_records_sql($sql, null, 2, PHP_INT_MAX); // Skip course {3,6}, get {4,5}

1581

$this->assertEquals(2, count($records));

1582

$this->assertEquals('5', end($records)->cid);

1583

$this->assertEquals('4', reset($records)->cid);

1584

1585

// test 2 tables with aliases and limits with order bys (limit which is highest INT number)

1586

$records = $DB->get_records_sql($sql, null, PHP_INT_MAX, 2); // Skip all courses

1587

$this->assertEquals(0, count($records));

1588

1589

// test 2 tables with aliases and limits with order bys (limit which s highest INT number)

1590

$records = $DB->get_records_sql($sql, null, PHP_INT_MAX, PHP_INT_MAX); // Skip all courses

1591

$this->assertEquals(0, count($records));

1592

1593

// TODO: Test limits in queries having DISTINCT clauses

1594

1595

// note: fetching nulls, empties, LOBs already tested by test_update_record() no needed here

1562

$records = $DB->get_records_sql($sql, null, 2, 2); // Skip courses 3 and 6, get 4 and 5.

1563

$this->assertCount(2, $records);

1564

$this->assertSame('5', end($records)->cid);

1565

$this->assertSame('4', reset($records)->cid);

1566

1567

// Test 2 tables with aliases and limits with the highest INT limit works.

1568

$records = $DB->get_records_sql($sql, null, 2, PHP_INT_MAX); // Skip course {3,6}, get {4,5}.

1569

$this->assertCount(2, $records);

1570

$this->assertSame('5', end($records)->cid);

1571

$this->assertSame('4', reset($records)->cid);

1572

1573

// Test 2 tables with aliases and limits with order bys (limit which is highest INT number).

1574

$records = $DB->get_records_sql($sql, null, PHP_INT_MAX, 2); // Skip all courses.

1575

$this->assertCount(0, $records);

1576

1577

// Test 2 tables with aliases and limits with order bys (limit which s highest INT number).

1578

$records = $DB->get_records_sql($sql, null, PHP_INT_MAX, PHP_INT_MAX); // Skip all courses.

1579

$this->assertCount(0, $records);

1580

1581

// TODO: Test limits in queries having DISTINCT clauses.

1582

1583

// Note: fetching nulls, empties, LOBs already tested by test_update_record() no needed here.

1596

1584

}

1597

1585

1598

1586

public function test_get_records_menu() {

1602

1590

$table = $this->get_test_table();

1603

1591

$tablename = $table->getName();

1604

1592

1605

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1606

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

1593

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1594

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

1607

1595

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

1608

1596

$dbman->create_table($table);

1609

1597

1613

1601

$DB->insert_record($tablename, array('course' => 2));

1614

1602

1615

1603

$records = $DB->get_records_menu($tablename, array('course' => 3));

1616

$this->assertTrue(is_array($records));

1617

$this->assertEquals(2, count($records));

1618

$this->assertFalse(empty($records[1]));

1619

$this->assertFalse(empty($records[2]));

1604

$this->assertInternalType('array', $records);

1605

$this->assertCount(2, $records);

1606

$this->assertNotEmpty($records[1]);

1607

$this->assertNotEmpty($records[2]);

1620

1608

$this->assertEquals(3, $records[1]);

1621

1609

$this->assertEquals(3, $records[2]);

1622

1610

1623

// note: delegate limits testing to test_get_records_sql()

1611

// Note: delegate limits testing to test_get_records_sql().

1624

1612

}

1625

1613

1626

1614

public function test_get_records_select_menu() {

1630

1618

$table = $this->get_test_table();

1631

1619

$tablename = $table->getName();

1632

1620

1633

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1634

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

1621

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1622

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

1635

1623

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

1636

1624

$dbman->create_table($table);

1637

1625

1641

1629

$DB->insert_record($tablename, array('course' => 5));

1642

1630

1643

1631

$records = $DB->get_records_select_menu($tablename, "course > ?", array(2));

1644

$this->assertTrue(is_array($records));

1645

1646

$this->assertEquals(3, count($records));

1647

$this->assertFalse(empty($records[1]));

1648

$this->assertTrue(empty($records[2]));

1649

$this->assertFalse(empty($records[3]));

1650

$this->assertFalse(empty($records[4]));

1651

$this->assertEquals(3, $records[1]);

1652

$this->assertEquals(3, $records[3]);

1653

$this->assertEquals(5, $records[4]);

1654

1655

// note: delegate limits testing to test_get_records_sql()

1632

$this->assertInternalType('array', $records);

1633

1634

$this->assertCount(3, $records);

1635

$this->assertArrayHasKey(1, $records);

1636

$this->assertArrayNotHasKey(2, $records);

1637

$this->assertArrayHasKey(3, $records);

1638

$this->assertArrayHasKey(4, $records);

1639

$this->assertSame('3', $records[1]);

1640

$this->assertSame('3', $records[3]);

1641

$this->assertSame('5', $records[4]);

1642

1643

// Note: delegate limits testing to test_get_records_sql().

1656

1644

}

1657

1645

1658

1646

public function test_get_records_sql_menu() {

1662

1650

$table = $this->get_test_table();

1663

1651

$tablename = $table->getName();

1664

1652

1665

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1666

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

1653

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1654

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

1667

1655

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

1668

1656

$dbman->create_table($table);

1669

1657

1673

1661

$DB->insert_record($tablename, array('course' => 5));

1674

1662

1675

1663

$records = $DB->get_records_sql_menu("SELECT * FROM {{$tablename}} WHERE course > ?", array(2));

1676

$this->assertTrue(is_array($records));

1677

1678

$this->assertEquals(3, count($records));

1679

$this->assertFalse(empty($records[1]));

1680

$this->assertTrue(empty($records[2]));

1681

$this->assertFalse(empty($records[3]));

1682

$this->assertFalse(empty($records[4]));

1683

$this->assertEquals(3, $records[1]);

1684

$this->assertEquals(3, $records[3]);

1685

$this->assertEquals(5, $records[4]);

1686

1687

// note: delegate limits testing to test_get_records_sql()

1664

$this->assertInternalType('array', $records);

1665

1666

$this->assertCount(3, $records);

1667

$this->assertArrayHasKey(1, $records);

1668

$this->assertArrayNotHasKey(2, $records);

1669

$this->assertArrayHasKey(3, $records);

1670

$this->assertArrayHasKey(4, $records);

1671

$this->assertSame('3', $records[1]);

1672

$this->assertSame('3', $records[3]);

1673

$this->assertSame('5', $records[4]);

1674

1675

// Note: delegate limits testing to test_get_records_sql().

1688

1676

}

1689

1677

1690

1678

public function test_get_record() {

1694

1682

$table = $this->get_test_table();

1695

1683

$tablename = $table->getName();

1696

1684

1697

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1698

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

1685

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1686

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

1699

1687

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

1700

1688

$dbman->create_table($table);

1701

1689

1703

1691

$DB->insert_record($tablename, array('course' => 2));

1704

1692

1705

1693

$record = $DB->get_record($tablename, array('id' => 2));

1706

$this->assertTrue($record instanceof stdClass);

1694

$this->assertInstanceOf('stdClass', $record);

1707

1695

1708

1696

$this->assertEquals(2, $record->course);

1709

1697

$this->assertEquals(2, $record->id);

1717

1705

$table = $this->get_test_table();

1718

1706

$tablename = $table->getName();

1719

1707

1720

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1721

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

1708

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1709

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

1722

1710

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

1723

1711

$dbman->create_table($table);

1724

1712

1726

1714

$DB->insert_record($tablename, array('course' => 2));

1727

1715

1728

1716

$record = $DB->get_record_select($tablename, "id = ?", array(2));

1729

$this->assertTrue($record instanceof stdClass);

1717

$this->assertInstanceOf('stdClass', $record);

1730

1718

1731

1719

$this->assertEquals(2, $record->course);

1732

1720

1733

// note: delegates limit testing to test_get_records_sql()

1721

// Note: delegates limit testing to test_get_records_sql().

1734

1722

}

1735

1723

1736

1724

public function test_get_record_sql() {

1737

global $CFG;

1738

1739

1725

$DB = $this->tdb;

1740

1726

$dbman = $DB->get_manager();

1741

1727

1742

1728

$table = $this->get_test_table();

1743

1729

$tablename = $table->getName();

1744

1730

1745

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1746

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

1731

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1732

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

1747

1733

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

1748

1734

$dbman->create_table($table);

1749

1735

1750

1736

$DB->insert_record($tablename, array('course' => 3));

1751

1737

$DB->insert_record($tablename, array('course' => 2));

1752

1738

1753

// standard use

1739

// Standard use.

1754

1740

$record = $DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(2));

1755

$this->assertTrue($record instanceof stdClass);

1741

$this->assertInstanceOf('stdClass', $record);

1756

1742

$this->assertEquals(2, $record->course);

1757

1743

$this->assertEquals(2, $record->id);

1758

1744

1759

// backwards compatibility with $ignoremultiple

1745

// Backwards compatibility with $ignoremultiple.

1760

1746

$this->assertFalse((bool)IGNORE_MISSING);

1761

1747

$this->assertTrue((bool)IGNORE_MULTIPLE);

1762

1748

1763

// record not found - ignore

1749

// Record not found - ignore.

1764

1750

$this->assertFalse($DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), IGNORE_MISSING));

1765

1751

$this->assertFalse($DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), IGNORE_MULTIPLE));

1766

1752

1767

// record not found error

1753

// Record not found error.

1768

1754

try {

1769

1755

$DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), MUST_EXIST);

1770

1756

$this->fail("Exception expected");

1774

1760

1775

1761

$this->assertNotEmpty($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MISSING));

1776

1762

$this->assertDebuggingCalled();

1777

$CFG->debug = DEBUG_MINIMAL;

1763

set_debugging(DEBUG_MINIMAL);

1778

1764

$this->assertNotEmpty($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MISSING));

1779

1765

$this->assertDebuggingNotCalled();

1780

$CFG->debug = DEBUG_DEVELOPER;

1766

set_debugging(DEBUG_DEVELOPER);

1781

1767

1782

// multiple matches ignored

1768

// Multiple matches ignored.

1783

1769

$this->assertNotEmpty($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MULTIPLE));

1784

1770

1785

// multiple found error

1771

// Multiple found error.

1786

1772

try {

1787

1773

$DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), MUST_EXIST);

1788

1774

$this->fail("Exception expected");

1798

1784

$table = $this->get_test_table();

1799

1785

$tablename = $table->getName();

1800

1786

1801

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1802

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

1787

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1788

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

1803

1789

$table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);

1804

1790

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

1805

1791

$dbman->create_table($table);

1811

1797

$this->assertEquals(3, $DB->get_field($tablename, 'course', array('id' => $id1)));

1812

1798

$this->assertEquals(3, $DB->get_field($tablename, 'course', array('course' => 3)));

1813

1799

1814

$this->assertSame(false, $DB->get_field($tablename, 'course', array('course' => 11), IGNORE_MISSING));

1800

$this->assertFalse($DB->get_field($tablename, 'course', array('course' => 11), IGNORE_MISSING));

1815

1801

try {

1816

1802

$DB->get_field($tablename, 'course', array('course' => 4), MUST_EXIST);

1817

1803

$this->fail('Exception expected due to missing record');

1825

1811

$this->assertEquals(5, $DB->get_field($tablename, 'course', array('course' => 5), IGNORE_MISSING));

1826

1812

$this->assertDebuggingCalled();

1827

1813

1828

// test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)

1814

// Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).

1829

1815

$conditions = array('onetext' => '1');

1830

1816

try {

1831

1817

$DB->get_field($tablename, 'course', $conditions);

1832

1818

if (debugging()) {

1833

// only in debug mode - hopefully all devs test code in debug mode...

1819

// Only in debug mode - hopefully all devs test code in debug mode...

1834

1820

$this->fail('An Exception is missing, expected due to equating of text fields');

1835

1821

}

1836

} catch (exception $e) {

1837

$this->assertTrue($e instanceof dml_exception);

1838

$this->assertEquals($e->errorcode, 'textconditionsnotallowed');

1822

} catch (moodle_exception $e) {

1823

$this->assertInstanceOf('dml_exception', $e);

1824

$this->assertSame('textconditionsnotallowed', $e->errorcode);

1839

1825

}

1840

1826

}

1841

1827

1846

1832

$table = $this->get_test_table();

1847

1833

$tablename = $table->getName();

1848

1834

1849

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1850

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

1835

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1836

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

1851

1837

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

1852

1838

$dbman->create_table($table);

1853

1839

1863

1849

$table = $this->get_test_table();

1864

1850

$tablename = $table->getName();

1865

1851

1866

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1867

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

1852

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1853

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

1868

1854

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

1869

1855

$dbman->create_table($table);

1870

1856

1880

1866

$table = $this->get_test_table();

1881

1867

$tablename = $table->getName();

1882

1868

1883

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1884

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

1869

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1870

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

1885

1871

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

1886

1872

$dbman->create_table($table);

1887

1873

1891

1877

$DB->insert_record($tablename, array('course' => 6));

1892

1878

1893

1879

$fieldset = $DB->get_fieldset_select($tablename, 'course', "course > ?", array(1));

1894

$this->assertTrue(is_array($fieldset));

1880

$this->assertInternalType('array', $fieldset);

1895

1881

1896

$this->assertEquals(3, count($fieldset));

1882

$this->assertCount(3, $fieldset);

1897

1883

$this->assertEquals(3, $fieldset[0]);

1898

1884

$this->assertEquals(2, $fieldset[1]);

1899

1885

$this->assertEquals(6, $fieldset[2]);

1906

1892

$table = $this->get_test_table();

1907

1893

$tablename = $table->getName();

1908

1894

1909

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1910

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

1895

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1896

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

1911

1897

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

1912

1898

$dbman->create_table($table);

1913

1899

1917

1903

$DB->insert_record($tablename, array('course' => 6));

1918

1904

1919

1905

$fieldset = $DB->get_fieldset_sql("SELECT * FROM {{$tablename}} WHERE course > ?", array(1));

1920

$this->assertTrue(is_array($fieldset));

1906

$this->assertInternalType('array', $fieldset);

1921

1907

1922

$this->assertEquals(3, count($fieldset));

1908

$this->assertCount(3, $fieldset);

1923

1909

$this->assertEquals(2, $fieldset[0]);

1924

1910

$this->assertEquals(3, $fieldset[1]);

1925

1911

$this->assertEquals(4, $fieldset[2]);

1932

1918

$table = $this->get_test_table();

1933

1919

$tablename = $table->getName();

1934

1920

1935

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1936

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

1921

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1922

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

1937

1923

$table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');

1938

1924

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

1939

1925

$dbman->create_table($table);

1945

1931

$this->assertEquals($record, $before);

1946

1932

1947

1933

$record = $DB->get_record($tablename, array('course' => 1));

1948

$this->assertTrue($record instanceof stdClass);

1934

$this->assertInstanceOf('stdClass', $record);

1949

1935

$this->assertSame('xx', $record->onechar);

1950

1936

1951

1937

$result = $DB->insert_record_raw($tablename, array('course' => 2, 'onechar' => 'yy'), false);

1952

$this->assertSame(true, $result);

1938

$this->assertTrue($result);

1953

1939

1954

// note: bulk not implemented yet

1940

// Note: bulk not implemented yet.

1955

1941

$DB->insert_record_raw($tablename, array('course' => 3, 'onechar' => 'zz'), true, true);

1956

1942

$record = $DB->get_record($tablename, array('course' => 3));

1957

$this->assertTrue($record instanceof stdClass);

1943

$this->assertInstanceOf('stdClass', $record);

1958

1944

$this->assertSame('zz', $record->onechar);

1959

1945

1960

// custom sequence (id) - returnid is ignored

1946

// Custom sequence (id) - returnid is ignored.

1961

1947

$result = $DB->insert_record_raw($tablename, array('id' => 10, 'course' => 3, 'onechar' => 'bb'), true, false, true);

1962

$this->assertSame(true, $result);

1948

$this->assertTrue($result);

1963

1949

$record = $DB->get_record($tablename, array('id' => 10));

1964

$this->assertTrue($record instanceof stdClass);

1950

$this->assertInstanceOf('stdClass', $record);

1965

1951

$this->assertSame('bb', $record->onechar);

1966

1952

1967

// custom sequence - missing id error

1953

// Custom sequence - missing id error.

1968

1954

try {

1969

1955

$DB->insert_record_raw($tablename, array('course' => 3, 'onechar' => 'bb'), true, false, true);

1970

1956

$this->fail('Exception expected due to missing record');

1972

1958

$this->assertTrue(true);

1973

1959

}

1974

1960

1975

// wrong column error

1961

// Wrong column error.

1976

1962

try {

1977

1963

$DB->insert_record_raw($tablename, array('xxxxx' => 3, 'onechar' => 'bb'));

1978

1964

$this->fail('Exception expected due to invalid column');

1980

1966

$this->assertTrue(true);

1981

1967

}

1982

1968

1983

// create something similar to "context_temp" with id column without sequence

1969

// Create something similar to "context_temp" with id column without sequence.

1984

1970

$dbman->drop_table($table);

1985

1971

$table = $this->get_test_table();

1986

1972

$tablename = $table->getName();

1987

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null);

1988

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

1973

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);

1974

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

1989

1975

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

1990

1976

$dbman->create_table($table);

1991

1977

2005

1991

$table = $this->get_test_table();

2006

1992

$tablename = $table->getName();

2007

1993

2008

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

2009

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

2010

$table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null, 100);

1994

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

1995

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

1996

$table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null, 100);

2011

1997

$table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);

2012

1998

$table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');

2013

1999

$table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);

2018

2004

$this->assertSame(1, $DB->insert_record($tablename, array('course' => 1), true));

2019

2005

$record = $DB->get_record($tablename, array('course' => 1));

2020

2006

$this->assertEquals(1, $record->id);

2021

$this->assertEquals(100, $record->oneint); // Just check column defaults have been applied

2007

$this->assertEquals(100, $record->oneint); // Just check column defaults have been applied.

2022

2008

$this->assertEquals(200, $record->onenum);

2023

2009

$this->assertSame('onestring', $record->onechar);

2024

2010

$this->assertNull($record->onetext);

2025

2011

$this->assertNull($record->onebinary);

2026

2012

2027

// without returning id, bulk not implemented

2028

$result = $this->assertSame(true, $DB->insert_record($tablename, array('course' => 99), false, true));

2013

// Without returning id, bulk not implemented.

2014

$result = $this->assertTrue($DB->insert_record($tablename, array('course' => 99), false, true));

2029

2015

$record = $DB->get_record($tablename, array('course' => 99));

2030

2016

$this->assertEquals(2, $record->id);

2031

2017

$this->assertEquals(99, $record->course);

2032

2018

2033

// Check nulls are set properly for all types

2019

// Check nulls are set properly for all types.

2034

2020

$record = new stdClass();

2035

2021

$record->oneint = null;

2036

2022

$record->onenum = null;

2046

2032

$this->assertNull($record->onetext);

2047

2033

$this->assertNull($record->onebinary);

2048

2034

2049

// Check zeros are set properly for all types

2035

// Check zeros are set properly for all types.

2050

2036

$record = new stdClass();

2051

2037

$record->oneint = 0;

2052

2038

$record->onenum = 0;

2055

2041

$this->assertEquals(0, $record->oneint);

2056

2042

$this->assertEquals(0, $record->onenum);

2057

2043

2058

// Check booleans are set properly for all types

2044

// Check booleans are set properly for all types.

2059

2045

$record = new stdClass();

2060

$record->oneint = true; // trues

2046

$record->oneint = true; // Trues.

2061

2047

$record->onenum = true;

2062

2048

$record->onechar = true;

2063

2049

$record->onetext = true;

2069

2055

$this->assertEquals(1, $record->onetext);

2070

2056

2071

2057

$record = new stdClass();

2072

$record->oneint = false; // falses

2058

$record->oneint = false; // Falses.

2073

2059

$record->onenum = false;

2074

2060

$record->onechar = false;

2075

2061

$record->onetext = false;

2080

2066

$this->assertEquals(0, $record->onechar);

2081

2067

$this->assertEquals(0, $record->onetext);

2082

2068

2083

// Check string data causes exception in numeric types

2069

// Check string data causes exception in numeric types.

2084

2070

$record = new stdClass();

2085

2071

$record->oneint = 'onestring';

2086

2072

$record->onenum = 0;

2087

2073

try {

2088

2074

$DB->insert_record($tablename, $record);

2089

2075

$this->fail("Expecting an exception, none occurred");

2090

} catch (exception $e) {

2091

$this->assertTrue($e instanceof dml_exception);

2076

} catch (moodle_exception $e) {

2077

$this->assertInstanceOf('dml_exception', $e);

2092

2078

}

2093

2079

$record = new stdClass();

2094

2080

$record->oneint = 0;

2096

2082

try {

2097

2083

$DB->insert_record($tablename, $record);

2098

2084

$this->fail("Expecting an exception, none occurred");

2099

} catch (exception $e) {

2100

$this->assertTrue($e instanceof dml_exception);

2085

} catch (moodle_exception $e) {

2086

$this->assertInstanceOf('dml_exception', $e);

2101

2087

}

2102

2088

2103

// Check empty string data is stored as 0 in numeric datatypes

2089

// Check empty string data is stored as 0 in numeric datatypes.

2104

2090

$record = new stdClass();

2105

$record->oneint = ''; // empty string

2091

$record->oneint = ''; // Empty string.

2106

2092

$record->onenum = 0;

2107

2093

$recid = $DB->insert_record($tablename, $record);

2108

2094

$record = $DB->get_record($tablename, array('id' => $recid));

2110

2096

2111

2097

$record = new stdClass();

2112

2098

$record->oneint = 0;

2113

$record->onenum = ''; // empty string

2099

$record->onenum = ''; // Empty string.

2114

2100

$recid = $DB->insert_record($tablename, $record);

2115

2101

$record = $DB->get_record($tablename, array('id' => $recid));

2116

2102

$this->assertTrue(is_numeric($record->onenum) && $record->onenum == 0);

2117

2103

2118

// Check empty strings are set properly in string types

2104

// Check empty strings are set properly in string types.

2119

2105

$record = new stdClass();

2120

2106

$record->oneint = 0;

2121

2107

$record->onenum = 0;

2126

2112

$this->assertTrue($record->onechar === '');

2127

2113

$this->assertTrue($record->onetext === '');

2128

2114

2129

// Check operation ((210.10 + 39.92) - 150.02) against numeric types

2115

// Check operation ((210.10 + 39.92) - 150.02) against numeric types.

2130

2116

$record = new stdClass();

2131

2117

$record->oneint = ((210.10 + 39.92) - 150.02);

2132

2118

$record->onenum = ((210.10 + 39.92) - 150.02);

2135

2121

$this->assertEquals(100, $record->oneint);

2136

2122

$this->assertEquals(100, $record->onenum);

2137

2123

2138

// Check various quotes/backslashes combinations in string types

2124

// Check various quotes/backslashes combinations in string types.

2139

2125

$teststrings = array(

2140

2126

'backslashes and quotes alone (even): "" \'\' \\\\',

2141

2127

'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',

2151

2137

$this->assertEquals($teststring, $record->onetext);

2152

2138

}

2153

2139

2154

// Check LOBs in text/binary columns

2140

// Check LOBs in text/binary columns.

2155

2141

$clob = file_get_contents(__DIR__ . '/fixtures/clob.txt');

2156

2142

$blob = file_get_contents(__DIR__ . '/fixtures/randombinary');

2157

2143

$record = new stdClass();

2164

2150

$this->assertEquals($clob, $record->onetext, 'Test CLOB insert (full contents output disabled)');

2165

2151

$this->assertEquals($blob, $record->onebinary, 'Test BLOB insert (full contents output disabled)');

2166

2152

2167

// And "small" LOBs too, just in case

2153

// And "small" LOBs too, just in case.

2168

2154

$newclob = substr($clob, 0, 500);

2169

2155

$newblob = substr($blob, 0, 250);

2170

2156

$record = new stdClass();

2176

2162

$rs->close();

2177

2163

$this->assertEquals($newclob, $record->onetext, 'Test "small" CLOB insert (full contents output disabled)');

2178

2164

$this->assertEquals($newblob, $record->onebinary, 'Test "small" BLOB insert (full contents output disabled)');

2179

$this->assertEquals(false, $rs->key()); // Ensure recordset key() method to be working ok after closing

2165

$this->assertEquals(false, $rs->key()); // Ensure recordset key() method to be working ok after closing.

2180

2166

2181

// And "diagnostic" LOBs too, just in case

2167

// And "diagnostic" LOBs too, just in case.

2182

2168

$newclob = '\'"\\;/ěščřžýáíé';

2183

2169

$newblob = '\'"\\;/ěščřžýáíé';

2184

2170

$record = new stdClass();

2190

2176

$rs->close();

2191

2177

$this->assertSame($newclob, $record->onetext);

2192

2178

$this->assertSame($newblob, $record->onebinary);

2193

$this->assertEquals(false, $rs->key()); // Ensure recordset key() method to be working ok after closing

2179

$this->assertEquals(false, $rs->key()); // Ensure recordset key() method to be working ok after closing.

2194

2180

2195

// test data is not modified

2181

// Test data is not modified.

2196

2182

$record = new stdClass();

2197

$record->id = -1; // has to be ignored

2183

$record->id = -1; // Has to be ignored.

2198

2184

$record->course = 3;

2199

$record->lalala = 'lalal'; // unused

2185

$record->lalala = 'lalal'; // Unused.

2200

2186

$before = clone($record);

2201

2187

$DB->insert_record($tablename, $record);

2202

2188

$this->assertEquals($record, $before);

2203

2189

2204

// make sure the id is always increasing and never reuses the same id

2190

// Make sure the id is always increasing and never reuses the same id.

2205

2191

$id1 = $DB->insert_record($tablename, array('course' => 3));

2206

2192

$id2 = $DB->insert_record($tablename, array('course' => 3));

2207

2193

$this->assertTrue($id1 < $id2);

2250

2236

2251

2237

// Let's insert one record violating the constraint multiple times.

2252

2238

$record = (object)array('course' => 1, 'oneint' => 1);

2253

$this->assertTrue($DB->insert_record($tablename, $record, false)); // insert 1st. No problem expected.

2239

$this->assertTrue($DB->insert_record($tablename, $record, false)); // Insert 1st. No problem expected.

2254

2240

2255

2241

// Re-insert same record, not returning id. dml_exception expected.

2256

2242

try {

2257

2243

$DB->insert_record($tablename, $record, false);

2258

2244

$this->fail("Expecting an exception, none occurred");

2259

} catch (exception $e) {

2260

$this->assertTrue($e instanceof dml_exception);

2245

} catch (moodle_exception $e) {

2246

$this->assertInstanceOf('dml_exception', $e);

2261

2247

}

2262

2248

2263

2249

// Re-insert same record, returning id. dml_exception expected.

2264

2250

try {

2265

2251

$DB->insert_record($tablename, $record, true);

2266

2252

$this->fail("Expecting an exception, none occurred");

2267

} catch (exception $e) {

2268

$this->assertTrue($e instanceof dml_exception);

2253

} catch (moodle_exception $e) {

2254

$this->assertInstanceOf('dml_exception', $e);

2269

2255

}

2270

2256

}

2271

2257

2279

2265

$table = $this->get_test_table();

2280

2266

$tablename = $table->getName();

2281

2267

2282

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

2283

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

2284

$table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null, 100);

2268

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

2269

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

2270

$table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null, 100);

2285

2271

$table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);

2286

2272

$table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');

2287

2273

$table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);

2292

2278

$this->assertSame(1, $DB->insert_record($tablename, array('course' => 1), true));

2293

2279

$record = $DB->get_record($tablename, array('course' => 1));

2294

2280

$this->assertEquals(1, $record->id);

2295

$this->assertEquals(100, $record->oneint); // Just check column defaults have been applied

2281

$this->assertEquals(100, $record->oneint); // Just check column defaults have been applied.

2296

2282

$this->assertEquals(200, $record->onenum);

2297

2283

$this->assertSame('onestring', $record->onechar);

2298

2284

$this->assertNull($record->onetext);

2299

2285

$this->assertNull($record->onebinary);

2300

2286

2301

// ignore extra columns

2287

// Ignore extra columns.

2302

2288

$record = (object)array('id'=>13, 'course'=>2, 'xxxx'=>788778);

2303

2289

$before = clone($record);

2304

$this->assertSame(true, $DB->import_record($tablename, $record));

2290

$this->assertTrue($DB->import_record($tablename, $record));

2305

2291

$this->assertEquals($record, $before);

2306

2292

$records = $DB->get_records($tablename);

2307

2293

$this->assertEquals(2, $records[13]->course);

2308

2294

2309

// Check nulls are set properly for all types

2295

// Check nulls are set properly for all types.

2310

2296

$record = new stdClass();

2311

2297

$record->id = 20;

2312

2298

$record->oneint = null;

2323

2309

$this->assertNull($record->onetext);

2324

2310

$this->assertNull($record->onebinary);

2325

2311

2326

// Check zeros are set properly for all types

2312

// Check zeros are set properly for all types.

2327

2313

$record = new stdClass();

2328

2314

$record->id = 23;

2329

2315

$record->oneint = 0;

2333

2319

$this->assertEquals(0, $record->oneint);

2334

2320

$this->assertEquals(0, $record->onenum);

2335

2321

2336

// Check string data causes exception in numeric types

2322

// Check string data causes exception in numeric types.

2337

2323

$record = new stdClass();

2338

2324

$record->id = 32;

2339

2325

$record->oneint = 'onestring';

2341

2327

try {

2342

2328

$DB->import_record($tablename, $record);

2343

2329

$this->fail("Expecting an exception, none occurred");

2344

} catch (exception $e) {

2345

$this->assertTrue($e instanceof dml_exception);

2330

} catch (moodle_exception $e) {

2331

$this->assertInstanceOf('dml_exception', $e);

2346

2332

}

2347

2333

$record = new stdClass();

2348

2334

$record->id = 35;

2351

2337

try {

2352

2338

$DB->import_record($tablename, $record);

2353

2339

$this->fail("Expecting an exception, none occurred");

2354

} catch (exception $e) {

2355

$this->assertTrue($e instanceof dml_exception);

2340

} catch (moodle_exception $e) {

2341

$this->assertInstanceOf('dml_exception', $e);

2356

2342

}

2357

2343

2358

// Check empty strings are set properly in string types

2344

// Check empty strings are set properly in string types.

2359

2345

$record = new stdClass();

2360

2346

$record->id = 44;

2361

2347

$record->oneint = 0;

2367

2353

$this->assertTrue($record->onechar === '');

2368

2354

$this->assertTrue($record->onetext === '');

2369

2355

2370

// Check operation ((210.10 + 39.92) - 150.02) against numeric types

2356

// Check operation ((210.10 + 39.92) - 150.02) against numeric types.

2371

2357

$record = new stdClass();

2372

2358

$record->id = 47;

2373

2359

$record->oneint = ((210.10 + 39.92) - 150.02);

2377

2363

$this->assertEquals(100, $record->oneint);

2378

2364

$this->assertEquals(100, $record->onenum);

2379

2365

2380

// Check various quotes/backslashes combinations in string types

2366

// Check various quotes/backslashes combinations in string types.

2381

2367

$i = 50;

2382

2368

$teststrings = array(

2383

2369

'backslashes and quotes alone (even): "" \'\' \\\\',

2396

2382

$i = $i + 3;

2397

2383

}

2398

2384

2399

// Check LOBs in text/binary columns

2385

// Check LOBs in text/binary columns.

2400

2386

$clob = file_get_contents(__DIR__ . '/fixtures/clob.txt');

2401

2387

$record = new stdClass();

2402

2388

$record->id = 70;

2419

2405

$rs->close();

2420

2406

$this->assertEquals($blob, $record->onebinary, 'Test BLOB insert (full contents output disabled)');

2421

2407

2422

// And "small" LOBs too, just in case

2408

// And "small" LOBs too, just in case.

2423

2409

$newclob = substr($clob, 0, 500);

2424

2410

$newblob = substr($blob, 0, 250);

2425

2411

$record = new stdClass();

2432

2418

$rs->close();

2433

2419

$this->assertEquals($newclob, $record->onetext, 'Test "small" CLOB insert (full contents output disabled)');

2434

2420

$this->assertEquals($newblob, $record->onebinary, 'Test "small" BLOB insert (full contents output disabled)');

2435

$this->assertEquals(false, $rs->key()); // Ensure recordset key() method to be working ok after closing

2421

$this->assertEquals(false, $rs->key()); // Ensure recordset key() method to be working ok after closing.

2436

2422

}

2437

2423

2438

2424

public function test_update_record_raw() {

2442

2428

$table = $this->get_test_table();

2443

2429

$tablename = $table->getName();

2444

2430

2445

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

2446

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

2431

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

2432

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

2447

2433

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

2448

2434

$dbman->create_table($table);

2449

2435

2462

2448

try {

2463

2449

$DB->update_record_raw($tablename, $record);

2464

2450

$this->fail("Expecting an exception, none occurred");

2465

} catch (Exception $e) {

2466

$this->assertTrue($e instanceof moodle_exception);

2451

} catch (moodle_exception $e) {

2452

$this->assertInstanceOf('moodle_exception', $e);

2467

2453

}

2468

2454

2469

2455

$record = $DB->get_record($tablename, array('course' => 3));

2471

2457

try {

2472

2458

$DB->update_record_raw($tablename, $record);

2473

2459

$this->fail("Expecting an exception, none occurred");

2474

} catch (Exception $e) {

2475

$this->assertTrue($e instanceof coding_exception);

2460

} catch (moodle_exception $e) {

2461

$this->assertInstanceOf('coding_exception', $e);

2476

2462

}

2477

2463

}

2478

2464

2487

2473

$table = $this->get_test_table();

2488

2474

$tablename = $table->getName();

2489

2475

2490

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

2491

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

2492

$table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null, 100);

2476

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

2477

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

2478

$table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null, 100);

2493

2479

$table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);

2494

2480

$table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');

2495

2481

$table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);

2504

2490

$this->assertTrue($DB->update_record($tablename, $record));

2505

2491

$this->assertFalse($record = $DB->get_record($tablename, array('course' => 1)));

2506

2492

$this->assertNotEmpty($record = $DB->get_record($tablename, array('course' => 2)));

2507

$this->assertEquals(100, $record->oneint); // Just check column defaults have been applied

2493

$this->assertEquals(100, $record->oneint); // Just check column defaults have been applied.

2508

2494

$this->assertEquals(200, $record->onenum);

2509

$this->assertEquals('onestring', $record->onechar);

2495

$this->assertSame('onestring', $record->onechar);

2510

2496

$this->assertNull($record->onetext);

2511

2497

$this->assertNull($record->onebinary);

2512

2498

2513

// Check nulls are set properly for all types

2499

// Check nulls are set properly for all types.

2514

2500

$record->oneint = null;

2515

2501

$record->onenum = null;

2516

2502

$record->onechar = null;

2524

2510

$this->assertNull($record->onetext);

2525

2511

$this->assertNull($record->onebinary);

2526

2512

2527

// Check zeros are set properly for all types

2513

// Check zeros are set properly for all types.

2528

2514

$record->oneint = 0;

2529

2515

$record->onenum = 0;

2530

2516

$DB->update_record($tablename, $record);

2532

2518

$this->assertEquals(0, $record->oneint);

2533

2519

$this->assertEquals(0, $record->onenum);

2534

2520

2535

// Check booleans are set properly for all types

2536

$record->oneint = true; // trues

2521

// Check booleans are set properly for all types.

2522

$record->oneint = true; // Trues.

2537

2523

$record->onenum = true;

2538

2524

$record->onechar = true;

2539

2525

$record->onetext = true;

2544

2530

$this->assertEquals(1, $record->onechar);

2545

2531

$this->assertEquals(1, $record->onetext);

2546

2532

2547

$record->oneint = false; // falses

2533

$record->oneint = false; // Falses.

2548

2534

$record->onenum = false;

2549

2535

$record->onechar = false;

2550

2536

$record->onetext = false;

2555

2541

$this->assertEquals(0, $record->onechar);

2556

2542

$this->assertEquals(0, $record->onetext);

2557

2543

2558

// Check string data causes exception in numeric types

2544

// Check string data causes exception in numeric types.

2559

2545

$record->oneint = 'onestring';

2560

2546

$record->onenum = 0;

2561

2547

try {

2562

2548

$DB->update_record($tablename, $record);

2563

2549

$this->fail("Expecting an exception, none occurred");

2564

} catch (exception $e) {

2565

$this->assertTrue($e instanceof dml_exception);

2550

} catch (moodle_exception $e) {

2551

$this->assertInstanceOf('dml_exception', $e);

2566

2552

}

2567

2553

$record->oneint = 0;

2568

2554

$record->onenum = 'onestring';

2569

2555

try {

2570

2556

$DB->update_record($tablename, $record);

2571

2557

$this->fail("Expecting an exception, none occurred");

2572

} catch (exception $e) {

2573

$this->assertTrue($e instanceof dml_exception);

2558

} catch (moodle_exception $e) {

2559

$this->assertInstanceOf('dml_exception', $e);

2574

2560

}

2575

2561

2576

// Check empty string data is stored as 0 in numeric datatypes

2577

$record->oneint = ''; // empty string

2562

// Check empty string data is stored as 0 in numeric datatypes.

2563

$record->oneint = ''; // Empty string.

2578

2564

$record->onenum = 0;

2579

2565

$DB->update_record($tablename, $record);

2580

2566

$record = $DB->get_record($tablename, array('course' => 2));

2581

2567

$this->assertTrue(is_numeric($record->oneint) && $record->oneint == 0);

2582

2568

2583

2569

$record->oneint = 0;

2584

$record->onenum = ''; // empty string

2570

$record->onenum = ''; // Empty string.

2585

2571

$DB->update_record($tablename, $record);

2586

2572

$record = $DB->get_record($tablename, array('course' => 2));

2587

2573

$this->assertTrue(is_numeric($record->onenum) && $record->onenum == 0);

2588

2574

2589

// Check empty strings are set properly in string types

2575

// Check empty strings are set properly in string types.

2590

2576

$record->oneint = 0;

2591

2577

$record->onenum = 0;

2592

2578

$record->onechar = '';

2596

2582

$this->assertTrue($record->onechar === '');

2597

2583

$this->assertTrue($record->onetext === '');

2598

2584

2599

// Check operation ((210.10 + 39.92) - 150.02) against numeric types

2585

// Check operation ((210.10 + 39.92) - 150.02) against numeric types.

2600

2586

$record->oneint = ((210.10 + 39.92) - 150.02);

2601

2587

$record->onenum = ((210.10 + 39.92) - 150.02);

2602

2588

$DB->update_record($tablename, $record);

2604

2590

$this->assertEquals(100, $record->oneint);

2605

2591

$this->assertEquals(100, $record->onenum);

2606

2592

2607

// Check various quotes/backslashes combinations in string types

2593

// Check various quotes/backslashes combinations in string types.

2608

2594

$teststrings = array(

2609

2595

'backslashes and quotes alone (even): "" \'\' \\\\',

2610

2596

'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',

2619

2605

$this->assertEquals($teststring, $record->onetext);

2620

2606

}

2621

2607

2622

// Check LOBs in text/binary columns

2608

// Check LOBs in text/binary columns.

2623

2609

$clob = file_get_contents(__DIR__ . '/fixtures/clob.txt');

2624

2610

$blob = file_get_contents(__DIR__ . '/fixtures/randombinary');

2625

2611

$record->onetext = $clob;

2629

2615

$this->assertEquals($clob, $record->onetext, 'Test CLOB update (full contents output disabled)');

2630

2616

$this->assertEquals($blob, $record->onebinary, 'Test BLOB update (full contents output disabled)');

2631

2617

2632

// And "small" LOBs too, just in case

2618

// And "small" LOBs too, just in case.

2633

2619

$newclob = substr($clob, 0, 500);

2634

2620

$newblob = substr($blob, 0, 250);

2635

2621

$record->onetext = $newclob;

2677

2663

$table = $this->get_test_table();

2678

2664

$tablename = $table->getName();

2679

2665

2680

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

2681

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

2666

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

2667

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

2682

2668

$table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null);

2683

2669

$table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);

2684

2670

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

2685

2671

$dbman->create_table($table);

2686

2672

2687

// simple set_field

2673

// Simple set_field.

2688

2674

$id1 = $DB->insert_record($tablename, array('course' => 1));

2689

2675

$id2 = $DB->insert_record($tablename, array('course' => 1));

2690

2676

$id3 = $DB->insert_record($tablename, array('course' => 3));

2694

2680

$this->assertEquals(3, $DB->get_field($tablename, 'course', array('id' => $id3)));

2695

2681

$DB->delete_records($tablename, array());

2696

2682

2697

// multiple fields affected

2683

// Multiple fields affected.

2698

2684

$id1 = $DB->insert_record($tablename, array('course' => 1));

2699

2685

$id2 = $DB->insert_record($tablename, array('course' => 1));

2700

2686

$id3 = $DB->insert_record($tablename, array('course' => 3));

2704

2690

$this->assertEquals(3, $DB->get_field($tablename, 'course', array('id' => $id3)));

2705

2691

$DB->delete_records($tablename, array());

2706

2692

2707

// no field affected

2693

// No field affected.

2708

2694

$id1 = $DB->insert_record($tablename, array('course' => 1));

2709

2695

$id2 = $DB->insert_record($tablename, array('course' => 1));

2710

2696

$id3 = $DB->insert_record($tablename, array('course' => 3));

2714

2700

$this->assertEquals(3, $DB->get_field($tablename, 'course', array('id' => $id3)));

2715

2701

$DB->delete_records($tablename, array());

2716

2702

2717

// all fields - no condition

2703

// All fields - no condition.

2718

2704

$id1 = $DB->insert_record($tablename, array('course' => 1));

2719

2705

$id2 = $DB->insert_record($tablename, array('course' => 1));

2720

2706

$id3 = $DB->insert_record($tablename, array('course' => 3));

2723

2709

$this->assertEquals(5, $DB->get_field($tablename, 'course', array('id' => $id2)));

2724

2710

$this->assertEquals(5, $DB->get_field($tablename, 'course', array('id' => $id3)));

2725

2711

2726

// test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)

2712

// Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).

2727

2713

$conditions = array('onetext' => '1');

2728

2714

try {

2729

2715

$DB->set_field($tablename, 'onechar', 'frog', $conditions);

2730

2716

if (debugging()) {

2731

// only in debug mode - hopefully all devs test code in debug mode...

2717

// Only in debug mode - hopefully all devs test code in debug mode...

2732

2718

$this->fail('An Exception is missing, expected due to equating of text fields');

2733

2719

}

2734

} catch (exception $e) {

2735

$this->assertTrue($e instanceof dml_exception);

2736

$this->assertEquals($e->errorcode, 'textconditionsnotallowed');

2720

} catch (moodle_exception $e) {

2721

$this->assertInstanceOf('dml_exception', $e);

2722

$this->assertSame('textconditionsnotallowed', $e->errorcode);

2737

2723

}

2738

2724

2739

2725

// Test saving a float in a CHAR column, and reading it back.

2767

2753

$this->assertEquals(1e300, $DB->get_field($tablename, 'onetext', array('id' => $id)));

2768

2754

2769

2755

// Note: All the nulls, booleans, empties, quoted and backslashes tests

2770

// go to set_field_select() because set_field() is just one wrapper over it

2756

// go to set_field_select() because set_field() is just one wrapper over it.

2771

2757

}

2772

2758

2773

2759

public function test_set_field_select() {

2781

2767

$table = $this->get_test_table();

2782

2768

$tablename = $table->getName();

2783

2769

2784

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

2785

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

2786

$table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null);

2770

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

2771

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

2772

$table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null);

2787

2773

$table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null);

2788

2774

$table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null);

2789

2775

$table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);

2796

2782

$this->assertTrue($DB->set_field_select($tablename, 'course', 2, 'id = ?', array(1)));

2797

2783

$this->assertEquals(2, $DB->get_field($tablename, 'course', array('id' => 1)));

2798

2784

2799

// Check nulls are set properly for all types

2800

$DB->set_field_select($tablename, 'oneint', null, 'id = ?', array(1)); // trues

2785

// Check nulls are set properly for all types.

2786

$DB->set_field_select($tablename, 'oneint', null, 'id = ?', array(1)); // Trues.

2801

2787

$DB->set_field_select($tablename, 'onenum', null, 'id = ?', array(1));

2802

2788

$DB->set_field_select($tablename, 'onechar', null, 'id = ?', array(1));

2803

2789

$DB->set_field_select($tablename, 'onetext', null, 'id = ?', array(1));

2808

2794

$this->assertNull($DB->get_field($tablename, 'onetext', array('id' => 1)));

2809

2795

$this->assertNull($DB->get_field($tablename, 'onebinary', array('id' => 1)));

2810

2796

2811

// Check zeros are set properly for all types

2797

// Check zeros are set properly for all types.

2812

2798

$DB->set_field_select($tablename, 'oneint', 0, 'id = ?', array(1));

2813

2799

$DB->set_field_select($tablename, 'onenum', 0, 'id = ?', array(1));

2814

2800

$this->assertEquals(0, $DB->get_field($tablename, 'oneint', array('id' => 1)));

2815

2801

$this->assertEquals(0, $DB->get_field($tablename, 'onenum', array('id' => 1)));

2816

2802

2817

// Check booleans are set properly for all types

2818

$DB->set_field_select($tablename, 'oneint', true, 'id = ?', array(1)); // trues

2803

// Check booleans are set properly for all types.

2804

$DB->set_field_select($tablename, 'oneint', true, 'id = ?', array(1)); // Trues.

2819

2805

$DB->set_field_select($tablename, 'onenum', true, 'id = ?', array(1));

2820

2806

$DB->set_field_select($tablename, 'onechar', true, 'id = ?', array(1));

2821

2807

$DB->set_field_select($tablename, 'onetext', true, 'id = ?', array(1));

2824

2810

$this->assertEquals(1, $DB->get_field($tablename, 'onechar', array('id' => 1)));

2825

2811

$this->assertEquals(1, $DB->get_field($tablename, 'onetext', array('id' => 1)));

2826

2812

2827

$DB->set_field_select($tablename, 'oneint', false, 'id = ?', array(1)); // falses

2813

$DB->set_field_select($tablename, 'oneint', false, 'id = ?', array(1)); // Falses.

2828

2814

$DB->set_field_select($tablename, 'onenum', false, 'id = ?', array(1));

2829

2815

$DB->set_field_select($tablename, 'onechar', false, 'id = ?', array(1));

2830

2816

$DB->set_field_select($tablename, 'onetext', false, 'id = ?', array(1));

2833

2819

$this->assertEquals(0, $DB->get_field($tablename, 'onechar', array('id' => 1)));

2834

2820

$this->assertEquals(0, $DB->get_field($tablename, 'onetext', array('id' => 1)));

2835

2821

2836

// Check string data causes exception in numeric types

2822

// Check string data causes exception in numeric types.

2837

2823

try {

2838

2824

$DB->set_field_select($tablename, 'oneint', 'onestring', 'id = ?', array(1));

2839

2825

$this->fail("Expecting an exception, none occurred");

2840

} catch (exception $e) {

2841

$this->assertTrue($e instanceof dml_exception);

2826

} catch (moodle_exception $e) {

2827

$this->assertInstanceOf('dml_exception', $e);

2842

2828

}

2843

2829

try {

2844

2830

$DB->set_field_select($tablename, 'onenum', 'onestring', 'id = ?', array(1));

2845

2831

$this->fail("Expecting an exception, none occurred");

2846

} catch (exception $e) {

2847

$this->assertTrue($e instanceof dml_exception);

2832

} catch (moodle_exception $e) {

2833

$this->assertInstanceOf('dml_exception', $e);

2848

2834

}

2849

2835

2850

// Check empty string data is stored as 0 in numeric datatypes

2836

// Check empty string data is stored as 0 in numeric datatypes.

2851

2837

$DB->set_field_select($tablename, 'oneint', '', 'id = ?', array(1));

2852

2838

$field = $DB->get_field($tablename, 'oneint', array('id' => 1));

2853

2839

$this->assertTrue(is_numeric($field) && $field == 0);

2856

2842

$field = $DB->get_field($tablename, 'onenum', array('id' => 1));

2857

2843

$this->assertTrue(is_numeric($field) && $field == 0);

2858

2844

2859

// Check empty strings are set properly in string types

2845

// Check empty strings are set properly in string types.

2860

2846

$DB->set_field_select($tablename, 'onechar', '', 'id = ?', array(1));

2861

2847

$DB->set_field_select($tablename, 'onetext', '', 'id = ?', array(1));

2862

2848

$this->assertTrue($DB->get_field($tablename, 'onechar', array('id' => 1)) === '');

2863

2849

$this->assertTrue($DB->get_field($tablename, 'onetext', array('id' => 1)) === '');

2864

2850

2865

// Check operation ((210.10 + 39.92) - 150.02) against numeric types

2851

// Check operation ((210.10 + 39.92) - 150.02) against numeric types.

2866

2852

$DB->set_field_select($tablename, 'oneint', ((210.10 + 39.92) - 150.02), 'id = ?', array(1));

2867

2853

$DB->set_field_select($tablename, 'onenum', ((210.10 + 39.92) - 150.02), 'id = ?', array(1));

2868

2854

$this->assertEquals(100, $DB->get_field($tablename, 'oneint', array('id' => 1)));

2869

2855

$this->assertEquals(100, $DB->get_field($tablename, 'onenum', array('id' => 1)));

2870

2856

2871

// Check various quotes/backslashes combinations in string types

2857

// Check various quotes/backslashes combinations in string types.

2872

2858

$teststrings = array(

2873

2859

'backslashes and quotes alone (even): "" \'\' \\\\',

2874

2860

'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',

2881

2867

$this->assertEquals($teststring, $DB->get_field($tablename, 'onetext', array('id' => 1)));

2882

2868

}

2883

2869

2884

// Check LOBs in text/binary columns

2870

// Check LOBs in text/binary columns.

2885

2871

$clob = file_get_contents(__DIR__ . '/fixtures/clob.txt');

2886

2872

$blob = file_get_contents(__DIR__ . '/fixtures/randombinary');

2887

2873

$DB->set_field_select($tablename, 'onetext', $clob, 'id = ?', array(1));

2889

2875

$this->assertEquals($clob, $DB->get_field($tablename, 'onetext', array('id' => 1)), 'Test CLOB set_field (full contents output disabled)');

2890

2876

$this->assertEquals($blob, $DB->get_field($tablename, 'onebinary', array('id' => 1)), 'Test BLOB set_field (full contents output disabled)');

2891

2877

2892

// And "small" LOBs too, just in case

2878

// And "small" LOBs too, just in case.

2893

2879

$newclob = substr($clob, 0, 500);

2894

2880

$newblob = substr($blob, 0, 250);

2895

2881

$DB->set_field_select($tablename, 'onetext', $newclob, 'id = ?', array(1));

2901

2887

// which converts the '1' to an integer, which cannot then be compared with

2902

2888

// onetext cast to a varchar. This should be fixed and working now.

2903

2889

$newchar = 'frog';

2904

// test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)

2890

// Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).

2905

2891

$params = array('onetext' => '1');

2906

2892

try {

2907

2893

$DB->set_field_select($tablename, 'onechar', $newchar, $DB->sql_compare_text('onetext') . ' = ?', $params);

2910

2896

$this->assertFalse(true, 'We have an unexpected exception.');

2911

2897

throw $e;

2912

2898

}

2913

2914

2915

2899

}

2916

2900

2917

2901

public function test_count_records() {

2922

2906

$table = $this->get_test_table();

2923

2907

$tablename = $table->getName();

2924

2908

2925

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

2926

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

2909

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

2910

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

2927

2911

$table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);

2928

2912

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

2929

2913

$dbman->create_table($table);

2936

2920

2937

2921

$this->assertSame(3, $DB->count_records($tablename));

2938

2922

2939

// test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)

2923

// Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).

2940

2924

$conditions = array('onetext' => '1');

2941

2925

try {

2942

2926

$DB->count_records($tablename, $conditions);

2943

2927

if (debugging()) {

2944

// only in debug mode - hopefully all devs test code in debug mode...

2928

// Only in debug mode - hopefully all devs test code in debug mode...

2945

2929

$this->fail('An Exception is missing, expected due to equating of text fields');

2946

2930

}

2947

} catch (exception $e) {

2948

$this->assertTrue($e instanceof dml_exception);

2949

$this->assertEquals($e->errorcode, 'textconditionsnotallowed');

2931

} catch (moodle_exception $e) {

2932

$this->assertInstanceOf('dml_exception', $e);

2933

$this->assertSame('textconditionsnotallowed', $e->errorcode);

2950

2934

}

2951

2935

}

2952

2936

2958

2942

$table = $this->get_test_table();

2959

2943

$tablename = $table->getName();

2960

2944

2961

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

2962

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

2945

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

2946

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

2963

2947

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

2964

2948

$dbman->create_table($table);

2965

2949

2979

2963

$table = $this->get_test_table();

2980

2964

$tablename = $table->getName();

2981

2965

2982

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

2983

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

2966

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

2967

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

2984

2968

$table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null);

2985

2969

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

2986

2970

$dbman->create_table($table);

2993

2977

2994

2978

$this->assertSame(2, $DB->count_records_sql("SELECT COUNT(*) FROM {{$tablename}} WHERE course > ?", array(3)));

2995

2979

2996

// test invalid use

2980

// Test invalid use.

2997

2981

try {

2998

2982

$DB->count_records_sql("SELECT onechar FROM {{$tablename}} WHERE course = ?", array(3));

2999

2983

$this->fail('Exception expected when non-number field used in count_records_sql');

3000

} catch (exception $e) {

2984

} catch (moodle_exception $e) {

3001

2985

$this->assertInstanceOf('coding_exception', $e);

3002

2986

}

3003

2987

3004

2988

try {

3005

2989

$DB->count_records_sql("SELECT course FROM {{$tablename}} WHERE 1 = 2");

3006

2990

$this->fail('Exception expected when non-number field used in count_records_sql');

3007

} catch (exception $e) {

2991

} catch (moodle_exception $e) {

3008

2992

$this->assertInstanceOf('coding_exception', $e);

3009

2993

}

3010

2994

}

3016

3000

$table = $this->get_test_table();

3017

3001

$tablename = $table->getName();

3018

3002

3019

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3020

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

3003

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3004

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

3021

3005

$table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);

3022

3006

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

3023

3007

$dbman->create_table($table);

3029

3013

3030

3014

$this->assertTrue($DB->record_exists($tablename, array('course' => 3)));

3031

3015

3032

3033

// test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)

3016

// Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).

3034

3017

$conditions = array('onetext' => '1');

3035

3018

try {

3036

3019

$DB->record_exists($tablename, $conditions);

3037

3020

if (debugging()) {

3038

// only in debug mode - hopefully all devs test code in debug mode...

3021

// Only in debug mode - hopefully all devs test code in debug mode...

3039

3022

$this->fail('An Exception is missing, expected due to equating of text fields');

3040

3023

}

3041

} catch (exception $e) {

3042

$this->assertTrue($e instanceof dml_exception);

3043

$this->assertEquals($e->errorcode, 'textconditionsnotallowed');

3024

} catch (moodle_exception $e) {

3025

$this->assertInstanceOf('dml_exception', $e);

3026

$this->assertSame('textconditionsnotallowed', $e->errorcode);

3044

3027

}

3045

3028

}

3046

3029

3051

3034

$table = $this->get_test_table();

3052

3035

$tablename = $table->getName();

3053

3036

3054

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3055

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

3037

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3038

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

3056

3039

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

3057

3040

$dbman->create_table($table);

3058

3041

3071

3054

$table = $this->get_test_table();

3072

3055

$tablename = $table->getName();

3073

3056

3074

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3075

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

3057

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3058

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

3076

3059

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

3077

3060

$dbman->create_table($table);

3078

3061

3088

3071

$DB = $this->tdb;

3089

3072

$dbman = $DB->get_manager();

3090

3073

3091

//Setup

3074

// Setup.

3092

3075

$table = $this->get_test_table();

3093

3076

$tablename = $table->getName();

3094

3077

3095

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3096

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

3078

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3079

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

3097

3080

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

3098

3081

$dbman->create_table($table);

3099

3082

3104

3087

$DB->insert_record($tablename, array('course' => 5));

3105

3088

$DB->insert_record($tablename, array('course' => 6));

3106

3089

3107

// Test against db write locking while on an open recordset

3108

$rs = $DB->get_recordset($tablename, array(), null, 'course', 2, 2); // get courses = {3,4}

3090

// Test against db write locking while on an open recordset.

3091

$rs = $DB->get_recordset($tablename, array(), null, 'course', 2, 2); // Get courses = {3,4}.

3109

3092

foreach ($rs as $record) {

3110

3093

$cid = $record->course;

3111

3094

$DB->delete_records($tablename, array('course' => $cid));

3120

3103

$DB = $this->tdb;

3121

3104

$dbman = $DB->get_manager();

3122

3105

3123

//Setup

3106

// Setup.

3124

3107

$table = $this->get_test_table();

3125

3108

$tablename = $table->getName();

3126

3109

3127

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3128

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

3110

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3111

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

3129

3112

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

3130

3113

$dbman->create_table($table);

3131

3114

3136

3119

$DB->insert_record($tablename, array('course' => 5));

3137

3120

$DB->insert_record($tablename, array('course' => 6));

3138

3121

3139

// Test against db write locking while on an open recordset

3140

$rs = $DB->get_recordset($tablename, array(), null, 'course', 2, 2); // get courses = {3,4}

3122

// Test against db write locking while on an open recordset.

3123

$rs = $DB->get_recordset($tablename, array(), null, 'course', 2, 2); // Get courses = {3,4}.

3141

3124

foreach ($rs as $record) {

3142

3125

$cid = $record->course;

3143

3126

$DB->set_field($tablename, 'course', 10, array('course' => $cid));

3155

3138

$table = $this->get_test_table();

3156

3139

$tablename = $table->getName();

3157

3140

3158

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3159

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

3141

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3142

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

3160

3143

$table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);

3161

3144

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

3162

3145

$dbman->create_table($table);

3165

3148

$DB->insert_record($tablename, array('course' => 2));

3166

3149

$DB->insert_record($tablename, array('course' => 2));

3167

3150

3168

// Delete all records

3151

// Delete all records.

3169

3152

$this->assertTrue($DB->delete_records($tablename));

3170

3153

$this->assertEquals(0, $DB->count_records($tablename));

3171

3154

3172

// Delete subset of records

3155

// Delete subset of records.

3173

3156

$DB->insert_record($tablename, array('course' => 3));

3174

3157

$DB->insert_record($tablename, array('course' => 2));

3175

3158

$DB->insert_record($tablename, array('course' => 2));

3177

3160

$this->assertTrue($DB->delete_records($tablename, array('course' => 2)));

3178

3161

$this->assertEquals(1, $DB->count_records($tablename));

3179

3162

3180

// delete all

3163

// Delete all.

3181

3164

$this->assertTrue($DB->delete_records($tablename, array()));

3182

3165

$this->assertEquals(0, $DB->count_records($tablename));

3183

3166

3184

// test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)

3167

// Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).

3185

3168

$conditions = array('onetext'=>'1');

3186

3169

try {

3187

3170

$DB->delete_records($tablename, $conditions);

3188

3171

if (debugging()) {

3189

// only in debug mode - hopefully all devs test code in debug mode...

3172

// Only in debug mode - hopefully all devs test code in debug mode...

3190

3173

$this->fail('An Exception is missing, expected due to equating of text fields');

3191

3174

}

3192

} catch (exception $e) {

3193

$this->assertTrue($e instanceof dml_exception);

3194

$this->assertEquals($e->errorcode, 'textconditionsnotallowed');

3175

} catch (moodle_exception $e) {

3176

$this->assertInstanceOf('dml_exception', $e);

3177

$this->assertSame('textconditionsnotallowed', $e->errorcode);

3195

3178

}

3196

3179

3197

// test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)

3180

// Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).

3198

3181

$conditions = array('onetext' => 1);

3199

3182

try {

3200

3183

$DB->delete_records($tablename, $conditions);

3201

3184

if (debugging()) {

3202

// only in debug mode - hopefully all devs test code in debug mode...

3185

// Only in debug mode - hopefully all devs test code in debug mode...

3203

3186

$this->fail('An Exception is missing, expected due to equating of text fields');

3204

3187

}

3205

} catch (exception $e) {

3206

$this->assertTrue($e instanceof dml_exception);

3207

$this->assertEquals($e->errorcode, 'textconditionsnotallowed');

3188

} catch (moodle_exception $e) {

3189

$this->assertInstanceOf('dml_exception', $e);

3190

$this->assertSame('textconditionsnotallowed', $e->errorcode);

3208

3191

}

3209

3192

}

3210

3193

3215

3198

$table = $this->get_test_table();

3216

3199

$tablename = $table->getName();

3217

3200

3218

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3219

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

3201

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3202

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

3220

3203

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

3221

3204

$dbman->create_table($table);

3222

3205

3235

3218

$table = $this->get_test_table();

3236

3219

$tablename = $table->getName();

3237

3220

3238

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3239

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

3221

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3222

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

3240

3223

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

3241

3224

$dbman->create_table($table);

3242

3225

3247

3230

$this->assertTrue($DB->delete_records_list($tablename, 'course', array(2, 3)));

3248

3231

$this->assertEquals(1, $DB->count_records($tablename));

3249

3232

3250

$this->assertTrue($DB->delete_records_list($tablename, 'course', array())); // Must delete 0 rows without conditions. MDL-17645

3233

$this->assertTrue($DB->delete_records_list($tablename, 'course', array())); // Must delete 0 rows without conditions. MDL-17645.

3251

3234

$this->assertEquals(1, $DB->count_records($tablename));

3252

3235

}

3253

3236

3257

3240

3258

3241

$table = $this->get_test_table();

3259

3242

$tablename = $table->getName();

3260

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3261

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

3243

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3244

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

3262

3245

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

3263

3246

$dbman->create_table($table);

3264

3247

3265

$o = new stdClass(); // objects without __toString - never worked

3248

$o = new stdClass(); // Objects without __toString - never worked.

3266

3249

try {

3267

3250

$DB->fix_sql_params("SELECT {{$tablename}} WHERE course = ? ", array($o));

3268

3251

$this->fail('coding_exception expected');

3269

} catch (Exception $e) {

3270

$this->assertTrue($e instanceof coding_exception);

3252

} catch (moodle_exception $e) {

3253

$this->assertInstanceOf('coding_exception', $e);

3271

3254

}

3272

3255

3273

// objects with __toString() forbidden everywhere since 2.3

3256

// Objects with __toString() forbidden everywhere since 2.3.

3274

3257

$o = new dml_test_object_one();

3275

3258

try {

3276

3259

$DB->fix_sql_params("SELECT {{$tablename}} WHERE course = ? ", array($o));

3277

3260

$this->fail('coding_exception expected');

3278

} catch (Exception $e) {

3279

$this->assertTrue($e instanceof coding_exception);

3261

} catch (moodle_exception $e) {

3262

$this->assertInstanceOf('coding_exception', $e);

3280

3263

}

3281

3264

3282

3265

try {

3283

3266

$DB->execute("SELECT {{$tablename}} WHERE course = ? ", array($o));

3284

3267

$this->fail('coding_exception expected');

3285

} catch (Exception $e) {

3286

$this->assertTrue($e instanceof coding_exception);

3268

} catch (moodle_exception $e) {

3269

$this->assertInstanceOf('coding_exception', $e);

3287

3270

}

3288

3271

3289

3272

try {

3290

3273

$DB->get_recordset_sql("SELECT {{$tablename}} WHERE course = ? ", array($o));

3291

3274

$this->fail('coding_exception expected');

3292

} catch (Exception $e) {

3293

$this->assertTrue($e instanceof coding_exception);

3275

} catch (moodle_exception $e) {

3276

$this->assertInstanceOf('coding_exception', $e);

3294

3277

}

3295

3278

3296

3279

try {

3297

3280

$DB->get_records_sql("SELECT {{$tablename}} WHERE course = ? ", array($o));

3298

3281

$this->fail('coding_exception expected');

3299

} catch (Exception $e) {

3300

$this->assertTrue($e instanceof coding_exception);

3282

} catch (moodle_exception $e) {

3283

$this->assertInstanceOf('coding_exception', $e);

3301

3284

}

3302

3285

3303

3286

try {

3305

3288

$record->course = $o;

3306

3289

$DB->insert_record_raw($tablename, $record);

3307

3290

$this->fail('coding_exception expected');

3308

} catch (Exception $e) {

3309

$this->assertTrue($e instanceof coding_exception);

3291

} catch (moodle_exception $e) {

3292

$this->assertInstanceOf('coding_exception', $e);

3310

3293

}

3311

3294

3312

3295

try {

3314

3297

$record->course = $o;

3315

3298

$DB->insert_record($tablename, $record);

3316

3299

$this->fail('coding_exception expected');

3317

} catch (Exception $e) {

3318

$this->assertTrue($e instanceof coding_exception);

3300

} catch (moodle_exception $e) {

3301

$this->assertInstanceOf('coding_exception', $e);

3319

3302

}

3320

3303

3321

3304

try {

3323

3306

$record->course = $o;

3324

3307

$DB->import_record($tablename, $record);

3325

3308

$this->fail('coding_exception expected');

3326

} catch (Exception $e) {

3327

$this->assertTrue($e instanceof coding_exception);

3309

} catch (moodle_exception $e) {

3310

$this->assertInstanceOf('coding_exception', $e);

3328

3311

}

3329

3312

3330

3313

try {

3333

3316

$record->course = $o;

3334

3317

$DB->update_record_raw($tablename, $record);

3335

3318

$this->fail('coding_exception expected');

3336

} catch (Exception $e) {

3337

$this->assertTrue($e instanceof coding_exception);

3319

} catch (moodle_exception $e) {

3320

$this->assertInstanceOf('coding_exception', $e);

3338

3321

}

3339

3322

3340

3323

try {

3343

3326

$record->course = $o;

3344

3327

$DB->update_record($tablename, $record);

3345

3328

$this->fail('coding_exception expected');

3346

} catch (Exception $e) {

3347

$this->assertTrue($e instanceof coding_exception);

3329

} catch (moodle_exception $e) {

3330

$this->assertInstanceOf('coding_exception', $e);

3348

3331

}

3349

3332

3350

3333

try {

3351

3334

$DB->set_field_select($tablename, 'course', 1, "course = ? ", array($o));

3352

3335

$this->fail('coding_exception expected');

3353

} catch (Exception $e) {

3354

$this->assertTrue($e instanceof coding_exception);

3336

} catch (moodle_exception $e) {

3337

$this->assertInstanceOf('coding_exception', $e);

3355

3338

}

3356

3339

3357

3340

try {

3358

3341

$DB->delete_records_select($tablename, "course = ? ", array($o));

3359

3342

$this->fail('coding_exception expected');

3360

} catch (Exception $e) {

3361

$this->assertTrue($e instanceof coding_exception);

3343

} catch (moodle_exception $e) {

3344

$this->assertInstanceOf('coding_exception', $e);

3362

3345

}

3363

3346

}

3364

3347

3365

function test_sql_null_from_clause() {

3348

public function test_sql_null_from_clause() {

3366

3349

$DB = $this->tdb;

3367

3350

$sql = "SELECT 1 AS id ".$DB->sql_null_from_clause();

3368

$this->assertEquals($DB->get_field_sql($sql), 1);

3351

$this->assertEquals(1, $DB->get_field_sql($sql));

3369

3352

}

3370

3353

3371

function test_sql_bitand() {

3354

public function test_sql_bitand() {

3372

3355

$DB = $this->tdb;

3373

3356

$dbman = $DB->get_manager();

3374

3357

3375

3358

$table = $this->get_test_table();

3376

3359

$tablename = $table->getName();

3377

3360

3378

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3379

$table->add_field('col1', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

3380

$table->add_field('col2', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

3361

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3362

$table->add_field('col1', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

3363

$table->add_field('col2', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

3381

3364

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

3382

3365

$dbman->create_table($table);

3383

3366

3384

3367

$DB->insert_record($tablename, array('col1' => 3, 'col2' => 10));

3385

3368

3386

3369

$sql = "SELECT ".$DB->sql_bitand(10, 3)." AS res ".$DB->sql_null_from_clause();

3387

$this->assertEquals($DB->get_field_sql($sql), 2);

3370

$this->assertEquals(2, $DB->get_field_sql($sql));

3388

3371

3389

3372

$sql = "SELECT id, ".$DB->sql_bitand('col1', 'col2')." AS res FROM {{$tablename}}";

3390

3373

$result = $DB->get_records_sql($sql);

3391

$this->assertEquals(count($result), 1);

3392

$this->assertEquals(reset($result)->res, 2);

3374

$this->assertCount(1, $result);

3375

$this->assertEquals(2, reset($result)->res);

3393

3376

3394

3377

$sql = "SELECT id, ".$DB->sql_bitand('col1', '?')." AS res FROM {{$tablename}}";

3395

3378

$result = $DB->get_records_sql($sql, array(10));

3396

$this->assertEquals(count($result), 1);

3397

$this->assertEquals(reset($result)->res, 2);

3379

$this->assertCount(1, $result);

3380

$this->assertEquals(2, reset($result)->res);

3398

3381

}

3399

3382

3400

function test_sql_bitnot() {

3383

public function test_sql_bitnot() {

3401

3384

$DB = $this->tdb;

3402

3385

3403

3386

$not = $DB->sql_bitnot(2);

3404

$notlimited = $DB->sql_bitand($not, 7); // might be positive or negative number which can not fit into PHP INT!

3387

$notlimited = $DB->sql_bitand($not, 7); // Might be positive or negative number which can not fit into PHP INT!

3405

3388

3406

3389

$sql = "SELECT $notlimited AS res ".$DB->sql_null_from_clause();

3407

$this->assertEquals($DB->get_field_sql($sql), 5);

3390

$this->assertEquals(5, $DB->get_field_sql($sql));

3408

3391

}

3409

3392

3410

function test_sql_bitor() {

3393

public function test_sql_bitor() {

3411

3394

$DB = $this->tdb;

3412

3395

$dbman = $DB->get_manager();

3413

3396

3414

3397

$table = $this->get_test_table();

3415

3398

$tablename = $table->getName();

3416

3399

3417

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3418

$table->add_field('col1', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

3419

$table->add_field('col2', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

3400

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3401

$table->add_field('col1', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

3402

$table->add_field('col2', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

3420

3403

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

3421

3404

$dbman->create_table($table);

3422

3405

3423

3406

$DB->insert_record($tablename, array('col1' => 3, 'col2' => 10));

3424

3407

3425

3408

$sql = "SELECT ".$DB->sql_bitor(10, 3)." AS res ".$DB->sql_null_from_clause();

3426

$this->assertEquals($DB->get_field_sql($sql), 11);

3409

$this->assertEquals(11, $DB->get_field_sql($sql));

3427

3410

3428

3411

$sql = "SELECT id, ".$DB->sql_bitor('col1', 'col2')." AS res FROM {{$tablename}}";

3429

3412

$result = $DB->get_records_sql($sql);

3430

$this->assertEquals(count($result), 1);

3431

$this->assertEquals(reset($result)->res, 11);

3413

$this->assertCount(1, $result);

3414

$this->assertEquals(11, reset($result)->res);

3432

3415

3433

3416

$sql = "SELECT id, ".$DB->sql_bitor('col1', '?')." AS res FROM {{$tablename}}";

3434

3417

$result = $DB->get_records_sql($sql, array(10));

3435

$this->assertEquals(count($result), 1);

3436

$this->assertEquals(reset($result)->res, 11);

3418

$this->assertCount(1, $result);

3419

$this->assertEquals(11, reset($result)->res);

3437

3420

}

3438

3421

3439

function test_sql_bitxor() {

3422

public function test_sql_bitxor() {

3440

3423

$DB = $this->tdb;

3441

3424

$dbman = $DB->get_manager();

3442

3425

3443

3426

$table = $this->get_test_table();

3444

3427

$tablename = $table->getName();

3445

3428

3446

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3447

$table->add_field('col1', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

3448

$table->add_field('col2', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

3429

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3430

$table->add_field('col1', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

3431

$table->add_field('col2', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

3449

3432

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

3450

3433

$dbman->create_table($table);

3451

3434

3452

3435

$DB->insert_record($tablename, array('col1' => 3, 'col2' => 10));

3453

3436

3454

3437

$sql = "SELECT ".$DB->sql_bitxor(10, 3)." AS res ".$DB->sql_null_from_clause();

3455

$this->assertEquals($DB->get_field_sql($sql), 9);

3438

$this->assertEquals(9, $DB->get_field_sql($sql));

3456

3439

3457

3440

$sql = "SELECT id, ".$DB->sql_bitxor('col1', 'col2')." AS res FROM {{$tablename}}";

3458

3441

$result = $DB->get_records_sql($sql);

3459

$this->assertEquals(count($result), 1);

3460

$this->assertEquals(reset($result)->res, 9);

3442

$this->assertCount(1, $result);

3443

$this->assertEquals(9, reset($result)->res);

3461

3444

3462

3445

$sql = "SELECT id, ".$DB->sql_bitxor('col1', '?')." AS res FROM {{$tablename}}";

3463

3446

$result = $DB->get_records_sql($sql, array(10));

3464

$this->assertEquals(count($result), 1);

3465

$this->assertEquals(reset($result)->res, 9);

3447

$this->assertCount(1, $result);

3448

$this->assertEquals(9, reset($result)->res);

3466

3449

}

3467

3450

3468

function test_sql_modulo() {

3451

public function test_sql_modulo() {

3469

3452

$DB = $this->tdb;

3470

3453

$sql = "SELECT ".$DB->sql_modulo(10, 7)." AS res ".$DB->sql_null_from_clause();

3471

$this->assertEquals($DB->get_field_sql($sql), 3);

3454

$this->assertEquals(3, $DB->get_field_sql($sql));

3472

3455

}

3473

3456

3474

function test_sql_ceil() {

3457

public function test_sql_ceil() {

3475

3458

$DB = $this->tdb;

3476

3459

$sql = "SELECT ".$DB->sql_ceil(665.666)." AS res ".$DB->sql_null_from_clause();

3477

$this->assertEquals($DB->get_field_sql($sql), 666);

3460

$this->assertEquals(666, $DB->get_field_sql($sql));

3478

3461

}

3479

3462

3480

function test_cast_char2int() {

3463

public function test_cast_char2int() {

3481

3464

$DB = $this->tdb;

3482

3465

$dbman = $DB->get_manager();

3483

3466

3484

3467

$table1 = $this->get_test_table("1");

3485

3468

$tablename1 = $table1->getName();

3486

3469

3487

$table1->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3470

$table1->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3488

3471

$table1->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);

3489

3472

$table1->add_field('nametext', XMLDB_TYPE_TEXT, 'small', null, null, null, null);

3490

3473

$table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

3495

3478

3496

3479

$table2 = $this->get_test_table("2");

3497

3480

$tablename2 = $table2->getName();

3498

$table2->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3499

$table2->add_field('res', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

3500

$table2->add_field('restext', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

3481

$table2->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3482

$table2->add_field('res', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

3483

$table2->add_field('restext', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

3501

3484

$table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

3502

3485

$dbman->create_table($table2);

3503

3486

3504

3487

$DB->insert_record($tablename2, array('res'=>100, 'restext'=>200));

3505

3488

3506

// casting varchar field

3489

// Casting varchar field.

3507

3490

$sql = "SELECT *

3508

3491

FROM {".$tablename1."} t1

3509

3492

JOIN {".$tablename2."} t2 ON ".$DB->sql_cast_char2int("t1.name")." = t2.res ";

3510

3493

$records = $DB->get_records_sql($sql);

3511

$this->assertEquals(count($records), 1);

3512

// also test them in order clauses

3494

$this->assertCount(1, $records);

3495

// Also test them in order clauses.

3513

3496

$sql = "SELECT * FROM {{$tablename1}} ORDER BY ".$DB->sql_cast_char2int('name');

3514

3497

$records = $DB->get_records_sql($sql);

3515

$this->assertEquals(count($records), 2);

3516

$this->assertEquals(reset($records)->name, '10');

3517

$this->assertEquals(next($records)->name, '0100');

3498

$this->assertCount(2, $records);

3499

$this->assertSame('10', reset($records)->name);

3500

$this->assertSame('0100', next($records)->name);

3518

3501

3519

// casting text field

3502

// Casting text field.

3520

3503

$sql = "SELECT *

3521

3504

FROM {".$tablename1."} t1

3522

3505

JOIN {".$tablename2."} t2 ON ".$DB->sql_cast_char2int("t1.nametext", true)." = t2.restext ";

3523

3506

$records = $DB->get_records_sql($sql);

3524

$this->assertEquals(count($records), 1);

3525

// also test them in order clauses

3507

$this->assertCount(1, $records);

3508

// Also test them in order clauses.

3526

3509

$sql = "SELECT * FROM {{$tablename1}} ORDER BY ".$DB->sql_cast_char2int('nametext', true);

3527

3510

$records = $DB->get_records_sql($sql);

3528

$this->assertEquals(count($records), 2);

3529

$this->assertEquals(reset($records)->nametext, '20');

3530

$this->assertEquals(next($records)->nametext, '0200');

3511

$this->assertCount(2, $records);

3512

$this->assertSame('20', reset($records)->nametext);

3513

$this->assertSame('0200', next($records)->nametext);

3531

3514

}

3532

3515

3533

function test_cast_char2real() {

3516

public function test_cast_char2real() {

3534

3517

$DB = $this->tdb;

3535

3518

$dbman = $DB->get_manager();

3536

3519

3537

3520

$table = $this->get_test_table();

3538

3521

$tablename = $table->getName();

3539

3522

3540

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3523

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3541

3524

$table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);

3542

3525

$table->add_field('nametext', XMLDB_TYPE_TEXT, 'small', null, null, null, null);

3543

3526

$table->add_field('res', XMLDB_TYPE_NUMBER, '12, 7', null, null, null, null);

3546

3529

3547

3530

$DB->insert_record($tablename, array('name'=>'10.10', 'nametext'=>'10.10', 'res'=>5.1));

3548

3531

$DB->insert_record($tablename, array('name'=>'91.10', 'nametext'=>'91.10', 'res'=>666));

3549

$DB->insert_record($tablename, array('name'=>'011.10','nametext'=>'011.10','res'=>10.1));

3532

$DB->insert_record($tablename, array('name'=>'011.10', 'nametext'=>'011.10', 'res'=>10.1));

3550

3533

3551

// casting varchar field

3534

// Casting varchar field.

3552

3535

$sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_cast_char2real('name')." > res";

3553

3536

$records = $DB->get_records_sql($sql);

3554

$this->assertEquals(count($records), 2);

3555

// also test them in order clauses

3537

$this->assertCount(2, $records);

3538

// Also test them in order clauses.

3556

3539

$sql = "SELECT * FROM {{$tablename}} ORDER BY ".$DB->sql_cast_char2real('name');

3557

3540

$records = $DB->get_records_sql($sql);

3558

$this->assertEquals(count($records), 3);

3559

$this->assertEquals(reset($records)->name, '10.10');

3560

$this->assertEquals(next($records)->name, '011.10');

3561

$this->assertEquals(next($records)->name, '91.10');

3541

$this->assertCount(3, $records);

3542

$this->assertSame('10.10', reset($records)->name);

3543

$this->assertSame('011.10', next($records)->name);

3544

$this->assertSame('91.10', next($records)->name);

3562

3545

3563

// casting text field

3546

// Casting text field.

3564

3547

$sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_cast_char2real('nametext', true)." > res";

3565

3548

$records = $DB->get_records_sql($sql);

3566

$this->assertEquals(count($records), 2);

3567

// also test them in order clauses

3549

$this->assertCount(2, $records);

3550

// Also test them in order clauses.

3568

3551

$sql = "SELECT * FROM {{$tablename}} ORDER BY ".$DB->sql_cast_char2real('nametext', true);

3569

3552

$records = $DB->get_records_sql($sql);

3570

$this->assertEquals(count($records), 3);

3571

$this->assertEquals(reset($records)->nametext, '10.10');

3572

$this->assertEquals(next($records)->nametext, '011.10');

3573

$this->assertEquals(next($records)->nametext, '91.10');

3553

$this->assertCount(3, $records);

3554

$this->assertSame('10.10', reset($records)->nametext);

3555

$this->assertSame('011.10', next($records)->nametext);

3556

$this->assertSame('91.10', next($records)->nametext);

3574

3557

}

3575

3558

3576

3559

public function test_sql_compare_text() {

3580

3563

$table = $this->get_test_table();

3581

3564

$tablename = $table->getName();

3582

3565

3583

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3566

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3584

3567

$table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);

3585

3568

$table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);

3586

3569

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

3627

3610

3628

3611

}

3629

3612

3630

function test_unique_index_collation_trouble() {

3631

// note: this is a work in progress, we should probably move this to ddl test

3613

public function test_unique_index_collation_trouble() {

3614

// Note: this is a work in progress, we should probably move this to ddl test.

3632

3615

3633

3616

$DB = $this->tdb;

3634

3617

$dbman = $DB->get_manager();

3636

3619

$table = $this->get_test_table();

3637

3620

$tablename = $table->getName();

3638

3621

3639

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3622

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3640

3623

$table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);

3641

3624

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

3642

3625

$table->add_index('name', XMLDB_INDEX_UNIQUE, array('name'));

3646

3629

3647

3630

try {

3648

3631

$DB->insert_record($tablename, array('name'=>'AAA'));

3649

} catch (Exception $e) {

3650

//TODO: ignore case insensitive uniqueness problems for now

3651

//$this->fail("Unique index is case sensitive - this may cause problems in some tables");

3632

} catch (moodle_exception $e) {

3633

// TODO: ignore case insensitive uniqueness problems for now.

3634

// $this->fail("Unique index is case sensitive - this may cause problems in some tables");

3652

3635

}

3653

3636

3654

3637

try {

3655

3638

$DB->insert_record($tablename, array('name'=>'aäa'));

3656

3639

$DB->insert_record($tablename, array('name'=>'aáa'));

3657

3640

$this->assertTrue(true);

3658

} catch (Exception $e) {

3641

} catch (moodle_exception $e) {

3659

3642

$family = $DB->get_dbfamily();

3660

3643

if ($family === 'mysql' or $family === 'mssql') {

3661

3644

$this->fail("Unique index is accent insensitive, this may cause problems for non-ascii languages. This is usually caused by accent insensitive default collation.");

3662

3645

} else {

3663

// this should not happen, PostgreSQL and Oracle do not support accent insensitive uniqueness.

3646

// This should not happen, PostgreSQL and Oracle do not support accent insensitive uniqueness.

3664

3647

$this->fail("Unique index is accent insensitive, this may cause problems for non-ascii languages.");

3665

3648

}

3666

3649

throw($e);

3667

3650

}

3668

3651

}

3669

3652

3670

function test_sql_binary_equal() {

3653

public function test_sql_binary_equal() {

3671

3654

$DB = $this->tdb;

3672

3655

$dbman = $DB->get_manager();

3673

3656

3674

3657

$table = $this->get_test_table();

3675

3658

$tablename = $table->getName();

3676

3659

3677

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3660

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3678

3661

$table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);

3679

3662

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

3680

3663

$dbman->create_table($table);

3686

3669

$DB->insert_record($tablename, array('name'=>'BBB'));

3687

3670

3688

3671

$records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE name = ?", array('bbb'));

3689

$this->assertEquals(count($records), 1, 'SQL operator "=" is expected to be case sensitive');

3672

$this->assertEquals(1, count($records), 'SQL operator "=" is expected to be case sensitive');

3690

3673

3691

3674

$records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE name = ?", array('aaa'));

3692

$this->assertEquals(count($records), 1, 'SQL operator "=" is expected to be accent sensitive');

3675

$this->assertEquals(1, count($records), 'SQL operator "=" is expected to be accent sensitive');

3693

3676

}

3694

3677

3695

function test_sql_like() {

3678

public function test_sql_like() {

3696

3679

$DB = $this->tdb;

3697

3680

$dbman = $DB->get_manager();

3698

3681

3699

3682

$table = $this->get_test_table();

3700

3683

$tablename = $table->getName();

3701

3684

3702

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3685

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3703

3686

$table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);

3704

3687

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

3705

3688

$dbman->create_table($table);

3715

3698

3716

3699

$sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false);

3717

3700

$records = $DB->get_records_sql($sql, array("%dup_r%"));

3718

$this->assertEquals(count($records), 2);

3701

$this->assertCount(2, $records);

3719

3702

3720

3703

$sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true);

3721

3704

$records = $DB->get_records_sql($sql, array("%dup%"));

3722

$this->assertEquals(count($records), 1);

3705

$this->assertCount(1, $records);

3723

3706

3724

$sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?'); // defaults

3707

$sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?'); // Defaults.

3725

3708

$records = $DB->get_records_sql($sql, array("%dup%"));

3726

$this->assertEquals(count($records), 1);

3709

$this->assertCount(1, $records);

3727

3710

3728

3711

$sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true);

3729

3712

$records = $DB->get_records_sql($sql, array("ouc\\_"));

3730

$this->assertEquals(count($records), 1);

3713

$this->assertCount(1, $records);

3731

3714

3732

3715

$sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true, false, '|');

3733

3716

$records = $DB->get_records_sql($sql, array($DB->sql_like_escape("ouc%", '|')));

3734

$this->assertEquals(count($records), 1);

3717

$this->assertCount(1, $records);

3735

3718

3736

3719

$sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true);

3737

3720

$records = $DB->get_records_sql($sql, array('aui'));

3738

$this->assertEquals(count($records), 1);

3721

$this->assertCount(1, $records);

3739

3722

3740

$sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true, true); // NOT LIKE

3723

$sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true, true); // NOT LIKE.

3741

3724

$records = $DB->get_records_sql($sql, array("%o%"));

3742

$this->assertEquals(count($records), 3);

3725

$this->assertCount(3, $records);

3743

3726

3744

$sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false, true, true); // NOT ILIKE

3727

$sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false, true, true); // NOT ILIKE.

3745

3728

$records = $DB->get_records_sql($sql, array("%D%"));

3746

$this->assertEquals(count($records), 6);

3729

$this->assertCount(6, $records);

3747

3730

3748

// verify usual escaping characters work fine

3731

// Verify usual escaping characters work fine.

3749

3732

$sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true, false, '\\');

3750

3733

$records = $DB->get_records_sql($sql, array("ouc\\_"));

3751

$this->assertEquals(count($records), 1);

3734

$this->assertCount(1, $records);

3752

3735

$sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true, false, '|');

3753

3736

$records = $DB->get_records_sql($sql, array("ouc|%"));

3754

$this->assertEquals(count($records), 1);

3737

$this->assertCount(1, $records);

3755

3738

3756

// TODO: we do not require accent insensitivness yet, just make sure it does not throw errors

3739

// TODO: we do not require accent insensitivness yet, just make sure it does not throw errors.

3757

3740

$sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, false);

3758

3741

$records = $DB->get_records_sql($sql, array('aui'));

3759

//$this->assertEquals(count($records), 2, 'Accent insensitive LIKE searches may not be supported in all databases, this is not a problem.');

3742

// $this->assertEquals(2, count($records), 'Accent insensitive LIKE searches may not be supported in all databases, this is not a problem.');

3760

3743

$sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false, false);

3761

3744

$records = $DB->get_records_sql($sql, array('aui'));

3762

//$this->assertEquals(count($records), 3, 'Accent insensitive LIKE searches may not be supported in all databases, this is not a problem.');

3745

// $this->assertEquals(3, count($records), 'Accent insensitive LIKE searches may not be supported in all databases, this is not a problem.');

3763

3746

}

3764

3747

3765

function test_coalesce() {

3748

public function test_coalesce() {

3766

3749

$DB = $this->tdb;

3767

3750

3768

// Testing not-null occurrences, return 1st

3751

// Testing not-null occurrences, return 1st.

3769

3752

$sql = "SELECT COALESCE('returnthis', 'orthis', 'orwhynotthis') AS test" . $DB->sql_null_from_clause();

3770

$this->assertEquals('returnthis', $DB->get_field_sql($sql, array()));

3753

$this->assertSame('returnthis', $DB->get_field_sql($sql, array()));

3771

3754

$sql = "SELECT COALESCE(:paramvalue, 'orthis', 'orwhynotthis') AS test" . $DB->sql_null_from_clause();

3772

$this->assertEquals('returnthis', $DB->get_field_sql($sql, array('paramvalue' => 'returnthis')));

3755

$this->assertSame('returnthis', $DB->get_field_sql($sql, array('paramvalue' => 'returnthis')));

3773

3756

3774

// Testing null occurrences, return 2nd

3757

// Testing null occurrences, return 2nd.

3775

3758

$sql = "SELECT COALESCE(null, 'returnthis', 'orthis') AS test" . $DB->sql_null_from_clause();

3776

$this->assertEquals('returnthis', $DB->get_field_sql($sql, array()));

3759

$this->assertSame('returnthis', $DB->get_field_sql($sql, array()));

3777

3760

$sql = "SELECT COALESCE(:paramvalue, 'returnthis', 'orthis') AS test" . $DB->sql_null_from_clause();

3778

$this->assertEquals('returnthis', $DB->get_field_sql($sql, array('paramvalue' => null)));

3761

$this->assertSame('returnthis', $DB->get_field_sql($sql, array('paramvalue' => null)));

3779

3762

$sql = "SELECT COALESCE(null, :paramvalue, 'orthis') AS test" . $DB->sql_null_from_clause();

3780

$this->assertEquals('returnthis', $DB->get_field_sql($sql, array('paramvalue' => 'returnthis')));

3763

$this->assertSame('returnthis', $DB->get_field_sql($sql, array('paramvalue' => 'returnthis')));

3781

3764

3782

// Testing null occurrences, return 3rd

3765

// Testing null occurrences, return 3rd.

3783

3766

$sql = "SELECT COALESCE(null, null, 'returnthis') AS test" . $DB->sql_null_from_clause();

3784

$this->assertEquals('returnthis', $DB->get_field_sql($sql, array()));

3767

$this->assertSame('returnthis', $DB->get_field_sql($sql, array()));

3785

3768

$sql = "SELECT COALESCE(null, :paramvalue, 'returnthis') AS test" . $DB->sql_null_from_clause();

3786

$this->assertEquals('returnthis', $DB->get_field_sql($sql, array('paramvalue' => null)));

3769

$this->assertSame('returnthis', $DB->get_field_sql($sql, array('paramvalue' => null)));

3787

3770

$sql = "SELECT COALESCE(null, null, :paramvalue) AS test" . $DB->sql_null_from_clause();

3788

$this->assertEquals('returnthis', $DB->get_field_sql($sql, array('paramvalue' => 'returnthis')));

3771

$this->assertSame('returnthis', $DB->get_field_sql($sql, array('paramvalue' => 'returnthis')));

3789

3772

3790

// Testing all null occurrences, return null

3773

// Testing all null occurrences, return null.

3791

3774

// Note: under mssql, if all elements are nulls, at least one must be a "typed" null, hence

3792

3775

// we cannot test this in a cross-db way easily, so next 2 tests are using

3793

// different queries depending of the DB family

3776

// different queries depending of the DB family.

3794

3777

$customnull = $DB->get_dbfamily() == 'mssql' ? 'CAST(null AS varchar)' : 'null';

3795

3778

$sql = "SELECT COALESCE(null, null, " . $customnull . ") AS test" . $DB->sql_null_from_clause();

3796

3779

$this->assertNull($DB->get_field_sql($sql, array()));

3797

3780

$sql = "SELECT COALESCE(null, :paramvalue, " . $customnull . ") AS test" . $DB->sql_null_from_clause();

3798

3781

$this->assertNull($DB->get_field_sql($sql, array('paramvalue' => null)));

3799

3782

3800

// Check there are not problems with whitespace strings

3801

$sql = "SELECT COALESCE(null, '', null) AS test" . $DB->sql_null_from_clause();

3802

$this->assertEquals('', $DB->get_field_sql($sql, array()));

3783

// Check there are not problems with whitespace strings.

3803

3784

$sql = "SELECT COALESCE(null, :paramvalue, null) AS test" . $DB->sql_null_from_clause();

3804

$this->assertEquals('', $DB->get_field_sql($sql, array('paramvalue' => '')));

3785

$this->assertSame('', $DB->get_field_sql($sql, array('paramvalue' => '')));

3805

3786

}

3806

3787

3807

function test_sql_concat() {

3788

public function test_sql_concat() {

3808

3789

$DB = $this->tdb;

3809

3790

$dbman = $DB->get_manager();

3810

3791

3811

// Testing all sort of values

3792

// Testing all sort of values.

3812

3793

$sql = "SELECT ".$DB->sql_concat("?", "?", "?")." AS fullname ". $DB->sql_null_from_clause();

3813

// string, some unicode chars

3794

// String, some unicode chars.

3814

3795

$params = array('name', 'áéíóú', 'name3');

3815

$this->assertEquals('nameáéíóúname3', $DB->get_field_sql($sql, $params));

3816

// string, spaces and numbers

3796

$this->assertSame('nameáéíóúname3', $DB->get_field_sql($sql, $params));

3797

// String, spaces and numbers.

3817

3798

$params = array('name', ' ', 12345);

3818

$this->assertEquals('name 12345', $DB->get_field_sql($sql, $params));

3819

// float, empty and strings

3799

$this->assertSame('name 12345', $DB->get_field_sql($sql, $params));

3800

// Float, empty and strings.

3820

3801

$params = array(123.45, '', 'test');

3821

$this->assertEquals('123.45test', $DB->get_field_sql($sql, $params));

3822

// only integers

3802

$this->assertSame('123.45test', $DB->get_field_sql($sql, $params));

3803

// Only integers.

3823

3804

$params = array(12, 34, 56);

3824

$this->assertEquals('123456', $DB->get_field_sql($sql, $params));

3825

// float, null and strings

3805

$this->assertSame('123456', $DB->get_field_sql($sql, $params));

3806

// Float, null and strings.

3826

3807

$params = array(123.45, null, 'test');

3827

$this->assertNull($DB->get_field_sql($sql, $params)); // Concatenate NULL with anything result = NULL

3808

$this->assertNull($DB->get_field_sql($sql, $params)); // Concatenate null with anything result = null.

3828

3809

3829

// Testing fieldnames + values and also integer fieldnames

3810

// Testing fieldnames + values and also integer fieldnames.

3830

3811

$table = $this->get_test_table();

3831

3812

$tablename = $table->getName();

3832

3813

3833

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3814

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3834

3815

$table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);

3835

3816

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

3836

3817

$dbman->create_table($table);

3839

3820

$DB->insert_record($tablename, array('description'=>'dxxx'));

3840

3821

$DB->insert_record($tablename, array('description'=>'bcde'));

3841

3822

3842

// fieldnames and values mixed

3823

// Fieldnames and values mixed.

3843

3824

$sql = 'SELECT id, ' . $DB->sql_concat('description', "'harcoded'", '?', '?') . ' AS result FROM {' . $tablename . '}';

3844

3825

$records = $DB->get_records_sql($sql, array(123.45, 'test'));

3845

$this->assertEquals(count($records), 3);

3846

$this->assertEquals($records[1]->result, 'áéíóúharcoded123.45test');

3847

// integer fieldnames and values

3826

$this->assertCount(3, $records);

3827

$this->assertSame('áéíóúharcoded123.45test', $records[1]->result);

3828

// Integer fieldnames and values.

3848

3829

$sql = 'SELECT id, ' . $DB->sql_concat('id', "'harcoded'", '?', '?') . ' AS result FROM {' . $tablename . '}';

3849

3830

$records = $DB->get_records_sql($sql, array(123.45, 'test'));

3850

$this->assertEquals(count($records), 3);

3851

$this->assertEquals($records[1]->result, '1harcoded123.45test');

3852

// all integer fieldnames

3831

$this->assertCount(3, $records);

3832

$this->assertSame('1harcoded123.45test', $records[1]->result);

3833

// All integer fieldnames.

3853

3834

$sql = 'SELECT id, ' . $DB->sql_concat('id', 'id', 'id') . ' AS result FROM {' . $tablename . '}';

3854

3835

$records = $DB->get_records_sql($sql, array());

3855

$this->assertEquals(count($records), 3);

3856

$this->assertEquals($records[1]->result, '111');

3836

$this->assertCount(3, $records);

3837

$this->assertSame('111', $records[1]->result);

3857

3838

3858

3839

}

3859

3840

3860

function test_concat_join() {

3841

public function test_concat_join() {

3861

3842

$DB = $this->tdb;

3862

3843

$sql = "SELECT ".$DB->sql_concat_join("' '", array("?", "?", "?"))." AS fullname ".$DB->sql_null_from_clause();

3863

3844

$params = array("name", "name2", "name3");

3865

3846

$this->assertEquals("name name2 name3", $result);

3866

3847

}

3867

3848

3868

function test_sql_fullname() {

3849

public function test_sql_fullname() {

3869

3850

$DB = $this->tdb;

3870

3851

$sql = "SELECT ".$DB->sql_fullname(':first', ':last')." AS fullname ".$DB->sql_null_from_clause();

3871

3852

$params = array('first'=>'Firstname', 'last'=>'Surname');

3872

3853

$this->assertEquals("Firstname Surname", $DB->get_field_sql($sql, $params));

3873

3854

}

3874

3855

3875

function test_sql_order_by_text() {

3856

public function test_sql_order_by_text() {

3876

3857

$DB = $this->tdb;

3877

3858

$dbman = $DB->get_manager();

3878

3859

3879

3860

$table = $this->get_test_table();

3880

3861

$tablename = $table->getName();

3881

3862

3882

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3863

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3883

3864

$table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);

3884

3865

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

3885

3866

$dbman->create_table($table);

3898

3879

$this->assertEquals(2, $last->id);

3899

3880

}

3900

3881

3901

function test_sql_substring() {

3882

public function test_sql_substring() {

3902

3883

$DB = $this->tdb;

3903

3884

$dbman = $DB->get_manager();

3904

3885

3905

3886

$table = $this->get_test_table();

3906

3887

$tablename = $table->getName();

3907

3888

3908

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3889

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3909

3890

$table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);

3910

3891

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

3911

3892

$dbman->create_table($table);

3923

3904

$this->assertEquals(substr($string, 5-1, 2), $record->name);

3924

3905

3925

3906

try {

3926

// silence php warning ;-)

3907

// Silence php warning.

3927

3908

@$DB->sql_substr("name");

3928

3909

$this->fail("Expecting an exception, none occurred");

3929

} catch (Exception $e) {

3930

$this->assertTrue($e instanceof coding_exception);

3910

} catch (moodle_exception $e) {

3911

$this->assertInstanceOf('coding_exception', $e);

3931

3912

}

3932

3913

}

3933

3914

3934

function test_sql_length() {

3915

public function test_sql_length() {

3935

3916

$DB = $this->tdb;

3936

3917

$this->assertEquals($DB->get_field_sql(

3937

3918

"SELECT ".$DB->sql_length("'aeiou'").$DB->sql_null_from_clause()), 5);

3939

3920

"SELECT ".$DB->sql_length("'áéíóú'").$DB->sql_null_from_clause()), 5);

3940

3921

}

3941

3922

3942

function test_sql_position() {

3923

public function test_sql_position() {

3943

3924

$DB = $this->tdb;

3944

3925

$this->assertEquals($DB->get_field_sql(

3945

3926

"SELECT ".$DB->sql_position("'ood'", "'Moodle'").$DB->sql_null_from_clause()), 2);

3947

3928

"SELECT ".$DB->sql_position("'Oracle'", "'Moodle'").$DB->sql_null_from_clause()), 0);

3948

3929

}

3949

3930

3950

function test_sql_empty() {

3931

public function test_sql_empty() {

3951

3932

$DB = $this->tdb;

3952

3933

$dbman = $DB->get_manager();

3953

3934

3957

3938

$this->assertSame('', $DB->sql_empty()); // Since 2.5 the hack is applied automatically to all bound params.

3958

3939

$this->assertDebuggingCalled();

3959

3940

3960

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3941

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3961

3942

$table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);

3962

3943

$table->add_field('namenotnull', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'default value');

3963

3944

$table->add_field('namenotnullnodeflt', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);

3970

3951

$DB->insert_record($tablename, array('name'=>0));

3971

3952

3972

3953

$records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE name = ?", array(''));

3973

$this->assertEquals(count($records), 1);

3954

$this->assertCount(1, $records);

3974

3955

$record = reset($records);

3975

$this->assertEquals($record->name, '');

3956

$this->assertSame('', $record->name);

3976

3957

3977

3958

$records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE namenotnull = ?", array(''));

3978

$this->assertEquals(count($records), 1);

3959

$this->assertCount(1, $records);

3979

3960

$record = reset($records);

3980

$this->assertEquals($record->namenotnull, '');

3961

$this->assertSame('', $record->namenotnull);

3981

3962

3982

3963

$records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE namenotnullnodeflt = ?", array(''));

3983

$this->assertEquals(count($records), 4);

3964

$this->assertCount(4, $records);

3984

3965

$record = reset($records);

3985

$this->assertEquals($record->namenotnullnodeflt, '');

3966

$this->assertSame('', $record->namenotnullnodeflt);

3986

3967

}

3987

3968

3988

function test_sql_isempty() {

3969

public function test_sql_isempty() {

3989

3970

$DB = $this->tdb;

3990

3971

$dbman = $DB->get_manager();

3991

3972

3992

3973

$table = $this->get_test_table();

3993

3974

$tablename = $table->getName();

3994

3975

3995

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3976

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

3996

3977

$table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);

3997

3978

$table->add_field('namenull', XMLDB_TYPE_CHAR, '255', null, null, null, null);

3998

3979

$table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL, null, null);

4006

3987

$DB->insert_record($tablename, array('name'=>0, 'namenull'=>0, 'description'=>0, 'descriptionnull'=>0));

4007

3988

4008

3989

$records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isempty($tablename, 'name', false, false));

4009

$this->assertEquals(count($records), 1);

3990

$this->assertCount(1, $records);

4010

3991

$record = reset($records);

4011

$this->assertEquals($record->name, '');

3992

$this->assertSame('', $record->name);

4012

3993

4013

3994

$records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isempty($tablename, 'namenull', true, false));

4014

$this->assertEquals(count($records), 1);

3995

$this->assertCount(1, $records);

4015

3996

$record = reset($records);

4016

$this->assertEquals($record->namenull, '');

3997

$this->assertSame('', $record->namenull);

4017

3998

4018

3999

$records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isempty($tablename, 'description', false, true));

4019

$this->assertEquals(count($records), 1);

4000

$this->assertCount(1, $records);

4020

4001

$record = reset($records);

4021

$this->assertEquals($record->description, '');

4002

$this->assertSame('', $record->description);

4022

4003

4023

4004

$records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isempty($tablename, 'descriptionnull', true, true));

4024

$this->assertEquals(count($records), 1);

4005

$this->assertCount(1, $records);

4025

4006

$record = reset($records);

4026

$this->assertEquals($record->descriptionnull, '');

4007

$this->assertSame('', $record->descriptionnull);

4027

4008

}

4028

4009

4029

function test_sql_isnotempty() {

4010

public function test_sql_isnotempty() {

4030

4011

$DB = $this->tdb;

4031

4012

$dbman = $DB->get_manager();

4032

4013

4033

4014

$table = $this->get_test_table();

4034

4015

$tablename = $table->getName();

4035

4016

4036

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4017

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4037

4018

$table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);

4038

4019

$table->add_field('namenull', XMLDB_TYPE_CHAR, '255', null, null, null, null);

4039

4020

$table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL, null, null);

4047

4028

$DB->insert_record($tablename, array('name'=>0, 'namenull'=>0, 'description'=>0, 'descriptionnull'=>0));

4048

4029

4049

4030

$records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isnotempty($tablename, 'name', false, false));

4050

$this->assertEquals(count($records), 3);

4031

$this->assertCount(3, $records);

4051

4032

$record = reset($records);

4052

$this->assertEquals($record->name, '??');

4033

$this->assertSame('??', $record->name);

4053

4034

4054

4035

$records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isnotempty($tablename, 'namenull', true, false));

4055

$this->assertEquals(count($records), 2); // nulls aren't comparable (so they aren't "not empty"). SQL expected behaviour

4036

$this->assertCount(2, $records); // Nulls aren't comparable (so they aren't "not empty"). SQL expected behaviour.

4056

4037

$record = reset($records);

4057

$this->assertEquals($record->namenull, 'la'); // so 'la' is the first non-empty 'namenull' record

4038

$this->assertSame('la', $record->namenull); // So 'la' is the first non-empty 'namenull' record.

4058

4039

4059

4040

$records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isnotempty($tablename, 'description', false, true));

4060

$this->assertEquals(count($records), 3);

4041

$this->assertCount(3, $records);

4061

4042

$record = reset($records);

4062

$this->assertEquals($record->description, '??');

4043

$this->assertSame('??', $record->description);

4063

4044

4064

4045

$records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isnotempty($tablename, 'descriptionnull', true, true));

4065

$this->assertEquals(count($records), 2); // nulls aren't comparable (so they aren't "not empty"). SQL expected behaviour

4046

$this->assertCount(2, $records); // Nulls aren't comparable (so they aren't "not empty"). SQL expected behaviour.

4066

4047

$record = reset($records);

4067

$this->assertEquals($record->descriptionnull, 'lalala'); // so 'lalala' is the first non-empty 'descriptionnull' record

4048

$this->assertSame('lalala', $record->descriptionnull); // So 'lalala' is the first non-empty 'descriptionnull' record.

4068

4049

}

4069

4050

4070

function test_sql_regex() {

4051

public function test_sql_regex() {

4071

4052

$DB = $this->tdb;

4072

4053

$dbman = $DB->get_manager();

4073

4054

4074

4055

$table = $this->get_test_table();

4075

4056

$tablename = $table->getName();

4076

4057

4077

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4058

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4078

4059

$table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);

4079

4060

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

4080

4061

$dbman->create_table($table);

4087

4068

$params = array('a$');

4088

4069

if ($DB->sql_regex_supported()) {

4089

4070

$records = $DB->get_records_sql($sql, $params);

4090

$this->assertEquals(count($records), 2);

4071

$this->assertCount(2, $records);

4091

4072

} else {

4092

4073

$this->assertTrue(true, 'Regexp operations not supported. Test skipped');

4093

4074

}

4096

4077

$params = array('.a');

4097

4078

if ($DB->sql_regex_supported()) {

4098

4079

$records = $DB->get_records_sql($sql, $params);

4099

$this->assertEquals(count($records), 1);

4080

$this->assertCount(1, $records);

4100

4081

} else {

4101

4082

$this->assertTrue(true, 'Regexp operations not supported. Test skipped');

4102

4083

}

4114

4095

$table = $this->get_test_table();

4115

4096

$tablename = $table->getName();

4116

4097

4117

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4118

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

4098

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4099

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

4119

4100

$table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);

4120

$table->add_field('content', XMLDB_TYPE_TEXT, 'big', XMLDB_UNSIGNED, XMLDB_NOTNULL);

4101

$table->add_field('content', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL);

4121

4102

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

4122

4103

$dbman->create_table($table);

4123

4104

4126

4107

$DB->insert_record($tablename, array('course' => 5, 'content' => 'hello', 'name'=>'def'));

4127

4108

$DB->insert_record($tablename, array('course' => 2, 'content' => 'universe', 'name'=>'abc'));

4128

4109

4129

// test grouping by expressions in the query. MDL-26819. Note that there are 4 ways:

4110

// Test grouping by expressions in the query. MDL-26819. Note that there are 4 ways:

4130

4111

// - By column position (GROUP by 1) - Not supported by mssql & oracle

4131

4112

// - By column name (GROUP by course) - Supported by all, but leading to wrong results

4132

4113

// - By column alias (GROUP by casecol) - Not supported by mssql & oracle

4143

4124

$records = $DB->get_records_sql($sql, null);

4144

4125

$this->assertEquals($result, $records);

4145

4126

4146

// another grouping by CASE expression just to ensure it works ok for multiple WHEN

4127

// Another grouping by CASE expression just to ensure it works ok for multiple WHEN.

4147

4128

$sql = "SELECT CASE name

4148

4129

WHEN 'xyz' THEN 'last'

4149

4130

WHEN 'def' THEN 'mid'

4169

4150

$sql = "SELECT id, course, name

4170

4151

FROM {{$tablename}}

4171

4152

ORDER BY CASE WHEN (course = 5 OR name = 'xyz') THEN 0 ELSE 1 END, name, course";

4172

// First, records matching the course = 5 OR name = 'xyz', then the rest. Each

4153

// First, records matching the course = 5 OR name = 'xyz', then the rest. Each.

4173

4154

// group ordered by name and course.

4174

4155

$result = array(

4175

4156

3 => (object)array('id' => 3, 'course' => 5, 'name' => 'def'),

4181

4162

// Verify also array keys, order is important in this test.

4182

4163

$this->assertEquals(array_keys($result), array_keys($records));

4183

4164

4184

// test limits in queries with DISTINCT/ALL clauses and multiple whitespace. MDL-25268

4165

// Test limits in queries with DISTINCT/ALL clauses and multiple whitespace. MDL-25268.

4185

4166

$sql = "SELECT DISTINCT course

4186

4167

FROM {{$tablename}}

4187

4168

ORDER BY course";

4188

// only limitfrom

4169

// Only limitfrom.

4189

4170

$records = $DB->get_records_sql($sql, null, 1);

4190

$this->assertEquals(2, count($records));

4171

$this->assertCount(2, $records);

4191

4172

$this->assertEquals(3, reset($records)->course);

4192

4173

$this->assertEquals(5, next($records)->course);

4193

// only limitnum

4174

// Only limitnum.

4194

4175

$records = $DB->get_records_sql($sql, null, 0, 2);

4195

$this->assertEquals(2, count($records));

4176

$this->assertCount(2, $records);

4196

4177

$this->assertEquals(2, reset($records)->course);

4197

4178

$this->assertEquals(3, next($records)->course);

4198

// both limitfrom and limitnum

4179

// Both limitfrom and limitnum.

4199

4180

$records = $DB->get_records_sql($sql, null, 2, 2);

4200

$this->assertEquals(1, count($records));

4181

$this->assertCount(1, $records);

4201

4182

$this->assertEquals(5, reset($records)->course);

4202

4183

4203

// we have sql like this in moodle, this syntax breaks on older versions of sqlite for example..

4184

// We have sql like this in moodle, this syntax breaks on older versions of sqlite for example..

4204

4185

$sql = "SELECT a.id AS id, a.course AS course

4205

4186

FROM {{$tablename}} a

4206

4187

JOIN (SELECT * FROM {{$tablename}}) b ON a.id = b.id

4207

4188

WHERE a.course = ?";

4208

4189

4209

4190

$records = $DB->get_records_sql($sql, array(3));

4210

$this->assertEquals(2, count($records));

4191

$this->assertCount(2, $records);

4211

4192

$this->assertEquals(1, reset($records)->id);

4212

4193

$this->assertEquals(2, next($records)->id);

4213

4194

4214

// do NOT try embedding sql_xxxx() helper functions in conditions array of count_records(), they don't break params/binding!

4195

// Do NOT try embedding sql_xxxx() helper functions in conditions array of count_records(), they don't break params/binding!

4215

4196

$count = $DB->count_records_select($tablename, "course = :course AND ".$DB->sql_compare_text('content')." = :content", array('course' => 3, 'content' => 'hello'));

4216

4197

$this->assertEquals(1, $count);

4217

4198

4218

// test int x string comparison

4199

// Test int x string comparison.

4219

4200

$sql = "SELECT *

4220

4201

FROM {{$tablename}} c

4221

4202

WHERE name = ?";

4222

$this->assertEquals(count($DB->get_records_sql($sql, array(10))), 0);

4223

$this->assertEquals(count($DB->get_records_sql($sql, array("10"))), 0);

4203

$this->assertCount(0, $DB->get_records_sql($sql, array(10)));

4204

$this->assertCount(0, $DB->get_records_sql($sql, array("10")));

4224

4205

$DB->insert_record($tablename, array('course' => 7, 'content' => 'xx', 'name'=>'1'));

4225

4206

$DB->insert_record($tablename, array('course' => 7, 'content' => 'yy', 'name'=>'2'));

4226

$this->assertEquals(count($DB->get_records_sql($sql, array(1))), 1);

4227

$this->assertEquals(count($DB->get_records_sql($sql, array("1"))), 1);

4228

$this->assertEquals(count($DB->get_records_sql($sql, array(10))), 0);

4229

$this->assertEquals(count($DB->get_records_sql($sql, array("10"))), 0);

4207

$this->assertCount(1, $DB->get_records_sql($sql, array(1)));

4208

$this->assertCount(1, $DB->get_records_sql($sql, array("1")));

4209

$this->assertCount(0, $DB->get_records_sql($sql, array(10)));

4210

$this->assertCount(0, $DB->get_records_sql($sql, array("10")));

4230

4211

$DB->insert_record($tablename, array('course' => 7, 'content' => 'xx', 'name'=>'1abc'));

4231

$this->assertEquals(count($DB->get_records_sql($sql, array(1))), 1);

4232

$this->assertEquals(count($DB->get_records_sql($sql, array("1"))), 1);

4212

$this->assertCount(1, $DB->get_records_sql($sql, array(1)));

4213

$this->assertCount(1, $DB->get_records_sql($sql, array("1")));

4233

4214

4234

4215

// Test get_in_or_equal() with a big number of elements. Note that ideally

4235

4216

// we should be detecting and warning about any use over, say, 200 elements

4236

// and recommend to change code to use subqueries and/or chunks instead.

4217

// And recommend to change code to use subqueries and/or chunks instead.

4237

4218

$currentcount = $DB->count_records($tablename);

4238

4219

$numelements = 10000; // Verify that we can handle 10000 elements (crazy!)

4239

4220

$values = range(1, $numelements);

4243

4224

FROM {{$tablename}}

4244

4225

WHERE id $insql";

4245

4226

$results = $DB->get_records_sql($sql, $inparams);

4246

$this->assertEquals($currentcount, count($results));

4227

$this->assertCount($currentcount, $results);

4247

4228

4248

4229

list($insql, $inparams) = $DB->get_in_or_equal($values, SQL_PARAMS_NAMED); // With NAMED params.

4249

4230

$sql = "SELECT *

4250

4231

FROM {{$tablename}}

4251

4232

WHERE id $insql";

4252

4233

$results = $DB->get_records_sql($sql, $inparams);

4253

$this->assertEquals($currentcount, count($results));

4254

}

4255

4256

function test_onelevel_commit() {

4257

$DB = $this->tdb;

4258

$dbman = $DB->get_manager();

4259

4260

$table = $this->get_test_table();

4261

$tablename = $table->getName();

4262

4263

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4264

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

4234

$this->assertCount($currentcount, $results);

4235

}

4236

4237

public function test_replace_all_text() {

4238

$DB = $this->tdb;

4239

$dbman = $DB->get_manager();

4240

4241

if (!$DB->replace_all_text_supported()) {

4242

$this->markTestSkipped($DB->get_name().' does not support replacing of texts');

4243

}

4244

4245

$table = $this->get_test_table();

4246

$tablename = $table->getName();

4247

4248

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4249

$table->add_field('name', XMLDB_TYPE_CHAR, '20', null, null);

4250

$table->add_field('intro', XMLDB_TYPE_TEXT, 'big', null, null);

4251

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

4252

$dbman->create_table($table);

4253

4254

$id1 = (string)$DB->insert_record($tablename, array('name' => null, 'intro' => null));

4255

$id2 = (string)$DB->insert_record($tablename, array('name' => '', 'intro' => ''));

4256

$id3 = (string)$DB->insert_record($tablename, array('name' => 'xxyy', 'intro' => 'vvzz'));

4257

$id4 = (string)$DB->insert_record($tablename, array('name' => 'aa bb aa bb', 'intro' => 'cc dd cc aa'));

4258

$id5 = (string)$DB->insert_record($tablename, array('name' => 'kkllll', 'intro' => 'kkllll'));

4259

4260

$expected = $DB->get_records($tablename, array(), 'id ASC');

4261

4262

$columns = $DB->get_columns($tablename);

4263

4264

$DB->replace_all_text($tablename, $columns['name'], 'aa', 'o');

4265

$result = $DB->get_records($tablename, array(), 'id ASC');

4266

$expected[$id4]->name = 'o bb o bb';

4267

$this->assertEquals($expected, $result);

4268

4269

$DB->replace_all_text($tablename, $columns['intro'], 'aa', 'o');

4270

$result = $DB->get_records($tablename, array(), 'id ASC');

4271

$expected[$id4]->intro = 'cc dd cc o';

4272

$this->assertEquals($expected, $result);

4273

4274

$DB->replace_all_text($tablename, $columns['name'], '_', '*');

4275

$DB->replace_all_text($tablename, $columns['name'], '?', '*');

4276

$DB->replace_all_text($tablename, $columns['name'], '%', '*');

4277

$DB->replace_all_text($tablename, $columns['intro'], '_', '*');

4278

$DB->replace_all_text($tablename, $columns['intro'], '?', '*');

4279

$DB->replace_all_text($tablename, $columns['intro'], '%', '*');

4280

$result = $DB->get_records($tablename, array(), 'id ASC');

4281

$this->assertEquals($expected, $result);

4282

4283

$long = '1234567890123456789';

4284

$DB->replace_all_text($tablename, $columns['name'], 'kk', $long);

4285

$result = $DB->get_records($tablename, array(), 'id ASC');

4286

$expected[$id5]->name = core_text::substr($long.'llll', 0, 20);

4287

$this->assertEquals($expected, $result);

4288

4289

$DB->replace_all_text($tablename, $columns['intro'], 'kk', $long);

4290

$result = $DB->get_records($tablename, array(), 'id ASC');

4291

$expected[$id5]->intro = $long.'llll';

4292

$this->assertEquals($expected, $result);

4293

}

4294

4295

public function test_onelevel_commit() {

4296

$DB = $this->tdb;

4297

$dbman = $DB->get_manager();

4298

4299

$table = $this->get_test_table();

4300

$tablename = $table->getName();

4301

4302

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4303

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

4265

4304

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

4266

4305

$dbman->create_table($table);

4267

4306

4274

4313

$this->assertEquals(1, $DB->count_records($tablename));

4275

4314

}

4276

4315

4277

function test_transaction_ignore_error_trouble() {

4316

public function test_transaction_ignore_error_trouble() {

4278

4317

$DB = $this->tdb;

4279

4318

$dbman = $DB->get_manager();

4280

4319

4311

4350

$this->assertEquals(1, $DB->count_records($tablename));

4312

4351

try {

4313

4352

$DB->get_records_sql('s e l e c t');

4314

} catch (Exception $e) {

4353

} catch (moodle_exception $e) {

4315

4354

// This must be ignored and it must not roll back the whole transaction.

4316

4355

}

4317

4356

$DB->insert_record($tablename, (object)array('course'=>2));

4328

4367

$this->assertEquals(1, $DB->count_records($tablename));

4329

4368

try {

4330

4369

$DB->execute('xxxx');

4331

} catch (Exception $e) {

4370

} catch (moodle_exception $e) {

4332

4371

// This must be ignored and it must not roll back the whole transaction.

4333

4372

}

4334

4373

$DB->insert_record($tablename, (object)array('course'=>2));

4345

4384

$this->assertEquals(1, $DB->count_records($tablename));

4346

4385

try {

4347

4386

$DB->change_database_structure('xxxx');

4348

} catch (Exception $e) {

4387

} catch (moodle_exception $e) {

4349

4388

// This must be ignored and it must not roll back the whole transaction.

4350

4389

}

4351

4390

$DB->insert_record($tablename, (object)array('course'=>2));

4357

4396

// NOTE: SQL_QUERY_STRUCTURE is intentionally not tested here because it should never fail.

4358

4397

}

4359

4398

4360

function test_onelevel_rollback() {

4399

public function test_onelevel_rollback() {

4361

4400

$DB = $this->tdb;

4362

4401

$dbman = $DB->get_manager();

4363

4402

4364

4403

$table = $this->get_test_table();

4365

4404

$tablename = $table->getName();

4366

4405

4367

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4368

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

4406

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4407

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

4369

4408

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

4370

4409

$dbman->create_table($table);

4371

4410

4372

// this might in fact encourage ppl to migrate from myisam to innodb

4411

// This might in fact encourage ppl to migrate from myisam to innodb.

4373

4412

4374

4413

$transaction = $DB->start_delegated_transaction();

4375

4414

$data = (object)array('course'=>3);

4380

4419

$transaction->rollback(new Exception('test'));

4381

4420

$this->fail('transaction rollback must rethrow exception');

4382

4421

} catch (Exception $e) {

4422

// Ignored.

4383

4423

}

4384

4424

$this->assertEquals(0, $DB->count_records($tablename));

4385

4425

}

4386

4426

4387

function test_nested_transactions() {

4427

public function test_nested_transactions() {

4388

4428

$DB = $this->tdb;

4389

4429

$dbman = $DB->get_manager();

4390

4430

4391

4431

$table = $this->get_test_table();

4392

4432

$tablename = $table->getName();

4393

4433

4394

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4395

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

4434

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4435

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

4396

4436

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

4397

4437

$dbman->create_table($table);

4398

4438

4399

// two level commit

4439

// Two level commit.

4400

4440

$this->assertFalse($DB->is_transaction_started());

4401

4441

$transaction1 = $DB->start_delegated_transaction();

4402

4442

$this->assertTrue($DB->is_transaction_started());

4413

4453

4414

4454

$DB->delete_records($tablename);

4415

4455

4416

// rollback from top level

4456

// Rollback from top level.

4417

4457

$transaction1 = $DB->start_delegated_transaction();

4418

4458

$data = (object)array('course'=>3);

4419

4459

$DB->insert_record($tablename, $data);

4431

4471

4432

4472

$DB->delete_records($tablename);

4433

4473

4434

// rollback from nested level

4474

// Rollback from nested level.

4435

4475

$transaction1 = $DB->start_delegated_transaction();

4436

4476

$data = (object)array('course'=>3);

4437

4477

$DB->insert_record($tablename, $data);

4444

4484

} catch (Exception $e) {

4445

4485

$this->assertEquals(get_class($e), 'Exception');

4446

4486

}

4447

$this->assertEquals(2, $DB->count_records($tablename)); // not rolled back yet

4487

$this->assertEquals(2, $DB->count_records($tablename)); // Not rolled back yet.

4448

4488

try {

4449

4489

$transaction1->allow_commit();

4450

} catch (Exception $e) {

4451

$this->assertEquals(get_class($e), 'dml_transaction_exception');

4490

} catch (moodle_exception $e) {

4491

$this->assertInstanceOf('dml_transaction_exception', $e);

4452

4492

}

4453

$this->assertEquals(2, $DB->count_records($tablename)); // not rolled back yet

4454

// the forced rollback is done from the default_exception handler and similar places,

4455

// let's do it manually here

4493

$this->assertEquals(2, $DB->count_records($tablename)); // Not rolled back yet.

4494

// The forced rollback is done from the default_exception handler and similar places,

4495

// let's do it manually here.

4456

4496

$this->assertTrue($DB->is_transaction_started());

4457

4497

$DB->force_transaction_rollback();

4458

4498

$this->assertFalse($DB->is_transaction_started());

4459

$this->assertEquals(0, $DB->count_records($tablename)); // finally rolled back

4499

$this->assertEquals(0, $DB->count_records($tablename)); // Finally rolled back.

4460

4500

4461

4501

$DB->delete_records($tablename);

4462

4502

4464

4504

$table2 = $this->get_test_table('2');

4465

4505

$tablename2 = $table2->getName();

4466

4506

4467

$table2->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4468

$table2->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

4507

$table2->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4508

$table2->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

4469

4509

$table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

4470

4510

$dbman->create_table($table2);

4471

4511

4527

4567

$this->assertEquals(3, $i);

4528

4568

}

4529

4569

4530

function test_transactions_forbidden() {

4570

public function test_transactions_forbidden() {

4531

4571

$DB = $this->tdb;

4532

4572

$dbman = $DB->get_manager();

4533

4573

4534

4574

$table = $this->get_test_table();

4535

4575

$tablename = $table->getName();

4536

4576

4537

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4538

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

4577

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4578

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

4539

4579

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

4540

4580

$dbman->create_table($table);

4541

4581

4545

4585

$DB->insert_record($tablename, $data);

4546

4586

try {

4547

4587

$DB->transactions_forbidden();

4548

} catch (Exception $e) {

4549

$this->assertEquals(get_class($e), 'dml_transaction_exception');

4588

} catch (moodle_exception $e) {

4589

$this->assertInstanceOf('dml_transaction_exception', $e);

4550

4590

}

4551

// the previous test does not force rollback

4591

// The previous test does not force rollback.

4552

4592

$transaction->allow_commit();

4553

4593

$this->assertFalse($DB->is_transaction_started());

4554

4594

$this->assertEquals(1, $DB->count_records($tablename));

4555

4595

}

4556

4596

4557

function test_wrong_transactions() {

4597

public function test_wrong_transactions() {

4558

4598

$DB = $this->tdb;

4559

4599

$dbman = $DB->get_manager();

4560

4600

4561

4601

$table = $this->get_test_table();

4562

4602

$tablename = $table->getName();

4563

4603

4564

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4565

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

4604

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4605

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

4566

4606

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

4567

4607

$dbman->create_table($table);

4568

4608

4569

4570

// wrong order of nested commits

4609

// Wrong order of nested commits.

4571

4610

$transaction1 = $DB->start_delegated_transaction();

4572

4611

$data = (object)array('course'=>3);

4573

4612

$DB->insert_record($tablename, $data);

4577

4616

try {

4578

4617

$transaction1->allow_commit();

4579

4618

$this->fail('wrong order of commits must throw exception');

4580

} catch (Exception $e) {

4581

$this->assertEquals(get_class($e), 'dml_transaction_exception');

4619

} catch (moodle_exception $e) {

4620

$this->assertInstanceOf('dml_transaction_exception', $e);

4582

4621

}

4583

4622

try {

4584

4623

$transaction2->allow_commit();

4585

4624

$this->fail('first wrong commit forces rollback');

4586

} catch (Exception $e) {

4587

$this->assertEquals(get_class($e), 'dml_transaction_exception');

4625

} catch (moodle_exception $e) {

4626

$this->assertInstanceOf('dml_transaction_exception', $e);

4588

4627

}

4589

// this is done in default exception handler usually

4628

// This is done in default exception handler usually.

4590

4629

$this->assertTrue($DB->is_transaction_started());

4591

$this->assertEquals(2, $DB->count_records($tablename)); // not rolled back yet

4630

$this->assertEquals(2, $DB->count_records($tablename)); // Not rolled back yet.

4592

4631

$DB->force_transaction_rollback();

4593

4632

$this->assertEquals(0, $DB->count_records($tablename));

4594

4633

$DB->delete_records($tablename);

4595

4634

4596

4597

// wrong order of nested rollbacks

4635

// Wrong order of nested rollbacks.

4598

4636

$transaction1 = $DB->start_delegated_transaction();

4599

4637

$data = (object)array('course'=>3);

4600

4638

$DB->insert_record($tablename, $data);

4602

4640

$data = (object)array('course'=>4);

4603

4641

$DB->insert_record($tablename, $data);

4604

4642

try {

4605

// this first rollback should prevent all other rollbacks

4643

// This first rollback should prevent all other rollbacks.

4606

4644

$transaction1->rollback(new Exception('test'));

4607

4645

} catch (Exception $e) {

4608

4646

$this->assertEquals(get_class($e), 'Exception');

4614

4652

}

4615

4653

try {

4616

4654

$transaction1->rollback(new Exception('test'));

4617

} catch (Exception $e) {

4618

// the rollback was used already once, no way to use it again

4619

$this->assertEquals(get_class($e), 'dml_transaction_exception');

4655

} catch (moodle_exception $e) {

4656

$this->assertInstanceOf('dml_transaction_exception', $e);

4620

4657

}

4621

// this is done in default exception handler usually

4658

// This is done in default exception handler usually.

4622

4659

$this->assertTrue($DB->is_transaction_started());

4623

4660

$DB->force_transaction_rollback();

4624

4661

$DB->delete_records($tablename);

4625

4662

4626

4627

// unknown transaction object

4663

// Unknown transaction object.

4628

4664

$transaction1 = $DB->start_delegated_transaction();

4629

4665

$data = (object)array('course'=>3);

4630

4666

$DB->insert_record($tablename, $data);

4632

4668

try {

4633

4669

$transaction2->allow_commit();

4634

4670

$this->fail('foreign transaction must fail');

4635

} catch (Exception $e) {

4636

$this->assertEquals(get_class($e), 'dml_transaction_exception');

4671

} catch (moodle_exception $e) {

4672

$this->assertInstanceOf('dml_transaction_exception', $e);

4637

4673

}

4638

4674

try {

4639

4675

$transaction1->allow_commit();

4640

4676

$this->fail('first wrong commit forces rollback');

4641

} catch (Exception $e) {

4642

$this->assertEquals(get_class($e), 'dml_transaction_exception');

4677

} catch (moodle_exception $e) {

4678

$this->assertInstanceOf('dml_transaction_exception', $e);

4643

4679

}

4644

4680

$DB->force_transaction_rollback();

4645

4681

$DB->delete_records($tablename);

4646

4682

}

4647

4683

4648

function test_concurent_transactions() {

4684

public function test_concurent_transactions() {

4649

4685

// Notes about this test:

4650

4686

// 1- MySQL needs to use one engine with transactions support (InnoDB).

4651

4687

// 2- MSSQL needs to have enabled versioning for read committed

4656

4692

$table = $this->get_test_table();

4657

4693

$tablename = $table->getName();

4658

4694

4659

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4660

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

4695

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4696

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

4661

4697

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

4662

4698

$dbman->create_table($table);

4663

4699

4667

4703

$DB->insert_record($tablename, $data);

4668

4704

$this->assertEquals(1, $DB->count_records($tablename));

4669

4705

4670

//open second connection

4706

// Open second connection.

4671

4707

$cfg = $DB->export_dbconfig();

4672

4708

if (!isset($cfg->dboptions)) {

4673

4709

$cfg->dboptions = array();

4675

4711

$DB2 = moodle_database::get_driver_instance($cfg->dbtype, $cfg->dblibrary);

4676

4712

$DB2->connect($cfg->dbhost, $cfg->dbuser, $cfg->dbpass, $cfg->dbname, $cfg->prefix, $cfg->dboptions);

4677

4713

4678

// second instance should not see pending inserts

4714

// Second instance should not see pending inserts.

4679

4715

$this->assertEquals(0, $DB2->count_records($tablename));

4680

4716

$data = (object)array('course'=>2);

4681

4717

$DB2->insert_record($tablename, $data);

4682

4718

$this->assertEquals(1, $DB2->count_records($tablename));

4683

4719

4684

// first should see the changes done from second

4720

// First should see the changes done from second.

4685

4721

$this->assertEquals(2, $DB->count_records($tablename));

4686

4722

4687

// now commit and we should see it finally in second connections

4723

// Now commit and we should see it finally in second connections.

4688

4724

$transaction->allow_commit();

4689

4725

$this->assertEquals(2, $DB2->count_records($tablename));

4690

4726

4691

// let's try delete all is also working on (this checks MDL-29198)

4692

// initially both connections see all the records in the table (2)

4727

// Let's try delete all is also working on (this checks MDL-29198).

4728

// Initially both connections see all the records in the table (2).

4693

4729

$this->assertEquals(2, $DB->count_records($tablename));

4694

4730

$this->assertEquals(2, $DB2->count_records($tablename));

4695

4731

$transaction = $DB->start_delegated_transaction();

4696

4732

4697

// delete all from within transaction

4733

// Delete all from within transaction.

4698

4734

$DB->delete_records($tablename);

4699

4735

4700

// transactional $DB, sees 0 records now

4736

// Transactional $DB, sees 0 records now.

4701

4737

$this->assertEquals(0, $DB->count_records($tablename));

4702

4738

4703

// others ($DB2) get no changes yet

4739

// Others ($DB2) get no changes yet.

4704

4740

$this->assertEquals(2, $DB2->count_records($tablename));

4705

4741

4706

// now commit and we should see changes

4742

// Now commit and we should see changes.

4707

4743

$transaction->allow_commit();

4708

4744

$this->assertEquals(0, $DB2->count_records($tablename));

4709

4745

4714

4750

$DB = $this->tdb;

4715

4751

$dbman = $DB->get_manager();

4716

4752

4717

// Open second connection

4753

// Open second connection.

4718

4754

$cfg = $DB->export_dbconfig();

4719

4755

if (!isset($cfg->dboptions)) {

4720

4756

$cfg->dboptions = array();

4722

4758

$DB2 = moodle_database::get_driver_instance($cfg->dbtype, $cfg->dblibrary);

4723

4759

$DB2->connect($cfg->dbhost, $cfg->dbuser, $cfg->dbpass, $cfg->dbname, $cfg->prefix, $cfg->dboptions);

4724

4760

4725

// Testing that acquiring a lock effectively locks

4726

// Get a session lock on connection1

4761

// Testing that acquiring a lock effectively locks.

4762

// Get a session lock on connection1.

4727

4763

$rowid = rand(100, 200);

4728

4764

$timeout = 1;

4729

4765

$DB->get_session_lock($rowid, $timeout);

4730

4766

4731

// Try to get the same session lock on connection2

4767

// Try to get the same session lock on connection2.

4732

4768

try {

4733

4769

$DB2->get_session_lock($rowid, $timeout);

4734

$DB2->release_session_lock($rowid); // Should not be executed, but here for safety

4770

$DB2->release_session_lock($rowid); // Should not be executed, but here for safety.

4735

4771

$this->fail('An Exception is missing, expected due to session lock acquired.');

4736

} catch (exception $e) {

4737

$this->assertTrue($e instanceof dml_sessionwait_exception);

4738

$DB->release_session_lock($rowid); // Release lock on connection1

4772

} catch (moodle_exception $e) {

4773

$this->assertInstanceOf('dml_sessionwait_exception', $e);

4774

$DB->release_session_lock($rowid); // Release lock on connection1.

4739

4775

}

4740

4776

4741

// Testing that releasing a lock effectively frees

4742

// Get a session lock on connection1

4777

// Testing that releasing a lock effectively frees.

4778

// Get a session lock on connection1.

4743

4779

$rowid = rand(100, 200);

4744

4780

$timeout = 1;

4745

4781

$DB->get_session_lock($rowid, $timeout);

4746

// Release the lock on connection1

4782

// Release the lock on connection1.

4747

4783

$DB->release_session_lock($rowid);

4748

4784

4749

// Get the just released lock on connection2

4785

// Get the just released lock on connection2.

4750

4786

$DB2->get_session_lock($rowid, $timeout);

4751

// Release the lock on connection2

4787

// Release the lock on connection2.

4752

4788

$DB2->release_session_lock($rowid);

4753

4789

4754

4790

$DB2->dispose();

4761

4797

$table = $this->get_test_table();

4762

4798

$tablename = $table->getName();

4763

4799

4764

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4800

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4765

4801

$table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);

4766

$table->add_field('content', XMLDB_TYPE_TEXT, 'big', XMLDB_UNSIGNED, XMLDB_NOTNULL);

4802

$table->add_field('content', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL);

4767

4803

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

4768

4804

$dbman->create_table($table);

4769

4805

4774

4810

$this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'bb', 'content'=>2)));

4775

4811

$this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'cc', 'content'=>'sometext')));

4776

4812

4777

4778

// Conditions in CHAR columns

4813

// Conditions in CHAR columns.

4779

4814

$this->assertTrue($DB->record_exists($tablename, array('name'=>1)));

4780

4815

$this->assertTrue($DB->record_exists($tablename, array('name'=>'1')));

4781

4816

$this->assertFalse($DB->record_exists($tablename, array('name'=>111)));

4786

4821

FROM {{$tablename}}

4787

4822

WHERE name = ?";

4788

4823

$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, array(1)));

4789

$this->assertEquals(1, count($records));

4824

$this->assertCount(1, $records);

4790

4825

$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, array('1')));

4791

$this->assertEquals(1, count($records));

4826

$this->assertCount(1, $records);

4792

4827

$records = $DB->get_records_sql($sqlqm, array(222));

4793

$this->assertEquals(0, count($records));

4828

$this->assertCount(0, $records);

4794

4829

$sqlnamed = "SELECT *

4795

4830

FROM {{$tablename}}

4796

4831

WHERE name = :name";

4797

4832

$this->assertNotEmpty($records = $DB->get_records_sql($sqlnamed, array('name' => 2)));

4798

$this->assertEquals(1, count($records));

4833

$this->assertCount(1, $records);

4799

4834

$this->assertNotEmpty($records = $DB->get_records_sql($sqlnamed, array('name' => '2')));

4800

$this->assertEquals(1, count($records));

4835

$this->assertCount(1, $records);

4801

4836

4802

4837

// Conditions in TEXT columns always must be performed with the sql_compare_text

4803

// helper function on both sides of the condition

4838

// helper function on both sides of the condition.

4804

4839

$sqlqm = "SELECT *

4805

4840

FROM {{$tablename}}

4806

4841

WHERE " . $DB->sql_compare_text('content') . " = " . $DB->sql_compare_text('?');

4807

4842

$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, array('1')));

4808

$this->assertEquals(1, count($records));

4843

$this->assertCount(1, $records);

4809

4844

$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, array(1)));

4810

$this->assertEquals(1, count($records));

4845

$this->assertCount(1, $records);

4811

4846

$sqlnamed = "SELECT *

4812

4847

FROM {{$tablename}}

4813

4848

WHERE " . $DB->sql_compare_text('content') . " = " . $DB->sql_compare_text(':content');

4814

4849

$this->assertNotEmpty($records = $DB->get_records_sql($sqlnamed, array('content' => 2)));

4815

$this->assertEquals(1, count($records));

4850

$this->assertCount(1, $records);

4816

4851

$this->assertNotEmpty($records = $DB->get_records_sql($sqlnamed, array('content' => '2')));

4817

$this->assertEquals(1, count($records));

4852

$this->assertCount(1, $records);

4818

4853

}

4819

4854

4820

4855

public function test_bound_param_reserved() {

4824

4859

$table = $this->get_test_table();

4825

4860

$tablename = $table->getName();

4826

4861

4827

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4828

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');

4862

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4863

$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

4829

4864

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

4830

4865

$dbman->create_table($table);

4831

4866

4832

4867

$DB->insert_record($tablename, array('course' => '1'));

4833

4868

4834

// make sure reserved words do not cause fatal problems in query parameters

4869

// Make sure reserved words do not cause fatal problems in query parameters.

4835

4870

4836

4871

$DB->execute("UPDATE {{$tablename}} SET course = 1 WHERE id = :select", array('select'=>1));

4837

4872

$DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE course = :select", array('select'=>1));

4841

4876

$DB->set_field_select($tablename, 'course', '1', "id = :select", array('select'=>1));

4842

4877

$DB->delete_records_select($tablename, "id = :select", array('select'=>1));

4843

4878

4844

// if we get here test passed ok

4879

// If we get here test passed ok.

4845

4880

$this->assertTrue(true);

4846

4881

}

4847

4882

4849

4884

$DB = $this->tdb;

4850

4885

$dbman = $DB->get_manager();

4851

4886

4852

if (false) $DB = new moodle_database ();

4853

4854

4887

$table = $this->get_test_table();

4855

4888

$tablename = $table->getName();

4856

4889

4857

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4890

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

4858

4891

$table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);

4859

$table->add_field('content', XMLDB_TYPE_TEXT, 'big', XMLDB_UNSIGNED, XMLDB_NOTNULL);

4892

$table->add_field('content', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL);

4860

4893

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

4861

4894

$dbman->create_table($table);

4862

4895

4870

4903

$sqlqm = "SELECT *

4871

4904

FROM {{$tablename}}";

4872

4905

$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 4));

4873

$this->assertEquals(2, count($records));

4874

$this->assertEquals('e', reset($records)->name);

4875

$this->assertEquals('f', end($records)->name);

4906

$this->assertCount(2, $records);

4907

$this->assertSame('e', reset($records)->name);

4908

$this->assertSame('f', end($records)->name);

4876

4909

4877

4910

$sqlqm = "SELECT *

4878

4911

FROM {{$tablename}}";

4881

4914

$sqlqm = "SELECT *

4882

4915

FROM {{$tablename}}";

4883

4916

$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 0, 4));

4884

$this->assertEquals(4, count($records));

4885

$this->assertEquals('a', reset($records)->name);

4886

$this->assertEquals('d', end($records)->name);

4917

$this->assertCount(4, $records);

4918

$this->assertSame('a', reset($records)->name);

4919

$this->assertSame('d', end($records)->name);

4887

4920

4888

4921

$sqlqm = "SELECT *

4889

4922

FROM {{$tablename}}";

4890

4923

$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 0, 8));

4891

$this->assertEquals(6, count($records));

4892

$this->assertEquals('a', reset($records)->name);

4893

$this->assertEquals('f', end($records)->name);

4924

$this->assertCount(6, $records);

4925

$this->assertSame('a', reset($records)->name);

4926

$this->assertSame('f', end($records)->name);

4894

4927

4895

4928

$sqlqm = "SELECT *

4896

4929

FROM {{$tablename}}";

4897

4930

$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 1, 4));

4898

$this->assertEquals(4, count($records));

4899

$this->assertEquals('b', reset($records)->name);

4900

$this->assertEquals('e', end($records)->name);

4931

$this->assertCount(4, $records);

4932

$this->assertSame('b', reset($records)->name);

4933

$this->assertSame('e', end($records)->name);

4901

4934

4902

4935

$sqlqm = "SELECT *

4903

4936

FROM {{$tablename}}";

4904

4937

$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 4, 4));

4905

$this->assertEquals(2, count($records));

4906

$this->assertEquals('e', reset($records)->name);

4907

$this->assertEquals('f', end($records)->name);

4938

$this->assertCount(2, $records);

4939

$this->assertSame('e', reset($records)->name);

4940

$this->assertSame('f', end($records)->name);

4908

4941

4909

4942

$sqlqm = "SELECT t.*, t.name AS test

4910

4943

FROM {{$tablename}} t

4911

4944

ORDER BY t.id ASC";

4912

4945

$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 4, 4));

4913

$this->assertEquals(2, count($records));

4914

$this->assertEquals('e', reset($records)->name);

4915

$this->assertEquals('f', end($records)->name);

4946

$this->assertCount(2, $records);

4947

$this->assertSame('e', reset($records)->name);

4948

$this->assertSame('f', end($records)->name);

4916

4949

4917

4950

$sqlqm = "SELECT DISTINCT t.name, t.name AS test

4918

4951

FROM {{$tablename}} t

4919

4952

ORDER BY t.name DESC";

4920

4953

$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 4, 4));

4921

$this->assertEquals(2, count($records));

4922

$this->assertEquals('b', reset($records)->name);

4923

$this->assertEquals('a', end($records)->name);

4954

$this->assertCount(2, $records);

4955

$this->assertSame('b', reset($records)->name);

4956

$this->assertSame('a', end($records)->name);

4924

4957

4925

4958

$sqlqm = "SELECT 1

4926

4959

FROM {{$tablename}} t

4927

4960

WHERE t.name = 'a'";

4928

4961

$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 0, 1));

4929

$this->assertEquals(1, count($records));

4962

$this->assertCount(1, $records);

4930

4963

4931

4964

$sqlqm = "SELECT 'constant'

4932

4965

FROM {{$tablename}} t

4933

4966

WHERE t.name = 'a'";

4934

4967

$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 0, 8));

4935

$this->assertEquals(1, count($records));

4968

$this->assertCount(1, $records);

4936

4969

4937

4970

$this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'a', 'content'=>'one')));

4938

4971

$this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'b', 'content'=>'two')));

4947

4980

GROUP BY t.name

4948

4981

ORDER BY t.name ASC";

4949

4982

$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm));

4950

$this->assertEquals(6, count($records)); // a,b,c,d,e,f

4951

$this->assertEquals(2, reset($records)->count); // a has 2 records now

4952

$this->assertEquals(1, end($records)->count); // f has 1 record still

4983

$this->assertCount(6, $records); // a,b,c,d,e,f.

4984

$this->assertEquals(2, reset($records)->count); // a has 2 records now.

4985

$this->assertEquals(1, end($records)->count); // f has 1 record still.

4953

4986

4954

4987

$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 0, 2));

4955

$this->assertEquals(2, count($records));

4988

$this->assertCount(2, $records);

4956

4989

$this->assertEquals(2, reset($records)->count);

4957

4990

$this->assertEquals(2, end($records)->count);

4958

4991

}

4992

4993

public function test_queries_counter() {

4994

4995

$DB = $this->tdb;

4996

$dbman = $this->tdb->get_manager();

4997

4998

// Test database.

4999

$table = $this->get_test_table();

5000

$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

5001

$table->add_field('fieldvalue', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

5002

$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

5003

5004

$dbman->create_table($table);

5005

$tablename = $table->getName();

5006

5007

// Initial counters values.

5008

$initreads = $DB->perf_get_reads();

5009

$initwrites = $DB->perf_get_writes();

5010

$previousqueriestime = $DB->perf_get_queries_time();

5011

5012

// Selects counts as reads.

5013

5014

// The get_records_sql() method generates only 1 db query.

5015

$whatever = $DB->get_records_sql("SELECT * FROM {{$tablename}}");

5016

$this->assertEquals($initreads + 1, $DB->perf_get_reads());

5017

5018

// The get_records() method generates 2 queries the first time is called

5019

// as it is fetching the table structure.

5020

$whatever = $DB->get_records($tablename);

5021

$this->assertEquals($initreads + 3, $DB->perf_get_reads());

5022

$this->assertEquals($initwrites, $DB->perf_get_writes());

5023

5024

// The elapsed time is counted.

5025

$lastqueriestime = $DB->perf_get_queries_time();

5026

$this->assertGreaterThanOrEqual($previousqueriestime, $lastqueriestime);

5027

$previousqueriestime = $lastqueriestime;

5028

5029

// Only 1 now, it already fetched the table columns.

5030

$whatever = $DB->get_records($tablename);

5031

$this->assertEquals($initreads + 4, $DB->perf_get_reads());

5032

5033

// And only 1 more from now.

5034

$whatever = $DB->get_records($tablename);

5035

$this->assertEquals($initreads + 5, $DB->perf_get_reads());

5036

5037

// Inserts counts as writes.

5038

5039

$rec1 = new stdClass();

5040

$rec1->fieldvalue = 11;

5041

$rec1->id = $DB->insert_record($tablename, $rec1);

5042

$this->assertEquals($initwrites + 1, $DB->perf_get_writes());

5043

$this->assertEquals($initreads + 5, $DB->perf_get_reads());

5044

5045

// The elapsed time is counted.

5046

$lastqueriestime = $DB->perf_get_queries_time();

5047

$this->assertGreaterThanOrEqual($previousqueriestime, $lastqueriestime);

5048

$previousqueriestime = $lastqueriestime;

5049

5050

$rec2 = new stdClass();

5051

$rec2->fieldvalue = 22;

5052

$rec2->id = $DB->insert_record($tablename, $rec2);

5053

$this->assertEquals($initwrites + 2, $DB->perf_get_writes());

5054

5055

// Updates counts as writes.

5056

5057

$rec1->fieldvalue = 111;

5058

$DB->update_record($tablename, $rec1);

5059

$this->assertEquals($initwrites + 3, $DB->perf_get_writes());

5060

$this->assertEquals($initreads + 5, $DB->perf_get_reads());

5061

5062

// The elapsed time is counted.

5063

$lastqueriestime = $DB->perf_get_queries_time();

5064

$this->assertGreaterThanOrEqual($previousqueriestime, $lastqueriestime);

5065

$previousqueriestime = $lastqueriestime;

5066

5067

// Sum of them.

5068

$totaldbqueries = $DB->perf_get_reads() + $DB->perf_get_writes();

5069

$this->assertEquals($totaldbqueries, $DB->perf_get_queries());

5070

}

4959

5071

}

4960

5072

4961

5073

/**

4970

5082

return $this->fix_table_names($sql);

4971

5083

}

4972

5084

4973

public function driver_installed(){}

4974

public function get_dbfamily(){}

4975

protected function get_dbtype(){}

4976

protected function get_dblibrary(){}

4977

public function get_name(){}

4978

public function get_configuration_help(){}

4979

public function get_configuration_hints(){}

4980

public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null){}

4981

public function get_server_info(){}

4982

protected function allowed_param_types(){}

4983

public function get_last_error(){}

4984

public function get_tables($usecache=true){}

4985

public function get_indexes($table){}

4986

public function get_columns($table, $usecache=true){}

4987

protected function normalise_value($column, $value){}

4988

public function set_debug($state){}

4989

public function get_debug(){}

4990

public function set_logging($state){}

4991

public function change_database_structure($sql){}

4992

public function execute($sql, array $params=null){}

4993

public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}

4994

public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}

4995

public function get_fieldset_sql($sql, array $params=null){}

4996

public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false){}

4997

public function insert_record($table, $dataobject, $returnid=true, $bulk=false){}

4998

public function import_record($table, $dataobject){}

4999

public function update_record_raw($table, $params, $bulk=false){}

5000

public function update_record($table, $dataobject, $bulk=false){}

5001

public function set_field_select($table, $newfield, $newvalue, $select, array $params=null){}

5002

public function delete_records_select($table, $select, array $params=null){}

5003

public function sql_concat(){}

5004

public function sql_concat_join($separator="' '", $elements=array()){}

5005

public function sql_substr($expr, $start, $length=false){}

5085

public function driver_installed() {}

5086

public function get_dbfamily() {}

5087

protected function get_dbtype() {}

5088

protected function get_dblibrary() {}

5089

public function get_name() {}

5090

public function get_configuration_help() {}

5091

public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null) {}

5092

public function get_server_info() {}

5093

protected function allowed_param_types() {}

5094

public function get_last_error() {}

5095

public function get_tables($usecache=true) {}

5096

public function get_indexes($table) {}

5097

public function get_columns($table, $usecache=true) {}

5098

protected function normalise_value($column, $value) {}

5099

public function set_debug($state) {}

5100

public function get_debug() {}

5101

public function set_logging($state) {}

5102

public function change_database_structure($sql) {}

5103

public function execute($sql, array $params=null) {}

5104

public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {}

5105

public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {}

5106

public function get_fieldset_sql($sql, array $params=null) {}

5107

public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) {}

5108

public function insert_record($table, $dataobject, $returnid=true, $bulk=false) {}

5109

public function import_record($table, $dataobject) {}

5110

public function update_record_raw($table, $params, $bulk=false) {}

5111

public function update_record($table, $dataobject, $bulk=false) {}

5112

public function set_field_select($table, $newfield, $newvalue, $select, array $params=null) {}

5113

public function delete_records_select($table, $select, array $params=null) {}

5114

public function sql_concat() {}

5115

public function sql_concat_join($separator="' '", $elements=array()) {}

5116

public function sql_substr($expr, $start, $length=false) {}

5006

5117

public function begin_transaction() {}

5007

5118

public function commit_transaction() {}

5008

5119

public function rollback_transaction() {}

Older »