CRUD generator DBuilder design and implementation, crud generator dbuilder_PHP tutorial

Design and implementation of CRUD generator DBuilder, crud generator dbuilder

Chapter 1 Introduction

1.1 Research background and significance

With the development of computer software technology, databases have become the most widely used medium for storing formatted data, and database program development technology has also become increasingly perfect. Large-scale ORM frameworks have made database program development simple, and have become the basis for operating relational databases. mainstream approach. The main code of the database program is CRUD (create, retrieve, update, delete) code. With the improvement of the ORM framework function, writing CRUD code also derives its fixed process. CRUD code that operates on different database tables is also highly reusable. . Currently, writing repetitive CRUD codes has become the norm for developers, which not only seriously reduces their enthusiasm, but also reduces their development efficiency. Therefore, there is an urgent need for a product that can quickly generate CRUD codes in order to reduce this work and improve development efficiency.

1.2 Current Research Status

At present, some CRUD generator products with high usability that solve the above needs have been produced abroad: CrudKit, CRUD-Admin-Generator, Dadabik, GroceryCrud, SximoBuilder. Each of these products has its own characteristics, but they also have one thing in common: they are all developed based on PHP (this is determined to a certain extent by the flexibility and parsing of PHP syntax). SximoBuilder is a typical representative. Although SximoBuilder has a unique design, high usability, and high popularity, it also has the following shortcomings:

• Does not support custom form controls;
• Does not support multiple databases;
• The verification rules are incomplete and asynchronous verification is not supported;
• The code is extremely redundant.

However, for today's increasingly complex web programs, the above points are issues that must be considered during the development process. Therefore, it is imperative to develop a CRUD generator product that not only has the existing functions of SximoBuilder but also improves its shortcomings. OK.

1.3 Research content

Based on the current research status of CRUD generators at home and abroad, the author developed a CRUD generator called DBuilder (D is the abbreviation of DataAdministrator).

DBuilder draws on SximoBuilder’s idea that modules are code units and code is generated from templates, but it has a code generator logic that is completely different from SximoBuilder. On the basis of realizing the core code generation and general CRUD functions of SximoBuilder, it improves its shortcomings by rewriting the code logic: large code redundancy and lack of front-end verification.

Chapter 2 DBuilder System Analysis

The main user groups of DBuilder are web backend administrators and developers, so its system analysis process will consider issues more from the perspective of web backend administrators and developers.

2.1 Requirements Analysis

The project needs to implement the following core functions.

1) Data source management

Users can configure multiple data sources for the project in the interface. The configured data source information includes database type, address, database name, port, user name, password and other information. Supports addition, deletion, modification and query of data sources to ensure that addition, deletion, modification and query of data sources will not easily cause system problems.

2)   Code generation

This function is the core function of DBuilder. After selecting the data source and data table, the user can simply configure the fields of the database table. The configuration includes Form configuration, List (Table) list configuration, relationship configuration, Global property configuration. After the configuration is completed, DBuilder must be able to generate CRUD MVC code for the database table, that is, it needs to implement the CRUD available functions, but there is no need to write code.

3)   DatabaseTable CRUD

The generated code must support the creation, deletion, update, and query operations of data table records.

4)   Menu Management

DBuilder must be able to bind the generated code to a menu item, so that after the user clicks on the menu item, they can use the CRUD function generated by DBuilder. This menu must include the background menu, the front menu is not required.

5)   User Management

Users need to implement multiple roles. It must be possible to use the email address as a unique identifier for the user and as a login parameter. In the future, we will also implement support for QQ, WeChat, and Sina Weibo's interconnected login based on OAuth2.0.

6)   Permission Management

DBuilder must be able to implement three-dimensional configurability of the execution and access rights of different CRUD codes for different user roles. For example, there is a CRUD function module for article management. The user roles are divided into system administrator (SuperAdmin), administrator (Admin), and guest (Guest). Then DBuilder must be able to implement the following three-dimensional permission configuration and use it as all Module's default permissions.

Table 2-1 Module permission configuration table

用户组与权限	查看	编辑	删除	导出
SuperAdmin	√	√	√	√
Admin	√	√	√
Guest	√

7)   Site parameter configuration

DBuilder, as a web background program for a website, is also necessary to configure the global parameters of the site. These parameters include website name, keywords, contact address, friendly links, etc.

8)   Operation log

DBuilder should record the user's operation information, including the pages visited, CRUD type executed, time and other information. The log recording format supports two methods: database and file.

9) Multiple language support

DBuilder should support switching between multiple languages. At least two languages, Chinese and English, should be supported, with Chinese as the default.

10) Multiple database types supported

DBuilder should support multiple types of databases, and currently mainly supports relational databases, including mysql, MS SqlServer, oracle, PostGreSQL, etc.

2.2 Data Prototype Analysis

The entities available for extraction according to requirements include: users, user groups, data sources, code modules, menus, and relationships: permissions and logs. The meanings of entities and relationships are as follows:

• User: indicates the user who uses DBuilder;
• User group: Indicates the type grouping of users. User types should include at least three types: guest, administrator, and super administrator;
• Data source: Indicates the database configuration included in DBuilder. The configuration of a data source contains the basic parameters required to connect to a database;
• Code module: represents the code module generated by DBuilder, describing the code files and configuration;
• Menu: Represents the left menu item of DBuilder;
• Permissions: Indicates the user group’s various operating permissions for each code module;
• Log: Indicates the user's CRUD access log for each code module.

The ER diagram of entities and relationships is as follows:

Figure 2-1 ER diagram

2.3 Principle Requirements

DBuilder should be made into a high-performance, highly available CRUD generator. To this end, the design of DBuilder should comply with the following principles:

• DBuilder should be so accurate that each database field can be configured;
• There should be a prototype of a WEB backend application, so that users can quickly build a complete WEB backend application on this basis;
• DBuilder should reduce SQL operations as much as possible. If necessary, it can use caching, asynchronous and other technologies to reduce request processing logic, improve page efficiency, and reduce user waiting time;
• DBuilder must have a beautiful, concise and intuitive user interface;
• DBuilder should leave a large number of expansion interfaces, allowing users to quickly implement more complex functions through secondary development.

Chapter 3 DBuilder System Design

3.1 System Architecture

DBuilder has the following two core components: Core CRUD module and GModule. GModule has an inheritance dependency relationship on the Core CRUD module. GModule is composed of MVC Code and CRUD Config; the Core CRUD module is manually written code, and GModule is the code generated by DBuilder; the Core CRUD module implements CRUD operations, and GModule implements extended functions. The figure below shows the composition and relationship of these two components

Figure 3-1 Concept and components

The following is a detailed explanation of the concepts, components, module relationships, and Build and CRUD processes designed in the figure.

3.1.1 Core CRUD module

The Core CRUD module implements core CRUD operations. All CRUD requests to the Controller in GModule MVC are eventually transferred to the Core CRUD module for processing. The Core CRUD module will open some pre-processing and post-processing interfaces for GModule to implement. These interfaces will be reflected in Model, Controller and View.

Core CRUD module mainly includes the following files

• app/controllers/admin/AdminController.php
• app/models/BaseModel.php
• app/config/crud/admin.php
• app/views/admin/core/list.blade.php
• app/views/admin/core/form.blade.php

Core CRUD module reads GModule Configuration to implement real CRUD operations.

3.1.2 GModule

GModule (Generated Module) not only implements the Core CRUD Module interface (MVC code), but also has its own configuration file (CRUD Configuration). Each GModule represents a database table as the main table and a collection of code files with CRUD functions (including the corresponding MVC Configuration code) . For example, a GModule generated by DBuilder, the main table is the core data source user table, and the name is User, then the User GModule should contain the following code file:

• controllers/UserController.php
• models/User.php
• views/user/_list.blade.php
• views/user/_form.blade.php
• views/user/view.blade.php
• config/crud/user.php

The naming of the code file depends on the name of the GModule. Therefore, in order to ensure that the generated code files do not conflict, the name of the GModule (GModule Key, GModule Name) is used as the unique identifier of the GModule. The information of each GModule is saved in the database. A new GModule operation will create all the above code files, update related files, and insert a GModule record into the database. An update GModule operation will only update the Configuration file.

GModule consists of MVC code and CRUD Configuration code, which are explained below:

• MVC code: used to implement extended interfaces. CRUD requests should first be routed to the Controller in GModule MVC. And GModule MVC should have an inheritance relationship with the MVC code of Core CRUD Module.
• CRUD Configuration code: implements the configuration of adding, deleting, modifying and querying parameters in the GModule main table. This file is placed in the app/config/crud/ directory and is defined in the format of php array. It includes configuration of parameters such as forms, lists, views, relationships, etc. for all fields, as well as global parameter configuration.

GModule does not represent a specific module, but refers to a type of module, which can be generated by DBuilder or manually created by developers. It is mainly used to implement the interface of Core CRUD Module, mainly including the following parts

1) Controller interface

Assume that the Controller of the GModule module is A and the Controller of the Core CRUD Module is B, then A should inherit from B. The CRUD request will be routed to A first, and the actual handler is B. A will implement the following interfaces opened by B.

• beforeListExcuteQuery(&querier): This interface is called before the List query executes the query, and the passed parameter is the query reference. Used to bind special query parameters before querying.
• beforeList(&data): This interface is called after the List query is executed and before the List view is rendered. The parameters passed are view parameter references, which include the queried model collection. It is used to post-process the queried model collection, or to bind some Module-specific parameters to the list view.
• beforeEditExcuteQuery(&querier): This interface is called before the Model queryer executes the query in the Edit request, passing the queryer reference. Used to bind special parameters required to query the model.
• beforeEdit(&data): This interface is called after the Model query in Edit is executed and before the view is rendered. It passes the view parameter reference, including the model queried by the query. Used for preprocessing before rendering.
• afterSave(&model): This interface is called after saving the edit in Edit. What is passed is the model that is saved in the database and the latest database record is persisted. Used to do some complex post-cascade processing on the model.
• beforeView(data): This interface is called after the View query is queried in the View request, and the reference of the view parameters is passed. Used to preprocess the view display.

2) Model interface

The Model in the GModule MVC code also inherits from BaseModel, and can be extended by implementing some interfaces opened by the BaseModel class.

formatXXXAttribute(): This interface is used to format a certain field. This product is based on Laravel, which already has a similar interface, which is getXXXXAttribute(). However, the priority of such an interface is higher than that of the field, which brings inconvenience to development in special cases, so a similar interface is designed with a lower priority than the field itself.

3) View interface

The extension interface of the view is different from the previous two, which is mainly reflected in the subview and view block, that is, based on the view of the Core CURD module, the view component is extended. By default, the Core CRUD MVC view generates a table or form that fills the page. The View interface will provide the ability to expand page components up, down, left, and right on the table.

4) Configuration

Each GModule corresponds to a Configuration file, which contains the GModule's configuration parameters for each field of the main table, as well as layout parameters.

3.1.3 Module relationship

CRUD requests are routed to the Controller of GModule. The GModule code implements the open interface of Core CRUD MVC, and the Core CRUD Module actually implements the CRUD operation on the database. The information of each GModule should be recorded in the database table in order to give the GModule associated menus, control permissions, record operation logs, etc. The relationship between some major modules is shown in the figure below.

Figure 3-2 Module Relationship

As can be seen from Figure 2-2, the GModule management module generates a GModule A according to the user configuration. When the user's CRUD request reaches GModule A, GModule will transfer the request to Core CRUD for processing, and the Core CRUD module will then Perform CRUD operations on the database with SQL.

3.1.4 Build and CRUD process

The solution of the DBuilder project hands over the real CRUD operations to the Core CRUD Module for execution. The CRUD parameters are composed of GET or POST request parameters and GModule Configuration, and the MVC code of GModule is only to implement some of the prerequisites opened by Core CRUD MVC. Processing or post-processing interface.

Figure 2-3 is the core flow chart of DBuilder, including the process of generating Modules and processing CRUD requests. Figure 2-4 is the flow chart of generating Modules and processing CRUD requests in SximoBuilder.

Figure 3-3 DBuilder code generation and CRUD processing process

Figure 3-4 SximoBuilder code generation and CRUD processing process

Comparing the two, you can see that the biggest difference between the two is that DBuilder reuses a CRUD code instead of generating a set of CRUD codes for each Module that can be executed independently like Sximo does. The advantage of this is that it improves reusability and achieves scalability through the pre-processing/post-processing interface through Module CRUD MVC.

3.2 Core data source

Core data source is the default data source of DBuilder. Its type is mysql and the database name is dbuilder. This section will conduct detailed database design according to the "Data Prototype Analysis" section. In order to improve program performance, data source information is stored in the code file app/config/datasource.php. The file content is as follows:

'core' =>

        array (

            'driver' => 'mysql',

            'host' => 'localhost',

            'database' => 'dbuilder',

            'username' => 'root',

            'password' => 'root',

            'charset' => 'utf8',

            'collation' => 'utf8_unicode_ci',

            'prefix' => '',

            'edit' => false,

            'port' => 3306,

        ),

        // more data source

 );

'core' =>

array (

'driver' => 'mysql',

field	type	default	info
id	int	auto_increment	PRI
module_id	int
module_name	varchar(12)
parent_id		null	父菜单项
title	varchar(12)	module_title	显示名称
_order	int	0	排序字段

'host' => 'localhost',<🎜> <🎜> 'database' => 'dbuilder',<🎜> <🎜> 'username' => 'root',<🎜> <🎜> 'password' => 'root',<🎜> <🎜> 'charset' => 'utf8',<🎜> <🎜> 'collation' => 'utf8_unicode_ci',<🎜> <🎜> 'prefix' => '',<🎜> <🎜> 'edit' => false,<🎜> <🎜> 'port' => 3306,<🎜> <🎜> ),<🎜> <🎜> // more data source<🎜> <🎜> );<🎜>

<🎜>The Core data source has the following data table: <🎜> <🎜>1) d_menu table: Represents the tree menu on the left side of the background. Each menu item that can be clicked to jump must be associated with a Module. <🎜> <🎜>Table 3-1 Web backend left menu table<🎜>

field<🎜>	type<🎜>	default<🎜>	info<🎜>
<🎜>id<🎜>	<🎜>int<🎜>	<🎜>auto_increment<🎜>	<🎜>PRI<🎜>
<🎜>module_id<🎜>	<🎜>int<🎜>	<🎜> <🎜>	<🎜> <🎜>
<🎜>module_name<🎜>	<🎜>varchar(12)<🎜>	<🎜> <🎜>	<🎜> <🎜>
<🎜>parent_id<🎜>	<🎜> <🎜>	<🎜>null<🎜>	<🎜>Parent menu item<🎜>
<🎜>title<🎜>	<🎜>varchar(12)<🎜>	<🎜>module_title<🎜>	<🎜>Display name<🎜>
<🎜>_order<🎜>	<🎜>int<🎜>	<🎜>0<🎜>	<🎜>Sort field<🎜>

2) D_module table: records module information. Each record in the d_module table represents a Module generated by DBuilder.

Table 3-2 module information description

field	type	default	info
id	int	auto_increment	PRI
name	varchar(32)		UIN
title	varchar(32)		module标题
note	varchar(32)		module 说明
db_source	varchar(16)	core	数据源名称
db_table	varchar(16)		module主表
db_table_key	varchar(16)		主表PRI

field

type

default

field	type	default	info
id	int	auto_increment	PRI
username	varhcar(64)		用户名
email	varchar(64)		邮箱
password	varchar(64)		HASH
salt	varchar(64)		盐
last_login	timestamp		最后登录时间
remember_token			记住密码口令
group_id	int		组ID

info

id

field	type	default	info
id	int	auto_increment	PRI
name	varhcar(64)		组名
note	varchar(128)		组说明
level	int		组级别

int<🎜>

<🎜>auto_increment<🎜>

<🎜>PRI<🎜>

<🎜>name<🎜>

<🎜>varchar(32)<🎜>

<🎜> <🎜>

<🎜>UIN<🎜>

<🎜>title<🎜>

<🎜>varchar(32)<🎜>

<🎜> <🎜>

<🎜>module title<🎜>

<🎜>note<🎜>

<🎜>varchar(32)<🎜>

<🎜> <🎜>

<🎜>module description<🎜>

<🎜>db_source<🎜>

<🎜>varchar(16)<🎜>

<🎜>core<🎜>

<🎜>Data source name<🎜>

<🎜>db_table<🎜>

<🎜>varchar(16)<🎜>

<🎜> <🎜>

<🎜>module main table<🎜>

<🎜>db_table_key<🎜>

<🎜>varchar(16)<🎜>

<🎜> <🎜>

<🎜>Main table PRI<🎜>

<🎜>3) d_user table: Saves users who use background programs. <🎜> <🎜>Table 3-3 web background users<🎜>

