Sorting multiple-typed data

[0002] 1. Field of the Invention

[0003] This invention relates to sorting systems and methods, and more particularly to multiple data type sorting systems.

[0004] 2. Description of Related Art

[0005] To enable a user to efficiently view of a group of records, the user may request that the records be ordered or sorted based on one or more fields in each record. In a record the fields to be sorted may be different data types. For example, an employee database is shown in FIG. 1. The database includes a plurality of records where each record has six fields: name, date of birth, department number, annual salary, and sex (male/female). In this database each field is stored as a different data type. In particular, the name field is stored as a string data type, the date-of-birth field is a stored as a date data type, the department-number field is stored as an integer data type, the annual-salary field is stored as a double integer, and the sex field is stored as a Boolean data type.

[0006] In an object-oriented language, the data would be represented as instances of different classes; for example, in Java, the classes would be respectively, String, Date, Integer, BigDecimal, and Boolean. Even though all the data may be displayed as strings with readable characters, the data cannot be correctly ordered according to the numeric values of these characters. Otherwise, January would appear after February, and 2 would appear after 11. Therefore, data in a database or user-interface table is typically stored and sorted in appropriate data types instead of exclusively in the strings that represent their values to the user.

[0007] When a user requests the records of the employee database to be sorted, a sorting system invokes an algorithm to sort the records based on the selected fields . The sorting systems commonly invoke a different algorithm for each field data type to be sorted due to their different storage representations and sorting characteristics. Accordingly, when a user requests a group of records to be sorted based on fields of different data types, the sorting system may invoke different sorting algorithms to complete the ordering or sorting of the records. Depending on the number of records or fields, this technique can be processor intensive.

[0008] The present invention provides an efficient sorting system and method for sorting a plurality of records containing multiple-typed fields arranged in a plurality of columns. The records can be sorted based on the values in a selected subset of columns. The system generates a collation key for each field of the columns to be considered for sorting the records. Then the system sorts or orders the records based on the collation keys. The multiple columns may include at least two different data types. Further the collation keys generated by the system for the columns ideally have the same data type, such as an array of bytes. Then the system may use a common sorting algorithm to sort the records because the collation keys have the same data type.

[0009] The present invention may also sort records based on multiple fields by converting each of the multiple fields of the records to common data type fields. Then the invention orders the records based on the common data type fields. The multiple fields may include at least two different data types. Then the system may use a common sorting algorithm to sort the records because the common data type fields have the same data type. Further, the common data type fields are ideally arrays of bytes.

[0010] FIG. 1 is a diagram of an exemplary database that may be manipulated by the present invention.

[0011] FIG. 2 is a block diagram of an embodiment of a processing system in accordance with the present invention.

[0012] FIG. 3 is a representation of a process of sorting a group of records to be displayed in a table via a row index for the table according to an embodiment of the present invention.

[0013] FIG. 4 is a flow diagram of a method that may be performed by the processing system of present invention.

[0014] Like reference numbers and designations in the various drawings indicate like elements.

[0015] Throughout this description, the preferred embodiment and examples shown should be considered as exemplars, rather than as limitations on the present invention.

[0016] A preferred embodiment of a processing system 18 that may be used to order records in accordance with the present invention is shown in FIG. 2. The processing system includes a Central Processing Unit (“CPU”) 30 coupled to a Random Access Memory (“RAM”) 32, a Read Only Memory (“ROM”) 34, a display 36, a user input 38, a Modulator/Demodulator (“MODEM”) 42, and a storage medium interface 44. The CPU 30 is a microprocessor such as a PowerPC, Intel®, AMD®, or Cyrix® microprocessor. The ROM 34 stores program instructions to be executed by the CPU 30. The RAM 32 stores data to be processed by the CPU 30 and program registers. The MODEM 42 may be coupled to a communications link and enables the CPU 30 to transceive data between another processor. The MODEM may be coupled to a plain old telephone system (“POTS”), local area network (“LAN”), or an Internet. The storage medium interface 44 enables the data transfers between the CPU 30 and a storage medium. The storage medium interface 44 may be any interface that enables communication between the CPU 30 and storage medium such as Small Computer System Interface (“SCSI”), Industry Standard Architecture (“ISA”), Peripheral Component Interconnect (“PCI”), and others. Accordingly, the CPU 30 may receive data including database records from a storage medium coupled to the storage medium interface 44 or via the MODEM 42 from almost any location.

[0017] The display 36 enables the CPU 30 to display data or information to a user in a human readable format. The display 36 may be any display device including a cathode ray tube (“CRT”), liquid crystal display (“LCD”), and others. The user input device 38 enables a user to provide instructions or requests to the CPU 30. The user input device 38 may be any input device including a keyboard, mouse, trackball, microphone for voice activated systems, touch based system coupled the display 36, and others. The user may request the CPU 30 to perform operations on database records including sorting a group of records based on one or more fields where the fields of the records are to be sorted.

