sqlcipher/test/alter.test at Xcode5 · sqlcipher/sqlcipher · GitHub

History

863 lines (824 loc) · 22.2 KB

Raw

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

641

642

643

644

645

646

647

648

649

650

651

652

653

654

655

656

657

658

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

674

675

676

677

678

679

680

681

682

683

684

685

686

687

688

689

690

691

692

693

694

695

696

697

698

699

700

701

702

703

704

705

706

707

708

709

710

711

712

713

714

715

716

717

718

719

720

721

722

723

724

725

726

727

728

729

730

731

732

733

734

735

736

737

738

739

740

741

742

743

744

745

746

747

748

749

750

751

752

753

754

755

756

757

758

759

760

761

762

763

764

765

766

767

768

769

770

771

772

773

774

775

776

777

778

779

780

781

782

783

784

785

786

787

788

789

790

791

792

793

794

795

796

797

798

799

800

801

802

803

804

805

806

807

808

809

810

811

812

813

814

815

816

817

818

819

820

821

822

823

824

825

826

827

828

829

830

831

832

833

834

835

836

837

838

839

840

841

842

843

844

845

846

847

848

849

850

851

852

853

854

855

856

857

858

859

860

861

862

863

# 2004 November 10

# The author disclaims copyright to this source code. In place of

# a legal notice, here is a blessing:

# May you do good and not evil.

# May you find forgiveness for yourself and forgive others.

# May you share freely, never taking more than you give.

#*************************************************************************

# This file implements regression tests for SQLite library. The

# focus of this script is testing the ALTER TABLE statement.

# $Id: alter.test,v 1.32 2009/03/24 15:08:10 drh Exp $

set testdir [file dirname $argv0]

source $testdir/tester.tcl

# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.

ifcapable !altertable {

finish_test

return

}

#----------------------------------------------------------------------

# Test organization:

# alter-1.1.* - alter-1.7.*: Basic tests of ALTER TABLE, including tables

# with implicit and explicit indices. These tests came from an earlier

# fork of SQLite that also supported ALTER TABLE.

# alter-1.8.*: Tests for ALTER TABLE when the table resides in an

# attached database.

# alter-1.9.*: Tests for ALTER TABLE when their is whitespace between the

# table name and left parenthesis token. i.e:

# "CREATE TABLE abc (a, b, c);"

# alter-2.*: Test error conditions and messages.

# alter-3.*: Test ALTER TABLE on tables that have TRIGGERs attached to them.

# alter-4.*: Test ALTER TABLE on tables that have AUTOINCREMENT fields.

# ...

# alter-12.*: Test ALTER TABLE on views.

# Create some tables to rename. Be sure to include some TEMP tables

# and some tables with odd names.

