Tridion 2009 embedded metadata storage format in the broker

Question

I'm fairly new to Tridion and I have to implement functionality that will allow a content editor to create a component and assign multiple date ranges (available dates) to it. These will need to be queried from the broker to provide a search functionality.

Originally, this was only require a single start and end date and so were implemented as individual meta data fields.

I am proposing to use an embedded schema within the schema's 'available dates' metadata field to allow multiple start and end dates to be assigned.

However, as the field is now allowing multiple values, the data is stored in the broker as comma separated values in the 'KEY_STRING_VALUE' column rather than as a date value in the 'KEY_DATE_VALUE' column as it was when it was only allowed a single start and end values.

eg.
KEY_NAME | KEY_STRING_VALUE
end_date | 2012-04-30T13:41:00, 2012-06-30T13:41:00
start_date | 2012-04-21T13:41:00, 2012-06-01T13:41:00

This is now causing issues with my broker querying as I can no longer use simple query logic to retrieve the items I require for the search based on the dates.

Before I start to write C# logic to parse these comma separated dates and search based on those, I was wondering if anyone had had similar requirements/experiences in the past and had implemented this in a different way to reduce the amount of code parsing required and to use the broker querying to complete the search.

I'm developing this on Tridion 2009 but using the 5.3 Broker (for legacy reasons) so the query currently looks like this (for the single start/end dates):

query.SetCustomMetaQuery((KEY_NAME='end_date' AND KEY_DATE_VALUE>'" + startDateStr + "') AND (ITEM_ID IN(SELECT ITEM_ID FROM CUSTOM_META WHERE KEY_NAME='start_date' AND KEY_DATE_VALUE<'" + endDateStr + "')))";

Any help is greatly appreciated.

Zoba

I've never used the 5.3 Broker, does it allow the IN keyword? – Pd

Yep, the query I've added above has examples of the IN keyword being used which is great for single values, but the multiple values are no longer stored as dates, but a string of comma separated dates (as a string value) meaning I can no longer query based on date as I have previously. – Zoba

So it does, sorry I didn't spot that and it wouldn't help anyway, sorry! – Pd

I would also like to point out that this format has been changed in 2011; they are now separate database fields, I believe. Just something to consider if you are thinking about upgrading at some point... – Sordino

Thanks Peter - definitely worth bearing this in mind – Zoba

Answer 1

This is a complex scenario, as you will have to go throughout all the DCPs and parse those strings to determine if match the search criteria

There is a way you could convert that metadata (comma separated) in single values in the broker, but the name of the fields need to be different Range1, Range2, ...., RangeN You can do that with a deployer extension where you change the XML Structure of the package and convert each those strings in different values (1,2, .., n). This extension can take some time if you are not familiar with deployer extensions and doesn't solve 100% your scenario.

The problem of this is that you still have to apply several conditions for retrieve those values and there is always a limit you have to set (Versus the User that can add as may values as wants)

Sample:

query.SetCustomMetaQuery((KEY_NAME='end_date1'
query.SetCustomMetaQuery((KEY_NAME='end_date2'
query.SetCustomMetaQuery((KEY_NAME='end_date3'
query.SetCustomMetaQuery((KEY_NAME='end_date4'

Probably the fastest and easiest way to achieve that is instead to use an multi-value field, use different fields. I understand that is not the most generic scenario and there are Business Requirements implications but can simplify the development.

My previous comments are in the context of use only the Broker API, but you can take advantage of a search engine if is part of your architecture. You can index the Broker Database and massage the data. Using the Search Engine API you can extract the ids of the Components/Component Templates and use the Broker API to retrieve the proper information

Provencher

Thanks Miguel, there are definitely some good ideas there but I think the best (and easiest to implement) may well be the use of multiple fields rather than the multi-value one. Seems a simple solution but one I hadn't even considered. As long as this can handle the business requirements (to be confirmed) then it's highly likely I'll use that approach. – Zoba

Hi Mike: Glad was useful. I recommend to perform a quick test with the multiple fields scenario to ensure that works properly for multiple ranges of dates – Provencher

Answer 2

Just wanted to come back and give some details on how I finally approached this should anyone else face the same scenario.

I proposed the set number of fields to the client (as suggested by Miguel) but the client wasn't happy with that level of restriction.

Therefore, I ended up implementing the embeddable schema containing the start and end dates which gave most flexibility. However, limitations in the Broker API meant that I had to access the Broker DB directly - not ideal, but the client has agreed to the approach to get the functionality required. Obviously this would need to be revisited should any upgrades be made in the future.

All the processing of dates and the available periods were done in C# which means the performance of the solution is actually pretty good.

One thing that I did discover that caused some issues was that if you have multiple values for the field using the embedded schema (ie in this case, multiple start and end dates) then the meta data is stored in the KEY_STRING_VALUE column in the CUSTOM_META table. However, if you only have a single value in the field (i.e. one start and end date) then these are stored as dates in the KEY_DATE_VALUE column in the same way as if you'd just used single fields rather than an embeddable schema. It seems a sensible approach for Tridion to take but it serves to make it slightly more complicated when writing the queries and the parsing code!

Zoba