lewtds/index.html

## index.html
<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport"
          content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Document</title>

    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/css/bootstrap.min.css" integrity="sha384-TX8t27EcRE3e/ihU7zmQxVncDAy5uIKz4rEkgIXeMed4M0jlfIDPvg6uqKI2xXr2" crossorigin="anonymous">

    <style>
        #messages {
            font-family: 'Consolas', 'Deja Vu Sans Mono', 'Bitstream Vera Sans Mono', monospace;
            padding-left: 0;
        }

        .msg-line {
            list-style: none;
        }

        .msg-timestamp {
            color: cadetblue;
        }

        .msg-sender {
            color: cornflowerblue;
        }
    </style>
</head>
<body>

<div class="container-fluid">
    Message count: <span id="message-count"></span>

    <hr>

    <ul id="messages">
    </ul>

    <hr>

    <form id="chat-box">
        <div class="form-group">
            <label class="col-form-label">Send messages as: </label>
            <input type="text" id="username" class="form-control" placeholder="Who are you?">
        </div>

        <div class="form-group">
            <div class="form-row">
                <div class="col-sm-10">
                    <input type="text" id="chat-input" class="form-control">
                </div>

                <div class="col-sm-2">
                    <button type="submit" class="btn btn-primary">Send</button>
                </div>
            </div>

        </div>
    </form>
</div>

<template id="template-message-line">
    <li class="msg-line"><span class="msg-timestamp">2020-12-05 14:19:42 +0200</span> <span class="msg-sender">trung</span>: <span class="msg-content">Message content</span></li>
</template>

<script>

    const chatMessages = [];
    const $chatList = document.getElementById("messages");
    const $chatBox = document.getElementById("chat-box");
    const $chatInput = document.getElementById("chat-input");
    const $messageCount = document.getElementById("message-count");
    const $messageLineTemplate = document.getElementById("template-message-line");
    const $username = document.getElementById("username");

    const chatInfo = {
        messageCount: 0
    };

    $chatBox.addEventListener('submit', function onSubmit(event) {
        event.preventDefault();

        fetch("send", {
            method: 'POST',
            headers: {
                'Content-Type': 'application/json'
            },
            body: JSON.stringify({
                timestamp: new Date().toISOString(),
                sender: $username.value,
                message: $chatInput.value
            })
        });

        $chatInput.value = "";
    });

    const msgEventStream = new EventSource("chat-content");
    msgEventStream.onmessage = function(event) {
        // Parse Materialize's TAIL format (probably should be done on the server but meh)
        const [revTimestamp, freq, sender, message, clientTimestamp, serverTimestamp] = event.data.split('\t');

        if (freq > 0) {
            for (let i = 0; i < freq; i++) {
                chatMessages.push({
                    revTimestamp,
                    freq,
                    sender,
                    message,
                    clientTimestamp: new Date(clientTimestamp + 'Z'),
                    serverTimestamp: new Date(serverTimestamp + 'Z'),
                });
            }
        }

        chatMessages.sort(function (a, b) {
            return a.clientTimestamp - b.clientTimestamp;
        });

        $chatList.innerHTML = "";

        chatMessages.forEach(function(msg) {
            const $msgLine = document.importNode($messageLineTemplate.content, true)
            $msgLine.querySelector(".msg-content").innerHTML = msg.message;
            $msgLine.querySelector(".msg-timestamp").innerHTML = msg.serverTimestamp.toLocaleString();
            $msgLine.querySelector(".msg-sender").innerHTML = msg.sender;

            $chatList.appendChild($msgLine);
        });
    };

    const chatInfoStream = new EventSource("chat-info");
    chatInfoStream.onmessage = function(event) {
        var [revTimestamp, freq, messageCount] = event.data.split('\t');
        chatInfo.messageCount = messageCount;
        $messageCount.innerHTML = chatInfo.messageCount;
    };
</script>
</body>
</html>

## main.py
from functools import partial
import psycopg3
from sanic import Sanic
from sanic.response import json, stream

app = Sanic("hello_example")

app.static('/', 'index.html', content_type="text/html; charset=utf-8")


async def init_app(app):
    await ensure_schema(await get_connection())


