Add timestamps to Nodes, PCUs and Interfaces to make concrete

statements about a node's configuration state.

Added:
    Node.last_boot             -- updated by BootUpdateNode()
    Node.last_download         -- updated by GetBootMedium()
    Node.last_pcu_reboot       -- updated by RebootNodeWithPCU()
    Node.last_pcu_confirmation -- updated by BootUpdateNode()
    Interface.last_updated     -- updated by UpdateInterface()
    PCU.last_updated           -- updated by UpdatePCU()

With these timestamps we can implement isValid() routines that indicate
whether the DB state is consistent with respect to the external deployment.
Previously, the DB state could be inconsistent, but there was no way to
identify this until runtime.  These checks should make it possible to validate
the DB state prior to runtime.

Also, isValid() is different from whether a node is online.  A node can be
offline and have a valid DB state.

For instance (in pseudocode):

    def Node.isValid():
        return Node.last_download < Node.last_boot &&
                Node.PCU.isValid(Node) &&
                Node.Interface.isValid(Node)

    def PCU.isValid(Node):
        return PCU.last_updated < Node.last_pcu_confirmation

    def Interface.isValid(Node):
        return Interface.last_updated < Node.last_download

In other words, if the primary interface for a node is updated more recently
than a bootimage is downloaded, then the DB configuration is out of sync with
the node deployment.

If the PCU is updated more recently than the last confirmed use of the PCU,
then the DB configuration is potentially out of sync with the deployment.

If the Node has downloaded a boot image prior to rebooting, then the DB config
is potentially out of sync with the deployment.

All of these states can be flagged in the GUI to help a technical contact
known the status of his configuration and deployment more easily.  The
recommended steps to take should be straight forward based on the conditions
above.  i.e. Create new boot image, reboot node, verify PCU, etc.

diff --git a/migrations/102-down-isvalid.sql b/migrations/102-down-isvalid.sql
new file mode 100644 (file)
index 0000000..e147fd1
--- /dev/null
+++ b/migrations/102-down-isvalid.sql
@@ -0,0 +1,88 @@
+ALTER TABLE nodes DROP COLUMN last_download; 
+ALTER TABLE nodes DROP COLUMN last_pcu_reboot; 
+ALTER TABLE nodes DROP COLUMN last_pcu_confirmation;
+
+ALTER TABLE pcus DROP COLUMN last_updated timestamp without time zone;
+
+ALTER TABLE interfaces DROP COLUMN last_updated timestamp without time zone;
+
+DROP VIEW view_nodes;
+CREATE OR REPLACE VIEW view_nodes AS
+SELECT
+nodes.node_id,
+nodes.node_type,
+nodes.hostname,
+nodes.site_id,
+nodes.boot_state,
+nodes.run_level,
+nodes.deleted,
+nodes.model,
+nodes.boot_nonce,
+nodes.version,
+nodes.verified,
+nodes.ssh_rsa_key,
+nodes.key,
+CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created,
+CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated,
+CAST(date_part('epoch', nodes.last_contact) AS bigint) AS last_contact,  
+peer_node.peer_id,
+peer_node.peer_node_id,
+COALESCE((SELECT interface_ids FROM node_interfaces 
+                WHERE node_interfaces.node_id = nodes.node_id), '{}') 
+AS interface_ids,
+COALESCE((SELECT nodegroup_ids FROM node_nodegroups 
+                WHERE node_nodegroups.node_id = nodes.node_id), '{}') 
+AS nodegroup_ids,
+COALESCE((SELECT slice_ids FROM node_slices 
+                WHERE node_slices.node_id = nodes.node_id), '{}') 
+AS slice_ids,
+COALESCE((SELECT slice_ids_whitelist FROM node_slices_whitelist 
+                WHERE node_slices_whitelist.node_id = nodes.node_id), '{}') 
+AS slice_ids_whitelist,
+COALESCE((SELECT pcu_ids FROM node_pcus 
+                WHERE node_pcus.node_id = nodes.node_id), '{}') 
+AS pcu_ids,
+COALESCE((SELECT ports FROM node_pcus
+                WHERE node_pcus.node_id = nodes.node_id), '{}') 
+AS ports,
+COALESCE((SELECT conf_file_ids FROM node_conf_files
+                WHERE node_conf_files.node_id = nodes.node_id), '{}') 
+AS conf_file_ids,
+COALESCE((SELECT node_tag_ids FROM node_tags 
+                WHERE node_tags.node_id = nodes.node_id), '{}') 
+AS node_tag_ids,
+node_session.session_id AS session
+FROM nodes
+LEFT JOIN peer_node USING (node_id)
+LEFT JOIN node_session USING (node_id);
+
+DROP VIEW view_pcus;
+CREATE OR REPLACE VIEW view_pcus AS
+SELECT
+pcus.*,
+COALESCE((SELECT node_ids FROM pcu_nodes WHERE pcu_nodes.pcu_id = pcus.pcu_id), '{}') AS node_ids,
+COALESCE((SELECT ports FROM pcu_nodes WHERE pcu_nodes.pcu_id = pcus.pcu_id), '{}') AS ports
+FROM pcus;
+
+
+DROP VIEW view_interfaces;
+CREATE OR REPLACE VIEW view_interfaces AS
+SELECT
+interfaces.interface_id,
+interfaces.node_id,
+interfaces.is_primary,
+interfaces.type,
+interfaces.method,
+interfaces.ip,
+interfaces.mac,
+interfaces.gateway,
+interfaces.network,
+interfaces.broadcast,
+interfaces.netmask,
+interfaces.dns1,
+interfaces.dns2,
+interfaces.bwlimit,
+interfaces.hostname,
+COALESCE((SELECT interface_tag_ids FROM interface_tags WHERE interface_tags.interface_id = interfaces.interface_id), '{}') AS interface_tag_ids
+FROM interfaces;
+

