Wrap enough functionality in the statement class to allow the user to execute statements and retrieve results.

This explicitly doesn't try to deal with the inherent non-threadsafety
of the sqlite statement type.  That will only come up if you're trying
to reuse statements, presumably for performance, and once we're doing
that, the code that mediates reuse should ensure that only one client
has its hands on a given statement at once.  (even non-threaded reuse
is dangerous!)

3 files changed, 280 insertions, 2 deletions

diff --git a/src/generic/util/sqlite.cc b/src/generic/util/sqlite.cc
index 4a10bd9a..1985b71c 100644
--- a/src/generic/util/sqlite.cc
+++ b/src/generic/util/sqlite.cc
@@ -100,7 +100,8 @@ namespace aptitude
 
     statement::statement(db &_parent, sqlite3_stmt *_handle)
       : parent(_parent),
-	handle(_handle)
+	handle(_handle),
+	has_data(false)
     {
       parent.active_statements.insert(this);
     }
@@ -170,6 +171,67 @@ namespace aptitude
 	return boost::shared_ptr<statement>(new statement(parent, handle));
     }
 
+    void statement::reset()
+    {
+      sqlite3_reset(handle);
+      has_data = false;
+    }
+
+    bool statement::step()
+    {
+      int result = sqlite3_step(handle);
+      if(result == SQLITE_ROW)
+	{
+	  has_data = true;
+	  return true;
+	}
+      else if(result == SQLITE_DONE)
+	{
+	  has_data = false;
+	  return false;
+	}
+      else
+	throw exception(parent.get_error());
+    }
+
+    const void *statement::get_blob(int column, int &bytes)
+    {
+      require_data();
+      const void *rval = sqlite3_column_blob(handle, column);
+      bytes = sqlite3_column_bytes(handle, column);
+
+      return rval;
+    }
+
+    double statement::get_double(int column)
+    {
+      require_data();
+      return sqlite3_column_double(handle, column);
+    }
+
+    int statement::get_int(int column)
+    {
+      require_data();
+      return sqlite3_column_int(handle, column);
+    }
+
+    sqlite3_int64 statement::get_int64(int column)
+    {
+      require_data();
+      return sqlite3_column_int64(handle, column);
+    }
+
+    std::string statement::get_string(int column)
+    {
+      require_data();
+
+      const unsigned char * const rval = sqlite3_column_text(handle, column);
+      const int bytes = sqlite3_column_bytes(handle, column);
+
+      return std::string(rval, rval + bytes);
+    }
+
+
 
     blob::blob(db &_parent, sqlite3_blob *_handle)
       : parent(_parent),
diff --git a/src/generic/util/sqlite.h b/src/generic/util/sqlite.h
index 7135929b..79f8593b 100644
--- a/src/generic/util/sqlite.h
+++ b/src/generic/util/sqlite.h
@@ -122,16 +122,38 @@ namespace aptitude
       std::string get_error();
     };
 
