dolibarr  20.0.0-alpha
Public Member Functions | List of all members
Database Interface Reference

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

Inheritance diagram for Database:
Inheritance graph
[legend]

Public Member Functions

 ifsql ($test, $resok, $resko)
 Format a SQL IF. More...
 
 stddevpop ($nameoffield)
 Return SQL string to aggregate using the Standard Deviation of population. More...
 
 fetch_row ($resultset)
 Return datas as an array @TODO deprecate this. 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...
 
 convertSQLFromMysql ($line, $type='ddl')
 Convert a SQL request in Mysql syntax to native syntax. 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...
 
 DDLListTablesFull ($database, $table='')
 List tables into a database with table type. More...
 
 lastquery ()
 Return last request executed with query() More...
 
 order ($sortfield='', $sortorder='')
 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...
 
 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)
 Connection 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...
 
 DDLInfoTable ($table)
 List information of columns into a table. 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...
 
 free ($resultset=null)
 Free last resultset used. More...
 
 close ()
 Close database connection. More...
 
 lastqueryerror ()
 Return last query in error. More...
 
 DDLGetConnectId ()
 Return connection ID. More...
 
 fetch_object ($resultset)
 Returns the current line (as an object) for the resultset cursor. More...
 
 select_db ($database)
 Select a database. More...
 

Detailed Description

Class to manage Dolibarr database access for an SQL database.

Definition at line 28 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
mysqli_result | resource | SQLite3Result$resultsetCursor 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$textinlogAdd a small text into log. '' by default.
Returns
int 1 if transaction successfully opened or already opened, 0 if error

Implemented in TraceableDB, and DoliDB.

◆ close()

Database::close ( )

Close database connection.

Returns
boolean True if disconnect successful, false otherwise
See also
connect()

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ commit()

Database::commit (   $log = '')

Validate a database transaction.

Parameters
string$logAdd 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 
)

Connection to server.

Parameters
string$hostDatabase server host
string$loginLogin
string$passwdPassword
string$nameName of database (not used for mysql, used for pgsql)
int$portPort of database server
Returns
false|resource|mysqli|mysqliDoli|PgSql\Connection|SQLite3 Database access handler
See also
close()

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ convertSQLFromMysql()

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

Convert a SQL request in Mysql syntax to native syntax.

Parameters
string$lineSQL request line to convert
string$typeType 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$tableName of table
string$field_nameName of field to add
array{type:string,label:string,enabled:int<0,2>|string,position:int,notnull?:int,visible:int,noteditable?:int,default?:string,index?:int,foreignkey?:string,searchall?:int,isameasure?:int,css?:string,csslist?:string,help?:string,showoncombobox?:int,disabled?:int,arrayofkeyval?:array<int,string>,comment?:string}$field_desc Associative array of description of the field to insert [parameter name][parameter value]
string$field_positionOptional ex .: "after field stuff"
Returns
int Return integer <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$databaseDatabase name to create
string$charsetCharset used to store data
string$collationCharset used to sort data
string$ownerUsername of database owner
Returns
bool|SQLite3Result|mysqli_result|resource Resource result of the query to create database 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$tableName of table
array<string,array{type:string,label:string,enabled:int<0,2>|string,position:int,notnull?:int,visible:int,noteditable?:int,default?:string,index?:int,foreignkey?:string,searchall?:int,isameasure?:int,css?:string,csslist?:string,help?:string,showoncombobox?:int,disabled?:int,arrayofkeyval?:array<int,string>,comment?:string}>$fields Associative table [field name][table of descriptions]
string$primary_keyName of the field that will be the primary key
string$typeType of the table
array$unique_keysAssociative array Name of fields that will be unique key => value
array$fulltext_keysField name table that will be indexed in fulltext
array$keysTable of key fields names => value
Returns
int Return integer <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_hostServer IP
string$dolibarr_main_db_userUsername to create
string$dolibarr_main_db_passUser password to create
string$dolibarr_main_db_nameDatabase name where user must be granted
Returns
int Return integer <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$tableName of table
string$fieldOptional : Name of field if we want description of field
Returns
bool|resource|mysqli_result|SQLite3Result Resource

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ DDLDropField()

Database::DDLDropField (   $table,
  $field_name 
)

Drop a field from table.

Parameters
string$tableName of table
string$field_nameName of field to drop
Returns
int Return integer <0 if KO, >0 if OK

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ DDLDropTable()

