Method for specifying a database import/export operation through a graphical user interface

[0002] 1. Field of the Invention

[0003] The present invention relates to database systems, and more particularly, to a technique of specifying a database operation via a graphical user interface.

[0004] 2. Description of the Prior Art

[0005] A database is an organized collection of data that can be readily searched, sorted and updated. The database is often logically arranged as tables identified by table names, with columns identified by column names. Access to the database is typically managed by a software interface known as a database management system (DBMS). A processor requiring access to the database issues a command to the DBMS. In accordance with the command, the DBMS modifies the database or returns a result to the processor.

[0006] Structured query language (SQL) is a standard protocol for accessing databases. SQL can be used to formulate a database interface statement for subsequently executing a database transaction. SQL includes features that allow users to query, update, insert data into, and delete data from, a database. Commands to a DBMS are made in the form of an SQL transaction.

[0007] Many users that require access to information from a database are not familiar with SQL. Some commercial software applications, for example most spreadsheet packages, include mechanisms to retrieve data from a data source. However, these mechanisms are typically static in nature, as they require formulation of fixed queries, placement of data in fixed spreadsheet locations and fixed formatting of data. Even then, complex database queries usually require a user to formulate the query in a pseudo-programming instruction, or through an alteration of software code.

[0008] It is an object of the present invention to provide a method that allows a user of a computer system to specify a data import-or export operation without requiring the user's knowledge of, or manipulation of, a query language or software code.

[0009] It is another object of the present invention to provide such a method in which the user specifies the request via a graphical user interface.

[0010] It is a further object of the present invention to provide such a method that allows the user to revise the requested operation.

[0011] These and other objects of the present invention are achieved by a computer-implemented method for specifying an operation via a graphical interface, comprising the steps of examining an image to distinguish a first bordered region within the image, examining the image to distinguish a second bordered region within the image, and determining a functional attribute of the second bordered region based on a position of the second bordered region relative to the first bordered region.

[0012] FIG. 1 is a diagram of a template from-which a table can be constructed in accordance with the present invention;

[0013] FIG. 2 is a diagram of a table based on the template in FIG. 1, and created by an end-user, as it would appear before sending a query to a database;

[0014] FIG. 3 is a diagram of the table in FIG. 2, as it would appear after receiving a result from a database;

[0015] FIG. 4 is a diagram of the table in FIG. 3, further edited by an end-user;

[0016] FIG. 5 is a flowchart of a method for specifying a database import/export operation through a graphical user interface in accordance with the present invention;

[0017] FIG. 6 is a flowchart of a method for validating a graphical table in accordance with the present invention;

[0018] FIG. 7 is a flowchart of a method for mapping data from a table into a buffer in accordance with the present invention;

[0019] FIG. 8 is a flowchart of a method for formatting a database command in accordance with the present invention;

[0020] FIG. 9 is a flowchart of a method for transferring data from a buffer.into a graphical table in accordance with the resent invention; and

[0021] FIG. 10 is a block diagram of a computer system particularly suited for employment of the present invention.

[0022] In accordance with the present invention, a method is implemented by a set of algorithms that interpret user-manipulated graphic and textual elements. Through a graphical user interface, a user demarcates two or more bordered regions of an image. The image is examined to distinguish each bordered region, and a functional attribute is associated with each bordered region based on a position of a given bordered region relative to a position of another bordered region. Thereafter, a database command is generated based on a functional attribute associated with the given bordered region.

[0023] A data structure referred to as a schema specifies a manner in which certain information shall be treated or interpreted. A region schema defines the names and the relative locations of each bordered region. A parameter schema specifies a manner in which data from a particular bordered region shall be formatted into a database command. The parameter schema also specifies a manner in which data shall be formatted .for placement into a particular bordered region. Collectively, these schemas set forth a functional attribute of a bordered region on the basis of the relative position of the region within the image.

[0024] FIG. 1 is a diagram of a template, based on a region schema, from which a table can be constructed in accordance with the present invention. The functional attributes and spatial relationships of the bordered regions are specified in the region schema. This template and the corresponding region schema represent an exemplary implementation for use with the present invention.