-    /** \brief Wraps a prepared sqlite3 statement. */
+    /** \brief Wraps a prepared sqlite3 statement.
+     *
+     *  This class is explicitly *not* thread-safe.  You should place
+     *  locks around it if it's going to be accessed from multiple
+     *  threads.
+     */
     class statement
     {
       db &parent;
       sqlite3_stmt *handle;
+      /** \brief Set to \b true when results are available.
+       *
+       *  Used to sanity-check that the database is being used
+       *  correctly (according to the rules laid down in the docs).
+       *  Maybe sqlite does this already, but since it's not
+       *  documented I don't want to rely on it.
+       */
+      bool has_data;
 
       friend class db;
 
       statement(db &_parent, sqlite3_stmt *_handle);
 
+      /** \brief Throw an exception if there isn't result data ready
+       *  to be read.
+       */
+      void require_data()
+      {
+	if(!has_data)
+	  throw exception("No data to retrieve.");
+      }
+
     public:
       ~statement();
 
@@ -146,6 +168,73 @@ namespace aptitude
       static boost::shared_ptr<statement>
       prepare(db &parent,
 	      const char *sql);
+
+      /** \brief Return to the beginning of the statement's result set
+       *  and discard parameter bindings.
+       */
+      void reset();
+
+      /** \brief Step to the next result row of the statement.
+       *
+       *  Mirroring the underlying sqlite behavior, there is no
+       *  "result" object -- meaning that if multiple threads might
+       *  retrieve results from the same statement, they need to lock
+       *  each other out.
+       *
+       *  \return \b true if a new row of results was retrieved, \b
+       *  false otherwise.
+       */
+      bool step();
+
+      /** \brief Execute a statement and discard its results.
+       *
+       *  This is equivalent to invoking step() until it returns \b
+       *  false.  Useful for, e.g., side-effecting statements.
+       */
+      void exec()
+      {
+	while(step())
+	  ; // Do nothing.
+      }
+
+
+      /** \brief Retrieve the value stored in a column as a BLOB.
+       *
+       *  The data block might be invalidated by any other method
+       *  invoked on this statement.
+       *
+       *  \param column  The zero-based index of the column that is to be
+       *                 retrieved.
+       *  \param bytes   A location in which to store the size of the
+       *                 result in bytes.
+       *  \return A pointer to the block of data stored in the
+       *  given column.
+       */
+      const void *get_blob(int column, int &bytes);
+
+      /** \brief Retrieve the value stored in a column as a double.
+       *
+       *  \param column The zero-based index of the column that is to be
+       *                retrieved.
+       */
+      double get_double(int column);
+
+      /** \brief Retrieve the value stored in a column as an integer.
+       *
+       *  \param column The zero-based index of the column that is to be
+       *                retrieved.
+       */
+      int get_int(int column);
+
+      /** \brief Retrieve the value stored in a column as a 64-bit integer.
+       *
+       *  \param column The zero-based index of the column that is to be
+       *                retrieved.
+       */
+      sqlite3_int64 get_int64(int column);
+
+      /** \brief Retrieve the value stored in a column as a string. */
+      std::string get_string(int column);
     };
 
     class blob
diff --git a/tests/test_sqlite.cc b/tests/test_sqlite.cc
index 2fe6ac6c..598bd194 100644
--- a/tests/test_sqlite.cc
+++ b/tests/test_sqlite.cc
@@ -15,6 +15,27 @@ struct memory_db_fixture
   }
 };
 
+// Allocates a database in memory for testing purposes and populates
+// it with some test data.
+//
+// Creates a table "test" with columns A (primary key), B, C, and
+// inserts some values:
+//
+// A       B            C
+// 50      "aardvark"   -5
+// 51      "balderdash" -5
+// 52      "contusion"  x'5412'
+struct test_db_fixture : public memory_db_fixture
+{
+  test_db_fixture()
+  {
+    statement::prepare(*tmpdb, "create table test(A integer primary key, B text, C integer)")->exec();
+    statement::prepare(*tmpdb, "insert into test (A, B, C) values (50, 'aardvark', -5)")->exec();
+    statement::prepare(*tmpdb, "insert into test (A, B, C) values (51, 'balderdash', -5)")->exec();
+    statement::prepare(*tmpdb, "insert into test (A, B, C) values (52, 'contusion', X'5412')")->exec();
+  }
+};
+
 BOOST_AUTO_TEST_CASE(cantOpenDb)
 {
   // Test that a failed open throws an exception (don't know how to
@@ -40,3 +61,109 @@ BOOST_FIXTURE_TEST_CASE(prepareStatementFail, memory_db_fixture)
   BOOST_REQUIRE_THROW(statement::prepare(*tmpdb, "select * from bar"),
 		      exception);
 }
