TEST_F(SQLiteUtilTests, test_direct_query_execution) {
auto dbc = getTestDBC();
QueryData results;
auto status = queryInternal(kTestQuery, results, dbc->db());
EXPECT_TRUE(status.ok());
EXPECT_EQ(results, getTestDBExpectedResults());
}
TEST_F(SQLiteUtilTests, test_aggregate_query) {
auto dbc = getTestDBC();
QueryData results;
auto status = queryInternal(kTestQuery, results, dbc.db());
EXPECT_TRUE(status.ok());
EXPECT_EQ(results, getTestDBExpectedResults());
}
TEST_F(SQLiteUtilTests, test_passing_callback_no_data_param) {
char* err = nullptr;
auto dbc = getTestDBC();
sqlite3_exec(dbc.db(), kTestQuery.c_str(), queryDataCallback, nullptr, &err);
EXPECT_TRUE(err != nullptr);
if (err != nullptr) {
sqlite3_free(err);
}
}
TEST_F(SQLiteUtilTests, test_affected_tables) {
auto dbc = getTestDBC();
QueryData results;
auto status = queryInternal("SELECT * FROM time", results, dbc->db());
// Since the table scanned from "time", it should be recorded as affected.
EXPECT_EQ(dbc->affected_tables_.count("time"), 1U);
dbc->clearAffectedTables();
EXPECT_EQ(dbc->affected_tables_.size(), 0U);
}
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_test_db_result_stream) {
auto dbc = getTestDBC();
auto results = getTestDBResultStream();
for (auto r : results) {
char* err_char = nullptr;
sqlite3_exec(dbc.db(), (r.first).c_str(), nullptr, nullptr, &err_char);
EXPECT_TRUE(err_char == nullptr);
if (err_char != nullptr) {
sqlite3_free(err_char);
ASSERT_TRUE(false);
}
QueryData expected;
auto status = queryInternal(kTestQuery, expected, dbc.db());
EXPECT_EQ(expected, r.second);
}
}
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());
}
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}));
}
