System for managing relational databases using XML objects

Abstract

The present invention relates to a system and methodology to facilitate the automated creation of an XML object model which overlays a standard relational database to allow both saving and retrieval of data via hierarchical XML objects from within the actual database server itself. The Automated Database Object Model (ADOM) process generates a set of log tables, triggers, stored procedures, functions, and views for all objects as determined by a database schema interrogation process. The result is a select query for every hierarchical object in the database, and a single point of entry stored procedure for all inserts, updates, and deletes. A managed application programming interface (API) is also provided to automatically generate a class object library in the application layer which matches the automated database object model, thus abstracting the application developer from directly managing the underlying database structure.

Claims

1. A computer system including an Automated Database Object Model (ADOM) database having a plurality of ADOM-generated stored procedures, comprising at least one non-transitory computer readable medium, comprising: a programming layer; and a computer memory including a database layer, wherein the database layer includes an information database including a first information table and a second information table, wherein the first information table includes a first portion of information, and the second information table includes a second portion of information, and linking information that links the second portion of information to the first portion of information, and the plurality of ADOM-generated stored procedures stored, executed, and operated within the database layer and separate from the programming layer, wherein each of the plurality of ADOM-generated stored procedures dictates a corresponding manipulation of the first and second portions of information based on the linking information, wherein the database layer is configured to receive a request, from the programming layer, for a completed hierarchical data object, the request including an identifier specifying at least one ADOM-generated stored procedure of the plurality of ADOM-generated stored procedures, execute, in the database layer, one or more ADOM-generated stored procedures of the plurality of ADOM-generated stored procedures based on the specified at least one ADOM-generated stored procedure, wherein the one or more ADOM-generated stored procedures retrieve the first and second portions of information based on the corresponding dictated manipulations, structure, in the database layer using the one or more ADOM-generated stored procedures, the retrieved first and second portions of information into a completed hierarchical data object structure, generate, in the database layer using the one or more ADOM-generated stored procedures, the completed hierarchical data object based on the completed hierarchical data object structure, and transmit the completed hierarchical data object from the database layer to the programming layer, the completed hierarchical data object including the first and second portions of information, and wherein all mapping of the first and second portions of information to the hierarchical data object is performed by the one or more ADOM-generated stored procedures within the database layer.

2. The system of claim 1, wherein: the plurality of ADOM-generated stored procedures include ADOM-generated generic search stored procedures, ADOM-generated xmlSelector stored procedures, ADOM-generated process changes stored procedures, and ADOM-generated delete stored procedures; the information database includes relational tables and foreign key relationships; and the information database passes entire hierarchical data objects in a single call.

3. The system of claim 2, wherein: the information database includes an ADOM class builder Application Programming Interface (API); the information database includes a class object structure that is auto-generated by the ADOM class builder API; and the information database includes a plurality of ADOM-generated class objects built as partial classes.

4. The system of claim 3, wherein: the information database is a Structured Query Language (SQL) database including relational table data; the information database returns relational table data as the entire hierarchical data objects in response to automated queries; the system deserializes the entire hierarchical data objects into auto-generated class objects; and the entire hierarchical data objects include entire hierarchical extensible markup language (XML) objects.

5. The system of claim 4, wherein: the computer system includes a middle-tier API that serializes the auto-generated class objects as JavaScript Object Notation (JSON) and sends the JSON to a client system; the computer system reserializes changed JSON received from the client system to create changed hierarchical XML objects; and the relational tables include XML compliant table names and column names, defined foreign key relationships, and defined primary key columns.

6. The system of claim 2, wherein the plurality of ADOM-generated stored procedures include an automated update stored procedure that processes a changed hierarchical data object back into the relational tables included in the information database, wherein the automated update stored procedure: interrogates the changed hierarchical data object to determine a root object type, a plurality of child object types, and an order for processing the root object type and plurality of child object types, wherein the order for processing is based on an ADOM configuration table, iterates through and processes the each of the root object type and the plurality of child object types in the order for processing by executing at least one of an insert ADOM-generated stored procedure, an update ADOM-generated stored procedure, or a delete ADOM-generated stored procedure to modify the information database based on the changed hierarchical data object, executes the one or more ADOM-generated xmlSelector stored procedures to obtain an updated changed hierarchical data object from the information database, and in response to detecting a concurrency issue while processing each of the root object type and each of the plurality of child object types, returns a notification of the concurrency issue to the programming layer, and continues processing the remaining root object type and plurality of child object types.

7. A computer system including an Automated Database Object Model (ADOM) database having a plurality of ADOM-generated stored procedures, a plurality of ADOM user-defined functions, and a plurality of ADOM tables, comprising at least one non-transitory computer readable medium, comprising: a programming layer; and a computer memory including a database layer, wherein the database layer includes an information database including a first information table and a second information table, wherein the first information table includes a first portion of information, and the second information table includes a second portion of information, and linking information that links the second portion of information to the first portion of information, and the plurality of ADOM-generated stored procedures stored, executed, and operated within the database layer and separate from the programming layer, wherein each of the plurality of ADOM-generated stored procedures dictates a corresponding manipulation of the first and second portions of information based on the linking information, wherein the database layer is configured to receive a request, from the programming layer, for a completed hierarchical extensible markup language (XML) object, the request including an identifier specifying at least one ADOM-generated stored procedure of plurality of ADOM-generated stored procedures, execute, in the database layer, one or more ADOM-generated stored procedures of the plurality of ADOM-generated stored procedures based on the specified at least one ADOM-generated stored procedure, wherein the one or more ADOM-generated stored procedures retrieve the first and second portions of information based on the corresponding dictated manipulations, structure, in the database layer using the one or more ADOM-generated stored procedures, the retrieved first and second portions of information into a completed hierarchical XML object structure, generate, in the database layer using the one or more ADOM-generated stored procedures, the completed hierarchical XML object based on the completed hierarchical XML object structure, and transmit the completed hierarchical XML object from the database layer to the programming layer, the completed hierarchical XML object including the first and second portions of information, and the plurality of ADOM user-defined functions stored in the database layer; wherein the plurality of ADOM-generated stored procedures includes a DatabaseUpdateController Structured Query Language (SQL) procedure, a DatabaseFirstRun SQL procedure, an ExecControllerADOM SQL procedure, and a spAddTableColumn SQL procedure.

8. The system of claim 7, wherein the plurality of ADOM user-defined functions includes a fnStringToTable function, a fnCreateAlterProc function, and a SpaceBeforeCap function.

9. The system of claim 7, wherein the information database includes an ADOMFunction table, a DefaultValue table, and a ProcsFunctionstoCopy table.

10. The system of claim 9, wherein the ADOMFunction table includes a CreateRequiredTables script, a LogTableSchemaUpdate script, a LogTableTriggerUpdate script, an UpdateMainSearch script, a CreateAtrributesSelect script, a CreateDeleteProcs script, and a CreateGenericSearch script.

11. The system of claim 10, wherein the CreateRequiredTables script generates an ADOM_Databaselist table, ADOM_MainQuery table, ADOM_ConcurrencyIssue table, ADOM_ForeignKey table, and ADOM_NeverDuplicate table.

12. The system of claim 10, wherein the CreateRequiredTables script generates an ADOM_Configuration table, ADOM_ConcurrencyFormControlType table, ADOM_ConcurrencyFormControl table, and ADOM_ModifyType table.

13. The system of claim 9, wherein the ADOMFunction table includes a CreateProcs script, a CreateProcsGUID script, a CreateProcsidentify script, a CreateProcsNoldentify script, a CreateSelector script, a CreateSelector_Trans script, and a CreateTableStructure script.

14. The system of claim 9, wherein the ADOMFunction table includes a TablelistInsert script, a ForeignKeyInsert script, a CreateLookUpTableStructure script, a CreateLookupSelect script, a DropExistingAdomProcs script, a ConcurrencyForm script, and an UpdateMainSearchLoop script.

15. A computer system including an Automated Database Object Model (ADOM) database having a plurality of ADOM-generated stored procedures, a plurality of ADOM user-defined functions, and a plurality of ADOM tables, comprising at least one non-transitory computer readable medium, comprising: a programming layer; and a computer memory including a database layer, wherein the database layer includes an information database including a first information table and a second information table, wherein the first information table includes a first portion of information, and the second information table includes a second portion of information, and linking information that links the second portion of information to the first portion of information, and the plurality of ADOM-generated stored procedures stored, executed, and operated within the database layer and separate from the programming layer, wherein each of the plurality of ADOM-generated stored procedures dictates a corresponding manipulation of the first and second portions of information based on the linking information, wherein the database layer is configured to receive a request, from the programming layer, for a completed hierarchical extensible markup language (XML) object, the request including an identifier specifying at least one ADOM-generated stored procedure of the plurality of ADOM-generated stored procedures, execute, in the database layer, one or more ADOM-generated stored procedures of the plurality of ADOM-generated stored procedures based on the specified at least one ADOM-generated stored procedure, wherein the one or more ADOM-generated stored procedures retrieve the first and second portions of information based on the corresponding dictated manipulations, structure, in the database layer using the one or more ADOM-generated stored procedures, the retrieved first and second portions of information into a completed hierarchical XML object structure, generate, in the database layer using the one or more ADOM-generated stored procedures, the completed hierarchical XML object based on the completed hierarchical XML object structure, and transmit the completed hierarchical XML object from the database layer to the programming layer, the completed hierarchical XML object including the first and second portions of information, and the plurality of ADOM user-defined functions stored in the database layer; wherein the plurality of ADOM-generated stored procedures includes a DatabaseUpdateController Structured Query Language (SQL) procedure, a DatabaseFirstRun SQL procedure, an ExecControllerADOM SQL procedure, and a spAddTableColumn SQL procedure; the information database includes an ADOM class builder Application Programming Interface (API); the information database includes a class object structure that is auto-generated by the ADOM class builder API; and the information database includes a plurality of ADOM-generated class objects built as partial classes.

16. The system of claim 15, wherein the information database includes relational tables having Extensible Markup Language (XML) compliant table names and column names, defined foreign key relationships, and defined primary key columns.

17. The system of claim 16, wherein: the information database returns relational table data as entire hierarchical XML objects in response to automated queries; and the system deserializes the entire hierarchical XML objects into auto-generated class objects.

18. The system of claim 17, wherein: the system includes a middle-tier API that serializes the auto-generated class objects as JavaScript Object Notation (JSON) and sends the JSON to a client system; the system reserializes changed JSON received from the client system to create changed hierarchical XML objects; and the information database includes an automated update stored procedure that processes the changed hierarchical XML objects back into the relational tables included in the information database.

19. The system of claim 18, wherein: the plurality of ADOM user-defined functions includes a fnStringToTable function, a fnCreateAlterProc function, and a SpaceBeforeCap function; the information database includes an ADOMFunction table, a DefaultValue table, and a ProcsFunctionstoCopy table; the ADOMFunction table includes a CreateRequiredTables script; and the CreateRequiredTables script generates an ADOM_Databaselist table, ADOM_MainQuery table, ADOM_ConcurrencyIssue table, ADOM_ForeignKey table, ADOM_NeverDuplicate table, ADOM_Configuration table, ADOM_ConcurrencyFormControlType table, ADOM_ConcurrencyFormControl table, and ADOM_ModifyType table.

20. The system of claim 19, wherein the ADOMFunction table includes a LogTableSchemaUpdate script, a LogTableTriggerUpdate script, an UpdateMainSearch script, a CreateAtrributesSelect script, a CreateDeleteProcs script, a CreateGenericSearch script, a CreateProcs script, a CreateProcsGUID script, a CreateProcsidentify script, a CreateProcsNoIdentify script, a CreateSelector script, a CreateSelector_Trans script, a CreateTableStructure script, a TablelistInsert script, a ForeignKeyInsert script, a CreatelookUpTableStructure script, a CreatelookupSelect script, a DropExistingAdomProcs script, a ConcurrencyForm script, and an UpdateMainSearchloop script.

Description

BRIEF DESCRIPTION OF THE DRAWINGS

(1) FIG. 1A and FIG. 1B are block diagrams comparing one embodiment of the ADOM Framework of the present invention (FIG. 1B) with a prior art Entity Framework (FIG. 1A).

(2) FIG. 2A and FIG. 2B are second block diagrams comparing another embodiment of the ADOM Framework of the present invention (FIG. 2B) with a prior art Entity Framework (FIG. 2A).

(3) FIG. 3 is a block diagram showing a sample database including three tables used with one embodiment of the present invention.

(4) FIG. 4 is a block diagram showing log tables generated by one embodiment the present invention.

(5) FIG. 5 is a printout showing nested phone and emails records generated by one embodiment of the present invention.

(6) FIG. 6 is a printout showing a stored procedure used in one embodiment of the present invention.

(7) FIG. 7 is a printout showing another stored procedure used in one embodiment of the present invention.

(8) FIG. 8 is a printout showing the output from a stored procedure used in one embodiment of the present invention.

(9) FIG. 9 is printout showing partial save person objects used in one embodiment of the present invention.

(10) FIG. 10 is a block diagram showing a custom web application development model used with one embodiment of the present invention.

(11) FIG. 11 is a block diagram showing a save process used in one embodiment of the present invention.

(12) FIGS. 12, 13, and 14 are printouts showing partial sample class structures used in one embodiment of the present invention.

(13) FIG. 15 is a screenshot showing a concurrency form user interface used with one embodiment of the present invention.

(14) FIG. 16 is a block diagram showing a project file structure used in one embodiment of the present invention.

DETAILED DESCRIPTION OF THE INVENTION

(15) While the making and using of various embodiments of the present invention are discussed in detail below, it should be appreciated that the present invention provides many applicable inventive concepts that are embodied in a wide variety of specific contexts. The specific embodiments discussed herein are merely illustrative of specific ways to make and use the invention and do not delimit the scope of the invention. Those of ordinary skill in the art will recognize numerous equivalents to the specific apparatus and methods described herein. Such equivalents are considered to be within the scope of this invention and are covered by the claims.

