Multi-layer database development 1: Design database applications

Chapter 1 design database applications

Database applications allow users to interact with information stored in the database. The database provides an information structure for different applications to share.
Delphi 4 supports relational databases. Relational databases organize information in the form of rows and columns, that is, tables. When designing a database application, you must understand the data structure, in this way, an appropriate user interface can be designed to display data in the database and allow users to enter new data or modify existing data.
1.1 Use
The component on the "Data Access" page of the database component option board is used to read and write the database. These components use the BDE (borland database engine) to access information in the database.
Different Versions of Delphi 4 contain different database drivers. However, all versions contain drivers for accessing the local database, while the client/server and enterprise versions also contain SQL links for accessing the remote database. Whether a local database is used or a remote database depends on several factors, such as how much data is stored in a table, how many users need to access the database at the same time, and what are the database performance requirements.
1.1.1 local database and remote database
The local database is located on the local disk or LAN. If several users access the database at the same time, the local database adopts a file-based locking policy. Therefore, the local database is also called a file-based database.
Because local databases are often in the same system as database applications, accessing local databases is faster than accessing remote databases.
The data that can be stored in the local database is not much data that can be stored in the remote database. This must be taken into account when you choose to use the local database or remote database.
Applications that use local databases are also called single-tier applications because databases and applications are in the same file system.
Typical local databases include paradox, dBase, Foxpro, and access.
The remote database is usually located on a remote computer. You can use structured querylanguage to access data in the remote database, remote databases are also called SQL servers or RDBMS (remote database management system ).
Remote databases are suitable for simultaneous access by several users. Unlike local databases based on file locking policies, remote databases provide transaction-based multi-user support.
The remote database can store much more data than the local database. Sometimes the data is not stored on one server, but distributed on several servers.
Applications that use remote databases are called two-tier or multi-tier applications because databases and applications are located in systems (layers) that are independent of each other.
Typical SQL servers include Interbase, Oracle, Sybase, Informix, Microsoft sqlserver, and DB2.
1.1.2 database security
Databases often contain sensitive information. To protect this information, different databases have different protection policies. Some databases such as Paradox and DBASE only provide table-level or field-level security protection. when a user attempts to access a protected table, he must enter a password. When the password is recognized, only allowed fields can be seen.
Most SQL servers require the user to enter the user name and password. Once the user successfully logs on to the server, the table he can see and the operations he can perform are related to the user name and password he entered.
When designing a database application, you must consider the security measures required by the database server. For example, if you do not want a user to enter a password, you can either use a database without a password or provide the user name and password in the program. However, providing usernames and passwords in programs is easy to leak.
If you need to enter a password, you must also consider when to enter the password. If you are using a local database but want to smoothly transition to a large SQL database in the future, it is best to prompt the user to enter a password before opening a table, although you may not need a password now.
Some servers may require multiple passwords. To simplify user operations, you can only require the user to enter one master password, and the other passwords are automatically provided by the program.
In multi-layer Client/Server Applications, different security modes may be used at the same time. For example, the access middle layer can be controlled by using CORBA or MTS, and the middle layer can be used to process the details of logon to a remote database.
1.1.3 transactions
A transaction is actually a group of actions that must be successfully executed before one or more tables are committed. If an action fails, all actions will be rolled back (UNDO ).
Most local databases do not support transactions, but the BDE driver still provides limited transaction processing capabilities. MySQL Databases and ODBC-compatible databases provide the ability to process transactions.
1.1.4 Data Dictionary
Applications have the right to access the data dictionary regardless of the local database or remote database. The data dictionary provides a customizable storage area that does not depend on the application. In this area, you can create an extended field attribute set and describe the content and appearance of the data.
For example, if you want to develop financial software frequently, you can create several special field property sets to display the amount in different formats. When you create a dataset during the design period, you do not need to manually set the display format of the amount field with the object observer. You only need to select an appropriate attribute set from the data dictionary, share the property of the amount field in the current dataset. The use of data dictionaries ensures consistent data appearance.
In the client/server environment, the data dictionary can be located on a remote server.
The drintf unit in the .. delphi4/lib directory provides a programming interface for accessing the data dictionary.
1.1.5 integrity verification, stored procedures and triggers
All relational databases provide data storage and operation functions. In addition, some databases provide functions that help ensure data integrity.
First, integrity verification. This function provides a mechanism to prevent master/detail relationships between two tables from being interrupted. When you try to delete a field in the master table, if this field is deleted, an isolated record will appear in the detail table. The integrity verification function cannot delete this field, you can also delete isolated records.
The second is the stored procedure. Stored procedures are actually a set of SQL statements placed on the SQL server. These SQL statements can execute database-related tasks and then return the execution results (record set ). Trigger. A trigger is also a set of SQL statements that are triggered and executed in response to a command.
1.2 architecture of database applications
A database application is logically composed of two parts: a database access link and a user interface. This is the architecture of the database application.
1.2.1 select an appropriate architecture
We recommend that you separate the components that implement the data access link from those that implement the user interface. All data access components should be placed on the data module to ensure that the application has a consistent user interface. If you add the designed data modules and forms to the object library, you do not have to start from scratch when creating a new database application. This not only improves programming efficiency, it also ensures that the program has a consistent style.
The architecture of database applications depends on whether a local database is used or a remote database, the number of users accessing the database at the same time, and the types of information to be stored in the database.
If the information in the database does not need to be shared among several users, we recommend that you use a local database to achieve faster access speeds without buying expensive servers. However, the data capacity that the local database can store is limited.
If you need to store a lot of information, you 'd better switch to a remote database. However, the two-layer architecture requires the support of SQL links, which is only included in the Client/Server and enterprise versions of Delphi 4.
If there is a complex relationship between the table and the table information, or the number of users increases, we recommend that you consider a multi-layer architecture. Compared with two-tier applications. Multi-tier applications have an intermediate layer, which is used to centrally process application logic. In this way, different customer programs can use the same data and ensure that the data logic is consistent. At the same time, the customer program can be relatively small, because a considerable part of the work is done by the middle layer, which is called a "thin" customer. Thin customers are easier to install, configure, and maintain because it does not need to contain database access links and does not need BDE. Another advantage of using a multi-layer architecture is that data processing tasks can be distributed across several different systems. Of course, only the Client/Server and enterprise versions support multi-layer architecture.
However, the more layers, the higher the development difficulty and cost. Therefore, when developing database applications, it is best to start from a single layer. As data and users increase, and then smoothly transitioned to the multi-layer architecture. The key here is to take into account the scalability of the architecture at the beginning to maximize code reuse and use previous investments.
1.2.2 scalability
The structure of BDE and the use of data modules make scalability possible. You can separate the user interface from the data access link, as shown in Figure 1.1.
The form is mainly used to implement the user interface, and its main component is the data control. The data module is mainly used to implement data access links. In other words, it is to introduce datasets. The dataset and data control are connected through the tdatasource component. The advantage of separating the user interface from the data access link is that when the application is later transitioned to a multi-layer architecture, only the dataset component on the data module needs to be modified, and the user interface does not need to be changed.
However, some user interfaces may need to be changed accordingly. For example, different databases have different security policies. Some databases require logon, while some databases do not.
BDE itself is scalable. It is very easy to transition a single-tier BDE-based application to two-tier applications, as long as you modify the dataset to connect to an SQL Server.
It is easy to transition a single-tier Application Based on the tclientdataset component to a multi-tier application, because tclientdataset also supports data access from files and data access through the iprovider interface.
If you plan to transition to a three-tier architecture, You can first design a single or two-tier architecture. In addition to separating the user interface, you must also separate the application logic, because the application logic will eventually be placed on the intermediate layer, that is, the application server. When designing the user interface, you can temporarily use the local database to provide data, and then use tclientdataset to 
Figure 1.2 architecture of a single-tier Database Application
1.2.3 single-layer database applications
In single-tier database applications, applications and databases share the same file system. They use local databases or files to access data.
A single-tier database application contains both the user interface and data access mechanism (either through BDE or through files ). Figure 1.2 shows the architecture of a single-tier database application. It can be seen that data can be obtained from the local database through the BDE-based dataset component, or the data can be obtained from the file through the tclientdataset component. One thing in common is that they all provide data to the user interface through the tdatasource component.
1.2.4 two-tier database applications
In two-tier database applications, the customer program provides a user interface to  from the remote database server through BDE. Figure 1.3 shows the architecture of a two-tier database application.
Figure 1.3 architecture of two-tier database applications
In this mode, all applications are customers, and customers exchange data with remote database servers through BDE. A server can simultaneously process requests from many customers, coordinate access and update data.
1.2.5 multi-layer database applications
In multi-tier database applications, customer programs, application servers, and remote servers are distributed on different machines. The customer program mainly provides a user interface, which can request data from the application server and apply for updating data. Then, the application server (also called remote data broker) requests data from the remote database server and applies for updating data. Figure 1.4 shows the architecture of multi-tier database applications.
Figure 1.4 architecture of multi-tier database applications
Delphi 4 can be used to create a customer program or an application server. The client program communicates with the application server through the iprovider interface. The communication protocol can be TCP/IP, DCOM, MTS, or CORBA. The communication protocol is related to the connection component of the customer program and the data module on the application server.
The application server uses the iprovider interface in several ways. If the application server contains the tdatasetprovider component or tprovider component, the iprovider interface is provided by the two components. If the application server does not have the tdatasetprovider and tprovider components, the iprovider interface is provided by the BDE-based dataset component. The advantage of using the tdatasetprovider component or the tprovider component to provide the iprovider interface is that the iprovider interface can be controlled. However, in either case, the iprovider interface can transmit data between the client program and the application server.
In the multi-layer mode, several customers may communicate with an application server at the same time. The application server actually acts as a gateway.
1.3 Design User Interface
The component (also called the Data Control) on the "Data Controls" page of the component palette is used to display the data of the database, and allows the user to edit the data and save it to the database. The data control forms the user interface (UI) of the database application ).
The data control is connected to the database through the tdatasource component. The tdatasource component is like a pipe between the user interface and the database. On the same form, several data controls can be connected to the same tdatasource component, which can be synchronized because the data control always displays the data of the current record. The tdatasource component is generally placed on the data module, which is separated from the user interface.
There are several data controls. The data control to be used depends on the type of the data to be displayed. It also depends on how the information is organized, how the user views the information, and how the user edits the data.
1.3.1 display single and multiple records
In many cases, at the same time, the application only needs to display one record, that is, the data of the current record. In fact, most data controls on the component option board are designed to display the data of the current record, such as the tdbtext component.
To display multiple records at the same time, use the TDBGrid component or the tdbctrlgrid component. The two components can display multiple records or multiple fields.
The master/detail relationship can be established between two tables. Correspondingly, the customer program can use a tdbtext component to display a field in the master table, and use a TDBGrid component to display multiple records in the detail table.
1.3.2 analyze data
Some database applications do not directly display the raw data in the database to the user, but analyze and make statistics on the data and then display it in an appropriate way, this will help you draw conclusions.
The "Data Controls" page of the component palette contains a tdbchart component that analyzes and displays data in charts. If you purchased the Client/Server version of Delphi 4, the "demo-cube" page is displayed on the component option board. The components on this page can perform multidimensional analysis and statistics on the data and display the data in the form of a grid or chart.
1.3.3 select the data to be displayed
A database application usually only cares about a portion of the data in the database. For example, some programs only care about some fields, while some programs only care about some records.
As for how to retrieve the concerned data, it depends on the dataset component you use. A database application may use several dataset components. Delphi 4 supports six types of datasets.
TTable component. Logically represents a table. You can create a permanent field object to adjust the field appearance. You can add lookup fields and calculated fields. You can set filter conditions and ranges to select records.
Tquery component. You can query databases and return qualified records.
Tstoredproc component. Used to execute stored procedures on the SQL server. Stored procedures can also return records that meet specific conditions.
Tclientdataset component. You can retrieve data from the application server and copy a copy in the local memory. For this reason, the number of records that tclientdataset can work simultaneously is limited. The customer program created with tclientdataset can be very small, because it does not need to depend on BDE, but only needs the dbclient. dll file. Tclientdataset can retrieve data from the application server or from files.
Tnestedtable component. Used to access records in a nested table. Although Delphi 4 cannot directly create an oracle8 table, it can display and edit the data in the nested table.
Custom dataset component. It is inherited from tdataset. Unlike the preceding Standard dataset component, the custom dataset component must interpret the content in the Record Buffer by itself. For a custom dataset, you can still use the field editor or use a standard data control to display the data.