SQLite のデータベースを古いまたは新しい SQLite へ移す

-- Written by IntoSQL from a SQLite dump. Load it with: sqlite3 -bail <database> < <file>
PRAGMA foreign_keys = OFF;
PRAGMA synchronous = OFF;
BEGIN;

CREATE TABLE customers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT COLLATE NOCASE NOT NULL,
    country TEXT DEFAULT 'US' NOT NULL,
    is_active INTEGER DEFAULT 1 NOT NULL,
    signed_up DATE,
    created_at DATETIME DEFAULT (datetime('now', 'localtime')) NOT NULL,
    CONSTRAINT customers_is_active_check CHECK (is_active IN (0, 1)),
    CONSTRAINT customers_check1 CHECK (length(country) = 2)
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER NOT NULL,
    status TEXT DEFAULT 'pending' NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    items INTEGER DEFAULT 1 NOT NULL,
    ordered_at DATETIME NOT NULL,
    shipped_at DATETIME,
    note TEXT,
    receipt BLOB,
    CONSTRAINT orders_check1 CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    CONSTRAINT orders_fkey FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE
);

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;
ring 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);

-- Indexes, triggers and counters, after the rows so each index builds once.
CREATE UNIQUE INDEX customers_email ON customers (email);
CREATE INDEX orders_idx_orders_customer ON orders (customer_id);
CREATE INDEX orders_idx_orders_status_date ON orders (status, ordered_at);
UPDATE sqlite_sequence SET seq = max(seq, 8) WHERE name = 'customers';
INSERT INTO sqlite_sequence (name, seq) SELECT 'customers', 8 WHERE NOT EXISTS (SELECT 1 FROM sqlite_sequence WHERE name = 'customers');
UPDATE sqlite_sequence SET seq = max(seq, 10) WHERE name = 'orders';
INSERT INTO sqlite_sequence (name, seq) SELECT 'orders', 10 WHERE NOT EXISTS (SELECT 1 FROM sqlite_sequence WHERE name = 'orders');
COMMIT;

-- Rows whose foreign key finds no parent, if any: the load doesn't stop for them.
PRAGMA foreign_key_check;