Oracle を MariaDB に変換

-- Written by IntoSQL from a Oracle 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 SEQUENCE `invoice_numbers` START WITH 1020 MINVALUE 1 INCREMENT BY 1;
CREATE TABLE `customers` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(150) NOT NULL,
  `country` char(2) NOT NULL DEFAULT 'US',
  `is_active` smallint NOT NULL DEFAULT 1,
  `signed_up` datetime,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `customers_email_uk` (`email`),
  CONSTRAINT `check1` CHECK (`is_active` IN (0, 1))
) AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='People who bought at least once';

CREATE TABLE `orders` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `customer_id` bigint NOT NULL,
  `invoice` bigint DEFAULT (nextval(`invoice_numbers`)),
  `status` varchar(10) 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` varchar(255),
  PRIMARY KEY (`id`),
  KEY `orders_status_date_ix` (`status`,`ordered_at`),
  CONSTRAINT `check1` CHECK (`status` IN ('pending', 'paid', 'shipped', 'cancelled')),
  CONSTRAINT `orders_customer_fk` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`)
) AUTO_INCREMENT=1 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 00:00:00','2023-01-15 10:24:00.000000');
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 00:00:00','2023-02-03 08:11:42.000000');
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 00:00:00','2023-03-21 19:05:17.000000');
INSERT INTO `customers` (`id`,`name`,`email`,`country`,`is_active`,`signed_up`,`created_at`) VALUES (4,'李雷','lilei@example.cn','CN',1,'2023-05-09 00:00:00','2023-05-09 02:40:00.000000');
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.000000');
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 00:00:00','2024-01-02 09:30:00.000000');
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 00:00:00','2024-02-29 23:59:59.000000');
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 00:00:00','2024-04-12 12:12:12.000000');
INSERT INTO `orders` (`id`,`customer_id`,`invoice`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`) VALUES (1,1,1000,'shipped',129.9,3,'2024-03-01 10:00:00','2024-03-02 16:30:00',NULL);
INSERT INTO `orders` (`id`,`customer_id`,`invoice`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`) VALUES (2,2,1001,'paid',54,1,'2024-03-02 11:15:00',NULL,'Gift wrap 🎁');
INSERT INTO `orders` (`id`,`customer_id`,`invoice`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`) VALUES (3,1,1002,'cancelled',12.5,1,'2024-03-03 09:45:00',NULL,'Customer changed their mind');
INSERT INTO `orders` (`id`,`customer_id`,`invoice`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`) VALUES (4,4,1003,'shipped',980,12,'2024-03-05 02:10:00','2024-03-07 08:00:00','请小心轻放');
INSERT INTO `orders` (`id`,`customer_id`,`invoice`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`) VALUES (5,5,1004,'pending',45.75,2,'2024-03-08 17:20:00',NULL,'Leave at the door; ring twice');
INSERT INTO `orders` (`id`,`customer_id`,`invoice`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`) VALUES (6,6,1005,'shipped',310.4,5,'2024-03-10 13:00:00','2024-03-11 09:00:00','Fragile: "glass"');
INSERT INTO `orders` (`id`,`customer_id`,`invoice`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`) VALUES (7,8,1006,'paid',76.2,2,'2024-03-11 18:45:00',NULL,NULL);
INSERT INTO `orders` (`id`,`customer_id`,`invoice`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`) VALUES (8,2,1007,'paid',19.99,1,'2024-03-12 08:00:00',NULL,'Paid at the counter');
INSERT INTO `orders` (`id`,`customer_id`,`invoice`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`) VALUES (9,3,1008,'shipped',64,2,'2024-03-14 15:30:00',NULL,'Shipping date never recorded');
INSERT INTO `orders` (`id`,`customer_id`,`invoice`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`) VALUES (10,7,1009,'paid',1499,1,'2024-03-15 21:05:00',NULL,'C:\\Users\\ravi\\invoice.pdf');

SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;