Class to manage Dolibarr database access for an SQL database. More...

Inheritance diagram for Database:

[legend]

Public Member Functions
	ifsql ($test, $resok, $resko)
	Format a SQL IF. More...

	fetch_row ($resultset)
	Return datas as an array. More...

	idate ($param)
	Convert (by PHP) a GM Timestamp date into a string date with PHP server TZ to insert into a date field. More...

	lasterrno ()
	Return last error code. More...

	begin ($textinlog='')
	Start transaction. More...

	DDLCreateDb ($database, $charset='', $collation='', $owner='')
	Create a new database Do not use function xxx_create_db (xxx=mysql, ...) as they are deprecated We force to create database with charset this->forcecharset and collate this->forcecollate. More...

	getVersionArray ()
	Return version of database server into an array. More...

	affected_rows ($resultset)
	Return the number of lines in the result of a request INSERT, DELETE or UPDATE. More...

	error ()
	Return description of last error. More...

	DDLListTables ($database, $table='')
	List tables into a database. More...

	lastquery ()
	Return last request executed with query() More...

	order ($sortfield=null, $sortorder=null)
	Define sort criteria of request. More...

	decrypt ($value)
	Decrypt sensitive data in database. More...

	fetch_array ($resultset)
	Return datas as an array. More...

	lasterror ()
	Return last error label. More...

	escape ($stringtoencode)
	Escape a string to insert data. More...

	escapeunderscore ($stringtoencode)
	Escape a string to insert data. More...

	escapeforlike ($stringtoencode)
	Escape a string to insert data into a like. More...

	sanitize ($stringtosanitize)
	Sanitize a string for SQL forging. More...

	last_insert_id ($tab, $fieldid='rowid')
	Get last ID after an insert INSERT. More...

	getPathOfRestore ()
	Return full path of restore program. More...

	rollback ($log='')
	Canceling a transaction and returning to old values. More...

	query ($query, $usesavepoint=0, $type='auto', $result_mode=0)
	Execute a SQL request and return the resultset. More...

	connect ($host, $login, $passwd, $name, $port=0)
	Connexion to server. More...

	plimit ($limit=0, $offset=0)
	Define limits and offset of request. More...

	getServerParametersValues ($filter='')
	Return value of server parameters. More...

	getServerStatusValues ($filter='')
	Return value of server status. More...

	getDefaultCollationDatabase ()
	Return collation used in database. More...

	num_rows ($resultset)
	Return number of lines for result of a SELECT. More...

	getPathOfDump ()
	Return full path of dump program. More...

	getDriverInfo ()
	Return version of database client driver. More...

	errno ()
	Return generic error code of last operation. More...

	DDLCreateTable ($table, $fields, $primary_key, $type, $unique_keys=null, $fulltext_keys=null, $keys=null)
	Create a table into database. More...

	DDLDropTable ($table)
	Drop a table into database. More...

	getListOfCharacterSet ()
	Return list of available charset that can be used to store data in database. More...

	DDLAddField ($table, $field_name, $field_desc, $field_position="")
	Create a new field into table. More...

	DDLDropField ($table, $field_name)
	Drop a field from table. More...

	DDLUpdateField ($table, $field_name, $field_desc)
	Update format of a field into a table. More...

	getListOfCollation ()
	Return list of available collation that can be used for database. More...

	DDLDescTable ($table, $field="")
	Return a pointer of line with description of a table or field. More...

	getVersion ()
	Return version of database server. More...

	getDefaultCharacterSetDatabase ()
	Return charset used to store data in database. More...

	DDLCreateUser ( $dolibarr_main_db_host, $dolibarr_main_db_user, $dolibarr_main_db_pass, $dolibarr_main_db_name)
	Create a user and privileges to connect to database (even if database does not exists yet) More...

	jdate ($string, $gm=false)
	Convert (by PHP) a PHP server TZ string date into a Timestamps date (GMT if gm=true) 19700101020000 -> 3600 with TZ+1 and gmt=0 19700101020000 -> 7200 whaterver is TZ if gmt=1. More...

	encrypt ($fieldorvalue, $withQuotes=1)
	Encrypt sensitive data in database Warning: This function includes the escape and add the SQL simple quotes on strings. More...

	commit ($log='')
	Validate a database transaction. More...

	DDLInfoTable ($table)
	List information of columns into a table. More...

	free ($resultset=null)
	Free last resultset used. More...

	close ()
	Close database connexion. More...

	lastqueryerror ()
	Return last query in error. More...

	DDLGetConnectId ()
	Return connexion ID. More...

	fetch_object ($resultset)
	Returns the current line (as an object) for the resultset cursor. More...

	select_db ($database)
	Select a database. More...

