Ferreteria/v0.5/sql/node

versions: 0.41, 0.42, 0.5

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	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 barfs at certain kinds of binary data, so changing to LONGBLOB.
2022-03-06	copying v0.42/sql/node and v0.42/sql/node_leaf 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 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...