数据库更改管理[外文翻译].doc

1、浙江万里学院现代物流学院 外文文献译文 1 毕业论文外文翻译 译文 数据库更改管理 资料来源 : http:/www.simple- 作者：道格拉斯赖利 让您的应用程序运行起来！ 在最近的一篇文章中，我指出，我已经成功的使用存储过程把数据库的变化和客户端应用程序隔离开。随后读者的评论使我的观点变得更加明确了。但是，使用或不使用存储过程把数据库的变化和应用程序分离开是一个热门话题。考虑到这一点，下面列举了一些关于修改数据库但不破坏客户端应用程序的实例。 数据库维护的第一条规则：不破坏 我们维护数据库的第一条规则是尽量通 过使用一个或多个应用程序来避免破坏现有的应用程序，除非它是不可避免的。 CO

2、M 程序员无疑很熟悉接口的概念。对于 COM 来讲，客户和组件的交互是通过接口完成的。所以，如果一个名为 IEmployee 的接口中有一个名为ReadEmployees()的方法接受了一个名为 DivisionID 的整型参数，那么客户可以继续通过这个方法作为实现接口 IEmployee 的有效方法来与组件进行交互。 有的时候，应用程序需要新的功能。比如在 IEmployee 接口，员工可能分为不同的等级，那么不同等级的管理人员只能检索权限范围内的信息 。有效的方法是添加一个名为 LocationID 的参数到 ReadEmployee()方法，确保组件不会将一个部门员工的信息暴露给另外一个

3、部门。对于 COM 组件来讲，添加一个参数会破坏原来的接口，所以你可以创建一个 IEmployee2 接口，并提供该接口两种方法。然后，你可以创建组件同时实现 IEmployee 和 IEmployee2 接口。 当使用 SQL Server 存储过程时，更方便。 SQL Server 存储过程接受有默认值的参数。这使得添加新的参数破坏小，并允许新功能以向下兼容的方式被处理 。 这里有一个例子： 最近我要扩容一个系统，在住院部不仅包括住院医师，还包括儿科医师。数据库要为这种变化做好准备，这种变化是使用映射表反映病人的多个服务被概括成为一个主服务。该数据库已经准备好接受一个名为小儿科的新的主服务

4、，这个主服务将包括小儿、新生儿和新生儿重症监护服务。 在存储过程中，有些地方使用了硬编码。这样做是因为原先组织部门不希望购买一个超过他们预算的系统。这样做以后，在存储过程内部添加一个主服务是没有问题的，但添加在外部就会花费很大的成本。 浙江万里学院现代物流学院 外文文献译文 2 因此有一些像这样的存储过程（大大简化）： CREATE Procedure spPatientsByResident ResidentID int = null As SELECT * FROM Patients LEFT OUTER JOIN Residents ON Patients.ResidentID = Re

