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:  * @Instance
 13:  */
 14: class DB {
 15: 
 16:     /** @Inject */
 17:     public $settingManager;
 18: 
 19:     /** @Inject */
 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:             // for MySQL 5.5 and later, use 'default_storage_engine'
 55:             // for previous versions use 'storage_engine'
 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:                 // fix for Sqlite schema changed error (retry the query)
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:     //Start of an transaction
159:     function beginTransaction() {
160:         $this->logger->log('DEBUG', "Starting transaction");
161:         $this->inTransaction = true;
162:         $this->sql->beginTransaction();
163:     }
164: 
165:     //Commit an transaction
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:     //Return the last inserted ID
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:      * @name: loadSQLFile
201:      * @description: Loads an sql file if there is an update
202:      *    Will load the sql file with name $namexx.xx.xx.xx.sql if xx.xx.xx.xx is greater than settings[$name . "_sql_version"]
203:      *    If there is an sql file with name $name.sql it would load that one every time
204:      */
205:     public function loadSQLFile($module, $name, $forceUpdate = false) {
206:         $name = strtolower($name);
207: 
208:         // only letters, numbers, underscores are allowed
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;  // 0 indicates no version
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:                     // If the file has no versioning in its filename, then we go off the modified timestamp
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:         // make sure setting is verified so it doesn't get deleted
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:                         // don't process comment lines or blank lines
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: