Uploaded image for project: 'RAML Module Builder'
  1. RAML Module Builder
  2. RMB-391

Implement foreign key support for CQLPGJson Parser (two tables, parent->child)



    • CP: sprint 64, CP: sprint 65, CP: sprint 66
    • 8
    • Core: Platform


      Implement Parsing and sql construction changes to bring about joining tables without using views. More information can be found at the related issue CQLPG-95.


      Implement changes in detection to determine presence of search terms / keys that are not present in the current table.

      Implement detection of common keys to join both tables on.

      Implement sub query using condition sent in to the key determined above with changes needed to correlate it to the common key also determined above.

      Determine if In clause is needed and include it in the translation.(see below)

      We are not going to resolve the duplicate result (potential) issue (aka DISTINCT) within this ticket.

      Requirement: the FK information must be in the schema.json

      Implementation notes
      How to determine location of the field:

      1. Each table has a list of indices (index, uniqueIndex, ginIndex, fulltextIndex) if a field matches – we generate query local to the original table.
      2. In case the field doesn't match AND the index name contains a dot (like "holdingsRecord.permanentLocationId") we take the string before the dot and convert it to a table name (we need to lowercase and remove underscored) and we check if the string after the dot matches a field name in that table AND that the two tables are linked via a foreign key (either parent->child or child->parent).

      mod-orders-storage query – find all purchase order records that already have a least one po_line record (parent -> child example):
      we will support notation in cql like
      id = poLine.purchaseOrderId

      This would result in sql added to the where clause(note the mod-order-storage example below):

      jsonb->>'id' in (Select jsonb->>'purchaseOrderId' from diku_mod_orders_storage.po_line )
      mod-inventory-storage query - find all instance records that have a least one holdings record that has a specific permanent location (parent -> child example)::

      will need to translate into
      select jsonb->> 'permanentLocationId'
      from diku_mod_inventory_storage.holdings_record where (lower(f_unaccent(instance.jsonb ->> 'id'::text)) = lower(f_unaccent(holdings_record.jsonb ->> 'instanceId'::text)))) LIKE lower(f_unaccent('53cf956f-c1df-410b-8bea-27f712cca7c0')

      TestRail: Results


          1. example-join.txt
            4 kB
          2. example-original.txt
            3 kB
          3. example-subquery.txt
            3 kB
          4. orders-storage-original.txt
            0.4 kB
          5. order-storage-join.txt
            0.6 kB
          6. order-storage-subquery.txt
            0.4 kB

          Issue Links



                evaluk Eric Valuk
                evaluk Eric Valuk
                0 Vote for this issue
                4 Start watching this issue



                  TestRail: Runs

                    TestRail: Cases