Method for persisting a schedule and database schema Number:7,412,451 from the United States Patent and Trademark Office (PTO) owispatent

Title: Method for persisting a schedule and database schema

Abstract: The invention provides a database schema for representing a workflow process definition (e.g., a schedule). The database schema may also include one or more bindings associated with the schedule, as well as persisted state information and data. The invention further includes a method for storing schedule information in a storage medium, as well as a computer-readable medium having a data structure stored thereon. The storage of schedule-related information provides for ease of version control, and ease of distribution, for example, where several engines point to the same database as a single source of transaction processing or workflow schedule definitions. The definitional database schema may be advantageously employed to reconstruct the schedule definition language solely from information in a database. In addition, the schema may be used for storing instances of running schedules and data associated therewith. This allows ease of schedule state and data monitoring using existing database query tools.

Patent Number: 7,412,451 Issued on 08/12/2008 to McCrady,   et al.

---

Inventors:	McCrady; Donald J. (Redmond, WA), Mital; Amit (Kirkland, WA), Sivakumar; A. S. (Issaquah, WA)
Assignee:	Microsoft Corporation (Redmond, WA)
Appl. No.:	10/899,262
Filed:	July 26, 2004

---

Related U.S. Patent Documents

---

	Application Number	Filing Date	Patent Number	Issue Date
	09741222	Dec., 2000	6901405

---

Current U.S. Class:	707/100 ; 707/10; 707/101
Current International Class:	G06F 17/30 (20060101); G06F 15/16 (20060101)
Field of Search:	707/100

---

References Cited [Referenced By]

---

U.S. Patent Documents

5261069	November 1993	Wilkinson et al.
5524241	June 1996	Ghoneimy et al.
5581691	December 1996	Hsu et al.
5630069	May 1997	Flores et al.
5706429	January 1998	Lai et al.
5857195	January 1999	Hayashi et al.
5862327	January 1999	Kwang et al.
5918218	June 1999	Harris et al.
5940839	August 1999	Chen et al.
5960420	September 1999	Leymann et al.
6009405	December 1999	Leymann et al.
6199068	March 2001	Carpenter
6446089	September 2002	Brodersen et al.
6496831	December 2002	Baulier et al.

Other References

Stillerman, J., et al.; "WWW Interfaces for Runtime Relational Database Applications", Fusion Engineering and Design, vol. 48, No. 1-2, Aug. 2000, p. 63-68. cited by other.

Primary Examiner: Mahmoudi; Tony
Attorney, Agent or Firm: Amin, Turocy & Calvin, LLP

---

Parent Case Text

---

CROSS REFERENCE TO RELATED APPLICATIONS

This application is a continuation of U.S. Ser. No. 09/741,222, filed on Dec. 20, 2000, entitled METHOD FOR PERSISTING A SCHEDULE AND DATABASE SCHEMA, the entirety of which is incorporated herein by reference.

---

Claims

---

What is claimed is:

1. A database schema for storage of process information in a database stored on a computer readable storage medium, comprising: at least one table representing process definition information derived from a process definition language representation of a process; and at least one table for storing runtime data associated with a process including storing a schedule state in the database when a transaction in the schedule is committed, storing a schedule state in the database when a compensation process is started and finished, and storing a schedule state in the database when schedule is dehydrated.

2. The schema of claim 1, further comprising a second table representing runtime information relating to execution of an instance of a schedule.

3. The schema of claim 2, wherein the process comprises a schedule and the second table is adapted to represent at least one of the state of transactions associated with an instance of the schedule, messages associated with an instance of the schedule, and the state of actions associated with an instance of the schedule.

4. The schema of claim 1, further comprising a second table representing runtime information relating to execution of an instance of the process.

5. The schema of claim 4, wherein the process comprises a schedule and the second table is adapted to represent at least one of the state of transactions associated with an instance of the schedule, messages associated with an instance of the schedule, and the state of actions associated with an instance of the schedule.

6. A computer-readable storage medium having stored thereon a data structure, comprising: a first data component with data representing a definition of a process; a second data component with data representing instance information relating to an instance of the process; and a third data component with data representing runtime data relating to an instance of the process, wherein the runtime data comprises at least one of schedule state when a transaction in the schedule is committed, a schedule state when a compensation process is started and finished, or a schedule state when a schedule is dehydrated.

7. The computer-readable medium of claim 6, wherein the data structure further comprises a fourth data component with data representing binding information associated with the process.

8. A database schema system, comprising: means for storing a process definition in a database according to at least one definition table of a database schema; means for storing instance information associated with the process in the database according to at least one instance table of the database schema; and means for storing runtime data associated with the process in the database according to at least one data table of the database schema including storing a schedule state in the database when a transaction in the schedule is committed, storing a schedule state in the database when compensation process is started and finished, and storing a schedule state in the database when a schedule is dehydrated.

9. A database schema for storage of process information in a database stored on a computer readable storage medium, comprising: a definitional element with at least one definition table for storing a process definition; a runtime element with at least one instance table for storing instance information associated with an instance of the process; and at least one data table for storing runtime data associated with the process including storing a schedule state in the database when a transaction in the schedule is committed, storing a schedule state in the database when a compensation process is started and finished, and storing a schedule state in the database when schedule is dehydrated.

10. The database schema of claim 9, wherein the runtime data comprises at least one of the state of transactions associated with an instance of the process, messages associated with an instance of the process, and the state of actions associated with an instance of the process.

11. The database schema of claim 10, wherein the definitional element further comprises at least one table for storing binding information associated with the process.

12. The database schema of claim 11, wherein the process comprises a schedule.

13. The database schema of claim 12, wherein at least a portion of the schema is normalized.

14. The database schema of claim 9, wherein the process comprises a schedule.

15. The database schema of claim 9, wherein the definitional element further comprises at least one table for storing binding information associated with the process.

