polardbxengine/mysql-test/suite/x/t/crud_table_expr_object.test

## Using Mysqlx::Expr::OBJECT in collection 

## Preamble
--source include/xplugin_preamble.inc
--source include/xplugin_create_user.inc
## Test starts here
--write_file $MYSQL_TMP_DIR/crud_table_expr_object.tmp

## Test data
-->sql
DROP SCHEMA IF EXISTS xtest;
CREATE SCHEMA xtest;
CREATE TABLE xtest.xtable (xfield JSON);
-->endsql

-->echo Inserting object into table
Mysqlx.Crud.Insert {
  collection {
    name: "xtable"
    schema: "xtest"
  }
  projection { name: "xfield" }
  data_model: TABLE
  row {
    field {
      type: OBJECT object {
        fld {
          key: "_id"
          value {
            type: LITERAL literal { type: V_OCTETS v_octets {value:"1"} }
          }
        }
        fld {
          key: "first_name"
          value {
            type: LITERAL literal { type: V_OCTETS v_octets {value:"John"} }
          }
        }
        fld {
          key: "last_name"
          value {
            type: LITERAL literal { type: V_OCTETS v_octets {value:"Snow"} }
          }
        }
      }
    }
  }
}

#-- Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->sql
SELECT * FROM xtest.xtable;
-->endsql

-->echo Selecting object from table with alias
Mysqlx.Crud.Find {
  collection {
    name: "xtable"
    schema: "xtest"
  }
  data_model: TABLE
  projection {
    alias: "result"
    source {
      type: OBJECT object {
        fld {
          key: "name"
          value {
            type: FUNC_CALL function_call {
              name {
                name: "concat"
              }
              param {
                type: IDENT identifier {
                name: "xfield"
                document_path {
                    type: MEMBER
                    value: "first_name"
                  }
                }
              }
              param {
                type: LITERAL literal { type: V_OCTETS v_octets {value:" "} }
              }
              param {
                type: IDENT identifier {
                  name: "xfield"
                  document_path {
                    type: MEMBER value: "last_name"
                  }
                }
              }
            }
          }
        }
        fld {
          key: "number"
          value {
            type: OPERATOR operator {
              name: "+"
              param {
                type: LITERAL literal {type: V_SINT v_signed_int: 1}
              }
              param {
                type: LITERAL literal { type: V_SINT v_signed_int: 1}
              }
            }
          }
        }
      }
    }
  }
}

#-- Mysqlx.Sql.StmtExecuteOk
-->recvresult


-->echo Selecting object with sub-object from table
Mysqlx.Crud.Find {
  collection {
    name: "xtable"
    schema: "xtest"
  }
  data_model: TABLE
  projection {
    alias: "result"
    source {
      type: OBJECT object {
        fld {
          key: "name"
          value {
            type: FUNC_CALL function_call {
              name {
                name: "concat"
              }
              param {
                type: IDENT identifier {
                name: "xfield"
                document_path {
                    type: MEMBER
                    value: "first_name"
                  }
                }
              }
              param {
                type: LITERAL literal { type: V_OCTETS v_octets {value:" "} }
              }
              param {
                type: IDENT identifier {
                name: "xfield"
                document_path {
                    type: MEMBER
                    value: "last_name"
                  }
                }
              }
            }
          }
        }
        fld {
          key: "detail"
          value {
            type: OBJECT object {
              fld {
                key: "first"
                value {
                  type: IDENT identifier {
                    name: "xfield"
                    document_path { type: MEMBER value: "first_name" }
                  }
                }
              }
              fld {
                key: "last"
                value {
                  type: IDENT identifier {
                    name: "xfield"
                    document_path { type: MEMBER value: "last_name" }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

#-- Mysqlx.Sql.StmtExecuteOk
-->recvresult


-->echo Updating table by object
Mysqlx.Crud.Update {
  collection {
    name: "xtable"
    schema: "xtest"
  }
  data_model: TABLE
  operation {
    source {
      name: "xfield"
      document_path { type: MEMBER value: "occupation" }
    }
    operation: ITEM_SET
    value {
      type: OBJECT object {
        fld {
          key: "location"
          value {
            type: LITERAL literal { type: V_OCTETS v_octets {value:"the wall"} }
          }
        }
        fld {
          key: "title"
          value {
            type: LITERAL literal { type: V_OCTETS v_octets {value:"lord commander"} }
          }
        }
      }
    }
  }
}
  
#-- Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->sql
SELECT * FROM xtest.xtable;
-->endsql


-->echo NOT Error: empty object
Mysqlx.Crud.Find {
  collection {
    name: "xtable"
    schema: "xtest"
  }
  data_model: TABLE
  projection {
    alias: "result"
    source {
      type: OBJECT object {
      }
    }
  }
}
  
-->recvresult


-->echo Error: empty object key
Mysqlx.Crud.Find {
  collection {
    name: "xtable"
    schema: "xtest"
  }
  data_model: TABLE
  projection {
    alias: "result"
    source {
      type: OBJECT object {
        fld {
          key: ""
          value {
            type: IDENT identifier {
              name: "xfield"
              document_path { type: MEMBER value: "first_name" }
            }
          }
        }
      }
    }
  }
}
  
-->recverror 5154


## Cleanup
-->sql
drop schema if exists xtest;
-->endsql
EOF

--exec $MYSQLXTEST -ux_root --password='' --file=$MYSQL_TMP_DIR/crud_table_expr_object.tmp 2>&1

## Cleanup
--remove_file $MYSQL_TMP_DIR/crud_table_expr_object.tmp
--source include/xplugin_drop_user.inc