18th Chapter-delphi Client Server application development (d) (2)

Introduction to 18.4.1.2 Database structure

The structure of the iblocal database is defined by the InterBase Server Tools Interactive SQL tool (ISQL).

To define a database with isql, you first build a database with the Create DB command, and a new database is generally created with GDB as its extension. Once established, you can define database tables in SQL language, such as the SQL statement that establishes the employee table as follows:

To define a domain name data type:

CREATE DOMAIN FIRSTNAME as VARCHAR (15);

CREATE DOMAIN LASTNAME as VARCHAR (20);

CREATE DOMAIN CountryName as VARCHAR (15);

CREATE DOMAIN EMPNO as SMALLINT;

CREATE DOMAIN DEPTNO as CHAR (3)

CHECK (value = ' or ' (Value > ' 0 ' and value <= ' 999 ') or value is NULL);

CREATE DOMAIN Jobcode as VARCHAR (5)

CHECK (VALUE > ' 99999 ');

CREATE DOMAIN Jobgrade as SMALLINT

CHECK (VALUE BETWEEN 0 and 6);

CREATE DOMAIN SALARY as NUMERIC (15, 2)

DEFAULT 0

CHECK (VALUE > 0);

Create Employee table:

CREATE TABLE EMPLOYEE (emp_no EMPNO not NULL,

First_Name FIRSTNAME not NULL,

Last_Name LASTNAME not NULL,

Phone_ext VARCHAR (4),

Hire_date DATE DEFAULT ' Now ' is not NULL,

Dept_no DEPTNO not NULL,

Job_code Jobcode not NULL,

Job_grade Jobgrade not NULL,

Job_country CountryName not NULL,

SALARY SALARY not NULL,

Full_name computed by (last_name | | ', ' || first_name),

PRIMARY KEY (emp_no));

A check statement is a constraint on the value range of a database field. A primary_key statement is a key index to a table.

By doing so, you can define all the tables in the iblocal.

The tables in iblocal include:

EMPLOYEE CUSTOMER DEPARTMENT Employee_project

PROJECT SALES Salary_hiscory

The contents of each database table are as follows:

Table 18.14 the contents of each database table in Employeedemodb

━━━━━━━━━━━━━━━━━━━━━━━━━━━

Contents of database table name table

───────────────────────────

Employee Employees Information

Customer Information

DEPARTMENT Department Information

Employee_project Employee-owned projects

Project Engineering Information

Sales information

Historical information on Salary_history employees ' salary adjustments

━━━━━━━━━━━━━━━━━━━━━━━━━━━

Key fields are defined in each database table. About the name, type, and size of the fields in the database table, don't repeat them here.

18.4.2 Application Analysis

Use of 18.4.2.1 tdatabase parts

The Csdemo program defines a database module part--tdmemployee, which is inherited from Tdatamodule. Tdatamodule is the framework for placing data access parts (such as Tdatabase, Ttable, Tquery, and so on) that appear in Delphi2.0. Other forms that involve database access can refer to the corresponding database access part as long as you insert the library unit in the uses statement that contains the database module.

A tdatabase type of part ──employeedatabase is defined in Tdmemployee. The main attributes and attribute values for Employeedatagase are as follows:

Table 18.15 Employeedatabase The value of the main property of the part

━━━━━━━━━━━━━━━━━━━━━━━

Property property Value

───────────────────────

AliasName iblocal

DatabaseName Employeedemodb

Keepconnection True

Loginprompt False

Transisolation tireadcommitted

Params USERNAME = SYSDBA

PASSWORD = Masterkey

Connected True

━━━━━━━━━━━━━━━━━━━━━━━

The iblocal specified by the AliasName property must already be configured in BDE, and the DatabaseName attribute specifies the name of the database to be used, which is defined by the application itself, and therefore does not react to BDE, which is ttable, Tquery such as a dataset part reference and appears in the DatabaseName drop-down list box of the dataset part. In this example, "Employeedemodb" is referenced by all dataset parts such as Employeetable,salestable.

Connected to TRUE indicates that the application will remain joined to the database.

The Keepconnection property is true to show that any table in the EMPLOYEEDEMODB database is opened and closed multiple times, and the application remains connected to the database, which eliminates the overhead of duplicating registrations.

