Ferreteria/v0.5/sql/node: Difference between revisions

From Woozle Writes Code
< Ferreteria‎ | v0.5‎ | sql
Jump to navigation Jump to search
No edit summary
Line 4: Line 4:
* '''record purpose''': a thing that has values
* '''record purpose''': a thing that has values
* '''Module''': {{l/version|node}}
* '''Module''': {{l/version|node}}
==Concept==
Every '''node'''
* is a record in the node table
* can have zero or more '''leaf'''s
** There's generally no point to having zero, but no reason to forbid it.
* can have a (node) '''type''' which points to a handler-class
** so that when we're looking at a group of unspecified nodes, we know how to handle each one
==Fields==
==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
* '''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==
==SQL==
<syntaxhighlight lang=mysql>CREATE TABLE `node` (
<syntaxhighlight lang=mysql>CREATE TABLE `node` (
Line 21: Line 22:
   `Value`    LONGBLOB        DEFAULT NULL COMMENT "value",
   `Value`    LONGBLOB        DEFAULT NULL COMMENT "value",
   `WhenMade` DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `WhenMade` DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `WhenEdit` DATETIME        DEFAULT NULL COMMENT "when last modified",
   PRIMARY KEY(`ID`)
   PRIMARY KEY(`ID`)
   UNIQUE KEY(`ID_Node`,`ID_Mom`)
   UNIQUE KEY(`ID_Node`,`ID_Mom`)

Revision as of 23:27, 14 March 2022

versions: 0.41, 0.42, 0.5

About

  • table purpose: root for all data
  • record purpose: a thing that has values
  • Module: Template:L/version

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(`ID_Node`,`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:
  • 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.