-- sample-mysql.sql: MySQL-specific syntax, as a mysql client script.
-- Free sample from 99helpers.com/file-types/sql
--
-- Tested with MySQL 8.0. Run it in a throwaway database:
--   mysql -e "CREATE DATABASE sample"
--   mysql sample < sample-mysql.sql
-- The DELIMITER lines are commands for the mysql client, not SQL: other
-- tools may reject them.

-- 1. Talk to the server in utf8mb4, MySQL's full UTF-8. Its older "utf8"
--    (utf8mb3) can't store emoji and other 4-byte characters.
SET NAMES utf8mb4;

DROP PROCEDURE IF EXISTS add_points;
DROP TABLE IF EXISTS audit_note;
DROP TABLE IF EXISTS members;

-- 2. Backticks quote identifiers in MySQL (standard SQL uses double
--    quotes). AUTO_INCREMENT numbers rows; ENGINE and the default
--    character set are table options.
CREATE TABLE `members` (
    `id`       INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `email`    VARCHAR(255) NOT NULL,
    `nickname` VARCHAR(50)  NOT NULL,
    `points`   INT          NOT NULL DEFAULT 0,
    `prefs`    JSON         NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_members_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. In MySQL a backslash escapes the next character inside a string,
--    unlike standard SQL: 'It\'s' and 'It''s' are the same value here.
INSERT INTO members (email, nickname, points, prefs) VALUES
    ('ada@example.com',  'Ada 🦀',     10, '{"theme": "dark", "lang": "nl"}'),
    ('ben@example.org',  'It\'s Ben',  0,  NULL),
    ('cleo@example.net', 'Cléo',       25, '{"theme": "light"}');

-- 4. An upsert in MySQL's own syntax.
INSERT INTO members (email, nickname, points) VALUES ('ben@example.org', 'Ben', 5)
ON DUPLICATE KEY UPDATE points = points + 5;

-- 5. A stored procedure. Its body contains semicolons, so the client's
--    statement separator is switched to // while it's defined, then back.
DELIMITER //
CREATE PROCEDURE add_points(IN member_email VARCHAR(255), IN amount INT)
BEGIN
    UPDATE members SET points = points + amount WHERE email = member_email;
    SELECT nickname, points FROM members WHERE email = member_email;
END //
DELIMITER ;

CALL add_points('cleo@example.net', 15);

-- 6. JSON: ->> extracts a value as text. CHAR_LENGTH counts characters,
--    LENGTH counts bytes, so they differ for 'Ada 🦀' and 'Cléo'.
SELECT id, nickname, points,
       prefs ->> '$.theme' AS theme,
       CHAR_LENGTH(nickname) AS chars,
       LENGTH(nickname) AS bytes
FROM members
ORDER BY id;

-- 7. Most DDL commits the open transaction automatically. The INSERT
--    below survives the ROLLBACK, because CREATE TABLE committed it.
START TRANSACTION;
INSERT INTO members (email, nickname) VALUES ('dev@example.com', 'Dev');
CREATE TABLE audit_note (id INT PRIMARY KEY);
ROLLBACK;
SELECT COUNT(*) AS members_after_rollback FROM members;