schema.diff

--- ha-schema-from-pypi.sql	2023-03-01 11:36:09.268122333 -0500
+++ ha-schema-from-ha.sql	2023-03-01 11:39:51.837005549 -0500
@@ -1,6 +1,6 @@
 -- MariaDB dump 10.19  Distrib 10.5.18-MariaDB, for debian-linux-gnu (x86_64)
 --
--- Host: 172.30.33.2    Database: homeassistant
+-- Host: 172.30.33.2    Database: hatest
 -- ------------------------------------------------------
 -- Server version	10.6.10-MariaDB

@@ -25,10 +25,10 @@
 CREATE TABLE `event_data` (
   `data_id` int(11) NOT NULL AUTO_INCREMENT,
   `hash` bigint(20) DEFAULT NULL,
-  `shared_data` text DEFAULT NULL,
+  `shared_data` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`data_id`),
   KEY `ix_event_data_hash` (`hash`)
-) ENGINE=InnoDB AUTO_INCREMENT=247384 DEFAULT CHARSET=utf8mb4;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;

 --
@@ -40,23 +40,23 @@
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `events` (
   `event_id` int(11) NOT NULL AUTO_INCREMENT,
-  `event_type` varchar(64) DEFAULT NULL,
-  `event_data` text DEFAULT NULL,
-  `origin` varchar(32) DEFAULT NULL,
-  `time_fired` datetime DEFAULT NULL,
-  `created` datetime DEFAULT NULL,
-  `context_id` varchar(36) DEFAULT NULL,
-  `context_user_id` varchar(36) DEFAULT NULL,
-  `context_parent_id` varchar(36) DEFAULT NULL,
+  `event_type` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+  `event_data` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+  `origin` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+  `origin_idx` smallint(6) DEFAULT NULL,
+  `time_fired` datetime(6) DEFAULT NULL,
+  `time_fired_ts` double DEFAULT NULL,
+  `context_id` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+  `context_user_id` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+  `context_parent_id` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `data_id` int(11) DEFAULT NULL,
-  `origin_idx` int(11) DEFAULT NULL,
-  `time_fired_ts` float DEFAULT NULL,
   PRIMARY KEY (`event_id`),
-  KEY `ix_events_event_type_time_fired_ts` (`event_type`,`time_fired_ts`),
   KEY `ix_events_time_fired_ts` (`time_fired_ts`),
   KEY `ix_events_data_id` (`data_id`),
-  KEY `ix_events_context_id` (`context_id`)
-) ENGINE=InnoDB AUTO_INCREMENT=206829608 DEFAULT CHARSET=utf8mb4;
+  KEY `ix_events_context_id` (`context_id`),
+  KEY `ix_events_event_type_time_fired_ts` (`event_type`,`time_fired_ts`),
+  CONSTRAINT `events_ibfk_1` FOREIGN KEY (`data_id`) REFERENCES `event_data` (`data_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;

 --
@@ -68,13 +68,13 @@
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `recorder_runs` (
   `run_id` int(11) NOT NULL AUTO_INCREMENT,
-  `start` datetime DEFAULT NULL,
-  `end` datetime DEFAULT NULL,
+  `start` datetime(6) DEFAULT NULL,
+  `end` datetime(6) DEFAULT NULL,
   `closed_incorrect` tinyint(1) DEFAULT NULL,
-  `created` datetime DEFAULT NULL,
+  `created` datetime(6) DEFAULT NULL,
   PRIMARY KEY (`run_id`),
   KEY `ix_recorder_runs_start_end` (`start`,`end`)
-) ENGINE=InnoDB AUTO_INCREMENT=350 DEFAULT CHARSET=utf8mb4;
+) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;

 --
@@ -87,9 +87,9 @@
 CREATE TABLE `schema_changes` (
   `change_id` int(11) NOT NULL AUTO_INCREMENT,
   `schema_version` int(11) DEFAULT NULL,
-  `changed` datetime DEFAULT NULL,
+  `changed` datetime(6) DEFAULT NULL,
   PRIMARY KEY (`change_id`)
-) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4;
+) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;

 --
@@ -102,10 +102,10 @@
 CREATE TABLE `state_attributes` (
   `attributes_id` int(11) NOT NULL AUTO_INCREMENT,
   `hash` bigint(20) DEFAULT NULL,
-  `shared_attrs` text DEFAULT NULL,
+  `shared_attrs` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`attributes_id`),
   KEY `ix_state_attributes_hash` (`hash`)
-) ENGINE=InnoDB AUTO_INCREMENT=1447125 DEFAULT CHARSET=utf8mb4;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;

 --
@@ -117,32 +117,31 @@
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `states` (
   `state_id` int(11) NOT NULL AUTO_INCREMENT,
-  `domain` varchar(64) DEFAULT NULL,
-  `entity_id` varchar(255) DEFAULT NULL,
-  `state` varchar(255) DEFAULT NULL,
-  `attributes` text DEFAULT NULL,
+  `entity_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+  `state` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+  `attributes` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `event_id` int(11) DEFAULT NULL,
-  `last_changed` datetime DEFAULT NULL,
-  `last_updated` datetime DEFAULT NULL,
-  `created` datetime DEFAULT NULL,
+  `last_changed` datetime(6) DEFAULT NULL,
+  `last_changed_ts` double DEFAULT NULL,
+  `last_updated` datetime(6) DEFAULT NULL,
+  `last_updated_ts` double DEFAULT NULL,
   `old_state_id` int(11) DEFAULT NULL,
   `attributes_id` int(11) DEFAULT NULL,
-  `origin_idx` int(11) DEFAULT NULL,
-  `context_id` varchar(36) DEFAULT NULL,
-  `context_user_id` varchar(36) DEFAULT NULL,
-  `context_parent_id` varchar(36) DEFAULT NULL,
-  `last_updated_ts` float DEFAULT NULL,
-  `last_changed_ts` float DEFAULT NULL,
+  `context_id` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+  `context_user_id` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+  `context_parent_id` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+  `origin_idx` smallint(6) DEFAULT NULL,
   PRIMARY KEY (`state_id`),
+  KEY `ix_states_old_state_id` (`old_state_id`),
   KEY `ix_states_last_updated_ts` (`last_updated_ts`),
   KEY `ix_states_entity_id_last_updated_ts` (`entity_id`,`last_updated_ts`),
-  KEY `ix_states_context_id` (`context_id`),
-  KEY `ix_states_attributes_id` (`attributes_id`),
   KEY `ix_states_event_id` (`event_id`),
-  KEY `ix_states_old_state_id` (`old_state_id`),
-  CONSTRAINT `states_FK_0_0` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`) ON DELETE SET NULL ON UPDATE NO ACTION,
-  CONSTRAINT `states_FK_1_0` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`) ON DELETE CASCADE ON UPDATE NO ACTION
-) ENGINE=InnoDB AUTO_INCREMENT=243432043 DEFAULT CHARSET=utf8mb4;
+  KEY `ix_states_attributes_id` (`attributes_id`),
+  KEY `ix_states_context_id` (`context_id`),
+  CONSTRAINT `states_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`) ON DELETE CASCADE,
+  CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`),
+  CONSTRAINT `states_ibfk_3` FOREIGN KEY (`attributes_id`) REFERENCES `state_attributes` (`attributes_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;

 --
