Класс обертки Mysqli by Koenig (Допилить)

Koenig

10 апр 2015 г., 9:41

(\/)____o_O____(\/)

# ramzes (10.04.2015 / 01:41)
з.ы. лучшее что выдал гугл по запросу "orm идеология"
http://geektimes.ru/post/126812/
примечательно)

интересно, я именно так и считал, многие начинают не с того конца, тупо по примерам лепят, по мне надо изнутри все понять, недавно только начал понимать как мвц работает, теперь можно свое писать гг

ramzes

10 апр 2015 г., 14:06

плюнул, начал с нуля)
теперь буду думать как джоины организовать)

microapp::orm()
    ->info('id', '>', 10)
    ->or('name', 'ramzes')
    ->find();
microapp::orm()
    ->user('level', 3)
     ->order('id')
     ->find(5, 20);

Koenig

17 апр 2015 г., 6:09

Изменено

(\/)____o_O____(\/)

набросок
еще доку пилить, автоматику запилить, плюс кое чего пересмотреть

быдлокодус (+/-)

<?php

trait KsqlQueries {
    
    public $pre = false;
    public $post = false;
        
    public function select($fieldsListArray = '*') {
        $this->pre = 'SELECT ' . (is_array($fieldsListArray) ? $this->concat(array_map(array($this, 'art'), $fieldsListArray)) : $fieldsListArray) . ' FROM ';
        
        return $this;
    }
    
    public function count($field = '*') {
        $this->pre = 'SELECT COUNT(' . $this->art($field) . ') FROM ';
        
        return $this;
    }
    
    public function delete() {
        $this->pre = 'DELETE FROM ';
       
        return $this;
    }
    
    public function insert($fieldsListArray, $values) {
        $this->pre = 'INSERT INTO ';
        $this->post = '(' . $this->concat(array_map(array($this, 'art'), $fieldsListArray)) . ') VALUES (' . implode(', ', array_fill(0, count($values), '?')) . ')';
        foreach($values as $value) {
            $this->placeholders[] = $this->esc($value);
        }
        
        return $this;
    }
    
    public function update($data) {
        $this->pre = 'UPDATE ';
        $this->post = 'SET ';
        $i = 0;
        foreach($data as $field => $value) {
            $this->post .= ($i++ > 0 ? ', ' : '') . $this->art($field) . ' = ?';
            $this->placeholders[] = $this->esc($value);
        }        
                
        return $this;
    }
}

trait KsqlArgs {
    
    public $table = array();
    public $on = array();
    public $limit;
    public $offset = 0;
    public $where = array();
    public $order = array();
    public $group = array();
    
    public function table($table) {
        $this->table[] = $table;
        
        return $this;
    }
    
    public function join($table) {
        return $this->table($table);
    }
    
    public function on() {
        if (func_num_args() == 2) {
            $this->on[] = $this->art(func_get_arg(0)) . ' = ' . $this->art(func_get_arg(1));
        } elseif (is_array($array)) {
            foreach($array as $k => $on) {
                $this->on[] = $this->art($on[0]) . ' = ' . $this->art($on[1]);
            }
        }
        
        return $this;
    }
    
    public function where() {
        if (func_num_args() == 3) {
            $this->where[] = $this->art(func_get_arg(0)) . ' ' . func_get_arg(1) . ' ?';
            $this->placeholders[] = $this->esc(func_get_arg(2));
        } elseif (is_array(func_get_arg(0))) {
            foreach($array as $k => $where) {
                $this->where[] = $this->art($where[0]) . ' ' . $where[1] . ' ?';
                $this->placeholders[] = $this->esc($where[2]);
            }
        }
        
        return $this;
    }
    
    //
    # прочие Where
    //
    
    public function group($field) {
        $this->group[] = $this->art($field);
        
        return $this;
    }
    
    public function order($field, $type = 'asc') {
        $type = in_array($type, array('asc', 'desc')) ? $type : 'asc';
        $this->order[] = $this->art($field) . ' ' . strtoupper($type);
        
        return $this;
    }
    
