2 * Copyright (C) 2013 Team XBMC
5 * This Program is free software; you can redistribute it and/or modify
6 * it under the terms of the GNU General Public License as published by
7 * the Free Software Foundation; either version 2, or (at your option)
10 * This Program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
15 * You should have received a copy of the GNU General Public License
16 * along with XBMC; see the file COPYING. If not, see
17 * <http://www.gnu.org/licenses/>.
21 #include "DatabaseQuery.h"
23 #include "XBDateTime.h"
24 #include "guilib/LocalizeStrings.h"
25 #include "utils/CharsetConverter.h"
26 #include "utils/StringUtils.h"
27 #include "utils/Variant.h"
28 #include "utils/XBMCTinyXML.h"
35 CDatabaseQueryRule::SEARCH_OPERATOR op;
39 static const operatorField operators[] = {
40 { "contains", CDatabaseQueryRule::OPERATOR_CONTAINS, 21400 },
41 { "doesnotcontain", CDatabaseQueryRule::OPERATOR_DOES_NOT_CONTAIN, 21401 },
42 { "is", CDatabaseQueryRule::OPERATOR_EQUALS, 21402 },
43 { "isnot", CDatabaseQueryRule::OPERATOR_DOES_NOT_EQUAL, 21403 },
44 { "startswith", CDatabaseQueryRule::OPERATOR_STARTS_WITH, 21404 },
45 { "endswith", CDatabaseQueryRule::OPERATOR_ENDS_WITH, 21405 },
46 { "greaterthan", CDatabaseQueryRule::OPERATOR_GREATER_THAN, 21406 },
47 { "lessthan", CDatabaseQueryRule::OPERATOR_LESS_THAN, 21407 },
48 { "after", CDatabaseQueryRule::OPERATOR_AFTER, 21408 },
49 { "before", CDatabaseQueryRule::OPERATOR_BEFORE, 21409 },
50 { "inthelast", CDatabaseQueryRule::OPERATOR_IN_THE_LAST, 21410 },
51 { "notinthelast", CDatabaseQueryRule::OPERATOR_NOT_IN_THE_LAST, 21411 },
52 { "true", CDatabaseQueryRule::OPERATOR_TRUE, 20122 },
53 { "false", CDatabaseQueryRule::OPERATOR_FALSE, 20424 },
54 { "between", CDatabaseQueryRule::OPERATOR_BETWEEN, 21456 }
57 static const size_t NUM_OPERATORS = sizeof(operators) / sizeof(operatorField);
59 #define RULE_VALUE_SEPARATOR " / "
61 CDatabaseQueryRule::CDatabaseQueryRule()
64 m_operator = OPERATOR_CONTAINS;
67 bool CDatabaseQueryRule::Load(const TiXmlNode *node, const std::string &encoding /* = "UTF-8" */)
72 const TiXmlElement *element = node->ToElement();
77 // <rule field="Genre" operator="contains">parameter</rule>
78 // where parameter can either be a string or a list of
79 // <value> tags containing a string
80 const char *field = element->Attribute("field");
81 const char *oper = element->Attribute("operator");
82 if (field == NULL || oper == NULL)
85 m_field = TranslateField(field);
86 m_operator = TranslateOperator(oper);
88 if (m_operator == OPERATOR_TRUE || m_operator == OPERATOR_FALSE)
91 const TiXmlNode *parameter = element->FirstChild();
92 if (parameter == NULL)
95 if (parameter->Type() == TiXmlNode::TINYXML_TEXT)
97 CStdString utf8Parameter;
98 if (encoding.empty()) // utf8
99 utf8Parameter = parameter->ValueStr();
101 g_charsetConverter.ToUtf8(encoding, parameter->ValueStr(), utf8Parameter);
103 if (!utf8Parameter.empty())
104 m_parameter.push_back(utf8Parameter);
106 else if (parameter->Type() == TiXmlNode::TINYXML_ELEMENT)
108 const TiXmlNode *valueNode = element->FirstChild("value");
109 while (valueNode != NULL)
111 const TiXmlNode *value = valueNode->FirstChild();
112 if (value != NULL && value->Type() == TiXmlNode::TINYXML_TEXT)
114 CStdString utf8Parameter;
115 if (encoding.empty()) // utf8
116 utf8Parameter = value->ValueStr();
118 g_charsetConverter.ToUtf8(encoding, value->ValueStr(), utf8Parameter);
120 if (!utf8Parameter.empty())
121 m_parameter.push_back(utf8Parameter);
124 valueNode = valueNode->NextSibling("value");
133 bool CDatabaseQueryRule::Load(const CVariant &obj)
135 if (!obj.isObject() ||
136 !obj.isMember("field") || !obj["field"].isString() ||
137 !obj.isMember("operator") || !obj["operator"].isString())
140 m_field = TranslateField(obj["field"].asString().c_str());
141 m_operator = TranslateOperator(obj["operator"].asString().c_str());
143 if (m_operator == OPERATOR_TRUE || m_operator == OPERATOR_FALSE)
146 if (!obj.isMember("value") || (!obj["value"].isString() && !obj["value"].isArray()))
149 const CVariant &value = obj["value"];
150 if (value.isString())
151 m_parameter.push_back(value.asString());
152 else if (value.isArray())
154 for (CVariant::const_iterator_array val = value.begin_array(); val != value.end_array(); val++)
156 if (val->isString() && !val->asString().empty())
157 m_parameter.push_back(val->asString());
159 if (m_parameter.empty())
160 m_parameter.push_back("");
168 bool CDatabaseQueryRule::Save(TiXmlNode *parent) const
170 if (parent == NULL || (m_parameter.empty() && m_operator != OPERATOR_TRUE && m_operator != OPERATOR_FALSE))
173 TiXmlElement rule("rule");
174 rule.SetAttribute("field", TranslateField(m_field).c_str());
175 rule.SetAttribute("operator", TranslateOperator(m_operator).c_str());
177 for (vector<CStdString>::const_iterator it = m_parameter.begin(); it != m_parameter.end(); it++)
179 TiXmlElement value("value");
180 TiXmlText text(it->c_str());
181 value.InsertEndChild(text);
182 rule.InsertEndChild(value);
185 parent->InsertEndChild(rule);
190 bool CDatabaseQueryRule::Save(CVariant &obj) const
192 if (obj.isNull() || (m_parameter.empty() && m_operator != OPERATOR_TRUE && m_operator != OPERATOR_FALSE))
195 obj["field"] = TranslateField(m_field);
196 obj["operator"] = TranslateOperator(m_operator);
198 obj["value"] = CVariant(CVariant::VariantTypeArray);
199 for (vector<CStdString>::const_iterator it = m_parameter.begin(); it != m_parameter.end(); it++)
200 obj["value"].push_back(*it);
205 CDatabaseQueryRule::SEARCH_OPERATOR CDatabaseQueryRule::TranslateOperator(const char *oper)
207 for (unsigned int i = 0; i < NUM_OPERATORS; i++)
208 if (StringUtils::EqualsNoCase(oper, operators[i].string)) return operators[i].op;
209 return OPERATOR_CONTAINS;
212 CStdString CDatabaseQueryRule::TranslateOperator(SEARCH_OPERATOR oper)
214 for (unsigned int i = 0; i < NUM_OPERATORS; i++)
215 if (oper == operators[i].op) return operators[i].string;
219 CStdString CDatabaseQueryRule::GetLocalizedOperator(SEARCH_OPERATOR oper)
221 for (unsigned int i = 0; i < NUM_OPERATORS; i++)
222 if (oper == operators[i].op) return g_localizeStrings.Get(operators[i].localizedString);
223 return g_localizeStrings.Get(16018);
226 void CDatabaseQueryRule::GetAvailableOperators(std::vector<std::string> &operatorList)
228 for (unsigned int index = 0; index < NUM_OPERATORS; index++)
229 operatorList.push_back(operators[index].string);
232 CStdString CDatabaseQueryRule::GetParameter() const
234 return StringUtils::JoinString(m_parameter, RULE_VALUE_SEPARATOR);
237 void CDatabaseQueryRule::SetParameter(const CStdString &value)
240 StringUtils::SplitString(value, RULE_VALUE_SEPARATOR, m_parameter);
243 void CDatabaseQueryRule::SetParameter(const std::vector<CStdString> &values)
245 m_parameter.assign(values.begin(), values.end());
248 CStdString CDatabaseQueryRule::ValidateParameter(const CStdString ¶meter) const
250 if ((GetFieldType(m_field) == NUMERIC_FIELD ||
251 GetFieldType(m_field) == SECONDS_FIELD) && parameter.empty())
252 return "0"; // interpret empty fields as 0
256 CStdString CDatabaseQueryRule::FormatParameter(const CStdString &operatorString, const CStdString ¶m, const CDatabase &db, const CStdString &strType) const
258 CStdString parameter;
259 if (GetFieldType(m_field) == TEXTIN_FIELD)
261 CStdStringArray split;
262 StringUtils::SplitString(param, ",", split);
263 for (CStdStringArray::iterator itIn = split.begin(); itIn != split.end(); ++itIn)
265 if (!parameter.empty())
267 parameter += db.PrepareSQL("'%s'", StringUtils::Trim(*itIn).c_str());
269 parameter = " IN (" + parameter + ")";
272 parameter = db.PrepareSQL(operatorString.c_str(), ValidateParameter(param).c_str());
274 if (GetFieldType(m_field) == DATE_FIELD)
276 if (m_operator == OPERATOR_IN_THE_LAST || m_operator == OPERATOR_NOT_IN_THE_LAST)
277 { // translate time period
278 CDateTime date=CDateTime::GetCurrentDateTime();
280 span.SetFromPeriod(param);
282 parameter = db.PrepareSQL(operatorString.c_str(), date.GetAsDBDate().c_str());
288 CStdString CDatabaseQueryRule::GetOperatorString(SEARCH_OPERATOR op) const
290 CStdString operatorString;
291 if (GetFieldType(m_field) != TEXTIN_FIELD)
293 // the comparison piece
296 case OPERATOR_CONTAINS:
297 operatorString = " LIKE '%%%s%%'"; break;
298 case OPERATOR_DOES_NOT_CONTAIN:
299 operatorString = " LIKE '%%%s%%'"; break;
300 case OPERATOR_EQUALS:
301 if (GetFieldType(m_field) == NUMERIC_FIELD || GetFieldType(m_field) == SECONDS_FIELD)
302 operatorString = " = %s";
304 operatorString = " LIKE '%s'";
306 case OPERATOR_DOES_NOT_EQUAL:
307 if (GetFieldType(m_field) == NUMERIC_FIELD || GetFieldType(m_field) == SECONDS_FIELD)
308 operatorString = " != %s";
310 operatorString = " LIKE '%s'";
312 case OPERATOR_STARTS_WITH:
313 operatorString = " LIKE '%s%%'"; break;
314 case OPERATOR_ENDS_WITH:
315 operatorString = " LIKE '%%%s'"; break;
317 case OPERATOR_GREATER_THAN:
318 case OPERATOR_IN_THE_LAST:
319 operatorString = " > ";
320 if (GetFieldType(m_field) == NUMERIC_FIELD || GetFieldType(m_field) == SECONDS_FIELD)
321 operatorString += "%s";
323 operatorString += "'%s'";
325 case OPERATOR_BEFORE:
326 case OPERATOR_LESS_THAN:
327 case OPERATOR_NOT_IN_THE_LAST:
328 operatorString = " < ";
329 if (GetFieldType(m_field) == NUMERIC_FIELD || GetFieldType(m_field) == SECONDS_FIELD)
330 operatorString += "%s";
332 operatorString += "'%s'";
335 operatorString = " = 1"; break;
337 operatorString = " = 0"; break;
342 return operatorString;
345 CStdString CDatabaseQueryRule::GetWhereClause(const CDatabase &db, const CStdString& strType) const
347 SEARCH_OPERATOR op = GetOperator(strType);
349 CStdString operatorString = GetOperatorString(op);
351 if (op == OPERATOR_DOES_NOT_CONTAIN || op == OPERATOR_FALSE ||
352 (op == OPERATOR_DOES_NOT_EQUAL && GetFieldType(m_field) != NUMERIC_FIELD && GetFieldType(m_field) != SECONDS_FIELD))
355 // boolean operators don't have any values in m_parameter, they work on the operator
356 if (m_operator == OPERATOR_FALSE || m_operator == OPERATOR_TRUE)
357 return GetBooleanQuery(negate, strType);
359 // The BETWEEN operator is handled special
360 if (op == OPERATOR_BETWEEN)
362 if (m_parameter.size() != 2)
365 FIELD_TYPE fieldType = GetFieldType(m_field);
366 if (fieldType == NUMERIC_FIELD)
367 return db.PrepareSQL("CAST(%s as DECIMAL(5,1)) BETWEEN %s AND %s", GetField(m_field, strType).c_str(), m_parameter[0].c_str(), m_parameter[1].c_str());
368 else if (fieldType == SECONDS_FIELD)
369 return db.PrepareSQL("CAST(%s as INTEGER) BETWEEN %s AND %s", GetField(m_field, strType).c_str(), m_parameter[0].c_str(), m_parameter[1].c_str());
371 return db.PrepareSQL("%s BETWEEN '%s' AND '%s'", GetField(m_field, strType).c_str(), m_parameter[0].c_str(), m_parameter[1].c_str());
374 // now the query parameter
375 CStdString wholeQuery;
376 for (vector<CStdString>::const_iterator it = m_parameter.begin(); it != m_parameter.end(); ++it)
378 CStdString query = "(" + FormatWhereClause(negate, operatorString, *it, db, strType) + ")";
380 if (it+1 != m_parameter.end())
389 CStdString CDatabaseQueryRule::FormatWhereClause(const CStdString &negate, const CStdString &oper, const CStdString ¶m,
390 const CDatabase &db, const CStdString &strType) const
392 CStdString parameter = FormatParameter(oper, param, db, strType);
398 if (GetFieldType(m_field) == NUMERIC_FIELD)
399 fmt = "CAST(%s as DECIMAL(5,1))";
400 else if (GetFieldType(m_field) == SECONDS_FIELD)
401 fmt = "CAST(%s as INTEGER)";
403 query = StringUtils::Format(fmt.c_str(), GetField(m_field,strType).c_str());
404 query += negate + parameter;
406 // special case for matching parameters in fields that might be either empty or NULL.
407 if (( param.empty() && negate.empty() ) ||
408 ( !param.empty() && !negate.empty() ))
409 query += " OR " + GetField(m_field,strType) + " IS NULL";
412 if (query.Equals(negate + parameter))
417 CDatabaseQueryRuleCombination::CDatabaseQueryRuleCombination()
418 : m_type(CombinationAnd)
421 void CDatabaseQueryRuleCombination::clear()
423 m_combinations.clear();
425 m_type = CombinationAnd;
428 CStdString CDatabaseQueryRuleCombination::GetWhereClause(const CDatabase &db, const CStdString& strType) const
430 CStdString rule, currentRule;
432 // translate the combinations into SQL
433 for (CDatabaseQueryRuleCombinations::const_iterator it = m_combinations.begin(); it != m_combinations.end(); ++it)
435 if (it != m_combinations.begin())
436 rule += m_type == CombinationAnd ? " AND " : " OR ";
437 rule += "(" + (*it)->GetWhereClause(db, strType) + ")";
440 // translate the rules into SQL
441 for (CDatabaseQueryRules::const_iterator it = m_rules.begin(); it != m_rules.end(); ++it)
444 rule += m_type == CombinationAnd ? " AND " : " OR ";
446 CStdString currentRule = (*it)->GetWhereClause(db, strType);
447 // if we don't get a rule, we add '1' or '0' so the query is still valid and doesn't fail
448 if (currentRule.empty())
449 currentRule = m_type == CombinationAnd ? "'1'" : "'0'";
457 bool CDatabaseQueryRuleCombination::Load(const CVariant &obj, const IDatabaseQueryRuleFactory *factory)
459 if (!obj.isObject() && !obj.isArray())
465 if (obj.isMember("and") && obj["and"].isArray())
467 m_type = CombinationAnd;
470 else if (obj.isMember("or") && obj["or"].isArray())
472 m_type = CombinationOr;
481 for (CVariant::const_iterator_array it = child.begin_array(); it != child.end_array(); it++)
486 if (it->isMember("and") || it->isMember("or"))
488 boost::shared_ptr<CDatabaseQueryRuleCombination> combo(factory->CreateCombination());
489 if (combo && combo->Load(*it, factory))
490 m_combinations.push_back(combo);
494 boost::shared_ptr<CDatabaseQueryRule> rule(factory->CreateRule());
495 if (rule && rule->Load(*it))
496 m_rules.push_back(rule);
503 bool CDatabaseQueryRuleCombination::Save(TiXmlNode *parent) const
505 for (CDatabaseQueryRules::const_iterator it = m_rules.begin(); it != m_rules.end(); ++it)
510 bool CDatabaseQueryRuleCombination::Save(CVariant &obj) const
512 if (!obj.isObject() || (m_combinations.empty() && m_rules.empty()))
515 CVariant comboArray(CVariant::VariantTypeArray);
516 if (!m_combinations.empty())
518 for (CDatabaseQueryRuleCombinations::const_iterator combo = m_combinations.begin(); combo != m_combinations.end(); combo++)
520 CVariant comboObj(CVariant::VariantTypeObject);
521 if ((*combo)->Save(comboObj))
522 comboArray.push_back(comboObj);
526 if (!m_rules.empty())
528 for (CDatabaseQueryRules::const_iterator rule = m_rules.begin(); rule != m_rules.end(); rule++)
530 CVariant ruleObj(CVariant::VariantTypeObject);
531 if ((*rule)->Save(ruleObj))
532 comboArray.push_back(ruleObj);
536 obj[TranslateCombinationType()] = comboArray;
541 std::string CDatabaseQueryRuleCombination::TranslateCombinationType() const
543 return m_type == CombinationAnd ? "and" : "or";