Method of modifying a populated database structure by modifying metadata describing the database structure6366917Abstract A method of modifying a database structure requires firstly modifying metadata that describes the relevant database structure. Thereafter, the relevant database structure is automatically modified to conform to the modified metadata. The step of modifying the metadata comprises modifying a description of a database object characteristic in a metadata object. Claims What is claimed is: Description FIELD OF THE INVENTION
TABLE 1
Procedure Type Description How Defined How Generated
MAN Manually Manually Manually
(Manual) Written
Procedure
DEF Defaulting Manually Manually
(Defaulting) Procedure
VAL Validation Manually Manually
(Validation) Procedure
CON Generated Manually Build +
(Control) Control of Upgrade Logic
Database Updates
SUB Generated Table Manually Build +
(Update Subset) Subset Update Upgrade Logic
Procedure
INS Generated Generated Build +
(Insert) Standard Table Upgrade Logic
Insert Procedure
UPD Generated Generated Build +
(Update) Standard Table Upgrade Logic
Update Procedure
DEL Generated Generated Build +
(Delete) Standard Table Upgrade Logic
Delete Procedure
Table 2 lists a number of stored procedure objects, in the "Associated Definitions" column, that are included in an exemplary database described below with reference to FIG. 6.
TABLE 2
Procedure How Associated Definitions
Type Associated Definitions Defined
MAN Procedure_Columns Automatically when compiled
(Manual) Procedure_Columns_Types Automatically when compiled
Procedure_Dependencies Automatically when compiled
Procedure_Views Automatically when compiled
DEF Procedure_Columns Automatically when compiled
(De- Procedure_Columns_Types Automatically when compiled
faulting) Procedure_Dependencies Automatically when compiled
Procedure_Views + Automatically when compiled
Procedure_Arguments Manually
VAL Procedure_Columns Automatically when compiled
(Valida- Procedure_Columns_Types Automatically when compiled
tion) Procedure_Dependencies Automatically when compiled
Procedure_Views + Automatically when compiled
Procedure_Arguments Manually
CON Procedure_Tables Manually
(Control)
SUB Procedure_Arguments Manually
(Update
Subset)
INS Tables (Insert Flag) Manually
(Insert)
UPD Tables (Update Flag) Manually
(Update)
DEL Tables (Delete Flag) Manually
(Delete)
Procedure types CON, DEL, INS, SUB, and UPD are code-generated Stored Procedures. Procedure types CON and SUB Stored Procedures are generated from the Procedures Table definitions. Procedure types DEL, INS and UPD Procedures Table definitions are automatically updated as the Stored Procedures are generated. Procedure types CON, DEF, MAN, SUB and VAL Procedures Table definitions must be manually updated. Procedure types DEL, INS, MAN, and UPD have only Procedures Table entries. Procedures type CON has Procedures (mandatory) and Procedure_Tables (optional) Table entries. Procedure types DEF, SUB and VAL have Procedures and Procedure_Arguments Table entries. FIG. 5 provides a diagrammatic representation of a portion of an exemplary metadata database 50 according to the present invention. The metadata database 50 is shown to include a number of table objects similar in structure to the table objects that comprise the generated database 18. The table objects of the metadata database 50 include records specifying structural, description and management information for objects within an associated database 60. The exemplary metadata database 50 comprises a COLUMN TYPE object 52, a COLUMNS object 54, a TABLES object 56, and a TABLE_COLUMNS object 58. The COLUMN TYPE object 52 specifies format information for various column types, and is shown to include three columns, namely an ID column, a type column and a length column. Two records are shown to exist in the object 52, these records specifying respective data lengths for a long date column type and a short date column type. The COLUMNS object 54 is shown to comprise four columns, namely an ID column, a code column, a description column and a type column. The COLUMNS object 54 includes records for enquiry date, estimated delivery date and actual delivery date columns which may be included within any of the tables of the database 60. The code column stores code names for the respective columns, the description column stores respective descriptions for the relevant columns, and the type column stores information specifying a column type attributed to each column. The enquiry date and actual delivery date column records are shown to have been attributed the long date column type, whereas the estimated delivery date column record is shown to have been attributed the short date column type. The TABLES object 56 is shown to specify an ID and a name (e.g., ID "67" and name "ORDERS") for each of the tables included in the generated database 60, and also includes various descriptive and management information pertaining to each table. The TABLE_COLUMNS object 58 provides a mapping of columns to a particular table ID, and thus specifies which columns are included within each table. Accordingly, the ORDERS object 62 of the generated database 60 is shown to include the enquiry date, the estimated delivery date, and the actual delivery date columns as defined in the COLUMNS object 54. The build logic 40 and update logic 42 include sequences of instructions that are executed by a processor of the server 12, and cause the processor to utilize the information contained within the TABLE_COLUMNS object 58, and also information within the objects 52, 54, and 56 upon which the TABLE_COLUMNS object 58 is dependent, to generate and update the ORDERS object 62 within the database 60. This is the reverse of the methodologies and logic taught in the prior art, where metadata is generated from existing objects within the database to provide a readily accessible description of the database. The present invention is particularly advantageous in that it allows a database manager to make changes to the database object descriptions within the metadata database 50, and these modified descriptions are then utilized to modify objects (e.g. table, view and stored procedure objects) within the database 60. For example, if a database manager wishes to change the format of all estimated delivery date columns within the database 60, the manager could simply alter the type specification in the COLUMNS object 54 to reflect the long date format. Accordingly, all records within the TABLE_COLUMNS object 58, that specify the estimated date column, and that are accordingly dependent on the COLUMNS object 54, would by reason of their dependencies include this updated information. Accordingly, objects may be generated and/or updated in the database 60 by the build and update logic 40 and 42 to reflect this modification. Similarly, a database manager could alter the length of the long date format from eight to nine characters, and this change would be propagated to all relevant objects within database 60 by reason of the dependencies that exist within the metadata database 50. The present invention is thus furthermore advantageous in that modifications are also made only to relevant objects within the database 60, and the modification process is accordingly "incremental" in nature. The ease with which modifications can be made to the database 60 is facilitated by the dependencies which are defined between the various objects which comprise the metadata, and by the use of metadata to generated and update structures and relationships of objects within the database 60. In an exemplary embodiment, the generated database 18 and database system 14 may be utilized to implement an Enterprise Resources Planning (ERP) system. FIG. 6 illustrates a Booch notation diagram depicting an exemplary metadata database 70 that may be utilized to describe a database (not shown) for implementing the ERP system. Objects (i.e., tables) are represented in broken line, and rows or records (i.e., instances of these objects) are represented by circles and shown to be included in respective table objects. Each row (or record) in each metadata object (e.g., row T1 in object TABLES) represents a table (i.e., object) in the generated database 60 and also a view (i.e., yet a further object). The objects shown to be included within the metadata database 70 include a COLUMN TYPES object 72, a COLUMNS object 74, a TABLE_COLUMNS object 76, a UNIQUE KEY COLUMNS object 78, a TABLES object 80, a PROCEDURE TABLES object 82, a PROCEDURE COLUMNS object 84, a PROCEDURES object 86, a PROCEDURE ARGUMENTS object 88, a PROCEDURE COLUMN TYPES object 90, a PROCEDURE VIEWS object 92 and a PROCEDURE DEPENDENCIES object 94. Arrows depict various dependencies that exist between the rows (or records) of metadata objects. For example, the TABLE_COLUMNS object row TC1 is shown to be dependent on the COLUMNS OBJECT row C1, the TABLES object row T1 and the UNIQUE KEY COLUMNS object row UK1 in a manner analogous to that described above with reference to FIG. 5. A brief description of each of the objects 72-94 will now be provided, with reference to FIG. 6 in conjunction with FIGS. 7-18. Turning first to the COLUMN TYPES objects 72, this object includes a number of rows that specify type and format information that can be referenced by a column to attribute the required type and format characteristics to the referencing column. FIG. 7 illustrates a view 100 showing selected information from a number of rows within an exemplary column types object 72. FIG. 8 illustrates a user interface 102 by which a user can generate and edit a COLUMN TYPE row for inclusion within the COLUMN TYPE object 72. The information that can be included within a COLUMN TYPE row is apparent from the input fields displayed in the user interface 102. Specifically, each COLUMN TYPE row includes an identifier (e.g., a name ), that comprises the "column type" input 102a. This identifier uniquely identifying each COLUMN TYPE row. A user further has the option of attributing a specific data type (i.e., a numeric, character or date type) and data length to the COLUMN TYPE row via input fields 102b and 102c. Also of interest is the ability by a user to specify data types, via input fields 102g and 102i, for both Oracle 8 and SQL Server databases. This feature is particularly useful in converting a database table from an Oracle 8 format to a SQL Server format, or vice versa. A view 104 illustrating exemplary COLUMNS rows included within the COLUMNS object 74 is shown in FIG. 9. Each COLUMNS row listed in the view 104 includes a column code, shown at 104a, by which the COLUMNS row is uniquely identified. Each COLUMNS row is furthermore shown to include a type specified, displayed in column 104d, that corresponds to a COLUMN TYPES row included within the COLUMN TYPES object 72. FIG. 10 illustrates a user interface 106, utilizing which a user can generate and edit a COLUMNS row. As illustrated, a user can input and modify column code and column type information via fields 106a and 106d respectively. Also of interest is the input field 106h, via which a user can specify a foreign key table within the database. FIG. 11 illustrates a view 108 listing various table rows that exist within the TABLES object 80. FIG. 12 illustrates a user interface 110, via which a user may edit a table definition. For example, a user may specify a unique identifier code in an input field 110a. FIG. 13 illustrates a view of the contents of exemplary rows within the TABLE_COLUMNS object 76. The rows from which the view 112 is extracted map a number of columns defined by rows within the COLUMNS objects 74 to a TABLES row within the TABLES object 80. A row for the ITEMS table, within the TABLES object 80 and listed in the view 108 of FIG. 11 (at 108a), is associated with a number of COLUMNS rows listed in column 112a and included within the COLUMNS object 74. FIG. 14 illustrates a user interface 114 with which a user can input and modify information pertaining to each of the columns listed within a TABLE_COLUMNS row. FIG. 15 illustrates a view 116 listing unique key definitions for the ITEMS table, while FIG. 16 illustrates a user interface 118 using which the unique key definitions for a specific table within the database can be inputted or updated. The user interface 118 provides a table column field 118a at which a user can identify columns within a table to comprise unique key columns for that table. FIG. 17 illustrates a view 120 listing exemplary procedures that are included within the PROCEDURES objects 86. The view 120 provides a first column 120a in which an identifier for each procedure is listed, and a second column 120b in which a type characteristic for each procedure is identified. Specifically, each procedure is shown to be identified as being a control (CON), a delete (DEL), an insert (INS), an update (UPD) or a manually written (MAN) procedure type. Each procedure may be identified as being any one of the procedure types listed above in Table 1. Each table object within the database may have one or more procedures associated therewith for performing control, deletion, insertion, updating and other operations with respect to the relevant table object. For example, the control procedure CON P1 of the PROCEDURES object 86 is shown in FIG. 6 to be related to, and to depend from, the TABLE row T1 within the TABLES object 80. FIG. 18 illustrates a user interface 122 through which a user can specify a particular procedure type for a procedure at field 122b, and define a foreign key relationship between the relevant procedure object and a table object at field 122c. Referring again to FIG. 6, the metadata database 70 finally also includes TABLE_VERSIONS and TABLE_VERSION_COLUMNS objects 96 and 98 that include records logging modifications made to objects within the generated database, via the metadata. Examples of the objects 96 and 98 are provided in FIGS. 19A and 19B . The examples provided log modifications that were made to ORG_STRUCTURE and ORGS tables, which are included within an exemplary generated database 60, between versions 3 and 4. The TABLE_VERSIONS table 96 illustrated in FIG. 19A contains a record (or row) for each version of the relevant tables that w has existed. Each record has an identifier (ID) associated therewith by which the version of the table is identified. For example, the ORG_STRUCTURE table, version 3, is identified by the ID 1092 while version 4 of this table is identified by the ID 1195. The TABLE_VERSION_COLUMNS table 98 illustrated in FIG. 19B includes a record for each column within each of the relevant tables, and records specific details regarding each of these columns. By comparing records for a specific column that were created for different versions of a relevant table, modifications to a column are recognized. For example, between versions 3 and 4 of the ORG_STRUCTURE table, the LEVEL_NUMBER column was changed from an integer (INT) data type with a length of 10 to a small integer (SMALLINT) data type with a length of 5. Further, in the ORGS table, the CURRENCY_ID column changed from being NOTNULLABLE to being NULLABLE from version 3 to version 4. These modifications are documented in FIG. 19B. Methodologies FIG. 20 is a flow chart illustrating at a high level a method 150, according to the present invention, of modifying a database structure by modifying metadata describing that database structure. The method 150 is performed by the database system 14, which interacts with the metadata database 16, the generated database 18 and the history base 20. The method 150 commences at 152, and then proceeds to step 154 where a user, such as a database manager, modifies the metadata database 16 using a user interface. For example, the user may modify any one of the rows included within the objects illustrated in FIG. 6 using user interfaces such as those illustrated in FIGS. 8, 10, 12, 14, 16 or 18. Referring to the exemplary metadata objects shown in FIG. 5, a user could, for example, modify the column type specification of the estimated delivery date from the short date format to the long date format using a user interface similar to that illustrated at 106 in FIG. 10. The method 150 advances from step 154 to step 156, where the server 12 is called to set a modified flag for each row of each object in the metadata database 16 affected by the modification. Specifically, each row within the object of the metadata includes a modified field that can be set or reset to indicate whether, for example, a table described a relevant table row is affected by the modification to the metadata. The setting of the modified flag for each affected row requires examining the dependencies and relationships that exist between rows of objects that exist within the metadata database 16. Referring to the example provided in FIG. 5, if the length of the long data column type is modified from 8 to 9 characters, this modification will be propagated down via dependencies that exist between the various tables to TABLES object 56. At step 158, tables within the generated database 18 are automatically modified in accordance with the modifications made to the metadata database 16 at step 154. Further details regarding this automatic modification of tables of the generated database 18 are provided below. At step 160, procedures within the database are also automatically modified in accordance with modifications made to the metadata database 16. Further details regarding the automatic modification of procedures by a server executing the update logic 42 are illustrated in FIGS. 27A and 27B. At step 162, the modified flag for table and procedure rows in the metadata database 16 are reset. The method 150 then terminates at step 164. FIG. 21 is a flow chart illustrating the sub-steps which comprise the step 158 of method 150. Step 158 commences at 166, and proceeds to decision box 168, where a determination is made as to whether more than one table is to be modified. This determination is made by examining the modifier flags of table rows within the metadata database 16. If so, table dependencies for tables within the generated database 18 (as opposed to tables within the metadata database 16) are set. For example, referring to FIG. 3, where the table 36 is dependent on the table 38 and assuming that the format for the department number data entries is to be modified, the table 36 would have a table dependency set to indicate its dependency on the table 38. Accordingly, a dependency sequence is established wherein, for example, the table 38 would be modified prior to the table 36 in the dependency sequence as the table 36 is dependent on the table 38. From step 170, the step 158 proceeds to step 172. Returning to decision box 168, should it be established that more than one table requires modification, the step 158 proceeds directly from decision box 168 to step 172. At step 172, the first table within the dependency sequence is modified in accordance with the modified description within the metadata database 16, whereafter a determination is made at decision box 174 whether there are any further tables which have a set modifier flag. If so, step 172 is revisited. The step 158 loops through steps 172 and 174 until all tables having set modifier flags have been modified. The step 158 then terminates at step 176. FIGS. 22A and 22B show a flow chart illustrating the sub-steps that comprise the table modification step 172 illustrated in FIG. 21. The step 172 is performed with respect to a single table (a "subject table"), and is iteratively performed with respect to each table that requires modification. After commencing at step 180, a transaction identifier is established for the modification operation to be performed on the subject table. At decision box 184, a row count with respect to the subject table is performed to determine whether the subject table in fact contains any records (i.e., rows). If the row count is greater than zero, indicating that at least one record is contained within the subject table, a "delete all" procedure is created and executed at step 186 to delete all rows from the subject table. Prior to actually deleting rows from the subject table, the "delete all" procedure will export data contained in such rows to the history base 20, which acts as a temporary repository for this information during modification of the subject table. Subsequent to this exporting of data from the rows, the "delete all" procedure will then delete all rows from the table. From step 186, the step 172 advances to step 188. Alternatively, should it be determined at decision box 184 that there are in fact no rows within the table, the step 172 proceeds directly from the decision box 184 to step 188. At step 188, foreign key constraints which are imposed upon related tables are removed. At step 190, the table is dropped from the generated database 18. If the database system 14 permits, dropping the table causes constraints on the table to also be removed. If the database system 14 does not permit this, these constraints on the table are first removed, and the table is then dropped. A new table is created at step 192 according to the modified definition contained in the metadata database 16. At step 194, unique key constraints for the new table are built, and the new table's standard view is then automatically re-built by the update logic 42 of the database system 14 at step 196. At decision box 198, a determination is again made as to whether the row count for the original subject table was greater than zero. If so, an "insert all" procedure is created and executed at step 200. Specifically, the "insert all" procedure retrieves the data exported at step 186 by the "delete all" procedure from the history base 20, recreates rows within the new table according to the modified metadata definition, and stores the imported data into respective rows. The step 172 then advances from step 200 to step 202. Alternatively, should it be established at decision box 198 that no rows existed within the original subject table, the step 172 advances directly to step 202. At step 202, foreign key constraints on the new table are again reconstructed. At step 204, foreign key constraints imposed by the new table on related tables are also reconstructed. At step 206, the table version is updated, and at step 208 new insert (INS), delete (DEL) and update (UPD) procedures are created. Regarding step 206, FIG. 12 illustrates at 110c that a version number is associated with each table. The TABLE_VERSIONS object 96 and TABLE_VERSION_COLUMNS object 98 are also updated at step 206. Details regarding modified, updated or deleted table, along with appropriate version numbers, may be stored in the history base 20 to provide an audit trail of table definitions. At step 210, procedure modified flags are set for all stored procedures within the database which require modification as a result of the modification to the subject table. Specifically, modified flags are set for all procedure types listed in Table 1 (other than INS, DEL and UPD procedures that were generated at step 205) that require modification. FIG. 23 is flow chart indicating the steps required to rebuild the standard view for a table. After commencing at step 220, a determination of the table code (as indicated at 110a in FIG. 12) is made. At step 224, a view_generate directory is retrieved. At step 226, the database system 14 loops through the table_columns rows for the table code determined at step 222 in order to build the selected columns. At step 228, the database system 14 loops through table columns object for linked tables, and then generates "from" and "where" clauses utilizing the table code and linked table information. FIGS. 24-26 are flowcharts illustrating the steps according to one embodiment of the invention, for setting a modified flag for each column, column type and table affected by a modification indicated by the metadata database 16. Specifically, FIG. 24 lists the steps in a SET_MODIFIED_COLUMN procedure that sets modified flags for rows (or records) included within a TABLES object within a metadata database 16, in response to a different column type being assigned to a column. FIG. 25 illustrates the steps in a SET_MODIFIED_COLUMN_TYPE procedure that sets modified flags for affected rows within a TABLES object in response to the modification of a column type to have an updated data type. FIG. 26 illustrates the steps in a SET_MODIFIED_TABLE procedure that sets modified flags for rows within TABLES objects in response to a modification of the definition of a table in the metadata database 16. Returning to FIG. 24, the procedure 300 commences in response to a manual event that occurs at step 302, where the definition of a column is modified by, for example, assigning a different column type to the relevant column. Steps 304-314 are then performed by the server 12 under direction of the database management algorithms 17, as illustrated in FIG. 4, that form part of the middleware of a software system according to the present invention. At step 304, the procedure 300 searches a TABLE_VERSION_COLUMNS table, such as that provided in FIG. 19B, for occurrences of the modified column with a data type indication not modified to reflect the modification made at step 302. If occurrences of the column within the TABLE_VERSION_COLUMNS table are encountered, a modified flag for the relevant row within a TABLES object is set. At step 308, a determination is then made as to whether any modified flags within a relevant TABLE object have been set. If so, the procedure 300 proceeds to step 310 and a search of an associated PROCEDURES COLUMNS table is performed to locate appropriate columns. At step 312, the PROCEDURES TABLE modified flag is set for located columns. The procedure 300 then ends at step 314. Referring to FIGS. 25 and 26, the steps of procedures of 320 and 340 correspond substantially to those of 300, and do not require further description. Turning to FIGS. 27A and 27B, a procedure 360 of automatically modifying procedures within a database in accordance with modifications made to metadata is described. The procedure 360 corresponds to the step 160 of method 150 shown in FIG. 20, The procedure 360 commences at step 362, and then proceeds to step 364 where a TABLES object (i.e. a TABLES table) is searched for table rows that have a set modified flag. For example, referring to FIG. 5, the TABLES object 56 would be subject to this search. At decision box 366, a determination is made as to whether a INSERT_FLAG is set. If so, a new INSERT procedure for the relevant table is generated at step 368. At decision box 370, a determination is made as to whether a UPDATE_FLAG for a relevant row in the TABLES object is set. If so, a new UPDATE procedure is generated at step 370. At decision box 374, a determination is made as to whether a DELETE_FLAG is set for the relevant row within the TABLES object. If so, a new DELETE procedure is generated for the relevant row at step 376. At step 374, the modified flag for the relevant row within the TABLES object is reset or nulled. Returning now to FIG. 27B, at steps 380, 386, 392 and 398, searches of the PROCEDURES object 86 for records including a set modified flag, and having the procedure types shown in FIG. 27B, are conducted. If such records are located, appropriated new procedures are created, and the relevant modified flags are set to null, at steps 384, 390 and 396. It will however be noted at step 402, in response to the determination in step 398, a procedure is executed that parses the program looking for, and modifying, local variables and their data types according to the COLUMN or COLUMN TYPE and also changing inconsistent data types. FIG. 28 is a diagrammatic representation of a computer system 250 which may host the server 12, or any of the clients 22, 26 or 30 shown in FIG. 1. The computer system 250 includes a processor 252, a main memory 254, a static memory 256 and a mass storage device 258, each of which is typically included within a housing 260, and coupled to a bus 262. External to the housing 260, and also coupled to the bus 262 are a display device 264, such as a Cathode Ray Tube (CRT) or an Liquid Crystal Display (LCD) unit; an alpha-numeric input device 266, such as a keyboard; a cursor control device 268, such as a mouse; a hard copy device 270, such as a printer; and a signal generation device 272, such as a microphone or speaker. The computer system 250 also includes a network interface device 274 which is also coupled to the bus 262. For the purposes of the specification, the term "computer-readable medium" shall be taken to include any media that may be utilized for the storage of a sequence of instructions for execution by the processor 252. In one embodiment of the present invention, the mass storage device 258 comprises a hard-disk drive unit, including a magnetic disk and the main memory 254 may be a Random Access Memory (RAM). The network interface device 274 may be coupled to a network (e.g., via an internet or the Internet), and has the capability to transmit and receive signals via the network. Accordingly, for the purposes of the specification, the term "computer-readable medium" shall be taken to include a magnetic disk included within the mass storage device 258, RAM, or a signal transmitted or received by the network interface device 274. The present invention extends to any computer-readable medium, as defined above, storing a sequence of instructions which, when executed by the processor 252, cause the processor 252 to perform the steps illustrated in any one of the flow charts of FIGS. 20-27B. A program 276 comprising such a sequence of instructions is shown to be resident, wholly or at least partially, in the main memory 254, the mass storage device 258 and the processor 252. The program 276 is also shown to be transmitting to (or from) the network interface device 274. Thus, a method of modifying a database structure by modifying metadata describing that database structure has been described. Although the present invention has been described with reference to specific exemplary embodiments, it will be evident that various modifications and changes may be made to these embodiments without departing from the broader spirit and scope of the invention. Accordingly, the specification and drawings are to be regarded in an illustrative rather than a restrictive sense.
|
Same subclass Same class Consider this |
||||||||||