- 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.)
- 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