ROSE  0.9.9.139
Classes | Typedefs | Enumerations | Functions | Variables
SqlDatabase Namespace Reference

Description

Support for a variety of relational database drivers.

ROSE originally supported only sqlite3x, a C++ API for SQLite3 databases. While SQLite3 is easy to configure (a database is just a local file), it is not the best relational database system to use for large databases with complex queries and parallel access. The sqlite and the sqlite3x interface also have some peculiarities that make it difficult to use. For instance,

ROSE's SqlDatabase is an abstraction layer around sqlite3x and other relational database systems. The goal is to be able to write C++ that manipulates a database and not care too much about what driver provides that database. There are a couple of existing APIs that provide this abstraction, but they're not C++ oriented.

The SqlDatabase API is used as follows:

First, a connection is established to the database via a SqlDatabase::Connection object. This causes the database to be opened. SQLite3 will create the database if necesssary; PostgreSQL will access an existing database. The connection specification varies depending on the underlying driver (a filename for SQLite3; a URL or connection parameters for PostgreSQL).

The connection is used to create one or more SQL statements. A statement may have value placeholders indicated with "?", which are numbered starting with zero according to their relative positions in the SQL string. The placeholders are bound to actual values with the Statement::bind() function. It is permissible to rebind new values to an existing statement in order to execute a statement repeatedly with different values.

Once all placeholders of a statement are bound to values, the statement can be executed. A statement is executed by calling its begin() method, which returns an iterator. The iterator points to consective rows of the result. Various convenience methods are available for query statements that return a single row with a single value, or statements that do not return any rows. In general, a new statement should not be executed until all rows of the previous statement are consumed (this is a limitation of the underlying drivers).

For instance, here's how one would run a query that prints file IDs, inode number, file name, and owner name for files containing N lines, where N is supplied as an argument to the function:

void show_files(SqlDatabase::TransactionPtr &tx, int nlines, std::ostream &out) {
FormatRestorer fr(out); // so we don't need to restore the original stream flags
SqlDatabase::StatementPtr stmt = tx->statement("select id, inode, name, owner from files where nlines = ?");
stmt.bind(0, nlines);
int nfiles = 0;
out << std::setw(7) <<std::right <<"ID"
<<" " <<std::setw(10) <<std::right <<"Inode"
<<" " <<std::setw(64) <<std::left <<"File Name"
<<" " <<std::setw(1) <<std::left <<"Owner Name" <<"\n"
for (SqlDatabase::Statement::iterator row=stmt->begin(); row!=stmt->end(); ++row)
out << std::setw(7) <<std::right <<row.get<int>(0) // the ID number
<<" " <<std::setw(10) <<std::right <<row.get<ino_t>(1) // the inode number
<<" " <<std::setw(64) <<std::left <<row.get<std::string>(2) // file name
<<" " <<std::setw(1) <<std::left <<row.get<std::string>(3) <<"\n"; // owner name
++nfiles;
}
out <<"number of matching files: " <<nfiles <<"\n";
}

There are a number of problems with that code:

We can solve all these problems and improve readability by using the SqlDatabase::Table template class, like this:

void show_files(SqlDatabase::TransactionPtr &tx, int nlines, std::ostream &out) {
FileTable table(tx->statement("select id, inode, name, owner from files where nlines=?")->bind(0, nlines));
table.headers("ID", "Inode", "File Name", "Owner Name");
table.reprint_headers(100); //print the headers again every 100 lines
table.print(out);
out <<"number of matching files: " <<table.size() <<"\n";
}

Classes

struct  AddrRenderer
 Renders a rose_addr_t as a hexadecimal string. More...
 
struct  ColumnTraits
 
struct  ColumnTraits< NoColumn >
 
class  Connection
 Connection to a database. More...
 
class  Exception
 Exceptions thrown by database operations. More...
 
class  Header
 
class  Header< NoColumn >
 
class  Header< std::string >
 
class  NoColumn
 
class  PrePostRow
 Called before and after printing each row of a table. More...
 
class  Renderer
 Renders a column value as a string. More...
 
class  Renderer< std::string >
 
class  RowSeparator
 
class  RowSeparator< NoColumn >
 
class  Statement
 A database operation. More...
 
class  Table
 In-memory representation of a database table. More...
 
struct  TimeRenderer
 Renders a time_t as a string. More...
 
class  Transaction
 Unit of work within a database. More...
 

Typedefs

typedef boost::shared_ptr< ConnectionConnectionPtr
 Shared-ownership pointer to a database connection. More...
 
typedef boost::shared_ptr< TransactionTransactionPtr
 Shared-ownership pointer to a transaction. More...
 
typedef boost::shared_ptr< StatementStatementPtr
 Shared-ownership pointer to a statement. More...
 

Enumerations

enum  Driver {
  NO_DRIVER,
  SQLITE3,
  POSTGRESQL
}
 Low-level driver to use for the database. More...
 

Functions

template<>
std::string Statement::iterator::get< std::string > (size_t idx)
 
std::string uriDocumentation ()
 Documentation about database connection URIs. More...
 
std::vector< std::string > split_sql (const std::string &sql)
 Split SQL source code into individual statements. More...
 
std::string escape (const std::string &, Driver, bool do_quote=true)
 Produce an SQL string literal from a C++ string. More...
 
