在IndexedDB中搜索复合索引 [英] Searching for compound indexes in IndexedDB

问题描述

在阅读这里好几年之后，我终于注册了一个问题。最近我一直在使用IndexedDB，并且偶然发现了复合索引的一个问题（我使用它们类似于这里）。

我在对象存储中有一个对象，其中包含一个字符串值和一对整数值。 E. g。：

  [description：text，value1：int，value2：int，value3：int] 
  

我在这个对象上创建了一个复合索引，如下所示：

 （compoundIndex，[value1，value2，value3]，{unique：false}）; 
  

在html中我有几个选择框和一个文本字段，允许用户搜索具体条目。整数作为keyrange传递给索引上的opencursor函数。然后我在结果集上使用indexOf（textfield）（就像完成这里）

如果选择框有一个值，则该值用作上限和下限。如果选择框未触及，则较低范围为1，而较高范围是我声明的MAX_INT变量（如所描述的）。

示例代码：

  transaction = db.transaction（[schaden]，readonly）。objectStore（schaden）; 
 index = transaction.index（compoundIndex）; 
 
 //另一个函数的keyrange-arrays 
 lowerBound = [valueOneLower，valueTwoLower，valueThreeLower]; 
 upperBound = [valueOneUpper，valueTwoUpper，valueThreeUpper]; 
 range = IDBKeyRange.bound（lowerBound，upperBound）; 
 
 index.openCursor（range）.onsuccess = function（e）{
 var cursor = e.target.result; 
 if（cursor）{
 if（getTextfield.length == 0）{
 console.log（Entry found：+ cursor.value.description +。Object：+ JSON .stringify（cursor.value））; 
} else if（cursor.value.bezeichnung.indexOf（getTextfield）！== -1）{
 console.log（条目找到：+ cursor.value.description +。对象： + JSON.stringify（cursor.value））; 
}; 
 cursor ['continue']（）; 
}; 
}; 
  

当我在所有选择框中设置所有值时，我可以很好地搜索条目。但是，如果我打开一个字段，会导致搜索错误。假设我没有触及value1选择框，并将其他框设置为2，我会得到lowerBound = [1,2,2]和upperBound = [4294967295,2,2]。这会让我回到我的IDB中的所有条目，它不考虑第二和第三个值。

这是打算吗？或者有没有办法解决这个问题？我一直在寻找有关这个问题的信息，但似乎已陷入死胡同。我对这个API的天真理解让我相信它会在搜索中考虑所有的数组字段。由于该对象及其使用的索引比上述示例复杂得多，因此在多个索引上执行搜索将非常麻烦。

感谢您的洞察！ / p>

编辑：
为了让第一条评论更清楚一点。假设在对象存储中有以下对象：

  obj1 {val1 = 1，val2 = 3，val3 = 1} 
 obj2 {val1 = 1，val2 = 2，val3 = 2} 
 obj3 {val1 = 2，val2 = 1，val3 = 3} 
 obj4 {val1 = 1，val2 = 1， val3 = 1} 
 obj5 {val1 = 1，val2 = 2，val3 = 3} 
  

索引按照预期方式排序：

 ＃1 [1,1,1] obj4 
＃2 [ 1,2,2] obj2 
＃3 [1,2,3] obj5 
＃4 [1,3,1] obj1 
＃5 [2,1,3] obj3 
 
 
 $ b $ p 
 $ b 
现在我们假设我搜索范围（下[1,1,1]，上[1， 1,1]）我会得到obj4。这是所有选择框选择了选项1时的行为。
现在，如果我搜索val1 = 1，val2 = unknown和val3 = 1的条目，我会得到以下范围：lower [1,1,1 ]，上[1,4294967295,1]。预期结果是obj4 [1,1,1]和obj1 [1,3,1]。取而代之的是，结果给了我4个命中，即obj4，obj2，obj5和obj1，尽管obj2和obj5的val3与关键字范围不匹配。
解决方案
 
 
 
 
在数组上创建索引时，如果数组中的每个元素对应于一个属性，则商店的条目只会出现在索引中底层对象有一个定义的值。
 
 
 
为了解决这个障碍，总是将定义的值存储在底层对象存储中。例如，要表示布尔属性，请使用整数，其中0为false，1为true。这样，商店中的每个对象都可以出现在索引中。 indexedDB在这里的行为与普通的旧javascript中的truthy / falsy处理完全不同（0 == undefined）。
在基于数组的索引中必须为数组的每个元素使用已定义的参数。
 
 
要绕过这个障碍，必须指定所有边界，即使这些边界不是真正的价值观（例如，就像我所举的例子中你所链接的那样，200最大年龄的作品，因为我们可以安全地假设没有人是200岁）。 
 
因此，为了解决您的问题，您的代码中可能存在一个问题，即边界变量（[valueOneLower，valueTwoLower，valueThreeLower]或[valueOneUpper，valueTwoUpper，valueThreeUpper]）的其中一个参数是未定义。
 
 
 
