Read the database information to build the remarks of the View Field to facilitate program code generation and view program code

Read the database information to build the remarks of the View Field to facilitate program code generation and view program code

In many cases, we need a quick code generation tool to improve the development efficiency, many of the information in the code generation tool is the completion of object table information by reading the table, view, and other metadata of the database. With this information, you can add Chinese comments for Attribute fields in the common entity-class code, or when the Winform or Web interface is quickly generated, You can edit the attribute fields in the query box or interface, serves as a tag prompt and other processing information. However, in general, there is no comment on the view, but there is a certain relationship between the view and the table. Although different database systems do not necessarily match their information, however, we can find out their mappings and add remarks to the View fields. This is the idea and practice described in this article, this article describes how to obtain the remarks of fields in the view by introducing the implementation of the Code Generation Tool Databae2Sharp.

1. View information processing of SQL Server databases

To demonstrate view processing, we need to create two tables and view information. First, we need to create a customer information table, as shown below.

We want the Creator field in the T_Customer table to be associated with the user table, and then add a view. The view code is as follows.

SELECT     dbo.T_Customer.ID, dbo.T_Customer.Name, dbo.T_Customer.Age, dbo.T_Customer.Creator, dbo.T_Customer.CreateTime, dbo.Users.UserNameFROM         dbo.T_Customer INNER JOINdbo.Users ON dbo.T_Customer.Creator = dbo.Users.ID

That is, to list the T_Customer field and the Username field in an Users table, it is equivalent to associating the views of two tables. The data obtained from this table is as follows.

Now, we have prepared the prerequisites. The rest is how to obtain the information of these views.

In SQLServer, if we need to obtain the user view information, we can obtain it through the system view, as shown below.

This is the view name and view SQL code. If we need to obtain the view field information in detail, this is not enough. We can further list the relationship between the user view and the specific table based on The View information, as shown below.

We can see the one-to-one correspondence between this view field and the table field, that is, we can use the field information of the specific table, including the remarks we are very concerned about here.

For example, based on these principles, the view directly extracts the remarks from the database table fields to achieve automatic extraction of the view remarks field, reducing the complexity and inconvenience of manual settings.

 

2. Processing of Oracle Database view information

The processing of View Field Information in Oracle is different from that in SQL Server, because it is difficult to find view information in the one-to-one correspondence between View fields and table fields, so it is a bit difficult.

In Oracle, we first prepare a T_Customer table and basic information such as views.

Create a view and a user table at the same time. The same way is done in SQL Server. The SQL statement is as follows.

create or replace view view_customer asselect u.fullname, t.id, t.name, t.age, t.creator, t.createtime from t_customer t inner join t_acl_user u on t.creator = u.id;

We can use the System View ALL_TAB_COLUMNS or DBA_TAB_COLUMNS of Oracle to obtain the field information of the view, as shown below.

However, unfortunately, the view information does not have any intersection with the view associated table, and the corresponding table information cannot be obtained. How can we get the information of the view corresponding table, the answer is that you can use the ALL_DEPENDENCIES view, as shown in.

However, here we only get the corresponding table, which is not associated with the field, but this is basically the same, because we can get the view field and the associated table contained in this view, the field information is basically locked, unless many fields in the two tables have the same name, which is difficult to process.

In combination with the following SQL statements, we can obtain the table and field information, as shown below.

select distinct table_name, column_name from all_tab_columns where table_name in  (select referenced_name from all_dependencies where name='VIEW_CUSTOMER' and type = 'VIEW' and referenced_type = 'TABLE')    and column_name in (select column_name from all_tab_columns where table_name='VIEW_CUSTOMER') order by table_name; 

In this way, the field information of the table corresponding to the view is almost the same as its remarks. Finally, let's look at the implementation effect based on Oracle.

We can see that the Code tool Database2Sharp automatically matches the Corresponding remarks from the corresponding fields of the table based on the logical relationship described above, this reduces the complexity of manual setting of View Field remarks, improving the speed and efficiency of view code generation and interface generation.

The above view remark extraction logic can be applied to our own business processing to achieve more extensive application.

The above view remark extraction requirements come from a customer's suggestion, which is very good. I would like to thank him for his contribution to this detail.