Abstract:
A system for multicolumn ordering includes a processor and a data store. The data store stores a table including a plurality of category and data columns. The processor applies key prefixes to column data values of a plurality of the data columns to form and store in the data store a set of prefixed data values. The processor then orders the set of prefixed data values in a first compound column and one or more category columns in a second compound column and responsive to the first and second compound columns selects and stores a subset for further processing.

Description:
BACKGROUND OF THE INVENTION  
       [0001]     1. Technical Field of the Invention  
         [0002]     This invention relates to sorting technology. More particularly, it relates to multicolumn sorting in one column.  
         [0003]     2. Background Art  
         [0004]     The IBM Lotus Notes™ program currently provides a multiple column sort capability for non-restricted document views. Views are dynamically changing windows in a Notes database for organizing and locating documents. When the view is embedded and restricted by category, then the multiple column sort capability is unavailable. In Notes, an embedded object, such as a view, is an object that becomes part of a Notes document.  
         [0005]     One approach to providing multiple column sort capability in embedded views restricted by category is with the use of multiple Notes views, at the cost of large overhead on the system for maintaining these multiple Notes views. Maintenance cost increases when servicing or updating multiple views. Another approach is to sort data inline with every query request. There is a need, therefore, to provide improved server performance, by providing multicolumn sort capability with fewer views while avoiding having to sort data inline with every query request.  
         [0006]     It is an object of the invention to provide an improved system and method for sorting on multiple columns when multiple column sort capability is not available or efficient.  
       SUMMARY OF THE INVENTION  
       [0007]     A system and method for multicolumn ordering by applying key prefixes to column data values of a plurality of data columns to form a set of prefixed data values; ordering the set of prefixed data values in a first compound column; and selecting from that ordered set a subset for further processing.  
         [0008]     In accordance with an aspect of the invention, there is provided a computer program product configured to be operable for ordering in a single column a table having a plurality of category and data columns by use of column key prefixes on column values.  
         [0009]     Other features and advantages of this invention will become apparent from the following detailed description of the presently preferred embodiment of the invention, taken in conjunction with the accompanying drawings. 
     
    
     BRIEF DESCRIPTION OF THE DRAWINGS  
       [0010]      FIG. 1  is a diagrammatic view illustrating an embedded subsetted view restricted by category within the view framework of Notes.  
         [0011]      FIG. 2  illustrates the format of a portion of a Notes document table including category columns requester  41  and initiator  43  and a plurality of data columns  50 - 58 .  
         [0012]      FIG. 3  illustrates a view of the document table of  FIG. 2 , with the category columns  41  and  43  combined into an owner column  42 .  
         [0013]      FIG. 4  illustrates a subsetted view of the table of  FIG. 3  restricted to Bill as owner (either requester or initiator).  
         [0014]      FIG. 5  illustrates a specialized view of the table of  FIG. 2  with two sorted compound fields (owner and data) in accordance with the preferred embodiment of the invention.  
         [0015]      FIG. 6  is a diagrammatic illustration of the format of a row entry in the specialized view of the table of  FIG. 5 .  
         [0016]      FIG. 7  illustrates a portion of the specialized view of  FIG. 5  selected to show the application of Notes methods: GetFirstEntry, GetNthEntry, GetAllEntriesByKey, and GetLastEntry.  
         [0017]      FIG. 8  illustrates an embedded view derived from the embedded view of  FIG. 4  showing the amount column sorted in descending order in accordance with the preferred embodiment of the invention.  
         [0018]      FIG. 9  is a diagrammatic illustration of formats of row entries in the specialized view of  FIG. 10 , illustrating concatenation of data fields for nested sorting in accordance with an alternative embodiment of the invention.  
         [0019]      FIG. 10  illustrates a specialized view showing concatenated data fields for nested sorting.  
         [0020]      FIG. 11  is a flow chart representation of the initialization steps of an illustrative embodiment of the invention.  
         [0021]      FIGS. 12A through 12F  are a flow chart representation of an illustrative embodiment of the invention for collecting into local arrays column sorted data for presentation in an embedded view. 
     
    
     DETAILED DESCRIPTION OF PREFERRED EMBODIMENTS  
       [0022]     In accordance with the preferred system and method of the invention, multicolumn ordering is accomplished in a single column by use of column key prefixes on column values.  
         [0023]     Exemplary embodiments of the invention draw upon the methods of the IBM Lotus Notes (TM) program.  
         [0024]     Referring to  FIG. 1 , a diagrammatic view illustrates an embedded subsetted view  30  within the view framework of Notes restricted by category. Non-restricted document view  20  includes category  22  and data columns  24 ,  26 . In the prior art, when selecting portion  28  restricted to a specific category  22  for display in embedded view  30 , the ability to sort data on columns  24  or  26  is lost. In accordance with the present invention, data columns  24  and/or  26  may be sorted in ascending or descending mode.  
         [0025]     Referring to  FIG. 2 , the format of a portion of a Notes document table  40  is illustrated including category columns requester  41  and initiator  43 , document reference number column  44 , and a plurality of data columns including number of items  50 , creation date  52 , dollar amount  54 , first supplier  56 , and first item  58 .  
         [0026]     Referring to  FIG. 3 , a modified view of Notes document table  40  illustrates a table of data for a collection of Notes documents, in which the requester  41  and initiator  43  columns have been combined into an owner column  42 , and data columns including document reference number  44 , number of items  50 , creation date  52 , dollar amount  54 , first supplier  56  and first item  58 . In this example, Bill is both requester  41  and initiator  43  of document reference number  6 , and the requester  41  and initiator  43  are different for the other illustrated entries in table  40 .  
         [0027]     Referring to  FIG. 4 , a subsetted view of the table of  FIG. 3  is illustrated which is restricted to include only rows or entries in which Bill is the owner  42  (that is, either requester  41  or initiator  43  ). Primary key  62  and column keys  64 ,  66  will be described hereafter in connection with sorting and nested sorting, respectively.  
         [0028]     Referring to  FIG. 5 , a specialized view  80  of the table of  FIG. 2  is illustrated with two sorted compound fields (owner  42  and data  84  ) in accordance with the preferred embodiment of the invention.  
         [0029]     In order to provide the ability to sort on several columns  41 - 58  in a Notes view, including at least one restricting column  41 ,  43  and a plurality of data columns  50 - 58 , a first sorted compound column (herein, owner  42 ) is created from the restricting columns  41 ,  43  and a second sorted compound column  84  is created from the data columns  50 - 58  and prefix data, or column keys,  64 .  
         [0030]     Referring to  FIG. 6 , the format of a row entry  90  in the specialized view of the table of  FIG. 5  is shown. A first compound column  42  is sorted on primary key  62  which, in the example of  FIG. 4 , includes owner column  42 . A second compound column  94  is sorted on column key  64 , including prefix values  70 - 74  and corresponding data columns  54 - 58 , as will be illustrated hereafter. Referring to  FIG. 5 , before sorting the second compound column  84 , a key  64  (preferably in character format) is prefixed to each data value  98 , which key identifies the specific data column  54 - 58  for that data value. Since the prefix is character in format, the data values  54 - 58  are converted to some appropriate character representation to ensure proper sorting. In this manner, a date column may be formatted as year, month and day, or YYYYMMDD. Number values would be left padded to represent the integer values by numbers of an appropriate length for proper ordering (so that 002 comes before 010, else 2 would come after 10 based on a comparison of first characters). A key/data delimiter, such as colon “:”  96 , is inserted between column key  64  and data value  98 .  
         [0031]     View  80  is defined to &#39;show multiple values as separate entries&#39;. So that instead of one document record in the view with six item column values, there are six separate document records with single values in a sorted second column  84 .  
         [0032]     The resulting view  80  shows a first column  42  that is sorted by owner (requester  41  or initiator  43 ), and a second column  84  that is sorted by data type  70 ,  72 ,  74  first and within data type by the value  98  of the data type. In this manner, for a table of N data columns  54 - 58  (those data columns selected for sort from a table which may include many more data columns, such as columns  50  and  52 ), N different sorts are combined into one column  84 . In the example of  FIGS. 4 and 5 , N=3.  
         [0033]     Referring to  FIG. 7 , a portion of the specialized view of  FIG. 5  is selected to show the application of Notes methods: GetFirstEntry  110 , GetNthEntry  112 , GetAllEntriesByKey  114 , and GetLastEntry  116 .  
         [0034]     For example, view  80  is opened by a Notes Agent to execute GetAllEntriesByKey  114 . As input to this request, the owner name  42  is the first key  62  to match on, and the second key is the prefix value  64  so that the second column  84  is matched (not an exact match) on just the prefix  64 . This process extracts from view  80  a unique set of documents that are sorted as illustrated by view  100  by the compound value prefix|data  64 | 98 .  
         [0035]     Referring to  FIG. 8 , an embedded view  120  derived from the embedded view of  FIG. 4  shows the amount column  54  sorted in descending order in accordance with the preferred embodiment of the invention.  
         [0036]     The above described GetAllEntriesByKey method  114  results in a NotesViewEntryCollection which includes a count property that yields the number of documents in the result set  120 . (In this example, there are 15 rows, and the count property is 15.) A NotesViewEntryCollection represents a collection of view entries of type document sorted in view order.  
         [0037]     GetFirstEntry  110 , GetLastEntry  116 , GetNextEntry, GetNthEntry  112  and GetPrevEntry methods allow navigation to a set of documents  108  that are of interest. Since the document set is ordered (as illustrated from rows  102  through  106  of  FIG. 7 ), they are sorted in the same order, either ascending or descending, and these methods and the count property provide sufficient tools to identify the set of documents from either the top  102  of the list or the bottom  106  for posting as view  120  back to the user.  
         [0038]     Referring to  FIGS. 9 and 10 , concatenation of data fields  54 ,  56  for nested sorting in accordance with an alternative embodiment of the invention is illustrated. In order to post back to the user a view in which a plurality of columns  54 ,  56  are sorted in nested fashion, a prefix value  76  may be appended to a concatenation of that plurality of columns  54 ,  56 , and then sorted as above along with column keys  78 ,  144  and data  58 ,  146 , respectively, into second compound column  142  of specialized view  140 . Rows  138  illustrated the sorted concatenated data values.  
         [0039]     Referring to  FIGS. 11 and 12 A through  12 F in connection with  FIG. 7  and Table 1, an exemplary embodiment of the invention is illustrated by pseudo code and flow charts. As used in Table 1, a Notes session is the root of the Domino Objects containment hierarchy, providing access to the other Domino objects, and represents the Domino environment of the current program. A specific view is a named Notes view. A view entry collection represents a collection of view entries, selected according to specific criteria. This class mainly provides a collection of documents in a view-sorted order. In the agent, document information is being collected. How many documents this is done for depends on a page size. The agent is filling this space/page with document data-therefore the reference to a data page. A local array is a data structure local to the processing agent to serve as the collection area for the view data of interest. Notes agents are stand-alone programs that perform a specific task in one or more databases. In steps  220 ,  222 , the “specific method” refers to GetLastEntry and GetFirstEntry versus GetNthEntry. The document entry in step  244  refers to a Notes View Entry, and represents a view entry. A view entry represents a row in a view. In this case, row reflects a Notes Document. For a Notes View Entry, the underlying document may be accessed via the property “Document” of the Notes View Entry. Thus, the document referred to in step  246  corresponds to a line of data from the view. At step  266 , the needed data has been captured into local arrays. It is expected that the code following this point will format the data and present it to a Web page.  
                                                             TABLE 1                       PSEUDO CODE REPRESENTATION OF AN ALGORITHM FOR       MULTICOLUMN SORTING IN ONE COLUMN                                    1   Step 202: Define a Notes Session.           2   Step 204: For the Notes Session, define a Notes Database           3   link to the current database.           4   Step 206: Define a Notes View to a specific view in the           5   current database.           6    View 100 has 2 sorted columns 42, 94.           7     First column 42 is multivalued - represents unique           8     document owners (in this case, Bill)           9     Second column 94 is multivalued - all data has           10     been converted in the document to character           11     appropriately padded on the front to allow for           12     correct sorting alignments           13     within the second column 94, every value is           14     uniquely prefixed with a column number 64           15     followed by a colon 96:                16      01:yyyy/mm/dd   (date)           17      02:$$$$$$$$$$   (dollar value front padded           18       with zeros to maximum $ value)           19      03:nnnnnnnnn   (number front padded with           20       zeros to maximum size)           21      04:text                22    The view 100 is defined to break up these multivalued           23    entities into separate view rows.           24   Thus, a multicolumn, bidirectional (done with programming)           25   sort is provided in a single column 94. The algorithm below           26   extracts the required data from the view 100.           27   Step 210: Get the input parameters: sort direction, column           28   index, desired page number, page size, and owner.           29   Step 212: Based on page size and desired page number,           30   compute starting record number.           31   Step 214: Define keys to restrict view.           32    First key 42 is full key representing one of the           33    document owners (in this example: Bill).           34    Second key is a partial one having the prefix 64           35    corresponding to the column of interest (e.g. “02”)           36    (colon 96 is used in prefix 64 to distinguish it from           37    remainder of column data 98)           38   Step 216: Define a View entry collection using defined keys           39   42, 64. Resulting view entry collection is a restricted           40   list 100 where the owner matches one of the owners (e.g.,           41   Bill), sorted on the indicated column 94).           42   Step 218: Note the View entry collection result count (In           43   this example, 15, the number of rows sorted on key 02 in           44     Figure 5 ).           45   Step 220: Based on direction and starting row, set pointer           46   for first display document.           47    (If at top or bottom of collection, quicker to use           48    specific method rather than getting Nth entry.)           49   Step 222: If (Sort Direction is descending) Then           50    Step 224: If (Starting Record is 1) Then ‘ First or           51    bottom entry 106 in collection           52           53     Step 226: Set DocumentEntry =           54     ViewCollection.GetLastEntry( )           55    Else ‘ Middle of collection 104 / reverse order           56     Step 228: Set DocumentEntry =           57     ViewCollection.GetNthEntry( CollectionCount less           58     StartingRecord )           59    End If           60   Else ‘ Sort Direction is ascending           61    Step 230: If (Starting Record is 1) Then ‘ First or top           62    entry 102 in collection           63     Step 232: Set DocumentEntry =           64     ViewCollection.GetFirstEntry( )           65    Else ‘ Middle 104 of collection           66     Step 234: Set DocumentEntry =           67     ViewCollection.GetNthEntry( StartingRecord )           68    End If           69   End If           70   Step 236: Create array structure to hold required document           71   data.           72   Step 238: Initialize documents captured to zero.           73   Step 240: Reset the next data page flag.           74   Step 240: Reset the page full flag.           75   Steps 242-264: Do While (DocumentEntry Is Available) ‘ If           76   we have a DocumentEntry to look at, start the look           77           78    Step 244: For the DocumentEntry, get the Document.           79    Step 246: If (Document is available) Then           80     Step 248: If (Page full flag is set, then           81     document represents start of another data page)           82     Then           83      Step 264: Set next data page flag to           84      indicate that there is more data after this           85      page           86      Exit Do           87     End If           88           89     Step 250: Get required document data into local           90     arrays for later presentation           91     Step 252: Increment counter of documents captured           92     for current data page.           93     Step 254: If (Documents captured equals the page           94     size) Then           95      Step 256: Set page full flag           96     End If           97    End If           98    Based on sort direction, get the next DocumentEntry           99    Step 258: If (Sort Direction is Descending) Then           100     Step 260: Get Previous DocumentEntry           101    Else           102     Step 262: Get Next DocumentEntry           103    End If           104           105   Loop           106   Step 266: Required data (if present) is in local arrays to           107   further process as required.                      
 
         [0040]     By sorting on two columns  42 ,  84 , when view restrictions are applied, a view  100  is presented which in a first step includes only a specified person&#39;s (Bill) data. In a second step, a Notes agent ripples through the clustered data to pull off what is needed to present to the user a view of his data in sorted order. The key  64  applied to the data values  98  collapses multiple columns  50 - 58  into one column  94 .  
         [0041]     In accordance with the preferred embodiment of the invention, one database is conceptualized. However, it may be implemented with N databases, in which case it would only be required to keep track of a next record to retrieve based on the largest (or smallest) record remaining in the N databases.  
       Advantages over the Prior Art  
       [0042]     It is an advantage of the invention that there is provided an improved system and method for sorting on multiple columns when multiple column sort capability is not available or efficient.  
       Alternative Embodiments  
       [0043]     It will be appreciated that, although specific embodiments of the invention have been described herein for purposes of illustration, various modifications may be made without departing from the spirit and scope of the invention. In particular, it is within the scope of the invention to provide a computer program product or program element, or a program storage or memory device such as a solid or fluid transmission medium, magnetic or optical wire, tape or disc, or the like, for storing signals readable by a machine, for controlling the operation of a computer according to the method of the invention and/or to structure its components in accordance with the system of the invention.  
         [0044]     Further, each step of the method may be executed on any general computer, such as IBM Systems designated as zSeries, iSeries, xSeries, and pSeries, or the like and pursuant to one or more, or a part of one or more, program elements, modules or objects generated from any programming language, such as C++, Java, Pl/1, Fortran or the like. And still further, each said step, or a file or object or the like implementing each said step, may be executed by special purpose hardware or a circuit module designed for that purpose.  
         [0045]     Accordingly, the scope of protection of this invention is limited only by the following claims and their equivalents.