api: UI Investigate Filters & Sorting

[DanCodedThis]

• Support asc / desc order for list endpoints
• Search example: /databases/${databaseName}/schemas?search=pub GET

Create sub-issues if needed

[DanCodedThis]

For search:

• SlateDB's lexicographical sort order is perfect for this.
• Without adding no more dependencies or spending time writing our own search we can just rely on SlateDB.
• Example: If we will search schemas for pub it will translate to range of something like sch/${database_name}/pub..sch/${database_name}/pub\x7F (the end_key may differ, not sure whether it's the right way to close this range) and we will get out RwObject<IceBucketSchema> in a Vec (which would be the Public Schema).
• When we will move to new unique identifiers (UUIDs / ULIDs / ANY OTHER) the underline mechanism won't change. We will just look up SlateDB twice, once for the unique identifier (which would be the same operation as before, but returns not the RwObject<IceBucketSchema> but a String, probably) and twice for the RwObject<IceBucketSchema> in a Vec itself.

P.S. The second lookup may prove itself tricky. Since, if we get a Vec<String> they are not lexicographically sorted (because they are the values in this instance). Essentially, the second time we won't build a range and won't scan, we will just get all the objects we need by key in a loop. But it shouldn't matter since when we scan we still loop over the iterator.

cc: @rampage644

[DanCodedThis]

For search:

• SlateDB's lexicographical sort order is perfect for this.
• Without adding no more dependencies or spending time writing our own search we can just rely on SlateDB.
• Example: If we will search schemas for pub it will translate to range of something like sch/${database_name}/pub..sch/${database_name}/pub\x7F (the end_key may differ, not sure whether it's the right way to close this range) and we will get out RwObject<IceBucketSchema> in a Vec (which would be the Public Schema).
• When we will move to new unique identifiers (UUIDs / ULIDs / ANY OTHER) the underline mechanism won't change. We will just look up SlateDB twice, once for the unique identifier (which would be the same operation as before, but returns not the RwObject<IceBucketSchema> but a String, probably) and twice for the RwObject<IceBucketSchema> in a Vec itself.

P.S. The second lookup may prove itself tricky. Since, if we get a Vec<String> they are not lexicographically sorted (because they are the values in this instance). Essentially, the second time we won't build a range and won't scan, we will just get all the objects we need by key in a loop.

cc: @rampage644

For sorting:

If we'll use the approach up above, before the second look up, we would be able to sort the keys Vec<String> in ASC or DSC order.

[YaroslavLitvinov]

@DanCodedThis I'm not sure I get the whole idea regarding schema changes you introduced, I think we need more details.

For filters, since we use slatedb, we need more requirements on what kind of filtering should be supported. Probably we need to think more about feeding our database by data from slateb for using it's analytical features,

Since this issue is more about future schema changes, I put here my vision on these changes. For schemas keys should be prepended by dbkey.

Option 1 - proposal itself

The same name key is preserved, id is used for referencing item.
Pros: Better performance for getting items in ascending order, and resolving by name.
Pros: Index powered access by Id
Pros: It's possible to do a data migration for keeping all the database data.
Cons: Full scan for descending sort order, as slatedb won't not cooperate as it can't iterate in reverse order.

Key: Name
Value: { Id, Name, Data }

INDEX, for getting name by id.
Key: Id
Value: Name

Examples of keys for:

• table: tbl/db-uuid/schema-uuid/table-name, idx/tbl/db-uuid/schema-uuid/table-uuid
• schema: sch/db-uuid/schema-name, idx/sch/db-uuid/schema-uuid
• database: db/db-name, /idx/db/db-uuid
• volume: vol/vol-name, no index for volume

Migration process:
Solution will iterate over all the keys in range for tables, schemas, databases, volumes and will create new object at a key mentioned above, and a value = { id, ...value }. It is also create an index for every item.

SearchByName - Single get pass. returns items in range.
GetById - Two get passes. Get name by id on first pass, get value by name on second pass.
Add - Two mutation passes. Add item - Name:Value. Add index - Id:Name.
Update - Three mutation passes. Add new item Name:Value, put index Id:Name, Delete old item Name:Value.
Delete - Two mutation passes. Delete index Id:Name, Delete item Name:Value.

VS

Option 2 - added for reference

key: Id instead of name, id is used for referencing item.
Pros: Optimized for access by id
Cons: Slow (Full scan) on fetching any list ordered by name

Key: id
Value: { Id, Name, Data }

INDEX, for getting id by name
Key: Name
Value: Id

SearchByName - Full scan VS one pass is SearchIdsByName, multiple passes get item by id.
GetById - Single pass
GetByName - Two passes. Get Id by name from index, get value by id.
Add - Two mutation passes.
Update - Three mutations passes. put id:value, in case of rename - put new index name:id, delete old index.
Delete - delete id:value, delete index name:id.
t

[DanCodedThis]

For search:

• SlateDB's lexicographical sort order is perfect for this.
• Without adding no more dependencies or spending time writing our own search we can just rely on SlateDB.
• Example: If we will search schemas for pub it will translate to range of something like sch/${database_name}/pub..sch/${database_name}/pub\x7F (the end_key may differ, not sure whether it's the right way to close this range) and we will get out RwObject<IceBucketSchema> in a Vec (which would be the Public Schema).
• When we will move to new unique identifiers (UUIDs / ULIDs / ANY OTHER) the underline mechanism won't change. We will just look up SlateDB twice, once for the unique identifier (which would be the same operation as before, but returns not the RwObject<IceBucketSchema> but a String, probably) and twice for the RwObject<IceBucketSchema> in a Vec itself.

P.S. The second lookup may prove itself tricky. Since, if we get a Vec<String> they are not lexicographically sorted (because they are the values in this instance). Essentially, the second time we won't build a range and won't scan, we will just get all the objects we need by key in a loop. But it shouldn't matter since when we scan we still loop over the iterator.

cc: @rampage644

Since id won't change, the range for searching by name should be search/sch/${database_id}/pub..search/sch/${database_id}/pub\x7F (also we prepend the word search). This should have two passes, one to find the id's of all schemas (tables, databases, volumes, the same concept applies) and then to get them.

[DanCodedThis]

For search:

• SlateDB's lexicographical sort order is perfect for this.
• Without adding no more dependencies or spending time writing our own search we can just rely on SlateDB.
• Example: If we will search schemas for pub it will translate to range of something like sch/${database_name}/pub..sch/${database_name}/pub\x7F (the end_key may differ, not sure whether it's the right way to close this range) and we will get out RwObject<IceBucketSchema> in a Vec (which would be the Public Schema).
• When we will move to new unique identifiers (UUIDs / ULIDs / ANY OTHER) the underline mechanism won't change. We will just look up SlateDB twice, once for the unique identifier (which would be the same operation as before, but returns not the RwObject<IceBucketSchema> but a String, probably) and twice for the RwObject<IceBucketSchema> in a Vec itself.

P.S. The second lookup may prove itself tricky. Since, if we get a Vec<String> they are not lexicographically sorted (because they are the values in this instance). Essentially, the second time we won't build a range and won't scan, we will just get all the objects we need by key in a loop. But it shouldn't matter since when we scan we still loop over the iterator.
cc: @rampage644

Since id won't change, the range for searching by name should be search/sch/${database_id}/pub..search/sch/${database_id}/pub\x7F (also we prepend the word search). This should have two passes, one to find the id's of all schemas (tables, databases, volumes, the same concept applies) and then to get them.

For table:

• search/tbl/database_id/schema_id/table_name (table_name may have just the part of the name) -> String where String is the ID of table.
• id/tbl/database_id/schema_id/table_id -> RwObject<IceBucketTable>

The same applies to all other objects (volumes, databases, schemas, etc).

I would also argue that tbl -> table, sch -> schema, etc. Before, this was used as a special "all name" key.

[rampage644]

I am inclined now to keep things as simple and quick as possible and thus voting for approach #2 described by @YaroslavLitvinov:

• Do not introduce indices to avoid extra complexity (consistency, synchronization)
• Keep existing primary key modeling (by name for everything)
• Implement required functionality with full scans and backend postprocessing