16. The database schema of claim 9, wherein at least a portion of the schema is normalized.

17. A computer-readable storage medium having computer-executable instructions for performing steps comprising: querying a database having schedule-related information stored therein according to a database schema to obtain a result set, wherein the database schema comprises a definitional element with at least one definition table for storing a process definition, and a runtime element with at least one instance table for storing instance information and at least one data table for storing runtime data, and wherein the result set comprises at least a portion of the schedule-related information; storing schedule-related information in the database according to the database schema, wherein a schedule state is stored in the database when a transaction in the schedule is committed, a schedule state is stored in the database when a compensation process is started and finished, and a schedule state is stored in the database when a schedule is dehydrated, and providing the result set to a monitoring device; and monitoring the result set via the monitoring device.

18. A computer-readable medium having computer-executable instructions for performing steps comprising: storing a process definition in a database according to at least one definition table of a database schema; storing instance information associated with the process in the database according to at least one instance table of the database schema; storing runtime data associated with the process in the database according to at least one data table of the database schema; storing a schedule state in the database when a transaction in the schedule is committed; storing a schedule state in the database when a compensation process is started and finished; and storing a schedule state in the database when a schedule is dehydrated.

19. The computer-readable medium of claim 18, further comprising computer-executable instruction for: querying the database according to the database schema to obtain a result set, wherein the result set comprises at least a portion of the runtime data associated with the process; providing the result set to a monitoring device; and monitoring the result set via the monitoring device.

20. A method of monitoring a schedule having schedule-related information associated therewith, comprising: querying a database having schedule-related information stored therein according to a database schema to obtain a result set, wherein the database schema comprises a definitional element with at least one definition table for storing a process definition, and a runtime element with at least one instance table for storing instance information and at least one data table for storing runtime data, and wherein the result set comprises at least a portion of the schedule-related information; storing schedule-related information in the database according to the database schema, wherein a schedule state is stored in the database when a transaction in the schedule is committed, a schedule state is stored in the database when a compensation process is started and finished, and a schedule state is stored in the database when a schedule is dehydrated, and providing the result set to a monitoring device; and monitoring the result set via the monitoring device.

21. The method of claim 20, wherein monitoring the schedule-related information comprises history reporting.

22. The method of claim 20, wherein monitoring the state information includes event monitoring.

23. A method for storing process information in a database, comprising: providing a database schema having a definitional element with at least one definition table for storing a process definition; and a runtime element with at least one instance table for storing instance information and at least one data table for storing runtime data; storing a process definition in the database according to the at least one definition table; storing instance information associated with the process in the database according to the at least one instance table; and storing runtime data associated with a process in the database according to the at least one data table, wherein storing runtime data associated with a process in the database according to the at least one data table comprises: storing a schedule state in the database when a transaction in the schedule is committed; storing a schedule state in the database when a compensation process is started and finished; and storing a schedule state in the database when a schedule is dehydrated.

24. The method of claim 23, wherein storing instance information in the database according to the at least one instance table comprises storing binding information in the database.

25. The method of claim 23, wherein storing runtime data in the database according to the at least one data table comprises storing at least one transaction state, actions, and processes executed, and messages in the database.

26. The method of claim 23, wherein storing runtime data in the database according to the at least one data table comprises storing at least one transaction state, actions, and processes executed, and messages in the database.

27. The method of claim 23, wherein storing instance information in the database according to the at least one instance table comprises storing binding information in the database.

---

Description

---

TECHNICAL FIELD

The present invention relates to transaction processing in computer systems. More particularly, the invention relates to a method for storing and monitoring a transaction schedule and information related thereto in a database, and a database schema therefor.

BACKGROUND OF THE INVENTION

Schedules are applications which may be defined for transaction processing systems, such as computer systems. A schedule instance may be executed in such a system, whereby one or more transactions within the schedule are executed. In executing an instance of such a schedule and its component transactions, information associated therewith may need to be stored or persisted in a storage medium, such as a memory system.

Storage of information in a storage medium may be facilitated using a database in conjunction with a database management system (DBMS). A database is a collection of related data that may be stored on a nonvolatile memory medium. Data in the database is commonly organized in a two-dimensional row and column form called a table. A database typically includes multiple tables.

A table is an object in the database having at least one record and at least one field within each record. Thus, a table may be thought of as an object having two-dimensional record and field organization. A record is a row of data in the table that is identified by a unique numeric called a record number. A field is a subdivision of a record to the extent that a column of data in the table represents the same field for each record in the table. Each field in a record is identified by a unique field name and a field name remains the same for the same field in each record of the table. Therefore, a specific datum in a table is referenced by identifying a record number and a field name.

A database management system (DBMS) is a control system that supports database features including, but not limited to, storing data on a memory medium, and retrieving data from the memory medium. Data in the database is typically organized among a plurality of objects that include, but are not limited to, tables and queries. An individual table or query may be referred to as a record source because it is a source of data or records from the database. A query object is an executable database interrogation statement, command, and/or instruction that communicates to the database management system the identity and location of data being extracted from the database. The product of an executed query is called a result set. A result set may be stored and/or manipulated as a two-dimensional object similar to the table discussed previously.

A relational database is a common database type managed by a database management system. Data in a relational database is distributed among multiple record sources that are typically related, or normalized, in a manner designed to minimize redundant data in the database, minimize the space required to store data in the database, and maximize data accessibility. Record sources in a database may be related to one another via key fields. A normalized database is one where each record source in the database is directly related to at least one other record source in the same database by key fields.

A key field can be a primary key or a foreign key. A primary key is a field or combination of fields in a record source that includes unique data for each record in the table. A foreign key is any non-primary key in a record source that is the basis for a direct relation with any other record source. A database remains a relational database regardless of the degree of normalization that exists. Record sources in a normalized relational database are typically related. However, a relational database may be normalized even if the database is disconnected in that at least one record source in the database is not related to any other record source by a key field.

