WebSVN – wasCSharpSQLite – Blame – Rev 1

1

office

1

# 2010 September 18

2

#

3

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

4

# a legal notice, here is a blessing:

5

#

6

# May you do good and not evil.

7

# May you find forgiveness for yourself and forgive others.

8

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

9

#

10

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

11

#

12

# The majority of this file implements tests to verify that the "testable

13

# statements" in the lang_insert.html document are correct.

14

#

15

# Also, it contains tests to verify the statements in (the very short)

16

# lang_replace.html.

17

#

18

set testdir [file dirname $argv0]

19

source $testdir/tester.tcl

20

21

# Organization of tests:

22

#

23

# e_insert-0.*: Test the syntax diagram.

24

#

25

# e_insert-1.*: Test statements of the form "INSERT ... VALUES(...)".

26

#

27

# e_insert-2.*: Test statements of the form "INSERT ... SELECT ...".

28

#

29

# e_insert-3.*: Test statements of the form "INSERT ... DEFAULT VALUES".

30

#

31

# e_insert-4.*: Test statements regarding the conflict clause.

32

#

33

# e_insert-5.*: Test that the qualified table name and "DEFAULT VALUES"

34

# syntaxes do not work in trigger bodies.

35

#

36

37

do_execsql_test e_insert-0.0 {

38

CREATE TABLE a1(a, b);

39

CREATE TABLE a2(a, b, c DEFAULT 'xyz');

40

CREATE TABLE a3(x DEFAULT 1.0, y DEFAULT 'string', z);

41

CREATE TABLE a4(c UNIQUE, d);

42

} {}

43

44

proc do_insert_tests {args} {

45

uplevel do_select_tests $args

46

}

47

48

# EVIDENCE-OF: R-41448-54465 -- syntax diagram insert-stmt

49

#

50