Static Public Member Functions
static	convertSQLFromMysql ($line, $type='ddl')
	Convert a SQL request in Mysql syntax to native syntax. More...

Detailed Description

Class to manage Dolibarr database access for an SQL database.

Definition at line 26 of file Database.interface.php.

Member Function Documentation

◆ affected_rows()

Database::affected_rows ( $resultset )

Return the number of lines in the result of a request INSERT, DELETE or UPDATE.

Parameters

resource $resultset Cursor of the desired request

Returns: int Number of lines

See also: num_rows()

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ begin()

Database::begin ( $textinlog = '' )

Start transaction.

Parameters

string $textinlog Add a small text into log. '' by default.

Returns: int 1 if transaction successfuly opened or already opened, 0 if error

Implemented in TraceableDB, and DoliDB.

◆ close()

Database::close ( )

Close database connexion.

Returns: boolean True if disconnect successfull, false otherwise

See also: connect()

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ commit()

Database::commit ( $log = '' )

Validate a database transaction.

Parameters

string $log Add more log to default log line

Returns: int 1 if validation is OK or transaction level no started, 0 if ERROR

Implemented in TraceableDB, and DoliDB.

◆ connect()

Database::connect	(	$host,
		$login,
		$passwd,
		$name,
		$port = `0`
	)

Connexion to server.

Parameters

string	$host	database server host
string	$login	login
string	$passwd	password
string	$name	name of database (not used for mysql, used for pgsql)
int	$port	Port of database server

Returns: resource Database access handler

See also: close()

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ convertSQLFromMysql()

static Database::convertSQLFromMysql	(	$line,
		$type = `'ddl'`
	)

static

Convert a SQL request in Mysql syntax to native syntax.

Parameters

string	$line	SQL request line to convert
string	$type	Type of SQL order ('ddl' for insert, update, select, delete or 'dml' for create, alter...)

Returns: string SQL request line converted

Implemented in TraceableDB, DoliDBSqlite3, and DoliDBMysqli.

◆ DDLAddField()

Database::DDLAddField	(	$table,
		$field_name,
		$field_desc,
		$field_position = `""`
	)

Create a new field into table.

Parameters

string	$table	Name of table
string	$field_name	Name of field to add
string	$field_desc	Associative array of description of the field to insert [parameter name][parameter value]
string	$field_position	Optional ex .: "after field stuff"

Returns: int <0 if KO, >0 if OK

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ DDLCreateDb()

Database::DDLCreateDb	(	$database,
		$charset = `''`,
		$collation = `''`,
		$owner = `''`
	)

Create a new database Do not use function xxx_create_db (xxx=mysql, ...) as they are deprecated We force to create database with charset this->forcecharset and collate this->forcecollate.

Parameters

string	$database	Database name to create
string	$charset	Charset used to store data
string	$collation	Charset used to sort data
string	$owner	Username of database owner

Returns: resource resource defined if OK, null if KO

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ DDLCreateTable()

Database::DDLCreateTable	(	$table,
		$fields,
		$primary_key,
		$type,
		$unique_keys = `null`,
		$fulltext_keys = `null`,
		$keys = `null`
	)

Create a table into database.

Parameters

string	$table	Name of table
array	$fields	Associative table [field name][table of descriptions]
string	$primary_key	Name of the field that will be the primary key
string	$type	Type of the table
array	$unique_keys	Associative array Name of fields that will be unique key => value
array	$fulltext_keys	Field name table that will be indexed in fulltext
array	$keys	Table of key fields names => value