do_test alter-1.1 {

ifcapable tempdb {

set ::temp TEMP

} else {

set ::temp {}

}

execsql [subst -nocommands {

CREATE TABLE t1(a,b);

INSERT INTO t1 VALUES(1,2);

CREATE TABLE [t1'x1](c UNIQUE, b PRIMARY KEY);

INSERT INTO [t1'x1] VALUES(3,4);

CREATE INDEX t1i1 ON T1(B);

CREATE INDEX t1i2 ON t1(a,b);

CREATE INDEX i3 ON [t1'x1](b,c);

CREATE $::temp TABLE "temp table"(e,f,g UNIQUE);

CREATE INDEX i2 ON [temp table](f);

INSERT INTO [temp table] VALUES(5,6,7);

}]

execsql {

SELECT 't1', * FROM t1;

SELECT 't1''x1', * FROM "t1'x1";

SELECT * FROM [temp table];

}

} {t1 1 2 t1'x1 3 4 5 6 7}

do_test alter-1.2 {

execsql [subst {

CREATE $::temp TABLE objlist(type, name, tbl_name);

INSERT INTO objlist SELECT type, name, tbl_name

FROM sqlite_master WHERE NAME!='objlist';

}]

ifcapable tempdb {

execsql {

INSERT INTO objlist SELECT type, name, tbl_name

FROM sqlite_temp_master WHERE NAME!='objlist';

}

execsql {

SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name;

}

} [list \

table t1 t1 \

index t1i1 t1 \

index t1i2 t1 \

table t1'x1 t1'x1 \

index i3 t1'x1 \

index {sqlite_autoindex_t1'x1_1} t1'x1 \

index {sqlite_autoindex_t1'x1_2} t1'x1 \

table {temp table} {temp table} \

index i2 {temp table} \

index {sqlite_autoindex_temp table_1} {temp table} \

]

# Make some changes

integrity_check alter-1.3.0

do_test alter-1.3 {

execsql {

ALTER TABLE [T1] RENAME to [-t1-];

ALTER TABLE "t1'x1" RENAME TO T2;

ALTER TABLE [temp table] RENAME to TempTab;

}

} {}

integrity_check alter-1.3.1

do_test alter-1.4 {

execsql {

SELECT 't1', * FROM [-t1-];

SELECT 't2', * FROM t2;

SELECT * FROM temptab;

}

} {t1 1 2 t2 3 4 5 6 7}

do_test alter-1.5 {

execsql {

DELETE FROM objlist;

INSERT INTO objlist SELECT type, name, tbl_name

FROM sqlite_master WHERE NAME!='objlist';

}

catchsql {

INSERT INTO objlist SELECT type, name, tbl_name

FROM sqlite_temp_master WHERE NAME!='objlist';

}

execsql {

SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name;

}

} [list \

table -t1- -t1- \

index t1i1 -t1- \

index t1i2 -t1- \

table T2 T2 \

index i3 T2 \

index {sqlite_autoindex_T2_1} T2 \

index {sqlite_autoindex_T2_2} T2 \

table {TempTab} {TempTab} \

index i2 {TempTab} \

index {sqlite_autoindex_TempTab_1} {TempTab} \

]

# Make sure the changes persist after restarting the database.

# (The TEMP table will not persist, of course.)

ifcapable tempdb {

do_test alter-1.6 {

db close

sqlite3 db test.db

set DB [sqlite3_connection_pointer db]

execsql {

CREATE TEMP TABLE objlist(type, name, tbl_name);

INSERT INTO objlist SELECT type, name, tbl_name FROM sqlite_master;

INSERT INTO objlist

SELECT type, name, tbl_name FROM sqlite_temp_master

WHERE NAME!='objlist';

SELECT type, name, tbl_name FROM objlist

ORDER BY tbl_name, type desc, name;

}

} [list \

table -t1- -t1- \

index t1i1 -t1- \

index t1i2 -t1- \

table T2 T2 \

index i3 T2 \

index {sqlite_autoindex_T2_1} T2 \

index {sqlite_autoindex_T2_2} T2 \

]

} else {

execsql {

DROP TABLE TempTab;

}

# Create bogus application-defined functions for functions used

# internally by ALTER TABLE, to ensure that ALTER TABLE falls back

# to the built-in functions.

proc failing_app_func {args} {error "bad function"}

do_test alter-1.7-prep {

db func substr failing_app_func

db func like failing_app_func

db func sqlite_rename_table failing_app_func

db func sqlite_rename_trigger failing_app_func

db func sqlite_rename_parent failing_app_func

catchsql {SELECT substr(name,1,3) FROM sqlite_master}

} {1 {bad function}}

# Make sure the ALTER TABLE statements work with the

# non-callback API

do_test alter-1.7 {

stepsql $DB {

ALTER TABLE [-t1-] RENAME to [*t1*];

ALTER TABLE T2 RENAME TO [<t2>];

}

execsql {

DELETE FROM objlist;

INSERT INTO objlist SELECT type, name, tbl_name

FROM sqlite_master WHERE NAME!='objlist';

}

catchsql {

INSERT INTO objlist SELECT type, name, tbl_name

FROM sqlite_temp_master WHERE NAME!='objlist';

}

execsql {

SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name;

}

} [list \

table *t1* *t1* \

index t1i1 *t1* \

index t1i2 *t1* \

table <t2> <t2> \

index i3 <t2> \

index {sqlite_autoindex_<t2>_1} <t2> \

index {sqlite_autoindex_<t2>_2} <t2> \

]

# Check that ALTER TABLE works on attached databases.

ifcapable attach {

do_test alter-1.8.1 {

forcedelete test2.db

forcedelete test2.db-journal

execsql {

ATTACH 'test2.db' AS aux;

}

} {}

do_test alter-1.8.2 {

execsql {

CREATE TABLE t4(a PRIMARY KEY, b, c);

CREATE TABLE aux.t4(a PRIMARY KEY, b, c);

CREATE INDEX i4 ON t4(b);

CREATE INDEX aux.i4 ON t4(b);

}

} {}

do_test alter-1.8.3 {

execsql {

INSERT INTO t4 VALUES('main', 'main', 'main');

INSERT INTO aux.t4 VALUES('aux', 'aux', 'aux');

SELECT * FROM t4 WHERE a = 'main';

}

} {main main main}

do_test alter-1.8.4 {

execsql {

ALTER TABLE t4 RENAME TO t5;

SELECT * FROM t4 WHERE a = 'aux';

}

} {aux aux aux}

do_test alter-1.8.5 {

execsql {

SELECT * FROM t5;

}

} {main main main}

do_test alter-1.8.6 {

execsql {

SELECT * FROM t5 WHERE b = 'main';

}

} {main main main}

do_test alter-1.8.7 {

execsql {

ALTER TABLE aux.t4 RENAME TO t5;

SELECT * FROM aux.t5 WHERE b = 'aux';

}

} {aux aux aux}

}

do_test alter-1.9.1 {

execsql {

CREATE TABLE tbl1 (a, b, c);

INSERT INTO tbl1 VALUES(1, 2, 3);

}

} {}

do_test alter-1.9.2 {

execsql {

SELECT * FROM tbl1;

}

} {1 2 3}

do_test alter-1.9.3 {

execsql {

ALTER TABLE tbl1 RENAME TO tbl2;

SELECT * FROM tbl2;

}

} {1 2 3}

do_test alter-1.9.4 {

execsql {

DROP TABLE tbl2;

}

} {}

# Test error messages

do_test alter-2.1 {

catchsql {

ALTER TABLE none RENAME TO hi;

}

} {1 {no such table: none}}

do_test alter-2.2 {

execsql {

CREATE TABLE t3(p,q,r);

}

catchsql {

ALTER TABLE [<t2>] RENAME TO t3;

}

} {1 {there is already another table or index with this name: t3}}

do_test alter-2.3 {

catchsql {

ALTER TABLE [<t2>] RENAME TO i3;

}

} {1 {there is already another table or index with this name: i3}}

do_test alter-2.4 {

catchsql {

ALTER TABLE SqLiTe_master RENAME TO master;

}

} {1 {table sqlite_master may not be altered}}

do_test alter-2.5 {

catchsql {

ALTER TABLE t3 RENAME TO sqlite_t3;

}

} {1 {object name reserved for internal use: sqlite_t3}}

do_test alter-2.6 {

catchsql {

ALTER TABLE t3 ADD COLUMN (ALTER TABLE t3 ADD COLUMN);

}

} {1 {near "(": syntax error}}

# If this compilation does not include triggers, omit the alter-3.* tests.

ifcapable trigger {

#-----------------------------------------------------------------------

# Tests alter-3.* test ALTER TABLE on tables that have triggers.

# alter-3.1.*: ALTER TABLE with triggers.

# alter-3.2.*: Test that the ON keyword cannot be used as a database,

# table or column name unquoted. This is done because part of the

# ALTER TABLE code (specifically the implementation of SQL function

# "sqlite_alter_trigger") will break in this case.

# alter-3.3.*: ALTER TABLE with TEMP triggers (todo).

# An SQL user-function for triggers to fire, so that we know they

# are working.

proc trigfunc {args} {

set ::TRIGGER $args

}

db func trigfunc trigfunc

do_test alter-3.1.0 {

execsql {

CREATE TABLE t6(a, b, c);

-- Different case for the table name in the trigger.

CREATE TRIGGER trig1 AFTER INSERT ON T6 BEGIN

SELECT trigfunc('trig1', new.a, new.b, new.c);

END;

}

} {}

do_test alter-3.1.1 {

execsql {

INSERT INTO t6 VALUES(1, 2, 3);

}

set ::TRIGGER

} {trig1 1 2 3}

do_test alter-3.1.2 {

execsql {

ALTER TABLE t6 RENAME TO t7;

INSERT INTO t7 VALUES(4, 5, 6);

}

set ::TRIGGER

} {trig1 4 5 6}

do_test alter-3.1.3 {

execsql {

DROP TRIGGER trig1;

}

} {}

do_test alter-3.1.4 {

execsql {

CREATE TRIGGER trig2 AFTER INSERT ON main.t7 BEGIN

SELECT trigfunc('trig2', new.a, new.b, new.c);

END;

INSERT INTO t7 VALUES(1, 2, 3);

}

set ::TRIGGER

} {trig2 1 2 3}

do_test alter-3.1.5 {

execsql {

ALTER TABLE t7 RENAME TO t8;

INSERT INTO t8 VALUES(4, 5, 6);

}

set ::TRIGGER

} {trig2 4 5 6}

do_test alter-3.1.6 {

execsql {

DROP TRIGGER trig2;

}

} {}

do_test alter-3.1.7 {

execsql {

CREATE TRIGGER trig3 AFTER INSERT ON main.'t8'BEGIN

SELECT trigfunc('trig3', new.a, new.b, new.c);

END;

INSERT INTO t8 VALUES(1, 2, 3);

}

set ::TRIGGER

} {trig3 1 2 3}

do_test alter-3.1.8 {

execsql {

ALTER TABLE t8 RENAME TO t9;

INSERT INTO t9 VALUES(4, 5, 6);

}

set ::TRIGGER

} {trig3 4 5 6}

# Make sure "ON" cannot be used as a database, table or column name without

# quoting. Otherwise the sqlite_alter_trigger() function might not work.

forcedelete test3.db

forcedelete test3.db-journal

ifcapable attach {

do_test alter-3.2.1 {

catchsql {

ATTACH 'test3.db' AS ON;

}

} {1 {near "ON": syntax error}}

do_test alter-3.2.2 {

catchsql {

ATTACH 'test3.db' AS 'ON';

}

} {0 {}}

do_test alter-3.2.3 {

catchsql {

CREATE TABLE ON.t1(a, b, c);

}

} {1 {near "ON": syntax error}}

do_test alter-3.2.4 {

catchsql {

CREATE TABLE 'ON'.t1(a, b, c);

}

} {0 {}}

do_test alter-3.2.4 {

catchsql {

CREATE TABLE 'ON'.ON(a, b, c);

}

} {1 {near "ON": syntax error}}

do_test alter-3.2.5 {

catchsql {

CREATE TABLE 'ON'.'ON'(a, b, c);

}

} {0 {}}

}

do_test alter-3.2.6 {

catchsql {

CREATE TABLE t10(a, ON, c);

}

} {1 {near "ON": syntax error}}

do_test alter-3.2.7 {

catchsql {

CREATE TABLE t10(a, 'ON', c);

}

} {0 {}}

do_test alter-3.2.8 {

catchsql {

CREATE TRIGGER trig4 AFTER INSERT ON ON BEGIN SELECT 1; END;

}

} {1 {near "ON": syntax error}}

ifcapable attach {

do_test alter-3.2.9 {

catchsql {

CREATE TRIGGER 'on'.trig4 AFTER INSERT ON 'ON' BEGIN SELECT 1; END;

}

} {0 {}}

}

do_test alter-3.2.10 {

execsql {

DROP TABLE t10;

}

} {}

do_test alter-3.3.1 {

execsql [subst {

CREATE TABLE tbl1(a, b, c);

CREATE $::temp TRIGGER trig1 AFTER INSERT ON tbl1 BEGIN

SELECT trigfunc('trig1', new.a, new.b, new.c);

END;

}]

} {}

do_test alter-3.3.2 {

execsql {

INSERT INTO tbl1 VALUES('a', 'b', 'c');

}

set ::TRIGGER

} {trig1 a b c}

do_test alter-3.3.3 {

execsql {

ALTER TABLE tbl1 RENAME TO tbl2;

INSERT INTO tbl2 VALUES('d', 'e', 'f');

}

set ::TRIGGER

} {trig1 d e f}

do_test alter-3.3.4 {

execsql [subst {

CREATE $::temp TRIGGER trig2 AFTER UPDATE ON tbl2 BEGIN

SELECT trigfunc('trig2', new.a, new.b, new.c);

END;

}]

} {}

do_test alter-3.3.5 {

execsql {

ALTER TABLE tbl2 RENAME TO tbl3;

INSERT INTO tbl3 VALUES('g', 'h', 'i');

}

set ::TRIGGER

} {trig1 g h i}

do_test alter-3.3.6 {

execsql {

UPDATE tbl3 SET a = 'G' where a = 'g';

}

set ::TRIGGER

} {trig2 G h i}

do_test alter-3.3.7 {

execsql {

DROP TABLE tbl3;

}

} {}

ifcapable tempdb {

do_test alter-3.3.8 {

execsql {

SELECT * FROM sqlite_temp_master WHERE type = 'trigger';

}

} {}

}

} ;# ifcapable trigger

# If the build does not include AUTOINCREMENT fields, omit alter-4.*.

ifcapable autoinc {

do_test alter-4.1 {

execsql {

CREATE TABLE tbl1(a INTEGER PRIMARY KEY AUTOINCREMENT);

INSERT INTO tbl1 VALUES(10);

}

} {}

do_test alter-4.2 {

execsql {

INSERT INTO tbl1 VALUES(NULL);

SELECT a FROM tbl1;

}

} {10 11}

do_test alter-4.3 {

execsql {

ALTER TABLE tbl1 RENAME TO tbl2;

DELETE FROM tbl2;

INSERT INTO tbl2 VALUES(NULL);

SELECT a FROM tbl2;

}

} {12}

do_test alter-4.4 {

execsql {

DROP TABLE tbl2;

}

} {}

} ;# ifcapable autoinc

# Test that it is Ok to execute an ALTER TABLE immediately after

# opening a database.

do_test alter-5.1 {

execsql {

CREATE TABLE tbl1(a, b, c);

INSERT INTO tbl1 VALUES('x', 'y', 'z');

}

} {}

do_test alter-5.2 {

sqlite3 db2 test.db

execsql {

ALTER TABLE tbl1 RENAME TO tbl2;

SELECT * FROM tbl2;

} db2

} {x y z}

do_test alter-5.3 {

db2 close

} {}

foreach tblname [execsql {

SELECT name FROM sqlite_master

WHERE type='table' AND name NOT GLOB 'sqlite*'

}] {

execsql "DROP TABLE \"$tblname\""

}

set ::tbl_name "abc\uABCDdef"

do_test alter-6.1 {

string length $::tbl_name

} {7}

do_test alter-6.2 {

execsql "

CREATE TABLE ${tbl_name}(a, b, c);

set ::oid [execsql {SELECT max(oid) FROM sqlite_master}]

execsql "

SELECT sql FROM sqlite_master WHERE oid = $::oid;

} "{CREATE TABLE ${::tbl_name}(a, b, c)}"

execsql "

SELECT * FROM ${::tbl_name}

set ::tbl_name2 "abcXdef"

do_test alter-6.3 {

execsql "

ALTER TABLE $::tbl_name RENAME TO $::tbl_name2

execsql "

SELECT sql FROM sqlite_master WHERE oid = $::oid

} "{CREATE TABLE \"${::tbl_name2}\"(a, b, c)}"

do_test alter-6.4 {

execsql "

ALTER TABLE $::tbl_name2 RENAME TO $::tbl_name

execsql "

SELECT sql FROM sqlite_master WHERE oid = $::oid

} "{CREATE TABLE \"${::tbl_name}\"(a, b, c)}"

set ::col_name ghi\1234\jkl

do_test alter-6.5 {

execsql "

ALTER TABLE $::tbl_name ADD COLUMN $::col_name VARCHAR

execsql "

SELECT sql FROM sqlite_master WHERE oid = $::oid

} "{CREATE TABLE \"${::tbl_name}\"(a, b, c, $::col_name VARCHAR)}"

set ::col_name2 B\3421\A

do_test alter-6.6 {

db close

sqlite3 db test.db

execsql "

ALTER TABLE $::tbl_name ADD COLUMN $::col_name2

execsql "

SELECT sql FROM sqlite_master WHERE oid = $::oid

} "{CREATE TABLE \"${::tbl_name}\"(a, b, c, $::col_name VARCHAR, $::col_name2)}"

do_test alter-6.7 {

execsql "

INSERT INTO ${::tbl_name} VALUES(1, 2, 3, 4, 5);

SELECT $::col_name, $::col_name2 FROM $::tbl_name;

} {4 5}

# Ticket #1665: Make sure ALTER TABLE ADD COLUMN works on a table

# that includes a COLLATE clause.

do_realnum_test alter-7.1 {

execsql {

CREATE TABLE t1(a TEXT COLLATE BINARY);

ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;

INSERT INTO t1 VALUES(1,'-2');

INSERT INTO t1 VALUES(5.4e-08,'5.4e-08');

SELECT typeof(a), a, typeof(b), b FROM t1;

}

} {text 1 integer -2 text 5.4e-08 real 5.4e-08}

# Make sure that when a column is added by ALTER TABLE ADD COLUMN and has

# a default value that the default value is used by aggregate functions.

do_test alter-8.1 {

execsql {

CREATE TABLE t2(a INTEGER);

INSERT INTO t2 VALUES(1);

INSERT INTO t2 VALUES(2);

ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;

SELECT sum(b) FROM t2;

}

} {27}

do_test alter-8.2 {

execsql {

SELECT a, sum(b) FROM t2 GROUP BY a;

}

} {1 18 2 9}

#--------------------------------------------------------------------------

# alter-9.X - Special test: Make sure the sqlite_rename_trigger() and

# rename_table() functions do not crash when handed bad input.

ifcapable trigger {

do_test alter-9.1 {

execsql {SELECT SQLITE_RENAME_TRIGGER(0,0)}

} {{}}

}

do_test alter-9.2 {

execsql {

SELECT SQLITE_RENAME_TABLE(0,0);

SELECT SQLITE_RENAME_TABLE(10,20);

SELECT SQLITE_RENAME_TABLE('foo', 'foo');

}

} {{} {} {}}

#------------------------------------------------------------------------

# alter-10.X - Make sure ALTER TABLE works with multi-byte UTF-8 characters

# in the names.

do_test alter-10.1 {

execsql "CREATE TABLE xyz(x UNIQUE)"

execsql "ALTER TABLE xyz RENAME TO xyz\u1234abc"

execsql {SELECT name FROM sqlite_master WHERE name GLOB 'xyz*'}

} [list xyz\u1234abc]

do_test alter-10.2 {

execsql {SELECT name FROM sqlite_master WHERE name GLOB 'sqlite_autoindex*'}

} [list sqlite_autoindex_xyz\u1234abc_1]

do_test alter-10.3 {

execsql "ALTER TABLE xyz\u1234abc RENAME TO xyzabc"

execsql {SELECT name FROM sqlite_master WHERE name GLOB 'xyz*'}

} [list xyzabc]

do_test alter-10.4 {

execsql {SELECT name FROM sqlite_master WHERE name GLOB 'sqlite_autoindex*'}

} [list sqlite_autoindex_xyzabc_1]

do_test alter-11.1 {

sqlite3_exec db {CREATE TABLE t11(%c6%c6)}

execsql {

ALTER TABLE t11 ADD COLUMN abc;

}

catchsql {

ALTER TABLE t11 ADD COLUMN abc;

}

} {1 {duplicate column name: abc}}

set isutf16 [regexp 16 [db one {PRAGMA encoding}]]

if {!$isutf16} {

do_test alter-11.2 {

execsql {INSERT INTO t11 VALUES(1,2)}

sqlite3_exec db {SELECT %c6%c6 AS xyz, abc FROM t11}

} {0 {xyz abc 1 2}}

}

do_test alter-11.3 {

sqlite3_exec db {CREATE TABLE t11b("%81%82%83" text)}

execsql {

ALTER TABLE t11b ADD COLUMN abc;

}

catchsql {

ALTER TABLE t11b ADD COLUMN abc;

}

} {1 {duplicate column name: abc}}

if {!$isutf16} {

do_test alter-11.4 {

execsql {INSERT INTO t11b VALUES(3,4)}

sqlite3_exec db {SELECT %81%82%83 AS xyz, abc FROM t11b}

} {0 {xyz abc 3 4}}

do_test alter-11.5 {

sqlite3_exec db {SELECT [%81%82%83] AS xyz, abc FROM t11b}

} {0 {xyz abc 3 4}}

do_test alter-11.6 {

sqlite3_exec db {SELECT "%81%82%83" AS xyz, abc FROM t11b}

} {0 {xyz abc 3 4}}

}

do_test alter-11.7 {

sqlite3_exec db {CREATE TABLE t11c(%81%82%83 text)}

execsql {

ALTER TABLE t11c ADD COLUMN abc;

}

catchsql {

ALTER TABLE t11c ADD COLUMN abc;

}

} {1 {duplicate column name: abc}}

if {!$isutf16} {

do_test alter-11.8 {

execsql {INSERT INTO t11c VALUES(5,6)}

sqlite3_exec db {SELECT %81%82%83 AS xyz, abc FROM t11c}

} {0 {xyz abc 5 6}}

do_test alter-11.9 {

sqlite3_exec db {SELECT [%81%82%83] AS xyz, abc FROM t11c}

} {0 {xyz abc 5 6}}

do_test alter-11.10 {

sqlite3_exec db {SELECT "%81%82%83" AS xyz, abc FROM t11c}

} {0 {xyz abc 5 6}}

}

do_test alter-12.1 {

execsql {

CREATE TABLE t12(a, b, c);

CREATE VIEW v1 AS SELECT * FROM t12;

}

} {}

do_test alter-12.2 {

catchsql {

ALTER TABLE v1 RENAME TO v2;

}

} {1 {view v1 may not be altered}}

do_test alter-12.3 {

execsql { SELECT * FROM v1; }

} {}

do_test alter-12.4 {

db close

sqlite3 db test.db

execsql { SELECT * FROM v1; }

} {}

do_test alter-12.5 {

catchsql {

ALTER TABLE v1 ADD COLUMN new_column;

}

} {1 {Cannot add a column to a view}}

# Ticket #3102:

# Verify that comments do not interfere with the table rename

# algorithm.

do_test alter-13.1 {

execsql {

CREATE TABLE /* hi */ t3102a(x);

CREATE TABLE t3102b -- comment

(y);

CREATE INDEX t3102c ON t3102a(x);

SELECT name FROM sqlite_master WHERE name GLOB 't3102*' ORDER BY 1;

}

} {t3102a t3102b t3102c}

do_test alter-13.2 {

execsql {

ALTER TABLE t3102a RENAME TO t3102a_rename;

SELECT name FROM sqlite_master WHERE name GLOB 't3102*' ORDER BY 1;

}

} {t3102a_rename t3102b t3102c}

do_test alter-13.3 {

execsql {

ALTER TABLE t3102b RENAME TO t3102b_rename;

SELECT name FROM sqlite_master WHERE name GLOB 't3102*' ORDER BY 1;

}

} {t3102a_rename t3102b_rename t3102c}

# Ticket #3651

do_test alter-14.1 {

catchsql {

CREATE TABLE t3651(a UNIQUE);

ALTER TABLE t3651 ADD COLUMN b UNIQUE;

}

} {1 {Cannot add a UNIQUE column}}

do_test alter-14.2 {

catchsql {

ALTER TABLE t3651 ADD COLUMN b PRIMARY KEY;

}

} {1 {Cannot add a PRIMARY KEY column}}

#-------------------------------------------------------------------------

# Test that it is not possible to use ALTER TABLE on any system table.

set system_table_list {1 sqlite_master}

catchsql ANALYZE

ifcapable analyze { lappend system_table_list 2 sqlite_stat1 }

ifcapable stat3 { lappend system_table_list 4 sqlite_stat3 }

foreach {tn tbl} $system_table_list {

do_test alter-15.$tn.1 {

catchsql "ALTER TABLE $tbl RENAME TO xyz"

} [list 1 "table $tbl may not be altered"]

do_test alter-15.$tn.2 {

catchsql "ALTER TABLE $tbl ADD COLUMN xyz"

} [list 1 "table $tbl may not be altered"]

}

finish_test

sqlcipher/test/alter.test at Xcode5 · sqlcipher/sqlcipher · GitHub

Uh oh!

Expand file tree

Breadcrumbs

alter.test

Latest commit

History

Breadcrumbs

alter.test

File metadata and controls