This class provides connectivity and convenience functions for database access in the US3 system. More...

#include "us_db2.h"

Public Types
enum	dbStatus { OK = 0, ERROR = -1, NOT_CONNECTED = 1, DUP_EMAIL = 101, NO_ACCT = 102, INACTIVE = 103, BADPASS = 104, EMPTY = 105, BAD_CHECKSUM = 106, NOTPERMITTED = 201, BADOPERATOR = 202, BADLABLOCATION = 203, BADGUID = 204, NOROWS = 301, INSERTNULL = 401, INSERTDUP = 402, DUPFIELD = 403, CONSTRAINT_FAILED = 404, NO_BUFFER = 501, NO_COMPONENT = 502, NO_ROTOR = 503, NO_ANALYTE = 504, NO_LAB = 505, NO_PERSON = 506, NO_MODEL = 507, NO_EXPERIMENT = 508, NO_RAWDATA = 509, NO_EDITDATA = 510, NO_SOLUTION = 511, CALIB_IN_USE = 512, ROTOR_IN_USE = 513, NO_NOISE = 514, NO_PROJECT = 515, BUFFR_IN_USE = 516, ANALY_IN_USE = 517, SOLUT_IN_USE = 518, NO_CALIB = 519, NO_REPORT = 520, NO_REPORT_DETAIL = 521, NO_REPORT_DOCUMENT = 522, UNKNOWN_ERR = 999 }
	Some status codes returned by the US3 database. More...

enum	dbUserlevels { USER = 0, PRIV = 1, ANALYST = 2, SUPER = 3, ADMIN = 4 }
	Some user levels used by the US3 database procedures. More...

Public Member Functions
	US_DB2 ()
	Generic constructor for the US_DB2 class. Initialization is performed but database connection is not attempted. More...

	US_DB2 (const QString &)
	Constructor for the US_DB2 class. Initialization is performed and connection is attempted. Connects to a database using the currently defined database stored by US_Config and US_Settings. This constructor uses an SSL connection. More...

	~US_DB2 ()
	Generic destructor for the US_DB2 class. Database connection is terminated and any resources in use are closed and deleted. More...

bool	test_db_connection (const QString &, const QString &, const QString &, const QString &, QString &)
	Provides a quick test of database connectivity to ensure that the parameters in the database setup are correct. The database is merely opened and then immediately closed. More...

bool	test_secure_connection (const QString &, const QString &, const QString &, const QString &, const QString &, const QString &, QString &)
	Provides a quick test of database connectivity over an SSL connection. The database is merely opened and then immediately closed. More...

bool	connect (const QString &, QString &)
	Connects to a database using the currently defined database stored by US_Config and US_Settings. This constructor uses an SSL connection. More...

bool	connect (const QString &, const QString &, const QString &, const QString &, QString &)
	Connects to a database using authentication parameters supplied in the argument list. More...

void	rawQuery (const QString &)
	Implements a simple query on the active database connection, cleaning up any unused results from a previous query. More...

int	statusQuery (const QString &)
	Implements a query on the active database connection, cleaning up any unused results from a previous query. StatusQuery() returns the integer status of the query from the US3 database, but no other result data. Useful for INSERT, UPDATE and DELETE style queries. More...

int	statusQuery (const QStringList &)
	An overloaded method that builds the statusQuery string from the passed arguments and the stored DB settings. More...

int	functionQuery (const QStringList &)
	Implements a query on the active database connection, cleaning up any unused results from a previous query. FunctionQuery() returns the integer status of the query from the US3 database, but no other result data. Useful for COUNT style queries. Uses a SELECT command. More...

void	query (const QString &)
	Implements a general-purpose query on the active database connection, cleaning up any unused results from a previous query. Query() can be used for INSERT, UPDATE, or DELETE style queries, but statusQuery() is easier to use in those cases. With SELECT queries, Query() retrieves the result set and prepares to return individual values using the next() and value() methods. Use the numRows() method to find out how many rows there are. More...

void	query (const QStringList &)
	A method to generate and make a CALL to the database and automatically add the user's guid and password. Queries are normally in the form of: CALL function_name( guid, pw, [arg1,] ... ) More...

bool	next (void)
	Fetches the next row in the result set, if one exists. Returns TRUE if the operation has been successful, or FALSE if there are no more rows. Use the value() function to retrieve the individual values in the row. More...

QVariant	value (unsigned)
	Returns the value of one column in the current row of the result set. This function can be called repeatedly to retrieve values for all columns in the row. If there are n columns in the row, the columns are numbered 0...n-1. Value() returns the QVariant::invalid type in cases where the data type is unknown, or if the index is out of bounds. Use next() to advance the row pointer. More...

bool	isConnected (void)
	Returns TRUE if the US3 system is connected to a database, or returns FALSE otherwise. More...

int	numRows (void)
	Returns the number of rows returned in the current result set. More...

int	writeBlobToDB (const QString &, const QString &, const int)
	Loads raw binary data from a file and writes it to the database. This makes writing a record with binary information in it a two-step process—first write the record with the rest of the information using statusQuery(), get the table ID for that record using lastInsertID(), and finally write the corresponding binary information into the same record using writeBlobToDB(). WriteBlobToDB() will return dbStatus.ERROR if the file cannot be opened, or pass through any error codes returned by the db. More...

int	readBlobFromDB (const QString &, const QString &, const int)
	Reads raw binary data from the database and writes it to the specified file. This makes reading a record with binary information in it a two-step process—first get the rest of the record with query(), and then use the table ID for that record to read the corresponding binary information to a file using readBlobFromDB(). ReadBlobFromDB() will return dbStatus.ERROR if the file cannot be opened, or pass through any error codes returned by the db. More...

int	readAucFromDB (const QString &, int)
	Reads AUC data from the database and writes it to the specified file. If the downloaded data is compressed, it is decompressed. More...

int	writeAucToDB (const QString &, int)
	Loads AUC data from a file, compresses it, and writes it to the database. More...

QString	lastError (void)
	Returns a text string containing the most recent error encountered by the US3 database system. If a query did not result in an error, lastError() might return a text string describing the previous error, or it might return an empty string indicating that there was no error. If you wish to determine if there has been an error, call lastErrno() first and then call lastError() to find out what the error was. More...

int	lastErrno (void)
	Returns the integer status code of the most recent query performed by the US3 database system. If a query did not result in an error, then lastErrno() will return 0. If the status code is not 0, then you can use the status code itself to determine what went wrong, or you can call lastError() for a text string describing the error. More...

int	lastInsertID (void)
	Returns the ID of the row resulting from the most recent INSERT or UPDATE statement. More...

QString	lastDebug (void)
	Returns the most recent debug statement. This is only used in the development of the MySQL stored routines. To use it, one would issue a statement like this inside a stored routine: More...

unsigned long	mysqlEscapeString (QByteArray &, QByteArray &, unsigned long)
	Inserts escape codes into a string, preparing the string to be inserted into a mysql database. More...

Private Member Functions
QString	buildQuery (const QStringList &)

QString	buildQuerySelect (const QStringList &)

Private Attributes
bool	connected

MYSQL *	db

MYSQL_RES *	result

MYSQL_ROW	row

QString	email

QString	userPW

QString	guid

QString	certFile

QString	keyFile

QString	caFile

QString	error

int	db_errno

Detailed Description

This class provides connectivity and convenience functions for database access in the US3 system.

Definition at line 18 of file us_db2.h.

Member Enumeration Documentation

enum US_DB2::dbStatus

Some status codes returned by the US3 database.

