Jelix 1.6.40

sqlsrvDbTools extends jDbTools
in package

Provides utilities methods for a database

Table of Contents

IBD_EMPTY_TABLE_BEFORE  = 1
IBD_IGNORE_IF_EXIST  = 3
IBD_INSERT_ONLY_IF_TABLE_IS_EMPTY  = 2
IBD_NO_CHECK  = 0
IBD_UPDATE_IF_EXIST  = 4
$falseValue  : mixed
$trueValue  : mixed
$_conn  : jDbConnection
the database connector
$dbmsStyle  : mixed
regular expression to detect comments and end of query
$functionNameCorrespondence  : mixed
$keywordNameCorrespondence  : mixed
$typesInfo  : mixed
$unifiedToPhp  : mixed
__construct()  : mixed
encloseName()  : string
Enclose the field name
escapeValue()  : string
execSQLScript()  : mixed
getBooleanValue()  : string
getFieldList()  : array<string|int, jDbFieldProperties>
retrieve the list of fields of a table
getNativeSQLFunction()  : string
Give the expression that works with the target database, corresponding to the given function name
getSQLColumnsList()  : string
getTableList()  : array<string|int, string>
returns the list of tables
getTypeInfo()  : array<string|int, mixed>
Get informations about the given SQL type
insertBulkData()  : int
Insert several records into a table
parseCREATETABLE()  : array<string|int, mixed>|bool
Parse a SQL CREATE TABLE statement and returns all of its components separately.
parseSQLFunctionAndConvert()  : mixed
parseSQLType()  : array<string|int, mixed>
Parse a SQL type and gives type, length.
stringToPhpValue()  : string
unifiedToPHPType()  : string
Return the PHP type corresponding to the given unified type
extractDateConverter()  : mixed

Constants

IBD_INSERT_ONLY_IF_TABLE_IS_EMPTY

public mixed IBD_INSERT_ONLY_IF_TABLE_IS_EMPTY = 2

Properties

$dbmsStyle

regular expression to detect comments and end of query

protected mixed $dbmsStyle = array('/^\s*(#|\-\- )/', '/;\s*$/')

$functionNameCorrespondence

