Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
26.00% |
26 / 100 |
|
0.00% |
0 / 8 |
CRAP | |
0.00% |
0 / 1 |
| IacSqlBuilder | |
26.26% |
26 / 99 |
|
0.00% |
0 / 8 |
1313.30 | |
0.00% |
0 / 1 |
| where | |
0.00% |
0 / 16 |
|
0.00% |
0 / 1 |
156 | |||
| arrayToSqlIn | |
0.00% |
0 / 6 |
|
0.00% |
0 / 1 |
20 | |||
| fieldit | |
85.71% |
6 / 7 |
|
0.00% |
0 / 1 |
3.03 | |||
| update | |
0.00% |
0 / 15 |
|
0.00% |
0 / 1 |
90 | |||
| insert | |
76.92% |
20 / 26 |
|
0.00% |
0 / 1 |
15.08 | |||
| insertInto | |
0.00% |
0 / 4 |
|
0.00% |
0 / 1 |
6 | |||
| insertValues | |
0.00% |
0 / 11 |
|
0.00% |
0 / 1 |
42 | |||
| insertOnDuplicateKey | |
0.00% |
0 / 14 |
|
0.00% |
0 / 1 |
56 | |||
| 1 | <?php |
| 2 | /** @noinspection PhpMissingParamTypeInspection */ |
| 3 | /** @noinspection PhpMissingReturnTypeInspection */ |
| 4 | |
| 5 | /** |
| 6 | * IacSqlBuilder.php |
| 7 | * Helps build sql statements like insert, update, where clauses |
| 8 | * |
| 9 | * @package sql |
| 10 | * @author Informatica Asociada |
| 11 | * @copyright 2015 |
| 12 | * @version 1.0.0 |
| 13 | */ |
| 14 | namespace Iac\inc\sql; |
| 15 | require_once( dirname(__FILE__) . "/IacStringer.php"); |
| 16 | |
| 17 | /** |
| 18 | * IacSqlBuilder |
| 19 | * Helps build sql statements like insert, update, where clausesactualiza_banco_cuenta_fiduciarios |
| 20 | * |
| 21 | * @author Informatica Asociada |
| 22 | * @copyright 2015 |
| 23 | * @version 1.0.0 |
| 24 | */ |
| 25 | class IacSqlBuilder { |
| 26 | use IacStringer; |
| 27 | |
| 28 | public $dontQuoteValue = array( |
| 29 | 'IA_UUID()' => 1, |
| 30 | 'CURDATE()'=>1,'CURRENT_DATE()'=>1,'CURRENT_DATE'=>1,'SYSDATE()'=>1,'UTC_DATE()'=>1, |
| 31 | 'CURRENT_DATETIME'=>1,'NOW()'=>1, 'NOW(6)' => 1, |
| 32 | 'CURRENT_TIME()'=>1,'CURRENT_TIME'=>1,'CURTIME()'=>1,'UTC_TIME()'=>1, |
| 33 | 'CURRENT_TIMESTAMP()'=>1,'CURRENT_TIMESTAMP'=>1,'LOCALTIMESTAMP()'=>1,'LOCALTIMESTAMP'=>1,'UNIX_TIMESTAMP()'=>1,'UTC_TIMESTAMP()'=>1 |
| 34 | ); |
| 35 | |
| 36 | public $dontQuoteFieldName = array( |
| 37 | ); |
| 38 | |
| 39 | public $dontOnUpdateFieldName = array( |
| 40 | 'alta_db'=>1 |
| 41 | ); |
| 42 | |
| 43 | /** |
| 44 | * Build a where clause from an array or string |
| 45 | * |
| 46 | * @param mixed $where array('col_1'=>3,...) col_1 = '3' AND ... or string col_1=1 |
| 47 | * @param string $extraStringWhere |
| 48 | * @return string |
| 49 | * |
| 50 | */ |
| 51 | public function where($where, $extraStringWhere = '', $booleanOperator = 'AND', $fieldNameDontQuote = []) { |
| 52 | if(!is_array($where) || empty($where)) |
| 53 | return (!empty($where)) ? $where.(empty($extraStringWhere) ? '' : " ($extraStringWhere)") : (empty($extraStringWhere) ? '' : " ($extraStringWhere)"); |
| 54 | $excludeQuote = array_merge($this->dontQuoteFieldName, array_flip($fieldNameDontQuote)); |
| 55 | $w = []; |
| 56 | foreach($where as $fieldName=>$value){ |
| 57 | if($value === null) { |
| 58 | $w[] = $this->fieldit($fieldName) . " IS NULL"; |
| 59 | } |
| 60 | elseif(is_array($value)) { |
| 61 | $w[] = $this->fieldit($fieldName) . " IN " . $this->arrayToSqlIn($value); |
| 62 | } |
| 63 | else { |
| 64 | $w[] = $this->fieldit($fieldName) . "=" . |
| 65 | (array_key_exists(strtoupper($value ?? ''), $this->dontQuoteValue) |
| 66 | || array_key_exists($fieldName, $excludeQuote) ? |
| 67 | $value : |
| 68 | $this->strit($value)); |
| 69 | } |
| 70 | } |
| 71 | |
| 72 | return '(' . implode(" $booleanOperator ", $w) . ')' . |
| 73 | (empty($extraStringWhere) ? '' : " $booleanOperator ($extraStringWhere)"); |
| 74 | } |
| 75 | |
| 76 | public function arrayToSqlIn(array $array):string { |
| 77 | $inArray = []; |
| 78 | foreach($array as $v) { |
| 79 | $inArray[] = array_key_exists(strtoupper($v ?? ''), $this->dontQuoteValue) ? $v : strit($v); |
| 80 | } |
| 81 | if(empty($inArray)) { |
| 82 | return "('\t')"; |
| 83 | } |
| 84 | return '(' . implode(',', $inArray) . ')'; |
| 85 | } |
| 86 | |
| 87 | /** |
| 88 | * Protect with ` quotes a: column name to `column name` respecting . table.column to `table`.`column` |
| 89 | * |
| 90 | * @param string $fieldName |
| 91 | * @return string |
| 92 | */ |
| 93 | public function fieldit($fieldName) { |
| 94 | if(str_contains($fieldName,"->", )) |
| 95 | return $fieldName; |
| 96 | $protected = []; |
| 97 | $n = explode('.',$fieldName); |
| 98 | foreach($n as $field) { |
| 99 | $protected[]= '`'.str_replace('`', '', self::strim($field ?? '') ).'`'; |
| 100 | } |
| 101 | return implode('.', $protected); |
| 102 | } |
| 103 | |
| 104 | /** |
| 105 | * update() |
| 106 | * |
| 107 | * |
| 108 | * @example |
| 109 | * |
| 110 | * |
| 111 | * @param string $table |
| 112 | * @param array $values array('col_1'=>'value', ...) |
| 113 | * @param mixed $where array('col_1'=>'3',...) col_1 = 3 AND ... or string col_1=1 |
| 114 | * @param array $fieldNameDontQuote array('col_2',...) col_2's value wont be quoted in the set clause |
| 115 | * @param array $whereDontQuoteFieldName array('col_1',...) col_1's value wont be quoted in the where clause |
| 116 | * @param string $comment comentario para el query |
| 117 | * @return string sql update command |
| 118 | * |
| 119 | * @see $dontQuoteValue |
| 120 | * @see $dontQuoteFieldName |
| 121 | */ |
| 122 | public function update($table, $values, $where=[], $fieldNameDontQuote=[], $whereDontQuoteFieldName=[], $comment='', $table_alias = '' ) { |
| 123 | $excludeQuote = array_merge($this->dontQuoteFieldName, array_flip($fieldNameDontQuote)); |
| 124 | $set = ''; |
| 125 | if (empty($comment)) |
| 126 | $comment = "/* ACTUALIZA $table */"; |
| 127 | else |
| 128 | $comment = str_starts_with($comment, '/*') ? $comment : "/* $comment */"; |
| 129 | foreach($values as $fieldName => $value) |
| 130 | if($value === null || strcasecmp($value, 'null') === 0) { |
| 131 | $set .= "," . $this->fieldit($fieldName) . "=NULL"; |
| 132 | } |
| 133 | elseif(array_key_exists($fieldName,$excludeQuote) || array_key_exists(strtoupper($value ?? ''),$this->dontQuoteValue) ) |
| 134 | $set .= "," . $this->fieldit($fieldName) . "=$value"; |
| 135 | else |
| 136 | $set .= "," . $this->fieldit($fieldName) . "=".$this->strit($value); |
| 137 | |
| 138 | if (!empty($table_alias)) { |
| 139 | $table_alias = "AS $table_alias"; |
| 140 | } |
| 141 | return "UPDATE $comment " . $this->fieldit($table) . " $table_alias SET ".substr($set ?? '',1)." WHERE " . |
| 142 | $this->where($where, '', 'AND', $whereDontQuoteFieldName); |
| 143 | } |
| 144 | |
| 145 | /** |
| 146 | * insert() |
| 147 | * |
| 148 | * @example |
| 149 | * |
| 150 | * |
| 151 | * @param string $table |
| 152 | * @param array $values array('col_1'=>'value', ...) |
| 153 | * @param bool $autoOnUpdate true: an on duplicate key update clause will be added with $onUpdate concateanted |
| 154 | * with all fields not in $fieldNameDontQuote nor in $this->dontOnUpdateFieldName |
| 155 | * @param string $onUpdate add to ON DUPLICATE KEY UPDATE ie set last_insert_id: primary_key=LAST_INSERT_ID(primary_key) |
| 156 | * @param array $fieldNameDontOnUpdate array('col_1',...) col_1's value wont be quoted in the values clause |
| 157 | * @param array $fieldNameDontQuote array('col_1',...) col_1's value wont be quoted in the values clause |
| 158 | * @return string sql insert command |
| 159 | * |
| 160 | * @see $dontQuoteValue |
| 161 | * @see $dontQuoteFieldName |
| 162 | * @see $dontOnUpdateFieldName |
| 163 | */ |
| 164 | function insert($table, $values, $autoOnUpdate=false, $onUpdate='', $fieldNameDontOnUpdate=[], $fieldNameDontQuote=[], $comment='', $table_alias = '') { |
| 165 | |
| 166 | $onDup = trim($onUpdate); |
| 167 | if(!empty($onDup)) { |
| 168 | if(!str_starts_with($onDup ?? '', ',')) |
| 169 | $onDup = ','.$onDup; |
| 170 | if(str_ends_with($onDup ?? '', ',')) |
| 171 | $onDup = substr($onDup ?? '',0,-1); |
| 172 | } |
| 173 | |
| 174 | // $comment = "/* INSERTA $table **/"; |
| 175 | |
| 176 | if (empty($comment)) |
| 177 | $comment = "/* INSERTA $table **/"; |
| 178 | else |
| 179 | $comment = "/* $comment **/"; |
| 180 | |
| 181 | $dontOnDup = array_merge($this->dontOnUpdateFieldName, array_flip($fieldNameDontOnUpdate)); |
| 182 | $excludeQuote = array_merge($this->dontQuoteFieldName, array_flip($fieldNameDontQuote)); |
| 183 | |
| 184 | $fields = ''; |
| 185 | $vals = ''; |
| 186 | foreach($values as $fieldName => $value) { |
| 187 | $fieldProtected = $this->fieldit($fieldName); |
| 188 | $fields .= ",$fieldProtected"; |
| 189 | if($autoOnUpdate && !array_key_exists($fieldName,$dontOnDup) && stripos($value ?? '','DEFAULT(') === false ) |
| 190 | $onDup .= ",$fieldProtected=VALUES($fieldProtected)"; |
| 191 | if($value === null) |
| 192 | $vals .= ',NULL'; |
| 193 | elseif(array_key_exists($fieldName,$excludeQuote) || array_key_exists(strtoupper($value ?? ''),$this->dontQuoteValue) ) |
| 194 | $vals .= ",$value"; |
| 195 | else |
| 196 | $vals .= ','.$this->strit($value); |
| 197 | } |
| 198 | if(!empty($onDup)) { |
| 199 | $onDup = ' ON DUPLICATE KEY UPDATE '.substr($onDup ?? '',1); |
| 200 | } |
| 201 | return "INSERT $comment INTO $table(".substr($fields ?? '',1).") VALUES(".substr($vals ?? '',1).")$onDup"; |
| 202 | } |
| 203 | |
| 204 | /** |
| 205 | * IacSqlBuilder::insertInto() |
| 206 | * |
| 207 | * @example |
| 208 | * |
| 209 | * |
| 210 | * @param mixed $table |
| 211 | * @param mixed $values array('col_1'=>'value', ...) |
| 212 | * @return string |
| 213 | * |
| 214 | * @see insertValues() |
| 215 | * @see insertOnDuplicateKey() |
| 216 | * |
| 217 | */ |
| 218 | public function insertInto($table,$values) { |
| 219 | $fields = ''; |
| 220 | foreach($values as $fieldName => $_) |
| 221 | $fields .= ',' . $this->fieldit($fieldName); |
| 222 | return "INSERT INTO " . $this->fieldit($table) . "(".substr($fields ?? '',1).") "; |
| 223 | } |
| 224 | |
| 225 | /** |
| 226 | * IacSqlBuilder::insertValues() |
| 227 | * |
| 228 | * |
| 229 | * @example |
| 230 | * |
| 231 | * |
| 232 | * @param array $values array('col_1'=>'value', ...) |
| 233 | * @param array $fieldNameDontQuote |
| 234 | * @return string |
| 235 | * |
| 236 | * @see $dontQuoteValue |
| 237 | * @see $dontQuoteFieldName |
| 238 | * @see insertInto() |
| 239 | * @see insertOnDuplicateKey() |
| 240 | */ |
| 241 | public function insertValues($values, $fieldNameDontQuote=[]) { |
| 242 | $excludeQuote = array_merge($this->dontQuoteFieldName, array_flip($fieldNameDontQuote)); |
| 243 | $vals = ''; |
| 244 | foreach($values as $fieldName => $value) { |
| 245 | // if($value === null) |
| 246 | // $vals .= ',NULL'; |
| 247 | // elseif(array_key_exists($fieldName,$excludeQuote) || array_key_exists(strtoupper($value ?? ''),$this->dontQuoteValue) ) |
| 248 | // $vals .= ",$value"; |
| 249 | // else |
| 250 | // $vals .= ','.$this->strit($value); |
| 251 | $vals .= empty($value) ? |
| 252 | ', NULL' : |
| 253 | (array_key_exists($fieldName,$excludeQuote) || array_key_exists(strtoupper($value ?? ''),$this->dontQuoteValue) ? |
| 254 | ", $value" : |
| 255 | ','.$this->strit($value) |
| 256 | ); |
| 257 | } |
| 258 | if(empty($vals)) |
| 259 | return ''; |
| 260 | return '('.substr($vals ?? '',1).')'; |
| 261 | } |
| 262 | |
| 263 | /** |
| 264 | * IacSqlBuilder::insertOnDuplicateKey() |
| 265 | * |
| 266 | * |
| 267 | * @example |
| 268 | * |
| 269 | * |
| 270 | * |
| 271 | * @param array $values array('col_1'=>'value', ...) |
| 272 | * @param string $onUpdate add to ON DUPLICATE KEY UPDATE ie set last_insert_id: primary_key=LAST_INSERT_ID(primary_key) |
| 273 | * @param array $fieldNameDontOnUpdate ['colDontUpdate'] |
| 274 | * @return string |
| 275 | * |
| 276 | * @see $dontOnUpdateFieldName |
| 277 | * @see insertInto() |
| 278 | * @see insertValues() |
| 279 | */ |
| 280 | public function insertOnDuplicateKey($values, $onUpdate='', $fieldNameDontOnUpdate=[]) { |
| 281 | $onDup = trim($onUpdate); |
| 282 | if(!empty($onDup)) { |
| 283 | if(!str_starts_with($onDup ?? '', ',')) |
| 284 | $onDup = ','.$onDup; |
| 285 | if(str_ends_with($onDup ?? '', ',')) |
| 286 | $onDup = substr($onDup ?? '',0,-1); |
| 287 | } |
| 288 | |
| 289 | $dontOnDup = array_merge($this->dontOnUpdateFieldName, array_flip($fieldNameDontOnUpdate)); |
| 290 | |
| 291 | foreach($values as $fieldName => $value) |
| 292 | if (!array_key_exists($fieldName, $dontOnDup)) { |
| 293 | $f = $this->fieldit($fieldName); |
| 294 | $onDup .= ",$f=VALUES($f)"; |
| 295 | } |
| 296 | |
| 297 | if(empty($onDup)) |
| 298 | return ''; |
| 299 | return ' ON DUPLICATE KEY UPDATE '.substr($onDup ?? '',1); |
| 300 | } |
| 301 | |
| 302 | } |