    public function limit() {
        if (func_num_args() == 2) {
            $this->offset = '?';
            $this->placeholders[] = $this->esc(func_get_arg(0));
            $this->limit = '?';
            $this->placeholders[] = $this->esc(func_get_arg(1));
        } else {
            $this->limit = '?';
            $this->placeholders[] = $this->esc(func_get_arg(0));
        }
        
        return $this;
    }
}

trait KsqlUtils {
    
    public function concat($array, $sep = 'comma') {
        $types = array('comma' => ', ', 1 => ' AND ');
        return implode($types[$sep], $array);
    }
    
    public function art($args) {
        if (!is_array($args)) {
            return '`' . str_replace(array('.', ' = '), array('`.`', '` = `'), str_replace('`', '', $args)) . '`';
        } else {
            return array_map(array($this, 'art'), $args);
        }
    }
    
    public function esc($data) {
        if (!is_array($data)) {
            return is_int($data) ? $data : '"' . $this->real_escape_string($data) . '"';
        } else {
            return array_map(array($this, 'esc'), $data);
        }
    }
    
    public function reset() {
        $this->sql = false;
        $this->table = array();
        $this->on = array();
        $this->limit = null;
        $this->offset = 0;
        $this->where = array();
        $this->order = array();
        $this->group = array();
        $this->pre = false;
        $this->post = false;
        $this->placeholders = false;
    }

}

class KMysqli extends Mysqli {
    use KsqlUtils, KsqlArgs, KsqlQueries;
    
    public $sql;
    
        public function __construct($db_host, $db_user, $db_pass, $db_name, $db_charset, $port = 3306) {
        $driver = new mysqli_driver();
        $driver->report_mode = MYSQLI_REPORT_STRICT;
        try {
            parent::__construct($db_host, $db_user, $db_pass, $db_name, $port);
            $this->set_charset($db_charset);
        }
        catch(mysqli_sql_exception $e) {
            throw $e;
        }
    }
    
    //
    # пересмотреть старый класс
    //
    
    public function sqlBuild() {
        if (!$this->pre) {
            return false;
        } else {
            $this->sql = $this->pre;
            $this->sql .= ((sizeof($this->table) > 1) ? implode(' JOIN ', $this->art($this->table)) : $this->art(array_shift($this->table))) . ' ';
            $this->sql .= ($this->post ? $this->post . ' ' : '');
            $this->sql .= ((sizeof($this->on) > 0) ? $this->concat($this->on, 1) . ' ' : '');
            $this->sql .= ((sizeof($this->where) > 0) ? 'WHERE ' . $this->concat($this->where, 1) . ' ' : '');
            $this->sql .= ((sizeof($this->group) > 0) ? 'GROUP BY ' . $this->concat($this->group, 1) . ' ' : '');
            $this->sql .= ((sizeof($this->order) > 0) ? 'ORDER BY ' . $this->concat($this->order, 1) . ' ' : '');
            $this->sql .= ($this->limit ? 'LIMIT ?, ? ;' : ';');
        
            return $this;
        }
    }
    
    public function test() {
        return '<pre>' . print_r($this->sql, 1) . '</pre>' . '<pre>' . print_r($this->placeholders, 1) . '</pre>';
    }
}

$db_host = '127.0.0.1'; 
$db_user = 'root'; 
$db_pass = ''; 
$db_name = 'test'; 
$db_charset = 'utf8';

$mysqli = new KMysqli($db_host, $db_user, $db_pass, $db_name, $db_charset);

$mysqli->select(array('users.name', 'users.id', 'forum_themes.name', 'forum_themes.id', 'forum_posts.text', 'forum_posts.time'))
    ->table('forum_posts')->join('forum_themes')->join('users')
    # это так же будет работать
   #  ->on('forum_posts.author', 'users.id')->on('forum_themes.id', 'forum_posts.tid') 
    ->on(array(array('forum_posts.author', 'users.id'), array('forum_themes.id', 'forum_posts.tid')))
    ->limit(1);
    
# SELECT `users`.`name`, `users`.`id`, `forum_themes`.`name`, `forum_themes`.`id`, `forum_posts`.`text`, `forum_posts`.`time` FROM `forum_posts` JOIN `forum_themes` JOIN `users` `forum_posts`.`author` = `users`.`id` AND `forum_themes`.`id` = `forum_posts`.`tid` LIMIT ?, ? ;

   
$mysqli->reset();

