[ Pobierz całość w formacie PDF ]
.For example, companies must number invoices with uniqueand consecutive numbers, without leaving holes in the numbering sequence.This situation isextremely complex to handle programmatically, if you consider that only the database candetermine these unique consecutive numbers when we send new data to it.At the same time,we need to identify the record before we send it to the database, otherwise we won t be ableto fetch it again.Practical examples of how to solve this situation are discussed in the nextchapter.OIDs to the ExtremeAn extension to the use of surrogate keys is the use of a unique identifier, also called objectidentifier (OID).An OID is a number, or a string with sequence of numbers and digits, addedto each record of each table representing an entity (and at times even to records of tables rep-resenting relations).Differently from client codes, invoice numbers, SSN, or purchase ordernumbers, OIDs are totally random, without any sequencing rule, and never visible to the enduser.This means you can still use surrogate keys (if your company is used to them) alongwith OIDs, but all the external references to the table will be based on OIDs.Another common rules suggested by the promoters of this approach (which is part of thetheories supporting object-relational mapping) is the use of system-wide unique identifiers.If you have a table of client companies and a table of employees, you might wonder why youshould use a unique ID for such diverse data.The reason is that, if you do so, you ll be ableto sell goods to an employee without having to duplicate the employee information into thecustomers table, but simply referring to the employee in your order and invoice.An order isplaced by someone identified by an OID, and this OID can refer to many different tables(but of course not all of them).NOTEUsing OIDs and the object-relational mapping is an advanced element of the design of Delphidatabase applications.My personal suggestion is to investigate this topic before embracingmedium or large-size Delphi projects, as the benefit can be relevant (after some investment instudying this approach and building some basic support code).External Keys and Referential IntegrityGetting back to the standard database design, the keys identifying a record (whichever theirtype) can be used as external keys in other tables, for example to represent the various typesof relations discussed earlier.All SQL servers are capable of verifying these external refer-ences, so that you cannot refer to a nonexistent record of another table.These referentialintegrity constraints are expressed when you create a table.Copyright ©2001 SYBEX, Inc., Alameda, CA www.sybex.com 2874c14.qxd 7/2/01 4:33 PM Page 608608 Chapter 14 " Client/Server ProgrammingBesides not being allowed to add references to nonexistent records, you re generally pre-vented from deleting a record if there are external references to it.Some SQL servers go onestep further: As you delete a record, instead of simply denying the operation, they can auto-matically delete all records that refer to it from other tables.More ConstraintsBesides the uniqueness of primary keys and the referential constraints, you can generally usethe database to impose more validity rules on the data.You can ask for specific columns (suchas those referring to a tax ID or a purchase order number) to include only unique values.Youcan impose uniqueness of the values of multiple columns for example, to indicate you can-not run two classes in the same room at the same time.In general, simple rules can be expressed imposing constraints on a table, while more com-plex rules generally imply the execution of stored procedures activated by triggers (everytime the data changes, for instance, or there is new data).Again, there is much more to proper database design, but the simple elements discussed inthis section can provide a starting point, or a good refresher.NOTEFor more on the Data Definition Language and Data Manipulation Language of SQL, see thebonus chapter Essential SQL on the Mastering Delphi CD.Client/Server with the BDENow let s consider how Delphi fits into the client/server picture.How does it help us buildclient/server applications? As I ve mentioned, you can still use all the components and tech-niques discussed in the Chapter 13, Delphi s Database Architecture, although in somecases alternate approaches will help you leverage the power of the RDBMS your applicationis dealing with.As a starting point, let s cover a few considerations on Delphi client/server developmentusing the BDE and its components.After this I ll move to dbExpress, which in Delphi 6 isthe recommended general solution for client/server development.NOTEFor a list of the alternative approaches Delphi 6 offers for database access, see the initial partof the preceding chapter.Copyright ©2001 SYBEX, Inc., Alameda, CA www.sybex.com 2874c14.qxd 7/2/01 4:33 PM Page 609Client/Server with the BDE 609SQL LinksThe BDE doesn t know how to handle the RDBMS; it uses some further drivers, called SQLLinks, to perform this operation.As an alternative, the BDE can also interact with ODBCdrivers.Borland provides native BDE drivers for InterBase, Oracle, Informix, MicrosoftSQL Server, Sybase, and DB2.If the BDE is still required on the local machines, it can actually be very efficient.Forexample, when you use the pass-through mode for queries, the BDE doesn t try to interpretthe SQL code but passes it directly to the RDBMS server.This allows you to use a server sspecific SQL commands and also to speed up the execution.The pass-through mode isactivated using the BDE Administrator utility.Having the BDE between the client and the server can also help in building applicationsdesigned to work with multiple servers.In practice, however, it s not easy to do this and stillobtain the best performance, because of differences in the SQL dialects understood by eachSQL server.In particular, data types are handled differently by the various servers.If thesame table were placed on two servers that have data type differences, Delphi would need touse two different TField objects (which creates a few headaches if you want to define thefields at design time).The Database ComponentIn local BDE applications, programmers usually refer to the database by indicating the aliasof the file path in the DatabaseName property of the Table and Query components.A betterapproach is to use the Database component to define a local alias and then let all the DataSetcomponents refer to this local alias.As an example, consider the components of the GetMax application mentioned at thebeginning of this chapter:object Database1: TDatabaseAliasName = IBLOCALConnected = TrueDatabaseName = IBLoginPrompt = FalseParams.Strings = ( USER NAME=SYSDBA PASSWORD=masterkey )SessionName = Defaultendobject EmpTable: TTableDatabaseName = IBTableName = EMPLOYEEendCopyright ©2001 SYBEX, Inc., Alameda, CA www.sybex.com 2874c14
[ Pobierz całość w formacie PDF ]