Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.

Koja struktura podataka...(Access vs. MySQL)?

[es] :: Baze podataka :: Koja struktura podataka...(Access vs. MySQL)?

[ Pregleda: 2925 | Odgovora: 8 ] > FB > Twit

Postavi temu Odgovori

Autor

Pretraga teme: Traži
Markiranje Štampanje RSS

mika
NBG-ML

Član broj: 55
Poruke: 640
*.privsav.co.yu



+1 Profil

icon Koja struktura podataka...(Access vs. MySQL)?27.09.2004. u 13:56 - pre 211 meseci
Imam jedan problem. U bazi postoji tabela koja treba da sadrži ENUM polje, i koje podržava multi-select, tj. da može da se izabere više od jedne opcije. Ako vam nije jasno, evo primera:

Code:

(x) podatak 1
(x) podatak 2
( ) podatak 3
( ) podatak 4
(x) podatak 5


Dakle, treba da izaberem više od jednog podatka, i da takav izbor sačuvam u bazu radi daljeg pregleda i obrade. E sad, znam da MySQL podržava takav tip podataka, ali problem je što bazu radim u Access-u, tako da ne znam šta da radim, tj. kakvu strukturu da koristim?

Pozdrav i hvala na odgovorima!
Bolje 100 godina biti milioner nego nedelju dana siromašak
(c) Alan ford
 
Odgovor na temu

JogyII

Član broj: 29257
Poruke: 623
*.SMIN.panline.net.



Profil

icon Re: Koja struktura podataka...(Access vs. MySQL)?27.09.2004. u 14:13 - pre 211 meseci
resenje koje radi na skoro bilo kojem RDBSM-u : napravis 5 polja koja se zovu
podatak1,podatak2,podatak3,podatak4,podatak5 koja su tipa boolean, i u njih upises selekciju, tako ne zavisis od tipa baze koju koristis




So Long, and Thanks for All the Fish


 
Odgovor na temu

mika
NBG-ML

Član broj: 55
Poruke: 640
*.privsav.co.yu



+1 Profil

icon Re: Koja struktura podataka...(Access vs. MySQL)?27.09.2004. u 14:23 - pre 211 meseci
Da, na to sam rešenje pomislio, ali šta ako imam 20 podataka od kojih mogu da biram nekoliko? Da li je to isto optimalno rešenje?
Bolje 100 godina biti milioner nego nedelju dana siromašak
(c) Alan ford
 
Odgovor na temu

_owl_

Član broj: 318
Poruke: 1043
*.vdial.verat.net



+3 Profil

icon Re: Koja struktura podataka...(Access vs. MySQL)?27.09.2004. u 15:13 - pre 211 meseci
Mozes da koristis CHAR (pa da simuliras enum) ili INTEGER pri cemu bi se svi izbori cuvali u jednom polju (redni broj bit-a predstavlja broj podatka a 1 ili 0 oznacavaju da li je on izabran ili nije). Ako koristis CHAR gubis prostor (ali nista sto je znacajno, samo proveravaj da li se u to polje unose validne vrednosti), resenje sa INT-om takodje ima svoje prednosti i mane (uglavnom u pogledu brzine kod kompleksnih upita).
Owl
 
Odgovor na temu

mika
NBG-ML

Član broj: 55
Poruke: 640
*.privsav.co.yu



+1 Profil

icon Re: Koja struktura podataka...(Access vs. MySQL)?27.09.2004. u 15:34 - pre 211 meseci
Ček, ček... Kako CHAR? Nije mi baš najjasnije?
Bolje 100 godina biti milioner nego nedelju dana siromašak
(c) Alan ford
 
Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.eqao.com



+79 Profil

icon Re: Koja struktura podataka...(Access vs. MySQL)?28.09.2004. u 14:03 - pre 211 meseci
Zaboravi na 5 polja u tabeli ili 20 ili bilo sta slicno. Takva tabela ne bi bila normalizovana i tme gubis sve prednosti relacionih baza. Ono sa CHAR ne valja, to ti je isto kao i 5 polja, samo sto umesto 5 polja imas jedan string sa pet podstringova, a i tesko je da se razume.

Ono sto tebi treba jesu DVE tabele, jedna tabela tblPitanja i druga tabela tblOdgovori. U tblPitanja smestis text pitanaj i PitanjeID (=PK). Za svako pitanje u tabeli tblOdgovori imaces onoliko rekrda koliko je izabrano za to pitanje. Tako se to cuva u bazi. Kako ces da napravis aplikaciju kojom ce korisnik da puni bazu (to jest tabele), to je drugo pitanje. Ovim dizajnom sebe ne ogranicavas na isti broj mogucih odgovora po pitanju. Mozes ako hoces da za svako pitanje imas razlicit broj ponudjenih odgovora, pa cak i da kontrolises koliko se maximalno sme odabrati odgovora iz ponudjenog seta. Veruj mi na rec, ako je baza pravilno dizajnirana, aplikacija se mnogo lakse pise i upotrebljivost sistema raste (fleksibilnost). Govorim ti iz licnog iskustva, jer me poprilicno placaju da izmedju ostalog pakujem u bazu studentske testove i odgovore, i jeste, imamo mnogo pitanja gde je dozvoljena multipna selekcija, i svako pitanje moze da ima neogranicen broj ponudjenih opcija, nezavisno jedno od drugog.

:-)
 
Odgovor na temu

mika
NBG-ML

Član broj: 55
Poruke: 640
*.privsav.co.yu



+1 Profil

icon Re: Koja struktura podataka...(Access vs. MySQL)?28.09.2004. u 14:14 - pre 211 meseci
U pravu si, poslušaću te, jer u principu za sada bi bilo dovoljno i pet polja, ali ko zna kakva situacija bude za godinu-dve dana, kada baza naraste? Uostalom, zar nije osnovna pretpostavka pri projektovanju relacionog sistema da baza može da raste neograničeno? To su nas naučili na prvom predavanju iz BP.

Hvala na predlogu i pozdrav!
Bolje 100 godina biti milioner nego nedelju dana siromašak
(c) Alan ford
 
Odgovor na temu

protech_v2
protech_v2
Novi Sad

Član broj: 12426
Poruke: 169
*.neobee.net.



+12 Profil

icon Re: Koja struktura podataka...(Access vs. MySQL)?27.10.2004. u 16:39 - pre 210 meseci
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]
 
Odgovor na temu

mika
NBG-ML

Član broj: 55
Poruke: 640
*.privsav.co.yu.



+1 Profil

icon Re: Koja struktura podataka...(Access vs. MySQL)?28.10.2004. u 08:25 - pre 210 meseci
Odličan info. Hvala! Samo će malo da prođe vremena dok ovo implementiram.
Bolje 100 godina biti milioner nego nedelju dana siromašak
(c) Alan ford
 
Odgovor na temu

[es] :: Baze podataka :: Koja struktura podataka...(Access vs. MySQL)?

[ Pregleda: 2925 | Odgovora: 8 ] > FB > Twit

Postavi temu Odgovori

Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.