API tools faq
paste
ZeroGravity200

zg SQL WorkBench

ZeroGravity200
Feb 16th, 2012
93
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 22.84 KB | None | 0 0
raw download clone embed print report
  1. <?php
  2.     /**
  3.      * zg SQL TestBench
  4.      *
  5.      * Free and open source software
  6.      * http://en.wikipedia.org/wiki/Free_and_open_source_software
  7.      *
  8.      * NOTE!
  9.      * DO NOT PUT THIS SCRIPT TO AN PUBLIC SERVER! YOUR DB WILL BE SERIOUSLY COMPROMISED, IF YOU DO SO!
  10.      * Use it in closed demo or development environment, where everyone can have anyway full access to DB.
  11.      *
  12.      * Do with it what you want. Absolutely no any(kind) warranties! Program is provided "as is"
  13.      * without warranty of any kind, either expressed or implied. The entire risk as to the
  14.      * quality and performance of the program is with you. In no event author will be liable to you
  15.      * for damages, including any general, special, incidental or consequential damages arising out
  16.      * of the use or inability to use the program (including but not limited to loss of data or data
  17.      * being rendered inaccurate or losses sustained by you or third parties or a failure of the
  18.      * program to operate with any other programs), even if the author has been advised of the
  19.      * possibility of such damages.
  20.      *
  21.      * By using this software you agree this licence.
  22.      *
  23.      * First Author: ZeroGravity        ([email protected])
  24.      * Release Date: 16.2.2012
  25.      * ============================================================
  26.      * DB Schema (created by program)
  27.      *
  28.      * product      tag         category        product_tag
  29.      * -----------      ----------      -----------     -----------
  30.      * id           id          id          product_id
  31.      * name         name            name            tag_id
  32.      * description      category_id
  33.      *
  34.      * Each product has random 0-N tags (each has same amount).
  35.      *  These tags can be same, might not be unique - set could be (3,22,25,25,57,58,79,79,79,99)
  36.      * Each tag has one and one category.
  37.      * Each tag can contain 0-N products.
  38.      */
  39.        
  40.     $mtime = explode(' ', microtime());
  41.     $mtime = $mtime[1] + $mtime[0];
  42.     $page_starttime = $mtime;
  43.        
  44.     /**
  45.      * Configuration - do your own configuration
  46.      */
  47.     $DB_ROOT_USER       = '<<ROOT>>';       // your root username -- needed for schema creation
  48.     $DB_ROOT_PASS       = '<<PASSWORD>>';   // your root password
  49.     $USE_ENGINE     = 'InnoDB';     // MyISAM, InnoDB, Blackhole, Memory ... you name it
  50.                             // all 4 tables use same engine
  51.     $PRODUCT_HAS_TAGS   = 10;           // how many tags each product has
  52.     $SHOW_MAX_ROWS      = 5;            // how many rows are shown in SQL Result Viewer
  53.     $INDEX_TYPE     = 'BTREE';      // BTREE or HASH (Memory and Heap engines)
  54.    
  55.     $PREDEFINED_SQLS    = array (
  56.         array('SELECT product_id FROM product_tag WHERE tag_id IN (22,25,30) GROUP BY product_id HAVING count(product_id)=3',0),
  57.         array('EXPLAIN SELECT product_id FROM product_tag WHERE tag_id IN (22,25,30) GROUP BY product_id HAVING count(product_id)=3',0),
  58.         array('SELECT p.name,pt.product_id FROM product p, product_tag pt WHERE pt.tag_id IN (22,25,30) AND pt.product_id = p.id GROUP BY pt.product_id HAVING count(pt.product_id)=3',0)
  59.     );
  60.  
  61.     // Test DB -- these are created on the fly ... does not need to exists
  62.     $TEST_DB_NAME       = 'dim_test';
  63.     $TEST_DB_USER       = 'dim_test_user';
  64.     $TEST_DB_PASSWD     = 'dim_test_pass';
  65.    
  66.     define('IGNORE_ERROR',1);
  67.     define('VERSION',1);
  68.     define('PRODUCT_NAME','zg SQL TestBench');
  69.  
  70.     // Execute sql in db - return query or null - show error realtime
  71.     function sqlExec($sql,$ignore=0) {
  72.         global $DB_CONN;
  73.    
  74.         if($DB_CONN==null) {
  75.             error('No connection to DB (schema missing, bad username/password?).');
  76.             return null;
  77.         }
  78.         try {
  79.             $query = $DB_CONN->prepare($sql);
  80.             $query->execute();
  81.             return $query;
  82.         } catch(PDOException $e) {
  83.             if(!$ignore) error($e);
  84.         }
  85.         return null;
  86.     }
  87.    
  88.     // dont ask... bad planning
  89.     function rootSqlExec($sql) {
  90.         global $ROOT_DB_CONN;
  91.    
  92.         if($ROOT_DB_CONN==null) {
  93.             error('No connection to DB (bad username/password?).');
  94.             return null;
  95.         }
  96.         try {
  97.             $query = $ROOT_DB_CONN->prepare($sql);
  98.             $query->execute();
  99.             return $query;
  100.         } catch(PDOException $e) {
  101.             error($e);
  102.         }
  103.         return null;
  104.     }
  105.  
  106.     // Do execute, and if succesfull prints out what was done
  107.     function sqlExecPrintOut($sql,$ignore=0) {
  108.         if(sqlExec($sql,$ignore)!=null) {
  109.             info('SQL ['.$sql.'] executed succesfully');
  110.         }
  111.     }
  112.    
  113.     function getCount($table) {
  114.         global $DB_CONN;
  115.  
  116.         if($DB_CONN==null) {
  117.             error('No connection to DB (schema missing, bad username/password?).');
  118.             return null;
  119.         }      
  120.         try {
  121.             $query = $DB_CONN->prepare('SELECT COUNT(*) FROM '.$table);
  122.             $query->execute();
  123.             if($row = $query->fetch(PDO::FETCH_NUM)) {
  124.                 return $row[0];
  125.             }
  126.         } catch(PDOException $e) {
  127.             // Table might not exists ... we just skip this
  128.         }
  129.         return -1;
  130.     }  
  131.    
  132.     function getMaxID($table) {
  133.         global $DB_CONN;
  134.  
  135.         if($DB_CONN==null) {
  136.             error('No connection to DB (schema missing, bad username/password?).');
  137.             return null;
  138.         }      
  139.         try {
  140.             $query = $DB_CONN->prepare('SELECT MAX(id) FROM '.$table);
  141.             $query->execute();
  142.             if($row = $query->fetch(PDO::FETCH_NUM)) {
  143.                 return $row[0];
  144.             }
  145.         } catch(PDOException $e) {
  146.             // Table might not exists ... we just skip this
  147.         }
  148.         return -1;
  149.     }
  150.  
  151.     function getRandomString($len,$words=0)  {
  152.         $code = md5(rand());
  153.         if($words==1) {
  154.             $code = preg_replace(array('/0/','/1/','/2/','/3/','/4/','/5/','/6/','/7/','/8/','/9/'),
  155.                     array(' ','h','i','j','k',' ','m','n','o','p'),
  156.                     $code);
  157.         } else {
  158.         $code = preg_replace(array('/0/','/1/','/2/','/3/','/4/','/5/','/6/','/7/','/8/','/9/'),
  159.                 array('g','h','i','j','k','l','m','n','o','p'),
  160.                 $code);
  161.         }
  162.         if(strlen($code)<$len) $code .= getRandomString($len-strlen($code));
  163.         return substr($code, 0, $len);
  164.     }
  165.    
  166.     function title($title,$fontcolor,$bgcolor,$nobr=0,$showhide=null) {
  167.         if($nobr!=1) echo '<br>';
  168.         echo '<div style="background: '.$bgcolor.'; font: 12px verdana; font-weight:900; font-variant: small-caps; padding: 3px; color: '.$fontcolor.'">'.$title;
  169.         if($showhide!=null) {
  170.             echo '<a href="javascript:toggleme('.$showhide.');" id="showhide'.$showhide.'" class="xbutton">-</a>';
  171.         }
  172.         echo '</div>';
  173.     }
  174.    
  175.     function box($color,$font_size='.75em',$id=null) {
  176.         echo '<div '.
  177.         ($id!=null?' id="'.$id.'" ':'')    
  178.         .'style="border: 2px '.$color.' solid; font: '.$font_size.' verdana; padding: 2px;">';
  179.     }
  180.    
  181.     function error($error) {
  182.         title('ERROR','white','red',1);
  183.         box('red');
  184.         echo $error.'</div>';
  185.     }
  186.    
  187.     function info($info) {
  188.         box('#333333','.65em');
  189.         echo $info.'</div>';
  190.     }
  191.    
  192.     function getIndexer() {
  193.         $s = '<form action="?do_index=1" method="post" style="padding:0;margin:0;"><div style="background: blue; font: 10px verdana; font-weight:900; font-variant: small-caps; padding: 3px; color: white">Indexer</div>
  194.         <div style="border: 2px blue solid; font: .8 verdana; padding: 2px;">
  195.         <div class="db_odd"><input type="checkbox" name="idx[]" value="product-id" '.(indexExists('product','id')?'checked':'').'/><input type="checkbox" name="idx[]" value="product-name" '.(indexExists('product','name')?'checked':'').' /><input type="checkbox" name="idx[]" value="product-description" '.(indexExists('product','description')?'checked':'').'/></div>
  196.         <div class="db_even"><input type="checkbox" name="idx[]" value="tag-id" '.(indexExists('tag','id')?'checked':'').'/><input type="checkbox" name="idx[]" value="tag-name" '.(indexExists('tag','name')?'checked':'').' /><input type="checkbox" name="idx[]" value="tag-category_id" '.(indexExists('tag','category_id')?'checked':'').'/></div>
  197.         <div class="db_odd"><input type="checkbox" name="idx[]" value="product_tag-product_id" '.(indexExists('product_tag','product_id')?'checked':'').' /><input type="checkbox" name="idx[]" value="product_tag-tag_id" '.(indexExists('product_tag','tag_id')?'checked':'').' /></div>
  198.         <div class="db_even"><input type="checkbox" name="idx[]" value="category-id" '.(indexExists('category','id')?'checked':'').'/><input type="checkbox" name="idx[]" value="category-name" '.(indexExists('category','name')?'checked':'').'/></div>
  199.         <div><input type="submit" value="Create" class="button1" /></div>
  200.         </div></form>';
  201.         return $s;
  202.     }
  203.    
  204.     function indexExists($table,$column) {
  205.         global $DB_NAME;
  206.        
  207.         $result = rootSqlExec('SHOW INDEX FROM '.$table.' WHERE column_name=\''.$column.'\'');
  208.         // info($result);
  209.         if($result!=null && $result->rowCount()>0) return true;
  210.         return false;
  211.     }
  212.    
  213.     function getTableFields($table) {
  214.         global $DB_CONN;
  215.    
  216.         if($DB_CONN==null) {
  217.             error('No connection to DB (schema missing, bad username/password?).');
  218.             return null;
  219.         }
  220.         try {      
  221.             $query = $DB_CONN->prepare("DESCRIBE ".$table);
  222.             $query->execute();
  223.             $table_fields = $query->fetchAll(PDO::FETCH_COLUMN);
  224.         } catch(PDOException $e) {
  225.             error($e);
  226.         }
  227.        
  228.         $str = '';
  229.         $s = '';
  230.         foreach($table_fields as $t) {
  231.             $str .= $s.$t;
  232.             $s = ', ';
  233.         }
  234.         return $str;
  235.     }
  236.        
  237.     /*****************
  238.      * END OF HELPER FUNCTIONS
  239.      *
  240.      * Beef starts here...
  241.      */
  242.    
  243.     $show_db    = 1;
  244.     $create_db  = 0;
  245.     $show_query = '';
  246.    
  247.     @session_start();
  248.     if(!isset($_SESSION['saved_sqls'])) $_SESSION['saved_sqls'] = $PREDEFINED_SQLS;
  249.    
  250.     // one script ugly css definition
  251.     echo '<!DOCTYPE html>
  252. <html><head>
  253. <style type="text/css">
  254. ul.menu {
  255.     list-style-type:none;
  256.     margin:0;
  257.     padding:0;
  258.     overflow:hidden;
  259. }
  260. li.menu {
  261.     float:left;
  262.     padding: 0 2px 0 0;
  263. }
  264. a.menu {
  265.     border: 1px black solid;
  266.     display:block;
  267.     width: 160px;
  268.     color: white;
  269.     background-color:#333333;
  270.     padding: 2px;
  271.     font: 11px verdana;
  272.     text-align: center;
  273.     font-weight: 900;
  274.     font-variant: small-caps;
  275.     text-decoration: none; 
  276. }
  277. a.save {
  278.     border: 1px white solid;
  279.     font: 11px verdana;
  280.     background-color: red;
  281.     color: white;
  282.     text-decoration: none;
  283. }
  284. .smaller {
  285.     font: 11px verdana;
  286. }
  287. a.xbutton {
  288.     border: 1px black solid;
  289.     float:right;
  290.     width: 10px;
  291.     color: black;
  292.     background: white;
  293.     font: 10px verdana;
  294.     text-align:center;
  295.     text-decoration: none;
  296. }
  297. tr.odd {
  298.     background: #888888;
  299.     color: white;
  300.     font: .9em verdana;
  301. }
  302. tr.even {
  303.     background: #444444;
  304.     color: white;
  305.     font: .9em verdana;
  306. }
  307. tr.db_odd, div.db_odd {
  308.     background: #008888;
  309.     color: white;
  310.     font: 1em verdana;
  311. }
  312. tr.db_even, div.db_even {
  313.     background: #004444;
  314.     color: white;
  315.     font: 1em verdana; 
  316. }
  317. div.db_even, div.db_odd {
  318.     padding: 1px 0 1px 0;
  319. }
  320. td.indexify {
  321.     font: 12px verdana;
  322.     font-weight:900;
  323.     font-variant: small-caps;
  324.     padding: 2px;
  325. }
  326. tr.dbresponse {
  327.     background: black;
  328.     color: white;
  329.     font: .9em verdana;
  330. }
  331. h1 {
  332.     padding:0;
  333.     margin:0;
  334.     background: gray;
  335.     text-align: center;
  336.     border: 2px gray solid;
  337.     font: 1.5em Arial Black;
  338.     color: white;
  339.     font-weight: 900;
  340. }
  341. body {
  342.     margin-top:2px;
  343. }
  344. div.showquery {
  345.     font: 1em courier new;
  346. }
  347. ul.smallinfo {
  348.     background: gray;
  349.     list-style-type:none;
  350.     margin:0;
  351.     padding:0;
  352.     overflow:hidden;
  353. }
  354. li.smallinfo {
  355.     background: gray;
  356.     float: right;
  357.     padding: 0 5px 0 5px;
  358.     font: .7em verdana;
  359.     color: white;
  360.     border-style:solid;
  361.     border: 0;
  362.     border-left: 1px solid white;
  363. }
  364. .button1 {
  365.     width: 100%;
  366.    border: 1px dotted #000;
  367.    background: #222;
  368.     color: white;
  369.     font: .85em verdana;
  370.     font-weight: 700;
  371. }
  372. </style></head><body>';
  373.    
  374.     echo '<h1>[ '.PRODUCT_NAME.' ]</h1>
  375.     <ul class="smallinfo">
  376.     <li class="smallinfo">Version: '.VERSION.'</li>
  377.     <li class="smallinfo">Author: ZeroGravity</li>
  378.     <li class="smallinfo">Product has tags: '.$PRODUCT_HAS_TAGS.'</li>
  379.     <li class="smallinfo">Schema used: '.strtoupper($TEST_DB_NAME).'</li>
  380.     </ul>';
  381.    
  382.     if($DB_ROOT_USER=='<<ROOT>>' || $DB_ROOT_PASS=='<<PASSWORD>>') {
  383.         error('You have not configured ['.PRODUCT_NAME.']. <b>Do that on rows 44-53 (approx)</b>. Exiting!');
  384.         exit();
  385.     }
  386.  
  387.     /**
  388.      * Show menu
  389.      */
  390.     // title('Menu','white','gray',1);
  391.     box('gray');
  392.     echo '<ul class="menu"><li class="menu"><a class="menu" href="?drop_create=1" title="Drop schema and user - and create it again">Recreate schema</a></li>
  393.           <li class="menu"><a class="menu" href="?clear=1" title="Actually drops all tables, and creates them again">Clear tables</a></li>
  394.           <li class="menu"><a class="menu" href="?more=1" title="Adds 1000 products to the product table and '.($PRODUCT_HAS_TAGS*1000).' connections to products_tag table">Add 1000 products</a></li>
  395.           <li class="menu"><a class="menu" href="?drop=1" title="Removes test schema and user">Drop schema and user</a></li></ul></div>';    
  396.    
  397.     /**
  398.      * Drop Schema
  399.      */
  400.     if(isset($_GET['drop'])) {
  401.         try {
  402.             $DB_CONN = new PDO("mysql:host=localhost", $DB_ROOT_USER, $DB_ROOT_PASS);
  403.             $DB_CONN->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  404.         } catch (PDOException $e) {
  405.             error($e);
  406.         }
  407.  
  408.         sqlExecPrintOut('DROP DATABASE IF EXISTS '.$TEST_DB_NAME);
  409.         sqlExecPrintOut('DROP USER \''.$TEST_DB_USER.'\'@\'localhost\'');
  410.         // cannot show db anymore
  411.         $show_db = 0;
  412.     }
  413.    
  414.     /**
  415.      * Drop and create schema and user
  416.      */
  417.     if(isset($_GET['drop_create'])) {
  418.         try {
  419.             $DB_CONN = new PDO("mysql:host=localhost", $DB_ROOT_USER, $DB_ROOT_PASS);
  420.             $DB_CONN->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  421.         } catch (PDOException $e) {
  422.             error($e);
  423.         }
  424.        
  425.         sqlExecPrintOut('DROP DATABASE IF EXISTS '.$TEST_DB_NAME);
  426.         sqlExecPrintOut('CREATE DATABASE '.$TEST_DB_NAME);
  427.         sqlExecPrintOut('DROP USER \''.$TEST_DB_USER.'\'@\'localhost\'',IGNORE_ERROR);
  428.         sqlExecPrintOut('CREATE USER \''.$TEST_DB_USER.'\'@\'localhost\' IDENTIFIED BY \''.$TEST_DB_PASSWD.'\'');
  429.         sqlExecPrintOut('GRANT USAGE ON *.* TO \''.$TEST_DB_USER.'\'@\'localhost\' IDENTIFIED BY \''.$TEST_DB_PASSWD.'\' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0');
  430.         sqlExecPrintOut('GRANT  ALL PRIVILEGES ON '.$TEST_DB_NAME.'.* TO \''.$TEST_DB_USER.'\'@\'localhost\'');
  431.         $create_db = 1;
  432.     }
  433.    
  434.     /**
  435.      * Create connection to our test schema
  436.      */
  437.     try {
  438.         // sadly due later "add-ons" we need also root access to schema ... bad planning, bad code
  439.         $ROOT_DB_CONN = new PDO('mysql:host=localhost;dbname='.$TEST_DB_NAME, $DB_ROOT_USER, $DB_ROOT_PASS);
  440.         $DB_CONN = new PDO('mysql:host=localhost;dbname='.$TEST_DB_NAME, $TEST_DB_USER, $TEST_DB_PASSWD);
  441.         $ROOT_DB_CONN->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  442.         $DB_CONN->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  443.     } catch (PDOException $e) {
  444.         // ignore and do not show db
  445.         $show_db = 0;
  446.     }
  447.  
  448.     /**
  449.      * Clear schema ... recreate tables
  450.      * Fill dimensions
  451.      */
  452.     if((isset($_GET['clear']) && $show_db==1) || $create_db==1) {
  453.         sqlExecPrintOut('DROP TABLE IF EXISTS product');
  454.         sqlExecPrintOut('CREATE TABLE product (
  455.                     id INT,
  456.                     name VARCHAR(20),
  457.                     description VARCHAR(500)
  458.                 ) ENGINE = '.$USE_ENGINE.';');
  459.                
  460.         sqlExecPrintOut('DROP TABLE IF EXISTS tag');
  461.         sqlExecPrintOut('CREATE TABLE tag (
  462.                     id INT,
  463.                     name VARCHAR(20),
  464.                     category_id INT
  465.                 ) ENGINE = '.$USE_ENGINE.';');
  466.  
  467.         sqlExecPrintOut('DROP TABLE IF EXISTS category');
  468.         sqlExecPrintOut('CREATE TABLE category (
  469.                     id INT,
  470.                     name VARCHAR(20)
  471.                 ) ENGINE = '.$USE_ENGINE.';');
  472.  
  473.         sqlExecPrintOut('DROP TABLE IF EXISTS product_tag');
  474.         sqlExecPrintOut('CREATE TABLE product_tag (
  475.                     product_id INT,
  476.                     tag_id INT
  477.                 ) ENGINE = '.$USE_ENGINE.';');
  478.                
  479.         sqlExecPrintOut('CREATE INDEX product_tag_tag_id_idx ON product_tag(tag_id) USING '.$INDEX_TYPE);
  480.         sqlExecPrintOut('CREATE INDEX product_tag_product_id_idx ON product_tag(product_id) USING '.$INDEX_TYPE);
  481.  
  482.         // add test dimensions
  483.         $sql = array();
  484.         for($i=1;$i<21;$i++) {
  485.             $sql[] = '('.$i.',\''.getRandomString(20).'\')';
  486.         }
  487.         sqlExec('INSERT INTO category (id,name) VALUES '.implode(',', $sql),0);
  488.         info('20 categories inserted');
  489.        
  490.         $sql = array();
  491.         for($i=1;$i<101;$i++) {
  492.             $sql[] = '('.$i.',\''.getRandomString(20).'\','.(($i % 5)+1).')';
  493.         }
  494.         sqlExec('INSERT INTO tag (id,name,category_id) VALUES '.implode(',', $sql),0); 
  495.         info('100 tags inserted');
  496.        
  497.         $show_db = 1;
  498.     }
  499.    
  500.     /**
  501.      * Save SQL for later use or just to remember execution time
  502.      * and redo, and removal functionality
  503.      */
  504.     if(isset($_GET['save_sql'])) {
  505.         $sql = array ($_SESSION['prev_sql'],$_SESSION['prev_exec_time']);
  506.         $_SESSION['saved_sqls'][] = $sql;
  507.         $show_query = $_SESSION['prev_sql'];
  508.     }
  509.    
  510.     if(isset($_GET['redo_sql'])) {
  511.         $s = $_SESSION['saved_sqls'][$_GET['redo_sql']];
  512.         $_POST['gogo'] = 1;
  513.         $_POST['query'] = $s[0];
  514.     }
  515.    
  516.     if(isset($_GET['remove_all_sql'])) {
  517.         unset($_SESSION['saved_sqls']);
  518.         $_SESSION['saved_sqls'] = $PREDEFINED_SQLS;
  519.     }
  520.    
  521.     if(isset($_GET['remove_sql'])) {
  522.         unset($_SESSION['saved_sqls'][$_GET['remove_sql']]);
  523.     }
  524.    
  525.     /**
  526.      * User has requested SQL query ... execute it ... no restrictions
  527.      */
  528.     if(isset($_POST['gogo']) && strlen($_POST['query'])>0) {
  529.         title('Executed SQL Query','white','green',0,'5');
  530.         box('green','.75em','toggle5');
  531.         echo '<div class="showquery">'.$_POST['query'].'</div></div>';
  532.        
  533.         $mtime = explode(' ', microtime());
  534.         $mtime = $mtime[1] + $mtime[0];
  535.         $starttime = $mtime;
  536.         $query = sqlExec($_POST['query'],0);
  537.  
  538.         $mtime = explode(' ', microtime());
  539.         $mtime = $mtime[1] + $mtime[0];
  540.         $totaltime = ($mtime - $starttime);
  541.        
  542.         title ('SQL took ' .$totaltime. ' seconds <a class="save" href="?save_sql=1">Save</a>','white','green',1);
  543.  
  544.         $max = $SHOW_MAX_ROWS;
  545.         title('Response: first ['.$max.'] rows' ,'white','black',0,'4');
  546.         box('black','.75em','toggle4');
  547.         if($query!=null) {
  548.             try {
  549.                 echo '<table><tr class="dbresponse">';
  550.                 foreach(range(0, $query->columnCount() - 1) as $column_index) {
  551.                     $meta = $query->getColumnMeta($column_index);
  552.                     echo '<td>&nbsp;'.$meta['name'].'&nbsp;</td>';
  553.                 }
  554.                 echo '</tr>';
  555.  
  556.                 $class = 'odd';
  557.                 while($row = $query->fetch(PDO::FETCH_NUM)) {
  558.                     echo '<tr class="'.$class.'">';
  559.                     foreach($row as $c) {
  560.                         if(is_string($c) && !isset($_POST['no_cut'])) if(strlen($c)>30) $c = substr($c,0,30).'...';
  561.                         echo '<td>'.$c.'</td>';
  562.                     }
  563.                     if($class=='odd')
  564.                         $class='even';
  565.                     else
  566.                         $class='odd';
  567.                     if(--$max<1) break;
  568.                     echo '</tr>';
  569.                 }
  570.             } catch(PDOException $e) {
  571.                 error($e);
  572.             }
  573.         }
  574.        
  575.         echo '</table></div>';
  576.         if($query!=null) title('Total '.$query->rowCount().' rows returned.','white','black',1);
  577.  
  578.         $show_query = $_POST['query'];
  579.         $_SESSION['prev_sql'] = $show_query;
  580.         $_SESSION['prev_exec_time'] = $totaltime;
  581.     }
  582.    
  583.     /**
  584.      * Create more products and create connection between tag and product
  585.      * (how many depends on $PRODUCT_HAS_TAGS)
  586.      */
  587.     if(isset($_GET['more'])) { 
  588.         $next_id = getMaxID('product')+1;
  589.         $sql = array();
  590.         for($i=$next_id;$i<$next_id+1000;$i++) {
  591.             $sql[] = '('.$i.',\''.getRandomString(20).'\',\''.getRandomString(500,1).'\')';
  592.         }
  593.         sqlExec('INSERT INTO product (id,name,description) VALUES '.implode(',', $sql),0); 
  594.         info('1000 products inserted');
  595.        
  596.         $tag_max_id = getMaxID('tag');
  597.         $sql = array();
  598.         for($i=$next_id;$i<$next_id+1000;$i++) {
  599.             for($j=0;$j<$PRODUCT_HAS_TAGS;$j++) {
  600.                 $sql[] = '('.$i.','.rand(1,$tag_max_id).')';
  601.             }
  602.         }
  603.         sqlExec('INSERT INTO product_tag (product_id,tag_id) VALUES '.implode(',', $sql),0);   
  604.         info('Relation between product and tags inserted.');       
  605.     }
  606.    
  607.     /**
  608.      * Manipulate indexes
  609.      */
  610.     if(isset($_REQUEST['do_index'])) {
  611.         $tables = array('product','tag','product_tag','category');
  612.        
  613.         // we go thru all cols, as user might have remove the idx
  614.         foreach($tables as $table) {
  615.             $colstr = getTableFields($table);
  616.             $cols = preg_split('/,/',$colstr);
  617.             foreach($cols as $col) {
  618.                 $col            = trim($col);
  619.                 $has_index      = indexExists($table,$col);
  620.                 if(isset($_POST['idx'])) {
  621.                     $is_in_post     = in_array($table.'-'.$col,$_POST['idx']);
  622.                 } else {
  623.                     $is_in_post = false;
  624.                 }
  625.                 // drop if exists and is not sent by user
  626.                 if($has_index && !$is_in_post) {
  627.                     sqlExecPrintOut('DROP INDEX '.$table.'_'.$col.'_idx ON '.$table);
  628.                 } else {
  629.                     // create if does not exist and is not sent by user
  630.                     if($is_in_post && !$has_index) {
  631.                         sqlExecPrintOut('CREATE INDEX '.$table.'_'.$col.'_idx ON '.$table.'('.$col.') USING BTREE');
  632.                     }
  633.                 }
  634.             }
  635.         }
  636.     }
  637.    
  638.     /**
  639.      * Show some basic info of schema (counts)
  640.      */
  641.     if($show_db==1) {
  642.         title('DB Contents [table,count,fields]','white','blue',0,'3');
  643.         box('blue','.75em','toggle3');
  644.         echo '<table>
  645.               <tr><td colspan="3" class="indexify">&nbsp;</td><td rowspan="6" style="vertical-align: top;">'.getIndexer().'</td></tr>
  646.               <tr class="db_odd"><td class="db_show">product:</td><td>'.getCount('product').'</td><td>['.getTableFields('product').']</td></tr>'.
  647.              '<tr class="db_even"><td class="db_show">tag:</td><td>'.getCount('tag').'</td><td>['.getTableFields('tag').']</td></tr>'.
  648.              '<tr class="db_odd"><td class="db_show">product_tag:</td><td>'.getCount('product_tag').'</td><td>['.getTableFields('product_tag').']</td></tr>'.
  649.              '<tr class="db_even"><td class="db_show">category:</td><td>'.getCount('category').'</td><td>['.getTableFields('category').']</td></tr>
  650.              <tr><td colspan="3">&nbsp;</td></tr>'.
  651.              '</table></div>';
  652.     } else {
  653.         title('DB Contents','white','blue',0,'3');
  654.         box('blue','.75em','toggle3');
  655.         echo 'NO SCHEMA</div>';
  656.     }
  657.    
  658.  
  659.     /**
  660.      * Show box where user can insert SQL and execute it
  661.      */
  662.     title('Send SQL Query','white','green',0,'2');   
  663.     box('green','.75em','toggle2');
  664.     echo '<form method="post" action="?"><textarea rows="10" cols="80" name="query">'.
  665.          $show_query.
  666.          '</textarea><br /><input type="submit" value="Send SQL Query" name="gogo" />&nbsp;<input type="checkbox" name="no_cut" value="1" /> do not cut strings to 30 chars</form>
  667.          </div>';
  668.  
  669.     /**
  670.      * Show SQL redo log
  671.      */
  672.     title('Saved SQLs [<a href="?remove_all_sql=1" title="Remove all and bring back defaults">reset</a>]','white','#666666',0,'1');
  673.     box('#666666','.75em','toggle1');
  674.     if(isset($_SESSION['saved_sqls'])) {
  675.         for($i=0;$i<sizeof($_SESSION['saved_sqls'])+1;$i++) {
  676.             if(!isset($_SESSION['saved_sqls'][$i])) continue;
  677.             $s = $_SESSION['saved_sqls'][$i];
  678.             echo '<span class="smaller">['.round($s[1],8).'] <a href="?redo_sql='.$i.'">#redo#</a> <a href="?remove_sql='.$i.'">#remove#</a> '.$s[0].'</span><br />';
  679.         }
  680.     }
  681.     echo '</div>';
  682.    
  683.     $mtime = explode(' ', microtime());
  684.     $mtime = $mtime[1] + $mtime[0];
  685.     $page_totaltime = ($mtime - $page_starttime);
  686.        
  687.     title('Page served in ' .$page_totaltime. ' seconds','white','black',0);
  688.        
  689.     // the end :P
  690.     echo '
  691.     <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
  692.     <script>
  693.         $(document).ready(function(){
  694.             $("#showhide1").data(\'is_visible\',true);
  695.             $("#showhide2").data(\'is_visible\',true);
  696.             $("#showhide3").data(\'is_visible\',true);
  697.             $("#showhide4").data(\'is_visible\',true);
  698.             $("#showhide5").data(\'is_visible\',true);
  699.         });
  700.        
  701.         function toggleme(targetId) {
  702.             $("#toggle" + targetId).slideToggle();
  703.             if($("#showhide" + targetId)) {
  704.                 $("#showhide" + targetId).data(\'is_visible\', !$("#showhide" + targetId).data(\'is_visible\'));
  705.                 $("#showhide" + targetId).html( (!$("#showhide" + targetId).data(\'is_visible\')) ? "+" : "-");
  706.             } else {
  707.                 $("#showhide" + targetId).data(\'is_visible\',false);
  708.             }
  709.         }
  710.  
  711.     </script>
  712.    
  713.     </body></html>';
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!