Relationships between any two record sources in a relational database may be either direct or indirect. Such a relationship may also be referred to as a relation or join. A direct relationship exists between two record sources if there is no intervening record source in the relationship path therebetween. An indirect relationship exists if there is at least one intervening record source in the relationship path between two record sources.

The record sources in a relational database and the relationships therebetween define the geography of a database, which may be called a database schema. A sub-schema of the database is any subset of the full database schema that is defined by a query, a result set of a query, or any other subset of record sources from the database. A database schema and database sub-schema may be displayed visually in graphic form as a graph having edges or arrows representing relationships between record sources, and vertices, also known as nodes or tables, representing the record sources at either end of a relationship.

Queries are used to access data in a database. A query may be constructed in a Structured Query Language (SQL) that may or may not be based on the American National Standards Institute (ANSI) standard SQL definition. To access data in a database, a user may construct a query using an SQL. Executing a query is called a join or joining wherein each relation identified in the query is joined during execution to retrieve the desired data from a database.

Schedule-related information comprises many diverse types of data. Prior transaction processing systems do not provide an effective mechanism for the storage any retrieval of such schedule-related information and data to and from a storage medium, such as a database. It is desirable to store information relating to a schedule definition in an efficient manner where the specifics of the definitional language may be easily reconstructed solely from information in a database. At the same time, updates to the database with runtime information may need to be fast, possibly at the expense of storage efficiency. Thus, fully normalized database structures are not an optimal match for the requirements of schedule information storage. In addition, schedule-related information may include binding information. One or more bindings may be associated with a schedule, to allow instances of a single schedule to be executed on different machines or systems with different hardware technologies. Conventional database structures and schemas do not provide efficient, easily-portable storage solutions for schedule-related information storage. Moreover, current schedule storage methods and systems do not provide for ease of querying and monitoring schedule-related information and data.

SUMMARY OF THE INVENTION

The present invention relates to a first (e.g., definitional) database schema for representing a process definition (e.g., a schedule), and a second (e.g., runtime) schema for representing the process definition, one or more bindings associated therewith, and persisted state information and data. The invention further includes a method for storing schedule information and/or data associated therewith in a storage medium, as well as a computer-readable medium having a data structure stored thereon. Database storage of the process definition information allows for ease of version control, and ease of distribution, for example, where several runtime engines or systems point to the same database as a single source of definitions. The table or tables in the database schema corresponding to the process definition include class level information. In this regard, the definitional database schema may be advantageously employed to reconstruct the schedule definition language solely from information in a database.

In addition to the definitional information, the second (e.g., runtime) schema may be used for storing instances of running schedules. The schema accordingly includes one or more tables related to instance information. The instance tables may comprise, for example, information describing what instances of a schedule are running and what definitions they correspond to, as well as positional information, such as what actions are currently live, how many messages are currently pending, and other schedule state information. Moreover, the schedule definition and binding information portions or tables of the schema may be normalized to a higher degree than are those relating to runtime information. In this way, an efficient storage methodology and system are provided for less frequently accessed tables (e.g., tables related to schedule definition and binding information), while fast access is provided to runtime information. Thus, the invention provides for optimal storage or persistence of the diverse types of schedule-related information.

The runtime database schema may also include data associated with one or more schedule instances. This data may include the value of specific messages, etc. For example, the schedule state information and data may be persisted to the database during execution at transaction boundaries. Schedule data and state information may also be persisted to the database during dehydration operations based on latency hints or attributes. The database schema provides a format for storing the persisted information in a relational database for ease of access and monitoring by a user.

The runtime schema may be fixed, thus allowing the use of monitoring and other tools to query against the database. Such database queries may be employed to indicate the status of a running workflow process, how specific instances of the process are running, (e.g., for each instance of the process, the current position in the schedule, etc.), and the data associated therewith. The ability to use common tools for obtaining this information is enabled by the use of overlapping schemas for definitional purposes and to describe runtime instances and information associated therewith.

The runtime schema may be a superset of the definitional schema, including all the information therefrom, as well as the instance related tables and runtime data tables. Because the runtime schema includes both static definitional information, as well as instance information (which may be accessed frequently), these different portions of the schema may be normalized differently. Accordingly, the definitional information may be normalized to minimize storage space requirements by reducing redundant information storage, whereas the instance information may be normalized to a lesser degree (or not at all) to allow for fast access thereto. This flexibility in normalization provides for runtime efficiency when data and state information is persisted to disk, for example, where persistence at transaction boundaries, and/or dehydration (and rehydration) are employed.

In accordance with one aspect of the invention, there is provided a database schema for storage of process information in a database, comprising a table representing process definition information derived from a process definition language representation of a process.

In accordance with another aspect of the invention, there is provided a database schema for storage of process information in a database. The database schema comprises a definitional element with at least one definition table for storing a process definition, and a runtime element with at least one instance table for storing instance information and at least one data table for storing runtime data.

In accordance with yet another aspect of the present invention, there is provided a method for storing process information in a database. The method comprises providing a database schema, having a definitional element with a definition table for storing a process definition, and a runtime element with at least one instance table for storing instance information and at least one data table for storing runtime data. The method further comprises storing a process definition in the database according to the definition table, storing instance information in the database according to the instance table, and storing runtime data in the database according to the data table. Another aspect of the invention provides for a computer-readable medium having computer-executable instructions for carrying out the steps of the above method.

In accordance with still another aspect of the invention, there is provided a computer-readable medium having stored thereon a data structure. The data structure comprises a first data component with data representing a definition of a process, a second data component with data representing instance information relating to one or more instances of the process, and a third data component with data representing runtime data relating to the process. The data structure thus includes all aspects of a schedule, including the schedule definition elements, and runtime elements. This data structure allows for efficient recreation of definitional language representations of the schedule, and monitoring of schedule status information and data using existing database query tools.

