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 11: | Line 11: | ||
* can have a (node) '''type''' which points to a handler-class | * 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 | ** so that when we're looking at a group of unspecified nodes, we know how to handle each one | ||
==Fields== | ==Fields== | ||
* '''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 | ||
==SQL== | ==SQL== | ||
<syntaxhighlight lang=mysql>CREATE TABLE `node` ( | <syntaxhighlight lang=mysql>CREATE TABLE `node` ( | ||
`ID` BIGINT | `ID` BIGINT NOT NULL AUTO_INCREMENT, | ||
`ID_Mom` BIGINT | `ID_Mom` BIGINT DEFAULT NULL COMMENT "ID of parent node, if any", | ||
`Type` VARCHAR(255) DEFAULT NULL COMMENT "name of node type", | `Type` VARCHAR(255) DEFAULT NULL COMMENT "name of node type", | ||
`WhenMade` DATETIME | `Name` VARCHAR(255) NOT NULL COMMENT "field name of this value", | ||
`Value` LONGBLOB DEFAULT NULL COMMENT "value", | |||
`WhenMade` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, | |||
PRIMARY KEY(`ID`) | PRIMARY KEY(`ID`) | ||
UNIQUE KEY(`ID_Node`,`ID_Mom`) | |||
) ENGINE=InnoDB;</syntaxhighlight> | ) ENGINE=InnoDB;</syntaxhighlight> | ||
==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 [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. | |||
Revision as of 21:56, 14 March 2022
About
- table purpose: root for all data
- record purpose: a thing that has values
- Module: Template:L/version
Concept
Every node
- is a record in the node table
- can have zero or more leafs
- 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
- Type: name of node type; code should know how to map these to node-handler classes
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,
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:
- "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.
- node:
- 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.