do_insert_tests e_insert-0 {

51

1 "INSERT INTO a1 DEFAULT VALUES" {}

52

2 "INSERT INTO main.a1 DEFAULT VALUES" {}

53

3 "INSERT OR ROLLBACK INTO main.a1 DEFAULT VALUES" {}

54

4 "INSERT OR ROLLBACK INTO a1 DEFAULT VALUES" {}

55

5 "INSERT OR ABORT INTO main.a1 DEFAULT VALUES" {}

56

6 "INSERT OR ABORT INTO a1 DEFAULT VALUES" {}

57

7 "INSERT OR REPLACE INTO main.a1 DEFAULT VALUES" {}

58

8 "INSERT OR REPLACE INTO a1 DEFAULT VALUES" {}

59

9 "INSERT OR FAIL INTO main.a1 DEFAULT VALUES" {}

60

10 "INSERT OR FAIL INTO a1 DEFAULT VALUES" {}

61

11 "INSERT OR FAIL INTO main.a1 DEFAULT VALUES" {}

62

12 "INSERT OR IGNORE INTO a1 DEFAULT VALUES" {}

63

13 "REPLACE INTO a1 DEFAULT VALUES" {}

64

14 "REPLACE INTO main.a1 DEFAULT VALUES" {}

65

15 "INSERT INTO a1 VALUES(1, 2)" {}

66

16 "INSERT INTO main.a1 VALUES(1, 2)" {}

67

17 "INSERT OR ROLLBACK INTO main.a1 VALUES(1, 2)" {}

68

18 "INSERT OR ROLLBACK INTO a1 VALUES(1, 2)" {}

69

19 "INSERT OR ABORT INTO main.a1 VALUES(1, 2)" {}

70

20 "INSERT OR ABORT INTO a1 VALUES(1, 2)" {}

71

21 "INSERT OR REPLACE INTO main.a1 VALUES(1, 2)" {}

72

22 "INSERT OR REPLACE INTO a1 VALUES(1, 2)" {}

73

23 "INSERT OR FAIL INTO main.a1 VALUES(1, 2)" {}

74

24 "INSERT OR FAIL INTO a1 VALUES(1, 2)" {}

75

25 "INSERT OR FAIL INTO main.a1 VALUES(1, 2)" {}

76

26 "INSERT OR IGNORE INTO a1 VALUES(1, 2)" {}

77

27 "REPLACE INTO a1 VALUES(1, 2)" {}

78

28 "REPLACE INTO main.a1 VALUES(1, 2)" {}

79

29 "INSERT INTO a1 (b, a) VALUES(1, 2)" {}

80

30 "INSERT INTO main.a1 (b, a) VALUES(1, 2)" {}

81

31 "INSERT OR ROLLBACK INTO main.a1 (b, a) VALUES(1, 2)" {}

82

32 "INSERT OR ROLLBACK INTO a1 (b, a) VALUES(1, 2)" {}

83

33 "INSERT OR ABORT INTO main.a1 (b, a) VALUES(1, 2)" {}

84

34 "INSERT OR ABORT INTO a1 (b, a) VALUES(1, 2)" {}

85

35 "INSERT OR REPLACE INTO main.a1 (b, a) VALUES(1, 2)" {}

86

36 "INSERT OR REPLACE INTO a1 (b, a) VALUES(1, 2)" {}

87

37 "INSERT OR FAIL INTO main.a1 (b, a) VALUES(1, 2)" {}

88

38 "INSERT OR FAIL INTO a1 (b, a) VALUES(1, 2)" {}

89

39 "INSERT OR FAIL INTO main.a1 (b, a) VALUES(1, 2)" {}

90

40 "INSERT OR IGNORE INTO a1 (b, a) VALUES(1, 2)" {}

91

41 "REPLACE INTO a1 (b, a) VALUES(1, 2)" {}

92

42 "REPLACE INTO main.a1 (b, a) VALUES(1, 2)" {}

93

43 "INSERT INTO a1 SELECT c, b FROM a2" {}

94

44 "INSERT INTO main.a1 SELECT c, b FROM a2" {}

95

45 "INSERT OR ROLLBACK INTO main.a1 SELECT c, b FROM a2" {}

96

46 "INSERT OR ROLLBACK INTO a1 SELECT c, b FROM a2" {}

97

47 "INSERT OR ABORT INTO main.a1 SELECT c, b FROM a2" {}

98

48 "INSERT OR ABORT INTO a1 SELECT c, b FROM a2" {}

99

49 "INSERT OR REPLACE INTO main.a1 SELECT c, b FROM a2" {}

100

50 "INSERT OR REPLACE INTO a1 SELECT c, b FROM a2" {}

101

51 "INSERT OR FAIL INTO main.a1 SELECT c, b FROM a2" {}

102

52 "INSERT OR FAIL INTO a1 SELECT c, b FROM a2" {}

103

53 "INSERT OR FAIL INTO main.a1 SELECT c, b FROM a2" {}

104

54 "INSERT OR IGNORE INTO a1 SELECT c, b FROM a2" {}

105

55 "REPLACE INTO a1 SELECT c, b FROM a2" {}

106

56 "REPLACE INTO main.a1 SELECT c, b FROM a2" {}

107

57 "INSERT INTO a1 (b, a) SELECT c, b FROM a2" {}

108

58 "INSERT INTO main.a1 (b, a) SELECT c, b FROM a2" {}

109

59 "INSERT OR ROLLBACK INTO main.a1 (b, a) SELECT c, b FROM a2" {}

110

60 "INSERT OR ROLLBACK INTO a1 (b, a) SELECT c, b FROM a2" {}

111

61 "INSERT OR ABORT INTO main.a1 (b, a) SELECT c, b FROM a2" {}

112

62 "INSERT OR ABORT INTO a1 (b, a) SELECT c, b FROM a2" {}

113

63 "INSERT OR REPLACE INTO main.a1 (b, a) SELECT c, b FROM a2" {}

114

64 "INSERT OR REPLACE INTO a1 (b, a) SELECT c, b FROM a2" {}

115

65 "INSERT OR FAIL INTO main.a1 (b, a) SELECT c, b FROM a2" {}

116

66 "INSERT OR FAIL INTO a1 (b, a) SELECT c, b FROM a2" {}

117

67 "INSERT OR FAIL INTO main.a1 (b, a) SELECT c, b FROM a2" {}

118

68 "INSERT OR IGNORE INTO a1 (b, a) SELECT c, b FROM a2" {}

119

69 "REPLACE INTO a1 (b, a) SELECT c, b FROM a2" {}

120

70 "REPLACE INTO main.a1 (b, a) SELECT c, b FROM a2" {}

}

