Database item versioning

The invention provides database item versioning, such as items like stored procedures found in databases such as Standard Query Language (SQL) databases. In one embodiment, the invention includes a program such as an editor, a database, a source code control and a mechanism. An editor program provides for editing a stored procedure. The databases includes the stored procedure. The SCC source code control system stores versions of the stored procedure. The mechanism checks in and checks out the stored procedure to provide for effective versioning thereof of the stored procedure.

FIELD OF THE INVENTION

This invention relates generally to databases such as Structured Query Language (SQL) databases, and more particularly to the versioning of items such as text-stream stored procedures (SP's) of such databases.

BACKGROUND OF THE INVENTION

Databases have become increasingly popular and important in today's computerized world. While there are many different types of databases, one common database is the Structured Query Language (SQL) database. SQL is a language used to interrogate and process data in a relational database; SQL commands can be used to interactively work with a database or can be embedded within a programming language to interface to a database. Programming extensions to SQL have turned it into a full-blown database programming language.

Within a database (DB), a stored procedure (SP) is a SQL program that is stored in the database, and which is executed by calling it directly from a client or from a database trigger. When the SQL procedure is stored in the database, for example, it does not have to be replicated in each client. This saves programming effort, especially when different client user interfaces and development systems are used. A stored procedure can be viewed as a standard computer program in some respects, with one important difference: while standard computer programs are usually stored as one or more files on a storage such as a hard disk drive, a stored procedure is not stored as a separate file or files, but rather as text and binary streams within the database itself.

Enterprise (viz., wide-scale) SQL database developers and administrators commonly build mission-critical systems, the failure of which can be catastrophic. Therefore, they are protective of the code, such as stored procedures, that exist on both production and development databases. To store their code in such a manner as to ensure protection, developers usually use basic files, and then utilize long compile scripts to execute. them and create their databases. This is less than desirable, however, since ultimately the database itself stores the code as a binary stream, as has been described.

Thus, once the databases are in production, anyone with system administrator permission can change code such as stored procedures, without any historical record as to what changes have been made. Bugs and errors introduced into the procedures, for example, may be difficult to trace. Furthermore, the database administrator is unable to examine how the code has changed over time. For these and other reasons, there is a need for the present invention.

SUMMARY OF THE INVENTION

The invention relates to the versioning of items such as stored procedures in databases. In one embodiment, a system includes a program such as an editor, a database, a source code control (SCC) system, and a mechanism. An editor program provides for the editing of an item such as a stored procedure of a database such as a SQL database. The database includes the stored procedure (for example, a version of the stored procedure), and the source code control system stores versions of the stored procedure. The mechanism provides for the checking in and checking out of the stored procedures. Besides stored procedures, the invention is applicable to any other item of databases, including but not limited to: views, triggers, constraints, persisted queries and scripts for creating any database object, such as tables, indexes, domains, etc., as those of ordinary skill within the art can appreciate.

In this manner, at least some embodiments of the invention provide for advantages not found in the prior art. For example, the mechanism that provides for checking in and checking out of the stored procedures of the database provides for effective versioning of the stored procedures. The mechanism keeps track of users who wish to edit the stored procedures, and once they have been changed, the mechanism keeps track of the changes that have been made to the stored procedures. Thus, it can be determined when a bug or error has been introduced into a stored procedure, and the database administrator is able to determine the history of changes made to a particular stored procedure.

DETAILED DESCRIPTION OF THE INVENTION

For example, the following detailed description is provided in relation to database stored procedures. However, as those of ordinary skill within the art can appreciate, the invention is not limited to database items that are stored procedures, but can be applied to any other type of database items. Such items include, but are not limited to, views, triggers, constraints, persisted queries and scripts for creating any database object, such as tables, indexes, domains, etc.

Computer

The exemplary hardware and operating environment ofFIG. 1for implementing the invention includes a general purpose computing device in the form of a computer20, including a processing unit21, a system memory22, and a system bus23that operatively couples various system components include the system memory to the processing unit21. There may be only one or there may be more than one processing unit21, such that the processor of computer20comprises a single central-processing unit (CPU), or a plurality of processing units, commonly referred to as a parallel processing environment. The computer20may be a conventional computer, a distributed computer, or any other type of computer; the invention is not so limited.

The hard disk drive27, magnetic disk drive28, and optical disk drive30are connected to the system bus23by a hard disk drive interface32, a magnetic disk drive interface33, and an optical disk drive interface34, respectively. The drives and their associated computer-readable media provide nonvolatile storage of computer-readable instructions, data structures, program modules and other data for the computer20. It should be appreciated by those skilled in the art that any type of computer-readable media which can store data that is accessible by a computer, such as magnetic cassettes, flash memory cards, digital video disks, Bernoulli cartridges, random access memories (RAMs), read only memories (ROMs), and the like, may be used in the exemplary operating environment.

The hardware and operating environment in conjunction with which embodiments of the invention may be practiced has been described. The computer in conjunction with which embodiments of the invention may be practiced may be a conventional computer, a distributed computer, or any other type of computer; the invention is not so limited. Such a computer typically includes one or more processing units as its processor, and a computer readable medium such as a memory. The computer may also include a communications device such as a network adapter or a modem, so that it is able to communicatively couple other computers.

System

Referring next toFIG. 2, a diagram of a system in accordance with an embodiment of the invention is shown. The system includes a database200, a Source Code Control (SCC) system202, a mechanism204, and an editor206. The system may be implemented over one or more computers, such as that described in the preceding section of the detailed description. In the case where the system is implemented over more than one computer, it may be implemented in what is known as a client-server architecture, or in what is known as a distributed system. The invention is not limited to a particular architecture, however, for the implementation of the system ofFIG. 2.

The database200may be any type of database, such as a SQL database known in the art. The database200stores records, or data, and also code, such as stored procedures as have been described in the background section. One such stored procedure is shown as stored procedure208, which is a binary stream including a compiled part210and a text part212(those of ordinary skill within the art can appreciate that the part210and the part212can in another embodiment be stored as separated streams). The text part212acts as the source code of the compiled part210. Those of ordinary skill within the art can appreciate, however, that the text part212may not be present, such that only the compiled part210is present.

The SCC system202stores versions of stored procedures, such as the stored procedure208stored in the database200, and the stored procedure214. The stored procedure214also has a compiled part216and a text part218. Like the stored procedure208, the text part218of the stored procedure214acts as the source code of the compiled part216. Those of ordinary skill within the art can appreciate that the text part218may not be present, such that only the compiled part216is present.

In one embodiment of the invention, the SCC system202is Visual Source Safe, from Microsoft Corp., which is a stand-alone product shipped as part of Visual Studio, a development environment for developing code such as stored procedures available from Microsoft Corp. The Visual Source Safe has Object Linking and Embedding (OLE) automation interfaces, as known in the art, for checking in and checking out different versions of code, such as stored procedures. The invention is not limited, however, to the embodiment utilizing Visual Source Safe and/or Visual Studio.

The SCC system202, as those of ordinary skill within the art can appreciate, is not limited to only handling database (e.g., SQL database) streams, but in other embodiments of the invention, can store other types of streams, such as including but not limited to: C code, Microsoft Word or other word processor documents, etc.

Furthermore, “checking out” as referred to herein refers to the concept of tagging a given item such as a stored procedure such that the item is “in use” by a given user and otherwise cannot be edited or modified by another user (although this second user may be able to use a read-only version of the stored procedure), until the item has been checked back in. Thus, “checking in” as referred to herein refers to the concept of tagging a given item that has been previously checked out by a given user such that this user is finished modifying or editing the item—such that other users are now able to check out the item for their own modification, editing, etc.

Thus, the SCC system202is able to store different versions of the same piece of code—such as-different versions of the same stored procedure. When a stored procedure is checked out, for example, edited, saved to the database, and checked back in, the new version of the stored procedure is saved to the SCC system, in a manner that provides for the changes between the new version and the previous version or versions to be viewed. That is, a change or version history of a given stored procedure can be provided by the SCC system202.

The mechanism204is the manner by which a given stored procedure is actually checked into and out of the SCC system, so that the stored procedure of a database is not modified without versioning of the stored procedure taking place. Versioning is the process by which different versions of the stored procedure are kept track of, for example, by the SCC system202. Thus, as shown inFIG. 2, a stored procedure of the database200cannot be accessed without the mechanism204knowing about the access, such that if modifications are made thereto, the mechanism204is able to indicate such changes as a new version of the stored procedure via the SCC system202. In another embodiment, the database system200is itself able to invoke the mechanism204, on any read access to the stream212, to provide the benefits of the invention to users who do not choose to use the editor206as the computer program used to manipulate the stream212, as described in the next paragraph.

The editor206is a specific type of a computer program that manipulates the stored procedures of the database200. In particular, the editor206is a program that provides for the editing of the stored procedures of the database200. The invention is not so limited to a program that is an editor program, however—that is, the invention is amenable to any type of computer program that is used to manipulate database items such as stared procedures of the database200. In one embodiment, the editor206is part of the Visual Studio development environment already described, such that the editor206includes a “data view” of all the tables and the stored procedures, among other data and code, stored in the database200. The invention is not so limited, however.

The system ofFIG. 2operates as follows. The editor206desires to retrieve a stored procedure of the database200for editing and modification. Thus, the editor206requests to check out the stored procedure, such that the mechanism204checks out the desired stored procedure thereto. The mechanism204does this as follows. It retrieves the stored procedure as stored in the database200, as well as the same stored procedure as stored in the SCC system202. If the version of the stored procedure in the database200is the same as in the SCC system202, then this version is provided to the editor206. Otherwise, the editor206has the ability to choose from either version as the version it wishes to edit and modify. If it is the SCC system version, then this version is automatically saved to the database200as the database version, too, by the mechanism204. Thus, the version of the stored procedure at the database200can be either identical to or different than the version of the stored procedure at the SCC system202.

Once the editor206has edited and modified the stored procedure, it is saved to the database200as the database version of the stored procedure. This version is then retrieved by the mechanism204to check into the SCC system202as the newest version of the stored procedure as saved by the SCC system202. In other words, the mechanism204checks in (i.e., saves) the stored procedure into the SCC system200, checking in the stored procedure as has been saved to the database200.

Methods

In this section, a check-out method and a check-in method, according to different embodiments of the invention, are described. These methods can be performed, for example, by the mechanism204of the system ofFIG. 2that has been described in the preceding section, although the invention is not so limited. These computer-implemented methods are desirably realized at least in part as one or more programs running on a computer—that is, as a program executed from a computer-readable medium such as a memory by a processor of a computer. The programs are desirably storable on a machine-readable medium such as a floppy disk or a CD-ROM, for distribution and installation and execution on another computer.

Referring first toFIG. 3, a computer-implemented check-out method according to an embodiment of the invention is shown. The check-out method ofFIG. 3is a manner by which a stored procedure can be checked out from a SCC system, according to one embodiment. It may be performed, for example, when an editor or other program indicates to the mechanism that it wishes to edit or otherwise manipulate the stored procedure. In300, the desired stored procedure is checked out from the SCC system—that is, it is indicated to the SCC system that the stored procedure is to be indicated as checked out. In302, the SCC system version of the stored procedure stream is retrieved, and in304, the database version of the stored procedure stream is retrieved.

In306, the SCC system version of the stored procedure stream is compared to the database version of the stored procedure stream. If they are the same, then the method is done at308—that is, the editor or other program can now conduct editing or other manipulation of the stored procedure. Otherwise, in310, the user is asked to select either the SCC version), and the method also ends at308, where the editor or other program can now conduct editing or other manipulation of the stored procedure.