Enumerator
OK	Query ok; no error.
ERROR	Unspecified error.
NOT_CONNECTED	Database system is not connected.
DUP_EMAIL	Duplicate email addresses exist.
NO_ACCT	No account with that email address was found.
INACTIVE	The account has not been activated.
BADPASS	Incorrect password.
EMPTY	A required parameter was empty.
BAD_CHECKSUM	A transmission error; bad MD5 checksum.
NOTPERMITTED	User does not have permission.
BADOPERATOR	Operator does not have permission to use that instrument.
BADLABLOCATION	Instrument does not belong in that lab.
BADGUID	The specified GUID is not in the correct format.
NOROWS	No rows returned.
INSERTNULL	Attempt to insert NULL value in a NOT NULL field.
INSERTDUP	Attempt to insert a duplicate value in a primary or unique key field
DUPFIELD	Attempt to insert duplicate value where one should not exist.
CONSTRAINT_FAILED	A database constraint failed.
NO_BUFFER	No buffer with that ID was found.
NO_COMPONENT	No buffer component with that ID was found.
NO_ROTOR	No rotor or abstract rotor with that ID exists.
NO_ANALYTE	No analyte with the specified ID exists.
NO_LAB	No lab with the specified ID exists.
NO_PERSON	No person with the specified ID exists.
NO_MODEL	No model with the specified ID exists.
NO_EXPERIMENT	No experiment with the specified ID exists.
NO_RAWDATA	No raw data with the specified ID exists.
NO_EDITDATA	No edit profile with the specified ID exists.
NO_SOLUTION	No solution with the specified ID exists.
CALIB_IN_USE	The specified rotor calibration profile is in use, and cannot be deleted
ROTOR_IN_USE	The specified rotor is in use, and cannot be deleted.
NO_NOISE	No noise file with the specified ID exists.
NO_PROJECT	No project with the specified ID exists.
BUFFR_IN_USE	The buffer to be deleted is in use.
ANALY_IN_USE	The analyte to be deleted is in use.
SOLUT_IN_USE	The solution to be deleted is in use.
NO_CALIB	The specified calibration profile cannot be found.
NO_REPORT	No global report structure with that ID exists.
NO_REPORT_DETAIL	No report detail with the specified ID exists.
NO_REPORT_DOCUMENT	No report document with the specified ID exists.
UNKNOWN_ERR	No project with the specified ID exists.

Definition at line 43 of file us_db2.h.

enum US_DB2::dbUserlevels

Some user levels used by the US3 database procedures.

Enumerator
USER	Regular user.
PRIV	Privileged user.
ANALYST	Data Analyst.
SUPER	Super User.
ADMIN	Admin.

Definition at line 97 of file us_db2.h.

Constructor & Destructor Documentation

US_DB2::US_DB2 ( )

Generic constructor for the US_DB2 class. Initialization is performed but database connection is not attempted.

Definition at line 8 of file us_db2.cpp.

US_DB2::US_DB2 ( const QString & masterPW )

Constructor for the US_DB2 class. Initialization is performed and connection is attempted. Connects to a database using the currently defined database stored by US_Config and US_Settings. This constructor uses an SSL connection.

Parameters

masterPW Master password to decrypt DB password

Definition at line 25 of file us_db2.cpp.

US_DB2::~US_DB2 ( )

Generic destructor for the US_DB2 class. Database connection is terminated and any resources in use are closed and deleted.

Definition at line 50 of file us_db2.cpp.

Member Function Documentation

QString US_DB2::buildQuery ( const QStringList & arguments )

private

Definition at line 453 of file us_db2.cpp.

QString US_DB2::buildQuerySelect ( const QStringList & arguments )

private

Definition at line 472 of file us_db2.cpp.

bool US_DB2::connect	(	const QString &	masterPW,
		QString &	err
	)

Connects to a database using the currently defined database stored by US_Config and US_Settings. This constructor uses an SSL connection.

Parameters

masterPW	Master password to decrypt DB password
err	A reference to a string for error responses.

Definition at line 170 of file us_db2.cpp.

bool US_DB2::connect	(	const QString &	host,
		const QString &	dbname,
		const QString &	user,
		const QString &	password,
		QString &	error
	)

Connects to a database using authentication parameters supplied in the argument list.

Parameters

host	The name of the host of the database server.
dbname	The name of the database to access.
user	The user name that can access the database.
password	The unencrypted password for the database/user.
error	A reference to a string for error responses.

Definition at line 286 of file us_db2.cpp.

int US_DB2::functionQuery ( const QStringList & arguments )

Implements a query on the active database connection, cleaning up any unused results from a previous query. FunctionQuery() returns the integer status of the query from the US3 database, but no other result data. Useful for COUNT style queries. Uses a SELECT command.

Parameters

arguments A list that contains the function name and any additional arguments needed.

Definition at line 397 of file us_db2.cpp.

bool US_DB2::isConnected ( void )

Returns TRUE if the US3 system is connected to a database, or returns FALSE otherwise.

Definition at line 519 of file us_db2.cpp.

QString US_DB2::lastDebug ( void )

Returns the most recent debug statement. This is only used in the development of the MySQL stored routines. To use it, one would issue a statement like this inside a stored routine:

SET @DEBUG = 'A Debugging message';

lastDebug() returns only the string that is currently assigned to @DEBUG

Definition at line 765 of file us_db2.cpp.

int US_DB2::lastErrno ( void )

inline

Returns the integer status code of the most recent query performed by the US3 database system. If a query did not result in an error, then lastErrno() will return 0. If the status code is not 0, then you can use the status code itself to determine what went wrong, or you can call lastError() for a text string describing the error.

