Overview
Gerrit Patch |
[]
|
Launchpad Blueprint |
[1]
|
This blueprint proposes to subdivide the Records table, which includes all record types, into a table per record type. For instance, an "a_records" table for A records, "mx_records" table for MX records, and so on.
At this time, the migration of data from the existing Records table to the new separate tables is not included in this specification document.
Database Schema Changes - WIP
The existing Records table would 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
Database Schema
There are several changes being done to the database. Even though these changes will be done by a separate blueprint, I'm describing them here so all the changes can be seen in one place. I think that will make it easier to understand them.
The RecordSet table will be divided up into a table per record type. The RecordSets table for each type will combine most of the data currently in the recordsets and records tables. The Record table associated with a type will only contain the RecordSet id and data associated with that recordset.
Questions:
1. How does "version" get set?
2. In the current RecordSets and Records table is says that created_at can be null. Is that accurate?
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
|
'Question'
1. Should it be VARCHAR 15 or INT 4 bytes?
And So On
There would be a recordset and record table for each record type. The record table would be customized for each record type's data.
A_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 |
IPv4 address
|
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
|
AAAA_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 |
IPv6 address
|
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
|
MX_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 |
Mail Server
|
priority |
INTEGER |
11 |
False |
Priority of MX 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
|
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
|