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() && !value.asString().empty())
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());
166 bool CDatabaseQueryRule::Save(TiXmlNode *parent) const
168 if (parent == NULL || (m_parameter.empty() && m_operator != OPERATOR_TRUE && m_operator != OPERATOR_FALSE))
171 TiXmlElement rule("rule");
172 rule.SetAttribute("field", TranslateField(m_field).c_str());
173 rule.SetAttribute("operator", TranslateOperator(m_operator).c_str());
175 for (vector<CStdString>::const_iterator it = m_parameter.begin(); it != m_parameter.end(); it++)
177 TiXmlElement value("value");
178 TiXmlText text(it->c_str());
179 value.InsertEndChild(text);
180 rule.InsertEndChild(value);
183 parent->InsertEndChild(rule);
188 bool CDatabaseQueryRule::Save(CVariant &obj) const
190 if (obj.isNull() || (m_parameter.empty() && m_operator != OPERATOR_TRUE && m_operator != OPERATOR_FALSE))
193 obj["field"] = TranslateField(m_field);
194 obj["operator"] = TranslateOperator(m_operator);
196 obj["value"] = CVariant(CVariant::VariantTypeArray);
197 for (vector<CStdString>::const_iterator it = m_parameter.begin(); it != m_parameter.end(); it++)
198 obj["value"].push_back(*it);
203 CDatabaseQueryRule::SEARCH_OPERATOR CDatabaseQueryRule::TranslateOperator(const char *oper)
205 for (unsigned int i = 0; i < NUM_OPERATORS; i++)
206 if (StringUtils::EqualsNoCase(oper, operators[i].string)) return operators[i].op;
207 return OPERATOR_CONTAINS;
210 CStdString CDatabaseQueryRule::TranslateOperator(SEARCH_OPERATOR oper)
212 for (unsigned int i = 0; i < NUM_OPERATORS; i++)
213 if (oper == operators[i].op) return operators[i].string;
217 CStdString CDatabaseQueryRule::GetLocalizedOperator(SEARCH_OPERATOR oper)
219 for (unsigned int i = 0; i < NUM_OPERATORS; i++)
220 if (oper == operators[i].op) return g_localizeStrings.Get(operators[i].localizedString);
221 return g_localizeStrings.Get(16018);
224 void CDatabaseQueryRule::GetAvailableOperators(std::vector<std::string> &operatorList)
226 for (unsigned int index = 0; index < NUM_OPERATORS; index++)
227 operatorList.push_back(operators[index].string);
230 CStdString CDatabaseQueryRule::GetParameter() const
232 return StringUtils::JoinString(m_parameter, RULE_VALUE_SEPARATOR);
235 void CDatabaseQueryRule::SetParameter(const CStdString &value)
238 StringUtils::SplitString(value, RULE_VALUE_SEPARATOR, m_parameter);
241 void CDatabaseQueryRule::SetParameter(const std::vector<CStdString> &values)
243 m_parameter.assign(values.begin(), values.end());
246 CStdString CDatabaseQueryRule::FormatParameter(const CStdString &operatorString, const CStdString ¶m, const CDatabase &db, const CStdString &strType) const
248 CStdString parameter;
249 if (GetFieldType(m_field) == TEXTIN_FIELD)
251 CStdStringArray split;
252 StringUtils::SplitString(param, ",", split);
253 for (CStdStringArray::iterator itIn = split.begin(); itIn != split.end(); ++itIn)
255 if (!parameter.IsEmpty())
257 parameter += db.PrepareSQL("'%s'", (*itIn).Trim().c_str());
259 parameter = " IN (" + parameter + ")";
262 parameter = db.PrepareSQL(operatorString.c_str(), param.c_str());
264 if (GetFieldType(m_field) == DATE_FIELD)
266 if (m_operator == OPERATOR_IN_THE_LAST || m_operator == OPERATOR_NOT_IN_THE_LAST)
267 { // translate time period
268 CDateTime date=CDateTime::GetCurrentDateTime();
270 span.SetFromPeriod(param);
272 parameter = db.PrepareSQL(operatorString.c_str(), date.GetAsDBDate().c_str());
278 CStdString CDatabaseQueryRule::GetOperatorString(SEARCH_OPERATOR op) const
280 CStdString operatorString;
281 if (GetFieldType(m_field) != TEXTIN_FIELD)
283 // the comparison piece
286 case OPERATOR_CONTAINS:
287 operatorString = " LIKE '%%%s%%'"; break;
288 case OPERATOR_DOES_NOT_CONTAIN:
289 operatorString = " LIKE '%%%s%%'"; break;
290 case OPERATOR_EQUALS:
291 if (GetFieldType(m_field) == NUMERIC_FIELD || GetFieldType(m_field) == SECONDS_FIELD)
292 operatorString = " = %s";
294 operatorString = " LIKE '%s'";
296 case OPERATOR_DOES_NOT_EQUAL:
297 if (GetFieldType(m_field) == NUMERIC_FIELD || GetFieldType(m_field) == SECONDS_FIELD)
298 operatorString = " != %s";
300 operatorString = " LIKE '%s'";
302 case OPERATOR_STARTS_WITH:
303 operatorString = " LIKE '%s%%'"; break;
304 case OPERATOR_ENDS_WITH:
305 operatorString = " LIKE '%%%s'"; break;
307 case OPERATOR_GREATER_THAN:
308 case OPERATOR_IN_THE_LAST:
309 operatorString = " > ";
310 if (GetFieldType(m_field) == NUMERIC_FIELD || GetFieldType(m_field) == SECONDS_FIELD)
311 operatorString += "%s";
313 operatorString += "'%s'";
315 case OPERATOR_BEFORE:
316 case OPERATOR_LESS_THAN:
317 case OPERATOR_NOT_IN_THE_LAST:
318 operatorString = " < ";
319 if (GetFieldType(m_field) == NUMERIC_FIELD || GetFieldType(m_field) == SECONDS_FIELD)
320 operatorString += "%s";
322 operatorString += "'%s'";
325 operatorString = " = 1"; break;
327 operatorString = " = 0"; break;
332 return operatorString;
335 CStdString CDatabaseQueryRule::GetWhereClause(const CDatabase &db, const CStdString& strType) const
337 SEARCH_OPERATOR op = GetOperator(strType);
339 CStdString operatorString = GetOperatorString(op);
341 if (op == OPERATOR_DOES_NOT_CONTAIN || op == OPERATOR_FALSE ||
342 (op == OPERATOR_DOES_NOT_EQUAL && GetFieldType(m_field) != NUMERIC_FIELD && GetFieldType(m_field) != SECONDS_FIELD))
345 // boolean operators don't have any values in m_parameter, they work on the operator
346 if (m_operator == OPERATOR_FALSE || m_operator == OPERATOR_TRUE)
347 return GetBooleanQuery(negate, strType);
349 // The BETWEEN operator is handled special
350 if (op == OPERATOR_BETWEEN)
352 if (m_parameter.size() != 2)
355 FIELD_TYPE fieldType = GetFieldType(m_field);
356 if (fieldType == NUMERIC_FIELD)
357 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());
358 else if (fieldType == SECONDS_FIELD)
359 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());
361 return db.PrepareSQL("%s BETWEEN '%s' AND '%s'", GetField(m_field, strType).c_str(), m_parameter[0].c_str(), m_parameter[1].c_str());
364 // now the query parameter
365 CStdString wholeQuery;
366 for (vector<CStdString>::const_iterator it = m_parameter.begin(); it != m_parameter.end(); ++it)
368 CStdString query = "(" + FormatWhereClause(negate, operatorString, *it, db, strType) + ")";
370 if (it+1 != m_parameter.end())
379 CStdString CDatabaseQueryRule::FormatWhereClause(const CStdString &negate, const CStdString &oper, const CStdString ¶m,
380 const CDatabase &db, const CStdString &strType) const
382 CStdString parameter = FormatParameter(oper, param, db, strType);
388 if (GetFieldType(m_field) == NUMERIC_FIELD)
389 fmt = "CAST(%s as DECIMAL(5,1))";
390 else if (GetFieldType(m_field) == SECONDS_FIELD)
391 fmt = "CAST(%s as INTEGER)";
393 query = StringUtils::Format(fmt.c_str(), GetField(m_field,strType).c_str());
394 query += negate + parameter;
397 if (query.Equals(negate + parameter))
402 CDatabaseQueryRuleCombination::CDatabaseQueryRuleCombination()
403 : m_type(CombinationAnd)
406 void CDatabaseQueryRuleCombination::clear()
408 m_combinations.clear();
410 m_type = CombinationAnd;
413 CStdString CDatabaseQueryRuleCombination::GetWhereClause(const CDatabase &db, const CStdString& strType) const
415 CStdString rule, currentRule;
417 // translate the combinations into SQL
418 for (CDatabaseQueryRuleCombinations::const_iterator it = m_combinations.begin(); it != m_combinations.end(); ++it)
420 if (it != m_combinations.begin())
421 rule += m_type == CombinationAnd ? " AND " : " OR ";
422 rule += "(" + (*it)->GetWhereClause(db, strType) + ")";
425 // translate the rules into SQL
426 for (CDatabaseQueryRules::const_iterator it = m_rules.begin(); it != m_rules.end(); ++it)
429 rule += m_type == CombinationAnd ? " AND " : " OR ";
431 CStdString currentRule = (*it)->GetWhereClause(db, strType);
432 // if we don't get a rule, we add '1' or '0' so the query is still valid and doesn't fail
433 if (currentRule.IsEmpty())
434 currentRule = m_type == CombinationAnd ? "'1'" : "'0'";
442 bool CDatabaseQueryRuleCombination::Load(const CVariant &obj, const IDatabaseQueryRuleFactory *factory)
444 if (!obj.isObject() && !obj.isArray())
450 if (obj.isMember("and") && obj["and"].isArray())
452 m_type = CombinationAnd;
455 else if (obj.isMember("or") && obj["or"].isArray())
457 m_type = CombinationOr;
466 for (CVariant::const_iterator_array it = child.begin_array(); it != child.end_array(); it++)
471 if (it->isMember("and") || it->isMember("or"))
473 boost::shared_ptr<CDatabaseQueryRuleCombination> combo(factory->CreateCombination());
474 if (combo && combo->Load(*it, factory))
475 m_combinations.push_back(combo);
479 boost::shared_ptr<CDatabaseQueryRule> rule(factory->CreateRule());
480 if (rule && rule->Load(*it))
481 m_rules.push_back(rule);
488 bool CDatabaseQueryRuleCombination::Save(TiXmlNode *parent) const
490 for (CDatabaseQueryRules::const_iterator it = m_rules.begin(); it != m_rules.end(); ++it)
495 bool CDatabaseQueryRuleCombination::Save(CVariant &obj) const
497 if (!obj.isObject() || (m_combinations.empty() && m_rules.empty()))
500 CVariant comboArray(CVariant::VariantTypeArray);
501 if (!m_combinations.empty())
503 for (CDatabaseQueryRuleCombinations::const_iterator combo = m_combinations.begin(); combo != m_combinations.end(); combo++)
505 CVariant comboObj(CVariant::VariantTypeObject);
506 if ((*combo)->Save(comboObj))
507 comboArray.push_back(comboObj);
511 if (!m_rules.empty())
513 for (CDatabaseQueryRules::const_iterator rule = m_rules.begin(); rule != m_rules.end(); rule++)
515 CVariant ruleObj(CVariant::VariantTypeObject);
516 if ((*rule)->Save(ruleObj))
517 comboArray.push_back(ruleObj);
521 obj[TranslateCombinationType()] = comboArray;
526 std::string CDatabaseQueryRuleCombination::TranslateCombinationType() const
528 return m_type == CombinationAnd ? "and" : "or";