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.5 $ $Name: rel5_0 $
 *  @date $Date: 2005/03/31 23:01:46 $
 *
 *  Copyright 2005 Joshua Hoblitt, University of Hawaii
 */

#ifndef PS_DB_H
#define PS_DB_H 1

#ifdef BUILD_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 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 true on success
 */
bool psDBCreate(
    psDB *dbh,                          ///< Database handle
    const char *dbname                  ///< New database namespace
);

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

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

/** 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 true on success
 */
bool psDBCreateTable(
    psDB *dbh,                          ///< Database handle
    const char *tableName,              ///< Table name
    psMetadata *md                      ///< Column names, types, and indexes
);

/** Deletes a database table
 *
 * @return 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 A psArray of strings or NULL on failure
 */
psArray *psDBSelectColumn(
    psDB *dbh,                          ///< Database handle
    const char *tableName,              ///< Table name
    const char *col,                    ///< Column name
    const psU64 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 A psVector or NULL on failure
 */
psVector *psDBSelectColumnNum(
    psDB *dbh,                          ///< Database handle
    const char *tableName,              ///< Table name
    const char *col,                    ///< Column name
    psElemType pType,                   ///< Resulting psVector type
    const psU64 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 A psArray of psMetadata or NULL on failure
 */
psArray *psDBSelectRows(
    psDB *dbh,                          ///< Database handle
    const char *tableName,              ///< Table name
    psMetadata *where,                  ///< Row match criteria
    const psU64 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 true on success
 */
bool psDBInsertOneRow(
    psDB *dbh,                          ///< Database handle
    const char *tableName,              ///< Table name
    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 true on success
 */
bool psDBInsertRows(
    psDB *dbh,                          ///< Database handle
    const char *tableName,              ///< Table name
    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 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 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 The number of rows modified or a negative value on error
 */
psS64 psDBUpdateRows(
    psDB *dbh,                          ///< Database handle
    const char *tableName,              ///< Table name
    psMetadata *where,                  ///< Row match criteria
    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 The number of rows removed or a negative value on error
 */
psS64 psDBDeleteRows(
    psDB *dbh,                          ///< Database handle
    const char *tableName,             ///< Table name
    psMetadata *where                  ///< Row match criteria
);

/// @}

#endif // BUILD_PSDB

#endif // PS_DB_H

---