In accordance with yet another aspect of the invention, there is provided a method of monitoring a schedule having schedule-related information associated therewith. The method comprises querying a database having schedule-related information stored therein according to a schema to obtain a result set, wherein the schema comprises a definitional element with at least one definition table for storing a process definition, and a runtime element with at least one instance table for storing instance information and at least one data table for storing runtime data. The result set may comprise at least a portion of the schedule-related information. The method may further comprise providing the result set to a monitoring device, and monitoring the result set via the monitoring device. The invention thus enables a user to monitor the progress of one or more instances of a schedule using existing database query tools, which is advantageous in diagnostic and troubleshooting applications. According to another aspect of the invention, the method may further include storing schedule-related information in the database according to the database schema. In addition, the invention includes a computer-readable medium having computer-executable instructions for performing the above methodology.

To the accomplishment of the foregoing and related ends, the invention comprises the features hereinafter fully described and particularly pointed out in the claims. The following description and the annexed drawings set forth in detail certain illustrative aspects of the invention. The illustrations and description below are indicative, however of but a few of the various ways in which the principles of the invention may be employed. Other objects, advantages and novel features of the invention will become apparent from the following detailed description of the invention when considered in conjunction with the drawings.

BRIEF DESCRIPTION OF THE DRAWINGS

The foregoing and other aspects of the invention will become apparent from the following detailed description of the invention and the attached drawings in which:

FIG. 1A is a schematic diagram illustrating an exemplary database schema in accordance with an aspect of the present invention;

FIG. 1B is a schematic diagram illustrating another exemplary database schema in accordance with the invention;

FIG. 2A is a schematic diagram illustrating yet another exemplary database schema in accordance with the invention;

FIG. 2B is a schematic diagram further illustrating the exemplary database schema of FIG. 2A;

FIG. 2C is a schematic diagram further illustrating the exemplary database schema of FIGS. 2A-2B;

FIG. 2D is a schematic diagram further illustrating the database schema of FIGS. 2A-2C;

FIG. 2E is a schematic diagram further illustrating the database schema of FIGS. 2A-2D;

FIG. 2F is a schematic diagram further illustrating the database schema of FIGS. 2A-2E;

FIG. 3 is a flow diagram illustrating an exemplary method for storing process information in a database in accordance with the invention;

FIG. 4 is a flow diagram illustrating an exemplary method of monitoring process information in a database according to the invention; and

FIG. 5 is a schematic diagram illustrating an exemplary environment in which the various aspects of the invention may be carried out.

DETAILED DESCRIPTION OF THE INVENTION

The following is a detailed description of the present invention made in conjunction with the attached figures, wherein like reference numerals will refer to like elements throughout. According to one aspect of the invention, a database schema is provided, having a definitional element or component with at least one table representing process definition information derived from a process definition language representation of a process. The schema may further comprise a runtime element with at least one instance table for storing instance information and at least one data table for storing runtime data.

FIG. 1A illustrates one exemplary database schema 2 for storage of process information in a database (not shown) in accordance with the invention. The schema 2 includes a table 4 having process definition information 6 therein. A user may advantageously extract or reconstruct a definitional language representation of a schedule or other process from the process definition information 6 in the database.

Another exemplary database schema 8 is illustrated in FIG. 1B, which may be a superset of the schema 2 of FIG. 1A. Schema 8 comprises a definitional element 10 with at least one definition table 12 for storing a process definition 14. Like the database schema 2 of FIG. 1A, the process definition 14 may be employed to extract or reconstruct a definitional language representation of a schedule or other process. The schema 8 further comprises a runtime element 16 with at least one instance table 18 for storing instance information 20, as well as at least one data table 22 for storing runtime data 24.

The exemplary schemas 2 and 8 of FIGS. 1A and 1B provide a complete definition of a transaction processing or workflow schedule. Thus, it is possible to reconstruct the schedule definition language representation of a schedule solely from the information in the database. The schema 8, moreover, provides a data structure (e.g., runtime element 16) for storing and managing runtime data 24 related to one or more instances of a schedule in a database. The runtime information or data 24 may comprise, for example, schedule state information, data, and/or messages (not shown) associated with a running schedule application. This runtime data 24 may be accessed using existing database query tools and database management systems (DBMSs) to provide a user with status and/or data associated with one or more processes. This allows users to easily monitor schedule execution progress, which is advantageous in diagnostic or debugging situations.

FIGS. 2A-2F graphically illustrate another exemplary database schema 28 in accordance with the invention. The schema 28 comprises various tables and relationships therebetween, as described hereinafter. The various tables of database schema 28 are described now, after which the fields within the tables and the relationships between tables are discussed.

Schema Tables

FIG. 2A illustrates a portion of schema 28 having a Schedule table 30, a Context Table 31, a Module table 32, a Message table 33, a Case Rule table 34, a Port table 35, and a GroupTb1 table 36. Referring also to FIG. 2B, the database schema 28 further comprises a Sequence table 40, a Task table 41, a SourceSink table 42, a Block table 43, an Action table 44, and a Contextlnvocation table 45.

FIG. 2C illustrates another portion of the schema 28 comprising a RunCOMObjects table 50, a RunCOMField table 51, a RunContexts table 53, and a RunInstances table 54. The schema 28 further comprises a RunContextStatus table 55, a Process table 56, and a Parts table 57. Referring to FIG. 2D, the schema 28 also includes a Call table 60, a CallMsgTable table 61, a CallPortTable table 62, a SwitchTh1 table 63, a CaseTh1 64, and a Copy table 65. Referring also to FIG. 2E, schema 28 includes a Cut table 70, a MapTb1 table 71, an Assignment table 72, a Connection table 73, a Partition table 74, a Connect table 75, and a PartitionElem table 76. As illustrated in FIG. 2F, the database schema 28 further comprises a Binding table 80, a COMPortBinding table 81, a COMMsgBinding table 82, a COMFieldBinding table 83, a ContextBinding table 84, a RuleBindingAll table 85, and a Match table 86.

Schema Table Fields

The various tables of the database schema 28 illustrated in FIGS. 2A-2F comprise fields having field names, exemplary data types, and exemplary field sizes as described hereinafter. It will be appreciated that other field names, data types and field sizes are possible and are contemplated as within the scope of the present invention. Referring now to FIG. 2A, the Schedule table 30 includes the following fields: ScheduleID: char(36); ScheduleName: varchar(100); ContextID: int; ModuleID: char(36); and ProcessID: int. The Schedule table 30 has a primary key ScheduleId and foreign keys FK1 ModuleID and FK2 ProcessID.

The Context table 31 comprises the following fields: ContextID: integer (identity); ContextName: varchar(100); ScheduleID: char(36); CompProcessID: integer, (null allowed); and TransactOpt: bit. In the Context table 31, the TransactOpt Boolean field specifies a transaction or a try-catch like construct. Since the context may be applied to multiple processes and actions, the tables that define actions and processes contain a reference to the context of which they are a part. The CompProcessID field of table 31 refers to a compensation process. Compensation is a process whereby an action may be taken upon failure or abortion of an action or transaction. Such an action may include, for example, rolling-back or otherwise modifying data associated with an action or transaction, sending a message, etc. A compensation process may refer to a specific action, message, etc. which is to be executed when it has been determined that the data or work associated with an action or transaction needs to be compensated. The Context table 31 further comprises a primary key ContextID and foreign keys FK1 ScheduleID and FK2 CompProcessID.

The Module table 32 comprises ModuleID: char(36), and Module Name: varchar(100) fields. The Module table 32 further includes a primary key ModuleID. It will be noted that foreign keys in the binding and schedule tables refer to module. Message table 33 comprises fields MessageID: integer (identity); MessageName: varchar(100); and ScheduleID: char(36). The Message table 33 also comprises a primary key Message ID and a foreign key FK1 ScheduleID. CaseRule table 34 includes fields for RuleID, RuleName, and ScheduleID: char(36), with a primary key RuleID and a foreign key FK1 ScheduleID. Port table 35 includes fields PortID: integer (identity); PortName: varchar(100); and ScheduleID: char(36). The Port table 35 further includes a primary key PortID and a foreign key FK1 ScheduleID.

GroupTb1 table 36 comprises the following fields: GroupID: char (16); Group Name: varchar(100); and SystemName: varchar(100). The GroupTb1 table 36 further comprises a primary key GroupID. The GroupID may be passed by a runtime engine, and may be a COM+ app id assigned while creating a group. It will be appreciated that other schemas are possible within the scope of the invention in which users may define associations between groups and modules.

Referring also to FIG. 2B, the Sequence table 40 comprises fields ProcessID:

integer; ScheduleID: char(36); ContextID: integer, (null allowed); and cProcessID: integer, (null allowed). The ProcessID field is a primary key as well as a foreign key into the Process table, as the process table contains all processes. The cProcessID field is the id of an optional tail process in a schedule sequence. The Action table 44 may have an entry pointing back to the Sequence table 40 for actions that are part of a sequence. The Sequence table 40 further comprises a primary key ProcessID, and foreign keys FK1 ProicessID, FK2 ScheduleID, FK3 ContextID, and FK4 cProcessID.

The Task table 41 comprises the following fields: ActionID: integer; ContextID: integer, (null allowed); and ChoiceIsAll: bit. The ChoiceIsAll field is true when all actions which are part of a task need to execute in parallel. The ActionID field is both a primary key and a foreign key (e.g., FK1) of the Task table 41. In the Task table 41, the ChoiceisAll field determines whether task property is, for example, All or None. The SourceSink table 42 comprises: ActionID: integer; ContextID: integer, (null allowed); IsSource: bit; MessageID: integer; and PortID: integer fields, in which the IsSource field is true if the action is of source type and false if the action is of sink type. The SourceSink table 42 includes a primary key ActionID and foreign keys FK ActionID, FK2 MessageID, and FK3 PortRefID.

The Block table 43 comprises fields for BlockID; ScheduleID: char(36); ContextID: integer, (null allowed); and pSequenceID. The Block table 43 includes a primary key BlockID and foreign keys FK1 pSequenceID and FK2 ContextID. The Action table 44 comprises Action: integer (identity); ContextID: integer, (null allowed); pSequence: integer, (null allowed); SortID: smallint, (null allowed); pTaskID: integer, (null allowed); and ActionType: varchar(10) fields. This table 44 has a primary key Action, and a foreign key FK1 BlockID.

An action may be part of a sequence or task, in which case, one of the fields pSequence and pTaskID may have a value, however both of them will not. The ActionType field may have one of the following values: `call`, `source`, `sink`, `return`, or `release`. In the Action table 44, the ActionType field may be an enum that identifies the type of action. Note that the ActionID may be the key for the tables corresponding to different types of actions. The pSequence field is a sequence id in the container sequence. The pTaskID field is the container task if it is part of the task. The ContextInvocation table 45 includes fields ActionID: integer; ContextID: integer; and IsReturn: bit; where the IsReturn field value determines whether an action is a return or release. Table 45 further includes a primary key ActionID and foreign keys FK1 ActionID and FK2 ContextID.

In FIG. 2C, the RunCOMObjects table 50 comprises RunID: char(36); PortRef: integer; Time: datetime; and ObjectData: image fields, and may be used to persist objects that support an Ipersistable interface. The ObjectData field is of image type which can store data BLOBs. The RunCOMObjects table primary keys RunID and PortRef, as well as foreign keys FK1 RunID and FK2 PortRef. The RunCOMField table 51 comprises the following fields: RunCOMFieldID: integer (identity); MsgID: integer; RunID: char (36); FieldLocation: varchar (100); BindingID: integer; Value: text; and Time: datetime. The table 51 has a primary key RunCOMFieldID, as well as foreign keys FK1 RunID, FK2 FieldLocation, FK2 BindingID, and FK2 MsgID. The RunCOMFieldID field of table 51 may be used as an identity field. None of the other fields can be combined to form a key since multiple calls could be made to the same method in the context of a schedule instance. The BindingID field may be used to identify a particular binding as there could be multiple bindings for the same schedule. In this case, the MsGID field alone is not enough to identify a binding. The Value field is of type text, which allows it to store content of an arbitrarily large size.

