Status TablePlugin::call(const PluginRequest& request,
PluginResponse& response) {
response.clear();
// TablePlugin API calling requires an action.
if (request.count("action") == 0) {
return Status(1, "Table plugins must include a request action");
}
if (request.at("action") == "generate") {
// "generate" runs the table implementation using a PluginRequest with
// optional serialized QueryContext and returns the QueryData results as
// the PluginRequest data.
QueryContext context;
if (request.count("context") > 0) {
setContextFromRequest(request, context);
}
response = generate(context);
} else if (request.at("action") == "columns") {
// "columns" returns a PluginRequest filled with column information
// such as name and type.
const auto& column_list = columns();
for (const auto& column : column_list) {
response.push_back(
{{"name", column.first}, {"type", columnTypeName(column.second)}});
}
} else if (request.at("action") == "definition") {
response.push_back({{"definition", columnDefinition()}});
} else {
return Status(1, "Unknown table plugin action: " + request.at("action"));
}
return Status(0, "OK");
}
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"]);
}
std::string columnDefinition(const PluginResponse& response) {
TableColumns columns;
for (const auto& column : response) {
columns.push_back(make_pair(column.at("name"), column.at("type")));
}
return columnDefinition(columns);
}
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");
}
Qt::ItemFlags RunsTableModel::flags(const QModelIndex &index) const
{
Qt::ItemFlags flgs = Super::flags(index);
ColumnDefinition cd = columnDefinition(index.column());
if(index.column() == col_runs_startTimeMs) {
flgs = Qt::ItemIsDragEnabled | Qt::ItemIsDropEnabled | flgs;
//qfInfo() << flgs;
}
return flgs;
}
Qt::ItemFlags RunsTableModel::flags(const QModelIndex &index) const
{
Qt::ItemFlags flgs = Super::flags(index);
ColumnDefinition cd = columnDefinition(index.column());
if(cd.matchesSqlId(QStringLiteral("startTimeMs"))) {
flgs = Qt::ItemIsDragEnabled | Qt::ItemIsDropEnabled | flgs;
//qfInfo() << flgs;
}
return flgs;
}
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_tableplugin_aliases) {
auto table = std::make_shared<aliasesTablePlugin>();
std::vector<std::string> expected_aliases = {"aliases1", "aliases2"};
EXPECT_EQ(expected_aliases, table->aliases());
PluginResponse response;
PluginRequest request = {{"action", "columns"}};
EXPECT_TRUE(table->call(request, response).ok());
auto default_option = static_cast<size_t>(ColumnOptions::DEFAULT);
PluginResponse expected_response = {
{{"id", "column"},
{"name", "username"},
{"type", "TEXT"},
{"op", INTEGER(default_option)}},
{{"id", "column"},
{"name", "name"},
{"type", "TEXT"},
{"op", INTEGER(default_option)}},
{{"alias", "aliases1"}, {"id", "alias"}},
{{"alias", "aliases2"}, {"id", "alias"}},
{{"id", "columnAlias"}, {"name", "name1"}, {"target", "name"}},
{{"id", "columnAlias"}, {"name", "name2"}, {"target", "name"}},
{{"id", "columnAlias"}, {"name", "user_name"}, {"target", "username"}},
{{"attributes", "0"}, {"id", "attributes"}},
};
EXPECT_EQ(response, expected_response);
// Compare the expected table definitions.
std::string expected_statement =
"(`username` TEXT, `name` TEXT, `name1` TEXT HIDDEN, `name2` TEXT HIDDEN,"
" `user_name` TEXT HIDDEN)";
EXPECT_EQ(expected_statement, columnDefinition(response, true));
expected_statement = "(`username` TEXT, `name` TEXT)";
EXPECT_EQ(expected_statement, columnDefinition(response, false));
}
int xCreate(sqlite3 *db,
void *pAux,
int argc,
const char *const *argv,
sqlite3_vtab **ppVtab,
char **pzErr) {
auto *pVtab = new VirtualTable;
if (!pVtab || argc == 0 || argv[0] == nullptr) {
delete pVtab;
return SQLITE_NOMEM;
}
memset(pVtab, 0, sizeof(VirtualTable));
pVtab->content = new VirtualTableContent;
pVtab->instance = (SQLiteDBInstance *)pAux;
// Create a TablePlugin Registry call, expect column details as the response.
PluginResponse response;
pVtab->content->name = std::string(argv[0]);
// Get the table column information.
auto status = Registry::call(
"table", pVtab->content->name, {{"action", "columns"}}, response);
if (!status.ok() || response.size() == 0) {
delete pVtab->content;
delete pVtab;
return SQLITE_ERROR;
}
// Generate an SQL create table statement from the retrieved column details.
auto statement =
"CREATE TABLE " + pVtab->content->name + columnDefinition(response);
int rc = sqlite3_declare_vtab(db, statement.c_str());
if (rc != SQLITE_OK || !status.ok() || response.size() == 0) {
delete pVtab->content;
delete pVtab;
return (rc != SQLITE_OK) ? rc : SQLITE_ERROR;
}
// Keep a local copy of the column details in the VirtualTableContent struct.
// This allows introspection into the column type without additional calls.
for (const auto &column : response) {
pVtab->content->columns.push_back(
std::make_pair(column.at("name"), columnTypeName(column.at("type"))));
}
*ppVtab = (sqlite3_vtab *)pVtab;
return rc;
}
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_tableplugin_options) {
auto table = std::make_shared<optionsTablePlugin>();
EXPECT_EQ(ColumnOptions::INDEX | ColumnOptions::REQUIRED,
std::get<2>(table->columns()[0]));
PluginResponse response;
PluginRequest request = {{"action", "columns"}};
EXPECT_TRUE(table->call(request, response).ok());
auto index_required =
static_cast<size_t>(ColumnOptions::INDEX | ColumnOptions::REQUIRED);
EXPECT_EQ(INTEGER(index_required), response[0]["op"]);
response = table->routeInfo();
EXPECT_EQ(INTEGER(index_required), response[0]["op"]);
std::string expected_statement =
"(`id` INTEGER PRIMARY KEY, `username` TEXT, `name` TEXT) WITHOUT ROWID";
EXPECT_EQ(expected_statement, columnDefinition(response, true));
}
bool RunsTableModel::setValue(int row_ix, int column_ix, const QVariant &val)
{
bool ret;
ColumnDefinition cd = columnDefinition(column_ix);
if(cd.matchesSqlId(QStringLiteral("finishTimeMs"))) {
QVariant start_ms = value(row_ix, "startTimeMs");
if(!start_ms.isNull()) {
int time_ms = val.toInt() - start_ms.toInt();
if(time_ms > 0) {
Super::setValue(row_ix, "timeMs", time_ms);
}
}
}
else if(cd.matchesSqlId(QStringLiteral("timeMs"))) {
QVariant start_ms = value(row_ix, "startTimeMs");
if(!start_ms.isNull()) {
int finish_ms = val.toInt() + start_ms.toInt();
if(finish_ms > 0) {
Super::setValue(row_ix, columnIndex("finishTimeMs"), finish_ms);
}
}
}
else if(cd.matchesSqlId(QStringLiteral("startTimeMs"))) {
if(!val.isNull()) {
int start_ms = val.toInt();
int finish_ms = value(row_ix, "finishTimeMs").toInt();
int time_ms = value(row_ix, "timeMs").toInt();
if(finish_ms > 0) {
int time_ms = finish_ms - start_ms;
Super::setValue(row_ix, columnIndex("timeMs"), time_ms);
}
else if(time_ms > 0) {
finish_ms = start_ms + time_ms;
Super::setValue(row_ix, "finishTimeMs", finish_ms);
}
}
}
ret = Super::setValue(row_ix, column_ix, val);
return ret;
}
TEST_F(VirtualTableTests, test_tableplugin_columndefinition) {
auto table = std::make_shared<sampleTablePlugin>();
EXPECT_EQ("(`foo` INTEGER, `bar` TEXT)", table->columnDefinition());
}
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++]);
}
}