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.
• 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`    LONGBLOB         DEFAULT NULL COMMENT "value",
  `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

• 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.