bool is_valid_table_name (const std::string &name)
 Returns true if name is a valid table name. More...
 
template<class Container >
std::string in (const Container &values)
 Converts a container of values to an SQL "in" clause. More...
 
template<class Container , class Stringifier >
std::string in_numbers (const Container &values, Stringifier &stringifier)
 Converts a container of numbers to an SQL "in" clause using StringUtility to format them. More...
 
template<class Container >
std::string in_strings (const Container &values, Driver driver)
 Converts a container of strings to an SQL "in" clause of strings. More...
 
std::ostream & operator<< (std::ostream &, const NoColumn &)
 
std::ostream & operator<< (std::ostream &, const Exception &)
 
std::ostream & operator<< (std::ostream &, const Connection &)
 
std::ostream & operator<< (std::ostream &, const Transaction &)
 
std::ostream & operator<< (std::ostream &, const Statement &)
 

Variables

AddrRenderer addr8Renderer
 Renders 8-bit addresses in hexadecimal. More...
 
AddrRenderer addr16Renderer
 Renders 16-bit addresses in hexadecimal. More...
 
AddrRenderer addr32Renderer
 Renders 32-bit addresses in hexadecimal. More...
 
AddrRenderer addr64Renderer
 Renders 64-bit addresses in hexadecimal. More...
 
TimeRenderer timeRenderer
 Renders time_t as YYYY-MM-DD HH:MM:SS in the local timezone. More...
 
TimeRenderer dateRenderer
 Renders only the date portion of a time as YYYY-MM-DD in local timezone. More...
 
TimeRenderer humanTimeRenderer
 Renders a time using the current locale, which is more human-friendly. More...
 

Typedef Documentation

typedef boost::shared_ptr<Connection> SqlDatabase::ConnectionPtr

Shared-ownership pointer to a database connection.

Database connections are always referenced through their smart pointers and are automatically deleted when all references disappear. See Connection::create and Shared ownership.

Definition at line 136 of file SqlDatabase.h.

typedef boost::shared_ptr<Transaction> SqlDatabase::TransactionPtr

Shared-ownership pointer to a transaction.

Transactions are always referenced through their smart pointers and are automatically deleted when all references disappear. See Connection::transaction and Shared ownership.

Definition at line 146 of file SqlDatabase.h.

typedef boost::shared_ptr<Statement> SqlDatabase::StatementPtr

Shared-ownership pointer to a statement.

Statements are always referenced through their smart pointers and are automatically deleted when all references disappear. See Transaction::statement and Shared ownership.

Definition at line 151 of file SqlDatabase.h.

Enumeration Type Documentation

Low-level driver to use for the database.

Multiple database drivers are supported depending on how ROSE was configured.

Enumerator
NO_DRIVER 

Used only by the default Connection constructor.

SQLITE3 

SQLite3 using the C++ sqlite3x API.

POSTGRESQL 

PostgreSQL.

Definition at line 120 of file SqlDatabase.h.

Function Documentation

std::string SqlDatabase::uriDocumentation ( )

Documentation about database connection URIs.

std::vector<std::string> SqlDatabase::split_sql ( const std::string &  sql)

Split SQL source code into individual statements.

This is not a full parser–it only looks for top-level semicolons.

std::string SqlDatabase::escape ( const std::string &  ,
Driver  ,
bool  do_quote = true 
)

Produce an SQL string literal from a C++ string.

If do_quote is false then don't add the surrounding quote characters.

Referenced by in_strings().

bool SqlDatabase::is_valid_table_name ( const std::string &  name)

Returns true if name is a valid table name.

template<class Container >
std::string SqlDatabase::in ( const Container &  values)

Converts a container of values to an SQL "in" clause.

Definition at line 474 of file SqlDatabase.h.

Referenced by in_strings().

template<class Container , class Stringifier >
std::string SqlDatabase::in_numbers ( const Container &  values,
Stringifier &  stringifier 
)

Converts a container of numbers to an SQL "in" clause using StringUtility to format them.

Definition at line 487 of file SqlDatabase.h.

References Rose::StringUtility::join(), and Rose::StringUtility::toStrings().

template<class Container >
std::string SqlDatabase::in_strings ( const Container &  values,
Driver  driver 
)

Converts a container of strings to an SQL "in" clause of strings.

The driver is necessary in order to properly quote and escape the supplied values.

Definition at line 495 of file SqlDatabase.h.

References escape(), and in().

Variable Documentation

AddrRenderer SqlDatabase::addr8Renderer

Renders 8-bit addresses in hexadecimal.

AddrRenderer SqlDatabase::addr16Renderer

Renders 16-bit addresses in hexadecimal.

AddrRenderer SqlDatabase::addr32Renderer

Renders 32-bit addresses in hexadecimal.

AddrRenderer SqlDatabase::addr64Renderer

Renders 64-bit addresses in hexadecimal.

TimeRenderer SqlDatabase::timeRenderer

Renders time_t as YYYY-MM-DD HH:MM:SS in the local timezone.

TimeRenderer SqlDatabase::dateRenderer

Renders only the date portion of a time as YYYY-MM-DD in local timezone.

TimeRenderer SqlDatabase::humanTimeRenderer

Renders a time using the current locale, which is more human-friendly.