<🎜>field<🎜>	<🎜>type<🎜>	<🎜>default<🎜>	<🎜>info<🎜>
<🎜>id<🎜>	<🎜>int<🎜>	<🎜>auto_increment<🎜>	<🎜>PRI<🎜>
<🎜>username<🎜>	<🎜>varhcar(64)<🎜>	<🎜> <🎜>	<🎜>Username<🎜>
<🎜>email<🎜>	<🎜>varchar(64)<🎜>	<🎜> <🎜>	<🎜>Email<🎜>
<🎜>password<🎜>	<🎜>varchar(64)<🎜>	<🎜> <🎜>	<🎜>HASH<🎜>
<🎜>salt<🎜>	<🎜>varchar(64)<🎜>	<🎜> <🎜>	<🎜>Salt<🎜>
<🎜>last_login<🎜>	<🎜>timestamp<🎜>	<🎜> <🎜>	<🎜>Last login time<🎜>
<🎜>remember_token<🎜>	<🎜> <🎜>	<🎜> <🎜>	<🎜>Remember the password<🎜>
<🎜>group_id<🎜>	<🎜>int<🎜>	<🎜> <🎜>	<🎜>Group ID<🎜>

<🎜>4) d_group table: Represents the grouping information of background users. <🎜> <🎜>Table 3-4 User grouping table<🎜>

<🎜>field<🎜>	<🎜>type<🎜>	<🎜>default<🎜>	<🎜>info<🎜>
<🎜>id<🎜>	<🎜>int<🎜>	<🎜>auto_increment<🎜>	<🎜>PRI<🎜>
<🎜>name<🎜>	<🎜>varhcar(64)<🎜>	<🎜> <🎜>	<🎜>Group name<🎜>
<🎜>note<🎜>	<🎜>varchar(128)<🎜>	<🎜> <🎜>	<🎜>Group Description<🎜>
<🎜>level<🎜>	<🎜>int<🎜>	<🎜> <🎜>	<🎜>Group Level<🎜>

5) D_group_access table: records the three-dimensional permission information of each GModule, different background user groups and various operation permissions.

Table 3-5 User group permissions table for Module

field	type	default	info
id	int	auto_increment	PRI
group_id	int		组id
module_id	int		Module模块ID
edit	int	1	可编辑
view	int	1	可查看
delete	int	1	可删除
export	int	1	可导出

field

type

default

field	type	default	info
id	int	auto_increment	PRI
user_id	int		用户id
ip_addr	varchar(15)		客户端IP
module_id	int		访问的moduleid
module_title	varchar(16)
task	varchar(16)		操作
created_at	timestamp		可导出

<🎜>info<🎜>

<🎜>id<🎜>

<🎜>int<🎜>

<🎜>auto_increment<🎜>

<🎜>PRI<🎜>

<🎜>group_id<🎜>

<🎜>int<🎜>

<🎜> <🎜>

<🎜>Group id<🎜>

<🎜>module_id<🎜>

<🎜>int<🎜>

<🎜> <🎜>

<🎜>Module module ID<🎜>

<🎜>edit<🎜>

<🎜>int<🎜>

<🎜>1<🎜>

<🎜>Editable<🎜>

<🎜>view<🎜>

<🎜>int<🎜>

<🎜>1<🎜>

<🎜>Can be viewed<🎜>

<🎜>delete<🎜>

<🎜>int<🎜>

<🎜>1<🎜>

<🎜>can be deleted<🎜>

<🎜>export<🎜>

<🎜>int<🎜>

<🎜>1<🎜>

<🎜>Exportable<🎜>

<🎜>6) D_log table: records the operation log of each user. <🎜> <🎜>Table 3-6 User operation log<🎜>

<🎜>field<🎜>	<🎜>type<🎜>	<🎜>default<🎜>	<🎜>info<🎜>
<🎜>id<🎜>	<🎜>int<🎜>	<🎜>auto_increment<🎜>	<🎜>PRI<🎜>
<🎜>user_id<🎜>	<🎜>int<🎜>	<🎜> <🎜>	<🎜>User id<🎜>
<🎜>ip_addr<🎜>	<🎜>varchar(15)<🎜>	<🎜> <🎜>	<🎜>Client IP<🎜>
<🎜>module_id<🎜>	<🎜>int<🎜>	<🎜> <🎜>	<🎜>Accessed moduleid<🎜>
<🎜>module_title<🎜>	<🎜>varchar(16)<🎜>	<🎜> <🎜>	<🎜> <🎜>
<🎜>task<🎜>	<🎜>varchar(16)<🎜>	<🎜> <🎜>	<🎜>Operation<🎜>
<🎜>created_at<🎜>	<🎜>timestamp<🎜>	<🎜> <🎜>	<🎜>Exportable<🎜>

3.3 Data source management module

DBuilder needs to support multiple data sources and multiple types of databases. Data source information is stored in the d_database table. Considering that database operations are frequent operations, if the data source information is saved in the database, each database operation will require one more data source query operation, which wastes performance. Then DBuilder should not save the data source information in the database, but in the code file. Data source management information includes data source name (the unique identifier of the data source, DBuilder’s default data source name is core), database type, address, port, database name, user name, password and other information. Because the data source management module does not perform addition, deletion, modification, and query operations on tables, the data source management module is not a GModule module. The code for this module is entirely hand-written.

3.4 GModule management module

DBuilder will use the GModule named "Module" as the user interface for generating GModule. This module is called the GModule management module. In other words, the GModule management module itself is a GModule, and the main table of the GModule is the core data. In the database table that stores GModule information in the source, change the name of GModule to "Module". GModule management module contains all code files and database records for creating, updating and deleting GModule. The creation and deletion of GModule requires updating the global GModule routing.

1) GModule routing

