WO2015016807A1

WO2015016807A1 - Database analysis and validation

Info

Publication number: WO2015016807A1
Application number: PCT/US2013/052461
Authority: WO
Inventors: Geethapriyan HARIKRISHNAN; Sivapratha MARIMUTHU; Jonathan David Gibson
Original assignee: Hewlett-Packard Development Company, L.P.
Priority date: 2013-07-29
Filing date: 2013-07-29
Publication date: 2015-02-05

Abstract

Example embodiments relate to database analysis and validation. In example embodiments, schema information is obtained for a first database object of a first database and a second database of a second database, where the first database and the second database are compatible with different database platforms. At this stage, differences between the first database object and the second database object are determined using the schema information. The differences are then used to generate a difference display that includes a missing element that is included in the first database object but is not included in the second database object and an element that exists in the first database object and the second database object but is modified in the second database object.

Description

DATABASE ANALYSIS AND VALIDATION BACKGROUND

[0001] Typically, a database management tool is configured with functionality for a specific database platform (e.g., ORACLE®, MICROSOFT SQL SERVER®, MYSQL®, SYBASE®, etc.). The database management tool may be licensed individually per user and installed on a client computing device. Further, the database management tool may be specialized to perform a specific function such as comparing, validating, or performing health check-ups for databases. ORACLE® and MYSQL® are registered trademarks of Oracle Corporation, which has its headquarters in Redwood City, California. MICROSOFT SQL SERVER® is a registered trademark of Microsoft Corporation, which has its headquarters in Redmond, Washington. SYBASE ® is a registered trademark of Sybase, Inc., which has its headquarters in Dublin, California.

BRIEF DESCRIPTION OF THE DRAWINGS

[0002] The following detailed description references the drawings, wherein:

[0003] FIG. 1 is a block diagram of an example computing device for providing database analysis and validation;

[0004] FIG. 2 is a block diagram of an example computing device in communication with databases and user devices for providing database analysis and validation;

[0005] FIG. 3 is a flowchart of an example method for execution by a computing device for comparing database objects;

[0006] FIG. 4 is a flowchart of an example method for execution by a computing device for providing database analysis and validation;

[0007] FIG. 5 is a diagram of an example user interface of a web browser in which a stored procedure version comparison is displayed;

[0008] FIG. 6 is a diagram of an example user interface of a web browser in which a database objects comparison is displayed; [0009] FIG. 7 is a diagram of an example user interface of a web browser in which a database structure comparison is displayed; and

[0010] FIG. 8 is a diagram of an example user interface of a web browser in which a stored procedure code comparison is displayed.

DETAILED DESCRIPTION

[0011] As detailed above, prior database management solutions are typically compatible with a single database platform and capable of performing a specific information management function. Accordingly, current solutions are disparate applications from different technology providers (usually the vendors of the database platforms). The management applications operate in non-integrated, disjointed fashion because each provider's information management platform tool has a unique user interface and security model that is not integrated with tools from other providers.

[0012] Example embodiments disclosed herein provide database analysis and validation. For example, in some embodiments, schema information is obtained for a first database object of a first database and a second database of a second database, where the first database and the second database are compatible with different database platforms. At this stage, differences between the first database object and the second database object are determined using the schema information. The differences are then used to generate a difference display that includes a missing element that is included in the first database object but is not included in the second database object and an element that exists in the first database object and the second database object but is modified in the second database object.

[0013] In this manner, example embodiments disclosed herein improve database analysis and validation by providing a web-based, unified management tool. Specifically, the management tool is easily accessible as a web-based application and provides a unified interface for various database analysis and validation functionality. [0014] Referring now to the drawings, FIG. 1 is a block diagram of an example server computing device 100 for providing database analysis and validation. Server computing device 100 may be any computing device accessible by user computing devices, such as user device A 260A and user device N 260N of FIG. 2. In the embodiment of FIG. 1 , server computing device 100 includes a processor 110, an interface 115, and a machine-readable storage medium 120.

[0015] Processor 110 may be one or more central processing units (CPUs), microprocessors, and/or other hardware devices suitable for retrieval and execution of instructions stored in machine-readable storage medium 120. Processor 110 may fetch, decode, and execute instructions 122, 124, 126 to provide database analysis and validation, as described below. As an alternative or in addition to retrieving and executing instructions, processor 110 may include one or more electronic circuits comprising a number of electronic components for performing the functionality of one or more of instructions 122, 124, 126.