Database::DDLDropTable (   $table)

Drop a table into database.

Parameters
string$tableName of table
Returns
int Return integer <0 if KO, >=0 if OK

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ DDLGetConnectId()

Database::DDLGetConnectId ( )

Return connection ID.

Returns
string Id connection

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ DDLInfoTable()

Database::DDLInfoTable (   $table)

List information of columns into a table.

Parameters
string$tableName 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$databaseName of database
string$tableName of table filter ('xxx')
Returns
array List of tables in an array

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ DDLListTablesFull()

Database::DDLListTablesFull (   $database,
  $table = '' 
)

List tables into a database with table type.

Parameters
string$databaseName of database
string$tableName 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$tableName of table
string$field_nameName of field to modify
array{type:string,label:string,enabled:int<0,2>|string,position:int,notnull?:int,visible:int,noteditable?:int,default?:string,index?:int,foreignkey?:string,searchall?:int,isameasure?:int,css?:string,csslist?:string,help?:string,showoncombobox?:int,disabled?:int,arrayofkeyval?:array<int,string>,comment?:string}$field_desc Array with description of field format
Returns
int Return integer <0 if KO, >0 if OK

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ decrypt()

Database::decrypt (   $value)

Decrypt sensitive data in database.

Parameters
string$valueValue 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$fieldorvalueField name or value to encrypt
int$withQuotesReturn 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 (Examples: 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$stringtoencodeString to escape
Returns
string String escaped

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

Referenced by DoliDB\regexpsql().

◆ escapeforlike()

Database::escapeforlike (   $stringtoencode)

Escape a string to insert data into a like.

Can be used this way: LIKE '%".dbhandler->escape(dbhandler->escapeforlike(...))."'

Parameters
string$stringtoencodeString to escape
Returns
string String escaped

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ fetch_array()

Database::fetch_array (   $resultset)

Return datas as an array.

Parameters
mysqli_result | resource | SQLite3Result$resultsetResultset of request
Returns
array|null|false Result with row

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
mysqli_result | resource | PgSql\Connection | SQLite3Result$resultsetHandler of the desired request
Returns
Object|false 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 @TODO deprecate this.

Use fetch_object() so you can access a field with its name instead of using an index of position of field.

Parameters
mysqli_result | resource | SQLite3Result$resultsetResultset of request
Returns
array Array

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ free()

Database::free (   $resultset = null)

Free last resultset used.

Parameters
resource | mysqli_result | SQLite3Result$resultsetFree 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$filterFilter 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$filterFilter 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$paramDate TMS to convert
Returns
string Date in a string YYYYMMDDHHMMSS

◆ ifsql()

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

Format a SQL IF.

Parameters
string$testTest string (example: 'cd.statut=0', 'field IS NULL')
string$resokresult if test is equal
string$reskoresult 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$stringDate in a string (YYYYMMDDHHMMSS, YYYYMMDD, YYYY-MM-DD HH:MM:SS)
bool$gm1=Input information are GMT values, otherwise local to server TZ
Returns
int|'' 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$tabTable name concerned by insert. Not used under MySql but required for compatibility with Postgresql
string$fieldidField 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
mysqli_result | resource | SQLite3Result$resultsetResulset of requests
Returns
int Nb of lines
See also
affected_rows()

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ order()

Database::order (   $sortfield = '',
  $sortorder = '' 
)

Define sort criteria of request.

Parameters
string$sortfieldList of sort fields
string$sortorderSort 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$limitMaximum number of lines returned (-1=conf->liste_limit, 0=no limit)
int$offsetNumero 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$querySQL query string
int$usesavepoint0=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$typeType of SQL order ('ddl' for insert, update, select, delete or 'dml' for create, alter...)
int$result_modeResult mode
Returns
bool|mysqli_result|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$logAdd more log to default log line
Returns
int 1 if cancellation 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$stringtosanitizeString to escape
Returns
string String escaped

◆ select_db()

Database::select_db (   $database)

Select a database.

Parameters
string$databaseName of database
Returns
boolean true if OK, false if KO

Implemented in TraceableDB, DoliDBSqlite3, DoliDBPgsql, and DoliDBMysqli.

◆ stddevpop()

Database::stddevpop (   $nameoffield)

Return SQL string to aggregate using the Standard Deviation of population.

Parameters
string$nameoffieldName of field
Returns
string SQL string

Implemented in DoliDB.


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