Query by List>>
Brimbox allows records to be stored in lists by setting bits in the
list_string field. 2000 lists for each layout are allowed.
To query by a list requires the use of these PostgreSQL functions installed with the database, along with knowledge of the list number and row_type. There are three Postgres functions for querying by lists installed with the Brimbox database. They are:
bb_list_unset(bit, integer)– Sets the list bit to zero or low regardless of current value.
bb_list_set(bit, integer)– Sets the list bit to one or high or low regardless of current value.
bb_list(bit, integer)– Tests if the list bit is high (or low).
These functions take two parameters. The first is a type bit, and should be set to
list_string, the name of the Brimbox
data_table column containing the bit values. The second is the list number which is expressed as an integer and can be found in the “Rename List, Update Description and Find List Number” area of the “Manage Lists” module.
So once you have the list number of “4” and the layout of “B” which corresponds to the row_type of “2” you can select all the rows in a list by:
SELECT * FROM data_table WHERE bb_list(list_string, 4) = 1 AND row_type = 2
Or not in a list by:
SELECT * FROM data_table WHERE bb_list(list_string, 4) = 0 AND row_type = 2
Likewise to update and set a list flag to high or 1 for a particular row, say 43:
UPDATE data_table SET list_string = bb_list_set(list_string, 4) WHERE id = 43 AND row_type = 2
Or to reset a list to low or 0 for a particular row, say 43:
UPDATE data_table SET list_string = bb_list_unset(list_string, 4) WHERE id = 43 AND row_type = 2
row_type is redundant and could be left out of either of the preceding SQL statements when
id is used.