[0016] Interface 115 may include a number of electronic components for communicating with databases and user computing devices. For example, interface 115 may be an Ethernet interface, a Universal Serial Bus (USB) interface, an IEEE 1394 (Firewire) interface, an external Serial Advanced Technology Attachment (eSATA) interface, or any other physical connection interface suitable for communication with the user computing device. Alternatively, interface 115 may be a wireless interface, such as a wireless local area network (WLAN) interface or a near-field communication (NFC) interface. In operation, as detailed below, interface 115 may be used to send and receive data, such as schema data, difference data, and user configuration data, to and from a corresponding interface of a user computing device.

[0017] Machine-readable storage medium 120 may be any electronic, magnetic, optical, or other physical storage device that stores executable instructions. Thus, machine-readable storage medium 120 may be, for example, Random Access Memory (RAM), an Electrically-Erasable Programmable Read-Only Memory (EEPROM), a storage drive, an optical disc, and the like. As described in detail below, machine-readable storage medium 120 may be encoded with executable instructions for providing database analysis and validation.

[0018] Schema obtaining instructions 122 may obtain schema information from databases. Specifically, schema obtaining instructions 122 may obtain schema information describing database objects such as database tables, stored procedures, database views, etc. For example, schema information may describe the data columns and corresponding data types of a database table. In another example, schema information may describe parameters, outputs, permissions, and code of a stored procedure. Schema obtaining instructions 122 may obtain the schema information to satisfy a request from a user of the server computing device 100. For example, a user may request a comparison of two database tables from two different databases. In this example, schema obtaining instructions 122 may obtain the schema information from the two databases so that the two database tables may be compared as described below.

[0019] Differences identifying instructions 124 may identify differences between the schemas of database objects from different databases. If the database objects are data tables, the differences may be identified by analyzing and comparing the schemas of the data tables to determine differences between the data columns and key designations. For example, the names of the data columns in the data tables may be compared to determine if any columns have been added, modified, or deleted. In this example, the data types of matching data columns may be compared to determine if any column names have been modified. Further, the data types of matching columns may also be compared to determine if there are any data length mismatches between the matching columns. In another example, data residing in data tables may be compared to identify data records that have been added, modified, or deleted. In yet another example, if the database objects are stored procedures, the differences may be identified by analyzing and comparing the procedure header and source code of the stored procedures. The differences identified in the database objects' schemas may be used to generate a difference display as discussed below. [0020] Differences display generating instructions 126 may generate a difference display based on the differences identified by the differences identifying instructions 124. For example, the difference display may be generated in a tabular format, where changes and modifications between the database objects are color coded. The difference display may also include identifiers and descriptions of the components of the database objects that are being compared. If the database objects are data tables or data views, the difference display may include column names and data types. If the database objects are stored procedures, the difference display may include a comparison of the source code of the stored procedures as discussed below with respect to FIG. 8.

[0021] FIG. 2 is a block diagram of an example server computing device 200 in communication via a network 245 with databases (e.g., database A 250A, database N 250N) and user devices (e.g., user device A 260A, user device N 260N). As illustrated in FIG. 2 and described below, server computing device 200 may communicate with databases and user devices to provide database analysis and validation.

[0022] As illustrated, server computing device 200 may include a number of modules 202-224. Each of the modules may include a series of instructions encoded on a machine-readable storage medium and executable by a processor of the server computing device 200. In addition or as an alternative, each module may include one or more hardware devices including electronic circuitry for implementing the functionality described below.

[0023] As with server computing device 100 of FIG. 1 , server computing device 200 may be a notebook, desktop, tablet, workstation, mobile device, or any other device suitable for executing the functionality described below. As detailed below, server computing device 200 may include a series of modules 202-224 for enabling database analysis and validation.

[0024] Interface module 202 may manage communications with the databases (e.g., database A 250A, database N 250N) and the user devices (e.g., user device A 260A, user device N 260N). Specifically, the interface module 202 may (1) initiate connections with the databases and then send or receive schema data 232 to/from the databases and (2) initiate connections with the user devices and then send or receive difference data 234 to/from the user devices.

