Exemple #1
0
    private string AddLastImportFilter(RollSet roll_set, bool added_where)
    {
        if (roll_set == null)
        {
            return(null);
        }

        return(String.Format(" {0}{1}", added_where ? " AND " : " WHERE ", roll_set.SqlClause()));
    }
	public Photo [] Query (Term searchexpression, string extra_condition, DateRange range, RollSet importidrange, RatingRange ratingrange)
	{
		bool hide = (extra_condition == null);

		// The SQL query that we want to construct is:
		//
		// SELECT photos.id
		//        photos.time
		//        photos.uri,
		//        photos.description,
		//	  photos.roll_id,
		//        photos.default_version_id
		//        photos.rating
		//                  FROM photos, photo_tags
		//		    WHERE photos.time >= time1 AND photos.time <= time2
		//				AND photos.rating >= rat1 AND photos.rating <= rat2
		//				AND photos.id NOT IN (select photo_id FROM photo_tags WHERE tag_id = HIDDEN)
		//				AND photos.id IN (select photo_id FROM photo_tags where tag_id IN (tag1, tag2..)
		//				AND extra_condition_string
		//                  GROUP BY photos.id
		
		StringBuilder query_builder = new StringBuilder ();
		ArrayList where_clauses = new ArrayList ();
		query_builder.Append ("SELECT id, " 			+
					     "time, "			+
					     "uri, "			+
					     "description, "		+
				      	     "roll_id, "   		+
					     "default_version_id, "	+
					     "rating, "			+
					     "md5_sum "			+
				      "FROM photos ");
		
		if (range != null) {
			where_clauses.Add (String.Format ("time >= {0} AND time <= {1}",
							  DbUtils.UnixTimeFromDateTime (range.Start), 
							  DbUtils.UnixTimeFromDateTime (range.End)));

		}

		if (ratingrange != null) {
			where_clauses.Add (ratingrange.SqlClause ());
		}

		if (importidrange != null) {
			where_clauses.Add (importidrange.SqlClause ());
		}		
		
		if (hide && Core.Database.Tags.Hidden != null) {
			where_clauses.Add (String.Format ("id NOT IN (SELECT photo_id FROM photo_tags WHERE tag_id = {0})", 
							  FSpot.Core.Database.Tags.Hidden.Id));
		}
		
		if (searchexpression != null) {
			where_clauses.Add (searchexpression.SqlCondition ());
		}

		if (extra_condition != null && extra_condition.Trim () != String.Empty) {
			where_clauses.Add (extra_condition);
		}
		
		if (where_clauses.Count > 0) {
			query_builder.Append (" WHERE ");
			query_builder.Append (String.Join (" AND ", ((String []) where_clauses.ToArray (typeof(String)))));
		}
		query_builder.Append (" ORDER BY time");
		return Query (query_builder.ToString ());
	}
	private string AddLastImportFilter (RollSet roll_set, bool added_where)
	{
		if (roll_set == null)
			return null;

		return  String.Format (" {0}{1}", added_where ? " AND " : " WHERE ", roll_set.SqlClause () );
	}
Exemple #4
0
    public Photo [] Query(Term searchexpression, string extra_condition, DateRange range, RollSet importidrange, RatingRange ratingrange)
    {
        bool hide = (extra_condition == null);

        // The SQL query that we want to construct is:
        //
        // SELECT photos.id
        //        photos.time
        //        photos.uri,
        //        photos.description,
        //	  photos.roll_id,
        //        photos.default_version_id
        //        photos.rating
        //                  FROM photos, photo_tags
        //		    WHERE photos.time >= time1 AND photos.time <= time2
        //				AND photos.rating >= rat1 AND photos.rating <= rat2
        //				AND photos.id NOT IN (select photo_id FROM photo_tags WHERE tag_id = HIDDEN)
        //				AND photos.id IN (select photo_id FROM photo_tags where tag_id IN (tag1, tag2..)
        //				AND extra_condition_string
        //                  GROUP BY photos.id

        StringBuilder query_builder = new StringBuilder();
        ArrayList     where_clauses = new ArrayList();

        query_builder.Append("SELECT id, " +
                             "time, " +
                             "uri, " +
                             "description, " +
                             "roll_id, " +
                             "default_version_id, " +
                             "rating, " +
                             "md5_sum " +
                             "FROM photos ");

        if (range != null)
        {
            where_clauses.Add(String.Format("time >= {0} AND time <= {1}",
                                            DbUtils.UnixTimeFromDateTime(range.Start),
                                            DbUtils.UnixTimeFromDateTime(range.End)));
        }

        if (ratingrange != null)
        {
            where_clauses.Add(ratingrange.SqlClause());
        }

        if (importidrange != null)
        {
            where_clauses.Add(importidrange.SqlClause());
        }

        if (hide && Core.Database.Tags.Hidden != null)
        {
            where_clauses.Add(String.Format("id NOT IN (SELECT photo_id FROM photo_tags WHERE tag_id = {0})",
                                            FSpot.Core.Database.Tags.Hidden.Id));
        }

        if (searchexpression != null)
        {
            where_clauses.Add(searchexpression.SqlCondition());
        }

        if (extra_condition != null && extra_condition.Trim() != String.Empty)
        {
            where_clauses.Add(extra_condition);
        }

        if (where_clauses.Count > 0)
        {
            query_builder.Append(" WHERE ");
            query_builder.Append(String.Join(" AND ", ((String [])where_clauses.ToArray(typeof(String)))));
        }
        query_builder.Append(" ORDER BY time");
        return(Query(query_builder.ToString()));
    }