Multi-tier Database Development 2: single-tier and two-tier applications

Chapter 2 single-layer and two-layer applications
Single-layer and two-layer database applications are relatively simple. applications and databases are usually in the same file system, or even on the same disk. Both types of database applications are not suitable for accessing the same database in a multi-user environment.
For a single-layer application, Delphi 4 provides two data acquisition methods, one is through BDE, and the other is through files. Two-tier applications generally use BDE.
2.1 BDE-based applications
Because BDE and Data Access Components process details such as reading data, updating data, and recording navigation, writing a two-tier application is almost the same as writing a single-tier BDE-based application.
When publishing BDE-based applications, BDE must be released at the same time, which greatly increases the number of bytes of applications and increases the difficulty of publishing and installation. However, the role of BDE cannot be replaced.
2.1.1 BDE-based architecture
A bde-based single-or two-tier application usually consists of the following parts:
The main component of the user interface is the data control;
One or more dataset components are used to import data from the database;
One or several tdatasource components used to connect datasets and data controls;
. One or several tdatabase components (optional), used to control transactions. In two-tier applications, the tdatabase component can also manage database connection continuity and whether to log on;
One or several tsession components (optional) are used in multi-threaded applications to manage database connections.
Figure 2.1 demonstrates the architecture of BDE-based applications: Figure 2.1 BDE-based architecture.
2.1.2 understand databases and datasets
Databases and datasets are two concepts that have both links and differences. A database contains data in a table, attributes, indexes, and stored procedures of the table. Datasets are mainly used to import data in tables. A bde-based application must have at least one dataset component.
Each BDE-based dataset component has a public attribute databasename, which is used to specify the database to be accessed.
The databasename attribute can be set to the database's BDE alias. An alias not only represents the database, but also represents the configuration information of the database. Different types of databases, such as Oracle, Sybase, Interbase, paradox, and DBASE, have different configuration information. You can use BDE administration or SQL explorer to create and manage BDE aliases.
For Paradox or DBASE databases, the databasename attribute can also be set to the directory where the table is located. If the application explicitly uses the tdatabase component to connect to the database, the databasename attribute can be set as an alias dedicated to the application.
2.1.3 use session objects
Session objects (tsessions) are used in multi-threaded database applications to manage database connections. The role of the session object is mainly reflected in four aspects.
Manage BDE aliases. You can use a session object to create new aliases, delete aliases, and modify alias parameters. By default, aliases created and modified by session objects are valid only during the session period. However, you can call the saveconfigfile of tsession to permanently Save the alias to the BDE configuration file. In this way, these aliases can be used by other sessions and applications.
Control database connections in two-tier database applications. If the keepconnections attribute of the tsession is set to true, the connection to the database is maintained even if no data set is currently active. This wastes some resources, however, you can avoid logging in again when you connect to the database next time. Password protection is provided for paradox and DBASE in single-tier database applications. A dialog box is displayed to allow users to enter the password or provide the password in the program. If you plan to smoothly transition a single-layer architecture to a two-or multi-layer architecture, you must design a human-machine interface that allows users to enter their usernames and passwords, even if it is currently not needed in a single-tier database application.
Specify the BDE network control file pdoxusrs. Net and the directory for storing private files.
If an application needs to access the same database in several places at the same time, it needs to use multiple session objects to manage the database connection. Otherwise, unexpected consequences may occur.
2.1.4 connect to the database
BDE contains different types of drivers used to connect to different types of databases. The standard version of delphi4 contains drivers for accessing local databases, such as paradox, dBase, Foxpro, and access. In addition to the driver for accessing the local database, the Professional version of Delphi 4 also contains the ODBC driver, which allows you to access a wider range of data sources. The Client/Server and enterprise versions of delphi4 also contain SQL links, which allow remote access to large databases, including Interbase, Oracle, Sybase, Informix, Microsoft SQL Server, and DB2.
2.2 things
A transaction is actually a group of actions that must be successfully executed before the table is committed. If an action fails to be executed, all the actions will be undo. This ensures that no inconsistent data exists in the database.
By default, BDE provides the implicit transaction processing capability. When a record of a dataset is to be written to a database, BDE ensures that some fields are not updated while other fields are not.
In a multi-user environment, especially when accessing the SQL server, it is best to explicitly use transactions, because the implied transaction processing capability is limited after all, it increases network overhead and degrades application performance.
2.2.1 explicitly use transactions
In BDE-based database applications, there are two methods to explicitly use transactions. These two methods are mutually exclusive and cannot be used at the same time.
Connect to the database through the tdatabase component, and control the transaction through attributes and methods such as starttransaction, commit, rollback, intransaction, and transisolation of the tdatabase. The advantage of this method is that it does not need to depend on a specific database or server. The program has good portability and no redundant code.
The so-called passthrough SQL is used to pass SQL statements directly to remote SQL servers or ODBC servers through the tquery component. The advantage of this method is that you can directly use the server's transaction management capabilities. However, this is related to a specific server and the portability of programs is hard to be guaranteed.
Single-layer applications cannot use the passthrough SQL method, but can only use the tdatabase component to control transactions. Two-tier applications can either use the tdatabase component or use the passthrough SQL method.
2.2.2 how the tdatabase component controls transactions
Call starttransaction to start a transaction. Once a transaction is started, all subsequent read/write operations are related to the transaction, unless the transaction is terminated explicitly. You can access the intransaction attribute of tdatabase. If this attribute returns true, a transaction has been started. In this case, the data in the database needs to be retrieved Based on the transaction isolation level (transisolation attribute ).
After starting a transaction, you can perform read and write operations on the database. to permanently Save the modified data to the database, you should call the tdatabase commit to submit the data. The commit is usually put in the try part of the try... commit T structure. In this way, if the commit fails to be called successfully, there is a chance to call rollback. The program example is as follows:
Database1.starttransaction;
Try
Table1.edit;
Table1.fieldbyname ('custno'). asinteger: = 100;
Table1.post; database1.commit;
Exception
Database1.rollback;
End;
2.2.3 transisolation attributes
If multiple transactions are simultaneously processed and accessed to the same table, how these transactions affect each other is determined by the transaction isolation level set by the transisolation attribute.
If this attribute is set to tidirtyread, other transactions can be read for uncommitted changes to the database. Uncommitted changes may be rolled back at any time. Therefore, the read data is unreliable.
If this attribute is set to tireadcommitted, other transactions are allowed to read the committed changes to the database. If this attribute is set to tirepeatableread, other transactions cannot be read to modify the database. Different servers support different transaction isolation levels. If the transaction isolation level set in the transisolation attribute is not supported by the server, BDE automatically reduces the transaction isolation level.
Note: When Using transactions for local databases such as paradox, dBase, access, and Foxpro, set the transisolation attribute to tidirtyread instead of the default value tireadcommitted. Otherwise, BDE reports an error.
2.2.4 passthrough SQL
Passthrough SQL directly transmits SQL statements to remote servers through tquery, tstoredproc, or tupdatesql components. These SQL statements include calls to server transaction processing functions.
To use passthrough SQL, you must meet the following conditions: the version must be client/server and the SQL links driver has been installed. The network protocol is correctly configured. Has the permission to access the remote server. You must use SQL explorer to set the "sqlpassthru mode" parameter to "not shared ".
2.2.5 local transaction
For local databases such as paradox, dBase, access, and Foxpro, BDE also provides transaction processing capabilities, which are called local transactions.
From a programming perspective, local transactions are no different from those for remote databases. However, its functions are limited:
. No crash auto-recovery function
. Data Definition statements are not supported.
. Transactions cannot be performed on temporary tables.
. For paradox tables, indexes must be created. Otherwise, data cannot be rolled back.
. The number of records that can be locked and modified is limited. paradox is limited to 255 records, and DBASE is limited to 100 records.
. Transactions cannot be performed on ASCII text files.
The transaction isolation level (transisolation attribute) can only be set to tidirtyread.
2.2.6 cache update
BDE provides cache update technology. The cache update process is as follows: the application retrieves data from the database and modifies the data. It is actually in the local cache. In the future, you can apply to update the data in the cache.
Obviously, the cache update technology can improve efficiency and reduce the amount of transmission on the network. However, there is a difference between cache updates and transactions. The cached data is only visible to the application. Before applying for an update, other applications cannot know what changes have been made to the data. Therefore, the cache update technology is not applicable to frequently modified data, because user a may change the data to 10, user B changes the data to 20, and user c changes the data to 30, they may apply for updates to the database at the same time, which may cause conflicts.
For the above reason, the dataset component has a cachedupdates attribute, so you can choose whether to use the cache update technology.
2.2.7 create and reconstruct a table
In BDE-based applications, you can use the tTable component to dynamically create a table or create an index in an existing table.
To create a table, you must first create a field definition (fielddefs attribute), then create an index definition (indexdefs attribute), and finally call createtable. You can also right-click the tTable component during the design period and select the "Create Table" command in the pop-up menu.
Note: To create an oracle8 table, you cannot create an ADT field, array field, reference field, or dataset field in Delphi 4.
At runtime, you can also change the table structure (this is called refactoring). Therefore, you need to call an API called dbidorestructure of BDE. However, if you only need to create an index, you can call addindex.
During the design period, you can use Database Desktop to create and reconstruct paradox and DBASE tables, and use SQL explorer to create and reconstruct SQL tables.
2.3 file-based Single-layer database applications
Tclientdataset components are required for file-based Single-layer database applications. The tclientdataset component can access data from files and create a copy of data in the memory. In this way, data access and operations are very fast, but the data capacity is limited by the memory.
2.3.1 tclientdataset
Tclientdataset does not depend on BDE, which means that the application does not have to overwrite the BDE memory, but the data itself requires memory. Tclientdataset only requires the support of a dynamic link library dbclient. dll. Therefore, it is relatively simple to publish and install the program created with tclientdataset, saving the BDE configuration and maintenance.
Because tclientdataset does not use BDE, file-based single-tier database applications are not applicable to multi-user environments. Although tclientdataset does not support BDE, since tclientdataset is inherited from tdataset, most operations that can be performed on the tTable component can also be performed on the tclientdataset component, including displaying data imported by tclientdataset with standard data controls. You do not need to use the tdatabase component because no database needs to be managed or transactions are supported. You do not need to use the tsession component unless the application is multi-threaded.
The difference between a BDE-based database application and a file-based database application is that the method for creating a dataset is different from that for accessing data.
2.3.2 create a dataset during the design phase
Because file-based single-tier database applications use not ready-made databases, the primary task is to create a dataset. After creating a dataset, you can save it to a file and then retrieve it from the file without having to recreate the dataset. However, the indexes are not saved together when you save the data set. Therefore, you have to re-create an index every time you read the data set from the file.
You can use the field editor to create a dataset during the design period. The general steps are as follows:
Place a tclientdataset component on the form, right-click the component, and select the "fields Editor" command in the pop-up menu. Delphi 4 opens the field editor, as shown in 2.2.
Figure 2.2 field editor
Right-click the field editor and choose "New Field" from the shortcut menu. Delphi 4 opens the "new field" dialog box, as shown in Figure 2.3.
In the "name" box, type the field name, select the Data Type of the field in the "type" box, and set the field length in the "size" box, the "component" box automatically generates the object name for this field.
The Field Type box is used to specify the generation type of the new field (not the Data Type of the field ), you can select "data", "calculated", "lookup", "internalcalc", and "aggregate ".
Fields created in the field editor are called permanent fields. After a permanent field is created, right-click the tclientdataset component and select the "Create dataset" command in the pop-up menu. At this time, Delphi 4 creates an empty dataset with no records. Right-click the tclientdataset component again, select the "Save to file" command in the pop-up menu, and save the dataset to the file. The extension is. CDs.
2.3.3 create a dataset at runtime
To create a dataset Based on tclientdataset at runtime, you must first create a field definition and an index definition. This is similar to creating a dataset Based on the tTable component. The difference is that, tclientdataset does not have attributes such as databasename, tablename, or tabletype, because tclientdataset does not need to directly access the database.
The indexdefs attribute is used to create an index. This attribute is a tindexdef object. It has two attributes: The descfields attribute and the caseinsfields attribute.
If the options attribute of tindexdef contains the ixdescending element, the records are sorted in descending order of all fields. If you want to sort records in ascending order of some fields and in descending order of other fields, you need to use the descfields attribute. Assume that a dataset has three fields: field1, field2, and field3. If you set the descfields attribute to "field1; field3", the data is sorted in ascending order of field2, sort by field1 and field3 in descending order.
The role of the caseinsfields attribute is similar to that of the descfields attribute.
After the field definition and index definition are created, createdataset is called. The program example is as follows:
Procedure tform1.formcreate (Sender: tobject );
Begin
With clientdataset1 do
Begin
{Define a field named field1}
With fielddefs. addfielddef do
Begin
Datatype: = ftinteger;
Name: = 'field1 ';
End;
{Define a field named field2}
With fielddefs. addfielddef do
Begindatatype: = ftstring;
Size: = 10;
Name: = 'field2 ';
End;
{Define an index called intindex}
With indexdefs. addindexdef do
Begin
Fields: = 'field1 ';
Name: = 'intindex ';
End;
Createdataset;
End;
End;
2.3.4 create a dataset based on an existing table
To convert a BDE-based application to a single-layer file-based application, first convert an existing table to a format that can be recognized by tclientdataset. The procedure is as follows:
Add a tclientdataset component to the form, right-click the component, and select the "Assign local data" command in the pop-up menu. A dialog box is displayed, this allows you to import data from a local BDE-based data set, as shown in Figure 2.4.
Select a local dataset and click OK.
Right-click the tclientdataset component again and select the "Save tofile" command in the pop-up menu.
2.3.5 access data in files
In a single-layer file-based application, tclientdataset can automatically maintain a record of data changes. If you do not want to restore the original data, you can call mergechangelog to merge the changes into the data.
However, even if the changes are merged into the data, the data is still in the memory and will be lost when the application is closed. Therefore, you must also call savetofile to save the data to the file.
Savetofile only stores the structure and data of the dataset, but does not store indexes. Therefore, the index must be re-created every time the dataset is opened.
To open a previously saved dataset with savetofile, call loadfromfile.
If the file opened and saved each time remains unchanged, you can also set the filename attribute of tclientdataset. When the active attribute of tclientdataset is set to true, data is automatically read from the specified file. When the active attribute of tclientdataset is set to false, the data is automatically saved to the specified file.
2.3.6 briefcase Mode
For those who often need to work on the road on a business trip, they often need to get some data from the database before departure, and then write the modified data back to the database, this is the so-called "briefcase" mode.
In multi-tier architecture applications, tclientdataset generally obtains data from the application server through the iprovider interface. To work in the "briefcase" mode, you must access data in the file.
The key to the "briefcase" mode is to save data to a file. Therefore, the user interface of the customer program should allow the user to retrieve data from the application server and save it to the file, users are allowed to call up data from files and update the database. tclientdataset loadfromfile and savetofile can fully implement these functions.
Another key to the "briefcase" mode is that the customer program must be able to determine whether the current application server is connected. In other words, the customer program must be able to work even offline.
2.3.7 smooth transition to three-tier architecture
In a two-tier architecture, one layer is the database server, and the other layer is the application. Applications are logically divided into two parts: user interface and data access link. To smoothly transition a two-tier client/server application to a three-tier client/server application, take these steps.
The first step is to create a new project as the application server, and then place the data access link on the application server. Add the tdatasetprovider or tprovider component to the application server. If there is a dataset, you must add a tdatasetprovider or tprovider component.
The second step is to modify the existing two-tier architecture and remove the data access link. In this way, this program only contains the user interface.
The third step is to replace the original dataset component with the tclientdataset component, and then add one or more connection components, such as tdcomconnection.