# AOS-Sqlite Workshop

SQLite is a small, fast, self-contained SQL Database engine. AOS is the AO System in WebAssembly and uses Lua. The combination provides you with the ability to add lightweight and powerful indexers to AO. More indexers are planned for the future but the AOS Sqlite Module is available now.

`GYrbbe0VbHim_7Hi6zrOpHQXrSQz07XNtwCnfbFo2I0` is the current AO Module Identifier.

## About Me

I am Tom Wilson (Rakis) online, and I am the lead Developer of AOS and AO. I have also developed and published several other projects on Arweave and the Permaweb, from PermaNotes, Permapages, Stamp Protocol, Now, Specs, Permaweb Cookbook, Universal Content Management Protocol, and more. I am passionate about the Permaweb. The current internet is not the cyberspace I envison, and think the Permaweb, ao, and Arweave give us hope to create a cyperspace that helps humanity move forward.

## Getting Started

### Requiements

* [NodeJS](https://nodejs.org) - v22 or greater
* [AOS](https://cookbook_ao.g8way.io)
* Terminal
* Editor
* Git

### What should I know?

You should be knowledgable in computer science and know your way around a terminal. Familiar with Arweave Wallets, AOS and the AOS Console is a plus. To learn more about AOS checkout the cookbook. [https://cookbook_ao.g8way.io](https://cookbook_ao.g8way.io)

### What to expect?

In this presentation, we will focus on AOS Handlers and Sqlite. In order to showcase this functionality we will build the backend of a DevChat Chatroom Process

> NOTE: this is a demo showing the features of AOS and Sqlite and not a guide for best practices.

### Setup :hammer_and_wrench:

We will start with the AOS Console installed, and in the terminal. If you do not have AOS Console installed, please see the cookbook.

Let's create a new aos project.

> I am using a template I created called test-kit, this template gives me the ability to run integration tests locally so I can better manage my AOS process code.

npx -y degit permaweb/aos-test-kit chatroom

We also want to add a package called `DbAdmin` it gives us some helper methods for AOS-SQLite.

cd chatroom
curl https://raw.githubusercontent.com/twilson63/aos-packages/main/packages/db-admin/src/main.lua -o src/DbAdmin.lua

We will be testing our sqlite process with the following command. At this point all tests should pass.

yarn test:sqlite

Lets make sure our DbAdmin Package gets loaded into our test suite.

test/main.test.js

test('load DbAdmin module', async () => {
  const dbAdminCode = fs.readFileSync('./src/DbAdmin.lua', 'utf-8')
  const result = await Send({
    Action: 'Eval',
    Data: `
local function _load() 
  ${dbAdminCode}
end
_G.package.loaded["DbAdmin"] = _load()
return "ok"
    `
  })
  assert.equal(result.Output.data.output, "ok")
})

Lets change our load source test to be the following:

test('load source', async () => {
  const code = fs.readFileSync('./src/main.lua', 'utf-8')
  const result = await Send({ Action: "Eval", Data: code })
  console.log(result)
  assert.equal(result.Output.data.output, "OK")

})

And update our `src/main.lua` to `return "OK"`

## Lets Build :rocket:

local sqlite3 = require('lsqlite3')
db = db or sqlite3.open_memory()
dbAdmin = require('DbAdmin').new(db)

return "OK"

## Lets use Sqlite to build a chatroom

Test

test('init db tables', async () => {
  const result = await Send({Action: "Eval", Data: "require('json').encode(InitDb())"})  assert.deepEqual(result.Output.data.output, ["Users", "Messages"])

Create Tables

* Users
* Messages

USERS = [[
  CREATE TABLE IF NOT EXISTS Users (
    PID TEXT PRIMARY KEY,
    Nickname TEXT
  );
]]

MESSAGES = [[
  CREATE TABLE IF NOT EXISTS Messages (
    MSG_ID TEXT PRIMARY KEY,
    PID TEXT,
    Nick TEXT,
    Body TEXT,
    FOREIGN KEY (PID) REFERENCES Users(PID)
  );
]]

function InitDb() 
  db:exec(USERS)
  db:exec(MESSAGES)
end

TDD -> Testing...

In this workshop, we will use TDD to provide us with happy path tests to
verify our code is working, then we will publish a process and manually test.

We are going to implement three Handlers:

1. Register

Lets write a test:

test('Register a User', async () => {
  const result = await Send({
    Action: 'Register',
    From: 'USER_PID',
    Nickname: 'rakis'
  })
  console.log(result)
  const reply = result.Messages[0]
  assert.ok(reply, "Should have message")
  assert.equal(reply.Target, "USER_PID")
  assert.equal(reply.Tags.find(t => t.name == "Action").value, "DevChat.Registered")

})

Lets write a Handler:

Handlers.add("DevChat.Register",
  function (msg)
    return msg.Action == "Register"
  end,
  function (msg)
    -- get user count
    local userCount = #dbAdmin:exec(
      string.format([[select * from Users where PID = "%s";]], msg.From)
    )
    if userCount > 0 then
      Send({Target = msg.From, Action = "Registered", Data = "Already Registered"})
      print("User already registered")
      return "Already Registered"
    end
    dbAdmin:exec(string.format([[
      INSERT INTO Users (PID, Nickname) VALUES ("%s", "%s");
    ]], msg.From, msg.Nickname or 'anon'))
    Send({
      Target = msg.From,
      Action = "DevChat.Registered",
      Data = "Successfully Registered."
    })
    print("Registered " .. msg.Nickname or "anon")
  end 
)

2. Broadcast

Test

test('Broadcast message', async () => {
  const result = await Send({
    From: 'USER_PID',
    Action: 'Broadcast',
    Data: 'Hello World'
  })
  console.log(result.Messages[0])
  assert.ok(
    result.Messages[0].Assignments.includes('USER_PID')
  ) 
})

Handler

Handlers.add("Chat.Broadcast", 
  function (msg) 
    return msg.Action == "Broadcast"
  end,
  function (msg) 
    -- get user
    local user = dbAdmin:exec(string.format([[
      select PID, Nickname from Users where PID = "%s";
    ]], msg.From))[1] 
    
    if user then
      -- add message
      dbAdmin:exec(string.format([[
        INSERT INTO Messages (MSG_ID, PID, Nick, Body) VALUES ("%s", "%s", "%s", "%s");
      ]], msg.Id, user.ID, user.Nickname, user.Body ))
      -- get users to broadcast message too
      local users = Utils.map(
        function(u)
          return u.PID 
        end, 
        dbAdmin:exec([[ SELECT PID FROM Users; ]])
      )
      Send({
        Target = msg.From, 
        Action = "Broadcasted",
        Broadcaster = msg.From,
        Assignments = users,
        Data = msg.Data,
        Type = "normal",
        Nickname = user.Nickname
      })
      print("Broadcasted Message")
      return "ok"
    else
      Send({Target = msg.From, Data = "Not Registered" })
      print("User not registered, can't broadcase")
    end
  end
)

## Demo

Spawning our Process

aos test-chat --module=GYrbbe0VbHim_7Hi6zrOpHQXrSQz07XNtwCnfbFo2I0
aos> .load src/main.lua
aos> InitDb()

Lets Register

Send({Target = ao.id, Action = "Register", Nickname = "ChatRoom"})

Send({Target = ao.id, Action = "Broadcast", Data = "Hello World"})

## Summary

With the AOS-SQLite module it is easy to add powerful indexeers to your AO Processes. You have the full power of a SQL Database available with a very small footprint.

## Questions

## References

* https://github.com/permaweb/aos-sqlite
* https://github.com/permaweb/aos-test-kit
* https://github.com/twilson63/aos-packages