Page Comparison

...

• When you have a stub for the prototype on the wiki, contact Angela Zoss (Old) to have a new prototype issue created in the LDP-QURY project. The JIRA issue will include a link to the wiki page and will be connected to the JIRA issue for the cluster or report being prototyped.
• Angela will assign the issue to you when it has been created so you can keep track of all of your tasks. The workflow status will be changed to "In Development."

Identify Data Elements

• review report details on the Reporting SIG Master Spreadsheet and the corresponding JIRA issue
• Note: if working on a report cluster, or if the report has variations listed, make sure to identify data elements required across all queries
• for any fields mentioned in the issue or present in a sample report, locate an appropriate match in the FOLIO source data
  • go to https://dev.folio.org/reference/api/
  • scan the modules to find the general area that is most likely to include the data element
  • use only "-storage" modules (and/or avoid "business logic" or "bl" modules)
  • when you find a likely module, click on "View 1" or "View 2" for the description in the API reference documentation
  • In the View pages, you will see a list of paths to the endpoints underneath that module. For example, under the "loan-storage" module, you will see several endpoints: loan-storage/loans, loan-storage/loans/{loanId}, etc.
  • For the endpoints that contain data we'll be using in the LDP, you should see two very similar paths. One has nothing at the end (e.g., "loan-storage/loans") while the second should have an identifier at the end of the path (e.g., "loan-storage/loans/{loanId}"). You want to focus on the second path, the one with the identifier in curly brackets at the end.
  • Click on either the "GET" or the "PUT" link attached with that second endpoint path. If you use "GET", you will find the data element documentation under "Response." If you use "PUT", it will be under "Request."
  • find the correct element, and note the full path to the data element
  • if you have trouble understanding the data elements:

    • create a JIRA issue linked to 

      Jira Legacy
      server System Jira serverId 01505d01-b853-3c2e-90f1-ee9b165564fc key FOLIO-1551

      for each data element in the FOLIO source data that is missing documentation or needs additional documentation; see examples in the "relates to" section under this epic

    • David Crossley has created a guide for FOLIO Developers to use to write FOLIO attribute documentation at https://dev.folio.org/guides/describe-schema/

    • for information from POs on data elements, you may refer to : https://docs.google.com/spreadsheets/d/1B924D2JaFUc6dqgItyKoOFdhn36lxwT6jaYmKpyk2Ws/edit#gid=1697459676

    • we are also building some Reporting SIG data documentation with helpful links and details
  • a note about the data elements in FOLIO
    • the FOLIO documentation should include a "type" for each data element
    • if the type is "array", that means that FOLIO is storing a list of items in this data element. When "type" is "array", you should also see "items", which will tell you what kind of element is being stored in the array.
    • if the type is "object", that means that FOLIO is storing a JavaScript object, which is like a dictionary or a list of key-value pairs. When "type" is "object", you should also see "properties", which will list all of the keys that might be included in the object.
    • See attached summary for more detail.
    • when you include FOLIO arrays and objects in a project, make sure you document the full path to the item you want and include notes about what (if any) processing might be needed, especially for arrays
• Once you have matched up the data elements that are visible in the final report, do a final pass through the FOLIO documentation to make sure that you also include any data elements that will be required for joining tables together. These are likely identifier elements. Even if you want just the name to appear in the report, please locate and list all required linking identifiers as well.

• On the report wiki page, create a table that lists all required data elements, their path in the FOLIO modules, and a description of how that field will be used (including whether or not the field should be displayed in the final report). Some find it helpful to group data elements by the FOLIO interface they come from, including adding an empty row that shows the name of the interface.

• Optional: for especially complicated reports, it might be nice to include a relationship diagram that helps query builders make the correct connections between tables
  

EXAMPLE TABLE

...

• Create new folder in ldp-analytics/sql to store sql, using the name of the query specified in the wiki page
  
  • If you have any trouble, contact one of the maintainers of ldp-analytics
• Create a file with the query name and the extension ".sql"
• Follow SQL conventions specified in LDP documentation
• Identify tables and data elements in the LDP. If you have any questions about the mapping between FOLIO interfaces and LDP tables, reach out to other query developers or Nassib.
• Let developers know ASAP if we need additional test data for these data elements, or if any needed tables are missing from the LDP database.  Please use the FOLIO Issue Tracker for these purposes, by creating an issue in the "Library Data Platform (LDP)" project.  Set the issue type to "New Feature", and fill in the summary and description fields.  Please do not set any other fields in the issue.
• Additional tips for SQL:
  • Use all-caps for keywords (e.g., "SELECT", "FROM")
  • Start new lines for new keywords, items in long lists
  • Indent lines with 4 spaces
  • Review previous queries for other style conventions 
  • Review Angela's recorded presentation on building an SQL query (name of the file on the Google drive is GMT20190920-135741_RPWG--Quer_1760x900.mp4)
  • Preferred: use Nassib's pgformatter configuration file to format the SQL automatically. (Install pgformatter and set up the configuration file. Then, after creating a .sql file, run the formatter over the SQL using a command like pg_format -i query.sql.)

Test Query

• Set up a connection to the test LDP (see FOLIO Reporting Reference Environment for connection details)
• Log into the LDP using your reporting tool of choice
• Execute the query
• Review results and revise query as needed

...