Add SQLite feature test to verify working foreign key support.

sql/sqlite_features_unittest.cc

 // Copyright (c) 2012 The Chromium Authors. All rights reserved.
 // Use of this source code is governed by a BSD-style license that can be
 // found in the LICENSE file.
 
 #include <string>
 
 #include "base/bind.h"
 #include "base/files/file_util.h"
 #include "base/files/scoped_temp_dir.h"
 #include "sql/connection.h"
@@ skipping 22 matching lines @@
 
     // The error delegate will set |error_| and |sql_text_| when any sqlite
     // statement operation returns an error code.
     db_.set_error_callback(base::Bind(&CaptureErrorCallback,
                                       &error_, &sql_text_));
   }
 
   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:
   base::ScopedTempDir temp_dir_;
   sql::Connection db_;
 
   // The error code of the most recent error.
   int error_;
   // Original statement which has caused the error.
   std::string sql_text_;
@@ skipping 48 matching lines @@
 
   ASSERT_TRUE(db().Execute("INSERT INTO foo (x) VALUES ('test')"));
 
   sql::Statement s(db().GetUniqueStatement(
       "SELECT x FROM foo WHERE x MATCH 'te*'"));
   ASSERT_TRUE(s.Step());
   EXPECT_EQ("test", s.ColumnString(0));
 }
 #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