Once the editor or other program has finished its editing or other manipulation of the stored procedure, than the procedure must be checked back into the SCC system. This is accomplished by the method shown inFIG. 4. The method ofFIG. 4assumes that the stored procedure stream has already been saved by the editor or other program back to the database; however, the invention is not so limited—for example, this functionality can also be performed within the context of the method ofFIG. 4, too. In400, then, the stored procedure stream is retrieved by the mechanism, from the database (if the stream as modified or edited has not already been saved to the database, then this is first accomplished).

Next, in402, this version of the stored procedure stream is checked back into the SCC system. That is, the stored procedure stream is saved to the SCC system as the newest version of this stored procedure. Furthermore, the SCC system is also told to indicate that the stored procedure stream has now been checked in, such that it may be subsequently check out. In at least one embodiment of the invention, for example, a stored procedure stream cannot be checked out unless it is indicated in the SCC system as having been checked in—that is, two different programs cannot check out the same stored procedure stream at the same time. The invention is not so limited, however. The method ofFIG. 4ends at404.

CONCLUSION

Versioning of stored procedures of databases has been described. Although specific embodiments have been illustrated and described herein, it will be appreciated by those of ordinary skill in the art that any arrangement which is calculated to achieve the same purpose may be substituted for the specific embodiments shown. For example, the specification has been described in relation to database items that are stored procedures, but the invention itself is not so limited. This application is intended to cover any adaptations or variations of the present invention. For example, the invention can be used in relation to any type of database item, such as views, triggers, constraints, persisted queries and scripts for creating any database object, such as tables, indexes, domains, etc. Therefore, it is manifestly intended that this invention be limited only by the following claims and equivalents thereof.