DataBase

Files
file	psDB.h
	database types and functions
Data Structures
struct	psDB
	Database handle. More...
Functions
psDB *	psDBInit (const char *host, const char *user, const char *passwd, const char *dbname)
	Opens a new database connection.
void	psDBCleanup (psDB *dbh)
	Closes a database connection.
bool	psDBCreate (psDB *dbh, const char *dbname)
	Creates a new database namespace.
bool	psDBChange (psDB *dbh, const char *dbname)
	Changes the current database namespace.
bool	psDBDrop (psDB *dbh, const char *dbname)
	Drops a database namespace.
bool	p_psDBRunQuery (psDB *dbh, const char *format,...)
	Executes a SQL query.
long	p_psDBRunQueryPrepared (psDB *dbh, const psArray *rowSet, const char *format,...)
	Executes a SQL query as a prepared statement.
psArray *	p_psDBFetchResult (psDB *dbh)
	Fetches the result of a SQL query.
bool	psDBCreateTable (psDB *dbh, const char *tableName, const psMetadata *md)
	Creates a new database table.
bool	psDBDropTable (psDB *dbh, const char *tableName)
	Deletes a database table.
psArray *	psDBSelectColumn (psDB *dbh, const char *tableName, const char *col, unsigned long long limit)
	Selects a column from a table.
psVector *	psDBSelectColumnNum (psDB *dbh, const char *tableName, const char *col, psElemType type, unsigned long long limit)
	Selects a column from a table and casts it to a given type.
psArray *	psDBSelectRows (psDB *dbh, const char *tableName, const psMetadata *where, unsigned long long limit)
	Selects a set of rows from a table.
bool	psDBInsertOneRow (psDB *dbh, const char *tableName, const psMetadata *row)
	Insert a single row into a table.
bool	psDBInsertRows (psDB *dbh, const char *tableName, const psArray *rowSet)
	Insert a set of rows into a table.
psArray *	psDBDumpRows (psDB *dbh, const char *tableName)
	Retrieves all rows from a table.
psMetadata *	psDBDumpCols (psDB *dbh, const char *tableName)
	Retrieves all columns from a table.
long	psDBUpdateRows (psDB *dbh, const char *tableName, const psMetadata *where, psMetadata *values)
	Updates the field values, as specified, in a table.
long	psDBDeleteRows (psDB *dbh, const char *tableName, const psMetadata *where, unsigned long long limit)
	Deletes rows, as specified, in a table.
long	psDBLastInsertID (psDB *dbh)
	Get the last insert ID.
bool	psDBExplicitTrans (psDB *dbh, bool mode)
	Enable/Disable explicit database transactions.
bool	psDBTransaction (psDB *dbh)
	Start a new transaction set.
bool	psDBCommit (psDB *dbh)
	Commits the current transaction.
bool	psDBRollback (psDB *dbh)
	Rollback the current transaction.

---

Function Documentation

psArray* p_psDBFetchResult (  psDB *  dbh  )

Fetches the result of a SQL query. This function returns the result of the most recent SQL query as a psArray of psMetadata. Caveat emptor. Returns: psArray*: A psArray of psMetadata or NULL on failure Parameters: dbh  Database handle

bool p_psDBRunQuery (  psDB *  dbh, const char *  format,   ... )

Executes a SQL query. This function will execute a string as a raw SQL query. No additional processing of the string or abstraction of the underlying database's SQL dialect is provided. Caveat emptor. Returns: bool: true on success Parameters: dbh  Database handle format  SQL string to execute

long p_psDBRunQueryPrepared (  psDB *  dbh, const psArray *  rowSet, const char *  format,   ... )

Executes a SQL query as a prepared statement. This function will execute a string as a raw SQL query. No additional processing of the string or abstraction of the underlying database's SQL dialect is provided. Caveat emptor. Returns: long: the number of database rows affected Parameters: dbh  Database handle rowSet  row data as psArray of psMetadata format  SQL string to execute

