Jelix 1.6.40

sqlsrvDbTools extends jDbTools
in package

jelix

Provides utilities methods for a database

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

IBD_EMPTY_TABLE_BEFORE


    public
    mixed
    IBD_EMPTY_TABLE_BEFORE
    = 1

IBD_IGNORE_IF_EXIST


    public
    mixed
    IBD_IGNORE_IF_EXIST
    = 3

IBD_INSERT_ONLY_IF_TABLE_IS_EMPTY


    public
    mixed
    IBD_INSERT_ONLY_IF_TABLE_IS_EMPTY
    = 2

IBD_NO_CHECK


    public
    mixed
    IBD_NO_CHECK
    = 0

IBD_UPDATE_IF_EXIST


    public
    mixed
    IBD_UPDATE_IF_EXIST
    = 4

$falseValue


    public
        mixed
    $falseValue
     = '0'

$trueValue


    public
        mixed
    $trueValue
     = '1'

$_conn

the database connector


    protected
        jDbConnection
    $_conn

$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')

__construct()


    public
                __construct([jDbConnection $connector = null ]) : mixed

Parameters

$connector : jDbConnection = null: the connection to a database

Return values

mixed —

encloseName()

Enclose the field name


    public
                encloseName(mixed $fieldName) : string

Parameters

$fieldName : mixed: the field name

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

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

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

Return values

string —

the list in SQL

getTableList()

returns the list of tables


    public
                getTableList() : array<string|int, string>

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

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

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 :

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

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

Return values

string —

the php type

extractDateConverter()


    protected
                extractDateConverter(mixed $parametersString) : mixed

Parameters

$parametersString : mixed

Return values

mixed —

sqlsrvDbTools extends jDbTools in package jelix

Table of Contents

Constants

IBD_EMPTY_TABLE_BEFORE

IBD_IGNORE_IF_EXIST

IBD_INSERT_ONLY_IF_TABLE_IS_EMPTY

IBD_NO_CHECK

IBD_UPDATE_IF_EXIST

Properties

$falseValue

$trueValue

$_conn

$dbmsStyle

$functionNameCorrespondence

$keywordNameCorrespondence

$typesInfo

$unifiedToPhp

Methods

__construct()

Parameters

Return values

encloseName()

Parameters

Tags

Return values

escapeValue()

Parameters

Tags

Return values

execSQLScript()

Parameters

Return values

getBooleanValue()

Parameters

Tags

Return values

getFieldList()

Parameters

Return values

getNativeSQLFunction()

Parameters

Return values

getSQLColumnsList()

Parameters

Tags

Return values

getTableList()

Tags

Return values

getTypeInfo()

Parameters

Tags

Return values

insertBulkData()

Parameters

Tags

Return values

parseCREATETABLE()

Parameters

Tags

Return values

parseSQLFunctionAndConvert()

Parameters

Return values

parseSQLType()

Parameters

Return values

stringToPhpValue()

Parameters

Tags

Return values

unifiedToPHPType()

Parameters

Tags

Return values

extractDateConverter()

Parameters

Return values

sqlsrvDbTools extends jDbTools
in package

jelix