(16) In the drawings, not all reference numbers are included in each drawing, for the sake of clarity. In addition, positional terms such as upper, lower, side, top, bottom, etc. refer to the apparatus when in the orientation shown in the drawing. A person of skill in the art will recognize that the apparatus can assume different orientations when in use.

(17) ADOM Framework

(18) The ADOM Framework consists of a set of SQL scripts all contained within a single managed database structure. When the contained scripts are executed against a specified database catalog, they automatically generate everything needed to implement the managed XML object model for the given data structure. This allows the DBA to focus on generating a solid base relational database structure, and then ADOM handles everything else. The result is a fully functional XML object model that lives inside the target database. Below we will detail the components of the ADOM Framework, and then how they are applied to a relational database to produce the XML Object model.

(19) ADOM Stored Procedures

(20) Below are a list of stored procedures contained within the ADOM database and a brief description of the processes that each procedure performs.

(21) DatabaseUpdateController

(22) The DatabaseUpdateController is the primary point of entry for applying the ADOM framework to a specific database catalog. As the name suggests, this SQL procedure is the controller for implementing the other stored procedures and SQL scripts required to implement the ADOM framework.

(23) Parameters:

(24) @db VARCHAR(MAX)=The name of the target database catalog to which the ADOM framework is to be applied.

(25) @DropExistingScripts BIT=Indicates whether previously generated ADOM scripts should be deleted.

(26) Process Steps:

(27) The first thing the DatabaseUpdateController does is determine if the ADOM framework has previously been applied to the target database catalog. If it is the first time the ADOM framework has been applied to the target database catalog, then the DatabaseUpdateController executes the DatabaseFirstRun stored procedure. If the ADOM framework has previously been applied, then the following steps are performed:

(28) If @DropExistingScripts=True, then Exec Adorn Function. DropExistingAdom Procs.

(29) Exec AdomFunction.LogTableSchemaUpdate

(30) Exec AdomFunction.LogTableTriggerUpdate

(31) Exec AdomFunction.CreateProcs

(32) Exec AdomFunction.CreateLookUpTableStructure

(33) Exec AdomFunction.CreateSelector

(34) Exec AdomFunction.CreateSelector_Trans

(35) Exec AdomFunction.UpdateMainSearch

(36) Exec AdomFunction.ConcurrencyForm

(37) DatabaseFirstRun

(38) The DatabaseFirstRun procedure controls the creation and population of a set of special ADOM tables used to set ADOM-specific configuration settings in the database.

(39) Parameters

(40) @db varchar(max)=Name of the database catalog to which the ADOM framework is to be applied

(41) Process Steps

(42) Exec AdomFunction.CreateRequiredTables

(43) Exec AdomFunction.ForeignKeyInsert

(44) Exec AdomFunction.TableListInsert

(45) ExecControllerADOM

(46) The ExecControllerADOM procedure interrogates each database catalog on a given server to determine if it is an ADOM-controlled database catalog and then executes the DatabaseUpdateController procedure for each. This is only used when the user wants to perform a mass update on ALL ADOM-controlled databases on a given server.

(47) spAddTableColumn

(48) The spAddTableColumn procedure is a programmatic way to add a new column to a given table.

(49) Parameters

(50) @TableName VARCHAR(255)=The name of the target table

(51) @ColumnName VARCHAR(255)=The name of the new column to be added

(52) @DataType VARCHAR(255)=The data type of the new column to be added

(53) Process Steps

(54) Checks to see if the column already exists in the target table

(55) If Not exists, then performs an ALTER TABLE statement and adds the column.

(56) ADOM Functions

(57) The following is a list of special ADOM user-defined functions contained within the ADOM database and a brief description of the processes that each function performs.

(58) fnStringToTable

(59) The fnStringToTable table-valued function splits a given string by a given separator string, and then returns the split components as a table for processing.

(60) Parameters

(61) @inputString NVARCHAR(MAX)=The string to be split

(62) @separator VARCHAR(10)=The string to use as the split point

(63) Process Steps

(64) Splits the @inputString value based on the @separator parameter and returns split strings in table format.

(65) fnCreateAlterProc

(66) Parameters

(67) @ProcName VARCHAR(500)=The name of the stored procedure to be created or altered.

(68) Process Steps

(69) Checks to see if the a stored procedure already exists with the @ProcName value.

(70) If Exists

(71) Returns ALTER PROCEDURE

(72) Else

(73) Returns CREATE PROCEDURE

(74) SpaceBeforeCap

(75) The SpaceBeforeCap user-defined function inserts a space before every capitalized letter in a given input string. This is used to make database object names more legible for a human. (i.e.SpaceBeforeCap would be changed to Space Before Cap)

(76) Parameters

(77) @str NVARCHAR(MAX)=The string to which the function is applied.

(78) Process Steps

(79) Inserts a space before all capitalized letters in a string

(80) ADOM Tables

(81) Below is a list of tables contained within the ADOM database and a brief description of the contents and purpose of each table.

(82) Adorn Function

(83) The AdomFunction table contains a set of SQL scripts which are the primary tools used to implement the ADOM framework in a specified database catalog. These scripts are loaded into a temporary table at the beginning of the Adomization process, so that they can be referenced from any point within the process. Some of the scripts are called multiple times from various places.

(84) CreateRequiredTablesThis script creates a set of special ADOM-specific configuration tables in the target database catalog.

(85) Create Table ADOM_DatabaseList and Populate Create Table ADOM_MainQuery Create Table ADOM_ConcurrencyIssue Create Table ADOM_ForeignKey Create View vwForeignKey Create Table ADOM_NeverDuplicate Create Table ADOM_Configuration Create Table ADOM_ConcurrencyFormControlType and Populate Create Table ADOM_ConcurrencyFormControl Create FK Constraint between ADOM_ConcurrencyFormControlType and ADOM_ConcurrencyForm Control tables Create Table ADOM_ModifyType and populate LogTableSchemaUpdateThis script creates or updates the ADOM-generated Log tables to reflect any changes in the Primary parent tables. All ADOM-generated Log tables are created to conform to XML Guidelines. Interrogates database schema structure to determine if any changes have been made in the Primary table structure

(86) Updates related Log table structure to match primary table structure

(87) LogTableTriggerUpdateThis script updates the ADOM-generated triggers on all primary tables to populate the ADOM-generated Log tables.

(88) Create or Update Log Table Trigger syntax based on primary table structure

(89) Interrogates schema structure of Primary tables and Log tables

(90) Deletes existing LogTable triggers

(91) Creates new LogTable Triggers

(92) UpdateMainSearchThis script updates custom queries to ensure the XML object structure matches the current relational database structure.

(93) Gets a list of custom queries that need to be updated by ADOM from the ADOM_MainQuery table.