bool psDBChange (  psDB *  dbh, const char *  dbname )

Changes the current database namespace. Returns: bool: true on success Parameters: dbh  Database handle dbname  Database namespace

void psDBCleanup (  psDB *  dbh  )

Closes a database connection. Parameters: dbh  Database handle

bool psDBCommit (  psDB *  dbh  )

Commits the current transaction. This function will commit the current transaction set (a rollback is not possible after this function is successfully executed). A commit also effectively starts a new transaction if explict transactions are enabled. Returns: bool: true on success Parameters: dbh  Database handle

bool psDBCreate (  psDB *  dbh, const char *  dbname )

Creates a new database namespace. Returns: bool: true on success Parameters: dbh  Database handle dbname  New database namespace

bool psDBCreateTable (  psDB *  dbh, const char *  tableName, const psMetadata *  md )

Creates a new database table. This function generates and executes the SQL needed to create a table named "tableName", with the column names and data types as described in "md". Each data item in the psMetadata collection represents a single table field. The name of the field is given by the name of the psMetadataItem and the data type is give by the psMetadataItem.type and psMetadataItem.ptype entries. A lookup table should be used to convert from PSLib types into MySQL compatible SQL data types. For example, a PS_DATA_STRING would map to an SQL99 varchar. If the value of type is PS_DATA_STRING then the psMetadataItem.data element is set to a string with the length for the field written as a text string. The value of the psMetadataItem.data element is unused for the PS_META_PRIMITIVE types. Other psMetadata types beyond PS_DATA_STRING and PS_META_PRIMITIVE are not allowed in a table definition. Database indexes can be specified setting the "comment" field to "Primary Key" or "Key". Comments are otherwise ignored. Returns: bool: true on success Parameters: dbh  Database handle tableName  Table name md  Column names, types, and indexes

long psDBDeleteRows (  psDB *  dbh, const char *  tableName, const psMetadata *  where, unsigned long long  limit )

Deletes rows, as specified, in a table. Delete the rows that are matched by "where" using the same semantics for "where" as in psDBUpdateRow(). If "where" is NULL, all rows in the table will be removed and regardless of the number of rows that were dropped, only 1 will be returned on success. Returns: long: The number of rows removed or a negative value on error Parameters: dbh  Database handle tableName  Table name where  Row match criteria limit  Maximum number of rows to delete

bool psDBDrop (  psDB *  dbh, const char *  dbname )

Drops a database namespace. Returns: bool: true on success Parameters: dbh  Database handle dbname  Database namespace

bool psDBDropTable (  psDB *  dbh, const char *  tableName )

Deletes a database table. Returns: bool: true on success Parameters: dbh  Database handle tableName  Table name

psMetadata* psDBDumpCols (  psDB *  dbh, const char *  tableName )

Retrieves all columns from a table. This function fetches all columns, as either a psVector or a psArray depending on whether or not the column is numeric, and return them in a psMetadata structure where psMetadataItem.name contains the column's name. Returns: psMetadata*: A psMetadata containing either a psArrays or psVector per column Parameters: dbh  Database handle tableName  Table name

psArray* psDBDumpRows (  psDB *  dbh, const char *  tableName )

Retrieves all rows from a table. This function fetches all rows as an psArray of psMetadata. The rows are in the same psMetadata format as used in psDBInsertOneRow() & psDBInsertRows(). Returns: psArray*: A psArray of psMetadata or NULL on failure Parameters: dbh  Database handle tableName  Table name

bool psDBExplicitTrans (  psDB *  dbh, bool  mode )

Enable/Disable explicit database transactions. This function is used to enable explicit transaction support. It is off by default. Returns: bool: true if transactions are enabled Parameters: dbh  Database handle mode  transactions enable/disable

psDB* psDBInit (  const char *  host, const char *  user, const char *  passwd, const char *  dbname )