+
+// Test that we can create the test DB and do nothing else.
+BOOST_FIXTURE_TEST_CASE(testSetupDb, test_db_fixture)
+{
+}
+
+BOOST_FIXTURE_TEST_CASE(testGetBlob, test_db_fixture)
+{
+  boost::shared_ptr<statement> stmt =
+    statement::prepare(*tmpdb, "select A, B, C from test where A = 52");
+
+  BOOST_REQUIRE(stmt->step());
+
+  int len = -1;
+  const void *val;
+
+  val = stmt->get_blob(0, len);
+  const char * const fiftytwo = "52";
+  BOOST_CHECK_EQUAL(len, strlen(fiftytwo));
+  BOOST_CHECK_EQUAL_COLLECTIONS(fiftytwo, fiftytwo + strlen(fiftytwo),
+				reinterpret_cast<const char *>(val),
+				reinterpret_cast<const char *>(val) + len);
+
+  val = stmt->get_blob(1, len);
+  const char * const contusion = "contusion";
+  BOOST_CHECK_EQUAL(len, strlen(contusion));
+  BOOST_CHECK_EQUAL_COLLECTIONS(contusion, contusion + strlen(contusion),
+				reinterpret_cast<const char *>(val),
+				reinterpret_cast<const char *>(val) + len);
+
+  val = stmt->get_blob(2, len);
+  const char arr[2] = { 0x54, 0x12 };
+  BOOST_CHECK_EQUAL(len, sizeof(arr));
+  BOOST_CHECK_EQUAL_COLLECTIONS(arr, arr + sizeof(arr),
+				reinterpret_cast<const char *>(val),
+				reinterpret_cast<const char *>(val) + len);
+
+
+  BOOST_CHECK(!stmt->step());
+  BOOST_CHECK_THROW(stmt->get_blob(2, len),
+		    exception);
+}
+
+BOOST_FIXTURE_TEST_CASE(testGetDouble, test_db_fixture)
+{
+  boost::shared_ptr<statement> stmt =
+    statement::prepare(*tmpdb, "select C from test where A = 51");
+
+  BOOST_REQUIRE(stmt->step());
+
+  BOOST_CHECK_EQUAL(stmt->get_double(0), -5);
+
+  BOOST_CHECK(!stmt->step());
+  BOOST_CHECK_THROW(stmt->get_double(0),
+		    exception);
+}
+
+BOOST_FIXTURE_TEST_CASE(testGetInt, test_db_fixture)
+{
+  boost::shared_ptr<statement> stmt =
+    statement::prepare(*tmpdb, "select A from test where A <> 51 order by A");
+
+  BOOST_REQUIRE(stmt->step());
+
+  BOOST_CHECK_EQUAL(stmt->get_int(0), 50);
+
+  BOOST_REQUIRE(stmt->step());
+
+  BOOST_CHECK_EQUAL(stmt->get_int(0), 52);
+
+  BOOST_CHECK(!stmt->step());
+  BOOST_CHECK_THROW(stmt->get_int(0),
+		    exception);
+}
+
+BOOST_FIXTURE_TEST_CASE(testGetInt64, test_db_fixture)
+{
+  boost::shared_ptr<statement> stmt =
+    statement::prepare(*tmpdb, "select A from test where A <> 51 order by A");
+
+  BOOST_REQUIRE(stmt->step());
+  BOOST_CHECK_EQUAL(stmt->get_int64(0), 50);
+
+  BOOST_REQUIRE(stmt->step());
+  BOOST_CHECK_EQUAL(stmt->get_int64(0), 52);
+
+  BOOST_CHECK(!stmt->step());
+  BOOST_CHECK_THROW(stmt->get_int64(0),
+		    exception);
+}
+
+BOOST_FIXTURE_TEST_CASE(testGetString, test_db_fixture)
+{
+  boost::shared_ptr<statement> stmt =
+    statement::prepare(*tmpdb, "select B from test where C = -5 order by A");
+
+  BOOST_REQUIRE(stmt->step());
+  BOOST_CHECK_EQUAL(stmt->get_string(0), "aardvark");
+
+  BOOST_REQUIRE(stmt->step());
+  BOOST_CHECK_EQUAL(stmt->get_string(0), "balderdash");
+
+  BOOST_CHECK(!stmt->step());
+  BOOST_CHECK_THROW(stmt->get_string(0),
+		    exception);
+}