Automatically generating web forms from database schema

• US 7,624,114 B2
• Filed: 06/05/2006
• Issued: 11/24/2009
• Est. Priority Date: 06/05/2006
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Guthrie, Scott David, Paulus, Polita Mila, Alam, Kashif, Goldfarb, Brian Matthew, Millington, Bradley D., Niyogi, Shanku Shivabrata, Gibbs, Matthew E., Sanabria, Andres
• Primary Examiner(s): Wu; Yicun
• Assistant Examiner(s): Zhao; Yu
• Application Number:
  US11/446,630
• Publication Number:
  US 20070282869A1
• Time in Patent Office: 1,268 Days
• Field of Search: None
• US Class Current: 1/1
• CPC Class Codes: G06F 16/972 Access to data in other repository systems, e.g. legacy data or dynamic Web page generation
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure

Abstract

The present invention extends to methods, systems, and computer program products for creating Web forms with automatically generated links. A database mapping for a database is accessed. One or more dynamic queries for querying a database are automatically formulated. The dynamic queries are included in an automatically generated Web site for the database in accordance with the database mapping. User-selected links are also included for executed the dynamic queries. Selection of a link causes a corresponding dynamic query to execute. Relationships between tables in a database can be inferred even when a relationship is not expressly described in schema describing the database. Dynamic queries can be any of sorting, pagination, addition, deletion, and modification queries.

Claims

1. At a computer system including a processor and system memory, a method for generating a Web based user-interface for interacting with a database, the method comprising:

an act of accessing a schema that defines a plurality tables in the database, the schema also defining relationships between fields in the plurality of tables of the database, definition of field relationships by the schema including defining a foreign key relationship from a first table to a second table of the database indicative of a key field in the first table identifying records in the second table;

an act of converting the schema, including the foreign key relationship, into source code, the source code including classes and subclasses that represent relationships between the plurality of tables and fields defined in accordance with the schema;

annotating the source code with a metadata annotation representing the foreign key relationship from the first table to the second table, the metadata annotation indicating the basis for creation of any classes or subclasses based on the foreign key relationship indicative of a key field in the first table identifying records in the second table;

an act of generating executable code from the source code, including;

compiling classes and subclasses of the source code into executable code of a dynamic link library, the executable code obscuring any indication of the relationships between the plurality of tables and fields in the database in the source code; and

retaining the metadata annotations from the source code in the executable code of the dynamic link library, including a metadata annotation representing the foreign key relationship indicative of a key field in the first table identifying records in the second table;

an act of identifying the metadata annotations from within the executable code of the dynamic link library, including the metadata annotation representing the foreign key relationship indicative of a key field in the first table identifying records in the second table, for use in creating a database mapping for the database;

an act of the processor creating a database mapping for the database from the metadata annotations, the database mapping describing the configuration of the database such that the database mapping is based on the schema and retains an indication of the foreign key relationship indicative of a key field in the first table identifying records in the second table in a format processable by a Web site generator;

an act of the Web site generator inferring, from the existence of the foreign key relationship from the first table to the second table, that a relationship from the second table back to the first table is also relevant to navigating Web based forms that present data from the database, even though a relationship from the second table to the first table is not expressly described in the schema, the inferred relationship for use in generating additional functionality in a Web site for the database permitting traversal of a navigable link from the second table back to the first table; and

an act of the Web site generator generating a Web site for the database in accordance with the database mapping, the Web site including at least one navigable link from the second table back to the first table based on the inferred relationship, the at least one navigable link implementing the inferred relationship from the second table back to the first table, the Web site configured to provide a plurality of navigable linked Web based forms for interacting with the tables and fields of the database, including a first Web based form for interacting with data from the second table, the first Web based form configured with a navigable link that can be selected to automatically formulate a dynamic query for querying database records from the first table in the context of a row from the second table.

2. The method as recited in claim 1, wherein the act of indentifying metadata annotations comprises an act of using reflection to identifying metadata annotations in the executable code.

3. The method as recited in claim 1, wherein the act of inferring, from the existence of the foreign key relationship, that a relationship from the second table back to the first second table is also relevant comprising an act of inferring that values for a field in the second table represented by the foreign key in the first table can be included in a plurality of rows in the first table.

4. The method as recited in claim 1, wherein the act of automatically generating a Web site for the database in accordance with the database mapping comprises an act including the dynamic query in the automatically generated Web site.

5. The method as recited in claim 1, wherein the act of automatically generating a Web site for the database in accordance with the database mapping comprises an act of automatically generating a Web site that includes links for executing dynamic queries to the database to perform at least of one sorting records from the first table and paging through records from the first table.

6. At a computer system including a processor and system memory, a method for generating a Web based user-interface for interacting with a database, the method comprising:

an act of accessing a schema that defines a plurality tables in the database, the schema also defining relationships between fields in the plurality of tables of the database, definition of field relationships by the schema including defining a foreign key relationship from a first table to a second table of the database indicative of a key field in the first table identifying records in the second table;

an act of converting the schema, including the foreign key relationship, into source code, the source code including classes and subclasses that represent relationships between the plurality of tables and fields defined in accordance with the schema;

annotating the source code with a metadata annotation representing the foreign key relationship from the first table to the second table, the metadata annotation indicating the basis for creation of any classes or subclasses based on the foreign key relationship indicative of a key field in the first table identifying records in the second table;

an act of generating executable code from the source code, including;

compiling classes and subclasses of the source code into executable code of a dynamic link library, the executable code obscuring any indication of the relationships between the plurality of tables and fields in the database in the source code; and

retaining the metadata annotations from the source code in the executable code of the dynamic link library, including a metadata annotation representing the foreign key relationship indicative of a key field in the first table identifying records in the second table;

an act of identifying the metadata annotations from within the executable code of the dynamic link library, including the metadata annotation representing the foreign key relationship indicative of a key field in the first table identifying records in the second table, for use in creating a database mapping for the database;

an act of the processor creating a database mapping for the database from the identified metadata annotations the database mapping describing the configuration of the database such that the database mapping is based on the schema and retains an indication of the foreign key relationship indicative of a key field in the first table identifying records in the second table in a format processable by a Web site generator;

an act of automatically formulating a dynamic query configured to query a database table included in the database, the dynamic query being executable in response to a user initiated command to change the arrangement of records from the database table within a Web based form, the dynamic query configured to request records from the first table that are to be presented in the Web based form in the order they are to be presented such that the entire database table need not be queried, the dynamic query for implementing one of;

a pagination query, an addition query, a deletion query, and a modification query; and

an act of automatically generating a Web site for the database in accordance with the database mapping, the Web site including a link for executing the automatically formulated dynamic query so as to allow users to query the database to automatically change the arrangement of records from the database presented at the Web site, the Web site including at least one navigable link from the second table back to the first table, the at least one navigable link implementing the inferred relationship from the second table back to the first table based on the metadata annotation representing the foreign key relationship indicative of a key field in the first table identifying records in the second table.

7. The method as recited in claim 6, wherein the act of identifying metadata annotations comprises an act of using reflection to identify metadata annotations in the executable code.

8. The method as recited in claim 6, wherein the act of automatically formulating a dynamic query configured to query a database table comprises an act formulating a dynamic query for one or more of sorting, deleting, adding, and modifying records in the database table.

9. The method as recited in claim 8, wherein the act of formulating a dynamic query for sorting records in the database table comprises an act formulating a dynamic query that can toggle between sorting records of the database table in ascending and descending order based on values in one of fields of the records.

10. The method as recited in claim 6, wherein the act of automatically formulating a dynamic query configured to query a database table comprises an act formulating a dynamic query for paging through records in the database table.

11. The method as recited in claim 6, wherein the act of automatically generating a Web site for the database in accordance with the database mapping comprises an act of including a user-selectable link that can be selected to execute a sorting query.

12. The method as recited in claim 6, wherein the act of automatically generating a Web site for the database in accordance with the database mapping comprises an act of including a user-selectable link that can be selected to execute a query to records form a first table in the context of a row form a second table.

13. A computer system, the computer system comprising:

one or more processors;

system memory;

one or more computer storage media having stored thereon computer-executable instructions representing in an object relational mapper, a compiler, and a Web site generator, wherein the object relational mapper is configured to;

access a schema that defines a plurality tables in the database, the schema also defining relationships between fields in the plurality of tables of the database, definition of field relationships by the schema including defining a foreign key relationship from a first table to a second table of the database indicative of a key field in the first table identifying records in the second table;

convert the schema, including the foreign key relationship, into source code the source code including classes and subclasses that represent relationships between the plurality of tables and fields defined in accordance with the schema; and

annotate the source code with a metadata annotation representing the foreign key relationship from the first table to the second table, the metadata annotation indicating the basis for creation of any classes or subclasses based on the foreign key relationship indicative of a key field in the first table identifying records in the second table;

wherein the compiler is configured to;

generate executable code from the source code, including;

compiling classes and subclasses of the source code into executable code of a dynamic link library, the executable code obscuring any indication of the relationships between the plurality of tables and fields in the database in the source code; and

retaining the metadata annotations from the source code in the executable code of the dynamic link library, including a metadata annotation representing the foreign key relationship indicative of a key field in the first table identifying records in the second table;

wherein the processor;

identifies the metadata annotations from within the executable code of the dynamic link library, including the metadata annotation representing the foreign key relationship indicative of a key field in the first table identifying records in the second table, for use in creating a database mapping for the database; and

creates a database mapping for the database from the metadata annotations, the database mapping describing the configuration of the database such that the database mapping is based on the schema and retains an indication of the foreign key relationship indicative of a key field in the first table identifying records in the second table in a format processable by the Web site generator; and

wherein the Web site generator is configured to;

infer, from the existence of the foreign key relationship from the first table to the second table that a relationship from the second table back to the first table is also relevant to navigating Web based forms that present data from the database, even though a relationship from the second table to the first table is not expressly described in the schema, the inferred relationship for use in generating additional functionality in a Web site for the database permitting traversal of a navigable link from the second table back to the first table; and

generate a Web site for the database in accordance with the database mapping, the Web site including at least one navigable link from the second table back to the first table based on the inferred relationship, the at least one navigable link implementing the inferred relationship from the second table back to the first the Web site configured to provide a plurality of navigable linked Web based forms for interacting with the tables and fields of the database, including a first Web based form for interacting with rows of data from the second table, the first Web based form configured with a navigable link back to a second Web based form that utilizes the inferred relationship to provide a view back to the first table in the context of a row from the second table, wherein selecting the navigable link back to the second Web based form issues a dynamic query back to the database to change the arrangement of records that are displayed at the Web site so as to provide the view back to the first table in the context of a row from the second table, the dynamic query for implementing one of;

a pagination query, an addition query, a deletion query, and a modification query.