1 /* Copyright 2015 The TensorFlow Authors. All Rights Reserved.
2
3 Licensed under the Apache License, Version 2.0 (the "License");
4 you may not use this file except in compliance with the License.
5 You may obtain a copy of the License at
6
7 http://www.apache.org/licenses/LICENSE-2.0
8
9 Unless required by applicable law or agreed to in writing, software
10 distributed under the License is distributed on an "AS IS" BASIS,
11 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12 See the License for the specific language governing permissions and
13 limitations under the License.
14 ==============================================================================*/
15 #include "tensorflow/core/lib/db/sqlite.h"
16
17 #include <array>
18 #include <climits>
19
20 #include "tensorflow/core/lib/core/status_test_util.h"
21 #include "tensorflow/core/lib/core/stringpiece.h"
22 #include "tensorflow/core/lib/io/path.h"
23 #include "tensorflow/core/lib/strings/stringprintf.h"
24 #include "tensorflow/core/platform/test.h"
25
26 namespace tensorflow {
27 namespace {
28
29 class SqliteTest : public ::testing::Test {
30 protected:
SetUp()31 void SetUp() override {
32 TF_ASSERT_OK(Sqlite::Open(":memory:", SQLITE_OPEN_READWRITE, &db_));
33 db_->PrepareOrDie("CREATE TABLE T (a BLOB, b BLOB)").StepAndResetOrDie();
34 }
35
TearDown()36 void TearDown() override { db_->Unref(); }
37
38 Sqlite* db_;
39 bool is_done_;
40 };
41
TEST_F(SqliteTest,InsertAndSelectInt)42 TEST_F(SqliteTest, InsertAndSelectInt) {
43 auto stmt = db_->PrepareOrDie("INSERT INTO T (a, b) VALUES (?, ?)");
44 stmt.BindInt(1, 3);
45 stmt.BindInt(2, -7);
46 TF_ASSERT_OK(stmt.StepAndReset());
47 stmt.BindInt(1, 123);
48 stmt.BindInt(2, -123);
49 TF_ASSERT_OK(stmt.StepAndReset());
50 stmt = db_->PrepareOrDie("SELECT a, b FROM T ORDER BY b");
51 TF_ASSERT_OK(stmt.Step(&is_done_));
52 ASSERT_FALSE(is_done_);
53 EXPECT_EQ(123, stmt.ColumnInt(0));
54 EXPECT_EQ(-123, stmt.ColumnInt(1));
55 TF_ASSERT_OK(stmt.Step(&is_done_));
56 ASSERT_FALSE(is_done_);
57 EXPECT_EQ(3, stmt.ColumnInt(0));
58 EXPECT_EQ(-7, stmt.ColumnInt(1));
59 TF_ASSERT_OK(stmt.Step(&is_done_));
60 ASSERT_TRUE(is_done_);
61 }
62
TEST_F(SqliteTest,InsertAndSelectDouble)63 TEST_F(SqliteTest, InsertAndSelectDouble) {
64 auto stmt = db_->PrepareOrDie("INSERT INTO T (a, b) VALUES (?, ?)");
65 stmt.BindDouble(1, 6.28318530);
66 stmt.BindDouble(2, 1.61803399);
67 TF_ASSERT_OK(stmt.StepAndReset());
68 stmt = db_->PrepareOrDie("SELECT a, b FROM T");
69 TF_ASSERT_OK(stmt.Step(&is_done_));
70 EXPECT_EQ(6.28318530, stmt.ColumnDouble(0));
71 EXPECT_EQ(1.61803399, stmt.ColumnDouble(1));
72 EXPECT_EQ(6, stmt.ColumnInt(0));
73 EXPECT_EQ(1, stmt.ColumnInt(1));
74 }
75
76 #ifdef DSQLITE_ENABLE_JSON1
TEST_F(SqliteTest,Json1Extension)77 TEST_F(SqliteTest, Json1Extension) {
78 string s1 = "{\"key\": 42}";
79 string s2 = "{\"key\": \"value\"}";
80 auto stmt = db_->PrepareOrDie("INSERT INTO T (a, b) VALUES (?, ?)");
81 stmt.BindText(1, s1);
82 stmt.BindText(2, s2);
83 TF_ASSERT_OK(stmt.StepAndReset());
84 stmt = db_->PrepareOrDie("SELECT json_extract(a, '$.key'), json_extract(b, '$.key') FROM T");
85 TF_ASSERT_OK(stmt.Step(&is_done_));
86 EXPECT_EQ(42, stmt.ColumnInt(0));
87 EXPECT_EQ("value", stmt.ColumnString(1));
88 }
89 #endif //DSQLITE_ENABLE_JSON1
90
TEST_F(SqliteTest,NulCharsInString)91 TEST_F(SqliteTest, NulCharsInString) {
92 string s; // XXX: Want to write {2, '\0'} but not sure why not.
93 s.append(static_cast<size_t>(2), '\0');
94 auto stmt = db_->PrepareOrDie("INSERT INTO T (a, b) VALUES (?, ?)");
95 stmt.BindBlob(1, s);
96 stmt.BindText(2, s);
97 TF_ASSERT_OK(stmt.StepAndReset());
98 stmt = db_->PrepareOrDie("SELECT a, b FROM T");
99 TF_ASSERT_OK(stmt.Step(&is_done_));
100 EXPECT_EQ(2, stmt.ColumnSize(0));
101 EXPECT_EQ(2, stmt.ColumnString(0).size());
102 EXPECT_EQ('\0', stmt.ColumnString(0).at(0));
103 EXPECT_EQ('\0', stmt.ColumnString(0).at(1));
104 EXPECT_EQ(2, stmt.ColumnSize(1));
105 EXPECT_EQ(2, stmt.ColumnString(1).size());
106 EXPECT_EQ('\0', stmt.ColumnString(1).at(0));
107 EXPECT_EQ('\0', stmt.ColumnString(1).at(1));
108 }
109
TEST_F(SqliteTest,Unicode)110 TEST_F(SqliteTest, Unicode) {
111 string s = "要依法治国是赞美那些谁是公义的和惩罚恶人。 - 韩非";
112 auto stmt = db_->PrepareOrDie("INSERT INTO T (a, b) VALUES (?, ?)");
113 stmt.BindBlob(1, s);
114 stmt.BindText(2, s);
115 TF_ASSERT_OK(stmt.StepAndReset());
116 stmt = db_->PrepareOrDie("SELECT a, b FROM T");
117 TF_ASSERT_OK(stmt.Step(&is_done_));
118 EXPECT_EQ(s, stmt.ColumnString(0));
119 EXPECT_EQ(s, stmt.ColumnString(1));
120 }
121
TEST_F(SqliteTest,StepAndResetClearsBindings)122 TEST_F(SqliteTest, StepAndResetClearsBindings) {
123 auto stmt = db_->PrepareOrDie("INSERT INTO T (a, b) VALUES (?, ?)");
124 stmt.BindInt(1, 1);
125 stmt.BindInt(2, 123);
126 TF_ASSERT_OK(stmt.StepAndReset());
127 stmt.BindInt(1, 2);
128 TF_ASSERT_OK(stmt.StepAndReset());
129 stmt = db_->PrepareOrDie("SELECT b FROM T ORDER BY a");
130 TF_ASSERT_OK(stmt.Step(&is_done_));
131 EXPECT_EQ(123, stmt.ColumnInt(0));
132 TF_ASSERT_OK(stmt.Step(&is_done_));
133 EXPECT_EQ(SQLITE_NULL, stmt.ColumnType(0));
134 }
135
TEST_F(SqliteTest,SafeBind)136 TEST_F(SqliteTest, SafeBind) {
137 string s = "hello";
138 auto stmt = db_->PrepareOrDie("INSERT INTO T (a, b) VALUES (?, ?)");
139 stmt.BindBlob(1, s);
140 stmt.BindText(2, s);
141 s.at(0) = 'y';
142 TF_ASSERT_OK(stmt.StepAndReset());
143 stmt = db_->PrepareOrDie("SELECT a, b FROM T");
144 TF_ASSERT_OK(stmt.Step(&is_done_));
145 EXPECT_EQ("hello", stmt.ColumnString(0));
146 EXPECT_EQ("hello", stmt.ColumnString(1));
147 }
148
TEST_F(SqliteTest,UnsafeBind)149 TEST_F(SqliteTest, UnsafeBind) {
150 string s = "hello";
151 auto stmt = db_->PrepareOrDie("INSERT INTO T (a, b) VALUES (?, ?)");
152 stmt.BindBlobUnsafe(1, s);
153 stmt.BindTextUnsafe(2, s);
154 s.at(0) = 'y';
155 TF_ASSERT_OK(stmt.StepAndReset());
156 stmt = db_->PrepareOrDie("SELECT a, b FROM T");
157 TF_ASSERT_OK(stmt.Step(&is_done_));
158 EXPECT_EQ("yello", stmt.ColumnString(0));
159 EXPECT_EQ("yello", stmt.ColumnString(1));
160 }
161
TEST_F(SqliteTest,UnsafeColumn)162 TEST_F(SqliteTest, UnsafeColumn) {
163 auto stmt = db_->PrepareOrDie("INSERT INTO T (a, b) VALUES (?, ?)");
164 stmt.BindInt(1, 1);
165 stmt.BindText(2, "hello");
166 TF_ASSERT_OK(stmt.StepAndReset());
167 stmt.BindInt(1, 2);
168 stmt.BindText(2, "there");
169 TF_ASSERT_OK(stmt.StepAndReset());
170 stmt = db_->PrepareOrDie("SELECT b FROM T ORDER BY a");
171 TF_ASSERT_OK(stmt.Step(&is_done_));
172 StringPiece p = stmt.ColumnStringUnsafe(0);
173 EXPECT_EQ('h', *p.data());
174 TF_ASSERT_OK(stmt.Step(&is_done_));
175 // This will actually happen, but it's not safe to test this behavior.
176 // EXPECT_EQ('t', *p.data());
177 }
178
TEST_F(SqliteTest,NamedParameterBind)179 TEST_F(SqliteTest, NamedParameterBind) {
180 auto stmt = db_->PrepareOrDie("INSERT INTO T (a) VALUES (:a)");
181 stmt.BindText(":a", "lol");
182 TF_ASSERT_OK(stmt.StepAndReset());
183 stmt = db_->PrepareOrDie("SELECT COUNT(*) FROM T");
184 TF_ASSERT_OK(stmt.Step(&is_done_));
185 EXPECT_EQ(1, stmt.ColumnInt(0));
186 stmt = db_->PrepareOrDie("SELECT a FROM T");
187 TF_ASSERT_OK(stmt.Step(&is_done_));
188 EXPECT_FALSE(is_done_);
189 EXPECT_EQ("lol", stmt.ColumnString(0));
190 }
191
TEST_F(SqliteTest,Statement_DefaultConstructor)192 TEST_F(SqliteTest, Statement_DefaultConstructor) {
193 SqliteStatement stmt;
194 EXPECT_FALSE(stmt);
195 stmt = db_->PrepareOrDie("INSERT INTO T (a) VALUES (1)");
196 EXPECT_TRUE(stmt);
197 EXPECT_TRUE(stmt.StepAndReset().ok());
198 }
199
TEST_F(SqliteTest,Statement_MoveConstructor)200 TEST_F(SqliteTest, Statement_MoveConstructor) {
201 SqliteStatement stmt{db_->PrepareOrDie("INSERT INTO T (a) VALUES (1)")};
202 EXPECT_TRUE(stmt.StepAndReset().ok());
203 }
204
TEST_F(SqliteTest,Statement_MoveAssignment)205 TEST_F(SqliteTest, Statement_MoveAssignment) {
206 SqliteStatement stmt1 = db_->PrepareOrDie("INSERT INTO T (a) VALUES (1)");
207 SqliteStatement stmt2;
208 EXPECT_TRUE(stmt1.StepAndReset().ok());
209 EXPECT_FALSE(stmt2);
210 stmt2 = std::move(stmt1);
211 EXPECT_TRUE(stmt2.StepAndReset().ok());
212 }
213
TEST_F(SqliteTest,PrepareFailed)214 TEST_F(SqliteTest, PrepareFailed) {
215 SqliteLock lock(*db_);
216 SqliteStatement stmt;
217 Status s = db_->Prepare("SELECT", &stmt);
218 ASSERT_FALSE(s.ok());
219 EXPECT_NE(string::npos, s.error_message().find("SELECT"));
220 EXPECT_EQ(SQLITE_ERROR, db_->errcode());
221 }
222
TEST_F(SqliteTest,BindFailed)223 TEST_F(SqliteTest, BindFailed) {
224 auto stmt = db_->PrepareOrDie("INSERT INTO T (a) VALUES (123)");
225 stmt.BindInt(1, 123);
226 Status s = stmt.StepOnce();
227 EXPECT_NE(string::npos,
228 s.error_message().find("INSERT INTO T (a) VALUES (123)"))
229 << s.error_message();
230 }
231
TEST_F(SqliteTest,SnappyExtension)232 TEST_F(SqliteTest, SnappyExtension) {
233 auto stmt = db_->PrepareOrDie("SELECT UNSNAP(SNAP(?))");
234 stmt.BindText(1, "hello");
235 EXPECT_EQ("hello", stmt.StepOnceOrDie().ColumnString(0));
236 }
237
TEST_F(SqliteTest,SnappyBinaryCompatibility)238 TEST_F(SqliteTest, SnappyBinaryCompatibility) {
239 EXPECT_EQ(
240 "today is the end of the republic",
241 db_->PrepareOrDie("SELECT UNSNAP(X'03207C746F6461792069732074686520656E64"
242 "206F66207468652072657075626C6963')")
243 .StepOnceOrDie()
244 .ColumnString(0));
245 }
246
TEST(SqliteOpenTest,CloseConnectionBeforeStatement_KeepsConnectionOpen)247 TEST(SqliteOpenTest, CloseConnectionBeforeStatement_KeepsConnectionOpen) {
248 Sqlite* db;
249 TF_ASSERT_OK(Sqlite::Open(":memory:", SQLITE_OPEN_READWRITE, &db));
250 SqliteStatement stmt = db->PrepareOrDie("SELECT ? + ?");
251 db->Unref();
252 stmt.BindInt(1, 7);
253 stmt.BindInt(2, 3);
254 EXPECT_EQ(10, stmt.StepOnceOrDie().ColumnInt(0));
255 }
256
TEST_F(SqliteTest,TransactionRollback)257 TEST_F(SqliteTest, TransactionRollback) {
258 {
259 SqliteTransaction txn(*db_);
260 auto stmt = db_->PrepareOrDie("INSERT INTO T (a, b) VALUES (?, ?)");
261 stmt.BindDouble(1, 6.28318530);
262 stmt.BindDouble(2, 1.61803399);
263 TF_ASSERT_OK(stmt.StepAndReset());
264 }
265 EXPECT_EQ(
266 0,
267 db_->PrepareOrDie("SELECT COUNT(*) FROM T").StepOnceOrDie().ColumnInt(0));
268 }
269
TEST_F(SqliteTest,TransactionCommit)270 TEST_F(SqliteTest, TransactionCommit) {
271 {
272 SqliteTransaction txn(*db_);
273 auto stmt = db_->PrepareOrDie("INSERT INTO T (a, b) VALUES (?, ?)");
274 stmt.BindDouble(1, 6.28318530);
275 stmt.BindDouble(2, 1.61803399);
276 TF_ASSERT_OK(stmt.StepAndReset());
277 TF_ASSERT_OK(txn.Commit());
278 }
279 EXPECT_EQ(
280 1,
281 db_->PrepareOrDie("SELECT COUNT(*) FROM T").StepOnceOrDie().ColumnInt(0));
282 }
283
TEST_F(SqliteTest,TransactionCommitMultipleTimes)284 TEST_F(SqliteTest, TransactionCommitMultipleTimes) {
285 {
286 SqliteTransaction txn(*db_);
287 auto stmt = db_->PrepareOrDie("INSERT INTO T (a, b) VALUES (?, ?)");
288 stmt.BindDouble(1, 6.28318530);
289 stmt.BindDouble(2, 1.61803399);
290 TF_ASSERT_OK(stmt.StepAndReset());
291 TF_ASSERT_OK(txn.Commit());
292 stmt.BindDouble(1, 6.28318530);
293 stmt.BindDouble(2, 1.61803399);
294 TF_ASSERT_OK(stmt.StepAndReset());
295 TF_ASSERT_OK(txn.Commit());
296 }
297 EXPECT_EQ(
298 2,
299 db_->PrepareOrDie("SELECT COUNT(*) FROM T").StepOnceOrDie().ColumnInt(0));
300 }
301
302 } // namespace
303 } // namespace tensorflow
304