5、sidents.ResidentID WHERE (Patients.Service IN (SELECT PatientService FROM ServiceLinks WHERE MasterService = MEDI) AND (ClinicResidentLink.DateDeActivated IS NULL) ORDER BY Residents.Name 添加一个参数到主服务导致了这样的存储过程调用： CREATE Procedure spPatientsByResident ResidentID int = null, MasterService nvarchar(20)

6、= MEDI As SELECT * FROM Patients LEFT OUTER JOIN Residents ON Patients.ResidentID = Residents.ResidentID WHERE (Patients.Service IN (SELECT PatientService FROM ServiceLinks WHERE MasterService = MasterService) AND (ClinicResidentLink.DateDeActivated IS NULL) ORDER BY Residents.Name 这个存储过程使用 ADO.NET在

7、 ASP.NET应用程序中可以很好的运行。（见下面对传统 ADO 的说明） 一 个较为复杂的存储过程案例，声明如下： CREATE PROCEDURE dbo.spAdminSaveMenu ( MenuID int, MenuTitle nvarchar(255), 浙江万里学院现代物流学院 外文文献译文 3 MenuAdminTitle nvarchar(255), MenuTypeID int, MenuBindToID int, UniqueID nvarchar(255) ) AS 当参数的命名不是很清楚地时候 ， UniqueID 作为全球唯一标识符将用来唯一标识用户。如果不考虑安

8、全问题，最初的系统曾采用全球 唯一标识符作为间接形式来跟踪用户。全球唯一标识符被认为是一个不透明的身份编号，它不仅没有任何意义，而且不会被用来猜测其他用户的标识符（不同于顺序整数标识符）。正如经常发生的，一个新的系统将要使用的数据库，会希望使用整数编号的标识符（已经在数据库中）而不是全球唯一标识符。我可以通过修改存储过程，使两个应用程序很好地协同工作。 CREATE PROCEDURE dbo.spAdminSaveMenu ( MenuID int, MenuTitle nvarchar(255), MenuAdminTitle nvarchar(255), MenuTypeID int,

9、MenuBindToID int, UniqueID nvarchar(255)=null, UserID int = null ) 通过参考 IsNull() 的使用，存储过 程是否可以使 用 UniqueID 或 UserID。使用 SqlClient ADO.NET，每个应用程序能正确地提供一个或多个参数和功能。在内部，存储过程验证了至少最后有两个参数中的一个是非空的。 在某些情况下，没有简单的方法区别存储过程和所需要的变化之间的区别。在 这种情况下，与其破坏现有的应用程序，我更愿意创建一个新的存储过程。即在原来的基础上增加第二个存储过程，如果第二个已经存在，就创建第三个，以此类推。因此

10、，“ spSaveSomething”将变成“ spSaveSomething2”。我发现存储过程增加了一个 OUTPUT 参数，将变成一个新的存储过程。 当在数据库的一个表中增加一列，存储过程可以通过创建一个有合理默认值的新列来避免这种变化。你还可以发现，现有的功能可以使用默认值继续工作。新列的功能可能需要增加，但是你经常不给现有的功能使用有默认值的列或完全忽视现有 列。 浙江万里学院现代物流学院 外文文献译文 4 虽然不是必须的，但我还是经常指出要返回的列使用行返回存储过程。这意味着在一个表中添加一列将不会导致在结果集中返回额外的列，就像它在select *中一样。实际上，我不会写代码 ,

11、因为使用传统的 ADOGetRow()增加一个列，会破坏原有的存储过程。 当然，参数的数量并不是改变一个接口的唯一因素。有时，一个存储过程的含义、作用，将要求你创建一个存储过程的新版本。一个存储过程的新版本可能会发生变化。例如，从接受用户名和明文密码到接受明文用户名和加密密码。更重要的是，一个存储过程可能需要完成其他任务，这个任务 是原来的应用程序无法预料的。 传统的 ADO 处理 我注意到一个主要区别是，当移动到 SqlClient ADO.NET，数据提供程序的是参数的命名而不是位置。在传统的 ADO 中，参数出现在存储过程的创建语句中，所以默认情况下需要添加的参数是完全一样的。你可以为参

12、数命名，但他们没有意义，除非你需要参考的参数对象再次在客户端应用程序中出现，就像你检索一个输出参数的值一样。 由于在 ADO2.6版中，你可以设置参数集合 NamedParameters的属性为 true，而它们将被命名为参数，就像他们是 SqlClient ADO.NET 中的参数一样。我从来没有这种困扰，我不建议大多数应用程序产生对 ADO 的特定版本的依赖。基于服务器的应用，你对数据库和 Web 服务器的完全控制，这可能是一个合理的方案。 参数的位置性质作出修改对存储过程的影响。在上面的例子中，假设我改变了存储过程的签名： CREATE Procedure spPatientsByRes

13、ident ResidentID int = null As 到： CREATE Procedure spPatientsByResident ResidentID int = null, MasterService nvarchar(20)=MEDI As 在有些情况下，这种变化会引起问题。如果你修改传统的 ADO 应用程序，而这个应用程序是调用此存储过程的。例如，如果有一个代码路径只发送 MasterService 参数，将会有一个错误，因为你发送的字符串 MasterService 将被解释为 ResidentID 的参数，而这将不会被转换为整数。如果没有运行错误浙江万里学院现代物流学院

14、 外文文献译文 5 （尽管可能混淆）会导致这两个参数都是字符串，而你只发送 MasterService参数，这将被解释为 ResidentID，并可能导致不希望的行为发生。 当使用 OLEDB 来访问 SQL Server，参数也有位置的。你可以提供参数名称，但它们将被忽略。参数需要添加在 SQL 字符串中允许它们出现的位置。 更改数据库，应用服务工作 存储过程不承担所有数据库的更改管理，但他们是非常重要的工具。在给定的数据库中，我经常提供有向下兼容的存储过程给两个或很多版本的 Web 应用程序。通过认真执行有关规定，是可以在改变但不破坏现有应用程序的情况下，让现有的应用程序继续工作，而数据库

15、变化和增长。我想这是我和大家都同意的一个好主意。 浙江万里学院现代物流学院 外文文献原文 6 外文 文献原文 Title: Managing database changes MaterialSource:http:/www.simple- Author: Douglas Reilly Keep your applications running! In a recent article, I noted that I have had great success in using stored procedures to isolate client applications from da

16、tabase changes. Ensuing reader comments make it clearly, however, that using or not using stored procedures to isolate applicatio ns from database changes is a hot topic. With that in mind, below are some real-life examples of changes made to a database that do not break client applications. First r

17、ule of database maintenance: Do no harm The first rule of maintaining a database that is actively used by one or more applications is to avoid breaking existing applications unless it is absolutely necessary. COM programmers will no doubt be familiar with the concept of interfaces. In COM, an interf

18、ace is designed as an immutable contract between a component and the consumer of the component. So, if a method named ReadEmployees() in an interface named IEmployee accepts a single integer parameter named DivisionID, the consumer can continue to rely on that method being available in any compone n

19、t that implements the IEmployee interface. There may be times, however, when an application needs new functionality. In the IEmployee interface, employees may be in many locations, and location-level managers might need the ability to retrieve only employees for a given location. Adding a parameter

20、named LocationID to ReadEmployee() might be useful to ensure that the component will never expose an employee for another division. For a COM component, adding a parameter breaks the interface, so you might create an IEmployee2 interface and provide both methods in that interface. You can then creat

21、e components that implement both IEmployee and IEmployee2. When dealing with SQL Server stored procedures, we are much more fortunate. SQL Server stored procedures accept parameters that have default values. This makes adding new parameters less traumatic, and allows new functionality to be 浙江万里学院现代

22、物流学院 外文文献原文 7 handled in backward-compatible ways. Heres an example: I was recently asked to expand a system to include not just residents in the department of medicine, but pediatric residents as well. The database was ready for this sort of change, with mapping tables that allowed patients from mu

23、ltiple patient services to be included in a single master service. The database was ready to accept a new master service named PEDI that would include PEDI (pediatric), NEWI (newborn) and NICI (neonatal intensive care) patient services. Looking at the stored procedure cone, there were a number of pl

24、aces in which MEDI was hard coded. This was done because the organization that originally funded the work did not wish to purchase a system beyond their needs at the time. Adding support for a master service internal to the stored procedures was not a problem, but adding external support was viewed

25、as cost prohibitive. So there were a number of stored procedures like this (greatly simplified): CREATE Procedure spPatientsByResident ResidentID int = nullAs SELECT * FROM Patients LEFT OUTER JOIN Residents ON Patients.ResidentID = Residents.ResidentID WHERE (Patients.Service IN (SELECT PatientServ

26、ice FROM ServiceLinks WHERE MasterService = MEDI) AND (ClinicResidentLink.DateDeActivated IS NULL) ORDER BY Residents.Name Adding a parameter for the master service resulted in a stored procedure call like this: CREATE Procedure spPatientsByResident ResidentID int = null, MasterService nvarchar(20)

27、= MEDI As SELECT * FROM Patients LEFT OUTER JOIN Residents ON Patients.ResidentID = Residents.ResidentID WHERE (Patients.Service IN (SELECT PatientService 浙江万里学院现代物流学院 外文文献原文 8 FROM ServiceLinks WHERE MasterService = MasterService) AND (ClinicResidentLink.DateDeActivated IS NULL) ORDER BY Residents.

28、Name This stored procedure worked fine for ASP.NET applications that use ADO.NET. (See the note below for classic ADO considerations.) A somewhat more complex case was a stored procedure declared as follows: CREATE PROCEDURE dbo.spAdminSaveMenu ( MenuID int, MenuTitle nvarchar(255), MenuAdminTitle n

29、varchar(255), MenuTypeID int, MenuBindToID int, UniqueID nvarchar(255) ) AS While it is not entirely clear in the name of the parameters, UniqueID was a GUID used to uniquely identify a user. The initial system that used this database tracked users employing the GUID as a form of indirection, if not

30、 security. The GUID was seen as an opaque ID, completely devoid of meaning, and an identifier that would likely not be used to guess some other users identifier (unlike a sequential-integer identifier). As often happens, a new system was going to use the database, and wanted to use an integer identi

31、fier (already in the database) rather than the GUID. I was able to modify the stored procedure to enable both applications to work well together: CREATE PROCEDURE dbo.spAdminSaveMenu ( MenuID int, MenuTitle nvarchar(255), MenuAdminTitle nvarchar(255), MenuTypeID int, MenuBindToID int, UniqueID nvarc

32、har(255)=null, UserID int = null 浙江万里学院现代物流学院 外文文献原文 9 ) Through use of IsNull(), I was then able to enable the stored procedure to determine whether UniqueID or UserID should be used. Using the SqlClient ADO.NET provider, each application could provide one or the other of the parameters and functio

33、n correctly. Internally, the stored procedure validates that at least one of the last two parameters is non-null. In some cases, there is no easy way to paper over differences between a stored procedure and the changes that are required. In cases like this, rather than break existing applications I

34、will create a new version of a stored procedure, appending the original base name with a 2 or, if a 2 already exists, a 3, and so on. So “spSaveSomething“ would become “spSaveSomething2“ and so on if there is a newer version. I commonly find that stored procedures that are adding an OUTPUT parameter

35、 are great candidates for a new version. When adding a column to a table in your database, a change to the stored procedure can generally be avoided by creating the new column with a reasonable default value. You may also discover that existing functionality can continue to work using the default va

36、lues. Functionality for the new column will need to be added, but you can often leave existing functionality using the default value of the column or ignoring the column altogether. While not required, I commonly specify which columns I want returned using a row-returning stored procedure. This mean

37、s that adding a column to a table will not result in additional columns returned in a result set, as it would in a SELECT * scenario. In practice I do not write code that would break based upon an additional column, as might result using the classic ADO GetRows(). The number of parameters is not the

38、 only thing that can change an interface, of course. Sometimes, the meaning of a stored procedure, or more frequently, its side effects, will require that you create a new version of a stored procedure. A new version of a stored procedure might change, for example, from accepting a username and pass

39、word in clear text, to accepting a username in clear text and an encrypted password. More important, a stored procedure might need to do additional tasks that an original application will not expect. Classic ADO considerations One of the major differences I noticed when moving to the SqlClient ADO.N

40、ET data provider is that parameters are named, rather than positional. In classic ADO, by default you need to add parameters in exactly the same order as they appear in 浙江万里学院现代物流学院 外文文献原文 10 the stored procedure create statement. You can provide names for the parameters, but they have no significan

41、ce, except if you need to refer to the parameter object again in your client application, like when you retrieve the value from an output parameter. As of ADO 2.6, you can set the Named Parameters property of the Parameters collection to true, and parameters will be named much like they are in the S

42、qlClient ADO.NET parameters. I have never bothered with this, and I dont recommend relying on a particular version of ADO for most applications. For server-based applications in which you have total control over the database and web servers, this might be a reasonable option. The positional nature o

43、f parameters has implications for making modifications to stored procedures. Suppose in the example above I changed the signature of a stored procedure from: CREATE Procedure spPatientsByResident ResidentID int = null As to: CREATE Procedure spPatientsByResident ResidentID int = null, MasterService

44、nvarchar(20)=MEDI As There are scenarios in which this change will cause problems. If you modify the classic ADO application that calls this stored procedure, for example, and if there is a code path that sends only the MasterService parameter, there will be an error, because the string you send as

45、the MasterService will be interpreted as the ResidentID parameter and will not likely be converted to an integer. No runtime error (though likely confusion) would result if both parameters were strings and you sent only what you intended as the MasterService parameter, since it would be interpreted

46、as the ResidentID and might result in undesired activity. When using OleDb to access a SQL Server, parameters are also positional. You can provide names for the parameters, but they are ignored. Parameters also need to be added left to right in the SQL String as they appear. Changing databases, working apps Stored procedures are not the be-all and end-all of managing changes in a database, but they can be very important tools. I commonly support two or more