[0018] In order to sort a group of records of a table in a database or graphical user interface (“GUI”) (such as the table shown in FIG. 1) based on multiple fields of each record, the CPU 30 generates a collation key for each selected field of each record. Each collation key represents the data of the associated field in a format that maintains the natural sorting order of the field. Each collation key also includes a pointer, an object reference, index value or the equivalent to the respective record. The CPU 30 then uses a sorting algorithm to sort the collation keys (and effectively the records of the table). The sorting algorithm may be any known sorting algorithm such a bubble sort algorithm or others. Then, the group of records of the database can be ordered based on the pointers, object references, or index values of the sorted collation keys. Accordingly, the group of records is effectively sorted by the selected fields of the records by the application of the sorted collation keys to the records.

[0019] For sorting multiple-type data using collation keys, the sorting logic or algorithm must handle collation keys for strings, sort false before true (Boolean types), sort large negative numbers before small negative numbers (integer or float types), and sort negative numbers before zero or positive numbers. Numbers are generally compared using numeric comparison operators such as “<” (less than), “=” (equal), and “>” (greater than). When dates and times are not internally represented as numbers, sorting logic must convert them to numbers to permit numeric comparison. The sorting logic is made more complex when fields may include the possibility of null values. Logic to test the data types of the collation keys and to select the appropriate conversions and comparisons diminishes the efficiency of the sorting.

[0020] Further, in multi-column sorting data in secondary columns or fields (and tertiary columns and so forth) must be compared. When two items in the previous column are equal, different comparisons must be made of another column or field. Preferably, the collation keys data type is the same for all selected fields regardless of the selected field data type. In prior art systems, when a user requests two or more of the records of the database shown in FIG. 1 to be sorted on multiple-typed fields, e.g., salary and name, a different sorting algorithm may be used to sort each field because the fields have different data types; the salary field is double integer data type and the name field is a string data type. Accordingly, an integer data type sorting algorithm may be employed to sort the salary field and a string data type sorting algorithm may be used to sort the name field. In the invention, the CPU 30 generates a collation key for each salary and name value in the records of the database to be sorted. In the preferred embodiment the collation keys representing the salary values have the same data type as the collation keys representing the name values. Preferably each collation key is an array of bytes.

[0021] When the collation keys representing different selected fields of a record have the same data type, a single or common sorting algorithm can be employed to sort all the collation keys. The records are ordered based on the sorted collation keys where each collation key includes a pointer, an object reference, or an index value to a respective record. This may reduce the time required to effectively sort multiple fields of records. FIG. 3 is a representation of a process shown in FIG. 4 of sorting a group of records to be displayed in a table via a row index for the table according to an embodiment of the present invention. A user may select a group of rows in a table 12 (that represents records of a database 14) to be sorted (step 52). The user may then select columns of the table 12 (that represent fields of the records of the database 14) to be sorted (step 54). As shown in FIG. 3, each row of the table 12 corresponds to a record 14 where the records 14 are displayed in the rows of the table 12 based on the row index 10. These steps, 52 and 54 may be performed in conjunction with a user or other mechanism that cooperates with a CPU 30 to select the records and select fields.

[0022] Collation keys are generated for each column (field) of each row (record) of the table 12 (database 14) to be sorted (at step 56). Ideally, the collation keys have one data type, in particular, an array of bytes regardless of the data types of the selected columns (fields). In this embodiment, the collation keys are generated by converting the selected column (field) values to array values based on rules that vary as a function of the data type of the index field. After the collation keys are generated, the keys are sorted (step 58). The collation keys may be sorted using a common sorting algorithm given the collation keys are commonly typed. The records then are effectively ordered based on the sorted collation keys (step 60) by sorting the row index 10 based on the collation keys in one embodiment. Each collation key includes a pointer, an object reference, or an index value to a corresponding record so the records can be ordered based on the sorted pointers, object references, or index values.

[0023] Many different methods or algorithms may be employed to convert all selected fields (or columns) to common data type collation keys. Even where the column data type is string, special treatment may be required to preserve the correct character order for the national language of the user. In particular, the character order may be different from the order of the numeric American Standard Code for Information Interchange (“ASCII”) values that represent the characters. For example, in ASCII, “A” is 65, “B” is 66, however, “A-grave” is 193. When sorting characters normally A-grave should sort between A and B. Sorting the numeric value that represents characters, “A-grave” would sort well after “B”, not before “B”. Therefore, a collation key may be ideally generated even for string data type fields.

[0024] For example, the collation keys for strings may be generated based on language-specific rules. To create a collation key, the CPU 30 applies ordering rules for the language to the string data and selects a sequence of characters that can be appropriately ordered in place of the original string. In detail, the CPU 30 applies the ordering rules to the numeric values of the characters of the string considered from left to right to generate a collation key. For a national language that is written right to left, the string would still be considered from left to right.

[0025] In one embodiment, the CPU 30 generates collation keys for String, null, Boolean, Date (including time values), BigDecimal, BigInteger, Float, Double, Long, Integer, and Short where the resultant collation keys are all string data and may be sorted by the same comparison or sorting algorithm. For string data types (fields having string values), the CPU 30 employs a rule based collator based on the locale (to determine national language characteristics). The CPU 30 generates collation keys for the other data types by getting their display values and applying data type based rules to the resultant strings.

[0026] Based on the rules, the CPU 30 preferably generates collation keys so: 1) null values sort before everything else; 2) false sorts before true for Boolean type data; 3) earlier dates and times sort before later dates and times for time/date types; 4) large negative numbers sort before small negative numbers and negative numbers sort before zero or positive numbers for integer data; 5) negative infinity sorts before the largest negative number, negative zero sorts just before zero, infinity sorts after the largest positive number, and non-numeric values sort last for float type data.

[0027] In one preferred embodiment, the rules to be executed by a CPU 30 are implemented in Java where the Java code is used for sorting values in two-dimensional tables in which each column may display values of a different data type. The Java implementation is in the following classes and with the following rules:

[0028] AssistKey:

[0029] 1) Keeps a source index and a sortable key, which is a byte array.

[0030] 2) Implements a compareTo method that lets each AssistKey compare itself to another AssistKey, returning an integer that is negative if this AssistKey is less than the other, zero if equal, and positive if greater than.

[0031] 3) The compareTo method compares the numeric values of the bytes in the two byte arrays from left to right.

[0032] 4) After the AssistKeys are sorted, their index values provide access to the original data.

[0033] AssistCollator:

[0034] 1) Creates AssistKeys not only for String, but also Boolean, Short, Integer, Long, Float, Double, BigInteger, BigDecimal, and null data.

[0035] 2) When constructed, an AssistCollator can be provided with a Locale. Otherwise, it uses the default Locale. This Locale lets AssistCollator construct an appropriate RuleBasedCollator, named “delegate,” that can generate a CollationKey for a String.

[0036] 3) AssistCollator.getByteArray(Object) methods exist for each data type.

[0037] Object:

[0038] 1) For null, returns a byte array for the minimum long value.

[0039] 2) For a known type, returns getByteArray for the known type.

[0040] 3) Otherwise, gets the displayable String for the object and returns getByteArray(String).

[0041] String:

[0042] 1) For null, returns a byte array for the minimum long value.

[0043] 2) Otherwise, gets a CollationKey from the delegate and return its byte array.

[0044] Boolean:

[0045] 1) For null, returns a byte array for the minimum long value.

[0046] 2) Otherwise, returns a one-byte array. The value of its only byte is 1 if the Boolean value is false, and 2 if true.

[0047] Date:

[0048] 1) For null, returns a byte array for the minimum long value.

[0049] 2) Otherwise, gets the long value of the Date and returns getByteArray(Long).

[0050] BigInteger:

[0051] 1) For null, returns a byte array for a BigDecimal smaller than the minimum decimal value that can be stored.

[0052] 2) If the value is 0, returns a one-byte array. The value of its only byte is 2.

[0053] 3) If the value is negative, gets the complement by adding a number larger than the maximum decimal value that can be stored.

[0054] 4) Gets an N-byte array containing the big-endian, two's-complement representation of the number.

[0055] 5) Returns a byte array with N+1 bytes, whose first byte represents the sign with a value of 1 if the number is negative, and 3 if positive, and whose remaining bytes contain the bigendian, two's-complement representation of the number.

[0056] BigDecimal:

[0057] 1) For null, returns a byte array for a BigDecimal smaller than the minimum decimal value that DB2 can store.

[0058] 2) If the value is 0, returns a one-byte array. The value of its only byte is 2.

[0059] 3) If the value is negative, calculates the fractional part and the complement of the whole part. Shifts the decimal point of the fractional part to the right by the scale (the number of digits to the right of the decimal point). The complement is the whole part subtracted from a number larger than the maximum decimal value that can be stored.

[0060] 4) For positive values, calculates the fractional and whole parts. Shifts the decimal point of the fractional part 31 positions to the right where 31 positions are the maximum decimal precision in the exemplary database.

[0061] 5) Gets an N-byte array containing the big-endian, two's-complement representation of the whole part, and an F-byte array containing the big-endian, two's-complement representation of the fractional part. Returns a byte array with N+F+1 bytes, whose first byte represents the sign with a value of 1 if the number is negative, and 3 if positive, and whose remaining bytes contain the big-endian, two's-complement representation of the whole part, padded to 12 bytes by zeros, followed by the shifted fractional part.

[0062] Float:

[0063] 1) For null, returns a byte array for the minimum single-precision floating-point value.

[0064] 2) Otherwise, considers the IEEE-754 “single-precision” bit layout of the 4-byte number, in which the sign is in the first bit, position 31, in which the exponent is 8 bits in positions 30-23, and in which the mantissa (significand) is 23 bits in positions 22-0.

[0065] 3) If the value is −0, adds 1.

[0066] 4) If the value is 0, returns a one-byte array. The value of its only byte is 2.

[0067] 5) Otherwise, returns an array of 6 bytes.

[0068] 6) Byte 0 represents the sign with a value of 1 if the number is negative, and 3 if positive.

[0069] 7) If the value is negative, flips the bits of the exponent and mantissa.

[0070] 8) Bytes 1 and 2 of the returned array contain the exponent, and bytes 3 through 5 contain the mantissa.

[0071] Double:

[0072] 1) For null, returns a byte array for the minimum double-precision floating-point value.

[0073] 2) Otherwise, considers the IEEE-754 “double-precision” bit layout of the 8-byte number, in which the sign is in the first bit, position 63, in which the exponent is 11 bits in positions 62-52, and in which the mantissa (significand) is 52 bits in positions 51-0.

[0074] 3) If the value is −0, adds 1.

[0075] 4) If the value is 0, returns a one-byte array. The value of its only byte is 2.

[0076] 5) Otherwise, returns an array of 10 bytes.

[0077] 6) Byte 0 represents the sign with a value of 1 if the number is negative, and 3 if positive.

[0078] 7) If the value is negative, flips the bits of the exponent and mantissa.

[0079] 8) Bytes 1 and 2 of the returned array contain the exponent, and bytes 3 through 9 contain the mantissa.

[0080] Long:

[0081] 1) For null, returns a byte array for the minimum long value.

[0082] 2) If the value is 0, returns a one-byte array. The value of its only byte is 2.

[0083] 3) Otherwise, returns an array of 9 bytes.

[0084] 4) Byte 0 represents the sign with a value of 1 if the number is negative, and 3 if positive.

[0085] 5) If the value is negative, flip the value (resulting in the absolute value minus 1) and subtract this from the maximum long value.

[0086] 6) Bytes 1 through 8 contain the positive value.

[0087] Integer:

[0088] 1) For null, returns a byte array for the minimum integer value.

[0089] 2) If the value is 0, returns a one-byte array. The value of its only byte is 2.

[0090] 3) Otherwise, returns an array of 5 bytes.

[0091] 4) Byte 0 represents the sign with a value of 1 if the number is negative, and 3 if positive.

[0092] 5) If the value is negative, flip the value (resulting in the absolute value minus 1) and subtract this from the maximum integer value.

[0093] 6) Bytes 1 through 4 contain the positive value.

[0094] Short:

[0095] 1) For null, returns a byte array for the minimum short value.

[0096] 2) If the value is 0, returns a one-byte array. The value of its only byte is 2.

[0097] 3) Otherwise, returns an array of 3 bytes.

[0098] 4) Byte 0 represents the sign with a value of 1 if the number is negative, and 3 if positive.

[0099] 5) If the value is negative, flip the value (resulting in the absolute value minus 1) and subtract this from the maximum short value.

[0100] 6) Bytes 1 through 2 contain the positive value.

[0101] While this invention has been described in terms of a best mode for achieving this invention's objectives, it will be appreciated by those skilled in the art that variations may be accomplished in view of these teachings without deviating from the spirit or scope of the present invention. For example, the present invention may be implemented using any combination of computer programming software, firmware or hardware (e.g., a software language other than Java, such as C++ or others may be used to implement the invention). As a preparatory step to practicing the invention or constructing an apparatus according to the invention, the computer programming code (whether software or firmware) according to the invention will typically be stored in one or more machine readable storage mediums such as fixed (hard) drives, diskettes, optical disks, magnetic tape, semiconductor memories such as ROMs, PROMs, etc., thereby making an article of manufacture in accordance with the invention. The article of manufacture containing the computer programming code is used by either executing the code directly from the storage device, by copying the code from the storage device into another storage device such as a hard disk, RAM, etc. or by transmitting the code on a network for remote execution.