diff --git a/migrations/102-up-isvalid.sql b/migrations/102-up-isvalid.sql
new file mode 100644 (file)
index 0000000..c1bd5c2
--- /dev/null
+++ b/migrations/102-up-isvalid.sql
@@ -0,0 +1,106 @@
+ALTER TABLE nodes ADD COLUMN last_boot timestamp without time zone;
+ALTER TABLE nodes ADD COLUMN last_download timestamp without time zone;
+ALTER TABLE nodes ADD COLUMN last_pcu_reboot timestamp without time zone;
+ALTER TABLE nodes ADD COLUMN last_pcu_confirmation timestamp without time zone;
+
+ALTER TABLE pcus ADD COLUMN last_updated timestamp without time zone;
+
+ALTER TABLE interfaces ADD COLUMN last_updated timestamp without time zone;
+
+DROP VIEW view_nodes;
+CREATE OR REPLACE VIEW view_nodes AS
+SELECT
+nodes.node_id,
+nodes.node_type,
+nodes.hostname,
+nodes.site_id,
+nodes.boot_state,
+nodes.run_level,
+nodes.deleted,
+nodes.model,
+nodes.boot_nonce,
+nodes.version,
+nodes.verified,
+nodes.ssh_rsa_key,
+nodes.key,
+CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created,
+CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated,
+CAST(date_part('epoch', nodes.last_contact) AS bigint) AS last_contact,  
+CAST(date_part('epoch', nodes.last_boot) AS bigint) AS last_boot,  
+CAST(date_part('epoch', nodes.last_download) AS bigint) AS last_download,  
+CAST(date_part('epoch', nodes.last_pcu_reboot) AS bigint) AS last_pcu_reboot,  
+CAST(date_part('epoch', nodes.last_pcu_confirmation) AS bigint) AS last_pcu_confirmation,  
+peer_node.peer_id,
+peer_node.peer_node_id,
+COALESCE((SELECT interface_ids FROM node_interfaces 
+                WHERE node_interfaces.node_id = nodes.node_id), '{}') 
+AS interface_ids,
+COALESCE((SELECT nodegroup_ids FROM node_nodegroups 
+                WHERE node_nodegroups.node_id = nodes.node_id), '{}') 
+AS nodegroup_ids,
+COALESCE((SELECT slice_ids FROM node_slices 
+                WHERE node_slices.node_id = nodes.node_id), '{}') 
+AS slice_ids,
+COALESCE((SELECT slice_ids_whitelist FROM node_slices_whitelist 
+                WHERE node_slices_whitelist.node_id = nodes.node_id), '{}') 
+AS slice_ids_whitelist,
+COALESCE((SELECT pcu_ids FROM node_pcus 
+                WHERE node_pcus.node_id = nodes.node_id), '{}') 
+AS pcu_ids,
+COALESCE((SELECT ports FROM node_pcus
+                WHERE node_pcus.node_id = nodes.node_id), '{}') 
+AS ports,
+COALESCE((SELECT conf_file_ids FROM node_conf_files
+                WHERE node_conf_files.node_id = nodes.node_id), '{}') 
+AS conf_file_ids,
+COALESCE((SELECT node_tag_ids FROM node_tags 
+                WHERE node_tags.node_id = nodes.node_id), '{}') 
+AS node_tag_ids,
+node_session.session_id AS session
+FROM nodes
+LEFT JOIN peer_node USING (node_id)
+LEFT JOIN node_session USING (node_id);
+
+--------------------------------------------------------------------------------
+DROP VIEW view_pcus;
+CREATE OR REPLACE VIEW view_pcus AS
+SELECT
+pcus.pcu_id,
+pcus.site_id,
+pcus.hostname,
+pcus.ip,
+pcus.protocol,
+pcus.username,
+pcus.password,
+pcus.model,
+pcus.notes,
+CAST(date_part('epoch', pcus.last_updated) AS bigint) AS last_updated,
+COALESCE((SELECT node_ids FROM pcu_nodes WHERE pcu_nodes.pcu_id = pcus.pcu_id), '{}') AS node_ids,
+COALESCE((SELECT ports FROM pcu_nodes WHERE pcu_nodes.pcu_id = pcus.pcu_id), '{}') AS ports
+FROM pcus;
+
+
+DROP VIEW view_interfaces;
+CREATE OR REPLACE VIEW view_interfaces AS
+SELECT
+interfaces.interface_id,
+interfaces.node_id,
+interfaces.is_primary,
+interfaces.type,
+interfaces.method,
+interfaces.ip,
+interfaces.mac,
+interfaces.gateway,
+interfaces.network,
+interfaces.broadcast,
+interfaces.netmask,
+interfaces.dns1,
+interfaces.dns2,
+interfaces.bwlimit,
+interfaces.hostname,
+CAST(date_part('epoch', interfaces.last_updated) AS bigint) AS last_updated,
+COALESCE((SELECT interface_tag_ids FROM interface_tags WHERE interface_tags.interface_id = interfaces.interface_id), '{}') AS interface_tag_ids
+FROM interfaces;
+
+
+UPDATE plc_db_version SET subversion = 102;