SQLite を MariaDB に変換

-- Written by IntoSQL from a SQLite dump. Load it with: mariadb --default-character-set=utf8mb4 <database> < <file>
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO,ALLOW_INVALID_DATES';

CREATE TABLE `customers` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  `email` varchar(768) NOT NULL,
  `country` text NOT NULL DEFAULT 'US',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `signed_up` date,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  CONSTRAINT `check1` CHECK (char_length(`country`) = 2)
) AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `orders` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `customer_id` bigint NOT NULL,
  `status` varchar(768) NOT NULL DEFAULT 'pending',
  `total` decimal(10,2) NOT NULL,
  `items` int NOT NULL DEFAULT 1,
  `ordered_at` datetime NOT NULL,
  `shipped_at` datetime,
  `note` text,
  `receipt` blob,
  PRIMARY KEY (`id`),
  KEY `idx_orders_customer` (`customer_id`),
  KEY `idx_orders_status_date` (`status`,`ordered_at`),
  CONSTRAINT `check1` CHECK (`status` IN ('pending', 'paid', 'shipped', 'cancelled')),
  FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `customers` (`id`,`name`,`email`,`country`,`is_active`,`signed_up`,`created_at`) VALUES (1,'Ana Martínez','ana@example.com','MX',1,'2023-01-15','2023-01-15 10:24:00');
INSERT INTO `customers` (`id`,`name`,`email`,`country`,`is_active`,`signed_up`,`created_at`) VALUES (2,'Zoë Müller','zoe@example.de','DE',1,'2023-02-03','2023-02-03 08:11:42');
INSERT INTO `customers` (`id`,`name`,`email`,`country`,`is_active`,`signed_up`,`created_at`) VALUES (3,'José Peña','jose@example.es','ES',0,'2023-03-21','2023-03-21 19:05:17');
INSERT INTO `customers` (`id`,`name`,`email`,`country`,`is_active`,`signed_up`,`created_at`) VALUES (4,'李雷','lilei@example.cn','CN',1,'2023-05-09','2023-05-09 02:40:00');
INSERT INTO `customers` (`id`,`name`,`email`,`country`,`is_active`,`signed_up`,`created_at`) VALUES (5,'Noah O\'Brien','noah@example.ie','IE',1,NULL,'2023-06-30 14:00:00');
INSERT INTO `customers` (`id`,`name`,`email`,`country`,`is_active`,`signed_up`,`created_at`) VALUES (6,'Amélie Laurent','amelie@example.fr','FR',1,'2024-01-02','2024-01-02 09:30:00');
INSERT INTO `customers` (`id`,`name`,`email`,`country`,`is_active`,`signed_up`,`created_at`) VALUES (7,'Ravi Patel','ravi@example.in','IN',0,'2024-02-29','2024-02-29 23:59:59');
INSERT INTO `customers` (`id`,`name`,`email`,`country`,`is_active`,`signed_up`,`created_at`) VALUES (8,'Sofia Rossi 🌻','sofia@example.it','IT',1,'2024-04-12','2024-04-12 12:12:12');
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`receipt`) VALUES (1,1,'shipped',129.9,3,'2024-03-01 10:00:00','2024-03-02 16:30:00',NULL,NULL);
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`receipt`) VALUES (2,2,'paid',54,1,'2024-03-02 11:15:00',NULL,'Gift wrap 🎁',NULL);
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`receipt`) VALUES (3,1,'cancelled',12.5,1,'2024-03-03 09:45:00',NULL,'Customer changed their mind',NULL);
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`receipt`) VALUES (4,4,'shipped',980,12,'2024-03-05 02:10:00','2024-03-07 08:00:00','请小心轻放',X'25504446');
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`receipt`) VALUES (5,5,'pending',45.75,2,'2024-03-08 17:20:00',NULL,'Leave at the door;\nring twice',NULL);
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`receipt`) VALUES (6,6,'shipped',310.4,5,'2024-03-10 13:00:00','2024-03-11 09:00:00','Fragile: "glass"',NULL);
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`receipt`) VALUES (7,8,'paid',76.2,2,'2024-03-11 18:45:00',NULL,NULL,NULL);
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`receipt`) VALUES (8,2,'paid',19.99,1,'2024-03-12 08:00:00',NULL,'Paid at the counter',NULL);
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`receipt`) VALUES (9,3,'shipped',64,2,'2024-03-14 15:30:00',NULL,'Shipping date never recorded',NULL);
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`receipt`) VALUES (10,7,'paid',1499,1,'2024-03-15 21:05:00',NULL,'C:\\Users\\ravi\\invoice.pdf',NULL);

SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;