Returns: int <0 if KO, >=0 if OK

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ DDLCreateUser()

Database::DDLCreateUser	(	$dolibarr_main_db_host,
		$dolibarr_main_db_user,
		$dolibarr_main_db_pass,
		$dolibarr_main_db_name
	)

Create a user and privileges to connect to database (even if database does not exists yet)

Parameters

string	$dolibarr_main_db_host	Server IP
string	$dolibarr_main_db_user	Username to create
string	$dolibarr_main_db_pass	User password to create
string	$dolibarr_main_db_name	Database name where user must be granted

Returns: int <0 if KO, >=0 if OK

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ DDLDescTable()

Database::DDLDescTable	(	$table,
		$field = `""`
	)

Return a pointer of line with description of a table or field.

Parameters

string	$table	Name of table
string	$field	Optional : Name of field if we want description of field

Returns: resource Resource

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ DDLDropField()

Database::DDLDropField	(	$table,
		$field_name
	)

Drop a field from table.

Parameters

string	$table	Name of table
string	$field_name	Name of field to drop

Returns: int <0 if KO, >0 if OK

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ DDLDropTable()

Database::DDLDropTable ( $table )

Drop a table into database.

Parameters

string $table Name of table

Returns: int <0 if KO, >=0 if OK

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ DDLGetConnectId()

Database::DDLGetConnectId ( )

Return connexion ID.

Returns: string Id connexion

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ DDLInfoTable()

Database::DDLInfoTable ( $table )

List information of columns into a table.

Parameters

string $table Name of table

Returns: array Array with information on table

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ DDLListTables()

Database::DDLListTables	(	$database,
		$table = `''`
	)

List tables into a database.

Parameters

string	$database	Name of database
string	$table	Name of table filter ('xxx')

Returns: array List of tables in an array

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ DDLUpdateField()

Database::DDLUpdateField	(	$table,
		$field_name,
		$field_desc
	)

Update format of a field into a table.

Parameters

string	$table	Name of table
string	$field_name	Name of field to modify
string	$field_desc	Array with description of field format

Returns: int <0 if KO, >0 if OK

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ decrypt()

Database::decrypt ( $value )

Decrypt sensitive data in database.

Parameters

string $value Value to decrypt

Returns: string Decrypted value if used

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ encrypt()

Database::encrypt	(	$fieldorvalue,
		$withQuotes = `1`
	)

Encrypt sensitive data in database Warning: This function includes the escape and add the SQL simple quotes on strings.

Parameters

string	$fieldorvalue	Field name or value to encrypt
int	$withQuotes	Return string including the SQL simple quotes. This param must always be 1 (Value 0 is bugged and deprecated).

Returns: string XXX(field) or XXX('value') or field or 'value'

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ errno()

Database::errno ( )

Return generic error code of last operation.

Returns: string Error code (Exemples: DB_ERROR_TABLE_ALREADY_EXISTS, DB_ERROR_RECORD_ALREADY_EXISTS...)

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ error()

Database::error ( )

Return description of last error.

Returns: string Error text

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ escape()

Database::escape ( $stringtoencode )

Escape a string to insert data.

Parameters

string $stringtoencode String to escape

Returns: string String escaped

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ escapeforlike()

Database::escapeforlike ( $stringtoencode )

Escape a string to insert data into a like.

Parameters

string $stringtoencode String to escape

Returns: string String escaped

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ escapeunderscore()

Database::escapeunderscore ( $stringtoencode )

Escape a string to insert data.

Parameters

string $stringtoencode String to escape

Returns: string String escaped

Deprecated:

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ fetch_array()

Database::fetch_array ( $resultset )

Return datas as an array.

Parameters

resource $resultset Resultset of request

Returns: array Array

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ fetch_object()

Database::fetch_object ( $resultset )

Returns the current line (as an object) for the resultset cursor.

Parameters