The RunProcessActions table 52 fields include RunID: char (36); ProcessID: integer; ActionID: integer; StartTime: datetime; EndTime: datetime; and ContextID: integer. The table 52 has primary keys ContextID, RunID, ProcessID, ActionID, and StartTime, along with foreign keys FK1 ProcessID, FK2 RunID, FK3 ActionID, and FK4 ContextID.

The table 52 may be used to store the runtime state of actions and processes (e.g., transactions) when a schedule instance is persisted. Each row may have the ActionID or ProcessID fields set but not both. Since the entries to the RunProcessActions table 52 are added while a schedule is running, fast access thereto may be provided thereto. The ContextID field is included in the table 52 to make it easy to query for the actions executed in the scope of a particular transaction. While it may be possible to obtain this information by querying the other tables, this information may need to be accessed frequently by a monitoring tool and the query to get the information may be complex. Thus, the ContextID field is provided in the table 52 in order to assure fast querying capabilities. In the RunProcessActions table 52, each row may include either the ProcessID or ActionID to give the status on that action or process.

The RunContexts table 53 fields comprise RunID: char(36); ContextID: integer; RetryCnt: integer, (null allowed); and NextRetryTime: datetime, (null allowed). The table 53 includes primary keys RunID and ContextID, as well as foreign keys FK1 ContextID and FK2 RunID. This table 53 may be used to track information about how many times a transactional context was retried. In this regard, a runtime engine may store information in the field NextRetryTime on when the transaction should be retried.

In the RunInstances table 54, the following fields are provided: RunID: char(36); ModuleID: char(36); Owner: varchar(50); Status: varchar(30); DeHydrationCnt: integer, (null allowed); LastDehydrated: datetime, (null allowed); LastRehydrated: datetime, (null allowed); StartTime: datetime; and EndTIme: datetime, (null allowed). The Owner field value may indicate the person (e.g., NT login) who started the schedule. The table 54 may contain an entry for each module that was executed. In addition, the status field value may have one of the values: Running, Done, Paused or Dehydrated. The RunInstances table includes a primary key RunID and a foreign key FK1 Module ID.

The RunContextStatus table 55 fields include: RunID: char (36); ContextID: integer; Time: datetime; and Status: varchar(20). This table 55 may track the state of transactions in a schedule. Any time a transaction is aborted or committed, an entry may be added to the table 55. The possible values for the Status field include: Pending, Committed, Aborted, Compensating, and Compensated. As an example, the "Pending" state will be set when a compensation process, which needs to be executed for an aborted transaction, is started. This table 55 includes primary keys Time, Status, ContextID, and RunID, as well as foreign keys FK1 ContextID and FK1 RunID.

In the Process table 56, ProcessID: integer (identity), and Type: varchar(10) fields are provided, wherein ProcessID is a primary key. The ProcessTable may be an enumeration table of types of elements. For example, in any row, only one of the columns may contain valid data. This data may indicate what kind of element that the corresponding row represents, and ID for the type which is a foreign key to the table of that type. Recursion is possible because the type table may refer back to the ProcessTable 56 using the ProcessID field. Possible values for the Type field include: Sequence, Map, Partition, Connect, Switch, Cut, and Copy. In the Parts table 57, ScheduleID: char(36), and Tablename fields are provided. This table 57 has a primary key ScheduleID and a foreign key Tablename.

Referring now to FIG. 2D, the Call table 60 fields comprises ActionID: integer; ContextID: integer, (null allowed); and ScheduleID: char (36) fields. This table 60 has a primary key ProcessID and foreign keys FK1 ScheduleID, FK2 ContextID, and FK3 ProcessID. CallMsgTable table 61 comprises fields ProcessID: integer (identity); SortID: smallint (null allowed); and MessageID: integer. Table 61 includes primary keys ProcessID and SortID, as well as foreign keys FK1 ProcessID and FK2 MessageID. The CallPortTable table 62 comprises fields ProcessID: integer (identity); SortID: smallint (null allowed); and PortRefID: integer. Table 62 further comprises primary keys ProcessID and SortID, as well as foreign keys FK1 ProcessID and FK2 PortRefID.

SwitchTb1 table 63 comprises fields: ProcessID: integer; ScheduleID: char (36); ContextID: integer, (null allowed); and DefProcessID: integer, (null allowed), wherein the DefProcessID is the id of the process to be invoked if this is true. The SwitchTb1 table 63 includes a primary key ProcessID and foreign keys FK1 ProcessID and FK2 ScheduleID. CaseTb1 table 64 comprises fields CaseID: integer; pSwitchID: integer; MsgID 1: integer; MsgID2: integer; RuleRefiD: integer; and ProcessID: integer. This table 64 has a primary key CaseID, and foreign keys FK1 pSwitchID, FK2 MsgID1, Fk3 MsgID2, and FK4 RuleRefID. The CaseTh1 table 64 comprises details on the non-default conditions. Copy table 65 fields include ProcessID: integer; ScheduleID: char (36); ContextID: integer, (null allowed); and cProcessID: integer. Table 65 further includes primary key ProcessID and foreign keys FK1 ScheduleID and FK2 ProcessID.