The GModule route is defined in an independent code file. It is a string with the GModule name divided by minus signs and all lowercase as the key (for example: the GModule name is OrderItem, then the key value is order-item), with Module The class name of the Controller class is a map dictionary of values, and the GModule routing is global.

2) GModule New & Update

Creating a new GModule will generate a record in the database, generate all module files, and update routes. The update operation only modifies the configuration file. Both new creation and update use the same editing view, which is a graphical configuration interface for GModule Configuration.

3) GModule Delete

GModule deletion will delete all GModule MVC code, delete GModule Configuration code, delete database table records, and update GModule routing.

3.5 Core CRUD module

Core CRUD module is the actual processor of DBuilder to handle CRUD requests. It consists of the following parts:

1) Parameter analysis initialization

Initialize the Model and instantiate a Module's Model object as the initial queryer. Load Module Configuration, set default values ​​for unset values, and aggregate parameters.

2) Form Form

Mainly includes new and update functions. Determine whether it is a new or update operation based on whether the primary key of the GModule main table primaryKey is set. The picture below is the process of the Form module

Figure 3-5 Form execution process

Form is divided into two parts. The first part renders the Form page for the user to fill in. The second part is saved as Form.

When rendering a Form page, you need to consider how to handle Form controls and fields with foreign key relationships. Form controls need to support types including text, text_date, text_datetime, textarea, select, radio, checkbox, file, hidden, address, and custom. Custom controls should inherit the FormControl class, and the rendering of custom controls is completed by the render method of the control. Form rendering needs to determine related fields for auxiliary loading. For example, when editing the post table, the post table has a field called category_id, which represents the column ID of the article and corresponds to the id field of the category table. At this time, you need to use select, radio, and checkbox controls to load category_id to facilitate user input. For example, if you use the select control, category.id should be used as the value of the option, and category.name should be used as the text in the option. This is also done to facilitate user input. This step has something in common with searching in List, so the code can be reused.

Form saving needs to consider the saving of some custom controls. The saving of custom controls is completed by the onSave method of the custom control class. When saving the Form, you also need to consider saving the relationship. By default, the subsidiary tables should be updated in cascade. After the user completes the input and clicks Save, the Form form has the following steps:

• Verify according to the validation rules configured in the field;
• The relationship in the Module Configuration should be judged for analysis and necessary cascading operations should be performed;
• And call the onSave method of the custom control;
• The main table data should be updated or created last;
• Jump: If the update or new creation is successful, it will jump to List, if it fails, it will jump to Form.

Form also needs to open the corresponding pre-processing and post-processing interfaces.

3) List List（Table）

List is a paging Table, which displays paging data according to the field configuration in Module Configuration. Supports list search, sorting, check deletion, export and other functions;

• Pagination display data uses the Model obtained by the InitQuerier module as the queryer, combined with paging, to query the basic data list. The paging type is full page refresh type (non-asynchronous paging);
• List search: supports fields where search is not equal to false defined in Module Configuration as search conditions. The search relationship is a logical AND relationship. And reflected on the GET parameters. The search input control is determined based on the form type of the field. Fields defined as select, radio, and checkbox controls in the Form will use the select control as the input control in the List;
• List sorting: Fields with form.sort not equal to false defined in Module Configuration are used as sortable fields. Sorting only supports sorting by a single field, and the descending order includes ascending order and descending order;
• List multi-select operations mainly support multi-select deletion and multi-select copy operations. Any deletion operation requires confirmation;
• The supported operations for each row of List data are given according to the configuration in Module Configuration. By default, three operations are supported: edit, delete, and view;
• List should also open preprocessing/postprocessing interfaces to Module CRUD MVC.

4) View View

View is temporarily based on Form and provides pre-processing and post-processing interfaces, but does not allow editing.

Chapter 4 DBuilder System Implementation

4.1 Directory structure

The code is stored in sub-directories according to concepts such as front-end resources, MVC, Configuration, and Library. A description of the main directories is given in the table below:

Table 4-2 Main directory of code

目录	作用
assets	此目录存放着各种各样的前端资源。包括bootstrap，以及自定义的css和js文件。
plugins	存放特殊前端插件的目录，比如富文本编辑器，视音频插件等等。
app/controllers/admin	存放着MVC中控制器的目录。其中，DBuilder的核心在admin目录下。
app/models	存放着MVC中模型(Model)的目录。用来做数据库查询用。
app/views	存放着MVC中视图的目录。文件名以*.blade.php的格式命名。
app/library	存放PHP辅助类，PHP库的目录。
app/config/crud	存放Module Configuration的目录。

Table of Contents

Function

assets

This directory stores various front-end resources. Includes bootstrap, as well as custom css and js files.

plugins

Directory that stores special front-end plug-ins, such as rich text editors, video and audio plug-ins, etc.

Configuration Key	类型	默认值	含义
fields	array	array()	字段列表
fields.field_name	array	array()	对field_name字段的配置
fields.field_name.label	string	UP(field_name)	显示在列表表格的表头的内容，和form控件旁边的内容
fields.field_name.form	array	array()	field_name字段的表单配置，具体参考 fields.field_name.form配置
fields.field_name.list	array	array()	field_name 字段的列表配置，具体参考 fields.field_name.list配置
fields.field_name. relation	array	array()	field_name 字段的关系

<🎜>app/controllers/admin<🎜>

<🎜> Directory that stores controllers in MVC. Among them, the core of DBuilder is in the admin directory. <🎜>

<🎜>app/models<🎜>

<🎜>The directory that stores the models in MVC. Used for database queries. <🎜>

<🎜>app/views<🎜>

<🎜>The directory that stores views in MVC. The file name is in the format *.blade.php. <🎜>

<🎜>app/library<🎜>

<🎜> Directory that stores PHP auxiliary classes and PHP libraries. <🎜>

<🎜>app/config/crud<🎜>

