>我正在创建实体(实体-视图-控制器)(换句话说,MVC 模型)类,这些类理论上与我拥有的 databse 表相匹配。是否有读取 mysql 表并创建模型类代码的工具?(不是在执行时,需要代码输出) 我希望输出如下
class{
public $columnname1;
public $columnname2;
public $columnname3;
public $columnname4;
public $columnname5;
public $columnname6;
function __construct(&$columnname1, &$columnname2){...}
function insert(&$columnname1, &$columnname2){}
function delete(&$columnname1){}
...
}
一个还可以通过id函数创建插入,更新和删除的工具将对我有很大帮助。
该工具可能是免费的,也可能是付费的。
PDO可以将结果提取到对象中。
设计一个与数据库/查询结构匹配的类,并使用PDO::FETCH_INTO
将结果集提取到已实例化的对象中。误读了这个问题,我的错。
要从数据库结构生成类本身,有几个项目(我还没有测试过,但这是在非常简单的搜索中出现的)。
- db2php
- PHP MySQL 类生成器
以下代码是我长期以来用于为 MySQL 和 DB2 表创建 PHP 模型的代码。 我已经为MSSQL,PGSQL和SQLite准备了存根,但从未需要完成它们。
下面是代码生成器类:
<?php
/**
* @license http://opensource.org/licenses/MIT The MIT License
* @version 1.0.0_20130220000000
*/
/**
* This class will generate PHP source code for a "model" that interfaces with
* a database table.
*
* @license http://opensource.org/licenses/MIT The MIT License
* @version 1.0.0_20130220000000
*/
class db_code_generator{
private $closing_tag;
private $columns;
private $database;
private $host;
private $password;
private $port;
private $table;
private $type;
private $username;
/**
* Constructor. By default we will try to connect to a MySQL database on
* localhost.
*
* @param string $database The name of the database the user wants to connect
* to.
*
* @param string $table The name of the table to generate code for.
*
* @param string $username The username we should use to connect to the
* database.
*
* @param string $password The password we need to connect to the database.
*
* @param string $host The host or server we will try to connect to. If the
* user doesn't give us a host we default to localhost.
*
* @param string $port The port we should try to connect to. Typically this
* will not be passed so we default to NULL.
*
* @param string $type The type of database we are connecting to. Valid
* values are: db2, mssql, mysql, pgsql, sqlite.
*/
public function __construct($database = NULL,
$table = NULL,
$username = NULL,
$password = NULL,
$host = 'localhost',
$type = 'mysql',
$port = NULL,
$closing_tag = TRUE){
$this->database = $database;
$this->table = $table;
$this->username = $username;
$this->password = $password;
$this->host = $host;
$this->port = $port;
$this->type = $type;
$this->closing_tag = $closing_tag;
}
/**
* Generate the code for a model that represents a record in a table.
*
* @return string The PHP code generated for this model.
*/
public function get_code(){
$this->get_data_definition();
$code = $this->get_file_head();
$code .= $this->get_properties();
$code .= $this->get_ctor();
$code .= $this->get_dtor();
$code .= $this->get_method_stubs();
$code .= $this->get_file_foot();
return $code;
}
/**
* Create the code needed for the __construct function.
*
* @return string The PHP code for the __construct function.
*/
private function get_ctor(){
$code = "t/**n";
$code .= "t * Constructor.n";
$code .= "t *n";
$code .= "t * @param mixed $id The unique id for a record in this table. Defaults to NULLn";
if ('db2' === $this->type){
$code .= "nt * @param string $library The library where the physical file resides. Defaults to LIBRARYn";
}
$code .= "t *n";
$code .= "t * @see base_$this->type::__constructn";
$code .= "t */n";
if ('db2' === $this->type){
$code .= "tpublic function __construct($id = NULL, $library = 'LIBRARY'){n";
$code .= "ttparent::__construct($id, $library);n";
}else{
$code .= "tpublic function __construct($id = NULL){n";
$code .= "ttparent::__construct($id);n";
}
$code .= "t}nn";
return $code;
}
/**
* Connect to the requested database and get the data definition for the
* requested table.
*/
private function get_data_definition(){
try{
switch ($this->type){
case 'db2':
$this->get_data_definition_db2();
break;
case 'mssql':
$this->get_data_definition_mssql();
break;
case 'mysql':
$this->get_data_definition_mysql();
break;
case 'pgsql':
$this->get_data_definition_pgsql();
break;
case 'sqlite':
$this->get_data_definition_sqlite();
break;
}
}catch(PDOException $e){
}
}
/**
* Get data definition information for a DB2 table.
*/
private function get_data_definition_db2(){
$con = new PDO("odbc:DRIVER={iSeries Access ODBC Driver};SYSTEM=$this->host;PROTOCOL=TCPIP", $this->username, $this->password);
$sql = "SELECT COLUMN_NAME, COLUMN_SIZE, COLUMN_TEXT, DECIMAL_DIGITS, ORDINAL_POSITION, TYPE_NAME FROM SYSIBM.SQLCOLUMNS WHERE TABLE_SCHEM = '". strtoupper($this->database) ."' AND TABLE_NAME = '". strtoupper($this->table) ."'";
$statement = $con->prepare($sql);
if ($statement->execute()){
while ($row = $statement->fetch()){
if (NULL !== $row['DECIMAL_DIGITS']){
$decimal = $row['DECIMAL_DIGITS'];
}else{
$decimal = NULL;
}
if ('DECIMAL' === $row['TYPE_NAME'] && NULL !== $row['DECIMAL_DIGITS'] && '0' !== $row['DECIMAL_DIGITS']){
$type = 'float';
}else if ('DECIMAL' === $row['TYPE_NAME']){
$type = 'integer';
}else{
$type = strtolower($row['TYPE_NAME']);
}
if ('1' === $row['ORDINAL_POSITION']){
$key = 'PRI';
}else{
$key = NULL;
}
$this->columns[$row['COLUMN_NAME']] = array('allow_null' => TRUE,
'decimal' => $decimal,
'default' => NULL,
'extra' => NULL,
'key' => $key,
'length' => $row['COLUMN_SIZE'],
'name' => $row['COLUMN_NAME'],
'text' => $row['COLUMN_TEXT'],
'type' => $type);
}
ksort($this->columns);
}
}
/**
* Get data definition information for a MS SQL table.
*/
private function get_data_definition_mssql(){
return "The code for generating MS SQL models is not yet implemented.n";
}
/**
* Get data definition information for a MySQL table.
*/
private function get_data_definition_mysql(){
$dsn = "mysql:host=$this->host;";
if (NULL !== $this->port){
$dsn .= "port=$this->port;";
}
$dsn .= "dbname=$this->database";
$con = new PDO($dsn, $this->username, $this->password);
$sql = "SHOW COLUMNS FROM $this->table";
$statement = $con->prepare($sql);
if ($statement->execute()){
while ($row = $statement->fetch()){
$this->columns[$row['Field']] = array('allow_null' => $row['Null'],
'decimal' => NULL,
'default' => $row['Default'],
'extra' => $row['Extra'],
'key' => $row['Key'],
'length' => NULL,
'name' => $row['Field'],
'text' => NULL,
'type' => $row['Type']);
}
ksort($this->columns);
}
}
/**
* Get data definition information for a PostgreSQL table.
*/
private function get_data_definition_pgsql(){
return "The code for generating PostgreSQL models is not yet implemented.n";
}
/**
* Get data definition information for a SQLite table.
*/
private function get_data_definition_sqlite(){
return "The code for generating SQLite models is not yet implemented.n";
}
/**
* Create the code needed for the __destruct function.
*
* @return string The PHP code for the __destruct function.
*/
private function get_dtor(){
$code = "t/**n";
$code .= "t * Destructor.n";
$code .= "t */n";
$code .= "tpublic function __destruct(){n";
$code .= "ttparent::__destruct();n";
$code .= "t}nn";
return $code;
}
/**
* Generate the code found at the end of the file - the closing brace, the
* ending PHP tag and a new line. Some PHP programmers prefer to not have a
* closing PHP tag while others want the closing tag and trailing newline -
* it probably just depends on their programming background. Regardless it's
* best to let everyone have things the way they want.
*/
private function get_file_foot(){
$code = '';
if ($this->closing_tag){
$code .= "}n?>n";
}else{
$code .= '}';
}
return $code;
}
/**
* Generate the code found at the beginning of the file - the PHPDocumentor
* doc block, the require_once for the correct base class and the class name.
*
* @return string The code generated for the beginning of the file.
*/
private function get_file_head(){
$code = "<?phpn";
$code .= "/**n";
$code .= " * Please enter a description of this class.n";
$code .= " *n";
$code .= " * @author XXX <XXX@domain.com>n";
$code .= " * @copyright Copyright (c) ". date('Y') ."n";
$code .= " * @license http://www.gnu.org/licenses/gpl-3.0.html GPLv3n";
$code .= " * @version ". date('Ymd') ."n";
$code .= " */nn";
$code .= "require_once('base_$this->type.php');nn";
$code .= "class ". strtolower($this->table) ." extends base_$this->type{n";
return $code;
}
/**
* Generate the code for a delete method stub.
*
* @return string The PHP code for the method stub.
*/
private function get_method_stub_delete(){
$code = "t/**n";
$code .= "t * Override the delete method found in the base class.n";
$code .= "t *n";
$code .= "t * @param mixed $id The unique record ID to be deleted.n";
$code .= "t *n";
$code .= "t * @return bool TRUE if a record was successfully deleted from the table, FALSE otherwise.n";
$code .= "t */n";
$code .= "tpublic function delete($id){n";
$code .= "ttreturn parent::delete($id);n";
$code .= "t}nn";
return $code;
}
/**
* Generate the code for an insert method stub.
*
* @return string The PHP code for the method stub.
*/
private function get_method_stub_insert(){
$code = "t/**n";
$code .= "t * Override the insert method found in the base class.n";
$code .= "t *n";
$code .= "t * @param array $parms An array of data, probably the $_POST array.n";
$code .= "t * @param bool $get_insert_id A flag indicating if we should get the autoincrement value of the record just created.n";
$code .= "t *n";
$code .= "t * @return bool TRUE if a record was successfully inserted into the table, FALSE otherwise.n";
$code .= "t */n";
$code .= "tpublic function insert($parms, $get_insert_id = FALSE){n";
$code .= "ttreturn parent::insert($parms, $get_insert_id);n";
$code .= "t}nn";
return $code;
}
/**
* Generate the code for an update method stub.
*
* @return string The PHP code for the method stub.
*/
private function get_method_stub_update(){
$code = "t/**n";
$code .= "t * Override the update method found in the base class.n";
$code .= "t *n";
$code .= "t * @param array &$parms An array of key=>value pairs - most likely the $_POST array.n";
$code .= "t *n";
$code .= "t * @param integer $limit The number of records to update. Defaults to NULL.n";
$code .= "t *n";
$code .= "t * @return bool TRUE if a record was successfully updated, FALSE otherwise.n";
$code .= "t */n";
$code .= "tpublic function update($parms, $limit = NULL){n";
$code .= "ttreturn parent::update($parms, $limit);n";
$code .= "t}nn";
return $code;
}
/**
* Create method stubs for create, delete and update.
*
* @return string The PHP code for these stubs.
*/
private function get_method_stubs(){
$code = $this->get_method_stub_delete();
$code .= $this->get_method_stub_insert();
$code .= $this->get_method_stub_update();
return $code;
}
private function get_properties(){
$code = '';
if (count($this->columns)){
foreach ($this->columns AS $index => $col){
$code .= "t/**n";
if (NULL !== $col['text']){
$code .= "t * $col[text]n";
}else{
$code .= "t * Descriptionn";
}
$code .= "t * @var ". $col['type'];
if (NULL !== $col['length']){
$code .= " ($col[length]";
if (NULL !== $col['decimal']){
$code .= ",$col[decimal]";
}
$code .= ")";
}
$code .= "nt */n";
$temp_name = str_replace('#', '_', $col['name']);
$code .= "tpublic $$temp_name;nn";
}
}
return $code;
}
}
?>
这里有一个简单的页面来使用它:
<?php
/**
* @license GPLv3 (http://www.gnu.org/licenses/gpl-3.0.html)
* @version 1.0.0_20130220000000
*/
require_once('db_code_generator.php');
$table_type = array();
$table_type['db2'] = 'DB2/400 (db2)';
$table_type['mssql'] = 'Microsoft SQL Server (mssql)';
$table_type['mysql'] = 'MySQL (mysql)';
$table_type['pgsql'] = 'PostGRESQL (pgsql)';
$table_type['sqlite'] = 'SQLite (sqlite)';
$database = (isset($_POST['database'])) ? $_POST['database'] : 'my_database';
$host = (isset($_POST['host'])) ? $_POST['host'] : 'localhost';
$username = (isset($_POST['username'])) ? $_POST['username'] : 'root';
$password = (isset($_POST['password'])) ? $_POST['password'] : '';
$table = (isset($_POST['table'])) ? $_POST['table'] : '';
$type = (isset($_POST['type'])) ? $_POST['type'] : 'mysql';
$library = (isset($_POST['library'])) ? $_POST['library'] : 'LIBRARY';
$file = (isset($_POST['file'])) ? $_POST['file'] : 'STATES';
//---------------------------------------------------------------------------
?>
<div class="data_input">
<form action="" method="post">
<fieldset class="top">
<legend>Code Generator</legend>
<label for="host">Hostname or IP:
<input id="host" maxlength="32" name="host" tabindex="<?php echo $tabindex++; ?>" title="Enter the database host name" type="text" value="<?php echo $host; ?>" />
</label>
<br />
<label for="username">Username:
<input id="username" maxlength="32" name="username" tabindex="<?php echo $tabindex++; ?>" title="Enter the database username" type="text" value="<?php echo $username; ?>" />
</label>
<br />
<label for="password">Password:
<input id="password" maxlength="32" name="password" tabindex="<?php echo $tabindex++; ?>" title="Enter the database password" type="password" value="<?php echo $password; ?>" />
</label>
<br />
<label for="type">Type:
<select id="type" name="type" tabindex="<?php echo $tabindex++; ?>">
<?php
foreach ($table_type AS $key=>$value){
echo('<option ');
if ($key == $type){
echo 'selected="selected" ';
}
echo 'value="'. $key .'">'. $value .'</option>';
}
?>
</select>
</label>
<br />
</fieldset>
<fieldset class="top">
<legend>PostGRESQL/MSSQL/MySQL Parameters</legend>
<label for="database">Database:
<input id="database" maxlength="100" name="database" tabindex="<?php echo $tabindex++; ?>" title="Enter the database name" type="text" value="<?php echo $database; ?>" />
</label>
<br />
<label for="table">Table:
<input id="table" maxlength="100" name="table" tabindex="<?php echo $tabindex++; ?>" title="Enter the table name" type="text" value="<?php echo $table; ?>" />
</label>
<br />
</fieldset>
<fieldset class="top">
<legend>DB2 Parameters</legend>
<label for="library">Library:
<input id="library" maxlength="10" name="library" tabindex="<?php echo $tabindex++; ?>" title="Enter the library name" type="text" value="<?php echo $library; ?>" />
</label>
<br />
<label for="file">Physical File:
<input id="file" maxlength="10" name="file" tabindex="<?php echo $tabindex++; ?>" title="Enter the file name" type="text" value="<?php echo $file; ?>" />
</label>
<br />
</fieldset>
<fieldset class="bottom">
<button tabindex="<?php echo $tabindex++; ?>" type="submit">Generate!</button>
</fieldset>
</form>
</div>
<?php
if (isset($_POST['host'])){
if ('db2' == $_POST['type']){
$_POST['database'] = strtoupper($_POST['library']); // Library
$_POST['table'] = strtoupper($_POST['file']); // Physical file
$_POST['host'] = 'db2_host';
$_POST['username'] = 'db2_username';
$_POST['password'] = 'db2_password';
}
$object = new db_code_generator($_POST['database'], $_POST['table'], $_POST['username'], $_POST['password'], $_POST['host'], $_POST['type']);
echo('<textarea rows="75" style="margin-left : 50px; width : 90%;" onfocus="select()">'. $object->get_code() .'</textarea>');
}
?>
我知道你正在寻找ORM之类的东西。
希望这有帮助
http://www.doctrine-project.org/
http://propelorm.org/
试试这个https://github.com/rcarvello/mysqlreflection
我为 MySQL 数据库的对象关系映射构建的一个有用的实用程序。
该实用程序为给定数据库模式的任何表自动生成 PHP 类。
该包是从我的个人PHP Web MVC框架中提取的。
symfony呢?它完全按照你说的去做,你会得到一个野兽般的好框架来配合它。
Symfony根据您提供的数据模型为您"编译"类。它将确保编译的类和MySQL数据库结构是同步的。
这种方法优于基于Reflection
的方法,因为它太慢了。
对于它的价值,Rafael Rocha在这里分享代码。
不过,我强烈建议使用 ORM。将MySQL结构转换回数据库抽象层绝不是一件好事......
是的,教义是你需要的。
-
运行一个命令,你会得到XML或YML格式的所有表的元数据(选择权在你)
$ php app/console doctrine:mapping:convert xml ./src/Bundle/Resources/config/doctrine/metadata/orm --from-database --force
-
生成元数据后,命令 Doctrine 导入架构以生成所需的相关实体类。您将在里面找到所有GET和SET功能(读取SELECT,UPDATE和INSERT)。
1.$ php 应用程序/控制台 原则:映射:导入 捆绑包注释
2.$ php 应用程序/控制台原则:生成:实体捆绑包
在此处阅读示例的详细信息
无脂肪框架允许您使用以下代码处理现有表:
$user=new DBSQLMapper($db,'users');
// or $user=new DBMongoMapper($db,'users');
// or $user=new DBJigMapper($db,'users');
$user->userID='jane';
$user->password=md5('secret');
$user->visits=0;
$user->save();
上面的代码在用户表中创建新记录
我编写了一个PHP代码,它将自动检测MYSQL数据库中的所有数据库,在选择任何数据库时,将加载所有相关表。您可以选择所有表或任何相应的表来生成模态类。
以下是我的存储库的链接
https://github.com/channaveer/EntityGenerator
这将自动创建实体文件夹,并将所有实体类转储到该文件夹中。
注意 - 目前仅支持 MYSQL
希望对您有所帮助。祝您编码愉快!
我认为你应该继续前进。在我看来,模型代码的每个项目和要求都是不同的。两个 mysql 查询将让您轻松自在。
-
SHOW TABLES FROM db_NAME //this gives the table listing, so its Master Looper
-
DESC tbl_name //detail query that fetches column information of given table
DESC tbl_name
将给出字段、类型、空、键、默认值和额外列。
例如,在管道 (|) 中分隔的值:
ID | int(11) |否 |PRI |空 |auto_increment |
我按照这些方法制作了模型、控制器和查看器文件,以支持 Codeigniter 2 中的 CRUD 操作。它工作得很好。