Chapter 1 Design Database Application Database Application Allows users to interact with information stored in the database. The database provides the structure of information for different applications.
Delphi 4 supports relational databases. The relational database organizes information in the form of rows and column table. When you design a database application, you must understand the structure of the data so that you can design a suitable user interface to display data in the database and allow the user to enter new data or modify existing data.
1.1 use
The components on the "Data Access" page of the Database Meter Plate are used to read and write the database, which will access information in the database by means of BDE (BORLAND DATABASE Engine).
Database drivers contained in Delphi 4 have different versions. However, all versions contain drivers to access the local database, and the Client / Server version and Enterprise also contain SQL Links to access remote databases. The use of local databases or remote database depends on several factors, for example, how much data is stored in the table, how many users should access the database at the same time, and what is required for the performance of the database.
1.1.1 Local Database and Remote Database
The local database is located on a local disk or a local area. If there are several users to access the database, the local database takes a file-based locking policy, so the local database is also called a file-based database.
As the local database is often in the same system with the database application, the speed of accessing the local database is faster than accessing the remote database.
The data that the local database can be stored does not have more data that can be stored remotely, and this must be taken into account when choosing a local database or a remote database.
Applications using local databases are also known as single-layer 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 the remote computer. The user accesses data in the remote database via SQL (Structured QueryLanguage), which is based on this reason, and the remote database sometimes called the SQL server or RDBMS (Remote Database Management System).
The remote database is ideal for several users at the same time, which is different from the local database based on file-locking policies. The remote database provides multi-user support based on transactions.
The data that the remote database can store is much more than the local database, and sometimes the data is not on one server, but is distributed on several servers.
The application using the remote database is called two or multi-layer applications because the database and applications are located in systems (layers) that are not dependent on each other.
Typical SQL servers have Interbase, Oracle, Sybase, Informix, Microsoft SQLServer, and DB2.
1.1.2 Database security
Databases often contain some sensitive information. In order to protect this information, different databases have different protection strategies. Some databases such as PARADOX and DBASE, only provide security in the table level or field level, when a user tries to access the protected table, he must enter a password. When the password is recognized, he can only see the allowed field.
Most SQL servers require users to enter usernames and passwords. Once the user is successfully logged in to the server, the table he can see is related to the username and password he entered.
When you design a database application, you must consider what kind of security measures you need. For example, if you don't want the user to enter a password, you either use the database that does not require a password, or provides the user name and password in the program, but provides the username and password in the program.
If you need a user input password, you must also consider when you enter a password. If you are using a local database but you want to smoothly transition to a large SQL database in the future, it is best to prompt the user to enter the password before opening a table, although it is now possible. Some servers may need multiple passwords, in order to simplify the user's operation, only require the user to enter a primary password, and the rest of the password is automatically provided by the program.
In multi-layered Client / Server applications, you may need to use different security modes simultaneously, for example, with CORBA or MTS control to access the intermediate layer, let the intermediate layer processes the details of the login to the remote database.
1.1.3 transaction
A transaction is actually a set of action, which must be successfully performed before one or several tables are submitted. If there is a certain action to fail, all the actions will roll back (Undo).
Most local databases do not support transactions, but the BDE driver also provides limited transaction processing capabilities. The SQL database and ODBC compatible database itself provide the ability to handle transactions.
1.1.4 Data Dictionary
Regardless of using the local database or a remote database, the application has the right to access the data dictionary. The data dictionary provides a customizable storage area that does not depend on the application. In this area, you can create extended field property sets and describe the contents and appearance of the data.
For example, if you want to develop financial software frequently, you can create a few special field property sets to display the amount in different formats. When creating a data set during the design period, you do not need to use the display format of the object viewer to manually set, as long as you select a suitable property set from the data dictionary, you will share its properties in the current data set. The use of data dictionary ensures that data has a consistent look.
In a Client / Server environment, the data dictionary can be on the remote server.
The DRINTF unit in the ... Delphi4 / lib directory provides programming interfaces for accessing the data dictionary.
1.1.5 Integrity Verification, Storage Procedure, and Trigger
All relational databases provide a function of storage and operational data. In addition, some databases provide functions that help ensure data complete.
First, integrity verification. This feature provides a mechanism that prevents the master / detail relationship between the two tables from being interrupted. When the user tries to delete a field in the master table, if this field is deleted will cause an isolated record in the Detail table, the integrity verification function either does not allow the field to delete the isolated record.
The second is the stored procedure. The stored procedure is actually a set of SQL statements that are placed on the SQL server, and these SQL statements can perform tasks related to the database, and then return to the execution result (record set). The third is the trigger. The trigger is also a set of SQL statements that will trigger execution when responding to a command.
1.2 architecture of database applications
A database application is logically usually consisting of two parts: First, the database access link, the other is the user interface, which is the architecture of the database application.
1.2.1 Select the appropriate architecture
It is recommended that the components that implement the data access link are also separated from the components that implement the user interface. Any data access component is preferably placed on the data module, which ensures that the application has a consistent user interface. If you add a design aforementioned data module and form to the object library, you don't have to start from the head when you create a new database application, which can not only improve programming efficiency, but also guarantee that the program has a consistent style.
The architecture of the database application depends on the use of the local database or the remote database depending on which type information is required to store the number of users who simultaneously access the database.
If the information in the database does not require sharing between several users, it is recommended to use the local database to get a fast access speed and do not have to buy expensive servers. However, the data capacity that the local database can be stored is limited.
If you need to store a lot of information, it is best to use the remote database. However, the two-story architecture requires support for SQL Links, while SQL Links is only included in the CLIENT / Server and Enterprise versions of Delphi 4. If the information between the table and the table has a more complicated relationship, or the number of users increases, it is recommended that you consider the multi-layer architecture. Compared to the two layers of applications. Multi-layer applications have multiple intermediate layers, and the intermediate layer is used to centralize application logic, so that different uses of client programs can use the same data and ensure that data logic is consistent. At the same time, the customer program can do a compact, because a considerable part of the work is done by the middle layer, this is the so-called "thin" customer. "Skinny" customers are easier to install, configure, and mainly because it does not need to include database access links, and do not require BDE. There is also a benefit of using multi-layered architectures that the task of data processing can be done in several different systems. Of course, only Client / Server and Enterprise are supported by multiple layers of architectures.
However, the more layers, the larger the development difficulty and cost, so when developing database applications, it is best to start from single layers, with data and users increase, and gradually transition to multi-layer architectures. The key here is that the scalability of the architecture should be taken into account to maximize the codes to maximize the previous investment.
1.2.2 scalability
The structure of the BDE and the use of the data module make the scalability possible. Whether it is a single layer, two layers is still a multi-layer, can separate the user interface to the data access link, as shown in Figure 1.1
Where, the form is mainly used to implement the user interface, and its main components are data controls. The data module is primarily used to implement data access links, in other words, it is to introduce data sets. Data sets are connected between the TDataSource component between the data sets. The advantage of separating the user interface and the data access link is that when the application transitions to the multilayer architecture, only the data set components on the data module need to modify, and the user interface does not need to change.
However, some user interfaces may need to change accordingly, for example, different databases have different security policies, and some databases require login, and some databases may not be required.
BDE itself has scalability, transitions a BDE-based single-layer application to two-story applications, which is very simple, just modifying the data set to connect a SQL server.
It is also very easy to transition to multi-layer applications based on a single-layer application based on TclientDataSet components, because TclientDataSet also supports access data from the file and access data through the iProvider interface.
If you plan to transition to a three-layer architecture, you can design a single layer or two layers, in addition to the user interface, you have to separate the application logic, because the application logic is eventually placed on the intermediate layer, i.e., the application server. When designing a user interface, you can temporarily provide data with a local database, and then use TclientDataSet to get data.
Figure 1.2 Architecture of single-layer database applications
1.2.3 single layer database application
In single-layer database applications, applications and database share the same file system, which uses local databases or files to access data.
A single layer of database application contains both user interfaces and data access mechanisms (probably by BDE, may also via files). Figure 1.2 is an architecture of a single layer database application. It can be seen that data can be obtained from the local database from the local database based on BDE-based data set components, or data can be obtained from the files from the file via the TclientDataSet component. There is a common point that they all provide data to the user interface through the TDataSource component.
1.2.4 Layer 2 database applications In two database applications, the client provides the user interface, and data is obtained from the remote database server via the BDE. Figure 1.3 is an architecture of a two-layer database application.
Figure 1.3 Architecture of two-layer database application
In this mode, all applications are customers, and the client exchanges data through the BDE with the remote database server. One server can handle the request, coordinate access, and update data at the same time.
1.2.5 multi-storey database applications
In multi-layer database applications, the client, application server, and remote server are distributed on different machines. Where the client program mainly provides a user interface, it can request data and application update data to the application server. The application server (also called Remote Data Broker) requests data and application update data to the remote database server. Figure 1.4 is an architecture of multi-layer database applications.
Figure 1. Architecture of multi-layer database applications
Delphi 4 can create a client or create an application server. The client is communicating with the application server via the iProvider interface. The protocol of communication can be TCP / IP, DCOM, MTS or CORBA. The communication protocol is related to the MIDAS connection component on the customer program and is also related to the data module on the application server.
Application servers have several ways through the iProvider interface. If the application server contains the TDataSetProvider component or the TPROVIDER component, the iProvider interface will be provided by both components. If there is no TDataSetProvider component and the TPROVIDER component on the application server, the iProvider interface is provided by BDE-based data set components. The advantage of providing an iProvider interface with a TDataSetProvider component or a TPROVIDER component is to control the iProvider interface. However, no matter which case, the iProvider interface can pass data between the client program and the application server.
In multi-level mode, several customers may communicate with an application server at the same time, and the application server actually acts as a gateway.
1.3 Design User Interface
The component (also called data control) on the "Data Controls" page of the component palette is used to display the data of the database and allow the user to edit the data and saved to the database. The data control constitutes a user interface (UI) of the database application.
The data control is connected to the database via the TDataSource component, and the TDataSource component is like the catheter between the user interface and the database. On the same form, several data controls can be connected to the same TDataSource component, which can remain synchronized because the data control always displays the current recorded data. The TDataSource component is typically placed on the data module and is separated from the user interface.
There are several data controls. Which data control is used depends on how to display the type of data, depending on how to organize this information, how to browse the information and how to let the user edit the data.
1.3.1 Display single records and multiple records
In many cases, the application only needs to display a record that is currently recorded, in fact, most of the data controls on the component palette is designed to display the current record, such as a TDBText component.
If you want to display multiple records at the same time, you must use the TDBGRID component or the TDBCTRLGRID component. Both components can display multiple records or multiple fields.
The Master / Detail relationship can be established between the two tables. Accordingly, the client program can display a field of the master table with a TDBText component, and a plurality of records of the Detail table is displayed with a TDBGRID component.
1.3.2 analysis data
Some database applications do not directly display the original data in the database to the user, but analyze the data, statistics, in an appropriate manner, which helps users get conclusions from them.
There is a TDBCHART component on the "Data Controls" page of the component palette that can be analyzed and displayed in the form of a chart. If you purchase a Client / Server version of Delphi 4, you will have the "Decision Cube" page on the component palette. The components on this page can perform multi-dimensional analysis and statistics on the data and displayed in the form of a raster and chart. 1.3.3 Select the data to display
A database application is often only concerned about part of the data in the database, for example, some programs only care about a part of the field, and some programs only care about part of the record.
As for how to take out concern, depending on the data set component you use. Several data set components may be used in a database application. Delphi 4 supports six types of data sets.
TTable components. On behalf of a table logically. You can adjust the appearance of the field by creating a permanent field object, you can increase the Lookup field and the calculation field, you can set the filter condition and range to select the record.
TQuery component. You can query the database and return the qualified record.
TSTOREDPROC components. The stored procedure used to perform the SQL server. The stored procedure can also return a record that conforms to a specific condition.
TclientDataSet components. You can retrieve data from the application server and copy a copy in local memory. It is based on this reason that the number of records that TclientDataSet works simultaneously is limited. The client created with TclientDataSet can be made small because it does not need to rely on BDE, just need DBClient.dll files. TclientDataSet can be obtained from the application server or data from the file.
TnestedTable components. Used to access records in the nested table. Although Delphi 4 cannot create an Oracle8 type table, you can display and edit the data in the nested table.
Customized data set components. It is inherited from TDataSet. Unlike the above-mentioned standard data set components, custom data sets have to explain the contents of the recording buffer. For custom data sets, you can still use the field editor, you can also display data using standard data controls.
