diff --git a/README.md b/README.md index 7cc7537..734b66f 100644 --- a/README.md +++ b/README.md @@ -1,4 +1,183 @@ -surreal_warehousing -=============== +# surreal_warehousing -This repository explores how a simple warehousing system could be implemented using SurrealDB. \ No newline at end of file +This repository explores how a simple warehousing system could be implemented using SurrealDB. + +## Setup + +Our warehouse will be represented in a tree like structure. There are *containers* which themselves can contain *containers*. The leave nodes of our tree are made up of *items* whose location we ultimately want to track in our warehousing system. + +There will be a relation table *contained_in* hierachically linking *containers*. The +table will have three nullable fields *x_position*, *y_position*, *z_position*. + +This hierarchical structure can be arbitrarily deep. + +In a relational database management system we could represent the same situation with a *container* table that has a foreign-key relation with itself. Each container could have a reference to its parent container. Querying such a database could need many joins. One per level in the tree we want to inspect in our query. We will explore if a graph-like data model in SurrealDB makes working with such a hierarchical data model easier (and potentially more performant) for us. + +This is how the *container* table is defined: +```SQL +define table if not exists container schemafull; + +define field code on table container type string; +define field has_x on table container type bool default false; +define field has_y on table container type bool default false; +define field has_z on table container type bool default false; + +define field x_min on table container type option default None; +define field x_max on table container type option default None; + +define field y_min on table container type option default None; +define field y_max on table container type option default None; + +define field z_min on table container type option default None; +define field z_max on table container type option default None; + +//additional fields here would make sense in a more complete implementation like +//names for the dimensions like "shelve", "rack", "slot" etc. +``` +The *contained_in* relation table looks like this: +```SQL +define table contained_in type relation in container|item out container schemafull; + +define field x_pos on table contained_in type option; +define field y_pos on table contained_in type option; +define field z_pos on table contained_in type option; +``` +And the *item* table is trivial since it only has a *code* field for the +name of each item: +```SQL +define table if not exists item schemafull; +define field if not exists code on table item type string; +``` + + +## Querying the test database + +Get all child containers for a given container. +```SQL +select in, x_pos, y_pos, z_pos +from contained_in +where out = container:box1; +``` +Returns an array containing only our single test item without any position information. +```JSON +[ + { + in: item:item1, + x_pos: NONE, + y_pos: NONE, + z_pos: NONE + } +] +``` + + + +The [```recursive paths```](https://surrealdb.com/docs/surrealql/datamodel/idioms#recursive-paths) in SurrealQL allows the user to recursively traverse the warehouse tree up to a certain number of levels. +```SQL +item:item1.{2}(->contained_in->container).code; +``` +"Get item1s parent parent container code". +Returns +```JSON +[ + 'slot1' +] +``` +We can also traverse "up to four levels" in our +warehouse container tree like this: +```SQL +item:item1.{1..4}.{code, contained_in: ->contained_in->(?).@}; +``` +Which returns: +```JSON +{ + code: 'item1', + contained_in: [ + { + code: 'box1', + contained_in: [ + { + code: 'slot1', + contained_in: [ + { + code: 'rack1', + contained_in: [] + } + ] + } + ] + } + ] +} +``` +This shows us the nesting of containers very nicely from *item1* to *rack1*. *rack1* being the root of +the container tree in our example. That's why only three levels +are returned. + +The graph like data modelling and querying that is possible with SurrealDB seems like a nice fit for a domain like our warehouse and lends itself to this approach. The SurrealQL syntax needs a little getting used to but it is extensively documented. + +The question of performance here between a relational data model where we would have a container table with a self-referential foreign-key relationship with the table itself versus the one explored here is beyond the scope of this little experiment unfortunately. + +## Table Constraints +In our warehouse system it seems reasonable to introduce further constraints on +the *contained_in* records. *Containers* can have size limits (but don't have to) and positions of *containers* or *items* inside other *containers* can be +recorded but aren't mandatory. We might want to be able to represent an unordered bag of *items* or a well structured *shelve*. + +The values for *x_pos*, *y_pos* and *z_pos* in the *contained_in* table can be None (or Null which is apparently the same type in SurrealDB? 👀) but don't have to. +This means that a index on these three fields with ```unique``` constraint won't +cut it since in a bag of *items* all of them would have the same None values for +*x_pos*, *y_pos* and *z_pos*. + +In summary we need to ensure that *item* or *container* positions are within +the limits of the parent *container* and that no two *containers* can be +documented as occupying the **same** position in their parent *container*. **Oh and by the way...** one *item* or *container* can not +be in two places at the same time. + +The constraint that each item or container be in one place at a time can +be asserted by creating an index on the *contained_in* table. +```SQL +define index idx_one_contained_in_only on table contained_in columns in unique; +``` + +### ```DEFINE FIELD``` with assert statements +The logic checking whether a *contained_in* record actually is valid and can +be created needs to take into account the values of multiple fields. The position +values (*x_pos*, *y_pos*, *z_pos*) and the respective *...min* and *...max* dimensions of the related parent *container*. + +Thus adding ```ASSERT``` clauses to individual +fields might not be the best way to implement the necessary logic. + + +### ```DEFINE EVENT``` to the rescue? +Let's explore whether we can implement our constraints on *contained_in* entries +by using events. As it turns out via ```EVENTS``` this constraining logic can be implemented. + +```SQL +define event contained_in_created on table contained_in + when $event == "CREATE" + then { + if $after.out.has_x and $after.x_pos == None { + THROW "Can't create contained_in relation: x position is required"; + } else if $after.out.has_y and $after.y_pos == None { + THROW "Can't create contained_in relation: y position is required"; + } else if $after.out.has_z and $after.z_pos == None { + THROW "Can't create contained_in relation: z position is required"; + } else if $after.out.has_x and !($after.out.x_min <= $after.x_pos and $after.x_pos <= $after.out.x_max) { + THROW "Can't create contained_in relation: x position is out of bounds"; + } else if $after.out.has_y and !($after.out.y_min <= $after.y_pos and $after.y_pos <= $after.out.y_max) { + THROW "Can't create contained_in relation: y position is out of bounds"; + } else if $after.out.has_z and !($after.out.z_min <= $after.z_pos and $after.z_pos <= $after.out.z_max) { + THROW "Can't create contained_in relation: z position is out of bounds"; + } else if array::len(select * + from contained_in + where out == $after.out + and x_pos==$after.x_pos + and y_pos==$after.y_pos + and z_pos==$after.z_pos + ) > 1 + { + THROW "Can't create contained_in relation: position in container is already occupied"; + } + }; +``` + diff --git a/src/queries.sql b/src/queries.sql new file mode 100644 index 0000000..d774560 --- /dev/null +++ b/src/queries.sql @@ -0,0 +1,180 @@ +--######################### item table ##################################################### +define table if not exists item schemafull; +define field if not exists code on table item type string; + +--######################### container table ################################################ +define table if not exists container schemafull; + +define field code on table container type string; +define field has_x on table container type bool default false; +define field has_y on table container type bool default false; +define field has_z on table container type bool default false; + +define field x_min on table container type option default None; +define field x_max on table container type option default None; + +define field y_min on table container type option default None; +define field y_max on table container type option default None; + +define field z_min on table container type option default None; +define field z_max on table container type option default None; + +//additional fields here would make sense in a more complete implementation like +//names for the dimensions like "shelve", "rack", "slot" etc. + +--######################### contained_in relation ########################################## + +define table contained_in type relation in container|item out container schemafull; + +define field x_pos on table contained_in type option; +define field y_pos on table contained_in type option; +define field z_pos on table contained_in type option; + + +//each item|container be only in one place at a time. +define index idx_one_contained_in_only on table contained_in columns in unique; + + +define event contained_in_created on table contained_in + when $event == "CREATE" + then { + if $after.out.has_x and $after.x_pos == None { + THROW "Can't create contained_in relation: x position is required"; + } else if $after.out.has_y and $after.y_pos == None { + THROW "Can't create contained_in relation: y position is required"; + } else if $after.out.has_z and $after.z_pos == None { + THROW "Can't create contained_in relation: z position is required"; + } else if $after.out.has_x and !($after.out.x_min <= $after.x_pos and $after.x_pos <= $after.out.x_max) { + THROW "Can't create contained_in relation: x position is out of bounds"; + } else if $after.out.has_y and !($after.out.y_min <= $after.y_pos and $after.y_pos <= $after.out.y_max) { + THROW "Can't create contained_in relation: y position is out of bounds"; + } else if $after.out.has_z and !($after.out.z_min <= $after.z_pos and $after.z_pos <= $after.out.z_max) { + THROW "Can't create contained_in relation: z position is out of bounds"; + } else if array::len(select * + from contained_in + where out == $after.out + and x_pos==$after.x_pos + and y_pos==$after.y_pos + and z_pos==$after.z_pos + ) > 1 + { + THROW "Can't create contained_in relation: position in container is already occupied"; + } + }; + +--######################### test containers and items #################################### +create container:box1 content { + code: "box1" +}; + +//valid additional box entry +create container:box2 content { + code: "box2", + has_x: true, + x_min: 0, + x_max: 10 +}; + +create container:slot1 content { + code: "slot1", + has_x: true, + x_min: 0, + x_max: 10 +}; + + +create container:rack1 content { + code: "rack1", + has_x: true, + has_y: true, + x_min: 0, + x_max: 10, + y_min: 0, + y_max: 10 +}; + +create item:item1 content { + code: "item1" +}; + +create item:item2 content { + code: "item2" +}; + +--######################## test relations ############################################### +//valid entry +relate container:box1->contained_in->container:slot1 content { + x_pos: 1, + y_pos: None, + z_pos: None +}; + +//valid entry +relate container:slot1->contained_in->container:rack1 content { + x_pos: 1, + y_pos: 1, + z_pos: None +}; + +//valid entry +relate item:item1->contained_in->container:box1; + + +//valid relation. x_pos=1 should be ignored because hax_x is false for box1. +relate item:item2->contained_in->container:box1 content { + x_pos: 1, + y_pos: None, + z_pos: None +}; + +//invalid entry. item1 is already related to a container. Each item|container can only +//be in a single place at a time. + +relate item:item1->contained_in->container:box1; +//this throws +//'Database index `idx_one_contained_in_only` already contains item:item1, +//with record `contained_in:pozanudnne041vcxt917`' + + +//invalid entry x_pos out of bounds. + +relate container:slot2->contained_in->container:rack1 content { + x_pos: -1, + y_pos: 1, + z_pos: None +}; +//this throws +//"An error occurred: Can't create contained_in relation: x position is out of bounds" + + +//invalid entry y_pos out of bounds. + +relate container:slot3->contained_in->container:rack1 content { + x_pos: 1, + y_pos: 100, + z_pos: None +}; +//this throws +//"An error occurred: Can't create contained_in relation: y position is out of bounds" + + +//invalid relation. x_pos=1 in slot1 is already taken. + +relate container:box2->contained_in->container:slot1 content { + x_pos: 1, + y_pos: None, + z_pos: None +}; +//this throws +//"An error occurred: Can't create contained_in relation: position in container is already occupied" + + +//invalid relation. slot1 has_x therefore a x_pos needs to be assigned. + +relate container:box2->contained_in->container:slot1 content { + x_pos: None, + y_pos: None, + z_pos: None +}; +//this throws +//"An error occurred: Can't create contained_in relation: x position is required" \ No newline at end of file