In FIG. 2E, the Cut table 70 comprises fields ProcessID: integer; ScheduleID: char (36); ContextID: integer, (null allowed); ProcessID 1: integer; ProcessID2: integer; and ProcessID3: integer. The Cut table 70 has a primary key ProcessID and foreign keys FK1 ScheduleID and ProcessID. The Cut table 70 comprises a list of components or elements which communicate with each other, and which are included in the Process table 56.

The MapTb1 table 71 comprises fields ProcessID: integer; ScheduleID: char (36); ContextID: integer, (null allowed); and mProcessID: integer. MapTb1 table 71 has a primary key ProcessID and a foreign key FK1 ScheduleID. The Assignment table 72 comprises fields ProcessID: integer; MessageID: integer; and PortID: integer. The table 72 further includes primary keys ProcessID, MessageID, and PortID, as well as foreign keys ProcessID, MessageID and PortID. The ProcessID field in the Assignment table 72 points to the ID of Map process, and the Assignment table 72 comprises port and message information related to the map.

The Connection table 73 comprises fields ConnectionID: integer (identity); ProcessID: integer; PortRef1: integer; and PortRef2: integer. Table 73 also comprises a primary key ConnectionID and foreign keys FK1 ProcessID, FK2 PortRef1, and FK3 PortRef2. The Partition table 74 includes fields ProcessID: integer; ScheduleID: char (36); and ContextID: integer, (null allowed). In addition, table 74 includes a primary key ProcessID and a foreign key FK1 ScheduleID. The Partition table 74 comprises a list of independent components or elements, which are included in the Process table 56.

The Connect table 75 comprises fields ProcessID: integer; ScheduleID: char (36); ContextID: integer, (null allowed); ProcessID1: integer; and ProcessID2: integer. Table 75 also has a primary key ProcessID, and foreign keys FK1 ProcessID2, FK2 ProcessID1, FK3 ScheduleID, and FK4 ProcessID. The ports that are connected as per this connect process may be specified in the Connection table 73. The PartitionElem table 76 includes fields ProcessID: integer and pProcessID: integer. The ProcessID field is the id of the processes that are part of partitions. The pProcessID field is the id of the corresponding partition process. PartitionElem table 76 includes primary keys ProcessID and pProcessID, and in addition, foreign keys ProcessID and pProcessID.

Referring now to FIG. 2F, the Binding table 80 fields include BindingID: integer (identity); ModuleID: integer; and BindingName: varchar(100). This table 80 has a primary key BindingID and a foreign key FK1 BindingName. The COMPortBinding table 81 comprises fields BindingID: integer; PortReflD: integer; IID: char(16); CLSID: char(16); PROGID: varchar(200); Moniker: varchar(200); Script: varchar(200); and NoInvocation: bit. The COMPortBinding table 81 also has primary keys PortRefID and BindingID along with foreign keys FK1 PortRefifD and FK2 BindingID. The COMMsgBinding table 82 comprises fields MsgID: integer; BindingID: integer; Method: varchar (100); Slot: smallint; and DispID: smallint. This table 82 has primary keys MsgID and BindingID and a foreign key FK1 BindingID. The COMFieldBinding table 83 comprises fields MsgID: integer; BindingID: integer; FieldLocation: varchar(100); FromX: varchar(100); Requires: bit; Provides: bit; Paramidx: smallint; ParamName: varchar(100); and FieldType: char(20). The table 83 also has primary keys MsgID, BindingID, and FieldLocation, and field keys FK1 MsgID and FK1 BindingID.

The ContextBinding table 84 comprises fields ContextID: integer; BindingID: integer; Retry: smallint, (null allowed); Backoff: smallint, (null allowed); and Timeout: smallint, (null allowed). This table 84 includes primary keys ContextID and BindingID as well as foreign keys FK1 ContextID and FK2 BindingID. The RuleBindingAll table 85 has fields RuleRefID: integer; BindingID: integer; MsgID1: integer; MsgID2: integer; and AllFields: bit. The RuleBindingAll table 85 has primary keys RuleRefID and BindingID as well as foreign keys FK1 RuleRefID, FK2 MsgID2, and FK3 BindingID. If the AllFields field is False, there will be entries in the Match table 86 to specify which fields need to be matched. The Match table 86 comprises fields RuleReflD: integer; BindingID: integer; FieldRef1: integer; and FieldRef2: integer. Table 86 has primary keys RuleRefiD, BindingID, FieldRef1, and FieldRef2, as well as foreign keys FK1 RuleRefID and FK1 BindingID.

Schema Table Relationships

The exemplary database schema 28 illustrated in FIGS. 2A-2F comprises various tables as illustrated and described supra, as well as relationships between the schema tables, which are illustrated and described in greater detail hereinafter. One or more of the schema tables include information relating to the definition of a schedule, which may be advantageously employed to reconstruct the schedule definition language solely from information in a database. In addition, the schema 28 may include one or more tables related to instance information having, for example, information describing what instances of a schedule are running and what definitions they correspond to, as well as positional information, such as what actions are currently live, how many messages are currently pending, and other schedule state information. Moreover, the schedule definition and binding information portions or tables of the schema may be normalized to a higher degree than are those relating to runtime information. In this way, an efficient storage methodology and system are provided for less frequently used tables (e.g., schedule definition and binding information), while fast access is provided to runtime information.

The database schema may also include runtime data associated with a schedule instance. Such data may include the value of specific messages, etc. According to one aspect of the invention, the schedule state information and data may be persisted to the database during execution at transaction boundaries. Schedule data and state information may also be persisted to the database during dehydration operations based on latency hints or attributes. The database schema provides a format for storing the persisted information in a relational database.

Because the schema may be fixed, the invention also allows the use of monitoring and other tools to query against the database, in order to indicate the status of a running workflow process, as well as how specific instances of the process are running. The use of existing or common database tools (e.g., from database management systems or DBMSs) for obtaining this information is enabled by the use of overlapping schemas for definitional purposes and to describe runtime instances and data.

