-
Overview
FREE
Without any strings attached. It's yours to try and use today!
SECURE
Built with NIST standards in mind. It's great for any federal, state or local organization that needs to share publicly available data through an API.
SUPPORTED
We regularly provide updates and are here to help you.
-
Features
Database Support
PostSQL - Version x or later
DB2 - Version 5.2 or later
Oracle - Version 9i or later
MySQL - Version 4 or later
MSSQL - Version 2010 or later
Protect Your Data
Delegation - Give access to local database administrators to control what data is shared
Obfuscation - Make your table names human-friendly by hiding the technical details
Ease of Use
For Administrators
Administrative interface with forms-based interface for managing:
Datasets
API users
Developer keys
For Developers
One stop shop for your organization's datasets One API key works across all of your datasets Software development kits for 8 languages & platforms
-
Requirements
Basic Configuration
To run Quarry you’ll need:
A Debian based Linux distribution that has at least 5 GB of memory, two 2 gigabyte processors, and 20 gigabytes of free space.
Access to a MySQL or Microsoft SQL server.
Ability to download the REMI and EPEL repositories.
Download Quarry -
Documentation
Quarry API Documentation
Quarry API User Manual
Overview
This document is an operational overview of the administrative interface for Quarry API. Details will be provided on how to manage and publish datasets.
Product Information
Quarry API is a convenient way to access DOL data and is designed using RESTful technology along with the user-friendly web 2.0 accessible layout. Therefore, the burden of data configuration, user management and RESTful API calls can be done with a less technically skilled staff.
Product Features
With Quarry API you can:
- Use any open web language to access data
- Obtain public datasets in JSON or XML formats
- Filter RESTful queries by key/pair parameters
- Run API CRUD (Create, Read, Update, and Delete) operations
- Conduct transactions via mobile device
- Add, edit, and delete users
- Add, edit, and delete user roles and permissions
- Add, edit, and delete Quarry API keys
- Add, edit, delete, and test data sources for DOL dataset retrieval
User Types
Quarry API User Roles User Type Definition Technical Proficiency Super Administrators (Highest category) Performs user and API management LOW Application Managers Manages how data is configured and propagated MID Developers Maintains the interface and writes applications HIGH Consumers End users LOW Quarry API User Roles
User Type: Super Administrators (Highest category)
Definition Performs user and API management
Technical Proficiency: LOW
User Type: Application Managers
Definition: Manages how data is configured and propagated
Technical Proficiency: MID
User Type: Developers
Definition Maintains the interface and writes applications
Technical Proficiency: HIGH
User Type: Consumers
Definition: End users
Technical Proficiency: LOW
User Type Hierarchical Details
This section describes how Quarry user types are defined and their contribution to the overall API data lifecycle.
Super Administrators
Administrators are the highest type of users in Quarry. They have access to all rights and features in the system. Their primary roles are to manage users, generate API keys for users, assign roles to all new users, and monitor the overall system health.
Application Managers
Users who only need to create datasets will be given this role. The application manager manages the data source’s external system and will have the configuration details to create a connection for remote access to the necessary data. The application manager will need a valid API key to test a connection, along with test and examine the dataset prior to giving developers public access.
Developers
Developers can be users who maintain Quarry API’s codebase or are responsible for creating end user applications. Developers generally will use Quarry API to populate their applications.
Consumers
End users will be able to consume Quarry API data via handheld, tablet and desktop.
User Management
User Lifecycle Flow
All Quarry API users will need to register. Below is the Quarry API registration screen.
API Registration
Get Approved by an Administrator
An administrator will see your registration in the Pending Request page and will begin the approval process.
Pending User Account Request List
Approve Pending User Account Request
The pending page allows administrators to select your role and modify other information in your profile. This user has been given Super Admin rights.
User Account Approval Message
Login Screen
Note: Request Access feature has not been implemented as of v.1.0.0
User Password Reset
User Policies
Permissions
AdminUI is a permission-based interface which represents user driven features. All actions and operations are assigned to a permission in the source code. However, a permission must be created in the interface prior to the action being programmed in the source code. The Access Control List (ACL) governs permission’s management in the AdminUI system. You can assign permissions to roles in the Role Manager page.
Access Control List (ACL)
Quarry Access Control List (ACL) Quarry API Permissions Feature/Operation Access ACL Allows access to view, and change settings in the ACL Add Datasets User can add datasets Add Key Generate new API keys Add Permissions Add permissions to the system Add Role Create new roles Add User Allows user to add roles to the system Assign Role Can assign roles to users Block Key Block API keys from interacting with Quarry Delete Dataset Delete datasets Delete Key Delete API keys Delete Permission Delete permissions from the system Delete Role Delete roles from the system Delete User Delete a user from the system Edit Datasets Edit datasets Edit Key Edit API key parameters Edit Permission Edit permission Edit Role Edit role properties Edit User Edit user profiles Modify Key Registration Edit registered API key Search Key Search API keys by properties Unblock Key Unblock an API key Validate Host Allowed to validate a data source connection View Datasets View datasets View Key View API keys View Permissions View permissions list View Rest Logs View REST transactions log View Roles View user role list View Users View user account list Quarry Access Control List (ACL)
Quarry API Permissions: Access ACL
Feature/Operation: Allows access to view, and change settings in the ACL
Quarry API Permissions: Add Datasets
Feature/Operation: User can add datasets
Quarry API Permissions: Add Key
Feature/Operation: Generate new API keys
Quarry API Permissions: Add Permissions
Feature/Operation: Add permissions to the system
Quarry API Permissions: Add Role
Feature/Operation: Create new roles
Quarry API Permissions: Add User
Feature/Operation: Allows user to add roles to the system
Quarry API Permissions: Assign Role
Feature/Operation: Can assign roles to users
Quarry API Permissions: Block Key
Feature/Operation: Block API keys from interacting with Quarry
Quarry API Permissions: Delete Dataset
Feature/Operation: Delete datasets
Quarry API Permissions: Delete Key
Feature/Operation: Delete API keys
Quarry API Permissions: Delete Permission
Feature/Operation: Delete permissions from the system
Quarry API Permissions: Delete Role
Feature/Operation: Delete roles from the system
Quarry API Permissions: Delete User
Feature/Operation: Delete a user from the system
Quarry API Permissions: Edit Datasets
Feature/Operation: Edit datasets
Quarry API Permissions: Edit Key
Feature/Operation: Edit API key parameters
Quarry API Permissions: Edit Permission
Feature/Operation: Edit permission
Quarry API Permissions: Edit Role
Feature/Operation: Edit role properties
Quarry API Permissions: Edit User
Feature/Operation: Edit user profiles
Quarry API Permissions: Modify Key Registration
Feature/Operation: Edit registered API key
Quarry API Permissions: Search Key
Feature/Operation: Search API keys by properties
Quarry API Permissions: Unblock Key
Feature/Operation: Unblock an API key
Quarry API Permissions: Validate Host
Feature/Operation: Allowed to validate a data source connection
Quarry API Permissions: View Datasets
Feature/Operation: View datasets
Quarry API Permissions: View Key
Feature/Operation: View API keys
Quarry API Permissions: View Permissions
Feature/Operation: View permissions list
Quarry API Permissions: View Rest Logs
Feature/Operation: View REST transactions log
Quarry API Permissions: View Roles
Feature/Operation: View user role list
Quarry API Permissions: View Users
Feature/Operation: View user account list
Adding Permissions
Adding permissions is a straightforward process. Please make sure you do not create duplicate system IDs or system names.
Quarry Permissions Property Definition System Name Human readable name for a permission System ID Slug name that is used in the source code Description Description of the permission Quarry Permissions
Property: System Name
Definition: Human readable name for a permission
Property: System ID
Definition: Slug name that is used in the source code
Property: Description
Definition: Description of the permission
Roles
All users must be assigned a role, which is a set of permissions that grants users access to AdminUI features.
Quarry Roles User Type Rights Super Administrators All permissions Application Managers Permissions only related to dataset management Quarry Roles
User Type: Super Administrators
Rights: All permissions
User Type: Application Managers
Rights: Permissions only related to dataset management
You can create roles in the Account Manager menu. A role must be assigned at least one permission during its creation. The table below describes the required role parameters.
List of User Roles Property Definition System Name Human readable name System ID Slug name that is used in the source code Description Description of the role Permissions You can assign permissions in the UI List of User Roles
Property: System Name
Definition: Human readable name
Property: System ID
Definition: Slug name that is used in the source code
Property: Description
Definition: Description of the role
Property: Permissions
Definition: You can assign permissions in the UI
Creating Roles
Permissions will vary based on the user role, which you cannot have duplicate system IDs or system names.
Quarry API Key Management
Creating Quarry API Keys
Administrators can create new API keys by using the Register New Key tab found under the API Key Manager menu. Quarry API will accept APIv1 keys as well. A user can own multiple Quarry API keys. New API keys are emailed to the user.
OSHA Violation General Duty Standards API Key Property Definition API key name API label given by the user or organization Assigned E-mail Address User’s email address Description API key description Quarry API Key Information
API Key Property: API key name
Definition: API label given by the user or organization
API Key Property: Assigned E-mail Address
Definition: User’s email address
API Key Property: Description
Definition: API key description
Quarry API Keys
The API Key Manager contains a list of API keys. In addition to deleting keys, administrators can block/unblock user keys should the need arises, along with additional features available on this page.
Dataset Service Management
The Manage Datasets menu allows administrators to configure datasets for the Quarry API REST engine. The definitions in table 6.1 are the main components for the dataset management service. The connection string parameters are supplied by the data source owner.
Be mindful that Quarry API v1.0.0 currently supports four database types.
Supported Database Types:
- MySQL
- Microsoft SQL
- Oracle
- PostgreSQL
Quarry Database Query Information Quarry Data service Definition Connection string Connection strings are input parameters that allow Quarry to connect to a remote data source Data source Remote data resource that will be RESTfully accessed by Quarry API Dataset RESTful data structure based on a user’s input Obfuscation Technique of obscuring the data source’s table names for security purposes Service Quarry provides round trip user input translation services from HTTP to SQL as outlined in list 6.1 Using advance technology lessens the technical learning curve while enhancing the user experience Quarry Database Query Information
Quarry Data service: Connection string
Definition: Connection strings are input parameters that allow Quarry to connect to a remote data source
Quarry Data service: Data source
Definition: Remote data resource that will be RESTfully accessed by Quarry API
Quarry Data service: Dataset
Definition: RESTful data structure based on a user’s input
Quarry Data service: Obfuscation
Definition: Technique of obscuring the data source’s table names for security purposes
Quarry Data service: Service
Definition: Quarry provides round trip user input translation services from HTTP to SQL
as outlined in list 6.1 Using advance technology lessens the technical learning curve while enhancing the user experienceTypical Configuration Sequence of Quarry API Data Service
- The application manager will contact the data source owner for the connection string information.
- The application manager will request that the data source owner provides an obfuscated list of available table names.
- The application manager will add a dataset by selecting the appropriate database type and entering the connection string parameters including the obfuscated table name information.
- Test the new connection string for datasets using Quarry’s AdminUI. You can see the datasets information in either JSON or XML format, dependent on your choice.
- If you are able to successfully connect to a data source, but cannot retrieve data, contact the data source owner and check if the remote configuration is working or whether the database has data.
Add Data Service
Obtain connection string parameters from the data source owner. Use table 6.2 as a reference. If the owner does not have the database type listed, Quarry API cannot offer service. Please note which fields in this table are required, optional or not applicable depending on the database type (MySQL, Microsoft SQL, Oracle and PostgreSQL).
Quarry Database Fields Database Parameters MySQL Microsoft SQL Oracle PostgreSQL FQDN/IP Address Required Required Required Required API Username Required Required Required Required API Password Required Required Required Required DB Name Required Required Required Required Instance N/A Optional N/A N/A Schema N/A Required Required Required Table Name Required Required Required Required Alias Required Required Required Required Column Name Optional Optional Optional Optional SID or Service Name N/A N/A Optional N/A Port Optional Optional Optional Optional Description Required Required Required Required Quarry Database Fields
Database Parameters: FQDN/IP Address
MySQL: Required
Microsoft SQL: Required
Oracle: Required
PostgreSQL: Required
Database Parameters: API Username
MySQL: Required
Microsoft SQL: Required
Oracle: Required
PostgreSQL: Required
Database Parameters: API Password
MySQL: Required
Microsoft SQL: Required
Oracle: Required
PostgreSQL: Required
Database Parameters: DB Name
MySQL: Required
Microsoft SQL: Required
Oracle: Required
PostgreSQL: Required
Database Parameters: Instance
MySQL: N/A
Microsoft SQL: Optional
Oracle: N/A
PostgreSQL: N/A
Database Parameters: Schema
MySQL: N/A
Microsoft SQL: Required
Oracle: Required
PostgreSQL: Required
Database Parameters: Table Name
MySQL: Required
Microsoft SQL: Required
Oracle: Required
PostgreSQL: Required
Database Parameters: Alias
MySQL: Required
Microsoft SQL: Required
Oracle: Required
PostgreSQL: Required
Database Parameters: Column Name
MySQL: Optional
Microsoft SQL: Optional
Oracle: Optional
PostgreSQL: Optional
Database Parameters: SID or Service Name
MySQL: N/A
Microsoft SQL: N/A
Oracle: Optional
PostgreSQL: N/A
Database Parameters: Port
MySQL: Optional
Microsoft SQL: Optional
Oracle: Optional
PostgreSQL: Optional
Database Parameters: Description
MySQL: Required
Microsoft SQL: Required
Oracle: Required
PostgreSQL: Required
Figure 6.1 shows how to connect to an Oracle data source to Quarry. Please use table 6.1 as a guide to which fields are necessary.
Rest User Manual
Use Your Quarry API Key (Required)
IF YOU HAVE AN APIv1 KEY, PLEASE SKIP THIS SECTION
Register at https://devtools.dol.gov/developer/Account/Register to get a QUARRY API key. The new API key will be e-mailed to you.
cURL Generic Quarry Usage (Strict Order)
curl -k -H "X-API-KEY: YOUR_API_KEY" –X GET https://data.dol.gov/get/ALIAS/filtername1/filtervalue1/filtername2/filtervalue2
cURL Syntax Name Description Rest_servername https://data.dol.gov/get/ YOUR_API_KEY Your QUARRY API key ALIAS You must provide a valid alias data table name filtername Filtername is part of a key/value pair filtervalue The value of the filter key/pair can either be set as a fixed parameter or as a number cURL Syntax
Name: Rest_servername
Description: https://data.dol.gov/get/
Name: YOUR_API_KEY
Description: Your QUARRY API key
Name: ALIAS
Description: You must provide a valid alias data table name
Name: filtername
Description: Filtername is part of a key/value pair
Name: filtervalue
Description: The value of the filter key/pair can either be set as a fixed parameter or as a number
Filtering Options
Format
Dataset will be in JSON or XML with JSON being the default format.
Usage
- format/json or format/xml
Limit
Number of rows Quarry will return. While the maximum allowed for Quarry is 200.
Usage
- limit/your_number = desired rows
- limit/200 = Quarry API limit
Order
Ascending or descending sorting order as determined by the targeted datasource.
Usage
- orderby/asc = data in ascending order sorted by the first field
- orderby/desc = data in descending order sorted by the first field
Columns
Columns can be selected individually with the columns keyword. The datasets will be sorted by the first field you list after the columns keyword.
Columns Syntax Syntax Description columns Must be included for column filtering { Begin columns option : Delimiter separating columns } End column selection Columns Syntax
Syntax: columns
Description: Must be included for column filtering
Syntax: {
Description: Begin columns option
Syntax: :
Description: Delimiter separating columns
Syntax: }
Description: End column selection
Usage – Separated by colon(s)
- columns/{first_column:second_column:third_column}
Dates - (yyyy-mm-dd)
Date Syntax Field Description date_column Must be included to search by dates start_date Start date will search from the start date until the latest date available end_date From the past up until the end date (returned in reversed order from the end date) Both Looks for the exact date range asked for from the start_date to the end_date Field: date_column
Description: Must be included to search by dates
Field: start_date
Description: Start date will search from the start date until the latest date available
Field: end_date
Description: From the past up until the end date (returned in reversed order from the end date)
Field: Both
Description: Looks for the exact date range asked for from the start_date to the end_date
Multi-Filtering
Examples
Usage One
curl -k -H "X-API-KEY: YOUR_API_KEY" –X GET https://data.dol.gov/get/datasource/format/xml/limit/10/orderby/desc/columns/{column_1: column_2: column_3}
Explanation One
Select 10 rows of columns 1, 2, and 3 from ‘table_alias’ in descending order of column 1 in XML format.
Usage Two
curl -k -H "X-API-KEY: YOUR_API_KEY" https://data.dol.gov/get/datasource /limit/130/orderby/asc/columns/{date_column_1:column_2:column_3}/date_column/date_column_1/ start_date/2005-03-15/end_date/2010-04-15
Explanation Two
Select 130 rows of columns 1, 2, and 3 from ‘table_alias’ in ascending order of date_column_1 by date range 2005-03-15 to 2010-04-15
Please Note
- Metadata information on data sources schemas is not included in this release.
- Supported SQL drivers - Oracle, MSSQL, MySQL and PostgreSQL
- APIv1 keys are accepted
REST Response Codes
Quarry REST Response Codes and Descriptions Code Text Description 200 OK Success! 201 Created/POST The request has been fulfilled and resulted in a new resource being created. The newly created resource can be referenced by the URI(s) returned in the entity of the response, with the most specific URI for the resource given by a Location header field. The response SHOULD include an entity containing a list of resource characteristics and location(s) from which the user or user agent can choose the one most appropriate. The entity format is specified by the media type given in the Content-Type header field. The origin server MUST create the resource before returning the 201 status code. If the action cannot be carried out immediately, the server SHOULD respond with 202 (Accepted) response instead. 202 Accepted The request has been accepted for processing, but the processing has not been completed. The request might or might not eventually be acted upon, as it might be disallowed when processing actually takes place. There is no facility for re-sending a status code from an asynchronous operation such as this. 400 Bad Request The request could not be understood by the server due to malformed syntax. The client SHOULD NOT repeat the request without modifications. 403 Forbidden The server understood the request, but is refusing to fulfill it. Authorization will not help and the request SHOULD NOT be repeated. If the request method was not HEAD and the server wishes to make public why the request has not been fulfilled, it SHOULD describe the reason for the refusal in the entity. If the server does not wish to make this information available to the client, the status code 404 (Not Found) can be used instead. 406 Not Accepted The resource identified by the request is only capable of generating response entities which have content characteristics not acceptable according to the accept headers sent in the request. Quarry REST Response Code Description
Code: 200
Text: OK
Description: Success!
Code: 201
Text: Created/POST
Description: The request has been fulfilled and resulted in a new resource being created. The newly created resource can be referenced by the URI(s) returned in the entity of the response, with the most specific URI for the resource given by a Location header field. The response SHOULD include an entity containing a list of resource characteristics and location(s) from which the user or user agent can choose the one most appropriate. The entity format is specified by the media type given in the Content-Type header field. The origin server MUST create the resource before returning the 201 status code. If the action cannot be carried out immediately, the server SHOULD respond with 202 (Accepted) response instead.
Code: 202
Text: Accepted
Description: The request has been accepted for processing, but the processing has not been completed. The request might or might not eventually be acted upon, as it might be disallowed when processing actually takes place. There is no facility for re-sending a status code from an asynchronous operation such as this.
Code: 400
Text: Bad Request
Description: The request could not be understood by the server due to malformed syntax. The client SHOULD NOT repeat the request without modifications.
Code: 403
Text: Forbidden
Description: The server understood the request, but is refusing to fulfill it. Authorization will not help and the request SHOULD NOT be repeated. If the request method was not HEAD and the server wishes to make public why the request has not been fulfilled, it SHOULD describe the reason for the refusal in the entity. If the server does not wish to make this information available to the client, the status code 404 (Not Found) can be used instead.
Code: 406
Text: Not Accepted
Description: The resource identified by the request is only capable of generating response entities which have content characteristics not acceptable according to the accept headers sent in the request.
-
FREE
Without any strings attached. It's
yours to try and use today! -
SECURE
Built with NIST standards in
mind. It's great for any federal,
state or local organization that
needs to share publicly available
data through an API. -
SUPPORTED
We regularly provide updates
and are here to help you.
Database Support
PostSQL - Version x or later
DB2 - Version 5.2 or later
Oracle - Version 9i or later
MySQL - Version 4 or later
MSSQL - Version 2010 or later
Protect Your Data
Delegation - Give access to local database administrators to control what data is shared
Obfuscation - Make your table names human-friendly by hiding the technical details
Ease of Use
For Administrators
For Developers
Administrative interface with forms-based interface for managing:
- Datasets
- API users
- Developer keys
One stop shop for your organization's datasets
One API key works across all of your datasets
Software development kits for 8 languages & platforms
Basic Configuration
To run Quarry you’ll need:
A Debian based Linux distribution that has at least 5 GB of memory, two 2 gigabyte processors, and 20 gigabytes of free space.
Access to a MySQL or Microsoft SQL server.
Ability to download the REMI and EPEL repositories.
Download QuarryQuarry API Documentation
Quarry API User Manual
Overview
This document is an operational overview of the administrative interface for Quarry API. Details will be provided on how to manage and publish datasets.
Product Information
Quarry API is a convenient way to access DOL data and is designed using RESTful technology along with the user-friendly web 2.0 accessible layout. Therefore, the burden of data configuration, user management and RESTful API calls can be done with a less technically skilled staff.
Product Features
With Quarry API you can:
- Use any open web language to access data
- Obtain public datasets in JSON or XML formats
- Filter RESTful queries by key/pair parameters
- Run API CRUD (Create, Read, Update, and Delete) operations
- Conduct transactions via mobile device
- Add, edit, and delete users
- Add, edit, and delete user roles and permissions
- Add, edit, and delete Quarry API keys
- Add, edit, delete, and test data sources for DOL dataset retrieval
User Types
| User Type | Definition | Technical Proficiency |
|---|---|---|
| Super Administrators (Highest category) | Performs user and API management | LOW |
| Application Managers | Manages how data is configured and propagated | MID |
| Developers | Maintains the interface and writes applications | HIGH |
| Consumers | End users | LOW |
Quarry API User Roles
User Type: Super Administrators (Highest category)
Definition Performs user and API management
Technical Proficiency: LOW
User Type: Application Managers
Definition: Manages how data is configured and propagated
Technical Proficiency: MID
User Type: Developers
Definition Maintains the interface and writes applications
Technical Proficiency: HIGH
User Type: Consumers
Definition: End users
Technical Proficiency: LOW
User Type Hierarchical Details
This section describes how Quarry user types are defined and their contribution to the overall API data lifecycle.
Super Administrators
Administrators are the highest type of users in Quarry. They have access to all rights and features in the system. Their primary roles are to manage users, generate API keys for users, assign roles to all new users, and monitor the overall system health.
Application Managers
Users who only need to create datasets will be given this role. The application manager manages the data source’s external system and will have the configuration details to create a connection for remote access to the necessary data. The application manager will need a valid API key to test a connection, along with test and examine the dataset prior to giving developers public access.
Developers
Developers can be users who maintain Quarry API’s codebase or are responsible for creating end user applications. Developers generally will use Quarry API to populate their applications.
Consumers
End users will be able to consume Quarry API data via handheld, tablet and desktop.
User Management
User Lifecycle Flow
All Quarry API users will need to register. Below is the Quarry API registration screen.
API Registration
Get Approved by an Administrator
An administrator will see your registration in the Pending Request page and will begin the approval process.
Pending User Account Request List
Approve Pending User Account Request
The pending page allows administrators to select your role and modify other information in your profile. This user has been given Super Admin rights.
User Account Approval Message
Login Screen
Note: Request Access feature has not been implemented as of v.1.0.0
User Password Reset
User Policies
Permissions
AdminUI is a permission-based interface which represents user driven features. All actions and operations are assigned to a permission in the source code. However, a permission must be created in the interface prior to the action being programmed in the source code. The Access Control List (ACL) governs permission’s management in the AdminUI system. You can assign permissions to roles in the Role Manager page.
Access Control List (ACL)
| Quarry API Permissions | Feature/Operation |
|---|---|
| Access ACL | Allows access to view, and change settings in the ACL |
| Add Datasets | User can add datasets |
| Add Key | Generate new API keys |
| Add Permissions | Add permissions to the system |
| Add Role | Create new roles |
| Add User | Allows user to add roles to the system |
| Assign Role | Can assign roles to users |
| Block Key | Block API keys from interacting with Quarry |
| Delete Dataset | Delete datasets |
| Delete Key | Delete API keys |
| Delete Permission | Delete permissions from the system |
| Delete Role | Delete roles from the system |
| Delete User | Delete a user from the system |
| Edit Datasets | Edit datasets |
| Edit Key | Edit API key parameters |
| Edit Permission | Edit permission |
| Edit Role | Edit role properties |
| Edit User | Edit user profiles |
| Modify Key Registration | Edit registered API key |
| Search Key | Search API keys by properties |
| Unblock Key | Unblock an API key |
| Validate Host | Allowed to validate a data source connection |
| View Datasets | View datasets |
| View Key | View API keys |
| View Permissions | View permissions list |
| View Rest Logs | View REST transactions log |
| View Roles | View user role list |
| View Users | View user account list |
Quarry Access Control List (ACL)
Quarry API Permissions: Access ACL
Feature/Operation: Allows access to view, and change settings in the ACL
Quarry API Permissions: Add Datasets
Feature/Operation: User can add datasets
Quarry API Permissions: Add Key
Feature/Operation: Generate new API keys
Quarry API Permissions: Add Permissions
Feature/Operation: Add permissions to the system
Quarry API Permissions: Add Role
Feature/Operation: Create new roles
Quarry API Permissions: Add User
Feature/Operation: Allows user to add roles to the system
Quarry API Permissions: Assign Role
Feature/Operation: Can assign roles to users
Quarry API Permissions: Block Key
Feature/Operation: Block API keys from interacting with Quarry
Quarry API Permissions: Delete Dataset
Feature/Operation: Delete datasets
Quarry API Permissions: Delete Key
Feature/Operation: Delete API keys
Quarry API Permissions: Delete Permission
Feature/Operation: Delete permissions from the system
Quarry API Permissions: Delete Role
Feature/Operation: Delete roles from the system
Quarry API Permissions: Delete User
Feature/Operation: Delete a user from the system
Quarry API Permissions: Edit Datasets
Feature/Operation: Edit datasets
Quarry API Permissions: Edit Key
Feature/Operation: Edit API key parameters
Quarry API Permissions: Edit Permission
Feature/Operation: Edit permission
Quarry API Permissions: Edit Role
Feature/Operation: Edit role properties
Quarry API Permissions: Edit User
Feature/Operation: Edit user profiles
Quarry API Permissions: Modify Key Registration
Feature/Operation: Edit registered API key
Quarry API Permissions: Search Key
Feature/Operation: Search API keys by properties
Quarry API Permissions: Unblock Key
Feature/Operation: Unblock an API key
Quarry API Permissions: Validate Host
Feature/Operation: Allowed to validate a data source connection
Quarry API Permissions: View Datasets
Feature/Operation: View datasets
Quarry API Permissions: View Key
Feature/Operation: View API keys
Quarry API Permissions: View Permissions
Feature/Operation: View permissions list
Quarry API Permissions: View Rest Logs
Feature/Operation: View REST transactions log
Quarry API Permissions: View Roles
Feature/Operation: View user role list
Quarry API Permissions: View Users
Feature/Operation: View user account list
Adding Permissions
Adding permissions is a straightforward process. Please make sure you do not create duplicate system IDs or system names.
| Property | Definition |
|---|---|
| System Name | Human readable name for a permission |
| System ID | Slug name that is used in the source code |
| Description | Description of the permission |
Quarry Permissions
Property: System Name
Definition: Human readable name for a permission
Property: System ID
Definition: Slug name that is used in the source code
Property: Description
Definition: Description of the permission
Roles
All users must be assigned a role, which is a set of permissions that grants users access to AdminUI features.
| User Type | Rights |
|---|---|
| Super Administrators | All permissions |
| Application Managers | Permissions only related to dataset management |
Quarry Roles
User Type: Super Administrators
Rights: All permissions
User Type: Application Managers
Rights: Permissions only related to dataset management
You can create roles in the Account Manager menu. A role must be assigned at least one permission during its creation. The table below describes the required role parameters.
| Property | Definition |
|---|---|
| System Name | Human readable name |
| System ID | Slug name that is used in the source code |
| Description | Description of the role |
| Permissions | You can assign permissions in the UI |
List of User Roles
Property: System Name
Definition: Human readable name
Property: System ID
Definition: Slug name that is used in the source code
Property: Description
Definition: Description of the role
Property: Permissions
Definition: You can assign permissions in the UI
Creating Roles
Permissions will vary based on the user role, which you cannot have duplicate system IDs or system names.
Quarry API Key Management
Creating Quarry API Keys
Administrators can create new API keys by using the Register New Key tab found under the API Key Manager menu. Quarry API will accept APIv1 keys as well. A user can own multiple Quarry API keys. New API keys are emailed to the user.
| API Key Property | Definition |
|---|---|
| API key name | API label given by the user or organization |
| Assigned E-mail Address | User’s email address |
| Description | API key description |
Quarry API Key Information
API Key Property: API key name
Definition: API label given by the user or organization
API Key Property: Assigned E-mail Address
Definition: User’s email address
API Key Property: Description
Definition: API key description
Quarry API Keys
The API Key Manager contains a list of API keys. In addition to deleting keys, administrators can block/unblock user keys should the need arises, along with additional features available on this page.
Dataset Service Management
The Manage Datasets menu allows administrators to configure datasets for the Quarry API REST engine. The definitions in table 6.1 are the main components for the dataset management service. The connection string parameters are supplied by the data source owner.
Be mindful that Quarry API v1.0.0 currently supports four database types.
Supported Database Types:
- MySQL
- Microsoft SQL
- Oracle
- PostgreSQL
| Quarry Data service | Definition |
|---|---|
| Connection string | Connection strings are input parameters that allow Quarry to connect to a remote data source |
| Data source | Remote data resource that will be RESTfully accessed by Quarry API |
| Dataset | RESTful data structure based on a user’s input |
| Obfuscation | Technique of obscuring the data source’s table names for security purposes |
| Service | Quarry provides round trip user input translation services from HTTP to SQL as outlined in list 6.1 Using advance technology lessens the technical learning curve while enhancing the user experience |
Quarry Database Query Information
Quarry Data service: Connection string
Definition: Connection strings are input parameters that allow Quarry to connect to a remote data source
Quarry Data service: Data source
Definition: Remote data resource that will be RESTfully accessed by Quarry API
Quarry Data service: Dataset
Definition: RESTful data structure based on a user’s input
Quarry Data service: Obfuscation
Definition: Technique of obscuring the data source’s table names for security purposes
Quarry Data service: Service
Definition: Quarry provides round trip user input translation services from HTTP to SQL
as outlined in list 6.1 Using advance technology lessens the technical learning curve while enhancing the user experience
Typical Configuration Sequence of Quarry API Data Service
- The application manager will contact the data source owner for the connection string information.
- The application manager will request that the data source owner provides an obfuscated list of available table names.
- The application manager will add a dataset by selecting the appropriate database type and entering the connection string parameters including the obfuscated table name information.
- Test the new connection string for datasets using Quarry’s AdminUI. You can see the datasets information in either JSON or XML format, dependent on your choice.
- If you are able to successfully connect to a data source, but cannot retrieve data, contact the data source owner and check if the remote configuration is working or whether the database has data.
Add Data Service
Obtain connection string parameters from the data source owner. Use table 6.2 as a reference. If the owner does not have the database type listed, Quarry API cannot offer service. Please note which fields in this table are required, optional or not applicable depending on the database type (MySQL, Microsoft SQL, Oracle and PostgreSQL).
| Database Parameters | MySQL | Microsoft SQL | Oracle | PostgreSQL |
|---|---|---|---|---|
| FQDN/IP Address | Required | Required | Required | Required |
| API Username | Required | Required | Required | Required |
| API Password | Required | Required | Required | Required |
| DB Name | Required | Required | Required | Required |
| Instance | N/A | Optional | N/A | N/A |
| Schema | N/A | Required | Required | Required |
| Table Name | Required | Required | Required | Required |
| Alias | Required | Required | Required | Required |
| Column Name | Optional | Optional | Optional | Optional |
| SID or Service Name | N/A | N/A | Optional | N/A |
| Port | Optional | Optional | Optional | Optional |
| Description | Required | Required | Required | Required |
Quarry Database Fields
Database Parameters: FQDN/IP Address
MySQL: Required
Microsoft SQL: Required
Oracle: Required
PostgreSQL: Required
Database Parameters: API Username
MySQL: Required
Microsoft SQL: Required
Oracle: Required
PostgreSQL: Required
Database Parameters: API Password
MySQL: Required
Microsoft SQL: Required
Oracle: Required
PostgreSQL: Required
Database Parameters: DB Name
MySQL: Required
Microsoft SQL: Required
Oracle: Required
PostgreSQL: Required
Database Parameters: Instance
MySQL: N/A
Microsoft SQL: Optional
Oracle: N/A
PostgreSQL: N/A
Database Parameters: Schema
MySQL: N/A
Microsoft SQL: Required
Oracle: Required
PostgreSQL: Required
Database Parameters: Table Name
MySQL: Required
Microsoft SQL: Required
Oracle: Required
PostgreSQL: Required
Database Parameters: Alias
MySQL: Required
Microsoft SQL: Required
Oracle: Required
PostgreSQL: Required
Database Parameters: Column Name
MySQL: Optional
Microsoft SQL: Optional
Oracle: Optional
PostgreSQL: Optional
Database Parameters: SID or Service Name
MySQL: N/A
Microsoft SQL: N/A
Oracle: Optional
PostgreSQL: N/A
Database Parameters: Port
MySQL: Optional
Microsoft SQL: Optional
Oracle: Optional
PostgreSQL: Optional
Database Parameters: Description
MySQL: Required
Microsoft SQL: Required
Oracle: Required
PostgreSQL: Required
Figure 6.1 shows how to connect to an Oracle data source to Quarry. Please use table 6.1 as a guide to which fields are necessary.
Rest User Manual
Use Your Quarry API Key (Required)
IF YOU HAVE AN APIv1 KEY, PLEASE SKIP THIS SECTION
Register at https://devtools.dol.gov/developer/Account/Register to get a QUARRY API key. The new API key will be e-mailed to you.
cURL Generic Quarry Usage (Strict Order)
curl -k -H "X-API-KEY: YOUR_API_KEY" –X GET https://data.dol.gov/get/ALIAS/filtername1/filtervalue1/filtername2/filtervalue2
| Name | Description |
|---|---|
| Rest_servername | https://data.dol.gov/get/ |
| YOUR_API_KEY | Your QUARRY API key |
| ALIAS | You must provide a valid alias data table name |
| filtername | Filtername is part of a key/value pair |
| filtervalue | The value of the filter key/pair can either be set as a fixed parameter or as a number |
cURL Syntax
Name: Rest_servername
Description: https://data.dol.gov/get/
Name: YOUR_API_KEY
Description: Your QUARRY API key
Name: ALIAS
Description: You must provide a valid alias data table name
Name: filtername
Description: Filtername is part of a key/value pair
Name: filtervalue
Description: The value of the filter key/pair can either be set as a fixed parameter or as a number
Filtering Options
Format
Dataset will be in JSON or XML with JSON being the default format.
Usage
- format/json or format/xml
Limit
Number of rows Quarry will return. While the maximum allowed for Quarry is 200.
Usage
- limit/your_number = desired rows
- limit/200 = Quarry API limit
Order
Ascending or descending sorting order as determined by the targeted datasource.
Usage
- orderby/asc = data in ascending order sorted by the first field
- orderby/desc = data in descending order sorted by the first field
Columns
Columns can be selected individually with the columns keyword. The datasets will be sorted by the first field you list after the columns keyword.
| Syntax | Description |
|---|---|
| columns | Must be included for column filtering |
| { | Begin columns option |
| : | Delimiter separating columns |
| } | End column selection |
Columns Syntax
Syntax: columns
Description: Must be included for column filtering
Syntax: {
Description: Begin columns option
Syntax: :
Description: Delimiter separating columns
Syntax: }
Description: End column selection
Usage – Separated by colon(s)
- columns/{first_column:second_column:third_column}
Dates - (yyyy-mm-dd)
| Field | Description |
|---|---|
| date_column | Must be included to search by dates |
| start_date | Start date will search from the start date until the latest date available |
| end_date | From the past up until the end date (returned in reversed order from the end date) |
| Both | Looks for the exact date range asked for from the start_date to the end_date |
Field: date_column
Description: Must be included to search by dates
Field: start_date
Description: Start date will search from the start date until the latest date available
Field: end_date
Description: From the past up until the end date (returned in reversed order from the end date)
Field: Both
Description: Looks for the exact date range asked for from the start_date to the end_date
Multi-Filtering
Examples
Usage One
curl -k -H "X-API-KEY: YOUR_API_KEY" –X GET https://data.dol.gov/get/datasource/format/xml/limit/10/orderby/desc/columns/{column_1: column_2: column_3}
Explanation One
Select 10 rows of columns 1, 2, and 3 from ‘table_alias’ in descending order of column 1 in XML format.
Usage Two
curl -k -H "X-API-KEY: YOUR_API_KEY" https://data.dol.gov/get/datasource /limit/130/orderby/asc/columns/{date_column_1:column_2:column_3}/date_column/date_column_1/ start_date/2005-03-15/end_date/2010-04-15
Explanation Two
Select 130 rows of columns 1, 2, and 3 from ‘table_alias’ in ascending order of date_column_1 by date range 2005-03-15 to 2010-04-15
Please Note
- Metadata information on data sources schemas is not included in this release.
- Supported SQL drivers - Oracle, MSSQL, MySQL and PostgreSQL
- APIv1 keys are accepted
REST Response Codes
| Code | Text | Description |
|---|---|---|
| 200 | OK | Success! |
| 201 | Created/POST | The request has been fulfilled and resulted in a new resource being created. The newly created resource can be referenced by the URI(s) returned in the entity of the response, with the most specific URI for the resource given by a Location header field. The response SHOULD include an entity containing a list of resource characteristics and location(s) from which the user or user agent can choose the one most appropriate. The entity format is specified by the media type given in the Content-Type header field. The origin server MUST create the resource before returning the 201 status code. If the action cannot be carried out immediately, the server SHOULD respond with 202 (Accepted) response instead. |
| 202 | Accepted | The request has been accepted for processing, but the processing has not been completed. The request might or might not eventually be acted upon, as it might be disallowed when processing actually takes place. There is no facility for re-sending a status code from an asynchronous operation such as this. |
| 400 | Bad Request | The request could not be understood by the server due to malformed syntax. The client SHOULD NOT repeat the request without modifications. |
| 403 | Forbidden | The server understood the request, but is refusing to fulfill it. Authorization will not help and the request SHOULD NOT be repeated. If the request method was not HEAD and the server wishes to make public why the request has not been fulfilled, it SHOULD describe the reason for the refusal in the entity. If the server does not wish to make this information available to the client, the status code 404 (Not Found) can be used instead. |
| 406 | Not Accepted | The resource identified by the request is only capable of generating response entities which have content characteristics not acceptable according to the accept headers sent in the request. |
Quarry REST Response Code Description
Code: 200
Text: OK
Description: Success!
Code: 201
Text: Created/POST
Description: The request has been fulfilled and resulted in a new resource being created. The newly created resource can be referenced by the URI(s) returned in the entity of the response, with the most specific URI for the resource given by a Location header field. The response SHOULD include an entity containing a list of resource characteristics and location(s) from which the user or user agent can choose the one most appropriate. The entity format is specified by the media type given in the Content-Type header field. The origin server MUST create the resource before returning the 201 status code. If the action cannot be carried out immediately, the server SHOULD respond with 202 (Accepted) response instead.
Code: 202
Text: Accepted
Description: The request has been accepted for processing, but the processing has not been completed. The request might or might not eventually be acted upon, as it might be disallowed when processing actually takes place. There is no facility for re-sending a status code from an asynchronous operation such as this.
Code: 400
Text: Bad Request
Description: The request could not be understood by the server due to malformed syntax. The client SHOULD NOT repeat the request without modifications.
Code: 403
Text: Forbidden
Description: The server understood the request, but is refusing to fulfill it. Authorization will not help and the request SHOULD NOT be repeated. If the request method was not HEAD and the server wishes to make public why the request has not been fulfilled, it SHOULD describe the reason for the refusal in the entity. If the server does not wish to make this information available to the client, the status code 404 (Not Found) can be used instead.
Code: 406
Text: Not Accepted
Description: The resource identified by the request is only capable of generating response entities which have content characteristics not acceptable according to the accept headers sent in the request.