<🎜>The directory where Module Configuration is stored. <🎜>

<🎜>4.2 GModule configuration file<🎜> <🎜><🎜>The GModule configuration file defines the parameters of GModule. This file is saved under app/config/crud/ and is a php file named after the string obtained by serpentine segmentation of GModule Name <🎜> (for example: a The name of GModule is OrderItem, and the GModule configuration file is order_item.php). Configuration parameters are returned in array format. <🎜> <🎜>Considering the appearance of PHP arrays in tables, the parameters are expressed in the form of Key=>Value in the configuration and in the dotted form Key.Value. <🎜> <🎜>Table 4-3 root configuration<🎜>

<🎜>Configuration Key<🎜>	<🎜>Type<🎜>	<🎜>Default value<🎜>	<🎜>Meaning<🎜>
<🎜>fields<🎜>	<🎜>array<🎜>	<🎜>array()<🎜>	<🎜>Field list<🎜>
<🎜>fields.field_name<🎜>	<🎜>array<🎜>	<🎜>array()<🎜>	<🎜>Configuration of field_name field<🎜>
<🎜>fields.field_name.label<🎜>	<🎜>string<🎜>	<🎜>UP(field_name)<🎜>	<🎜>The content displayed in the header of the list table and the content next to the form control<🎜>
<🎜>fields.field_name.form<🎜>	<🎜>array<🎜>	<🎜>array()<🎜>	<🎜>Form configuration of field_name field, please refer to <🎜> for details <🎜>fields.field_name.form configuration<🎜>
<🎜>fields.field_name.list<🎜>	<🎜>array<🎜>	<🎜>array()<🎜>	<🎜>List configuration of field_name field, please refer to <🎜> for details <🎜>fields.field_name.list configuration<🎜> <🎜> <🎜>
<🎜>fields.field_name. relation<🎜>	<🎜>array<🎜>	<🎜>array()<🎜>	<🎜>Relationship between field_name fields<🎜>

The form configuration description of each field in Table 4-3 is as shown in the following table:

Table 4-4 fields.field_name.form configuration

Configuration Key	值类型	默认	含义
type	string	text	Form控件类型
show	bool	true	是否出现在表单
hidden	bool	false	是否以隐藏的空间在表单中
rule	string	required	验证规则
ajax_validate	bool	false	是否异步验证
placeholder	string		控件中的提示

Configuration Key

Value type

Default

Configuration Key	值类型	默认	含义
show	bool	true	是否出现在表单
sort	bool	true	字段是否可以排序，默认可排序
search	bool,array	array()	是否可搜索以及搜索规则
	string		控件中的提示

Meaning

type

Configuration Key	值类型	默认	含义
table	string		关联表
foreign_key	string	id	对应关联表里的字段
show	string		关联表里的一个字段，当需要转义时，将用该字段代替field_nae字段显示
as	string	table_show	转义查询出的值用哪个字段表示，主要为了防止主表和关联表有重复字段

string<🎜>

<🎜>text<🎜>

<🎜>Form control type<🎜>

<🎜>show<🎜>

<🎜>bool<🎜>

<🎜>true<🎜>

<🎜>Whether it appears in the form<🎜>

<🎜>hidden<🎜>

<🎜>bool<🎜>

<🎜>false<🎜>

<🎜>Whether to hide the space in the form<🎜>

<🎜>rule<🎜>

<🎜>string<🎜>

<🎜>required<🎜>

<🎜>Validation Rules<🎜>

<🎜>ajax_validate<🎜>

<🎜>bool<🎜>

<🎜>false<🎜>

<🎜>Whether to verify asynchronously<🎜>

<🎜>placeholder<🎜>

<🎜>string<🎜>

<🎜> <🎜>

<🎜>Prompts in controls<🎜>

<🎜>The list configuration description of each field in Table 4-3 is as shown in the following table: <🎜> <🎜>Table 4-5 fields.field_name.list configuration<🎜>

<🎜>Configuration Key<🎜>	<🎜>Value type<🎜>	<🎜>Default<🎜>	<🎜>Meaning<🎜>
<🎜>show<🎜>	<🎜>bool<🎜>	<🎜>true<🎜>	<🎜>Whether it appears in the form<🎜>
<🎜>sort<🎜>	<🎜>bool<🎜>	<🎜>true<🎜>	<🎜>Whether the field can be sorted, it can be sorted by default<🎜>
<🎜>search<🎜>	<🎜>bool,array<🎜>	<🎜>array()<🎜>	<🎜>Whether it is searchable and search rules<🎜>
<🎜> <🎜>	<🎜>string<🎜>	<🎜> <🎜>	<🎜>Prompts in controls<🎜>

<🎜>The relationship configuration description of each field in Table 4-3 is as shown in the following table: <🎜> <🎜>Table 4-6 fields.field_name.relation configuration<🎜>

<🎜>Configuration Key<🎜>	<🎜>Value type<🎜>	<🎜>Default<🎜>	<🎜>Meaning<🎜>
<🎜>table<🎜>	<🎜>string<🎜>	<🎜> <🎜>	<🎜>Association table<🎜>
<🎜>foreign_key<🎜>	<🎜>string<🎜>	<🎜>id<🎜>	<🎜>Corresponds to the fields in the related table<🎜>
<🎜>show<🎜>	<🎜>string<🎜>	<🎜> <🎜>	<🎜>A field in the associated table. When escaping is required, this field will be used to display instead of the field_nae field<🎜>
<🎜>as<🎜>	<🎜>string<🎜>	<🎜>table_show<🎜>	<🎜>Which field should be used to represent the value from the escaped query? This is mainly to prevent duplicate fields in the main table and related tables<🎜>

The following is the Configuration file of a GModule named post

