Overriding RedDog’s reference queries

Index

1. Introduction
2. How to override queries
3. SQL Files
4. Needed Catalogs
5. Additional Notes

Introduction

This document is a formal definition of the SQL Provider’s query interface. Users that aim to implement Option 2: Overriding SQL Provider queries, need to provide queries that fulfill these requirements.

How to override queries

The queries are expected to be found in a directory called user_sql_files/ at the installation directory WEB-INF/classes/ (which is created when the application is deployed). This directory should be a collection of .sql files, mirroring the ones from the default implementation. The page Deploying rdap-server with the customized SQL Provider states where this directory should be placed to be detected by RedDog.

Except for queries referred as “catalogs”, it isn’t expected to override all the objects, and within each object isn’t required to define every query. Only the necessary queries should be identified and provided. RedDog will return an HTTP 501 status code if it receives a request whose result information depends of an undefined query.

Each .sql file should list the queries needed to interact with the object. The following rules must be followed by each query:

• Name the query: the first line must contain the name, which is declared using a ‘#’ character followed by a string (eg. #getByRange).
• Parameters must be represented with the character ‘?’, since PreparedStatement objects are used by the implementation.
• The query MUST end with a semi-colon ‘;’, since this is the expected delimiter per query definition.
• Optionally, the DB schema for each query can be parameterized using the string {schema}. This parameter will be replaced with the default value defined at sql_provider_configuration.properties, or with the custom value at data-access.properties.

As an example, here’s how one might implement Autnum.sql:

	#getByRange
	SELECT asn.asn_id, asn.asn_handle, asn.asn_start_autnum, asn.asn_end_autnum, asn.asn_name, asn.asn_type, asn.asn_port43, asn.ccd_id 
	FROM {schema}.autonomous_system_number asn 
	WHERE asn.asn_start_autnum <= ? AND asn.asn_end_autnum >= ?;

	#getAutnumByEntity
	SELECT asn.asn_id, asn.asn_handle, asn.asn_start_autnum, asn.asn_end_autnum, asn.asn_name, asn.asn_type, asn.asn_port43, asn.ccd_id 
	FROM {schema}.autonomous_system_number asn 
	JOIN {schema}.asn_entity_roles ent ON ent.asn_id = asn.asn_id 
	WHERE ent.ent_id = ?;

The names of the columns and the number and order of parameters (‘?’ symbols) need to match the requirements listed for each query in the following sections. It should be noted that SQL aliases can be used to rename columns when the query alone would yield some other name.

The RedDog implemented SQL files are the following:

• Autnum.sql
• Domain.sql
• DsData.sql
• Entity.sql
• Event.sql
• IpAddress.sql
• IpNetwork.sql
• KeyData.sql
• Link.sql
• Nameserver.sql
• PublicId.sql
• RdapAccessRole.sql
• RdapUser.sql
• Remark.sql
• RemarkDescription.sql
• SecureDNS.sql
• Variant.sql
• VCard.sql
• VCardPostalInfo.sql
• Zone.sql

The RedDog SQL files calling catalogs and other needed catalogs: (If overwritten, the implementer MUST make sure all of these are set)

• CountryCode
• EventAction
• IpVersion
• Roles
• Status
• VariantRelation

SQL Files

Autnum.sql

This file loads an Autnum object which models Autonomous System number registrations found in RIRs.

The following table describes each alias and value type that the queries must return as result columns:

The new SQL file must define the same queries and aliases as the RedDog’s implementation do, so both RedDog’s implementation and the own database columns coincide. The queries and aliases must be like META-INF/sql/Autnum.sql.

Domain.sql

This file loads a Domain object that represents a DNS name and point of delegation. For RIRs, these delegation points are in the reverse DNS tree, whereas for DNRs, these delegation points are in the forward DNS tree.

The following table describes each alias and value type that the queries must return as result columns:

The new SQL file must define the same queries and aliases as the RedDog’s implementation do, so both RedDog’s implementation and the own database columns coincide. The queries and aliases must be like META-INF/sql/Domain.sql.