resource | Connection $resultset Handler of the desired request

Returns: Object Object result line or false if KO or end of cursor

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ fetch_row()

Database::fetch_row ( $resultset )

Return datas as an array.

Parameters

resource $resultset Resultset of request

Returns: array Array

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ free()

Database::free ( $resultset = null )

Free last resultset used.

Parameters

resource $resultset Free cursor

Returns: void

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ getDefaultCharacterSetDatabase()

Database::getDefaultCharacterSetDatabase ( )

Return charset used to store data in database.

Returns: string Charset

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ getDefaultCollationDatabase()

Database::getDefaultCollationDatabase ( )

Return collation used in database.

Returns: string Collation value

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ getDriverInfo()

Database::getDriverInfo ( )

Return version of database client driver.

Returns: string Version string

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ getListOfCharacterSet()

Database::getListOfCharacterSet ( )

Return list of available charset that can be used to store data in database.

Returns: array List of Charset

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ getListOfCollation()

Database::getListOfCollation ( )

Return list of available collation that can be used for database.

Returns: array List of Collation

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ getPathOfDump()

Database::getPathOfDump ( )

Return full path of dump program.

Returns: string Full path of dump program

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ getPathOfRestore()

Database::getPathOfRestore ( )

Return full path of restore program.

Returns: string Full path of restore program

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ getServerParametersValues()

Database::getServerParametersValues ( $filter = '' )

Return value of server parameters.

Parameters

string $filter Filter list on a particular value

Returns: array Array of key-values (key=>value)

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ getServerStatusValues()

Database::getServerStatusValues ( $filter = '' )

Return value of server status.

Parameters

string $filter Filter list on a particular value

Returns: array Array of key-values (key=>value)

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ getVersion()

Database::getVersion ( )

Return version of database server.

Returns: string Version string

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

Referenced by DoliDB\getVersionArray().

◆ getVersionArray()

Database::getVersionArray ( )

Return version of database server into an array.

Returns: array Version array

Implemented in TraceableDB, and DoliDB.

◆ idate()

Database::idate ( $param )

Convert (by PHP) a GM Timestamp date into a string date with PHP server TZ to insert into a date field.

Function to use to build INSERT, UPDATE or WHERE predica

Parameters

int $param Date TMS to convert

Returns: string Date in a string YYYYMMDDHHMMSS

◆ ifsql()

Database::ifsql	(	$test,
		$resok,
		$resko
	)

Format a SQL IF.

Parameters

string	$test	Test string (example: 'cd.statut=0', 'field IS NULL')
string	$resok	result if test is equal
string	$resko	result if test is not equal

Returns: string SQL string

Implemented in TraceableDB, DoliDBPgsql, and DoliDB.

◆ jdate()

Database::jdate	(	$string,
		$gm = `false`
	)

Convert (by PHP) a PHP server TZ string date into a Timestamps date (GMT if gm=true) 19700101020000 -> 3600 with TZ+1 and gmt=0 19700101020000 -> 7200 whaterver is TZ if gmt=1.

Parameters

string	$string	Date in a string (YYYYMMDDHHMMSS, YYYYMMDD, YYYY-MM-DD HH:MM:SS)
bool	$gm	1=Input informations are GMT values, otherwise local to server TZ

Returns: int|string Date TMS or ''

Implemented in TraceableDB, and DoliDB.

◆ last_insert_id()

Database::last_insert_id	(	$tab,
		$fieldid = `'rowid'`
	)

Get last ID after an insert INSERT.

Parameters

string	$tab	Table name concerned by insert. Not used under MySql but required for compatibility with Postgresql
string	$fieldid	Field name

Returns: int Id of row

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ lasterrno()

Database::lasterrno ( )

Return last error code.

Returns: string lasterrno

Implemented in TraceableDB, and DoliDB.

◆ lasterror()

Database::lasterror ( )

Return last error label.

Returns: string lasterror

Implemented in TraceableDB, and DoliDB.

◆ lastquery()

Database::lastquery ( )

Return last request executed with query()

