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
TEST_F(SqliteTest,NulCharsInString)76 TEST_F(SqliteTest, NulCharsInString) {
77 string s; // XXX: Want to write {2, '\0'} but not sure why not.
78 s.append(static_cast<size_t>(2), '\0');
79 auto stmt = db_->PrepareOrDie("INSERT INTO T (a, b) VALUES (?, ?)");
80 stmt.BindBlob(1, s);
81 stmt.BindText(2, s);
82 TF_ASSERT_OK(stmt.StepAndReset());
83 stmt = db_->PrepareOrDie("SELECT a, b FROM T");
84 TF_ASSERT_OK(stmt.Step(&is_done_));
85 EXPECT_EQ(2, stmt.ColumnSize(0));
86 EXPECT_EQ(2, stmt.ColumnString(0).size());
87 EXPECT_EQ('\0', stmt.ColumnString(0).at(0));
88 EXPECT_EQ('\0', stmt.ColumnString(0).at(1));
89 EXPECT_EQ(2, stmt.ColumnSize(1));
90 EXPECT_EQ(2, stmt.ColumnString(1).size());
91 EXPECT_EQ('\0', stmt.ColumnString(1).at(0));
92 EXPECT_EQ('\0', stmt.ColumnString(1).at(1));
93 }
94
TEST_F(SqliteTest,Unicode)95 TEST_F(SqliteTest, Unicode) {
96 string s = "要依法治国是赞美那些谁是公义的和惩罚恶人。 - 韩非";
97 auto stmt = db_->PrepareOrDie("INSERT INTO T (a, b) VALUES (?, ?)");
98 stmt.BindBlob(1, s);
99 stmt.BindText(2, s);
100 TF_ASSERT_OK(stmt.StepAndReset());
101 stmt = db_->PrepareOrDie("SELECT a, b FROM T");
102 TF_ASSERT_OK(stmt.Step(&is_done_));
103 EXPECT_EQ(s, stmt.ColumnString(0));
104 EXPECT_EQ(s, stmt.ColumnString(1));
105 }
106
TEST_F(SqliteTest,StepAndResetClearsBindings)107 TEST_F(SqliteTest, StepAndResetClearsBindings) {
108 auto stmt = db_->PrepareOrDie("INSERT INTO T (a, b) VALUES (?, ?)");
109 stmt.BindInt(1, 1);
110 stmt.BindInt(2, 123);
111 TF_ASSERT_OK(stmt.StepAndReset());
112 stmt.BindInt(1, 2);
113 TF_ASSERT_OK(stmt.StepAndReset());
114 stmt = db_->PrepareOrDie("SELECT b FROM T ORDER BY a");
115 TF_ASSERT_OK(stmt.Step(&is_done_));
116 EXPECT_EQ(123, stmt.ColumnInt(0));
117 TF_ASSERT_OK(stmt.Step(&is_done_));
118 EXPECT_EQ(SQLITE_NULL, stmt.ColumnType(0));
119 }
120
TEST_F(SqliteTest,SafeBind)121 TEST_F(SqliteTest, SafeBind) {
122 string s = "hello";
123 auto stmt = db_->PrepareOrDie("INSERT INTO T (a, b) VALUES (?, ?)");
124 stmt.BindBlob(1, s);
125 stmt.BindText(2, s);
126 s.at(0) = 'y';
127 TF_ASSERT_OK(stmt.StepAndReset());
128 stmt = db_->PrepareOrDie("SELECT a, b FROM T");
129 TF_ASSERT_OK(stmt.Step(&is_done_));
130 EXPECT_EQ("hello", stmt.ColumnString(0));
131 EXPECT_EQ("hello", stmt.ColumnString(1));
132 }
133
TEST_F(SqliteTest,UnsafeBind)134 TEST_F(SqliteTest, UnsafeBind) {
135 string s = "hello";
136 auto stmt = db_->PrepareOrDie("INSERT INTO T (a, b) VALUES (?, ?)");
137 stmt.BindBlobUnsafe(1, s);
138 stmt.BindTextUnsafe(2, s);
139 s.at(0) = 'y';
140 TF_ASSERT_OK(stmt.StepAndReset());
141 stmt = db_->PrepareOrDie("SELECT a, b FROM T");
142 TF_ASSERT_OK(stmt.Step(&is_done_));
143 EXPECT_EQ("yello", stmt.ColumnString(0));
144 EXPECT_EQ("yello", stmt.ColumnString(1));
145 }
146
TEST_F(SqliteTest,UnsafeColumn)147 TEST_F(SqliteTest, UnsafeColumn) {
148 auto stmt = db_->PrepareOrDie("INSERT INTO T (a, b) VALUES (?, ?)");
149 stmt.BindInt(1, 1);
150 stmt.BindText(2, "hello");
151 TF_ASSERT_OK(stmt.StepAndReset());
152 stmt.BindInt(1, 2);
153 stmt.BindText(2, "there");
154 TF_ASSERT_OK(stmt.StepAndReset());
155 stmt = db_->PrepareOrDie("SELECT b FROM T ORDER BY a");
156 TF_ASSERT_OK(stmt.Step(&is_done_));
157 StringPiece p = stmt.ColumnStringUnsafe(0);
158 EXPECT_EQ('h', *p.data());
159 TF_ASSERT_OK(stmt.Step(&is_done_));
160 // This will actually happen, but it's not safe to test this behavior.
161 // EXPECT_EQ('t', *p.data());
162 }
163
TEST_F(SqliteTest,NamedParameterBind)164 TEST_F(SqliteTest, NamedParameterBind) {
165 auto stmt = db_->PrepareOrDie("INSERT INTO T (a) VALUES (:a)");
166 stmt.BindText(":a", "lol");
167 TF_ASSERT_OK(stmt.StepAndReset());
168 stmt = db_->PrepareOrDie("SELECT COUNT(*) FROM T");
169 TF_ASSERT_OK(stmt.Step(&is_done_));
170 EXPECT_EQ(1, stmt.ColumnInt(0));
171 stmt = db_->PrepareOrDie("SELECT a FROM T");
172 TF_ASSERT_OK(stmt.Step(&is_done_));
173 EXPECT_FALSE(is_done_);
174 EXPECT_EQ("lol", stmt.ColumnString(0));
175 }
176
TEST_F(SqliteTest,Statement_DefaultConstructor)177 TEST_F(SqliteTest, Statement_DefaultConstructor) {
178 SqliteStatement stmt;
179 EXPECT_FALSE(stmt);
180 stmt = db_->PrepareOrDie("INSERT INTO T (a) VALUES (1)");
181 EXPECT_TRUE(stmt);
182 EXPECT_TRUE(stmt.StepAndReset().ok());
183 }
184
TEST_F(SqliteTest,Statement_MoveConstructor)185 TEST_F(SqliteTest, Statement_MoveConstructor) {
186 SqliteStatement stmt{db_->PrepareOrDie("INSERT INTO T (a) VALUES (1)")};
187 EXPECT_TRUE(stmt.StepAndReset().ok());
188 }
189
TEST_F(SqliteTest,Statement_MoveAssignment)190 TEST_F(SqliteTest, Statement_MoveAssignment) {
191 SqliteStatement stmt1 = db_->PrepareOrDie("INSERT INTO T (a) VALUES (1)");
192 SqliteStatement stmt2;
193 EXPECT_TRUE(stmt1.StepAndReset().ok());
194 EXPECT_FALSE(stmt2);
195 stmt2 = std::move(stmt1);
196 EXPECT_TRUE(stmt2.StepAndReset().ok());
197 }
198
TEST_F(SqliteTest,PrepareFailed)199 TEST_F(SqliteTest, PrepareFailed) {
200 SqliteLock lock(*db_);
201 SqliteStatement stmt;
202 Status s = db_->Prepare("SELECT", &stmt);
203 ASSERT_FALSE(s.ok());
204 EXPECT_NE(string::npos, s.error_message().find("SELECT"));
205 EXPECT_EQ(SQLITE_ERROR, db_->errcode());
206 }
207
TEST_F(SqliteTest,BindFailed)208 TEST_F(SqliteTest, BindFailed) {
209 auto stmt = db_->PrepareOrDie("INSERT INTO T (a) VALUES (123)");
210 stmt.BindInt(1, 123);
211 Status s = stmt.StepOnce();
212 EXPECT_NE(string::npos,
213 s.error_message().find("INSERT INTO T (a) VALUES (123)"))
214 << s.error_message();
215 }
216
TEST_F(SqliteTest,SnappyExtension)217 TEST_F(SqliteTest, SnappyExtension) {
218 auto stmt = db_->PrepareOrDie("SELECT UNSNAP(SNAP(?))");
219 stmt.BindText(1, "hello");
220 EXPECT_EQ("hello", stmt.StepOnceOrDie().ColumnString(0));
221 }
222
TEST_F(SqliteTest,SnappyBinaryCompatibility)223 TEST_F(SqliteTest, SnappyBinaryCompatibility) {
224 EXPECT_EQ(
225 "today is the end of the republic",
226 db_->PrepareOrDie("SELECT UNSNAP(X'03207C746F6461792069732074686520656E64"
227 "206F66207468652072657075626C6963')")
228 .StepOnceOrDie()
229 .ColumnString(0));
230 }
231
TEST(SqliteOpenTest,CloseConnectionBeforeStatement_KeepsConnectionOpen)232 TEST(SqliteOpenTest, CloseConnectionBeforeStatement_KeepsConnectionOpen) {
233 Sqlite* db;
234 TF_ASSERT_OK(Sqlite::Open(":memory:", SQLITE_OPEN_READWRITE, &db));
235 SqliteStatement stmt = db->PrepareOrDie("SELECT ? + ?");
236 db->Unref();
237 stmt.BindInt(1, 7);
238 stmt.BindInt(2, 3);
239 EXPECT_EQ(10, stmt.StepOnceOrDie().ColumnInt(0));
240 }
241
TEST_F(SqliteTest,TransactionRollback)242 TEST_F(SqliteTest, TransactionRollback) {
243 {
244 SqliteTransaction txn(*db_);
245 auto stmt = db_->PrepareOrDie("INSERT INTO T (a, b) VALUES (?, ?)");
246 stmt.BindDouble(1, 6.28318530);
247 stmt.BindDouble(2, 1.61803399);
248 TF_ASSERT_OK(stmt.StepAndReset());
249 }
250 EXPECT_EQ(
251 0,
252 db_->PrepareOrDie("SELECT COUNT(*) FROM T").StepOnceOrDie().ColumnInt(0));
253 }
254
TEST_F(SqliteTest,TransactionCommit)255 TEST_F(SqliteTest, TransactionCommit) {
256 {
257 SqliteTransaction txn(*db_);
258 auto stmt = db_->PrepareOrDie("INSERT INTO T (a, b) VALUES (?, ?)");
259 stmt.BindDouble(1, 6.28318530);
260 stmt.BindDouble(2, 1.61803399);
261 TF_ASSERT_OK(stmt.StepAndReset());
262 TF_ASSERT_OK(txn.Commit());
263 }
264 EXPECT_EQ(
265 1,
266 db_->PrepareOrDie("SELECT COUNT(*) FROM T").StepOnceOrDie().ColumnInt(0));
267 }
268
TEST_F(SqliteTest,TransactionCommitMultipleTimes)269 TEST_F(SqliteTest, TransactionCommitMultipleTimes) {
270 {
271 SqliteTransaction txn(*db_);
272 auto stmt = db_->PrepareOrDie("INSERT INTO T (a, b) VALUES (?, ?)");
273 stmt.BindDouble(1, 6.28318530);
274 stmt.BindDouble(2, 1.61803399);
275 TF_ASSERT_OK(stmt.StepAndReset());
276 TF_ASSERT_OK(txn.Commit());
277 stmt.BindDouble(1, 6.28318530);
278 stmt.BindDouble(2, 1.61803399);
279 TF_ASSERT_OK(stmt.StepAndReset());
280 TF_ASSERT_OK(txn.Commit());
281 }
282 EXPECT_EQ(
283 2,
284 db_->PrepareOrDie("SELECT COUNT(*) FROM T").StepOnceOrDie().ColumnInt(0));
285 }
286
287 } // namespace
288 } // namespace tensorflow
289