Revision history for DbInfoAction
Additions:
~-[[Docs:DbInfoActionInfo DbInfoActionInfo]]
Deletions:
Revision [18558]
Edited on 2008-01-28 00:11:57 by JavaWoman [Modified links pointing to docs server]No Differences
Revision [10116]
Edited on 2005-07-18 14:23:38 by JavaWoman [minor tweak and reference to supporting methods]Additions:
Documentation:
~-""DbInfoActionInfo""
Supporting code:
~-AdvancedFormOpen
~-GenerateUniqueId
>>This is the development page for the DbInfo action.::c::
Just a little action that provides Admins an easy way to inspect the **structure** of the tables Wikka is using. (It's not a data browser!)
* @uses MakeId()
$idDbInfo = $this->makeId('div','dbinfo');
echo '<div id="'.$idDbInfo.'">'."\n";
====Supporting methods====
As can be seen (and is documented in the docblock) the new ##""DbInfo()""## action uses two methods that are themselves beta features - both of them documented already: [[AdvancedFormOpen FormOpen()]] (in its beta version!) and [[GenerateUniqueId makeId()]]. See the documentation pages for for each for necessary supporting code for the DbInfo action!
====Styling====
~-""DbInfoActionInfo""
Supporting code:
~-AdvancedFormOpen
~-GenerateUniqueId
>>This is the development page for the DbInfo action.::c::
Just a little action that provides Admins an easy way to inspect the **structure** of the tables Wikka is using. (It's not a data browser!)
* @uses MakeId()
$idDbInfo = $this->makeId('div','dbinfo');
echo '<div id="'.$idDbInfo.'">'."\n";
====Supporting methods====
As can be seen (and is documented in the docblock) the new ##""DbInfo()""## action uses two methods that are themselves beta features - both of them documented already: [[AdvancedFormOpen FormOpen()]] (in its beta version!) and [[GenerateUniqueId makeId()]]. See the documentation pages for for each for necessary supporting code for the DbInfo action!
====Styling====
Deletions:
Just a little action that provides Admins an easy way to inspect the structure of the tables Wikka is using.
echo '<div id="dbinfo">'."\n";
===Styling===
Additions:
Not a real todo, but it could potentially be extended to prompt for a user/password combination for another database user; that might be handy for Admins taking care of several Wikis installed on the same server. Let us know if you'd be interested in such a feature.
Deletions:
Revision [10073]
Edited on 2005-07-17 22:46:10 by JavaWoman [adding styling and reference to AdvancedReferrersHandler]Additions:
Documentation: ""DbInfoActionInfo"">>This is the development page for the DbInfo action.::c::
Although the PHP implementation of MySQL provides a few shortcuts with special functions, these don't support all information that we need. However, the MySQL **##SHOW##** command can reveal all we need to know, and it can be used with the PHP PHP:mysql_query function.
===Styling===
>>**see also:**
""<a href="AdvancedReferrersHandler#hn_7._cssrefmenu.css">refmenu.css</a>"" on AdvancedReferrersHandler
>>The forms the action display are accessible. Like those for the AdvancedReferrersHandler, they need corresponding styling. In fact the styling is the same, but since these are stillbeta features they have not been turned into a general "forms" styling yet (as will eventually have to happen to give all forms a constent look and feel). For now, the solution was to extend the extra stylesheet created for the AdvancedReferrersHandler; it looks as follows now: ::c::
**##css/refmenu.css##**
%%(css;1)/*
This stylesheet is for the referrers and blacklist handlers.
It will need to be integrated with the main stylesheet.
JW 2005-07-08 - extended for the dbinfo action and forms.
*/
h4 {
margin-top: 0.3em !important; /* remove !important when integrating into main stylesheet or including it after that */
.refmenu {
margin: 0;
padding: 0;
margin-top: 1em;
.refmenu .menu {
margin: 0;
padding: 0;
.refmenu .menu li {
list-style: none;
float: left;
margin-right: 3px; /* margin-right goes together with float left (or vice versa) */
padding: 1px 2px;
font-size: 85%;
line-height: 1.2em;
color: #000000;
background-color: #DDDDDD;
br.clear {
clear: both;
form fieldset.hidden { /* for all forms! not just referrers/dbinfo */
display: none;
#refform, #dbinfo {
color: inherit;
background-color: inherit;
margin-top: 1em;
margin-bottom: 1em;
#refform {
width: 32em;
#form_dbsel, #form_tablesel {
width: 40em;
#refform fieldset, #form_dbsel fieldset, #form_tablesel fieldset {
padding: 1em;
margin-bottom: 0.3em;
border: 1px solid #666666;
#refform legend, #form_dbsel legend, #form_tablesel legend {
padding: 0 2px;
color: #000000;
background-color: #DDDDDD;
border: 1px solid #666666;
margin-bottom: 0.3em;
#refform .mainlabel {
float: left;
width: 4.6em; /* width will work on _floated_ element, even if not a block! */
padding-right: 0.5em;
#form_dbsel .mainlabel, #form_tablesel .mainlabel {
float: left;
width: 9.8em; /* width will work on _floated_ element, even if not a block! */
padding-right: 0.5em;
#q, #qo, #ho {
width: 10em;
#h {
width: 3em;
text-align: right;
#reflist {
margin-top: 1em;
margin-bottom: 1em;
border: none;
#reflist .hits {
width: 3em;
padding-right: 5px;
text-align: right;
vertical-align: middle;
#reflist .action {
width: 5em;
padding-left: 5px;
padding-right: 5px;
text-align: center;
vertical-align: middle;
#reflist .refs {
padding-left: 5px;
text-align: left;
vertical-align: middle;
The little stylesheet file to import into your own "skin" has been extended accordingly:
**##css/refmenu_col.css##**
%%(css;1)/*
For custom stylesheets: copy this into your stylesheet; the
adapt the colors here (made to match the default Wikka skin)
to match your own.
JW 2005-07-08 - extended for the dbinfo action and forms.
*/
.refmenu .menu li {
color: #000000;
background-color: #DDDDDD;
form fieldset.hidden { /* for all forms! not just referrers/dbinfo */
display: none;
#refform fieldset, #form_dbsel fieldset, #form_tablesel fieldset {
border: 1px solid #666666;
#refform legend, #form_dbsel legend, #form_tablesel legend {
color: #000000;
background-color: #DDDDDD;
border: 1px solid #666666;
Although the PHP implementation of MySQL provides a few shortcuts with special functions, these don't support all information that we need. However, the MySQL **##SHOW##** command can reveal all we need to know, and it can be used with the PHP PHP:mysql_query function.
===Styling===
>>**see also:**
""<a href="AdvancedReferrersHandler#hn_7._cssrefmenu.css">refmenu.css</a>"" on AdvancedReferrersHandler
>>The forms the action display are accessible. Like those for the AdvancedReferrersHandler, they need corresponding styling. In fact the styling is the same, but since these are stillbeta features they have not been turned into a general "forms" styling yet (as will eventually have to happen to give all forms a constent look and feel). For now, the solution was to extend the extra stylesheet created for the AdvancedReferrersHandler; it looks as follows now: ::c::
**##css/refmenu.css##**
%%(css;1)/*
This stylesheet is for the referrers and blacklist handlers.
It will need to be integrated with the main stylesheet.
JW 2005-07-08 - extended for the dbinfo action and forms.
*/
h4 {
margin-top: 0.3em !important; /* remove !important when integrating into main stylesheet or including it after that */
.refmenu {
margin: 0;
padding: 0;
margin-top: 1em;
.refmenu .menu {
margin: 0;
padding: 0;
.refmenu .menu li {
list-style: none;
float: left;
margin-right: 3px; /* margin-right goes together with float left (or vice versa) */
padding: 1px 2px;
font-size: 85%;
line-height: 1.2em;
color: #000000;
background-color: #DDDDDD;
br.clear {
clear: both;
form fieldset.hidden { /* for all forms! not just referrers/dbinfo */
display: none;
#refform, #dbinfo {
color: inherit;
background-color: inherit;
margin-top: 1em;
margin-bottom: 1em;
#refform {
width: 32em;
#form_dbsel, #form_tablesel {
width: 40em;
#refform fieldset, #form_dbsel fieldset, #form_tablesel fieldset {
padding: 1em;
margin-bottom: 0.3em;
border: 1px solid #666666;
#refform legend, #form_dbsel legend, #form_tablesel legend {
padding: 0 2px;
color: #000000;
background-color: #DDDDDD;
border: 1px solid #666666;
margin-bottom: 0.3em;
#refform .mainlabel {
float: left;
width: 4.6em; /* width will work on _floated_ element, even if not a block! */
padding-right: 0.5em;
#form_dbsel .mainlabel, #form_tablesel .mainlabel {
float: left;
width: 9.8em; /* width will work on _floated_ element, even if not a block! */
padding-right: 0.5em;
#q, #qo, #ho {
width: 10em;
#h {
width: 3em;
text-align: right;
#reflist {
margin-top: 1em;
margin-bottom: 1em;
border: none;
#reflist .hits {
width: 3em;
padding-right: 5px;
text-align: right;
vertical-align: middle;
#reflist .action {
width: 5em;
padding-left: 5px;
padding-right: 5px;
text-align: center;
vertical-align: middle;
#reflist .refs {
padding-left: 5px;
text-align: left;
vertical-align: middle;
The little stylesheet file to import into your own "skin" has been extended accordingly:
**##css/refmenu_col.css##**
%%(css;1)/*
For custom stylesheets: copy this into your stylesheet; the
adapt the colors here (made to match the default Wikka skin)
to match your own.
JW 2005-07-08 - extended for the dbinfo action and forms.
*/
.refmenu .menu li {
color: #000000;
background-color: #DDDDDD;
form fieldset.hidden { /* for all forms! not just referrers/dbinfo */
display: none;
#refform fieldset, #form_dbsel fieldset, #form_tablesel fieldset {
border: 1px solid #666666;
#refform legend, #form_dbsel legend, #form_tablesel legend {
color: #000000;
background-color: #DDDDDD;
border: 1px solid #666666;
Deletions:
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.
Additions:
$dbresult = '<p>'.sprintf(MSG_NO_DB_DDL,$seldb).'</p>';
$tableresult = '<p>'.sprintf(MSG_NO_TABLE_DDL,$seltable).'</p>';
$tableresult = '<p>'.sprintf(MSG_NO_TABLE_DDL,$seltable).'</p>';
Deletions:
$tableresult = sprintf(MSG_NO_TABLE_DDL,$seltable);
Additions:
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.
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.
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.
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.
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.
Deletions:
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.
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 Wikak, so your mileage may vary.
Additions:
=====DbInfo Action=====
//Now implemented as a [[WikkaBetaFeatures beta feature]] on this server.//
Documentation: DbInfoActionInfo.>>This is the development page for the DbInfo action.::c::
====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 Wikak, so your mileage may vary.
===The Code===
Save as **##actions/dbinfo.php##**:
%%(php;1)<?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
if (defined('HD_DBINFO'))
{
echo '{{dbinfo}}';
return;
}
// ----------------- constants and variables ------------------
// constants
// set defaults
$bAll = FALSE; # one column for columnar layout
$bPrefix = TRUE; # default display type
// UI strings
define('HD_DBINFO','Database Information');
define('HD_DBINFO_DB','Database');
define('HD_DBINFO_TABLES','Tables');
define('HD_DB_CREATE_DDL','DDL to create database %s:'); # %s will hold database name
define('HD_TABLE_CREATE_DDL','DDL to create table %s:'); # %s will hold table name
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,');
define('TXT_INFO_4',' including defaults that may not have been specified explicitly.');
define('FORM_SELDB_LEGEND','Databases');
define('FORM_SELTABLE_LEGEND','Tables');
define('FORM_SELDB_OPT_LABEL','Select a database:');
define('FORM_SELTABLE_OPT_LABEL','Select a table:');
define('FORM_SUBMIT_SELDB','Select');
define('FORM_SUBMIT_SELTABLE','Select');
define('MSG_ONLY_ADMIN','Sorry, only administrators can view database information');
define('MSG_SINGLE_DB',"Information for the '%s' database."); # %s will hold database name
define('MSG_NO_TABLES',"No tables found in the '%s' database."); # %s will hold database name
define('MSG_NO_DB_DDL','Creation DDL for %s could not be retrieved.'); # %s will hold database name
define('MSG_NO_TABLE_DDL','Creation DDL for %s could not be retrieved.');# %s will hold table name
$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)
{
if (is_array($vars))
{
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
$aDbList = array();
if ($bAll)
{
$query = 'SHOW DATABASES';
$tableresult = mysql_query($query);
if ($tableresult)
{
while ($row = mysql_fetch_assoc($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)
{
if (isset($_POST['dbselect']) || isset($_POST['tableselect'])) # form submitted
{
if (isset($_POST['seldb']) && in_array($_POST['seldb'],$aDbList)) # valid choice
{
$seldb = $_POST['seldb'];
}
else # ignore invalid choice
{
$seldb = $database;
}
}
}
else
{
$seldb = $database; # no choice: wikka database
}
if (isset($seldb))
{
$query = 'SHOW CREATE DATABASE '.$seldb;
$dbcreateresult = mysql_query($query);
if ($dbcreateresult)
{
$row = mysql_fetch_assoc($dbcreateresult);
$dbcreate = $row['Create Database'];
}
}
// table list
$aTableList = array();
if (isset($seldb))
{
$query = 'SHOW TABLES FROM '.$seldb;
if ($bPrefix)
{
$pattern = $prefix.'%';
$query .= " LIKE '".$pattern."'";
}
$tablelistresult = mysql_query($query);
if ($tablelistresult)
{
$colname = 'Tables_in_'.$seldb;
if ($bPrefix)
{
$colname .= ' ('.$pattern.')';
}
while ($row = mysql_fetch_assoc($tablelistresult))
{
$aTableList[] = $row[$colname];
}
}
}
// data for selected table
if (isset($_POST['tableselect'])) # form submitted
{
if (isset($_POST['seltable']) && in_array($_POST['seltable'],$aTableList)) # valid choice
{
$seltable = $_POST['seltable'];
$query = 'SHOW CREATE TABLE '.$seltable;
$tablecreateresult = mysql_query($query);
if ($tablecreateresult)
{
$row = mysql_fetch_assoc($tablecreateresult);
$tablecreate = $row['Create Table'];
}
}
}
}
// ---------------- build forms ------------------
if ($isAdmin)
{
// build datatabase selection form if more than one database to show
if (count($aDbList) > 1)
{
$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)
{
if (isset($seldb))
{
$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
{
$dbselmsg = '<p>'.sprintf(MSG_SINGLE_DB,$aDbList[0])."</p>\n";
}
// build table selection form
if (isset($seldb))
{
if (count($aTableList) > 0)
{
$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)
{
if (isset($seltable))
{
$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
{
$tableselmsg = '<p>'.sprintf(MSG_NO_TABLES,$seldb)."</p>\n";
}
}
// build results
if (isset($seldb))
{
$hdDbDdl = sprintf(HD_DB_CREATE_DDL,$seldb);
if (isset($dbcreate))
{
$dbresult = $this->Format('% %(sql)'.$dbcreate.'% %');
}
else
{
$dbresult = sprintf(MSG_NO_DB_DDL,$seldb);
}
if (isset($seltable))
{
$hdTableDdl = sprintf(HD_TABLE_CREATE_DDL,$seltable);
if (isset($tablecreate))
{
$tableresult = $this->Format('% %(sql)'.$tablecreate.'% %');
}
else
{
$tableresult = sprintf(MSG_NO_TABLE_DDL,$seltable);
}
}
}
// 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";
if (isset($dbselform))
{
echo $dbselform;
}
elseif (isset($dbselmsg))
{
echo $dbselmsg;
}
if (isset($seldb))
{
echo "<br />\n";
echo '<h5 id="'.$idDbDdl.'">'.$hdDbDdl.'</h5>'."\n";
echo $dbresult;
echo "<br />\n";
echo '<h4>'.$hdTables.'</h4>'."\n";
if (isset($tableselform))
{
echo $tableselform;
}
elseif (isset($tableselmsg))
{
echo $tableselmsg;
}
if (isset($seltable))
{
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.
//Now implemented as a [[WikkaBetaFeatures beta feature]] on this server.//
Documentation: DbInfoActionInfo.>>This is the development page for the DbInfo action.::c::
====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 Wikak, so your mileage may vary.
===The Code===
Save as **##actions/dbinfo.php##**:
%%(php;1)<?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
if (defined('HD_DBINFO'))
{
echo '{{dbinfo}}';
return;
}
// ----------------- constants and variables ------------------
// constants
// set defaults
$bAll = FALSE; # one column for columnar layout
$bPrefix = TRUE; # default display type
// UI strings
define('HD_DBINFO','Database Information');
define('HD_DBINFO_DB','Database');
define('HD_DBINFO_TABLES','Tables');
define('HD_DB_CREATE_DDL','DDL to create database %s:'); # %s will hold database name
define('HD_TABLE_CREATE_DDL','DDL to create table %s:'); # %s will hold table name
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,');
define('TXT_INFO_4',' including defaults that may not have been specified explicitly.');
define('FORM_SELDB_LEGEND','Databases');
define('FORM_SELTABLE_LEGEND','Tables');
define('FORM_SELDB_OPT_LABEL','Select a database:');
define('FORM_SELTABLE_OPT_LABEL','Select a table:');
define('FORM_SUBMIT_SELDB','Select');
define('FORM_SUBMIT_SELTABLE','Select');
define('MSG_ONLY_ADMIN','Sorry, only administrators can view database information');
define('MSG_SINGLE_DB',"Information for the '%s' database."); # %s will hold database name
define('MSG_NO_TABLES',"No tables found in the '%s' database."); # %s will hold database name
define('MSG_NO_DB_DDL','Creation DDL for %s could not be retrieved.'); # %s will hold database name
define('MSG_NO_TABLE_DDL','Creation DDL for %s could not be retrieved.');# %s will hold table name
$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)
{
if (is_array($vars))
{
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
$aDbList = array();
if ($bAll)
{
$query = 'SHOW DATABASES';
$tableresult = mysql_query($query);
if ($tableresult)
{
while ($row = mysql_fetch_assoc($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)
{
if (isset($_POST['dbselect']) || isset($_POST['tableselect'])) # form submitted
{
if (isset($_POST['seldb']) && in_array($_POST['seldb'],$aDbList)) # valid choice
{
$seldb = $_POST['seldb'];
}
else # ignore invalid choice
{
$seldb = $database;
}
}
}
else
{
$seldb = $database; # no choice: wikka database
}
if (isset($seldb))
{
$query = 'SHOW CREATE DATABASE '.$seldb;
$dbcreateresult = mysql_query($query);
if ($dbcreateresult)
{
$row = mysql_fetch_assoc($dbcreateresult);
$dbcreate = $row['Create Database'];
}
}
// table list
$aTableList = array();
if (isset($seldb))
{
$query = 'SHOW TABLES FROM '.$seldb;
if ($bPrefix)
{
$pattern = $prefix.'%';
$query .= " LIKE '".$pattern."'";
}
$tablelistresult = mysql_query($query);
if ($tablelistresult)
{
$colname = 'Tables_in_'.$seldb;
if ($bPrefix)
{
$colname .= ' ('.$pattern.')';
}
while ($row = mysql_fetch_assoc($tablelistresult))
{
$aTableList[] = $row[$colname];
}
}
}
// data for selected table
if (isset($_POST['tableselect'])) # form submitted
{
if (isset($_POST['seltable']) && in_array($_POST['seltable'],$aTableList)) # valid choice
{
$seltable = $_POST['seltable'];
$query = 'SHOW CREATE TABLE '.$seltable;
$tablecreateresult = mysql_query($query);
if ($tablecreateresult)
{
$row = mysql_fetch_assoc($tablecreateresult);
$tablecreate = $row['Create Table'];
}
}
}
}
// ---------------- build forms ------------------
if ($isAdmin)
{
// build datatabase selection form if more than one database to show
if (count($aDbList) > 1)
{
$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)
{
if (isset($seldb))
{
$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
{
$dbselmsg = '<p>'.sprintf(MSG_SINGLE_DB,$aDbList[0])."</p>\n";
}
// build table selection form
if (isset($seldb))
{
if (count($aTableList) > 0)
{
$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)
{
if (isset($seltable))
{
$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
{
$tableselmsg = '<p>'.sprintf(MSG_NO_TABLES,$seldb)."</p>\n";
}
}
// build results
if (isset($seldb))
{
$hdDbDdl = sprintf(HD_DB_CREATE_DDL,$seldb);
if (isset($dbcreate))
{
$dbresult = $this->Format('% %(sql)'.$dbcreate.'% %');
}
else
{
$dbresult = sprintf(MSG_NO_DB_DDL,$seldb);
}
if (isset($seltable))
{
$hdTableDdl = sprintf(HD_TABLE_CREATE_DDL,$seltable);
if (isset($tablecreate))
{
$tableresult = $this->Format('% %(sql)'.$tablecreate.'% %');
}
else
{
$tableresult = sprintf(MSG_NO_TABLE_DDL,$seltable);
}
}
}
// 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";
if (isset($dbselform))
{
echo $dbselform;
}
elseif (isset($dbselmsg))
{
echo $dbselmsg;
}
if (isset($seldb))
{
echo "<br />\n";
echo '<h5 id="'.$idDbDdl.'">'.$hdDbDdl.'</h5>'."\n";
echo $dbresult;
echo "<br />\n";
echo '<h4>'.$hdTables.'</h4>'."\n";
if (isset($tableselform))
{
echo $tableselform;
}
elseif (isset($tableselmsg))
{
echo $tableselmsg;
}
if (isset($seltable))
{
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.
Deletions:
Documentation: xxxxxActionInfo.>>This is the development page for the xxxxx action.::c::
//This page is a **template**. It belongs to CategoryTemplate (which contains more handy templates). To create an action development page, [[http://wikka.jsnx.com/ActionTemplate/clone clone this page]] to a page called **xxxxxAction** (where xxxxx is the (capitalized) name of the action), replace all occurrences of 'xxxxx' with the name of the action and replace this paragraph with the actual content.//
