We appreciate submissions of SQL code for new report queries and derived table queries, as well as bug fixes. All submissions will be considered as time allows. We ask your patience; it may require considerable time to decide whether a pull request will be accepted.
Please follow these guidelines when preparing a pull request.
1. New contributors
2. Commits and pull requests
3. Code review
4. Documentation
5. Testing
6. Derived tables
7. Formatting SQL
8. Naming things
9. Checklist
Before beginning work on a contribution, we recommend that you ask the community in advance about the work you propose to do. They may have feedback that would save you time.
At present most contributors use the #reporting-lab channel in the FOLIO Slack organization.
Alternatively, for general questions, feel free to use the Discussions area. For bugs or specific technical proposals, use Issues.
All commits should be made in a "forked" repository, not in this repository.
It is strongly recommended to create a new branch based on the
main branch, rather than committing directly to main.
Pull requests will be "squash merged," and so the pull request
branch should be discarded or not reused after being merged, in order
to avoid confusion. If the main branch is used for a pull
request, it is a good idea to re-fork the repository after the merge.
Since the entire pull request will be squashed into a single commit, it is recommended to keep the scope of the pull request relatively narrow, preferrably addressing only a single issue.
Quoting roughly from the Git Reference Manual, Git commits are documented with a short, one-line title which summarizes the changes, followed by a more thorough, long description. The title should be no more than 75 characters. It is important that at least the title be filled in with a meaningful summary so that the Git history will be readable. Hyperlinks or other references such as "Fixes #" may be included in the long description, but not in the title and not as a substitute for a complete description of the changes.
The CHANGES.md file should be updated with release notes describing the changes made in the pull request. This should be a self-contained, textual summary of the changes, rather than a hyperlink to an issue.
A pull request is reviewed by at least two people other than the contributor before it can be merged.
If a review suggests expanding the scope of the changes to include additional functionality, those changes do not have to be addressed in the current pull request but may be handled in a separate, new pull request.
See below for a suggested checklist for code reviews.
Additions or changes to queries should be accompanied by the corresponding additions or changes to user documentation, together in the same pull request.
The user documentation should contain three sections:
- Purpose of report
- Sample output
- Query instructions, e.g. how to set parameters
All queries or changes to queries should be tested with an LDP database before they are submitted in a pull request. We do not currently have a test data set to allow significant automated testing.
Additions or changes to derived tables should be accompanied by any
required additions or changes to the runlist.txt file, together in
the same pull request.
Derived table queries for Metadb should also include any relevant external SQL directives, in particular:
--metadb:table <table>
We use the pg_format tool from
pgFormatter to make SQL code
more readable. For maximum consistency, a configuration
file
has been provided. The configuration file should be copied as
~/.pg_format.
Please use this method to format submitted code if possible.
Note that pg_format has known issues such as with formatting
CTEs. In these
cases, manual formatting is preferred.
Names of tables and columns should be in lowercase and use underscores
( _ ) to separate words. Double underscores ( __ ) should be
avoided because they are used in Metadb databases as special
indicators.
In the rare case where a column name is both the same as a reserved
word and stands alone without a table or alias prefix, it must be
enclosed in quotation marks ( " ). Apart from these cases, it is
better not to use quotation marks.
For derived tables, the table names do not follow a purely mechanical
pattern, which might result in some names being impractically long.
The schema/table name of the first table listed after FROM is
normally used as a prefix for the derived table name, for example,
instance_ or item_. Added to this is often a suffix that suggests
the most salient table join, or ext for general extensions of the
data. Overall, it is helpful to think about how to describe the
derived table concisely.
The checklist below can be used to guide your review of a pull request
(PR). A copy of the checklist may be added to a comment attached to a
review. Check off the items that have been confirmed in your review
by adding an x between the square brackets [] on each line.
If any items remain unchecked or you have further questions, you can indicate that in the comment as well and select "Request changes" as the review response.
All queries:
- [ ] PR Title and Description are accurate and thorough
- [ ] PR is based on a new branch (not main)
- [ ] PR scope is not overly broad
- [ ] Query runs without errors
- [ ] Query output is correct
- [ ] Query logic is clear and well documented
- [ ] Query is readable and properly indented
- [ ] Table and column names are in all-lowercase
- [ ] Quotation marks are used only where necessary
- [ ] JSON extraction is in standard form, for example:
LDP: t #>> '{f1,f2,f3}' [for compatibility across LDP 1 & 2]
Metadb: jsonb_extract_path_text(t, f1, f2, f3)
Report queries:
- [ ] Query has complete user documentation
- [ ] Purpose of report
- [ ] Sample output
- [ ] Query instructions
Derived tables:
- [ ] First line is "--metadb:table" directive, followed by blank line
- [ ] User documentation in comment lines, followed by blank line
- [ ] File name is listed in `runlist.txt` after dependencies