TEST_F(VirtualTableTests, test_sqlite3_attach_vtable) { auto table = std::make_shared<sampleTablePlugin>(); table->setName("sample"); // Request a managed "connection". // This will be a single (potentially locked) instance or a transient // SQLite database if there is contention and a lock was not requested. auto dbc = SQLiteDBManager::get(); // Virtual tables require the registry/plugin API to query tables. auto status = attachTableInternal("failed_sample", "(foo INTEGER)", dbc.db()); EXPECT_EQ(status.getCode(), SQLITE_ERROR); // The table attach will complete only when the table name is registered. Registry::add<sampleTablePlugin>("table", "sample"); PluginResponse response; status = Registry::call("table", "sample", {{"action", "columns"}}, response); EXPECT_TRUE(status.ok()); // Use the table name, plugin-generated schema to attach. status = attachTableInternal("sample", columnDefinition(response), dbc.db()); EXPECT_EQ(status.getCode(), SQLITE_OK); std::string q = "SELECT sql FROM sqlite_temp_master WHERE tbl_name='sample';"; QueryData results; status = queryInternal(q, results, dbc.db()); EXPECT_EQ( "CREATE VIRTUAL TABLE sample USING sample(`foo` INTEGER, `bar` TEXT)", results[0]["sql"]); }
TEST_F(VirtualTableTests, test_table_cache) { // Get a database connection. Registry::add<cacheTablePlugin>("table", "cache"); auto dbc = SQLiteDBManager::getUnique(); { auto cache = std::make_shared<cacheTablePlugin>(); attachTableInternal("cache", cache->columnDefinition(), dbc); } QueryData results; // Run a query with a join within. std::string statement = "SELECT c2.data as data FROM cache c1, cache c2;"; auto status = queryInternal(statement, results, dbc->db()); dbc->clearAffectedTables(); EXPECT_TRUE(status.ok()); ASSERT_EQ(results.size(), 1U); EXPECT_EQ(results[0]["data"], "more_awesome_data"); // Run the query again, the virtual table cache should have been expired. results.clear(); statement = "SELECT data from cache c1"; queryInternal(statement, results, dbc->db()); ASSERT_EQ(results.size(), 1U); ASSERT_EQ(results[0]["data"], "awesome_data"); }
static void SQL_virtual_table_internal_long(benchmark::State& state) { Registry::add<BenchmarkLongTablePlugin>("table", "long_benchmark"); PluginResponse res; Registry::call("table", "long_benchmark", {{"action", "columns"}}, res); // Attach a sample virtual table. auto dbc = SQLiteDBManager::get(); attachTableInternal("long_benchmark", columnDefinition(res), dbc->db()); while (state.KeepRunning()) { QueryData results; queryInternal("select * from long_benchmark", results, dbc->db()); } }
Status SQLiteSQLPlugin::attach(const std::string& name) { PluginResponse response; auto status = Registry::call("table", name, {{"action", "columns"}}, response); if (!status.ok()) { return status; } auto statement = columnDefinition(response); // Attach requests occurring via the plugin/registry APIs must act on the // primary database. To allow this, getConnection can explicitly request the // primary instance and avoid the contention decisions. auto dbc = SQLiteDBManager::getConnection(true); return attachTableInternal(name, statement, dbc); }
TEST_F(VirtualTableTests, test_json_extract) { // Get a database connection. Registry::add<jsonTablePlugin>("table", "json"); auto dbc = SQLiteDBManager::get(); { auto json = std::make_shared<jsonTablePlugin>(); attachTableInternal("json", json->columnDefinition(), dbc->db()); } QueryData results; // Run a query with a join within. std::string statement = "SELECT JSON_EXTRACT(data, '$.test') AS test FROM json;"; auto status = queryInternal(statement, results, dbc->db()); EXPECT_TRUE(status.ok()); ASSERT_EQ(results.size(), 1U); EXPECT_EQ(results[0]["test"], "1"); }
TEST_F(VirtualTableTests, test_constraints_stacking) { // Add two testing tables to the registry. Registry::add<pTablePlugin>("table", "p"); Registry::add<kTablePlugin>("table", "k"); auto dbc = SQLiteDBManager::get(); { // To simplify the attach, just access the column definition directly. auto p = std::make_shared<pTablePlugin>(); attachTableInternal("p", p->columnDefinition(), dbc->db()); auto k = std::make_shared<kTablePlugin>(); attachTableInternal("k", k->columnDefinition(), dbc->db()); } QueryData results; std::string statement; std::map<std::string, std::string> expected; std::vector<std::pair<std::string, QueryData> > constraint_tests = { MP("select k.x from p, k", makeResult("x", {"1", "2", "1", "2"})), MP("select k.x from (select * from k) k2, p, k where k.x = p.x", makeResult("k.x", {"1", "1", "2", "2"})), MP("select k.x from (select * from k where z = 1) k2, p, k where k.x = " "p.x", makeResult("k.x", {"1", "2"})), MP("select k.x from k k1, (select * from p) p1, k where k.x = p1.x", makeResult("k.x", {"1", "1", "2", "2"})), MP("select k.x from (select * from p) p1, k, (select * from k) k2 where " "k.x = p1.x", makeResult("k.x", {"1", "1", "2", "2"})), MP("select k.x from (select * from p) p1, k, (select * from k where z = " "2) k2 where k.x = p1.x", makeResult("k.x", {"1", "2"})), MP("select k.x from k, (select * from p) p1, k k2, (select * from k " "where z = 1) k3 where k.x = p1.x", makeResult("k.x", {"1", "1", "2", "2"})), MP("select p.x from (select * from k where z = 1) k1, (select * from k " "where z != 1) k2, p where p.x = k2.x", makeResult("p.x", {"1"})), MP("select p.x from (select * from k, (select x as xx from k where x = " "1) k2 where z = 1) k1, (select * from k where z != 1) k2, p, k as k3 " "where p.x = k2.x", makeResult("p.x", {"1", "1"})), }; for (const auto& test : constraint_tests) { QueryData results; queryInternal(test.first, results, dbc->db()); EXPECT_EQ(results, test.second); } std::vector<QueryData> union_results = { makeResult("x", {"1", "2"}), makeResult("k.x", {"1", "2"}), makeResult("k.x", {"1", "2"}), makeResult("k.x", {"1", "2"}), makeResult("k.x", {"1", "2"}), makeResult("k.x", {"1", "2"}), makeResult("k.x", {"1", "2"}), makeResult("p.x", {"1"}), makeResult("p.x", {"1"}), }; size_t index = 0; for (const auto& test : constraint_tests) { QueryData results; queryInternal(test.first + " union " + test.first, results, dbc->db()); EXPECT_EQ(results, union_results[index++]); } }