SproutCMS

This is the code documentation for the SproutCMS project

source of /sprout/Controllers/DbToolsController.php

  1. <?php
  2. /*
  3.  * Copyright (C) 2017 Karmabunny Pty Ltd.
  4.  *
  5.  * This file is a part of SproutCMS.
  6.  *
  7.  * SproutCMS is free software: you can redistribute it and/or modify it under the terms
  8.  * of the GNU General Public License as published by the Free Software Foundation, either
  9.  * version 2 of the License, or (at your option) any later version.
  10.  *
  11.  * For more information, visit <http://getsproutcms.com>.
  12.  */
  13.  
  14. namespace Sprout\Controllers;
  15.  
  16. use DOMDocument;
  17. use Exception;
  18. use InvalidArgumentException;
  19. use PDO;
  20. use RecursiveDirectoryIterator;
  21. use RecursiveIteratorIterator;
  22. use ReflectionMethod;
  23. use ZipArchive;
  24.  
  25. use Kohana;
  26. use Kohana_404_Exception;
  27. use Kohana_Exception;
  28. use karmabunny\pdb\Exceptions\QueryException;
  29. use karmabunny\pdb\Exceptions\RowMissingException;
  30. use Sprout\Exceptions\ValidationException;
  31. use Sprout\Helpers\Admin;
  32. use Sprout\Helpers\AdminAuth;
  33. use Sprout\Helpers\Archive;
  34. use Sprout\Helpers\Auth;
  35. use Sprout\Helpers\Constants;
  36. use Sprout\Helpers\Csrf;
  37. use Sprout\Helpers\DatabaseSync;
  38. use Sprout\Helpers\Email;
  39. use Sprout\Helpers\Enc;
  40. use Sprout\Helpers\Export;
  41. use Sprout\Helpers\ExportTableSQL;
  42. use Sprout\Helpers\Fb;
  43. use Sprout\Helpers\File;
  44. use Sprout\Helpers\FileIndexing;
  45. use Sprout\Helpers\FileUpload;
  46. use Sprout\Helpers\Form;
  47. use Sprout\Helpers\Html;
  48. use Sprout\Helpers\ImportCMS;
  49. use Sprout\Helpers\Inflector;
  50. use Sprout\Helpers\Itemlist;
  51. use Sprout\Helpers\Json;
  52. use Sprout\Helpers\LaunchChecks;
  53. use Sprout\Helpers\Navigation;
  54. use Sprout\Helpers\Needs;
  55. use Sprout\Helpers\Notification;
  56. use Sprout\Helpers\Pdb;
  57. use Sprout\Helpers\QrCode;
  58. use Sprout\Helpers\QueryTo;
  59. use Sprout\Helpers\Register;
  60. use Sprout\Helpers\Request;
  61. use Sprout\Helpers\Router;
  62. use Sprout\Helpers\Session;
  63. use Sprout\Helpers\Sprout;
  64. use Sprout\Helpers\SubsiteSelector;
  65. use Sprout\Helpers\Subsites;
  66. use Sprout\Helpers\Text;
  67. use Sprout\Helpers\TreenodeValueMatcher;
  68. use Sprout\Helpers\Url;
  69. use Sprout\Helpers\Validator;
  70. use Sprout\Helpers\Validity;
  71. use Sprout\Helpers\View;
  72.  
  73.  
  74. /**
  75. * Provides tools for dealing with the database
  76. * Tools include a sync tool and a structure viewer
  77. **/
  78. class DbToolsController extends Controller
  79. {
  80. private $template_enabled = true;
  81.  
  82.  
  83.  
  84. /**
  85.   * List of tools to show in the sidebar and index view
  86.   */
  87. public static $tools = [
  88. 'Development' => [
  89. [ 'url' => 'dbtools/sql', 'name' => 'SQL', 'desc' => 'Allows the user to execute SQL queries' ],
  90. [ 'url' => 'dbtools/sync', 'name' => 'Database sync', 'desc' => 'Syncs the db structure to match db_struct.xml' ],
  91. [ 'url' => 'dbtools/struct', 'name' => 'View db structure', 'desc' => 'Shows table and column definitions' ],
  92. [ 'url' => 'dbtools/testSkinTemplates', 'name' => 'Test skin templates', 'desc' => 'Simple tool for testing skin templates' ],
  93. [ 'url' => 'dbtools/sessionEditor', 'name' => 'Session editor', 'desc' => 'Edit the $_SESSION' ],
  94. [ 'url' => 'dbtools/listRoutes', 'name' => 'Routes inspector', 'desc' => 'View a list of routes' ],
  95. [ 'url' => 'admin/extra/worker_job/manual_run', 'name' => 'Manual run worker job', 'desc' => 'Manually run a worker job in a browser' ],
  96. [ 'url' => 'admin/extra/cron_job/manual_run', 'name' => 'Manual run cron job', 'desc' => 'Manually run a cron job in a browser' ],
  97. [ 'url' => 'admin/style_guide', 'name' => 'Admin style guide', 'desc' => 'View styles of various admin features - form fields, etc' ],
  98. ],
  99. 'Code generation' => [
  100. [ 'url' => 'dbtools/moduleBuilder', 'name' => 'Module builder', 'desc' => 'Generates blank modules' ],
  101. [ 'url' => 'dbtools/moduleBuilderDb', 'name' => 'Database struct generator', 'desc' => 'Generates db_struct.xml content for a module' ],
  102. [ 'url' => 'dbtools/moduleBuilderExisting', 'name' => 'Module builder from existing', 'desc' => 'Generates modules from an existing db_struct.xml file' ],
  103. [ 'url' => 'dbtools/multimake', 'name' => 'Generate multiedit', 'desc' => 'Generate multiedit code' ],
  104. ],
  105. 'Environment' => [
  106. [ 'url' => 'dbtools/info', 'name' => 'Env and PHP info', 'desc' => 'Sprout information + phpinfo()' ],
  107. [ 'url' => 'dbtools/varDump', 'name' => 'Var dump', 'desc' => 'View session, cookie & server data'],
  108. [ 'url' => 'dbtools/email', 'name' => 'Test email', 'desc' => 'Renders form to send emails' ],
  109. [ 'url' => 'dbtools/launchChecks', 'name' => 'Launch checks', 'desc' => 'Run a series of self-tests to ensure everything is configured correctly' ],
  110. [ 'url' => 'admin/user-agent', 'name' => 'User agent tool', 'desc' => 'Show browser information<br><span>(this link doesn\'t require auth)</span>' ],
  111. [ 'url' => 'dbtools/generatePasswordHash', 'name' => 'Generate password hash', 'desc' => 'Generate a password hash to store in a config file' ],
  112. [ 'url' => 'dbtools/bcryptSpeed', 'name' => 'Test hashing speed', 'desc' => 'Test hasing speed of bcrypt' ],
  113. [ 'url' => 'dbtools/fileTypesIndexingSupport', 'name' => 'File indexing support', 'desc' => 'List of file types which can be indexed for full-text search' ],
  114. ],
  115. 'Logs' => [
  116. [ 'url' => 'dbtools/exceptionLog', 'name' => 'Exception log', 'desc' => 'Browse and search exceptions' ],
  117. [ 'url' => 'admin/intro/cron_job', 'name' => 'Cron job log', 'desc' => 'Cron (scheduled task) log' ],
  118. [ 'url' => 'admin/intro/worker_job', 'name' => 'Worker job log', 'desc' => 'Log of worker (background) processes' ],
  119. ],
  120. 'Migration' => [
  121. [ 'url' => 'dbtools/exportFiles', 'name' => 'Export files', 'desc' => 'Exports all files' ],
  122. [ 'url' => 'dbtools/exportTables', 'name' => 'Export database', 'desc' => 'Export tables to an SQL file' ],
  123. [ 'url' => 'dbtools/importFiles', 'name' => 'Import files', 'desc' => 'Imports a files into the cms' ],
  124. [ 'url' => 'dbtools/importTables', 'name' => 'Import database', 'desc' => 'Import database tables from a .sql file' ],
  125. [ 'url' => 'dbtools/importXML', 'name' => 'Import XML', 'desc' => 'Import Sprout2 CMS export' ],
  126. ],
  127. ];
  128.  
  129.  
  130. /**
  131.   * Constructor
  132.   **/
  133. public function __construct()
  134. {
  135. parent::__construct();
  136.  
  137. // Command-line access does not require auth OR output buffering
  138. if (PHP_SAPI === 'cli') return;
  139.  
  140. // Require remote (super) auth
  141. AdminAuth::checkLogin();
  142. if (AdminAuth::isSuper() !== true) {
  143. Notification::error('Access denied');
  144. Url::redirect('admin');
  145. }
  146.  
  147. // Don't start output buffering for some methods
  148. if (strpos(Router::$method, 'json_') === 0) return;
  149. if (Router::$method == 'gettempfile') return;
  150. if (Router::$method == 'sqlcsv') return;
  151.  
  152. // Execute some code for each module
  153. // This usually just loads some menu items
  154. $module_paths = Register::getModuleDirs();
  155. foreach ($module_paths as $path) {
  156. $path .= '/admin_load.php';
  157. if (file_exists($path)) include_once $path;
  158. }
  159.  
  160. // Load registered API test controllers
  161. $apis = Register::getDbtoolsApi();
  162. if (count($apis) > 0) self::$tools['APIs'] = [];
  163.  
  164. foreach ($apis as $api) {
  165. // Validate
  166. if (empty($api['class']) || empty($api['method'])) continue;
  167.  
  168. // Populate dbtools list
  169. self::$tools['APIs'][] = array(
  170. 'url' => sprintf('dbtools/api/%s/%s', Enc::url($api['class']), Enc::url($api['method'])),
  171. 'name' => !empty($api['title']) ? $api['title'] : $api['class'],
  172. 'desc' => !empty($api['desc']) ? $api['desc'] : 'API test form',
  173. );
  174. }
  175.  
  176. // Output buffering allows the methods to "echo" directly, and then the output
  177. // is captured and wrapped up in a template by the ->template() method
  178. }
  179.  
  180.  
  181. /**
  182.   * Render dbtools template
  183.   *
  184.   * @param string HTML
  185.   * @return void Echos HTML directly
  186.   */
  187. private function template($main_title, $html = null)
  188. {
  189. if (!$this->template_enabled) return;
  190.  
  191. $main_content = ob_get_clean();
  192.  
  193. Needs::fileGroup('jquery.tablesorter');
  194.  
  195. $nav = new View('sprout/dbtools/navigation');
  196.  
  197. $view = new View('sprout/admin/main_layout');
  198. $view->main_title = $main_title;
  199. $view->browser_title = $main_title;
  200. $view->controller_name = 'dbtools';
  201. $view->controller_navigation_name = 'Dev tools';
  202. $view->live_url = '';
  203. $view->nav = $nav->render();
  204. $view->nav_tools = '';
  205. $view->main_content = $main_content . $html;
  206.  
  207. echo $view->render();
  208. }
  209.  
  210.  
  211. /**
  212.   * Shows a list of database tools
  213.   **/
  214. public function index()
  215. {
  216. $view = new View('sprout/dbtools/overview');
  217. $view->sections = self::$tools;
  218. $view->base_class = 'dbtools-box white-box column column-3';
  219.  
  220. $this->template('Database and system tools', $view->render());
  221. }
  222.  
  223.  
  224. /**
  225.   * Output some sprout and platform info
  226.   **/
  227. public function info()
  228. {
  229. $vals = array(
  230. 'PHP version' => phpversion(),
  231. 'PHP sapi' => php_sapi_name(),
  232. 'Server software' => @$_SERVER['SERVER_SOFTWARE'],
  233. 'Server OS' => PHP_OS,
  234. 'IN_PRODUCTION' => IN_PRODUCTION ? 'true' : 'false',
  235. 'DOCROOT' => DOCROOT,
  236. 'KOHANA' => KOHANA,
  237. 'APPPATH' => APPPATH,
  238. 'HTTP_X_FORWARDED_FOR' => @$_SERVER['HTTP_X_FORWARDED_FOR'],
  239. 'REMOTE_ADDR' => @$_SERVER['REMOTE_ADDR'],
  240. 'Request::userIp' => Request::userIp(),
  241. 'Request::method' => Request::method(),
  242. 'Request::isAjax' => (Request::isAjax() ? 'true' : 'false'),
  243. 'Request::protocol' => Request::protocol(),
  244. 'PHP date' => date('Y-m-d H:i:s'),
  245. );
  246.  
  247. try {
  248. $row = Pdb::q("SELECT NOW() AS now, @@session.time_zone AS tz", [], 'row');
  249. $vals['MySQL date'] = $row['now'];
  250. $vals['MySQL TZ'] = $row['tz'];
  251. } catch (Exception $ex) {
  252. $vals['MySQL date'] = 'Lookup failure';
  253. $vals['MySQL TZ'] = 'Lookup failure';
  254. }
  255.  
  256. $vals['Sprout::absRoot'] = Sprout::absRoot();
  257. $vals['Subsite ID'] = SubsiteSelector::$subsite_id;
  258.  
  259. $q = "SELECT id, name, code, active FROM ~subsites ORDER BY id";
  260. $subsites = Pdb::query($q, [], 'arr');
  261.  
  262. $view = new View('sprout/dbtools/php_info');
  263. $view->vals = $vals;
  264. $view->subsites = $subsites;
  265.  
  266. $this->template('Env and PHP info', $view->render());
  267. }
  268.  
  269.  
  270. /**
  271.   * Benchmark the server to find appropriate cost parameter
  272.   */
  273. public function bcryptSpeed()
  274. {
  275. $results = [];
  276. $cost = 8;
  277. $thresh_secs = 0.5;
  278. do {
  279. $start = microtime(true);
  280. password_hash("test", PASSWORD_BCRYPT, ['cost' => $cost]);
  281. $time_secs = microtime(true) - $start;
  282. $results[$cost] = $time_secs;
  283. ++$cost;
  284. } while ($time_secs < $thresh_secs);
  285.  
  286. echo '<p>This tool reports the time required to hash a password using bcrypt and varying levels of difficulty cost.</p>';
  287. echo '<pre>';
  288. foreach ($results as $cost => $time_secs) {
  289. echo 'Cost ', $cost, ' took ', number_format($time_secs * 1000, 2), ' ms', PHP_EOL;
  290. }
  291. echo '</pre>';
  292.  
  293. $this->template('Test hashing speed');
  294. }
  295.  
  296.  
  297. /**
  298.   * Renders SQL result set into a table
  299.   *
  300.   * @param PDOStatement $results Query result
  301.   * @param mixed
  302.   * @return int Number of rows
  303.   */
  304. private function outputSqlResultset($results, $headings = null)
  305. {
  306. if ($results->columnCount() == 0) return;
  307. if ($results->rowCount() == 0) return;
  308.  
  309. $results->setFetchMode(PDO::FETCH_NUM);
  310. $columns = [];
  311.  
  312. for ($i = 0; $i < $results->columnCount(); ++$i) {
  313. $col = $results->getColumnMeta($i);
  314. $columns[] = $col['name'];
  315. }
  316.  
  317. $view = new View('sprout/dbtools/sql_result');
  318. $view->results = $results;
  319. $view->columns = $columns;
  320. $view->render(true);
  321.  
  322. return $results->rowCount();
  323. }
  324.  
  325. #
  326. # Tools are below.
  327. #
  328.  
  329.  
  330. /**
  331.   * Syncs the db structure to match db_struct.xml
  332.   **/
  333. public function sync()
  334. {
  335. $act = false;
  336. if (isset($_POST['act']) and $_POST['act'] === 'yes') $act = true;
  337. if (PHP_SAPI === 'cli') $act = true;
  338.  
  339. // If there is no tables, act straight away without asking the user
  340. $num = 0;
  341. try {
  342. $num += (int) Pdb::query("SELECT COUNT(*) FROM ~operators", [], 'val');
  343. $num += (int) Pdb::query("SELECT COUNT(*) FROM ~pages", [], 'val');
  344. $num += (int) Pdb::query("SELECT COUNT(*) FROM ~subsites", [], 'val');
  345. } catch (Exception $ex) {}
  346. if ($num == 0) {
  347. $act = true;
  348. }
  349.  
  350. $sync = new DatabaseSync($act);
  351.  
  352. $chk = $sync->checkConnPermissions();
  353. if ($chk !== true) {
  354. echo '<p>Insufficent database permissions for this tool.</p>';
  355. echo '<p>Additional permissions required: ', implode(', ', $chk), '.</p>';
  356. return;
  357. }
  358.  
  359. $sync->loadStandardXmlFiles();
  360. $sync->sanityCheck();
  361.  
  362. if ($sync->hasLoadErrors()) {
  363. echo $sync->getLoadErrorsHtml();
  364. $this->template('Database sync');
  365. return;
  366. }
  367.  
  368. $out = $sync->updateDatabase();
  369. if (PHP_SAPI === 'cli') {
  370. if ($out) {
  371. $out = trim($out);
  372. $out = preg_replace('!<h3>(.+?)</h3>!', '== $1 ==', $out);
  373. $out = str_replace('<pre class="query">', ' --> ', $out);
  374. echo strip_tags($out);
  375. echo PHP_EOL;
  376. } else {
  377. echo 'Everything is up to date.', PHP_EOL;
  378. }
  379. return;
  380. }
  381.  
  382. if ($out == '') {
  383. echo '<p>Everything is up to date.</p>';
  384. } else {
  385. echo '<style>';
  386. echo '.update-log .query { color: blue; border: none; padding: 0 0 0 100px; }';
  387. echo '.update-log b { display: inline-block; width: 100px; }';
  388. echo '.update-log p.heading { margin: 20px 0 5px; }';
  389. echo '.update-log pre { margin-bottom: 0; }';
  390. echo '</style>';
  391. if (!$act) {
  392. echo '<form action="dbtools/sync" method="post">';
  393. echo '<input type="hidden" name="act" value="yes">';
  394. echo '<div class="action-bar"><button type="submit" class="button button-orange icon-after icon-loop">Run this sync</button></div>';
  395. echo '</form>';
  396. }
  397. echo '<div class="update-log">';
  398. echo $out;
  399. echo '</div>';
  400. if (!$act) {
  401. echo '<form action="dbtools/sync" method="post">';
  402. echo '<input type="hidden" name="act" value="yes">';
  403. echo '<div class="action-bar"><button type="submit" class="button button-orange icon-after icon-loop">Run this sync</button></div>';
  404. echo '</form>';
  405. }
  406. }
  407.  
  408. // Clear the Kohana caches too
  409. if (file_exists(APPPATH . 'cache/kohana_configuration')) {
  410. unlink(APPPATH . 'cache/kohana_configuration');
  411. }
  412. if (file_exists(APPPATH . 'cache/kohana_find_file_paths')) {
  413. unlink(APPPATH . 'cache/kohana_find_file_paths');
  414. }
  415. if (file_exists(APPPATH . 'cache/kohana_language')) {
  416. unlink(APPPATH . 'cache/kohana_language');
  417. }
  418.  
  419. $this->template('Database sync');
  420. }
  421.  
  422.  
  423. /**
  424.   * Shows table and column definitions
  425.   **/
  426. public function struct($arg = '')
  427. {
  428. // Show columns if a table was specified
  429. if (!empty($arg)) {
  430. echo "<h3>Columns from {$arg}</h3>";
  431. $q = "SHOW COLUMNS FROM `{$arg}`";
  432. $res = Pdb::query($q, [], 'pdo');
  433. $this->outputSqlResultset($res);
  434. $res->closeCursor();
  435.  
  436. echo "<h3>Example data from {$arg}</h3>";
  437. $q = "SELECT * FROM `{$arg}` LIMIT 3";
  438. $res = Pdb::query($q, [], 'pdo');
  439. $count = $this->outputSqlResultset($res);
  440. $res->closeCursor();
  441. if ($count == 0) echo '<p><i>No data is in this table at this time</i></p>';
  442. }
  443.  
  444.  
  445. // Show a list of tables
  446. $params = [];
  447. $q = "SHOW TABLE STATUS";
  448. if (!empty($_GET['search'])) {
  449. $q .= " WHERE NAME LIKE CONCAT('%', ?, '%')";
  450. $params[] = $_GET['search'];
  451. }
  452. $res = Pdb::query($q, $params, 'pdo');
  453.  
  454. $ignore_cols = ['Row_format', 'Max_data_length', 'Auto_increment', 'Comment', 'Version', 'Create_time',
  455. 'Update_time', 'Check_time', 'Collation', 'Checksum', 'Create_options'];
  456. $headings = array();
  457. for ($i = 0; $i < $res->columnCount(); ++$i) {
  458. $meta = $res->getColumnMeta($i);
  459. $val = $meta['name'];
  460. if (in_array($val, $ignore_cols)) continue;
  461. $headings[$val] = $val;
  462. }
  463.  
  464. // Remove ignored columns
  465. $results = [];
  466. foreach ($res as $row) {
  467. foreach ($ignore_cols as $ignore) {
  468. unset($row[$ignore]);
  469. }
  470.  
  471. $columns = [];
  472. foreach ($row as $name => $val) {
  473. $columns[$name] = $val;
  474. }
  475. $results[] = $columns;
  476. }
  477.  
  478. $res->closeCursor();
  479.  
  480. $view = new View('sprout/dbtools/db_struct');
  481. $view->headings = $headings;
  482. $view->results = $results;
  483.  
  484. $this->template('Database structure', $view->render());
  485. }
  486.  
  487.  
  488. /**
  489.   * Splits a set of SQL queries into individual queries
  490.   **/
  491. private function splitSql($all)
  492. {
  493. $all = preg_replace('/^\s*--.*$/m', '', $all);
  494.  
  495. $queries = array();
  496.  
  497. $offset = 0;
  498. $length = strlen($all);
  499. $query = '';
  500. $quote = '';
  501.  
  502. while ($offset < $length) {
  503. // Search for end-of-statement
  504. if ($quote == '' and $all[$offset] == ';') {
  505. $queries[] = $query;
  506. $query = '';
  507. $offset++;
  508. continue;
  509. }
  510.  
  511. if ($quote != '' and $all[$offset] == '\\') {
  512. $query .= '\\';
  513. $offset++;
  514. $query .= $all[$offset];
  515. $offset++;
  516. continue;
  517. }
  518.  
  519. if ($all[$offset] == "'") {
  520. if ($quote == "'") {
  521. $quote = '';
  522. } else if ($quote == '') {
  523. $quote = "'";
  524. }
  525. }
  526.  
  527. if ($all[$offset] == '"') {
  528. if ($quote == '"') {
  529. $quote = '';
  530. } else if ($quote == '') {
  531. $quote = '"';
  532. }
  533. }
  534.  
  535. $query .= $all[$offset];
  536. $offset++;
  537. }
  538.  
  539. if ($query) $queries[] = $query;
  540.  
  541. return $queries;
  542. }
  543.  
  544.  
  545. /**
  546.   * Allows the user to execute SQL queries
  547.   **/
  548. public function sql()
  549. {
  550. Needs::fileGroup('sprout/dbtools_sql');
  551. $out = '';
  552.  
  553. $vars = [0 => []];
  554. $binds = [];
  555. if (@is_array($_POST['vars'])) {
  556. $idx = 0;
  557. foreach ($_POST['vars'] as $var) {
  558. if (empty($var['key']) and (!isset($var['val']) or $var['val'] === '')) continue;
  559.  
  560. $key = (string) @$var['key'];
  561. $val = (string) @$var['val'];
  562. if ($key) {
  563. $binds[$key] = $val;
  564. } else {
  565. $binds[] = $val;
  566. }
  567. $vars[$idx]['key'] = $key;
  568. $vars[$idx]['val'] = $val;
  569. ++$idx;
  570. }
  571. }
  572.  
  573.  
  574. // Split up the queries
  575. if (isset($_POST['sql'])) {
  576. Csrf::checkOrDie();
  577.  
  578. $successes = 0;
  579. $failures = 0;
  580.  
  581. $queries = $this->splitSql($_POST['sql']);
  582.  
  583. // Execute the queries
  584. foreach ($queries as $q) {
  585. $q = trim($q);
  586. $res = false;
  587.  
  588. if ($q == '') continue;
  589.  
  590. $out .= "<div class=\"sql-block\">\n";
  591.  
  592. $out .= '<pre class="sql">' . Enc::html($q) . '</pre>';
  593.  
  594. if (!empty($_POST['profile'])) {
  595. Pdb::query("SET profiling=1", [], 'count');
  596. }
  597.  
  598. $bind_subset = Pdb::getBindSubset($q, $binds);
  599. try {
  600. $res = Pdb::query($q, $bind_subset, 'pdo');
  601. $successes ++;
  602. } catch (QueryException $ex) {
  603. $out .= '<ul class="messages all-type-error"><li class="error">';
  604. $out .= nl2br(Enc::html($ex->getMessage()));
  605.  
  606. $failures ++;
  607.  
  608. // If a DROP TABLE query fails due to a foreign key constraint, list the constraining columns
  609. if ($ex->state == 23000) {
  610. $matches = [];
  611. if (preg_match('/^DROP\s+TABLE\s+~([a-z0-9_]+)$/i', $q, $matches)) {
  612. $deps = Pdb::getDependentKeys($matches[1]);
  613. if (count($deps) > 0) {
  614. $out .= '<p style="margin-bottom: 0;">The following columns link to the specified table:</p>';
  615. $out .= '<p style="padding-left: 30px; margin: 0;">';
  616. $out = '';
  617. foreach ($deps as $dep) {
  618. $out .= Enc::html("{$dep['table']}.{$dep['column']}") . '<br>';
  619. }
  620. $out .= substr($out, 0, -4);
  621. $out .= '</p>';
  622. }
  623. }
  624. }
  625. $out .= '</li></ul>';
  626. }
  627.  
  628. if (!empty($_POST['profile'])) {
  629. Pdb::query("SET profiling=0", [], 'count');
  630. }
  631.  
  632. if (! $res) continue;
  633.  
  634. $out .= sprintf('<ul class="messages"><li class="neutral neutral-grey">%u %s</li></ul>', $res->rowCount(), Inflector::singular('rows', $res->rowCount()));
  635.  
  636. $this->outputSqlResultset($res);
  637. $out .= ob_get_clean();
  638.  
  639. $res->closeCursor();
  640.  
  641. if (!empty($_POST['explain'])) {
  642. $q = "EXPLAIN {$q}";
  643. $res = Pdb::query($q, [], 'pdo');
  644.  
  645. $this->outputSqlResultset($res);
  646. $out .= ob_get_clean();
  647.  
  648. $res->closeCursor();
  649. }
  650. $out .= "</div>\n";
  651. }
  652.  
  653. if ((!empty($queries) and count($queries) > 0) and $failures > 0) {
  654. Notification::error(sprintf('Failed to execute %u %s. Scroll down for results', $failures, Inflector::singular('queries', $failures)));
  655. }
  656.  
  657. if ((!empty($queries) and count($queries) > 0) and $successes > 0) {
  658. Notification::confirm(sprintf('Executed %u %s. Scroll down for results', $successes, Inflector::singular('queries', $successes)));
  659. }
  660.  
  661. // Show profiling info
  662. if (!empty($_POST['profile'])) {
  663.  
  664. $q = "SHOW PROFILES";
  665. $res = Pdb::query($q, [], 'arr');
  666.  
  667. foreach ($res as $row) {
  668. $q = "SHOW PROFILE FOR QUERY {$row['Query_ID']}";
  669. $res2 = Pdb::query($q, [], 'pdo');
  670.  
  671. $out .= "<h3>Query #{$row['Query_ID']}; total duration: {$row['Duration']}</h3>";
  672. $out .= '<pre class="sql">' . Enc::html($row['Query']) . '</pre>';
  673.  
  674. $this->outputSqlResultset($res2);
  675. $out .= ob_get_clean();
  676.  
  677. $res2->closeCursor();
  678. }
  679. }
  680. }
  681.  
  682. $out .= '</div>';
  683.  
  684. $res = Pdb::query('SHOW TABLES', [], 'col');
  685. $tables = [];
  686. foreach ($res as $row) {
  687. $tables[] = preg_replace('/^' . preg_quote(Pdb::prefix()) . '/', '~', $row);
  688. }
  689.  
  690. $view = new View('sprout/dbtools/sql');
  691. $view->vars = $vars;
  692. $view->tables = $tables;
  693. $view->results = $out;
  694.  
  695. echo $view->render();
  696. $this->template('SQL query');
  697. }
  698.  
  699.  
  700. public function sqlcsv()
  701. {
  702. AdminAuth::checkLogin();
  703. Csrf::checkOrDie();
  704.  
  705. if (empty($_POST['sql'])) {
  706. Url::redirect('dbtools/sql');
  707. }
  708.  
  709. $rows = Pdb::q($_POST['sql'], [], 'pdo');
  710. $csv = QueryTo::csv($rows);
  711. $rows->closeCursor();
  712.  
  713. if (!$csv) {
  714. echo "CSV generation failed";
  715. return;
  716. }
  717.  
  718. header('Content-type: text/csv');
  719. header('Content-disposition: attachment; filename="sql.csv"');
  720. echo $csv;
  721. }
  722.  
  723.  
  724. public function ajaxTableDefn($table)
  725. {
  726. $table = trim($table, '~');
  727. try {
  728. Pdb::validateIdentifier($table);
  729. } catch (InvalidArgumentException $ex) {
  730. return;
  731. }
  732.  
  733. // Get FKs and group them by columns
  734. $fks = [];
  735. $fk_defs = Pdb::getForeignKeys($table);
  736. foreach ($fk_defs as $fk) {
  737. $fks[$fk['source_column']][] = "{$fk['target_table']}.{$fk['target_column']}";
  738. }
  739.  
  740. $allowed_keys = ['Field', 'Type', 'Null', 'Key', 'Default', 'Collation'];
  741. $raw_res = Pdb::q("SHOW FULL COLUMNS FROM ~{$table}", [], 'pdo');
  742. $res = [];
  743. foreach ($raw_res as $raw_row) {
  744. $row = [];
  745. foreach ($allowed_keys as $key) {
  746. $row[$key] = $raw_row[$key];
  747. }
  748. if (isset($fks[$row['Field']])) {
  749. $row['FK'] = implode(', ', $fks[$row['Field']]);
  750. } else {
  751. $row['FK'] = '';
  752. }
  753. $res[] = $row;
  754. }
  755. $raw_res->closeCursor();
  756.  
  757. echo Json::out($res);
  758. }
  759.  
  760.  
  761. /**
  762.   * Import database tables from a .sql file
  763.   **/
  764. public function importTables()
  765. {
  766. if (class_exists('ZipArchive')) {
  767. echo '<p><em>Accepts raw SQL files, and zip archives.</em></p>';
  768. } else {
  769. echo '<p><em>Accepts raw SQL files.</em></p>';
  770. }
  771.  
  772. echo '<form action="SITE/dbtools/importSave" method="post" enctype="multipart/form-data">';
  773. echo Csrf::token();
  774. Form::nextFieldDetails('File', true);
  775. echo Form::upload('filename');
  776. echo '<div class="action-bar"><button type="submit" class="button icon-after icon-file_upload">Upload file</button></div>';
  777. echo '</form>';
  778.  
  779. $this->template('Import tables');
  780. }
  781.  
  782.  
  783. public function importSave()
  784. {
  785. Csrf::checkOrDie();
  786.  
  787. $ext = strtolower(File::getExt($_FILES['filename']['name']));
  788.  
  789. $valid_exts = ['sql'];
  790. if (class_exists('ZipArchive')) {
  791. $valid_exts[] = 'zip';
  792. }
  793.  
  794. if (!in_array($ext, $valid_exts)) {
  795. echo "Invalid file type, suported types: " . implode(', ', $valid_exts);
  796. return;
  797. }
  798.  
  799. // Determine temp filename
  800. $timestamp = time();
  801. $tempname = "dbtools_import_{$timestamp}.{$ext}";
  802.  
  803. $res = @copy($_FILES['filename']['tmp_name'], APPPATH . 'temp/' . $tempname);
  804. if (! $res) {
  805. echo 'Unable to copy file to temporary directory';
  806. return;
  807. }
  808.  
  809. Url::redirect('dbtools/importOptions?tempname=' . Enc::url($tempname));
  810. }
  811.  
  812.  
  813.  
  814. public function importOptions()
  815. {
  816. echo '<p>Uploaded file: <code>', Enc::html($_GET['tempname']), '</code></p>';
  817.  
  818. $tempname = APPPATH . 'temp/' . $_GET['tempname'];
  819. if (File::getExt($tempname) == 'zip') {
  820. $za = new ZipArchive();
  821. $za->open($tempname);
  822. $sql = 0;
  823. for ($i = 0; $i < $za->numFiles; $i++) {
  824. $info = $za->statIndex($i);
  825. if (preg_match('/\.sql/', $info['name'])) {
  826. $sql++;
  827. }
  828. }
  829. $za->close();
  830.  
  831. echo "<p>It's a zip file containing {$sql} SQL files.</p>";
  832.  
  833. } else if (File::getExt($tempname) == 'sql') {
  834. echo "<p>It's a single SQL file.</p>";
  835. }
  836.  
  837.  
  838. echo '<form action="SITE/dbtools/importAction" method="post" target="process" onsubmit="$(\'iframe\').show(); $(this).find(\'.action-bar\').remove();">';
  839. echo Csrf::token();
  840. echo ' <input type="hidden" name="tempname" value="', Enc::html($_GET['tempname']), '">';
  841. echo ' <div class="action-bar"><button type="submit" class="button icon-before icon-check">Process file</button></div>';
  842. echo '</form>';
  843.  
  844. echo '<iframe name="process" style="border: 1px #999 dashed; margin: 30px 0; width: 700px; height: 300px; display: none;"></iframe>';
  845.  
  846. $this->template('Import tables');
  847. }
  848.  
  849. /**
  850.   * Action for importing tables
  851.   **/
  852. public function importAction()
  853. {
  854. Csrf::checkOrDie();
  855. Kohana::closeBuffers();
  856. echo '<style>body { font-size: 11px; font-family: sans-serif; } p { margin: 0; }</style>';
  857.  
  858.  
  859. $tempname = APPPATH . 'temp/' . $_POST['tempname'];
  860. echo "<p>Processing: '{$tempname}'.</p>";
  861.  
  862.  
  863. // Rip SQL files out of ZIP file
  864. $sql_files = array();
  865. if (File::getExt($tempname) == 'zip') {
  866. $za = new ZipArchive();
  867. $za->open($tempname);
  868.  
  869. for ($i = 0; $i < $za->numFiles; $i++) {
  870. $info = $za->statIndex($i);
  871.  
  872. if (preg_match('/\.sql/', $info['name'])) {
  873. echo "<p>Found sql file in zip: '{$info['name']}'.</p>";
  874. $sql_files[] = $za->getFromIndex($i);
  875. }
  876. }
  877.  
  878. $za->close();
  879. unset($za);
  880.  
  881. } else if (File::getExt($tempname) == 'sql') {
  882. echo "<p>Found sql file: '{$tempname}'.</p>";
  883. $sql_files[] = file_get_contents($tempname);
  884. }
  885.  
  886. unlink($tempname);
  887.  
  888.  
  889. echo '<p>Processing ' . count($sql_files) . ' sql file(s).</p>';
  890. @ob_flush(); @flush(); usleep(100 * 1000);
  891.  
  892. // Process files
  893. $idx = 0;
  894. foreach ($sql_files as $file) {
  895. $idx++;
  896. echo '<p>Processing file # ', $idx, '.</p>';
  897. @ob_flush(); @flush(); usleep(100 * 1000);
  898.  
  899. $queries = $this->splitSql($file);
  900.  
  901. echo '<p>File # ', $idx, ' contains ', count($queries), ' queries.</p>';
  902. @ob_flush(); @flush(); usleep(100 * 1000);
  903.  
  904. Pdb::q("SET FOREIGN_KEY_CHECKS=0", [], 'null');
  905.  
  906. $qidx = 0;
  907. foreach ($queries as $q) {
  908. $q = trim($q);
  909. if ($q == '') continue;
  910.  
  911. try {
  912. Pdb::q($q, [], 'count');
  913. } catch (Exception $ex) {
  914. echo '<p>Failed query: <code>' . Enc::html($q) . '</code>, exception: ',
  915. Enc::html($ex->getMessage()), '</p>';
  916. }
  917.  
  918. unset($q);
  919.  
  920. $qidx++;
  921. if ($qidx % 1000 == 0) {
  922. echo '<p>Done ', $qidx, ' queries.</p>';
  923. @ob_flush(); @flush(); usleep(100 * 1000);
  924. }
  925. }
  926.  
  927. unset($queries);
  928.  
  929. Pdb::q("SET FOREIGN_KEY_CHECKS=1", [], 'null');
  930.  
  931. echo '<p>File # ', $idx, ' finished.</p>';
  932. @ob_flush(); @flush(); usleep(100 * 1000);
  933. }
  934.  
  935. echo '<p>Import complete, running sync...</p>';
  936.  
  937. $sync = new DatabaseSync(true);
  938. $sync->loadStandardXmlFiles();
  939. $sync->sanityCheck();
  940.  
  941. if ($sync->hasLoadErrors()) {
  942. echo $sync->getLoadErrorsHtml();
  943. }
  944.  
  945. echo $sync->updateDatabase();
  946.  
  947. echo '<p>Sync complete</p>';
  948.  
  949. }
  950.  
  951.  
  952. /**
  953.   * Run a series of self-tests to ensure everything is configured correctly
  954.   **/
  955. public function launchChecks()
  956. {
  957. $results = LaunchChecks::runTests();
  958.  
  959. echo '<style>';
  960. echo '.status--okay { color: #005306; }';
  961. echo '.status--warning { color: #C26600; }';
  962. echo '.status--error { color: #B20000; }';
  963. echo '</style>';
  964.  
  965. $itemlist = new Itemlist();
  966. $itemlist->items = $results;
  967. $itemlist->main_columns = [
  968. 'Check' => 'check',
  969. 'Skin' => 'skin',
  970. 'Result' => 'result',
  971. 'Message' => 'message',
  972. ];
  973. $itemlist->setRowClassesFunc(function($row){
  974. return 'status--' . $row['result'];
  975. });
  976. echo $itemlist->render();
  977.  
  978. $this->template('Launch checks');
  979. }
  980.  
  981.  
  982. /**
  983.   * Returns a list of file types which can be indexed for fulltext search
  984.   **/
  985. public function fileTypesIndexingSupport()
  986. {
  987. $exts = array(
  988. 'txt' => '',
  989. 'csv' => '',
  990. 'pdf' => 'pdftotext',
  991. 'doc' => 'antiword',
  992. 'docx' => 'perl',
  993. 'odt' => 'odt2txt',
  994. 'xls' => 'xls2csv'
  995. );
  996.  
  997. $out = '<table class="main-list">';
  998. $out .= '<thead><tr><th>Format</th><th>Supported?</th></thead>';
  999. $out .= '<tbody>';
  1000. foreach ($exts as $e => $pkg) {
  1001. $out .= '<tr>';
  1002. $out .= '<td>' . $e . '</td>';
  1003.  
  1004. if (FileIndexing::isExtSupported($e)) {
  1005. $out .= '<td>Yes</td>';
  1006. } else {
  1007. $out .= '<td><span style="color: #900;">No.</span> Please install "' . $pkg . '".</td>';
  1008. }
  1009.  
  1010. $out .= '</tr>';
  1011. }
  1012. $out .= '</tbody>';
  1013. $out .= '</table>';
  1014.  
  1015. echo $out;
  1016. $this->template('File types with indexing support');
  1017. }
  1018.  
  1019.  
  1020. /**
  1021.   * Export tables to an SQL file
  1022.   **/
  1023. public function exportTables()
  1024. {
  1025. ?>
  1026. <script type="text/javascript">
  1027. $(document).ready(function() {
  1028. $('a.next-toggle').click(function() {
  1029. $(this).next().toggle();
  1030. return false;
  1031. });
  1032.  
  1033. $('a.next-toggle').next().hide();
  1034. });
  1035. </script>
  1036. <?php
  1037.  
  1038.  
  1039. // Show a list of tables
  1040. $q = "SHOW TABLE STATUS";
  1041. $db_tables = Pdb::q($q, [], 'arr');
  1042.  
  1043. ?>
  1044. <p><a class="preview" id="select-all-none" href="#">Select all/none</a></p>
  1045. <script type="text/javascript">
  1046. $('#select-all-none').click(function(){
  1047. var all_checked = true;
  1048. $("input[name*='tables[']").each(function() {
  1049. if (!$(this).attr('checked')) all_checked = false;
  1050. });
  1051. if (all_checked) {
  1052. $("input[name*='tables[']").each(function() {
  1053. $(this).removeAttr('checked');
  1054. });
  1055. } else {
  1056. $("input[name*='tables[']").each(function() {
  1057. $(this).attr('checked', 'checked');
  1058. });
  1059. }
  1060. return false;
  1061. });
  1062. </script>
  1063. <?php
  1064.  
  1065. echo '<form action="SITE/dbtools/exportAction" method="post">';
  1066. echo Csrf::token();
  1067. echo '<table class="main-list main-list-no-js">';
  1068. echo '<col width="1"><col width="230"><col width="40"><col width="40"><col width="200"><col width="200"><col width="1"><col width="1"><col width="75">';
  1069. echo '<thead><tr><th style="width: 1px;">&nbsp;</th>';
  1070. echo '<th>Name</th><th>Rows</th><th>Size</th><th colspan="2">Options</th><th>Drop</th><th>Structure</th><th>Data</th>';
  1071. echo '</tr></thead>';
  1072. echo '<tbody>';
  1073. $idx = 0;
  1074. $prefix = Pdb::prefix();
  1075. foreach ($db_tables as $table) {
  1076. $column_options = '<p>No options yet.</p>';
  1077.  
  1078. $where_options = '<p><b>Where clause:</b>';
  1079. $where_options .= "<br><input type=\"text\" name=\"where[{$idx}]\"></p>";
  1080.  
  1081. $type_options = "<select name=\"data[{$idx}]\">";
  1082. $type_options .= '<option value="' . ExportTableSQL::DATA_INSERT . '">Insert</option>';
  1083. $type_options .= '<option value="' . ExportTableSQL::DATA_BOTH . '">Insert...update</option>';
  1084. $type_options .= '<option value="' . ExportTableSQL::DATA_UPDATE .'">Update</option>';
  1085. $type_options .= '<option value="' . ExportTableSQL::DATA_CSV . '">CSV file</option>';
  1086. $type_options .= '<option value="' . ExportTableSQL::DATA_NONE . '">None</option>';
  1087. $type_options .= '</select>';
  1088.  
  1089. $checked = '';
  1090. if (strpos($table['Name'], $prefix) === 0) $checked = ' checked';
  1091.  
  1092. echo '<tr>';
  1093. echo "<td><input type=\"checkbox\" name=\"tables[{$idx}]\" value=\"{$table['Name']}\"{$checked}></td>";
  1094. echo "<td>{$table['Name']}</td>";
  1095. echo "<td>{$table['Rows']}</td>";
  1096. echo "<td>" . $this->sizeToHuman($table['Data_length']) . "</td>";
  1097. echo '<td><a href="#" class="next-toggle">Columns</a><div>' . $column_options . '</div></td>';
  1098. echo '<td><a href="#" class="next-toggle">Where clause</a><div>' . $where_options . '</div></td>';
  1099. echo "<td><input type=\"checkbox\" name=\"drop[{$idx}]\" value=\"1\" checked></td>";
  1100. echo "<td><input type=\"checkbox\" name=\"structure[{$idx}]\" value=\"1\" checked></td>";
  1101. echo '<td>' . $type_options . '</td>';
  1102. echo '</tr>';
  1103.  
  1104. $idx++;
  1105. }
  1106. echo '</tbody>';
  1107. echo '</table>';
  1108.  
  1109. echo '<p><input type="checkbox" name="split_table" value="1"> Split the export per table.</p>';
  1110. echo '<p><input type="checkbox" name="split_size" value="1" checked> Split the export into chunks no bigger than <input type="text" name="split_amount" value="8m">. <small>(prefixes: k, m, g)</small></p>';
  1111.  
  1112. echo '<p><input type="checkbox" name="compress" value="1" checked> Compress the file into a zip archive.</p>';
  1113.  
  1114. echo '<p><b>DBMS:</b> &nbsp; <select name="dbms" style="width: 100px;"><option>MySQL<option>SQLite</select></p>';
  1115.  
  1116. echo '<div class="action-bar"><button type="submit" class="button icon-after icon-save">Export</button></div>';
  1117. echo '</form>';
  1118.  
  1119.  
  1120. $this->template('Export tables');
  1121. }
  1122.  
  1123.  
  1124. /**
  1125.   * Render table data size in human readable form
  1126.   * @param int $size
  1127.   * @return string HTML
  1128.   */
  1129. private function sizeToHuman($size)
  1130. {
  1131. static $types = ['Bytes', 'KB', 'MB', 'GB', 'TB'];
  1132. $size = (int) $size;
  1133.  
  1134. $type = 0;
  1135. while ($size > 1024) {
  1136. $size /= 1024;
  1137. $type++;
  1138. if ($type > 5) break;
  1139. }
  1140.  
  1141. return sprintf('%s&nbsp;%s', round($size, 1), $types[$type]);
  1142. }
  1143.  
  1144.  
  1145. /**
  1146.   * Does the actual export
  1147.   *
  1148.   * @post tables Array of tables
  1149.   * @post dbms DBMS - MySQL or SQLite
  1150.   * @post split_tables Split the export per table
  1151.   * @post split_size Split the export by size
  1152.   * @post split_amount Split the export by size - size in kb, mb or gb
  1153.   * @post compress Should the data be compressed
  1154.   * @return void Outputs HTML directly
  1155.   **/
  1156. public function exportAction()
  1157. {
  1158. Csrf::checkOrDie();
  1159.  
  1160. if (empty($_POST['tables'])) {
  1161. throw new InvalidArgumentException('No tables specified');
  1162. }
  1163.  
  1164. $temp_filename = 'temp/export-' . time() . '-';
  1165.  
  1166. $class = 'Sprout\Helpers\ExportDBMS_' . $_POST['dbms'];
  1167. $dbms = Sprout::instance($class);
  1168.  
  1169. $export = new Export();
  1170. $export->setFilenamePrefix(APPPATH . $temp_filename);
  1171. $export->setDbms($dbms);
  1172.  
  1173. if (!empty($_POST['split_table'])) {
  1174. $export->split_table = true;
  1175. }
  1176.  
  1177. $matches = array();
  1178. if (
  1179. isset($_POST['split_size'])
  1180. and isset($_POST['split_amount'])
  1181. and preg_match('/([0-9]+)\s?([kmg])/', $_POST['split_amount'], $matches)
  1182. ) {
  1183. if ($matches[2] == 'k') $amt = $matches[1] * 1000;
  1184. if ($matches[2] == 'm') $amt = $matches[1] * 1000 * 1000;
  1185. if ($matches[2] == 'g') $amt = $matches[1] * 1000 * 1000 * 1000;
  1186. $export->split_size = $amt;
  1187. }
  1188.  
  1189. foreach ($_POST['tables'] as $idx => $table_name) {
  1190. $table = new ExportTableSQL();
  1191. $table->name = $table_name;
  1192. $table->drop = (bool)@$_POST['drop'][$idx];
  1193. $table->structure = (bool)@$_POST['structure'][$idx];
  1194. $table->data = $_POST['data'][$idx];
  1195. $table->where = $_POST['where'][$idx];
  1196.  
  1197. $export->addTable($table);
  1198. }
  1199.  
  1200. $export->exportSql();
  1201.  
  1202. if (!empty($_POST['compress'])) {
  1203. $name = Kohana::config('sprout.site_title');
  1204. $name = str_replace(' ', '_', strtolower($name));
  1205. $name = preg_replace('/[^a-z_]/', '', $name);
  1206.  
  1207. $name = 'sql_' . date('Y-m-d') . '_' . $name . '.zip';
  1208. $export->buildArchive($name);
  1209. }
  1210.  
  1211. $files = $export->getGeneratedFiles();
  1212. foreach ($files as $f) {
  1213. $f_url = basename($temp_filename . $f);
  1214. echo "<p><a href=\"SITE/dbtools/gettempfile/export-database/{$f_url}/{$f}\">{$f}</a></p>";
  1215. }
  1216.  
  1217. $this->template('Export tables');
  1218. }
  1219.  
  1220.  
  1221. /**
  1222.   * Imports a files into the cms
  1223.   **/
  1224. public function importFiles()
  1225. {
  1226. if (! class_exists('ZipArchive')) {
  1227. echo '<p><em>ARGH! No ZIP support!</em></p>';
  1228. return;
  1229. }
  1230.  
  1231. echo '<form action="SITE/dbtools/importFileAction" method="post" enctype="multipart/form-data">';
  1232. echo Csrf::token();
  1233. Form::nextFieldDetails('File', true);
  1234. echo Form::upload('filename');
  1235. echo '<div class="action-bar"><button type="submit" class="button icon-after icon-file_upload">Import files</button></div>';
  1236. echo '</form>';
  1237.  
  1238. $this->template('Import files');
  1239. }
  1240.  
  1241. /**
  1242.   * Action for importing files
  1243.   **/
  1244. public function importFileAction()
  1245. {
  1246. Csrf::checkOrDie();
  1247.  
  1248. if (! class_exists('ZipArchive')) {
  1249. echo '<p><em>ARGH! No ZIP support!</em></p>';
  1250. return;
  1251. }
  1252.  
  1253. if (empty($_FILES['filename']) or $_FILES['filename']['error'] !== UPLOAD_ERR_OK) {
  1254. Notification::error('There was an error uploading your file, please try again.');
  1255. Url::redirect('dbtools/importFiles');
  1256. }
  1257.  
  1258. if (File::getExt($_FILES['filename']['name']) !== 'zip') {
  1259. Notification::error('Invalid file type; only .zip files are supported.');
  1260. Url::redirect('dbtools/importFiles');
  1261. }
  1262.  
  1263. copy($_FILES['filename']['tmp_name'], APPPATH . 'temp/import.zip');
  1264.  
  1265. $za = new ZipArchive();
  1266. $za->open(APPPATH . 'temp/import.zip');
  1267.  
  1268. // Check for disallowed file types
  1269. $invalid = [];
  1270. for ($i = 0; $i < $za->numFiles; $i++) {
  1271. $filename = $za->getNameIndex($i);
  1272. if (!FileUpload::checkFilename($filename)) {
  1273. $invalid[] = $filename;
  1274. }
  1275. }
  1276.  
  1277. // If there are any disallowed files in the ZIP, then stop
  1278. if (count($invalid)) {
  1279. $za->close();
  1280. unlink(APPPATH . 'temp/import.zip');
  1281. header('Content-type: text/plain');
  1282. echo "DISALLOWED FILES FOUND:\n - ", implode("\n - ", $invalid);
  1283. die(1);
  1284. }
  1285.  
  1286. for ($i = 0; $i < $za->numFiles; $i++) {
  1287. @File::putString($za->getNameIndex($i), $za->getFromIndex($i));
  1288. }
  1289.  
  1290. $za->close();
  1291.  
  1292. unlink(APPPATH . 'temp/import.zip');
  1293.  
  1294.  
  1295. echo '<p>Done.</p>';
  1296.  
  1297. $this->template('Import files');
  1298. }
  1299.  
  1300.  
  1301. /**
  1302.   * Allows files to be downloaded from the temporary directory
  1303.   *
  1304.   * Only certain files from the temp directory can be downloaded.
  1305.   * Each 'source' has a regex which is used to restrict downloads to only that particular type of file
  1306.   *
  1307.   * @param string $source The source of the file, one of 'export-database', 'export-files', 'module-builder'
  1308.   * @param string $tempfile File name in the temporary directory
  1309.   * @param string $orig Alternate name to use when providing file to browser
  1310.   **/
  1311. public function gettempfile($source, $tempfile, $orig = null)
  1312. {
  1313. AdminAuth::checkLogin();
  1314.  
  1315. $validation_regexes = [
  1316. 'export-database' => '/^export-[0-9]+-(?:sql_[-0-9]+_.+\.zip|.+\.sql)$/',
  1317. 'export-files' => '/^files_[-0-9]+_.+\.zip$/',
  1318. 'module-builder' => '/^mt_[0-9]+\.tar\.bz2$/',
  1319. ];
  1320.  
  1321. if (!isset($validation_regexes[$source])) {
  1322. throw new InvalidArgumentException('Invalid source specified');
  1323. }
  1324. if (!preg_match($validation_regexes[$source], $tempfile)) {
  1325. throw new InvalidArgumentException('Invalid tempfile specified');
  1326. }
  1327.  
  1328. $disk_filename = APPPATH . 'temp/' . $tempfile;
  1329. if (! file_exists($disk_filename)) {
  1330. throw new Kohana_404_Exception($tempfile);
  1331. }
  1332.  
  1333. // If no original name, use the disk filename
  1334. if (! $orig) {
  1335. $orig = $tempfile;
  1336. }
  1337. $orig = addslashes($orig);
  1338.  
  1339. // Determine mimetype
  1340. $parts = explode('.', $orig);
  1341. $ext = array_pop($parts);
  1342. $mimetypes = array(
  1343. 'txt' => 'text/plain',
  1344. 'sql' => 'text/plain',
  1345. 'zip' => 'application/zip',
  1346. 'bz2' => 'application/bzip2',
  1347. 'gz' => 'application/gzip',
  1348. );
  1349. if (! $mime = $mimetypes[$ext]) {
  1350. $mime = 'application/octet-stream';
  1351. }
  1352.  
  1353. // Make sure there's no buffering, or large files will exhaust PHP's memory allocation
  1354. while (count(ob_list_handlers()) > 0) {
  1355. }
  1356.  
  1357.  
  1358. header ("Content-type: {$mime}");
  1359. header ('Content-length: ' . filesize($disk_filename));
  1360. header ("Content-disposition: attachment; filename=\"{$orig}\"");
  1361. readfile($disk_filename);
  1362. }
  1363.  
  1364.  
  1365. /**
  1366.   * UI to export all files
  1367.   **/
  1368. public function exportFiles()
  1369. {
  1370. $files = File::glob('*');
  1371.  
  1372. echo "<p>Found " . count($files) . " files.\n";
  1373.  
  1374. echo '<p>NOTE: Exports of many files may take a long time and/or fail.</p>';
  1375.  
  1376. echo '<form action="dbtools/exportFilesAction" method="post">';
  1377. echo Csrf::token();
  1378. echo '<div class="action-bar"><button type="submit" class="button">Export files</button></div>';
  1379. echo '</form>';
  1380.  
  1381. $this->template('Export files');
  1382. }
  1383.  
  1384.  
  1385. /**
  1386.   * Action to export all files
  1387.   **/
  1388. public function exportFilesAction()
  1389. {
  1390. Csrf::checkOrDie();
  1391.  
  1392. $files = File::glob('*');
  1393.  
  1394. echo "<p>Found " . count($files) . " files.\n";
  1395.  
  1396. // Prep archive
  1397. $arch = new Archive('zip');
  1398. foreach ($files as $filename) {
  1399. $temp = File::createLocalCopy($filename);
  1400. if (!$temp) continue;
  1401. $arch->add($temp, $filename);
  1402. $temp_names[] = $temp;
  1403. }
  1404.  
  1405. // Build file name
  1406. $name = Kohana::config('sprout.site_title');
  1407. $name = str_replace(' ', '_', strtolower($name));
  1408. $name = preg_replace('/[^a-z_]/', '', $name);
  1409. $name = 'files_' . date('Y-m-d') . '_' . $name . '.zip';
  1410.  
  1411. // Save archive
  1412. echo "<p>Saving archive.\n";
  1413. $arch->save(APPPATH . 'temp/' . $name);
  1414.  
  1415. // Nuke temps
  1416. foreach ($temp_names as $temp) {
  1417. File::cleanupLocalCopy($temp);
  1418. }
  1419.  
  1420. // Offer download link
  1421. echo "<div class=\"action-bar\"><a href=\"SITE/dbtools/gettempfile/export-files/{$name}\" class=\"button icon-after icon-save\">Download: {$name}</a></div>";
  1422.  
  1423. $this->template('Export files');
  1424. }
  1425.  
  1426.  
  1427. /**
  1428.   * Edit the $_SESSION
  1429.   **/
  1430. public function sessionEditor()
  1431. {
  1432. Session::instance();
  1433.  
  1434. echo '<style>';
  1435. echo 'div.val { margin: 0 20px 15px; padding: 5px; clear: right; background: rgba(0,0,0,0.1); border-radius: 3px; }';
  1436. echo 'div.val div.val:last-child { margin-bottom: 6px; }';
  1437. echo 'div.val p { margin: 0; padding: 0; cursor: default; line-height: 32px; }';
  1438. echo 'div.val h3 { margin: 0 0 5px 0; padding: 0; }';
  1439. echo 'div.val form { margin: 0; padding: 0; }';
  1440. echo 'div.val:hover { background: rgba(0,0,0,0.3); }';
  1441. echo 'div.val:hover p { color: #fff; }';
  1442. echo 'div.val:hover h3 { color: #fff; }';
  1443. echo 'div.val button.right { margin: 0; }';
  1444. echo '</style>';
  1445.  
  1446. echo '<h3>$_SESSION</h3>';
  1447. $this->sessionLoop($_SESSION, 0, []);
  1448.  
  1449. $this->template('Session editor');
  1450. }
  1451.  
  1452. /**
  1453.   * List items in an array, along with the tools buttons
  1454.   **/
  1455. private function sessionLoop(&$a, $depth, $keys)
  1456. {
  1457. if ($depth > 50) {
  1458. echo '<p>TOO DEEP!</p>';
  1459. return;
  1460. }
  1461.  
  1462. foreach ($a as $key => $val) {
  1463. $this_keys = $keys;
  1464. $this_keys[] = $key;
  1465.  
  1466. // Can't delete protected/private members
  1467. $can_delete = true;
  1468. foreach ($this_keys as $k) {
  1469. if (strpos($k, '->') !== false) {
  1470. $can_delete = false;
  1471. break;
  1472. };
  1473. }
  1474.  
  1475. echo '<div class="val -clearfix">';
  1476. if ($can_delete) {
  1477. echo '<form action="SITE/dbtools/sessionEditorAction" method="post">';
  1478. foreach ($this_keys as $k) {
  1479. echo '<input type="hidden" name="key[]" value="' . Enc::url($k) . '">';
  1480. }
  1481. echo '<button type="submit" value="delete" name="do" class="button right button-orange button-small icon-after icon-close">Delete</button>';
  1482. echo '</form>';
  1483. }
  1484.  
  1485. if (is_array($val)) {
  1486. echo '<h3>' . Enc::html($key) . '</h3>';
  1487. if (empty($val)) {
  1488. echo '<div class="val"><p><i>(empty array)</i></p></div>';
  1489. } else {
  1490. $this->sessionLoop($val, $depth + 1, $this_keys);
  1491. }
  1492.  
  1493. } else if (is_object($val)) {
  1494. echo '<h3>' . Enc::html($key) . ' <i>' . get_class($val);
  1495. $parent = $val;
  1496. while ($parent = get_parent_class($parent)) {
  1497. echo " ex $parent";
  1498. }
  1499. $implements = class_implements($val);
  1500. $i = 0;
  1501. foreach ($implements as $interface) {
  1502. if (++$i == 1) {
  1503. echo ' impl ';
  1504. } else {
  1505. echo ', ';
  1506. }
  1507. echo $interface;
  1508. }
  1509. echo '</i></h3>';
  1510. $obj_pub_members = array_keys(get_object_vars($val));
  1511. $obj_all_members = (array) $val;
  1512.  
  1513. $members = array();
  1514. foreach ($obj_all_members as $mem_key => $mem_val) {
  1515. // N.B. This doesn't work (it returns an array):
  1516. // $new_key = preg_replace('/[^\0]*\0+/', '', $mem_key);
  1517. // Undocumented PHP behaviour; assume \0 is interpreted
  1518. // in $pattern as it is in $replacement
  1519. $null_pos = strrpos($mem_key, "\0");
  1520. if ($null_pos !== false) {
  1521. $new_key = substr($mem_key, $null_pos + 1);
  1522. } else {
  1523. $new_key = $mem_key;
  1524. }
  1525. $new_key = str_replace('*', '', $new_key);
  1526.  
  1527. if (in_array($new_key, $obj_pub_members)) {
  1528. $members["->{$new_key}"] = $mem_val;
  1529. continue;
  1530. }
  1531.  
  1532. $getter = false;
  1533. $getters = array('get_' . $new_key, 'get' . $new_key);
  1534. foreach ($getters as $fn) {
  1535. if (method_exists($val, $fn)) {
  1536. $getter = $fn;
  1537. break;
  1538. }
  1539. }
  1540. if (!$getter) continue;
  1541.  
  1542. $members["->{$getter}()"] = $mem_val;
  1543. }
  1544. $this->sessionLoop($members, $depth + 1, $this_keys);
  1545.  
  1546. } else if (gettype($val) == 'object') {
  1547. // e.g. __PHP_Incomplete_Class objects
  1548. echo '<p>' . $key . ' = <i>OBJECT OF UNKNOWN CLASS</i></p>';
  1549. } else {
  1550. echo '<p>' . $key . ' = ' . $val . '</p>';
  1551. }
  1552.  
  1553. echo '</div>';
  1554.  
  1555. unset ($this_keys, $key, $val);
  1556. }
  1557. }
  1558.  
  1559. /**
  1560.   * Session Editor Action
  1561.   **/
  1562. public function sessionEditorAction()
  1563. {
  1564. Session::instance();
  1565.  
  1566. $_POST['do'] = preg_replace('/[^a-z]/', '', strtolower($_POST['do']));
  1567.  
  1568. // Find the array & key which will do the action
  1569. $final_key = array_pop($_POST['key']);
  1570. $final_array = &$_SESSION;
  1571. foreach ($_POST['key'] as $key) {
  1572. $final_array = &$final_array[$key];
  1573. }
  1574.  
  1575. // Do the action
  1576. switch ($_POST['do']) {
  1577. case 'delete':
  1578. unset ($final_array[$final_key]);
  1579. break;
  1580. }
  1581.  
  1582. Url::redirect('dbtools/sessionEditor');
  1583. }
  1584.  
  1585.  
  1586. /**
  1587.   *
  1588.   * @return void
  1589.   */
  1590. public function listRoutes()
  1591. {
  1592. $routes = Router::getRoutes();
  1593.  
  1594. echo "<table>";
  1595. echo "<thead><tr>";
  1596. echo "<th>Rule</th>";
  1597. echo "<th>Target</th>";
  1598. echo "</thead></tr>\n";
  1599. echo "<tbody>";
  1600.  
  1601. foreach ($routes as $rule => $target) {
  1602. $rule = Enc::html($rule);
  1603. $target = Enc::html(json_encode($target, JSON_UNESCAPED_SLASHES));
  1604.  
  1605. echo "<tr>";
  1606. echo "<td>{$rule}</td>";
  1607. echo "<td>{$target}</td>";
  1608. echo "</tr>\n";
  1609. }
  1610.  
  1611. echo "</tbody>";
  1612. echo "</table>";
  1613.  
  1614. $this->template('Route Inspector');
  1615. }
  1616.  
  1617.  
  1618. /**
  1619.   * For a given field name, a type to use which is better than VARCHAR(200)
  1620.   **/
  1621. private static $module_builder_type_guess = array(
  1622. 'active' => 'TINYINT UNSIGNED',
  1623. 'amount' => 'DECIMAL(6,2)',
  1624. 'data' => 'BLOB',
  1625. 'description' => 'TEXT',
  1626. 'email' => 'VARCHAR(150)',
  1627. 'fax' => 'VARCHAR(20)',
  1628. 'filename' => 'VARCHAR(255)',
  1629. 'first_name' => 'VARCHAR(100)',
  1630. 'image' => 'VARCHAR(255)',
  1631. 'last_name' => 'VARCHAR(100)',
  1632. 'mobile' => 'VARCHAR(20)',
  1633. 'notes' => 'TEXT',
  1634. 'phone' => 'VARCHAR(20)',
  1635. 'photo' => 'VARCHAR(255)',
  1636. 'postcode' => 'VARCHAR(10)',
  1637. 'price' => 'DECIMAL(6,2)',
  1638. 'state' => 'VARCHAR(50)',
  1639. 'suburb' => 'VARCHAR(50)',
  1640. 'text' => 'TEXT',
  1641. 'visible' => 'TINYINT UNSIGNED',
  1642. );
  1643.  
  1644.  
  1645. /**
  1646.   * Generates blank modules
  1647.   **/
  1648. public function moduleBuilder()
  1649. {
  1650. $temp = APPPATH . 'temp';
  1651.  
  1652. // Generate list of modules
  1653. $mod_dir = DOCROOT . '/modules/';
  1654. $modules = scandir($mod_dir);
  1655. foreach ($modules as $key => $mod) {
  1656. if ($mod[0] == '.' or !is_dir($mod_dir . $mod)) unset($modules[$key]);
  1657. }
  1658.  
  1659. // Prep array for form data
  1660. $modules_list = [];
  1661. foreach($modules as $mod) {
  1662. $modules_list[$mod] = $mod;
  1663. }
  1664.  
  1665. $view = new View('sprout/dbtools/module_builder');
  1666. $view->temp_writeable = (is_dir($temp) and is_writeable($temp));
  1667. $view->bad_fields = array('id', 'name', 'active', 'date_added', 'date_modified', 'record_order');
  1668. $view->modules = $modules_list;
  1669. echo $view->render();
  1670.  
  1671. $this->template('Module builder');
  1672. }
  1673.  
  1674.  
  1675. public function moduleBuilderAction()
  1676. {
  1677. if (empty($_POST['module_author'])) {
  1678. throw new InvalidArgumentException('Module author not specified');
  1679. }
  1680. if (empty($_POST['module_name'])) {
  1681. throw new InvalidArgumentException('Module name not specified');
  1682. } else {
  1683. $module_name = trim($_POST['module_name']);
  1684. if (!preg_match('/^([A-Z][a-z0-9]+)+$/', $module_name)) {
  1685. throw new InvalidArgumentException('Invalid module name');
  1686. }
  1687. }
  1688.  
  1689. // Name => type
  1690. $inbuilt_fields = [
  1691. 'id' => 'INT UNSIGNED',
  1692. 'name' => 'VARCHAR(60)',
  1693. 'active' => 'TINYINT UNSIGNED',
  1694. 'date_added' => 'DATETIME',
  1695. 'date_modified' => 'DATETIME',
  1696. ];
  1697. if (in_array($_POST['module_type'], ['list', 'tree'])) {
  1698. $inbuilt_fields['record_order'] = 'INT UNSIGNED';
  1699. }
  1700.  
  1701. $fields = array_keys($inbuilt_fields);
  1702. if ($_POST['module_type'] == 'tree') $fields[] = 'parent_id';
  1703. foreach (explode("\n", $_POST['fields']) as $field) {
  1704. $field = trim($field);
  1705. if ($field == '') continue;
  1706. if (in_array($field, $fields)) continue;
  1707. $fields[] = $field;
  1708. }
  1709.  
  1710. $fields_xml = array();
  1711. $fields_manual = array();
  1712.  
  1713. $t = " ";
  1714. foreach ($fields as $f) {
  1715. if (!in_array($f, ['name']) and isset($inbuilt_fields[$f])) continue;
  1716.  
  1717. $l = ucfirst(str_replace('_', ' ', $f));
  1718.  
  1719. // Try to guess a name using a basic algorithm
  1720. if (isset($inbuilt_fields[$f])) {
  1721. $type = $inbuilt_fields[$f];
  1722. } else if (preg_match('!_id$!', $f)) {
  1723. $type = 'INT UNSIGNED';
  1724. $l = substr($l, 0, -3);
  1725. } else {
  1726. $type = @self::$module_builder_type_guess[$f];
  1727. }
  1728.  
  1729. // If it all fails, fall back to something generic
  1730. if (! $type) {
  1731. $type = 'VARCHAR(100)';
  1732. }
  1733.  
  1734. $attrs = 'allownull="0" ';
  1735.  
  1736. if ($f == 'parent_id') {
  1737. $input_method = 'Fb::dropdown';
  1738. $items = "{\"query\": \"SELECT id, name FROM ~{$_POST['pname']} WHERE parent_id = 0 ORDER BY record_order\"}";
  1739. } else {
  1740. $input_method = 'Fb::text';
  1741. $items = "{}";
  1742. }
  1743.  
  1744. $json = "{$t}{$t}{\n" .
  1745. "{$t}{$t}{$t}\"field\": {\n" .
  1746. "{$t}{$t}{$t}{$t}\"name\": \"{$f}\",\n" .
  1747. "{$t}{$t}{$t}{$t}\"label\": \"{$l}\",\n" .
  1748. "{$t}{$t}{$t}{$t}\"display\": \"{$input_method}\",\n" .
  1749. "{$t}{$t}{$t}{$t}\"items\": {$items},\n" .
  1750. "{$t}{$t}{$t}{$t}\"required\": false,\n" .
  1751. "{$t}{$t}{$t}{$t}\"validate\": [\n";
  1752.  
  1753. // Use length as basic validation where possible, allowing an extra char for a decimal point if relevant
  1754. $matches = [];
  1755. if (preg_match('/\([0-9]+(\s*,)?/', $type, $matches)) {
  1756. $field_len = (int) substr($matches[0], 1);
  1757. if (!empty($matches[1])) ++$field_len;
  1758. $json .= "{$t}{$t}{$t}{$t}{$t}{\"func\": \"Validity::length\", \"args\": [0, {$field_len}]}\n";
  1759. }
  1760. $json .= "{$t}{$t}{$t}{$t}]\n" .
  1761. "{$t}{$t}{$t}}\n" .
  1762. "{$t}{$t}}";
  1763. $fields_json[] = $json;
  1764.  
  1765. if (isset($inbuilt_fields[$f])) continue;
  1766.  
  1767. $fields_xml[] = "{$t}{$t}<column name=\"{$f}\" type=\"{$type}\" {$attrs}/>";
  1768. $fields_manual[] = "<p><b>{$l}</b>\n<br><!-- description goes here --></p>\n";
  1769. }
  1770.  
  1771. $possible_main_fields = [
  1772. 'name',
  1773. 'email',
  1774. 'first_name',
  1775. 'last_name',
  1776. 'price',
  1777. 'suburb',
  1778. 'state',
  1779. 'visible',
  1780. ];
  1781.  
  1782. $fields_main = [];
  1783. foreach ($possible_main_fields as $ind) {
  1784. if (in_array($ind, $fields)) {
  1785. $label = ucfirst(str_replace('_', ' ', $ind));
  1786. if (in_array($ind, ['visible'])) {
  1787. $field = "[new ColModifierBinary(), '{$ind}']";
  1788. } else {
  1789. $field = "'{$ind}'";
  1790. }
  1791. $fields_main[] = "'{$label}' => {$field},";
  1792. }
  1793. }
  1794.  
  1795. $_POST['_fields_xml'] = rtrim(implode("\n", $fields_xml));
  1796. $_POST['_fields_json'] = implode(",\n", $fields_json);
  1797. $_POST['_fields_manual'] = rtrim(implode("\n", $fields_manual));
  1798. $_POST['_fields_main'] = implode("\n{$t}{$t}{$t}", $fields_main);
  1799.  
  1800.  
  1801. $temp = APPPATH . 'temp';
  1802. $template_dir = APPPATH . 'module_template/' . $_POST['module_type'];
  1803.  
  1804. shell_exec("rm -f {$temp}/mt_*.tar.bz2");
  1805. if (!file_exists("{$temp}/{$module_name}") and !@mkdir("{$temp}/{$module_name}")) {
  1806. echo "<ul class=\"messages\"><li class=\"error\">Failed to create temp directory {$module_name}</li></ul>";
  1807. $this->template('Module builder');
  1808. return;
  1809. }
  1810.  
  1811. $dir_iterator = new RecursiveDirectoryIterator($template_dir);
  1812. $iterator = new RecursiveIteratorIterator($dir_iterator, RecursiveIteratorIterator::SELF_FIRST);
  1813.  
  1814. echo '<pre>';
  1815. echo "Ready.\n";
  1816.  
  1817. foreach ($iterator as $file) {
  1818. $basename = basename($file);
  1819. if ($basename[0] == '.') continue;
  1820. if (strpos($file, '.svn') !== false) continue;
  1821. if (strpos($file, '~') !== false) continue;
  1822.  
  1823. $relative_name = substr($file, strlen($template_dir));
  1824.  
  1825. echo "Processing: '/{$_POST['module_type']}{$relative_name}'";
  1826.  
  1827. if ($file->isDir()) {
  1828. // directories
  1829. $new_name = self::mtTransform($relative_name);
  1830. @mkdir ("{$temp}/{$module_name}" . $new_name);
  1831.  
  1832. } else if ($file->isFile()) {
  1833. // files
  1834. $text = file_get_contents($template_dir . $relative_name);
  1835. $text = self::mtTransform($text);
  1836.  
  1837. $new_name = "{$temp}/{$module_name}" . self::mtTransform($relative_name);
  1838. file_put_contents($new_name, $text);
  1839. }
  1840.  
  1841. echo " => '{$new_name}'.\n";
  1842. }
  1843.  
  1844. echo "Done building, now compressing.\n";
  1845.  
  1846. $rand = time();
  1847. echo shell_exec("cd {$temp}; tar -cjvf mt_{$rand}.tar.bz2 {$module_name}");
  1848. shell_exec("cd {$temp}; rm -rf {$module_name}");
  1849.  
  1850. echo "Done.\n";
  1851. echo '</pre>';
  1852.  
  1853. echo "<p><a href=\"SITE/dbtools/gettempfile/module-builder/mt_{$rand}.tar.bz2/sprout_module_{$_POST['sname']}_{$_POST['module_type']}.tar.bz2\">Download module</a></p>";
  1854.  
  1855. $this->template('Module builder');
  1856. }
  1857.  
  1858.  
  1859. /**
  1860.   * Generates db_struct.xml content for a module
  1861.   */
  1862. public function moduleBuilderDb()
  1863. {
  1864. if (!empty($_GET['table']) and in_array(@$_GET['type'], ['has_categories', 'list', 'tree'])) {
  1865. $template_path = APPPATH . 'module_template/' . $_GET['type'] . '/db_struct.xml';
  1866. $content = file_get_contents($template_path);
  1867. $content = str_replace('PNAME', $_GET['table'], $content);
  1868. $content = str_replace('SNAME', Inflector::singular($_GET['table']), $content);
  1869. $content = str_replace('FIELDS_XML', '', $content);
  1870. $content = preg_replace('/^[\t ]+$/m', '', $content);
  1871. $_GET['xml'] = $content;
  1872. }
  1873.  
  1874. $view = new View('sprout/dbtools/module_builder_db');
  1875. $view->data = $_GET;
  1876. echo $view->render();
  1877.  
  1878. $this->template('Module builder - DB');
  1879. }
  1880.  
  1881.  
  1882. /**
  1883.   * Generates modules from an existing db_struct.xml file
  1884.   **/
  1885. public function moduleBuilderExisting()
  1886. {
  1887. $temp = APPPATH . 'temp';
  1888. $temp_writeable = (is_dir($temp) and is_writeable($temp));
  1889.  
  1890. $existing_files = [];
  1891. $modules = glob(DOCROOT . 'modules/*');
  1892. foreach ($modules as $mod) {
  1893. if (is_dir($mod) and file_exists($mod . '/db_struct.xml')) {
  1894. $mod = basename($mod);
  1895. $existing_files[$mod] = $mod;
  1896. }
  1897. }
  1898.  
  1899. $view = new View('sprout/dbtools/module_builder_existing_upload');
  1900. $view->temp_writeable = $temp_writeable;
  1901. $view->existing_files = $existing_files;
  1902. echo $view->render();
  1903.  
  1904. $this->template('Module builder existing');
  1905. }
  1906.  
  1907.  
  1908. /**
  1909.   * Generates modules from an existing db_struct.xml file
  1910.   **/
  1911. public function moduleBuilderExistingUploadAction()
  1912. {
  1913. $filename = 'mbe' . time() . '.xml';
  1914.  
  1915. if (isset($_FILES['file']['tmp_name'])) {
  1916. // Upload a new file
  1917. copy($_FILES['file']['tmp_name'], APPPATH . 'temp/' . $filename);
  1918.  
  1919. } else if (isset($_POST['existing'])) {
  1920. // Process an existing file
  1921. if (!preg_match('!^[a-zA-Z0-9]+$!', $_POST['existing'])) {
  1922. die('Invalid module');
  1923. }
  1924. copy(DOCROOT . 'modules/' . $_POST['existing'] . '/db_struct.xml', APPPATH . 'temp/' . $filename);
  1925. $_SESSION['module_builder_existing']['field_values']['module_name'] = $_POST['existing'];
  1926. $_SESSION['module_builder_existing']['field_values']['module_author'] = 'Karmabunny';
  1927.  
  1928. } else if (isset($_POST['content'])) {
  1929. // Copy and paste XML content
  1930. if (strpos($_POST['content'], '<database>') === false) {
  1931. $_POST['content'] = '<database>' . $_POST['content'] . '</database>';
  1932. }
  1933. file_put_contents(APPPATH . 'temp/' . $filename, $_POST['content']);
  1934.  
  1935. } else {
  1936. die('No file');
  1937. }
  1938.  
  1939. Url::redirect('dbtools/moduleBuilderExistingForm/' . $filename);
  1940. }
  1941.  
  1942. /**
  1943.   * Generates modules from an existing db_struct.xml file
  1944.   **/
  1945. public function moduleBuilderExistingForm($input_xml)
  1946. {
  1947. if (!preg_match('/^mbe[0-9]+\.xml$/', $input_xml)) die('Invalid filename');
  1948.  
  1949. $sync = new DatabaseSync(false);
  1950. $sync->loadXml(APPPATH . 'temp/' . $input_xml);
  1951.  
  1952. $tables = $sync->tables;
  1953. ksort($tables);
  1954.  
  1955. if (!empty($_SESSION['module_builder_existing']['field_values'])) {
  1956. $data = $_SESSION['module_builder_existing']['field_values'];
  1957. } else {
  1958. $data = [];
  1959. }
  1960.  
  1961. if (!isset($data['tables_cname'])) {
  1962. $data = ['tables_cname' => [], 'tables_sname' => [], 'tables_snice' => [], 'tables_pnice' => []];
  1963. foreach ($tables as $name => $defn) {
  1964. $data['tables_cname'][$name] = Text::lc2camelCaps(Inflector::singular($name));
  1965. $data['tables_sname'][$name] = Inflector::singular($name);
  1966. $data['tables_snice'][$name] = ucfirst(str_replace('_', ' ', Inflector::singular($name)));
  1967. $data['tables_pnice'][$name] = ucfirst(str_replace('_', ' ', $name));
  1968. }
  1969. if (empty($data['module_author'])) $data['module_author'] = 'Karmabunny';
  1970. }
  1971.  
  1972. $view = new View('sprout/dbtools/module_builder_existing_form');
  1973. $view->tables = $tables;
  1974. $view->templates = [
  1975. 'has_categories' => 'Categories',
  1976. 'tree' => 'Tree',
  1977. 'list' => 'List',
  1978. ];
  1979. $view->data = $data;
  1980. $view->input_xml = $input_xml;
  1981. if (!empty($_SESSION['module_builder_existing']['field_errors'])) {
  1982. $view->errors = $_SESSION['module_builder_existing']['field_errors'];
  1983. } else {
  1984. $view->errors = [];
  1985. }
  1986. echo $view->render();
  1987.  
  1988. $this->template('Module builder existing');
  1989. }
  1990.  
  1991.  
  1992. /**
  1993.   * Generates modules from an existing db_struct.xml file
  1994.   **/
  1995. public function moduleBuilderExistingAction($input_xml)
  1996. {
  1997. static $tab = " ";
  1998.  
  1999. $errs = [];
  2000. if (!preg_match('/^mbe[0-9]+\.xml$/', $input_xml)) $errs[] = 'Invalid filename';
  2001.  
  2002. if (empty($_POST['module_author'])) {
  2003. $errs['module_author'] = 'Required';
  2004. }
  2005. if (empty($_POST['module_name'])) {
  2006. $errs['module_name'] = 'Required';
  2007. } else {
  2008. $module_name = trim($_POST['module_name']);
  2009. if (!preg_match('/^([A-Z][a-z0-9]+)+$/', $module_name)) {
  2010. $errs['module_name'] = 'Invalid value';
  2011. }
  2012. }
  2013. if ($errs) {
  2014. $_SESSION['module_builder_existing']['field_values'] = Validator::trim($_POST);
  2015. $_SESSION['module_builder_existing']['field_errors'] = $errs;
  2016. Url::redirect('/dbtools/moduleBuilderExistingForm/' . $input_xml);
  2017. }
  2018.  
  2019. $temp = APPPATH . 'temp';
  2020. if (!file_exists("{$temp}/{$module_name}") and !@mkdir("{$temp}/{$module_name}")) {
  2021. echo "<ul class=\"messages\"><li class=\"error\">Failed to create temp directory {$module_name}</li></ul>";
  2022. $this->template('Module builder');
  2023. return;
  2024. }
  2025.  
  2026. $sync = new DatabaseSync(false);
  2027. $sync->loadXml(APPPATH . 'temp/' . $input_xml);
  2028. $tables = $sync->tables;
  2029.  
  2030. foreach ($tables as $t => $defn) {
  2031. if (empty($_POST['tables'][$t])) continue;
  2032.  
  2033. $template_dir = APPPATH . 'module_template/' . $_POST['tables'][$t];
  2034. $dir_iterator = new \RecursiveDirectoryIterator($template_dir);
  2035. $iterator = new \RecursiveIteratorIterator($dir_iterator, \RecursiveIteratorIterator::SELF_FIRST);
  2036.  
  2037. $fields_json = array();
  2038. $fields_manual = array();
  2039.  
  2040. echo '<h3>', Enc::html($t), '</h3>';
  2041. echo "<pre>\n";
  2042. foreach ($defn['columns'] as $f => $col) {
  2043. if (in_array($f, ['id', 'date_added', 'date_modified'])) continue;
  2044.  
  2045. $items = "{}";
  2046.  
  2047. // Determine HTML input field based on field name and type
  2048. // Active field (every table should have one of these)
  2049. if ($f == 'active') {
  2050. $input_method = 'Fb::multiradio';
  2051. $items = "{\"0\": \"No\", \"1\": \"Yes\"}";
  2052.  
  2053. // Numeric fields
  2054. } else if (preg_match('/^(num|max|min)_/', $f)) {
  2055. $input_method = 'Fb::number';
  2056.  
  2057. // ENUM and SET fields
  2058. } else if (preg_match('/^ENUM\s*\(/', $col['type'])) {
  2059. $input_method = 'Fb::dropdown';
  2060. $items = '{"func": "Pdb::extractEnumArr", "args": ["' . Enc::js($t) . '", "' . Enc::js($f) . '"]}';
  2061. } else if (preg_match('/^SET\s*\(/', $col['type'])) {
  2062. $input_method = 'Fb::checkboxSet';
  2063. $items = '{"func": "Pdb::extractEnumArr", "args": ["' . Enc::js($t) . '", "' . Enc::js($f) . '"]}';
  2064.  
  2065. // Foreign keys (ending in _id)
  2066. } else if (preg_match('/_id$/', $f)) {
  2067. $target_table = Inflector::plural(substr($f, 0, -3));
  2068. $input_method = 'Fb::dropdown';
  2069. $items = '{"func": "Pdb::lookup", "args": ["' . Enc::js($target_table) . '"]}';
  2070.  
  2071. // Other columns: determine field type
  2072. } else {
  2073. $col_def_parts = preg_split('/\s+/', $col['type']);
  2074. $type = strtoupper(Sprout::iterableFirstValue($col_def_parts));
  2075.  
  2076. switch ($type) {
  2077. case 'DATETIME':
  2078. $input_method = 'Fb::datetimepicker';
  2079. break;
  2080.  
  2081. case 'DATE':
  2082. $input_method = 'Fb::datepicker';
  2083. break;
  2084.  
  2085. case 'TEXT':
  2086. $input_method = 'Fb::multiline';
  2087. break;
  2088.  
  2089. case 'TINYINT':
  2090. $input_method = 'Fb::multiradio';
  2091. $items = '{"0": "No", "1": "Yes"}';
  2092. break;
  2093.  
  2094. default:
  2095. $input_method = 'Fb::text';
  2096. }
  2097. }
  2098.  
  2099. $l = ucfirst(trim(str_replace('_', ' ', $f)));
  2100. $l = preg_replace('/\s+id$/', '', $l);
  2101.  
  2102. // Handle common acronyms
  2103. $l_parts = preg_split('/\s+/', $l);
  2104. foreach ($l_parts as &$l_part) {
  2105. if (in_array(strtolower($l_part), ['url', 'gst'])) {
  2106. $l_part = strtoupper($l_part);
  2107. }
  2108. }
  2109. $l = implode(' ', $l_parts);
  2110.  
  2111. $json = "{$tab}{$tab}{\n" .
  2112. "{$tab}{$tab}{$tab}\"field\": {\n" .
  2113. "{$tab}{$tab}{$tab}{$tab}\"name\": \"{$f}\",\n" .
  2114. "{$tab}{$tab}{$tab}{$tab}\"label\": \"{$l}\",\n" .
  2115. "{$tab}{$tab}{$tab}{$tab}\"display\": \"{$input_method}\",\n" .
  2116. "{$tab}{$tab}{$tab}{$tab}\"items\": {$items},\n" .
  2117. "{$tab}{$tab}{$tab}{$tab}\"required\": false,\n" .
  2118. "{$tab}{$tab}{$tab}{$tab}\"validate\": [\n";
  2119.  
  2120. // Use length as basic validation where possible, allowing an extra char for a decimal point if relevant
  2121. $matches = [];
  2122. if (preg_match('/\([0-9]+(\s*,)?/', $col['type'], $matches)) {
  2123. $field_len = (int) substr($matches[0], 1);
  2124. if (!empty($matches[1])) ++$field_len;
  2125. $json .= "{$tab}{$tab}{$tab}{$tab}{$tab}{\"func\": \"Validity::length\", \"args\": [0, {$field_len}]}\n";
  2126. }
  2127.  
  2128. $json .= "{$tab}{$tab}{$tab}{$tab}]\n" .
  2129. "{$tab}{$tab}{$tab}}\n" .
  2130. "{$tab}{$tab}}";
  2131. $fields_json[] = $json;
  2132.  
  2133. $fields_manual[] = "<p><b>{$l}</b>\n<br><!-- description goes here --></p>\n";
  2134. }
  2135.  
  2136. $possible_main_fields = [
  2137. 'name',
  2138. 'active',
  2139. 'email',
  2140. 'first_name',
  2141. 'last_name',
  2142. 'price',
  2143. 'suburb',
  2144. 'state',
  2145. 'visible',
  2146. ];
  2147.  
  2148. $fields_main = [];
  2149. foreach ($possible_main_fields as $ind) {
  2150. if (in_array($ind, array_keys($defn['columns']))) {
  2151. $label = ucfirst(str_replace('_', ' ', $ind));
  2152. if (in_array($ind, ['active', 'visible'])) {
  2153. $field = "[new ColModifierBinary(), '{$ind}']";
  2154. } else {
  2155. $field = "'{$ind}'";
  2156. }
  2157. $fields_main[] = "'{$label}' => {$field},";
  2158. }
  2159. }
  2160.  
  2161. $_POST['_fields_xml'] = '';
  2162. $_POST['_fields_json'] = implode(",\n", $fields_json);
  2163. $_POST['_fields_manual'] = rtrim(implode("\n", $fields_manual));
  2164. $_POST['_fields_main'] = implode("\n{$tab}{$tab}{$tab}", $fields_main);
  2165.  
  2166. $_POST['cname'] = $_POST['tables_cname'][$t];
  2167. $_POST['sname'] = $_POST['tables_sname'][$t];
  2168. $_POST['pname'] = $t;
  2169. $_POST['snice'] = $_POST['tables_snice'][$t];
  2170. $_POST['pnice'] = $_POST['tables_pnice'][$t];
  2171.  
  2172. foreach ($iterator as $file) {
  2173. $basename = basename($file);
  2174. if ($basename[0] == '.') continue;
  2175. if (strpos($file, '.svn') !== false) continue;
  2176. if (strpos($file, '~') !== false) continue;
  2177.  
  2178. $relative_name = substr($file, strlen($template_dir));
  2179. if ($relative_name == '/db_struct.xml') continue;
  2180. if ($relative_name == '/admin_load.php') continue;
  2181. if ($relative_name == '/sprout_load.php') continue;
  2182.  
  2183. echo "Processing: '{$relative_name}'";
  2184.  
  2185. if ($file->isDir()) {
  2186. $new_name = self::mtTransform($relative_name);
  2187. @mkdir ("{$temp}/{$module_name}" . $new_name);
  2188.  
  2189. } else if ($file->isFile()) {
  2190. $text = file_get_contents($template_dir . $relative_name);
  2191. $text = self::mtTransform($text);
  2192.  
  2193. $new_name = "{$temp}/{$module_name}" . self::mtTransform($relative_name);
  2194. file_put_contents($new_name, $text);
  2195. }
  2196.  
  2197. echo " => '{$new_name}'.\n";
  2198. }
  2199.  
  2200. echo '</pre>';
  2201. }
  2202.  
  2203. copy(APPPATH . 'temp/' . $input_xml, "{$temp}/{$module_name}/db_struct.xml");
  2204.  
  2205. echo "<p>Done building, now compressing...\n";
  2206.  
  2207. echo '<pre>';
  2208. $rand = time();
  2209. echo shell_exec("cd {$temp}; tar -cjvf mt_{$rand}.tar.bz2 {$module_name}");
  2210. shell_exec("cd {$temp}; rm -rf {$module_name}");
  2211. echo '</pre>';
  2212.  
  2213. echo "<p>Done.\n";
  2214. echo "<div class=\"action-bar\"><a href=\"SITE/dbtools/gettempfile/module-builder/mt_{$rand}.tar.bz2/sprout_module.tar.bz2\" class=\"button icon-after icon-save\">Download module</a></div>";
  2215.  
  2216. $this->template('Module builder');
  2217. }
  2218.  
  2219.  
  2220.  
  2221. /**
  2222.   * Used by the module_template_action method
  2223.   **/
  2224. public static function mtTransform($text)
  2225. {
  2226. $text = str_replace('AUTHOR', $_POST['module_author'], $text);
  2227. $text = str_replace('MODULE', $_POST['module_name'], $text);
  2228. $text = str_replace('CNAME', $_POST['cname'], $text);
  2229. $text = str_replace('SNAME', $_POST['sname'], $text);
  2230. $text = str_replace('PNAME', $_POST['pname'], $text);
  2231. $text = str_replace('SNICE', $_POST['snice'], $text);
  2232. $text = str_replace('PNICE', $_POST['pnice'], $text);
  2233. $text = str_replace('SLWR', strtolower($_POST['snice']), $text);
  2234. $text = str_replace('PLWR', strtolower($_POST['pnice']), $text);
  2235.  
  2236. $text = str_replace('FIELDS_XML', $_POST['_fields_xml'], $text);
  2237. $text = str_replace('FIELDS_JSON', $_POST['_fields_json'], $text);
  2238. $text = str_replace('FIELDS_MANUAL', $_POST['_fields_manual'], $text);
  2239. $text = str_replace('FIELDS_MAIN', $_POST['_fields_main'], $text);
  2240.  
  2241. return $text;
  2242. }
  2243.  
  2244.  
  2245. /**
  2246.   * Browse and search exceptions
  2247.   */
  2248. public function exceptionLog()
  2249. {
  2250. $conditions = array();
  2251. if (!empty($_GET['class'])) {
  2252. $conditions[] = ['class_name', '=', $_GET['class']];
  2253. }
  2254. if (!empty($_GET['message'])) {
  2255. $conditions[] = ['message', 'CONTAINS', $_GET['message']];
  2256. }
  2257. if (empty($_GET['show_row_missing'])) {
  2258. $conditions[] = ['class_name', '!=', 'karmabunny\pdb\Exceptions\RowMissingException'];
  2259. }
  2260. if (empty($_GET['show_404'])) {
  2261. $conditions[] = ['class_name', '!=', 'Kohana_404_Exception'];
  2262. }
  2263. if (count($conditions) == 0) $conditions[] = '1';
  2264.  
  2265. $page_size = 100;
  2266. $page = max((int)@$_GET['page'], 1);
  2267. $offset = ($page - 1) * $page_size;
  2268.  
  2269. $binds = array();
  2270. $where = Pdb::buildClause($conditions, $binds);
  2271. $q = "SELECT id, date_generated, class_name, message
  2272. FROM ~exception_log
  2273. WHERE {$where}
  2274. ORDER BY id DESC
  2275. LIMIT {$offset}, {$page_size}";
  2276. $res = Pdb::query($q, $binds, 'pdo');
  2277.  
  2278. $row_count = $res->rowCount();
  2279. if ($row_count == 0) {
  2280. $itemlist = '<p><em>No items found</em></p>';
  2281. } else {
  2282. $itemlist = new Itemlist();
  2283. $itemlist->items = $res;
  2284. $itemlist->addAction('edit', 'dbtools/exceptionDetail?id=%%');
  2285. $itemlist->main_columns = array(
  2286. 'Date' => 'date_generated',
  2287. 'Class' => 'class_name',
  2288. 'Message' => 'message',
  2289. );
  2290. }
  2291.  
  2292. // View
  2293. $view = new View('sprout/dbtools/exception_log');
  2294. $view->itemlist = $itemlist;
  2295. $view->page = $page;
  2296. $view->row_count = $row_count;
  2297. $view->page_size = $page_size;
  2298. echo $view->render();
  2299.  
  2300. $res->closeCursor();
  2301. $this->template('Exception log');
  2302. }
  2303.  
  2304.  
  2305. /**
  2306.   * Browse recent exceptions - details
  2307.   */
  2308. public function exceptionDetail()
  2309. {
  2310. $_GET['id'] = preg_replace('/^SE/i', '', trim($_GET['id']));
  2311.  
  2312. try {
  2313. $log = Pdb::get('exception_log', $_GET['id']);
  2314. $title = $log['id'];
  2315. } catch (RowMissingException $ex) {
  2316. $log = [];
  2317. $title = 'Not found';
  2318. }
  2319.  
  2320. // View
  2321. $view = new View('sprout/dbtools/exception_details');
  2322. $view->log = $log;
  2323.  
  2324. echo $view->render();
  2325. $this->template('Exception #' . $title);
  2326. }
  2327.  
  2328.  
  2329. /**
  2330.   * Generate a password hash to store in a config file
  2331.   * The username and hash are used by {@see AdminAuth::processLocal}
  2332.   */
  2333. public function generatePasswordHash()
  2334. {
  2335. $username = trim(@$_POST['username']);
  2336.  
  2337. echo '<form method="post">';
  2338. echo '<div class="field-group-wrap -clearfix">';
  2339. echo '<div class="field-group-item col col--one-half">';
  2340. Form::nextFieldDetails('Username', true, 'Letters and numbers only');
  2341. echo Form::text('username', ['-wrapper-class' => 'white']);
  2342. echo '</div><!-- .col.col--one-half -->';
  2343.  
  2344. echo '<div class="field-group-item col col--one-half">';
  2345. Form::nextFieldDetails('Password', true, 'Will be displayed on screen');
  2346. echo Form::text('pass', ['-wrapper-class' => 'white']);
  2347. echo '</div><!-- .col.col--one-half -->';
  2348. echo '</div><!-- .field-group-wrap -->';
  2349.  
  2350. echo '<div class="action-bar"><button type="submit" class="button icon-after icon-keyboard_arrow_right">Generate hash</button></div>';
  2351. echo '</form>';
  2352.  
  2353. if (!empty($username) and !empty($_POST['pass'])) {
  2354. $data = Auth::hashPassword($_POST['pass'], Constants::PASSWORD_BCRYPT12);
  2355.  
  2356. $users = AdminAuth::injectLocalSuperConf($username, $data[0], $data[2]);
  2357.  
  2358. echo "<h4>Paste this into a config/super_ops.php file</h4>\n";
  2359.  
  2360. echo "<pre>&lt;?php\n\$config['operators'] = [\n";
  2361. foreach ($users as $username => $user) {
  2362. echo " '", Enc::html(Enc::js($username));
  2363. echo "' =&gt; ['uid' => {$user['uid']}, 'hash' =&gt; '", Enc::html(Enc::js($user['hash']));
  2364. echo "', 'salt' =&gt; '", Enc::html(Enc::js($user['salt'])), "'],\n";
  2365. }
  2366. echo "];</pre>";
  2367. }
  2368.  
  2369. $this->template('Generate password hash');
  2370. }
  2371.  
  2372.  
  2373. /**
  2374.   * Render view to see session and cookie data
  2375.   *
  2376.   * @return void Echos HTML
  2377.   */
  2378. public function varDump()
  2379. {
  2380. echo '<h2>$_SESSION</h2>';
  2381. echo sprintf('<pre>%s</pre>', print_r($_SESSION, true));
  2382. echo '<h2>$_COOKIE</h2>';
  2383. echo sprintf('<pre>%s</pre>', print_r($_COOKIE, true));
  2384. echo '<h2>$_SERVER</h2>';
  2385. echo sprintf('<pre>%s</pre>', print_r($_SERVER, true));
  2386.  
  2387. $this->template('Var dump');
  2388. }
  2389.  
  2390. /**
  2391.   * Simple tool for testing skin templates
  2392.   *
  2393.   * @return void Outputs HTML directly
  2394.   */
  2395. public function testSkinTemplates()
  2396. {
  2397. $skins = Subsites::getCodes();
  2398. $skins[] = 'unavailable';
  2399.  
  2400. echo '<div class="info highlight-warning">';
  2401. echo 'Note: Not all templates will work if they rely on variables being set which are not set by this tool.';
  2402. echo '</div>';
  2403.  
  2404. foreach ($skins as $s) {
  2405. echo '<h3>', Enc::html($s), '</h3>';
  2406.  
  2407. $templates = glob(DOCROOT . 'skin/' . $s . '/*.php');
  2408. foreach ($templates as $f) {
  2409. $f = basename($f, '.php');
  2410. if ($f[0] == '.' or $f[0] == '_') continue;
  2411. if ($f == 'exception' or $f == 'popup' or $f == 'google_analytics') continue;
  2412.  
  2413. $url = '/dbtools/testSkinTemplatesAction/' . Enc::url($s) . '/' . Enc::url($f);
  2414.  
  2415. echo '<p><a href="' . Enc::html($url) . '">' . Enc::html($f) . '</a></p>';
  2416. }
  2417. }
  2418.  
  2419. $this->template('Template test tool');
  2420. }
  2421.  
  2422.  
  2423. /**
  2424.   * Actual viewing UI for templates
  2425.   *
  2426.   * @param string $skin Skin name, e.g. 'default'
  2427.   * @param string $tmpl Template filename, e.g. 'inner'
  2428.   * @return void Outputs HTML directly
  2429.   */
  2430. public function testSkinTemplatesAction($skin, $tmpl)
  2431. {
  2432. $skin = preg_replace('![^-_a-zA-Z0-9]!', '', $skin);
  2433. $tmpl = preg_replace('![^-_a-zA-Z0-9]!', '', $tmpl);
  2434.  
  2435. if (empty($skin) or empty($tmpl)) {
  2436. throw new Kohana_404_Exception();
  2437. }
  2438.  
  2439. try {
  2440. $q = "SELECT id FROM ~subsites WHERE code = ?";
  2441. $subsite_id = Pdb::query($q, [$skin], 'val');
  2442. } catch (RowMissingException $ex) {
  2443. $subsite_id = SubsiteSelector::$subsite_id;
  2444. }
  2445.  
  2446. // Fake the subsite environment so nav and breadcrumb will work
  2447. SubsiteSelector::$subsite_id = $subsite_id;
  2448. SubsiteSelector::$subsite_code = $skin;
  2449. SubsiteSelector::$content_id = $subsite_id;
  2450.  
  2451. // Force a reload of the tree (in case tree is already loaded for some reason)
  2452. $root = Navigation::loadPageTree($subsite_id, false, true);
  2453.  
  2454. // Find a node for sidenav, breadcrumb, etc.
  2455. // Preference goes to one with children, but fallback is one without
  2456. if ($root and count($root->children) > 0) {
  2457. $fake_node = null;
  2458. foreach ($root->children as $nd) {
  2459. if ($nd['show_in_nav'] and count($nd->children)) {
  2460. $fake_node = $nd;
  2461. break;
  2462. }
  2463. }
  2464.  
  2465. if ($fake_node == null) {
  2466. foreach ($root->children as $nd) {
  2467. if ($nd['show_in_nav']) {
  2468. $fake_node = $nd;
  2469. break;
  2470. }
  2471. }
  2472. }
  2473.  
  2474. if ($fake_node != null) {
  2475. $fake_node['name'] = 'Template test';
  2476. Navigation::setPageNodeMatcher(new TreenodeValueMatcher('id', $fake_node['id']));
  2477. }
  2478. }
  2479.  
  2480. $content = new View('sprout/dbtools/skin_test_content');
  2481. $email = new View('sprout/email/testing_long');
  2482.  
  2483. // Page templates
  2484. $view = new View('skin/' . $tmpl);
  2485. $view->page_title = 'Template test';
  2486. $view->browser_title = 'Template test';
  2487. $view->main_content = $content->render();
  2488.  
  2489. // Email template
  2490. $view->html_title = $view->page_title;
  2491. $view->content = $email->render();
  2492.  
  2493. echo $view->render();
  2494. }
  2495.  
  2496.  
  2497. /**
  2498.   * Generate multiedit code
  2499.   **/
  2500. public function multimake()
  2501. {
  2502. echo '<style type="text/css">';
  2503. echo '.mini {font-size: 9px; color: #555;}';
  2504. echo '</style>';
  2505.  
  2506. if (empty($_POST)) {
  2507. $modules_dir = DOCROOT . 'modules';
  2508. $xml_files = glob("$modules_dir/*/db_struct.xml");
  2509. $opts = array();
  2510. foreach ($xml_files as $file) {
  2511. $file = substr($file, strlen($modules_dir) + 1);
  2512. $module = dirname($file);
  2513. $opts[$module] = $module;
  2514. }
  2515.  
  2516. echo '<form method="POST">';
  2517. Form::nextFieldDetails('Select module', true);
  2518. echo Form::dropdown('module', ['-wrapper-class' => 'white'], $opts);
  2519. echo '<div class="action-bar"><button type="submit" class="button icon-after icon-keyboard_arrow_right">Next</button></div>';
  2520.  
  2521. $this->template('Generate multiedit code');
  2522. return;
  2523. }
  2524.  
  2525. if (!empty($_POST['module'])) {
  2526. $doc = self::xmlLoad($_POST['module']);
  2527. $tables = self::xmlGetTables($doc);
  2528.  
  2529. $module_tables = array();
  2530. $invalid_tables = array();
  2531. foreach ($tables as $table) {
  2532. $cols = self::xmlGetColumns($doc, $table);
  2533. if (!isset($cols['id'])) continue;
  2534.  
  2535. $full_name = $_POST['module'] . '/' . $table;
  2536.  
  2537. // Look for a {something}_id column.
  2538. // A table without such a column is probably invalid, so put it
  2539. // at the bottom of the list, with an asterisk to warn the user
  2540. $valid = false;
  2541. foreach ($cols as $name => $defn) {
  2542. if ($name == 'id') continue;
  2543. if ($name == 'subsite_id') continue;
  2544. if (preg_match('/_id$/', $name)) {
  2545. $valid = true;
  2546. break;
  2547. }
  2548. }
  2549.  
  2550. if ($valid) {
  2551. $module_tables[$full_name] = $table;
  2552. } else {
  2553. $invalid_tables[$full_name] = "* $table";
  2554. }
  2555. }
  2556. $opts = array_merge($module_tables, $invalid_tables);
  2557.  
  2558. echo "<h3>Module: <b>{$_POST['module']}</b></h3>";
  2559. if (count($opts) > 0) {
  2560. echo '<form method="POST">';
  2561. Form::nextFieldDetails('Select table which will store the data', true, 'i.e. the sub-table');
  2562. echo Form::dropdown('table', ['-wrapper-class' => 'white'], $opts);
  2563. echo '<div class="action-bar"><button type="submit" class="button icon-after icon-keyboard_arrow_right">Next</button></div>';
  2564. echo '</form>';
  2565. } else {
  2566. echo '<p>No useable tables in this module</p>';
  2567. }
  2568.  
  2569. $this->template('Generate multiedit code');
  2570. return;
  2571. }
  2572.  
  2573. if (!empty($_POST['table'])) {
  2574. list($module, $table) = explode('/', $_POST['table']);
  2575.  
  2576. $doc = self::xmlLoad($module);
  2577. $columns = self::xmlGetColumns($doc, $table);
  2578. $opts = array();
  2579. $selected = null;
  2580. foreach ($columns as $name => $defn) {
  2581. if ($name == 'id') continue;
  2582. $value = "{$module}/{$table}/{$name}";
  2583. $opts[$value] = $name;
  2584.  
  2585. if ($selected) continue;
  2586. if ($name == 'subsite_id') continue;
  2587.  
  2588. if (substr($name, -3) == '_id') $selected = $value;
  2589. }
  2590.  
  2591. $matches = array();
  2592. preg_match('/_([a-z0-9]+)$/', $table, $matches);
  2593. Fb::setData(array('group' => @$matches[1], 'linker' => $selected));
  2594.  
  2595. echo "<h3>Module: <b>{$module}</b><br>Table: <b>{$table}</b></h3>";
  2596.  
  2597. echo '<form method="POST">';
  2598. echo '<div class="field-group-wrap -clearfix"><div class="field-group-item col col--one-half">';
  2599. Form::nextFieldDetails('Group name', true, 'e.g. people');
  2600. echo Form::text('group', ['-wrapper-class' => 'white']);
  2601. echo '</div>';
  2602.  
  2603. echo '<div class="field-group-item col col--one-half">';
  2604. Form::nextFieldDetails('Column which links to base table', true, 'e.g. user_id');
  2605. echo Form::dropdown('linker', ['-wrapper-class' => 'white'], $opts);
  2606. echo '</div></div>';
  2607.  
  2608. echo '<div class="action-bar"><button type="submit" class="button icon-after icon-keyboard_arrow_right">Generate code</button></div>';
  2609.  
  2610. echo '</form>';
  2611.  
  2612. $this->template('Generate multiedit code');
  2613. return;
  2614. }
  2615.  
  2616. if (empty($_POST['linker']) or empty($_POST['group'])) {
  2617. echo '<p>Huh?</p>';
  2618.  
  2619. $this->template('Generate multiedit code');
  2620. return;
  2621. }
  2622.  
  2623. list($module, $table, $linker) = explode('/', $_POST['linker']);
  2624. $doc = self::xmlLoad($module);
  2625. $columns = self::xmlGetColumns($doc, $table);
  2626. $ordered = isset($columns['record_order']);
  2627.  
  2628. echo "<h3>Module: <b>{$module}</b><br>Table: <b>{$table}</b><br>Group name: <b>{$_POST['group']}</b><br>Linking column: <b>{$linker}</b></h3>";
  2629.  
  2630. $file = DOCROOT . 'sprout/views/dbtools/multimake_template.php';
  2631. $template = file_get_contents($file);
  2632. $template = preg_replace('/(multiedit[-_])people/', '$1' . $_POST['group'], $template);
  2633. $template = str_replace('user_people', $table, $template);
  2634. $template = str_replace('People', ucfirst($_POST['group']), $template);
  2635. $template = str_replace('people', $_POST['group'], $template);
  2636. $single = ucfirst(Inflector::singular($_POST['group']));
  2637. $template = str_replace('Person', $single, $template);
  2638. $template = str_replace('user_id', $linker, $template);
  2639.  
  2640. if ($ordered) {
  2641. $reorder = '<?php MultiEdit::reorder(); ?>';
  2642. $init = '$order = 0;';
  2643. } else {
  2644. $reorder = '';
  2645. $init = '';
  2646. $template = str_replace('record_order', 'id', $template);
  2647. }
  2648. $template = preg_replace('#//\s*REORDER\s*//#', $reorder, $template);
  2649. $template = preg_replace('#//\s*INIT_ORDER\s*//#', $init, $template);
  2650.  
  2651. // Generate form fields for view
  2652. $data = '';
  2653. foreach ($columns as $name => $type) {
  2654. if ($name == 'id') continue;
  2655. if ($name == $linker) continue;
  2656. if ($name == 'record_order') continue;
  2657. $label = ucfirst($name);
  2658. $label = preg_replace('/_id$/', '', $label);
  2659. $label = str_replace('_', ' ', $label);
  2660. $data .= "\t<p><b>{$label}:</b>\n";
  2661. $data .= "\t" . '<br><input type="text" name="m_' .
  2662. Enc::html($name) . "\"></p>\n\n";
  2663. }
  2664. $template = preg_replace('#//\s*INPUTS\s*//#', trim($data), $template);
  2665.  
  2666. // Generate update data
  2667. $data = '';
  2668. foreach ($columns as $name => $type) {
  2669. if ($name == 'id') continue;
  2670. $data .= "\t\$update_fields['" . $name . "'] = ";
  2671. if ($name == 'record_order') {
  2672. $data .= "\$order++;\n";
  2673. } else if ($name == $linker) {
  2674. $data .= "\$item_id;\n";
  2675. } else {
  2676. $data .= "\$data['" . $name . "'];";
  2677. if ($name == 'date_added' or $name == 'date_modified') {
  2678. $data .= ' // WARNING!!!';
  2679. }
  2680. $data .= "\n";
  2681. }
  2682. }
  2683. $template = preg_replace('#//\s*UPDATES\s*//#', trim($data), $template);
  2684.  
  2685. $template = str_replace("\n\n\n", "\n\n", $template);
  2686.  
  2687. highlight_string($template);
  2688.  
  2689. $this->template('Generate multiedit code');
  2690. }
  2691.  
  2692. /**
  2693.   * @return array
  2694.   */
  2695. private static function xmlGetTables(DOMDocument $doc)
  2696. {
  2697. $table_els = $doc->getElementsByTagName('table');
  2698. $tables = array();
  2699. foreach ($table_els as $el) {
  2700. $tables[] = $el->getAttribute('name');
  2701. }
  2702. return $tables;
  2703. }
  2704.  
  2705. /**
  2706.   * @return array
  2707.   */
  2708. private static function xmlGetColumns(DOMDocument $doc, $table_name)
  2709. {
  2710. $table_els = $doc->getElementsByTagName('table');
  2711. $table = null;
  2712. foreach ($table_els as $el) {
  2713. if ($el->getAttribute('name') == $table_name) {
  2714. $table = $el;
  2715. break;
  2716. }
  2717. }
  2718. if (!$table) return array();
  2719.  
  2720. $columns = array();
  2721. $column_els = $table->getElementsByTagName('column');
  2722. foreach ($column_els as $el) {
  2723. $name = $el->getAttribute('name');
  2724. $type = $el->getAttribute('type');
  2725. $columns[$name] = $type;
  2726. }
  2727.  
  2728. return $columns;
  2729. }
  2730.  
  2731. private static function xmlLoad($module)
  2732. {
  2733. $file = DOCROOT . "modules/{$module}/db_struct.xml";
  2734. $doc = new DOMDocument();
  2735. $doc->loadXML(file_get_contents($file));
  2736. return $doc;
  2737. }
  2738.  
  2739.  
  2740. /**
  2741.   * Renders form to send emails
  2742.   **/
  2743. public function email()
  2744. {
  2745. $op = AdminAuth::getDetails();
  2746.  
  2747. $data = [];
  2748. $data['emails'] = $op['email'];
  2749. $data['from'] = Kohana::config('sprout.site_email');
  2750. $data['msg'] = 'long';
  2751.  
  2752. Form::setData($data);
  2753.  
  2754. $out = '<form action="SITE/dbtools/emailSend" method="post">';
  2755. $out .= Csrf::token();
  2756.  
  2757. Form::nextFieldDetails('Who to send to', false, 'one address per line');
  2758. $out .= Form::multiline('emails', []);
  2759.  
  2760. Form::nextFieldDetails('Different FROM address', false);
  2761. $out .= Form::email('from');
  2762.  
  2763. Form::nextFieldDetails('Message to send', false);
  2764. $out .= Form::multiradio('msg', [], ['long' => 'Long test email - tables, headings, unicode, etc.', 'short' => 'Short simple test email']);
  2765.  
  2766. Form::nextFieldDetails('Information', false);
  2767. $out .= Form::checkboxBoolList('', [], ['debug' => 'Show debugging information']);
  2768.  
  2769. $out .= '<button class="button" type="submit">Send emails</button>';
  2770. $out .= '</form>';
  2771.  
  2772. echo $out;
  2773. $this->template('Email');
  2774. }
  2775.  
  2776.  
  2777. /**
  2778.   * Process form submission
  2779.   **/
  2780. public function emailSend()
  2781. {
  2782. Csrf::checkOrDie();
  2783.  
  2784. if (empty($_POST['emails'])) {
  2785. Url::redirect('dbtools/email');
  2786. }
  2787.  
  2788. if ($_POST['msg'] == 'long') {
  2789. $subject = "Test email containing a little bit of üńìĉȯḍē.";
  2790. $view = new View('sprout/email/testing_long');
  2791. $body = $view->render();
  2792.  
  2793. } else if ($_POST['msg'] == 'short') {
  2794. $subject = 'Test';
  2795. $body = '<p>This is a test email.</p>';
  2796. }
  2797.  
  2798. if (!empty($_POST['from'])) {
  2799. Validity::email($_POST['from']);
  2800. }
  2801.  
  2802. $addresses = explode("\n", $_POST['emails']);
  2803. $succ = $fail = 0;
  2804. foreach ($addresses as $e) {
  2805. $e = trim($e);
  2806. if (! $e) continue;
  2807.  
  2808. echo '<h2>', Enc::html($e), '</h2>';
  2809.  
  2810. try {
  2811. Validity::email($e);
  2812. } catch (ValidationException $ex) {
  2813. echo '<p>', Enc::html($ex->getMessage()), '</p>';
  2814. continue;
  2815. }
  2816.  
  2817. $mail = new Email();
  2818. $mail->AddAddress($e);
  2819. $mail->Subject = $subject;
  2820. $mail->SkinnedHTML($body);
  2821.  
  2822. if (!empty($_POST['debug'])) {
  2823. $mail->SMTPDebug = 3;
  2824. }
  2825.  
  2826. if (!empty($_POST['from'])) {
  2827. $mail->From = $_POST['from'];
  2828. }
  2829.  
  2830. $result = $mail->Send();
  2831. $log = ob_get_clean();
  2832.  
  2833. if ($log) {
  2834. echo '<pre>', Enc::html($log), '</pre>';
  2835. }
  2836.  
  2837. if ($result) {
  2838. echo '<p>Sent email to <b>', Enc::html($e), '</b>.</p>';
  2839. $succ++;
  2840. } else {
  2841. echo '<p>Sending to <b>', Enc::html($e), '</b> failed!</p>';
  2842. $fail++;
  2843. }
  2844. }
  2845.  
  2846. echo '<h2>Summary</h2>';
  2847. echo '<p><b>Success:</b> ', $succ, '<br><b>Failed:</b> ', $fail, '</p>';
  2848. echo '<p><a href="SITE/dbtools/email" class="button">Send more!</a></p>';
  2849. $this->template('Email');
  2850. }
  2851.  
  2852.  
  2853. /**
  2854.   * Renders form to imoprt Sprout2 Export XML
  2855.   *
  2856.   * @return void Echos HTML directly
  2857.   */
  2858. public function importXML()
  2859. {
  2860. $view = new View('sprout/dbtools/import_xml');
  2861. $view->subsites = Pdb::lookup('subsites');
  2862. echo $view->render();
  2863.  
  2864. $this->template('Import Sprout 2 pages');
  2865. }
  2866.  
  2867.  
  2868. /**
  2869.   * Process Sprout2 Export XML into this CMS
  2870.   *
  2871.   * @return void Redirects
  2872.   */
  2873. public function importXmlAction()
  2874. {
  2875. Csrf::checkOrDie();
  2876.  
  2877. $_POST['subsite_id'] = (int) @$_POST['subsite_id'];
  2878. $_POST['page_id'] = (int) @$_POST['page_id'];
  2879.  
  2880. // Validate sub-site
  2881. if (empty($_POST['subsite_id'])) {
  2882. Notification::error('Please select a sub-site');
  2883. Url::redirect('dbtools/importXML');
  2884. }
  2885.  
  2886. // Validate file type
  2887. $ext = strtolower(File::getExt($_FILES['filename']['name']));
  2888. if ($ext != 'xml') {
  2889. Notification::error('Invalid file type');
  2890. Url::redirect('dbtools/importXML');
  2891. }
  2892.  
  2893. // Determine temp filename
  2894. $timestamp = time();
  2895. $tempname = APPPATH . "temp/dbtools_import_{$timestamp}.{$ext}";
  2896.  
  2897. // Attempt upload
  2898. $res = @copy($_FILES['filename']['tmp_name'], $tempname);
  2899. if (! $res) {
  2900. Notification::error('Unable to copy file to temporary directory');
  2901. Url::redirect('dbtools/importXML');
  2902. }
  2903.  
  2904. // Run the import tool
  2905. $pages = ImportCMS::import($tempname);
  2906.  
  2907. unlink($tempname);
  2908.  
  2909. // Render table of pages that need widgets replaced
  2910. $list = new Itemlist();
  2911. $list->main_columns = [
  2912. 'Old ID' => 'old_id',
  2913. 'New ID' => 'new_id',
  2914. 'Widgets' => 'widgets',
  2915. ];
  2916. $list->items = $pages;
  2917.  
  2918. echo $list->render();
  2919.  
  2920. $this->template('Successfully imported Sprout 2 pages');
  2921. }
  2922.  
  2923.  
  2924. /**
  2925.   * Render page drop-down for given sub-site
  2926.   *
  2927.   * @param int $subsite_id
  2928.   * @return void Echos HTML directly
  2929.   */
  2930. public function ajaxPageIds($subsite_id)
  2931. {
  2932. $subsite_id = (int) $subsite_id;
  2933. AdminAuth::checkLogin();
  2934.  
  2935. Form::nextFieldDetails('Parent page', false, 'Import as child pages of selected parent page');
  2936. echo Form::pageDropdown('page_id', [], ['subsite' => $subsite_id]);
  2937. }
  2938.  
  2939.  
  2940. /**
  2941.   * Render API test form within DB tools
  2942.   *
  2943.   * @param string $class
  2944.   * @param string $method
  2945.   * @return void Echos HTML directly
  2946.   */
  2947. public function api($class, $method)
  2948. {
  2949. AdminAuth::checkLogin();
  2950.  
  2951. $ctlr = Sprout::instance($class);
  2952.  
  2953. if (!method_exists($ctlr, $method)) throw new InvalidArgumentException(sprintf('Method "%s" does not exist', $method));
  2954.  
  2955. $reflect = new ReflectionMethod($ctlr, $method);
  2956. if (!$reflect->isPublic()) throw new InvalidArgumentException(sprintf('Method "%s" does not exist', $method));
  2957.  
  2958. $args = array_slice(func_get_args(), 2);
  2959. $html = call_user_func_array([$ctlr, $method], $args);
  2960.  
  2961. // Fetch page title
  2962. $title = 'API test';
  2963. foreach (self::$tools['APIs'] as $api) {
  2964. $matches = array();
  2965. preg_match('/dbtools\/api\/([a-zA-Z0-9_\%]+)\/([a-zA-Z0-9_\%]+)/', $api['url'], $matches);
  2966.  
  2967. if ((!empty($matches[1]) and urldecode($matches[1]) == $class) and (!empty($matches[2]) and urldecode($matches[2]) == $method)) {
  2968. $title = $api['name'];
  2969. break;
  2970. }
  2971. }
  2972.  
  2973. $this->template($title, $html);
  2974. }
  2975.  
  2976.  
  2977. /**
  2978.   * Render form to set QR Code string
  2979.   * @return void Echos HTML directly
  2980.   */
  2981. public function qrCodeForm()
  2982. {
  2983. $view = new View('sprout/dbtools/qr_form');
  2984.  
  2985. if (!empty($_GET['payload'])) {
  2986. $view->img = sprintf('%sdbtools/qrCodeImage?payload=%s', Sprout::absRoot(), Enc::url($_GET['payload']));
  2987. }
  2988.  
  2989. echo $view->render();
  2990. }
  2991.  
  2992.  
  2993. /**
  2994.   * Renders QR code image
  2995.   * @return void Echos PNG directly
  2996.   */
  2997. public function qrCodeImage()
  2998. {
  2999. header('Content-Type: image/png');
  3000. QrCode::render(urldecode($_GET['payload']));
  3001. }
  3002. }
  3003.