SqlDatabase Namespace Reference

Support for a variety of relational database drivers. More...

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

Typedefs
typedef boost::shared_ptr < Connection >	ConnectionPtr
	Smart pointer to a database connection. More...
typedef boost::shared_ptr < Transaction >	TransactionPtr
	Smart pointer to a transaction. More...
typedef boost::shared_ptr < Statement >	StatementPtr
	Smart 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::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...

Detailed 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,

• An SQLite3 database can be accessed in parallel only if one first implements and registers a function to handle lock contention.
• All parallel writes to an SQLite3 database must use "immediate" transaction locks if any one of the writers uses such a lock. Since we don't always know what kinds of locks might be used, we must always uses immediate locking.
• The indices for statement binding are one-origin, while the indices for cursor reading are zero origin. This is especially confusing when a single statement has both binding and reading.
• sqlite3x_reader (an iterator over results) has an odd interface. After the iterator is created, it must be advanced one row before reading.
• The SQL variant supported by SQLite3 is somewhat limited. For instance, it has no full outer join or column renaming. This means that many complex queries need to be implemented in C++ rather than SQL.
• The sqlite3x API is minimally documented; the only documentation is that which the ROSE team has added.

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:

• The output operators are so verbose that it's hard to see what's actually being printed.
• We kludged the column widths without really knowing how wide the data is. For instance, the file name column is likely to be much to wide if most names don't include path components, and much to narrow if they do. The too-narrow case causes the owner name column to not line up properly. Fixing this would require a previous traversal of an identical query in order to count the width, but even that has the potential for being wrong when other processes are modifying the "files" table.
• We didn't include a separator between the column headings and the table data. Adding one would make the code even more verbose and unreadable. There's also no separator at the end of the table, although that's not nearly as critical.
• If the table is exceptionally long it might be nice to re-print the headers every so often.
• In order to count the number of matching files we need to increment a counter in the body of the loop.
• Since we're monkeying with stream formatters, we need to restore their original values so as not to leave surprises for the caller, including when something we call might throw an exception. Fortunately ROSE's FormatRestorer class makes this easy to do.

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

typedef SqlDatabase::Table<int, ino_t, std::string, std::string> FileTable;
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";
}

Typedef Documentation

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

Smart 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().

Definition at line 135 of file SqlDatabase.h.

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

Smart pointer to a transaction.

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

Definition at line 143 of file SqlDatabase.h.

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

Smart pointer to a statement.

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

Definition at line 147 of file SqlDatabase.h.

Enumeration Type Documentation

enum SqlDatabase::Driver

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 119 of file SqlDatabase.h.

Function Documentation

template<>

std::string SqlDatabase::Statement::iterator::get< std::string >

(

size_t

idx)

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 456 of file SqlDatabase.h.

Referenced by LivenessAnalysis::getIn(), in_strings(), and LivenessAnalysis::setIn().

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 469 of file SqlDatabase.h.

References StringUtility::join(), and 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 477 of file SqlDatabase.h.

References escape(), and in().

std::ostream& SqlDatabase::operator<<	(	std::ostream &	,
		const NoColumn &
	)

std::ostream& SqlDatabase::operator<<	(	std::ostream &	,
		const Exception &
	)

std::ostream& SqlDatabase::operator<<	(	std::ostream &	,
		const Connection &
	)

std::ostream& SqlDatabase::operator<<	(	std::ostream &	,
		const Transaction &
	)

std::ostream& SqlDatabase::operator<<	(	std::ostream &	,
		const Statement &
	)

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.