psDB.h

Go to the documentation of this file.

/** @file  psDB.h
 *
 *  @brief database types and functions
 *
 *  This file defines the abstract database type and functions that
 *  perform basic database operations.
 *
 *  @ingroup DataBase
 *
 *  @author Joshua Hoblitt
 *
 *  @version $Revision: 1.1.1.1 $ $Name:  $
 *  @date $Date: 2005/09/14 20:42:48 $
 *
 *  Copyright 2005 Joshua Hoblitt, University of Hawaii
 */

#ifndef PS_DB_H
#define PS_DB_H 1

#ifndef OMIT_PSDB

#include "psType.h"
#include "psMetadata.h"

/// @addtogroup DataBase
/// @{

/** Database handle
 *
 *  An opaque object representing a database connection.
 *
 */
typedef struct
{
    void* mysql;                       ///< MySQL database handle
}
psDB;

/** Opens a new database connection
 *
 *  @return psDB*:      A new psDB object if the database connection is successful or NULL on
 *  failure.
 */
psDB *psDBInit(
    const char *host,                  ///< Database server hostname
    const char *user,                  ///< Database username
    const char *passwd,                ///< Database password
    const char *dbname                 ///< Database namespace
);

/** Closes a database connection  */
void psDBCleanup(
    psDB *dbh                          ///< Database handle
);

/** Creates a new database namespace
 *
 * @return bool:    true on success
 */
bool psDBCreate(
    psDB *dbh,                         ///< Database handle
    const char *dbname                 ///< New database namespace
);

/** Changes the current database namespace
 *
 * @return bool:    true on success
 */
bool psDBChange(
    psDB *dbh,                         ///< Database handle
    const char *dbname                 ///< Database namespace
);

/** Drops a database namespace
 *
 * @return bool:    true on success
 */
bool psDBDrop(
    psDB *dbh,                         ///< Database handle
    const char *dbname                 ///< Database namespace
);

/** 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.
 *
 * @return bool:    true on success
 */
bool p_psDBRunQuery(
    psDB *dbh,                         ///< Database handle
    const char *format,                ///< SQL string to execute
    ...                                ///< Arguments for name formatting and metadata item data.
);

/** 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_META_STR would map to an SQL99
 * varchar.  If the value of type is PS_META_STR 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_META_STR 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.
 *
 * @return bool:    true on success
 */
bool psDBCreateTable(
    psDB *dbh,                         ///< Database handle
    const char *tableName,             ///< Table name
    const psMetadata *md               ///< Column names, types, and indexes
);

/** Deletes a database table
 *
 * @return bool:    true on success
*/
bool psDBDropTable(
    psDB *dbh,                          ///< Database handle
    const char *tableName               ///< Table name
);

/** 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.
 *
 * @return psArray*:    A psArray of strings or NULL on failure
 */
psArray *psDBSelectColumn(
    psDB *dbh,                         ///< Database handle
    const char *tableName,             ///< Table name
    const char *col,                   ///< Column name
    unsigned long long limit           ///< Maximum number of elements to return
);

/** 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".
 *
 * @return psVector*:   A psVector or NULL on failure
 */
psVector *psDBSelectColumnNum(
    psDB *dbh,                         ///< Database handle
    const char *tableName,             ///< Table name
    const char *col,                   ///< Column name
    psElemType type,                   ///< Resulting psVector type
    unsigned long long limit           ///< Maximum number of elements to return
);

/** 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_META_STR type.
 * The string value can be a SQL match pattern, e.g. "%foo%", or an empty
 * string, e.g. "", to match NULL field values.
 *
 * @return psArray*:    A psArray of psMetadata or NULL on failure
 */
psArray *psDBSelectRows(
    psDB *dbh,                         ///< Database handle
    const char *tableName,             ///< Table name
    const psMetadata *where,           ///< Row match criteria
    unsigned long long limit           ///< Maximum number of elements to return
);

/** 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_META_STR and PS_META_PRIMITIVE are not supported.  If fields are
 * specified in "row" that do not exist in "tableName", the insert will fail.
 *
 * @return bool:    true on success
 */
bool psDBInsertOneRow(
    psDB *dbh,                         ///< Database handle
    const char *tableName,             ///< Table name
    const psMetadata *row              ///< Row description
);

/** 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().
 *
 * @return bool:    true on success
 */
bool psDBInsertRows(
    psDB *dbh,                         ///< Database handle
    const char *tableName,             ///< Table name
    const psArray *rowSet              ///< Set of rows to insert
);

/** 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().
 *
 * @return psArray*:    A psArray of psMetadata or NULL on failure
 */
psArray *psDBDumpRows(
    psDB *dbh,                         ///< Database handle
    const char *tableName              ///< Table name
);

/** 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.
 *
 * @return psMetadata*:     A psMetadata containing either a psArrays or psVector per column
 */
psMetadata *psDBDumpCols(
    psDB *dbh,                         ///< Database handle
    const char *tableName              ///< Table name
);

/** 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.
 *
 * @return long:    The number of rows modified or a negative value on error
 */
long psDBUpdateRows(
    psDB *dbh,                         ///< Database handle
    const char *tableName,             ///< Table name
    const psMetadata *where,           ///< Row match criteria
    const psMetadata *values           ///< new field values
);

/** 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.
 *
 * @return long:    The number of rows removed or a negative value on error
 */
long psDBDeleteRows(
    psDB *dbh,                         ///< Database handle
    const char *tableName,             ///< Table name
    const psMetadata *where,           ///< Row match criteria
    unsigned long long limit           ///< Maximum number of rows to delete
);

/// @}

#endif // OMIT_PSDB

#endif // PS_DB_H

---