Jump to: navigation, search

Designate/Blueprints/Records Table Redesign

< Designate‎ | Blueprints
Revision as of 16:51, 12 June 2014 by Betsy (talk | contribs) (TXT_Records)

Overview

Gerrit Patch []
Launchpad Blueprint [1]

Currently, there is only one RecordSets table and one Records table for all the different record types. This blueprint proposes to subdivide those tables into a RecordSet table and Record table per record type.

Having one monolithic RecordSets table and Records table for all record types can become problematic as the size of the table grows. If any modifications need to be made, for example, adding a column, the entire table would have to be locked and it could take a long time to update a table with millions of records. In addition, if a table gets too large, it has to be sharded, which involves writing a lot of code to know which shard to call for which records. On the other hand, joins across tables are not a problem, as long as the indexing is done correctly. In addition, having a table per record type is a proven DNS database design as some companies have already implemented it in this fashion and have millions of records.

Most of the information that is currently in the Records table will be consolidated into the RecordSet table. The Records table for each type will only contain the recordset id and the unique data for that particular record type. For instance, the A_Record table will contain the recordset id and the IPv4 address. The MX_Record table will contain the recordset id and the FQDN for the mail server and its preference value. See the full specification for more detail.

Database Schema Changes

The existing Recordset table and Records table will become obsolete and be replaced with new tables for each record type.

Existing Tables

Recordsets Table

Name Data Type Length Nullable Details
id CHAR 32 False UUID, Primary Key
created_at datetime - True Time of creation
updated_at datetime - True Time of last update
version INT 11 False Designate API version
tenant_id VARCHAR 36 True ID of recordset owner
domain_id CHAR 32 False Non-unique Key
name VARCHAR 255 False Recordset name
type ENUM - False A, AAAA, CNAME, MX, SRV, TXT, SPF, NS, PTR, SSHFP
ttl INT 11 True time to live
description VARCHAR 160 True Description of Recordset

Records Table

Name Data Type Length Nullable Details
id CHAR 32 False Primary Key, Generated UUID
created_at DATETIME - True UTC time of creation
updated_at DATETIME - True UTC time of last update
version INTEGER 11 False Designate API version
data MEDIUMTEXT - False Data unique to a specific record
priority INTEGER 11 True Priority value for certain types of records
domain_id CHAR 32 False Non-unique Key, Domain ID
managed TINYINT 1 True Boolean value indicates a managed resource
managed_resource_type VARCHAR 50 True Managed resource type
managed_resource_id CHAR 32 True Managed resource id
managed_plugin_name VARCHAR 50 True Managed plugin name
managed_plugin_type VARCHAR 50 True Managed plugin type
hash VARCHAR 32 False Unique Key, Hash value of record
description VARCHAR 160 True Description of record
status ENUM("Active", "Pending", "Deleted") - False Record status
tenant_id VARCHAR 36 True ID of record owner
recordset_id CHAR 32 False Non-unique Key, ID of recordset
managed_tenant_id CHAR 32 True ID of managed owner
managed_resource_region VARCHAR 100 True Region of managed
managed_extra VARCHAR 100 True Extra field

New Tables

RecordSets

Name Data Type Length Nullable Details
id CHAR 32 False Primary Key, Generated UUID
created_at DATETIME - False UTC time of creation
updated_at DATETIME - True UTC time of last update
version INTEGER 11 False Designate API version
tenant_id CHAR 36 True The tenant_id to which the record belongs
domain_id CHAR 32 False Non-Unique Key; the domain_id to which the record belongs
name VARCHAR 255 False The zone to which the A record belongs
ttl INTEGER 11 True The time-to-live assigned to the record
description VARCHAR 160 True A description of the record
status ENUM 'Active', 'Pending', 'Deleted' False Current status of RecordSet
hash VARCHAR 32 False Unique Key
managed TINYINT 1 True Indicates whether it is managed or not
managed_resource_type VARCHAR 50 True
managed_resource_id CHAR 32 True
managed_plugin_name VARCHAR 50 True
managed_plugin_type VARCHAR 50 True
managed_tenant_id VARCHAR 36 True
managed_resource_region VARCHAR 100 True
managed_extra VARCHAR 100 True

A_Records

Name Data Type Length Nullable Details
id CHAR 32 False UUID, Unique Key
recoredset_id CHAR 32 False Non-unique Key
address VARCHAR 15 False iPv4

AAAA_Records

Name Data Type Length Nullable Details
id CHAR 32 False UUID, Unique Key
recoredset_id CHAR 32 False Non-unique Key
address BINARY 16 False iPv6 address

CNAME_Records

Name Data Type Length Nullable Details
id CHAR 32 False UUID, Unique Key
recoredset_id CHAR 32 False Non-unique Key
cname VARCHAR 255 False CNAME

MX_Records

Name Data Type Length Nullable Details
id CHAR 32 False UUID, Unique Key
recoredset_id CHAR 32 False Non-unique Key
exchange VARCHAR 255 False Hostname
preference INT 11 False Lowest number has highest priority

NS_Records

Name Data Type Length Nullable Details
id CHAR 32 False UUID, Unique Key
recoredset_id CHAR 32 False Non-unique Key
nsdname VARCHAR 255 False Hostname

PTR_Records

Name Data Type Length Nullable Details
id CHAR 32 False UUID, Unique Key
recoredset_id CHAR 32 False Non-unique Key
ptrdname VARCHAR 255 False Hostname

SOA_Records

Name Data Type Length Nullable Details
id CHAR 32 False UUID, Unique Key
recoredset_id CHAR 32 False Non-unique Key
mname VARCHAR 255 False Primary name server
rname VARCHAR 255 False Domain name that indicates the email address
serial INT 11 False Serial number
refresh INT 11 False Number of seconds between data refresh
retry INT 11 False Number of seconds between retries
expire INT 11 False Number of seconds before information is no longer considered authoritative
minimum INT 11 False Number of seconds that the records in the zone are valid

SRV_Records

Name Data Type Length Nullable Details
id CHAR 32 False UUID, Unique Key
recoredset_id CHAR 32 False Non-unique Key
target VARCHAR 255 False Name of the service
priority INT 11 False Priority of the service
weight INT 11 False Weight of the service
port INT 11 False Port of the service

TXT_Records

Name Data Type Length Nullable Details
id CHAR 32 False UUID, Unique Key
recoredset_id CHAR 32 False Non-unique Key
text VARCHAR 255 False Text