Posted By

jonniespratley on 02/26/09


Tagged

sql php json Flex


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

umang_nine


MySQL JSON Schema Tree


 / Published in: PHP
 

  1. <?php
  2. /**
  3.  * I hold mysql methods
  4.  *
  5.  * @name MySQLService
  6.  * @author Jonnie Spratley
  7.  * @version 1.0
  8.  * @license http://opensource.org/licenses/gpl-license.php GNU Public License
  9.  *
  10.  */
  11. class MySQLConnect
  12. {
  13. public $mysqli;
  14.  
  15. /**
  16. * I hold methods to alter a mysql database
  17. *
  18. * @param [string] $host
  19. * @param [string] $username
  20. * @param [string] $password
  21. */
  22. public function __construct( $host, $username, $password )
  23. {
  24. $link = new mysqli ( $host, $username, $password );
  25.  
  26. /* check connection */
  27. if ( mysqli_connect_errno () )
  28. {
  29. trigger_error ( 'Database connection failure: Username/Password was incorrect.', E_USER_ERROR );
  30. exit ();
  31. }
  32. else
  33. {
  34. $this->setMysqli ( $link );
  35. }
  36. }
  37.  
  38. /**
  39. * I execute a query
  40. *
  41. * @param [string] $sql
  42. * @return [array]
  43. */
  44. public function execute( $sql )
  45. {
  46. return $this->queryToARRAY ( $sql );
  47. }
  48.  
  49. /**
  50. * I get the databases
  51. *
  52. * @return [array]
  53. */
  54. public function getDatabases()
  55. {
  56. return $this->queryToARRAY ( "SHOW DATABASES" );
  57. }
  58.  
  59. /**
  60. * I execute a raw query
  61. *
  62. * @param [string] $query
  63. * @return [link]
  64. */
  65. public function realQuery( $query )
  66. {
  67. return $this->mysqli->query ( $query );
  68. }
  69.  
  70. /**
  71. * I start the tree
  72. *
  73. * @return [array]
  74. */
  75. public function tree_getTree()
  76. {
  77. $mysqlVersion = mysqli_get_client_info ( $this->mysqli );
  78. $host = $_SERVER [ 'HTTP_HOST' ] . " (MySQL v. $mysqlVersion )";
  79.  
  80. $hostArray = array (
  81. 'label' => $host, 'type' => 'server', 'children' => $this->tree_getSchemas ()
  82. );
  83. $treeArray [] = array (
  84. 'label' => 'SQL Databases', 'type' => 'servers', 'children' => $hostArray
  85. );
  86.  
  87. return json_encode ( $treeArray );
  88. }
  89.  
  90. /**
  91. * I build the tree
  92. *
  93. * @return [array]
  94. */
  95. private function tree_getSchemas()
  96. {
  97. //Database query
  98. $databaseSQL = $this->realQuery ( "SHOW DATABASES" );
  99.  
  100. //New database array
  101. $databases = array ();
  102.  
  103. //Loop the query
  104. while ( $database = mysqli_fetch_assoc ( $databaseSQL ) )
  105. {
  106. //Create a new array of tables for each database
  107. $tables = array ();
  108. $status = array ();
  109. $size = array ();
  110.  
  111. foreach ( $database as $key => $value )
  112. {
  113. //Set the table array to get the tbles from the database
  114. $tables = $this->tree_db_getTables ( $value );
  115. $status = $this->_db_getStatus ( $value );
  116. $size = $this->_db_getSize ( $value );
  117. }
  118.  
  119. //Add the tables to the database array
  120. $databases [] = array (
  121. "aDatabase" => $value, "aData" => $key, "aType" => "database", "aIcon" => "database", "aStatus" => $status, "aSize" => $size, "aTables" => $tables
  122. );
  123. }
  124.  
  125. $databaseFolder [] = array (
  126. 'label' => 'Schemas', 'children' => $databases
  127. );
  128.  
  129. return $databaseFolder;
  130. }
  131.  
  132. /**
  133. * I get the users auth
  134. *
  135. * @return [array]
  136. */
  137. private function tree_db_getAuthorizations()
  138. {
  139. $authorizationsArray = array (
  140. 'label' => 'Authorization IDs', 'children' => array (
  141. 'label' => 'rfd'
  142. )
  143. );
  144.  
  145. return $authorizationsArray;
  146. }
  147.  
  148. //TODO:
  149. private function tree_db_getDependcenies( $database )
  150. {
  151. $dependceniesArray = array (
  152. 'label' => 'Dependcencies', 'children' => array (
  153. 'label' => 'test'
  154. )
  155. );
  156.  
  157. return $dependceniesArray;
  158. }
  159.  
  160. //TODO:
  161. private function tree_db_getStoredProcs( $database )
  162. {
  163. $storedProcsArray = array (
  164. 'label' => 'Stored Procedures', 'children' => array (
  165. 'label' => 'test'
  166. )
  167. );
  168.  
  169. return $storedProcsArray;
  170. }
  171.  
  172. /**
  173. * I get the tables
  174. *
  175. * @param [string] $database the database
  176. * @return [array]
  177. */
  178. private function tree_db_getTables( $database )
  179. {
  180. //table query
  181. $tableSQL = $this->realQuery ( "SHOW TABLES FROM $database" );
  182.  
  183. //create a new array of tables
  184. $tables = array ();
  185.  
  186. //loop all the results
  187. while ( $table = mysqli_fetch_assoc ( $tableSQL ) )
  188. {
  189. $columns = array ();
  190. $statuss = array ();
  191. $indexes = array ();
  192. $constraints = array ();
  193. $dependicies = array ();
  194. $triggers = array ();
  195.  
  196. //for each table in the result make an array
  197. foreach ( $table as $t_key => $t_value )
  198. {
  199. //get the tables fields for each table
  200. $columns = $this->tree_tbl_getColumns ( $database, $t_value );
  201.  
  202. //now get the primary key for each table
  203. $constraints = $this->tree_tbl_getConstraints ( $database, $t_value );
  204.  
  205. //now get the indexes for each table
  206. $indexes = $this->tree_tbl_getIndexes ( $database, $t_value );
  207.  
  208. //now get the dependencys for each table
  209. $dependicies = $this->tree_tbl_getDependcenies ( $database, $t_value );
  210.  
  211. //now get the triggers for each table
  212. $triggers = $this->tree_tbl_getTriggers ( $database, $t_value );
  213.  
  214. //now get the status for each table
  215. $statuss = $this->_tbl_getStatus ( $database, $t_value );
  216.  
  217. }
  218.  
  219. $columnArr = $columns;
  220. $constraintArr = $constraints;
  221. $indexArr = $indexes;
  222. $dependencyArr = $dependicies;
  223. $triggerArr = $triggers;
  224. $statusArr = $statuss;
  225. $tables [] = array (
  226. "label" => $t_value, "type" => "table", "icon" => "table", "children" => array (
  227. $columnArr, $constraintArr, $indexArr, $dependencyArr, $triggerArr, $statusArr
  228. )
  229. );
  230. }
  231.  
  232. $tableFolder [] = array (
  233. 'label' => 'Tables', 'children' => $tables
  234. );
  235.  
  236. return $tableFolder;
  237. }
  238.  
  239. //TODO:
  240. private function tree_db_getUserFunctions( $database )
  241. {
  242.  
  243. }
  244.  
  245. //TODO:
  246. private function tree_db_getViews( $database )
  247. {
  248.  
  249. }
  250.  
  251. /**
  252. * I get the columns
  253. *
  254. * @param [string] $database
  255. * @param [string] $table
  256. * @return [array]
  257. */
  258. private function tree_tbl_getColumns( $database, $table )
  259. {
  260. $sql = "SHOW FIELDS FROM $database.$table";
  261. $query = $this->realQuery ( $sql );
  262.  
  263. $columnsArray = array ();
  264.  
  265. while ( $row = mysqli_fetch_row ( $query ) )
  266. {
  267. $type = strtoupper ( $row [ 1 ] );
  268. $null = '';
  269.  
  270. //Check if the column can be null
  271. if ( $row [ 2 ] == 'YES' )
  272. {
  273. $null = 'Nullable';
  274. }
  275. $type = '[' . $type . ' ' . $null . ']';
  276.  
  277. $columnsArray [] = array (
  278. 'label' => $row [ 0 ] . ' ' . $type
  279. );
  280. }
  281. //Create the folder
  282. $columnsFolder = array (
  283. 'label' => 'Columns', 'children' => $columnsArray
  284. );
  285.  
  286. return $columnsFolder;
  287. }
  288.  
  289. /**
  290. * I get the primary keys
  291. *
  292. * @param [string] $database
  293. * @param [string] $table
  294. * @return [array]
  295. */
  296. private function tree_tbl_getConstraints( $database, $table )
  297. {
  298. $sql = "SHOW INDEX FROM $database.$table";
  299. $result = $this->realQuery ( $sql );
  300. $constraintArray = array ();
  301.  
  302. while ( $constraint = mysqli_fetch_assoc ( $result ) )
  303. {
  304. //check if the key is the primary key
  305. if ( $constraint [ 'Key_name' ] == 'PRIMARY' )
  306. {
  307. $constraintArray = array (
  308. 'label' => $constraint [ 'Key_name' ]
  309. );
  310. }
  311. }
  312. $constraintFolder = array (
  313. 'label' => 'Constraints', 'children' => array (
  314. $constraintArray
  315. )
  316. );
  317.  
  318. return $constraintFolder;
  319. }
  320.  
  321. //TODO:
  322. /**
  323. * I get the dependcencies
  324. *
  325. * @param [string] $database
  326. * @param [string] $table
  327. * @return [array]
  328. */
  329. private function tree_tbl_getDependcenies( $database, $table )
  330. {
  331. $dependArray = array (
  332. 'label' => 'admin table'
  333. );
  334.  
  335. $dependFolder = array (
  336. 'label' => 'Dependencies', 'children' => array (
  337. $dependArray
  338. )
  339. );
  340.  
  341. return $dependFolder;
  342. }
  343.  
  344. /**
  345. * I get the indexes
  346. *
  347. * @param [string] $database
  348. * @param [string] $table
  349. * @return [array]
  350. */
  351. private function tree_tbl_getIndexes( $database, $table )
  352. {
  353. $sql = "SHOW INDEX FROM $database.$table";
  354. $query = mysqli_query ( $this->mysqli, $sql );
  355.  
  356. $indexArray = array ();
  357.  
  358. while ( $row = mysqli_fetch_row ( $query ) )
  359. {
  360. if ( $row [ 2 ] !== 'PRIMARY' )
  361. {
  362. $indexArray [] = array (
  363. 'label' => $row [ 4 ] . "($row[2])"
  364. );
  365. }
  366. }
  367.  
  368. $indexFolder = array (
  369. 'label' => 'Indexes', 'children' => $indexArray
  370. );
  371.  
  372. return $indexFolder;
  373. }
  374.  
  375. //TODO:
  376. /**
  377. * I get the triggers
  378. *
  379. * @param [string] $database
  380. * @param [string] $table
  381. * @return [array]
  382. */
  383. private function tree_tbl_getTriggers( $database, $table )
  384. {
  385. $triggerArray = $this->queryToARRAY ( "SHOW INDEX FROM $database.$table" );
  386.  
  387. $triggerFolder = array (
  388. 'label' => 'Triggers', 'children' => array (
  389. $triggerArray
  390. )
  391. );
  392.  
  393. return $triggerFolder;
  394. }
  395.  
  396. /**
  397. * I get the table status
  398. *
  399. * @param [string] $database
  400. * @param [string] $table
  401. * @return [array]
  402. */
  403. private function _tbl_getStatus( $database, $table )
  404. {
  405. return $this->queryToARRAY ( "SHOW TABLE STATUS FROM $database LIKE '$table'" );
  406. }
  407.  
  408. /**
  409. * I get the size of all the databases
  410. *
  411. * @param [string] $database the database
  412. * @return [array]
  413. */
  414. private function _db_getSize( $database )
  415. {
  416. $statusSQL = $this->realQuery ( "SHOW TABLE STATUS FROM $database" );
  417. $sizeArray = array ();
  418.  
  419. $totalSize = 0;
  420. $dataSize = 0;
  421. $indexSize = 0;
  422.  
  423. //loop all the results
  424. while ( $size = mysqli_fetch_assoc ( $statusSQL ) )
  425. {
  426. $dataSize += $size [ 'Data_length' ];
  427. $indexSize += $size [ 'Index_length' ];
  428. }
  429. $totalSize = $dataSize + $indexSize;
  430. $sizeArray [] = array (
  431. 'totalSize' => $totalSize, 'dataSize' => $dataSize, 'indexSize' => $indexSize
  432. );
  433.  
  434. return $sizeArray;
  435. }
  436.  
  437. /**
  438. * I get the status of the all the tables for a database.
  439. *
  440. * @param [string] $database the database
  441. * @return [array]
  442. */
  443. private function _db_getStatus( $database )
  444. {
  445. return $this->queryToARRAY ( "SHOW TABLE STATUS FROM $database" );
  446. }
  447.  
  448. /**
  449. * I execute a query and return the results as json.
  450. *
  451. * @param [string] $sql the query to be executed
  452. * @return [json] the result in json
  453. */
  454. private function queryToJSON( $sql )
  455. {
  456. $result = $this->realQuery ( $sql );
  457.  
  458. while ( $row = mysqli_fetch_assoc ( $result ) )
  459. {
  460. $array [] = $row;
  461. }
  462. return json_encode ( $array );
  463. }
  464.  
  465. /**
  466. * I execute a query and return the result as an array.
  467. *
  468. * @param [string] $sql the query to be executed
  469. * @return [array] the result array
  470. */
  471. public function queryToARRAY( $sql )
  472. {
  473. $query = $this->realQuery ( $sql );
  474. $array = array ();
  475.  
  476. while ( $row = mysqli_fetch_assoc ( $query ) )
  477. {
  478. $array [] = $row;
  479. }
  480.  
  481. return $array;
  482. }
  483.  
  484. /**
  485. * I get the query status
  486. *
  487. * @param [string] $sql
  488. * @return [json] mysql status with the ('_') striped out
  489. */
  490. public function queryStatusToJSON( $sql )
  491. {
  492. $result = $this->realQuery ( $sql );
  493.  
  494. while ( $row = mysqli_fetch_assoc ( $result ) )
  495. {
  496. //replace some of the names
  497. $row = str_replace ( 'Com_', '', $row );
  498. //take out the _ of the rows
  499. $row = str_replace ( '_', ' ', $row );
  500.  
  501. $array [] = $row;
  502. }
  503. sort ( $array );
  504.  
  505. return json_encode ( $array );
  506. }
  507.  
  508. /**
  509. * I dump vars
  510. *
  511. * @param [string] $title the title of the dump
  512. * @param [var] $var the var
  513. */
  514. public function dump( $title, $var )
  515. {
  516. print "<h4>$title</h4>";
  517. print "<pre>";
  518. print_r ( $var );
  519. print "</pre>";
  520. }
  521.  
  522. /**
  523. * @return [link]
  524. */
  525. public function getMysqli()
  526. {
  527. return $this->mysqli;
  528. }
  529.  
  530. /**
  531. * @param [link] $mysqli
  532. */
  533. public function setMysqli( $mysqli )
  534. {
  535. $this->mysqli = $mysqli;
  536. }
  537.  
  538. }
  539. ?>

Report this snippet  

You need to login to post a comment.