/**
* sql insert statement
*
* @param $datasource
* @param $tablename
* @param $exclude_missing_tables
* @param $return if want return sql string, set true.
* @return string
*/
function getInsertSql($datasource, $tablename, $exclude_missing_tables = false, $return = false)
{
if (!$this->_checkCurrentDatasource($datasource)) {
$this->_setupDataSource();
}
if (!$return && (empty($this->File) || !$this->File->writable())) {
return false;
}
$tables = $this->_getProcessTables($tablename, $exclude_missing_tables);
$insert_sql = '';
foreach ($tables as $table => $fields) {
/* @var $model AppModel */
$model = ClassRegistry::init(array('class' => Inflector::classify($table), 'table' => $table));
$field_names = array_keys($this->DataSource->describe($model));
$full_tablename = $this->DataSource->fullTableName($model);
$all_fields = implode(', ', array_map(array($this->DataSource, 'name'), $field_names));
$count_query = array('table' => $full_tablename, 'fields' => 'count(*) ' . $this->DataSource->alias . 'count', 'alias' => $this->DataSource->alias . $this->DataSource->name($model->alias), 'joins' => '', 'conditions' => 'WHERE 1=1', 'group' => '', 'order' => '', 'limit' => '');
$count_sql = $this->DataSource->renderStatement('select', $count_query);
$total = $this->DataSource->fetchRow($count_sql);
if (is_array($total)) {
$total = $total[0]['count'];
}
$query = array('table' => $full_tablename, 'fields' => implode(', ', $this->DataSource->fields($model)), 'alias' => $this->DataSource->alias . $this->DataSource->name($model->alias), 'joins' => '', 'conditions' => '', 'group' => '', 'order' => '', 'limit' => '');
$limit = 100;
$record = array();
for ($offset = 0; $offset < $total; $offset += $limit) {
$query['limit'] = $this->DataSource->limit($limit, $offset);
$select_sql = $this->DataSource->renderStatement('select', $query);
$datas = $this->DataSource->fetchAll($select_sql, false);
foreach ($datas as $record) {
$insert_query = array('table' => $full_tablename, 'fields' => $all_fields, 'values' => implode(', ', array_map(array($this->DataSource, 'value'), array_values($record[$model->alias]))));
$_sql = $this->out($this->DataSource->renderStatement('create', $insert_query) . ';');
if ($return) {
$insert_sql .= $_sql;
}
}
}
// -- sequence update section for postgres
// NOTE: only primary key sequence..
if (method_exists($this->DataSource, 'getSequence')) {
foreach ($fields as $field => $column) {
if ($field == 'indexes' || empty($record)) {
continue;
}
if ($column['type'] == 'integer' && isset($column['key']) && $column['key'] == 'primary') {
// only primary key
$sequence_name = $this->DataSource->getSequence($this->DataSource->fullTableName($model, false), $field);
$_sql = $this->out(sprintf('SELECT setval(%s, %s);', $this->DataSource->value($sequence_name), $record[$model->alias][$field]));
if ($return) {
$insert_sql .= $_sql;
}
}
}
}
}
return $insert_sql;
}
public function testResetSequence()
{
$model = new Article();
$table = $this->Dbo->fullTableName($model, false);
$fields = array('id', 'user_id', 'title', 'body', 'published');
$values = array(array(1, 1, 'test', 'first post', false), array(2, 1, 'test 2', 'second post post', false));
$this->Dbo->insertMulti($table, $fields, $values);
$sequence = $this->Dbo->getSequence($table);
$result = $this->Dbo->rawQuery("SELECT nextval('{$sequence}')");
$original = $result->fetch(PDO::FETCH_ASSOC);
$this->assertTrue($this->Dbo->resetSequence($table, 'id'));
$result = $this->Dbo->rawQuery("SELECT currval('{$sequence}')");
$new = $result->fetch(PDO::FETCH_ASSOC);
$this->assertTrue($new['currval'] > $original['nextval'], 'Sequence did not update');
}