SQL Manager

From FUDforum Wiki
Revision as of 12:12, 21 February 2011 by Naudefj (Talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search
SQL humor.jpg

The SQL Manager admin control panel allows administrators to execute SQL statements against the forum database. Enter the SQL statement and click "Run it" to execute the statement. To execute more than one statement, start each new statement on a new line and terminate them with semicolons (;).

Click on the "Insert table" drop-down to embed a table into your statement. One can also use {SQL_TABLE_PREFIX} in statements to indicate the database table prefix (as commonly used in FUDforum's SQL files).

Note that administrators can also run CREATE, DROP, INSERT, UPDATE and DELETE statements against the database (only for advanced users that know what they're doing). For security reasons, one cannot select data from other databases (schemas).

The SQL Manager was introduced with FUDforum release 2.8.1.

Example SQL to try

Here are some example SQL statements (safe to try) that can be entered to get you started:

Describe a table (show its structure). This command can be used on all databases that FUDforum supports (DB neutral implementation):

DESC {SQL_TABLE_PREFIX}users

Select the first 10 users from the USERS table:

SELECT * FROM {SQL_TABLE_PREFIX}users LIMIT 10;

List all tables in the database (MySQL only):

SHOW TABLES

List database connections with SQL statements currently executing (MySQL only):

SHOW PROCESSLIST

Show database settings (MySQL only):

SHOW VARIABLES

List the 10 largest database tables (MySQL only):

SELECT 
 concat(table_schema,'.',table_name) table_name,
 concat(round(table_rows/1000000,2),'M') rows,
 concat(round(data_length/(1024*1024*1024),2),'G') data_size,
 concat(round(index_length/(1024*1024*1024),2),'G') index_size,
 concat(round((data_length+index_length)/(1024*1024*1024),2),'G') total_size,
 round(index_length/data_length,2) idxfrac 
FROM information_schema.TABLES 
ORDER BY data_length+index_length DESC LIMIT 10;

Also see

  • Tables, a list of the forum's database tables.
Languages
Personal tools