1: <?php
2:
3: namespace Budabot\Core;
4:
5: use PDO;
6: use PDOException;
7: use Exception;
8:
9: require_once 'DBRow.class.php';
10:
11: 12: 13:
14: class DB {
15:
16:
17: public $settingManager;
18:
19:
20: public $util;
21:
22: private $type;
23: private $sql;
24: private $botname;
25: private $dim;
26: private $guild;
27: private $lastQuery;
28: private $inTransaction = false;
29:
30: private $logger;
31:
32: const MYSQL = 'mysql';
33: const SQLITE = 'sqlite';
34:
35: public function __construct() {
36: $this->logger = new LoggerWrapper('SQL');
37: }
38:
39: function connect($type, $dbName, $host = null, $user = null, $pass = null) {
40: global $vars;
41: $this->type = strtolower($type);
42: $this->botname = strtolower($vars["name"]);
43: $this->dim = $vars["dimension"];
44: $this->guild = str_replace("'", "''", $vars["my_guild"]);
45:
46: if ($this->type == self::MYSQL) {
47: $this->sql = new PDO("mysql:dbname=$dbName;host=$host", $user, $pass);
48: $this->sql->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
49: $this->exec("SET sql_mode = 'TRADITIONAL,NO_BACKSLASH_ESCAPES'");
50: $this->exec("SET time_zone = '+00:00'");
51:
52: $mysqlVersion = $this->sql->getAttribute(PDO::ATTR_SERVER_VERSION);
53:
54:
55:
56: if (version_compare($mysqlVersion, "5.5") >= 0) {
57: $this->exec("SET default_storage_engine = MyISAM");
58: } else {
59: $this->exec("SET storage_engine = MyISAM");
60: }
61: } else if ($this->type == self::SQLITE) {
62: if ($host == null || $host == "" || $host == "localhost") {
63: $dbName = "./data/$dbName";
64: } else {
65: $dbName = "$host/$dbName";
66: }
67:
68: $this->sql = new PDO("sqlite:".$dbName);
69: $this->sql->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
70: } else {
71: throw new Exception("Invalid database type: '$type'. Expecting '" . self::MYSQL . "' or '" . self::SQLITE . "'.");
72: }
73: }
74:
75: function getType() {
76: return $this->type;
77: }
78:
79: function queryRow($sql) {
80: $sql = $this->formatSql($sql);
81:
82: $args = $this->getParameters(func_get_args());
83:
84: $ps = $this->executeQuery($sql, $args);
85: $result = $ps->fetchAll(PDO::FETCH_CLASS, 'budabot\core\DBRow');
86:
87: if (count($result) == 0) {
88: return null;
89: } else {
90: return $result[0];
91: }
92: }
93:
94: function query($sql) {
95: $sql = $this->formatSql($sql);
96:
97: $args = $this->getParameters(func_get_args());
98:
99: $ps = $this->executeQuery($sql, $args);
100: return $ps->fetchAll(PDO::FETCH_CLASS, 'budabot\core\DBRow');
101: }
102:
103: function exec($sql) {
104: $sql = $this->formatSql($sql);
105:
106: if (substr_compare($sql, "create", 0, 6, true) == 0) {
107: if ($this->type == self::MYSQL) {
108: $sql = str_ireplace("AUTOINCREMENT", "AUTO_INCREMENT", $sql);
109: } else if ($this->type == self::SQLITE) {
110: $sql = str_ireplace("AUTO_INCREMENT", "AUTOINCREMENT", $sql);
111: $sql = str_ireplace(" INT ", " INTEGER ", $sql);
112: }
113: }
114:
115: $args = $this->getParameters(func_get_args());
116:
117: $ps = $this->executeQuery($sql, $args);
118:
119: return $ps->rowCount();
120: }
121:
122: private function getParameters($args) {
123: array_shift($args);
124: if (isset($args[0]) && is_array($args[0])) {
125: return $args[0];
126: } else {
127: return $args;
128: }
129: }
130:
131: private function executeQuery($sql, $params) {
132: $this->lastQuery = $sql;
133: $this->logger->log('DEBUG', $sql . " - " . print_r($params, true));
134:
135: try {
136: $ps = $this->sql->prepare($sql);
137: $count = 1;
138: forEach ($params as $param) {
139: if ($param === "NULL") {
140: $ps->bindValue($count++, $param, PDO::PARAM_NULL);
141: } else if (is_int($param)) {
142: $ps->bindValue($count++, $param, PDO::PARAM_INT);
143: } else {
144: $ps->bindValue($count++, $param);
145: }
146: }
147: $ps->execute();
148: return $ps;
149: } catch (PDOException $e) {
150: if ($this->type == self::SQLITE && $e->errorInfo[1] == 17) {
151:
152: return $this->executeQuery($sql, $params);
153: }
154: throw new SQLException("{$e->errorInfo[2]} in: $sql - " . print_r($params, true), 0, $e);
155: }
156: }
157:
158:
159: function beginTransaction() {
160: $this->logger->log('DEBUG', "Starting transaction");
161: $this->inTransaction = true;
162: $this->sql->beginTransaction();
163: }
164:
165:
166: function commit() {
167: $this->logger->log('DEBUG', "Committing transaction");
168: $this->inTransaction = false;
169: $this->sql->Commit();
170: }
171:
172: function rollback() {
173: $this->logger->log('DEBUG', "Rolling back transaction");
174: $this->inTransaction = false;
175: $this->sql->rollback();
176: }
177:
178: function inTransaction() {
179: return $this->inTransaction;
180: }
181:
182:
183: function lastInsertId() {
184: return $this->sql->lastInsertId();
185: }
186:
187: function formatSql($sql) {
188: $sql = str_replace("<dim>", $this->dim, $sql);
189: $sql = str_replace("<myname>", $this->botname, $sql);
190: $sql = str_replace("<myguild>", $this->guild, $sql);
191:
192: return $sql;
193: }
194:
195: function getLastQuery() {
196: return $this->lastQuery;
197: }
198:
199: 200: 201: 202: 203: 204:
205: public function loadSQLFile($module, $name, $forceUpdate = false) {
206: $name = strtolower($name);
207:
208:
209: if (!preg_match('/^[a-z0-9_]+$/i', $name)) {
210: $msg = "Invalid SQL file name: '$name' for module: '$module'! Only numbers, letters, and underscores permitted!";
211: $this->logger->log('ERROR', $msg);
212: return $msg;
213: }
214:
215: $settingName = $name . "_db_version";
216:
217: $dir = $this->util->verifyFilename($module);
218: if (empty($dir)) {
219: $msg = "Could not find module '$module'.";
220: $this->logger->log('ERROR', $msg);
221: return $msg;
222: }
223: $d = dir($dir);
224:
225: if ($this->settingManager->exists($settingName)) {
226: $currentVersion = $this->settingManager->get($settingName);
227: } else {
228: $currentVersion = false;
229: }
230: if ($currentVersion === false) {
231: $currentVersion = 0;
232: }
233:
234: $file = false;
235: $maxFileVersion = 0;
236: if ($d) {
237: while (false !== ($entry = $d->read())) {
238: if (is_file("$dir/$entry") && preg_match("/^" . $name . "([0-9.]*)\\.sql$/i", $entry, $arr)) {
239:
240:
241: if ($arr[1] == '') {
242: $file = $entry;
243: $maxFileVersion = filemtime("$dir/$file");
244: break;
245: }
246:
247: if ($this->util->compareVersionNumbers($arr[1], $maxFileVersion) >= 0) {
248: $maxFileVersion = $arr[1];
249: $file = $entry;
250: }
251: }
252: }
253: }
254:
255: if ($file === false) {
256: $msg = "No SQL file found with name '$name' in module '$module'!";
257: $this->logger->log('ERROR', $msg);
258: return $msg;
259: }
260:
261:
262: $this->settingManager->add($module, $settingName, $settingName, 'noedit', 'text', 0);
263:
264: if ($forceUpdate || $this->util->compareVersionNumbers($maxFileVersion, $currentVersion) > 0) {
265: $handle = @fopen("$dir/$file", "r");
266: if ($handle) {
267: try {
268: while (($line = fgets($handle)) !== false) {
269: $line = trim($line);
270:
271: if ($line != '' && substr($line, 0, 1) != "#" && substr($line, 0, 2) != "--") {
272: $this->exec($line);
273: }
274: }
275:
276: $this->settingManager->save($settingName, $maxFileVersion);
277:
278: if ($maxFileVersion != 0) {
279: $msg = "Updated '$name' database from '$currentVersion' to '$maxFileVersion'";
280: $this->logger->log('DEBUG', $msg);
281: } else {
282: $msg = "Updated '$name' database";
283: $this->logger->log('DEBUG', $msg);
284: }
285: } catch (SQLException $e) {
286: $msg = "Error loading sql file '$file': " . $e->getMessage();
287: $this->logger->log('ERROR', $msg);
288: }
289: } else {
290: $msg = "Could not load SQL file: '$dir/$file'";
291: $this->logger->log('ERROR', $msg);
292: }
293: } else {
294: $msg = "'$name' database already up to date! version: '$currentVersion'";
295: $this->logger->log('DEBUG', $msg);
296: }
297:
298: return $msg;
299: }
300: }
301: