// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/main/README.md' | perl -ne '$y=$1 if /^```(ts\\b)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~^// deno .*?/example1.ts~)' > /tmp/example1.ts
// deno run --allow-env --allow-net /tmp/example1.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.5.2/mod.ts';

const pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');

pool.forConn
(	async conn =>
    {	await conn.query("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, message text)");
        await conn.query("INSERT INTO t_log (message) VALUES ('Message 1'), ('Message 2'), ('Message 3')");

        for await (const row of await conn.query("SELECT * FROM t_log"))
        {	console.log(row);
        }
    }
);

await pool.shutdown();

MyPool.constructor(options?: Dsn | string | MyPoolOptions)

interface MyPoolOptions
{	dsn?: Dsn | string;
    maxConns?: number;
    retryQueryTimes?: number;
    onLoadFile?: (filename: string) => Promise<(Deno.Reader & Deno.Closer) | undefined>;
    onBeforeCommit?: (conns: Iterable<MyConn>) => Promise<void>;
    managedXaDsns?: Dsn | string | (Dsn|string)[];
    xaCheckEach?: number;
    xaInfoTables?: {dsn: Dsn|string, table: string}[];
    sqlLogger?: SqlLogger | true;
    logger?:
    {	debug(...args: any[]): unknown;
        info(...args: any[]): unknown;
        log(...args: any[]): unknown;
        warn(...args: any[]): unknown;
        error(...args: any[]): unknown;
    };
}

MyPool.forConn<T>(callback: (conn: MyConn) => Promise<T>, dsn?: Dsn|string): Promise<T>

MyPool.session<T>(callback: (session: MySession) => Promise<T>): Promise<T>

MySession.conn(dsn?: Dsn|string, fresh=false): MyConn

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/main/README.md' | perl -ne '$y=$1 if /^```(ts\\b)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~^// deno .*?/example2.ts~)' > /tmp/example2.ts
// deno run --allow-env --allow-net /tmp/example2.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.5.2/mod.ts';

const pool = new MyPool(Deno.env.get('DSN') || 'mysql://root@localhost');

pool.session
(	async session =>
    {	const conn1 = session.conn(); // default DSN
        const conn2 = session.conn(); // the same object
        const conn3 = session.conn(undefined, true); // another connection to default DSN
        const conn4 = session.conn('mysql://tests@localhost'); // connection to different DSN

        console.log(conn1 == conn2); // prints true
        console.log(conn2 != conn3); // prints true

        const connId2 = conn2.queryCol("SELECT Connection_id()").first();
        const connId3 = conn3.queryCol("SELECT Connection_id()").first();
        const connId4 = conn4.queryCol("SELECT Connection_id()").first();

        console.log(await Promise.all([connId2, connId3, connId4])); // prints 3 different connection ids
    }
);

await pool.shutdown();

MyConn.queryVoid(sql: SqlSource, params?: Params): Promise<Resultsets>

MyConn.query(sql: SqlSource, params?: Params): ResultsetsPromise
MyConn.queryMap(sql: SqlSource, params?: Params): ResultsetsPromise
MyConn.queryArr(sql: SqlSource, params?: Params): ResultsetsPromise
MyConn.queryCol(sql: SqlSource, params?: Params): ResultsetsPromise

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/main/README.md' | perl -ne '$y=$1 if /^```(ts\\b)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~^// deno .*?/example3.ts~)' > /tmp/example3.ts
// deno run --allow-env --allow-net /tmp/example3.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.5.2/mod.ts';

const pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');

pool.forConn
(	async conn =>
    {	await conn.queryVoid("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, message text)");
        await conn.queryVoid("INSERT INTO t_log (message) VALUES ('Message 1'), ('Message 2'), ('Message 3')");

        // use ResultsetsPromise.all()
        console.log(await conn.query("SELECT * FROM t_log").all());

        // use Resultsets.all()
        const res = await conn.query("SELECT * FROM t_log");
        console.log(res.columns);
        console.log(await res.all());
    }
);

