// 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.10.0/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;
    maxConnsWaitQueue?: number;
    onLoadFile?: (filename: string) => Promise<(Deno.Reader & Deno.Closer) | undefined>;
    onBeforeCommit?: (conns: readonly MyConn[]) => Promise<void>;
    managedXaDsns?: Dsn | string | (Dsn|string)[];
    xaCheckEach?: number;
    xaInfoTables?: {dsn: Dsn|string, table: string}[];
    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.10.0/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.10.0/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.10.0/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.10.0/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.10.0/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.10.0/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.10.0/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.10.0/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.10.0/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.10.0/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.10.0/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.10.0/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.10.0/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.10.0/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.10.0/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.10.0/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?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 .*?/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.10.0/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 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();

function MyConn.setSqlLogger(sqlLogger?: SqlLogger|true): void;
function MySession.setSqlLogger(sqlLogger?: SqlLogger|true): 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.10.0/mod.ts';

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

let result;

try
{	pool.forConn
    (	async conn =>
        {	// Enable SQL logger
            conn.setSqlLogger(true);

            // 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 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 (the query is not executed, but stored on the server).
        This function can return object that implements `SqlLoggerQuery` for further logging the query process.
        Query SQL (if any) will be handed to the methods of `SqlLoggerQuery`.
     **/
    query?: (dsn: Dsn, connectionId: number, isPrepare: boolean, noBackslashEscapes: boolean) => Promise<SqlLoggerQuery | undefined>;

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

    /**	I'll call this function at the end of `MyPool.forConn()` or `MyPool.session()`.
     **/
    dispose?: () => Promise<unknown>;
}

/**	1. First one of `appendToQuery()` or `setStmtId()` is called.
    To start writing a regular query, `appendToQuery()` is called one or multiple times.
    To write a prepared query, `setStmtId()` is called (once).
    2. Then, in case of prepared query, a sequence of `appendToParam()` (one or multiple times) and `paramEnd()` can be called.
    3. Then, if writing queries batch, `nextQuery()` is called, and the process repeats from the beginning.
    4. Then, after all the queries in batch are written, `start()` is called. At this point queries are sent to the database server.
    5. Then, when the server responds, `end()` is called.
 **/
interface SqlLoggerQuery
{	appendToQuery?: (data: Uint8Array) => Promise<unknown>;

    setStmtId?: (stmtId: number) => Promise<unknown>;

    appendToParam?: (nParam: number, data: Uint8Array|number|bigint|Date) => Promise<unknown>;

    paramEnd?: (nParam: number) => Promise<unknown>;

    nextQuery?: () => Promise<unknown>;

    start?: () => Promise<unknown>;

    /**	If this was query preparation (`SqlLogger.query(_, _, true)`), `stmtId` will be the statement ID that the server returned.
        Else `stmtId` will be `-1`.
     **/
    end?: (result: Resultsets<unknown>|Error|undefined, stmtId: number) => Promise<unknown>;
}

conn.setSqlLogger(true);

// Is the same as:

conn.setSqlLogger(new SqlLogToWriter(Deno.stderr, !Deno.noColor, 10_000, 3_000, 100));

// 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 --allow-write /tmp/example20.ts

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

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

const LOG_FILE = '/tmp/sql.log';

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, truncate: true});
        return new SqlLogToFile(fd, withColor);
    }

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

let result;

try
{	pool.forConn
    (	async conn =>
        {	// Enable SQL logger
            conn.setSqlLogger(await SqlLogToFile.inst(LOG_FILE, !Deno.noColor));

            // 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}`);

less -r /tmp/sql.log

/**	Commit current transaction (if any), and start new.
    This is lazy operation. The corresponding command will be sent to the server later (however commit of the current transaction will happen immediately).
    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)`.
    This is lazy operation. The corresponding command will be sent to the server later.
    Calling `savepoint()` immediately followed by `rollback(pointId)` to this point will send no commands.
 **/
function MyConn.savepoint(): number;

/**	If the current transaction is of distributed type, this function prepares the 2-phase commit.
    Else does nothing.
    If this function succeeds, 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.
 **/
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 (not to savepoint) failed, will disconnect from server and throw ServerDisconnectedError.
    If `toPointId` was `0` (not for XAs), the transaction will be restarted after the disconnect if rollback failed.
 **/
function MyConn.rollback(toPointId?: number): Promise<void>;

/**	Commit.
    If the current transaction is XA, and you didn't call `prepareCommit()` i'll throw error.
    With `andChain` parameter will commit and then restart the same transaction (doesn't work with XAs).
    If commit fails will rollback and throw error. If rollback also fails, will disconnect from server and throw ServerDisconnectedError.
 **/
function MyConn.commit(andChain=false): 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 .*?/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.10.0/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()+'-'});

/**	Commit current transaction (if any), and start new.
    If there're active transactions, they will be properly (2-phase if needed) committed.
    Then new transaction will be started on all connections in this session.
    If then you'll ask a new connection, it will join the transaction.
    If commit fails, this function does rollback, and throws the Error.
 **/
function MySession.startTrx(options?: {readonly?: boolean, xa?: boolean}): Promise<void>;

/**	Create session-level savepoint, and return it's ID number.
    Then you can call `session.rollback(pointId)`.
    This is lazy operation. The corresponding command will be sent to the server later.
    Calling `savepoint()` immediately followed by `rollback(pointId)` to this point will send no commands.
    Using `MySession.savepoint()` doesn't interfere with `MyConn.savepoint()`, so it's possible to use both.
 **/
function MySession.savepoint(): number;

/**	Rollback all the active transactions in this session.
    If `toPointId` is not given or undefined - rolls back the whole transaction.
    If `toPointId` is a number returned from `savepoint()` call, rolls back all the transactions to that point.
    If `toPointId` is `0`, rolls back to the beginning of transaction, and doesn't end this transaction (also works with XAs).
    If rollback (not to savepoint) failed, will disconnect from server and throw ServerDisconnectedError.
    If `toPointId` was `0`, the transaction will be restarted after the disconnect if rollback failed.
 **/
function MySession.rollback(toPointId?: number): Promise<void>;

/**	Commit all the active transactions in this session.
    If the session transaction was started with `{xa: true}`, will do 2-phase commit.
    If failed will rollback. If failed and `andChain` was true, will rollback and restart the same transaction (also XA).
    If rollback failed, will disconnect (and restart the transaction in case of `andChain`).
 **/
function MySession.commit(andChain=false): 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 .*?/example22.ts~)' > /tmp/example22.ts
// deno run --allow-env --allow-net /tmp/example22.ts

import {MyPool, Dsn} from 'https://deno.land/x/office_spirit_mysql@v0.10.0/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 =>
        {	// Enable SQL logger
            session.setSqlLogger(true);

            // 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();
}