Ferreteria/v0.5/sql/node: Difference between revisions
< Ferreteria | v0.5 | sql
Jump to navigation
Jump to search
No edit summary |
No edit summary |
||
Line 21: | Line 21: | ||
`Type` VARCHAR(255) DEFAULT NULL COMMENT "name of node type", | `Type` VARCHAR(255) DEFAULT NULL COMMENT "name of node type", | ||
`Name` VARCHAR(255) NOT NULL COMMENT "field name of this value", | `Name` VARCHAR(255) NOT NULL COMMENT "field name of this value", | ||
`Value` | `Value` LONGTEXT DEFAULT NULL COMMENT "value, JSON-encoded", | ||
`WhenMade` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, | `WhenMade` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, | ||
`WhenEdit` DATETIME DEFAULT NULL COMMENT "when last modified", | `WhenEdit` DATETIME DEFAULT NULL COMMENT "when last modified", | ||
Line 29: | Line 29: | ||
==History== | ==History== | ||
{| class="wikitable sortable" | |||
! date || what | |||
|- | |||
| '''2017-03-12''' || node started | |||
|- | |||
| '''2017-04-09''' || node: decided there really should be a WhenMade timestamp; can always remove later | |||
|- | |||
| '''2017-08-05''' || node: renamed '''Class''' to '''Type''' for consistency with tf_leaf: records use "types", code maps these to "classes" | |||
|- | |||
| '''2017-08-03''' || node_leaf started, because the need became apparent. | |||
|- | |||
| '''2020-01-16''' || redesigning for v0.4 (copied from [[Ferreteria/sql/tf node]] / [[Ferreteria/sql/tf leaf]]) - this became 0.41 | |||
|- | |||
| '''2020-01-23''' || | |||
* node: copying from v0.41 to v0.42 essentially unaltered | |||
* node_leaf: tweaking for v0.42 | |||
|- | |||
| '''2020-05-26''' || node: decided to remove NOT NULL from Type user can create new Nodes without any input. | |||
* Strongly inclined to make Type a pseudofield (Leaf), in any case. | |||
|- | |||
| '''2021-01-19''' || | |||
* node: | |||
** "integer width is deprecated"; also decided it should be 8 bytes | |||
** added ID_Mom because it seems necessary to be able to support sub-nodes | |||
* node_leaf: | |||
** "integer width is deprecated"; also decided it should be 8 bytes | |||
** Turns out LONGTEXT [https://stackoverflow.com/questions/10957238/incorrect-string-value-when-trying-to-insert-utf-8-into-mysql-via-jdbc barfs at certain kinds of binary data], so changing to LONGBLOB. | |||
|- | |||
| '''2022-03-06''' || | |||
* copying {{l/ferreteria|v0.42/sql/node|v0.42}} and {{l/ferreteria|v0.42/sql/node_leaf|v0.42}} verbatim for v0.5 | |||
* node_leaf: "Type" is in the documentation but not the table design. I'm thinking that it should be defined by the code, which will determine the data format based on registered field names. When we want to implement searching, there can be a separate table for indexing individual field-types. | |||
|- | |||
| '''2022-03-14''' || Decided to consolidate {{l/same|node leaf}} into node for ease of coding, proof-of-concept. Can design more optimized structures/code later, if it seems worth the trouble. | |||
|- | |||
| '''2022-12-12''' || Decided to use JSON for Value field (changing type back to LONGTEXT), since that preserves type while allowing easier data inspection. "Optimize later" is now my mantra... | |||
|} |
Latest revision as of 01:59, 13 December 2022
About
- table purpose: root for all data
- record purpose: a thing that has values
- Module: node
Fields
- ID: unique identifier for every node in the current nodeset
- You can, at least in theory, have more than one nodeset per db by putting them in different tables.
- ID_Mom: ID of parent node, if any.
- Type: name of node type; code should know how to map these to node-handler classes
- Not actually sure yet how this will be used.
- 2022-12-12 Let's try putting the SpecSlug in here. That should at least enable lookup-class-from-node.
- Name: name of this node, unique within all nodes of the same parent. Equivalent to field name.
- Value: node value, if any
- WhenMade: (should be nonessential) when this Node was first created
- WhenEdit: (should be nonessential) when this Node was last modified
SQL
CREATE TABLE `node` (
`ID` BIGINT NOT NULL AUTO_INCREMENT,
`ID_Mom` BIGINT DEFAULT NULL COMMENT "ID of parent node, if any",
`Type` VARCHAR(255) DEFAULT NULL COMMENT "name of node type",
`Name` VARCHAR(255) NOT NULL COMMENT "field name of this value",
`Value` LONGTEXT DEFAULT NULL COMMENT "value, JSON-encoded",
`WhenMade` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`WhenEdit` DATETIME DEFAULT NULL COMMENT "when last modified",
PRIMARY KEY(`ID`),
UNIQUE KEY(`Name`,`ID_Mom`)
) ENGINE=InnoDB;
History
date | what |
---|---|
2017-03-12 | node started |
2017-04-09 | node: decided there really should be a WhenMade timestamp; can always remove later |
2017-08-05 | node: renamed Class to Type for consistency with tf_leaf: records use "types", code maps these to "classes" |
2017-08-03 | node_leaf started, because the need became apparent. |
2020-01-16 | redesigning for v0.4 (copied from Ferreteria/sql/tf node / Ferreteria/sql/tf leaf) - this became 0.41 |
2020-01-23 |
|
2020-05-26 | node: decided to remove NOT NULL from Type user can create new Nodes without any input.
|
2021-01-19 |
|
2022-03-06 |
|
2022-03-14 | Decided to consolidate node leaf into node for ease of coding, proof-of-concept. Can design more optimized structures/code later, if it seems worth the trouble. |
2022-12-12 | Decided to use JSON for Value field (changing type back to LONGTEXT), since that preserves type while allowing easier data inspection. "Optimize later" is now my mantra... |