Difference between revisions of "Db-string-type-cleanup"

Revision as of 16:15, 23 October 2013

Blueprint Name: db-string-type-cleanup

Summary

Rationale

We use string columns to store a variety of logical datatypes: UUIDs, IP addresses, generic strings, among others.

The current practice is for developers to use SQLAlchemy's `String` datatype directly and specify each time the desired length, e.g. String(255), or String(36).

This has a few problems:

Typos. For example, https://review.openstack.org/#/c/39296 accidentally changed '255' to '25'
Unecessary Variance For example, we have 'String(256)' in places where String(255) was preferable (since that's the de facto standard for generic strings)
No Physical Storage/Logical Datatype Abstraction Some databases support certain logical datatypes natively, like UUID. By declaring the columns as strings, instead of say 'UUID', we're tying that column down to a particular underlying storage type. (In fact for IP Addresses and CIDR addresses, we're already using this abstraction.)

Count	Type	Notes
2	String(12)	PCI Address
2	String(5)	Protocol
2	String(8)	PCI Device Type
4	String(4)	PCI Vendor/Product IDs
5	String(39)	IPAddress
7	String(256)	Typos?
8	String(43)	CIDR
56	String(36)	UUID
388	String(255)	Generic String

@@ Line 5: / Line 5: @@
 == Rationale ==
-We use String columns as the physical storage for many kinds of logical datatypes, whether it's a UUID as String(36), IPAddress as String(39), or generic strings which are usually String(255).
+We use string columns to store a variety of logical datatypes: UUIDs, IP addresses, generic strings, among others.
+The current practice is for developers to use SQLAlchemy's `String` datatype directly and specify each time the desired length, e.g. String(255), or String(36).
+This has a few problems:
+* '''Typos'''. For example, https://review.openstack.org/#/c/39296 accidentally changed '255' to '25'
+* '''Unecessary Variance''' For example, we have 'String(256)' in places where String(255) was preferable (since that's the de facto standard for generic strings)
+*'''No Physical Storage/Logical Datatype Abstraction''' Some databases support certain logical datatypes natively, like UUID.  By declaring the columns as strings, instead of say 'UUID', we're tying that column down to a particular underlying storage type. (In fact for IP Addresses and CIDR addresses, we're already using this abstraction.)
 {| class="wikitable"
@@ Line 30: / Line 38: @@
 | 388 || String(255) || Generic String
 |}
-Right now, the standard is for developers to use SQLAlchemy's String type directly, where they pass in the desired length.
-There are a few problems with this approach:
-. UNSAFE: Each time a developer types '255', there is an opportunity to introduce a bug. Two examples of this are the 7 256-length columns we have and https://review.openstack.org/#/c/39296/ where '255' was accidentally changed to '25' without reviewers noticing.
-What makes this issue particularly nefarious, is that these kinds of column-length bugs usually do not surface until after a deployment when we start using real data, with all of its natural variance. This makes recovering from these bugs much harder than normal, since additional, carefully-crafted migrations need to be written to fix the existing bad data. As such, it would be nice to turn these runtime bugs into things the interpreter can catch at parse-time.
 == Design ==