View-based addition, deletion, modification, and query operations (subverts traditional thinking), and adds, deletes, and subverts traditions

View-based addition, deletion, modification, and query operations (subverts traditional thinking), and adds, deletes, and subverts traditions

View is a powerful and useful function provided by relational databases. It provides data extraction, reorganization, and separation technology based on basic tables (compared to virtual tables in views.

By extracting and reorganizing data from one or more basic tables, the view re-presents the data as expected.

It should be noted that the main purpose of a view is to reorganize the data of multiple basic tables to be displayed in a new way. The focus is on data presentation and does not involve the addition, deletion, and modification functions. (Another major function is data isolation)

For different databases on the current market, addition, deletion, and modification of views are not supported, or support is poor. There are many constraints.

Some people have said that product functions are limited, user needs are unlimited, and truth is true. I met customers with unlimited demands.

Let's talk about the general requirements of users:

Our product is an intelligent development platform. Customers can define forms and processes in a visualized manner, and quickly develop an information management system using a zero-coding method. This method runs well until one day.

Customer: we have a system with a c/s structure. If you want to change it to a B/s structure, can you use your platform?

Me: Yes, very fast.

Customer: after reading your video introduction, we basically use a form and a data table, but sometimes a form in our system corresponds to several data tables. What should we do?

Me: Use the view function to define a form.

Customer: I tried it. The view function can only be viewed, but cannot be added or modified.

..... (Indeed)

In fact, in product R & D and use, customers have always put forward such requirements. Therefore, many customers have been lost, so they have to think about it. Therefore, a new view operation concept was born.

View-based addition, deletion, modification, and query operations

• Preface:

In this article, all operations are completed in a graphical interface, and no programming is involved. Therefore, even if you do not understand the program, it does not matter.

• Basic Concepts

• • View: you do not need to explain more, but you do not know what you know.
  • Basic table: the basic table in this article is different from the basic table you understand. What you understand or the basic table mentioned in the book refers to the actual physical data table. The basic table in this article refers to the actual physical table or view on which the view depends. (A little detour, because the view can re-reorganize data through the "View" function ). In this article, as long as the table or view referenced by the view is called the basic table
  • Physical table: Real tables in the database
• Scenario

A view-based addition, deletion, and modification operation does not provide an interface tool for you to input SQL statements. Instead, it provides a graphical interface to customize the operation interface through view definitions, add, modify, delete, and query functions on the operation interface. When you add, delete, modify, and query views, the view analysis engine is run in the background. All view operations are performed by the view engine.

• Implementation Principle

The principle is not complex. In a single sentence, all addition, deletion, modification, and query operations on views are converted to the basic table corresponding to the view through the view engine and form engine (if the basic table itself is also a view, recursive conversion is performed until the last physical table is found ).

All truth is simple and clear, but it is really complicated to prove it. This is true for the view engine and form engine. Especially when there is a foreign key relationship between the basic tables corresponding to the view, it is necessary to maintain the foreign key relationship (add at the same time, add at the same time.

• Graphic Interface

1. view definition

A wizard view is provided here. If you are familiar with SQL statements, you can directly write the SQL view.

Step 1: Select base table

　　　　　　

Step 2: select required fields

　　　　　　

Step 3: Set the relationship between tables: the system has already helped you to associate the tables of the selected base table. You can modify the relationship again.

　　　　　　

2. Generate a View Interface (Design Drawing)

After the view SQL statement is built, the view can be generated. The system will do two parts.

One is to generate a real view in the database based on the input view SQL statement.

The other is to generate an interface for adding, deleting, modifying, and querying this view.

The following is an automatically generated Design Interface (editing interface)

　　　　　　

3. View Interface Adjustment

You can adjust it to a better-looking interface on the design interface automatically generated by the system. Here, no adjustment is made and the format generated by the system is used directly.

After the page is automatically generated, you can perform operations on the view editing page.

4. List

This is the automatically generated View list interface. You can use this interface to add, delete, modify, query, export, and Count View data.

　　　　　　　　

5. Add a record

　　　　　　　　

Here, we need to note that the created view obtains data from two basic tables, one is the username and name of the user table, and the other is the department name of the department table. Therefore, when adding data, you need to take values from the current interface, and then add the values to the corresponding department table and user table respectively, at the same time, set the association relationship between the two data items ("which department the user belongs)

6. Modify records

The modification interface is the same as the new interface. When you modify the interface, you also need to update the data modified by the user to the corresponding physical table.

7. delete records

Note that When deleting a table, only the data of the master table of this view is deleted (in this demonstration case, the user table is the master table of this view, you cannot delete data from a department table. In other cases, if there is no association (primary or foreign key) between the basic tables that constitute the view, all the data of the basic physical table will be deleted at the same time.

• Technical details (to be continued)