The relationships between the various tables of the exemplary database schema 28 are now discussed in association with FIGS. 2A-2F. In FIG. 2A, Module table 32 has relationships or links 100 from Binding table 80 in FIG. 2F, 102 from RunInstances table 54 in FIG. 2C, and 104 from Schedule table 30. Schedule table 30, has further links 106 from Message table 33, 108 from CaseRule table 34, 110 from Port table 35, and 112 from Context table 31. The Schedule table 30 further includes link 114 to the Process table 56 of FIG. 2C, link 116 from Copy table 65 of FIG. 2D, link 118 from Connect table 75 in FIG. 2E, link 120 from the Sequence table 40 of FIG. 2B, link 122 from the MapThl table 71 in FIG. 2E, link 124 from the Call table 60 in FIG. 2D, link 126 from the Cut table 70 in FIG. 2E, link 128 from the SwitchTb1 table 63 in FIG. 2D, and link 130 from the Partition table 74 in FIG. 2E.

The Context table 31 of FIG. 2A includes incoming relationships or links 132 from RunContexts table 53 in FIG. 2C, 134 from ContextBinding table 84 in FIG. 2F, 136 from RunProcessActions table 52 in FIG. 2C, and 138 from Call table 60 in FIG. 2D. The Context table 31 also has an outgoing link 140 to the Process table 56 of FIG. 2C. Context table 31 of FIG. 2A also includes incoming links 142 from Sequence table 40 of FIG. 2B, 144 from Contextlnvocation table 45 of FIG. 2B, and 146 from Block table 43 of FIG. 2B.

The Message table 33 of FIG. 2A has incoming links 150 from SourceSink table 42 of FIG. 2B, 152 from RuleBindingAll table 85 of FIG. 2F, 154 from CallMsgTable table 61 of FIG. 2D, 156 from the Assignment table 72 of FIG. 2E, and 160 from CaseTbl table 64 of FIG. 2D. The CaseRule table 34 of FIG. 2A has incoming links 162 from RuleBindingAll table 85 of FIGS. 2F and 164 from the CaseTbl table 64 of FIG. 2D.

Port table 35 of FIG. 2A includes incoming links 166 from RunCOMObjects table 50 of FIG. 2C, 168 from SourceSink table 42 of FIG. 2B, 170 from COMPortBinding table 81 of FIG. 2F, 172 from CallPortTable table 62 of FIG. 2D, 174 from Assignment table 72 of FIG. 2E, and 178 from the Connection table 73 of FIG. 2E.

In FIG. 2B, Task table 41 has a link 180 to Action table 44. Table 44, in turn, has incoming links 182 and 184 from SourceSink table 42 and ContextInvocation table 45, respectively. Action table 44 further includes outgoing link 186 to the Block table 43, which in turn, has an outgoing link 188 to the Sequence table 40. Sequence table 40 further has an outgoing link 190 to the Process table 56 of FIG. 2C. Action table 44 further includes an incoming link 192 from the RunProcessActions table 52 of FIG. 2C.

Referring now to FIG. 2C, RunInstances Table 54 has incoming links 200 from RunCOMObjects table 50, 202 from RunCOMField table 51, 204 from RunProcessActions table 52, and 210 from RunContexts table 53. Link 206 relates RunProcessActions table 52 to Process table 56. Link 208 relates RunContextStatus table 55 to RunContext table 53. RunCOMField table 51 has an outgoing link 220 to COMFieldBinding table 83 of FIG. 2F. In addition to those incoming links described above, Process table 56 of FIG. 2C further includes incoming links 222 from Connect table 75 in FIG. 2E, 224 from Copy table 65 in FIG. 2D, 226 from Call table 60 in FIG. 2D, 228 from PartitionElem table 76 in FIG. 2E, 230 from Cut table 70 in FIG. 2E, and 232 from SwitchTbl table 63 in FIG. 2D.

Referring now to FIGS. 2D-2F, CallMsgTable 61 has an outgoing link to Call table 60, which in turn has an incoming link 242 from CallPortTable 62. In addition, CaseTbl table 64 has a link 244 to SwitchTbl table 63. Assignment table 72 of FIG. 2E has an outgoing link 250 to MapThl table 71. Link 252 relates Connection table 73 to Connect table 75, and link 254 relates PartitionElem table 76 to the Partition table 74. In FIG. 2F, a link 260 relates COMMsgBinding table 82 to Binding table 80. Binding table 80 has further incoming links 262 from COMPortBinding table 81, 264 from ContextBinding table 84, and 266 from RuleBindingAll table 85. Link 268 relates Match table 86 to the RuleBindingAll table 85, and 270 relates the COMFieldBinding table 83 to COMMsgBinding table 82.

The schema 28 of FIGS. 2A-2F provides a complete definition of a transaction processing schedule, as well as one or more bindings for specific hardware applications thereof. Thus, it is possible to reconstruct the schedule definition language representation of a schedule, and the binding for the schedule solely from the information in the database. The schema 28, moreover, provides a data structure for storing and managing runtime information related to instances of a schedule in a database. The runtime information may comprise, for example, schedule state information, data, and messages associated with a running schedule application.

In order to make updates to the database by the runtime engine fast, the schema 28 may not be fully normalized and some tables contain redundant information. At the same time, the tables of the schema 28 for storing schedule and binding definition should be efficient. As such, these tables are normalized with clearly defined foreign key relationships to make storage optimal. Accordingly, the tables designed to contain non-runtime information in the exemplary schema 28 have been normalized to the third normal form. Thus, most of the schema is normalized to third normal form. In some instances, especially in the case of tables that will be accessed frequently by the runtime engine (e.g., RunProcActions 52) some redundant information may be stored in the interest of simplifying and increasing the speed of queries.

In addition, the database schema according to the invention is portable to multiple database systems. This allows users to employ many different relational database management systems (RDBMS) to store and access the schedule-related information. Accordingly, the schema 2