delete_all_data

# EVIDENCE-OF: R-20288-20462 The first form (with the "VALUES" keyword)

126

# creates a single new row in an existing table.

127

#

128

do_insert_tests e_insert-1.1 {

129

130

131

1a "INSERT INTO a2 VALUES(1, 2, 3)" {}

132

1b "SELECT count(*) FROM a2" {1}

133

134

2a "INSERT INTO a2(a, b) VALUES(1, 2)" {}

135

2b "SELECT count(*) FROM a2" {2}

136

}

137

138

# EVIDENCE-OF: R-36040-20870 If no column-list is specified then the

139

# number of values must be the same as the number of columns in the

140

# table.

141

#

142

# A test in the block above verifies that if the VALUES list has the

143

# correct number of columns (for table a2, 3 columns) works. So these

144

# tests just show that other values cause an error.

145

#

146

do_insert_tests e_insert-1.2 -error {

147

table %s has %d columns but %d values were supplied

148

} {

149

1 "INSERT INTO a2 VALUES(1)" {a2 3 1}

150

2 "INSERT INTO a2 VALUES(1,2)" {a2 3 2}

151

3 "INSERT INTO a2 VALUES(1,2,3,4)" {a2 3 4}

152

4 "INSERT INTO a2 VALUES(1,2,3,4,5)" {a2 3 5}

153

}

154

155

# EVIDENCE-OF: R-04006-57648 In this case the result of evaluating the

156

# left-most expression in the VALUES list is inserted into the left-most

157

# column of the new row, and so on.

158

#

159

delete_all_data

160

do_insert_tests e_insert-1.3 {

161

1a "INSERT INTO a2 VALUES(1, 2, 3)" {}

162

1b "SELECT * FROM a2 WHERE oid=last_insert_rowid()" {1 2 3}

163

164

2a "INSERT INTO a2 VALUES('abc', NULL, 3*3+1)" {}

165

2b "SELECT * FROM a2 WHERE oid=last_insert_rowid()" {abc {} 10}

166

167

3a "INSERT INTO a2 VALUES((SELECT count(*) FROM a2), 'x', 'y')" {}

168

3b "SELECT * FROM a2 WHERE oid=last_insert_rowid()" {2 x y}

169

}

170

171

# EVIDENCE-OF: R-62524-00361 If a column-list is specified, then the

172

# number of values must match the number of specified columns.

173

#

174

do_insert_tests e_insert-1.4 -error {

175

%d values for %d columns

176

} {

177

1 "INSERT INTO a2(a, b, c) VALUES(1)" {1 3}

178

2 "INSERT INTO a2(a, b, c) VALUES(1,2)" {2 3}

179

3 "INSERT INTO a2(a, b, c) VALUES(1,2,3,4)" {4 3}

180

4 "INSERT INTO a2(a, b, c) VALUES(1,2,3,4,5)" {5 3}

181

182

5 "INSERT INTO a2(c, a) VALUES(1)" {1 2}

183

6 "INSERT INTO a2(c, a) VALUES(1,2,3)" {3 2}

184

7 "INSERT INTO a2(c, a) VALUES(1,2,3,4)" {4 2}

185

8 "INSERT INTO a2(c, a) VALUES(1,2,3,4,5)" {5 2}

186

}

