1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
|
<?php
namespace Db;
class Util {
public static function execute($dbh, $sql, $parameters = array()) {
if (!($sth = $dbh->prepare($sql))) {
throw new Exception("sql prepare statement failure: $sql");
}
$sth->setFetchMode(\PDO::FETCH_ASSOC);
if (!$sth->execute($parameters)) {
throw new Exception("sql execute statement failure: $sql");
}
return $sth;
}
public static function uuid($dbh) {
$sql = "SELECT uuid() AS uuid";
$sth = static::execute($dbh, $sql);
$record = $sth->fetch(\PDO::FETCH_ASSOC);
return $record['uuid'];
}
public static function columns($dbh, $table) {
$sql = "SELECT column_name FROM information_schema.columns WHERE table_schema = (SELECT DATABASE()) AND table_name = ?";
$parameters = array($table);
$sth = static::execute($dbh, $sql, $parameters);
return $sth->fetchAll();
}
public static function create($dbh, $table, array $data, array $columns = null) {
$insert_data = array();
if (empty($columns)) {
$columns = static::columns($dbh, $table);
}
foreach ($columns as $column) {
$column_name = $column['column_name'];
if (isset($data[$column_name])) {
$insert_data[$column_name] = $data[$column_name];
}
}
$now = date('Y-m-d H:i:s');
if (!isset($insert_data['createdate'])) {
$insert_data['createdate'] = $now;
}
if (!isset($insert_data['lastupdate'])) {
$insert_data['lastupdate'] = $now;
}
if (!isset($insert_data['createby'])) {
$insert_data['createby'] = $_SESSION['webadmin']['id'];
}
if (!isset($insert_data['lastupby'])) {
$insert_data['lastupby'] = $_SESSION['webadmin']['id'];
}
$insert_columns = array();
$values = array();
$parameters = array();
foreach ($insert_data as $column => $value) {
$insert_columns[] = "`$column`";
$parameters[] = !strlen($value) ? null : $value;
$values[] = '?';
}
$sql = "INSERT `$table` (" . implode(', ', $insert_columns) . ") VALUES (" . implode(', ', $values) . ")";
$sth = static::execute($dbh, $sql, $parameters);
return $dbh->lastInsertId();
}
public static function update($dbh, $table, array $data, array $columns = null, $primary_key = 'id') {
if (empty($data[$primary_key])) {
throw new Exception('No primary key value.');
}
$update_data = array();
if (empty($columns)) {
$columns = static::columns($dbh, $table);
}
foreach ($columns as $column) {
$column_name = $column['column_name'];
if (array_key_exists($column_name, $data)) {
$update_data[$column_name] = $data[$column_name];
}
}
$now = date('Y-m-d H:i:s');
$update_data['lastupdate'] = $now;
$update_data['lastupby'] = $_SESSION['webadmin']['id'];
$values = array();
$parameters = array();
foreach ($update_data as $column => $value) {
if ($column != $primary_key) {
$parameters[] = !strlen($value) ? null : $value;
$values[] = "`$column` = ?";
}
}
$sql = "UPDATE `$table` SET " . implode(', ', $values) . " WHERE `$primary_key` = ?";
$parameters[] = $data[$primary_key];
$sth = static::execute($dbh, $sql, $parameters);
return $sth->rowCount();
}
public static function transaction($dbh, $runnable) {
if (!$dbh->beginTransaction()) {
throw new Exception('mysql begin transaction failure.');
}
try {
$runnable();
if (!$dbh->commit()) {
throw new Exception('mysql commit transaction failure.');
}
} catch (Exception $exception) {
if (!$dbh->rollBack()) {
throw new Exception('mysql roll back transaction failure.');
}
throw $exception;
}
}
}
|