[0025] Database module 206 may access and manage data in the databases (e.g., database A 250A, database N 250N). Although the components of database module 206 are described in detail below, additional details regarding an example implementation of module 206 are provided above in connection with instructions 122 of FIG. 1.

[0026] Structured Query Language (SQL) command module 208 may build and execute SQL commands on the databases (e.g., database A 250A, database N 250N). The SQL commands may query, modify, or create database objects in the databases (e.g., database A 250A, database N 2 SON). Further, the SQL commands may be generated for different database providers based on the SQL syntax of the target database (e.g., database A 250A, database N 250N). For example, SQL query commands may be generated to query the database (e.g., database A 250A, database N 250N) for schema data 232 associated with database objects. In another example, SQL alter commands may be generated to synchronize a database object with another. In this example, a SQL alter command may be generated based on differences identified between the database objects as described below.

[0027] Schema module 210 may manage schema data 232 obtained from databases (e.g., database A 250A, database N 250N). For example, schema module 210 may use the schema data to build data structures that are used by the comparison module 212. In another example, schema module 210 may log schema data obtained from a database (e.g., database A 250A, database N 250N). In this example, the schema module 210 may manage a history of schema data 232 for database objects in a database (e.g., database A 250A, database N 250N). The history of schema data 232 may be used to perform snapshot comparisons of the databases as described below. In other words, historical database schemas may be compared to other databases even though no active databases include the historical database schemas. [0028] Comparison module 212 may compare database objects using schema data 232 obtained by database module 206. Although the components of comparison module 212 are described in detail below, additional details regarding an example implementation of module 212 are provided above in connection with instructions 124 of FIG. 1.

[0029] Object comparison module 214 may compare schema data of database objects to identify differences between the database objects. For example, the schema data of data tables from different databases may be compared to identify differences in data columns and data types. In another example, the data in data tables may be compared to identify data records that have been added, modified, or deleted. In yet another example, the schema data of stored procedures may be compared to identify differences in procedure headers and source code. The identified differences may be stored as difference data 234 in storage device 230.

[0030] Object validation module 216 may use schema data to validate a database object. Validation of a database object may include confirming that the database object conforms to user configurations specified by the user using the user interface module 220. For example, objection validation module 210 may confirm that data tables and/or stored procedures conform to user-specified naming standard for a database (e.g., database A 250A, database N 250N). Validation of a database object may also include a validation of readability, a rollback validation, versioning validation, a health check-up, etc. In the case of versioning, the version of stored procedures may be extracted from metadata in the source code that is formatted as defined by a user. Health check-ups may include environmental health check-ups (e.g., CPU utilization bottlenecks, database jobs, tablespace information, etc.), schema health checks (e.g., session statistics, hit/miss ratios, contention/locking, data dictionary information, rollback segments, etc.), and performance health checks (full table scans, cursor/SQL processing, etc.). Validation of the database may be performed automatically at scheduled intervals, where the results of the validation are provided to an administrator of the target database(s). [0031] User interface module 220 may provide a database management application to users of user devices (e.g., user device A 260A, user device N 260N). Although the components of user interface module 220 are described in detail below, additional details regarding an example implementation of module 220 are provided above in connection with instructions 126 of FIG. 1.

[0032] User configuration module 222 may provide user interface elements for receiving configuration parameters from user devices (e.g., user device A 260A, user device N 260N). Configuration parameters may include naming standards for database objects or versioning metadata formatting. Configuration parameters may also include parameters included in a analysis request from user devices (e.g., user device A 260A, user device N 260N). A analysis request may request a comparison of database objects or databases and may include parameters such as the type of comparison or the selection of database objects to be compared. For example, a analysis request may specify that a version comparison should be performed for all stored procedures in two different databases. In another example, a analysis request may specify that a structure comparison should be performed for all data tables in two different databases. Configuration parameters may be stored in storage device 230 as user configuration data 236.

[0033] In some cases, each user is allocated a separate subset of the databases (e.g., database A 250A, database N 250N) as specified by user permissions. In this case, a user may only perform database management on the databases he has access to. Further, access to the databases assigned to a user may be encrypted such that it is associated with the credentials of the user.

