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)

    XMLWordPrintable

Details

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

    Description

      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.

      Summary:

      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)::
      holdingsRecords.permanentLocationId=53cf956f-c1df-410b-8bea-27f712cca7c0

      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

        Attachments

          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

            Activity

              People

                evaluk Eric Valuk
                evaluk Eric Valuk
                Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases