<?php
namespace ticky;
use PDO;
use ticky\debug;
class model {
public $config;
public static $sqldebug = array();
public $prefix;
public $sql; private $driver;
private $conn = null; private $alias = []; private $starttime;
private $querynum = 0;
protected $methods = array('strict', 'order', 'alias', 'having', 'group', 'lock', 'distinct', 'auto', 'filter', 'validate', 'result', 'token', 'index', 'force', 'limit');
public function __construct($table = '') {
$this->starttime = microtime(true);
$config = config::get('db');
$this->driver = $config['driver'];
$this->config = $config[$this->driver];
$this->prefix = $this->config['prefix'];
if (isset($table)) {
$this->table($table);
}
if (is_null($this->conn)) {
try {
$this->conn = new PDO($this->dsn(), $this->config['user'], $this->config['password']);
$this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->conn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true); $this->conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, true); $this->conn->setAttribute(PDO::ATTR_CASE, PDO::CASE_NATURAL); $this->conn->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_NATURAL); $this->conn->setAttribute(PDO::ATTR_AUTOCOMMIT, 1); $this->conn->exec('SET NAMES ' . $this->config['charset']);
} catch (PDOException $e) {
$this->conn = null;
app::halt('Connection failed: ' . $e->getMessage());
}
}
}
private function dsn() {
$host = $this->config['host'];
$port = $this->config['port'];
$name = $this->config['name'];
if (!$host || !$port || !$name) {
throw new exception("Required DSN parameter is missing");
}
return "mysql:host={$host};port={$port};dbname={$name}";
}
public function getTable() {
return $this->alias['table'];
}
public function count() {
$this->starttime = microtime(true);
$this->alias['field'] = " count(*) as count ";
$this->ParseSelectSql();
$row = $this->conn->query($this->sql)->fetchAll(PDO::FETCH_ASSOC);
$result = [];
foreach ($row as $key => $vo) {
$result[$key] = $vo;
}
$this->printSql();
$this->clearsql();
return $result;
}
public function field($field) {
if (!is_string($field)) {
throw new exception("field语句的参数必须为字符串");
}
$this->alias['field'] = $field;
return $this;
}
public function table($table) {
if (!is_string($table)) {
throw new exception("table语句的参数必须为字符串");
}
if (strpos($table, $this->prefix) !== false) {
$this->alias['table'] = $table;
} else {
$this->alias['table'] = $this->prefix . $table;
}
return $this;
}
public function where($where) {
$this->alias['where'] = '';
if (is_array($where)) {
foreach ($where as $key => $vo) {
$this->alias['where'] .= " $key" . ' = "' . $vo . '" and ';
}
$this->alias['where'] = rtrim($this->alias['where'], 'and ');
} else if (is_string($where)) {
$this->alias['where'] = $where;
} else {
throw new exception("where语句的参数必须为数组或字符串");
}
return $this;
}
public function limit($limit) {
$this->alias['limit'] = '';
if (is_numeric($limit)) {
$this->alias['limit'] = '0,' . $limit;
} else if (is_string($limit)) {
$this->alias['limit'] = $limit;
} else {
throw new exception("limit语句的参数必须为数字或字符串");
}
return $this;
}
public function order($order) {
if (!is_string($order)) {
throw new exception("order语句的参数必须为字符串");
}
$this->alias['order'] = $order;
return $this;
}
public function group($group) {
if (!is_string($group)) {
throw new exception("group语句的参数必须为字符串");
}
$this->alias['group'] = $group;
return $this;
}
public function join($join, $type = 'INNER') {
$prefix = $this->prefix;
$this->alias['join'] = '';
if (is_array($join)) {
foreach ($join as $key => &$_join) {
$_join = preg_replace_callback("/__([A-Z0-9_-]+)__/sU", function($match) use($prefix) {
return $prefix . strtolower($match[1]);
}, $_join);
$_join = false !== stripos($_join, 'JOIN') ? $_join : $type . ' JOIN ' . $_join;
}
$this->alias['join'] = $join;
} elseif (!empty($join)) {
$join = preg_replace_callback("/__([A-Z0-9_-]+)__/sU", function($match) use($prefix) {
return $prefix . strtolower($match[1]);
}, $join);
$this->alias['join'][] = false !== stripos($join, 'JOIN') ? $join : $type . ' JOIN ' . $join;
}
return $this;
}
private function ParseSelectSql() {
$this->sql = 'select *';
if (!empty($this->alias['field'])) {
$this->sql = str_replace('*', $this->alias['field'], $this->sql);
}
if (empty($this->alias['table'])) {
throw new exception("请用table子句设置查询表");
} else {
$this->sql .= ' from ' . $this->alias['table'];
}
if (!empty($this->alias['join'])) {
$prefix = $this->prefix;
if (is_array($this->alias['join'])) {
$join = $this->alias['join'];
foreach ($join as $key => &$_join) {
$_join = preg_replace_callback("/__([A-Z0-9_-]+)__/sU", function($match) use($prefix) {
return $prefix . strtolower($match[1]);
}, $_join);
$_join = false !== stripos($_join, 'JOIN') ? $_join : $type . ' JOIN ' . $_join;
}
$this->sql .= ' ' . $_join;
} else if ($this->alias['join']) {
$this->sql .= ' ' . $this->alias['join'];
}
}
if (!empty($this->alias['where'])) {
$this->sql .= ' where ' . $this->alias['where'];
}
if (!empty($this->alias['group'])) {
$this->sql .= ' group by ' . $this->alias['group'];
}
if (!empty($this->alias['order'])) {
$this->sql .= ' order by ' . $this->alias['order'];
}
if (!empty($this->alias['limit'])) {
$this->sql .= ' limit ' . $this->alias['limit'];
}
return $this->sql;
}
private function ParseAddSql() {
$this->sql = 'insert into ';
if (empty($this->alias['table'])) {
throw new exception("请用table子句设置添加表");
} else {
$this->sql .= $this->alias['table'] . ' set ';
}
return $this->sql;
}
private function ParseUpdateSql() {
$this->sql = 'update ';
if (empty($this->alias['table'])) {
throw new exception("请用table子句设置修改表");
} else {
$this->sql .= $this->alias['table'];
}
if (!empty($this->alias['where'])) {
$this->sql .= ' set ';
}
return $this->sql;
}
private function ParseDeleteSql() {
$this->sql = 'delete from ';
if (empty($this->alias['table'])) {
throw new exception("请用table子句设置删除表");
} else {
$this->sql .= $this->alias['table'];
}
if (empty($this->alias['where'])) {
throw new exception("删除语句必须有where子句指定条件");
}
$this->sql .= ' where ' . $this->alias['where'];
return $this->sql;
}
public function select() {
$this->starttime = microtime(true);
$this->ParseSelectSql();
$this->printSql($this->sql);
$row = $this->conn->query($this->sql)->fetchAll(PDO::FETCH_ASSOC);
$result = [];
foreach ($row as $key => $vo) {
$result[$key] = $vo;
}
$this->clearsql();
return $result;
}
public function find() {
$this->starttime = microtime(true);
$this->ParseSelectSql();
$this->printSql();
$row = $this->conn->query($this->sql)->fetch(PDO::FETCH_ASSOC);
$this->clearsql();
return $row;
}
public function add($data) {
$this->starttime = microtime(true);
if (!is_array($data)) {
throw new exception("添加数据add方法参数必须为数组");
}
$this->ParseAddSql();
foreach ($data as $key => $vo) {
$this->sql .= " `{$key}` = '" . $this->safe_data($vo) . "',";
}
$this->printSql();
try {
$this->conn->exec(rtrim($this->sql, ','));
$newId = $this->conn->lastInsertId();
$this->clearsql();
return $newId;
} catch (Exception $e) {
return false;
}
}
public function update($data) {
$this->starttime = microtime(true);
if (!is_array($data)) {
$this->ParseUpdateSql();
$this->sql .= $data;
$this->sql = rtrim($this->sql, ',') . ' where ' . $this->alias['where'];
} else {
$this->ParseUpdateSql();
foreach ($data as $key => $vo) {
$this->sql .= " `{$key}` = '" . $this->safe_data($vo) . "',";
}
$this->sql = rtrim($this->sql, ',') . ' where ' . $this->alias['where'];
}
$this->printSql();
$ret = $this->conn->exec($this->sql);
$this->clearsql();
return $ret;
}
public function delete() {
$this->starttime = microtime(true);
$this->ParseDeleteSql();
$ret = $this->conn->exec($this->sql);
$this->printSql();
$this->clearsql();
return $ret;
}
public function page($table, $search, $order = '', $pageNum = 1, $pageSize = 10) {
$this->starttime = microtime(true);
$pageStr = (($pageNum - 1) * $pageSize) . "," . $pageSize;
$orm = new model();
$total = count($orm->table($table)->where($search)->select());
$res = $orm->table($table)->where($search)->order($order)->limit($pageStr)->select();
$page = new page($total, $pageSize);
$this->printSql($this->sql);
$this->clearsql();
return [
'items' => $res,
'page' => $page,
];
}
public function newpage() {
$field = $this->alias['field'];
$total = $this->total();
$page = new page($total, 10);
$this->alias['field'] = $field;
$res = $this->limit($page->limit())->select();
return [
'items' => $res,
'page' => $page,
];
}
public function total() {
$this->field('COUNT(*) AS total');
$this->ParseSelectSql();
$this->printSql($this->sql);
$total = @$this->conn->query($this->sql)->fetchAll(PDO::FETCH_ASSOC);
return $total[0]['total'];
}
public function __call($method, $args) {
if (in_array(strtolower($method), $this->methods, true)) {
$this->alias[strtolower($method)] = $args[0];
return $this;
}
}
public function getsql() {
return $this->sql;
}
private function clearsql() {
$this->alias = array();
$this->sql = "";
}
private function printSql($sql = null) {
if (APP_DEBUG) {
if (empty($sql)) {
$sql = $this->sql;
}
$this->querynum++;
self::$sqldebug[] = $sql;
debug::addmsg($sql, 1);
runlog('sql', $sql);
}
}
private function safe_data($value, $chars = false) {
if (!MAGIC_QUOTES_GPC) {
$value = addslashes($value);
}
if ($chars) {
$value = htmlspecialchars($value);
}
return $value;
}
private function filter_field($arr, $primary = true) {
$fields = $this->get_fields();
foreach ($arr as $k => $v) {
if (!in_array($k, $fields)) {
unset($arr[$k]);
}
}
if ($primary) {
$p = $this->get_primary();
if (isset($arr[$p])) {
unset($arr[$p]);
}
}
return $arr;
}
public function query($sql) {
$this->starttime = microtime(true);
$this->printSql($sql);
$row = @$this->conn->query($sql)->fetchAll(PDO::FETCH_ASSOC);
$result = [];
foreach ($row as $key => $vo) {
$result[$key] = $vo;
}
$this->clearsql();
return $result;
}
public function exec($sql) {
$this->starttime = microtime(true);
$this->printSql($sql);
$ret = $this->conn->exec($sql);
$this->clearsql();
return $ret;
}
public function version() {
return @$this->conn->getAttribute(PDO::ATTR_SERVER_VERSION);
}
public function list_tables() {
$tables = array();
$listqeury = @$this->conn->query('SHOW TABLES');
$data = $listqeury->fetchAll(PDO::FETCH_NUM);
foreach ($data as $key => $value) {
$tables[] = $value[0];
}
return $tables;
}
public function get_fields($table = '') {
$table = empty($table) ? $this->getTable() : $table;
$fields = array();
$sql = "SHOW COLUMNS FROM $table";
$this->printSql($sql);
$r = @$this->conn->query($sql);
$data = $r->fetchAll(PDO::FETCH_ASSOC);
foreach ($data as $key => $value) {
$fields[] = $value['Field'];
}
return $fields;
}
public function table_exists($table) {
$tables = $this->list_tables();
return in_array($table, $tables);
}
public function field_exists($table, $field) {
$fields = $this->get_fields($table);
return in_array($field, $fields);
}
public function get_primary($table = '') {
$table = empty($table) ? $this->get_tablename() : $table;
$sql = "SHOW COLUMNS FROM $table";
$this->printSql($sql);
if (empty($table))
return false;
$r = @$this->conn->query($sql);
$data = $r->fetchAll(PDO::FETCH_ASSOC);
foreach ($data as $key => $value) {
if ($value['Key'] == 'PRI') {
return $value['Field'];
}
}
return $data[0]['Field'];
}
public function insert($data, $filter = false, $primary = true) {
if (!is_array($data)) {
return false;
}
$data = $this->filter_field($data, $primary);
$fields = $values = array();
foreach ($data AS $key => $val) {
$fields[] = '`' . $key . '`';
$values[] = "'" . $this->safe_data($val, $filter) . "'";
}
if (empty($fields)) {
return false;
}
$sql = 'INSERT INTO ' . $this->getTable() . ' (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $values) . ')';
$this->printSql($sql);
$this->conn->exec($sql);
return $this->conn->lastInsertId();
}
}