(94) Loops through list and updates custom query's select syntax with latest version of generic search syntax based on special tags.

(95) CreateAttributesSelectThis script generates a special set of Attribute Select stored procedures for each primary object in the database which return fully-populated samples of each hierarchical XML object. The resulting object also includes special attributes which are used by the ADOM class builder

(96) API to construct the middle-tier class objects.

(97) Gets the ProcPrefix value from the ADOM_Configuration table

(98) Checks the ADOM_TableList.GenerateAttribute value

(99) If GenerateAttribute=1

(100) Builds the XML Object structure based on FK relationships

(101) Populates each column with the values in the ADOM.dbo.DefaultValue table for each data type.

(102) Creates the ADOM_AttributeSelect stored procedure for the given table.

(103) CreateDeleteProcsThis script generates a Delete stored procedure for a given table in the database.

(104) Creates or Updates the Delete stored procedure for a given Table

(105) CreateGenericSearchThis script creates a base Generic Search stored procedure for a given Primary table object in the database, which returns the hierarchical XML object based on a primary key.

(106) Gets the ProcPrefix value from the ADOM_Configuration table

(107) Ensures that the @TableName parameter value is cased the same as the Table name in the database.

(108) Sets the CREATE/ALTER statement based on whether the stored procedure already exists.

(109) Sets the ROOT node in the XML object based on the @TableName parameter.

(110) Sets the PK of the root object based on the ADOM_TableList data

(111) Sets the ROOT PK data type based on schema interrogation

(112) Iterates through the ADOM_TableList data to set the tables to be included in the Object.

(113) Populates the XML object nodes for each node-level based on schema interrogation of columns in each table.

(114) Adds any non-primary lookup table columns to the referenced node level.

(115) Creates the Generic Search stored procedure.

(116) CreateProcsThis script generates the Insert/Update/Delete stored procedure logic structure for each primary table object in the database.

(117) Gets ProcPrefix from the ADOM_Configuration table

(118) Interrogates the ADOM_TableList data

(119) Loops through table list and does the following:

(120) Exec AdomFunction.CreateTableStructure

(121) Strings together syntax for join statements on FK's

(122) Strings together syntax to update non primary keys

(123) Create/Update Process Changes sproc

(124) Exec spXmlCreateDeleteProcs

(125) CreateProcsGUIDThis script creates the Process Changes stored procedure for a given table which has a GUID as its primary key.

(126) Sets the sproc name as follows: @ProcName=@ProcPrefix+spXmlProcess+@TableName+Changes

(127) Gets the ModifyUserColumn and ModifyDateColumn values from the ADOM_TableList configuration table

(128) Checks the ADOM_ForeignKey table to see if the GUID PK is used as a FK anywhere in the database

(129) Checks to see if the given table is configured for Concurrency Checking

(130) Sets the UPDATE statement based on columns in Table

(131) Sets the INSERT statement based on columns in Table

(132) Checks the ADOM_NeverDuplicate table for columns which must contain unique values, and adds the syntax to check this to the query logic

(133) If the table is configured for concurrency checking, then the syntax for this is added to the query logic

(134) CREATE/ALTER PROCEDURE statement is executed

(135) CreateProcsldentityThis script creates the Process Changes stored procedure for a given table which has an Identity column as its primary key.

(136) Creates the Insert/Update procedure for a given table which has an Identity column as the Primary Key

(137) Sets the sproc name as follows: @ProcName=@ProcPrefix+spXmlProcess+@TableName+Changes

(138) Gets the ModifyUserColumn and ModifyDateColumn values from the ADOM_TableList configuration table

(139) Checks the ADOM_ForeignKey table to see if the GUID PK is used as a FK anywhere in the database

(140) Checks to see if the given table is configured for Concurrency Checking

(141) Sets the UPDATE statement based on columns in Table

(142) Sets the INSERT statement based on columns in Table

(143) Checks the ADOM_NeverDuplicate table for columns which must contain unique values, and adds the syntax to check this to the query logic

(144) If the table is configured for concurrency checking, then the syntax for this is added to the query logic

(145) CREATE/ALTER PROCEDURE statement is executed

(146) CreateProcsNoIdentityThis script creates the Process Changes stored procedure for a given table which has something other than a GUID or an identity column as its primary key.

(147) Creates the Insert/Update procedure for a given table which has something other than a GUID or Identity as the Primary Key

(148) Sets the sproc name as follows: @ProcName=@ProcPrefix+spXmlProcess+@TableName+Changes

(149) Gets the ModifyUserColumn and ModifyDateColumn values from the ADOM_TableList configuration table

(150) Checks the ADOM_ForeignKey table to see if the GUID PK is used as a FK anywhere in the database

(151) Checks to see if the given table is configured for Concurrency Checking

(152) Sets the UPDATE statement based on columns in Table

(153) Sets the INSERT statement based on columns in Table

(154) Checks the ADOM_NeverDuplicate table for columns which must contain unique values, and adds the syntax to check this to the query logic

(155) If the table is configured for concurrency checking, then the syntax for this is added to the query logic

(156) CREATE/ALTER PROCEDURE statement is executed

(157) CreateSelectorThis script creates the xmlSelector stored procedure which is the non-Transactional controller for all Inserts/Updates/Deletes

(158) Sets the ProcPrefix value based on the ConfigurationValue in the ADOM_Configuration table

(159) Sets the CREATE/ALTER PROCEDURE statement based on whether the procedure already exists in the database

(160) Strings together the query logic based on the objects defined in the ADOM_TableList table

(161) Adds the syntax to extract the ModifyUser value from the root node in the XML Object

(162) Adds the logic to call the Process Changes and Delete sprocs for all objects in the database

(163) CREATE/ALTER PROCEDURE statement is executed

(164) CreateSelector_TransThis script creates the xmlSelector stored procedure which is the Transactional controller for all Inserts/Updates/Deletes

(165) Creates the xmlSelector Trans stored procedure which is the Transactional controller for all Inserts/Updates/Deletes

(166) Sets the ProcPrefix value based on the ConfigurationValue in the ADOM_Configuration table

(167) Sets the CREATE/ALTER PROCEDURE statement based on whether the procedure already exists in the database

(168) Strings together the query logic based on the objects defined in the ADOM_TableList table

(169) Adds the syntax to extract the ModifyUser value from the root node in the XML Object

(170) Adds the logic to call the Process Changes and Delete sprocs for all objects in the database

(171) CREATE/ALTER PROCEDURE statement is executed

(172) CreateTableStructureThis script Interrogates the ADOM_TableList and database schema to generate the XML object structure and join logic for XML Queries of a given table

(173) Ensures that the @CreateTableStructureTableName value is cased the same as Table Name in the database

(174) Loops through the ADOM_TableList and ADOM_ForeignKey tables to determine related tables to build the hierarchical XML object tree structure

(175) Adds the syntax to return the columns for each object node level as well as any lookup table values for non-primary tables

(176) Adds the JOIN syntax to link the object tree together

(177) Exec Adorn Function. CreateGenericSearch

