/**
*/
private function _processDdl()
{
// this will turn off buffering,
// so we see outout right away
while (ob_get_level() > 0) {
ob_end_flush();
}
/*
*
* $table = new \Zend\Db\Sql\Ddl\CreateTable ( 'keyper_bar' );
*
* $table->addColumn ( new \Zend\Db\Sql\Ddl\Column\Integer ( 'id' ) );
* $table->addColumn ( new \Zend\Db\Sql\Ddl\Column\Varchar ( 'name', 200 ) );
* $table->addConstraint ( new \Zend\Db\Sql\Ddl\Constraint\PrimaryKey ( 'id' ) );
*
* $sql = new \Zend\Db\Sql\Sql ( $this->adapter );
*
* $this->adapter->query ( $sql->getSqlStringForSqlObject ( $table ), \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE );
*/
echo "<div>";
try {
require_once DOC_ROOT . "/config/schema/ddl.php";
foreach ($schema as $key => $value) {
$this->validateTable($key);
$this->createTable($key, $prefix);
// create table
$this->createColumn("id", "integer", 11, true);
if (count($value['associate'])) {
foreach ($value['associate'] as $key_3 => $value_3) {
$this->table->addConstraint(new \Zend\Db\Sql\Ddl\Constraint\ForeignKey($value_3, $value_3, $prefix . $key_3, "id", "cascade", "cascade"));
}
}
// create columns
foreach ($value['columns'] as $key_2 => $value_2) {
$col = $this->validateColumn($value_2[0]);
$data_type = $this->validateDataType($value_2[1]['data_type'][0]);
$data_type_param = $data_type == "varchar" ? 200 : $value_2[1]['data_type'][1];
$this->createColumn($col, $data_type, $data_type_param, false);
}
// execute now
$sql = new \Zend\Db\Sql\Sql($this->adapter);
echo $sql->getSqlStringForSqlObject($this->table);
echo "<br>";
$this->adapter->query($sql->getSqlStringForSqlObject($this->table), \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);
// create physical file now
$this->generateClassFile($key, $value);
}
} catch (\Exception $e) {
echo "<span class=''>{$e->getMessage()}</span>";
}
echo "</div>";
}
/**
*
* @param unknown $schema
*/
private function dropDb()
{
$metadata = new \Zend\Db\Metadata\Metadata($this->adapter);
// get the table names
$tableNames = $metadata->getTableNames();
$this->adapter->query('SET foreign_key_checks = 0')->execute();
foreach ($tableNames as $tableName) {
$sql = new \Zend\Db\Sql\Sql($this->adapter);
$drop = new \Zend\Db\Sql\Ddl\DropTable($tableName);
@$this->adapter->query($sql->getSqlStringForSqlObject($drop), \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);
}
$this->adapter->query('SET foreign_key_checks = 1')->execute();
}
public function search($nome)
{
// preparar objeto SQL
$adapter = $this->tableGateway->getAdapter();
$sql = new \Zend\Db\Sql\Sql($adapter);
// montagem do select com where, like e limit para tabela contatos
$select = (new \Zend\Db\Sql\Select('contatos'))->limit(8);
$select->columns(array('id', 'nome'))->where->like('nome', "%{$nome}%");
// executar select
$statement = $sql->getSqlStringForSqlObject($select);
$results = $adapter->query($statement, $adapter::QUERY_MODE_EXECUTE);
return $results;
}
public function getEmailByCredential($credential)
{
$adapter = $this->adapter;
$sql = new \Zend\Db\Sql\Sql($adapter);
$select = $sql->select();
$select->from('user');
$select->where(sprintf('username = "******" OR email = "%s"', $credential, $credential));
//$select->where(function (\Zend\Db\Sql\Where $where) { $where->greaterThanOrEqualTo('ts.cfp_closed', date('Y-m-d H:i:s'));});
$selectString = $sql->getSqlStringForSqlObject($select);
$results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
if ($results->count() != 1) {
return false;
}
return $results->current()->email;
}
public function indexAction()
{
//默认Action
//test db library
$dbObj = new Db_Table('test', 'user');
$res = $dbObj->getCount('id=3');
var_dump($res);
$field = array();
$whereArr = array('order' => 'id', 'limit' => '3');
$res = $dbObj->getAll($field, $whereArr);
var_dump($res);
echo "</br>";
//test smarty library
$this->getView()->assign("contentaa", "Hello Worldssss");
//test zendDb library
$adapter = Yaf_Registry::get('db');
//use adapter
// $qi = function ($name) use ($adapter) {
// return $adapter->platform->quoteIdentifier($name);
// };
// $fp = function ($name) use ($adapter) {
// return $adapter->driver->formatParameterName($name);
// };
// $sql = 'select * from ' . $qi('usertest') . ' where' . $qi('user_id') . ' <' . $fp('user_id');
// $statement = $adapter->query($sql);
// $results = $statement->execute(array(
// 'user_id' => 5
// ));
// //$row = $results->current();
// $resultSet = new Zend\Db\ResultSet\ResultSet;
// $resultSet->initialize($results);
// var_dump($results->toArray());
//use sql
$sql = new Zend\Db\Sql\Sql($adapter);
$select = $sql->select();
$select->from('usertest');
$select->where(array('user_id <3'));
$selectString = $sql->getSqlStringForSqlObject($select);
$results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
// var_dump($results);
var_dump($results->toArray());
echo "</br>";
// var_dump($results);
// $resultSet = new Zend\Db\ResultSet\ResultSet;
// $resultSet->initialize($results);
}