187

188

# EVIDENCE-OF: R-07016-26442 Each of the named columns of the new row is

189

# populated with the results of evaluating the corresponding VALUES

190

# expression.

191

#

192

# EVIDENCE-OF: R-12183-43719 Table columns that do not appear in the

193

# column list are populated with the default column value (specified as

194

# part of the CREATE TABLE statement), or with NULL if no default value

# is specified.

#

delete_all_data

do_insert_tests e_insert-1.5 {

199

1a "INSERT INTO a2(b, c) VALUES('b', 'c')" {}

200

1b "SELECT * FROM a2" {{} b c}

201

202

2a "INSERT INTO a2(a, b) VALUES('a', 'b')" {}

203

2b "SELECT * FROM a2" {{} b c a b xyz}

204

}

205

206

# EVIDENCE-OF: R-52173-30215 A new entry is inserted into the table for

207

# each row of data returned by executing the SELECT statement.

208

#

209

delete_all_data

210

do_insert_tests e_insert-2.1 {

211

212

213

1a "SELECT count(*) FROM (SELECT 1, 2)" {1}

214

1b "INSERT INTO a1 SELECT 1, 2" {}

215

1c "SELECT count(*) FROM a1" {1}

216

217

2a "SELECT count(*) FROM (SELECT b, a FROM a1)" {1}

218

2b "INSERT INTO a1 SELECT b, a FROM a1" {}

219

2c "SELECT count(*) FROM a1" {2}

220

221

3a "SELECT count(*) FROM (SELECT b, a FROM a1)" {2}

222

3b "INSERT INTO a1 SELECT b, a FROM a1" {}

223

3c "SELECT count(*) FROM a1" {4}

224

225

4a "SELECT count(*) FROM (SELECT b, a FROM a1)" {4}

226

4b "INSERT INTO a1 SELECT b, a FROM a1" {}

227

4c "SELECT count(*) FROM a1" {8}

228

229

4a "SELECT count(*) FROM (SELECT min(b), min(a) FROM a1)" {1}

230

4b "INSERT INTO a1 SELECT min(b), min(a) FROM a1" {}

231

4c "SELECT count(*) FROM a1" {9}

}

# EVIDENCE-OF: R-63614-47421 If a column-list is specified, the number

236

# of columns in the result of the SELECT must be the same as the number

237

# of items in the column-list.

238

#

239

do_insert_tests e_insert-2.2 -error {

240

%d values for %d columns

241

} {

242

1 "INSERT INTO a3(x, y) SELECT a, b, c FROM a2" {3 2}

243

2 "INSERT INTO a3(x, y) SELECT * FROM a2" {3 2}

244

3 "INSERT INTO a3(x, y) SELECT * FROM a2 CROSS JOIN a1" {5 2}

245

4 "INSERT INTO a3(x, y) SELECT * FROM a2 NATURAL JOIN a1" {3 2}

246

5 "INSERT INTO a3(x, y) SELECT a2.a FROM a2,a1" {1 2}

247

248

6 "INSERT INTO a3(z) SELECT a, b, c FROM a2" {3 1}

249

7 "INSERT INTO a3(z) SELECT * FROM a2" {3 1}

250

8 "INSERT INTO a3(z) SELECT * FROM a2 CROSS JOIN a1" {5 1}

251

9 "INSERT INTO a3(z) SELECT * FROM a2 NATURAL JOIN a1" {3 1}

252

10 "INSERT INTO a3(z) SELECT a1.* FROM a2,a1" {2 1}

253

}

254

255

# EVIDENCE-OF: R-58951-07798 Otherwise, if no column-list is specified,

256

# the number of columns in the result of the SELECT must be the same as

257

# the number of columns in the table.

258

#

259