Opens a new database connection. Returns: psDB*: A new psDB object if the database connection is successful or NULL on failure. Parameters: host  Database server hostname user  Database username passwd  Database password dbname  Database namespace

bool psDBInsertOneRow (  psDB *  dbh, const char *  tableName, const psMetadata *  row )

Insert a single row into a table. This function inserts the data from "row" into "tableName". The "row" specification uses the psMetadataItem name as the column name. The field values may be specified in any order. psMetadata types beyond PS_DATA_STRING and PS_META_PRIMITIVE are not supported. If fields are specified in "row" that do not exist in "tableName", the insert will fail. Returns: bool: true on success Parameters: dbh  Database handle tableName  Table name row  Row description

bool psDBInsertRows (  psDB *  dbh, const char *  tableName, const psArray *  rowSet )

Insert a set of rows into a table. This function inserts the data from "rowSet" into "tableName". "rowSet" is a psArray of psMetadata containing row specifications identical to those used in psDBInsertOneRow(). Returns: bool: true on success Parameters: dbh  Database handle tableName  Table name rowSet  Set of rows to insert

long psDBLastInsertID (  psDB *  dbh  )

Get the last insert ID. Returns the value of MySQLs 'LAST_INSERT_ID()' function Returns: long: The last insert ID Parameters: dbh  Database handle

bool psDBRollback (  psDB *  dbh  )

Rollback the current transaction. This function will rollback the current transaction set. Returns: bool: true on success Parameters: dbh  Database handle

psArray* psDBSelectColumn (  psDB *  dbh, const char *  tableName, const char *  col, unsigned long long  limit )

Selects a column from a table. This function generates and executes the SQL needed to select an entire column from a table or up to "limit" rows from it. If "limit" is 0, the entire range is returned. Returns: psArray*: A psArray of strings or NULL on failure Parameters: dbh  Database handle tableName  Table name col  Column name limit  Maximum number of elements to return

psVector* psDBSelectColumnNum (  psDB *  dbh, const char *  tableName, const char *  col, psElemType  type, unsigned long long  limit )

Selects a column from a table and casts it to a given type. This function generates and executes the SQL needed to select an entire column from a table or up to "limit" rows from it. If "limit" is 0, the entire range is returned. The data in the column is cast to to "pType". Returns: psVector*: A psVector or NULL on failure Parameters: dbh  Database handle tableName  Table name col  Column name type  Resulting psVector type limit  Maximum number of elements to return

psArray* psDBSelectRows (  psDB *  dbh, const char *  tableName, const psMetadata *  where, unsigned long long  limit )

Selects a set of rows from a table. This function returns rows from the specified table which match the restrictions given by "where". The restrictions are specified as field / value pairs. The psMetadata collection "where" must consist of valid database fields. The selected rows are returned as a psArray of psMetadata values, one per row. Currently, the "where" specification only supports the PS_DATA_STRING type. The string value can be a SQL match pattern, e.g. "%foo%", or an empty string, e.g. "", to match NULL field values. Returns: psArray*: A psArray of psMetadata or NULL on failure Parameters: dbh  Database handle tableName  Table name where  Row match criteria limit  Maximum number of elements to return

bool psDBTransaction (  psDB *  dbh  )

Start a new transaction set. This is only a meaningful action if explict transactions are disabled. Returns: bool: true on success Parameters: dbh  Database handle

long psDBUpdateRows (  psDB *  dbh, const char *  tableName, const psMetadata *  where, psMetadata *  values )

Updates the field values, as specified, in a table. This function updates the fields contained in "values" in the row(s) that have a field with the value indicated by "where". Where "where" is in the same format as used in psDBSelectRows(). The "values" specification uses the same format as the row specification used in psDBInsertOneRow(), etc. Returns: long: The number of rows modified or a negative value on error Parameters: dbh  Database handle tableName  Table name where  Row match criteria values  new field values

---