KBEC-00098 - Breaking up lengthy property values to accommodate search

Description

There is a property on a Resource that is a long, space-separated list.

For instance, a Resource has a custom property named "components" that contains a long list of component names. One of these names is "buildim210" and another is "wlan300".

In the search tab, look for Resources that have a custom property named "components" that "contains" a value of "buildim210" and it returns the Resource as expected.

Doing the exact same thing, except searching for "wlan300", which is definitely in the list, the search comes back empty.

Discussion

There are several considerations regarding how databases store text:

1. Limits on how many characters/bytes can be stored in text fields
2. Limits on how many characters/bytes of a text field can be indexed
3. Behavior when these limits are exceeded at runtime

For example:

1. Most databases allow up to 4K bytes to be stored in a text column. MySQL is the most flexible here.
2. All databases now support LOB (Large OBject) columns that can store much more. Some databases store the data for these columns in the file system, with attendant performance problems.
3. The contents of LOB columns typically must be read out separately from the result set itself.
4. No database allows a standard index to be created on the contents of a LOB column.
5. Oracle has limits on the type of queries it supports when LOB columns are being fetched. In particular, it does not allow scrolling queries with LOB contents in the result set. Scrolling queries are important for scalability because they do not load the entire result set into memory at once.
6. SQL Server imposes a limit on index entries of 900 bytes/450 characters and will not allow the data in the columns in an index to exceed that amount.

To accommodate all of this, ElectricFlow stores long strings as follows:

1. The first 450 characters (900 bytes) are stored directly in an indexed column in the table.
2. If the string is longer than that, the entire contents are stored in a LOB column in a separate table.
3. A foreign key in the first table points to the LOB row in the LOB table.

If you are not using SQL Server...

The issue is that the Flow schema definition is done statically with Hibernate, without regard to the particular database used at runtime, so one size has to fit all, unfortunately. It would be nice to have a more database-specific approach to how this is done.

Solution

Make each entry in the components list its own property. To be searchable, it MUST be a TOP-LEVEL property, so you cannot put them into a property sheet. However, you can name it something "unique" so they do not collide with other properties, like componentOnResource- buildim210.

You are now able to search to see if that top-level property is non-null.