User: Alexandr
Date: 29.09.10
public function index()
{
$bDrop = $this->uri->segment(3);
$aTableNames = modelsToTableNames();
foreach ($aTableNames as $k => $sTableName) {
$oSqlBuilder = new SqlBuilder();
//** Build tables
$oTableName = new $k();
$sCreateSql = $oSqlBuilder->setTableName($sTableName)->getCreateTableString($oTableName->db);
$this->db->query('FLUSH TABLES;');
if ($bDrop) {
$this->db->query($oSqlBuilder->getDropTableString());
}
if (!tableExists($sTableName)) {
$this->db->query($sCreateSql);
}
//** Add in any default data
foreach ($oSqlBuilder->getDefaultDataArray($oTableName->data) as $v) {
//$oTableName->
$this->db->query($v);
}
}
foreach ($aTableNames as $k => $sTableName) {
$oTableName = new $k();
//** Make forms
if (!empty($oTableName->form)) {
$oFormBuilder = new FormBuilder();
$sForm = $oFormBuilder->setTableName($sTableName)->setFormName($sTableName)->setFormData($oTableName->form)->getFormString();
createFile('views/admin/includes/forms/' . $sTableName . '_form.php', $sForm);
}
}
}
public function edit()
{
$postBuilder = new SqlBuilder();
$post = $postBuilder->from('post')->with(array('status' => array('status_name', 'label', 'id', 'status')))->where(array('id = ' . $_GET['id']))->limit(1)->query();
$tagsBuilder = new SqlBuilder();
$tags = $tagsBuilder->select(array('tag'))->from('tag')->where(array('post_id = ' . $post->id))->query();
$this->render('post/edit', array('post' => $post, 'tags' => $tags));
}
/**
* undocumented function
*
* @return void
* @author Justin Palmer
**/
public function findAll($forceSet = true)
{
$database_name = $this->model->database_name();
$table_name = $this->model->table_name();
$query = $this->builder->build("SELECT ? FROM `{$database_name}`.`{$table_name}`");
$this->builder->reset();
$this->Statement = $this->prepare(array_shift($query->query));
$this->Statement->execute(array_values($query->params));
return ResultFactory::factory($this->Statement, $forceSet);
}
public function __construct(Connection $conn, BaseBuilder $builder = null, Logger $logger = null)
{
$this->conn = $conn;
$this->queryDriver = $conn->createQueryDriver();
if (!$builder) {
$builder = SqlBuilder::create($this->queryDriver);
}
$this->builder = $builder;
if (!$logger) {
$c = ServiceContainer::getInstance();
$logger ?: $c['logger'];
}
$this->logger = $logger;
}
public function getRow($field = '*', $where = array(), $order = array(), $offset = 0)
{
// 校验参数
if (!is_string($field) || empty($field)) {
throw new LibraryException('参数错误：$field');
}
if (!is_array($where)) {
throw new LibraryException('参数错误：$where');
}
if (!is_array($order)) {
throw new LibraryException('参数错误：$order');
}
if (!is_numeric($offset) || intval($offset) != $offset || $offset < 0) {
throw new LibraryException('参数错误：$offset');
}
$sql = $this->sqlBuilder->createSelectSql($field, $where, $order, 1, $offset);
$row = $this->slaveHandle->queryRow($sql);
return $row;
}
/**
* @request_handler
* @return array
*/
public function enter($params)
{
$fields = array_fill_keys(array('email', 'passwd'), '');
$post = array_intersect_key(array_merge($fields, $_POST), $fields);
$message = '';
$db = DB::getInstance();
$q_user = $db->query(SqlBuilder::newQuery()->from('user')->select('*')->where('email', $db->quote($post['email']))->limit(1)->getSql());
if ($q_user->rowCount() == 0) {
$message = _('Entered email not registered');
} else {
$user = $q_user->fetch(PDO::FETCH_ASSOC);
if ($user['passwd'] != $post['passwd']) {
$message = _('Incorrect password');
} else {
empty($user['name']) and $user['name'] = $user['email'];
User::setAuth($user);
}
}
return array('data' => array('message' => $message, 'form' => $post), 'redirect' => empty($message) ? 'profile' : 'login');
}
/**
* Wraps an SQL query.
*
* @param array $aColumns
* @param array $aTables
* @param array $aLimitations
* @param array $aGroupColumns
* @param string $primaryKey
* @param array $aLeftJoinedTables
* @return integer The number of rows affected by the query
*/
function _select($aColumns, $aTables, $aLimitations, $aGroupColumns, $primaryKey, $aLeftJoinedTables = null)
{
$conf = $GLOBALS['_MAX']['CONF'];
$oDbh = OA_DB::singleton();
$columns = '';
if (is_array($aColumns)) {
foreach ($aColumns as $column => $alias) {
$columns .= $columns == '' ? "SELECT {$column} AS {$alias}" : ",{$column} AS {$alias}";
}
} else {
$columns = "SELECT {$aColumns}";
}
$prev_aliases = array();
$tables = '';
if (is_array($aTables)) {
$x = 0;
if (!is_null($aLeftJoinedTables)) {
$aTables = array_diff($aTables, $aLeftJoinedTables);
}
$joinType = 'INNER';
while (count($aTables) && $x < 50) {
$x++;
foreach ($aTables as $tableKey => $alias) {
$table = $tableKey;
// check for prefix
if (!empty($conf['table']['prefix']) && strpos($table, $conf['table']['prefix']) != 0) {
$table = $conf['table']['prefix'] . $table;
}
$qTable = $oDbh->quoteIdentifier($table, true);
$joinLimitation = '';
if (count($prev_aliases)) {
if (is_array($aLimitations)) {
foreach ($aLimitations as $limitationKey => $limitation) {
if (preg_match("/({$alias}\\.[a-z0-9_]+ *= *(" . join('|', $prev_aliases) . ")\\..+|(" . join('|', $prev_aliases) . ")\\.[a-z0-9_]+ *= *{$alias}\\..+)/", $limitation)) {
$joinLimitation = $limitation;
unset($aLimitations[$limitationKey]);
break;
}
}
}
} else {
$tables .= " FROM {$qTable} AS {$alias}";
}
if ($joinLimitation) {
$tables .= " {$joinType} JOIN {$qTable} AS {$alias} ON ({$joinLimitation})";
} elseif (count($prev_aliases)) {
continue;
}
$prev_aliases[] = $alias;
unset($aTables[$tableKey]);
}
if (!is_null($aLeftJoinedTables) && !count($aTables)) {
$aTables = $aLeftJoinedTables;
$aLeftJoinedTables = null;
$joinType = 'LEFT';
}
}
} else {
$tables = "FROM " . $oDbh->quoteIdentifier($aTables, true);
}
$where = '';
if (is_array($aLimitations)) {
foreach ($aLimitations as $limitation) {
$where .= $where == '' ? " WHERE {$limitation}" : " AND {$limitation}";
}
} else {
$where = " WHERE {$aLimitations}";
}
$group = '';
if (is_array($aGroupColumns) && count($aGroupColumns) > 0) {
$group = ' GROUP BY ' . implode(',', $aGroupColumns);
}
$query = $columns . $tables . $where . $group;
//var_dump($query);
return SqlBuilder::_query($query, $primaryKey);
}
/**
* @param array $aParams
* @param bool $allFields
* @return mixed 0 if no rows affected, true on success, false otherwise
*/
function deletePlacementZones($aParams, $allFields = false)
{
$result = SqlBuilder::_doDelete('placement_zone_assoc', $aParams);
// Unlink any ads in the campaign that are linked to the zone.
$pAds = Admin_DA::getAds(array('placement_id' => $aParams['placement_id']));
foreach ($pAds as $adId => $pAd) {
SqlBuilder::_doDelete('ad_zone_assoc', array('zone_id' => $aParams['zone_id'], 'ad_id' => $adId));
}
return $result;
}
protected static function construct_calculation_sql($class_name, $column_name, $operation, &$options)
{
$columns = self::$connection->columns(self::get_table_name($class_name));
if (isset($columns[$column_name])) {
$column_name = self::quoted_table_name($class_name) . '.' . $column_name;
}
$sql = 'SELECT ' . $operation . '(' . (isset($options['distinct']) ? 'DISTINCT ' : '') . $column_name . ') ' . 'FROM ' . (isset($options['from']) ? $options['from'] : self::quoted_table_name($class_name));
SqlBuilder::add_sql_trail($sql, $options);
return $sql;
}
/**
* @see SqlBuilder::getRawTable
* @return string
*/
function getRawTable()
{
return $this->sqlBuilder->getRawTable();
}
/**
* @request_handler
* @return array
*/
public function download($params)
{
empty($params['name']) and Template::show404Page();
$db = DB::getInstance();
$file = $db->query(SqlBuilder::newQuery()->from('file')->select('*')->where('file_name', $db->quote($params['name']))->limit(1)->getSql())->fetch() or Template::show404Page();
$dir = rtrim(Config::getConfig('repository'), '\\/') . DIRECTORY_SEPARATOR;
ob_end_clean();
header("Content-Type: {$file['type']}");
header("Accept-Ranges: bytes");
header("Content-Length: {$file['size']}");
header("Content-Disposition: attachment; filename={$file['original_name']}");
header("Content-Transfer-Encoding: binary");
readfile($dir . $file['file_name']);
}
/**
* Return a SqlBuilder object which has set the table and optionally
* assigned values to columns based on this instances' properties. This is used in
* insert(), update(), and delete()
*
* @param ModelDescriptor $descriptor
* @param boolean $useAssignment
* @param boolean $excludePrimaryKey
* @return SqlBuilder
*/
protected function assignmentSqlForThisObject(ModelDescriptor $descriptor, $useAssignment = true, $excludePrimaryKey = false)
{
$sqlBuilder = new SqlBuilder();
$sqlBuilder->from($descriptor->getTable());
if (empty($descriptor->columns)) {
throw new RecessException('The "' . $descriptor->getTable() . '" table does not appear to exist in your database.', get_defined_vars());
}
foreach ($this as $column => $value) {
if ($excludePrimaryKey && $descriptor->primaryKey == $column) {
continue;
}
if (in_array($column, $descriptor->columns) && isset($value)) {
if ($useAssignment) {
$sqlBuilder->assign($column, $value);
} else {
$sqlBuilder->equal($column, $value);
}
}
}
return $sqlBuilder;
}
function test_insert()
{
$aVariables = array('affiliateid' => 23, 'zonename' => 'foo', 'description' => 'this is the desc', 'category' => 0, 'ad_selection' => 0, 'chain' => 0, 'prepend' => 0, 'append' => 0, 'updated' => '2007-04-03 16:41:15');
$ret = SqlBuilder::_insert('zones', $aVariables);
$this->assertTrue(is_int($ret));
$this->assertTrue($ret > 0);
}
function getStatementForBuilder(SqlBuilder $builder, $action, PdoDataSource $source)
{
$criteria = $builder->getCriteria();
$builderTable = $builder->getTable();
$tableDescriptors = array();
foreach ($criteria as $criterion) {
$table = $builderTable;
$column = $criterion->column;
if (strpos($column, '.') !== false) {
$parts = explode('.', $column);
$table = $parts[0];
$column = $parts[1];
}
if (!isset($tableDescriptors[$table])) {
$tableDescriptors[$table] = $source->getTableDescriptor($table)->getColumns();
}
if (isset($tableDescriptors[$table][$column])) {
switch ($tableDescriptors[$table][$column]->type) {
case RecessType::DATETIME:
case RecessType::TIMESTAMP:
if (is_int($criterion->value)) {
$criterion->value = date('Y-m-d H:i:s', $criterion->value);
} else {
$criterion->value = null;
}
break;
case RecessType::DATE:
$criterion->value = date('Y-m-d', $criterion->value);
break;
case RecessType::TIME:
$criterion->value = date('H:i:s', $criterion->value);
break;
case RecessType::BOOLEAN:
$criterion->value = $criterion->value == true ? 1 : 0;
break;
case RecessType::INTEGER:
if (is_array($criterion->value)) {
break;
} else {
if (is_numeric($criterion->value)) {
$criterion->value = (int) $criterion->value;
} else {
$criterion->value = null;
}
}
break;
case RecessType::FLOAT:
if (!is_numeric($criterion->value)) {
$criterion->value = null;
}
break;
}
}
}
$sql = $builder->{$action}();
$statement = $source->prepare($sql);
$arguments = $builder->getPdoArguments();
foreach ($arguments as &$argument) {
// Begin workaround for PDO's poor numeric binding
$param = $argument->getQueryParameter();
if (is_numeric($param)) {
continue;
}
if (is_string($param) && strlen($param) > 0 && substr($param, 0, 1) !== ':') {
continue;
}
// End Workaround
// Ignore parameters that aren't used in this $action (i.e. assignments in select)
if ('' === $param || strpos($sql, $param) === false) {
continue;
}
$statement->bindValue($param, $argument->value);
}
return $statement;
}
function onDeleteNullify(Model $model)
{
if (isset($this->through)) {
return $this->onDeleteDelete($model);
}
$modelPk = Model::primaryKeyName($model);
$queryBuilder = new SqlBuilder();
$queryBuilder->from(Model::tableFor($this->foreignClass))->assign($this->foreignKey, null)->equal($this->foreignKey, $model->{$modelPk});
$source = Model::sourceFor($model);
$source->executeStatement($queryBuilder->update(), $queryBuilder->getPdoArguments());
}
}
// Open the database connection
$Connection = false;
if ($WarningCollector->Count() == 0) {
$Connection = @mysql_connect($DBHost, $DBUser, $DBPass);
if (!$Connection) {
$WarningCollector->Add("We couldn't connect to the server you provided (" . $DBHost . "). Are you sure you entered the right server, username and password?");
} elseif (!mysql_select_db($DBName, $Connection)) {
$WarningCollector->Add("We connected to the server, but we couldn't access the \"" . $DBName . "\" database. Are you sure it exists and that the specified user has access to it?");
}
}
// Create the administrative user
if ($WarningCollector->Count() == 0 && $Connection) {
$Username = FormatStringForDatabaseInput($Username);
$Password = FormatStringForDatabaseInput($Password);
$s = new SqlBuilder($Context);
$s->SetMainTable("User", "u");
$s->AddFieldNameValue("FirstName", "Administrative");
$s->AddFieldNameValue("LastName", "User");
$s->AddFieldNameValue("Email", FormatStringForDatabaseInput($SupportEmail));
$s->AddFieldNameValue("Name", $Username);
$s->AddFieldNameValue("Password", $Password, 1, "md5");
$s->AddFieldNameValue("DateFirstVisit", MysqlDateTime());
$s->AddFieldNameValue("DateLastActive", MysqlDateTime());
$s->AddFieldNameValue("CountVisit", 0);
$s->AddFieldNameValue("CountDiscussions", 0);
$s->AddFieldNameValue("CountComments", 0);
$s->AddFieldNameValue("RoleID", 6);
$s->AddFieldNameValue("StyleID", 1);
$s->AddFieldNameValue("UtilizeEmail", 0);
$s->AddFieldNameValue("RemoteIP", GetRemoteIp(1));
$DBPass = $Configuration['DATABASE_PASSWORD'];
$Connection = @mysql_connect($DBHost, $DBUser, $DBPass);
if (!$Connection) {
$Context->WarningCollector->Add('We couldn’t connect to the server you provided (' . $DBHost . '). Are you sure you entered the right server, username and password?');
} elseif (!mysql_select_db($DBName, $Connection)) {
$Context->WarningCollector->Add('We connected to the server, but we couldn’t access the \'' . $DBName . '\' database. Are you sure it exists and that the specified user has access to it?');
} elseif (version_compare(mysql_get_server_info($Connection), '4.1.0', '>=')) {
/* On MySQL 4.1 and later, force UTF-8 */
mysql_query('SET NAMES "utf8"', $Connection);
}
}
// Create the administrative user
if ($Context->WarningCollector->Count() == 0 && $Connection) {
$Username = FormatStringForDatabaseInput($Username);
$Password = FormatStringForDatabaseInput($Password);
$s = new SqlBuilder($Context);
$s->SetMainTable('User', 'u');
$s->AddFieldNameValue('FirstName', 'Administrative');
$s->AddFieldNameValue('LastName', 'User');
$s->AddFieldNameValue('Email', FormatStringForDatabaseInput($SupportEmail));
$s->AddFieldNameValue('Name', $Username);
$s->AddFieldNameValue('Password', $Password, 1, 'md5');
$s->AddFieldNameValue('DateFirstVisit', MysqlDateTime());
$s->AddFieldNameValue('DateLastActive', MysqlDateTime());
$s->AddFieldNameValue('CountVisit', 0);
$s->AddFieldNameValue('CountDiscussions', 0);
$s->AddFieldNameValue('CountComments', 0);
$s->AddFieldNameValue('RoleID', 4);
$s->AddFieldNameValue('StyleID', 1);
$s->AddFieldNameValue('UtilizeEmail', 0);
$s->AddFieldNameValue('RemoteIp', GetRemoteIp(1));
public function getMorphinxFieldName($field)
{
$table = $this->getMorphinxTableForField($field);
if (is_null($table)) {
return $field;
}
return str_replace('`', '', SqlBuilder::selectAlias($table, $field));
}
protected static function attribute_condition($quoted_column_name, $value)
{
if ($value === null) {
return $quoted_column_name . ' IS NULL';
} else {
if (is_array($value)) {
return SqlBuilder::sanitize_sql($quoted_column_name . ' IN (?)', $value);
} else {
return SqlBuilder::sanitize_sql($quoted_column_name . ' = ?', $value);
}
}
}
function getStatementForBuilder(SqlBuilder $builder, $action, PdoDataSource $source)
{
$sql = $builder->{$action}();
$statement = $source->prepare($sql);
$arguments = $builder->getPdoArguments();
foreach ($arguments as &$argument) {
// Begin workaround for PDO's poor numeric binding
$param = $argument->getQueryParameter();
if (is_numeric($param)) {
continue;
}
if (is_string($param) && strlen($param) > 0 && substr($param, 0, 1) !== ':') {
continue;
}
// End Workaround
// Ignore parameters that aren't used in this $action (i.e. assignments in select)
if ('' === $param || strpos($sql, $param) === false) {
continue;
}
$statement->bindValue($param, $argument->value);
}
return $statement;
}
/**
* createInsertSql
*
* build the insert sql query for the model and return it
*
* @access private
* @return string
*/
private function createInsertSql()
{
$to_insert = $this->fieldsToInsert();
return "insert into `" . $this->_table . "` " . SqlBuilder::set($to_insert);
}
/**
* Deletes all rows in result set.
* @return int number of affected rows or FALSE in case of an error
*/
public function delete()
{
return $this->query($this->sqlBuilder->buildDeleteQuery())->rowCount();
}
/**
* Return the SQL representation of this PdoDataSet
*
* @return string
*/
public function toSql()
{
return $this->sqlBuilder->select();
}
/**
* @todo cache the result
* @param boolean $with_limit
* @return integer
*/
public function get_count($with_limit = false)
{
$limit_stmt = $with_limit ? SqlBuilder::order_by($this->order, $this->order_dir) . SqlBuilder::limit($this->offset, $this->limit) : '';
$rs = SqlTools::sqlQuery("SELECT count(1)\n \nFROM " . SqlBuilder::from($this->get_from_tables()) . SqlBuilder::joins($this->joins, $this->getJoinType()) . SqlBuilder::where($this->where, $this->sql_where) . $limit_stmt);
if ($rs) {
return (int) mysql_result($rs, 0);
} else {
throw new Exception("Fatal error:" . mysql_error());
}
}
private function load()
{
if ($this->id == NULL || $this->loaded) {
return false;
}
$queryArgs = [];
$query = SqlBuilder::buildSelectQuery($this->table, '*', ['id' => $this->id], $queryArgs);
$params = self::execute($query, $queryArgs, 'single');
foreach ($params as $column => $value) {
$this->fields[$column] = $value;
}
$this->modified = false;
$this->loaded = true;
return true;
}
protected function prepare_conditions(&$options)
{
$conditions = '';
if (isset($options['conditions'])) {
$conditions .= '(' . SqlBuilder::sanitize_sql_for_conditions($options['conditions']) . ')';
}
if (isset($this->association_options['conditions'])) {
if (!empty($conditions)) {
$conditions .= ' AND ';
}
$conditions .= '(' . SqlBuilder::sanitize_sql_for_conditions($this->association_options['conditions']) . ')';
}
$options['conditions'] = $conditions;
}
