azimutt
azimutt copied to clipboard
azimuttapp
Improve Couchbase's support
Thanks for supporting Couchbase Server! There are a bunch of things doable to improve it's general support.
- [ ] Get the key and keyspace of the document in a meta field for each Azimutt Schema. The reason being that some JOINs are made with the key of the document. This is doable by adding
Meta() as metain the select clause. - [ ] Infer relationships through the existing queries and index. You can get them with
SELECT * FROM system:indexes;andSELECT * FROM system:completed_requests;
Added the Meta object on document query, we will have this object on every collection, I named it _meta to minimize conflicts and be explicit it's a special property.
I looked into the indexes on the sample bucket (travel-sample) but I don't know what to look, here are the results I get:
[
{
condition: '(`_type` = "User")',
datastore_id: 'http://127.0.0.1:8091',
id: 'c2dd55e30a1f1de6',
index_key: [ '`name`' ],
keyspace_id: 'travel-sample',
name: 'def_name_type',
namespace_id: 'default',
state: 'online',
using: 'gsi'
},
{
condition: '(`type` = "route")',
datastore_id: 'http://127.0.0.1:8091',
id: '805ddfba2bf9e530',
index_key: [
'`sourceairport`',
'`destinationairport`',
'(distinct (array (`v`.`day`) for `v` in `schedule` end))'
],
keyspace_id: 'travel-sample',
name: 'def_route_src_dst_day',
namespace_id: 'default',
state: 'online',
using: 'gsi'
},
{
bucket_id: 'travel-sample',
datastore_id: 'http://127.0.0.1:8091',
id: 'df5d4e42cae537c4',
index_key: [],
is_primary: true,
keyspace_id: 'airline',
name: 'def_inventory_airline_primary',
namespace_id: 'default',
scope_id: 'inventory',
state: 'online',
using: 'gsi'
},
{
bucket_id: 'travel-sample',
datastore_id: 'http://127.0.0.1:8091',
id: '8fd75114fc9d28c2',
index_key: [ 'array (`s`.`utc`) for `s` in `schedule` end' ],
keyspace_id: 'route',
name: 'def_inventory_route_schedule_utc',
namespace_id: 'default',
scope_id: 'inventory',
state: 'online',
using: 'gsi'
},
{
bucket_id: 'travel-sample',
datastore_id: 'http://127.0.0.1:8091',
id: '219e2dcd0b5e2306',
index_key: [
'`sourceairport`',
'`destinationairport`',
'(distinct (array (`v`.`day`) for `v` in `schedule` end))'
],
keyspace_id: 'route',
name: 'def_inventory_route_route_src_dst_day',
namespace_id: 'default',
scope_id: 'inventory',
state: 'online',
using: 'gsi'
}
]
What I identified I could get from them:
- show indexed columns (
nameon columns inindex_keyfor those who don't have an expression) - primary keys (using
is_primary), I'm a bit surprised to have only 2 globally, is it expected in Couchbase? I'm not sure whatconditionmeans, I feel like it could be important ^^ Also, any idea why sometimes theindex_keyis empty? Especially for the primary_key one? I have no idea on which field I should flag the primary key...
On the completed_requests I have no result, so I don't know how to expect from them ^^
But I didn't find how I could infer relations :/
I extracted the code in a separate lib so it will be easier experiment, here is the test with the commands: https://github.com/azimuttapp/azimutt/blob/main/libs/connector-couchbase/tests/couchbase.test.ts#L18
The available results of completed requests really depends on what is configured to capture the request. Maybe a better route is to get the prepared statement with a query like Select meta().plan, statement from system:prepareds where contains(statement, "JOIN") OR contains(statement, "UNNEST") OR contains(statement, "NEST")
This could yield a results like
[
{
"plan": {
"#operator": "Authorize",
"privileges": {
"List": [
{
"Priv": 7,
"Props": 0,
"Target": "default:travel-sample.inventory.airport"
},
{
"Priv": 7,
"Props": 0,
"Target": "default:travel-sample.inventory.route"
}
]
},
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IntersectScan",
"scans": [
{
"#operator": "IndexScan3",
"bucket": "travel-sample",
"index": "def_inventory_airport_faa",
"index_id": "425ef0580d0d0c67",
"index_projection": {
"primary_key": true
},
"keyspace": "airport",
"namespace": "default",
"scope": "inventory",
"spans": [
{
"exact": true,
"range": [
{
"inclusion": 0,
"index_key": "`faa`",
"low": "null"
}
]
}
],
"using": "gsi"
},
{
"#operator": "IndexScan3",
"bucket": "travel-sample",
"index": "def_inventory_airport_city",
"index_id": "549aea566dce6b5e",
"index_projection": {
"primary_key": true
},
"keyspace": "airport",
"namespace": "default",
"scope": "inventory",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"San Francisco\"",
"inclusion": 3,
"index_key": "`city`",
"low": "\"San Francisco\""
}
]
}
],
"using": "gsi"
}
]
},
{
"#operator": "Fetch",
"bucket": "travel-sample",
"keyspace": "airport",
"namespace": "default",
"scope": "inventory"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(((`airport`.`city`) = \"San Francisco\") and ((`airport`.`faa`) is not null))"
}
]
}
},
{
"#operator": "HashJoin",
"build_aliases": [
"subquery"
],
"build_exprs": [
"(`subquery`.`sourceairport`)"
],
"on_clause": "((`airport`.`faa`) = (`subquery`.`sourceairport`))",
"probe_exprs": [
"(`airport`.`faa`)"
],
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "PrimaryScan3",
"bucket": "travel-sample",
"index": "def_inventory_route_primary",
"index_projection": {
"primary_key": true
},
"keyspace": "route",
"namespace": "default",
"scope": "inventory",
"using": "gsi"
},
{
"#operator": "Fetch",
"bucket": "travel-sample",
"keyspace": "route",
"namespace": "default",
"scope": "inventory"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(`route`.`destinationairport`)"
},
{
"expr": "(`route`.`sourceairport`)"
}
]
}
]
}
}
]
},
{
"#operator": "Alias",
"as": "subquery",
"secondary_term": true
}
]
}
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"distinct": true,
"result_terms": [
{
"expr": "(`subquery`.`destinationairport`)"
}
]
},
{
"#operator": "Distinct"
}
]
}
},
{
"#operator": "Distinct"
}
]
},
{
"#operator": "Limit",
"expr": "10"
}
]
},
{
"#operator": "Stream"
}
]
}
},
"statement": "prepare p4 as SELECT DISTINCT subquery.destinationairport \n FROM `travel-sample`.inventory.airport \n JOIN ( \n SELECT destinationairport, sourceairport \n FROM `travel-sample`.inventory.route \n ) AS subquery \n ON airport.faa = subquery.sourceairport \n WHERE airport.city = \"San Francisco\"\n LIMIT 10"
},
{
"plan": {
"#operator": "Authorize",
"privileges": {
"List": [
{
"Priv": 7,
"Props": 0,
"Target": "default:travel-sample.inventory.route"
},
{
"Priv": 7,
"Props": 0,
"Target": "default:travel-sample.inventory.airport"
},
{
"Priv": 7,
"Props": 0,
"Target": "default:travel-sample.inventory.landmark"
}
]
},
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "PrimaryScan3",
"as": "rte",
"bucket": "travel-sample",
"index": "def_inventory_route_primary",
"index_projection": {
"primary_key": true
},
"keyspace": "route",
"namespace": "default",
"scope": "inventory",
"using": "gsi"
},
{
"#operator": "Fetch",
"as": "rte",
"bucket": "travel-sample",
"keyspace": "route",
"namespace": "default",
"scope": "inventory"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "NestedLoopJoin",
"alias": "apt",
"on_clause": "((`rte`.`destinationairport`) = (`apt`.`faa`))",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan3",
"as": "apt",
"bucket": "travel-sample",
"index": "def_inventory_airport_faa",
"index_id": "425ef0580d0d0c67",
"index_projection": {
"primary_key": true
},
"keyspace": "airport",
"namespace": "default",
"nested_loop": true,
"scope": "inventory",
"spans": [
{
"exact": true,
"range": [
{
"high": "(`rte`.`destinationairport`)",
"inclusion": 3,
"index_key": "`faa`",
"low": "(`rte`.`destinationairport`)"
}
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"as": "apt",
"bucket": "travel-sample",
"keyspace": "airport",
"namespace": "default",
"nested_loop": true,
"scope": "inventory"
}
]
}
},
{
"#operator": "NestedLoopNest",
"alias": "lmk",
"on_clause": "((`apt`.`city`) = (`lmk`.`city`))",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan3",
"as": "lmk",
"bucket": "travel-sample",
"index": "def_inventory_landmark_city",
"index_id": "41d0d8cfe42ebcbe",
"index_projection": {
"primary_key": true
},
"keyspace": "landmark",
"namespace": "default",
"nested_loop": true,
"scope": "inventory",
"spans": [
{
"exact": true,
"range": [
{
"high": "(`apt`.`city`)",
"inclusion": 3,
"index_key": "`city`",
"low": "(`apt`.`city`)"
}
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"as": "lmk",
"bucket": "travel-sample",
"keyspace": "landmark",
"namespace": "default",
"nested_loop": true,
"scope": "inventory"
}
]
}
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "self",
"star": true
}
]
}
]
}
}
]
},
{
"#operator": "Limit",
"expr": "2"
}
]
},
{
"#operator": "Stream"
}
]
}
},
"statement": "prepare pnnest as SELECT * \n FROM `travel-sample`.inventory.route AS rte \n JOIN `travel-sample`.inventory.airport AS apt \n ON rte.destinationairport = apt.faa \n NEST `travel-sample`.inventory.landmark AS lmk \n ON apt.city = lmk.city \n LIMIT 2"
},
{
"plan": {
"#operator": "Authorize",
"privileges": {
"List": [
{
"Priv": 7,
"Props": 0,
"Target": "default:travel-sample.inventory.route"
},
{
"Priv": 7,
"Props": 0,
"Target": "default:travel-sample.inventory.airline"
}
]
},
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan3",
"as": "r",
"bucket": "travel-sample",
"index": "def_inventory_route_route_src_dst_day",
"index_id": "ac1bab4b598b61e8",
"index_projection": {
"primary_key": true
},
"keyspace": "route",
"namespace": "default",
"scope": "inventory",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"SEA\"",
"inclusion": 3,
"index_key": "`sourceairport`",
"low": "\"SEA\""
},
{
"high": "\"MCO\"",
"inclusion": 3,
"index_key": "`destinationairport`",
"low": "\"MCO\""
}
]
}
],
"using": "gsi"
}
},
{
"#operator": "Fetch",
"as": "r",
"bucket": "travel-sample",
"keyspace": "route",
"namespace": "default",
"scope": "inventory"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(((`r`.`sourceairport`) = \"SEA\") and ((`r`.`destinationairport`) = \"MCO\") and is_array((`r`.`schedule`)))"
},
{
"#operator": "Unnest",
"as": "s",
"expr": "(`r`.`schedule`)",
"filter": "(((`s`.`day`) = 6) and (`s` is not missing))"
}
]
}
},
{
"#operator": "Join",
"as": "a",
"bucket": "travel-sample",
"keyspace": "airline",
"namespace": "default",
"on_keys": "(`r`.`airlineid`)",
"scope": "inventory"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(`a`.`name`)"
},
{
"expr": "(`s`.`flight`)"
},
{
"expr": "(`s`.`utc`)"
},
{
"expr": "(`r`.`sourceairport`)"
},
{
"expr": "(`r`.`destinationairport`)"
},
{
"expr": "(`r`.`equipment`)"
}
]
}
]
}
}
]
},
{
"#operator": "Order",
"sort_terms": [
{
"expr": "random()"
}
]
}
]
},
{
"#operator": "Stream"
}
]
}
},
"statement": "PREPARE p1 as SELECT a.name, s.flight, s.utc, r.sourceairport, r.destinationairport, r.equipment\nFROM `travel-sample`.inventory.route r\nUNNEST r.schedule s\nJOIN `travel-sample`.inventory.airline a ON KEYS r.airlineid\nWHERE r.sourceairport='SEA' AND r.destinationairport='MCO' AND s.day=6\nORDER BY Random()"
},
{
"plan": {
"#operator": "Authorize",
"privileges": {
"List": [
{
"Priv": 7,
"Props": 0,
"Target": "default:travel-sample.inventory.route"
},
{
"Priv": 7,
"Props": 0,
"Target": "default:travel-sample.inventory.airline"
}
]
},
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "PrimaryScan3",
"bucket": "travel-sample",
"index": "def_inventory_route_primary",
"index_projection": {
"primary_key": true
},
"keyspace": "route",
"namespace": "default",
"scope": "inventory",
"using": "gsi"
},
{
"#operator": "Fetch",
"bucket": "travel-sample",
"keyspace": "route",
"namespace": "default",
"scope": "inventory"
},
{
"#operator": "Join",
"bucket": "travel-sample",
"keyspace": "airline",
"namespace": "default",
"on_keys": "(`route`.`airlineid`)",
"scope": "inventory"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "((`airline`.`country`) = \"France\")"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "self",
"star": true
}
]
}
]
}
}
]
},
{
"#operator": "Limit",
"expr": "3"
}
]
},
{
"#operator": "Stream"
}
]
}
},
"statement": "prepare p3 as SELECT * \n FROM `travel-sample`.inventory.route \n JOIN `travel-sample`.inventory.airline \n ON route.airlineid = META(airline).id \n WHERE airline.country = \"France\" \n LIMIT 3"
},
{
"plan": {
"#operator": "Authorize",
"privileges": {
"List": [
{
"Priv": 7,
"Props": 0,
"Target": "default:travel-sample.inventory.hotel"
}
]
},
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "PrimaryScan3",
"bucket": "travel-sample",
"index": "def_inventory_hotel_primary",
"index_projection": {
"primary_key": true
},
"keyspace": "hotel",
"namespace": "default",
"scope": "inventory",
"using": "gsi"
},
{
"#operator": "Fetch",
"bucket": "travel-sample",
"keyspace": "hotel",
"namespace": "default",
"scope": "inventory"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "is_array((`hotel`.`reviews`))"
},
{
"#operator": "Unnest",
"as": "r",
"expr": "(`hotel`.`reviews`)",
"filter": "((((`r`.`ratings`).`Rooms`) < 2) and (`r` is not missing))"
}
]
}
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"raw": true,
"result_terms": [
{
"expr": "(`r`.`author`)"
}
]
}
]
}
}
]
},
{
"#operator": "Limit",
"expr": "4"
}
]
},
{
"#operator": "Stream"
}
]
}
},
"statement": "prepare pnest as SELECT RAW r.author \n FROM `travel-sample`.inventory.hotel \n UNNEST reviews AS r \n WHERE r.ratings.Rooms < 2 \n LIMIT 4"
},
{
"plan": {
"#operator": "Authorize",
"privileges": {
"List": [
{
"Priv": 7,
"Props": 0,
"Target": "default:travel-sample.inventory.route"
},
{
"Priv": 7,
"Props": 0,
"Target": "default:travel-sample.inventory.airport"
}
]
},
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan3",
"as": "rte",
"bucket": "travel-sample",
"index": "def_inventory_route_sourceairport",
"index_id": "44cf488c501bda75",
"index_projection": {
"primary_key": true
},
"keyspace": "route",
"namespace": "default",
"scope": "inventory",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"SFO\"",
"inclusion": 3,
"index_key": "`sourceairport`",
"low": "\"SFO\""
}
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"as": "rte",
"bucket": "travel-sample",
"keyspace": "route",
"namespace": "default",
"scope": "inventory"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "((`rte`.`sourceairport`) = \"SFO\")"
},
{
"#operator": "NestedLoopJoin",
"alias": "apt",
"on_clause": "((`rte`.`destinationairport`) = (`apt`.`faa`))",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan3",
"as": "apt",
"bucket": "travel-sample",
"index": "def_inventory_airport_faa",
"index_id": "425ef0580d0d0c67",
"index_projection": {
"primary_key": true
},
"keyspace": "airport",
"namespace": "default",
"nested_loop": true,
"scope": "inventory",
"spans": [
{
"exact": true,
"range": [
{
"high": "(`rte`.`destinationairport`)",
"inclusion": 3,
"index_key": "`faa`",
"low": "(`rte`.`destinationairport`)"
}
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"as": "apt",
"bucket": "travel-sample",
"keyspace": "airport",
"namespace": "default",
"nested_loop": true,
"scope": "inventory"
}
]
}
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "self",
"star": true
}
]
}
]
}
}
]
},
{
"#operator": "Limit",
"expr": "5"
}
]
},
{
"#operator": "Stream"
}
]
}
},
"statement": "PREPARE p2 as SELECT * \n FROM `travel-sample`.inventory.route AS rte \n JOIN `travel-sample`.inventory.airport AS apt ON rte.destinationairport = apt.faa \n WHERE rte.sourceairport='SFO' \n LIMIT 5"
}
]