def get_connection():
    return psycopg3.AsyncConnection.connect("postgresql://localhost:6875/chat")


async def ensure_schema(conn):
    async with conn:
        async with await conn.cursor() as cursor:
            await cursor.execute("SELECT 1 FROM mz_catalog.mz_tables WHERE name = %s", ['messages'])

            if not bool(cursor.rowcount):
                await cursor.execute("""
                CREATE TABLE messages (
                    sender TEXT NOT NULL,
                    message TEXT NOT NULL,
                    client_timestamp TIMESTAMP NOT NULL,
                    server_timestamp TIMESTAMP NOT NULL
                )
                """)

            await cursor.execute("""
            CREATE MATERIALIZED VIEW IF NOT EXISTS chat_info AS (
                SELECT count(*) FROM messages
            )
            """)


@app.route("/send", methods=["POST"])
async def chat_send(request):
    conn = await get_connection()

    try:
        async with await conn.cursor() as cursor:
            await cursor.execute("INSERT INTO messages (sender, message, client_timestamp, server_timestamp) VALUES (%s, %s, %s, now())",
                                 (request.json['sender'], request.json['message'], request.json['timestamp']))
    finally:
        await conn.close()

    return json({})


@app.route("/chat-content")
async def chat_content(request):
    return stream(partial(streaming_response, 'messages', request), content_type="text/event-stream")


@app.route("/chat-info")
async def chat_info(request):
    return stream(partial(streaming_response, 'chat_info', request), content_type="text/event-stream")


async def streaming_response(relation_name, request, response):
    conn = await get_connection()
    try:
        async with await conn.cursor() as cursor:
            async with cursor.copy(f"COPY (TAIL {relation_name}) TO stdout") as copy:
                while (result := await copy.read()) != b"":
                    await response.write(b'data: ' + result + b'\n')
    finally:
        await conn.close()


if __name__ == "__main__":
    app.add_task(init_app)
    app.run(host="0.0.0.0", port=8000, debug=True)