protected mixed $functionNameCorrespondence = array( // sqlsrv, -> date+time //'sysdatetime' => '', // sqlsrv, -> date+time+offset //'sysdatetimeoffset' => '', // sqlsrv, -> date+time at utc //'sysutcdatetime' => '', // sqlsrv -> date+time //'getdate' => '', // sqlsrv -> date+time at utc //'getutcdate' => '', // sqlsrv,mysql (datetime)-> integer //'day' => '', // sqlsrv,mysql (datetime)-> integer //'month' => '', // sqlsrv, mysql (datetime)-> integer //'year' => '', // mysql -> date 'curdate' => 'DATEFROMPARTS(DATEPART(year,GETDATE()),DATEPART(month,GETDATE()),DATEPART(day,GETDATE()))', // mysql -> date 'current_date' => 'DATEFROMPARTS(DATEPART(year,GETDATE()),DATEPART(month,GETDATE()),DATEPART(day,GETDATE()))', // mysql -> time 'curtime' => 'TIMEFROMPARTS(DATEPART(hour,GETDATE()),DATEPART(minute,GETDATE()),DATEPART(second,GETDATE()),0,0)', // mysql -> time 'current_time' => 'TIMEFROMPARTS(DATEPART(hour,GETDATE()),DATEPART(minute,GETDATE()),DATEPART(second,GETDATE()),0,0)', // mysql,pgsql -> date+time 'now' => 'GETDATE()', // mysql date+time 'current_timestamp' => 'GETDATE()', // mysql (datetime)->date, sqlite (timestring, modifier)->date 'date' => 'DATEFROMPARTS(DATEPART(year,%!p),DATEPART(month,%!p),DATEPART(day,%!p))', // mysql = day() 'dayofmonth' => 'day(%!p)', // mysql -> date+time 'localtime' => 'GETDATE()', // mysql -> date+time 'localtimestamp' => 'GETDATE()', // mysql utc current date 'utc_date' => 'DATEFROMPARTS(DATEPART(year,GETUTCDATE()),DATEPART(month,GETUTCDATE()),DATEPART(day,GETUTCDATE()))', // mysql utc current time 'utc_time' => 'TIMEFROMPARTS(DATEPART(hour,GETUTCDATE()),DATEPART(minute,GETUTCDATE()),DATEPART(second,GETUTCDATE()),0,0)', // mysql utc current date+time 'utc_timestamp' => 'GETUTCDATE()', // mysql (datetime)->time, , sqlite (timestring, modifier)->time 'time' => 'TIMEFROMPARTS(DATEPART(hour,%!p),DATEPART(minute,%!p),DATEPART(second,%!p),0,0)', // mysql (datetime/time)-> hour 'hour' => 'DATEPART(hour,GETDATE())', // mysql (datetime/time)-> minute 'minute' => 'DATEPART(minute,GETDATE())', // mysql (datetime/time)-> second 'second' => 'DATEPART(second,GETDATE())', // sqlite (timestring, modifier)->datetime //'datetime' => '!sqliteDateTime', // oci, mysql (year|month|day|hour|minute|second FROM <datetime>)->value , // pgsql (year|month|day|hour|minute|second <datetime>)->value 'extract' => '!extractDateConverter', // pgsql ('year'|'month'|'day'|'hour'|'minute'|'second', <datetime>)->value 'date_part' => '!extractDateConverter', )

$keywordNameCorrespondence

protected mixed $keywordNameCorrespondence = array( // sqlsrv,mysql,oci,pgsql -> date+time //'current_timestamp' => '', // mysql,oci,pgsql -> date 'current_date' => 'DATEFROMPARTS(DATEPART(year,GETDATE()),DATEPART(month,GETDATE()),DATEPART(day,GETDATE()))', // mysql -> time, pgsql -> time+timezone 'current_time' => 'TIMEFROMPARTS(DATEPART(hour,GETDATE()),DATEPART(minute,GETDATE()),DATEPART(second,GETDATE()),0,0)', // oci -> date+fractional secon + timezone 'systimestamp' => 'GETDATE()', // oci -> date+time+tz 'sysdate' => 'GETDATE()', // pgsql -> time 'localtime' => 'TIMEFROMPARTS(DATEPART(hour,GETDATE()),DATEPART(minute,GETDATE()),DATEPART(second,GETDATE()),0,0)', // pgsql -> date+time 'localtimestamp' => 'GETDATE()', )

$typesInfo

protected mixed $typesInfo = array( // type native type unified type minvalue maxvalue minlength maxlength 'bool' => array('tinyint', 'boolean', 0, 1, ull, ull), 'boolean' => array('tinyint', 'boolean', 0, 1, ull, ull), 'bit' => array('bit', 'integer', 0, 1, ull, ull), 'tinyint' => array('tinyint', 'integer', 0, 255, ull, ull), 'smallint' => array('smallint', 'integer', -32768, 32767, ull, ull), 'mediumint' => array('int', 'integer', -8388608, 8388607, ull, ull), 'integer' => array('int', 'integer', -2147483648, 2147483647, ull, ull), 'int' => array('int', 'integer', -2147483648, 2147483647, ull, ull), 'bigint' => array('bigint', 'numeric', '-9223372036854775808', '9223372036854775807', ull, ull), 'serial' => array('int', 'integer', -2147483648, 2147483647, ull, ull), 'bigserial' => array('bigint', 'numeric', '-9223372036854775808', '9223372036854775807', ull, ull), 'autoincrement' => array('int', 'integer', -2147483648, 2147483647, ull, ull), // for old dao files 'bigautoincrement' => array('bigint', 'numeric', '-9223372036854775808', '9223372036854775807', ull, ull), // for old dao files 'float' => array('float', 'float', ull, ull, ull, ull), //4bytes 'money' => array('money', 'float', ull, ull, ull, ull), //8bytes 'smallmoney' => array('smallmoney', 'float', ull, ull, ull, ull), //4bytes 'double precision' => array('real', 'float', ull, ull, ull, ull), //8bytes 'double' => array('real', 'float', ull, ull, ull, ull), //8bytes 'real' => array('real', 'float', ull, ull, ull, ull), //8bytes 'number' => array('real', 'decimal', ull, ull, ull, ull), //8bytes 'binary_float' => array('real', 'float', ull, ull, ull, ull), //4bytes 'binary_double' => array('real', 'decimal', ull, ull, ull, ull), //8bytes 'numeric' => array('numeric', 'decimal', ull, ull, ull, ull), 'decimal' => array('decimal', 'decimal', ull, ull, ull, ull), 'dec' => array('decimal', 'decimal', ull, ull, ull, ull), 'date' => array('date', 'date', ull, ull, 10, 10), 'time' => array('time', 'time', ull, ull, 8, 16), //23:59:59.9999999 'datetime' => array('datetime', 'datetime', ull, ull, 19, 23), // 9999-12-31 23:59:59.997 'datetime2' => array('datetime2', 'datetime', ull, ull, 19, 27), // 9999-12-31 23:59:59.9999999 'datetimeoffset' => array('datetimeoffset', 'datetime', ull, ull, 19, 34), // 9999-12-31 23:59:59.9999999 +14:00 'smalldatetime' => array('smalldatetime', 'datetime', ull, ull, 19, 19), // 2079-06-06 23:59 'timestamp' => array('datetime', 'datetime', ull, ull, 19, 19), // oracle/pgsql timestamp 'utimestamp' => array('integer', 'integer', 0, 2147483647, ull, ull), // mysql timestamp 'year' => array('integer', 'year', ull, ull, 2, 4), 'interval' => array('datetime', 'datetime', ull, ull, 19, 19), 'char' => array('char', 'char', ull, ull, 0, 0), 'nchar' => array('nchar', 'char', ull, ull, 0, 0), 'varchar' => array('varchar', 'varchar', ull, ull, 0, 0), 'varchar2' => array('varchar', 'varchar', ull, ull, 0, 0), 'nvarchar' => array('nvarchar', 'varchar', ull, ull, 0, 0), 'nvarchar2' => array('nvarchar', 'varchar', ull, ull, 0, 0), 'character' => array('varchar', 'varchar', ull, ull, 0, 0), 'character varying' => array('varchar', 'varchar', ull, ull, 0, 0), 'name' => array('varchar', 'varchar', ull, ull, 0, 64), 'longvarchar' => array('varchar', 'varchar', ull, ull, 0, 0), 'string' => array('varchar', 'varchar', ull, ull, 0, 0), // for old dao files 'tinytext' => array('text', 'text', ull, ull, 0, 255), 'text' => array('text', 'text', ull, ull, 0, 0), 'ntext' => array('ntext', 'text', ull, ull, 0, 0), 'mediumtext' => array('text', 'text', ull, ull, 0, 0), 'longtext' => array('text', 'text', ull, ull, 0, 0), 'long' => array('text', 'text', ull, ull, 0, 0), 'clob' => array('text', 'text', ull, ull, 0, 0), 'nclob' => array('text', 'text', ull, ull, 0, 0), 'tinyblob' => array('varbinary', 'blob', ull, ull, 0, 255), 'blob' => array('varbinary', 'blob', ull, ull, 0, 65535), 'mediumblob' => array('varbinary', 'blob', ull, ull, 0, 16777215), 'longblob' => array('varbinary', 'blob', ull, ull, 0, 0), 'bfile' => array('varbinary', 'blob', ull, ull, 0, 0), 'bytea' => array('varbinary', 'varbinary', ull, ull, 0, 0), 'binary' => array('binary', 'binary', ull, ull, 0, 8000), 'varbinary' => array('varbinary', 'varbinary', ull, ull, 0, 8000), 'raw' => array('varbinary', 'varbinary', ull, ull, 0, 2000), 'long raw' => array('varbinary', 'varbinary', ull, ull, 0, 0), 'image' => array('image', 'varbinary', ull, ull, 0, 0), 'enum' => array('varchar', 'varchar', ull, ull, 0, 65535), 'set' => array('varchar', 'varchar', ull, ull, 0, 65535), 'xmltype' => array('varchar', 'varchar', ull, ull, 0, 65535), 'xml' => array('xml', 'text', ull, ull, 0, 0), 'point' => array('varchar', 'varchar', ull, ull, 0, 16), 'line' => array('varchar', 'varchar', ull, ull, 0, 32), 'lsed' => array('varchar', 'varchar', ull, ull, 0, 32), 'box' => array('varchar', 'varchar', ull, ull, 0, 32), 'path' => array('varchar', 'varchar', ull, ull, 0, 65535), 'polygon' => array('varchar', 'varchar', ull, ull, 0, 65535), 'circle' => array('varchar', 'varchar', ull, ull, 0, 24), 'cidr' => array('varchar', 'varchar', ull, ull, 0, 24), 'inet' => array('varchar', 'varchar', ull, ull, 0, 24), 'macaddr' => array('integer', 'integer', 0, 0xffffffffffff, ull, ull), 'bit varying' => array('varchar', 'varchar', ull, ull, 0, 65535), 'arrays' => array('varchar', 'varchar', ull, ull, 0, 65535), 'complex types' => array('varchar', 'varchar', ull, ull, 0, 65535), )

$unifiedToPhp

protected mixed $unifiedToPhp = array('boolean' => 'boolean', 'integer' => 'integer', 'float' => 'float', 'double' => 'float', 'numeric' => 'numeric', 'decimal' => 'decimal', 'date' => 'string', 'time' => 'string', 'datetime' => 'string', 'year' => 'string', 'char' => 'string', 'varchar' => 'string', 'text' => 'string', 'blob' => 'string', 'binary' => 'string', 'varbinary' => 'string')

Methods

encloseName()

Enclose the field name

public encloseName(mixed $fieldName) : string
Parameters
$fieldName : mixed

the field name

Tags
inheritdoc
Return values
string

the enclosed field name

escapeValue()

public escapeValue(string $unifiedType, mixed $value[, mixed $checkNull = false ][, mixed $toPhpSource = false ]) : string
Parameters
$unifiedType : string

the unified type name

$value : mixed

the value

$checkNull : mixed = false
$toPhpSource : mixed = false
Tags
since
1.2
Return values
string

the value which is ready to include a SQL query string

execSQLScript()

public execSQLScript(mixed $file) : mixed
Parameters
$file : mixed
Return values
mixed

getBooleanValue()

public getBooleanValue(string|bool $value) : string
Parameters
$value : string|bool

a value which is a boolean

Tags
since
1.2
Return values
string

the string value representing a boolean in SQL

getFieldList()

retrieve the list of fields of a table

public getFieldList(string $tableName[, string $sequence = '' ][, string $schemaName = '' ]) : array<string|int, jDbFieldProperties>
Parameters
$tableName : string

the name of the table

$sequence : string = ''

the sequence used to auto increment the primary key (not supported here)

$schemaName : string = ''

the name of the schema (only for PostgreSQL, not supported here)

Return values
array<string|int, jDbFieldProperties>

keys are field names and values are jDbFieldProperties objects

getNativeSQLFunction()

Give the expression that works with the target database, corresponding to the given function name

public getNativeSQLFunction(string $name[, string|null $parametersString = null ]) : string
Parameters
$name : string

a SQL function, maybe a SQL function of another database type

$parametersString : string|null = null

parameters given to the function. Null if no parenthesis

Return values
string

the SQL expression, possibly with a native SQL function corresponding to the given foreign SQL function

getSQLColumnsList()

public getSQLColumnsList(array<string|int, string> $columns) : string
Parameters
$columns : array<string|int, string>

list of column names

Tags
since
1.6.16
Return values
string

the list in SQL

getTableList()

returns the list of tables

public getTableList() : array<string|int, string>
Tags
throws
jException
Return values
array<string|int, string>

list of table names

getTypeInfo()

Get informations about the given SQL type

public getTypeInfo(string $nativeType) : array<string|int, mixed>
Parameters
$nativeType : string

the SQL type

Tags
since
1.2
Return values
array<string|int, mixed>

an array which contains characteristics of the type array ( 'nativetype', 'corresponding unifiedtype', minvalue, maxvalue, minlength, maxlength, autoincrement) minvalue, maxvalue, minlength, maxlength can be null.

insertBulkData()

Insert several records into a table

public insertBulkData(string $tableName, array<string|int, string> $columns, array<string|int, array<string|int, mixed>> $data[, string|array<string|int, string>|null $primaryKey = null ], int $options) : int
Parameters
$tableName : string
$columns : array<string|int, string>

the column names in which data will be inserted

$data : array<string|int, array<string|int, mixed>>

the data. each row is an array of values. Values are in the same order as $columns

$primaryKey : string|array<string|int, string>|null = null

the column names that are the primary key. Don't give the primary key if it is an autoincrement field, or if option is not IBD_*_IF_EXIST

$options : int

one of IDB_* const

Tags
since
1.6.16
Return values
int

number of records inserted/updated

parseCREATETABLE()

Parse a SQL CREATE TABLE statement and returns all of its components separately.

public parseCREATETABLE( $createTableStatement) : array<string|int, mixed>|bool
Parameters
$createTableStatement :
Tags
since
1.6.16
Return values
array<string|int, mixed>|bool

false if parsing has failed. Else an array : 'name' => the schema/table name, 'temporary'=> true if there is the temporary keywork , 'ifnotexists' => true if there is the IF NOT EXISTS statement, 'columns' => list of columns definitions, 'constraints' => list of table constraints definitions, 'options' => all options at the end of the CREATE TABLE statement.

parseSQLFunctionAndConvert()

public parseSQLFunctionAndConvert(mixed $expression) : mixed
Parameters
$expression : mixed
Return values
mixed

parseSQLType()

Parse a SQL type and gives type, length.

public parseSQLType(string $type) : array<string|int, mixed>

..

Parameters
$type : string
Return values
array<string|int, mixed>

[$realtype, $length, $precision, $scale, $otherTypeDef]

stringToPhpValue()

public stringToPhpValue(string $unifiedType, string $value[, mixed $checkNull = false ]) : string
Parameters
$unifiedType : string

the unified type name

$value : string

the value

$checkNull : mixed = false
Tags
since
1.2
Return values
string

the php value corresponding to the type

unifiedToPHPType()

Return the PHP type corresponding to the given unified type

public unifiedToPHPType(string $unifiedType) : string
Parameters
$unifiedType : string
Tags
throws
Exception
since
1.2
Return values
string

the php type

extractDateConverter()

protected extractDateConverter(mixed $parametersString) : mixed
Parameters
$parametersString : mixed
Return values
mixed

Search results