1 return array ( 2 'data_source' => 'core', 3 'table' => 'post', 4 'fields' => 5 array ( 6 'id' => 7 array ( 8 'label' => 'ID', 9 'form' => 10 array ( 11 'show' => true, 12 'hidden' => true, 13 'type' => 'text', 14 'rule' => 'required', 15 'ajax_validate' => false, 16 'placeholder' => '', 17 ), 18 'list' => 19 array ( 20 'show' => true, 21 'sort' => true, 22 'search' => '=', 23 'lookup' => false, 24 ), 25 'relation' => 26 array ( 27 'type' => '', 28 'table' => '', 29 'foreign_key' => '', 30 'show' => '', 31 'as' => '', 32 ), 33 ), 34 'title' => 35 array ( 36 'label' => '标题', 37 'form' => 38 array ( 39 'show' => true, 40 'hidden' => false, 41 'type' => 'text', 42 'rule' => 'required', 43 'ajax_validate' => false, 44 'placeholder' => '', 45 ), 46 'list' => 47 array ( 48 'show' => true, 49 'sort' => true, 50 'search' => '=', 51 'lookup' => false, 52 ), 53 'relation' => 54 array ( 55 'type' => '', 56 'table' => '', 57 'foreign_key' => '', 58 'show' => '', 59 'as' => '', 60 ), 61 ), 62 'short' => 63 array ( 64 'label' => '摘要', 65 'form' => 66 array ( 67 'show' => true, 68 'hidden' => false, 69 'type' => 'textarea', 70 'rule' => 'required', 71 'ajax_validate' => false, 72 'placeholder' => '', 73 ), 74 'list' => 75 array ( 76 'show' => true, 77 'sort' => true, 78 'search' => '=', 79 'lookup' => false, 80 ), 81 'relation' => 82 array ( 83 'type' => '', 84 'table' => 'category', 85 'foreign_key' => 'id', 86 'show' => 'id', 87 'as' => '', 88 ), 89 ), 90 'content' => 91 array ( 92 'label' => '正文', 93 'form' => 94 array ( 95 'show' => true, 96'hidden' => false, 97 'type' => 'wysiwyg', 98 'rule' => 'required', 99 'ajax_validate' => false, 100 'placeholder' => '', 101 ), 102 'list' => 103 array ( 104 'show' => false, 105 'sort' => true, 106 'search' => '=', 107 'lookup' => false, 108 ), 109 'relation' => 110 array ( 111 'type' => '', 112 'table' => 'category', 113 'foreign_key' => 'id', 114 'show' => 'id', 115 'as' => '', 116 ), 117 ), 118 'view_ct' => 119 array ( 120 'label' => '查看次数', 121 'form' => 122 array ( 123 'show' => false, 124 'hidden' => false, 125 'type' => 'text', 126 'rule' => 'required', 127 'ajax_validate' => false, 128 'placeholder' => '', 129 ), 130 'list' => 131 array ( 132 'show' => true, 133 'sort' => true, 134 'search' => '=', 135 'lookup' => false, 136 ), 137 'relation' => 138 array ( 139 'type' => '', 140 'table' => '', 141 'foreign_key' => '', 142 'show' => '', 143 'as' => '', 144 ), 145 ), 146 'created_at' => 147 array ( 148 'label' => '创建时间', 149 'form' => 150 array ( 151 'show' => false, 152 'hidden' => false, 153 'type' => 'text', 154 'rule' => 'required', 155 'ajax_validate' => false, 156 'placeholder' => '', 157 ), 158 'list' => 159 array ( 160 'show' => false, 161 'sort' => true, 162 'search' => '=', 163 'lookup' => false, 164 ), 165 'relation' => 166 array ( 167 'type' => '', 168 'table' => '', 169 'foreign_key' => '', 170 'show' => '', 171 'as' => '', 172 ), 173 ), 174 'updated_at' => 175 array ( 176 'label' => '更新时间', 177 'form' => 178 array ( 179 'show' => false, 180 'hidden' => false, 181 'type' => 'text', 182 'rule' => 'required', 183 'ajax_validate' => false, 184 'placeholder' => '', 185 ), 186 'list' => 187 array ( 188 'show' => true, 189 'sort' => true, 190 'search' => '=', 191 'lookup' => false, 192 ), 193 'relation' => 194 array ( 195 'type' => '', 196'table' => '', 197 'foreign_key' => '', 198 'show' => '', 199 'as' => '', 200 ), 201 ), 202 'category_id' => 203 array ( 204 'label' => '栏目', 205 'form' => 206 array ( 207 'show' => true, 208 'hidden' => false, 209 'type' => 'select', 210 'rule' => 'required', 211 'ajax_validate' => false, 212 'placeholder' => '', 213 ), 214 'list' => 215 array ( 216 'show' => true, 217 'sort' => true, 218 'search' => '=', 219 'lookup' => false, 220 ), 221 'relation' => 222 array ( 223 'type' => 'belongsTo', 224 'table' => 'category', 225 'foreign_key' => 'id', 226 'show' => 'title', 227 'as' => 'category_title', 228 ), 229 ), 230 ), 231 'list_options' => 232 array ( 233 'page' => 10, 234 'create' => true, 235 'update' => true, 236 'delete' => true, 237 ), 238 'form_options' => 239 array ( 240 'layout' => 241 array ( 242 'cols' => 12, 243 'label_cols' => 1, 244 'input_cols' => 11, 245 ), 246 ), 247 'relations' => 248 array ( 249 'id' => 250 array ( 251 'type' => '', 252 'table' => '', 253 'foreign_key' => '', 254 'show' => '', 255 'as' => '', 256 ), 257 'title' => 258 array ( 259 'type' => '', 260 'table' => '', 261 'foreign_key' => '', 262 'show' => '', 263 'as' => '', 264 ), 265 'short' => 266 array ( 267 'type' => '', 268 'table' => '', 269 'foreign_key' => '', 270 'show' => '', 271 'as' => '', 272 ), 273 'content' => 274 array ( 275 'type' => '', 276 'table' => '', 277 'foreign_key' => '', 278 'show' => '', 279 'as' => '', 280 ), 281 'view_ct' => 282 array ( 283 'type' => '', 284 'table' => '', 285 'foreign_key' => '', 286 'show' => '', 287 'as' => '', 288 ), 289 'created_at' => 290 array ( 291 'type' => '', 292 'table' => '', 293 'foreign_key' => '', 294 'show' => '', 295 'as' => '', 296 ), 297 'updated_at' => 298 array ( 299 'type' => '', 300 'table' => '', 301 'foreign_key' => '', 302 'show' => '', 303 'as' => '', 304 ), 305 'category_id' => 306 array ( 307 'type' => '', 308 'table' => '', 309 'foreign_key' => '', 310 'show' => '', 311 'as' => '', 312 ), 313 ), 314 ); GModule Configuration

4.3 Data source management module implementation

The data source management module completes the configuration of the app/config/datasource.php file based on the web interface. Contains data source list page, data source creation and editing page.

• Data source list page: The corresponding request path is admin/data-source/list. This page reads the DataSource file and renders the List (table) page. The password is not displayed.
• Data source editing page: The corresponding request path is admin/data-source/edit, the GET request reads the DataSource price inquiry content and renders the form, and the POST request outputs the data source parameters to the DataSource file. This page provides a test connection function. The implementation method is to connect to the database based on the data source parameters of the form and execute a SQL statement. Whether the connection is successful depends on whether the SQL is executed successfully.

The core controller code that implements data source management is placed in the DataSourceController.php file.

1 php 2 /** 3 * Created by PhpStorm. 4 * User: lvyahui 5 * Date: 2016/5/12 6 * Time: 15:35 7 */ 8 9 namespace admin; 10 11 use BaseModel; 12 use IlluminateSupportFacadesConfig; 13 use IlluminateSupportFacadesRedirect; 14 use SiteHelpers; 15 use IlluminateSupportFacadesResponse; 16 use IlluminateSupportFacadesInput; 17 18 use PDOException; 19 use PDO; 20 class DataSourceController extends AdminController 21 { 22 /** 23 * 呈现数据源列表 24 */ 25 public function getList() 26 { 27 $datasources = SiteHelpers::loadDataSources(); 28 $this->makeView(array( 29 'datasources' => $datasources, 30 )); 31 } 32 33 /** 34 * 异步加载某数据源的所有数据表 35 * @return IlluminateHttpJsonResponse 36 */ 37 public function getTables() 38 { 39 $dataSourceName = Input::get("data_source"); 40 $dataSources = SiteHelpers::loadDataSources(); 41 42 $dataSource = $dataSources[$dataSourceName]; 43 $tables = BaseModel::getTableList($dataSource['database'], $dataSourceName); 44 return Response::json(array( 45 'success' => true, 46 'data' => array( 47 'tables' => $tables, 48 'selected' => Input::get('table'), 49 ), 50 )); 51 } 52 53 /** 54 * 呈现数据源编辑或者新建FORM 55 * @param null $slug 56 */ 57 public function getEdit($slug = null) 58 { 59 $dataSource = null; 60 if ($slug) { 61 // 更新 62 $dataSource = $slug === 'core' ? Config::get('database.connections.core') 63 : Config::get('datasource.' . $slug); 64 $dataSource['name'] = $slug; 65 }else { 66 // 新建 67 $dataSource = array( 68 'name' => '', 69 'driver' => 'mysql', 70 'host' => 'localhost', 71 'port' => 3306, 72 'database' => '', 73 'username' => 'root', 74 'password' => '', 75 'charset' => 'utf8', 76 'collation' => 'utf8_unicode_ci' 77 ); 78 } 79 80 $this->makeView(array( 81 'dataSource' => $dataSource 82 )); 83 } 84 85 /** 86 * 测试数据源连接是否可靠 87 * @return IlluminateHttpJsonResponse 88 */ 89 public function postTest(){ 90 $success = true; 91 try{ 92 $dsn = Input::get('driver').':'.Input::get('host').':'.Input::get('port').';dbname='.Input::get('database'); 93 $dbh = new PDO($dsn,Input::get('username'),Input::get('password')); 94 // $connection = new Connection($dbh,Input::get('database')); 95 // $key = md5(date("Y-m-d H:i:s")); 96 // DB::addConnection($key,$connection); 97 // if(!DB::connection($key)->getDatabaseName()){ 98 // $success = false; 99 // } 100 $dbh = null; 101 }catch(PDOException $e){ 102 $success = false; 103 } 104 return Response::json(array( 105 'success' => $success, 106 )); 107 } 108 109 /** 110 * 保存编辑好的数据源信息 111 * @param null $primaryKeyValue 112 * @return mixed 113 */ 114 public function postEdit($primaryKeyValue = null) 115 { 116 $dataSources = SiteHelpers::loadDataSources(); 117 $name = Input::get('name'); 118 $dataSources[$name] = Input::all(); 119 SiteHelpers::saveDataSources($dataSources); 120 121 return Redirect::action(get_class($this).'@getList'); 122 } 123 124 125 public function getTableFields(){ 126 $connection = Input::get('connection'); 127 $table = Input::get('table'); 128 129 $rawFields = BaseModel::getTableColumns($table,$connection); 130 131 $fields = array(); 132 $pri = null; 133 foreach($rawFields as $field){ 134 if($field->Key === 'PRI'){ 135 $pri = $field->Field; 136 }137 $fields