根据您的意见，我建议您使用 indexedDB.cmp 。编写这些测试非常简单。它不需要任何数据库连接。这是一个非常基本的例子：
 
 
  //建立测试值
 
 var lower1 = 1，lower2 = 1，lower3 = 1; 
 var upper1 = 3，upper3 = 3，upper3 = 3; 
 var middle1 = 2，middle2 = 2，middle3 = 2; 
 
 var lowerBound = [lower1，lower2，lower3]; 
 var upperBound = [upper1，upper2，upper3]; 
 var middleValue = [middle1，middle2，middle3]; 
 
 //如链接页面提供，如果第一个小于第二个，则cmp返回-1，如果相等则返回0，如果第一个大于第二个，则返回1。 
 
 var lowerVsMiddle = indexedDB.cmp（lowerBound，middleValue）; 
 console.log（'是％s<％s？％s'，lowerBound，middleValue，lowerVsMiddle == -1）; 
 console.log（'％s>％s？％s'，lowerBound，middleValue，lowerVsMiddle == 1）; 
 
 var upperVsMiddle = indexedDB.cmp（upperBound，middleValue）; 
 console.log（'是％s<％s？％s'，upperBound，middleValue，upperVsMiddle == -1）; 
 console.log（'％s>％s？％s'，upperBound，middleValue，upperVsMiddle == 1）; 
  
您应该可以通过运行这样的测试来准确回答您的问题。
 
 
 
我检索了 indexedDB spec 给你。首先请注意如果数组中的每个项都已定义并且是有效键，则数组只是一个有效键...。这与该对象是否会出现在索引中有关系，并且与您的关键参数是否与cmp或IDBKeyRange.bound / lowerBound / upperBound一起工作。其次，请注意以下内容：
 
 
 
将Array类型的值与Array类型的其他值进行比较，如下所示：
 
 
设A为第一个Array值，B为第二个Array值。 
 
 
让长度为A的长度和B的长度中的较小者。如果A的第i个值小于B的第i个值，那么A就是小于
的元素。
比B.略过其余的步骤。  
 
如果A的第i个值大于B的第i个值，那么A大于B.跳过其余的步骤。 
 
 
将i增加1. 
 
 
如果我不等于长度，请返回步骤4。否则继续下一步。如果A的长度小于B的长度，则A小于B.如果A的长度大于B的长度，则A大于B，否则A和B相等。 
 
 
从KeyRange部分：如果满足以下两个条件，则键在键范围内： 
 
 
 
 
键范围下限值未定义或小于键。如果lowerOpen为false，它也可能等于key。 
 
 
键范围上限值未定义或大于键。如果upperOpen为false，它也可能等于键。
 
 
 
 
 
现在再次澄清我基于评论和您的理解了您的问题进一步的编辑：基本上indexedDB提供了条件的联合行为，但你想要的是一个交集。解决这个问题的方法之一就是不要考虑普通形式的数据，而是要考虑如何设置数据，以便以您想要的方式查询数据。这是有趣的思考，我没有立即回答你。
 
After reading here for ages, I've finally registered to ask a question. I've been messing around with IndexedDB lately and stumbled over a problem with compound indexes (I use them somilar to the example here). 

I have an object in the objectstore with a string value, and a couple of integer values. E. g.:
[description:text, value1:int, value2:int, value3:int]
I created a compound index on this object like this:  
("compoundIndex", ["value1" , "value2" , "value3"] , { unique: false });
In the html I got a couple of select boxes and a textfield, that allows the user to search for specific entries. The integers are passed as a keyrange to the opencursor-function on the index. Then I use indexOf(textfield) on the resulting set (like done here) 

If the selectbox has a value, that value is used as upper and lower bound. If the select box is untouched, the lower range is 1 and the upper is a MAX_INT variable I declared (like described here).

sample code:
transaction = db.transaction(["schaden"] , "readonly").objectStore("schaden");
index = transaction.index("compoundIndex");

// keyrange-arrays from another function    
lowerBound = [valueOneLower, valueTwoLower, valueThreeLower];
upperBound = [valueOneUpper, valueTwoUpper, valueThreeUpper];
range = IDBKeyRange.bound( lowerBound, upperBound );

index.openCursor(range).onsuccess = function(e){
  var cursor = e.target.result;
  if (cursor){
    if (getTextfield.length == 0){
      console.log("Entry found: " + cursor.value.description + ". Object: " + JSON.stringify(cursor.value));
    }else if (cursor.value.bezeichnung.indexOf(getTextfield) !== -1){
      console.log("Entry found: " + cursor.value.description + ". Object: " + JSON.stringify(cursor.value));
    };
    cursor['continue']();                           
    };
  };    
I can search for entries perfectly well, when I have all values set in all the select-boxes. However, if I leave a field open, it messes up the search. Let's say I have not touched the value1-select box, and set the other boxes to 2, I'll get the lowerBound = [1,2,2] and the upperBound = [4294967295,2,2]. This will give me back all entries in my IDB, it doesn't take the 2nd and 3rd value into account.

