Bese davno, ali ajde ipak cu se ukljuciti, mozda ce ovo jos nekome trebati:
najbolje resenje, pogotovo ako imas puno takvih podataka, sa aspekta memorije, a manje-vise i jednostavnosti upotrebe je koriscenje integera za smestaj.
Sve je objasnjeno detaljno sa primerima u clanku sa Delphi Informant sajta, a posto nemam gde da uploadujem niti je dostupno na sajtu bez login-a, stavljam ovako - valjda nece nikome smetati.
P.S. Ako nekome treba ovo lepo u *.mht -u sa slikama i zip sa primerima nek se javi, pa mu posaljem na mail
-----------------------------------------------------------
OP Tech
Delphi 1-4 / Bit Manipulation / Database / OOP
By Steve Griffiths
When Every Bit Counts
Compact Storage Using Integer Bitfields
In many situations, a user may be required to select between 0 and n items from a list, e.g. languages spoken, past illnesses, etc. A ListBox, CheckListBox, or collection of checkboxes can easily be used to display the selection to the user, but storing the selected items in a database table requires consideration.
It would be simple to provide a Boolean field for each item in the list, but this approach leads to wide tables, and wouldn't allow an end user to add an item to the list. Alternatively, a child table could be used to write a record for each item that was selected. This would work and would allow an extendable list, but it would require coding to update the list each time the master record changes and to save changes to the child table.
A far more compact method is to store a numeric reference to each selected item in a single integer field. This article explains the use of a technique called bit shifting to achieve this, and also details a data-aware CheckListBox that implements the bitshift mechanism.
Bits Broken Down
A four-byte integer contains 32 bits, each of which can be individually addressed and used as a switch. This means that with a little trickery, a single integer can be used to determine the selection status of up to 31 items. (The reason 31 is the maximum - and not 32 - is that most databases use a signed integer type, and use the most significant bit - bit 31 - to store the sign.) All we need to do is convert each selected item in the list to an equivalent bit number in the integer and turn it on.
Back to Binary
The binary system uses a base of 2, with each bit representing either 0 or 2 to the power of the bit position (zero-based). Binary values are read right-to-left, for example:
binary 110101
= 2^0 + 0 + 2^2 + 0 + 2^4 + 2^5
= 1 + 4 + 16 + 32
= 53
List Indexing
Each item in a StringList (e.g. ListBox1.Items) has an index value between 0, and the number of items in the list minus one. To store a reference to each selected item in single integer, we need to convert the index value of the item to the number that sets the equivalent bit in the integer; selecting items 2 and 5 in the list should set bits 2 and 5 in the integer. To do this, we use bit shifting.
One of the Boolean operators provided by Delphi is shl. This stands for "Shift Left," and, when applied, moves each bit of an integer one step to the left. For example, 53 shl 1 = 106. In binary representation, 0110101 shl 1 = 1101010. As you can see, each bit has been moved to the left. To convert an ordinal value to a bit equivalent, start with 1, and shift it left by the ordinal value, e.g. 1 shl 4 = 16. In binary representation, 00001 shl 4 = 10000.
Truth in Logic
Since we want to represent more than one item in the integer, we cannot simply assign the converted index value; this would erase any previous entry. For our purposes, Delphi provides the or Boolean operator. When two numbers are or-ed together, the resulting number will consist of any bit that is set in the first or second number. For example, 1 or 4 = 5. In binary representation:
0001
or 0100
= 0101
This is not the same as addition. For example, 2 or 2 = 2. In binary:
0010
or 0010
= 0010.
By use of the or operator, we are able to iterate the items in a list, convert the index value of each selected item, and set the equivalent bit of an integer.
An Example
Figure 1 shows a form with a CheckListBox containing five languages. The index values are from 0 to 4. Items 0 and 2 have been selected.
Figure 1: A demonstration form with a CheckListBox.
In addition to the Items property, the CheckListBox contains a Checked property, which maintains the checked status of each item. To assess the checked status of an item, use the item's index value. For example, Checked[0] is True; Checked[1] is False.
The GetBitField function (see Figure 2) will iterate the Checked list, test each to see if it is checked, and if so, will update the integer field. Once obtained, the result can be stored to any integer field in a table.
// If Item is selected, set corresponding Result bit.
function TForm1.GetBitField: Integer;
var
i : Integer;
begin
Result := 0; // Initialize Result.
with CheckListBox1 do
for i := 0 to Items.Count - 1 do
if Checked
then
Result := Result or (1 shl i);
end;
Figure 2: Obtaining an integer bitfield from a list.
Getting It Back
To set the item's Checked property based on the contents of a bitfield, we must use a technique known as bit masking. Delphi provides the and operator, which is useful for this purpose. When two numbers are and-ed together, the resulting number will only have a bit set if that bit is set in both numbers. For example, 1 and 2 = 0. In binary representation:
1001
and 1010
= 1000.
To set the Checked property of the items in the list, iterate the list and mask the integer against the bit equivalent of the item's index value. The SetChecks function shown in Figure 3 demonstrates how this works.
// For each item, mask its equivalent bit.
// If true (non-zero), set checked true.
procedure TForm1.SetChecks(BitField : Integer);
var
i : Integer;
begin
with CheckListBox1 do
for i := 0 to Items.Count - 1 do
Checked := LongBool(BitField and (1 shl i));
end;
Figure 3: Setting the Checked items from a bitfield.
Most of the work here is done by one statement:
Checked := LongBool(BitField and (1 shl i));
First, 1 is shifted left by the iterator value to give a number that represents the iterator's equivalent bit setting. This value is then and-ed against a bitfield, and the result is cast as a LongBool. A result, zero (0) is considered False, and a non-zero result is True. A LongBool is used because a Boolean type only occupies one byte, and will essentially ignore any bits in the remaining three upper bytes, whereas a LongBool occupies four bytes and will evaluate the entire integer. Finally, the Checked property for the item being referred to by the iterator is set to the resulting Boolean value.
Filtering on a Bitfield
Now that we have a mechanism for representing a selection of items as an integer, it would be nice if we could filter a DataSet based on the value of that number. The same Boolean techniques discussed previously can be used to provide a flexible filtering mechanism.
Figure 4 shows a form containing several components: a DBGrid, a RadioGroup, a CheckListBox, a Table, and a DataSource.
Figure 4: The second demonstration form.
The Table contains two fields: one for a name, and the other an integer representing the languages spoken by the person. The RadioGroup is used to select the filter type, and the CheckListBox is used to select which languages are to be selected by the filter. Notice that the RadioGroup can be used to display matches for any selected language, or only those records where the person speaks all selected languages.
The Code
There are four event handlers used to control the filters. Two of these are used to filter the data; one is used to obtain a bitfield, and the last is used to dynamically assign one of the two OnFilterRecord event handlers to the table. Whenever a language is checked or unchecked, the OnClickCheck event handler iterates the items list and updates the Tag property with the new bitfield value (see Figure 5).
// Re-evaluate bitfield whenever a language is checked
// or unchecked.
procedure TForm1.CheckListBox1ClickCheck(Sender: TObject);
var
i : Integer;
begin
with CheckListBox1 do begin
Tag := 0; // Clear Result.
// Iterate list and set checked bits in Tag property.
for i := 0 to Items.Count - 1 do
if Checked then
Tag := Tag or (1 shl i);
end;
// Refresh Filter.
Table1.Filtered := (RadioGroup1.ItemIndex > 0);
end;
Figure 5: The OnClickCheck event handler.
The Filtered property of the table is then set to True or False, depending on the selection in the RadioGroup. This will refresh the table and update the grid contents to reflect the new selection.
Changing the filter criteria in the RadioGroup will fire its OnClick event handler (see Figure 6). This handler will assign the appropriate event handler for the table's OnFilterRecord event, and set the table's Filtered property appropriately.
procedure TForm1.RadioGroup1Click(Sender: TObject);
begin
with RadioGroup1 do
case ItemIndex of
// If Filtered is False, the event handler is
// irrelevant as it will not be called.
0: Table1.Filtered := False;
1: begin
Table1.OnFilterRecord := FilterAny;
Table1.Filtered := True;
end;
2: begin
Table1.OnFilterRecord := FilterAll;
Table1.Filtered := True;
end;
end;
end;
Figure 6: Setting the correct filter event handler.
The FilterAny event handler performs a Boolean and with the bitfield value contained in the CheckListBox's Tag property. If the result is non-zero, at least one of the selected languages is contained in the table's Languages field. When cast to a LongBool, this non-zero value will return True, and the record will be accepted by the filter:
// Accept records containing any of the selected languages.
procedure TForm1.FilterAny(DataSet: TDataSet;
var Accept: Boolean);
begin
Accept := LongBool(CheckListBox1.Tag and
Table1.FieldbyName('Languages').AsInteger);
end;
The FilterAll event handler also performs a Boolean and operation, but instead of assessing if the result is True or False, the numeric result is compared with the bitfield value. If the two numbers are identical, all the selected languages are contained in the Languages field, and the record is selected:
// Only accept records that contain all selected languages.
procedure TForm1.FilterAll(DataSet: TDataSet;
var Accept: Boolean);
begin
Accept := (CheckListBox1.Tag and
Table1.FieldbyName('Languages').AsInteger =
CheckListBox1.Tag) and (CheckListBox1.Tag > 0);
end;
When writing code, it is often useful to use the Tag property of components for storage of arbitrary values, as the storage is essentially free. However, in this instance, it created a small problem. As the form closes, the OnFilterRecord event was being fired. The two OnFilterRecord event handlers both refer to the Tag property of the CheckListBox. The CheckListBox had already been destroyed, resulting in an Access Violation error. A line in the form's OnCloseQuery event handler solves the problem by unassigning any OnFilterRecord event handler from the table. An alternative is to use a variable for the bitfield value:
procedure TForm1.FormCloseQuery(Sender: TObject;
var CanClose: Boolean);
begin
Table1.OnFilterRecord := nil;
end;
Do It Once
The routines we've discussed provide a straightforward mechanism for storing and retrieving an integer representing the selected items in a list - specifically a CheckListBox, in this example. If we wish to store this information in a table, there still remains the issue of calling these functions from the correct event handlers - typically, a DataSource.OnChange to update the CheckListBox, and a Table.BeforePost event to save the changes to the table. There is also the issue of informing the table that the CheckListBox contents have changed. Adding this small piece of code wherever it's necessary to store the checked item status can become tedious.
In true Delphi fashion, the cleaner alternative is to create a data-aware descendant of TCheckListBox that incorporates the previous routines. This way, the mechanics of storage and keeping the CheckListBox synchronized with the table can be written once and used code-free.
TSgDbCheckListBox
The TSgDbCheckListBox component is a descendant of TCheckListBox that adds DataSource and DataField properties to the standard TCheckListBox. It contains the functionality to store and retrieve the selected items status, and as a nicety, contains a procedure to ensure the selected DataField is of ftInteger type. (Making a standard component data-aware is described in the Delphi developer's guide and is not discussed in detail here.)
The DataField selected to store the bitfield must be of type Integer, and, to track 31 items, it cannot be a Shortint. The SetDataField procedure calls the CheckFieldType procedure. If the selected field is not the right type, an EInvalidFieldType exception is raised. This procedure is also called when the DataSet becomes active (see Figure 7).
type
EInvalidFieldType = class(Exception);
// Ensure selected field is of Type FtInteger; If it is any
// other type, an EInvalidFieldType exception is raised.
procedure TSgDbCheckListBox.CheckFieldType(
const Value: string);
var
FieldType : TFieldType;
begin
if (Value <> '') and
(FDataLink <> nil) and
(FDataLink.DataSet <> nil) and
(FDataLink.DataSet.Active) then
begin
FieldType :=
FDataLink.DataSet.FieldByName(Value).DataType;
if FieldType <> ftInteger then
raise EInvalidFieldType.Create(
'TSgDbCheckListBox.DataField must be of type ftInteger');
end;
end;
Figure 7: Making sure that the selected datafield is an integer type.
Most of the code is fairly standard for making a component data-aware. What we need to do is make sure that when the record changes, the checkmarks reflect the selected items, and that when the user changes the selection in the CheckListBox, the table is updated. When a record changes in a DataSet, it fires the DataSource OnDataChange event, which is relayed to our component via the TFieldDataLink. The handler calls the SetCheckMarks procedure, which updates the checkmarks as described in Figure 8.
procedure TSgDbCheckListBox.DataChange(Sender: TObject);
begin
if FDataLink.Field <> nil then
SetCheckMarks;
end;
procedure TSgDbCheckListBox.SetCheckMarks;
var
i : Integer;
begin
FDataLink.OnDataChange := nil;
for i := 0 to Items.Count - 1 do
if (FDataLink.Field.Value and
(1 shl i)) = (1 Shl i) then
Checked := True
else
Checked := False;
FDataLink.OnDataChange := DataChange;
end;
Figure 8: Setting the Checkmarks when the data changes.
The UpdateData event handler is provided for the TFieldDataLink's OnUpdateData event. This iterates the items in the CheckListBox to build the bitfield, and assigns the result to the DataField's Value property (see Figure 9).
procedure TSgDbCheckListBox.UpdateData(Sender: TObject);
var
i : Integer;
Value : Integer;
begin
Value := 0;
for i := 0 to Items.Count - 1 do
if Checked then
Value := Value or ((1 shl i));
FDataLink.Field.Value := Value;
end;
Figure 9: Creating a bitfield for storage.
Putting It All Together
Figure 10 shows an extended version of the filter demonstration.
Figure 10: An extended version of the filter demonstration.
A TSgDbCheckListBox component has been placed onto the form. Its DataSource property is set to DataSource1, and its DataField is set to the Languages field of the table. The Items property is initialized to the same list of languages as the language selection CheckListBox. (In the spirit of reuse, the items were pasted in from the other CheckListBox.)
When the program is run, the filter section works as before, and now the SgDbCheckListBox displays the languages spoken by the selected person. The languages spoken may be edited by checking and un-checking the selections and posting the record. Notice that the table automatically goes into edit mode when a selection is changed.
Extending the Component
This component uses a 32-bit integer for storage, and provides storage for a total of 31 items. Although this is fine for most visual lists, as in user-interface terms, multiple selection of more than this number of items in a single control can be a little daunting, and it's not easy to display all selected items without having to scroll. However, if more items are required, the component may be extended by adding a second (or third) DataField, and breaking the Items list into groups of 31. For a truly dynamic storage system, the items should be grouped into a collection of seven-item groups, with the result for each group being cast as a char and concatenated into a string for storage to the table.
Another enhancement is the addition of a Lookup Source and Lookup Field so the list items may be provided from an external table.
Wrapping Up
It's easy to represent the selected status of multi-selection lists as an integer, and the techniques described here can be readily adapted to other list-type classes, both visual and non visual. As always, a little bit of planning can save a great deal of cutting and pasting.
The files referenced in this article are available for download.
Steve Griffiths has been programming with x-base languages for over 20 years, switching to Delphi as his primary development tool from version 1.0. He is currently working for NuMedics Inc., writing medical software with an emphasis on diabetes and disease management. He can be reached via e-mail at mailto:[email protected].
Koja struktura podataka...(Access vs. MySQL)?