[0034] Display module 224 may generate comparison displays for user devices (e.g., user device A 260A, user device N 260N). For example, identified differences between database objects may be included in a difference display for review by a user of a user device (e.g., user device A 260A, user device N 260N). The comparison displays may be rendered in a tabular format such as described below with respect to FIGS. 5-8. In some cases, consolidated reports including multiple types of comparison may be generated. In this case, the consolidated reports may be filterable and sortable by the user. [0035] Storage device 230 may be any hardware storage device for maintaining data accessible to server computing device 200. For example, storage device 230 may include one or more hard disk drives, solid state drives, tape drives, and/or any other storage devices. The storage devices may be located in server computing device 200 and/or in another device in communication with server computing device 200. As detailed above, storage device 230 may maintain schema data 232, difference data 234, and user configuration data 236.

[0036] Databases (e.g., database A 250A, database N 250N) may be any database accessible to server computing device 200 over the network 245 that is suitable for providing database content. Databases (e.g., database A 250A, database N 250N) may provide database content as data tables, data views, stored procedures, indexes, sequences, etc.

[0037] User devices (e.g., user device A 260A, user device N 260N) may be local, networked, or cloud user devices accessible to server computing device 200 via the network 245. Each user device (e.g., user device A 260A, user device N 260N) may be, for example, smartphone, tablet, laptop computer, desktop computer, or any other user device suitable for accessing a database management application provided by server computing device 200.

[0038] FIG. 3 is a flowchart of an example method 300 for execution by a server computing device 100 for providing database analysis and validation. Although execution of method 300 is described below with reference to server computing device 100 of FIG. 1 , other suitable devices for execution of method 300 may be used, such as server computing device 200 of FIG. 2. Method 300 may be implemented in the form of executable instructions stored on a machine- readable storage medium, such as storage medium 120, and/or in the form of electronic circuitry.

[0039] Method 300 may start in block 305 and continue to block 310, where server computing device 100 obtains a first scheme for a first database object of a first database. The first database object may be a data table, data view, or stored procedure. The first schema may describe the structure of the first database object. In block 315, a second schema for a second database object of a second database may be obtained. Similarly, the second schema may describe the structure of the second database object. In some cases, the first database and the second database may be compatible with different database platforms.

[0040] Next, in block 320, the first schema and the second schema may be analyzed to identify differences between the first database object and the second database objects. For examples, differences between the data columns and data types of data tables may be identified. In block 325, a difference display including the differences identified in block 320 may be generated. For example, the difference display may be as described below with respect to FIGS. 5-8. Method 300 may then continue to block 330, where method 300 may stop.

[0041] FIG. 4 is a flowchart of an example method 400 for execution by a server computing device 100 for database analysis and validation. Although execution of method 400 is described below with reference to server computing device 100 of FIG. 1 , other suitable devices for execution of method 400 may be used, such as server computing device 200 of FIG. 2. Method 400 may be implemented in the form of executable instructions stored on a machine-readable storage medium, such as storage medium 120, and/or in the form of electronic circuitry.

[0042] Method 400 may start in block 405 and continue to block 410, where server computing device 100 may display a user interface for database management. For example, the user interface may be presented as a web application that is accessible to user devices. After user credentials are provided, the web application may provide access to the database management functionality. In block 415, an analysis request is received from a user via the user interface. The analysis request may specify the type of analysis and the set of database objects that should be analyzed.

[0043] In block 420, schema information is obtained from the database(s). In the case of data tables, the schema information may describe the structure of the data tables in the database(s). In the case of stored procedures, the schema information may include procedure headers and source code for the stored procedures in the database(s). In block 425, the schema information is analyzed to generate output. For example, a difference display may be generated that includes identified differences between database objects. In another example, a validation display may be generated that shows the results of a validation analysis of database objects of a database.

[0044] In block 430, the output is provided to the user. For example, the output may be display via the web application. In another example, the output may be provided in a tabular format such as a spreadsheet file. In yet another example, the output may be sent via electronic mail to the user. Method 400 may then continue to block 435, where method 400 may stop.

[0045] FIG. 5 is a diagram of an example user interface 500 of a web application in which a difference display for a stored procedure version comparison is shown. As depicted, the user interface 500 includes a legend 510 describing the rows of the differences 550, 555 shown in tabular format. The user interface 500 also includes an export button 515, an email button 520, and a back button 525. The export button 515 may be selected by the user to export the differences 550, 555 to a spreadsheet file. The email button 520 may be selected by the user to email the differences 550, 555 to a target user. The back button 525 may be selected by the user to navigate to the main menu of the web application. The user interface 500 includes a scroll bar for 560 for browsing the list of differences 550, 555.