@@ -154,8 +153,7 @@
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `statistics` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
-  `created` datetime DEFAULT NULL,
-  `metadata_id` int(11) DEFAULT NULL,
+  `created` datetime(6) DEFAULT NULL,
   `start` datetime(6) DEFAULT NULL,
   `mean` double DEFAULT NULL,
   `min` double DEFAULT NULL,
@@ -163,12 +161,13 @@
   `last_reset` datetime(6) DEFAULT NULL,
   `state` double DEFAULT NULL,
   `sum` double DEFAULT NULL,
+  `metadata_id` int(11) DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `ix_statistics_statistic_id_start` (`metadata_id`,`start`),
   KEY `ix_statistics_start` (`start`),
   KEY `ix_statistics_metadata_id` (`metadata_id`),
-  CONSTRAINT `statistics_FK_0_0` FOREIGN KEY (`metadata_id`) REFERENCES `statistics_meta` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
-) ENGINE=InnoDB AUTO_INCREMENT=1823758 DEFAULT CHARSET=utf8mb4;
+  CONSTRAINT `statistics_ibfk_1` FOREIGN KEY (`metadata_id`) REFERENCES `statistics_meta` (`id`) ON DELETE CASCADE
+) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;

 --
@@ -180,15 +179,15 @@
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `statistics_meta` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
-  `statistic_id` varchar(255) DEFAULT NULL,
-  `source` varchar(32) DEFAULT NULL,
-  `unit_of_measurement` varchar(255) DEFAULT NULL,
+  `statistic_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+  `source` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+  `unit_of_measurement` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `has_mean` tinyint(1) DEFAULT NULL,
   `has_sum` tinyint(1) DEFAULT NULL,
-  `name` varchar(255) DEFAULT NULL,
+  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `ix_statistics_meta_statistic_id` (`statistic_id`)
-) ENGINE=InnoDB AUTO_INCREMENT=299 DEFAULT CHARSET=utf8mb4;
+) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;

 --
@@ -200,10 +199,10 @@
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `statistics_runs` (
   `run_id` int(11) NOT NULL AUTO_INCREMENT,
-  `start` datetime DEFAULT NULL,
+  `start` datetime(6) DEFAULT NULL,
   PRIMARY KEY (`run_id`),
   KEY `ix_statistics_runs_start` (`start`)
-) ENGINE=InnoDB AUTO_INCREMENT=147588 DEFAULT CHARSET=utf8mb4;
+) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;

 --
@@ -215,7 +214,7 @@
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `statistics_short_term` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
-  `created` datetime DEFAULT NULL,
+  `created` datetime(6) DEFAULT NULL,
   `start` datetime(6) DEFAULT NULL,
   `mean` double DEFAULT NULL,
   `min` double DEFAULT NULL,
@@ -226,18 +225,18 @@
   `metadata_id` int(11) DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `ix_statistics_short_term_statistic_id_start` (`metadata_id`,`start`),
-  KEY `ix_statistics_short_term_start` (`start`),
   KEY `ix_statistics_short_term_metadata_id` (`metadata_id`),
-  CONSTRAINT `statistics_short_term_FK_0_0` FOREIGN KEY (`metadata_id`) REFERENCES `statistics_meta` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
-) ENGINE=InnoDB AUTO_INCREMENT=20647000 DEFAULT CHARSET=utf8mb4;
+  KEY `ix_statistics_short_term_start` (`start`),
+  CONSTRAINT `statistics_short_term_ibfk_1` FOREIGN KEY (`metadata_id`) REFERENCES `statistics_meta` (`id`) ON DELETE CASCADE
+) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;