Repository: dbpunk-labs/db3 (language: rust)

Issue:
Support filter condition `>, <, >=, <=`
## Motivations

<!--
If your feature request is related to a problem, please describe it.
-->

- Would you like to implement this feature? [y/n]

## Solution

<!-- Describe the solution you'd like. -->

## Alternatives

<!-- Describe any alternative solutions or features you've considered. -->

## Additional context

<!-- Add any other context or screenshots about the feature request here. -->



Discussion / hints:

Fix patch:
diff --git a/src/base/src/bson_util.rs b/src/base/src/bson_util.rs
index c37c026a..9211fe20 100644
--- a/src/base/src/bson_util.rs
+++ b/src/base/src/bson_util.rs
@@ -177,7 +177,11 @@ pub fn filter_from_json_value(json_str: &str) -> std::result::Result<Option<Filt
         })?;
         let op = match op_str {
             "==" => Operator::Equal,
-            ">" | "<" | ">=" | "<=" | "!=" => {
+            ">" => Operator::GreaterThan,
+            "<" => Operator::LessThan,
+            ">=" => Operator::GreaterThanOrEqual,
+            "<=" => Operator::LessThanOrEqual,
+            "!=" => {
                 return Err(DB3Error::InvalidFilterOp(format!(
                     "OP {} un-support currently",
                     op_str
@@ -406,6 +410,35 @@ mod tests {
             serde_json::to_string(&filter).unwrap()
         );
 
+        let filter = filter_from_json_value(r#"{"field": "flag", "value": true, "op": ">="}"#)
+            .unwrap()
+            .unwrap();
+        assert_eq!(
+            r#"{"filter_type":{"FieldFilter":{"field":"flag","op":4,"value":{"value_type":{"BooleanValue":true}}}}}"#,
+            serde_json::to_string(&filter).unwrap()
+        );
+        let filter = filter_from_json_value(r#"{"field": "flag", "value": true, "op": ">"}"#)
+            .unwrap()
+            .unwrap();
+        assert_eq!(
+            r#"{"filter_type":{"FieldFilter":{"field":"flag","op":3,"value":{"value_type":{"BooleanValue":true}}}}}"#,
+            serde_json::to_string(&filter).unwrap()
+        );
+        let filter = filter_from_json_value(r#"{"field": "flag", "value": true, "op": "<="}"#)
+            .unwrap()
+            .unwrap();
+        assert_eq!(
+            r#"{"filter_type":{"FieldFilter":{"field":"flag","op":2,"value":{"value_type":{"BooleanValue":true}}}}}"#,
+            serde_json::to_string(&filter).unwrap()
+        );
+        let filter = filter_from_json_value(r#"{"field": "flag", "value": true, "op": "<"}"#)
+            .unwrap()
+            .unwrap();
+        assert_eq!(
+            r#"{"filter_type":{"FieldFilter":{"field":"flag","op":1,"value":{"value_type":{"BooleanValue":true}}}}}"#,
+            serde_json::to_string(&filter).unwrap()
+        );
+
         assert!(filter_from_json_value("{}").is_err());
         assert!(filter_from_json_value(r#"{"field": "name"}"#).is_err());
     }
diff --git a/src/node/src/command.rs b/src/node/src/command.rs
index f85fd833..69fdea6d 100644
--- a/src/node/src/command.rs
+++ b/src/node/src/command.rs
@@ -687,7 +687,7 @@ mod tests {
         assert!(cmd.execute(&mut ctx).await.is_ok());
         std::thread::sleep(time::Duration::from_millis(2000));
 
-        // run show document limit 2
+        // run show document no limit
         // r#"{"name": "John Doe","age": 43,"phones": ["+44 1234567","+44 2345678"]}"#
         // r#"{"name": "Mike","age": 44,"phones": ["+44 1234567","+44 2345678"]}"#.to_string(),
         // r#"{"name": "Bill","age": 44,"phones": ["+44 1234567","+44 2345678"]}"#.to_string(),
@@ -704,7 +704,7 @@ mod tests {
         let doc_id3 = table.get_row(2).unwrap().get_cell(0).unwrap().get_content();
         let doc_id4 = table.get_row(3).unwrap().get_cell(0).unwrap().get_content();
 
-        // run show document limit 2
+        // run show document limit 3
         let cmd = DB3ClientCommand::ShowDocument {
             addr: addr.clone(),
             collection_name: collection_books.to_string(),
@@ -714,32 +714,59 @@ mod tests {
         assert_eq!(3, cmd.execute(&mut ctx).await.unwrap().len());
 
         // run show document --filter = '{"field": "name", "value": "Bill", "op": "=="}'
-        let cmd = DB3ClientCommand::ShowDocument {
-            addr: addr.clone(),
-            collection_name: collection_books.to_string(),
-            filter: r#"{"field": "name", "value": "Bill", "op": "=="}"#.to_string(),
-            limit: -1,
-        };
-        if let Ok(table) = cmd.execute(&mut ctx).await {
-            assert_eq!(2, table.len());
-            assert!(table
-                .get_row(0)
-                .unwrap()
-                .get_cell(2)
-                .unwrap()
-                .get_content()
-                .contains(r#""name": String("Bill")"#));
-            assert!(table
-                .get_row(1)
-                .unwrap()
-                .get_cell(2)
-                .unwrap()
-                .get_content()
-                .contains(r#""name": String("Bill")"#));
-        } else {
-            assert!(false)
-        }
 
+        for (filter, exp) in [
+            (
+                r#"{"field": "name", "value": "Bill", "op": "=="}"#,
+                vec![r#""name": String("Bill")"#, r#""name": String("Bill")"#],
+            ),
+            (
+                r#"{"field": "name", "value": "John Doe", "op": "<"}"#,
+                vec![r#""name": String("Bill")"#, r#""name": String("Bill")"#],
+            ),
+            (
+                r#"{"field": "name", "value": "John Doe", "op": "<="}"#,
+                vec![
+                    r#""name": String("Bill")"#,
+                    r#""name": String("Bill")"#,
+                    r#""name": String("John Doe")"#,
+                ],
+            ),
+            (
+                r#"{"field": "name", "value": "John Doe", "op": ">="}"#,
+                vec![r#""name": String("John Doe")"#, r#""name": String("Mike")"#],
+            ),
+            (
+                r#"{"field": "name", "value": "John Doe", "op": ">"}"#,
+                vec![r#""name": String("Mike")"#],
+            ),
+        ] {
+            let cmd = DB3ClientCommand::ShowDocument {
+                addr: addr.clone(),
+                collection_name: collection_books.to_string(),
+                filter: filter.to_string(),
+                limit: -1,
+            };
+            if let Ok(table) = cmd.execute(&mut ctx).await {
+                assert_eq!(exp.len(), table.len());
+                for i in 0..exp.len() {
+                    assert!(
+                        table
+                            .get_row(i)
+                            .unwrap()
+                            .get_cell(2)
+                            .unwrap()
+                            .get_content()
+                            .contains(exp[i]),
+                        "expect contains {} but {}",
+                        exp[i],
+                        table.get_row(i).unwrap().get_cell(2).unwrap().get_content()
+                    );
+                }
+            } else {
+                assert!(false)
+            }
+        }
         // run show document --filter = '{"field": "age", "value": 44, "op": "=="}'
         let cmd = DB3ClientCommand::ShowDocument {
             addr: addr.clone(),
diff --git a/src/storage/src/db_store.rs b/src/storage/src/db_store.rs
index 0c130c65..68628522 100644
--- a/src/storage/src/db_store.rs
+++ b/src/storage/src/db_store.rs
@@ -720,7 +720,7 @@ impl DbStore {
                                     }
                                 };
 
-                                let keys = match &field_filter.value {
+                                let key = match &field_filter.value {
                                     Some(value) => bson_util::bson_into_comparison_bytes(
                                         &bson_util::bson_value_from_proto_value(value)?,
                                     )?,
@@ -730,30 +730,12 @@ impl DbStore {
                                         ));
                                     }
                                 };
-                                let range = match Operator::from_i32(field_filter.op) {
-                                    Some(Operator::Equal) => {
-                                        let start_key = IndexId::create(
-                                            &collection_id,
-                                            index.id,
-                                            keys.as_slice(),
-                                            &DocumentId::zero(),
-                                        )?;
-
-                                        let end_key = IndexId::create(
-                                            &collection_id,
-                                            index.id,
-                                            keys.as_slice(),
-                                            &DocumentId::one(),
-                                        )?;
-                                        (Bound::Included(start_key), Bound::Included(end_key))
-                                    }
-                                    _ => {
-                                        return Err(DB3Error::InvalidFilterType(format!(
-                                            "Filed Filter Op {:?} un-support",
-                                            &field_filter.op
-                                        )));
-                                    }
-                                };
+                                let range = Self::generate_range_with_single_field_filter(
+                                    &collection_id,
+                                    index,
+                                    &key,
+                                    Operator::from_i32(field_filter.op),
+                                )?;
                                 Self::execute_query(db, &range, limit)
                             }
                             None => {
@@ -780,6 +762,85 @@ impl DbStore {
         }
     }
 
+    /// generate range for single field filter
+    /// e.g : field = "name", value = "jack", op = "equal"
+    /// range = [name:jack:00000, name:jack:11111]
+    /// e.g : field = "name", value = "jack", op = "less_than"
+    /// range = (unbounded, name:jack:00000)
+    /// etc.
+    fn generate_range_with_single_field_filter(
+        collection_id: &CollectionId,
+        index: &Index,
+        key: &Vec<u8>,
+        op: Option<Operator>,
+    ) -> Result<(Bound<IndexId>, Bound<IndexId>)> {
+        match op {
+            Some(Operator::Equal) => {
+                let start_key = IndexId::create(
+                    &collection_id,
+                    index.id,
+                    key.as_slice(),
+                    &DocumentId::zero(),
+                )?;
+
+                let end_key =
+                    IndexId::create(&collection_id, index.id, key.as_slice(), &DocumentId::one())?;
+                Ok((Bound::Included(start_key), Bound::Included(end_key)))
+            }
+            Some(Operator::LessThan) => {
+                let start_key =
+                    IndexId::create(&collection_id, index.id, "".as_bytes(), &DocumentId::zero())?;
+                let end_key = IndexId::create(
+                    &collection_id,
+                    index.id,
+                    key.as_slice(),
+                    &DocumentId::zero(),
+                )?;
+                Ok((Bound::Included(start_key), Bound::Excluded(end_key)))
+            }
+            Some(Operator::LessThanOrEqual) => {
+                let start_key =
+                    IndexId::create(&collection_id, index.id, "".as_bytes(), &DocumentId::zero())?;
+                let end_key =
+                    IndexId::create(&collection_id, index.id, key.as_slice(), &DocumentId::one())?;
+                Ok((Bound::Included(start_key), Bound::Included(end_key)))
+            }
+            Some(Operator::GreaterThan) => {
+                let start_key =
+                    IndexId::create(&collection_id, index.id, key.as_slice(), &DocumentId::one())?;
+                let end_key = IndexId::create(
+                    &collection_id,
+                    index.id + 1,
+                    "".as_bytes(),
+                    &DocumentId::zero(),
+                )?;
+                Ok((Bound::Excluded(start_key), Bound::Excluded(end_key)))
+            }
+            Some(Operator::GreaterThanOrEqual) => {
+                let start_key = IndexId::create(
+                    &collection_id,
+                    index.id,
+                    key.as_slice(),
+                    &DocumentId::zero(),
+                )?;
+                let end_key = IndexId::create(
+                    &collection_id,
+                    index.id + 1,
+                    "".as_bytes(),
+                    &DocumentId::zero(),
+                )?;
+                Ok((Bound::Included(start_key), Bound::Excluded(end_key)))
+            }
+            _ => {
+                // TODO: support more not equal operator
+                return Err(DB3Error::InvalidFilterType(format!(
+                    "Filed Filter Op {:?} un-support",
+                    op
+                )));
+            }
+        }
+    }
+
     /// execute a query to fetch target documents from given database and index range
     fn execute_query(
         db: Pin<&Merk>,
@@ -811,10 +872,10 @@ impl DbStore {
         it_end.seek_to_last();
         let it_end_key = it_end.key().unwrap();
 
-        let (end_key_ref, end_key_include) = match &range.1 {
-            Bound::Unbounded => (it_end_key, true),
-            Bound::Included(end) => (end.as_ref().as_slice(), true),
-            Bound::Excluded(end) => (end.as_ref().as_slice(), false),
+        let (end_key_ref, end_key_exclude) = match &range.1 {
+            Bound::Unbounded => (it_end_key, false),
+            Bound::Included(end) => (end.as_ref().as_slice(), false),
+            Bound::Excluded(end) => (end.as_ref().as_slice(), true),
         };
 
         while it.valid() {
@@ -825,7 +886,7 @@ impl DbStore {
                 if k > end_key_ref {
                     break;
                 }
-                if end_key_include && k == end_key_ref {
+                if k == end_key_ref && end_key_exclude {
                     break;
                 }
                 let index_id = IndexId::new(k.to_vec());
@@ -1019,6 +1080,7 @@ impl DbStore {
 #[cfg(test)]
 mod tests {
     use super::*;
+    use bson::Bson;
     use db3_base::bson_util;
     use db3_crypto::key_derive;
     use db3_crypto::signature_scheme::SignatureScheme;
@@ -1034,6 +1096,7 @@ mod tests {
     use db3_proto::db3_mutation_proto::DocumentMask;
     use db3_proto::db3_mutation_proto::DocumentMutation;
     use std::boxed::Box;
+    use std::ops::Bound::{Excluded, Included};
     use tempdir::TempDir;
 
     fn gen_address() -> DB3Address {
@@ -1393,27 +1456,50 @@ mod tests {
         let docs = DbStore::run_query(db.as_ref(), &db_id, &query).unwrap();
         assert_eq!(2, docs.len());
 
-        // run query: select * from collection where name = "Bill"
-        let query = StructuredQuery {
-            collection_name: collection_name.to_string(),
-            select: Some(Projection { fields: vec![] }),
-            r#where: Some(Filter {
-                filter_type: Some(FilterType::FieldFilter(FieldFilter {
-                    field: "name".to_string(),
-                    op: Operator::Equal.into(),
-                    value: Some(Value {
-                        value_type: Some(ValueType::StringValue("Bill".to_string())),
-                    }),
-                })),
-            }),
-            limit: None,
-        };
-        let docs = DbStore::run_query(db.as_ref(), &db_id, &query).unwrap();
-        assert_eq!(2, docs.len());
-        let document = bson_util::bytes_to_bson_document(docs[0].doc.clone()).unwrap();
-        assert_eq!("Bill", document.get_str("name").unwrap());
-        let document = bson_util::bytes_to_bson_document(docs[1].doc.clone()).unwrap();
-        assert_eq!("Bill", document.get_str("name").unwrap());
+        // test query with ==, <, <=, >, >= condition
+        for (field, value_str, op, exp) in [
+            ("name", "Bill", Operator::Equal, vec!["Bill", "Bill"]),
+            ("name", "John Doe", Operator::LessThan, vec!["Bill", "Bill"]),
+            (
+                "name",
+                "John Doe",
+                Operator::LessThanOrEqual,
+                vec!["Bill", "Bill", "John Doe"],
+            ),
+            (
+                "name",
+                "John Doe",
+                Operator::GreaterThanOrEqual,
+                vec!["John Doe", "Mike"],
+            ),
+            ("name", "John Doe", Operator::GreaterThan, vec!["Mike"]),
+        ] {
+            let query = StructuredQuery {
+                collection_name: collection_name.to_string(),
+                select: Some(Projection { fields: vec![] }),
+                r#where: Some(Filter {
+                    filter_type: Some(FilterType::FieldFilter(FieldFilter {
+                        field: field.to_string(),
+                        op: op.into(),
+                        value: Some(Value {
+                            value_type: Some(ValueType::StringValue(value_str.to_string())),
+                        }),
+                    })),
+                }),
+                limit: None,
+            };
+            let docs = DbStore::run_query(db.as_ref(), &db_id, &query).unwrap();
+            assert_eq!(exp.len(), docs.len(), "run query fail for {:?}", query);
+            for i in 0..exp.len() {
+                let document = bson_util::bytes_to_bson_document(docs[i].doc.clone()).unwrap();
+                assert_eq!(
+                    exp[i],
+                    document.get_str(field).unwrap(),
+                    "run query fail for {:?}",
+                    query
+                );
+            }
+        }
 
         // run query: select * from collection where name = "Bill" limit 1
         let query = StructuredQuery {
@@ -1867,7 +1953,6 @@ mod tests {
             block_id,
             mutation_id,
         );
-        println!("{:?}", res);
         assert!(res.is_err(), "{:?}", res);
         assert_eq!(
             r#"DocumentNotExist("AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA==")"#,
@@ -1902,7 +1987,6 @@ mod tests {
             block_id,
             mutation_id,
         );
-        println!("{:?}", res);
         assert!(res.is_err(), "{:?}", res);
         assert_eq!(
             r#"ApplyDocumentError("invalid update document mutation, ids and masks size different")"#,
@@ -2062,4 +2146,154 @@ mod tests {
             assert!(false);
         }
     }
+
+    #[test]
+    fn generate_range_with_single_field_filter_test() {
+        let collection_id = CollectionId::create(1000, 100, 10).unwrap();
+
+        let index_field_name = IndexField {
+            field_path: "name".to_string(),
+            value_mode: Some(ValueMode::Order(Order::Ascending as i32)),
+        };
+
+        let index_name = Index {
+            id: 0,
+            name: "idx1".to_string(),
+            fields: vec![index_field_name],
+        };
+        let key = bson_util::bson_into_comparison_bytes(&Bson::String("Bill".to_string())).unwrap();
+
+        let res = DbStore::generate_range_with_single_field_filter(
+            &collection_id,
+            &index_name,
+            &key,
+            Some(Operator::Equal),
+        );
+        assert!(res.is_ok(), "{:?}", res);
+        let (start, end) = res.unwrap();
+        // range [collection-index-key-00000000, collection-index-key-11111111]
+        match start {
+            Included(start) => {
+                assert_eq!(start.get_document_id().unwrap(), DocumentId::zero());
+            }
+            _ => {
+                assert!(false);
+            }
+        }
+        match end {
+            Included(start) => {
+                assert_eq!(start.get_document_id().unwrap(), DocumentId::one());
+            }
+            _ => {
+                assert!(false);
+            }
+        }
+
+        let res = DbStore::generate_range_with_single_field_filter(
+            &collection_id,
+            &index_name,
+            &key,
+            Some(Operator::GreaterThan),
+        );
+        assert!(res.is_ok(), "{:?}", res);
+        let (start, end) = res.unwrap();
+        // range (collection-index-key-11111111, collection-next_index-0-00000000)
+        match start {
+            Excluded(start) => {
+                assert_eq!(start.get_document_id().unwrap(), DocumentId::one());
+            }
+            _ => {
+                assert!(false);
+            }
+        }
+        match end {
+            Excluded(end) => {
+                assert_eq!(end.get_document_id().unwrap(), DocumentId::zero());
+                assert_eq!(end.get_index_field_id(), index_name.id + 1);
+            }
+            _ => {
+                assert!(false);
+            }
+        }
+
+        let res = DbStore::generate_range_with_single_field_filter(
+            &collection_id,
+            &index_name,
+            &key,
+            Some(Operator::GreaterThanOrEqual),
+        );
+        assert!(res.is_ok(), "{:?}", res);
+        let (start, end) = res.unwrap();
+        // range [collection-index-key-00000000, collection-next_index-0-00000000)
+        match start {
+            Included(start) => {
+                assert_eq!(start.get_document_id().unwrap(), DocumentId::zero());
+            }
+            _ => {
+                assert!(false);
+            }
+        }
+        match end {
+            Excluded(end) => {
+                assert_eq!(end.get_document_id().unwrap(), DocumentId::zero());
+                assert_eq!(end.get_index_field_id(), index_name.id + 1);
+            }
+            _ => {
+                assert!(false);
+            }
+        }
+
+        let res = DbStore::generate_range_with_single_field_filter(
+            &collection_id,
+            &index_name,
+            &key,
+            Some(Operator::LessThan),
+        );
+        assert!(res.is_ok(), "{:?}", res);
+        let (start, end) = res.unwrap();
+        // range [collection-index-0-00000000, collection-index-key-00000000)
+        match end {
+            Excluded(end) => {
+                assert_eq!(end.get_document_id().unwrap(), DocumentId::zero());
+            }
+            _ => {
+                assert!(false);
+            }
+        }
+
+        match start {
+            Included(start) => {
+                assert_eq!(start.get_document_id().unwrap(), DocumentId::zero());
+            }
+            _ => {
+                assert!(false);
+            }
+        }
+
+        let res = DbStore::generate_range_with_single_field_filter(
+            &collection_id,
+            &index_name,
+            &key,
+            Some(Operator::LessThanOrEqual),
+        );
+        assert!(res.is_ok(), "{:?}", res);
+        let (start, end) = res.unwrap();
+        // range [collection-index-0-00000000, collection-index-key-11111111]
+        match end {
+            Included(end) => {
+                assert_eq!(end.get_document_id().unwrap(), DocumentId::one());
+            }
+            _ => {
+                assert!(false);
+            }
+        }
+        match start {
+            Included(start) => {
+                assert_eq!(start.get_document_id().unwrap(), DocumentId::zero());
+            }
+            _ => {
+                assert!(false);
+            }
+        }
+    }
 }


Test patch: