Source for file TDatabase.php
Documentation is available at TDatabase.php
namespace
Adianti \
Database ;
* @author Pablo Dall'Oglio
* @copyright Copyright (c) 2018 Adianti Solutions Ltd. (http://www.adianti.com.br)
* @license http://www.adianti.com.br/framework-license
* @param $conn Connection
* @param $table Table name
* @param $ifexists Drop only if exists
public static function dropTable ( $conn , $table , $ifexists =
false )
$driver =
$conn -> getAttribute ( PDO :: ATTR_DRIVER_NAME ) ;
if ( in_array ( $driver , [ 'oci' , 'dblib' , 'sqlsrv' ] ))
$list [ 'oci' ] =
" SELECT * FROM cat WHERE table_type in ('TABLE', 'VIEW') AND table_name = '{$table_upper } ' " ;
$list [ 'dblib' ] =
" SELECT * FROM sysobjects WHERE (type = 'U' or type='V') AND name = '{$table } ' " ;
$list [ 'sqlsrv' ] =
$list [ 'dblib' ] ;
$result =
$conn -> query ( $sql ) ;
if ( count ( $result -> fetchAll ( )) >
0 )
$sql =
" DROP TABLE {$table } " ;
return $conn -> query ( $sql ) ;
$sql =
" DROP TABLE {$table } " ;
return $conn -> query ( $sql ) ;
$ife =
$ifexists ?
' IF EXISTS ' :
'' ;
$sql =
" DROP TABLE {$ife } {$table } " ;
return $conn -> query ( $sql ) ;
* @param $conn Connection
* @param $table Table name
* @param $columns Array of columns
public static function createTable ( $conn , $table , $columns )
foreach ( $columns as $column =>
$type )
$columns_list [ ] =
" {
$column } {
$type } " ;
$sql =
" CREATE TABLE {$table } ( " .
implode ( ',' , $columns_list ) .
")" ;
return $conn -> query ( $sql ) ;
* @param $conn Connection
* @param $table Table name
* @param $column Column name
public static function dropColumn ( $conn , $table , $column )
$sql =
" ALTER TABLE {$table } DROP COLUMN {$column } " ;
return $conn -> query ( $sql ) ;
* @param $conn Connection
* @param $table Table name
* @param $column Column name
* @param $type Column type
* @param $options Column options
public static function addColumn ( $conn , $table , $column , $type , $options )
$sql =
" ALTER TABLE {$table } ADD {$column } {$type } {$options } " ;
return $conn -> query ( $sql ) ;
* @param $conn Connection
* @param $table Table name
* @param $values Array of values
* @param $avoid_criteria Criteria to avoid insertion
public static function insertData ( $conn , $table , $values , $avoid_criteria =
null )
if ( ! empty ( $avoid_criteria ))
if ( self :: countData ( $conn , $table , $avoid_criteria ) >
0 )
foreach ( $values as $key =>
$value )
$sql -> setRowData ( $key , $value ) ;
if ( isset
( $dbinfo [ 'prep' ] ) AND $dbinfo [ 'prep' ] ==
'1' ) // prepared ON
$result =
$conn -> prepare ( $sql -> getInstruction ( TRUE ) , array ( PDO :: ATTR_CURSOR =>
PDO :: CURSOR_FWDONLY )) ;
$result -> execute ( $sql -> getPreparedVars ( ) ) ;
$result =
$conn -> query ( $sql -> getInstruction ( )) ;
* @param $conn Connection
* @param $table Table name
* @param $values Array of values
* @param $avoid_criteria Criteria to avoid insertion
public static function updateData ( $conn , $table , $values , $criteria =
null )
$sql -> setCriteria ( $criteria ) ;
foreach ( $values as $key =>
$value )
$sql -> setRowData ( $key , $value ) ;
if ( isset
( $dbinfo [ 'prep' ] ) AND $dbinfo [ 'prep' ] ==
'1' ) // prepared ON
$result =
$conn -> prepare ( $sql -> getInstruction ( TRUE ) , array ( PDO :: ATTR_CURSOR =>
PDO :: CURSOR_FWDONLY )) ;
$result -> execute ( $sql -> getPreparedVars ( ) ) ;
$result =
$conn -> query ( $sql -> getInstruction ( )) ;
* @param $conn Connection
* @param $table Table name
* @param $criteria Filter criteria
public static function clearData ( $conn , $table , $criteria =
null )
$sql -> setCriteria ( $criteria ) ;
return $conn -> query ( $sql -> getInstruction ( ) ) ;
* @param $conn Connection
public static function execute ( $conn , $query )
return $conn -> query ( $query ) ;
* @param $conn Connection
* @param $mapping Mapping between fields
* @param $prepared_values Parameters for SQL Query
public static function getData ( $conn , $query , $mapping =
null , $prepared_values =
null , Closure $action =
null )
$result =
$conn -> prepare ( $query ) ;
$result -> execute ( $prepared_values ) ;
foreach ( $result as $row )
foreach ( $mapping as $map )
$values [ $newcolumn ] =
self :: transform ( $row , $map ) ;
* @param $conn PDO source connection
* @param $table Source table
* @param $criteria Filter criteria
public static function countData ( PDO $conn , $table , $criteria =
null )
$sql -> setCriteria ( $criteria ) ;
$sql -> addColumn ( 'count(*)' ) ;
if ( isset
( $dbinfo [ 'prep' ] ) AND $dbinfo [ 'prep' ] ==
'1' ) // prepared ON
$result =
$conn -> prepare ( $sql -> getInstruction ( TRUE ) , array ( PDO :: ATTR_CURSOR =>
PDO :: CURSOR_FWDONLY )) ;
$result -> execute ( $criteria -> getPreparedVars ( ) ) ;
// executes the SELECT statement
$result =
$conn -> query ( $sql -> getInstruction ( )) ;
* Copy data from table to table
* @param $source_conn PDO source connection
* @param $target_conn PDO target connection
* @param $source_table Source table
* @param $target_table Target table
* @param $mapping Mapping between fields
* @param $criteria Filter criteria
* @param $bulk_inserts Inserts per time
* @param $auto_commit Auto commit after x inserts
public static function copyData ( PDO $source_conn , PDO $target_conn , $source_table , $target_table , $mapping , $criteria =
null , $bulk_inserts =
1 , $auto_commit =
false )
$driver =
$target_conn -> getAttribute ( PDO :: ATTR_DRIVER_NAME ) ;
$bulk_inserts =
$driver ==
'oci' ?
1 :
$bulk_inserts ;
foreach ( $mapping as $map )
if ( ! empty ( $map [ 0 ] ) AND substr ( $map [ 0 ] , 0 , 4 ) !==
'VAL:' )
$source_columns [ ] =
$map [ 0 ] ;
$target_columns [ ] =
$map [ 1 ] ;
$sel -> setEntity ( $source_table ) ;
$sel -> setCriteria ( $criteria ) ;
foreach ( $source_columns as $source_column )
$sel -> addColumn ( $source_column ) ;
$result =
$source_conn -> query ( $sel -> getInstruction ( )) ;
$ins -> setEntity ( $target_table ) ;
foreach ( $result as $row )
foreach ( $mapping as $map )
$values [ $newcolumn ] =
self :: transform ( $row , $map ) ;
$ins -> addRowValues ( $values ) ;
if ( $buffer_counter ==
$bulk_inserts )
$target_conn -> query ( $ins -> getInstruction ( )) ;
$ins -> setEntity ( $target_table ) ;
if ( $commit_counter ==
$auto_commit )
$target_conn -> beginTransaction ( ) ;
$target_conn -> query ( $ins -> getInstruction ( )) ;
* Copy data from query to table
* @param $source_conn PDO source connection
* @param $target_conn PDO target connection
* @param $query SQL Query
* @param $target_table Target table
* @param $mapping Mapping between fields
* @param $prepared_values Parameters for SQL Query
* @param $bulk_inserts Inserts per time
* @param $auto_commit Auto commit after x inserts
public static function copyQuery ( PDO $source_conn , PDO $target_conn , $query , $target_table , $mapping , $prepared_values =
null , $bulk_inserts =
1 , $auto_commit =
false )
$driver =
$target_conn -> getAttribute ( PDO :: ATTR_DRIVER_NAME ) ;
$bulk_inserts =
$driver ==
'oci' ?
1 :
$bulk_inserts ;
foreach ( $mapping as $map )
$target_columns [ ] =
$map [ 1 ] ;
$result =
$source_conn -> prepare ( $query ) ;
$result -> execute ( $prepared_values ) ;
$ins -> setEntity ( $target_table ) ;
foreach ( $result as $row )
foreach ( $mapping as $map )
$values [ $newcolumn ] =
self :: transform ( $row , $map ) ;
$ins -> addRowValues ( $values ) ;
if ( $buffer_counter ==
$bulk_inserts )
$target_conn -> query ( $ins -> getInstruction ( )) ;
$ins -> setEntity ( $target_table ) ;
if ( $commit_counter ==
$auto_commit )
$target_conn -> beginTransaction ( ) ;
$target_conn -> query ( $ins -> getInstruction ( )) ;
* Import data from CSV file
* @param $filename CSV File to import
* @param $target_conn Target connection
* @param $target_table Target table
* @param $mapping Mapping between fields
* @param $separator Columns separator [,]
public static function importFromFile ( $filename , $target_conn , $target_table , $mapping , $separator =
',' , $bulk_inserts =
1 )
$driver =
$target_conn -> getAttribute ( PDO :: ATTR_DRIVER_NAME ) ;
$bulk_inserts =
$driver ==
'oci' ?
1 :
$bulk_inserts ;
$ins -> setEntity ( $target_table ) ;
while ( isset
( $data [ $counter ] ))
$row =
str_getcsv ( $data [ $counter ++
] , $separator ) ;
foreach ( $row as $key =>
$value )
$row [ $header [ $key ] ] =
$value ;
foreach ( $mapping as $map )
$values [ $newcolumn ] =
self :: transform ( $row , $map ) ;
$ins -> addRowValues ( $values ) ;
if ( $buffer_counter ==
$bulk_inserts )
$target_conn -> query ( $ins -> getInstruction ( )) ;
$ins -> setEntity ( $target_table ) ;
$target_conn -> query ( $ins -> getInstruction ( )) ;
* Export data to CSV file
* @param $source_conn Source connection
* @param $source_table Target table
* @param $filename CSV File to import
* @param $mapping Mapping between fields
* @param $criteria Select criteria
* @param $separator Columns separator [,]
public static function exportToFile ( $source_conn , $source_table , $filename , $mapping , $criteria =
null , $separator =
',' )
foreach ( $mapping as $map )
if ( ! empty ( $map [ 0 ] ) AND substr ( $map [ 0 ] , 0 , 4 ) !==
'VAL:' )
$source_columns [ ] =
$map [ 0 ] ;
$target_columns [ ] =
$map [ 1 ] ;
$sel -> setEntity ( $source_table ) ;
$sel -> setCriteria ( $criteria ) ;
foreach ( $source_columns as $source_column )
$sel -> addColumn ( $source_column ) ;
$result =
$source_conn -> query ( $sel -> getInstruction ( )) ;
$file =
new SplFileObject ( $filename , 'w' ) ;
$file -> setCsvControl ( ',' ) ;
$file -> fputcsv ( $target_columns ) ;
foreach ( $result as $row )
foreach ( $mapping as $map )
$values [ $newcolumn ] =
self :: transform ( $row , $map ) ;
* Transform value according to mapping rules
* @param $map Array with mapping instruction
private static function transform ( $row , $map )
$callback = isset
( $map [ 2 ] ) ?
$map [ 2 ] :
null ;
$value =
( substr ( $column , 0 , 4 ) ==
'VAL:' ) ?
substr ( $column , 4 ) :
$row [ $column ] ;