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
A_RecordSet
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 |
VARCHAR |
36 |
True |
The tenant_id to which the record belongs
|
domain_id |
VARCHAR |
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_Record
Name |
Data Type |
Length |
Nullable |
Details
|
recordset_id |
VARCHAR |
32 |
False |
UUID, Unique Key
|
record |
VARCHAR |
15 |
False |
iPv4
|
CNAME_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 |
The Canonical Name
|
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
|
TXT_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 |
Text associated with record
|
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
|
SRV_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 |
Weight, Port, Target
|
priority |
INTEGER |
11 |
False |
Priority of SRV record
|
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
|