Definition at line 341 of file us_db2.h.

QString US_DB2::lastError ( void )

inline

Returns a text string containing the most recent error encountered by the US3 database system. If a query did not result in an error, lastError() might return a text string describing the previous error, or it might return an empty string indicating that there was no error. If you wish to determine if there has been an error, call lastErrno() first and then call lastError() to find out what the error was.

Definition at line 333 of file us_db2.h.

int US_DB2::lastInsertID ( void )

Returns the ID of the row resulting from the most recent INSERT or UPDATE statement.

Definition at line 744 of file us_db2.cpp.

unsigned long US_DB2::mysqlEscapeString	(	QByteArray &	to,
		QByteArray &	from,
		unsigned long	length
	)

Inserts escape codes into a string, preparing the string to be inserted into a mysql database.

Parameters

to	A reference to a data structure where the escaped string will go
from	A reference to the source string
length	The length of the source data

Definition at line 786 of file us_db2.cpp.

bool US_DB2::next ( void )

Fetches the next row in the result set, if one exists. Returns TRUE if the operation has been successful, or FALSE if there are no more rows. Use the value() function to retrieve the individual values in the row.

Definition at line 494 of file us_db2.cpp.

int US_DB2::numRows ( void )

Returns the number of rows returned in the current result set.

Definition at line 527 of file us_db2.cpp.

void US_DB2::query ( const QString & sqlQuery )

Implements a general-purpose query on the active database connection, cleaning up any unused results from a previous query. Query() can be used for INSERT, UPDATE, or DELETE style queries, but statusQuery() is easier to use in those cases. With SELECT queries, Query() retrieves the result set and prepares to return individual values using the next() and value() methods. Use the numRows() method to find out how many rows there are.

Parameters

sqlQuery A string containing the US3 query to execute.

Definition at line 405 of file us_db2.cpp.

void US_DB2::query ( const QStringList & arguments )

A method to generate and make a CALL to the database and automatically add the user's guid and password.

Queries are normally in the form of:
CALL function_name( guid, pw, [arg1,] ... )

Parameters

arguments A list that contains the function name and any additional arguments needed.

Definition at line 448 of file us_db2.cpp.

void US_DB2::rawQuery ( const QString & sqlQuery )

Implements a simple query on the active database connection, cleaning up any unused results from a previous query.

Parameters

sqlQuery A string containing the US3 query to execute.

Definition at line 338 of file us_db2.cpp.

int US_DB2::readAucFromDB	(	const QString &	filename,
		int	tableID
	)

Reads AUC data from the database and writes it to the specified file. If the downloaded data is compressed, it is decompressed.

Parameters

filename	The complete and absolute pathname of the destination file name.
tableID	The integer primary-key index of the record that contains the raw AUC data.

Returns: dbStatus.ERROR if the file cannot be opened, or any error codes returned by the db.

Definition at line 832 of file us_db2.cpp.

int US_DB2::readBlobFromDB	(	const QString &	filename,
		const QString &	procedure,
		const int	tableID
	)

Reads raw binary data from the database and writes it to the specified file. This makes reading a record with binary information in it a two-step process—first get the rest of the record with query(), and then use the table ID for that record to read the corresponding binary information to a file using readBlobFromDB(). ReadBlobFromDB() will return dbStatus.ERROR if the file cannot be opened, or pass through any error codes returned by the db.

Parameters

filename	The complete and absolute pathname of the file to write the binary data to. ReadBlobFromDB() will try to open the file and write the raw binary data to it.
procedure	The name of the MySQL stored procedure that will read the data from the database. The procedure must implement a parameter list as follows: CALL procedure( yourGUID, yourPassword, tableID); and should return two fields: the blob data and an MD5 checksum
tableID	The integer primary-key index of the record that contains the raw binary data.

Definition at line 655 of file us_db2.cpp.

int US_DB2::statusQuery ( const QString & sqlQuery )

Implements a query on the active database connection, cleaning up any unused results from a previous query. StatusQuery() returns the integer status of the query from the US3 database, but no other result data. Useful for INSERT, UPDATE and DELETE style queries.

Parameters

sqlQuery A string containing the US3 query to execute.

Definition at line 363 of file us_db2.cpp.

int US_DB2::statusQuery ( const QStringList & arguments )