The Loginprompt property is False, indicating that the application automatically processes the join registration with the database, so the registered username and password are defined in the params property:

USERNAME = SYSDBA

PASSWORD = Masterkey

The Transisolation property for tireadcommitted indicates that a transaction allows only read data submitted by other transactions if there are multiple concurrent transactions.

The application of Employeedatabase in the program is also related to the transaction control. This is covered in the following sections.

18.4.2.2 the switching of different database tables

In many database applications, you have to switch between different database tables in response to changes in user input conditions or system state. At this point, special processing is often required, such as changing the cursor shape or hiding data changes, especially in client/server applications. Because the SQL statement is used to access the remote database, and sometimes compute tasks are performed on the server side, the data changes at the client are spaced out, so the user should be able to understand what is happening. Here's how the Csdemo is handled when the database table is switched:

Procedure tfrmviewdemo.showtable (atable:string);

Begin

Screen.cursor: = Crhourglass; {Prompts the user for the current action State}

Varyingtable.disablecontrols; {Hide data changes}

Varyingtable.active: = FALSE; {Close the original database table}

Varyingtable.tablename: = ATable; {Update database table name}

Varyingtable.open; {Open database table}

Varyingtable.enablecontrols; {Show Changes made}

Screen.cursor: = Crdefault; {Reset Cursor shape}

End

The Crhourglass cursor indicates that an SQL query is executing. The role of Disablecontrols and Enablecontrols is to hide and display data changes.

Application of 18.4.2.3 InterBase trigger (Trigger)

In the Csdemo application, the form that the demo trigger applies is tfromtriggerdemo;

Contains two Tdbgrid objects in the form. DBGRID1 displays the data in the employeetable, DBGrid2 displays the data in the salaryhistorytable. Their primary attributes and property values are as follows:

Table 18.16 Emlpoyeetable The value of the main property of the part

━━━━━━━━━━━━━━━━━━━━━

Property property Value

─────────────────────

DatabaseName Employeedemodb

Indexfieldname Emp_no

TableName EMPLOYEE

━━━━━━━━━━━━━━━━━━━━━

Table 18.17 Salaryhistorytable The value of the main property of the part

━━━━━━━━━━━━━━━━━━━━━

Property property sheet

─────────────────────

DatabaseName Employeedemodb

Indexfieldname Emp_no

Masterfields Emp_no

Mastersource Employeesource

TableName Salary_history

━━━━━━━━━━━━━━━━━━━━━

There are two kinds of relationships between the two tables:

Connection relationship

When a employeetable record changes, the salaryhistorytable data is changed accordingly. This connection is achieved through an index.

Data consistency

Changes to the value of the salary field in employeetable must be reflected in the salaryhistorytable, salaryhistorytable maintaining historical information about salary changes. This data consistency requirement is implemented through triggers in this program.

A trigger is a program executed on the SQL Server that is triggered on the server side to perform a certain data calculation task.

The following are the trigger programs associated with the employee table on the InterBase server:

Triggers on Table EMPLOYEE:

Save_salary_change, sequence:0, Type:after UPDATE, Active as

BEGIN

IF (old.salary <> new.salary) THEN

INSERT into Salary_history

(Emp_no, Change_date, updater_id, Old_salary, Percent_change)

VALUES (

Old.emp_no,

' Now ',

User

Old.salary,

(new.salary-old.salary) * 100/old.salary);

End

Because triggers are executed automatically by the server, which corresponds to data modifications on the employee table, there is no explicit invocation on the client application. An action that is displayed when the client has a program that opens and displays the contents of the database table and updates when the data in the Salary_history table changes.

Procedure Tfrmtriggerdemo.formshow (Sender:tobject);

Begin

DmEmployee.EmployeeTable.Open;

DmEmployee.SalaryHistoryTable.Open;

End

Procedure Tdmemployee.employeetableafterpost (Dataset:tdataset);

Begin

{An employee's salary change will trigger a change in the salary adjustment history,

Therefore, if the salaryhistory is open, you need to update the display}

With Salaryhistorytable do if Active then Refresh;

End