do_insert_tests e_insert-2.3 -error {

260

table %s has %d columns but %d values were supplied

261

} {

262

1 "INSERT INTO a1 SELECT a, b, c FROM a2" {a1 2 3}

263

2 "INSERT INTO a1 SELECT * FROM a2" {a1 2 3}

264

3 "INSERT INTO a1 SELECT * FROM a2 CROSS JOIN a1" {a1 2 5}

265

4 "INSERT INTO a1 SELECT * FROM a2 NATURAL JOIN a1" {a1 2 3}

266

5 "INSERT INTO a1 SELECT a2.a FROM a2,a1" {a1 2 1}

267

}

268

269

# EVIDENCE-OF: R-31074-37730 Any SELECT statement, including compound

270

# SELECTs and SELECT statements with ORDER BY and/or LIMIT clauses, may

271

# be used in an INSERT statement of this form.

272

#

273

delete_all_data

274

do_execsql_test e_insert-2.3.0 {

275

INSERT INTO a1 VALUES('x', 'y');

276

} {}

277

do_insert_tests e_insert-2.3 {

278

1 "INSERT INTO a1 SELECT a,b FROM a1 UNION SELECT b,a FROM a1 ORDER BY 1" {}

279

2 "INSERT INTO a1(b, a) SELECT * FROM a1 LIMIT 1" {}

280

3 "INSERT INTO a1 SELECT 'a'||a, 'b'||b FROM a1 LIMIT 2 OFFSET 1" {}

281

4 "INSERT INTO a1 SELECT * FROM a1 ORDER BY b, a" {}

282

S "SELECT * FROM a1" {

x y

x y y x

y x

ax by ay bx

ay bx ax by y x y x x y x y

}

}

# EVIDENCE-OF: R-25149-22012 The INSERT ... DEFAULT VALUES statement

292

# inserts a single new row into the named table.

293

#

294

delete_all_data

295

do_insert_tests e_insert-3.1 {

296

1 "SELECT count(*) FROM a3" {0}

297

2a "INSERT INTO a3 DEFAULT VALUES" {}

298

2b "SELECT count(*) FROM a3" {1}

299

}

300

301

# EVIDENCE-OF: R-18927-01951 Each column of the new row is populated

302

# with its default value, or with a NULL if no default value is

303

# specified as part of the column definition in the CREATE TABLE

# statement.

#

delete_all_data

do_insert_tests e_insert-3.2 {

308

1.1 "INSERT INTO a3 DEFAULT VALUES" {}

309

1.2 "SELECT * FROM a3" {1.0 string {}}

310

311

2.1 "INSERT INTO a3 DEFAULT VALUES" {}

312

2.2 "SELECT * FROM a3" {1.0 string {} 1.0 string {}}

313

314

3.1 "INSERT INTO a2 DEFAULT VALUES" {}

315

3.2 "SELECT * FROM a2" {{} {} xyz}

316

317

4.1 "INSERT INTO a2 DEFAULT VALUES" {}

318

4.2 "SELECT * FROM a2" {{} {} xyz {} {} xyz}

319

320

5.1 "INSERT INTO a1 DEFAULT VALUES" {}

321

5.2 "SELECT * FROM a1" {{} {}}

322

323

6.1 "INSERT INTO a1 DEFAULT VALUES" {}

324

6.2 "SELECT * FROM a1" {{} {} {} {}}

325

}

326

327

# EVIDENCE-OF: R-46928-50290 The optional conflict-clause allows the

328

# specification of an alternative constraint conflict resolution

329

# algorithm to use during this one INSERT command.

330

#

331

# EVIDENCE-OF: R-23110-47146 the parser allows the use of the single

332

# keyword REPLACE as an alias for "INSERT OR REPLACE".

333

#

334

# The two requirements above are tested by e_select-4.1.* and

335

# e_select-4.2.*, respectively.

336

#

337

# EVIDENCE-OF: R-03421-22330 The REPLACE command is an alias for the

338

# "INSERT OR REPLACE" variant of the INSERT command.

339

#

340

# This is a dup of R-23110-47146. Therefore it is also verified

341

# by e_select-4.2.*. This requirement is the only one from

342

# lang_replace.html.

343

#

344

do_execsql_test e_insert-4.1.0 {

345

INSERT INTO a4 VALUES(1, 'a');

346

INSERT INTO a4 VALUES(2, 'a');

347

INSERT INTO a4 VALUES(3, 'a');

348

} {}

349

foreach {tn sql error ac data } {

350

1.1 "INSERT INTO a4 VALUES(2,'b')" {column c is not unique} 1 {1 a 2 a 3 a}

351

1.2 "INSERT OR REPLACE INTO a4 VALUES(2, 'b')" {} 1 {1 a 3 a 2 b}

352

1.3 "INSERT OR IGNORE INTO a4 VALUES(3, 'c')" {} 1 {1 a 3 a 2 b}

353

1.4 "BEGIN" {} 0 {1 a 3 a 2 b}

354

1.5 "INSERT INTO a4 VALUES(1, 'd')" {column c is not unique} 0 {1 a 3 a 2 b}

355

1.6 "INSERT OR ABORT INTO a4 VALUES(1, 'd')"

356

{column c is not unique} 0 {1 a 3 a 2 b}

357

1.7 "INSERT OR ROLLBACK INTO a4 VALUES(1, 'd')"

358

{column c is not unique} 1 {1 a 3 a 2 b}

359

1.8 "INSERT INTO a4 SELECT 4, 'e' UNION ALL SELECT 3, 'e'"

360

{column c is not unique} 1 {1 a 3 a 2 b}

361

1.9 "INSERT OR FAIL INTO a4 SELECT 4, 'e' UNION ALL SELECT 3, 'e'"

362

{column c is not unique} 1 {1 a 3 a 2 b 4 e}

363

364

2.1 "INSERT INTO a4 VALUES(2,'f')"

365

{column c is not unique} 1 {1 a 3 a 2 b 4 e}

366

2.2 "REPLACE INTO a4 VALUES(2, 'f')" {} 1 {1 a 3 a 4 e 2 f}

367

} {

368

do_catchsql_test e_insert-4.1.$tn.1 $sql [list [expr {$error!=""}] $error]

369

do_execsql_test e_insert-4.1.$tn.2 {SELECT * FROM a4} [list {*}$data]

370

do_test e_insert-4.1.$tn.3 {sqlite3_get_autocommit db} $ac

371

}

372

373

# EVIDENCE-OF: R-64196-02418 The optional "database-name." prefix on the

374

# table-name is support for top-level INSERT statements only.

375

#

376

# EVIDENCE-OF: R-05731-00924 The table name must be unqualified for

377

# INSERT statements that occur within CREATE TRIGGER statements.

378

#

379

set err {1 {qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers}}

380

381

do_catchsql_test e_insert-5.1.1 {

382

CREATE TRIGGER AFTER UPDATE ON a1 BEGIN

383

INSERT INTO main.a4 VALUES(new.a, new.b);

384

END;

385

} $err

386

do_catchsql_test e_insert-5.1.2 {

387

CREATE TEMP TABLE IF NOT EXISTS tmptable(a, b);

388

CREATE TRIGGER AFTER DELETE ON a3 BEGIN

389

INSERT INTO temp.tmptable VALUES(1, 2);

END;

} $err

# EVIDENCE-OF: R-15888-36326 Similarly, the "DEFAULT VALUES" form of the

394

# INSERT statement is supported for top-level INSERT statements only and

395

# not for INSERT statements within triggers.

396

#

397

do_catchsql_test e_insert-5.2.1 {

398

CREATE TRIGGER AFTER UPDATE ON a1 BEGIN

399

INSERT INTO a4 DEFAULT VALUES;

400

END;

401

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

delete_all_data

finish_test

wasCSharpSQLite – Blame information for rev 1