An overloaded method that builds the statusQuery string from the passed arguments and the stored DB settings.

Parameters

arguments A list that contains the function name and any additional arguments needed.

Definition at line 392 of file us_db2.cpp.

bool US_DB2::test_db_connection	(	const QString &	host,
		const QString &	dbname,
		const QString &	user,
		const QString &	password,
		QString &	error
	)

Provides a quick test of database connectivity to ensure that the parameters in the database setup are correct. The database is merely opened and then immediately closed.

Parameters

host	The name of the host of the database server.
dbname	The name of the database to access.
user	The user name that can access the database.
password	The unencrypted password for the database/user.
error	A reference to a string for error responses.

Definition at line 65 of file us_db2.cpp.

bool US_DB2::test_secure_connection	(	const QString &	host,
		const QString &	dbname,
		const QString &	user,
		const QString &	password,
		const QString &	email,
		const QString &	pw,
		QString &	err
	)

Provides a quick test of database connectivity over an SSL connection. The database is merely opened and then immediately closed.

Parameters

host	The name of the host of the database server.
dbname	The name of the database to access.
user	The user name that can access the database.
password	The unencrypted password for the database/user.
email	The email address of the investigator
pw	The investigator's password
err	A reference to a string for error responses.

Definition at line 106 of file us_db2.cpp.

QVariant US_DB2::value ( unsigned index )

Returns the value of one column in the current row of the result set. This function can be called repeatedly to retrieve values for all columns in the row. If there are n columns in the row, the columns are numbered 0...n-1. Value() returns the QVariant::invalid type in cases where the data type is unknown, or if the index is out of bounds. Use next() to advance the row pointer.

Parameters

index The column number in the current row.

Definition at line 510 of file us_db2.cpp.

int US_DB2::writeAucToDB	(	const QString &	filename,
		int	tableID
	)

Loads AUC data from a file, compresses it, and writes it to the database.

Parameters

filename	The complete and absolute pathname of the file with the binary data.
tableID	The integer primary-key index of the record where the raw AUC data will be pplaced.

Returns: dbStatus.ERROR if the file cannot be opened, or any error codes returned by the db.

Definition at line 808 of file us_db2.cpp.

int US_DB2::writeBlobToDB	(	const QString &	filename,
		const QString &	procedure,
		const int	tableID
	)

Loads raw binary data from a file and writes it to the database. This makes writing a record with binary information in it a two-step process—first write the record with the rest of the information using statusQuery(), get the table ID for that record using lastInsertID(), and finally write the corresponding binary information into the same record using writeBlobToDB(). WriteBlobToDB() will return dbStatus.ERROR if the file cannot be opened, or pass through any error codes returned by the db.

Parameters

filename	The complete and absolute pathname of the file with the binary data. WriteBlobToDB() will try to open the file and read the raw binary data.
procedure	The name of the MySQL stored procedure that will accept the data and write it to the database. The procedure must implement a parameter list as follows: CALL procedure( yourGUID, yourPassword, tableID, blobData, checksum ); where checksum is an MD5 checksum<br>
tableID	The integer primary-key index of the record that the raw binary data should be written to.

Definition at line 536 of file us_db2.cpp.

Member Data Documentation

QString US_DB2::caFile

private

Definition at line 383 of file us_db2.h.

QString US_DB2::certFile

private

Definition at line 381 of file us_db2.h.

bool US_DB2::connected

private

Definition at line 371 of file us_db2.h.

MYSQL* US_DB2::db

private

Definition at line 373 of file us_db2.h.

int US_DB2::db_errno

private

Definition at line 385 of file us_db2.h.

QString US_DB2::email

private

Definition at line 377 of file us_db2.h.

QString US_DB2::error

private

Definition at line 384 of file us_db2.h.

QString US_DB2::guid

private

Definition at line 379 of file us_db2.h.

QString US_DB2::keyFile

private

Definition at line 382 of file us_db2.h.

MYSQL_RES* US_DB2::result

private

Definition at line 374 of file us_db2.h.

MYSQL_ROW US_DB2::row

private

Definition at line 375 of file us_db2.h.

QString US_DB2::userPW

private

Definition at line 378 of file us_db2.h.

The documentation for this class was generated from the following files:

Public Types

Public Member Functions

Private Member Functions

Private Attributes

Detailed Description

Member Enumeration Documentation

Constructor & Destructor Documentation

Member Function Documentation

Member Data Documentation