Revision [10064]
This is an old revision of DbInfoAction made by JavaWoman on 2005-07-17 13:44:01.
DbInfo Action
Now implemented as a WikkaBetaFeatures beta feature on this server.See also:
Documentation: DbInfoActionInfo.What
Just a little action that provides Admins an easy way to inspect the structure of the tables Wikka is using.
Why
Admins, and especially admins that like to tweak Wikka and create their own extensions, frequently need to check what exactly the database structure is that Wikka is using. Column names and sizes, indexes, and such are importantthings to know when creating extensions.
Of course it's possible to use an external client like PhpMyAdmin or a desktop client but for a quick lookup it's handy to have that information available right inside Wikka.
How
Although the PHP implementation of MySQL provides a few shortcuts with special functions, these don't support all information that we need. However, the *SHOW* MySQL command can reveal all we need to know, and it can be used with the PHP PHP:mysql_query function.
Limitations
Since revealing database structure is a potential security risk, only adminstrators will be able to see what this little action has on offer.
Apart from that, it makes use of the configured Wikka database user; what this user can see, really depends on what permissions it has been given prior to installation of Wikka, so your mileage may vary.
The Code
Save as actions/dbinfo.php:
- <?php
- /**
- * Displays definition data (DDL) for the database and tables Wikka uses.
- *
- * Features:
- * By default shows creation DDL for the database Wikka uses, and a drill-down form to show
- * creation DDL for each of the wikka tables.
- * By specifying all='1' possibly more databases become visible (depending on the permissions of the Wikka database user);
- * if multiple databases are visible, a selection form is shown to pick a database.
- * By specifying prefix='0' the prefix configured for Wikka is ignored, allowing other tables in the same database (if any)
- * to be inspected.
- *
- * Syntax:
- * {{dbinfo [all="0|1"] [prefix="0|1"]}}
- *
- * @package Actions
- * @subpackage DatabaseAdmin
- * @name DBinfo
- *
- * @author {@link http://wikka.jsnx.com/JavaWoman JavaWoman}
- * @copyright Copyright © 2005, Marjolein Katsma
- * @license http://www.gnu.org/copyleft/lesser.html GNU Lesser General Public License
- * @since Wikka 1.1.6.x
- * @version 0.3
- *
- * @input string $all optional: 0|1; default: 0
- * - 0: show only the database Wikka's tables are in (if visible)
- * - 1: show all (visible) databases
- * @input integer $prefix optional: 0|1; default: 1
- * - 0: all tables regardless of prefix
- * - 1: show only tables with Wikka-configured name prefix
- *
- * @output string drill-down forms to show databases, tables and creation DDL for them
- *
- * @uses IsAdmin()
- * @uses FormOpen()
- * @uses FormClose()
- * @uses Format()
- */
- // escape & placeholder: action allowed only once per page
- {
- echo '{{dbinfo}}';
- return;
- }
- // ----------------- constants and variables ------------------
- // constants
- // set defaults
- $bAll = FALSE; # one column for columnar layout
- $bPrefix = TRUE; # default display type
- // UI strings
- define('TXT_INFO_1','This utility provides some information about the database(s) and tables in your system.');
- define('TXT_INFO_2',' Depending on permissions for the Wikka database user, not all databases or tables may be visible.');
- define('TXT_INFO_3',' Where creation DDL is given, this reflects everything that would be needed to exactly recreate the same database and table definitions,');
- $hdDbInfo = HD_DBINFO;
- $hdDatabase = HD_DBINFO_DB;
- $hdTables = HD_DBINFO_TABLES;
- $txtActionInfo = TXT_INFO_1.TXT_INFO_2.TXT_INFO_3.TXT_INFO_4;
- $msgOnlyAdmin = MSG_ONLY_ADMIN;
- // variables
- $isAdmin = $this->IsAdmin();
- $database = $this->config['mysql_database'];
- $prefix = $this->config['table_prefix'];
- // ---------------------- processsing --------------------------
- // --------------- get parameters ----------------
- if ($isAdmin)
- {
- {
- foreach ($vars as $param => $value)
- {
- switch ($param)
- {
- case 'all':
- if ($value == 1) $bAll = TRUE;
- break;
- case 'prefix':
- if ($value == 0) $bPrefix = FALSE;
- break;
- }
- }
- }
- }
- // ------------------ get data -------------------
- if ($isAdmin)
- {
- // list of databases to choose from
- if ($bAll)
- {
- $query = 'SHOW DATABASES';
- if ($tableresult)
- {
- {
- $aDbList[] = $row['Database'];
- }
- }
- else # catch-all if no databases are / can be shown
- {
- $aDbList[] = $database;
- }
- }
- else
- {
- $aDbList[] = $database;
- }
- // data for selected database
- if ($bAll)
- {
- {
- {
- $seldb = $_POST['seldb'];
- }
- else # ignore invalid choice
- {
- $seldb = $database;
- }
- }
- }
- else
- {
- $seldb = $database; # no choice: wikka database
- }
- {
- $query = 'SHOW CREATE DATABASE '.$seldb;
- if ($dbcreateresult)
- {
- $dbcreate = $row['Create Database'];
- }
- }
- // table list
- {
- $query = 'SHOW TABLES FROM '.$seldb;
- if ($bPrefix)
- {
- $pattern = $prefix.'%';
- $query .= " LIKE '".$pattern."'";
- }
- if ($tablelistresult)
- {
- $colname = 'Tables_in_'.$seldb;
- if ($bPrefix)
- {
- $colname .= ' ('.$pattern.')';
- }
- {
- $aTableList[] = $row[$colname];
- }
- }
- }
- // data for selected table
- {
- {
- $seltable = $_POST['seltable'];
- $query = 'SHOW CREATE TABLE '.$seltable;
- if ($tablecreateresult)
- {
- $tablecreate = $row['Create Table'];
- }
- }
- }
- }
- // ---------------- build forms ------------------
- if ($isAdmin)
- {
- // build datatabase selection form if more than one database to show
- {
- $dbselform = $this->FormOpen('','','POST','dbsel');
- $dbselform .= '<fieldset>'."\n";
- $dbselform .= ' <legend>'.FORM_SELDB_LEGEND.'</legend>'."\n";
- $dbselform .= ' <label for="seldb" class="mainlabel">'.FORM_SELDB_OPT_LABEL.'</label> '."\n";
- $dbselform .= ' <select name="seldb" id="seldb">'."\n";
- foreach ($aDbList as $db)
- {
- {
- $dbselform .= ' <option value="'.$db.'"'.(($seldb == $db)? ' selected="selected"' : '').'>'.$db.'</option>'."\n";
- }
- else
- {
- $dbselform .= ' <option value="'.$db.'">'.$db.'</option>'."\n";
- }
- }
- $dbselform .= " </select>\n";
- $dbselform .= ' <input type="submit" name="dbselect" "value="'.FORM_SUBMIT_SELDB.'">'."\n";
- $dbselform .= "</fieldset>\n";
- $dbselform .= $this->FormClose();
- }
- else
- {
- }
- // build table selection form
- {
- {
- $tableselform = $this->FormOpen('','','POST','tablesel');
- $tableselform .= '<fieldset class="hidden">'."\n";
- $tableselform .= ' <input type="hidden" name="seldb" "value="'.$seldb.'">'."\n";
- $tableselform .= '</fieldset>'."\n";
- $tableselform .= '<fieldset>'."\n";
- $tableselform .= ' <legend>'.FORM_SELTABLE_LEGEND.'</legend>'."\n";
- $tableselform .= ' <label for="seltable" class="mainlabel">'.FORM_SELTABLE_OPT_LABEL.'</label> '."\n";
- $tableselform .= ' <select name="seltable" id="seltable">'."\n";
- foreach ($aTableList as $table)
- {
- {
- $tableselform .= ' <option value="'.$table.'"'.(($seltable == $table)? ' selected="selected"' : '').'>'.$table.'</option>'."\n";
- }
- else
- {
- $tableselform .= ' <option value="'.$table.'">'.$table.'</option>'."\n";
- }
- }
- $tableselform .= " </select>\n";
- $tableselform .= ' <input type="submit" name="tableselect" "value="'.FORM_SUBMIT_SELTABLE.'">'."\n";
- $tableselform .= "</fieldset>\n";
- $tableselform .= $this->FormClose();
- }
- else
- {
- }
- }
- // build results
- {
- {
- $dbresult = $this->Format('% %(sql)'.$dbcreate.'% %');
- }
- else
- {
- }
- {
- {
- $tableresult = $this->Format('% %(sql)'.$tablecreate.'% %');
- }
- else
- {
- }
- }
- }
- // ids - use constant for variable-content heading
- $idDbDdl = $this->makeId('hn','ddl_for_database');
- $idTableDdl = $this->makeId('hn','ddl_for_table');
- }
- // ------------ show data and forms --------------
- echo '<div id="dbinfo">'."\n";
- echo '<h3>'.$hdDbInfo.'</h3>'."\n";
- if ($isAdmin)
- {
- echo '<p>'.$txtActionInfo."</p>\n";
- echo '<h4>'.$hdDatabase.'</h4>'."\n";
- {
- echo $dbselform;
- }
- {
- echo $dbselmsg;
- }
- {
- echo "<br />\n";
- echo '<h5 id="'.$idDbDdl.'">'.$hdDbDdl.'</h5>'."\n";
- echo $dbresult;
- echo "<br />\n";
- echo '<h4>'.$hdTables.'</h4>'."\n";
- {
- echo $tableselform;
- }
- {
- echo $tableselmsg;
- }
- {
- echo "<br />\n";
- echo '<h5 id="'.$idTableDdl.'">'.$hdTableDdl.'</h5>'."\n";
- echo $tableresult;
- }
- }
- echo "</div>\n";
- }
- else
- {
- echo '<p>'.$msgOnlyAdmin."</p>\n";
- }
- ?>
Make sure to replace all occurrences of '% %' with '%%'!
Todo
maybe later
Comments?
As always, comments and suggestions very welcome.
CategoryDevelopmentActions