Project

General

Profile

CSV_QE_API Documentation

Latest distribution available via UPD.

See also the documentation on Query Engine for specifics on clause function options: https://cdcvs.fnal.gov/redmine/projects/qengine/wiki

In order to use the Query Engine with a Postgres database, a ServiceNow request must first be submitted in order to enable Query Engine for that database.

Basic Usage

The csv_qe_api is a header only library that utilizes the Query Engine REST interface to facilitate easy, type-safe retrieval of data from Postgres databases that have Query Engine enabled.
It must be compiled with a C++14 enabled compiler, and linked against LibWDA version 2_24_0, also available via UPD. (https://cdcvs.fnal.gov/redmine/projects/libwda).

Central to the idea of this api is that the database is represented by a Query Engine object. Queries are built by executing member methods such as where(), limit(), and order_by() on these Query Engine objects. Queries are not executed until the .get()
member method is called upon a Query Engine object. The get() method then returns a representation of a table comprised of rows which behave as individual tuples.

Usage of the csv_qe_api is simple, first instantiate a Query Engine object:

#include "csv_qe_api/query_engine_api.h" 
query_engine<int, double, string> example_query_engine("http://ifb-data.fnal.gov:port/your_experiment/query_engine/uri_path", "my_db_name", "my_table_name", "int_column", "double_column", "string_column");

The arguments to the constructor of the query_engine object correspond to the hostname, port number, and specific path provided to your experiment for query engine access. Also the database name, the table name, and then any number of column names.
The query engine is templated and instantiated based on the datatypes of the columns being requested.

Note: The number of, order of, and data types of the column names MUST correspond to the number of, order of, and data types of the template arguments.
Hint: Alias declarations can make this declaration much more concise and tidy.

The query_engine object is then used to execute queries on the database through the Query Engine interface. Queries are executed by calling the .get() member function of the query_engine class. The fact that queries are not executed until get() is called allows queries to be built progressively using further member function clauses including where(), order_by(), and limit().

Note: Legal operator arguments to where() are lt (less-than), le (less-than-or-equal), eq (equal), ne (not-equal), ge (greater-than-or-equal), gt (greater-than)

auto results1 = example_query_engine1.limit(10).get();                                                       //Retrieve the first 10 rows of int_column, double_column, and string_column
auto results2 = example_query_engine2.where("int_column", "lt", "2").get();                                  //Retrieve rows of int_column, double_column, and string_column where int_column is less-than 2
auto results3 = example_query_engine3.where("int_column", "lt", "2").order_by("double_column").get();        //Retrieve rows of int_column, double_column, and string_column where int_column is less-than 2 and order results by double column
auto results4 = example_query_engine4.where("int_column", "lt", "2").order_by("double_column", '-').get();   //Same as previous example, but the order direction is reversed

Each of the result objects in the example above will be populated with the results of the requested operation as a table of rows after the execution of .get()

From this point, accessing the result data in each row is trivial:

for (auto& row : result1) {
   std::cout << "(" << column<0>(row) << "," << column<1>(row) << "," << column<2>(row) << ")" << std::endl;
}

Note: At this time, columns must be referred to by index, not by name. This is a limitation of C++14.

Progressive Query Construction

Elaborating on the earlier statement that queries may be built progressively until executed with .get(), consider the following code:

query_engine<int, double, string> example_query_engine5("http://ifb-data.fnal.gov:port", "my_db_name", "my_table_name", "int_column", "double_column", "string_column");

example_query_engine5.limit(5);
example_query_engine5.where("int_column", "lt", "2");
example_query_engine5.order_by("double_column", '-');
auto result5 = example_query_engine5.get();

This block of code will behave exactly the same as if it were written as below while instead allowing for flexible runtime designation of the exact specifications of the query:

auto results6 = example_query_engine.where("int_column", "lt", "2").order_by("double_column", '-').limit(5).get();

This functionality also allows for a Query Engine query to be constructed, but have its actual execution delayed for any amount of time:

example_query_engine6 = query_engine<int, double, string> example_query_engine("http://ifb-data.fnal.gov:port", "my_db_name", "my_table_name", "int_column", "double_column", "string_column");
example_query_engine6 = example_query_engine6.limit(10); //Note: Both copy construction and copy assignment are supported by the Query Engine class
.
.
.
.
auto results6 = example_query_engine6.get();

Array Data Types

If you have an array stored in your Query Engine enabled database you may also access those records via this API.

query_engine<std::vector<int>> qe_array(db_uri, database, table, array_column_name);
auto vector_test_result = qe_array.get();

for (const auto& row : vector_test_result) {
  for ( const auto& vec_element : column<0>(row) ) {
    std::cout << "Vector element: " << vec_element << std::endl;
  }
  std::cout << std::endl;
}

Binary Data Retrieval

Retrieval of binary data uses the Unstructured Conditions Database instead of a Query Engine Database

The interface to interact with UConDB is almost exactly the same as interacting with Query Engine enabled databases.
Documentation on the specifics of UConDB may be found at:
https://cdcvs.fnal.gov/redmine/projects/ucondb/wiki/UConDBDoc

  const int max_attempts = 1;
  const std::string ucondb_url = "http://dbweb3.fnal.gov:9090/ucondb_demo";
  const std::string ucondb_folder = "test";
  const std::string ucondb_object = "bjwhite_test_file_2";

  // Create a UConDB database object
  ucondb ucondb(ucondb_url);

  std::string ucondb_version = "";
  std::string ucondb_tv = "1234";
  std::string ucondb_tr = "1532532107.278193";
  std::string ucondb_tag = "test_tag";
  std::string ucondb_key = "test_key";

  // Test get with strings
  std::string result_1 = ucondb.get<std::string>(ucondb_folder, ucondb_object, max_attempts);
  std::string result_2 = ucondb.get<std::string>(ucondb_folder, ucondb_object, max_attempts, ucondb_version, ucondb_tv, "", "", "");
  std::string result_3 = ucondb.get<std::string>(ucondb_folder, ucondb_object, max_attempts, ucondb_version, "", ucondb_tr, "", "");
  std::string result_4 = ucondb.get<std::string>(ucondb_folder, ucondb_object, max_attempts, ucondb_version, "", "", ucondb_tag, "");
  std::string result_5 = ucondb.get<std::string>(ucondb_folder, ucondb_object, max_attempts, ucondb_version, "", "", "", ucondb_key);
  std::string result_6 = ucondb.get<std::string>(ucondb_folder, ucondb_object, max_attempts, ucondb_version, ucondb_tv, "", ucondb_tag, ucondb_key);

  // Test get with std::vector<char>
  std::vector<char> result_7 = ucondb.get<std::vector<char>>(ucondb_folder, ucondb_object, max_attempts);
  std::vector<char> result_8 = ucondb.get<std::vector<char>>(ucondb_folder, ucondb_object, max_attempts, ucondb_version, ucondb_tv, "", "", "");
  std::vector<char> result_9 = ucondb.get<std::vector<char>>(ucondb_folder, ucondb_object, max_attempts, ucondb_version, "", ucondb_tr, "", "");
  std::vector<char> result_10 = ucondb.get<std::vector<char>>(ucondb_folder, ucondb_object, max_attempts, ucondb_version, "", "", ucondb_tag, "");
  std::vector<char> result_11 = ucondb.get<std::vector<char>>(ucondb_folder, ucondb_object, max_attempts, ucondb_version, "", "", "", ucondb_key);
  std::vector<char> result_12 = ucondb.get<std::vector<char>>(ucondb_folder, ucondb_object, max_attempts, ucondb_version, ucondb_tv, "", ucondb_tag, ucondb_key);