Is this intended? Or is there a way around this? I have been searching for information about this over and over but seem to be in a dead end. My naive understanding of this API led me to believe it would take all array fields into account on the search. Since the object and therefor also the index I use are much more complex than the example above, performing searches on multiple indexes would be quite messy.

Thanks for your insights!

Edit:
To make it a little more clear after the first comments. Let's say if have the following object in the object store:  
obj1 { val1 = 1 , val2 = 3 , val3 = 1 }
obj2 { val1 = 1 , val2 = 2 , val3 = 2 }
obj3 { val1 = 2 , val2 = 1 , val3 = 3 }
obj4 { val1 = 1 , val2 = 1 , val3 = 1 }
obj5 { val1 = 1 , val2 = 2 , val3 = 3 }
The index sorts it the way expected:  
#1 [1,1,1] obj4
#2 [1,2,2] obj2
#3 [1,2,3] obj5
#4 [1,3,1] obj1
#5 [2,1,3] obj3
Let's assume now I search for the range (lower[1,1,1] , upper[1,1,1]) I'll get obj4. This is the behaviour when all select boxes have selected option 1.
Now if I search for an entry with val1 = 1, val2 = unknown and val3 = 1, I get the following range: lower[1,1,1] , upper[1,4294967295,1]. Expected results are obj4 [1,1,1] and obj1 [1,3,1]. Instead of these, the result is giving me 4 hits, namely obj4, obj2, obj5 and obj1 although val3 of obj2 and obj5 doesn't match the key range.
解决方案

When you create an index on an array, the entries of your store only appear in the index if each element in the array that corresponds to a property in the underlying object has a defined value.

To get around this obstacle, always store defined values in your underlying object store. For example, to represent a boolean property, use an integer, where 0 is false, and 1 is true. This way, each object in the store can appear in the index. indexedDB's behavior here is quite different than truthy/falsy handling in plain old javascript (where 0 == undefined ).
The key range you specify when opening a cursor on a array-based index must use defined parameters for each element of the array.

To get around this obstacle, you must specify all boundaries, even if those boundaries are not real values (e.g. like in my example you linked to, 200 as max age works because we can safely assume no one is 200 yrs old).
So to address your question, it might be a problem in your code in that one of the parameters to your boundaries variables (either [valueOneLower, valueTwoLower, valueThreeLower] or [valueOneUpper, valueTwoUpper, valueThreeUpper]) is not defined.

Based on your comments, I suggest that you test your expectations with indexedDB.cmp. It is pretty simple to write these tests. It does not require any database connection. Here is a pretty basic example to get you started:
// Build our test values

var lower1 = 1, lower2 = 1, lower3 = 1;
var upper1 = 3, upper3 = 3, upper3 = 3;
var middle1 = 2, middle2 = 2, middle3 = 2;

var lowerBound = [lower1,lower2,lower3];
var upperBound = [upper1,upper2,upper3];
var middleValue = [middle1,middle2,middle3];

// As the linked page provides, cmp returns -1 if first is less than second, 0 if equal, 1 if first is greater than second.

var lowerVsMiddle = indexedDB.cmp(lowerBound, middleValue);
console.log('Is %s < %s ? %s', lowerBound, middleValue, lowerVsMiddle == -1);
console.log('Is %s > %s ? %s', lowerBound, middleValue, lowerVsMiddle == 1);

var upperVsMiddle = indexedDB.cmp(upperBound, middleValue);
console.log('Is %s < %s ? %s', upperBound, middleValue, upperVsMiddle == -1);
console.log('Is %s > %s ? %s', upperBound, middleValue, upperVsMiddle == 1);
You should be able to answer your questions accurately by running tests like this.

I retrieved the relevant part of the indexedDB spec for you. First note that "An Array is only a valid key if every item in the array is defined and is a valid key...". This ties into whether the object will appear in the index, and also ties into whether your key parameters to either cmp or IDBKeyRange.bound/lowerBound/upperBound will work. Second, farther down, note the following: 

Values of type Array are compared to other values of type Array as follows:

Let A be the first Array value and B be the second Array value. 
Let length be the lesser of A's length and B's length. 
Let i be 0. 
If the ith value of A is less than the ith value of B, then A is less
than B. Skip the remaining steps. 
If the ith value of A is greater than the ith value of B, then A is greater than B. Skip the remaining steps. 
Increase i by 1. 
If i is not equal to length, go back to step 4. Otherwise continue to next step. 
If A's length is less than B's length, then A is less than B.  If A's length is greater than B's length, then A is greater than B. Otherwise A and B    are equal.
From the KeyRange section: A key is in a key range if both the following conditions are fulfilled:


The key range lower value is undefined or less than key. It may also be equal to key if lowerOpen is false. 
The key range upper value is undefined or greater than key. It may also be equal to key if upperOpen is false.


One more clarification now that I understand your question based on the comments and your further edits: Essentially indexedDB is providing a union behavior of the criteria but you want is an intersection. One way of solving this is to not think about the data in normal form at all, but to think about how to setup the data so it can be queried in the manner you want. It is interesting food for thought and I do not have an immediate answer for you.

                        
这篇关于在IndexedDB中搜索复合索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！