C++ CDatabase::PrepareSQL方法代码示例

CStdString CDatabaseQueryRule::FormatParameter(const CStdString &operatorString, const CStdString &param, const CDatabase &db, const CStdString &strType) const
{
  CStdString parameter;
  if (GetFieldType(m_field) == TEXTIN_FIELD)
  {
    vector<string> split = StringUtils::Split(param, ",");
    for (vector<string>::iterator itIn = split.begin(); itIn != split.end(); ++itIn)
    {
      if (!parameter.empty())
        parameter += ",";
      parameter += db.PrepareSQL("'%s'", StringUtils::Trim(*itIn).c_str());
    }
    parameter = " IN (" + parameter + ")";
  }
  else
    parameter = db.PrepareSQL(operatorString.c_str(), ValidateParameter(param).c_str());

  if (GetFieldType(m_field) == DATE_FIELD)
  {
    if (m_operator == OPERATOR_IN_THE_LAST || m_operator == OPERATOR_NOT_IN_THE_LAST)
    { // translate time period
      CDateTime date=CDateTime::GetCurrentDateTime();
      CDateTimeSpan span;
      span.SetFromPeriod(param);
      date-=span;
      parameter = db.PrepareSQL(operatorString.c_str(), date.GetAsDBDate().c_str());
    }
  }
  return parameter;
}

std::string CDatabaseQueryRule::GetWhereClause(const CDatabase &db, const std::string& strType) const
{
  SEARCH_OPERATOR op = GetOperator(strType);

  std::string operatorString = GetOperatorString(op);
  std::string negate;
  if (op == OPERATOR_DOES_NOT_CONTAIN || op == OPERATOR_FALSE ||
     (op == OPERATOR_DOES_NOT_EQUAL && GetFieldType(m_field) != REAL_FIELD && GetFieldType(m_field) != NUMERIC_FIELD &&
      GetFieldType(m_field) != SECONDS_FIELD))
    negate = " NOT ";

  // boolean operators don't have any values in m_parameter, they work on the operator
  if (m_operator == OPERATOR_FALSE || m_operator == OPERATOR_TRUE)
    return GetBooleanQuery(negate, strType);

  // The BETWEEN operator is handled special
  if (op == OPERATOR_BETWEEN)
  {
    if (m_parameter.size() != 2)
      return "";

    FIELD_TYPE fieldType = GetFieldType(m_field);
    if (fieldType == REAL_FIELD)
      return db.PrepareSQL("%s BETWEEN %s AND %s", GetField(m_field, strType).c_str(), m_parameter[0].c_str(), m_parameter[1].c_str());
    else if (fieldType == NUMERIC_FIELD)
      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());
    else if (fieldType == SECONDS_FIELD)
      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());
    else
      return db.PrepareSQL("%s BETWEEN '%s' AND '%s'", GetField(m_field, strType).c_str(), m_parameter[0].c_str(), m_parameter[1].c_str());
  }

  // now the query parameter
  std::string wholeQuery;
  for (std::vector<std::string>::const_iterator it = m_parameter.begin(); it != m_parameter.end(); ++it)
  {
    std::string query = '(' + FormatWhereClause(negate, operatorString, *it, db, strType) + ')';

    if (it + 1 != m_parameter.end())
    {
      if (negate.empty())
        query += " OR ";
      else
        query += " AND ";
    }

    wholeQuery += query;
  }

  return wholeQuery;
}