$mysqli->select()->table('users')
    ->where('id', '=', 1)
    ->order('id', 'desc')
    ->group('name')
    ->limit(20, 10);
    
$mysqli->reset();

$mysqli->insert(array('name', 'pass'), array('vasya', 'dsfsdfsdf'))->table('users');

$mysqli->reset();

$mysqli->update(array('name' => 'Vasya'))->table('users')->where('id', '=', 1);


echo $mysqli->sqlBuild()->test();
#$mysqli->reset();
echo '<pre>' . print_r($mysqli, 1) . '</pre>';

ну а из этого уже в моделях пилить ОРМ

Koenig

21 апр 2015 г., 4:42

(\/)____o_O____(\/)

если у кого есть время, потестите, и отпишитесь

пререлиз (+/-)

<?php

trait KsqlQueries {
    
    public $pre = false;
    public $post = false;
        
    public function select($fieldsListArray = '*') {
        $this->pre = 'SELECT ' . (is_array($fieldsListArray) ? $this->concat(array_map(array($this, 'art'), $fieldsListArray)) : $fieldsListArray) . ' FROM ';
        
        return $this;
    }
    
    public function count($field = '*') {
        $this->pre = 'SELECT COUNT(' . $this->art($field) . ') FROM ';
        
        return $this;
    }
    
    public function delete() {
        $this->pre = 'DELETE FROM ';
       
        return $this;
    }
    
    public function insert($fieldsListArray, $values) {
        $this->pre = 'INSERT INTO ';
        $this->post = '(' . $this->concat(array_map(array($this, 'art'), $fieldsListArray)) . ') VALUES (' . implode(', ', array_fill(0, count($values), '?')) . ')';
        foreach($values as $value) {
            $this->placeholders[] = $this->esc($value);
        }
        
        return $this;
    }
    
    public function update($data) {
        $this->pre = 'UPDATE ';
        $this->post = 'SET ';
        $i = 0;
        $placeholders = array();
        foreach($data as $field => $value) {
            $this->post .= ($i++ > 0 ? ', ' : '') . $this->art($field) . ' = ?';
            $placeholders[] = $this->esc($value);
        }        
        array_unshift($this->placeholders, $this->concat($placeholders));
                
        return $this;
    }
}

trait KsqlArgs {
    
    public $table = array();
    public $on = array();
    public $limit;
    public $offset = 0;
    public $where = array();
    public $order = array();
    public $group = array();
    
    public function table($table) {
        $this->table[] = $table;
        
        return $this;
    }
    
    public function join($table) {
        return $this->table($table);
    }
    
    public function on() {
        if (func_num_args() == 2) {
            $this->on[] = $this->art(func_get_arg(0)) . ' = ' . $this->art(func_get_arg(1));
        } elseif (is_array($array)) {
            foreach($array as $k => $on) {
                $this->on[] = $this->art($on[0]) . ' = ' . $this->art($on[1]);
            }
        }
        
        return $this;
    }
    
    public function where() {
        if (func_num_args() == 3) {
            $this->where[] = $this->art(func_get_arg(0)) . ' ' . func_get_arg(1) . ' ?';
            $this->placeholders[] = $this->esc(func_get_arg(2));
        } elseif (is_array(func_get_arg(0))) {
            foreach($array as $k => $where) {
                $this->where[] = $this->art($where[0]) . ' ' . $where[1] . ' ?';
                $this->placeholders[] = $this->esc($where[2]);
            }
        }
        
        return $this;
    }
    
    //
    # прочие Where
    //
    
    public function group($field) {
        $this->group[] = $this->art($field);
        
        return $this;
    }
    
    public function order($field, $type = 'asc') {
        $type = in_array($type, array('asc', 'desc')) ? $type : 'asc';
        $this->order[] = $this->art($field) . ' ' . strtoupper($type);
        
        return $this;
    }
    
    public function limit() {
        if (func_num_args() == 2) {
            $this->offset = '?';
            $this->placeholders[] = $this->esc(func_get_arg(0));
            $this->limit = '?';
            $this->placeholders[] = $this->esc(func_get_arg(1));
        } else {
            $this->limit = '?';
            $this->placeholders[] = $this->esc(func_get_arg(0));
        }
        
        return $this;
    }
}

