Last night I noticed now Zend IDE has the Data Source Explorer set up like a tree of your database and tables, columns, keys, etc.
I thought that was pretty cool, so I decided to do the same thing.
But with my buddy JSON and his right hand man PHP, we will let Flex come and play with all of us.
This is going to move fast:
First method – Utility method for sending us an array from MySQL.
public function queryToJSON( $sql )
{
$result = mysqli_query ( $this->mysqli, $sql );
while ( $row = mysqli_fetch_assoc ( $result ) )
{
$array [] = $row;
}
return json_encode ( $array );
}Next we are going to start from the bottom up, some of these methods are dummy methods and dont get any data. Because I havent wanted to yet. Triggers Folder
private function tree_tbl_getTriggers( $database, $table )
{
//$triggerArray = $this->queryToARRAY ( "SHOW INDEX FROM $database.$table" );
$triggerFolder = array ( 'label' => 'Triggers', 'children' => array ( $triggerArray ) );
return $triggerFolder;
}Index Folder:
private function tree_tbl_getIndexes( $database, $table )
{
$sql = "SHOW INDEX FROM $database.$table";
$query = mysqli_query ( $this->mysqli, $sql );
$indexArray = array ();
while ( $row = mysqli_fetch_row ( $query ) )
{
if ( $row [ 2 ] !== 'PRIMARY' )
{
$indexArray [] = array (
'label' => $row [ 4 ] . "($row[2])" );
}
}
$indexFolder = array ( 'label' => 'Indexes', 'children' => $indexArray );
return $indexFolder;
}Dependencies Folder:
private function tree_tbl_getDependcenies( $database, $table )
{
$dependArray = array ( 'label' => 'admin table' );
$dependFolder = array ( 'label' => 'Dependencies', 'children' => array ( $dependArray ) );
return $dependFolder;
}Constraints Folder:
private function tree_tbl_getConstraints( $database, $table )
{
$sql = "SHOW INDEX FROM $database.$table";
$result = mysqli_query ( $this->mysqli, $sql );
$constraintArray = array ();
while ( $constraint = mysqli_fetch_assoc ( $result ) )
{
if ( $constraint [ 'Key_name' ] == 'PRIMARY' )
{
$constraintArray = array ( 'label' => $constraint [ 'Key_name' ] );
}
}
$constraintFolder = array ( 'label' => 'Constraints', 'children' => array ( $constraintArray ) );
return $constraintFolder;
}Columns Folder:
private function tree_tbl_getColumns( $database, $table )
{
$sql = "SHOW FIELDS FROM $database.$table";
$query = mysqli_query ( $this->mysqli, $sql );
$columnsArray = array ();
while ( $row = mysqli_fetch_row ( $query ) )
{
$type = strtoupper ( $row [ 1 ] );
$null = '';
if ( $row [ 2 ] == 'YES' )
{
$null = 'Nullable';
}
$type = '[' . $type . ' ' . $null . ']';
$columnsArray [] = array ( 'label' => $row [ 0 ] . ' ' . $type );
}
$columnsFolder = array ( 'label' => 'Columns', 'children' => $columnsArray );
return $columnsFolder;
}Some dummy methods I haven’t finished yet.
Dependencies, Stored Procedures, User Functions, Authorizations, etc. Folders:
private function tree_db_getDependcenies( $database )
{
$dependceniesArray = array ( 'label' => 'Dependcencies', 'children' => array ( 'label' => 'test' ) );
return $dependceniesArray;
}
private function tree_db_getStoredProcs( $database )
{
$storedProcsArray = array ( 'label' => 'Stored Procedures', 'children' => array ( 'label' => 'test' ) );
return $storedProcsArray;
}
private function tree_db_getUserFunctions( $database )
{
}
private function tree_db_getAuthorizations()
{
$authorizationsArray = array ( 'label' => 'Authorization IDs', 'children' => array ( 'label' => 'rfd' ) );
return $authorizationsArray;
}
private function tree_db_getViews( $database )
{
}Tables Folder:
private function tree_db_getTables( $database )
{
//table query
$tableSQL = mysqli_query ( $this->mysqli, "SHOW TABLES FROM $database" );
//create a new array of tables
$tables = array ();
//loop all the results
while ( $table = mysqli_fetch_assoc ( $tableSQL ) )
{
$columns = array ();
$statuss = array ();
$indexes = array ();
//for each table in the result make an array
foreach ( $table as $t_key => $t_value )
{
//get the tables fields for each table
$columns = $this->tree_tbl_getColumns ( $database, $t_value );
//now get the primary key for each table
$constraints = $this->tree_tbl_getConstraints ( $database, $t_value );
//now get the indexes for each table
$indexes = $this->tree_tbl_getIndexes ( $database, $t_value );
//now get the dependencys for each table
$dependicy = $this->tree_tbl_getDependcenies ( $database, $t_value );
//now get the triggers for each table
$triggers = $this->tree_tbl_getTriggers ( $database, $t_value );
}
$columnArr = $columns;
$constraintArr = $constraints;
$indexArr = $indexes;
$dependencyArr = $dependicy;
$triggerArr = $triggers;
$tables [] = array ( 'label' => $t_value, "type" => "table", "icon" => "table", 'children' => array ( $columnArr, $constraintArr, $indexArr, $dependencyArr, $triggerArr ) );
}
$tableFolder [] = array ( 'label' => 'Tables', 'children' => $tables );
return $tableFolder;
}Database Folder:
public function tree_getSchemas()
{
//Database query
$databaseSQL = $this->realQuery ( "SHOW DATABASES" );
//New database array
$databases = array ();
//Loop the query
while ( $database = mysqli_fetch_assoc ( $databaseSQL ) )
{
//Create a new array of tables for each database
$tables = array ();
//$status = array ();
//$size = array ();
foreach ( $database as $key => $value )
{
//Set the table array to get the tbles from the database
$tables = $this->tree_db_getTables ( $value );
//$status = $this->_getTableStatus ( $value );
//$size = $this->_getDatabaseSize ( $value );
}
//Add the tables to the database array
$databases [] = array ( "label" => $value, "data" => $key, "type" => "database", "icon" => "database", "children" => $tables );
}
$databaseFolder [] = array ( 'label' => 'Schemas', 'children' => $databases );
return $databaseFolder;
}Host Folder:
public function tree_getTree()
{
$mysqlVersion = mysqli_get_client_info ( $this->mysqli );
$host = $_SERVER [ 'HTTP_HOST' ] . " (MySQL v. $mysqlVersion )";
$hostArray = array ( 'label' => $host, 'type' => 'server', 'children' => $this->tree_getSchemas () );
$treeArray [] = array ( 'label' => 'SQL Databases', 'type' => 'servers', 'children' => $hostArray );
return $treeArray;
}
I thought that was pretty cool, so I decided to do the same thing.
But with my buddy JSON and his right hand man PHP, we will let Flex come and play with all of us.
This is going to move fast:
First method – Utility method for sending us an array from MySQL.
public function queryToJSON( $sql )
{
$result = mysqli_query ( $this->mysqli, $sql );
while ( $row = mysqli_fetch_assoc ( $result ) )
{
$array [] = $row;
}
return json_encode ( $array );
}Next we are going to start from the bottom up, some of these methods are dummy methods and dont get any data. Because I havent wanted to yet. Triggers Folder
private function tree_tbl_getTriggers( $database, $table )
{
//$triggerArray = $this->queryToARRAY ( "SHOW INDEX FROM $database.$table" );
$triggerFolder = array ( 'label' => 'Triggers', 'children' => array ( $triggerArray ) );
return $triggerFolder;
}Index Folder:
private function tree_tbl_getIndexes( $database, $table )
{
$sql = "SHOW INDEX FROM $database.$table";
$query = mysqli_query ( $this->mysqli, $sql );
$indexArray = array ();
while ( $row = mysqli_fetch_row ( $query ) )
{
if ( $row [ 2 ] !== 'PRIMARY' )
{
$indexArray [] = array (
'label' => $row [ 4 ] . "($row[2])" );
}
}
$indexFolder = array ( 'label' => 'Indexes', 'children' => $indexArray );
return $indexFolder;
}Dependencies Folder:
private function tree_tbl_getDependcenies( $database, $table )
{
$dependArray = array ( 'label' => 'admin table' );
$dependFolder = array ( 'label' => 'Dependencies', 'children' => array ( $dependArray ) );
return $dependFolder;
}Constraints Folder:
private function tree_tbl_getConstraints( $database, $table )
{
$sql = "SHOW INDEX FROM $database.$table";
$result = mysqli_query ( $this->mysqli, $sql );
$constraintArray = array ();
while ( $constraint = mysqli_fetch_assoc ( $result ) )
{
if ( $constraint [ 'Key_name' ] == 'PRIMARY' )
{
$constraintArray = array ( 'label' => $constraint [ 'Key_name' ] );
}
}
$constraintFolder = array ( 'label' => 'Constraints', 'children' => array ( $constraintArray ) );
return $constraintFolder;
}Columns Folder:
private function tree_tbl_getColumns( $database, $table )
{
$sql = "SHOW FIELDS FROM $database.$table";
$query = mysqli_query ( $this->mysqli, $sql );
$columnsArray = array ();
while ( $row = mysqli_fetch_row ( $query ) )
{
$type = strtoupper ( $row [ 1 ] );
$null = '';
if ( $row [ 2 ] == 'YES' )
{
$null = 'Nullable';
}
$type = '[' . $type . ' ' . $null . ']';
$columnsArray [] = array ( 'label' => $row [ 0 ] . ' ' . $type );
}
$columnsFolder = array ( 'label' => 'Columns', 'children' => $columnsArray );
return $columnsFolder;
}Some dummy methods I haven’t finished yet.
Dependencies, Stored Procedures, User Functions, Authorizations, etc. Folders:
private function tree_db_getDependcenies( $database )
{
$dependceniesArray = array ( 'label' => 'Dependcencies', 'children' => array ( 'label' => 'test' ) );
return $dependceniesArray;
}
private function tree_db_getStoredProcs( $database )
{
$storedProcsArray = array ( 'label' => 'Stored Procedures', 'children' => array ( 'label' => 'test' ) );
return $storedProcsArray;
}
private function tree_db_getUserFunctions( $database )
{
}
private function tree_db_getAuthorizations()
{
$authorizationsArray = array ( 'label' => 'Authorization IDs', 'children' => array ( 'label' => 'rfd' ) );
return $authorizationsArray;
}
private function tree_db_getViews( $database )
{
}Tables Folder:
private function tree_db_getTables( $database )
{
//table query
$tableSQL = mysqli_query ( $this->mysqli, "SHOW TABLES FROM $database" );
//create a new array of tables
$tables = array ();
//loop all the results
while ( $table = mysqli_fetch_assoc ( $tableSQL ) )
{
$columns = array ();
$statuss = array ();
$indexes = array ();
//for each table in the result make an array
foreach ( $table as $t_key => $t_value )
{
//get the tables fields for each table
$columns = $this->tree_tbl_getColumns ( $database, $t_value );
//now get the primary key for each table
$constraints = $this->tree_tbl_getConstraints ( $database, $t_value );
//now get the indexes for each table
$indexes = $this->tree_tbl_getIndexes ( $database, $t_value );
//now get the dependencys for each table
$dependicy = $this->tree_tbl_getDependcenies ( $database, $t_value );
//now get the triggers for each table
$triggers = $this->tree_tbl_getTriggers ( $database, $t_value );
}
$columnArr = $columns;
$constraintArr = $constraints;
$indexArr = $indexes;
$dependencyArr = $dependicy;
$triggerArr = $triggers;
$tables [] = array ( 'label' => $t_value, "type" => "table", "icon" => "table", 'children' => array ( $columnArr, $constraintArr, $indexArr, $dependencyArr, $triggerArr ) );
}
$tableFolder [] = array ( 'label' => 'Tables', 'children' => $tables );
return $tableFolder;
}Database Folder:
public function tree_getSchemas()
{
//Database query
$databaseSQL = $this->realQuery ( "SHOW DATABASES" );
//New database array
$databases = array ();
//Loop the query
while ( $database = mysqli_fetch_assoc ( $databaseSQL ) )
{
//Create a new array of tables for each database
$tables = array ();
//$status = array ();
//$size = array ();
foreach ( $database as $key => $value )
{
//Set the table array to get the tbles from the database
$tables = $this->tree_db_getTables ( $value );
//$status = $this->_getTableStatus ( $value );
//$size = $this->_getDatabaseSize ( $value );
}
//Add the tables to the database array
$databases [] = array ( "label" => $value, "data" => $key, "type" => "database", "icon" => "database", "children" => $tables );
}
$databaseFolder [] = array ( 'label' => 'Schemas', 'children' => $databases );
return $databaseFolder;
}Host Folder:
public function tree_getTree()
{
$mysqlVersion = mysqli_get_client_info ( $this->mysqli );
$host = $_SERVER [ 'HTTP_HOST' ] . " (MySQL v. $mysqlVersion )";
$hostArray = array ( 'label' => $host, 'type' => 'server', 'children' => $this->tree_getSchemas () );
$treeArray [] = array ( 'label' => 'SQL Databases', 'type' => 'servers', 'children' => $hostArray );
return $treeArray;
}
发表评论
-
解决MySQL error 1036: table is read only
2011-05-05 22:37 1341改变数据库文件的宿主权限 sudo chown -R mysq ... -
VMware中去掉Linux报警声
2010-05-05 13:09 738将/etc/inputrc中的set bell-style n ... -
Ajax传递数据对加号变空格的处理
2010-01-26 15:11 3074采用Ajax传递数据时,通常会将数据整理为data=" ... -
MySQL 字符串函数大全
2009-12-18 17:29 1190DING | TRAILING] [remstr] FROM] ... -
sybase字符串函数
2009-12-18 12:56 1971长度和语法分析 datalength(char_expr) ... -
json转为java的vo对象
2009-12-17 11:29 6418String str = "[{'name':'he ... -
Java 防SQL 注入函数
2009-12-09 16:34 870看了网上的几个版本,感觉比较别扭,为何不直接用replaceA ... -
Spring启动异常
2009-11-29 22:53 0Spring启动异常: cvc-elt.1: Cannot f ... -
Class IOUtils
2009-11-26 09:03 1076public class IOUtilsextends jav ... -
org.apache.commons.io使用实例
2009-11-26 09:01 26611.文件内容拷贝: import java.io.File; ... -
Firefox 取 TextArea的值
2009-11-06 16:34 811用value代替innerHTML才取的到刚输入的值。 之前竟 ... -
Apache配置Weblogic
2009-10-28 09:21 1519LoadModule weblogic_module modu ... -
logic:itertae标签的使用
2009-10-20 00:37 665相关文章: Struts1.2.4 ... -
Struts多行提交
2009-10-19 16:38 985ActionForm: public List checkRe ... -
ibatis批量Update
2009-10-19 16:33 2464public boolean updateCheckRus ...
相关推荐
json转换json schema
JSON Schema 生成库——json-schema-inferrer(java版).rar
JSON Schema 校验库——json-schema-validator(java版本).rar
react-jsonschema-form - 一个React组件用于从JSONSchema构建Web表单
同样,对于JSON文件的定义,也应该有一个JSON Schema以规范JSON文件内容。IETF负责起草相关规范最新版本是2018年3月19日发布的Draft 7。JSON Schema用以标注和验证JSON文档的元数据的文档,可以类比于XML Schema。相...
jsonschema-generator最新源代码jsonschema-generator最新源代码jsonschema-generator最新源代码jsonschema-generator最新源代码jsonschema-generator最新源代码jsonschema-generator最新源代码jsonschema-generator...
go-jsonschema是一个从JSON Schema定义生成Go数据类型的工具
基于Vue的JSON可视化编辑器,通过定义 JSON Schema 直接生成 UI 界面
JSON Schema 规范(中文版).pdf
JSON Schema(application / schema + json)有几个目的,其中一个是JSON实例验证。本文档为JSON Schema指定了一个词汇表,用于描述JSON文档的含义,为使用JSON数据的用户界面提供提示,以及对有效文档的schema进行...
这是“ Json Schema”的可视化构建器,可导出为生成的“ Json Schema”而生成的“ Json Schema”,然后将该模式的结构粘贴到您的 为什么? 因为我需要可视地创建表单,才能使用react-jsonschema-form语法将其导出...
资源分类:Python库 所属语言:Python 使用前提:需要解压 资源全名:jsonschema-4.0.0a1-py2.py3-none-any.whl 资源来源:官方 安装方法:https://lanzao.blog.csdn.net/article/details/101784059
jsonschema在开发及测试中的应用.doc
将JSONSchema编译成TypeScript typings
Flask-json-schema 通过此扩展,可以轻松验证使用jsonschema规范发送到Flask应用的JSON数据 设置 Flask-json-schema在PyPI上可用,并且可以与 pip install flask-json-schema 扩展名可以直接初始化: from flask ...
从Protobuf到JSON-Schema编译器这将采用protobuf定义并将其转换为JSONSchemas,可用于动态验证JSON消息。 对于使用ProtoBuf定义数据但将JSON用作“ wire”格式的人们很有用。 受到“重大影响”。安装注意:此工具...
资源来自pypi官网。 资源全名:jsonschema_pyref-0.1.0-py3-none-any.whl
jsonschema2pojo jsonschema2pojo从JSON模式(或示例JSON)生成Java类型,并可以注释这些类型以便与Jackson 1.x,Jackson 2.x或Gson进行数据绑定。 注意:在0.5.1和1.0.0之间存在重大变化。 检查。 1.0.0 Alpha,...
前端开源库-raml-jsonschema-expanderraml json schema expander,从使用raml2obj创建的raml对象扩展json模式草案4引用。
json-schema-editorJSON数据可视化/JSONSchema, 以可视化界面编辑json schema数据(带结构/格式的json数据)。技术栈:React/Mobx/Ant Design特点:支持12种基础类型组件(input、boolean、 date、date-time、 time...