db/mysql

import db.mysql

func main:
    d := mysql.Open("127.0.0.1", "salam", "secret", "shop", 3306)
    if not mysql.Ok(d):
        println "cannot connect:", mysql.Error(d)
        ret 1
    end
    defer mysql.Close(d)

    println "server:", mysql.ServerVersion(d)
    println "client:", mysql.ClientVersion()
    println "alive:", mysql.Ping(d)
    println "database:", mysql.QueryText(d, "SELECT DATABASE()")
    println "charset:", mysql.QueryText(d, "SELECT @@character_set_connection")
    println "tls:", mysql.QueryText(d, "SELECT VARIABLE_VALUE FROM information_schema.SESSION_STATUS WHERE VARIABLE_NAME = 'Ssl_version'")

    bad := mysql.Open("127.0.0.1", "salam", "wrong", "shop", 3306)
    println "with a wrong password:", mysql.Ok(bad)
    println mysql.Error(bad)

    nobody := mysql.Open("127.0.0.1", "salam", "secret", "shop", 3307)
    println mysql.Error(nobody)
    ret 0
end

import db.mysql

func main:
    d := mysql.Open("127.0.0.1", "salam", "secret", "shop", 3306)
    if not mysql.Ok(d):
        println "cannot connect:", mysql.Error(d)
        ret 1
    end
    defer mysql.Close(d)

    mysql.Exec(d, "DROP TABLE IF EXISTS orders")
    mysql.Exec(d, "DROP TABLE IF EXISTS books")

    books := `CREATE TABLE books (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(200) NOT NULL UNIQUE,
        author VARCHAR(100) NOT NULL,
        price DECIMAL(8, 2) NOT NULL,
        stock INT NOT NULL DEFAULT 0,
        isbn VARCHAR(20) NULL,
        details JSON NULL
    ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4`
    println "books:", mysql.Exec(d, books)

    orders := `CREATE TABLE orders (
        id INT AUTO_INCREMENT PRIMARY KEY,
        book_id INT NOT NULL,
        customer VARCHAR(100) NOT NULL,
        qty INT NOT NULL,
        total DECIMAL(10, 2) NOT NULL,
        FOREIGN KEY (book_id) REFERENCES books (id)
    ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4`
    println "orders:", mysql.Exec(d, orders)

    mut r := mysql.Query(d, "SELECT TABLE_NAME, COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'shop' AND TABLE_NAME IN ('books', 'orders') GROUP BY TABLE_NAME ORDER BY TABLE_NAME")
    until mysql.Next(r):
        println mysql.Text(r, 0), "has", mysql.Int(r, 1), "columns"
    end
    mysql.Finish(r)
    ret 0
end

import db.mysql

func main:
    d := mysql.Open("127.0.0.1", "salam", "secret", "shop", 3306)
    if not mysql.Ok(d):
        println "cannot connect:", mysql.Error(d)
        ret 1
    end
    defer mysql.Close(d)

    mysql.Exec(d, `INSERT INTO books (title, author, price, stock, details) VALUES ('Master and Commander', 'Patrick O''Brian', 14.50, 4, '{"pages": 412, "tags": ["sea", "navy"]}')`)
    println "first book id:", mysql.LastInsertId(d)

    mysql.Exec(d, "INSERT INTO books (title, author, price, stock, isbn) VALUES ('The Remains of the Day', 'Kazuo Ishiguro', 11.25, 2, '9780571200733')")
    println "second book id:", mysql.LastInsertId(d)

    many := `INSERT INTO books (title, author, price, stock, details) VALUES
        ('A Wizard of Earthsea', 'Ursula K. Le Guin', 9.99, 6, '{"pages": 183, "tags": ["fantasy"]}'),
        ('The Blind Owl', 'Sadegh Hedayat', 8.00, 3, NULL),
        ('شازده احتجاب', 'هوشنگ گلشیری', 7.50, 5, '{"pages": 120, "tags": ["novel"]}')`
    mysql.Exec(d, many)
    println "rows added:", mysql.AffectedRows(d)
    println "id of the first row of that insert:", mysql.LastInsertId(d)

    println "books now:", mysql.QueryInt(d, "SELECT COUNT(*) FROM books")
    println "stock in total:", mysql.QueryInt(d, "SELECT SUM(stock) FROM books")
    ret 0
end

import str
import db.mysql

struct Book:
    pub id: int = 0
    pub title: str = ""
    pub author: str = ""
    pub price: str = ""
    pub stock: int = 0
end

func load_books(d: mysql.Database, sql: str): Vector<Book>:
    mut out := Vector {} as Vector<Book>
    mut r := mysql.Query(d, sql)
    if not mysql.QueryOk(r):
        println "query failed:", mysql.Error(d)
        ret out
    end
    until mysql.Next(r):
        out.push(Book {
            id = mysql.Int(r, 0), title = mysql.Text(r, 1), author = mysql.Text(r, 2), price = mysql.Text(r, 3), stock = mysql.Int(r, 4)
        })
    end
    mysql.Finish(r)
    ret out
end

func main:
    d := mysql.Open("127.0.0.1", "salam", "secret", "shop", 3306)
    if not mysql.Ok(d):
        println "cannot connect:", mysql.Error(d)
        ret 1
    end
    defer mysql.Close(d)

    r := mysql.Query(d, "SELECT id, title, author, price, stock FROM books LIMIT 0")
    mut names := Vector {} as Vector<str>
    repeat mysql.ColumnCount(r) in i:
        names.push(mysql.ColumnName(r, i))
    end
    mysql.Finish(r)
    println "columns:", str.Join(names, ", ")

    books := load_books(d, "SELECT id, title, author, price, stock FROM books ORDER BY price DESC")
    each b in books:
        println b.id, b.title, "by", b.author, "costs", b.price, "with", b.stock, "in stock"
    end

    cheap := load_books(d, "SELECT id, title, author, price, stock FROM books WHERE price < 9 ORDER BY id")
    println "under 9:", cheap.len()
    ret 0
end

import str
import db.mysql

func count_by_author(d: mysql.Database, name: str): int:
    ret mysql.QueryInt(d, "SELECT COUNT(*) FROM books WHERE author = " + d.Quote(name))
end

func like_pattern(d: mysql.Database, word: str): str:
    mut w := mysql.Escape(d, word)
    w = str.Replace(w, "%", "\\%")
    w = str.Replace(w, "_", "\\_")
    ret "'%" + w + "%'"
end

func main:
    d := mysql.Open("127.0.0.1", "salam", "secret", "shop", 3306)
    if not mysql.Ok(d):
        println "cannot connect:", mysql.Error(d)
        ret 1
    end
    defer mysql.Close(d)

    println "by Patrick O'Brian:", count_by_author(d, "Patrick O'Brian")
    println "by هوشنگ گلشیری:", count_by_author(d, "هوشنگ گلشیری")

    attack := "nobody' OR '1'='1"
    println "quoted:", d.Quote(attack)
    println "safe query finds:", count_by_author(d, attack)
    unsafe := "SELECT COUNT(*) FROM books WHERE author = '" + attack + "'"
    println "pasted query finds:", mysql.QueryInt(d, unsafe)

    println "titles with 'the':", mysql.QueryInt(d, "SELECT COUNT(*) FROM books WHERE title LIKE " + like_pattern(d, "the"))
    println "titles with '%':", mysql.QueryInt(d, "SELECT COUNT(*) FROM books WHERE title LIKE " + like_pattern(d, "%"))
    ret 0
end

import db.mysql