await pool.shutdown();

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/main/README.md' | perl -ne '$y=$1 if /^```(ts\\b)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~^// deno .*?/example4.ts~)' > /tmp/example4.ts
// deno run --allow-env --allow-net /tmp/example4.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.5.2/mod.ts';

const pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');

pool.forConn
(	async conn =>
    {	await conn.queryVoid("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, message text)");
        await conn.queryVoid("INSERT INTO t_log (message) VALUES ('Message 1'), ('Message 2'), ('Message 3')");

        // use ResultsetsPromise.first()
        console.log(await conn.query("SELECT Count(*) FROM t_log").first());

        // use Resultsets.first()
        const res = await conn.query("SELECT Count(*) FROM t_log");
        console.log(res.columns);
        console.log(await res.first());
    }
);

await pool.shutdown();

ResultsetsPromise.forEach<T>(callback: (row: any) => T|Promise<T>): Promise<T|undefined>

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/main/README.md' | perl -ne '$y=$1 if /^```(ts\\b)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~^// deno .*?/example5.ts~)' > /tmp/example5.ts
// deno run --allow-env --allow-net /tmp/example5.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.5.2/mod.ts';

const pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');

pool.forConn
(	async conn =>
    {	await conn.queryVoid("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, message text)");
        await conn.queryVoid("INSERT INTO t_log (message) VALUES ('Message 1'), ('Message 2'), ('Message 3')");

        // for await loop
        for await (const row of await conn.query("SELECT * FROM t_log"))
        {	console.log(row);
        }

        // ResultsetsPromise.forEach()
        await conn.query("SELECT * FROM t_log").forEach
        (	row =>
            {	console.log(row);
            }
        );
    }
);

await pool.shutdown();

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/main/README.md' | perl -ne '$y=$1 if /^```(ts\\b)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~^// deno .*?/example6.ts~)' > /tmp/example6.ts
// deno run --allow-env --allow-net /tmp/example6.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.5.2/mod.ts';

const pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');

pool.forConn
(	async conn =>
    {	await conn.queryVoid("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, message text)");
        await conn.queryVoid("INSERT INTO t_log (message) VALUES ('Message 1'), ('Message 2'), ('Message 3')");

        const count = await conn.queryCol("SELECT Count(*) FROM t_log").first();
        console.log(count); // prints 3
    }
);

await pool.shutdown();

MyConn.queryVoid(sql: SqlSource, params?: Params): Promise<Resultsets<void>> {...}
MyConn.query<ColumnType=ColumnValue>(sql: SqlSource, params?: Params): ResultsetsPromise<Record<string, ColumnType>> {...}
MyConn.queryMap<ColumnType=ColumnValue>(sql: SqlSource, params?: Params): ResultsetsPromise<Map<string, ColumnType>> {...}
MyConn.queryArr<ColumnType=ColumnValue>(sql: SqlSource, params?: Params): ResultsetsPromise<ColumnType[]> {...}
MyConn.queryCol<ColumnType=ColumnValue>(sql: SqlSource, params?: Params): ResultsetsPromise<ColumnType> {...}

type SqlSource = string | Uint8Array | Deno.Reader&Deno.Seeker | Deno.Reader&{readonly size: number} | ToSqlBytes;
type Params = any[] | Record<string, any> | null;
class ResultsetsPromise<Row> extends Promise<Resultsets<Row>> {...}
type ColumnValue = bigint | Date | Uint8Array | JsonNode;
type JsonNode = null | boolean | number | string | JsonNode[] | {[member: string]: JsonNode};

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/main/README.md' | perl -ne '$y=$1 if /^```(ts\\b)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~^// deno .*?/example7.ts~)' > /tmp/example7.ts
// deno run --allow-env --allow-net /tmp/example7.ts

import {MyPool, ColumnValue} from 'https://deno.land/x/office_spirit_mysql@v0.5.2/mod.ts';

const pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');

pool.forConn
(	async conn =>
    {	await conn.queryVoid("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, message text)");
        await conn.queryVoid("INSERT INTO t_log (message) VALUES ('Message 1'), ('Message 2'), ('Message 3')");

        const row = await conn.query("SELECT * FROM t_log WHERE id=1").first();
        if (row)
        {	// The type of `row` here is `Record<string, ColumnValue>`
            let message = '';
            // Remember that the `message` column can also be null
            if (typeof(row.message) == 'string') // Without this check, the error will be: Type 'ColumnValue' is not assignable to type 'string'
            {	message = row.message;
            }
            console.log(message); // Prints 'Message 1'
        }
    }
);

await pool.shutdown();

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/main/README.md' | perl -ne '$y=$1 if /^```(ts\\b)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~^// deno .*?/example8.ts~)' > /tmp/example8.ts
// deno run --allow-env --allow-net /tmp/example8.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.5.2/mod.ts';

const pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');

pool.forConn
(	async conn =>
    {	await conn.queryVoid("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, message text)");
        await conn.queryVoid("INSERT INTO t_log (message) VALUES ('Message 1'), ('Message 2'), ('Message 3')");

        // Use query<any>()
        const row = await conn.query<any>("SELECT * FROM t_log WHERE id=1").first();
        if (row)
        {	// The type of `row` here is `Record<string, any>`
            const message: string = row.message;
            console.log(message); // Prints 'Message 1'
        }
    }
);

await pool.shutdown();

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/main/README.md' | perl -ne '$y=$1 if /^```(ts\\b)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~^// deno .*?/example9.ts~)' > /tmp/example9.ts
// deno run --allow-env --allow-net /tmp/example9.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.5.2/mod.ts';

const pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');

pool.forConn
(	async conn =>
    {	await conn.queryVoid("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, `time` timestamp, message text)");
        await conn.queryVoid("INSERT INTO t_log SET `time`=Now(), message='Message 1'");

        const row = await conn.query("SELECT `time` + INTERVAL ? DAY AS 'time', message FROM t_log WHERE id=?", [3, 1]).first();
        console.log(row);
    }
);

await pool.shutdown();

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/main/README.md' | perl -ne '$y=$1 if /^```(ts\\b)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~^// deno .*?/example10.ts~)' > /tmp/example10.ts
// deno run --allow-env --allow-net /tmp/example10.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.5.2/mod.ts';

const pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');

pool.forConn
(	async conn =>
    {	await conn.queryVoid("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, `time` timestamp, message text)");
        await conn.queryVoid("INSERT INTO t_log SET `time`=Now(), message='Message 1'");

        const row = await conn.query("SELECT `time` + INTERVAL @days DAY AS 'time', message FROM t_log WHERE id=@`id`", {days: 3, id: 1}).first();
        console.log(row);
    }
);

await pool.shutdown();

type SqlSource = string | Uint8Array | Deno.Reader&Deno.Seeker | Deno.Reader&{readonly size: number} | ToSqlBytes;

interface ToSqlBytes
{	toSqlBytesWithParamsBackslashAndBuffer(putParamsTo: any[]|undefined, noBackslashEscapes: boolean, buffer: Uint8Array): Uint8Array;
}

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/main/README.md' | perl -ne '$y=$1 if /^```(ts\\b)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~^// deno .*?/example11.ts~)' > /tmp/example11.ts
// deno run --allow-env --allow-net /tmp/example11.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.5.2/mod.ts';

// 1. Define the generator

const encoder = new TextEncoder;

/**	Generates SELECT query for demonstrational purposes only
 **/
class SqlSelectGenerator
{	constructor(private table: string, private idValue: number)
    {
    }

    toSqlBytesWithParamsBackslashAndBuffer(putParamsTo: any[]|undefined, noBackslashEscapes: boolean, buffer: Uint8Array)
    {	let sql;
        if (putParamsTo)
        {	putParamsTo.push(this.idValue);
            sql = `SELECT * FROM ${this.table} WHERE id = ?`;
        }
        else
        {	sql = `SELECT * FROM ${this.table} WHERE id = ${this.idValue}`;
        }
        const {read, written} = encoder.encodeInto(sql, buffer);
        if (read == sql.length)
        {	return buffer.subarray(0, written);
        }
        return encoder.encode(sql);
    }
}

// 2. Use the generator

const pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');

pool.forConn
(	async conn =>
    {	await conn.query("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, `time` timestamp, message text)");
        await conn.query("INSERT INTO t_log SET `time`=Now(), message='message'");

        const rows = await conn.query<any>(new SqlSelectGenerator('t_log', 1), []).all();
        console.log(rows);
    }
);

await pool.shutdown();

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/main/README.md' | perl -ne '$y=$1 if /^```(ts\\b)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~^// deno .*?/example12.ts~)' > /tmp/example12.ts
// deno run --allow-env --allow-net /tmp/example12.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.5.2/mod.ts';

const pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');

const N_ROWS = 100;
const N_QUERIES = 800;

try
{	pool.forConn
    (	async conn =>
        {	// CREATE DATABASE
            await conn.query("DROP DATABASE IF EXISTS test1");
            await conn.query("CREATE DATABASE `test1`");

            // USE
            await conn.query("USE test1");

            // CREATE TABLE
            await conn.query("CREATE TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, val integer)");

            // INSERT
            let sql = "INSERT INTO t_log (val) VALUES (0)";
            for (let i=1; i<N_ROWS; i++)
            {	sql += `,(${i})`;
            }
            await conn.queryVoid(sql);

            // Begin tests
            console.log('Begin tests');

            // Text Protocol
            let since = Date.now();
            let sum = 0;
            for (let i=0; i<N_QUERIES; i++)
            {	const n = 1 + Math.floor(Math.random() * N_ROWS);
                sum += await conn.queryCol("SELECT val FROM t_log WHERE id = "+n).first();
            }
            console.log(`Text Protocol took ${(Date.now()-since) / 1000} sec (random=${sum})`);

            // Named params
            since = Date.now();
            sum = 0;
            for (let i=0; i<N_QUERIES; i++)
            {	const n = 1 + Math.floor(Math.random() * N_ROWS);
                sum += await conn.queryCol("SELECT val FROM t_log WHERE id = @n", {n}).first();
            }
            console.log(`Named params took ${(Date.now()-since) / 1000} sec (random=${sum})`);

            // Positional params
            since = Date.now();
            sum = 0;
            for (let i=0; i<N_QUERIES; i++)
            {	const n = 1 + Math.floor(Math.random() * N_ROWS);
                sum += await conn.queryCol("SELECT val FROM t_log WHERE id = ?", [n]).first();
            }
            console.log(`Positional params took ${(Date.now()-since) / 1000} sec (random=${sum})`);

            // Positional params prepared once
            since = Date.now();
            sum = 0;
            await conn.forQueryCol
            (	"SELECT val FROM t_log WHERE id = ?",
                async stmt =>
                {	for (let i=0; i<N_QUERIES; i++)
                    {	const n = 1 + Math.floor(Math.random() * N_ROWS);
                        sum += Number(await stmt.exec([n]).first());
                    }
                }
            );
            console.log(`Positional params prepared once took ${(Date.now()-since) / 1000} sec (random=${sum})`);

            // Drop database that i created
            await conn.query("DROP DATABASE test1");
        }
    );
}
finally
{	await pool.shutdown();
}

forQuery<T>(sql: SqlSource, callback: (prepared: Resultsets) => Promise<T>): Promise<T>

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/main/README.md' | perl -ne '$y=$1 if /^```(ts\\b)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~^// deno .*?/example13.ts~)' > /tmp/example13.ts
// deno run --allow-env --allow-net /tmp/example13.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.5.2/mod.ts';

const pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');

pool.forConn
(	async conn =>
    {	// CREATE TABLE
        await conn.query("CREATE TEMPORARY TABLE t_messages (id integer PRIMARY KEY AUTO_INCREMENT, message text)");

        // INSERT
        await conn.forQuery
        (	"INSERT INTO t_messages SET message=?",
            async prepared =>
            {	for (let i=1; i<=3; i++)
                {	await prepared.exec(['Message '+i]);
                }
            }
        );

        // SELECT
        console.log(await conn.query("SELECT * FROM t_messages").all());
    }
);

await pool.shutdown();

MyConn.forQueryVoid<T>(sql: SqlSource, callback: (prepared: Resultsets<void>) => Promise<T>): Promise<T>
MyConn.forQuery<ColumnType=ColumnValue, T=unknown>(sql: SqlSource, callback: (prepared: Resultsets<Record<string, ColumnType>>) => Promise<T>): Promise<T>
MyConn.forQueryMap<ColumnType=ColumnValue, T=unknown>(sql: SqlSource, callback: (prepared: Resultsets<Map<string, ColumnType>>) => Promise<T>): Promise<T>
MyConn.forQueryArr<ColumnType=ColumnValue, T=unknown>(sql: SqlSource, callback: (prepared: Resultsets<ColumnType[]>) => Promise<T>): Promise<T>
MyConn.forQueryCol<ColumnType=ColumnValue, T=unknown>(sql: SqlSource, callback: (prepared: Resultsets<ColumnType>) => Promise<T>): Promise<T>

Resultsets<Row>.exec(params: any[]): ResultsetsPromise<Row>

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/main/README.md' | perl -ne '$y=$1 if /^```(ts\\b)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~^// deno .*?/example14.ts~)' > /tmp/example14.ts
// deno run --allow-env --allow-net /tmp/example14.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.5.2/mod.ts';
import {copy} from 'https://deno.land/std@0.117.0/streams/conversion.ts';

const pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');

pool.forConn
(	async conn =>
    {	await conn.query("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, `time` timestamp, message text)");
        await conn.query("INSERT INTO t_log SET `time`=Now(), message='long long message'");

        const row = await conn.makeLastColumnReader<any>("SELECT `time`, message FROM t_log WHERE id=1");
        await copy(row!.message, Deno.stdout);
    }
);

await pool.shutdown();

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/main/README.md' | perl -ne '$y=$1 if /^```(ts\\b)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~^// deno .*?/example15.ts~)' > /tmp/example15.ts
// deno run --allow-env --allow-net /tmp/example15.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.5.2/mod.ts';
import {copy} from 'https://deno.land/std@0.117.0/streams/conversion.ts';

const pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');

pool.forConn
(	async conn =>
    {	await conn.queryVoid("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, `time` timestamp, message text)");

        const file = await Deno.open('/etc/passwd', {read: true});
        try
        {	// Write the file to db
            await conn.queryVoid("INSERT INTO t_log SET `time`=Now(), message=?", [file]);
        }
        finally
        {	file.close();
        }

        // Read the contents back from db
        const row = await conn.makeLastColumnReader<any>("SELECT `time`, message FROM t_log WHERE id=1");
        await copy(row!.message, Deno.stdout);
    }
);

await pool.shutdown();

MyConn.query(sql: SqlSource, params?: object|null): ResultsetsPromise;

type SqlSource = string | Uint8Array | Deno.Reader&Deno.Seeker | Deno.Reader&{readonly size: number} | ToSqlBytes;

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests?multiStatements'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/main/README.md' | perl -ne '$y=$1 if /^```(ts\\b)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~^// deno .*?/example16.ts~)' > /tmp/example16.ts
// deno run --allow-env --allow-net /tmp/example16.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.5.2/mod.ts';

// Don't forget `?multiStatements`
const pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests?multiStatements');

pool.forConn
(	async conn =>
    {	const filename = await Deno.makeTempFile();
        try
        {	await Deno.writeTextFile
            (	filename,
                `	CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, c_time timestamp, message text);

                    INSERT INTO t_log SET c_time=Now(), message='long long message';
                `
            );

            const file = await Deno.open(filename, {read: true});
            try
            {	await conn.queryVoid(file);
            }
            finally
            {	file.close();
            }

            console.log(await conn.query("SELECT c_time, message FROM t_log").all());
        }
        finally
        {	await Deno.remove(filename);
        }
    }
);

await pool.shutdown();

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/main/README.md' | perl -ne '$y=$1 if /^```(ts\\b)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~^// deno .*?/example17.ts~)' > /tmp/example17.ts
// deno run --allow-env --allow-net /tmp/example17.ts

import {MyPool, sql} from 'https://deno.land/x/office_spirit_mysql@v0.5.2/mod.ts';
import {dirname} from "https://deno.land/std@0.117.0/path/mod.ts";

const pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');

// Set handler for LOAD DATA LOCAL INFILE queries
const ALLOWED_DIRS = ['/tmp'];
pool.options
(	{	onLoadFile(filename: string)
        {	if (ALLOWED_DIRS.includes(dirname(filename)))
            {	return Deno.open(filename, {read: true});
            }
            else
            {	return Promise.resolve(undefined);
            }
        }
    }
);

// Download some public example CSV file from github
const data = await fetch('https://raw.githubusercontent.com/lukes/ISO-3166-Countries-with-Regional-Codes/master/all/all.csv');
const filename = await Deno.makeTempFile();
await Deno.writeTextFile(filename, await data.text());

// Create temporary table, load the data to it, and then select it back
pool.forConn
(	async conn =>
    {	// CREATE TABLE
        await conn.queryVoid
        (	`	CREATE TEMPORARY TABLE t_countries
                (	country_code char(2) CHARACTER SET latin1 NOT NULL PRIMARY KEY,
                    country_name varchar(128) NOT NULL
                )
            `
        );

        // SQL-quote filename, because `LOAD DATA LOCAL INFILE` doesn't accept parameters
        const filenameSql = await conn.queryCol("SELECT Quote(?)", [filename]).first();

        // LOAD DATA
        const res = await conn.queryVoid
        (	`	LOAD DATA LOCAL INFILE ${filenameSql}
                INTO TABLE t_countries
                FIELDS TERMINATED BY ','
                ENCLOSED BY '"'
                IGNORE 1 LINES
                (@name, @code)
                SET
                    country_code = @code,
                    country_name = @name
            `
        );
        console.log(res.statusInfo);

        // SELECT
        console.log(await conn.query("SELECT * FROM t_countries LIMIT 3").all());
    }
);

await pool.shutdown();

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/main/README.md' | perl -ne '$y=$1 if /^```(ts\\b)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~^// deno .*?/example18.ts~)' > /tmp/example18.ts
// deno run --allow-env --allow-net /tmp/example18.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.5.2/mod.ts';

const pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests?multiStatements');

pool.forConn
(	async conn =>
    {	const resultsets = await conn.query
        (	`	CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, message text);

                INSERT INTO t_log (message) VALUES ('Message 1'), ('Message 2'), ('Message 3');

                SELECT * FROM t_log;
            `
        );

        console.log(resultsets.affectedRows); // prints 0

        await resultsets.nextResultset();
        console.log(resultsets.affectedRows); // prints 3

        await resultsets.nextResultset();
        console.log(resultsets.columns.length); // prints 2

        for await (const row of resultsets)
        {	console.log(row);
        }
    }
);

await pool.shutdown();

/**	Start transaction.
    To start regular transaction, call `startTrx()` without parameters.
    To start READONLY transaction, pass `{readonly: true}`.
    To start distributed transaction, pass `{xaId: '...'}`.
    If you want `conn.connectionId` to be automatically appended to XA identifier, pass `{xaId1: '...'}`, where `xaId1` is the first part of the `xaId`.
    If connection to server was not yet established, the `conn.connectionId` is not known (and `startTrx()` will not connect), so `conn.connectionId` will be appended later on first query.
 **/
function MyConn.startTrx(options?: {readonly?: boolean, xaId?: string, xaId1?: string}): Promise<void>;

/**	Creates transaction savepoint, and returns Id number of this new savepoint.
    Then you can call `conn.rollback(pointId)`.
 **/
function MyConn.savepoint(): Promise<number>;

/**	If the current transaction is of distributed type, this function prepares the 2-phase commit.
    Else does nothing.
    If this function succeeded, the transaction will be saved on the server till you call `commit()`.
    The saved transaction can survive server restart and unexpected halt.
    You need to commit it as soon as possible, to release all the locks that it holds.
    Usually, you want to prepare transactions on all servers, and immediately commit them, if `prepareCommit()` succeeded, or rollback them, if it failed.
    If you create cross-server session with `pool.session()`, you can start and commit transaction on session level, and in this case no need to explicitly prepare the commit (`session.commit()` will do it implicitly).
 **/
function MyConn.prepareCommit(): Promise<void>;

/**	Rollback to a savepoint, or all.
    If `toPointId` is not given or undefined - rolls back the whole transaction (XA transactions can be rolled back before `prepareCommit()` called, or after that).
    If `toPointId` is a number returned from `savepoint()` call, rolls back to that point (also works with XAs).
    If `toPointId` is `0`, rolls back to the beginning of transaction, and doesn't end this transaction (doesn't work with XAs).
    If rollback fails, will disconnect from server and throw ServerDisconnectedError.
 **/
function MyConn.rollback(toPointId?: number): Promise<void>;

/**	Commit.
    If the current transaction started with `{xa: true}`, i'll throw error, because you need to call `prepareCommit()` first.
    If commit fails will rollback and throw error. If rollback also fails, will disconnect from server and throw ServerDisconnectedError.
 **/
function MyConn.commit(): Promise<void>;

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/main/README.md' | perl -ne '$y=$1 if /^```(ts\\b)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~^// deno .*?/example19.ts~)' > /tmp/example19.ts
// deno run --allow-env --allow-net /tmp/example19.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.5.2/mod.ts';

const pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');

await pool.forConn
(	async conn =>
    {	// CREATE DATABASE
        await conn.query("DROP DATABASE IF EXISTS test1");
        await conn.query("CREATE DATABASE `test1`");

        // USE
        await conn.query("USE test1");

        // CREATE TABLE
        await conn.query("CREATE TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, a int)");

        // Start transaction
        await conn.startTrx();

        // Insert a row
        await conn.query("INSERT INTO t_log SET a = 123");

        // Ensure that the row is present
        console.log(await conn.queryCol("SELECT a FROM t_log WHERE id=1").first()); // prints: 123

        // Rollback
        await conn.rollback();

        // The inserted row not persisted
        console.log(await conn.queryCol("SELECT Count(*) FROM t_log").first()); // prints: 0

        // Drop database that i created
        await conn.query("DROP DATABASE test1");
    }
);

await pool.shutdown();

await conn.startTrx({readonly: true});

await conn.startTrx({xaId: Math.random()+''});
// OR
await conn.startTrx({xaId1: Math.random()+'-'});

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/main/README.md' | perl -ne '$y=$1 if /^```(ts\\b)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~^// deno .*?/example20.ts~)' > /tmp/example20.ts
// deno run --allow-env --allow-net /tmp/example20.ts

import {MyPool, Dsn} from 'https://deno.land/x/office_spirit_mysql@v0.5.2/mod.ts';

const dsn1 = new Dsn('mysql://app:app@localhost/test1');
const dsn2 = new Dsn('mysql://app:app@localhost/test2');

const pool = new MyPool
(	{	managedXaDsns:
        [	'mysql://manager:manager@localhost/test1',
            'mysql://manager:manager@localhost/test2',
        ],
        xaInfoTables:
        [	{dsn: 'mysql://manager:manager@localhost/test1', table: 't_xa_info_1'},
            {dsn: 'mysql://manager:manager@localhost/test1', table: 't_xa_info_2'},
            {dsn: 'mysql://manager:manager@localhost/test2', table: 't_xa_info_1'},
            {dsn: 'mysql://manager:manager@localhost/test2', table: 't_xa_info_2'},
        ]
    }
);

try
{	await pool.session
    (	async session =>
        {	// Start distributed transaction
            await session.startTrx({xa: true});

            // Get connection objects (actual connection will be established on first query)
            const conn1 = session.conn(dsn1);
            const conn2 = session.conn(dsn2);

            // Query
            await conn1.query("INSERT INTO t_log SET message = 'Msg 1'");
            await conn2.query("INSERT INTO t_log SET message = 'Msg 1'");

            // 2-phase commit
            await session.commit();
        }
    );
}
finally
{	await pool.shutdown();
}

