TEST_F(SQLiteUtilTests, test_get_query_columns) {
auto dbc = getTestDBC();
TableColumns results;
std::string query = "SELECT seconds, version FROM time JOIN osquery_info";
auto status = getQueryColumnsInternal(query, results, dbc->db());
ASSERT_TRUE(status.ok());
ASSERT_EQ(2U, results.size());
EXPECT_EQ(std::make_pair(std::string("seconds"), INTEGER_TYPE), results[0]);
EXPECT_EQ(std::make_pair(std::string("version"), TEXT_TYPE), results[1]);
query = "SELECT * FROM foo";
status = getQueryColumnsInternal(query, results, dbc->db());
ASSERT_FALSE(status.ok());
}
TEST_F(SQLiteUtilTests, test_get_query_columns) {
auto dbc = getTestDBC();
std::string query;
Status status;
tables::TableColumns results;
query =
"SELECT hour, minutes, seconds, version, config_md5, config_path, \
pid FROM time JOIN osquery_info";
status = getQueryColumnsInternal(query, results, dbc.db());
ASSERT_TRUE(status.ok());
ASSERT_EQ(7, results.size());
EXPECT_EQ(std::make_pair(std::string("hour"), std::string("INTEGER")),
results[0]);
EXPECT_EQ(std::make_pair(std::string("minutes"), std::string("INTEGER")),
results[1]);
EXPECT_EQ(std::make_pair(std::string("seconds"), std::string("INTEGER")),
results[2]);
EXPECT_EQ(std::make_pair(std::string("version"), std::string("TEXT")),
results[3]);
EXPECT_EQ(std::make_pair(std::string("config_md5"), std::string("TEXT")),
results[4]);
EXPECT_EQ(std::make_pair(std::string("config_path"), std::string("TEXT")),
results[5]);
EXPECT_EQ(std::make_pair(std::string("pid"), std::string("INTEGER")),
results[6]);
query = "SELECT hour + 1 AS hour1, minutes + 1 FROM time";
status = getQueryColumnsInternal(query, results, dbc.db());
ASSERT_TRUE(status.ok());
ASSERT_EQ(2, results.size());
EXPECT_EQ(std::make_pair(std::string("hour1"), std::string("UNKNOWN")),
results[0]);
EXPECT_EQ(std::make_pair(std::string("minutes + 1"), std::string("UNKNOWN")),
results[1]);
query = "SELECT * FROM foo";
status = getQueryColumnsInternal(query, results, dbc.db());
ASSERT_FALSE(status.ok());
}
Status SQLiteSQLPlugin::getQueryColumns(const std::string& q,
TableColumns& columns) const {
auto dbc = SQLiteDBManager::get();
return getQueryColumnsInternal(q, columns, dbc->db());
}
TEST_F(SQLiteUtilTests, test_query_planner) {
using TypeList = std::vector<ColumnType>;
auto dbc = getTestDBC();
TableColumns columns;
std::string query = "select path, path from file";
getQueryColumnsInternal(query, columns, dbc->db());
EXPECT_EQ(getTypes(columns), TypeList({TEXT_TYPE, TEXT_TYPE}));
query = "select path, seconds from file, time";
getQueryColumnsInternal(query, columns, dbc->db());
EXPECT_EQ(getTypes(columns), TypeList({TEXT_TYPE, INTEGER_TYPE}));
query = "select path || path from file";
getQueryColumnsInternal(query, columns, dbc->db());
EXPECT_EQ(getTypes(columns), TypeList({TEXT_TYPE}));
query = "select seconds, path || path from file, time";
getQueryColumnsInternal(query, columns, dbc->db());
EXPECT_EQ(getTypes(columns), TypeList({INTEGER_TYPE, TEXT_TYPE}));
query = "select seconds, seconds from time";
getQueryColumnsInternal(query, columns, dbc->db());
EXPECT_EQ(getTypes(columns), TypeList({INTEGER_TYPE, INTEGER_TYPE}));
query = "select count(*) from time";
getQueryColumnsInternal(query, columns, dbc->db());
EXPECT_EQ(getTypes(columns), TypeList({BIGINT_TYPE}));
query = "select count(*), count(seconds), seconds from time";
getQueryColumnsInternal(query, columns, dbc->db());
EXPECT_EQ(getTypes(columns),
TypeList({BIGINT_TYPE, BIGINT_TYPE, INTEGER_TYPE}));
query = "select 1, 'path', path from file";
getQueryColumnsInternal(query, columns, dbc->db());
EXPECT_EQ(getTypes(columns), TypeList({INTEGER_TYPE, TEXT_TYPE, TEXT_TYPE}));
query = "select weekday, day, count(*), seconds from time";
getQueryColumnsInternal(query, columns, dbc->db());
EXPECT_EQ(getTypes(columns),
TypeList({TEXT_TYPE, INTEGER_TYPE, BIGINT_TYPE, INTEGER_TYPE}));
query = "select seconds + 1 from time";
getQueryColumnsInternal(query, columns, dbc->db());
EXPECT_EQ(getTypes(columns), TypeList({BIGINT_TYPE}));
query = "select seconds * seconds from time";
getQueryColumnsInternal(query, columns, dbc->db());
EXPECT_EQ(getTypes(columns), TypeList({BIGINT_TYPE}));
query = "select seconds > 1, seconds, count(seconds) from time";
getQueryColumnsInternal(query, columns, dbc->db());
EXPECT_EQ(getTypes(columns),
TypeList({INTEGER_TYPE, INTEGER_TYPE, BIGINT_TYPE}));
query =
"select f1.*, seconds, f2.directory from (select path || path from file) "
"f1, file as f2, time";
getQueryColumnsInternal(query, columns, dbc->db());
EXPECT_EQ(getTypes(columns), TypeList({TEXT_TYPE, INTEGER_TYPE, TEXT_TYPE}));
}