bool CGUIDialogMediaFilter::GetMinMax(const std::string &table, const std::string &field, int &min, int &max, const CDatabase::Filter &filter /* = CDatabase::Filter() */)
{
  if (table.empty() || field.empty())
    return false;

  CDatabase *db = NULL;
  CDbUrl *dbUrl = NULL;
  if (m_mediaType == "movies" || m_mediaType == "tvshows" || m_mediaType == "episodes" || m_mediaType == "musicvideos")
  {
    CVideoDatabase *videodb = new CVideoDatabase();
    if (!videodb->Open())
    {
      delete videodb;
      return false;
    }

    db = videodb;
    dbUrl = new CVideoDbUrl();
  }
  else if (m_mediaType == "artists" || m_mediaType == "albums" || m_mediaType == "songs")
  {
    CMusicDatabase *musicdb = new CMusicDatabase();
    if (!musicdb->Open())
    {
      delete musicdb;
      return false;
    }

    db = musicdb;
    dbUrl = new CMusicDbUrl();
  }

  if (db == NULL || !db->IsOpen() || dbUrl == NULL)
  {
    delete db;
    delete dbUrl;
    return false;
  }

  CDatabase::Filter extFilter = filter;
  std::string strSQLExtra;
  if (!db->BuildSQL(m_dbUrl->ToString(), strSQLExtra, extFilter, strSQLExtra, *dbUrl))
  {
    delete db;
    delete dbUrl;
    return false;
  }

  std::string strSQL = "SELECT %s FROM %s ";

  min = static_cast<int>(strtol(db->GetSingleValue(db->PrepareSQL(strSQL, std::string("MIN(" + field + ")").c_str(), table.c_str()) + strSQLExtra).c_str(), NULL, 0));
  max = static_cast<int>(strtol(db->GetSingleValue(db->PrepareSQL(strSQL, std::string("MAX(" + field + ")").c_str(), table.c_str()) + strSQLExtra).c_str(), NULL, 0));

  db->Close();
  delete db;
  delete dbUrl;

  return true;
}

CStdString CSmartPlaylist::GetOrderClause(CDatabase &db)
{
  CStdString order;
  if (m_orderField != CSmartPlaylistRule::FIELD_NONE)
  {
    if (CSmartPlaylistRule::GetFieldType(m_orderField) == CSmartPlaylistRule::NUMERIC_FIELD)
      order.Format("ORDER BY 1*%s", CSmartPlaylistRule::GetDatabaseField(m_orderField,GetType()));
    else
      order = db.PrepareSQL("ORDER BY %s", CSmartPlaylistRule::GetDatabaseField(m_orderField,GetType()).c_str());
    if (!m_orderAscending)
      order += " DESC";
  }
  if (m_limit)
  {
    CStdString limit;
    limit.Format(" LIMIT %i", m_limit);
    order += limit;
  }
  return order;
}

