Add SQLite feature test to verify working foreign key support.

sql/sqlite_features_unittest.cc

Index: sql/sqlite_features_unittest.cc
diff --git a/sql/sqlite_features_unittest.cc b/sql/sqlite_features_unittest.cc
index 1aff80e9bbac20d11dfe40c9e90e0021166de30c..bb10a4a568558833e70d7d4476e1228fcba6b323 100644
--- a/sql/sqlite_features_unittest.cc
+++ b/sql/sqlite_features_unittest.cc
@@ -40,10 +40,16 @@ class SQLiteFeaturesTest : public testing::Test {
   void TearDown() override {
     // If any error happened the original sql statement can be found in
     // |sql_text_|.
-    EXPECT_EQ(SQLITE_OK, error_);
+    EXPECT_EQ(SQLITE_OK, error_) << sql_text_;
     db_.Close();
   }
 
+  void VerifyAndClearLastError(int expected_error) {
+    EXPECT_EQ(expected_error, error_);
+    error_ = SQLITE_OK;
+    sql_text_.clear();
+  }
+
   sql::Connection& db() { return db_; }
 
  private:
@@ -112,4 +118,29 @@ TEST_F(SQLiteFeaturesTest, FTS3_Prefix) {
 }
 #endif
 
+// Ensure that our SQLite version has working foreign key support.
+TEST_F(SQLiteFeaturesTest, ForeignKeySupport) {
+  ASSERT_TRUE(db().Execute("PRAGMA foreign_keys=1"));
+  ASSERT_TRUE(db().Execute("CREATE TABLE parents (id INTEGER PRIMARY KEY)"));
+  ASSERT_TRUE(db().Execute(
+      "CREATE TABLE children ("
+      "    id INTEGER PRIMARY KEY,"
+      "    pid INTEGER NOT NULL REFERENCES parents(id) ON DELETE CASCADE)"));
+
+  // Inserting without a matching parent should fail with constraint violation.
+  EXPECT_FALSE(db().Execute("INSERT INTO children VALUES (10, 1)"));
+  VerifyAndClearLastError(SQLITE_CONSTRAINT);
+
+  // Inserting with a matching parent should work.
+  ASSERT_TRUE(db().Execute("INSERT INTO parents VALUES (1)"));
+  EXPECT_TRUE(db().Execute("INSERT INTO children VALUES (11, 1)"));
+  EXPECT_TRUE(db().Execute("INSERT INTO children VALUES (12, 1)"));
+
+  // Deleting the parent should cascade, i.e., delete the children as well.
+  ASSERT_TRUE(db().Execute("DELETE FROM parents"));
+  sql::Statement s(db().GetUniqueStatement("SELECT * FROM children"));
+  EXPECT_FALSE(s.Step());
+  EXPECT_TRUE(s.Succeeded());

[Scott Hess - ex-Googler, 2015/01/21 18:17:02]

This may be prone to false positives, since there are many ways to generate an
empty result.  Maybe before the delete, check that this SELECT statement
actually returned elements.

[engedy, 2015/01/21 19:50:22]

Do you think doing it with SELECT COUNT(*) would be better?

[Scott Hess - ex-Googler, 2015/01/21 20:23:30]

Not in-place, because that still wouldn't demonstrate the transition between
having elements and not having elements.  What I mean is that while above you
tested that a particular insert which shouldn't work doesn't, you don't check
that an insert which should work does.  Admittedly, we don't generally test for
such things in here, but we have lots of databases without foreign_keys enabled,
so I'm pretty sure some of the test coverage would throw something up.  Mostly
I'm worried about foreign_keys being subtle, could just be nerves.

You could do something like:

EXPECT_TRUE(sql::test::CountTableRows(db(), "children", &rows));
EXPECT_GT(rows, 0);
// execute delete
EXPECT_TRUE(sql::test::CountTableRows(db(), "children", &rows));
EXPECT_EQ(rows, 0);

I thought I had a test helper for "query everything in a table and format it a
certain way", but I don't see it at this time.  Maybe it's in a particular unit
test instead of in the sql/test/test_helpers.h file.  Maybe I should promote
it...

[engedy, 2015/01/22 09:11:07]

Done.

+}
+
 }  // namespace