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.
This is the development page for the DbInfo action.
 

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:
  1. <?php
  2. /**
  3.  * Displays definition data (DDL) for the database and tables Wikka uses.
  4.  *
  5.  * Features:
  6.  *  By default shows creation DDL for the database Wikka uses, and a drill-down form to show
  7.  *  creation DDL for each of the wikka tables.
  8.  *  By specifying all='1' possibly more databases become visible (depending on the permissions of the Wikka database user);
  9.  *  if multiple databases are visible, a selection form is shown to pick a database.
  10.  *  By specifying prefix='0' the prefix configured for Wikka is ignored, allowing other tables in the same database (if any)
  11.  *  to be inspected.
  12.  *
  13.  * Syntax:
  14.  *  {{dbinfo [all="0|1"] [prefix="0|1"]}}
  15.  *
  16.  * @package     Actions
  17.  * @subpackage  DatabaseAdmin
  18.  * @name        DBinfo
  19.  *
  20.  * @author      {@link http://wikka.jsnx.com/JavaWoman JavaWoman}
  21.  * @copyright   Copyright © 2005, Marjolein Katsma
  22.  * @license     http://www.gnu.org/copyleft/lesser.html GNU Lesser General Public License
  23.  * @since       Wikka 1.1.6.x
  24.  * @version     0.3
  25.  *
  26.  * @input       string  $all        optional: 0|1; default: 0
  27.  *                                  - 0: show only the database Wikka's tables are in (if visible)
  28.  *                                  - 1: show all (visible) databases
  29.  * @input       integer $prefix     optional: 0|1; default: 1
  30.  *                                  - 0: all tables regardless of prefix
  31.  *                                  - 1: show only tables with Wikka-configured name prefix
  32.  *
  33.  * @output      string  drill-down forms to show databases, tables and creation DDL for them
  34.  *
  35.  * @uses    IsAdmin()
  36.  * @uses    FormOpen()
  37.  * @uses    FormClose()
  38.  * @uses    Format()
  39.  */
  40.  
  41. // escape & placeholder: action allowed only once per page
  42. if (defined('HD_DBINFO'))
  43. {
  44.     echo '{{dbinfo}}';
  45.     return;
  46. }
  47.  
  48. // ----------------- constants and variables ------------------
  49.  
  50. // constants
  51.  
  52. // set defaults
  53. $bAll       = FALSE;        # one column for columnar layout
  54. $bPrefix    = TRUE;         # default display type
  55.  
  56. // UI strings
  57. define('HD_DBINFO','Database Information');
  58. define('HD_DBINFO_DB','Database');
  59. define('HD_DBINFO_TABLES','Tables');
  60. define('HD_DB_CREATE_DDL','DDL to create database %s:');                # %s will hold database name
  61. define('HD_TABLE_CREATE_DDL','DDL to create table %s:');                # %s will hold table name
  62. define('TXT_INFO_1','This utility provides some information about the database(s) and tables in your system.');
  63. define('TXT_INFO_2',' Depending on permissions for the Wikka database user, not all databases or tables may be visible.');
  64. 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,');
  65. define('TXT_INFO_4',' including defaults that may not have been specified explicitly.');
  66. define('FORM_SELDB_LEGEND','Databases');
  67. define('FORM_SELTABLE_LEGEND','Tables');
  68. define('FORM_SELDB_OPT_LABEL','Select a database:');
  69. define('FORM_SELTABLE_OPT_LABEL','Select a table:');
  70. define('FORM_SUBMIT_SELDB','Select');
  71. define('FORM_SUBMIT_SELTABLE','Select');
  72. define('MSG_ONLY_ADMIN','Sorry, only administrators can view database information');
  73. define('MSG_SINGLE_DB',"Information for the '%s' database.");           # %s will hold database name
  74. define('MSG_NO_TABLES',"No tables found in the '%s' database.");        # %s will hold database name
  75. define('MSG_NO_DB_DDL','Creation DDL for %s could not be retrieved.');  # %s will hold database name
  76. define('MSG_NO_TABLE_DDL','Creation DDL for %s could not be retrieved.');# %s will hold table name
  77.  
  78. $hdDbInfo       = HD_DBINFO;
  79. $hdDatabase     = HD_DBINFO_DB;
  80. $hdTables       = HD_DBINFO_TABLES;
  81. $txtActionInfo  = TXT_INFO_1.TXT_INFO_2.TXT_INFO_3.TXT_INFO_4;
  82. $msgOnlyAdmin   = MSG_ONLY_ADMIN;
  83.  
  84. // variables
  85.  
  86. $isAdmin    = $this->IsAdmin();
  87. $database   = $this->config['mysql_database'];
  88. $prefix     = $this->config['table_prefix'];
  89.  
  90. // ---------------------- processsing --------------------------
  91.  
  92. // --------------- get parameters ----------------
  93.  
  94. if ($isAdmin)
  95. {
  96.     if (is_array($vars))
  97.     {
  98.         foreach ($vars as $param => $value)
  99.         {
  100.             switch ($param)
  101.             {
  102.                 case 'all':
  103.                     if ($value == 1) $bAll = TRUE;
  104.                     break;
  105.                 case 'prefix':
  106.                     if ($value == 0) $bPrefix = FALSE;
  107.                     break;
  108.             }
  109.         }
  110.     }
  111. }
  112.  
  113. // ------------------ get data -------------------
  114.  
  115. if ($isAdmin)
  116. {
  117.     // list of databases to choose from
  118.     $aDbList = array();
  119.     if ($bAll)
  120.     {
  121.         $query = 'SHOW DATABASES';
  122.         $tableresult = mysql_query($query);
  123.         if ($tableresult)
  124.         {
  125.             while ($row = mysql_fetch_assoc($tableresult))
  126.             {
  127.                 $aDbList[] = $row['Database'];
  128.             }
  129.         }
  130.         else                                            # catch-all if no databases are / can be shown
  131.         {
  132.             $aDbList[] = $database;
  133.         }
  134.     }
  135.     else
  136.     {
  137.         $aDbList[] = $database;
  138.     }
  139.  
  140.     // data for selected database
  141.     if ($bAll)
  142.     {
  143.         if (isset($_POST['dbselect']) || isset($_POST['tableselect']))              # form submitted
  144.         {
  145.             if (isset($_POST['seldb']) && in_array($_POST['seldb'],$aDbList))       # valid choice
  146.             {
  147.                 $seldb = $_POST['seldb'];
  148.             }
  149.             else                                        # ignore invalid choice
  150.             {
  151.                 $seldb = $database;
  152.             }
  153.         }
  154.     }
  155.     else
  156.     {
  157.         $seldb = $database;                             # no choice: wikka database
  158.     }
  159.     if (isset($seldb))
  160.     {
  161.         $query = 'SHOW CREATE DATABASE '.$seldb;
  162.         $dbcreateresult = mysql_query($query);
  163.         if ($dbcreateresult)
  164.         {
  165.             $row = mysql_fetch_assoc($dbcreateresult);
  166.             $dbcreate = $row['Create Database'];
  167.         }
  168.     }
  169.  
  170.     // table list
  171.     $aTableList = array();
  172.     if (isset($seldb))
  173.     {
  174.         $query = 'SHOW TABLES FROM '.$seldb;
  175.         if ($bPrefix)
  176.         {
  177.             $pattern = $prefix.'%';
  178.             $query .= " LIKE '".$pattern."'";
  179.         }
  180.         $tablelistresult = mysql_query($query);
  181.         if ($tablelistresult)
  182.         {
  183.             $colname = 'Tables_in_'.$seldb;
  184.             if ($bPrefix)
  185.             {
  186.                 $colname .= ' ('.$pattern.')';
  187.             }
  188.             while ($row = mysql_fetch_assoc($tablelistresult))
  189.             {
  190.                 $aTableList[] = $row[$colname];
  191.             }
  192.         }
  193.     }
  194.  
  195.     // data for selected table
  196.     if (isset($_POST['tableselect']))                   # form submitted
  197.     {
  198.         if (isset($_POST['seltable']) && in_array($_POST['seltable'],$aTableList))  # valid choice
  199.         {
  200.             $seltable = $_POST['seltable'];
  201.             $query = 'SHOW CREATE TABLE '.$seltable;
  202.             $tablecreateresult = mysql_query($query);
  203.             if ($tablecreateresult)
  204.             {
  205.                 $row = mysql_fetch_assoc($tablecreateresult);
  206.                 $tablecreate = $row['Create Table'];
  207.             }
  208.         }
  209.     }
  210. }
  211.  
  212. // ---------------- build forms ------------------
  213.  
  214. if ($isAdmin)
  215. {
  216.     // build datatabase selection form if more than one database to show
  217.     if (count($aDbList) > 1)
  218.     {
  219.         $dbselform  = $this->FormOpen('','','POST','dbsel');
  220.         $dbselform .= '<fieldset>'."\n";
  221.         $dbselform .= ' <legend>'.FORM_SELDB_LEGEND.'</legend>'."\n";
  222.         $dbselform .= ' <label for="seldb" class="mainlabel">'.FORM_SELDB_OPT_LABEL.'</label> '."\n";
  223.         $dbselform .= ' <select name="seldb" id="seldb">'."\n";
  224.         foreach ($aDbList as $db)
  225.         {
  226.             if (isset($seldb))
  227.             {
  228.                 $dbselform .= '     <option value="'.$db.'"'.(($seldb == $db)? ' selected="selected"' : '').'>'.$db.'</option>'."\n";
  229.             }
  230.             else
  231.             {
  232.                 $dbselform .= '     <option value="'.$db.'">'.$db.'</option>'."\n";
  233.             }
  234.         }
  235.         $dbselform .= " </select>\n";
  236.         $dbselform .= ' <input type="submit" name="dbselect" "value="'.FORM_SUBMIT_SELDB.'">'."\n";
  237.         $dbselform .= "</fieldset>\n";
  238.         $dbselform .= $this->FormClose();
  239.     }
  240.     else
  241.     {
  242.         $dbselmsg = '<p>'.sprintf(MSG_SINGLE_DB,$aDbList[0])."</p>\n";
  243.     }
  244.  
  245.     // build table selection form
  246.     if (isset($seldb))
  247.     {
  248.         if (count($aTableList) > 0)
  249.         {
  250.             $tableselform  = $this->FormOpen('','','POST','tablesel');
  251.             $tableselform .= '<fieldset class="hidden">'."\n";
  252.             $tableselform .= '  <input type="hidden" name="seldb" "value="'.$seldb.'">'."\n";
  253.             $tableselform .= '</fieldset>'."\n";
  254.             $tableselform .= '<fieldset>'."\n";
  255.             $tableselform .= '  <legend>'.FORM_SELTABLE_LEGEND.'</legend>'."\n";
  256.             $tableselform .= '  <label for="seltable" class="mainlabel">'.FORM_SELTABLE_OPT_LABEL.'</label> '."\n";
  257.             $tableselform .= '  <select name="seltable" id="seltable">'."\n";
  258.             foreach ($aTableList as $table)
  259.             {
  260.                 if (isset($seltable))
  261.                 {
  262.                     $tableselform .= '      <option value="'.$table.'"'.(($seltable == $table)? ' selected="selected"' : '').'>'.$table.'</option>'."\n";
  263.                 }
  264.                 else
  265.                 {
  266.                     $tableselform .= '      <option value="'.$table.'">'.$table.'</option>'."\n";
  267.                 }
  268.             }
  269.             $tableselform .= "  </select>\n";
  270.             $tableselform .= '  <input type="submit" name="tableselect" "value="'.FORM_SUBMIT_SELTABLE.'">'."\n";
  271.             $tableselform .= "</fieldset>\n";
  272.             $tableselform .= $this->FormClose();
  273.         }
  274.         else
  275.         {
  276.             $tableselmsg = '<p>'.sprintf(MSG_NO_TABLES,$seldb)."</p>\n";
  277.         }
  278.     }
  279.  
  280.     // build results
  281.     if (isset($seldb))
  282.     {
  283.         $hdDbDdl = sprintf(HD_DB_CREATE_DDL,$seldb);
  284.         if (isset($dbcreate))
  285.         {
  286.             $dbresult = $this->Format('% %(sql)'.$dbcreate.'% %');
  287.         }
  288.         else
  289.         {
  290.             $dbresult = '<p>'.sprintf(MSG_NO_DB_DDL,$seldb).'</p>';
  291.         }
  292.         if (isset($seltable))
  293.         {
  294.             $hdTableDdl = sprintf(HD_TABLE_CREATE_DDL,$seltable);
  295.             if (isset($tablecreate))
  296.             {
  297.                 $tableresult = $this->Format('% %(sql)'.$tablecreate.'% %');
  298.             }
  299.             else
  300.             {
  301.                 $tableresult = '<p>'.sprintf(MSG_NO_TABLE_DDL,$seltable).'</p>';
  302.             }
  303.         }
  304.     }
  305.     // ids - use constant for variable-content heading
  306.     $idDbDdl    = $this->makeId('hn','ddl_for_database');
  307.     $idTableDdl = $this->makeId('hn','ddl_for_table');
  308. }
  309.  
  310.  
  311. // ------------ show data and forms --------------
  312.  
  313. echo '<div id="dbinfo">'."\n";
  314. echo '<h3>'.$hdDbInfo.'</h3>'."\n";
  315. if ($isAdmin)
  316. {
  317.     echo '<p>'.$txtActionInfo."</p>\n";
  318.     echo '<h4>'.$hdDatabase.'</h4>'."\n";
  319.     if (isset($dbselform))
  320.     {
  321.         echo $dbselform;
  322.     }
  323.     elseif (isset($dbselmsg))
  324.     {
  325.         echo $dbselmsg;
  326.     }
  327.     if (isset($seldb))
  328.     {
  329.         echo "<br />\n";
  330.         echo '<h5 id="'.$idDbDdl.'">'.$hdDbDdl.'</h5>'."\n";
  331.         echo $dbresult;
  332.  
  333.         echo "<br />\n";
  334.         echo '<h4>'.$hdTables.'</h4>'."\n";
  335.         if (isset($tableselform))
  336.         {
  337.             echo $tableselform;
  338.         }
  339.         elseif (isset($tableselmsg))
  340.         {
  341.             echo $tableselmsg;
  342.         }
  343.         if (isset($seltable))
  344.         {
  345.             echo "<br />\n";
  346.             echo '<h5 id="'.$idTableDdl.'">'.$hdTableDdl.'</h5>'."\n";
  347.             echo $tableresult;
  348.         }
  349.     }
  350.     echo "</div>\n";
  351. }
  352. else
  353. {
  354.     echo '<p>'.$msgOnlyAdmin."</p>\n";
  355. }
  356. ?>


Make sure to replace all occurrences of '% %' with '%%'!


Todo


maybe later


Comments?


As always, comments and suggestions very welcome.



CategoryDevelopmentActions
There are no comments on this page.
Valid XHTML :: Valid CSS: :: Powered by WikkaWiki