DsData.sql

This file loads a DSData data object. The following table describes each alias and value type that the queries must return as result columns:

The new SQL file must define the same queries and aliases as the RedDog’s implementation do, so both RedDog’s implementation and the own database columns coincide. The queries and aliases must be like META-INF/sql/DsData.sql.

Entity.sql

This file loads an Entity object that represents the information of organizations, corporations, governments, non-profits, clubs, individual persons, and informal groups of people.

The following table describes each alias and value type that the queries must return as result columns:

The new SQL file must define the same queries and aliases as the RedDog’s implementation do, so both RedDog’s implementation and the own database columns coincide. The queries and aliases must be like META-INF/sql/Entity.sql.

Event.sql

This file loads an Event object that represents events that have occurred on an object instance.

The following table describes each alias and value type that the queries must return as result columns:

The new SQL file must define the same queries and aliases as the RedDog’s implementation do, so both RedDog’s implementation and the own database columns coincide. The queries and aliases must be like META-INF/sql/Event.sql.

IpAddress.sql

This file loads the IP addresses of a Nameserver object.

The following table describes each alias and value type that the queries must return as result columns:

The new SQL file must define the same queries and aliases as the RedDog’s implementation do, so both RedDog’s implementation and the own database columns coincide. The queries and aliases must be like META-INF/sql/IpAddress.sql.

IpNetwork.sql

This file loads an IpNetwork object which models IP network registrations found in RIRs and contains information about the network registration and entities related to the IP network.

The following table describes each alias and value type that the queries must return as result columns:

The new SQL file must define the same queries and aliases as the RedDog’s implementation do, so both RedDog’s implementation and the own database columns coincide. The queries and aliases must be like META-INF/sql/IpNetwork.sql.

KeyData.sql

This file loads the Key Data of a Secure DNS object.

The following table describes each alias and value type that the queries must return as result columns:

The new SQL file must define the same queries and aliases as the RedDog’s implementation do, so both RedDog’s implementation and the own database columns coincide. The queries and aliases must be like META-INF/sql/KeyData.sql.

Link.sql

This file loads a Link used by an object.

The following table describes each alias and value type that the queries must return as result columns:

The new SQL file must define the same queries and aliases as the RedDog’s implementation do, so both RedDog’s implementation and the own database columns coincide. The queries and aliases must be like META-INF/sql/Link.sql.

Nameserver.sql

This file loads a Nameserver object which represents information regarding DNS nameservers used in both forward and reverse DNS.

The following table describes each alias and value type that the queries must return as result columns:

The new SQL file must define the same queries and aliases as the RedDog’s implementation do, so both RedDog’s implementation and the own database columns coincide. The queries and aliases must be like META-INF/sql/Nameserver.sql.

PublicId.sql

This file loads the Public ID used by an Entity or Domain object.

The following table describes each alias and value type that the queries must return as result columns:

The new SQL file must define the same queries and aliases as the RedDog’s implementation do, so both RedDog’s implementation and the own database columns coincide. The queries and aliases must be like META-INF/sql/PublicId.sql.

RdapAccessRole.sql

This file loads the Access Roles that a user may have to customize its access level.

The following table describes each alias and value type that the queries must return as result columns:

The new SQL file must define the same queries and aliases as the RedDog’s implementation do, so both RedDog’s implementation and the own database columns coincide. The queries and aliases must be like META-INF/sql/RdapAccessRole.sql.

RdapUser.sql

This file loads a User that can authenticate to the application.

The following table describes each alias and value type that the queries must return as result columns:

The new SQL file must define the same queries and aliases as the RedDog’s implementation do, so both RedDog’s implementation and the own database columns coincide. The queries and aliases must be like META-INF/sql/RdapUser.sql.

Remark.sql

This file loads a Remark that can be related to an object.

The following table describes each alias and value type that the queries must return as result columns:

The new SQL file must define the same queries and aliases as the RedDog’s implementation do, so both RedDog’s implementation and the own database columns coincide. The queries and aliases must be like META-INF/sql/Remark.sql.

RemarkDescription.sql

This file loads the descriptions that a Remark can have.

The following table describes each alias and value type that the queries must return as result columns:

The new SQL file must define the same queries and aliases as the RedDog’s implementation do, so both RedDog’s implementation and the own database columns coincide. The queries and aliases must be like META-INF/sql/RemarkDescription.sql.

SecureDNS.sql

This file loads the general Secure DNS information related to a Domain object.

The following table describes each alias and value type that the queries must return as result columns:

The new SQL file must define the same queries and aliases as the RedDog’s implementation do, so both RedDog’s implementation and the own database columns coincide. The queries and aliases must be like META-INF/sql/SecureDNS.sql.

Variant.sql

This file loads the Variants that a Domain object has, as well as its relation.

The following table describes each alias and value type that the queries must return as result columns:

The new SQL file must define the same queries and aliases as the RedDog’s implementation do, so both RedDog’s implementation and the own database columns coincide. The queries and aliases must be like META-INF/sql/Variant.sql.

VCard.sql

This file loads the VCard object that can be related to another RDAP object.

The following table describes each alias and value type that the queries must return as result columns:

The new SQL file must define the same queries and aliases as the RedDog’s implementation do, so both RedDog’s implementation and the own database columns coincide. The queries and aliases must be like META-INF/sql/VCard.sql.

VCardPostalInfo.sql

This file loads the VCard Postal Info related to a VCard object.

The following table describes each alias and value type that the queries must return as result columns:

The new SQL file must define the same queries and aliases as the RedDog’s implementation do, so both RedDog’s implementation and the own database columns coincide. The queries and aliases must be like META-INF/sql/VCardPostalInfo.sql.

Zone.sql

This file is extremely important for the Domain object as all of the queries need this id to work.

The following table describes each alias and value type that the queries must return as result columns:

The new SQL file must define the same queries and aliases as the RedDog’s implementation do, so both RedDog’s implementation and the own database columns coincide. The queries and aliases must be like META-INF/sql/Zone.sql.

Needed Catalogs

A custom implementation will need to add the following catalogs, as well as its queries so that RedDog server will properly work.

CountryCode

RedDog uses the standard for area codes used by the United Nations Statistics Division.The complete list of country codes can be found here.

This catalog does not have a SQL file but is needed at DB since Autnum.sql and IpNetwork.sql objects retrieve it.

EventAction

The following values have been registered in the “RDAP JSON Values” registry for the EventAction catalog:

This catalog does not have a SQL file but is needed at DB since a Event.sql object retrieves it.

IpVersion

The following values have been registered for the IpVersion catalog:

This catalog does not have a SQL file but is needed at DB since a IpNetwork.sql object retrieves it.

Roles

The following values have been registered in the “RDAP JSON Values” registry for the Roles catalog:

The new SQL file must define the same queries and aliases as the RedDog’s implementation do, so both RedDog’s implementation and the own database columns coincide. The queries and aliases must be like META-INF/sql/Role.sql.

This catalog is needed so an object can have Entities with an associated role.

Status

The following values have been registered in the “RDAP JSON Values” registry for the Status of the RDAP objects:

The new SQL file must define the same queries and aliases as the RedDog’s implementation do, so both RedDog’s implementation and the own database columns coincide. The queries and aliases must be like META-INF/sql/Status.sql.

This catalog is needed since the following objects can retrieve it: Autnum.sql, Domain.sql, Entity.sql, IpNetwork.sql, and Nameserver.sql.

VariantRelation

The following values have been registered in the “RDAP JSON Values” registry for VariantRelations catalog:

This catalog does not have a SQL file but is needed at DB since a Variant.sql object retrieves it.

Additional Notes

• All necessary values for the RDAP catalogs can be found here.
• All the provided queries are those implemented by RedDog, if they are overwritten they don´t have to be the same, they only need to return the same type of data.

Where to go next