func main:
    d := mysql.Open("127.0.0.1", "salam", "secret", "shop", 3306)
    if not mysql.Ok(d):
        println "cannot connect:", mysql.Error(d)
        ret 1
    end
    defer mysql.Close(d)

    mysql.Exec(d, "UPDATE books SET isbn = '9780393307054' WHERE id = 1")

    mut r := mysql.Query(d, "SELECT title, isbn, JSON_VALUE(details, '$.pages') FROM books ORDER BY id")
    until mysql.Next(r):
        mut isbn := mysql.Text(r, 1)
        if mysql.IsNull(r, 1):
            isbn = "(no isbn)"
        end
        mut pages := "unknown"
        if not mysql.IsNull(r, 2):
            pages = mysql.Text(r, 2) + " pages"
        end
        println mysql.Text(r, 0), "|", isbn, "|", pages
    end
    mysql.Finish(r)

    println "IS NULL finds:", mysql.QueryInt(d, "SELECT COUNT(*) FROM books WHERE isbn IS NULL")
    println "= NULL finds:", mysql.QueryInt(d, "SELECT COUNT(*) FROM books WHERE isbn = NULL")
    println "Int of NULL:", mysql.QueryInt(d, "SELECT NULL")
    println "COALESCE:", mysql.QueryText(d, "SELECT COALESCE(isbn, 'none') FROM books WHERE id = 3")
    ret 0
end

import db.mysql

func main:
    d := mysql.Open("127.0.0.1", "salam", "secret", "shop", 3306)
    if not mysql.Ok(d):
        println "cannot connect:", mysql.Error(d)
        ret 1
    end
    defer mysql.Close(d)

    mysql.Exec(d, "UPDATE books SET stock = stock + 10 WHERE author = 'Kazuo Ishiguro'")
    println "restocked:", mysql.AffectedRows(d)

    mysql.Exec(d, "UPDATE books SET price = price * 1.10 WHERE price < 9")
    println "repriced:", mysql.AffectedRows(d)

    mysql.Exec(d, "UPDATE books SET stock = 12 WHERE id = 2")
    println "setting the same value again changes:", mysql.AffectedRows(d)

    mysql.Exec(d, "DELETE FROM books WHERE title = 'The Blind Owl'")
    println "deleted:", mysql.AffectedRows(d)

    mut r := mysql.Query(d, "SELECT id, title, price, stock FROM books ORDER BY id")
    until mysql.Next(r):
        println mysql.Int(r, 0), mysql.Text(r, 1), mysql.Text(r, 2), mysql.Int(r, 3)
    end
    mysql.Finish(r)
    ret 0
end

import str
import db.mysql

func place_order(d: mysql.Database, book: int, customer: str, qty: int): bool:
    if mysql.Begin(d) != 0:
        ret false
    end
    id := str.FromInt(book)
    n := str.FromInt(qty)
    mysql.Exec(d, "UPDATE books SET stock = stock - " + n + " WHERE id = " + id + " AND stock >= " + n)
    if mysql.AffectedRows(d) != 1:
        mysql.Rollback(d)
        ret false
    end
    sql := "INSERT INTO orders (book_id, customer, qty, total) SELECT id, " + d.Quote(customer) + ", " + n + ", price * " + n + " FROM books WHERE id = " + id
    if mysql.Exec(d, sql) != 0:
        mysql.Rollback(d)
        ret false
    end
    ret mysql.Commit(d) == 0
end

func main:
    d := mysql.Open("127.0.0.1", "salam", "secret", "shop", 3306)
    if not mysql.Ok(d):
        println "cannot connect:", mysql.Error(d)
        ret 1
    end
    defer mysql.Close(d)

    println "Sara, 2 x book 3:", place_order(d, 3, "Sara", 2)
    println "Reza, 1 x book 1:", place_order(d, 1, "Reza", 1)
    println "Neda, 3 x book 5:", place_order(d, 5, "Neda", 3)
    println "Ali, 9 x book 1:", place_order(d, 1, "Ali", 9)
    println "Omid, 1 x book 99:", place_order(d, 99, "Omid", 1)

    println "orders:", mysql.QueryInt(d, "SELECT COUNT(*) FROM orders")
    println "stock of book 1:", mysql.QueryInt(d, "SELECT stock FROM books WHERE id = 1")
    println "stock of book 3:", mysql.QueryInt(d, "SELECT stock FROM books WHERE id = 3")
    println "takings:", mysql.QueryText(d, "SELECT SUM(total) FROM orders")
    ret 0
end

import db.mysql

func attempt(d: mysql.Database, sql: str):
    if mysql.Exec(d, sql) == 0:
        println "ok"
        ret
    end
    println "error", mysql.Errno(d), "->", mysql.Error(d)
end

func main:
    d := mysql.Open("127.0.0.1", "salam", "secret", "shop", 3306)
    if not mysql.Ok(d):
        println "cannot connect:", mysql.Error(d)
        ret 1
    end
    defer mysql.Close(d)

    attempt(d, "INSERT INTO books (title, author, price) VALUES ('A Wizard of Earthsea', 'Someone', 5)")
    attempt(d, "INSERT INTO orders (book_id, customer, qty, total) VALUES (99, 'Omid', 1, 5)")
    attempt(d, "INSERT INTO books (title, author) VALUES ('No Price', 'Nobody')")
    attempt(d, "SELEC title FROM books")
    attempt(d, "UPDATE books SET stock = stock WHERE id = 1")

    r := mysql.Query(d, "SELECT name FROM authors")
    if not mysql.QueryOk(r):
        println "query failed:", mysql.Errno(d), mysql.Error(d)
    end

    println "still connected:", mysql.Ping(d)
    println "stock of book 1:", mysql.QueryInt(d, "SELECT stock FROM books WHERE id = 1")
    ret 0
end

import db
import db.mysql

func main:
    mut pool := db.NewPool(3)
    repeat 3 in i:
        d := mysql.Open("127.0.0.1", "salam", "secret", "shop", 3306)
        if not mysql.Ok(d):
            println "connection", i, "failed:", mysql.Error(d)
            ret 1
        end
        db.Add(pool, mysql.Conn(d))
    end
    println "size:", db.Size(pool), "idle:", db.Idle(pool)

    a := db.Acquire(pool)
    b := db.Acquire(pool)
    ca := db.Conn(pool, a)
    cb := db.Conn(pool, b)
    println "kind:", ca.Kind(), "in use:", db.InUse(pool)
    println "separate sessions:", db.QueryInt(ca, "SELECT CONNECTION_ID()") != db.QueryInt(cb, "SELECT CONNECTION_ID()")

    restock := Vector {} as Vector<str>
    restock.push("UPDATE books SET stock = stock + 5 WHERE id = 1")
    restock.push("UPDATE books SET stock = stock + 5 WHERE id = 3")
    println "restock in one transaction:", db.InTx(cb, restock)
    println "stock of book 1 seen from the other connection:", db.QueryInt(ca, "SELECT stock FROM books WHERE id = 1")

    c := db.Acquire(pool)
    full := db.Acquire(pool)
    println "a fourth lease:", db.Leased(full)

    db.Release(pool, a)
    db.Release(pool, b)
    db.Release(pool, c)
    println "idle again:", db.Idle(pool)
    println "dead connections dropped:", db.DropDead(pool)
    db.Free(pool)
    ret 0
end

import db.mysql

func main:
    d := mysql.Open("127.0.0.1", "salam", "secret", "shop", 3306)
    if not mysql.Ok(d):
        println "cannot connect:", mysql.Error(d)
        ret 1
    end
    defer mysql.Close(d)

    sales := `SELECT b.title, SUM(o.qty), SUM(o.total), COUNT(DISTINCT o.customer)
        FROM orders o JOIN books b ON b.id = o.book_id
        GROUP BY b.id, b.title
        ORDER BY SUM(o.total) DESC`
    mut r := mysql.Query(d, sales)
    println mysql.RowCount(r), "books sold"
    until mysql.Next(r):
        println mysql.Text(r, 0), "| copies", mysql.Int(r, 1), "| takings", mysql.Text(r, 2), "| customers", mysql.Int(r, 3)
    end
    mysql.Finish(r)

    println "never sold:", mysql.QueryText(d, "SELECT GROUP_CONCAT(title ORDER BY id SEPARATOR ', ') FROM books WHERE id NOT IN (SELECT book_id FROM orders)")
    println "about the sea:", mysql.QueryText(d, "SELECT title FROM books WHERE JSON_CONTAINS(details, '\"sea\"', '$.tags')")
    println "pages on the shelf:", mysql.QueryInt(d, "SELECT SUM(stock * JSON_VALUE(details, '$.pages')) FROM books")
    println "stock value:", mysql.QueryText(d, "SELECT SUM(stock * price) FROM books")
    ret 0
end

import str
import db.mysql

struct Delivery:
    pub title: str = ""
    pub author: str = ""
    pub price: str = ""
    pub copies: int = 0
end

func main:
    d := mysql.Open("127.0.0.1", "salam", "secret", "shop", 3306)
    if not mysql.Ok(d):
        println "cannot connect:", mysql.Error(d)
        ret 1
    end
    defer mysql.Close(d)

    mut crate := Vector {} as Vector<Delivery>
    crate.push(Delivery { title = "A Wizard of Earthsea", author = "Ursula K. Le Guin", price = "9.99", copies = 4 })
    crate.push(Delivery { title = "The Tombs of Atuan", author = "Ursula K. Le Guin", price = "9.99", copies = 3 })
    crate.push(Delivery { title = "Kafka on the Shore", author = "Haruki Murakami", price = "13.40", copies = 2 })

    mut rows := Vector {} as Vector<str>
    each b in crate:
        rows.push("(" + d.Quote(b.title) + ", " + d.Quote(b.author) + ", " + b.price + ", " + str.FromInt(b.copies) + ")")
    end
    sql := "INSERT INTO books (title, author, price, stock) VALUES " + str.Join(rows, ", ") + " ON DUPLICATE KEY UPDATE stock = stock + VALUES(stock), price = VALUES(price)"
    if mysql.Exec(d, sql) != 0:
        println "import failed:", mysql.Error(d)
        ret 1
    end
    println "affected rows:", mysql.AffectedRows(d)
    println "books now:", mysql.QueryInt(d, "SELECT COUNT(*) FROM books")
    println "Earthsea stock:", mysql.QueryInt(d, "SELECT stock FROM books WHERE title = 'A Wizard of Earthsea'")
    ret 0
end

import db
import db.mysql

func main:
    d := mysql.Open("127.0.0.1", "salam", "secret", "shop", 3306)
    if not mysql.Ok(d):
        println "cannot connect:", mysql.Error(d)
        ret 1
    end
    c := mysql.Conn(d)

    mut m := db.NewMigrator()
    db.AddMigration(m, 1, "reviews", "CREATE TABLE reviews (id INT AUTO_INCREMENT PRIMARY KEY, book_id INT NOT NULL, stars TINYINT NOT NULL)", "DROP TABLE reviews")
    db.AddMigration(m, 2, "review text", "ALTER TABLE reviews ADD COLUMN body TEXT NULL", "ALTER TABLE reviews DROP COLUMN body")

    println "version before:", db.SchemaVersion(m, c), "pending:", db.PendingCount(m, c)
    println "applied:", db.MigrateUp(m, c)
    println "version after:", db.SchemaVersion(m, c)
    println "applied on a second run:", db.MigrateUp(m, c)
    println "review columns:", db.QueryInt(c, "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'shop' AND TABLE_NAME = 'reviews'")

    println "rolled back:", db.Rollback(m, c)
    println "version now:", db.SchemaVersion(m, c)
    println "migrated to 0:", db.MigrateTo(m, c, 0)
    println "reviews table left:", db.QueryInt(c, "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'shop' AND TABLE_NAME = 'reviews'")
    db.FreeMigrator(m)
    c.Close()
    ret 0
end

import env
import db.mysql

func status(d: mysql.Database, name: str): str:
    ret mysql.QueryText(d, "SELECT VARIABLE_VALUE FROM information_schema.SESSION_STATUS WHERE VARIABLE_NAME = '" + name + "'")
end

func main:
    mode := env.GetOr("SALAM_MYSQL_SSL_MODE", "(not set)")
    println "SALAM_MYSQL_SSL_MODE:", mode
    d := mysql.Open("127.0.0.1", "salam", "secret", "shop", 3306)
    if not mysql.Ok(d):
        println "cannot connect:", mysql.Error(d)
        ret 1
    end
    defer mysql.Close(d)

    version := status(d, "Ssl_version")
    if version == "":
        println "this connection is not encrypted"
    else:
        println "encrypted with", version, "using", status(d, "Ssl_cipher")
    end
    ret 0
end