public function populateRecord($attributes, $callAfterFind = true) { $table = $this->getMetaData()->tableSchema; foreach ($table->columns as $column) { if (DataType::getInputType($column->dbType) != "file") { if (isset($attributes[$column->name])) { SqlUtil::FixValue($attributes[$column->name]); } } } return parent::populateRecord($attributes, $callAfterFind); }
/** * Exports all rows of the given array and writes the dump to the output buffer. * * @param array array with identifiers of rows */ private function exportRowData() { $db = Yii::app()->db; $pdo = $db->getPdoInstance(); // Columns $cols = Column::model()->findAllByAttributes(array('TABLE_NAME' => $this->table, 'TABLE_SCHEMA' => $this->schema)); $blobCols = array(); // Create insert statement if ($this->settings['completeInserts']) { $columns = array(); $i = 0; foreach ($cols as $col) { $columns[] = $db->quoteColumnName($col->COLUMN_NAME); if (in_array(DataType::getBaseType($col->DATA_TYPE), array('smallblob', 'blob', 'mediumblob', 'longblob'))) { $blobCols[] = $i; } $i++; } $columns = ' (' . implode(', ', $columns) . ')'; } else { $columns = ''; } $insert = $this->settings['insertCommand'] . ($this->settings['delayedInserts'] ? ' DELAYED' : '') . ($this->settings['ignoreInserts'] ? ' IGNORE' : '') . ' INTO ' . $db->quoteTableName($this->table) . $columns . ' VALUES'; // Find all rows $rowCount = count($this->rows); // Settings $hexBlobs = $this->settings['hexBlobs']; $rowsPerInsert = (int) $this->settings['rowsPerInsert']; // Cycle rows $i = 0; $k = 1; foreach ($this->rows as $row) { // Add comment if ($i == 0) { $this->comment('Data for table ' . $db->quoteTableName($this->table)); echo "\n\n"; echo $insert; } $attributes = $row->getAttributes(); SqlUtil::FixRow($attributes); // Escape all contents foreach ($attributes as $key => $value) { if ($value === null) { $attributes[$key] = 'NULL'; } elseif ($hexBlobs && in_array($key, $blobCols) && $value) { $attributes[$key] = '0x' . bin2hex($value); } else { $attributes[$key] = $pdo->quote($value); } } // Add this row echo "\n (", implode(', ', $attributes), ')'; if ($i == $rowCount - 1) { echo ";\n\n"; } elseif ($k == $rowsPerInsert) { echo ";\n\n", $insert; $k = 0; } else { echo ','; } $i++; $k++; } }
/** * Exports all rows of the given array and writes the dump to the output buffer. * * @param array array with identifiers of rows */ private function exportRowData() { $db = Yii::app()->db; $pdo = $db->getPdoInstance(); // Columns $cols = Column::model()->findAllByAttributes(array('TABLE_NAME' => $this->table, 'TABLE_SCHEMA' => $this->schema)); $blobCols = array(); $columns = array(); $i = 0; foreach ($cols as $col) { $columns[] = $db->quoteColumnName($col->COLUMN_NAME); if (in_array(DataType::getBaseType($col->DATA_TYPE), array('smallblob', 'blob', 'mediumblob', 'longblob'))) { $blobCols[] = $i; } $i++; } $columns = implode($this->settings['fieldTerminator'], $columns); $insert = ""; // Find all rows $rowCount = count($this->rows); // Settings $hexBlobs = $this->settings['hexBlobs']; $rowsPerInsert = (int) $this->settings['rowsPerInsert']; // Cycle rows $i = 0; $k = 1; foreach ($this->rows as $row) { if ($i == 0 && $this->settings['fieldsFirstRow']) { echo $columns; } $attributes = $row->getAttributes(); SqlUtil::FixRow($attributes); // Escape all contents foreach ($attributes as $key => $value) { if ($value === null) { $attributes[$key] = 'NULL'; } elseif ($hexBlobs && in_array($key, $blobCols) && $value) { $attributes[$key] = '0x' . bin2hex($value); } else { $attributes[$key] = $this->settings['fieldEncloseString'] . addcslashes($value, $this->settings['fieldEncloseString']) . $this->settings['fieldEncloseString']; } } echo "\n", implode($this->settings['fieldTerminator'], $attributes); if ($i == $rowCount - 1) { echo "\n\n"; } $i++; $k++; } }
public function run() { $response = new AjaxResponse(); $profiling = Yii::app()->user->settings->get('profiling'); try { $sqlQuery = new SqlQuery($this->query); } catch (SQPException $ex) { $response->addNotification('error', Yii::t('core', 'errorExecuteQuery'), $ex->getMessage()); $this->response = $response; return; } if (!$this->query) { $this->query = $this->getDefaultQuery(); $queries = (array) $this->query; } else { if ($profiling) { $cmd = $this->db->createCommand('FLUSH STATUS'); $cmd->execute(); $cmd = $this->db->createCommand('SET PROFILING = 1'); $cmd->execute(); } $splitter = new SqlSplitter($this->query); $queries = $splitter->getQueries(); } if ($this->execute) { $queryCount = count($queries); $i = 1; foreach ($queries as $query) { try { $sqlQuery = new SqlQuery($query); } catch (SQPException $ex) { $response->addNotification('error', Yii::t('core', 'errorExecuteQuery'), $ex->getMessage()); break; } $type = $sqlQuery->getType(); $this->table = $sqlQuery->getTable(); $this->tables = $sqlQuery->getTables(); $this->singleTableSelect = count($this->tables) == 1; // SELECT if ($type == "select") { // Pagination $pages = new Pagination(); $pages->route = $this->route; $pageSize = $pages->setupPageSize('pageSize', 'schema.table.browse'); // Sorting $sort = new Sort($this->db); $sort->multiSort = false; $sort->route = $this->route; $sqlQuery->applyCalculateFoundRows(); $limit = $sqlQuery->getLimit(); $order = $sqlQuery->getOrder(); // Apply sort if ($sort->getOrder()) { $sqlQuery->applySort($sort->getOrder(), true); } if (isset($_REQUEST['page'])) { $offset = $_REQUEST['page'] * $pageSize - $pageSize; $sqlQuery->applyLimit($pageSize, $offset, true); } // Set pagesize from query limit if ($limit && !isset($_REQUEST[$pages->pageSizeVar])) { $_REQUEST[$pages->pageSizeVar] = $limit['length']; $pageSize = $pages->setupPageSize('pageSize', 'schema.table.browse'); $offset = $limit['start']; } elseif (!$limit) { $offset = 0; $sqlQuery->applyLimit($pageSize, $offset, true); } elseif (isset($_REQUEST[$pages->pageSizeVar])) { $pageSize = $pages->setupPageSize('pageSize', 'schema.table.browse'); $offset = 0; $sqlQuery->applyLimit($pageSize, $offset, true); } $this->start = (int) $offset; } elseif ($type == "insert" || $type == "update" || $type == "delete") { #predie("insert / update / delete statement"); $response->refresh = true; } elseif ($type == "show") { // show create table etc. } elseif ($type == "explain") { } elseif ($type == "analyze" || $type == "optimize" || $type == "repair" || $type == "check") { // Table functions } elseif ($type == "use") { $name = $sqlQuery->getDatabase(); if ($queryCount == 1 && $name && $this->schema != $name) { $response->redirectUrl = Yii::app()->baseUrl . '/schema/' . $name . '#sql'; $response->addNotification('success', Yii::t('core', 'successChangeDatabase', array('{name}' => $name))); } } elseif ($type == "create") { $response->reload = true; } elseif ($type == "drop") { $response->reload = true; } $this->executedQueries[] = $sqlQuery->getQuery(); $this->originalQueries[] = $sqlQuery->getOriginalQuery(); if ($type == "select") { $pages->postVars = $sort->postVars = array('query' => $sqlQuery->getOriginalQuery()); } // Prepare query for execution $cmd = $this->db->createCommand($sqlQuery->getQuery()); $cmd->prepare(); if ($this->hasResultSet = $sqlQuery->returnsResultSet() !== false) { try { // Fetch data $start = microtime(true); $data = $cmd->queryAll(); $time = round(microtime(true) - $start, 6); SqlUtil::FixTable($data); if ($type == 'select') { $total = (int) $this->db->createCommand('SELECT FOUND_ROWS()')->queryScalar(); $pages->setItemCount($total); $this->total = $total; $keyData = array(); } $columns = array(); // Fetch column headers if (isset($data[0])) { $columns = array_keys($data[0]); } $isSent = true; $this->lastResultSetQuery = $sqlQuery->getOriginalQuery(); } catch (CDbException $ex) { $ex = new DbException($cmd); $response->addNotification('error', Yii::t('core', 'errorExecuteQuery'), $ex->getText(), $ex->getSql()); } } else { try { // Measure time $start = microtime(true); $result = $cmd->execute(); $time = round(microtime(true) - $start, 6); $response->addNotification('success', Yii::t('core', 'successExecuteQuery'), Yii::t('core', 'affectedRowsQueryTime', array($result, '{rows}' => $result, '{time}' => $time)), $sqlQuery->getQuery()); } catch (CDbException $ex) { $dbException = new DbException($cmd); $response->addNotification('error', Yii::t('core', 'errorExecuteQuery'), Yii::t('core', 'sqlErrorOccured', array('{errno}' => $dbException->getNumber(), '{errmsg}' => $dbException->getText()))); } } $i++; } if ($profiling) { $cmd = $this->db->createCommand('select state, SUM(duration) as total, COUNT(*) AS count FROM information_schema.profiling GROUP BY state ORDER by total desc'); $cmd->prepare(); $profileData = $cmd->queryAll(); if (count($profileData)) { $results = '<table class="profiling">'; foreach ($profileData as $item) { $results .= '<tr>'; $results .= '<td class="state">' . ucfirst($item['state']) . '</td>'; $results .= '<td class="time">' . $item['total'] . '</td>'; $results .= '<td class="count">(' . $item['count'] . ')</td>'; $results .= '</tr>'; } $results .= '</table>'; $response->addNotification('info', Yii::t('core', 'profilingResultsSortedByExecutionTime'), $results, null); } } else { if (isset($total) && isset($time)) { $response->addNotification('success', Yii::t('core', 'successExecuteQuery'), Yii::t('core', 'foundRowsQueryTime', array(null, '{rows}' => $total, '{time}' => $time)), $sqlQuery->getQuery()); } } } // Assign local variables to class properties if (isset($pages)) { $this->pagination = $pages; } if (isset($sort)) { $this->sort = $sort; } if (isset($type)) { $this->queryType = $type; } if (isset($columns)) { $this->columns = $columns; foreach ($this->columns as $column) { if ($this->getColumn($column) == null) { $this->singleTableSelect = false; break; } } } if (isset($data)) { $this->data = $data; } if (isset($response)) { $this->response = $response; } }
public function getChildLevelData() { $filterPageSort = null; $parentId = $_GET["parent_id"]; if (!isset($parentId)) { $parentId = FALSE; } if (isset($_GET["filterPageSort"])) { $filterPageSort = json_decode((string) $_GET["filterPageSort"]); } else { $filterPageSort = FALSE; } $response = new Response(); if (!$parentId) { $response->success = false; $response->data = null; $response->message = "For Child data parentId is must."; return $response; } $query = "SELECT * FROM " . DB::$tableName; if ($filterPageSort != FALSE) { if (property_exists($filterPageSort, "filters")) { $query .= SqlUtil::buildFilterQuery($filterPageSort->filters); $query .= " AND record_id = " . $parentId; } else { $query .= " WHERE record_id = " . $parentId; } if (property_exists($filterPageSort, "sorts")) { $query .= SqlUtil::buildSortQuery($filterPageSort->sorts); } } else { $query .= " WHERE record_id = " . $parentId; } $conn = DB::getConnection(); $result = $conn->query($query); $responseData = array(); if ($result->num_rows > 0) { while ($row = $result->fetch_assoc()) { $serverRecord = new ServerRecord(); $serverRecord->copyFrom($row); array_push($responseData, $serverRecord); } } $conn->close(); $response->data = $responseData; $response->message = "Data Fetched Successfully.."; $response->success = true; return $response; }