interface MyPoolOptions
{	// ...
    sqlLogger?: SqlLogger | true;
    // ...
}

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/main/README.md' | perl -ne '$y=$1 if /^```(ts\\b)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~^// deno .*?/example21.ts~)' > /tmp/example21.ts
// deno run --allow-env --allow-net /tmp/example21.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.5.2/mod.ts';

const pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');

pool.options({sqlLogger: true});

let result;

try
{	pool.forConn
    (	async conn =>
        {	// CREATE DATABASE
            await conn.query("DROP DATABASE IF EXISTS test1");
            await conn.query("CREATE DATABASE `test1`");

            // USE
            await conn.query("USE test1");

            // CREATE TABLE
            await conn.query("CREATE TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, message text)");

            // INSERT
            await conn.query("INSERT INTO t_log SET message = 'Message 1'");

            result = await conn.queryCol("SELECT message FROM t_log WHERE id = @n", {n: 1}).first();

            // Drop database that i created
            await conn.query("DROP DATABASE test1");
        }
    );
}
finally
{	await pool.shutdown();
}

console.log(`Result: ${result}`);

interface MyPoolOptions
{	// ...
    sqlLogger?: SqlLogger | true;
    // ...
}

interface SqlLogger
{	/**	A new connection established.
     **/
    connect?: (dsn: Dsn, connectionId: number) => Promise<unknown>;

    /**	Connection state reset (before returning this connection to it's pool).
     **/
    resetConnection?: (dsn: Dsn, connectionId: number) => Promise<unknown>;

    /**	Disconnected.
     **/
    disconnect?: (dsn: Dsn, connectionId: number) => Promise<unknown>;

    /**	Started to send a new query to the server.
        `isPrepare` means that this is query preparation operation, and following `queryEnd()` will receive `stmtId` that server returned.
        `previousResultNotRead` means that i'm sending queries batch without reading results. `queryEnd()` of previous query will be called later, but before the `queryEnd()` of this query.
        In other words, i can call the sequence of `queryNew()`, `querySql()`, `queryStart()` several times, and then call `queryEnd()` corresponding number of times.
     **/
    queryNew?: (dsn: Dsn, connectionId: number, isPrepare: boolean, previousResultNotRead: boolean) => Promise<unknown>;

    /**	After `queryNew()` called, i can call `querySql()` one or several times (in case of error even 0 times).
        Each call to `querySql()` appends more bytes to current SQL query.
        `data` is SQL query serialized to bytes (you can use `TextDecoder` to restore the original SQL string).
        The query SQL always comes as bytes, no matter what you passed to `conn.query()` function (bytes, string, `Deno.Reader`, etc).
        Since `data` is a pointer to internal buffer (that is changing all the time), you need to use the `data` immediately (without await), or to copy it to another variable.
     **/
    querySql?: (dsn: Dsn, connectionId: number, data: Uint8Array, noBackslashEscapes: boolean) => Promise<unknown>;

    /**	After `queryNew()` and one or more `querySql()` called, i call `queryStart()`.
        At this point the query is sent to the server.
     **/
    queryStart?: (dsn: Dsn, connectionId: number) => Promise<unknown>;

    /**	Query completed (it's result status is read from the server, but rows, if any, are not yet read).
        The query can either complete with success or with error.
        If this was query preparation, the `stmtId` will be the numeric ID of this prepared statement.
     **/
    queryEnd?: (dsn: Dsn, connectionId: number, result: Resultsets<unknown>|Error, stmtId?: number) => Promise<unknown>;

    /**	Started executing a prepared statement.
     **/
    execNew?: (dsn: Dsn, connectionId: number, stmtId: number) => Promise<unknown>;

    /**	After `execNew()` called, i can call `execParam()` zero or more times to bind parameter values.
        I can call `execParam()` for the same parameter several times - each time appends data to the parameter.
        If i don't call `execParam()` for some parameter, this means that it's value is NULL.
        Strings and `Deno.Reader`s always come as `Uint8Array`.
        Since `data` is a pointer to internal buffer (that is changing all the time), you need to use the `data` immediately (without await), or to copy it to another variable.
     **/
    execParam?: (dsn: Dsn, connectionId: number, nParam: number, data: Uint8Array|number|bigint|Date) => Promise<unknown>;

    /**	After `execNew()` and zero or more `execParam()` called, i call `execStart()`.
        At this point the query parameters are sent to the server.
     **/
    execStart?: (dsn: Dsn, connectionId: number) => Promise<unknown>;

    /**	Query completed (it's result status is read from the server, but rows, if any, are not yet read).
        The query can either complete with success or with error.
        `result` can be undefined for internal queries.
     **/
    execEnd?: (dsn: Dsn, connectionId: number, result: Resultsets<unknown>|Error|undefined) => Promise<unknown>;

    /**	Prepared query deallocated (unprepared).
     **/
    deallocatePrepare?: (dsn: Dsn, connectionId: number, stmtId: number) => Promise<unknown>;

    /**	I'll call this function when `MyPool.shutdown()` is called.
     **/
    shutdown?: () => Promise<unknown>;
}

pool.options({sqlLogger: true});

// Is the same as:

pool.options({sqlLogger: new SqlLogToWriter(Deno.stderr, !Deno.noColor)});

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/main/README.md' | perl -ne '$y=$1 if /^```(ts\\b)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~^// deno .*?/example22.ts~)' > /tmp/example22.ts
// deno run --allow-env --allow-net /tmp/example22.ts

import {MyPool, SqlLogToWriter} from 'https://deno.land/x/office_spirit_mysql@v0.5.2/mod.ts';

const pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');

class SqlLogToFile extends SqlLogToWriter
{	protected closer: Deno.Closer;

    private constructor(writer: Deno.Writer&Deno.Closer, withColor=false)
    {	super(writer, withColor);
        this.closer = writer;
    }

    static async inst(path: string|URL, withColor=false)
    {	const fd = await Deno.open(path, {write: true, create: true});
        return new SqlLogToFile(fd, withColor);
    }

    async shutdown()
    {	await super.shutdown();
        this.closer.close();
    }
}

pool.options({sqlLogger: await SqlLogToFile.inst('/tmp/sql.log', !Deno.noColor)});

let result;

try
{	pool.forConn
    (	async conn =>
        {	// CREATE DATABASE
            await conn.query("DROP DATABASE IF EXISTS test1");
            await conn.query("CREATE DATABASE `test1`");

            // USE
            await conn.query("USE test1");

            // CREATE TABLE
            await conn.query("CREATE TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, message text)");

            // INSERT
            await conn.query("INSERT INTO t_log SET message = 'Message 1'");

            result = await conn.queryCol("SELECT message FROM t_log WHERE id = @n", {n: 1}).first();

            // Drop database that i created
            await conn.query("DROP DATABASE test1");
        }
    );
}
finally
{	await pool.shutdown();
}

console.log(`Result: ${result}`);