Changeset View
Changeset View
Standalone View
Standalone View
resources/sql/autopatches/20140902.almanacdevice.1.sql
- This file was added.
CREATE TABLE {$NAMESPACE}_almanac.almanac_device ( | |||||
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, | |||||
phid VARBINARY(64) NOT NULL, | |||||
name VARCHAR(255) NOT NULL COLLATE {$COLLATE_TEXT}, | |||||
dateCreated INT UNSIGNED NOT NULL, | |||||
epriestley: There are some changes after T1191:
Instead of `utf8_bin`, use `{$COLLATE_TEXT}`.
Instead of… | |||||
dateModified INT UNSIGNED NOT NULL, | |||||
UNIQUE KEY `key_phid` (phid) | |||||
) ENGINE=InnoDB, COLLATE {$COLLATE_TEXT}; | |||||
Not Done Inline ActionsThis should be UNIQUE KEY ... epriestley: This should be `UNIQUE KEY ...` | |||||
CREATE TABLE {$NAMESPACE}_almanac.almanac_deviceproperty ( | |||||
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, | |||||
devicePHID VARBINARY(64) NOT NULL, | |||||
`key` VARCHAR(128) NOT NULL COLLATE {$COLLATE_TEXT}, | |||||
Not Done Inline ActionsThis should be VARBINARY(64) now. epriestley: This should be VARBINARY(64) now. | |||||
value LONGTEXT NOT NULL, | |||||
dateCreated INT UNSIGNED NOT NULL, | |||||
dateModified INT UNSIGNED NOT NULL, | |||||
KEY `key_device` (devicePHID, `key`) | |||||
) ENGINE=InnoDB, COLLATE {$COLLATE_TEXT}; | |||||
Not Done Inline ActionsThis should probably be (devicePHID, key) so we can look up specific properties of a device. epriestley: This should probably be `(devicePHID, key)` so we can look up specific properties of a device. |
There are some changes after T1191:
Instead of utf8_bin, use {$COLLATE_TEXT}.
Instead of utf8_general_ci, use {$COLLATE_TEXT}, unless the column needs case insensitivity (e.g., a unique key on something like a project name). For these, use {$COLLATE_SORT}.
These variables are similar to {$NAMESPACE}.
Otherwise, the phid column is now VARBINARY(64).
Config > Database Status should walk you through most of this: your table should be fully green after the patch runs. If there are warnings, fix the patch rather than letting storage adjust fix them.
This stuff has zero documentation yet so yell if you get into trouble.