DBConnection Class Reference [Database IO]

A class for connecting to a SQL database. More...

#include <OpenMS/FORMAT/DB/DBConnection.h>

List of all members.

Classes
class	InvalidQuery
	Exception in case of an invalid Query. More...
class	NotConnected
	Exception in case of trying to execute a query without having established a database connection. More...
Public Member Functions
	DBConnection ()
	Default constructor.
	~DBConnection ()
	Destructor.
void	connect (const String &db, const String &user, const String &password, const String &host="localhost", UInt port=3306, const String &QTDBDriver=DB_PLUGIN, const String &connection_name="OpenMS_default_connection")
	Connects to a SQL database.
bool	isConnected () const
	returns if a connection is established.
void	disconnect ()
	disconnects from the SQL database
QSqlQuery	executeQuery (const String &query, bool first=false)
	Executes a query and returns the result.
Int	getIntValue (const String &table, const String &column, const String &id)
	Returns a single field of a table as an integer.
double	getDoubleValue (const String &table, const String &column, const String &id)
	Returns a single field of a table as a double.
String	getStringValue (const String &table, const String &column, const String &id)
	Returns a single field of a table as string.
UInt	getId (const String &table, const String &column, const String &value)
	Looks up the ID for a specific entry in an table.
UInt	getAutoId ()
	Returns the last auto_increment ID of the SQL database.
String	DBName () const
	Returns the name of the connected DB.
void	render (QSqlQuery &result, std::ostream &out=std::cout, const String &separator=" | ", const String &line_begin="", const String &line_end="\n")
	Dumps a query result in table format into a stream.
template<class StringListType >
void	executeQueries (const StringListType &queries)
	Executes all SQL queries from an container.
Private Member Functions
QSqlDatabase	getDB_ () const
	Retruns the current database connection defined by connection_name_.
Private Attributes
QString	connection_name_
	Name (handle) of the connection.

---

Detailed Description

A class for connecting to a SQL database.

Note:

Do not use '*' in SELECT statments. The order of result columns is not definded then! Read the QT documentation for details.

---

Constructor & Destructor Documentation

DBConnection

(

)

Default constructor.

~DBConnection

(

)

Destructor.

---

Member Function Documentation

void connect	(	const String &	db,
		const String &	user,
		const String &	password,
		const String &	host = "localhost",
		UInt	port = 3306,
		const String &	QTDBDriver = DB_PLUGIN,
		const String &	connection_name = "OpenMS_default_connection"
	)

Connects to a SQL database.

Parameters:

	db	the database name
	user	the login of the server
	password	the password for the user
	host	the host where the server is running (default: "localhost")
	port	the port where the server is listening (default: 3306)
	QTDBDriver	the QT database driver used for the connection (default: "QMYSQL", unless you canged it in configure. See "OpenMS/include/OpenMS/config.h")
	connection_name	Name of the connection (needed for several concurrent connections only)

Exceptions:

InvalidQuery

is thrown if the database connection could not be opened

Referenced by TOPPViewBase::connectToDB_().

String DBName

(

)

const

Returns the name of the connected DB.

void disconnect

(

)

disconnects from the SQL database

Note:

In order to disconnect, all queries running on a database must be closed. See the QSqlDatabase::removeDatabase(...) documentation.

Referenced by TOPPViewBase::openDatabaseDialog().

void executeQueries

(

const StringListType &

queries

)

Executes all SQL queries from an container.

Each line has to be a query or empty.

Parameters:

queries

A STL-compliant container of OpenMS String objects

Exceptions:

	InvalidQuery	is thrown if an invalid SQL query was given
	NotConnected	if there is no database connection

References DBConnection::executeQuery(), and String::trim().

QSqlQuery executeQuery	(	const String &	query,
		bool	first = false
	)

Executes a query and returns the result.

The internal pointer of the returned result is positioned before the first row.

Parameters:

	query	an SQL query
	first	if true, the internal pointer of the returned result is positioned to the first record.

Exceptions:

	InvalidQuery	is thrown if an invalid SQL query was given
	NotConnected	if there is no database connection

Referenced by TOPPViewBase::addDataDB(), DBConnection::executeQueries(), DBAdapter::loadExperiment(), DBAdapter::loadSpectrum(), and DBAdapter::storeExperiment().

UInt getAutoId

(

)

Returns the last auto_increment ID of the SQL database.

Referenced by DBAdapter::storeExperiment().

QSqlDatabase getDB_

(

)

const [inline, private]

Retruns the current database connection defined by connection_name_.

double getDoubleValue	(	const String &	table,
		const String &	column,
		const String &	id
	)

Returns a single field of a table as a double.

The table has to contain an id column.

Parameters:

	table	The table to look the field up
	column	The column of the table
	id	The id of the dataset

Exceptions:

	InvalidQuery	is thrown if an invalid SQL query was given
	NotConnected	if there is no database connection
	Exception::ConversionError	is thrown if the value could not be converted to the requested type

UInt getId	(	const String &	table,
		const String &	column,
		const String &	value
	)

Looks up the ID for a specific entry in an table.

If several entries in the table have the desired value in the column, the first one is returned.

Parameters:

	table	The table to look the field up
	column	The column of the table
	value	The value the selected column has

Exceptions:

	InvalidQuery	is thrown if an invalid SQL query was given
	NotConnected	if there is no database connection

Int getIntValue	(	const String &	table,
		const String &	column,
		const String &	id
	)

Returns a single field of a table as an integer.

The table has to contain an id column.

Parameters:

	table	The table to look the field up
	column	The column of the table
	id	The id of the dataset

Exceptions:

	InvalidQuery	is thrown if an invalid SQL query was given
	NotConnected	if there is no database connection
	Exception::ConversionError	is thrown if the value could not be converted to the requested type

String getStringValue	(	const String &	table,
		const String &	column,
		const String &	id
	)

Returns a single field of a table as string.

The table has to contain an id column.

Parameters:

	table	The table to look the field up
	column	The column of the table
	id	The id of the dataset

Exceptions:

	InvalidQuery	is thrown if an invalid SQL query was given
	NotConnected	if there is no database connection
	Exception::ConversionError	is thrown if the value could not be converted to the requested type

bool isConnected

(

)

const

returns if a connection is established.

Referenced by TOPPViewBase::addDataDB(), TOPPViewBase::metadataDatabaseDialog(), and TOPPViewBase::openDatabaseDialog().

void render	(	QSqlQuery &	result,
		std::ostream &	out = std::cout,
		const String &	separator = " | ",
		const String &	line_begin = "",
		const String &	line_end = "\n"
	)

Dumps a query result in table format into a stream.

To dump a result as HTML table, use render(result, cout,"</td><td>","<tr><td>","</td></tr>");

Parameters:

	result	The result to render
	out	The output stream to use
	separator	The string between the fields
	line_begin	The string at the beginning of each line
	line_end	The string at the end of each line

---

Member Data Documentation

QString connection_name_ [private]

Name (handle) of the connection.