<?php
//****************************************************************************
//
// Copyright (C) 2001 Eric SEIGNE <hide@address.com>
//
// This program is free software; you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with this program; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
//
//****************************************************************************
//
// For more informations, get to the project's main source file.
//
// Important Note that some functions inside this file use the sql_*
// functions declared in the sql.php library file.
// So it must be linked to it.
//
//****************************************************************************
if(!defined("__db_php__")):
define("__db_php__", "1");
//****************************************************************************
//****************************************************************************
//
// CONSTANTS
//
//****************************************************************************
//****************************************************************************
//----------------------------------------------------------------------------
// database types definitions
//----------------------------------------------------------------------------
// basic types
define("DBT_SERIAL", 0x00000001); // PgSQL's SERIAL equivalent - MySQL's AUTO_INCREMENT equivalent - Note that DBT_SERIAL is not null, by definition
define("DBT_INT4", 0x00000002); // signed integer (4 bytes)
define("DBT_INT8", 0x00000004); // signed integer (8 bytes)
define("DBT_UINT4", 0x00000008); // unsigned integer (4 bytes) - note that unsigned values are not supported by PostgreSQL
define("DBT_UINT8", 0x00000010); // unsigned integer (8 bytes) - note that unsigned values are not supported by PostgreSQL
define("DBT_FLOAT", 0x00000020);
define("DBT_STRING", 0x00000040); // for a single-line text - <input type="text">
define("DBT_TEXT", 0x00000080); // for a multi-line text - <textarea>
// special types
define("DBT_NOTNULL", 0x00010000); // means that entry must not be empty
define("DBT_MASK", 0x00020000); // means that entry must be masked (ie: entry is a password)
define("DBT_LINK", 0x00040000); // means that entry is linked to a line of an another table
//----------------------------------------------------------------------------
// constants related to the DBDESC array usage
//----------------------------------------------------------------------------
// In column arrays
define("DBD_TYPE", 0); // column type
define("DBD_CAPTION", 1); // column caption (title)
define("DBD_EXTRA", 2); // column extra infos array (note that array can be NULL)
// Extra Info array - Link Info (only if type is DBT_LINK)
define("DBDE_LI_TABLE", "li_table"); // name of linked destination table
define("DBDE_LI_COLUMN", "li_column"); // name of linked destination column (in the dest table)
// Extra info array - Type Info (only if type is DBT_STRING or DBT_TEXT)
define("DBDE_TI_MAXLENGTH", "ti_maxlength"); // integer that represent the max length of a string or a text. no limit is specified when it equal to zero.
//----------------------------------------------------------------------------
// DataBase Tables Names
//----------------------------------------------------------------------------
define("DBTN_CONFIG", "brad_config");
define("DBTN_USER", "brad_user");
define("DBTN_USERSESSIONS", "brad_user_sessions");
define("DBTN_ENTERPRISE", "brad_enterprise");
define("DBTN_CONTACT", "brad_contact");
define("DBTN_CONTACTCATEGORY", "brad_contact_category");
define("DBTN_ITEM", "brad_item");
define("DBTN_ITEMCATEGORY", "brad_item_category");
//----------------------------------------------------------------------------
// Columns specific definitions
//----------------------------------------------------------------------------
define("USERCAT_ADMINISTRATOR", "1");
define("USERCAT_USER", "2");
define("CONTACTCLASS_PRIVATE", "1");
define("CONTACTCLASS_PUBLIC", "2");
define("CONTACTCLASS_PROTECTED", "3");
//----------------------------------------------------------------------------
// Program database(s) description
//
// - DESCRIPTION -
// Main and only one DBDESCription variable.
// ALL databases descriptions must be reported inside this one !
// Even if there is multiple databases (sorry for the inconvenience).
// That's why you must name all your tables with unique strings.
//
// - USAGE -
// DBDESC["table_name"]["column_name"][DBD_*]
//----------------------------------------------------------------------------
$DBDESC = array(
DBTN_CONFIG => array(
"currencies" => array(DBT_STRING | DBT_NOTNULL, "Currencies", NULL),
"dateformat" => array(DBT_STRING | DBT_NOTNULL, "Date format", NULL),
"vat" => array(DBT_STRING | DBT_NOTNULL, "VAT", NULL),
"external_modules" => array(DBT_TEXT, "External modules", NULL),
"mails_alertthresholds" => array(DBT_TEXT, "Alert thresholds mailing list", NULL)
),
DBTN_USER => array(
"id" => array(DBT_SERIAL, "ID", NULL),
"login" => array(DBT_STRING | DBT_NOTNULL, "Login", array(DBDE_TI_MAXLENGTH=>"32")),
"pass" => array(DBT_STRING | DBT_NOTNULL | DBT_MASK, "Password", array(DBDE_TI_MAXLENGTH=>"32")),
"lastname" => array(DBT_STRING, "Lastname", array(DBDE_TI_MAXLENGTH=>"32")),
"firstname" => array(DBT_STRING, "Firstname", array(DBDE_TI_MAXLENGTH=>"32")),
"email" => array(DBT_STRING, "EMail", array(DBDE_TI_MAXLENGTH=>"64")),
"category" => array(DBT_INT4 | DBT_NOTNULL, "Category", NULL),
"access_intmods" => array(DBT_INT8 | DBT_NOTNULL, "Authorized internal modules", NULL)
),
DBTN_USERSESSIONS => array(
"startstamp" => array(DBT_STRING, "Start stamp", array(DBDE_TI_MAXLENGTH=>"32")),
"login" => array(DBT_STRING | DBT_NOTNULL, "Login", array(DBDE_TI_MAXLENGTH=>"32")),
"addr" => array(DBT_STRING, "Address", array(DBDE_TI_MAXLENGTH=>"64")),
"id" => array(DBT_STRING | DBT_NOTNULL, "ID", NULL)
),
DBTN_ENTERPRISE => array(
"id" => array(DBT_SERIAL, "ID", NULL),
"logopath" => array(DBT_STRING, "Logo path", NULL),
"name" => array(DBT_STRING | DBT_NOTNULL, "Name", array(DBDE_TI_MAXLENGTH=>"32")),
"description" => array(DBT_STRING, "Description", NULL),
"address" => array(DBT_STRING, "Address", NULL),
"postalcode" => array(DBT_STRING, "Postal code", NULL),
"city" => array(DBT_STRING, "City", NULL),
"country" => array(DBT_STRING, "Country", NULL),
"phone" => array(DBT_STRING, "Phone", NULL),
"fax" => array(DBT_STRING, "Fax", NULL),
"emails" => array(DBT_TEXT, "EMails", NULL),
"legalstatus" => array(DBT_STRING, "Legal status", NULL),
"trade_register" => array(DBT_STRING, "Trade register", NULL),
"ape" => array(DBT_STRING, "APE", NULL),
"capital" => array(DBT_STRING, "Capital", NULL)
),
DBTN_CONTACTCATEGORY => array(
"id" => array(DBT_SERIAL, "ID", NULL),
"description" => array(DBT_STRING | DBT_NOTNULL, "Description", array(DBDE_TI_MAXLENGTH=>"32"))
),
DBTN_CONTACT => array(
"id" => array(DBT_SERIAL, "ID", NULL),
"lastname" => array(DBT_STRING, "Lastname", NULL),
"firstname" => array(DBT_STRING, "Firstname", NULL),
"enterprise" => array(DBT_STRING, "Enterprise", NULL),
"address" => array(DBT_TEXT, "Address", NULL),
"postalcode" => array(DBT_STRING, "Postal code", NULL),
"city" => array(DBT_STRING, "City", NULL),
"country" => array(DBT_STRING, "Country", NULL),
"phone" => array(DBT_STRING, "Phone", NULL),
"mobile" => array(DBT_STRING, "Mobile", NULL),
"fax" => array(DBT_STRING, "Fax", NULL),
"email" => array(DBT_STRING, "EMail", NULL),
"website" => array(DBT_STRING, "Website", NULL),
"comments" => array(DBT_TEXT, "Comments", NULL),
"category" => array(DBT_INT4 | DBT_NOTNULL | DBT_LINK, "Category", array(DBDE_LI_TABLE=>DBTN_CONTACTCATEGORY, DBDE_LI_COLUMN=>"id")),
"owner" => array(DBT_INT4 | DBT_NOTNULL | DBT_LINK, "Owner", array(DBDE_LI_TABLE=>DBTN_USER, DBDE_LI_COLUMN=>"id")),
"class" => array(DBT_INT4 | DBT_NOTNULL, "Class", NULL)
),
DBTN_ITEMCATEGORY => array(
"id" => array(DBT_SERIAL, "ID", NULL),
"description" => array(DBT_STRING | DBT_NOTNULL, "Description", array(DBDE_TI_MAXLENGTH=>"32"))
),
DBTN_ITEM => array(
"id" => array(DBT_SERIAL, "ID", NULL),
"ref" => array(DBT_STRING | DBT_NOTNULL, "Reference", array(DBDE_TI_MAXLENGTH=>"32")),
"description" => array(DBT_TEXT, "Description", NULL),
"in_price" => array(DBT_FLOAT, "In price", NULL),
"out_price" => array(DBT_FLOAT, "Out price", NULL),
"quantity" => array(DBT_INT4 | DBT_NOTNULL, "Quantity", NULL),
"alert_threshold" => array(DBT_INT4 | DBT_NOTNULL, "Alert threshold", NULL),
"category" => array(DBT_INT4 | DBT_LINK, "Category", array(DBDE_LI_TABLE=>DBTN_ITEMCATEGORY, DBDE_LI_COLUMN=>"id"))
)
);
//****************************************************************************
//****************************************************************************
//
// DB UTILITIES
//
//****************************************************************************
//****************************************************************************
//----------------------------------------------------------------------------
// DB_Delete - SQL DELETE command handler.
//
// PARAMETERS
// table_name - Name of the table to delete inside.
// to_delete - The SQL DELETE command.
// ie : "*".
// extra - Additionnal arguments to add to the SQL request.
// ie : "WITH col1='foo'"
// or "ORDER BY id"
// Can be NULL.
//
// RETURN VALUE
// FALSE on error.
//----------------------------------------------------------------------------
function DB_Delete ($table_name, $to_delete, $extra)
{
// prepare SQL request
$req = "DELETE";
if(!empty($to_delete))
$req .= " ".trim($to_delete);
$req .= " FROM ".$table_name;
if(!empty($extra))
$req .= " ".trim($extra);
// send it to the SQL engine
$res = sql_exec($req);
if(!$res)
return FALSE;
return TRUE;
}
//----------------------------------------------------------------------------
// DB_GetColCaption - Returns the caption of a column specified by its index
// or its name.
//----------------------------------------------------------------------------
function DB_GetColCaption ($table_name, $column_indexname)
{
global $DBDESC;
if(is_int($column_indexname))
{
$table_array = $DBDESC[$table_name];
reset($table_array);
for($i=0 ; list($key, $value)=each($table_array) ; $i++)
if($column_indexname == $i)
return $table_array[$key][DBD_CAPTION];
return 0;
}
else
return $DBDESC[$table_name][$column_indexname][DBD_CAPTION];
}
//----------------------------------------------------------------------------
// DB_GetColCaptions - Returns a 1D array that contains captions of each
// column.
//----------------------------------------------------------------------------
function DB_GetColCaptions ($table_name)
{
global $DBDESC;
$table_array = $DBDESC[$table_name];
$cols_names = DB_GetColNames($table_name);
$n = count($table_array);
for($i=0 ; $i<$n ; $i++)
{
$return_table[$i] = $table_array[$cols_names[$i]][DBD_CAPTION];
$return_table[$cols_names[$i]] = $return_table[$i];
}
return $return_table;
}
//----------------------------------------------------------------------------
// DB_GetColIndex - Returns the index of a column specified by its name.
// On error, returns -1.
//----------------------------------------------------------------------------
function DB_GetColIndex ($table_name, $column_name)
{
global $DBDESC;
$table_array = $DBDESC[$table_name];
reset($table_array);
for($i=0 ; list($key, $value)=each($table_array) ; $i++)
if($column_name == $key)
return $i;
// Error, column name not found.
return -1;
}
//----------------------------------------------------------------------------
// DB_GetColLinkArray - Returns the link array of a column specified by its
// index or its name.
//----------------------------------------------------------------------------
function DB_GetColLinkArray ($table_name, $column_indexname)
{
global $DBDESC;
if(is_int($column_indexname))
{
$table_array = $DBDESC[$table_name];
reset($table_array);
for($i=0 ; list($key, $value)=each($table_array) ; $i++)
if($column_indexname == $i)
return $table_array[$key][DBD_CAPTION];
return 0;
}
else
return $DBDESC[$table_name][$column_indexname][DBD_CAPTION];
}
//----------------------------------------------------------------------------
// DB_GetColName - Returns the name of a column specified by its index.
// On error, returns NULL.
//----------------------------------------------------------------------------
function DB_GetColName ($table_name, $column_index)
{
global $DBDESC;
// NOTE
// I could call sql_fieldname function here, but i found this method faster
// with this implementation. But maybe there is a better way ?
$table_array = $DBDESC[$table_name];
reset($table_array);
for($i=0 ; list($key, $value)=each($table_array) ; $i++)
if($i == $column_index)
return $key;
// Error, column index not found.
return NULL;
}
//----------------------------------------------------------------------------
// DB_GetColNames - Returns a 1D array that contains names of each column.
//----------------------------------------------------------------------------
function DB_GetColNames ($table_name)
{
global $DBDESC;
$table_array = $DBDESC[$table_name];
reset($table_array);
for($i=0 ; list($key, $value)=each($table_array) ; $i++)
$return_table[$i] = $key;
return $return_table;
}
//----------------------------------------------------------------------------
// DB_GetColNames_String - This functions formats the columns names array
// returned by the DB_GetColNames function in a string
// like this : "col1, col2, col3".
// It's useful, to send it to the "to_select" argument
// of DB_Select function.
//----------------------------------------------------------------------------
function DB_GetColNames_String ($table_name)
{
$column_names = DB_GetColNames($table_name);
$to_select = "";
$n = count($column_names);
for($i=0 ; $i<$n ; $i++)
{
$to_select .= $column_names[$i];
if($i < $n-1)
$to_select .= ", ";
}
return $to_select;
}
//----------------------------------------------------------------------------
// DB_GetColNbr - Calculate the number of columns in a table.
//
// RETURN VALUE
// The PHP "count" function result.
//----------------------------------------------------------------------------
function DB_GetColNbr ($table_name)
{
global $DBDESC;
return count($DBDESC[$table_name]);
}
//----------------------------------------------------------------------------
// DB_GetColType - Get a column type af a specified column.
// Column can be specified by its index or its name.
//
// RETURN VALUE
// Returns 0 on error.
//----------------------------------------------------------------------------
function DB_GetColType ($table_name, $column_indexname)
{
global $DBDESC;
if(is_int($column_indexname))
{
$table_array = $DBDESC[$table_name];
reset($table_array);
for($i=0 ; list($key, $value)=each($table_array) ; $i++)
if($column_indexname == $i)
return $table_array[$key][DBD_TYPE];
return 0;
}
else
return $DBDESC[$table_name][$column_indexname][DBD_TYPE];
}
//----------------------------------------------------------------------------
// DB_GetColTypes - Returns a 1D array that contains type of each columns.
//----------------------------------------------------------------------------
function DB_GetColTypes ($table_name)
{
global $DBDESC;
$table_array = $DBDESC[$table_name];
$cols_names = DB_GetColNames($table_name);
$n = count($table_array);
for($i=0 ; $i<$n ; $i++)
$return_table[$i] = $table_array[$cols_names[$i]][DBD_TYPE];
return $return_table;
}
//----------------------------------------------------------------------------
// DB_GetExtraInfo
//----------------------------------------------------------------------------
function DB_GetExtraInfo ($table_name, $column_indexname, $extrainfo_name)
{
global $DBDESC;
if(is_int($column_indexname))
{
$table_array = $DBDESC[$table_name];
reset($table_array);
for($i=0 ; list($key, $value)=each($table_array) ; $i++)
{
if($column_indexname == $i)
{
if($table_array[$key][DBD_EXTRA])
return $table_array[$key][DBD_EXTRA][$extrainfo_name];
else
return NULL;
}
}
return 0;
}
else
{
if($DBDESC[$table_name][$column_indexname][DBD_EXTRA])
return $DBDESC[$table_name][$column_indexname][DBD_EXTRA][$extrainfo_name];
else
return NULL;
}
}
//----------------------------------------------------------------------------
// DB_GetRowNbr - Retrieve to the SQL engine the number of row/entries in
// table.
//
// RETURN VALUE
// -1 on error.
//----------------------------------------------------------------------------
function DB_GetRowNbr ($table_name)
{
$req = "SELECT * FROM ".$table_name;
$res = sql_exec($req);
if(!$res)
return -1;
$numrows = sql_numrows($res);
sql_freeresult($res);
return $numrows;
}
//----------------------------------------------------------------------------
// DB_Insert - SQL INSERT command handler.
//
// PARAMETERS
// table_name - Name of the table to retrieve.
// columns_to_set - List of columns to be modified when inserting
// values. This string must be formated like in
// a usual SQL request : "col1, col2".
// values_to_set - List of values to insert in each column listed
// in columns_to_set parameter. This string must
// also be formated like in a usual SQL request :
// "'value1_for_col1', 'value2_for_col2'".
//
// RETURN VALUE
// FALSE on error, TRUE otherwise.
//----------------------------------------------------------------------------
function DB_Insert ($table_name, $columns_to_set, $values_to_set)
{
// prepare SQL request
$req = "INSERT INTO ".trim($table_name)."(".trim($columns_to_set).") VALUES(".trim($values_to_set).");";
// send it to the SQL engine
$res = sql_exec($req);
if(!$res)
return FALSE;
return TRUE;
}
//----------------------------------------------------------------------------
// DB_InsertFromArray - SQL INSERT command handler.
// Same as DB_Insert but parameters must not be strings
// but one array formated like this :
// $insert_array = array(
// array("column_name1", "new_value_to_insert1"),
// array("column_name2", "new_value_to_insert2")
// );
//
// IMPORTANT
// Magic quotes GPC are handled, so you do not need to
// test them with a get_magic_quotes_gpc() !!!
//
// RETURN VALUE
// FALSE on error, TRUE otherwise.
//----------------------------------------------------------------------------
function DB_InsertFromArray ($table_name, $insert_array)
{
// prepare SQL request
$req = "INSERT INTO ".trim($table_name)."(";
$n = count($insert_array);
for($i=0 ; $i<$n ; $i++)
{
$req .= $insert_array[$i][0];
if($i < $n-1)
$req .= ", ";
}
$req .= ") VALUES(";
for($i=0 ; $i<$n ; $i++)
{
$req .= "'".MagicQuotes_Encode($insert_array[$i][1])."'";
if($i < $n-1)
$req .= ", ";
}
$req .= ");";
// send it to the SQL engine
$res = sql_exec($req);
if(!$res)
return FALSE;
return TRUE;
}
//----------------------------------------------------------------------------
// DB_InsertFromArrays - SQL INSERT command handler.
// Same as DB_Insert but parameters must not be strings
// but arrays.
//
// IMPORTANT
// Magic quotes GPC are handled, so you do not need to
// test them with a get_magic_quotes_gpc() !!!
//
// RETURN VALUE
// FALSE on error, TRUE otherwise.
//----------------------------------------------------------------------------
/*function DB_InsertFromArrays ($table_name, $columns_to_set_array, $values_to_set_array)
{
// prepare SQL request
$req = "INSERT INTO ".trim($table_name)."(";
$n = min(count($columns_to_set_array), count($values_to_set_array));
for($i=0 ; $i<$n ; $i++)
$req .= $columns_to_set_array[$i] . ($i<$n-1) ? ", " : "";
$req .= ") VALUES(";
for($i=0 ; $i<$n ; $i++)
$req .= MagicQuotes_Encode($values_to_set_array[$i]) . ($i<$n-1) ? ", " : "";
$req .= ");";
// send it to the SQL engine
$res = sql_exec($req);
if(!$res)
return FALSE;
return TRUE;
}*/
//----------------------------------------------------------------------------
// DB_Select - SQL SELECT command handler.
//
// Returns a 2D array that contains the wanted entries of a given
// table.
// Hope the table is not too big if you want all entries.
//
// IMPORTANT
// It is important to understand that returned array is ALWAYS in
// 2D.
// Returned array is indexed AND associative.
//
// PARAMETERS
// table_name - Name of the table to retrieve.
// to_select - The SQL SELECT command.
// ie : "*".
// extra - Additionnal arguments to add to the SQL request.
// ie : "WITH col1='foo'"
// Can be NULL.
//
// RETURN VALUE
// FALSE on error, or if there is no entry in table.
// Note that if there is no entry in table, "db_noentry" global
// variable will be set up to TRUE.
//----------------------------------------------------------------------------
function DB_Select ($table_name, $to_select, $extra)
{
// reset "noentry" flag
$GLOBALS["db_noentry"] = FALSE;
// prepare SQL request
$req = "SELECT";
if(!empty($to_select))
$req .= " ".trim($to_select);
$req .= " FROM ".$table_name;
if(!empty($extra))
$req .= " ".trim($extra);
// send it to the SQL engine
$res = sql_exec($req);
if(!$res)
return FALSE;
// retrieve each rows
$return_array = NULL;
for($i=0 ; $row=sql_fetcharray($res) ; $i++)
$return_array[$i] = $row;
// no row in table ?
if(!$return_array) {
$GLOBALS["db_noentry"] = TRUE;
return FALSE;
}
sql_freeresult($res);
return $return_array;
}
//----------------------------------------------------------------------------
// DB_Update - SQL UPDATE command handler.
//
// PARAMETERS
// table_name - Name of the table to retrieve.
// to_set - The SET part of the SQL UPDATE command.
// ie : "login=newlogin, pass=newpass".
// extra - Additionnal arguments to add to the SQL request.
// ie : "WHERE id='yourid'"
// Can be NULL.
//
// RETURN VALUE
// FALSE on error, TRUE otherwise.
//----------------------------------------------------------------------------
function DB_Update ($table_name, $to_set, $extra)
{
// prepare SQL request
$req = "UPDATE ".trim($table_name)." SET ".trim($to_set);
if($extra)
$req .= " ".trim($extra);
// send it to the SQL engine
$res = sql_exec($req);
if(!$res)
return FALSE;
return TRUE;
}
//----------------------------------------------------------------------------
// DB_UpdateFromArray - SQL UPDATE command handler.
// Same as DB_Update but parameters must not be strings
// but one array formated like this :
// $update_array = array(
// array("column_name1", "value_to_update1"),
// array("column_name2", "value_to_update2")
// );
//
// IMPORTANT
// Magic quotes GPC are handled, so you do not need to
// test them with a get_magic_quotes_gpc() !!!
//
// RETURN VALUE
// FALSE on error, TRUE otherwise.
//----------------------------------------------------------------------------
function DB_UpdateFromArray ($table_name, $update_array, $extra)
{
// prepare SQL request
$req = $req = "UPDATE ".trim($table_name)." SET ";
$n = count($update_array);
for($i=0 ; $i<$n ; $i++)
{
$req .= $update_array[$i][0]."='".MagicQuotes_Encode($update_array[$i][1])."'";
if($i < $n-1)
$req .= ", ";
}
if($extra)
$req .= " ".trim($extra);
// send it to the SQL engine
$res = sql_exec($req);
if(!$res)
return FALSE;
return TRUE;
}
endif;
?>