Database

interface Database (View source)

Class to manage Dolibarr database access for an SQL database

Methods

string
ifsql(string $test, string $resok, string $resko)

Format a SQL IF

string
stddevpop(string $nameoffield)

Return SQL string to aggregate using the Standard Deviation of population

array<string|int, mixed>|null|int<0, 0>
fetch_row(mysqli_result|resource|SQLite3Result $resultset)

Return data as an array

string
idate(int $param, "gmt"|"tzserver" $gm = 'tzserver')

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

string
lasterrno()

Return last error code

int
begin(string $textinlog = '')

Start transaction

bool|SQLite3Result|mysqli_result|resource
DDLCreateDb(string $database, string $charset = '', string $collation = '', string $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

string[]
getVersionArray()

Return version of database server into an array

string
convertSQLFromMysql(string $line, string $type = 'ddl')

Convert a SQL request in Mysql syntax to native syntax

int
affected_rows(mysqli_result|resource|SQLite3Result $resultset)

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

string
error()

Return description of last error

DDLListTables($database, string $table = '')

List tables into a database

DDLListTablesFull($database, string $table = '')

List tables into a database with table type

string
lastquery()

Return last request executed with query()

string
order(string $sortfield = '', string $sortorder = '')

Define sort criteria of request

string
decrypt(string $value)

Decrypt sensitive data in database

array<int|string, mixed>|null|false
fetch_array(mysqli_result|resource|SQLite3Result $resultset)

Return data as an array

string
lasterror()

Return last error label

string
escape(string $stringtoencode)

Escape a string to insert data

string
escapeforlike(string $stringtoencode)

Escape a string to insert data into a like.

string
sanitize(string $stringtosanitize)

Sanitize a string for SQL forging

int
last_insert_id(string $tab, string $fieldid = 'rowid')

Get last ID after an insert INSERT

string
getPathOfRestore()

Return full path of restore program

int
rollback(string $log = '')

Canceling a transaction and returning to old values

bool|mysqli_result|resource
query(string $query, int $usesavepoint = 0, string $type = 'auto', int $result_mode = 0)

Execute a SQL request and return the resultset

false|resource|mysqli|mysqliDoli|Connection|SQLite3
connect(string $host, string $login, string $passwd, string $name, int $port = 0)

Connection to server

string
plimit(int $limit = 0, int $offset = 0)

Define limits and offset of request

array<string, string>
getServerParametersValues(string $filter = '')

Return value of server parameters

array<string, string>
getServerStatusValues(string $filter = '')

Return value of server status

string
getDefaultCollationDatabase()

Return collation used in database

int
num_rows(mysqli_result|resource|SQLite3Result $resultset)

Return number of lines for result of a SELECT

string
getPathOfDump()

Return full path of dump program

string
getDriverInfo()

Return version of database client driver

string
errno()

Return generic error code of last operation.

int
DDLCreateTable(string $table, array<string, array{type: string, label?: string, enabled?: int<0, 2>|string, position?: int, notnull?: int, visible?: int<-2, 5>|string, alwayseditable?: int<0, 1>, noteditable?: int<0, 1>, default?: string, index?: int, foreignkey?: string, searchall?: int<0, 1>, isameasure?: int<0, 1>, css?: string, csslist?: string, help?: string, showoncombobox?: int<0, 2>, disabled?: int<0, 1>, arrayofkeyval?: array<int, string>, autofocusoncreate?: int<0, 1>, comment?: string, copytoclipboard?: int<1, 2>, validate?: int<0, 1>}> $fields, string $primary_key, string $type, ?array<string, mixed> $unique_keys = null, string[] $fulltext_keys = null, string[] $keys = null)

Create a table into database

int
DDLDropTable(string $table)

Drop a table into database

?array<int, array{charset: string, description: string}>
getListOfCharacterSet()

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

int
DDLAddField(string $table, string $field_name, array{type: string, label?: string, enabled?: int<0, 2>|string, position?: int, notnull?: int, visible?: int, noteditable?: int, default?: string, extra?: string, null?: 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, string $field_position = "")

Create a new field into table

int
DDLDropField(string $table, string $field_name)

Drop a field from table

int
DDLUpdateField(string $table, string $field_name, 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)

Update format of a field into a table

?array<int, array{collation: string}>
getListOfCollation()

Return list of available collation that can be used for database

bool|resource|mysqli_result|SQLite3Result
DDLDescTable(string $table, string $field = "")

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

string
getVersion()

Return version of database server

string
getDefaultCharacterSetDatabase()

Return charset used to store data in database

int
DDLCreateUser(string $dolibarr_main_db_host, string $dolibarr_main_db_user, string $dolibarr_main_db_pass, string $dolibarr_main_db_name)

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

array<array<string, mixed>>
DDLInfoTable(string $table)

List information of columns into a table.

int|""
jdate(string $string, bool $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 whatever is TZ if gmt=1

string
encrypt(string $fieldorvalue, int $withQuotes = 1)

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

int
commit(string $log = '')

Validate a database transaction

void
free(resource|mysqli_result|SQLite3Result $resultset = null)

Free last resultset used.

bool
close()

Close database connection

string
lastqueryerror()

Return last query in error

string
DDLGetConnectId()

Return connection ID

object|false
fetch_object(mysqli_result|resource|Connection|SQLite3Result $resultset)

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

bool
select_db(string $database)

Select a database

Details

at line 38
string ifsql(string $test, string $resok, string $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

Return Value

string

SQL string

at line 46
string stddevpop(string $nameoffield)

Return SQL string to aggregate using the Standard Deviation of population

Parameters

string $nameoffield

Name of field

Return Value

string

SQL string

at line 56
array<string|int, mixed>|null|int<0, 0> fetch_row(mysqli_result|resource|SQLite3Result $resultset)

Return data as an array

Parameters

mysqli_result|resource|SQLite3Result $resultset

Resultset of request

Return Value

array<string|int, mixed>|null|int<0, 0> Array

at line 67
string idate(int $param, "gmt"|"tzserver" $gm = 'tzserver')

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
"gmt"|"tzserver" $gm

'gmt'=Input information are GMT values, 'tzserver'=Local to server TZ

Return Value

string

Date in a string YYYYMMDDHHMMSS

at line 74
string lasterrno()

Return last error code

Return Value

string lasterrno

at line 82
int begin(string $textinlog = '')

Start transaction

Parameters

string $textinlog

Add a small text into log. '' by default.

Return Value

int

1 if transaction successfully opened or already opened, 0 if error

at line 96
bool|SQLite3Result|mysqli_result|resource DDLCreateDb(string $database, string $charset = '', string $collation = '', string $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

Return Value

bool|SQLite3Result|mysqli_result|resource

Resource result of the query to create database if OK, null if KO

at line 104
string[] getVersionArray()

Return version of database server into an array

Return Value

string[]

Version array

at line 113
string convertSQLFromMysql(string $line, string $type = 'ddl')

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...)

Return Value

string

SQL request line converted

at line 123
int affected_rows(mysqli_result|resource|SQLite3Result $resultset)

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

Parameters

mysqli_result|resource|SQLite3Result $resultset

Cursor of the desired request

Return Value

int

Number of lines

See also

num_rows()

at line 131
string error()

Return description of last error

Return Value

string

Error text

at line 141
DDLListTables($database, string $table = '')

List tables into a database

@param string $database Name of database

Parameters

$database
string $table

Name of table filter ('xxx%') @return string[] of tables in an array

at line 152
DDLListTablesFull($database, string $table = '')

List tables into a database with table type

@param string $database Name of database

Parameters

$database
string $table

Name of table filter ('xxx%') @return array<array{0:string,1:string}> List of tables in an array

at line 160
string lastquery()

Return last request executed with query()

Return Value

string

Last query

at line 169
string order(string $sortfield = '', string $sortorder = '')

Define sort criteria of request

Parameters

string $sortfield

List of sort fields
string $sortorder

Sort order

Return Value

string

String to provide syntax of a sort sql string

at line 177
string decrypt(string $value)

Decrypt sensitive data in database

Parameters

string $value

Value to decrypt

Return Value

string

Decrypted value if used

at line 186
array<int|string, mixed>|null|false fetch_array(mysqli_result|resource|SQLite3Result $resultset)

Return data as an array

Parameters

mysqli_result|resource|SQLite3Result $resultset

Resultset of request

Return Value

array<int|string, mixed>|null|false

Result with row

at line 194
string lasterror()

Return last error label

Return Value

string lasterror

at line 202
string escape(string $stringtoencode)

Escape a string to insert data

Parameters

string $stringtoencode

String to escape

Return Value

string

String escaped

at line 211
string escapeforlike(string $stringtoencode)

Escape a string to insert data into a like.

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

Parameters

string $stringtoencode

String to escape

Return Value

string

String escaped

at line 219
string sanitize(string $stringtosanitize)

Sanitize a string for SQL forging

Parameters

string $stringtosanitize

String to escape

Return Value

string

String escaped

at line 229
int last_insert_id(string $tab, string $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

Return Value

int

Id of row

at line 237
string getPathOfRestore()

Return full path of restore program

Return Value

string

Full path of restore program

at line 245
int rollback(string $log = '')

Canceling a transaction and returning to old values

Parameters

string $log

Add more log to default log line

Return Value

int

1 if cancellation ok or transaction not open, 0 if error

at line 257
bool|mysqli_result|resource query(string $query, int $usesavepoint = 0, string $type = 'auto', int $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

Return Value

bool|mysqli_result|resource

Resultset of answer or false

at line 270
false|resource|mysqli|mysqliDoli|Connection|SQLite3 connect(string $host, string $login, string $passwd, string $name, int $port = 0)

Connection 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

Return Value

false|resource|mysqli|mysqliDoli|Connection|SQLite3

Database access handler

See also

close()

at line 279
string plimit(int $limit = 0, int $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

Return Value

string

String with SQL syntax to add a limit and offset

at line 287
array<string, string> getServerParametersValues(string $filter = '')

Return value of server parameters

Parameters

string $filter

Filter list on a particular value

Return Value

array<string, string>

Array of key-values (key=>value)

at line 295
array<string, string> getServerStatusValues(string $filter = '')

Return value of server status

Parameters

string $filter

Filter list on a particular value

Return Value

array<string, string>

Array of key-values (key=>value)

at line 302
string getDefaultCollationDatabase()

Return collation used in database

Return Value

string

Collation value

at line 312
int num_rows(mysqli_result|resource|SQLite3Result $resultset)

Return number of lines for result of a SELECT

Parameters

mysqli_result|resource|SQLite3Result $resultset

Resulset of requests

Return Value

int

Nb of lines

See also

affected_rows()

at line 320
string getPathOfDump()

Return full path of dump program

Return Value

string

Full path of dump program

at line 327
string getDriverInfo()

Return version of database client driver

Return Value

string

Version string

at line 334
string errno()

Return generic error code of last operation.

Return Value

string

Error code (Examples: DB_ERROR_TABLE_ALREADY_EXISTS, DB_ERROR_RECORD_ALREADY_EXISTS...)

at line 349
int DDLCreateTable(string $table, array<string, array{type: string, label?: string, enabled?: int<0, 2>|string, position?: int, notnull?: int, visible?: int<-2, 5>|string, alwayseditable?: int<0, 1>, noteditable?: int<0, 1>, default?: string, index?: int, foreignkey?: string, searchall?: int<0, 1>, isameasure?: int<0, 1>, css?: string, csslist?: string, help?: string, showoncombobox?: int<0, 2>, disabled?: int<0, 1>, arrayofkeyval?: array<int, string>, autofocusoncreate?: int<0, 1>, comment?: string, copytoclipboard?: int<1, 2>, validate?: int<0, 1>}> $fields, string $primary_key, string $type, ?array<string, mixed> $unique_keys = null, string[] $fulltext_keys = null, string[] $keys = null)

Create a table into database

Parameters

string $table

Name of table
array<string, array{type: string, label?: string, enabled?: int<0, 2>|string, position?: int, notnull?: int, visible?: int<-2, 5>|string, alwayseditable?: int<0, 1>, noteditable?: int<0, 1>, default?: string, index?: int, foreignkey?: string, searchall?: int<0, 1>, isameasure?: int<0, 1>, css?: string, csslist?: string, help?: string, showoncombobox?: int<0, 2>, disabled?: int<0, 1>, arrayofkeyval?: array<int, string>, autofocusoncreate?: int<0, 1>, comment?: string, copytoclipboard?: int<1, 2>, validate?: int<0, 1>}> $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<string, mixed> $unique_keys

Associative array Name of fields that will be unique key => value
string[] $fulltext_keys

Field name table that will be indexed in fulltext
string[] $keys

Table of key fields names => value

Return Value

int

Return integer <0 if KO, >=0 if OK

at line 359
int DDLDropTable(string $table)

Drop a table into database

Parameters

string $table

Name of table

Return Value

int

Return integer <0 if KO, >=0 if OK

at line 367
?array<int, array{charset: string, description: string}> getListOfCharacterSet()

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

Return Value

?array<int, array{charset: string, description: string}>

List of Charset

at line 379
int DDLAddField(string $table, string $field_name, array{type: string, label?: string, enabled?: int<0, 2>|string, position?: int, notnull?: int, visible?: int, noteditable?: int, default?: string, extra?: string, null?: 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, string $field_position = "")

Create a new field into table

Parameters

string $table

Name of table
string $field_name

Name of field to add
array{type: string, label?: string, enabled?: int<0, 2>|string, position?: int, notnull?: int, visible?: int, noteditable?: int, default?: string, extra?: string, null?: 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_position

Optional ex .: "after field stuff"

Return Value

int

Return integer <0 if KO, >0 if OK

at line 390
int DDLDropField(string $table, string $field_name)

Drop a field from table

Parameters

string $table

Name of table
string $field_name

Name of field to drop

Return Value

int

Return integer <0 if KO, >0 if OK

at line 402
int DDLUpdateField(string $table, string $field_name, 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)

Update format of a field into a table

Parameters

string $table

Name of table
string $field_name

Name 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

Return Value

int

Return integer <0 if KO, >0 if OK

at line 410
?array<int, array{collation: string}> getListOfCollation()

Return list of available collation that can be used for database

Return Value

?array<int, array{collation: string}>

List of Collation

at line 420
bool|resource|mysqli_result|SQLite3Result DDLDescTable(string $table, string $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

Return Value

bool|resource|mysqli_result|SQLite3Result Resource

at line 428
string getVersion()

Return version of database server

Return Value

string

Version string

at line 435
string getDefaultCharacterSetDatabase()

Return charset used to store data in database

Return Value

string Charset

at line 447
int DDLCreateUser(string $dolibarr_main_db_host, string $dolibarr_main_db_user, string $dolibarr_main_db_pass, string $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

Return Value

int

Return integer <0 if KO, >=0 if OK

at line 462
array<array<string, mixed>> DDLInfoTable(string $table)

List information of columns into a table.

Parameters

string $table

Name of table

Return Value

array<array<string, mixed>>

Array with information on table

at line 474
int|"" jdate(string $string, bool $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 whatever is TZ if gmt=1

Parameters

string $string

Date in a string (YYYYMMDDHHMMSS, YYYYMMDD, YYYY-MM-DD HH:MM:SS)
bool $gm

1=Input information are GMT values, otherwise local to server TZ

Return Value

int|""

Date TMS or ''

at line 484
string encrypt(string $fieldorvalue, int $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).

Return Value

string

XXX(field) or XXX('value') or field or 'value'

at line 492
int commit(string $log = '')

Validate a database transaction

Parameters

string $log

Add more log to default log line

Return Value

int

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

at line 500
void free(resource|mysqli_result|SQLite3Result $resultset = null)

Free last resultset used.

Parameters

resource|mysqli_result|SQLite3Result $resultset

Free cursor

Return Value

void

at line 508
bool close()

Close database connection

Return Value

bool

True if disconnect successful, false otherwise

See also

connect()

at line 515
string lastqueryerror()

Return last query in error

Return Value

string lastqueryerror

at line 523
string DDLGetConnectId()

Return connection ID

Return Value

string

Id connection

at line 533
object|false fetch_object(mysqli_result|resource|Connection|SQLite3Result $resultset)

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

Parameters

mysqli_result|resource|Connection|SQLite3Result $resultset

Handler of the desired request

Return Value

object|false

Object result line or false if KO or end of cursor

at line 543
bool select_db(string $database)

Select a database

Parameters

string $database

Name of database

Return Value

bool

true if OK, false if KO