DB_utility.php 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424
  1. <?php
  2. /**
  3. * CodeIgniter
  4. *
  5. * An open source application development framework for PHP
  6. *
  7. * This content is released under the MIT License (MIT)
  8. *
  9. * Copyright (c) 2014 - 2019, British Columbia Institute of Technology
  10. *
  11. * Permission is hereby granted, free of charge, to any person obtaining a copy
  12. * of this software and associated documentation files (the "Software"), to deal
  13. * in the Software without restriction, including without limitation the rights
  14. * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
  15. * copies of the Software, and to permit persons to whom the Software is
  16. * furnished to do so, subject to the following conditions:
  17. *
  18. * The above copyright notice and this permission notice shall be included in
  19. * all copies or substantial portions of the Software.
  20. *
  21. * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
  22. * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
  23. * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
  24. * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
  25. * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
  26. * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
  27. * THE SOFTWARE.
  28. *
  29. * @package CodeIgniter
  30. * @author EllisLab Dev Team
  31. * @copyright Copyright (c) 2008 - 2014, EllisLab, Inc. (https://ellislab.com/)
  32. * @copyright Copyright (c) 2014 - 2019, British Columbia Institute of Technology (https://bcit.ca/)
  33. * @license https://opensource.org/licenses/MIT MIT License
  34. * @link https://codeigniter.com
  35. * @since Version 1.0.0
  36. * @filesource
  37. */
  38. defined('BASEPATH') OR exit('No direct script access allowed');
  39. /**
  40. * Database Utility Class
  41. *
  42. * @category Database
  43. * @author EllisLab Dev Team
  44. * @link https://codeigniter.com/user_guide/database/
  45. */
  46. abstract class CI_DB_utility {
  47. /**
  48. * Database object
  49. *
  50. * @var object
  51. */
  52. protected $db;
  53. // --------------------------------------------------------------------
  54. /**
  55. * List databases statement
  56. *
  57. * @var string
  58. */
  59. protected $_list_databases = FALSE;
  60. /**
  61. * OPTIMIZE TABLE statement
  62. *
  63. * @var string
  64. */
  65. protected $_optimize_table = FALSE;
  66. /**
  67. * REPAIR TABLE statement
  68. *
  69. * @var string
  70. */
  71. protected $_repair_table = FALSE;
  72. // --------------------------------------------------------------------
  73. /**
  74. * Class constructor
  75. *
  76. * @param object &$db Database object
  77. * @return void
  78. */
  79. public function __construct(&$db)
  80. {
  81. $this->db =& $db;
  82. log_message('info', 'Database Utility Class Initialized');
  83. }
  84. // --------------------------------------------------------------------
  85. /**
  86. * List databases
  87. *
  88. * @return array
  89. */
  90. public function list_databases()
  91. {
  92. // Is there a cached result?
  93. if (isset($this->db->data_cache['db_names']))
  94. {
  95. return $this->db->data_cache['db_names'];
  96. }
  97. elseif ($this->_list_databases === FALSE)
  98. {
  99. return ($this->db->db_debug) ? $this->db->display_error('db_unsupported_feature') : FALSE;
  100. }
  101. $this->db->data_cache['db_names'] = array();
  102. $query = $this->db->query($this->_list_databases);
  103. if ($query === FALSE)
  104. {
  105. return $this->db->data_cache['db_names'];
  106. }
  107. for ($i = 0, $query = $query->result_array(), $c = count($query); $i < $c; $i++)
  108. {
  109. $this->db->data_cache['db_names'][] = current($query[$i]);
  110. }
  111. return $this->db->data_cache['db_names'];
  112. }
  113. // --------------------------------------------------------------------
  114. /**
  115. * Determine if a particular database exists
  116. *
  117. * @param string $database_name
  118. * @return bool
  119. */
  120. public function database_exists($database_name)
  121. {
  122. return in_array($database_name, $this->list_databases());
  123. }
  124. // --------------------------------------------------------------------
  125. /**
  126. * Optimize Table
  127. *
  128. * @param string $table_name
  129. * @return mixed
  130. */
  131. public function optimize_table($table_name)
  132. {
  133. if ($this->_optimize_table === FALSE)
  134. {
  135. return ($this->db->db_debug) ? $this->db->display_error('db_unsupported_feature') : FALSE;
  136. }
  137. $query = $this->db->query(sprintf($this->_optimize_table, $this->db->escape_identifiers($table_name)));
  138. if ($query !== FALSE)
  139. {
  140. $query = $query->result_array();
  141. return current($query);
  142. }
  143. return FALSE;
  144. }
  145. // --------------------------------------------------------------------
  146. /**
  147. * Optimize Database
  148. *
  149. * @return mixed
  150. */
  151. public function optimize_database()
  152. {
  153. if ($this->_optimize_table === FALSE)
  154. {
  155. return ($this->db->db_debug) ? $this->db->display_error('db_unsupported_feature') : FALSE;
  156. }
  157. $result = array();
  158. foreach ($this->db->list_tables() as $table_name)
  159. {
  160. $res = $this->db->query(sprintf($this->_optimize_table, $this->db->escape_identifiers($table_name)));
  161. if (is_bool($res))
  162. {
  163. return $res;
  164. }
  165. // Build the result array...
  166. $res = $res->result_array();
  167. $res = current($res);
  168. $key = str_replace($this->db->database.'.', '', current($res));
  169. $keys = array_keys($res);
  170. unset($res[$keys[0]]);
  171. $result[$key] = $res;
  172. }
  173. return $result;
  174. }
  175. // --------------------------------------------------------------------
  176. /**
  177. * Repair Table
  178. *
  179. * @param string $table_name
  180. * @return mixed
  181. */
  182. public function repair_table($table_name)
  183. {
  184. if ($this->_repair_table === FALSE)
  185. {
  186. return ($this->db->db_debug) ? $this->db->display_error('db_unsupported_feature') : FALSE;
  187. }
  188. $query = $this->db->query(sprintf($this->_repair_table, $this->db->escape_identifiers($table_name)));
  189. if (is_bool($query))
  190. {
  191. return $query;
  192. }
  193. $query = $query->result_array();
  194. return current($query);
  195. }
  196. // --------------------------------------------------------------------
  197. /**
  198. * Generate CSV from a query result object
  199. *
  200. * @param object $query Query result object
  201. * @param string $delim Delimiter (default: ,)
  202. * @param string $newline Newline character (default: \n)
  203. * @param string $enclosure Enclosure (default: ")
  204. * @return string
  205. */
  206. public function csv_from_result($query, $delim = ',', $newline = "\n", $enclosure = '"')
  207. {
  208. if ( ! is_object($query) OR ! method_exists($query, 'list_fields'))
  209. {
  210. show_error('You must submit a valid result object');
  211. }
  212. $out = '';
  213. // First generate the headings from the table column names
  214. foreach ($query->list_fields() as $name)
  215. {
  216. $out .= $enclosure.str_replace($enclosure, $enclosure.$enclosure, $name).$enclosure.$delim;
  217. }
  218. $out = substr($out, 0, -strlen($delim)).$newline;
  219. // Next blast through the result array and build out the rows
  220. while ($row = $query->unbuffered_row('array'))
  221. {
  222. $line = array();
  223. foreach ($row as $item)
  224. {
  225. $line[] = $enclosure.str_replace($enclosure, $enclosure.$enclosure, $item).$enclosure;
  226. }
  227. $out .= implode($delim, $line).$newline;
  228. }
  229. return $out;
  230. }
  231. // --------------------------------------------------------------------
  232. /**
  233. * Generate XML data from a query result object
  234. *
  235. * @param object $query Query result object
  236. * @param array $params Any preferences
  237. * @return string
  238. */
  239. public function xml_from_result($query, $params = array())
  240. {
  241. if ( ! is_object($query) OR ! method_exists($query, 'list_fields'))
  242. {
  243. show_error('You must submit a valid result object');
  244. }
  245. // Set our default values
  246. foreach (array('root' => 'root', 'element' => 'element', 'newline' => "\n", 'tab' => "\t") as $key => $val)
  247. {
  248. if ( ! isset($params[$key]))
  249. {
  250. $params[$key] = $val;
  251. }
  252. }
  253. // Create variables for convenience
  254. extract($params);
  255. // Load the xml helper
  256. get_instance()->load->helper('xml');
  257. // Generate the result
  258. $xml = '<'.$root.'>'.$newline;
  259. while ($row = $query->unbuffered_row())
  260. {
  261. $xml .= $tab.'<'.$element.'>'.$newline;
  262. foreach ($row as $key => $val)
  263. {
  264. $xml .= $tab.$tab.'<'.$key.'>'.xml_convert($val).'</'.$key.'>'.$newline;
  265. }
  266. $xml .= $tab.'</'.$element.'>'.$newline;
  267. }
  268. return $xml.'</'.$root.'>'.$newline;
  269. }
  270. // --------------------------------------------------------------------
  271. /**
  272. * Database Backup
  273. *
  274. * @param array $params
  275. * @return string
  276. */
  277. public function backup($params = array())
  278. {
  279. // If the parameters have not been submitted as an
  280. // array then we know that it is simply the table
  281. // name, which is a valid short cut.
  282. if (is_string($params))
  283. {
  284. $params = array('tables' => $params);
  285. }
  286. // Set up our default preferences
  287. $prefs = array(
  288. 'tables' => array(),
  289. 'ignore' => array(),
  290. 'filename' => '',
  291. 'format' => 'gzip', // gzip, zip, txt
  292. 'add_drop' => TRUE,
  293. 'add_insert' => TRUE,
  294. 'newline' => "\n",
  295. 'foreign_key_checks' => TRUE
  296. );
  297. // Did the user submit any preferences? If so set them....
  298. if (count($params) > 0)
  299. {
  300. foreach ($prefs as $key => $val)
  301. {
  302. if (isset($params[$key]))
  303. {
  304. $prefs[$key] = $params[$key];
  305. }
  306. }
  307. }
  308. // Are we backing up a complete database or individual tables?
  309. // If no table names were submitted we'll fetch the entire table list
  310. if (count($prefs['tables']) === 0)
  311. {
  312. $prefs['tables'] = $this->db->list_tables();
  313. }
  314. // Validate the format
  315. if ( ! in_array($prefs['format'], array('gzip', 'zip', 'txt'), TRUE))
  316. {
  317. $prefs['format'] = 'txt';
  318. }
  319. // Is the encoder supported? If not, we'll either issue an
  320. // error or use plain text depending on the debug settings
  321. if (($prefs['format'] === 'gzip' && ! function_exists('gzencode'))
  322. OR ($prefs['format'] === 'zip' && ! function_exists('gzcompress')))
  323. {
  324. if ($this->db->db_debug)
  325. {
  326. return $this->db->display_error('db_unsupported_compression');
  327. }
  328. $prefs['format'] = 'txt';
  329. }
  330. // Was a Zip file requested?
  331. if ($prefs['format'] === 'zip')
  332. {
  333. // Set the filename if not provided (only needed with Zip files)
  334. if ($prefs['filename'] === '')
  335. {
  336. $prefs['filename'] = (count($prefs['tables']) === 1 ? $prefs['tables'] : $this->db->database)
  337. .date('Y-m-d_H-i', time()).'.sql';
  338. }
  339. else
  340. {
  341. // If they included the .zip file extension we'll remove it
  342. if (preg_match('|.+?\.zip$|', $prefs['filename']))
  343. {
  344. $prefs['filename'] = str_replace('.zip', '', $prefs['filename']);
  345. }
  346. // Tack on the ".sql" file extension if needed
  347. if ( ! preg_match('|.+?\.sql$|', $prefs['filename']))
  348. {
  349. $prefs['filename'] .= '.sql';
  350. }
  351. }
  352. // Load the Zip class and output it
  353. $CI =& get_instance();
  354. $CI->load->library('zip');
  355. $CI->zip->add_data($prefs['filename'], $this->_backup($prefs));
  356. return $CI->zip->get_zip();
  357. }
  358. elseif ($prefs['format'] === 'txt') // Was a text file requested?
  359. {
  360. return $this->_backup($prefs);
  361. }
  362. elseif ($prefs['format'] === 'gzip') // Was a Gzip file requested?
  363. {
  364. return gzencode($this->_backup($prefs));
  365. }
  366. return;
  367. }
  368. }