[0025] A table prepared in accordance with the exemplary region schema includes nine bordered regions, each having a functional attribute, namely a Title, a Table Descriptor, an Item Header₁, an Item Header₂, a Data Header, a Data Key, an Item descriptor₁, an Item Descriptor₂, and Data. The attribute associated with each region is described below.

[0026] A functional attribute of a given region is based on that region's position relative to the position of another region. For example, the region at the top of the template is recognized as having the functional attribute of “Title”, and a region having the functional attribute of “Item Descriptor₁” is below a bordered region having the functional attribute of “Item Header₁.”

[0027] Title: The Title provides the name of the table. A table has one Title region.

[0028] Table Descriptor: The Table Descriptor specifies a number of columns of Data Keys, and a columnar offset to a first column of data. It can also specify other properties of the table or operations that can be performed on the table. For example, it can indicate features such as whether to (1) recognize a double click of a mouse button to launch a graph, (2) allow the user to insert rows or columns, and (3) allow an automatic expansion of the table upon an import of data. A table has one Table Descriptor region.

[0029] Data Key: The Data Key specifies one or more characteristics associated with a set of one or more contiguous Data regions that contain the same type of data. For example, the characteristics can include, a position or a columnar offset to the set of Data regions, a columnar width of the set, behavioral aspects (e.g., import (I), export (X), protected (P), or unprotected(U)) of data in the set, and a source of the data in the Data regions. The contiguous data regions specified by the Data Key are in a single row, however, a row can contain more than one type of data, and each type must have a corresponding Data Key.

[0030] Item Header₁: The Item Header₁defines a category of one or more Item descriptor₁regions in the table. Exemplary Item Headers₁include “Line of Business”, “Product Line”, and “Sub-Product.”

[0031] Item Header₂: The Item Header₂defines a category of one or more Item Descriptor₂regions in the table.

[0032] Data Header: The Data Header specifies a parameter or dimension of one or more Data regions in the table. For example, a Data Header can specify that an underlying Data region is part of a time series, i.e. “1995”, “1996”, “1997”, etc. A table can include one or more Data Header regions, that is one Data Header region for each column of data.

[0033] Item descriptor₁: The Item Descriptor₁describes a type of data item to be accessed in a database. For example, an Item Descriptor₁can be a broad descriptor of a product such as “Gloves” or “Bats.” A table can include one or more Item Descriptor₁regions, and a single Item Descriptor₁region can be associated with one or more contiguous rows of data.

[0034] Item Descriptor₂: The Item Descriptor₂, in conjunction with an Item descriptor₁, further describes a type of data item to be accessed in the database. Exemplary Item Descriptors₂include, “Forecast Sales”, “Actual Sales”, “Units In Inventory.” A table can include one Item Descriptor₂region per row, but one or more Item Descriptor₂regions may be associated with a single Item Descriptor₁region.

[0035] Data: The Data is the datum or data that is sent to, or retrieved from a database. In some cases, data is entered into a Data region manually by the end-user. In other cases, it is entered automatically as a result of a database access. A Data region can be blank, such as in a case where data has not yet been retrieved from the database. A table can include one or more Data regions per row.

[0036] FIG. 2 is a diagram of a table based on the template shown in FIG. 1, and created by a user who wishes to access a database. The user defines bordered regions in an image by drawing lines with a graphical user interface. The graphical user interface can be any software application that enables the user to place lines and text into the image, including a conventional word processor, spreadsheet, or drawing package. For example, in a spreadsheet application a user can demarcate a bordered region by selecting one or more adjacent cells and highlighting a perimeter formed by the sides of the cells.

[0037] In the table shown in FIG. 2, the user is permitted to add columns and rows of Data, and corresponding Data Keys, Item Descriptors₁, Item Descriptors₂, Data Headers and Data regions, thus increasing the dimension of the table beyond that illustrated by the template shown in FIG. 1. The table shown in FIG. 2 has a Title region 22 indicating “Projected Bat and Glove Sales”. An Item Header₁region 26 defines a category of “Product Line” for underlying Item Descriptor₁regions 34aand 34b. An Item Header₂region 28 defines a category of “Data Category” for underlying Item Descriptor₂regions 36athrough 36f. Data Header regions 30a, 30band 30cindicating “1999”, “2000”, and “2001”, respectively, are associated with three columns of Data regions organized into six Data rows 38athrough 38f.

[0038] Note that the user is permitted to associate an Item Descriptor₁region with one or more contiguous rows of data by drawing the Item Descriptor₁region adjacent to the rows with which it is associated. Accordingly, Item Descriptor₁region 34aindicating “Gloves” is associated with rows 38a, 38band 38c, which are further identified in Item Descriptor₂region 36aas “Current Sales Forecast”, Item Descriptor₂region 36bas “Projected Sales Gain”, and Item Descriptor₂region 36cas “Planned Inventory”. Likewise, Item Descriptor₁region 34bindicating “Bats” is associated with rows 38d, 38eand 38f, which are further identified in Item Descriptor₂region 36das “Discounted Units”, Item Descriptor₂region 36eas “Planned Inventory” and Item Descriptor₂region 36fas “Projected Sales”.

[0039] A Table Descriptor region 24, as illustrated in FIG. 2, contains the text “A:1,3”. The “A” is an arbitrary symbol, shown here as an example to indicate a property or operation that can be performed on the table as discussed above in the paragraph describing the Table Descriptor in the context of FIG. 1. The “1” indicates that the table contains 1 Data Key column, and the “3” indicates that the first column of Data rows 38athrough 38fis an offset of 3 columns from the end of the Data Key region. Any desired property or operation can be represented in the Table Descriptor by any convenient symbol. The manner in which this information is represented is not critical to the invention.

[0040] The table includes six Data Key regions 32athrough 32f, each corresponding to one of the six Data rows 38athrough 38f. As stated above in the paragraph describing the Data Key in the context of FIG. 1, a Data Key specifies characteristics associated with a set of one or more contiguous Data regions. The content of a Data Key region can be entered manually, or it can be generated and entered automatically after examining the content of other regions in the table. By way of example, Data Key 32acontains a string of text, “PS:IX:3,1:Gloves23:Y:T”. This text indicates that the data in Data row 38ais recognized by a database as Projected Sales (PS), the data can be Imported and Exported (IX), the corresponding Data region in row 38astarts at column “3” and is offset by“1” column from the end of Item Descriptor₂region 36a, a database item key is “Gloves23”, and the data is Yearly (Y) Total (T) sales. In FIG. 2, Data Key regions 32bthrough 32fcontain strings of text similar to that of Data Key region 32a, all of which are presented here only for the purpose of example. In implementation, any desired characteristic can be represented by any convenient symbol.

[0041] Given an image that includes a table such as that shown in FIG. 2, the present invention operates by examining the image, distinguishing each bordered region described above, and determining a functional attribute for each of these regions. The invention proceeds by reading data from within the regions, and formatting the data into a database command based on the functional attributes. The invention then issues the database command to a database. Any database command can be created, including a query command, an update command, an insert command, and a delete command.

[0042] FIG. 3 is a diagram of the table in FIG. 2, as it would appear after receiving a result of a query from a database. The invention has written the result into the Data regions in rows 38athrough 38f.

[0043] FIG. 4 is a diagram of the table in FIG. 3, further edited by a user. In particular, the user has modified the image by changing the content of the Data regions in row 38a, and by adding a Data Key region 32g, an Item Descriptor₁region 34c, an Item Descriptor₂region 36g, and Data regions in a row 38g. Each of these added regions has a functional attribute derived from the region's position in the image.

[0044] In addition to changing the content of the Data regions in row 38a, the user also added content to each of the new regions in row 38g. Note that Item Descriptor₂region 36gindicates “Discounted Units” and that the user has written data to the Data regions in row 38g. Data Key region 32gindicates that the data in row 38gis for Export (X) to a database. Accordingly, the present invention will apply the data from rows 38aand 38gto update the database. Thus, as shown in FIGS. 3 and 4, an import/export operation can include a query, a data retrieval, a data update, etc., for an individual cell of data as well as for multiple units of data.

[0045] FIG. 5 is a flowchart of a method for specifying a database import/export operation through a graphical user interface in accordance with the present invention. The method begins with step 105.

[0046] In step 105, via a graphical user interface, a user creates an image, which includes two or more bordered regions. A functional attribute is associated with each bordered region based on its position in.the image relative to another bordered region. The bordered regions are arranged in accordance with a region schema, which defines the names and the relative locations of the bordered regions. Preferably, the bordered regions are organized into a table. An image can include more than one table. The method then advances to step 110.

[0047] In step 110, the method validates a table to determine whether the table conforms to the requirements set forth in the region schema. This step is described below in greater detail in association with FIG. 6. The method then advances to step 115.

[0048] In step 115, data from the bordered regions is mapped into a buffer for subsequent processing. This step is described below in greater detail in association with FIG. 7. The method then advances to step 120.

[0049] In step 120, the method reads data from the buffer indicated in step 115, and formats the data into a database command. This step is described below in greater detail in association with FIG. 8. The method then advances to step 125.

[0050] In step 125, the database command from step 120 is issued to a database. The method then advances to step 130.

[0051] In step 130, the method receives a result of the database command and loads the result into a region in the image. This step is described below in greater detail in association with FIG. 9. The method then advances to step 135.

[0052] In step 135, the method for specifying a database import/export operation through a graphical user interface ends.

[0053] FIG. 6 is a flowchart of a method for validating a graphical table in accordance with the present invention. Generally, this method determines whether an image includes a properly structured table. That is, it determines whether the image includes a correct quantity of properly located bordered regions. FIG. 6 is a detailed illustration of the operation of step 110 of FIG. 5. The method illustrated by FIG. 6 begins with step 205.

[0054] In step 205, the method examines the image to determine whether it includes a bordered region. If the image does not include a bordered region, then the method branches to step 255. If the image does include a bordered region, then the method advances to step 210.

[0055] In step 210, the method determines the spatial extent of a current region, i.e., the bordered region being examined. The method then advances to step 215.

[0056] In step 215, the method determines the spatial extent of regions adjacent to the current region. The method then advances to step 220.

[0057] In step 220, the method associates the current region with a region set forth in a region schema. The region schema defines names and relative locations of bordered regions within a table. The method then advances to step 225.

[0058] In step 225, the method determines whether the image contains an unexamined region. If the image does contain an unexamined region, then the method loops back to step 210. If the image does not contain an unexamined region, then the method advances to step 235.

[0059] In step 235, the method determines whether the relative positions of the bordered ranges, which were.obtained in steps 210 and 215, conform to the region schema. If the relative positions do not conform to the region schema, then the method branches to step 255. If the relative positions do conform to the region schema, then the method advances to step 240.

[0060] In step 240, the method determines whether the image includes a valid Table Descriptor region, which includes valid table descriptor data. If the image does not include a valid Table Descriptor region, then the method branches to step 255. If the image does include a valid Table Descriptor region, then the method advances to step 245.

[0061] In step 245, for the case where a file includes multiple sheets of images, the method gets a sheet identifier for the present image. The present invention can operate on an image that includes a plurality of tables. Furthermore, the tables can be represented on different sheets of a graphical interface. A sheet identifier, such a Tab in an Excel™, spreadsheet, distinguishes sheets from one another. Excel™ is available from Microsoft Corporation of Redmond, Wash. The method then advances to step 250.

[0062] In step 250, the method declares that the image includes a valid table. The method determines the row and column offsets of the various bordered regions. The method then advances to step 260.

[0063] In step 255, the method declares that the image does not include a valid table. The method then advances to step 260.

[0064] In step 260, the method for validating a graphical table ends.

[0065] FIG. 7 is a flowchart of a method for mapping data from a table into a buffer in accordance with the present invention. Generally, this method examines a validated table, determines which aspects of the table are relevant to the desired purpose, i.e., formatting a database command, extracts data elements and puts data into a buffer. FIG. 7 is a detailed illustration of the operation of FIG. 5, step 115. The method illustrated by FIG. 7 begins with step 305.

[0066] In step 305, the method examines the image to identify a current region, i.e., the bordered region being examined. Having identified the region, the method can ascertain a functional attribute associated with the region, and recognize the relevance of data contained within the region. The method then advances to step 310.

[0067] In step 310, the method extracts relevant data from the current region. The method then advances to step 315.