## requirements.txt
psycopg3[binary]
sanic
	<!doctype html>
	<html lang="en">
	<head>
	<meta charset="UTF-8">
	<meta name="viewport"
	content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
	<meta http-equiv="X-UA-Compatible" content="ie=edge">
	<title>Document</title>

	<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/css/bootstrap.min.css" integrity="sha384-TX8t27EcRE3e/ihU7zmQxVncDAy5uIKz4rEkgIXeMed4M0jlfIDPvg6uqKI2xXr2" crossorigin="anonymous">

	<style>
	#messages {
	font-family: 'Consolas', 'Deja Vu Sans Mono', 'Bitstream Vera Sans Mono', monospace;
	padding-left: 0;
	}

	.msg-line {
	list-style: none;
	}

	.msg-timestamp {
	color: cadetblue;
	}

	.msg-sender {
	color: cornflowerblue;
	}
	</style>
	</head>
	<body>

	<div class="container-fluid">
	Message count: <span id="message-count"></span>

	<hr>

	<ul id="messages">
	</ul>

	<hr>

	<form id="chat-box">
	<div class="form-group">
	<label class="col-form-label">Send messages as: </label>
	<input type="text" id="username" class="form-control" placeholder="Who are you?">
	</div>

	<div class="form-group">
	<div class="form-row">
	<div class="col-sm-10">
	<input type="text" id="chat-input" class="form-control">
	</div>

	<div class="col-sm-2">
	<button type="submit" class="btn btn-primary">Send</button>
	</div>
	</div>

	</div>
	</form>
	</div>

	<template id="template-message-line">
	<li class="msg-line"><span class="msg-timestamp">2020-12-05 14:19:42 +0200</span> <span class="msg-sender">trung</span>: <span class="msg-content">Message content</span></li>
	</template>

	<script>

	const chatMessages = [];
	const $chatList = document.getElementById("messages");
	const $chatBox = document.getElementById("chat-box");
	const $chatInput = document.getElementById("chat-input");
	const $messageCount = document.getElementById("message-count");
	const $messageLineTemplate = document.getElementById("template-message-line");
	const $username = document.getElementById("username");

	const chatInfo = {
	messageCount: 0
	};

	$chatBox.addEventListener('submit', function onSubmit(event) {
	event.preventDefault();

	fetch("send", {
	method: 'POST',
	headers: {
	'Content-Type': 'application/json'
	},
	body: JSON.stringify({
	timestamp: new Date().toISOString(),
	sender: $username.value,
	message: $chatInput.value
	})
	});

	$chatInput.value = "";
	});

	const msgEventStream = new EventSource("chat-content");
	msgEventStream.onmessage = function(event) {
	// Parse Materialize's TAIL format (probably should be done on the server but meh)
	const [revTimestamp, freq, sender, message, clientTimestamp, serverTimestamp] = event.data.split('\t');

	if (freq > 0) {
	for (let i = 0; i < freq; i++) {
	chatMessages.push({
	revTimestamp,
	freq,
	sender,
	message,
	clientTimestamp: new Date(clientTimestamp + 'Z'),
	serverTimestamp: new Date(serverTimestamp + 'Z'),
	});
	}
	}

	chatMessages.sort(function (a, b) {
	return a.clientTimestamp - b.clientTimestamp;
	});

	$chatList.innerHTML = "";

	chatMessages.forEach(function(msg) {
	const $msgLine = document.importNode($messageLineTemplate.content, true)
	$msgLine.querySelector(".msg-content").innerHTML = msg.message;
	$msgLine.querySelector(".msg-timestamp").innerHTML = msg.serverTimestamp.toLocaleString();
	$msgLine.querySelector(".msg-sender").innerHTML = msg.sender;

	$chatList.appendChild($msgLine);
	});
	};

	const chatInfoStream = new EventSource("chat-info");
	chatInfoStream.onmessage = function(event) {
	var [revTimestamp, freq, messageCount] = event.data.split('\t');
	chatInfo.messageCount = messageCount;
	$messageCount.innerHTML = chatInfo.messageCount;
	};
	</script>
	</body>
	</html>
	from functools import partial
	import psycopg3
	from sanic import Sanic
	from sanic.response import json, stream

	app = Sanic("hello_example")

	app.static('/', 'index.html', content_type="text/html; charset=utf-8")


	async def init_app(app):
	await ensure_schema(await get_connection())


	def get_connection():
	return psycopg3.AsyncConnection.connect("postgresql://localhost:6875/chat")


	async def ensure_schema(conn):
	async with conn:
	async with await conn.cursor() as cursor:
	await cursor.execute("SELECT 1 FROM mz_catalog.mz_tables WHERE name = %s", ['messages'])

	if not bool(cursor.rowcount):
	await cursor.execute("""
	CREATE TABLE messages (
	sender TEXT NOT NULL,
	message TEXT NOT NULL,
	client_timestamp TIMESTAMP NOT NULL,
	server_timestamp TIMESTAMP NOT NULL
	)
	""")

	await cursor.execute("""
	CREATE MATERIALIZED VIEW IF NOT EXISTS chat_info AS (
	SELECT count(*) FROM messages
	)
	""")


	@app.route("/send", methods=["POST"])
	async def chat_send(request):
	conn = await get_connection()

	try:
	async with await conn.cursor() as cursor:
	await cursor.execute("INSERT INTO messages (sender, message, client_timestamp, server_timestamp) VALUES (%s, %s, %s, now())",
	(request.json['sender'], request.json['message'], request.json['timestamp']))
	finally:
	await conn.close()

	return json({})


	@app.route("/chat-content")
	async def chat_content(request):
	return stream(partial(streaming_response, 'messages', request), content_type="text/event-stream")


	@app.route("/chat-info")
	async def chat_info(request):
	return stream(partial(streaming_response, 'chat_info', request), content_type="text/event-stream")


	async def streaming_response(relation_name, request, response):
	conn = await get_connection()
	try:
	async with await conn.cursor() as cursor:
	async with cursor.copy(f"COPY (TAIL {relation_name}) TO stdout") as copy:
	while (result := await copy.read()) != b"":
	await response.write(b'data: ' + result + b'\n')
	finally:
	await conn.close()


	if __name__ == "__main__":
	app.add_task(init_app)
	app.run(host="0.0.0.0", port=8000, debug=True)