trait KsqlUtils {
    
    public function concat($array, $sep = 'comma') {
        $types = array('comma' => ', ', 1 => ' AND ');
        return implode($types[$sep], $array);
    }
    
    public function art($args) {
        if (!is_array($args)) {
            return '`' . str_replace(array('.', ' = '), array('`.`', '` = `'), str_replace('`', '', $args)) . '`';
        } else {
            return array_map(array($this, 'art'), $args);
        }
    }
    
    public function esc($data) {
        if (!is_array($data)) {
            return $this->real_escape_string($data);
        } else {
            return array_map(array($this, 'esc'), $data);
        }
    }
    
    public function reset() {
        $this->sql = false;
        $this->table = array();
        $this->on = array();
        $this->limit = null;
        $this->offset = 0;
        $this->where = array();
        $this->order = array();
        $this->group = array();
        $this->pre = false;
        $this->post = false;
        $this->placeholders = false;
    }

}

trait KsqlStmtUtils {
    
    public $stmt;
    
    public function count_params() {
        try {
            if ($this->stmt) {
                return $this->stmt->param_count;
            }
        }
        catch(mysqli_sql_exception $e) {
            throw $e;
        }
    }
    
    public function count_fields() {
        try {
            if ($this->stmt) {
                return $this->stmt->field_count;
            }
        }
        catch(mysqli_sql_exception $e) {
            throw $e;
        }
    }
    
    public function is_fetch() {
        try {
            if ($this->stmt && $this->sql) {
                return preg_match('/select/i', $this->sql) ? true : false;
            }
        }
        catch(InvalidArgumentException $e) {
            throw $e;
        }
    }
    
    public function bind_result() {
        $result = array();
        $binds = array();
        $meta = $this->stmt->result_metadata();
        while($field = $meta->fetch_field()) {
            $binds[] = &$row[$field->name];
        }
        if (!call_user_func_array(array($this->stmt, 'bind_result') , $binds)) {
            throw new InvalidArgumentException('Not bind result');
        }
        return $this->stmt;
    }

    public function placeholders() {
        $args = $this->placeholders;
        $types = '';
        $newarray = array();
        $count = sizeof($args);
        for ($i = 0; $i < $count; $i++) {
            $types.= is_numeric($args[$i]) ? 'i' : ((is_double($args[$i]) || is_float($args[$i])) ? 'd' : 's');
            $newarray[] = $args[$i];
        }
        $args = array();
        $args[] = $types;
        foreach($newarray as $k => $v) {
            $args[] = &$newarray[$k];
        }
        if (!call_user_func_array(array($this->stmt, 'bind_param') , $args)) {
            throw new InvalidArgumentException('Not bind params');
        }
        return $this->stmt;
    }
    
    public function result() {
        $this->stmt = false;
        $this->sqlBuild();
        
        try {
            if ($this->stmt = $this->prepare($this->sql)) {
                if ($this->count_params() && is_array($this->placeholders)) {
                    $this->placeholders();
                }
            }
        }
        catch(mysqli_sql_exception $e) {
            throw $e;
        }
        
        if (!$this->is_fetch()) {
            try {
                $this->stmt->execute();
            }
            catch(mysqli_sql_exception $e) {
                throw $e;
            }
            $this->reset();
            
            return $this->stmt->affected_rows;
        } else {
            try {
                $this->stmt->execute();
            }
            catch(mysqli_sql_exception $e) {
                throw $e;
            }
            $this->bind_result();
        }

        return $this->fetch_result();
    }
}

class KMysqli extends Mysqli {
    use KsqlUtils, KsqlArgs, KsqlQueries, KsqlStmtUtils;
    
    public $sql;
    public $stmt;
    
    public function __construct($db_host, $db_user, $db_pass, $db_name, $db_charset, $port = 3306) {
        $driver = new mysqli_driver();
        $driver->report_mode = MYSQLI_REPORT_STRICT;
        try {
            @parent::__construct($db_host, $db_user, $db_pass, $db_name, $port);
            @$this->set_charset($db_charset);
        }
        catch(mysqli_sql_exception $e) {
            throw $e;
        }
    }
    