(178) Exec AdomFunction.CreateAttributesSelect

(179) TableListinsertThis script populates the ADOM_TableList table data

(180) Create ADOM_TableList Table if it does not already exist

(181) Interrogate Data Schema Structure

(182) Inserts/Updates data into ADOM_TableList Table based on schema interrogation

(183) Deletes rows for any tables which no longer exist in the data structure

(184) Sets the row for the ADOM_ConcurrencyFormControl table as a Primary table

(185) ForeignKeyInsertThis script populates the ADOM_ForeignKey table

(186) Inserts the database catalog name into the ADOM_DatabaseList table

(187) Interrogate ADOM_TableList and FK Schema definitions

(188) Insert into ADOM_ForeignKey table

(189) CreateLookUpTableStructureCreates the XML Object structure for a given non-primary lookup table, and then calls the create functions

(190) Interrogate schema structure and generate the XML Object structure for the given non-primary lookup table

(191) Exec AdomFunction.CreateGenericSearch

(192) Exec AdomFunction.CreateAttributesSelect

(193) Exec AdomFunction.CreateLookupSelect

(194) CreateLookupSelectThis script creates the XML object select stored procedure for a given non-primary lookup table

(195) Sets ProcPrefix based on ConfigurationValue in ADOM_Configuration table

(196) Sets the CREATE/ALTER PROCEDURE syntax

(197) If ADOM_TableList.GenerateSelectAllLookup=1

(198) Create an XML Select statement to return ALL rows in table

(199) DropExistingAdomProcsThis script deletes all pre-existing ADOM generated stored procedures, user-defined functions, views, and triggers

(200) Drops existing ADOM generated Procs, functions, views and triggers

(201) ConcurrencyFormThis script populates the ADOM_ConcurrencyFormControl table based on schema interrogation

(202) Interrogates database schema to determine tables to check for concurrency

(203) Populates the ADOM_ConcurrencyFormControl table based on schema interrogation and data from ADOM_TableList table

(204) UpdateMainSearchLoopThis script updates any custom search stored procedures with the latest XML object structure created in the Generic Search stored procedures

(205) Loops through the ADOM_MainQuery table data

(206) updates custom search logic with the latest Generic Search logic for the given XML Object based on special tags

(207) DefaultValue

(208) The DefaultValue table contains a list of all available data types and their associated default values, which are used by the Attribute Select stored procedures.

(209) DATA_TYPE=The data type for the given record

(210) DefaultValue=The default value for the given data type

(211) ProcsFunctionstoCopy

(212) The ProcsFunctionstoCopy table contains a list of special functions and procedures to copy to ALL ADOM-controlled databases.

(213) ProcsFunctionstoCopyID INT=Primary Key for the ProcsFunctionstoCopy table

(214) Type CHAR=Indicates whether the object is a user-defined function (F) or a stored procedure (P)

(215) Name VARCHAR(255)=The name of the object to copy

(216) Ground Rules for Database Design

(217) As previously mentioned, the ADOM process interrogates the relational database structure in order to determine the XML object structures. Thus, the structure of the database will determine the structure of the resulting hierarchical XML objects. With this in mind, there are certain ground rules and best practices which should be adhered to in order to produce the most optimal ADOM-generated XML and class object structures, as well as how those structures are manipulated and available to the consuming application. Below are some of the primary rules to consider when building a data structure which you intend to Adomize.

(218) All ADOM controlled database objects must have XML compliant names (i.e. Table name and column names should not contain spaces, slashes, etc.)

(219) All related tables should have Foreign Key Relationships defined.

(220) All ADOM controlled object tables must have Primary Key columns defined.

(221) You should limit the use of circular referencing Tables/Columns. Although ADOM can handle circular references, your resulting object structure can become less efficient.

(222) Binary objects (i.e. Images, Documents, etc. . . . ) should not be stored in primary object tables along with the metadata for the record. This will greatly increase the overall size of your objects.

(223) Sample Database

(224) For the purposes of this patent submission we have included a sample Adomized SQL database named ADOM_Sample4Patent (101). This simplified database only contained the following three tables and their foreign key relationships as identified in FIG. 3 prior to running the ADOM process.

(225) 102Person

(226) 103Email

(227) 104Phone

(228) The Person table (102) is shown to be related to the Email (103) and the Phone (104) tables via Foreign Key relationships (105) using the Person tables PK of PersonID. Thus, in this example the Person table would be the parent and the Email and Phone tables would be considered to be children. It is this Parent/Child relationship which is crucial throughout the entire set of ADOM processes. This relationship not only determines the XML object structure, but also the processing order on saves to ensure that FK values are present at the point of inserting any child records. We will reference these tables and concepts extensively throughout the remainder of this document.

(229) Special ADOM Tables

(230) The following is a list of special tables automatically generated and used by the ADOM process:

(231) ADOM_DatabaseListHolds a list of database catalog names referenced . . . .

(232) ADOM_TableListHolds a list of Tables in the database as well as other configurable data used by the ADOM process.

(233) ADOM_ForeignKeyHolds a configurable list of FK definitions in the database . . . .

(234) ADOM_NeverDuplicateHolds a list of Table/Column combinations which should contain unique values. This list will be used by the ADOM insert/update process to ensure uniqueness prior to update.

(235) ADOM_DefaultValueHolds a list of default values for each data type to be used by the Attribute Select statements which will be described later in this document.

(236) ADOM_MainQueriesHolds a list of custom queries which will be updated when the ADOM process is run.

(237) ADOM_ConcurrencyIssueHolds information about any concurrency issues found while attempting to save changes . . . .

(238) ADOM_ConcurrencyFormControlHolds information related to Tables/Columns which have been configured for concurrency checking. This data is used to automate the concurrency form controls in the UI. This topic will be described in detail later in this document . . . .

(239) ADOM_ConcurrencyFormControlTypeHolds a list of UI control types to be used in the ADOM_ConcurrencyFormControl table.

(240) ADOM_ModifyTypeHolds the modify type data used by the ADOM process . . . .

(241) The first time the ADOM process is ran against a database the above tables will be created and populated based on an interrogation of the system object tables with default configuration values. All subsequent runs of the ADOM process will merely update these tables in order to maintain configuration setting changes over time. The two most important tables in the ADOM framework and their primary contents are listed below. These tables are used during the ADOM processing to determine everything from the XML object structures to the middle-tier class object libraries, and how changes are processed during saves.

(242) ADOM_TableList:

(243) SchemaNameThis is the name of the schema to which the referenced table belongs . . . .

(244) TableNameThe name of the table being referenced . . . .

(245) PrimaryTableThis bit field indicates whether the table is a primary object table . . . .

(246) PrimaryKeyName of the PK field for the table being referenced . . . .

(247) IgnoreTableThis bit field tells the ADOM process to ignore the table in the primary XML object structure. Default Value=FALSE

(248) ModifyUserColumnThis is the column name in the referenced table which holds the modify user value, if any . . . . This field is handled in a special way by the ADOM framework . . . .

(249) ModifyDateColumnThis is the column name in the referenced table which holds the modify date value, if any . . . . This field is handled in a special way by the ADOM framework . . . .

(250) CreateUserColumnThis is the column name in the referenced table which holds the create user value, if any . . . . This field is handled in a special way by the ADOM framework . . . .

(251) CreateDateColumnThis is the column name in the referenced table which holds the create date value, if any . . . . This field is handled in a special way by the ADOM framework . . . .

(252) ADOM_ForeignKey: We will use the Person ID FK (105) in FIG. 3 which relates the Person table (102) to the Email table (103) as an example for the description of each below.

(253) PrimaryDatabaseNameThis is the name of the Database Catalog in which the primary table being referenced by the FK definition exists.

(254) PrimarySchemaNameThis is the schema name to which the Primary Table belongs.

(255) Primary TableThe name of the table in which the FK exists . . . . This would be the Person table (102) in FIG. FIG. 3.

(256) PrimaryColumnThe name of the column in the primary table as identified by the FK definition . . . . This would be the PersonID column in the Person table (102) in FIG. FIG. 3.

(257) ForeignKeyDatabaseNameThis is the name of the Database Catalog in which the foreign key table being referenced by the FK definition exists. (ADOM_Sample4Patent in our example)

(258) ForeignKeySchemaNameThis is the schema name to which the Foreign Key Table belongs. (dbo in our example)

(259) ForeignKeyTableThis is the table being related by the FK definition . . . . This would be the Email table (103) in FIG. 3.

(260) ForeignKeyColumnThis is the PK column of the table being related as identified by the FK definition . . . . This would be the PersonID column in the Email table (103) in FIG. 3.

(261) LinkedServerThis is the name of any Linked Server object required to relate the Primary and Foreign Key tables together. This is ONLY required for external FK references to catalogs on different servers.

(262) IgnoreForeignKeyThis bit field tells the ADOM process to ignore the FK relationship when creating the XML Object structure.

(263) Log Tables

(264) Once the above tables have been created and populated by the ADOM process, then Log tables are automatically generated for each table in the ADOM_TableList table where the PrimaryTable column equals TRUE. In FIG. 4 we have expanded the table list from FIG. 3 to include the auto-generated Log tables. In our example, the ADOM_TableList.PrimaryTable value would have been set to True for all three of the pre-existing tables, resulting in the new autogenerated tables Person_Log (205), Email_Log (206), and Phone_Log (207). You will notice that the structures of these tables are direct copies of their parent Primary Object tables, with the addition of the following two new columns:

(265) [ParentTableName]_LogIDThis is the primary key for the Log table.

(266) ModifyTypeIDThis is a FK to the ADOM_ModifyType table which has the following values:

(267) 1=Insert

(268) 2=Update

(269) 3=Delete

(270) Once the ADOM generated Log tables have been created, the ADOM process generates triggers to populate the Log table with the changes that occur in the Primary table. On any subsequent run of the ADOM process against the database, the process will automatically update the Log tables and Triggers to reflect any changes in the database structure.

(271) XML Object Structure

(272) The next step in the ADOM process is to determine the hierarchical object structure of the relational database tables. This process combines data from both the ADOM_TableList and ADOM_ForeignKey tables. ADOM iterates through the tables in the ADOM_TableList data where PrimaryTable=TRUE and IgnoreTable=FALSE. For each primary table found, another process is spawned which then starts walking the chain of related tables based on the FK definitions contained in the ADOM_ForeignKey table where IgnoreForeignKey=FALSE. For each new relationship found, a Path value is created starting with the original Primary Table as the Root node. In our ADOM_Sample4Patent database, the resulting Path values would be as follows:

(273) Person

(274) Person/Email

(275) Person/Phone

(276) Email

(277) Email/Person

(278) Email/Person/Phone

(279) Phone

(280) Phone/Person

(281) Phone/Person/Email

(282) Once the process has walked the relationship tree as far as it can in every direction, for a given Primary Table, then the generated Path values are evaluated and reduced down to only contain those with maximum distinct values. So, this would reduce the above list down to the following Paths:

(283) Person/Email

(284) Person/Phone

(285) Email/Person/Phone

(286) Phone/Person/Email

(287) Then this information is used to generate a hierarchical XML object structure by iterating through each table in the Path list and gathering any column and attribute data required to construct the Generic Search and Attribute Select stored procedures. In our simplified example, the 4 paths above will result in only 3 root object perspectives:

(288) Person

(289) Email

(290) Phone

(291) The resulting XML object structures for each of these can best be seen in our ADOM_Sample4Patent database by executing the following stored procedures:

(292) ADOM_AttributeSelectPerson

(293) ADOM_AttributeSelectEmail

(294) ADOM_AttributeSelectPhone

(295) Generic Search Stored Procedures

(296) At this point, ADOM uses custom syntax to generate an XML object select stored procedure and save it in the database under the ADOM_spXmlGenericSearch[TableName] naming convention. This stored procedure will select the hierarchical XML object based on the PK parameter of the root primary object. In our example, this process would result in the following three stored procedures, which can be seen in the attached ADOM_Sample4Patent database:

(297) ADOM_spXmlGenericSearchPerson @PersonID (400)This would return a hierarchical Person object with all associated Email and Phone records.

(298) ADOM_spXmlGenericSearchEmail @EmailIDThis would return a hierarchical Email object with the associated Person and all Phone records.

(299) ADOM_spXmlGenericSearchPhone @PhoneIDThis would return a hierarchical Phone object with the associated Person and all Email records.

(300) The above mentioned stored procedures will give you a default way to select data in your database from the perspective of any Primary object table. In our example, you could pull the data for the Person XML object and get all of their nested Email and/or Phone records (301), or you could pull the data for a Phone or Email and get the person as a nested child record in the XML (302). This allows for a maximum level of flexibility in how you can view and manage data from an Adomized database.

(301) Custom Search Stored Procedures

(302) The Generic Search approach above is great for working with data when you know the PK of the root object, but what about when you don't know the PK of the record with which you are wanting to work? The ADOM framework has a way to allow the developer to create custom search stored procedures which will be automatically updated every time the ADOM process is run. All the developer has to do is copy the select logic from the generic search for the object type for which he is wanting to search, and paste that logic into a new stored procedure (500). Then the developer simply adds a few special comment tags (501, 502, & 503) to the new SQL select query, and adds a single entry to the ADOM_MainQueries table as follows:

(303) BaseSelect=The name of the ADOM Generic Search stored procedure from which you copied the original select logic. This is the ADOM_spXmlGenericSearchPerson (400) sproc in our example.

(304) MainQuery=The name of the custom search sproc which you wish to be auto-maintained by ADOM. This is the Person_SearchByName (500) sproc in our example.

(305) Attributes Select

(306) ADOM also creates a special stored procedure for each primary object which returns a fully-formed hierarchical XML object representing every potential node in the object structure populated with sample data and special attributes for each node. The sample data in the attribute select stored procedures is populated based on the values for each data type contained in the ADOM_DefaultValue table. The resulting attribute select stored procedures are used by the ADOM Class Generator API process which will be described later in this document. FIG. 8 is the output for the ADOM_spXmlAttributesSelectPerson stored procedure included in the ADOM_Sample4Patent database accompanying this submission. The attributes returned are listed below:

(307) DatabaseName (601): The database catalog to which the Source Table belongs

(308) SchemaName (602): The database schema to which the Source Table belongs

(309) Type (603): Data Type

(310) DataTypeLength (604): The maximum length for a character-based data type

(311) IsNullable (605): Whether the element is nullable

(312) SourceTable (606): The Source Table for the element

(313) IsForeignKey (607): Whether the field is a Foreign Key to another table

(314) LinkedServer (not listed): The linked server name (if required) to which the FK catalog

(315) ForeignKeyDatabase (608): The database catalog to which the Foreign Key Table belongs

(316) ForeignKeySchema (609): The database schema to which the Foreign Key Table belongs

(317) ForeignKeyTable (not visible): Table to which the Foreign Key relates

(318) ForeignKeyColumn (not visible): The related key field in the Foreign Key table

(319) Class Builder API

(320) The ADOM framework also includes a custom Class Builder API that can automatically generate a Microsoft.NET class library structure which matches the ADOM-generated XML object structure in the database. This allows the XML objects retrieved from the database to be directly de-serialized into .NET manageable objects.

(321) The Class Builder API utilizes the XML output that is returned from the ADOM generated Attribute Select stored procedures as seen in FIG. 8. The XML output returned from these stored procedures contains attributes which the Class Builder API uses to define the class objects, such as the namespace (1001), which is a combination of the DatabaseName (601) and SchemaName (602) attributes, and object relationships.

(322) Additionally, the Class Builder API appends .NET Data Attributes to the generated fields that correspond with the supplied attributes in the XML attribute select stored procedure. For example, the DataTypeLength (604) and IsNullable (605) attributes translate to data validation attributes (1002 & 1003) in the .NET class that can then be used downstream in a client application.

(323) When generating the class objects, only the top level corresponding attribute XML output needs to be supplied to the Class Builder since any referenced objects are already contained in the hierarchical structure. After selecting the data object for which to build a class, the Class Builder API then examines the solution in Visual Studio and if multiple projects are found, the user is prompted to select the project to which the .NET classes are to be added. The project file structure (FIG. 16) that is generated, mirrors the namespace defined for each of the classes which, as previously stated, is a combination of the schema and database name (1401). This solves the problem of generating classes that may have the same name as other classes which have been (or will be) generated for a different schema or catalog.

(324) Partial sample class structures which were generated from the attached ADOM_Sample4Patent database have been provided in FIGS. 12-14. The Person.cs primary class object output can be seen in FIG. 12. The Email.cs class object output can be seen in FIG. 13, and the Phone.cs class object output can be seen in FIG. 14. Not only does the Class Builder handle relationships from parent to child, it also generates a place holder reference for each class that serves as a breadcrumb trail back up the structure. For example, the Person table has a reference to Phone, but it may not be directly clear to a developer that Phone ties back to a Person record, or possibly multiple Person records. As seen in FIG. 8, the Phone and Email definitions also contain an empty Person node with the GenerateClass=NO attribute (609 & 610). If the Class Builder API encounters this node when parsing the XML, it recognizes the GenerateClass attribute and creates an object reference in the class, but does not build another Person class object again, since it was already previously defined at a higher level in the object hierarchy.

(325) There is also a dependency reference to the AdomFramework assembly which is automatically added to the project that was previously selected in the Class Builder API process. This reference contains the definition for the base class that all of the generated data object classes inherit. The AdomFramework assembly also contains a library of Utility methods that assist the developer in serializing and de-serializing the .NET objects to and from XML. The assembly additionally contains other methods which handle merging multiple objects into one, as it is possible the developer is expecting multiple XML responses from the server. This scenario arises when objects are referenced across separate database catalogs.

(326) Database Update Save Process

(327) In a standard relational database management approach there are usually many different queries which must be manually managed in order to save changes to the underlying data. These processes must be manually updated every time there is a change in the data structure, which can be a very tedious task for a DBA or developer. The ADOM framework includes the automated generation of all logic required to save changes to the data. There is a single point of entry stored procedure called the ADOM_spXmlSelector, which takes a single XML parameter that is your updated XML object. This stored procedure is the coordinator for all saves, and is the real workhorse behind the update process. There are two versions of the ADOM_spXmlSelector stored procedure available as options for all saves:

(328) spXmlSelector: This version saves each level of the object individually allowing for the greatest potential of persistence at all levels on the save.

(329) spXmlSelector Trans: This version saves the entire object as a Transaction, which is rolled back if any part of the save fails.

(330) Once the ADOM_spXmlSelector stored procedure has been executed, it interrogates the incoming XML document object parameter to determine contents, and then calls subsequent Process Changes stored procedures for each object level which handle inserts, updates, and deletes for the underlying relational database tables.

(331) Concurrency Checking

(332) It is at this point that any concurrency checks required will be performed and automatically logged when found. Each concurrency issue found in the execution batch will be written to a special ADOM_ConcurrencyIssue table, and returned to the calling application as described below in the final results set. Once all updates have been processed by the ADOM_spXmlSelector stored procedure, then the primary key of the root node in the XML parameter object is used to re-select the latest version of the object as persisted in the relational tables. Also returned are any concurrency issues found during the save process. There will be a single row for each concurrency issue found which contains the data that was attempted to be saved when the concurrency issue was detected. The resulting returned set of data will then potentially contain two different data sets:

(333) The updated XML object as constructed based on the latest persisted data in the relational database tables.

(334) Any concurrency issues detected during the save process with the data attempted to be saved in its original XML format. This data is used in the application layer to allow the user to decide what is ultimately persisted to the database.

(335) The ADOM framework includes a special set of functionality regarding the concurrency data elements which includes the ADOM_ConcurrencyFormControl and the ADOM_ConcurrencyFormControlType tables. Any data elements in a table which is configured for concurrency checking will be automatically processed into the ADOM_ConcurrencyFormControl table, which includes the following columns:

(336) ConcurrencyFormControlIDInteger based Identity PK column

(337) ConcurrencyFormNameThis is the name of the table which contains the data element

(338) ConcurrencyFormControlTypeIDThis is a FK value to the appropriate ADOM_ConcurrencyFormControlType value. (This is described below)

(339) DisplayTextThis is the text which will be displayed to the user in the concurrency UI control which will be defined later in this document. This value defaults to the column name, but can be configured to any text value.

(340) ValueColumnThis is the name of the column from the originating table.

(341) FKTableNameIf the ValueColumn is a FK value, then this would be the table name of the lookup or type table referenced.

(342) FKValueColumnIf the ValueColumn is a FK value, then this would be the FK value column name in the FKTableName table.