CStdString CSmartPlaylistRule::GetWhereClause(CDatabase &db, const CStdString& strType)
{
  SEARCH_OPERATOR op = m_operator;
  if ((strType == "tvshows" || strType == "episodes") && m_field == FIELD_YEAR)
  { // special case for premiered which is a date rather than a year
    // TODO: SMARTPLAYLISTS do we really need this, or should we just make this field the premiered date and request a date?
    if (op == OPERATOR_EQUALS)
      op = OPERATOR_CONTAINS;
    else if (op == OPERATOR_DOES_NOT_EQUAL)
      op = OPERATOR_DOES_NOT_CONTAIN;
  }
  CStdString operatorString, negate;
  CStdString parameter;
  if (GetFieldType(m_field) == TEXTIN_FIELD)
  {
    CStdStringArray split;
    StringUtils::SplitString(m_parameter, ",", split);
    for (CStdStringArray::iterator it=split.begin(); it!=split.end(); ++it)
    {
      if (!parameter.IsEmpty())
        parameter += ",";
      parameter += db.PrepareSQL("'%s'", (*it).Trim().c_str());
    }
    parameter = " IN (" + parameter + ")";
    if (op == OPERATOR_DOES_NOT_EQUAL)
      negate = " NOT";
  }
  else
  {
    // the comparison piece
    switch (op)
    {
    case OPERATOR_CONTAINS:
      operatorString = " LIKE '%%%s%%'"; break;
    case OPERATOR_DOES_NOT_CONTAIN:
      negate = " NOT"; operatorString = " LIKE '%%%s%%'"; break;
    case OPERATOR_EQUALS:
      operatorString = " LIKE '%s'"; break;
    case OPERATOR_DOES_NOT_EQUAL:
      negate = " NOT"; operatorString = " LIKE '%s'"; break;
    case OPERATOR_STARTS_WITH:
      operatorString = " LIKE '%s%%'"; break;
    case OPERATOR_ENDS_WITH:
      operatorString = " LIKE '%%%s'"; break;
    case OPERATOR_AFTER:
    case OPERATOR_GREATER_THAN:
    case OPERATOR_IN_THE_LAST:
      operatorString = " > '%s'"; break;
    case OPERATOR_BEFORE:
    case OPERATOR_LESS_THAN:
    case OPERATOR_NOT_IN_THE_LAST:
      operatorString = " < '%s'"; break;
    case OPERATOR_TRUE:
      operatorString = " = 1"; break;
    case OPERATOR_FALSE:
      negate = " NOT "; operatorString = " = 0"; break;
    default:
      break;
    }

    parameter = db.PrepareSQL(operatorString.c_str(), m_parameter.c_str());
  }

  if (m_field == FIELD_LASTPLAYED || m_field == FIELD_AIRDATE)
  {
    if (m_operator == OPERATOR_IN_THE_LAST || m_operator == OPERATOR_NOT_IN_THE_LAST)
    { // translate time period
      CDateTime date=CDateTime::GetCurrentDateTime();
      CDateTimeSpan span;
      span.SetFromPeriod(m_parameter);
      date-=span;
      parameter = db.PrepareSQL(operatorString.c_str(), date.GetAsDBDate().c_str());
    }
  }
  else if (m_field == FIELD_TIME)
  { // translate time to seconds
    CStdString seconds; seconds.Format("%i", StringUtils::TimeStringToSeconds(m_parameter));
    parameter = db.PrepareSQL(operatorString.c_str(), seconds.c_str());
  }

  // now the query parameter
  CStdString query;
  if (strType == "songs")
  {
    if (m_field == FIELD_GENRE)
      query = negate + " ((strGenre" + parameter + ") or idSong IN (select idSong from genre,exgenresong where exgenresong.idGenre = genre.idGenre and genre.strGenre" + parameter + "))";
    else if (m_field == FIELD_ARTIST)
      query = negate + " ((strArtist" + parameter + ") or idSong IN (select idSong from artist,exartistsong where exartistsong.idArtist = artist.idArtist and artist.strArtist" + parameter + "))";
    else if (m_field == FIELD_ALBUMARTIST)
      query = negate + " (idalbum in (select idalbum from artist,album where album.idArtist=artist.idArtist and artist.strArtist" + parameter + ") or idalbum in (select idalbum from artist,exartistalbum where exartistalbum.idArtist = artist.idArtist and artist.strArtist" + parameter + "))";
    else if (m_field == FIELD_LASTPLAYED && (m_operator == OPERATOR_LESS_THAN || m_operator == OPERATOR_BEFORE || m_operator == OPERATOR_NOT_IN_THE_LAST))
      query = "lastPlayed is NULL or lastPlayed" + parameter;
  }
  else if (strType == "albums")
  {
    if (m_field == FIELD_GENRE)
      query = negate + " (idAlbum in (select song.idAlbum from song join genre on song.idGenre=genre.idGenre where genre.strGenre" + parameter + ") or "
              "idAlbum in (select song.idAlbum from song join exgenresong on song.idSong=exgenresong.idSong join genre on exgenresong.idGenre=genre.idGenre where genre.strGenre" + parameter + "))";
    else if (m_field == FIELD_ARTIST)
      query = negate + " (idAlbum in (select song.idAlbum from song join artist on song.idArtist=artist.idArtist where artist.strArtist" + parameter + ") or "
//.........这里部分代码省略.........

CStdString CSmartPlaylistRule::GetWhereClause(CDatabase &db, const CStdString& strType) const
{
  SEARCH_OPERATOR op = m_operator;
  if ((strType == "tvshows" || strType == "episodes") && m_field == FieldYear)
  { // special case for premiered which is a date rather than a year
    // TODO: SMARTPLAYLISTS do we really need this, or should we just make this field the premiered date and request a date?
    if (op == OPERATOR_EQUALS)
      op = OPERATOR_CONTAINS;
    else if (op == OPERATOR_DOES_NOT_EQUAL)
      op = OPERATOR_DOES_NOT_CONTAIN;
  }

  CStdString operatorString, negate;
  if (GetFieldType(m_field) == TEXTIN_FIELD)
  {
    if (op == OPERATOR_DOES_NOT_EQUAL)
      negate = " NOT";
  }
  else
  {
    // the comparison piece
    switch (op)
    {
    case OPERATOR_CONTAINS:
      operatorString = " LIKE '%%%s%%'"; break;
    case OPERATOR_DOES_NOT_CONTAIN:
      negate = " NOT"; operatorString = " LIKE '%%%s%%'"; break;
    case OPERATOR_EQUALS:
      operatorString = " LIKE '%s'"; break;
    case OPERATOR_DOES_NOT_EQUAL:
      negate = " NOT"; operatorString = " LIKE '%s'"; break;
    case OPERATOR_STARTS_WITH:
      operatorString = " LIKE '%s%%'"; break;
    case OPERATOR_ENDS_WITH:
      operatorString = " LIKE '%%%s'"; break;
    case OPERATOR_AFTER:
    case OPERATOR_GREATER_THAN:
    case OPERATOR_IN_THE_LAST:
      operatorString = " > '%s'"; break;
    case OPERATOR_BEFORE:
    case OPERATOR_LESS_THAN:
    case OPERATOR_NOT_IN_THE_LAST:
      operatorString = " < '%s'"; break;
    case OPERATOR_TRUE:
      operatorString = " = 1"; break;
    case OPERATOR_FALSE:
      negate = " NOT "; operatorString = " = 0"; break;
    default:
      break;
    }
  }

  // FieldInProgress does not have any values in m_parameter, it works on the operator
  if (m_field == FieldInProgress && (strType == "movies" || strType == "episodes"))
    return "idFile " + negate + " in (select idFile from bookmark where type = 1)";

  // now the query parameter
  CStdString wholeQuery;
  for (vector<CStdString>::const_iterator it = m_parameter.begin(); it != m_parameter.end(); /* it++ is done further down */)
  {
    CStdString parameter;
    if (GetFieldType(m_field) == TEXTIN_FIELD)
    {
      CStdStringArray split;
      StringUtils::SplitString(*it, ",", split);
      for (CStdStringArray::iterator itIn = split.begin(); itIn != split.end(); ++itIn)
      {
        if (!parameter.IsEmpty())
          parameter += ",";
        parameter += db.PrepareSQL("'%s'", (*itIn).Trim().c_str());
      }
      parameter = " IN (" + parameter + ")";
    }
    else
      parameter = db.PrepareSQL(operatorString.c_str(), it->c_str());

    if (GetFieldType(m_field) == DATE_FIELD)
    {
      if (m_operator == OPERATOR_IN_THE_LAST || m_operator == OPERATOR_NOT_IN_THE_LAST)
      { // translate time period
        CDateTime date=CDateTime::GetCurrentDateTime();
        CDateTimeSpan span;
        span.SetFromPeriod(*it);
        date-=span;
        parameter = db.PrepareSQL(operatorString.c_str(), date.GetAsDBDate().c_str());
      }
    }
    else if (m_field == FieldTime)
    { // translate time to seconds
      CStdString seconds; seconds.Format("%i", StringUtils::TimeStringToSeconds(*it));
      parameter = db.PrepareSQL(operatorString.c_str(), seconds.c_str());
    }

    CStdString query;
    CStdString table;
    if (strType == "songs")
    {
      table = "songview";

      if (m_field == FieldGenre)
//.........这里部分代码省略.........