    public function sql($sql) {
        $this->sql = $sql;
        
        return $this;
    }
    
    public function fetch_result() {
        if (!$this->stmt->num_rows) {
            $return = false;
        }
        if ($this->count_fields() == 1) {
            $res = $this->stmt->get_result();
            $row = $res->fetch_row();
            $return = $row[0];
        }
        else {
            $return = array();
            $res = $this->stmt->get_result();
            while ($row = $res->fetch_assoc()) {
                array_push($return, $row);
            }
        }
        
        $this->reset();
        
        return $return;
    }
    
    public function sqlBuild() {
        if (!$this->pre) {
            return false;
        } else {
            $sql = $this->pre;
            $sql .= ((sizeof($this->table) > 1) ? implode(' JOIN ', $this->art($this->table)) : $this->art(array_shift($this->table))) . ' ';
            $sql .= ($this->post ? $this->post . ' ' : '');
            $sql .= ((sizeof($this->on) > 0) ? 'ON ' . $this->concat($this->on, 1) . ' ' : '');
            $sql .= ((sizeof($this->where) > 0) ? 'WHERE ' . $this->concat($this->where, 1) . ' ' : '');
            $sql .= ((sizeof($this->group) > 0) ? 'GROUP BY ' . $this->concat($this->group, 1) . ' ' : '');
            $sql .= ((sizeof($this->order) > 0) ? 'ORDER BY ' . $this->concat($this->order, 1) . ' ' : '');
            $sql .= ($this->limit ? 'LIMIT ?, ? ;' : ';');
            
            $this->sql($sql);
        }
    }
    
    public function debug() {
        return '<pre>' . print_r($this->sql, 1) . '</pre>' . '<pre>' . print_r($this->placeholders, 1) . '</pre>' . '<pre>' . print_r($this, 1) . '</pre>';
    }
}

$db_host = '127.0.0.1'; 
$db_user = 'root'; 
$db_pass = ''; 
$db_name = 'annimon'; 
$db_charset = 'utf8';

$mysqli = new KMysqli($db_host, $db_user, $db_pass, $db_name, $db_charset);

#$mysqli->table('forum')->select()->where('refid', '=', 6787);
#echo $mysqli->debug(); 
#$res = $mysqli->result();
#echo '<pre>' . print_r($res, 1);


//282896
$mysqli->table('forum')->where('id', '=', 282896)->select();
echo $mysqli->debug(); 
$res = $mysqli->result();
echo '<pre>' . print_r($res, 1);

$mysqli->table('forum')->where('id', '=', 282896)->update(array('text' => 'Te7stus testus'));
echo $mysqli->debug();
$res = $mysqli->result();
echo '<pre>' . print_r($res, 1);

$mysqli->table('forum')->join('users')->on('users.id', 'forum.user_id')->where('forum.id', '=', 282896)->select();
echo $mysqli->debug();
$res = $mysqli->result();
echo '<pre>' . print_r($res, 1);

Jahak

21 апр 2015 г., 6:58

Koenig, Залейте в архив . Мне неудобно с телефона копировать.

Simba

21 апр 2015 г., 8:21

Кадило крутится, лавэха мутится

# Jahak (21.04.2015 / 06:58)
Koenig, Залейте в архив . Мне неудобно с телефона копировать.

вот

Прикрепленные файлы:

206

(2.46 KB)

1429593676php.zip

Jahak

21 апр 2015 г., 8:40

Simba, благодарю

Koenig

21 апр 2015 г., 11:06

(\/)____o_O____(\/)

еще вопрос, автокомплит у вас видит все методы? и как это победить, не хотелось бы все в один класс запиливать

Swank

21 апр 2015 г., 12:47

Сексуальность валенка

Koenig, О_о прикольно но не оч привычно для меня, хотя выглядит очень классно, мне легче на sql языке делать, есть что то типо?

$sql = $db->prepare("SELECT * FROM cats WHERE namelat = :namelat LIMIT 1", array(':namelat' => $namelat));
$row = $sql->fetch();

Koenig

21 апр 2015 г., 12:51

(\/)____o_O____(\/)

Swank, впринципе на данном уровне я добился тех задач, которые ставил, осталось доработать напильником и получится хорошая платформа для орм