Returns: string Last query

Implemented in TraceableDB, and DoliDB.

◆ lastqueryerror()

Database::lastqueryerror ( )

Return last query in error.

Returns: string lastqueryerror

Implemented in TraceableDB, and DoliDB.

◆ num_rows()

Database::num_rows ( $resultset )

Return number of lines for result of a SELECT.

Parameters

resource $resultset Resulset of requests

Returns: int Nb of lines

See also: affected_rows()

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ order()

Database::order	(	$sortfield = `null`,
		$sortorder = `null`
	)

Define sort criteria of request.

Parameters

string	$sortfield	List of sort fields
string	$sortorder	Sort order

Returns: string String to provide syntax of a sort sql string

Implemented in TraceableDB, and DoliDB.

◆ plimit()

Database::plimit	(	$limit = `0`,
		$offset = `0`
	)

Define limits and offset of request.

Parameters

int	$limit	Maximum number of lines returned (-1=conf->liste_limit, 0=no limit)
int	$offset	Numero of line from where starting fetch

Returns: string String with SQL syntax to add a limit and offset

Implemented in TraceableDB, DoliDBPgsql, and DoliDB.

◆ query()

Database::query	(	$query,
		$usesavepoint = `0`,
		$type = `'auto'`,
		$result_mode = `0`
	)

Execute a SQL request and return the resultset.

Parameters

string	$query	SQL query string
int	$usesavepoint	0=Default mode, 1=Run a savepoint before and a rollback to savepoint if error (this allow to have some request with errors inside global transactions). Note that with Mysql, this parameter is not used as Myssql can already commit a transaction even if one request is in error, without using savepoints.
string	$type	Type of SQL order ('ddl' for insert, update, select, delete or 'dml' for create, alter...)
int	$result_mode	Result mode

Returns: bool|resource Resultset of answer or false

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ rollback()

Database::rollback ( $log = '' )

Canceling a transaction and returning to old values.

Parameters

string $log Add more log to default log line

Returns: int 1 if cancelation ok or transaction not open, 0 if error

Implemented in TraceableDB, and DoliDB.

◆ sanitize()

Database::sanitize ( $stringtosanitize )

Sanitize a string for SQL forging.

Parameters

string $stringtosanitize String to escape

Returns: string String escaped

◆ select_db()

Database::select_db ( $database )

Select a database.

Parameters

string $database Name of database

Returns: boolean true if OK, false if KO

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

The documentation for this interface was generated from the following file:

dolibarr_17.0/htdocs/core/db/Database.interface.php

Public Member Functions

Static Public Member Functions

Detailed Description

Member Function Documentation

◆ affected_rows()

◆ begin()

◆ close()

◆ commit()

◆ connect()

◆ convertSQLFromMysql()

◆ DDLAddField()

◆ DDLCreateDb()

◆ DDLCreateTable()

◆ DDLCreateUser()

◆ DDLDescTable()

◆ DDLDropField()

◆ DDLDropTable()

◆ DDLGetConnectId()

◆ DDLInfoTable()

◆ DDLListTables()

◆ DDLUpdateField()

◆ decrypt()

◆ encrypt()

◆ errno()

◆ error()

◆ escape()

◆ escapeforlike()

◆ escapeunderscore()

◆ fetch_array()

◆ fetch_object()

◆ fetch_row()

◆ free()

◆ getDefaultCharacterSetDatabase()

◆ getDefaultCollationDatabase()

◆ getDriverInfo()

◆ getListOfCharacterSet()

◆ getListOfCollation()

◆ getPathOfDump()

◆ getPathOfRestore()

◆ getServerParametersValues()

◆ getServerStatusValues()

◆ getVersion()

◆ getVersionArray()

◆ idate()

◆ ifsql()

◆ jdate()

◆ last_insert_id()

◆ lasterrno()

◆ lasterror()

◆ lastquery()

◆ lastqueryerror()

◆ num_rows()

◆ order()

◆ plimit()

◆ query()

◆ rollback()

◆ sanitize()

◆ select_db()