[0068] In step 315, the method puts the extracted data into a buffer. The method then advances to step 320.

[0069] In step 320, the method determines whether the table contains an unexamined region. If the table does contain an unexamined region, then the method loops back to step 305. If the table does not contain an unexamined region, then the method advances to step 325.

[0070] In step 325, the method for mapping data from a table into a buffer ends.

[0071] FIG. 8 is a detailed illustration of the operation of step 120 of FIG. 5. FIG. 8 is a flowchart of a method for formatting a database command in accordance with the present invention. Because each bordered region has an associated functional attribute, the method effectively formats the data into a database command based on the functional attribute of the region from which the data was extracted. The method begins with step 405.

[0072] In step 405, the method gets a data element from the buffer indicated in FIG. 5 at step 115. The method then advances to step 410.

[0073] In step 410, the method associates the current data element with a parameter schema, which specifies a manner in which data from a particular bordered region shall be formatted into a database command. The method then advances to step 415.

[0074] In step 415, the method puts the current data element into an appropriate position of a database command based on the parameter schema indicated in step 410. The method then advances to step 420.

[0075] In step 420, the method determines whether the buffer contains more data. If the buffer contains more data then the method loops back to step 405. If the buffer does not contain more data, then the method advances to step 425.

[0076] In step 425, the method prepares a header for the database command. The header can include, for example, a user name, a date, a work station identification, a file name, and a document sheet identification. The method then advances to step 430.

[0077] In step 430, the method prepares a trailer for the database command. The trailer can include, for example, an end-of-file marker. The method then advances to step 435.

[0078] In step 435, the method for formatting a database command ends.

[0079] FIG. 9 is a detailed illustration of the operation of step 130 of FIG. 5. FIG. 9 is a flowchart of a method for transferring data from a buffer into a graphical table in accordance with the present invention. The method begins with step 505.

[0080] In step 505, the method gets a data element from a buffer that holds a result of a database command. The method then advances to step 510.

[0081] In step 510, the method associates the current data element with a parameter schema, which specifies a manner in which data shall be formatted for placement into a particular bordered region. The method then advances to step 515.

[0082] In step 515, the method identifies the destination region for the data based on a region schema. The method then advances to step 520.

[0083] In step 520, the method puts the data into the region indicated in step 515. The method then advances to step 525.

[0084] In step 525, the method determines whether the buffer contains more data. If the buffer contains more data, then the method loops back to step 505. If the buffer does not contain more data, then the method advances to step 530.

[0085] In step 530, the method for transferring data from a buffer into a graphical table ends.

[0086] FIG. 10 is a block diagram of a computer system 605 particularly suited for employment of the present invention. Computer system 605 includes a user interface 610, a local computer 615, a network 620, a database server 625 and a database 630. Local computer 615 includes a memory 640 for the storage of data and instructions, and an associated processor 635 for the execution of instructions.

[0087] Processor 635 executes instructions from memory 640 to perform the method steps described above. While the procedures required to execute the invention hereof are indicated as already loaded into processor memory 640, they may be configured on a storage media, such as data memory 645, for subsequent loading into processor memory 640.

[0088] User interface 610 is coupled to local computer 615. Local computer 615 is connected to server 625 across network 620. Server 625 is coupled to database 630.

[0089] A user of user interface 610 demarcates two or more bordered regions in an image using a graphical interface such as a word processor, spreadsheet or drawing application. Local computer 615 examines the image to distinguish a first bordered region and a second bordered region within the image. Local computer 615 then determines a functional attribute of the second bordered region based on a position of the second bordered region relative to the first bordered region. In an image containing more than two bordered regions, local computer 615 further examines the image to distinguish and determine a functional attribute of each bordered region.

[0090] Thereafter, local computer 615 reads data from within the second bordered region, formats the data into a database command based on the functional attribute and issues the database command to server 625. In turn, server 625 obtains a result of the database command from database 630 and sends the result to local computer 615. Local computer 615 writes the result into a bordered region of the image.

[0091] It should be understood that various alternatives and modifications can be devised by those skilled in the art The present invention is intended to embrace all such alternatives, modifications and variances that fall within the scope of the appended claims.