kenken999/php
0
1<?php2class DB3{4 public static function CurrentConnection()5 {6 global $currentConnection;7 return $currentConnection ? $currentConnection : DB::DefaultConnection();8 }9 10 public static function CurrentConnectionId()11 {12 $conn = DB::CurrentConnection();13 return $conn->connId;14 }15 16 public static function DefaultConnection()17 {18 global $cman;19 return $cman->getDefault();20 }21 22 public static function ConnectionByTable( $table )23 {24 global $cman;25 return $cman->byTable($table);26 }27 28 public static function ConnectionByName( $name )29 {30 global $cman;31 return $cman->byName( $name );32 }33 34 public static function SetConnection( $connection )35 {36 global $currentConnection;37 if ( is_string( $connection ) )38 {39 $currentConnection = DB::ConnectionByName( $connection );40 }41 else if ( is_a($connection, 'Connection') ) {42 $currentConnection = $connection;43 }44 }45 46 public static function LastId()47 {48 return DB::CurrentConnection()->getInsertedId();49 }50 51 public static function Query( $sql )52 {53 return DB::CurrentConnection()->querySilent( $sql );54 }55 56 public static function Exec( $sql )57 {58 return DB::CurrentConnection()->execSilent( $sql ) != NULL;59 }60 61 public static function LastError()62 {63 return DB::CurrentConnection()->lastError();64 }65 /**66 * @param Array $userOrders array("field1", "field2") -> ORDER BY field1, field267 * array( array( "field1", "d" ), "field2" ) -> ORDER BY field1 DESC, field268 * array( array( "field1", "a" ), array( "field2", "d" ) ) -> ORDER BY field1 ASC, field2 DESC69 */70 public static function Select( $table, $userConditions = array(), $userOrders = array() )71 {72 $dataSource = getDbTableDataSource( $table, DB::CurrentConnectionId() );73 if( !$dataSource )74 return false;75 76 $dc = new DsCommand();77 $dc->filter = DB::_createFilterCondition( $userConditions );78 $dc->order = array();79 80 foreach( $userOrders as $userOrder ){81 if( is_array( $userOrder ) ){82 $orderClause = array( "column" => $userOrder[0] );83 $dir = $userOrder[1];84 switch ( $dir ) {85 case "a":86 $orderClause["dir"] = "ASC";87 break;88 case "d":89 $orderClause["dir"] = "DESC";90 break;91 }92 }93 else94 $orderClause = array( "column" => $userOrder );95 96 $dc->order[] = $orderClause;97 }98 $queryResult = $dataSource->getList( $dc );99 return $queryResult;100 }101 102 public static function SelectValue( $field, $table, $userConditions = array(), $order = array() ){103 $rs = DB::Select( $table, $userConditions, $order );104 if( !$rs )105 return false;106 $data = $rs->fetchAssoc();107 if( $data[ $field ] )108 return $data[ $field ];109 return false;110 }111 112 public static function Delete($table, $userConditions = array() )113 {114 $dataSource = getDbTableDataSource( $table, DB::CurrentConnectionId() );115 if( !$dataSource )116 return false;117 118 $dc = new DsCommand();119 $dc->filter = DB::_createFilterCondition( $userConditions );120 $prep = $dataSource->prepareSQL( $dc );121 if( $prep["where"] == "" )122 return false;123 $ret = $dataSource->deleteSingle( $dc, false );124 return $ret;125 }126 127 public static function Insert($table, $data)128 {129 $dataSource = getDbTableDataSource( $table, DB::CurrentConnectionId() );130 if( !$dataSource ) {131 return false;132 }133 $dc = new DsCommand();134 $dc->values = $data;135 $result = $dataSource->insertSingle( $dc );136 return !!$result;137 }138 139 public static function Update($table, $data, $userConditions)140 {141 $dataSource = getDbTableDataSource( $table, DB::CurrentConnectionId() );142 if( !$dataSource ) {143 return false;144 }145 if( !$userConditions ) {146 return false;147 }148 $dc = new DsCommand();149 $dc->values = $data;150 $dc->filter = DB::_createFilterCondition( $userConditions );151 $result = $dataSource->updateSingle( $dc, false );152 return !!$result;153 }154 155 public static function Count( $table, $userConditions = array() ){156 $dataSource = getDbTableDataSource( $table, DB::CurrentConnectionId() );157 if( !$dataSource )158 return false;159 $dc = new DsCommand();160 $dc->filter = DB::_createFilterCondition( $userConditions );161 $count = $dataSource->getCount( $dc );162 return $count;163 }164 protected static function _createFilterCondition( $userConditions )165 {166 if( !is_array( $userConditions ) ) {167 return DataCondition::SQLCondition( $userConditions );168 }169 170 $conditions = array();171 foreach($userConditions as $fieldName => $value)172 {173 if ( is_null($value) ) {174 $conditions[] = DataCondition::FieldIs( $fieldName, dsopEMPTY, '' );175 } else {176 $conditions[] = DataCondition::FieldEquals( $fieldName, $value );177 }178 }179 return DataCondition::_And( $conditions );180 }181 182 183 /**184 * @param Array blobs185 * @param String dalSQL186 * @param Array tableinfo187 */188 protected static function _execSilentWithBlobProcessing($blobs, $dalSQL, $tableinfo, $autoincField = null)189 {190 $blobTypes = array();191 if( DB::CurrentConnection()->dbType == nDATABASE_Informix )192 {193 foreach( $blobs as $fname => $fvalue )194 {195 $blobTypes[ $fname ] = $tableinfo[ $fname ]["type"];196 }197 }198 199 DB::CurrentConnection()->execSilentWithBlobProcessing( $dalSQL, $blobs, $blobTypes, $autoincField );200 }201 202 protected static function _prepareValue($value, $type)203 {204 if ( is_null($value) )205 return "NULL";206 207 if( DB::CurrentConnection()->dbType == nDATABASE_Oracle || DB::CurrentConnection()->dbType == nDATABASE_DB2 || DB::CurrentConnection()->dbType == nDATABASE_Informix )208 {209 if( IsBinaryType($type) )210 {211 if( DB::CurrentConnection()->dbType == nDATABASE_Oracle )212 return "EMPTY_BLOB()";213 214 return "?";215 }216 217 if( DB::CurrentConnection()->dbType == nDATABASE_Informix && IsTextType($type) )218 return "?";219 }220 221 if( IsNumberType($type) && !is_numeric($value) )222 {223 $value = trim($value);224 $value = str_replace(",", ".", $value);225 if ( !is_numeric($value) )226 return "NULL";227 }228 229 if( IsDateFieldType($type) || IsTimeType($type) )230 {231 if( !$value )232 return "NULL";233 234 // timestamp235 if ( is_int($value) )236 {237 if ( IsDateFieldType($type) )238 {239 $value = getYMDdate($value) . " " . getHISdate($value);240 }241 else if ( IsTimeType($type) )242 {243 $value = getHISdate($value);244 }245 }246 247 return DB::CurrentConnection()->addDateQuotes( $value );248 }249 250 if( NeedQuotes($type) )251 return DB::CurrentConnection()->prepareString( $value );252 253 return $value;254 }255 256 /**257 * Find table info stored in the project file258 *259 */260 public static function _findDalTable( $table, $conn = null )261 {262 global $dalTables;263 if( !$conn )264 $conn = DB::CurrentConnection();265 $tableName = $conn->getTableNameComponents( $table );266 267 DB::_fillTablesList( $conn );268 269 // exact match270 foreach( $dalTables[$conn->connId] as $t ) {271 if( ( !$tableName["schema"] || $t["schema"] == $tableName["schema"] )272 && $t["name"] == $tableName["table"] )273 return $t;274 }275 276 // case-insensitive277 $tableName["schema"] = strtoupper( $tableName["schema"] );278 $tableName["table"] = strtoupper( $tableName["table"] );279 280 foreach( $dalTables[$conn->connId] as $t )281 {282 if( ( !$tableName["schema"] || strtoupper( $t["schema"] ) == $tableName["schema"] )283 && strtoupper( $t["name"] ) == $tableName["table"] )284 return $t;285 }286 return null;287 }288 289 /**290 * Get list of table field names and types291 * Check tables stored in the project first, then fetch it from the database.292 *293 */294 public static function _getTableInfo($table, $connId = null )295 {296 global $dal_info, $tableinfo_cache, $cman;297 if( !$connId )298 $connId = DB::CurrentConnectionId();299 300 // prepare cache301 if( !isset($tableinfo_cache[ $connId ] ) )302 $tableinfo_cache[ $connId ] = array();303 304 $tableInfo = array();305 306 307 $tableDescriptor = DB::_findDalTable( $table, $cman->byId( $connId ) );308 309 if ( $tableDescriptor )310 {311 importTableInfo( $tableDescriptor["varname"] );312 313 $tableInfo["fields"] = $dal_info[ $tableDescriptor["varname"] ];314 315 if( $tableDescriptor["schema"] )316 $tableInfo["fullName"] = $tableDescriptor["schema"] . "." . $tableDescriptor["name"];317 else318 $tableInfo["fullName"] = $tableDescriptor["name"];319 }320 else321 {322 // check cache first323 if( isset($tableinfo_cache[ $connId ][ $table ] ) )324 return $tableinfo_cache[ $connId ][ $table ];325 326 // fetch table info from the database327 $helpSql = "select * from " . DB::CurrentConnection()->addTableWrappers( $table ) . " where 1=0";328 329 $tableInfo["fullName"] = $table;330 $tableInfo["fields"] = array();331 332 // in case getFieldsList throws error333 $tableinfo_cache[ $connId ][ $table ] = false;334 335 $fieldList = DB::CurrentConnection()->getFieldsList($helpSql);336 foreach ($fieldList as $f )337 {338 $tableInfo["fields"][ $f["fieldname"] ] = array( "type" => $f["type"], "name" => $f["fieldname"] );339 }340 $tableinfo_cache[ $connId ][ $table ] = $tableInfo;341 }342 343 return $tableInfo;344 }345 346 347 protected static function _fillTablesList( $conn )348 {349 global $dalTables;350 if( !$conn )351 $conn = DB::CurrentConnection();352 if( isset($dalTables[ $conn->connId ]) )353 return;354 $dalTables[ $conn->connId ] = array();355 if( "chats_at_localhost" == $conn->connId )356 {357 $dalTables[$conn->connId][] = array("name" => "_DIA_DA_APPRAISER", "varname" => "chats_at_localhost___DIA_DA_APPRAISER", "altvarname" => "_DIA_DA_APPRAISER", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");358 $dalTables[$conn->connId][] = array("name" => "_DIA_DA_CLARITY", "varname" => "chats_at_localhost___DIA_DA_CLARITY", "altvarname" => "_DIA_DA_CLARITY", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");359 $dalTables[$conn->connId][] = array("name" => "_DIA_DA_COLOR", "varname" => "chats_at_localhost___DIA_DA_COLOR", "altvarname" => "_DIA_DA_COLOR", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");360 $dalTables[$conn->connId][] = array("name" => "_DIA_DA_CUT", "varname" => "chats_at_localhost___DIA_DA_CUT", "altvarname" => "_DIA_DA_CUT", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");361 $dalTables[$conn->connId][] = array("name" => "_DIA_DA_FLUO", "varname" => "chats_at_localhost___DIA_DA_FLUO", "altvarname" => "_DIA_DA_FLUO", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");362 $dalTables[$conn->connId][] = array("name" => "agett_prompt", "varname" => "chats_at_localhost__agett_prompt", "altvarname" => "agett_prompt", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");363 $dalTables[$conn->connId][] = array("name" => "chat126_users1", "varname" => "chats_at_localhost__chat126_users1", "altvarname" => "chat126_users1", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");364 $dalTables[$conn->connId][] = array("name" => "Chat2uggroups", "varname" => "chats_at_localhost__Chat2uggroups", "altvarname" => "Chat2uggroups", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");365 $dalTables[$conn->connId][] = array("name" => "Chat2ugmembers", "varname" => "chats_at_localhost__Chat2ugmembers", "altvarname" => "Chat2ugmembers", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");366 $dalTables[$conn->connId][] = array("name" => "Chat2ugrights", "varname" => "chats_at_localhost__Chat2ugrights", "altvarname" => "Chat2ugrights", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");367 $dalTables[$conn->connId][] = array("name" => "chat_agent", "varname" => "chats_at_localhost__chat_agent", "altvarname" => "chat_agent", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");368 $dalTables[$conn->connId][] = array("name" => "chat_external", "varname" => "chats_at_localhost__chat_external", "altvarname" => "chat_external", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");369 $dalTables[$conn->connId][] = array("name" => "chat_files", "varname" => "chats_at_localhost__chat_files", "altvarname" => "chat_files", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");370 $dalTables[$conn->connId][] = array("name" => "chat_groups", "varname" => "chats_at_localhost__chat_groups", "altvarname" => "chat_groups", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");371 $dalTables[$conn->connId][] = array("name" => "chat_history", "varname" => "chats_at_localhost__chat_history", "altvarname" => "chat_history", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");372 $dalTables[$conn->connId][] = array("name" => "chat_peopletype", "varname" => "chats_at_localhost__chat_peopletype", "altvarname" => "chat_peopletype", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");373 $dalTables[$conn->connId][] = array("name" => "chat_settings", "varname" => "chats_at_localhost__chat_settings", "altvarname" => "chat_settings", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");374 $dalTables[$conn->connId][] = array("name" => "chat_timezone", "varname" => "chats_at_localhost__chat_timezone", "altvarname" => "chat_timezone", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");375 $dalTables[$conn->connId][] = array("name" => "chat_users", "varname" => "chats_at_localhost__chat_users", "altvarname" => "chat_users", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");376 $dalTables[$conn->connId][] = array("name" => "Eoc", "varname" => "chats_at_localhost__Eoc", "altvarname" => "Eoc", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");377 $dalTables[$conn->connId][] = array("name" => "Eoc_mitsumori", "varname" => "chats_at_localhost__Eoc_mitsumori", "altvarname" => "Eoc_mitsumori", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");378 $dalTables[$conn->connId][] = array("name" => "Eoc_refining", "varname" => "chats_at_localhost__Eoc_refining", "altvarname" => "Eoc_refining", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");379 $dalTables[$conn->connId][] = array("name" => "Eoc_takuhai", "varname" => "chats_at_localhost__Eoc_takuhai", "altvarname" => "Eoc_takuhai", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");380 $dalTables[$conn->connId][] = array("name" => "mst_processing", "varname" => "chats_at_localhost__mst_processing", "altvarname" => "mst_processing", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");381 $dalTables[$conn->connId][] = array("name" => "mst_producing_area", "varname" => "chats_at_localhost__mst_producing_area", "altvarname" => "mst_producing_area", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");382 $dalTables[$conn->connId][] = array("name" => "mst_quality", "varname" => "chats_at_localhost__mst_quality", "altvarname" => "mst_quality", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");383 $dalTables[$conn->connId][] = array("name" => "PMT_EV001", "varname" => "chats_at_localhost__PMT_EV001", "altvarname" => "PMT_EV001", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");384 $dalTables[$conn->connId][] = array("name" => "webreport_admin", "varname" => "chats_at_localhost__webreport_admin", "altvarname" => "webreport_admin", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");385 $dalTables[$conn->connId][] = array("name" => "webreport_style", "varname" => "chats_at_localhost__webreport_style", "altvarname" => "webreport_style", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");386 $dalTables[$conn->connId][] = array("name" => "webreports", "varname" => "chats_at_localhost__webreports", "altvarname" => "webreports", "connId" => "chats_at_localhost", "schema" => "", "connName" => "chats at localhost");387 }388 }389 390 public static function PrepareConnectionSQL( $conn, $sql,391 $arg1 = null,392 $arg2 = null,393 $arg3 = null,394 $arg4 = null,395 $arg5 = null,396 $arg6 = null,397 $arg7 = null,398 $arg8 = null,399 $arg9 = null,400 $arg10 = null ) {401 402 $prevConn = DB::CurrentConnection();403 DB::SetConnection( $conn );404 $result = DB::PrepareSQL( $sql, $arg1, $arg2, $arg3, $arg4, $arg5, $arg6, $arg7, $arg8, $arg9, $arg10 );405 DB::SetConnection( $prevConn );406 return $result;407 }408 409 public static function PrepareSQL( $sql )410 {411 $args = func_get_args();412 413 $conn = DB::CurrentConnection();414 415 $tokens = DB::scanTokenString($sql);416 417 $replacements = array();418 // build array of replacements in this format:419 // "offset" => position in the string where replacement should be done420 // "len" => length of original substring to cut out421 // "insert" => string to insert in place of cut out422 423 foreach ($tokens["matches"] as $i => $match) {424 $offset = $tokens["offsets"][$i];425 $token = $tokens["tokens"][$i];426 427 $repl = array(428 "offset" => $offset,429 "len" => strlen($match)430 );431 432 $val = "";433 if (is_numeric($token) && count( $args ) > $token) {434 $val = $args[(int)$token];435 } else {436 $val = RunnerContext::getValue($token);437 }438 439 440 /**441 * Don't ever dare to alter this code!442 * Everything outside quotes must be converted to number to avoid SQL injection443 */444 $inQuotes = $conn->positionQuoted( $sql, $offset );445 if( is_array( $val ) ) {446 $_values = array();447 foreach( $val as $v ) {448 if ( $inQuotes ) {449 $_values[] = '\''.$conn->addSlashes( $v ).'\'';450 } else {451 $_values[] = DB::prepareNumberValue( $v );452 }453 }454 $glued = implode( ",", $_values );455 $repl["insert"] = $inQuotes ? substr( $glued, 1, strlen( $glued ) - 2 ) : $glued;456 } else {457 if( $inQuotes ) {458 $repl["insert"] = $conn->addSlashes( $val );459 } else {460 $repl["insert"] = DB::prepareNumberValue( $val );461 }462 }463 464 $replacements[] = $repl;465 }466 467 // do replacements468 return RunnerContext::doReplacements( $sql, $replacements );469 }470 471 /**472 * @return Array473 */474 public static function readSQLTokens( $sql )475 {476 $arr = DB::scanTokenString( $sql );477 return $arr["tokens"];478 }479 480 /**481 * @return Array482 */483 public static function readMasterTokens( $sql )484 {485 $masterTokens = array();486 487 $allTokens = DB::readSQLTokens( $sql );488 foreach ( $allTokens as $key => $token )489 {490 $dotPos = strpos( $token, "." );491 if( $dotPos !== FALSE && strtolower( substr( $token, 0, $dotPos ) ) == "master")492 {493 $masterTokens[] = $token;494 }495 }496 497 return $masterTokens;498 }499 500 /**501 * Scans SQL string, finds all tokens. Returns three arrays - 'tokens', 'matches' and 'offsets'502 * Offsets are positions of corresponding 'matches' items in the string503 * Example:504 * insert into table values (':aaa', :old.bbb, ':{master.order date}')505 * tokens: ["aaa", "old.bbb", "master.order date"]506 * matches: [":aaa", ":old.bbb", ":{master.order date}"]507 * offsets: [28, 35, 46]508 *509 * Exceptions for tokens without {}510 * 1. shouldn't start with number511 * :62aaa512 * 2. shouldn't follow letter513 * x:aaa514 * 3. shouldn't follow :515 * ::aaa516 *517 * @return Array [ "tokens" => Array, "matches" => Array, "offsets" => Array ]518 */519 public static function scanTokenString($sql)520 {521 $tokens = array();522 $offsets = array();523 $matches = array();524 525 // match aaa, old.bbb, master.order date from:526 // insert into table values (':aaa', :old.bbb, ':{master.order date}')527 528 $pattern = '/(?:[^\w\:]|^)(\:([a-zA-Z_]{1}[\w\.]*))|\:\{([^\:]*?)\}|(?:[^\w\:]|^)(\:([1-9]+[0-9]*))/';529 530 $result = findMatches($pattern, $sql);531 foreach ($result as $m) {532 if ($m["submatches"][0] != "") {533 // first variant, no {}534 $matches[] = $m["submatches"][0];535 $tokens[] = $m["submatches"][1];536 $offsets[] = $m["offset"] + strpos($m["match"], $m["submatches"][0]);537 } else if ($m["submatches"][2] != "") {538 // second variant, in {}539 $matches[] = $m["match"];540 $tokens[] = $m["submatches"][2];541 $offsets[] = $m["offset"];542 } else if ($m["submatches"][3] != "") {543 // third variant, numeric like (:1, ':2')544 $matches[] = $m["submatches"][3];545 $tokens[] = $m["submatches"][4];546 $offsets[] = $m["offset"] + strpos($m["match"], $m["submatches"][3]);547 }548 }549 550 return array("tokens" => $tokens, "matches" => $matches, "offsets" => $offsets);551 }552 553 public static function scanNewTokenString($sql)554 {555 $tokens = array();556 $offsets = array();557 $matches = array();558 559 // match aaa, old.bbb, master.order date from:560 // insert into table values (':aaa', :old.bbb, ':{master.order date}')561 562 $pattern = "/\\\${[^\\s\{\\}]+}/";563 564 565 $result = findMatches($pattern, $sql);566 foreach ($result as $m) {567 $match = $m["match"];568 if ( $match != "" ) {569 $matches[] = $match;570 $tokens[] = substr( $match, 2, strlen( $match ) - 3 );571 $offsets[] = $m["offset"];572 }573 }574 575 return array("tokens" => $tokens, "matches" => $matches, "offsets" => $offsets);576 }577 578 579 public static function prepareNumberValue( $value )580 {581 $strvalue = str_replace( ",", ".", (string)$value );582 if( is_numeric($strvalue) )583 return $strvalue;584 return 0;585 }586 587 public static function Lookup( $sql ) {588 $result = DB::Query( $sql );589 if( !$result ) {590 return null;591 }592 $data = $result->fetchNumeric();593 if( !$data ) {594 return null;595 }596 return $data[0];597 }598 599 public static function DBLookup( $sql ) {600 return DB::Lookup( $sql );601 }602 603}604 605?>