(343) FKTextColumnIf the ValueColumn is a FK value, then this is an optional column used to identify the appropriate Text column in the FKTableName table. If there are only two columns in the FKTableName table, then this column is not necessary.

(344) OrderByThis column tells the Concurrency UI control, which will be discussed later, what order to display the concurrency issue results.

(345) The ADOM_ConcurrencyFormControlType table holds a list of UI input form control types to be referenced by the ADOM_ConcurrencyFormControl table. By default, the list of control types below will be automatically added to this table:

(346) TextboxAny data type can be assigned to this type

(347) DropdownAny column that is a FK to a lookup/type table will default to this type

(348) NotesAny text column which would require multi-line display would be assigned to this type

(349) CheckboxBit columns would default to this type

(350) RadioADOM does not default any specific data type to this concurrency form control type, but the developer may assign it as desired

(351) DatetimeAny datetime column will be defaulted to this type

(352) MoneyAny money column will default to this type

(353) As the ADOM process is adding records to the ADOM_ConcurrencyFormControl table, the data type of the column determines which of these ADOM_ConcurrencyFormControlType records to relate to by default. The default values listed above will be auto-assigned by the ADOM process, but these assignments can be overridden by the developer simply by changing the values directly in the table. Any custom control types may also be added to the ADOM_ConcurrencyFormControlType table, such as a custom control formatted for specific data types like phone numbers or SSN.

(354) Primary Key Handling

(355) ADOM has the ability to handle multiple types of Primary Key (PK) definitions including the following data types:

(356) Integer-based Identity columns

(357) Globally Unique Identifier (GUID) columns

(358) Any other data type where the PK value is passed into the XML object

(359) As new records are saved to a table, the ADOM framework automatically handles the generation and persistence of the new PK value to any other hierarchical node levels in the XML parameter document which may use this value in a FK definition. This requires ADOM to intelligently handle the order in which individual node levels (Tables) are processed in order to ensure any required FK values exist at the point of save. The PK values for newly inserted records are then updated throughout the XML parameter object in order to satisfy any FK needs before any child records are written to the database. Since the process of iterating through the object to update any FK values may potentially negatively impact performance on saves of large objects with many new records, the ADOM object structure also includes a special XML node at each table level called IgnoreIdentityInsert (303). The developer can tell the save process to ignore the new identity update process for any part of the XML object structure by setting this value to TRUE.

(360) Partial Saves

(361) By default, the ADOM framework is set up to allow the application to save the entire object structure in a single stored procedure call, which iterates through the XML object and saves each node level individually. This allows the developer the greatest level of abstraction throughout the entire application development life-cycle. However, as the complexity and size of the object grows, the developer may eventually see a performance hit on the save due to the large number of node levels which must be processed. In such cases, the ADOM framework elegantly handles what we call a Partial Save. This is where the developer can explicitly determine what is sent to the ADOM_spXmlSelector stored procedure and only the modified node levels sent will be processed in the save. The modified nodes are simply wrapped in an Envelope root node level (701 & 702) which only contains the PK value for the parent node and a ModifyTypeID node with a value of 0 (no changes). In the example shown in FIG. 9 the Phone record (703) has been set to be deleted (ModifyTypeID=3). This allows the save process to still return the entire updated hierarchical object at the end of the stored procedure (704), thus requiring no modifications to the refresh or concurrency check processes in the UI. In our example, you will notice that the original Person object (301) has changed to the new state of 704, which is missing (705) the deleted phone record with PhoneID=1.

(362) Full Life Cycle

(363) In order to discuss the full life cycle of an application process which utilizes an Adomized database, the custom web application development model represented in FIG. 10 will be used as an example. However, the ADOM framework is open to use with all application platforms.

(364) Select the Objects

(365) The first step in the process would be to select the hierarchical XML object from the database using one of the Generic Search stored procedures which are generated by the ADOM framework (801). Once we have selected the appropriate XML object (802), then we deserialize the XML object directly into the auto-generated middle-tier class object library (803). At this point we serialize the middle-tier class objects into JSON objects (804) using the DataContractJsonSerializer class provided by the Microsoft.NET Framework.

(366) Modify the Objects

(367) The JSON objects are then passed to the calling web components and bound directly to the web UI controls (note: It could be any UI platform at this point). The user interacts with these controls to manage the data (i.e. Insert/Update/Delete). The UI can also be set up to utilize the JSON object schema for automated field-level validation based on the data type, length, and whether the field is nullable. The data is manipulated by the user through the web UI controls and then once all desired changes have been made, the entire updated JSON object can be passed back to the middle tier and deserialized back into the middle-tier class object library.

(368) Save the Objects

(369) The updated class objects are serialized back into XML (805), and the entire XML object (806) is assigned to a single XML parameter in the ADOM_spXmlSelector stored procedure (807), and then the stored procedure is executed. FIG. 11 details the save process as orchestrated by the ADOM_spXmlSelector stored procedure (901). The first step in the save process (902) is to interrogate the XML object structure to determine the root object type, and determine all child object types and the order in which each object level should be processed to ensure all FK values will be present at the appropriate times during the save. Then the appropriate ADOM-generated sub-procedures (903) are called in the correct order passing in the appropriate node level for each.

(370) Each node level is processed based on the ModifyTypeID node value (1=Insert, 2=Update, 3=Delete). On Inserts, if the node level's IgnoreIdentityInsert value is FALSE, the new PK value is updated across the XML document in any FK references found (904), in order to ensure that all constraints are met. If IgnoreIdentityInsert is TRUE, then this part of the process is ignored by the stored procedure, which helps to ensure optimal performance during the save process. On Updates, any tables which have been configured for concurrency checking will be evaluated in the sub procedure (905). If a concurrency issue is found, then the data the user was attempting to save is written to the ADOM_ConcurrencyIssue table to be returned at the end of the save process.

(371) Return the Updated Objects

(372) Once all node levels have been processed, the ADOM_spXmlSelector logic determines the PK for the root object (906), and then the root XML object is selected (907) using the appropriate ADOM_GenericSearch* stored procedure. This object will contain the latest state of the hierarchical object data as persisted to the database. Also, if there were any concurrency issues discovered during the save process, then these are also returned (908) along with the updated XML object (909) to the calling process. At this point, the process starts back over at 801, with the exception of the fact that any concurrency issue records found will also be returned to the middle tier.

(373) Handle Concurrency Issues

(374) Any concurrency issues found are displayed to the user based on a combination of the concurrency records returned and the data from the ADOM_ConcurrencyFormControl table. The UI dynamically marries the two of these together to render the concurrency form UI (FIG. 15). This UI allows the user to select whether they want to keep the server version (1302), or override it with their changes (1301). Once the user has iterated through all concurrency issues, then the selected changes are saved (1303) and persisted to the database.

(375) Thus, although there have been described particular embodiments of the present invention of a new and useful SYSTEM FOR MANAGING A RELATIONAL DATABASE USING XML OBJECTS, it is not intended that such references be construed as limitations upon the scope of this invention.