Jump to: navigation, search

Designate/Blueprints/Records Table Redesign

< Designate‎ | Blueprints
Revision as of 21:40, 20 May 2014 by Betsy (talk | contribs) (→‎Database Schema Changes - WIP)

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

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

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
Retrieved from "https://wiki.openstack.org/w/index.php?title=Designate/Blueprints/Records_Table_Redesign&oldid=52862"