Movatterモバイル変換

[0]ホーム
URL:

Google Git
Sign in
chromium /chromium /src /main /. /sql /sqlite_features_unittest.cc
blob: c09e6fbbe5f1d1a2d651b7c1d58e1ad8442517d4 [file] [log] [blame]
Avi Drissman69b874f2022-09-15 19:11:14[diff] [blame]1// Copyright 2012 The Chromium Authors
shess@chromium.org67361b32011-04-12 20:13:06[diff] [blame]2// Use of this source code is governed by a BSD-style license that can be
3// found in the LICENSE file.
4
Arthur Sonzogni73832d62024-07-24 16:24:38[diff] [blame]5#ifdef UNSAFE_BUFFERS_BUILD
6// TODO(crbug.com/351564777): Remove this and convert code to safer constructs.
7#pragma allow_unsafe_buffers
8#endif
9
avi0b519202015-12-21 07:25:19[diff] [blame]10#include<stddef.h>
11#include<stdint.h>
12
Takuto Ikuta2eb61342024-05-10 09:05:35[diff] [blame]13#include<cstring>
shess@chromium.org67361b32011-04-12 20:13:06[diff] [blame]14#include<string>
Avi Drissman70127472022-01-08 23:20:23[diff] [blame]15#include<tuple>
Takuto Ikuta2eb61342024-05-10 09:05:35[diff] [blame]16#include<vector>
shess@chromium.org67361b32011-04-12 20:13:06[diff] [blame]17
Takuto Ikuta2eb61342024-05-10 09:05:35[diff] [blame]18#include"base/files/file.h"
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]19#include"base/files/file_path.h"
thestig22dfc4012014-09-05 08:29:44[diff] [blame]20#include"base/files/file_util.h"
shess53adf162015-12-17 22:07:26[diff] [blame]21#include"base/files/memory_mapped_file.h"
brettw@chromium.orgea1a3f62012-11-16 20:34:23[diff] [blame]22#include"base/files/scoped_temp_dir.h"
Takuto Ikuta2eb61342024-05-10 09:05:35[diff] [blame]23#include"base/time/time.h"
Scott Graham502836c2017-09-14 23:26:23[diff] [blame]24#include"build/build_config.h"
Victor Costancfbfa602018-08-01 23:24:46[diff] [blame]25#include"sql/database.h"
tfarina@chromium.orgf0a54b22011-07-19 18:40:21[diff] [blame]26#include"sql/statement.h"
Takuto Ikuta2eb61342024-05-10 09:05:35[diff] [blame]27#include"sql/statement_id.h"
Victor Costan205b96dc2021-07-21 20:27:46[diff] [blame]28#include"sql/test/scoped_error_expecter.h"
engedybe80d53e2015-01-22 09:54:50[diff] [blame]29#include"sql/test/test_helpers.h"
shess@chromium.org67361b32011-04-12 20:13:06[diff] [blame]30#include"testing/gtest/include/gtest/gtest.h"
31#include"third_party/sqlite/sqlite3.h"
32
Xiaohan Wang7d09c5e2022-01-08 02:37:36[diff] [blame]33#if BUILDFLAG(IS_APPLE)
Avi Drissmand4f07082023-05-12 18:05:44[diff] [blame]34#include"base/apple/backup_util.h"
shess5f2c3442017-01-24 02:15:10[diff] [blame]35#endif
36
shess@chromium.org67361b32011-04-12 20:13:06[diff] [blame]37// Test that certain features are/are-not enabled in our SQLite.
38
shessf7fcc452017-04-19 22:10:41[diff] [blame]39namespace sql{
shess@chromium.org67361b32011-04-12 20:13:06[diff] [blame]40namespace{
41
shessf7fcc452017-04-19 22:10:41[diff] [blame]42using sql::test::ExecuteWithResult;
43using sql::test::ExecuteWithResults;
44
shessf7fcc452017-04-19 22:10:41[diff] [blame]45}// namespace
46
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]47classSQLiteFeaturesTest:public testing::Test{
shess@chromium.org67361b32011-04-12 20:13:06[diff] [blame]48public:
Anthony Vallée-Duboise3c94912024-12-12 16:47:47[diff] [blame]49SQLiteFeaturesTest(): db_(sql::test::kTestTag){}
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]50~SQLiteFeaturesTest() override=default;
shess@chromium.org67361b32011-04-12 20:13:06[diff] [blame]51
dcheng1b3b125e2014-12-22 23:00:24[diff] [blame]52voidSetUp() override{
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]53 ASSERT_TRUE(temp_dir_.CreateUniqueTempDir());
54 db_path_= temp_dir_.GetPath().AppendASCII("sqlite_features_test.sqlite");
55 ASSERT_TRUE(db_.Open(db_path_));
shess@chromium.org67361b32011-04-12 20:13:06[diff] [blame]56}
57
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]58boolReopen(){
59 db_.Close();
60return db_.Open(db_path_);
61}
shess@chromium.org67361b32011-04-12 20:13:06[diff] [blame]62
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]63protected:
64 base::ScopedTempDir temp_dir_;
65 base::FilePath db_path_;
66Database db_;
67
pkotwicz@chromium.org49dc4f22012-10-17 17:41:16[diff] [blame]68// The error code of the most recent error.
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]69int error_= SQLITE_OK;
pkotwicz@chromium.org49dc4f22012-10-17 17:41:16[diff] [blame]70// Original statement which has caused the error.
71 std::string sql_text_;
shess@chromium.org67361b32011-04-12 20:13:06[diff] [blame]72};
73
74// Do not include fts1 support, it is not useful, and nobody is
75// looking at it.
76TEST_F(SQLiteFeaturesTest,NoFTS1){
Victor Costan205b96dc2021-07-21 20:27:46[diff] [blame]77 sql::test::ScopedErrorExpecter expecter;
78 expecter.ExpectError(SQLITE_ERROR);
79 EXPECT_FALSE(db_.Execute("CREATE VIRTUAL TABLE foo USING fts1(x)"));
80 EXPECT_TRUE(expecter.SawExpectedErrors());
shess@chromium.org67361b32011-04-12 20:13:06[diff] [blame]81}
82
shess37437cb2015-03-11 20:24:46[diff] [blame]83// Do not include fts2 support, it is not useful, and nobody is
84// looking at it.
85TEST_F(SQLiteFeaturesTest,NoFTS2){
Victor Costan205b96dc2021-07-21 20:27:46[diff] [blame]86 sql::test::ScopedErrorExpecter expecter;
87 expecter.ExpectError(SQLITE_ERROR);
88 EXPECT_FALSE(db_.Execute("CREATE VIRTUAL TABLE foo USING fts2(x)"));
89 EXPECT_TRUE(expecter.SawExpectedErrors());
shess@chromium.org67361b32011-04-12 20:13:06[diff] [blame]90}
shess355d9a1e2015-01-10 00:42:29[diff] [blame]91
Andrew Paseltiner57621bf2025-03-31 15:18:00[diff] [blame]92// Do not include fts3 support.
93TEST_F(SQLiteFeaturesTest,NoFTS3){
94 sql::test::ScopedErrorExpecter expecter;
95 expecter.ExpectError(SQLITE_ERROR);
96 EXPECT_FALSE(db_.Execute("CREATE VIRTUAL TABLE foo USING fts3(x)"));
97 EXPECT_TRUE(expecter.SawExpectedErrors());
shess355d9a1e2015-01-10 00:42:29[diff] [blame]98}
shess355d9a1e2015-01-10 00:42:29[diff] [blame]99
shess156733db2015-01-21 21:52:24[diff] [blame]100// Verify that Chromium's SQLite is compiled with HAVE_USLEEP defined. With
101// HAVE_USLEEP, SQLite uses usleep() with millisecond granularity. Otherwise it
102// uses sleep() with second granularity.
103TEST_F(SQLiteFeaturesTest,UsesUsleep){
104 base::TimeTicks before= base::TimeTicks::Now();
105 sqlite3_sleep(1);
106 base::TimeDelta delta= base::TimeTicks::Now()- before;
107
shessf7fcc452017-04-19 22:10:41[diff] [blame]108// It is not impossible for this to be over 1000 if things are compiled
109// correctly, but that is very unlikely. Most platforms seem to be exactly
110// 1ms, with the rest at 2ms, and the worst observed cases was ASAN at 7ms.
shess156733db2015-01-21 21:52:24[diff] [blame]111 EXPECT_LT(delta.InMilliseconds(),1000);
112}
shess156733db2015-01-21 21:52:24[diff] [blame]113
engedybe80d53e2015-01-22 09:54:50[diff] [blame]114// Ensure that our SQLite version has working foreign key support with cascade
115// delete support.
116TEST_F(SQLiteFeaturesTest,ForeignKeySupport){
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]117 ASSERT_TRUE(db_.Execute("PRAGMA foreign_keys=1"));
118 ASSERT_TRUE(db_.Execute("CREATE TABLE parents (id INTEGER PRIMARY KEY)"));
119 ASSERT_TRUE(db_.Execute(
engedybe80d53e2015-01-22 09:54:50[diff] [blame]120"CREATE TABLE children ("
121" id INTEGER PRIMARY KEY,"
122" pid INTEGER NOT NULL REFERENCES parents(id) ON DELETE CASCADE)"));
Victor Costan1d868352018-06-26 19:06:48[diff] [blame]123staticconstchar kSelectParentsSql[]="SELECT * FROM parents ORDER BY id";
124staticconstchar kSelectChildrenSql[]="SELECT * FROM children ORDER BY id";
engedybe80d53e2015-01-22 09:54:50[diff] [blame]125
126// Inserting without a matching parent should fail with constraint violation.
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]127 EXPECT_EQ("",ExecuteWithResult(&db_, kSelectParentsSql));
Victor Costan205b96dc2021-07-21 20:27:46[diff] [blame]128{
129 sql::test::ScopedErrorExpecter expecter;
130 expecter.ExpectError(SQLITE_CONSTRAINT| SQLITE_CONSTRAINT_FOREIGNKEY);
131 EXPECT_FALSE(db_.Execute("INSERT INTO children VALUES (10, 1)"));
132 EXPECT_TRUE(expecter.SawExpectedErrors());
133}
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]134 EXPECT_EQ("",ExecuteWithResult(&db_, kSelectChildrenSql));
engedybe80d53e2015-01-22 09:54:50[diff] [blame]135
136// Inserting with a matching parent should work.
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]137 ASSERT_TRUE(db_.Execute("INSERT INTO parents VALUES (1)"));
138 EXPECT_EQ("1",ExecuteWithResults(&db_, kSelectParentsSql,"|","\n"));
139 EXPECT_TRUE(db_.Execute("INSERT INTO children VALUES (11, 1)"));
140 EXPECT_TRUE(db_.Execute("INSERT INTO children VALUES (12, 1)"));
shessf7fcc452017-04-19 22:10:41[diff] [blame]141 EXPECT_EQ("11|1\n12|1",
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]142ExecuteWithResults(&db_, kSelectChildrenSql,"|","\n"));
engedybe80d53e2015-01-22 09:54:50[diff] [blame]143
shessf7fcc452017-04-19 22:10:41[diff] [blame]144// Deleting the parent should cascade, deleting the children as well.
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]145 ASSERT_TRUE(db_.Execute("DELETE FROM parents"));
146 EXPECT_EQ("",ExecuteWithResult(&db_, kSelectParentsSql));
147 EXPECT_EQ("",ExecuteWithResult(&db_, kSelectChildrenSql));
engedybe80d53e2015-01-22 09:54:50[diff] [blame]148}
149
Victor Costanbc7ab062018-04-20 19:03:56[diff] [blame]150// Ensure that our SQLite version supports booleans.
151TEST_F(SQLiteFeaturesTest,BooleanSupport){
152 ASSERT_TRUE(
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]153 db_.Execute("CREATE TABLE flags ("
154" id INTEGER PRIMARY KEY,"
155" true_flag BOOL NOT NULL DEFAULT TRUE,"
156" false_flag BOOL NOT NULL DEFAULT FALSE)"));
157 ASSERT_TRUE(db_.Execute(
Victor Costanbc7ab062018-04-20 19:03:56[diff] [blame]158"ALTER TABLE flags ADD COLUMN true_flag2 BOOL NOT NULL DEFAULT TRUE"));
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]159 ASSERT_TRUE(db_.Execute(
Victor Costanbc7ab062018-04-20 19:03:56[diff] [blame]160"ALTER TABLE flags ADD COLUMN false_flag2 BOOL NOT NULL DEFAULT FALSE"));
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]161 ASSERT_TRUE(db_.Execute("INSERT INTO flags (id) VALUES (1)"));
Victor Costanbc7ab062018-04-20 19:03:56[diff] [blame]162
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]163 sql::Statement s(db_.GetUniqueStatement(
Victor Costanbc7ab062018-04-20 19:03:56[diff] [blame]164"SELECT true_flag, false_flag, true_flag2, false_flag2"
165" FROM flags WHERE id=1;"));
166 ASSERT_TRUE(s.Step());
167
Victor Costanf087c842018-04-27 00:47:50[diff] [blame]168 EXPECT_TRUE(s.ColumnBool(0))<<" default TRUE at table creation time";
Victor Costanbc7ab062018-04-20 19:03:56[diff] [blame]169 EXPECT_TRUE(!s.ColumnBool(1))<<" default FALSE at table creation time";
170
Victor Costanf087c842018-04-27 00:47:50[diff] [blame]171 EXPECT_TRUE(s.ColumnBool(2))<<" default TRUE added by altering the table";
Victor Costanbc7ab062018-04-20 19:03:56[diff] [blame]172 EXPECT_TRUE(!s.ColumnBool(3))<<" default FALSE added by altering the table";
173}
174
Victor Costan02bf6b6d2019-05-21 23:27:27[diff] [blame]175TEST_F(SQLiteFeaturesTest,IcuEnabled){
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]176 sql::Statement lower_en(db_.GetUniqueStatement("SELECT lower('I', 'en_us')"));
Victor Costan02bf6b6d2019-05-21 23:27:27[diff] [blame]177 ASSERT_TRUE(lower_en.Step());
178 EXPECT_EQ("i", lower_en.ColumnString(0));
179
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]180 sql::Statement lower_tr(db_.GetUniqueStatement("SELECT lower('I', 'tr_tr')"));
Victor Costan02bf6b6d2019-05-21 23:27:27[diff] [blame]181 ASSERT_TRUE(lower_tr.Step());
182 EXPECT_EQ("\u0131", lower_tr.ColumnString(0));
183}
184
shess53adf162015-12-17 22:07:26[diff] [blame]185// Verify that OS file writes are reflected in the memory mapping of a
186// memory-mapped file. Normally SQLite writes to memory-mapped files using
187// memcpy(), which should stay consistent. Our SQLite is slightly patched to
188// mmap read only, then write using OS file writes. If the memory-mapped
189// version doesn't reflect the OS file writes, SQLite's memory-mapped I/O should
190// be disabled on this platform using SQLITE_MAX_MMAP_SIZE=0.
191TEST_F(SQLiteFeaturesTest,Mmap){
192// Try to turn on mmap'ed I/O.
Avi Drissman70127472022-01-08 23:20:23[diff] [blame]193 std::ignore= db_.Execute("PRAGMA mmap_size = 1048576");
shess53adf162015-12-17 22:07:26[diff] [blame]194{
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]195 sql::Statement s(db_.GetUniqueStatement("PRAGMA mmap_size"));
shess53adf162015-12-17 22:07:26[diff] [blame]196
shess53adf162015-12-17 22:07:26[diff] [blame]197 ASSERT_TRUE(s.Step());
198 ASSERT_GT(s.ColumnInt64(0),0);
shess53adf162015-12-17 22:07:26[diff] [blame]199}
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]200 db_.Close();
shess53adf162015-12-17 22:07:26[diff] [blame]201
avi0b519202015-12-21 07:25:19[diff] [blame]202constuint32_t kFlags=
203 base::File::FLAG_OPEN| base::File::FLAG_READ| base::File::FLAG_WRITE;
shess53adf162015-12-17 22:07:26[diff] [blame]204char buf[4096];
205
206// Create a file with a block of '0', a block of '1', and a block of '2'.
207{
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]208 base::File f(db_path_, kFlags);
shess53adf162015-12-17 22:07:26[diff] [blame]209 ASSERT_TRUE(f.IsValid());
210 memset(buf,'0',sizeof(buf));
211 ASSERT_EQ(f.Write(0*sizeof(buf), buf,sizeof(buf)),(int)sizeof(buf));
212
213 memset(buf,'1',sizeof(buf));
214 ASSERT_EQ(f.Write(1*sizeof(buf), buf,sizeof(buf)),(int)sizeof(buf));
215
216 memset(buf,'2',sizeof(buf));
217 ASSERT_EQ(f.Write(2*sizeof(buf), buf,sizeof(buf)),(int)sizeof(buf));
218}
219
220// mmap the file and verify that everything looks right.
221{
222 base::MemoryMappedFile m;
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]223 ASSERT_TRUE(m.Initialize(db_path_));
shess53adf162015-12-17 22:07:26[diff] [blame]224
225 memset(buf,'0',sizeof(buf));
226 ASSERT_EQ(0, memcmp(buf, m.data()+0*sizeof(buf),sizeof(buf)));
227
228 memset(buf,'1',sizeof(buf));
229 ASSERT_EQ(0, memcmp(buf, m.data()+1*sizeof(buf),sizeof(buf)));
230
231 memset(buf,'2',sizeof(buf));
232 ASSERT_EQ(0, memcmp(buf, m.data()+2*sizeof(buf),sizeof(buf)));
233
234// Scribble some '3' into the first page of the file, and verify that it
235// looks the same in the memory mapping.
236{
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]237 base::File f(db_path_, kFlags);
shess53adf162015-12-17 22:07:26[diff] [blame]238 ASSERT_TRUE(f.IsValid());
239 memset(buf,'3',sizeof(buf));
240 ASSERT_EQ(f.Write(0*sizeof(buf), buf,sizeof(buf)),(int)sizeof(buf));
241}
242 ASSERT_EQ(0, memcmp(buf, m.data()+0*sizeof(buf),sizeof(buf)));
243
244// Repeat with a single '4' in case page-sized blocks are different.
245constsize_t kOffset=1*sizeof(buf)+123;
246 ASSERT_NE('4', m.data()[kOffset]);
247{
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]248 base::File f(db_path_, kFlags);
shess53adf162015-12-17 22:07:26[diff] [blame]249 ASSERT_TRUE(f.IsValid());
250 buf[0]='4';
251 ASSERT_EQ(f.Write(kOffset, buf,1),1);
252}
253 ASSERT_EQ('4', m.data()[kOffset]);
254}
255}
shess53adf162015-12-17 22:07:26[diff] [blame]256
shess001ae162016-10-20 04:04:32[diff] [blame]257// Verify that http://crbug.com/248608 is fixed. In this bug, the
258// compiled regular expression is effectively cached with the prepared
259// statement, causing errors if the regular expression is rebound.
260TEST_F(SQLiteFeaturesTest,CachedRegexp){
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]261 ASSERT_TRUE(db_.Execute("CREATE TABLE r (id INTEGER UNIQUE, x TEXT)"));
262 ASSERT_TRUE(db_.Execute("INSERT INTO r VALUES (1, 'this is a test')"));
263 ASSERT_TRUE(db_.Execute("INSERT INTO r VALUES (2, 'that was a test')"));
264 ASSERT_TRUE(db_.Execute("INSERT INTO r VALUES (3, 'this is a stickup')"));
265 ASSERT_TRUE(db_.Execute("INSERT INTO r VALUES (4, 'that sucks')"));
shess001ae162016-10-20 04:04:32[diff] [blame]266
Victor Costan1d868352018-06-26 19:06:48[diff] [blame]267staticconstchar kSimpleSql[]="SELECT SUM(id) FROM r WHERE x REGEXP ?";
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]268 sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE, kSimpleSql));
shess001ae162016-10-20 04:04:32[diff] [blame]269
270 s.BindString(0,"this.*");
271 ASSERT_TRUE(s.Step());
272 EXPECT_EQ(4, s.ColumnInt(0));
273
274 s.Reset(true);
275 s.BindString(0,"that.*");
276 ASSERT_TRUE(s.Step());
277 EXPECT_EQ(6, s.ColumnInt(0));
278
279 s.Reset(true);
280 s.BindString(0,".*test");
281 ASSERT_TRUE(s.Step());
282 EXPECT_EQ(3, s.ColumnInt(0));
283
284 s.Reset(true);
285 s.BindString(0,".* s[a-z]+");
286 ASSERT_TRUE(s.Step());
287 EXPECT_EQ(7, s.ColumnInt(0));
288}
289
Victor Costandcffe38e2022-02-28 17:39:02[diff] [blame]290TEST_F(SQLiteFeaturesTest,JsonIsDisabled){
291staticconstexprchar kCreateSql[]=
292"CREATE TABLE rows(id INTEGER PRIMARY KEY NOT NULL, data TEXT NOT NULL)";
293 ASSERT_TRUE(db_.Execute(kCreateSql));
294 ASSERT_TRUE(db_.Execute("INSERT INTO rows(data) VALUES('{\"a\": 1}')"));
295
296{
297 sql::test::ScopedErrorExpecter expecter;
298 expecter.ExpectError(SQLITE_ERROR);
299 EXPECT_FALSE(db_.Execute("SELECT data -> '$.a' FROM rows"));
300 EXPECT_TRUE(expecter.SawExpectedErrors());
301}
302}
303
Victor Costana31f9c92022-02-28 19:38:00[diff] [blame]304TEST_F(SQLiteFeaturesTest,WindowFunctionsAreDisabled){
305staticconstexprchar kCreateSql[]=
306"CREATE TABLE rows(id INTEGER PRIMARY KEY NOT NULL, data TEXT NOT NULL)";
307 ASSERT_TRUE(db_.Execute(kCreateSql));
308 ASSERT_TRUE(db_.Execute("INSERT INTO rows(id, data) VALUES(1, 'a')"));
309 ASSERT_TRUE(db_.Execute("INSERT INTO rows(id, data) VALUES(2, 'c')"));
310 ASSERT_TRUE(db_.Execute("INSERT INTO rows(id, data) VALUES(3, 'b')"));
311
312{
313 sql::test::ScopedErrorExpecter expecter;
314 expecter.ExpectError(SQLITE_ERROR);
315 EXPECT_FALSE(db_.Execute(
316"SELECT data, row_number() OVER (ORDER BY data) AS rank FROM rows "
317"ORDER BY id"));
318 EXPECT_TRUE(expecter.SawExpectedErrors());
319}
320}
321
Victor Costan9031b97e2022-02-18 01:55:36[diff] [blame]322// The "No Isolation Between Operations On The Same Database Connection" section
Victor Costan8655b772022-02-18 06:58:45[diff] [blame]323// in https://sqlite.org/isolation.html implies that it's safe to issue multiple
324// concurrent SELECTs against the same area.
325//
326// Chrome code is allowed to rely on this guarantee. So, we test for it here, to
327// catch any regressions introduced by SQLite upgrades.
328TEST_F(SQLiteFeaturesTest,ConcurrentSelects){
329 ASSERT_TRUE(db_.Execute("CREATE TABLE rows(id INTEGER PRIMARY KEY, t TEXT)"));
330 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(2, 'two')"));
331 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(3, 'three')"));
332 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(4, 'four')"));
333
334staticconstchar kSelectAllSql[]="SELECT id,t FROM rows";
335staticconstchar kSelectEvenSql[]="SELECT id,t FROM rows WHERE id%2=0";
336
337 sql::Statement select1(db_.GetCachedStatement(SQL_FROM_HERE, kSelectEvenSql));
338 sql::Statement select2(db_.GetCachedStatement(SQL_FROM_HERE, kSelectEvenSql));
339 sql::Statement select3(db_.GetCachedStatement(SQL_FROM_HERE, kSelectAllSql));
340
341 ASSERT_TRUE(select1.Step());
342 EXPECT_EQ(select1.ColumnInt(0),2);
343 EXPECT_EQ(select1.ColumnString(1),"two");
344
345 ASSERT_TRUE(select2.Step());
346 EXPECT_EQ(select2.ColumnInt(0),2);
347 EXPECT_EQ(select2.ColumnString(1),"two");
348
349 ASSERT_TRUE(select3.Step());
350 EXPECT_EQ(select3.ColumnInt(0),2);
351 EXPECT_EQ(select3.ColumnString(1),"two");
352
353 ASSERT_TRUE(select1.Step());
354 EXPECT_EQ(select1.ColumnInt(0),4);
355 EXPECT_EQ(select1.ColumnString(1),"four");
356
357 ASSERT_TRUE(select3.Step());
358 EXPECT_EQ(select3.ColumnInt(0),3);
359 EXPECT_EQ(select3.ColumnString(1),"three");
360
361 ASSERT_TRUE(select2.Step());
362 EXPECT_EQ(select2.ColumnInt(0),4);
363 EXPECT_EQ(select2.ColumnString(1),"four");
364
365 EXPECT_FALSE(select2.Step());
366
367 ASSERT_TRUE(select3.Step());
368 EXPECT_EQ(select3.ColumnInt(0),4);
369 EXPECT_EQ(select3.ColumnString(1),"four");
370
371 select2.Reset(/*clear_bound_vars=*/true);
372 ASSERT_TRUE(select2.Step());
373 EXPECT_EQ(select2.ColumnInt(0),2);
374 EXPECT_EQ(select2.ColumnString(1),"two");
375
376 EXPECT_FALSE(select1.Step());
377}
378
379// The "No Isolation Between Operations On The Same Database Connection" section
Victor Costan9031b97e2022-02-18 01:55:36[diff] [blame]380// in https://sqlite.org/isolation.html states that it's safe to DELETE a row
381// that was just returned by sqlite_step() executing a SELECT statement.
382//
383// Chrome code is allowed to rely on this guarantee. So, we test for it here, to
384// catch any regressions introduced by SQLite upgrades.
385TEST_F(SQLiteFeaturesTest,DeleteCurrentlySelectedRow){
386 ASSERT_TRUE(db_.Execute("CREATE TABLE rows(id INTEGER PRIMARY KEY, t TEXT)"));
387 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(2, 'two')"));
388 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(3, 'three')"));
389 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(4, 'four')"));
390 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(5, 'five')"));
391 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(6, 'six')"));
392
Victor Costan8655b772022-02-18 06:58:45[diff] [blame]393staticconstchar kSelectEvenSql[]="SELECT id,t FROM rows WHERE id%2=0";
394 sql::Statement select(db_.GetCachedStatement(SQL_FROM_HERE, kSelectEvenSql));
Victor Costan9031b97e2022-02-18 01:55:36[diff] [blame]395
396 ASSERT_TRUE(select.Step());
397 ASSERT_EQ(select.ColumnInt(0),2);
398 ASSERT_EQ(select.ColumnString(1),"two");
399 ASSERT_TRUE(db_.Execute("DELETE FROM rows WHERE id=2"));
400
401 ASSERT_TRUE(select.Step());
402 ASSERT_EQ(select.ColumnInt(0),4);
403 ASSERT_EQ(select.ColumnString(1),"four");
404 ASSERT_TRUE(db_.Execute("DELETE FROM rows WHERE id=4"));
405
406 ASSERT_TRUE(select.Step());
407 ASSERT_EQ(select.ColumnInt(0),6);
408 ASSERT_EQ(select.ColumnString(1),"six");
409 ASSERT_TRUE(db_.Execute("DELETE FROM rows WHERE id=6"));
410
411 EXPECT_FALSE(select.Step());
Victor Costan4ad0798d2022-02-23 03:46:31[diff] [blame]412
413// Check that the DELETEs were applied as expected.
414
415staticconstchar kSelectAllSql[]="SELECT id,t FROM rows";
416 sql::Statement select_all(
417 db_.GetCachedStatement(SQL_FROM_HERE, kSelectAllSql));
418 std::vector<int> remaining_ids;
419 std::vector<std::string> remaining_texts;
420while(select_all.Step()){
421 remaining_ids.push_back(select_all.ColumnInt(0));
422 remaining_texts.push_back(select_all.ColumnString(1));
423}
424
425 std::vector<int> expected_remaining_ids={3,5};
426 EXPECT_EQ(expected_remaining_ids, remaining_ids);
427 std::vector<std::string> expected_remaining_texts={"three","five"};
428 EXPECT_EQ(expected_remaining_texts, remaining_texts);
Victor Costan9031b97e2022-02-18 01:55:36[diff] [blame]429}
430
431// The "No Isolation Between Operations On The Same Database Connection" section
432// in https://sqlite.org/isolation.html states that it's safe to DELETE a row
433// that was previously by sqlite_step() executing a SELECT statement.
434//
435// Chrome code is allowed to rely on this guarantee. So, we test for it here, to
436// catch any regressions introduced by SQLite upgrades.
437TEST_F(SQLiteFeaturesTest,DeletePreviouslySelectedRows){
438 ASSERT_TRUE(db_.Execute("CREATE TABLE rows(id INTEGER PRIMARY KEY, t TEXT)"));
439 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(2, 'two')"));
440 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(3, 'three')"));
441 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(4, 'four')"));
442 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(5, 'five')"));
443 ASSERT_TRUE(db_.Execute("INSERT INTO rows VALUES(6, 'six')"));
444
Victor Costan8655b772022-02-18 06:58:45[diff] [blame]445staticconstchar kSelectEvenSql[]="SELECT id,t FROM rows WHERE id%2=0";
446 sql::Statement select(db_.GetCachedStatement(SQL_FROM_HERE, kSelectEvenSql));
Victor Costan9031b97e2022-02-18 01:55:36[diff] [blame]447
448 ASSERT_TRUE(select.Step());
449 ASSERT_EQ(select.ColumnInt(0),2);
450 ASSERT_EQ(select.ColumnString(1),"two");
451
452 ASSERT_TRUE(select.Step());
453 ASSERT_EQ(select.ColumnInt(0),4);
454 ASSERT_EQ(select.ColumnString(1),"four");
455 ASSERT_TRUE(db_.Execute("DELETE FROM rows WHERE id=2"));
456
457 ASSERT_TRUE(select.Step());
458 ASSERT_EQ(select.ColumnInt(0),6);
459 ASSERT_EQ(select.ColumnString(1),"six");
460 ASSERT_TRUE(db_.Execute("DELETE FROM rows WHERE id=4"));
461 ASSERT_TRUE(db_.Execute("DELETE FROM rows WHERE id=6"));
462
463 EXPECT_FALSE(select.Step());
Victor Costan4ad0798d2022-02-23 03:46:31[diff] [blame]464
465// Check that the DELETEs were applied as expected.
466
467staticconstchar kSelectAllSql[]="SELECT id,t FROM rows";
468 sql::Statement select_all(
469 db_.GetCachedStatement(SQL_FROM_HERE, kSelectAllSql));
470 std::vector<int> remaining_ids;
471 std::vector<std::string> remaining_texts;
472while(select_all.Step()){
473 remaining_ids.push_back(select_all.ColumnInt(0));
474 remaining_texts.push_back(select_all.ColumnString(1));
475}
476
477 std::vector<int> expected_remaining_ids={3,5};
478 EXPECT_EQ(expected_remaining_ids, remaining_ids);
479 std::vector<std::string> expected_remaining_texts={"three","five"};
480 EXPECT_EQ(expected_remaining_texts, remaining_texts);
481}
482
483// The "No Isolation Between Operations On The Same Database Connection" section
484// in https://sqlite.org/isolation.html states that it's safe to DELETE a row
485// while a SELECT statement executes, but the DELETEd row may or may not show up
486// in the SELECT results. (See the test above for a case where the DELETEd row
487// is guaranteed to now show up in the SELECT results.)
488//
489// This seems to imply that DELETEing from a table that is not read by the
490// concurrent SELECT statement is safe and well-defined, as the DELETEd row(s)
491// cannot possibly show up in the SELECT results.
492//
493// Chrome features are allowed to rely on the implication above, because it
494// comes in very handy for DELETEing data across multiple tables. This test
495// ensures that our assumption remains valid.
496TEST_F(SQLiteFeaturesTest,DeleteWhileSelectingFromDifferentTable){
497 ASSERT_TRUE(db_.Execute("CREATE TABLE main(id INTEGER PRIMARY KEY, t TEXT)"));
498 ASSERT_TRUE(db_.Execute("INSERT INTO main VALUES(2, 'two')"));
499 ASSERT_TRUE(db_.Execute("INSERT INTO main VALUES(3, 'three')"));
500 ASSERT_TRUE(db_.Execute("INSERT INTO main VALUES(4, 'four')"));
501 ASSERT_TRUE(db_.Execute("INSERT INTO main VALUES(5, 'five')"));
502 ASSERT_TRUE(db_.Execute("INSERT INTO main VALUES(6, 'six')"));
503
504 ASSERT_TRUE(
505 db_.Execute("CREATE TABLE other(id INTEGER PRIMARY KEY, t TEXT)"));
506 ASSERT_TRUE(db_.Execute("INSERT INTO other VALUES(1, 'one')"));
507 ASSERT_TRUE(db_.Execute("INSERT INTO other VALUES(2, 'two')"));
508 ASSERT_TRUE(db_.Execute("INSERT INTO other VALUES(3, 'three')"));
509 ASSERT_TRUE(db_.Execute("INSERT INTO other VALUES(4, 'four')"));
510 ASSERT_TRUE(db_.Execute("INSERT INTO other VALUES(5, 'five')"));
511 ASSERT_TRUE(db_.Execute("INSERT INTO other VALUES(6, 'six')"));
512 ASSERT_TRUE(db_.Execute("INSERT INTO other VALUES(7, 'seven')"));
513
514staticconstchar kSelectEvenSql[]="SELECT id,t FROM main WHERE id%2=0";
515 sql::Statement select(db_.GetCachedStatement(SQL_FROM_HERE, kSelectEvenSql));
516
517 ASSERT_TRUE(select.Step());
518 ASSERT_EQ(select.ColumnInt(0),2);
519 ASSERT_EQ(select.ColumnString(1),"two");
520 EXPECT_TRUE(db_.Execute("DELETE FROM other WHERE id=2"));
521
522 ASSERT_TRUE(select.Step());
523 ASSERT_EQ(select.ColumnInt(0),4);
524 ASSERT_EQ(select.ColumnString(1),"four");
525
526 ASSERT_TRUE(select.Step());
527 ASSERT_EQ(select.ColumnInt(0),6);
528 ASSERT_EQ(select.ColumnString(1),"six");
529 ASSERT_TRUE(db_.Execute("DELETE FROM other WHERE id=4"));
530 ASSERT_TRUE(db_.Execute("DELETE FROM other WHERE id=5"));
531 ASSERT_TRUE(db_.Execute("DELETE FROM other WHERE id=6"));
532
533 EXPECT_FALSE(select.Step());
534
535// Check that the DELETEs were applied as expected.
536
537staticconstchar kSelectAllSql[]="SELECT id,t FROM other";
538 sql::Statement select_all(
539 db_.GetCachedStatement(SQL_FROM_HERE, kSelectAllSql));
540 std::vector<int> remaining_ids;
541 std::vector<std::string> remaining_texts;
542while(select_all.Step()){
543 remaining_ids.push_back(select_all.ColumnInt(0));
544 remaining_texts.push_back(select_all.ColumnString(1));
545}
546
547 std::vector<int> expected_remaining_ids={1,3,7};
548 EXPECT_EQ(expected_remaining_ids, remaining_ids);
549 std::vector<std::string> expected_remaining_texts={"one","three","seven"};
550 EXPECT_EQ(expected_remaining_texts, remaining_texts);
Victor Costan9031b97e2022-02-18 01:55:36[diff] [blame]551}
552
Victor Costan70c5d6902022-02-22 18:45:05[diff] [blame]553// The "No Isolation Between Operations On The Same Database Connection" section
554// in https://sqlite.org/isolation.html states that it's possible to INSERT in
555// a table while concurrently executing a SELECT statement reading from it, but
556// it's undefined whether the row will show up in the SELECT statement's results
557// or not.
558//
559// Given this ambiguity, Chrome code is not allowed to INSERT in the same table
560// as a concurrent SELECT. However, it is allowed to INSERT in a table which is
561// not covered by SELECT, because this greatly simplifes migrations. So, we test
562// the ability to INSERT in a table while SELECTing from another table, to
563// catch any regressions introduced by SQLite upgrades.
564TEST_F(SQLiteFeaturesTest,InsertWhileSelectingFromDifferentTable){
565 ASSERT_TRUE(db_.Execute("CREATE TABLE src(id INTEGER PRIMARY KEY, t TEXT)"));
566 ASSERT_TRUE(db_.Execute("CREATE TABLE dst(id INTEGER PRIMARY KEY, t TEXT)"));
567 ASSERT_TRUE(db_.Execute("INSERT INTO src VALUES(2, 'two')"));
568 ASSERT_TRUE(db_.Execute("INSERT INTO src VALUES(3, 'three')"));
569 ASSERT_TRUE(db_.Execute("INSERT INTO src VALUES(4, 'four')"));
570 ASSERT_TRUE(db_.Execute("INSERT INTO src VALUES(5, 'five')"));
571 ASSERT_TRUE(db_.Execute("INSERT INTO src VALUES(6, 'six')"));
572
573staticconstchar kSelectSrcEvenSql[]="SELECT id,t FROM src WHERE id%2=0";
574 sql::Statement select_src(
575 db_.GetCachedStatement(SQL_FROM_HERE, kSelectSrcEvenSql));
576
577 ASSERT_TRUE(select_src.Step());
578 ASSERT_EQ(select_src.ColumnInt(0),2);
579 ASSERT_EQ(select_src.ColumnString(1),"two");
Victor Costan4ad0798d2022-02-23 03:46:31[diff] [blame]580 EXPECT_TRUE(db_.Execute("INSERT INTO dst VALUES(2, 'two')"));
Victor Costan70c5d6902022-02-22 18:45:05[diff] [blame]581 ASSERT_TRUE(db_.Execute("INSERT INTO dst VALUES(3, 'three')"));
582
583 ASSERT_TRUE(select_src.Step());
584 ASSERT_EQ(select_src.ColumnInt(0),4);
585 ASSERT_EQ(select_src.ColumnString(1),"four");
586 ASSERT_TRUE(db_.Execute("INSERT INTO dst VALUES(4, 'four')"));
587
588 ASSERT_TRUE(select_src.Step());
589 ASSERT_EQ(select_src.ColumnInt(0),6);
590 ASSERT_EQ(select_src.ColumnString(1),"six");
591 ASSERT_TRUE(db_.Execute("INSERT INTO dst VALUES(5, 'five')"));
592 ASSERT_TRUE(db_.Execute("INSERT INTO dst VALUES(6, 'six')"));
593
594 EXPECT_FALSE(select_src.Step());
595
596staticconstchar kSelectDstSql[]="SELECT id,t FROM dst";
597 sql::Statement select_dst(
598 db_.GetCachedStatement(SQL_FROM_HERE, kSelectDstSql));
599 std::vector<int> dst_ids;
600 std::vector<std::string> dst_texts;
601while(select_dst.Step()){
602 dst_ids.push_back(select_dst.ColumnInt(0));
603 dst_texts.push_back(select_dst.ColumnString(1));
604}
605
Victor Costan4ad0798d2022-02-23 03:46:31[diff] [blame]606 std::vector<int> expected_dst_ids={2,3,4,5,6};
607 EXPECT_EQ(expected_dst_ids, dst_ids);
608 std::vector<std::string> expected_dst_texts={"two","three","four","five",
609"six"};
610 EXPECT_EQ(expected_dst_texts, dst_texts);
Victor Costan70c5d6902022-02-22 18:45:05[diff] [blame]611}
612
Xiaohan Wang7d09c5e2022-01-08 02:37:36[diff] [blame]613#if BUILDFLAG(IS_APPLE)
Ali Juma47cc95af2021-12-07 14:49:33[diff] [blame]614// If a database file is marked to be excluded from backups, verify that journal
615// files are also excluded.
Victor Costan653b14f2018-07-23 19:32:33[diff] [blame]616TEST_F(SQLiteFeaturesTest,TimeMachine){
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]617 ASSERT_TRUE(db_.Execute("CREATE TABLE t (id INTEGER PRIMARY KEY)"));
618 db_.Close();
shess5f2c3442017-01-24 02:15:10[diff] [blame]619
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]620 base::FilePath journal_path= sql::Database::JournalPath(db_path_);
621 ASSERT_TRUE(base::PathExists(db_path_));
622 ASSERT_TRUE(base::PathExists(journal_path));
shess5f2c3442017-01-24 02:15:10[diff] [blame]623
624// Not excluded to start.
Avi Drissmand4f07082023-05-12 18:05:44[diff] [blame]625 EXPECT_FALSE(base::apple::GetBackupExclusion(db_path_));
626 EXPECT_FALSE(base::apple::GetBackupExclusion(journal_path));
shess5f2c3442017-01-24 02:15:10[diff] [blame]627
628// Exclude the main database file.
Avi Drissmand4f07082023-05-12 18:05:44[diff] [blame]629 EXPECT_TRUE(base::apple::SetBackupExclusion(db_path_));
shess5f2c3442017-01-24 02:15:10[diff] [blame]630
Avi Drissmand4f07082023-05-12 18:05:44[diff] [blame]631 EXPECT_TRUE(base::apple::GetBackupExclusion(db_path_));
632 EXPECT_FALSE(base::apple::GetBackupExclusion(journal_path));
shess5f2c3442017-01-24 02:15:10[diff] [blame]633
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]634 EXPECT_TRUE(db_.Open(db_path_));
635 ASSERT_TRUE(db_.Execute("INSERT INTO t VALUES (1)"));
Avi Drissmand4f07082023-05-12 18:05:44[diff] [blame]636 EXPECT_TRUE(base::apple::GetBackupExclusion(db_path_));
637 EXPECT_TRUE(base::apple::GetBackupExclusion(journal_path));
shess5f2c3442017-01-24 02:15:10[diff] [blame]638
639// TODO(shess): In WAL mode this will touch -wal and -shm files. -shm files
640// could be always excluded.
641}
642#endif
643
Xiaohan Wang7d09c5e2022-01-08 02:37:36[diff] [blame]644#if !BUILDFLAG(IS_FUCHSIA)
shessf7fcc452017-04-19 22:10:41[diff] [blame]645// SQLite WAL mode defaults to checkpointing the WAL on close. This would push
646// additional work into Chromium shutdown. Verify that SQLite supports a config
647// option to not checkpoint on close.
648TEST_F(SQLiteFeaturesTest,WALNoClose){
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]649 base::FilePath wal_path= sql::Database::WriteAheadLogPath(db_path_);
shessf7fcc452017-04-19 22:10:41[diff] [blame]650
651// Turn on WAL mode, then verify that the mode changed (WAL is supported).
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]652 ASSERT_TRUE(db_.Execute("PRAGMA journal_mode = WAL"));
653 ASSERT_EQ("wal",ExecuteWithResult(&db_,"PRAGMA journal_mode"));
shessf7fcc452017-04-19 22:10:41[diff] [blame]654
655// The WAL file is created lazily on first change.
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]656 ASSERT_TRUE(db_.Execute("CREATE TABLE foo (a, b)"));
shessf7fcc452017-04-19 22:10:41[diff] [blame]657
658// By default, the WAL is checkpointed then deleted on close.
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]659 ASSERT_TRUE(base::PathExists(wal_path));
660 db_.Close();
661 ASSERT_FALSE(base::PathExists(wal_path));
shessf7fcc452017-04-19 22:10:41[diff] [blame]662
663// Reopen and configure the database to not checkpoint WAL on close.
664 ASSERT_TRUE(Reopen());
Victor Costan49a903a2021-05-07 22:21:00[diff] [blame]665 ASSERT_TRUE(db_.Execute("PRAGMA journal_mode = WAL"));
666 ASSERT_TRUE(db_.Execute("ALTER TABLE foo ADD COLUMN c"));
667 ASSERT_EQ(
668 SQLITE_OK,
669 sqlite3_db_config(db_.db_, SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE,1,nullptr));
670 ASSERT_TRUE(base::PathExists(wal_path));
671 db_.Close();
672 ASSERT_TRUE(base::PathExists(wal_path));
shessf7fcc452017-04-19 22:10:41[diff] [blame]673}
674#endif
675
676}// namespace sql
[8]ページ先頭
©2009-2025 Movatter.jp