sbLocalDatabaseQuery.cpp
Go to the documentation of this file.
1 /*
2 //
3 // BEGIN SONGBIRD GPL
4 //
5 // This file is part of the Songbird web player.
6 //
7 // Copyright(c) 2005-2008 POTI, Inc.
8 // http://songbirdnest.com
9 //
10 // This file may be licensed under the terms of of the
11 // GNU General Public License Version 2 (the "GPL").
12 //
13 // Software distributed under the License is distributed
14 // on an "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either
15 // express or implied. See the GPL for the specific language
16 // governing rights and limitations.
17 //
18 // You should have received a copy of the GPL along with this
19 // program. If not, go to http://www.gnu.org/licenses/gpl.html
20 // or write to the Free Software Foundation, Inc.,
21 // 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
22 //
23 // END SONGBIRD GPL
24 //
25 */
26 
27 #include "sbLocalDatabaseQuery.h"
28 #include "sbLocalDatabaseSchemaInfo.h"
29 
30 
31 #include <sbIDatabaseQuery.h>
32 #include <sbIDatabaseResult.h>
33 #include <sbILocalDatabasePropertyCache.h>
34 #include <sbISQLBuilder.h>
35 
36 #include <nsComponentManagerUtils.h>
37 #include <sbStandardProperties.h>
38 #include <sbSQLBuilderCID.h>
39 #include <sbStringUtils.h>
40 
41 #define COUNT_COLUMN NS_LITERAL_STRING("count(1)")
42 #define CREATED_COLUMN NS_LITERAL_STRING("created")
43 #define GUID_COLUMN NS_LITERAL_STRING("guid")
44 #define OBJ_COLUMN NS_LITERAL_STRING("obj")
45 #define OBJSORTABLE_COLUMN NS_LITERAL_STRING("obj_sortable")
46 #define OBJSECONDARYSORTABLE_COLUMN NS_LITERAL_STRING("obj_secondary_sortable")
47 #define MEDIAITEMID_COLUMN NS_LITERAL_STRING("media_item_id")
48 #define PROPERTYID_COLUMN NS_LITERAL_STRING("property_id")
49 #define ORDINAL_COLUMN NS_LITERAL_STRING("ordinal")
50 #define PROPERTYNAME_COLUMN NS_LITERAL_STRING("property_name")
51 #define MEDIALISTYPEID_COLUMN NS_LITERAL_STRING("media_list_type_id")
52 #define ISLIST_COLUMN NS_LITERAL_STRING("is_list")
53 #define ROWID_COLUMN NS_LITERAL_STRING("rowid")
54 
55 #define PROPERTIES_TABLE NS_LITERAL_STRING("resource_properties")
56 #define PROPERTIES_FTS_TABLE NS_LITERAL_STRING("resource_properties_fts")
57 #define PROPERTIES_FTS_ALL_TABLE NS_LITERAL_STRING("resource_properties_fts_all")
58 #define MEDIAITEMS_TABLE NS_LITERAL_STRING("media_items")
59 #define SIMPLEMEDIALISTS_TABLE NS_LITERAL_STRING("simple_media_lists")
60 #define PROPERTYIDS_TABLE NS_LITERAL_STRING("properties")
61 
62 #define MEDIAITEMS_ALIAS NS_LITERAL_STRING("_mi")
63 #define CONSTRAINT_ALIAS NS_LITERAL_STRING("_con")
64 #define GETNOTNULL_ALIAS NS_LITERAL_STRING("_getnotnull")
65 #define GETNULL_ALIAS NS_LITERAL_STRING("_getnull")
66 #define SORT_ALIAS NS_LITERAL_STRING("_sort")
67 #define DISTINCT_ALIAS NS_LITERAL_STRING("_d")
68 #define CONPROP_ALIAS NS_LITERAL_STRING("_conprop")
69 
70 #define ORDINAL_PROPERTY NS_LITERAL_STRING(SB_PROPERTY_ORDINAL)
71 #define ISLIST_PROPERTY NS_LITERAL_STRING(SB_PROPERTY_ISLIST)
72 
73 sbLocalDatabaseQuery::sbLocalDatabaseQuery(const nsAString& aBaseTable,
74  const nsAString& aBaseConstraintColumn,
75  PRUint32 aBaseConstraintValue,
76  const nsAString& aBaseForeignKeyColumn,
77  nsTArray<sbLocalDatabaseGUIDArray::FilterSpec>* aFilters,
78  nsTArray<sbLocalDatabaseGUIDArray::SortSpec>* aSorts,
79  PRBool aIsDistinct,
80  PRBool aDistinctWithSortableValues,
81  sbILocalDatabasePropertyCache* aPropertyCache) :
82  mBaseTable(aBaseTable),
83  mBaseConstraintColumn(aBaseConstraintColumn),
84  mBaseConstraintValue(aBaseConstraintValue),
85  mBaseForeignKeyColumn(aBaseForeignKeyColumn),
86  mFilters(aFilters),
87  mSorts(aSorts),
88  mIsDistinct(aIsDistinct),
89  mDistinctWithSortableValues(aDistinctWithSortableValues),
90  mPropertyCache(aPropertyCache),
91  mHasSearch(PR_FALSE)
92 {
93  mIsFullLibrary = mBaseTable.Equals(MEDIAITEMS_TABLE);
94 
95  mBuilder = do_CreateInstance(SB_SQLBUILDER_SELECT_CONTRACTID);
96  NS_ASSERTION(mBuilder, "Could not create builder");
97 
98  // Check to see if we have a search. This determines if we need to ask for
99  // distinct results or not
100  // Combine all searches with the same term into one search
101  PRUint32 len = mFilters->Length();
102  for (PRUint32 i = 0; i < len; i++) {
103  if (mFilters->ElementAt(i).isSearch) {
104  mHasSearch = PR_TRUE;
105  break;
106  }
107  }
108 
109 }
110 
111 nsresult
112 sbLocalDatabaseQuery::GetFullCountQuery(nsAString& aQuery)
113 {
114  nsresult rv;
115 
116  rv = mBuilder->Reset();
117  NS_ENSURE_SUCCESS(rv, rv);
118 
119  rv = AddCountColumns();
120  NS_ENSURE_SUCCESS(rv, rv);
121 
122  rv = AddBaseTable();
123  NS_ENSURE_SUCCESS(rv, rv);
124 
125  if (mIsDistinct) {
126  rv = AddDistinctConstraint();
127  NS_ENSURE_SUCCESS(rv, rv);
128  }
129 
130  rv = AddFilters();
131  NS_ENSURE_SUCCESS(rv, rv);
132 
133  rv = mBuilder->ToString(aQuery);
134  NS_ENSURE_SUCCESS(rv, rv);
135 
136  return NS_OK;
137 }
138 
139 nsresult
140 sbLocalDatabaseQuery::GetFullGuidRangeQuery(nsAString& aQuery)
141 {
142  nsresult rv;
143 
144  rv = mBuilder->Reset();
145  NS_ENSURE_SUCCESS(rv, rv);
146 
147  rv = AddGuidColumns(PR_FALSE);
148  NS_ENSURE_SUCCESS(rv, rv);
149 
150  rv = AddBaseTable();
151  NS_ENSURE_SUCCESS(rv, rv);
152 
153  rv = AddFilters();
154  NS_ENSURE_SUCCESS(rv, rv);
155 
156  if (mIsDistinct) {
157  rv = AddDistinctGroupBy();
158  NS_ENSURE_SUCCESS(rv, rv);
159  }
160 
161  // If we are sorted on a top level property, we need to add a constraint
162  // to ensure we only get rows with non-null values. Note that we don't have
163  // to do this when it is not a top level property because we don't get nulls
164  // by virtue of the join
165  if (SB_IsTopLevelProperty(mSorts->ElementAt(0).property)) {
166  nsAutoString columnName;
167  rv = SB_GetTopLevelPropertyColumn(mSorts->ElementAt(0).property, columnName);
168  NS_ENSURE_SUCCESS(rv, rv);
169 
170  nsCOMPtr<sbISQLBuilderCriterion> criterion;
171  rv = mBuilder->CreateMatchCriterionNull(MEDIAITEMS_ALIAS,
172  columnName,
173  sbISQLSelectBuilder::MATCH_NOTEQUALS,
174  getter_AddRefs(criterion));
175  rv = mBuilder->AddCriterion(criterion);
176  NS_ENSURE_SUCCESS(rv, rv);
177  }
178 
179  rv = AddPrimarySort();
180  NS_ENSURE_SUCCESS(rv, rv);
181 
182  rv = AddRange();
183  NS_ENSURE_SUCCESS(rv, rv);
184 
185  rv = mBuilder->ToString(aQuery);
186  NS_ENSURE_SUCCESS(rv, rv);
187 
188  return NS_OK;
189 }
190 
191 nsresult
192 sbLocalDatabaseQuery::GetNonNullCountQuery(nsAString& aQuery)
193 {
194  nsresult rv;
195 
196  rv = mBuilder->Reset();
197  NS_ENSURE_SUCCESS(rv, rv);
198 
199  // There are no non-null rows in a distinct count query
200  if (mIsDistinct) {
201  aQuery = EmptyString();
202  return NS_OK;
203  }
204 
205  rv = AddCountColumns();
206  NS_ENSURE_SUCCESS(rv, rv);
207 
208  rv = AddBaseTable();
209  NS_ENSURE_SUCCESS(rv, rv);
210 
211  rv = AddFilters();
212  NS_ENSURE_SUCCESS(rv, rv);
213 
214  rv = AddNonNullPrimarySortConstraint();
215  NS_ENSURE_SUCCESS(rv, rv);
216 
217  rv = mBuilder->ToString(aQuery);
218  NS_ENSURE_SUCCESS(rv, rv);
219 
220  return NS_OK;
221 }
222 
223 nsresult
224 sbLocalDatabaseQuery::GetNullGuidRangeQuery(nsAString& aQuery)
225 {
226  nsresult rv;
227 
228  rv = mBuilder->Reset();
229  NS_ENSURE_SUCCESS(rv, rv);
230 
231  // There are no non-null rows in a distinct count query
232  if (mIsDistinct) {
233  aQuery = EmptyString();
234  return NS_OK;
235  }
236 
237  rv = AddGuidColumns(PR_TRUE);
238  NS_ENSURE_SUCCESS(rv, rv);
239 
240  rv = AddBaseTable();
241  NS_ENSURE_SUCCESS(rv, rv);
242 
243  rv = AddFilters();
244  NS_ENSURE_SUCCESS(rv, rv);
245 
246  rv = AddJoinToGetNulls();
247  NS_ENSURE_SUCCESS(rv, rv);
248 
249  // Always sort nulls by ascending media item so their positions never change
250  rv = mBuilder->AddOrder(MEDIAITEMS_ALIAS,
251  MEDIAITEMID_COLUMN,
252  PR_TRUE);
253  NS_ENSURE_SUCCESS(rv, rv);
254 
255  rv = AddRange();
256  NS_ENSURE_SUCCESS(rv, rv);
257 
258  rv = mBuilder->ToString(aQuery);
259  NS_ENSURE_SUCCESS(rv, rv);
260 
261  return NS_OK;
262 }
263 
264 nsresult
265 sbLocalDatabaseQuery::GetPrefixSearchQuery(nsAString& aQuery)
266 {
267  nsresult rv;
268 
269  rv = mBuilder->Reset();
270  NS_ENSURE_SUCCESS(rv, rv);
271 
272  rv = AddCountColumns();
273  NS_ENSURE_SUCCESS(rv, rv);
274 
275  rv = AddBaseTable();
276  NS_ENSURE_SUCCESS(rv, rv);
277 
278  if (mIsDistinct) {
279  rv = AddDistinctConstraint();
280  NS_ENSURE_SUCCESS(rv, rv);
281  }
282 
283  rv = AddFilters();
284  NS_ENSURE_SUCCESS(rv, rv);
285 
286  rv = AddPrimarySort();
287  NS_ENSURE_SUCCESS(rv, rv);
288 
289  nsCOMPtr<sbISQLBuilderCriterion> criterion;
290  if (mSorts->ElementAt(0).property.Equals(ORDINAL_PROPERTY)) {
291 
292  nsAutoString baseTable;
293  rv = mBuilder->GetBaseTableName(baseTable);
294  NS_ENSURE_SUCCESS(rv, rv);
295 
296  if (baseTable.Equals(SIMPLEMEDIALISTS_TABLE)) {
297  rv = mBuilder->CreateMatchCriterionParameter(CONSTRAINT_ALIAS,
298  ORDINAL_COLUMN,
299  sbISQLSelectBuilder::MATCH_LESS,
300  getter_AddRefs(criterion));
301  NS_ENSURE_SUCCESS(rv, rv);
302  }
303  else {
304  return NS_OK;
305  }
306  }
307  else {
308  rv = mBuilder->CreateMatchCriterionParameter(SORT_ALIAS,
309  OBJSORTABLE_COLUMN,
310  sbISQLSelectBuilder::MATCH_LESS,
311  getter_AddRefs(criterion));
312  NS_ENSURE_SUCCESS(rv, rv);
313  }
314  rv = mBuilder->AddCriterion(criterion);
315  NS_ENSURE_SUCCESS(rv, rv);
316 
317  rv = mBuilder->ToString(aQuery);
318  NS_ENSURE_SUCCESS(rv, rv);
319 
320  return NS_OK;
321 }
322 
323 nsresult
324 sbLocalDatabaseQuery::GetResortQuery(nsAString& aQuery)
325 {
326  NS_ENSURE_FALSE(mIsDistinct, NS_ERROR_UNEXPECTED);
327  NS_ENSURE_TRUE(mSorts->Length() > 1, NS_ERROR_UNEXPECTED);
328 
329  nsresult rv;
330 
331  rv = mBuilder->Reset();
332  NS_ENSURE_SUCCESS(rv, rv);
333 
334  rv = AddResortColumns();
335  NS_ENSURE_SUCCESS(rv, rv);
336 
337  rv = AddBaseTable();
338  NS_ENSURE_SUCCESS(rv, rv);
339 
340  // Constrain the rows in the base table to a given value parameter
341  nsCOMPtr<sbISQLBuilderCriterion> criterion;
342  if (SB_IsTopLevelProperty(mSorts->ElementAt(0).property)) {
343 
344  // If the primary sort is a top level property, we can put the value
345  // constraint directly on the media items table
346  nsString columnName;
347  rv = SB_GetTopLevelPropertyColumn(mSorts->ElementAt(0).property,
348  columnName);
349  NS_ENSURE_SUCCESS(rv, rv);
350 
351  rv = mBuilder->CreateMatchCriterionParameter(MEDIAITEMS_ALIAS,
352  columnName,
353  sbISQLSelectBuilder::MATCH_EQUALS,
354  getter_AddRefs(criterion));
355  NS_ENSURE_SUCCESS(rv, rv);
356  rv = mBuilder->AddCriterion(criterion);
357  NS_ENSURE_SUCCESS(rv, rv);
358  }
359  else {
360  // Join the properties table to apply the constraint
361  rv = mBuilder->AddJoin(sbISQLSelectBuilder::JOIN_INNER,
362  PROPERTIES_TABLE,
363  CONPROP_ALIAS,
364  MEDIAITEMID_COLUMN,
365  MEDIAITEMS_ALIAS,
366  MEDIAITEMID_COLUMN);
367  NS_ENSURE_SUCCESS(rv, rv);
368 
369  rv = mBuilder->CreateMatchCriterionLong(CONPROP_ALIAS,
370  PROPERTYID_COLUMN,
371  sbISQLSelectBuilder::MATCH_EQUALS,
372  GetPropertyId(mSorts->ElementAt(0).property),
373  getter_AddRefs(criterion));
374  NS_ENSURE_SUCCESS(rv, rv);
375  rv = mBuilder->AddCriterion(criterion);
376  NS_ENSURE_SUCCESS(rv, rv);
377 
378  rv = mBuilder->CreateMatchCriterionParameter(CONPROP_ALIAS,
379  OBJSORTABLE_COLUMN,
380  sbISQLSelectBuilder::MATCH_EQUALS,
381  getter_AddRefs(criterion));
382  NS_ENSURE_SUCCESS(rv, rv);
383  rv = mBuilder->AddCriterion(criterion);
384  NS_ENSURE_SUCCESS(rv, rv);
385  }
386 
387  rv = AddFilters();
388  NS_ENSURE_SUCCESS(rv, rv);
389 
390  rv = AddMultiSorts();
391  NS_ENSURE_SUCCESS(rv, rv);
392 
393  rv = mBuilder->ToString(aQuery);
394  NS_ENSURE_SUCCESS(rv, rv);
395 
396  return NS_OK;
397 }
398 
399 nsresult
400 sbLocalDatabaseQuery::GetNullResortQuery(nsAString& aQuery)
401 {
402  NS_ENSURE_FALSE(mIsDistinct, NS_ERROR_UNEXPECTED);
403  NS_ENSURE_TRUE(mSorts->Length() > 1, NS_ERROR_UNEXPECTED);
404 
405  if (SB_IsTopLevelProperty(mSorts->ElementAt(0).property)) {
406  return NS_ERROR_NOT_IMPLEMENTED;
407  }
408 
409  nsresult rv;
410 
411  rv = mBuilder->Reset();
412  NS_ENSURE_SUCCESS(rv, rv);
413 
414  rv = AddResortColumns();
415  NS_ENSURE_SUCCESS(rv, rv);
416 
417  rv = AddBaseTable();
418  NS_ENSURE_SUCCESS(rv, rv);
419 
420  rv = AddFilters();
421  NS_ENSURE_SUCCESS(rv, rv);
422 
423  // Left join the properties table to the base table includig a null
424  // constraint on the obj_sortable column
425  nsCOMPtr<sbISQLBuilderCriterion> criterionGuid;
426  rv = mBuilder->CreateMatchCriterionTable(NS_LITERAL_STRING("_pX"),
427  MEDIAITEMID_COLUMN,
428  sbISQLSelectBuilder::MATCH_EQUALS,
429  MEDIAITEMS_ALIAS,
430  MEDIAITEMID_COLUMN,
431  getter_AddRefs(criterionGuid));
432  NS_ENSURE_SUCCESS(rv, rv);
433 
434  nsCOMPtr<sbISQLBuilderCriterion> criterionProperty;
435  rv = mBuilder->CreateMatchCriterionLong(NS_LITERAL_STRING("_pX"),
436  NS_LITERAL_STRING("property_id"),
437  sbISQLSelectBuilder::MATCH_EQUALS,
438  GetPropertyId(mSorts->ElementAt(0).property),
439  getter_AddRefs(criterionProperty));
440  NS_ENSURE_SUCCESS(rv, rv);
441 
442  nsCOMPtr<sbISQLBuilderCriterion> criterion;
443  rv = mBuilder->CreateAndCriterion(criterionGuid,
444  criterionProperty,
445  getter_AddRefs(criterion));
446  NS_ENSURE_SUCCESS(rv, rv);
447 
448  rv = mBuilder->AddJoinWithCriterion(sbISQLSelectBuilder::JOIN_LEFT,
449  PROPERTIES_TABLE,
450  NS_LITERAL_STRING("_pX"),
451  criterion);
452  NS_ENSURE_SUCCESS(rv, rv);
453 
454  rv = mBuilder->CreateMatchCriterionNull(NS_LITERAL_STRING("_pX"),
455  OBJSORTABLE_COLUMN,
456  sbISQLSelectBuilder::MATCH_EQUALS,
457  getter_AddRefs(criterion));
458  NS_ENSURE_SUCCESS(rv, rv);
459 
460  rv = mBuilder->AddCriterion(criterion);
461  NS_ENSURE_SUCCESS(rv, rv);
462 
463  rv = AddMultiSorts();
464  NS_ENSURE_SUCCESS(rv, rv);
465 
466  rv = mBuilder->ToString(aQuery);
467  NS_ENSURE_SUCCESS(rv, rv);
468 
469  return NS_OK;
470 }
471 
472 PRBool
473 sbLocalDatabaseQuery::GetIsFullLibrary()
474 {
475  return mIsFullLibrary;
476 }
477 
478 nsresult
479 sbLocalDatabaseQuery::AddCountColumns()
480 {
481  nsresult rv;
482 
483  if (mIsDistinct) {
484  if (SB_IsTopLevelProperty(mSorts->ElementAt(0).property)) {
485  nsAutoString columnName;
486  rv = SB_GetTopLevelPropertyColumn(mSorts->ElementAt(0).property,
487  columnName);
488  NS_ENSURE_SUCCESS(rv, rv);
489 
490  nsAutoString count;
491  count.AssignLiteral("count(distinct _mi.");
492  count.Append(columnName);
493  count.AppendLiteral(")");
494 
495  rv = mBuilder->AddColumn(EmptyString(), count);
496  NS_ENSURE_SUCCESS(rv, rv);
497  }
498  else {
499  rv = mBuilder->AddColumn(EmptyString(),
500  NS_LITERAL_STRING("count(distinct _d.obj_sortable)"));
501  NS_ENSURE_SUCCESS(rv, rv);
502  }
503  }
504  else {
505  rv = mBuilder->AddColumn(EmptyString(), COUNT_COLUMN);
506  NS_ENSURE_SUCCESS(rv, rv);
507  }
508 
509  return NS_OK;
510 }
511 
512 nsresult
513 sbLocalDatabaseQuery::AddGuidColumns(PRBool aIsNull)
514 {
515  nsresult rv;
516 
517  rv = mBuilder->AddColumn(MEDIAITEMS_ALIAS, MEDIAITEMID_COLUMN);
518  NS_ENSURE_SUCCESS(rv, rv);
519 
520  rv = mBuilder->AddColumn(MEDIAITEMS_ALIAS, GUID_COLUMN);
521  NS_ENSURE_SUCCESS(rv, rv);
522 
523  if (aIsNull) {
524  rv = mBuilder->AddColumn(EmptyString(), NS_LITERAL_STRING("''"));
525  NS_ENSURE_SUCCESS(rv, rv);
526  }
527  else {
528  if (SB_IsTopLevelProperty(mSorts->ElementAt(0).property)) {
529  nsAutoString columnName;
530  rv = SB_GetTopLevelPropertyColumn(mSorts->ElementAt(0).property, columnName);
531  NS_ENSURE_SUCCESS(rv, rv);
532 
533  rv = mBuilder->AddColumn(MEDIAITEMS_ALIAS, columnName);
534  NS_ENSURE_SUCCESS(rv, rv);
535  }
536  else {
537  if (mSorts->ElementAt(0).property.Equals(ORDINAL_PROPERTY)) {
538 
539  if (mBaseTable.Equals(SIMPLEMEDIALISTS_TABLE)) {
540  rv = mBuilder->AddColumn(CONSTRAINT_ALIAS,
541  ORDINAL_COLUMN);
542  NS_ENSURE_SUCCESS(rv, rv);
543  }
544  }
545  else {
546  if (mIsDistinct && !mDistinctWithSortableValues) {
547  rv = mBuilder->AddColumn(SORT_ALIAS, OBJ_COLUMN);
548  NS_ENSURE_SUCCESS(rv, rv);
549  }
550  else {
551  rv = mBuilder->AddColumn(SORT_ALIAS, OBJSORTABLE_COLUMN);
552  NS_ENSURE_SUCCESS(rv, rv);
553  }
554  }
555  }
556  }
557 
558  // If this query is on the simple media list table, add the ordinal
559  // as the next column
560  if (mBaseTable.Equals(SIMPLEMEDIALISTS_TABLE)) {
561  rv = mBuilder->AddColumn(CONSTRAINT_ALIAS,
562  ORDINAL_COLUMN);
563  NS_ENSURE_SUCCESS(rv, rv);
564  }
565  else {
566  rv = mBuilder->AddColumn(EmptyString(), NS_LITERAL_STRING("''"));
567  NS_ENSURE_SUCCESS(rv, rv);
568  }
569 
570  // If this is a library query, add the rowid from the media_items table,
571  // otherwise add it from the simple_media_lists table
572  nsString base;
573  if (mIsFullLibrary) {
574  base = MEDIAITEMS_ALIAS;
575  }
576  else {
577  base = CONSTRAINT_ALIAS;
578  }
579 
580  rv = mBuilder->AddColumn(base, ROWID_COLUMN);
581  NS_ENSURE_SUCCESS(rv, rv);
582 
583  return NS_OK;
584 }
585 
586 nsresult
587 sbLocalDatabaseQuery::AddBaseTable()
588 {
589  nsresult rv;
590 
591  if (mIsFullLibrary) {
592  rv = mBuilder->SetBaseTableName(MEDIAITEMS_TABLE);
593  NS_ENSURE_SUCCESS(rv, rv);
594 
595  rv = mBuilder->SetBaseTableAlias(MEDIAITEMS_ALIAS);
596  NS_ENSURE_SUCCESS(rv, rv);
597  }
598  else {
599  /*
600  * Join the base table to the media items table and add the needed
601  * constraint.
602  */
603  rv = mBuilder->SetBaseTableName(mBaseTable);
604  NS_ENSURE_SUCCESS(rv, rv);
605 
606  rv = mBuilder->SetBaseTableAlias(CONSTRAINT_ALIAS);
607  NS_ENSURE_SUCCESS(rv, rv);
608 
609  nsCOMPtr<sbISQLBuilderCriterion> criterion;
610  rv = mBuilder->CreateMatchCriterionLong(CONSTRAINT_ALIAS,
611  mBaseConstraintColumn,
612  sbISQLSelectBuilder::MATCH_EQUALS,
613  mBaseConstraintValue,
614  getter_AddRefs(criterion));
615  NS_ENSURE_SUCCESS(rv, rv);
616 
617  rv = mBuilder->AddCriterion(criterion);
618  NS_ENSURE_SUCCESS(rv, rv);
619 
620  rv = mBuilder->AddJoin(sbISQLSelectBuilder::JOIN_INNER,
621  MEDIAITEMS_TABLE,
622  MEDIAITEMS_ALIAS,
623  MEDIAITEMID_COLUMN,
624  CONSTRAINT_ALIAS,
625  mBaseForeignKeyColumn);
626  NS_ENSURE_SUCCESS(rv, rv);
627  }
628 
629  return NS_OK;
630 }
631 
632 nsresult
633 sbLocalDatabaseQuery::AddFilters()
634 {
635  nsresult rv;
636 
637  PRUint32 joinNum = 0;
638 
639  // Add the filters as joins
640  PRUint32 len = mFilters->Length();
641  for (PRUint32 i = 0; i < len; i++) {
642  const sbLocalDatabaseGUIDArray::FilterSpec& fs = mFilters->ElementAt(i);
643 
644  if (!fs.isSearch) {
645 
646  PRBool isTopLevelProperty = SB_IsTopLevelProperty(fs.property);
647  nsCOMPtr<sbISQLBuilderCriterion> criterion;
648 
649  // If the property is the "is list" property, we handle this in a
650  // special way. If the value is 1, we filter the media_list_type_id
651  // column on not null, if the value is 0 we filter said column on null
652  if (fs.property.Equals(ISLIST_PROPERTY)) {
653  PRUint32 numValues = fs.values.Length();
654  NS_ENSURE_STATE(numValues);
655  nsCOMPtr<sbISQLBuilderCriterion> nullCriterion;
656  PRUint32 matchType;
657  if (fs.values[0].EqualsLiteral("0")) {
658  matchType = sbISQLSelectBuilder::MATCH_EQUALS;
659  }
660  else {
661  matchType = sbISQLSelectBuilder::MATCH_NOTEQUALS;
662  }
663 
664  // XXX HACK XXX HACK XXX HACK UGH
665  // The |media_list_type_id is null| constraint will cause sqlite to
666  // access the data using the idx_media_items_media_list_type_id index
667  // which turns out to be a poor choice because the values in that
668  // column are mostly null. Using |+_mi.media_list_type_id| in the
669  // criterion causes sqlite to not use the index, and should allow it
670  // to select a better one.
671  //
672  // Possibly related sqlite bug:
673  // http://www.sqlite.org/cvstrac/tktview?tn=3036
674  // I also posted about this issue on sqlite-users:
675  // http://www.mail-archive.com/sqlite-users@sqlite.org/msg32924.html
676  nsString indexKillingAlias;
677  indexKillingAlias.AssignLiteral("+");
678  indexKillingAlias.Append(MEDIAITEMS_ALIAS);
679 
680  rv = mBuilder->CreateMatchCriterionNull(indexKillingAlias,
681  MEDIALISTYPEID_COLUMN,
682  matchType,
683  getter_AddRefs(nullCriterion));
684  NS_ENSURE_SUCCESS(rv, rv);
685 
686  rv = mBuilder->AddCriterion(nullCriterion);
687  NS_ENSURE_SUCCESS(rv, rv);
688  }
689  else {
690  nsCOMPtr<sbISQLBuilderCriterionIn> inCriterion;
691  if (isTopLevelProperty) {
692 
693  // Add the constraint for the top level table.
694  nsAutoString columnName;
695  rv = SB_GetTopLevelPropertyColumn(fs.property, columnName);
696  NS_ENSURE_SUCCESS(rv, rv);
697 
698  rv = mBuilder->CreateMatchCriterionIn(MEDIAITEMS_ALIAS,
699  columnName,
700  getter_AddRefs(inCriterion));
701  NS_ENSURE_SUCCESS(rv, rv);
702  }
703  else {
704  nsAutoString tableAlias;
705  tableAlias.AppendLiteral("_p");
706  tableAlias.AppendInt(joinNum++);
707 
708  rv = mBuilder->AddJoin(sbISQLSelectBuilder::JOIN_INNER,
709  PROPERTIES_TABLE,
710  tableAlias,
711  MEDIAITEMID_COLUMN,
712  MEDIAITEMS_ALIAS,
713  MEDIAITEMID_COLUMN);
714  NS_ENSURE_SUCCESS(rv, rv);
715 
716  // Add the propery constraint for the filter
717  rv = mBuilder->CreateMatchCriterionLong(tableAlias,
718  NS_LITERAL_STRING("property_id"),
719  sbISQLSelectBuilder::MATCH_EQUALS,
720  GetPropertyId(fs.property),
721  getter_AddRefs(criterion));
722  NS_ENSURE_SUCCESS(rv, rv);
723 
724  rv = mBuilder->AddCriterion(criterion);
725  NS_ENSURE_SUCCESS(rv, rv);
726 
727  rv = mBuilder->CreateMatchCriterionIn(tableAlias,
728  OBJSORTABLE_COLUMN,
729  getter_AddRefs(inCriterion));
730  NS_ENSURE_SUCCESS(rv, rv);
731  }
732 
733  // For each filter value, add the term to an IN constraint
734  PRUint32 numValues = fs.values.Length();
735  for (PRUint32 j = 0; j < numValues; j++) {
736  const nsAString& filterTerm = fs.values[j];
737  rv = inCriterion->AddString(filterTerm);
738  NS_ENSURE_SUCCESS(rv, rv);
739  }
740 
741  rv = mBuilder->AddCriterion(inCriterion);
742  NS_ENSURE_SUCCESS(rv, rv);
743  }
744  }
745  }
746 
747  // Add search constraints
748 
749  PRInt32 searchIndex = -1;
750  PRBool isEverythingSearch = PR_FALSE;
751 
752  for (PRUint32 i = 0; i < len; i++) {
753  const sbLocalDatabaseGUIDArray::FilterSpec& fs = mFilters->ElementAt(i);
754  if (fs.isSearch) {
755  if (searchIndex < 0) {
756 
757  if (SB_IsTopLevelProperty(fs.property)) {
758  NS_WARNING("Top level properties not supported in search");
759  return NS_ERROR_INVALID_ARG;
760  }
761 
762  searchIndex = i;
763  if (fs.property.EqualsLiteral("*")) {
764  isEverythingSearch = PR_TRUE;
765  }
766  break;
767  }
768  }
769  }
770 
771  if (searchIndex >= 0) {
772 
773  // XXX Now that we are using sqlite FTS, we need to live with its
774  // limitations, such as not being able to have more than one MATCH
775  // in there WHERE clause. This prevents us from joining the fts table
776  // more than once. From my testing, it seems like the fastest way to
777  // do this is to use a single fts join with both an obj MATCH and
778  // propertyid IN (when needed) in the where clause. The match string
779  // will be each string in the value list for the filter. The propertyid
780  // in list will be the ids of all of the properties from all of the
781  // filters (if needed). Note that this means that every search filter
782  // must have the same value list. This is enforced in
783  // sbLocalDatabaseMediaListView::SetSearchConstraint
784 
785  // XXXAus: Because of bug 9488, all searches must use the everythingSearch.
786  // See bug 9488 and bug 9617 for more information.
787  isEverythingSearch = PR_TRUE;
788 
789  if (isEverythingSearch) {
790  // Join the all fts table. The foreign key of this table is the
791  // media item id so we can simply join it to _mi
792  rv = mBuilder->AddJoin(sbISQLSelectBuilder::JOIN_INNER,
793  PROPERTIES_FTS_ALL_TABLE,
794  NS_LITERAL_STRING("_fts"),
795  ROWID_COLUMN,
796  MEDIAITEMS_ALIAS,
797  MEDIAITEMID_COLUMN);
798  NS_ENSURE_SUCCESS(rv, rv);
799  }
800 
801  /* XXXAus: resource_properties_fts is disabled. See bug 9488 and bug 9617.
802  else {
803  // This is not the everything search so we need to use the per-property
804  // fts table. The foreign key on that table is the resource_properties
805  // row id, so we must join both a "bridge" table as well as the fts table
806  rv = mBuilder->AddJoin(sbISQLSelectBuilder::JOIN_INNER,
807  PROPERTIES_TABLE,
808  NS_LITERAL_STRING("_fts_bridge"),
809  MEDIAITEMID_COLUMN,
810  MEDIAITEMS_ALIAS,
811  MEDIAITEMID_COLUMN);
812  NS_ENSURE_SUCCESS(rv, rv);
813 
814  rv = mBuilder->AddJoin(sbISQLSelectBuilder::JOIN_INNER,
815  PROPERTIES_FTS_TABLE,
816  NS_LITERAL_STRING("_fts"),
817  ROWID_COLUMN,
818  NS_LITERAL_STRING("_fts_bridge"),
819  ROWID_COLUMN);
820  NS_ENSURE_SUCCESS(rv, rv);
821  }
822  */
823 
824  // Add the match constraint
825  nsString match;
826  const sbLocalDatabaseGUIDArray::FilterSpec& fs =
827  mFilters->ElementAt(searchIndex);
828 
829  for (PRUint32 i = 0; i < fs.values.Length(); i++) {
830 
831  nsString stripped(fs.values[i]);
832 
833  // '*' and '-' are special FTS operators. Make sure they are not in the
834  // search filter otherwise the query will fail with a logic error.
835  PRInt32 pos = 0;
836 
837  do {
838  pos = nsString_FindCharInSet(stripped, "*-", pos);
839  if(pos != -1) {
840  if(static_cast<PRUint32>(pos) + 1 < stripped.Length() &&
841  stripped[pos + 1] != '*' &&
842  stripped[pos + 1] != '-' &&
843  stripped[pos + 1] != ' ') {
844  stripped.Replace(pos, 1, NS_LITERAL_STRING(" NEAR/0 "));
845  pos += 8;
846  }
847  else {
848  stripped.Cut(pos, 1);
849  }
850  }
851  }
852  while(pos != -1);
853 
854  // Quotes have to be escaped, otherwise if they are unbalanced they will
855  // cause a SQL error when the statement is compiled :( We can't actually
856  // use parameter binding here because of the way the underlying GUID array
857  // that uses this object is built. The GUID array completely relies on the
858  // automatic query building to generate the queries it uses. Because of this
859  // it's completely unaware of which query it's running, hence we can't use
860  // parameter binding.
861  nsString_ReplaceSubstring(stripped,
862  NS_LITERAL_STRING("\""),
863  NS_LITERAL_STRING("\\\""));
864  nsString_ReplaceSubstring(stripped,
865  NS_LITERAL_STRING("'"),
866  NS_LITERAL_STRING("\\'"));
867 
868  match.AppendLiteral("'");
869  match.Append(stripped);
870  match.AppendLiteral("*'");
871 
872  if (i + 1 < fs.values.Length()) {
873  match.AppendLiteral(" ");
874  }
875  }
876 
877  nsCOMPtr<sbISQLBuilderCriterion> criterion;
878  rv = mBuilder->CreateMatchCriterionString(NS_LITERAL_STRING("_fts"),
879  isEverythingSearch ?
880  NS_LITERAL_STRING("alldata") :
881  NS_LITERAL_STRING("obj_searchable"),
882  sbISQLSelectBuilder::MATCH_MATCH,
883  match,
884  getter_AddRefs(criterion));
885  NS_ENSURE_SUCCESS(rv, rv);
886 
887  rv = mBuilder->AddCriterion(criterion);
888  NS_ENSURE_SUCCESS(rv, rv);
889 
890  // Add the property ID constraint if needed
891  if (!isEverythingSearch) {
892  nsCOMPtr<sbISQLBuilderCriterion> criterion;
893  rv = mBuilder->CreateMatchCriterionLong(NS_LITERAL_STRING("_fts"),
894  NS_LITERAL_STRING("propertyid"),
895  sbISQLSelectBuilder::MATCH_EQUALS,
896  GetPropertyId(fs.property),
897  getter_AddRefs(criterion));
898  NS_ENSURE_SUCCESS(rv, rv);
899 
900  rv = mBuilder->AddCriterion(criterion);
901  NS_ENSURE_SUCCESS(rv, rv);
902  }
903 
904  }
905 
906  // If this is a top level distinct query, make sure the primary sort property
907  // is never null
908  if (mIsDistinct && SB_IsTopLevelProperty(mSorts->ElementAt(0).property)) {
909  nsCOMPtr<sbISQLBuilderCriterion> criterion;
910  nsAutoString columnName;
911  rv = SB_GetTopLevelPropertyColumn(mSorts->ElementAt(0).property, columnName);
912  NS_ENSURE_SUCCESS(rv, rv);
913 
914  rv = mBuilder->CreateMatchCriterionNull(MEDIAITEMS_ALIAS,
915  columnName,
916  sbISQLSelectBuilder::MATCH_NOTEQUALS,
917  getter_AddRefs(criterion));
918  NS_ENSURE_SUCCESS(rv, rv);
919  rv = mBuilder->AddCriterion(criterion);
920  NS_ENSURE_SUCCESS(rv, rv);
921  }
922 
923  return NS_OK;
924 }
925 
926 nsresult
927 sbLocalDatabaseQuery::AddRange()
928 {
929  nsresult rv;
930 
931  rv = mBuilder->SetOffsetIsParameter(PR_TRUE);
932  NS_ENSURE_SUCCESS(rv, rv);
933 
934  rv = mBuilder->SetLimitIsParameter(PR_TRUE);
935  NS_ENSURE_SUCCESS(rv, rv);
936 
937  return NS_OK;
938 }
939 
940 nsresult
941 sbLocalDatabaseQuery::AddPrimarySort()
942 {
943  nsresult rv;
944 
945  /*
946  * If this is a top level propery, simply add the sort
947  */
948  if (SB_IsTopLevelProperty(mSorts->ElementAt(0).property)) {
949  nsAutoString columnName;
950  rv = SB_GetTopLevelPropertyColumn(mSorts->ElementAt(0).property, columnName);
951  NS_ENSURE_SUCCESS(rv, rv);
952 
953  rv = mBuilder->AddOrder(MEDIAITEMS_ALIAS,
954  columnName,
955  mSorts->ElementAt(0).ascending);
956  NS_ENSURE_SUCCESS(rv, rv);
957 
958  /*
959  * Add the media_item_id to the sort so like values always sort the same
960  */
961  rv = mBuilder->AddOrder(MEDIAITEMS_ALIAS,
962  MEDIAITEMID_COLUMN,
963  mSorts->ElementAt(0).ascending);
964  NS_ENSURE_SUCCESS(rv, rv);
965 
966  return NS_OK;
967  }
968 
969  /*
970  * If this is the custom sort, sort by the "ordinal" column in the
971  * "simple_media_lists" table. Make sure that base table is actually
972  * the simple media lists table before proceeding.
973  */
974  if (mSorts->ElementAt(0).property.Equals(ORDINAL_PROPERTY)) {
975  nsAutoString baseTable;
976  rv = mBuilder->GetBaseTableName(baseTable);
977  NS_ENSURE_SUCCESS(rv, rv);
978 
979  if (baseTable.Equals(SIMPLEMEDIALISTS_TABLE)) {
980  rv = mBuilder->AddOrder(CONSTRAINT_ALIAS,
981  ORDINAL_COLUMN,
982  mSorts->ElementAt(0).ascending);
983  NS_ENSURE_SUCCESS(rv, rv);
984 
985  return NS_OK;
986  }
987 
988  // Default to the created property for non-lists. This addresses
989  // bug 13287 where the sort wasn't specified for some lists
990  mBuilder->AddOrder(CONSTRAINT_ALIAS,
991  CREATED_COLUMN,
992  mSorts->ElementAt(0).ascending);
993  NS_ENSURE_SUCCESS(rv, rv);
994  return NS_OK;
995  }
996 
997  /*
998  * Join an instance of the properties table to the base table
999  */
1000  rv = mBuilder->AddJoin(sbISQLSelectBuilder::JOIN_INNER,
1001  PROPERTIES_TABLE,
1002  SORT_ALIAS,
1003  MEDIAITEMID_COLUMN,
1004  MEDIAITEMS_ALIAS,
1005  MEDIAITEMID_COLUMN);
1006  NS_ENSURE_SUCCESS(rv, rv);
1007 
1008  /*
1009  * Restrict the sort table to the sort property
1010  */
1011  nsCOMPtr<sbISQLBuilderCriterion> criterion;
1012  rv = mBuilder->CreateMatchCriterionLong(SORT_ALIAS,
1013  PROPERTYID_COLUMN,
1014  sbISQLSelectBuilder::MATCH_EQUALS,
1015  GetPropertyId(mSorts->ElementAt(0).property),
1016  getter_AddRefs(criterion));
1017  NS_ENSURE_SUCCESS(rv, rv);
1018 
1019  rv = mBuilder->AddCriterion(criterion);
1020  NS_ENSURE_SUCCESS(rv, rv);
1021 
1022  /*
1023  * Add a sort on the primary sort
1024  */
1025  rv = mBuilder->AddOrder(SORT_ALIAS,
1026  OBJSORTABLE_COLUMN,
1027  mSorts->ElementAt(0).ascending);
1028  NS_ENSURE_SUCCESS(rv, rv);
1029 
1030  /*
1031  * Add a sort on the pre-baked secondary sort, unless
1032  * this is a distinct list, in which case the secondary
1033  * sort will make no difference.
1034  */
1035  if (!mIsDistinct) {
1036 
1037  rv = mBuilder->AddOrder(SORT_ALIAS,
1038  OBJSECONDARYSORTABLE_COLUMN,
1039  mSorts->ElementAt(0).ascending);
1040  NS_ENSURE_SUCCESS(rv, rv);
1041 
1042  /*
1043  * Sort on media_item_id to make the order of the rows always the same. Make
1044  * sure we sort on the same direction as the primary sort so reversing the
1045  * primary sort will reverse the ordering when the primary sort values are
1046  * the same.
1047  */
1048  rv = mBuilder->AddOrder(SORT_ALIAS,
1049  MEDIAITEMID_COLUMN,
1050  mSorts->ElementAt(0).ascending);
1051  NS_ENSURE_SUCCESS(rv, rv);
1052  }
1053 
1054 
1055 
1056  return NS_OK;
1057 
1058 }
1059 
1060 nsresult
1061 sbLocalDatabaseQuery::AddNonNullPrimarySortConstraint()
1062 {
1063  nsresult rv;
1064 
1065  nsCOMPtr<sbISQLBuilderCriterion> criterion;
1066 
1067  if (SB_IsTopLevelProperty(mSorts->ElementAt(0).property)) {
1068  nsAutoString columnName;
1069  rv = SB_GetTopLevelPropertyColumn(mSorts->ElementAt(0).property, columnName);
1070  NS_ENSURE_SUCCESS(rv, rv);
1071 
1072  rv = mBuilder->CreateMatchCriterionNull(MEDIAITEMS_ALIAS,
1073  columnName,
1074  sbISQLSelectBuilder::MATCH_NOTEQUALS,
1075  getter_AddRefs(criterion));
1076  rv = mBuilder->AddCriterion(criterion);
1077  NS_ENSURE_SUCCESS(rv, rv);
1078  }
1079  else {
1080  if (mSorts->ElementAt(0).property.Equals(ORDINAL_PROPERTY)) {
1081 
1082  nsAutoString baseTable;
1083  rv = mBuilder->GetBaseTableName(baseTable);
1084  NS_ENSURE_SUCCESS(rv, rv);
1085 
1086  if (baseTable.Equals(SIMPLEMEDIALISTS_TABLE)) {
1087  rv = mBuilder->CreateMatchCriterionNull(CONSTRAINT_ALIAS,
1088  ORDINAL_COLUMN,
1089  sbISQLSelectBuilder::MATCH_NOTEQUALS,
1090  getter_AddRefs(criterion));
1091  rv = mBuilder->AddCriterion(criterion);
1092  NS_ENSURE_SUCCESS(rv, rv);
1093  }
1094  }
1095  else {
1096  rv = mBuilder->AddJoin(sbISQLSelectBuilder::JOIN_INNER,
1097  PROPERTIES_TABLE,
1098  GETNOTNULL_ALIAS,
1099  MEDIAITEMID_COLUMN,
1100  MEDIAITEMS_ALIAS,
1101  MEDIAITEMID_COLUMN);
1102  NS_ENSURE_SUCCESS(rv, rv);
1103 
1104  rv = mBuilder->CreateMatchCriterionLong(GETNOTNULL_ALIAS,
1105  PROPERTYID_COLUMN,
1106  sbISQLSelectBuilder::MATCH_EQUALS,
1107  GetPropertyId(mSorts->ElementAt(0).property),
1108  getter_AddRefs(criterion));
1109  NS_ENSURE_SUCCESS(rv, rv);
1110  rv = mBuilder->AddCriterion(criterion);
1111  NS_ENSURE_SUCCESS(rv, rv);
1112  }
1113  }
1114 
1115  return NS_OK;
1116 }
1117 
1118 nsresult
1119 sbLocalDatabaseQuery::AddDistinctConstraint()
1120 {
1121  nsresult rv;
1122 
1123  // When doing a distinct query, add a constraint so we only select media items
1124  // that have a value for the primary sort
1125  if (SB_IsTopLevelProperty(mSorts->ElementAt(0).property)) {
1126  nsAutoString columnName;
1127  rv = SB_GetTopLevelPropertyColumn(mSorts->ElementAt(0).property, columnName);
1128  NS_ENSURE_SUCCESS(rv, rv);
1129 
1130  nsCOMPtr<sbISQLBuilderCriterion> notNull;
1131  rv = mBuilder->CreateMatchCriterionNull(MEDIAITEMS_ALIAS,
1132  columnName,
1133  sbISQLSelectBuilder::MATCH_NOTEQUALS,
1134  getter_AddRefs(notNull));
1135  NS_ENSURE_SUCCESS(rv, rv);
1136 
1137  nsCOMPtr<sbISQLBuilderCriterion> notEmptyString;
1138  rv = mBuilder->CreateMatchCriterionString(MEDIAITEMS_ALIAS,
1139  columnName,
1140  sbISQLSelectBuilder::MATCH_NOTEQUALS,
1141  EmptyString(),
1142  getter_AddRefs(notEmptyString));
1143  NS_ENSURE_SUCCESS(rv, rv);
1144 
1145 
1146  nsCOMPtr<sbISQLBuilderCriterion> criterion;
1147  rv = mBuilder->CreateAndCriterion(notNull,
1148  notEmptyString,
1149  getter_AddRefs(criterion));
1150  NS_ENSURE_SUCCESS(rv, rv);
1151 
1152  rv = mBuilder->AddCriterion(criterion);
1153  NS_ENSURE_SUCCESS(rv, rv);
1154  }
1155  else {
1156  rv = mBuilder->AddJoin(sbISQLSelectBuilder::JOIN_INNER,
1157  PROPERTIES_TABLE,
1158  DISTINCT_ALIAS,
1159  MEDIAITEMID_COLUMN,
1160  MEDIAITEMS_ALIAS,
1161  MEDIAITEMID_COLUMN);
1162  NS_ENSURE_SUCCESS(rv, rv);
1163 
1164  nsCOMPtr<sbISQLBuilderCriterion> property;
1165  rv = mBuilder->CreateMatchCriterionLong(DISTINCT_ALIAS,
1166  PROPERTYID_COLUMN,
1167  sbISQLSelectBuilder::MATCH_EQUALS,
1168  GetPropertyId(mSorts->ElementAt(0).property),
1169  getter_AddRefs(property));
1170  NS_ENSURE_SUCCESS(rv, rv);
1171 
1172  nsCOMPtr<sbISQLBuilderCriterion> notEmptyString;
1173  rv = mBuilder->CreateMatchCriterionString(DISTINCT_ALIAS,
1174  OBJSORTABLE_COLUMN,
1175  sbISQLSelectBuilder::MATCH_NOTEQUALS,
1176  EmptyString(),
1177  getter_AddRefs(notEmptyString));
1178  NS_ENSURE_SUCCESS(rv, rv);
1179 
1180  nsCOMPtr<sbISQLBuilderCriterion> criterion;
1181  rv = mBuilder->CreateAndCriterion(property,
1182  notEmptyString,
1183  getter_AddRefs(criterion));
1184  NS_ENSURE_SUCCESS(rv, rv);
1185 
1186  rv = mBuilder->AddCriterion(criterion);
1187  NS_ENSURE_SUCCESS(rv, rv);
1188  }
1189 
1190  return NS_OK;
1191 }
1192 
1193 nsresult
1194 sbLocalDatabaseQuery::AddDistinctGroupBy()
1195 {
1196  nsresult rv;
1197 
1198  if (SB_IsTopLevelProperty(mSorts->ElementAt(0).property)) {
1199  nsAutoString columnName;
1200  rv = SB_GetTopLevelPropertyColumn(mSorts->ElementAt(0).property, columnName);
1201  NS_ENSURE_SUCCESS(rv, rv);
1202 
1203  // Prevent empty values
1204  nsCOMPtr<sbISQLBuilderCriterion> notEmptyString;
1205  rv = mBuilder->CreateMatchCriterionString(MEDIAITEMS_ALIAS,
1206  columnName,
1207  sbISQLSelectBuilder::MATCH_NOTEQUALS,
1208  EmptyString(),
1209  getter_AddRefs(notEmptyString));
1210  NS_ENSURE_SUCCESS(rv, rv);
1211 
1212  rv = mBuilder->AddCriterion(notEmptyString);
1213  NS_ENSURE_SUCCESS(rv, rv);
1214 
1215  rv = mBuilder->AddGroupBy(MEDIAITEMS_ALIAS, columnName);
1216  NS_ENSURE_SUCCESS(rv, rv);
1217 
1218  }
1219  else {
1220  if (mSorts->ElementAt(0).property.Equals(ORDINAL_PROPERTY)) {
1221 
1222  if (mBaseTable.Equals(SIMPLEMEDIALISTS_TABLE)) {
1223  rv = mBuilder->AddGroupBy(CONSTRAINT_ALIAS,
1224  ORDINAL_COLUMN);
1225  NS_ENSURE_SUCCESS(rv, rv);
1226  }
1227  }
1228  else {
1229  nsCOMPtr<sbISQLBuilderCriterion> notEmptyString;
1230  rv = mBuilder->CreateMatchCriterionString(SORT_ALIAS,
1231  OBJSORTABLE_COLUMN,
1232  sbISQLSelectBuilder::MATCH_NOTEQUALS,
1233  EmptyString(),
1234  getter_AddRefs(notEmptyString));
1235  NS_ENSURE_SUCCESS(rv, rv);
1236 
1237  rv = mBuilder->AddCriterion(notEmptyString);
1238  NS_ENSURE_SUCCESS(rv, rv);
1239 
1240  rv = mBuilder->AddGroupBy(SORT_ALIAS, OBJSORTABLE_COLUMN);
1241  NS_ENSURE_SUCCESS(rv, rv);
1242  }
1243  }
1244 
1245  return NS_OK;
1246 }
1247 
1248 nsresult
1249 sbLocalDatabaseQuery::AddJoinToGetNulls()
1250 {
1251  nsresult rv;
1252 
1253  if (SB_IsTopLevelProperty(mSorts->ElementAt(0).property)) {
1254  nsAutoString columnName;
1255  rv = SB_GetTopLevelPropertyColumn(mSorts->ElementAt(0).property, columnName);
1256  NS_ENSURE_SUCCESS(rv, rv);
1257 
1258  nsCOMPtr<sbISQLBuilderCriterion> criterion;
1259  rv = mBuilder->CreateMatchCriterionNull(MEDIAITEMS_ALIAS,
1260  columnName,
1261  sbISQLSelectBuilder::MATCH_EQUALS,
1262  getter_AddRefs(criterion));
1263  NS_ENSURE_SUCCESS(rv, rv);
1264 
1265  rv = mBuilder->AddCriterion(criterion);
1266  NS_ENSURE_SUCCESS(rv, rv);
1267  }
1268  else {
1269  nsCOMPtr<sbISQLBuilderCriterion> criterionGuid;
1270  rv = mBuilder->CreateMatchCriterionTable(GETNULL_ALIAS,
1271  MEDIAITEMID_COLUMN,
1272  sbISQLSelectBuilder::MATCH_EQUALS,
1273  MEDIAITEMS_ALIAS,
1274  MEDIAITEMID_COLUMN,
1275  getter_AddRefs(criterionGuid));
1276  NS_ENSURE_SUCCESS(rv, rv);
1277 
1278  nsCOMPtr<sbISQLBuilderCriterion> criterionProperty;
1279  rv = mBuilder->CreateMatchCriterionLong(GETNULL_ALIAS,
1280  PROPERTYID_COLUMN,
1281  sbISQLSelectBuilder::MATCH_EQUALS,
1282  GetPropertyId(mSorts->ElementAt(0).property),
1283  getter_AddRefs(criterionProperty));
1284  NS_ENSURE_SUCCESS(rv, rv);
1285 
1286  nsCOMPtr<sbISQLBuilderCriterion> criterion;
1287  rv = mBuilder->CreateAndCriterion(criterionGuid,
1288  criterionProperty,
1289  getter_AddRefs(criterion));
1290  NS_ENSURE_SUCCESS(rv, rv);
1291 
1292  rv = mBuilder->AddJoinWithCriterion(sbISQLSelectBuilder::JOIN_LEFT,
1293  PROPERTIES_TABLE,
1294  GETNULL_ALIAS,
1295  criterion);
1296  NS_ENSURE_SUCCESS(rv, rv);
1297 
1298  rv = mBuilder->CreateMatchCriterionNull(GETNULL_ALIAS,
1299  OBJSORTABLE_COLUMN,
1300  sbISQLSelectBuilder::MATCH_EQUALS,
1301  getter_AddRefs(criterion));
1302  NS_ENSURE_SUCCESS(rv, rv);
1303 
1304  rv = mBuilder->AddCriterion(criterion);
1305  NS_ENSURE_SUCCESS(rv, rv);
1306  }
1307 
1308  return NS_OK;
1309 }
1310 
1311 nsresult
1312 sbLocalDatabaseQuery::AddResortColumns()
1313 {
1314  nsresult rv;
1315 
1316  rv = mBuilder->SetDistinct(PR_TRUE);
1317  NS_ENSURE_SUCCESS(rv, rv);
1318 
1319  rv = mBuilder->AddColumn(MEDIAITEMS_ALIAS, MEDIAITEMID_COLUMN);
1320  NS_ENSURE_SUCCESS(rv, rv);
1321 
1322  rv = mBuilder->AddColumn(MEDIAITEMS_ALIAS, GUID_COLUMN);
1323  NS_ENSURE_SUCCESS(rv, rv);
1324 
1325  if (mIsFullLibrary) {
1326  rv = mBuilder->AddColumn(EmptyString(), NS_LITERAL_STRING("''"));
1327  NS_ENSURE_SUCCESS(rv, rv);
1328 
1329  rv = mBuilder->AddColumn(MEDIAITEMS_ALIAS, ROWID_COLUMN);
1330  NS_ENSURE_SUCCESS(rv, rv);
1331  }
1332  else {
1333  rv = mBuilder->AddColumn(CONSTRAINT_ALIAS, ORDINAL_COLUMN);
1334  NS_ENSURE_SUCCESS(rv, rv);
1335 
1336  rv = mBuilder->AddColumn(CONSTRAINT_ALIAS, ROWID_COLUMN);
1337  NS_ENSURE_SUCCESS(rv, rv);
1338  }
1339 
1340  return NS_OK;
1341 }
1342 
1343 nsresult
1344 sbLocalDatabaseQuery::AddMultiSorts()
1345 {
1346  nsresult rv;
1347  PRUint32 numSorts = mSorts->Length();
1348  for (PRUint32 i = 1; i < numSorts; i++) {
1349  const sbLocalDatabaseGUIDArray::SortSpec& sort = mSorts->ElementAt(i);
1350 
1351  nsString joinedAlias(SORT_ALIAS);
1352  joinedAlias.AppendInt(i);
1353 
1354  nsCOMPtr<sbISQLBuilderCriterion> criterionGuid;
1355  rv = mBuilder->CreateMatchCriterionTable(joinedAlias,
1356  MEDIAITEMID_COLUMN,
1357  sbISQLSelectBuilder::MATCH_EQUALS,
1358  MEDIAITEMS_ALIAS,
1359  MEDIAITEMID_COLUMN,
1360  getter_AddRefs(criterionGuid));
1361  NS_ENSURE_SUCCESS(rv, rv);
1362 
1363  nsCOMPtr<sbISQLBuilderCriterion> criterion;
1364  if (SB_IsTopLevelProperty(sort.property)) {
1365  rv = mBuilder->AddJoinWithCriterion(sbISQLSelectBuilder::JOIN_INNER,
1366  MEDIAITEMS_TABLE,
1367  joinedAlias,
1368  criterion);
1369  NS_ENSURE_SUCCESS(rv, rv);
1370 
1371  nsString columnName;
1372  rv = SB_GetTopLevelPropertyColumn(sort.property, columnName);
1373  NS_ENSURE_SUCCESS(rv, rv);
1374 
1375  mBuilder->AddOrder(joinedAlias, columnName, sort.ascending);
1376  NS_ENSURE_SUCCESS(rv, rv);
1377  }
1378  else {
1379  nsCOMPtr<sbISQLBuilderCriterion> criterionProperty;
1380  rv = mBuilder->CreateMatchCriterionLong(joinedAlias,
1381  NS_LITERAL_STRING("property_id"),
1382  sbISQLSelectBuilder::MATCH_EQUALS,
1383  GetPropertyId(sort.property),
1384  getter_AddRefs(criterionProperty));
1385  NS_ENSURE_SUCCESS(rv, rv);
1386 
1387  rv = mBuilder->CreateAndCriterion(criterionGuid,
1388  criterionProperty,
1389  getter_AddRefs(criterion));
1390  NS_ENSURE_SUCCESS(rv, rv);
1391 
1392  rv = mBuilder->AddJoinWithCriterion(sbISQLSelectBuilder::JOIN_LEFT,
1393  PROPERTIES_TABLE,
1394  joinedAlias,
1395  criterion);
1396  NS_ENSURE_SUCCESS(rv, rv);
1397 
1398  rv = mBuilder->AddOrder(joinedAlias, OBJSORTABLE_COLUMN, sort.ascending);
1399  NS_ENSURE_SUCCESS(rv, rv);
1400  }
1401 
1402  }
1403 
1404  // Sort on the base table's media_item_id column so make sure things are
1405  // sorted the same on all platforms
1406  rv = mBuilder->AddOrder(MEDIAITEMS_ALIAS, MEDIAITEMID_COLUMN, PR_TRUE);
1407  NS_ENSURE_SUCCESS(rv, rv);
1408 
1409  return NS_OK;
1410 }
1411 
1412 PRInt32
1413 sbLocalDatabaseQuery::GetPropertyId(const nsAString& aProperty)
1414 {
1415  return SB_GetPropertyId(aProperty, mPropertyCache);
1416 }
1417 
1418 void
1419 sbLocalDatabaseQuery::MaxExpr(const nsAString& aAlias,
1420  const nsAString& aColumn,
1421  nsAString& aExpr)
1422 {
1423  nsAutoString buff;
1424  buff.AssignLiteral("max(");
1425  buff.Append(aAlias);
1426  buff.AppendLiteral(".");
1427  buff.Append(aColumn);
1428  buff.AppendLiteral(")");
1429 
1430  aExpr = buff;
1431 }
1432 
sbLocalDatabaseQuery::GetNullResortQuery
nsresult GetNullResortQuery(nsAString &aQuery)
Definition: sbLocalDatabaseQuery.cpp:400
CREATED_COLUMN
#define CREATED_COLUMN
Definition: sbLocalDatabaseQuery.cpp:42
MEDIALISTYPEID_COLUMN
#define MEDIALISTYPEID_COLUMN
Definition: sbLocalDatabaseQuery.cpp:51
NS_OK
return NS_OK
Definition: sbLibraryConstraints.cpp:90
sbLocalDatabaseQuery::GetFullGuidRangeQuery
nsresult GetFullGuidRangeQuery(nsAString &aQuery)
Definition: sbLocalDatabaseQuery.cpp:140
GUID_COLUMN
#define GUID_COLUMN
Definition: sbLocalDatabaseQuery.cpp:43
sbILocalDatabasePropertyCache
[USER CODE SHOULD NOT REFERENCE THIS CLASS]
Definition: sbILocalDatabasePropertyCache.idl:48
sbLocalDatabaseQuery::GetResortQuery
nsresult GetResortQuery(nsAString &aQuery)
Definition: sbLocalDatabaseQuery.cpp:324
pos
_dialogDatepicker pos
Definition: jquery-ui-personalized-1.6rc2.min.js:395
sbLocalDatabaseQuery::GetFullCountQuery
nsresult GetFullCountQuery(nsAString &aQuery)
Definition: sbLocalDatabaseQuery.cpp:112
nsString_ReplaceSubstring
void nsString_ReplaceSubstring(nsAString &aString, const nsAString &aOldString, const nsAString &aNewString)
Definition: sbStringUtils.cpp:252
sbLocalDatabaseQuery::GetNonNullCountQuery
nsresult GetNonNullCountQuery(nsAString &aQuery)
Definition: sbLocalDatabaseQuery.cpp:192
GETNULL_ALIAS
#define GETNULL_ALIAS
Definition: sbLocalDatabaseQuery.cpp:65
SB_GetTopLevelPropertyColumn
static nsresult SB_GetTopLevelPropertyColumn(const nsAString &aProperty, nsAString &aColumnName)
Definition: sbLocalDatabaseSchemaInfo.h:147
sbLocalDatabaseQuery::GetNullGuidRangeQuery
nsresult GetNullGuidRangeQuery(nsAString &aQuery)
Definition: sbLocalDatabaseQuery.cpp:224
COUNT_COLUMN
#define COUNT_COLUMN
Definition: sbLocalDatabaseQuery.cpp:41
SB_IsTopLevelProperty
static PRBool SB_IsTopLevelProperty(PRUint32 aPropertyDBID)
Definition: sbLocalDatabaseSchemaInfo.h:127
SORT_ALIAS
#define SORT_ALIAS
Definition: sbLocalDatabaseQuery.cpp:66
CONPROP_ALIAS
#define CONPROP_ALIAS
Definition: sbLocalDatabaseQuery.cpp:68
sbLocalDatabaseQuery::sbLocalDatabaseQuery
sbLocalDatabaseQuery(const nsAString &aBaseTable, const nsAString &aBaseConstraintColumn, PRUint32 aBaseConstraintValue, const nsAString &aBaseForeignKeyColumn, nsTArray< sbLocalDatabaseGUIDArray::FilterSpec > *aFilters, nsTArray< sbLocalDatabaseGUIDArray::SortSpec > *aSorts, PRBool aIsDistinct, PRBool aDistinctWithSortableValues, sbILocalDatabasePropertyCache *aPropertyCache)
Definition: sbLocalDatabaseQuery.cpp:73
ORDINAL_COLUMN
#define ORDINAL_COLUMN
Definition: sbLocalDatabaseQuery.cpp:49
OBJ_COLUMN
#define OBJ_COLUMN
Definition: sbLocalDatabaseQuery.cpp:44
MEDIAITEMID_COLUMN
#define MEDIAITEMID_COLUMN
Definition: sbLocalDatabaseQuery.cpp:47
OBJSECONDARYSORTABLE_COLUMN
#define OBJSECONDARYSORTABLE_COLUMN
Definition: sbLocalDatabaseQuery.cpp:46
SB_SQLBUILDER_SELECT_CONTRACTID
#define SB_SQLBUILDER_SELECT_CONTRACTID
Definition: sbSQLBuilderCID.h:30
count
var count
Definition: test_bug7406.js:32
GETNOTNULL_ALIAS
#define GETNOTNULL_ALIAS
Definition: sbLocalDatabaseQuery.cpp:64
CONSTRAINT_ALIAS
#define CONSTRAINT_ALIAS
Definition: sbLocalDatabaseQuery.cpp:63
DISTINCT_ALIAS
#define DISTINCT_ALIAS
Definition: sbLocalDatabaseQuery.cpp:67
SB_GetPropertyId
static PRInt32 SB_GetPropertyId(const nsAString &aProperty, sbILocalDatabasePropertyCache *aPropertyCache)
Definition: sbLocalDatabaseSchemaInfo.h:199
sbISQLBuilder::MATCH_LESS
const unsigned long MATCH_LESS
Definition: sbISQLBuilder.idl:53
sbISQLBuilder::MATCH_MATCH
const unsigned long MATCH_MATCH
Definition: sbISQLBuilder.idl:57
PROPERTIES_TABLE
#define PROPERTIES_TABLE
Definition: sbLocalDatabaseQuery.cpp:55
sbISQLBuilder::JOIN_INNER
const unsigned long JOIN_INNER
Definition: sbISQLBuilder.idl:79
match
ExtensionSchemeMatcher prototype match
Definition: head_songbird.js:422
sbISQLBuilder::JOIN_LEFT
const unsigned long JOIN_LEFT
Definition: sbISQLBuilder.idl:80
ISLIST_PROPERTY
#define ISLIST_PROPERTY
Definition: sbLocalDatabaseQuery.cpp:71
sbISQLBuilder::MATCH_NOTEQUALS
const unsigned long MATCH_NOTEQUALS
Definition: sbISQLBuilder.idl:50
PROPERTYID_COLUMN
#define PROPERTYID_COLUMN
Definition: sbLocalDatabaseQuery.cpp:48
PROPERTIES_FTS_ALL_TABLE
#define PROPERTIES_FTS_ALL_TABLE
Definition: sbLocalDatabaseQuery.cpp:57
OBJSORTABLE_COLUMN
#define OBJSORTABLE_COLUMN
Definition: sbLocalDatabaseQuery.cpp:45
MEDIAITEMS_TABLE
#define MEDIAITEMS_TABLE
Definition: sbLocalDatabaseQuery.cpp:58
SIMPLEMEDIALISTS_TABLE
#define SIMPLEMEDIALISTS_TABLE
Definition: sbLocalDatabaseQuery.cpp:59
ORDINAL_PROPERTY
#define ORDINAL_PROPERTY
Definition: sbLocalDatabaseQuery.cpp:70
i
_getSelectedPageStyle s i
Definition: nsSessionStore.js:1538
nsString_FindCharInSet
PRInt32 nsString_FindCharInSet(const nsAString &aString, const char *aPattern, PRInt32 aOffset)
Definition: sbStringUtils.cpp:38
sbLocalDatabaseQuery::GetPrefixSearchQuery
nsresult GetPrefixSearchQuery(nsAString &aQuery)
Definition: sbLocalDatabaseQuery.cpp:265
ROWID_COLUMN
#define ROWID_COLUMN
Definition: sbLocalDatabaseQuery.cpp:53
MEDIAITEMS_ALIAS
#define MEDIAITEMS_ALIAS
Definition: sbLocalDatabaseQuery.cpp:62
sbISQLBuilder::MATCH_EQUALS
const unsigned long MATCH_EQUALS
Definition: sbISQLBuilder.idl:49