[0046] The columns 530, 535, 540, 545 of the differences 550, 555 are shown. The object type 530 of each of the differences 550, 555 is shown as PROCEDURE, which shows that the differences are related to stored procedures. The object name 535 of the columns 530, 535, 540, 545 shows the name of each of the stored procedures. The first database column 540 and the second database column 545 show the versions of the databases being compared in the user interface 500. In this example, difference 550 shows that the stored procedure named SP_NAME_1 has the same version in database 1 ("DB ) and database 2 ("DB 2"), and difference 555 shows that the stored procedure named SP NAME 2 is version 1 in DB 1 and version 5 in DB 2. [0047] FIG. 6 is a diagram of an example user interface 600 of a web application in which a difference display for a stored procedure comparison is shown. As depicted, the user interface 600 includes a legend 610 describing the rows of the differences 650, 655 shown in tabular format. The user interface 600 also includes an export button 615, an email button 620, and a back button 625. The columns 630, 635, 640 of the differences 650, 655 are shown. The object type 630 of each of the differences 650, 655 is shown as PROCEDURE, which shows that the differences are related to stored procedures. The user interface 600 includes a scroll bar for 660 for browsing the list of differences 650, 655.

[0048] The first database column 635 and the second database column 640 show the stored procedure names of the databases being compared in the user interface 600. In this example, difference 650 shows that both database 1 and database 2 have a stored procedure named SP_SAVE_ENTITY, and difference 655 shows that database 1 has a stored procedure named SP_RETRIEVE_ENTITY that is not included in database 2.

[0049] FIG. 7 is a diagram of an example user interface 700 of a web application in which a difference display for a data table comparison is shown. As depicted, the user interface 700 includes a legend 710, 712 describing the rows of the differences 750, 755, 757 shown in tabular format. The legend 710, 712 may show that the differences 750, 755, 757 are rendered in different colors based on the type of difference. The user interface 700 also includes an export button 715, an email button 720, and a back button 725. The columns 735, 740 of the differences 750, 755, 760 are shown. The user interface 700 includes a scroll bar for 760 for browsing the list of differences 750, 755, 757.

[0050] The first database column 735 and the second database column 740 show the data table names of the databases being compared in the user interface 700. In this example, difference 750 shows that both database 1 and database 2 have a data table named TBL_ENTITY, difference 755 shows that database 1 has a data table named TBL_ENTITY_ORDERS that is not included in database 2, and difference 757 shows that both database 1 and database 2 have a data table named TBL_RECORD_DETAILS but that the table has been modified in database 2.

[0051] FIG. 8 is a diagram of an example user interface 800 of a web application in which a difference display for a stored procedure source code comparison is shown. As depicted, the user interface 800 includes a legend 810, 812 describing the rows of the differences 850, 855, 859 shown in tabular format. The user interface 800 also includes an export button 815, an email button 820, and a back button 825. The columns 835, 840 of the differences 850, 855, 859 are shown. The user interface 800 also shows the name of the stored procedure 830 being compared in the difference display. The user interface 800 includes a scroll bar for 860 for browsing the list of differences 850, 855, 859.

[0052] The first database column 835 and the second database column 840 show the lines of source code of the stored procedure being compared between database 1 and database 2. In this example, difference 850 shows that both database 1 and database 2 have the same procedure header, difference 855 shows that database 1 returns a VARCHAR2 data type and database 2 returns a BLOB data type, and difference 859 shows that both database 1 includes a line of source code that is not included in database 2. The differences 850, 855, 859 also show source code comments that are formatted to include versioning information. The version of the stored procedure in each of the databases may be extracted from the source code comments.

[0053] FIGS. 5-8 show user interfaces for comparing two different databases; however, any number of databases may be compared as described above. In this case, an additional column may be added to the difference displays as shown for each of the additional databases. Further, a consolidated difference display may be generated to include two or more of the difference displays shown in FIGS. 5-8.

[0054] The foregoing disclosure describes a number of example embodiments for providing database analysis and validation by a server computing device. In this manner, the embodiments disclosed herein enable analysis and validations for databases by cross-platform management application that generates unified displays of modifications and omissions between database objects.

Claims

CLAIMS We claim:

1. A system for providing database analysis and validation, the system comprising:

a processor to:

perform a first query to obtain a first schema from a first database, wherein the first schema describes a first database object of the first database;

perform a second query to obtain a second schema from a second database, wherein the second schema describes a second database object of the second database, and wherein the first database and the second database are compatible with different database platforms;

determine a plurality of differences between the first schema and the second schema; and

generate a difference display based on the plurality of differences, wherein the difference display comprises a missing element that is included in the first schema but is not included in the second schema and an element that exists in the first schema and the second schema but is modified in the second schema.

2. The system of claim 1 , wherein the first database object is a first stored procedure and the second database object is a second stored procedure, and wherein the plurality of differences comprises a plurality of source code differences, and wherein the processor is further to:

obtain first header comments from the first stored procedure and second header comments from the second stored procedure; and

extract a first version number from the first header comments and a second version number from the second header comments, wherein the difference display includes the first version number and the second version number.

3. The system of claim 1 , wherein the processor is further to:

obtain user-defined naming standards from the first database; and identify non-conforming database objects in the first database that fail to satisfy the user-defined naming standards.

4. The system of claim 1 , wherein the missing element and the element are rendered in the difference display in different colors.

5. The system of claim 1 , wherein the first database object is a first data table and the second database object is a second data table, and wherein the plurality of differences comprises a plurality of column name differences, a plurality of data type differences, and a plurality of data length differences.

6. The system of claim 5, wherein the processor is further to:

generate an alter command based on the plurality of differences to modify the first data table to match a structure of the second data table.

7. A method for providing database analysis and validation, the method comprising:

performing a first query to obtain a first schema from a first database, wherein the first schema describes a first database object of the first database; performing a second query to obtain a second schema from a second database, wherein the second schema describes a second database object of the second database, and wherein the first database and the second database are compatible with different database platforms;

determining a plurality of differences between the first schema and the second schema;

generating a difference display based on the plurality of differences, wherein the difference display comprises a missing element that is included in the first schema but is not included in the second schema and an element that exists in the first schema and the second schema but is modified in the second schema; and generating an alter command based on the plurality of differences to modify the first database object to match the second schema of the second database object.

8. The method of claim 7, wherein the first database object is a first stored procedure and the second database object is a second stored procedure, and wherein the plurality of differences comprises a plurality of source code differences, and wherein the method further comprises:

obtaining first header comments from the first stored procedure and second header comments from the second stored procedure; and

extracting a first version number from the first header comments and a second version number from the second header comments, wherein the difference display includes the first version number and the second version number.

9. The method of claim 7, further comprising:

obtaining user-defined naming standards from the first database; and identifying non-conforming database objects in the first database that fail to satisfy the user-defined naming standards.

10. The method of claim 7, wherein the missing element and the element are rendered in the difference display in different colors.

11. The method of claim 7, wherein the first database object is a first data table and the second database object is a second data table, and wherein the plurality of differences comprises a plurality of column name differences, a plurality of data type differences, and a plurality of data length differences.

12. A non-transitory machine-readable storage medium encoded with instructions executable by a processor for providing database analysis and validation, the machine-readable storage medium comprising instructions to: perform a first query to obtain a first schema from a first database, wherein the first schema describes a first database object of the first database; perform a second query to obtain a second schema from a second database, wherein the second schema describes a second database object of the second database, and wherein the first database and the second database are compatible with different database platforms;

generate a difference display based on the plurality of differences, wherein the difference display comprises a missing element that is included in the first schema but is not included in the second schema and an element that exists in the first schema and the second schema but is modified in the second schema, wherein the missing element and the element are rendered in the difference display in different colors.

13. The storage medium of claim 12, wherein the first database object is a first stored procedure and the second database object is a second stored procedure, and wherein the plurality of differences comprises a plurality of source code differences, and wherein the machine-readable storage medium further comprises instructions to:

14. The storage medium of claim 12, further comprising instructions to:

15. The storage medium of claim 12, wherein the first database object is a first data table and the second database object is a second data table, and wherein the plurality of differences comprises a plurality of data record differences, and wherein the machine-readable storage medium further comprises instructions to:

generate an sql command based on the plurality of differences to modify the first data table to match a plurality of data records of the second data table.