Powered by AppSignal & Oban Pro
Notesclub
Sign in with GitHub
created by hec & contributors
terms privacy

Setup database

service/mock-database.livemd

avatar
avatar
avatar
Share to X
Share to Bluesky
More notebooks

Setup database

Mix.install([
  {:kino_db, "~> 0.4.0"},
  {:exqlite, "~> 0.23.0"},
  {:postgrex, "~> 0.18"}
])

Setup mux

opts = [database: "bkhack/service/db"]
{:ok, conn_sqlite} = Kino.start_child({Exqlite, opts})
alias Exqlite, as: Sqlx_sqlite

Target 2: Supabase Postgres

opts = [
  hostname: "db.dojhbipasrejjurzremz.supabase.co",
  port: 5432,
  username: "postgres",
  password: System.fetch_env!("LB_BKHACK_SUPABASE_PASSWORD"),
  database: "postgres",
  socket_options: [:inet6]
]
{:ok, conn_supabase} = Kino.start_child({Postgrex, opts})
alias Postgrex, as: Sqlx_supabase

Target 3: Firestore (TBA)

Target 4: local MongoDB (TBA)

Target 5: Mongo Atlas MongoDB (TBA)

Setup

Choose

conn = conn_sqlite
alias Sqlx_sqlite, as: Sqlx

Libraries

defmodule RunSql do
  def query(conn, queryList) do
    Enum.map( queryList , fn q -> Sqlx.query!(conn,q, []) end )
  end
  def query(conn, queryList, opts) do
    Enum.map( queryList , fn q -> Sqlx.query!(conn,q, opts) end )
  end
end
result = Exqlite.query!(conn, ~S"SELECT * FROM users;", [])

Relational tables

Note:

  • All string types should be of length at least 255 e.g. varchar(255).
  • DDL statements should be laxed i.e. do only if (not) exists 
dropTablesList = [
  fn -> Sqlx.query!(conn, ~S"drop table if exists pullrequests", []) end,
  fn -> Sqlx.query!(conn, ~S"drop table if exists commentratings", []) end,
  fn -> Sqlx.query!(conn, ~S"drop table if exists comments", []) end,
  fn -> Sqlx.query!(conn, ~S"drop table if exists posts", []) end,
  fn -> Sqlx.query!(conn, ~S"drop table if exists users", []) end,
  fn -> IO.puts "Dropped tables" end
]
IO.inspect dropTablesList
createTablesList = [
  fn -> Sqlx.query!(
    conn,
    ~S"""
    create table if not exists users (
      user_id integer primary key,
      name varchar(255) not null,
      role integer not null
    )
    """,
    []
  ) end,
  fn -> Sqlx.query!(
    conn,
    ~S"""
    create table if not exists posts (
      post_id integer primary key,
      post_title varchar(255) not null,
      creator_id integer not null,
      post_text varchar(2048) not null,
      foreign key (creator_id) references users(user_id)
    )
    """,
    []
  ) end,
  fn -> Sqlx.query!(
    conn,
    ~S"""
    create table if not exists pullrequests (
      pr_id integer primary key,
      post_id integer not null,
      contributor_id integer not null,
      title varchar(255) not null,
      description varchar(1024) not null,
      status varchar(16) not null,
      tags varchar(255) not null,
      date_created_utc varchar(255) not null,
      foreign key (post_id) references posts(post_id),
      foreign key (contributor_id) references users(user_id)
    )
    """,
    []
  ) end,
  fn -> Sqlx.query!(
    conn,
    ~S"""
    create table if not exists comments (
      comment_id integer primary key,
      parent_post_id integer,
      parent_comment_id integer,
      content varchar(255) not null,
      commenter_id integer not null,
      date_created_utc varchar(255) not null,
      post_version integer not null,
    
      foreign key (commenter_id) references users(user_id),
      foreign key (parent_post_id) references posts(post_id),
      foreign key (parent_comment_id) references comments(comment_id)
    )
    """,
    []
  ) end,
  fn -> Sqlx.query!(
    conn,
    ~S"""
    create table if not exists commentratings (
      comment_rating_id integer primary key,
      voter_id integer,
      comment_id integer,
      rating integer,
      foreign key (voter_id) references users(user_id),
      foreign key (comment_id) references comments(comment_id)
    )
    """,
    []
  ) end,
  fn -> IO.puts "Created tables" end,
]
IO.inspect createTablesList

Scenarios

Yaron Minsky’s fangirl

for drop <- dropTablesList do drop.() end
for create <- createTablesList do create.() end
Sqlx.query!(conn, ~S"delete from comments", [])
Sqlx.query!(conn, ~S"delete from pullrequests", []) 
Sqlx.query!(conn, ~S"delete from posts", [])
Sqlx.query!(conn, ~S"delete from users", [])
Sqlx.query!(
  conn,
  ~S"""
  insert into users values (0, 'yminsky_fan', 0)
  """,
  []
)
Sqlx.query!(
  conn,
  ~S"""
  insert into posts values (77, 'Seven Implementations of Incremental', 0, 'Video from Jane Street Tech Talk

  Incremental là một thư viện FRP (functional reactive programming) dùng để viết hàm tính toán tích hợp đạo hàm ‌và giải tích tự động. Anh em nào đam mê Giải tích 1 2, và thích code React, và thích đại số trừu tượng, thì có thể tham khảo.

  # FRP là gì?

  TBA')
  """,
  []
)
Sqlx.query!(
  conn,
  ~S"""
  insert into pullrequests values (6, 77, 0, 'Original post edits', 'Sau ngày 9/3/2016, admin bên Jane Street blog có chỉnh nội dung blog ngầm. Mấy này không ảnh hưởng đến integrity của post nhưng mình vẫn nên bump ref cho chính xác.', 'open', 'doc', '2026-04-28T15:18:26Z')
  """,
  []
)
Sqlx.query!(
  conn,
  ~S"""
  insert into pullrequests values (7, 77, 0, 'Giới thiệu về FRP cho người học mới', 'Post có thể cải thiện thêm bằng cách thêm phần giới thiệu kiến thức. Này mình không có opinion, nhưng OP có thể tham khảo ở đây: https://blog.janestreet.com/breaking-down-frp/', 'closed', 'doc,frp', '2026-03-12T11:08:06Z')
  """,
  []
)
Sqlx.query!(
  conn,
  ~S"""
  insert into pullrequests values (8, 77, 0, 'Giới thiệu về FRP cho người học mới', 'Post có thể cải thiện thêm bằng cách thêm phần giới thiệu kiến thức. Này mình không có opinion, nhưng OP có thể tham khảo ở đây: https://blog.janestreet.com/breaking-down-frp/', 'merged', '', '2026-03-12T11:08:06Z')
  """,
  []
)
Sqlx.query!(
  conn,
  ~S"select users.name, posts.post_title, posts.post_text from users, posts, pullrequests where users.name = 'yminsky_fan'",
  []
)

Comments

for drop <- dropTablesList do drop.() end
for create <- createTablesList do create.() end
RunSql.query(conn,[
  ~S"delete from comments",
  ~S"delete from posts",
  ~S"delete from users",
  ~S"delete from commentratings",
])
RunSql.query(conn,[
  ~S"insert into users values (0, 'rhebed rhamma', 0)",
  ~S"insert into users values (1, 'bukhen bame', 0)",
  ~S"insert into users values (2, 'tandulm Emberspark', 0)",
  ~S"insert into users values (3, 'bao Doomforge', 0)",
  ~S"insert into users values (4, 'nogan kez', 1)",
])
RunSql.query(conn,[
    ~S"""
    insert into posts values (0, 'Seven Implementations of Incremental', 0, 'Video from Jane Street Tech Talk\n\nIncremental là một thư viện FRP (functional reactive programming) dùng để viết hàm tính toán tích hợp đạo hàm ‌và giải tích tự động. Anh em nào đam mê Giải tích 1 2, và thích code React, và thích đại số trừu tượng, thì có thể tham khảo.\n\n# FRP là gì?\n\nTBA')
    """,
])
RunSql.query(conn,[
  ~S"""
  insert into comments values (
  0,
  0,
  null,
  'Dont you worry bout your curly hair',
  0,
  '2026-04-28T15:18:26Z',
  0)
  """,
  ~S"""
  insert into comments values (
  1,
  0,
  null,
  'Clothes that dont quite fit you anywhere',
  1,
  '2026-04-28T15:18:26Z',
  0)
  """,
  ~S"""
  insert into comments values (
  2,
  0,
  null,
  'Voices echo in the gym',
  2,
  '2026-04-28T15:18:26Z',
  0)
  """,
  ~S"""
  insert into comments values (
  3,
  null,
  1,
  'Another girls had her first kiss',
  3,
  '2026-04-28T15:18:26Z',
  0)
  """,
  ~S"""
  insert into comments values (
  4,
  null,
  1,
  'Please dont think too much of it, darling',
  4,
  '2026-04-28T15:18:26Z',
  0)
  """,
])
RunSql.query(conn,[
  ~S"insert into commentratings values (0, 0, 1, -1)",
  ~S"insert into commentratings values (1, 1, 1, -1)",
  ~S"insert into commentratings values (2, 2, 1, -1)",
  ~S"insert into commentratings values (3, 0, 3, 1)",
  ~S"insert into commentratings values (4, 1, 3, 1)",
  ~S"insert into commentratings values (5, 2, 3, 1)",
  ~S"insert into commentratings values (6, 3, 4, 1)",
  ~S"insert into commentratings values (7, 4, 4, 1)",
])
# fetch the comments count of a post
RunSql.query(conn,[
  
# fetch comments count
  ~S"""
  WITH RECURSIVE all_comments AS (
    SELECT comment_id, parent_post_id AS post_id
    FROM comments
    WHERE parent_post_id IS NOT NULL
  
    UNION ALL
  
    SELECT n.comment_id, an.post_id
    FROM comments n
    INNER JOIN all_comments an ON n.parent_comment_id = an.comment_id
  )
  SELECT
    COUNT(comment_id) as total_comment_count
  FROM all_comments
  where post_id = 0
  """,
  
# fetch the comments of a post
  ~S"""
  WITH commentsumratings as (
    SELECT comment_id, SUM(CASE WHEN rating = 1 THEN 1 ELSE -1 END) as total
    FROM commentratings
    GROUP BY comment_id
  )
  SELECT
    c.comment_id as id,
    c.content as text,
    MAX(CASE WHEN r.voter_id = 0 THEN r.rating ELSE 0 END) as user_rating,
    CASE WHEN s.total IS NOT NULL then s.total else 0 end as rating,
    c.date_created_utc as timestamp,
    c.post_version as post_vers,
    u.name as author_name,
    u.user_id as author_id,
    u.role as author_role
  FROM comments c 
  LEFT JOIN commentratings r on c.comment_id = r.comment_id
  LEFT JOIN commentsumratings s on c.comment_id = s.comment_id
  LEFT JOIN users u on c.commenter_id = u.user_id
  where c.parent_post_id = 0
  group by id
  """,
# fetch the replies of a comment
  ~S"""
  WITH commentsumratings as (
    SELECT comment_id, SUM(CASE WHEN rating = 1 THEN 1 ELSE -1 END) as total
    FROM commentratings
    GROUP BY comment_id
  )
  SELECT
    c.comment_id as id,
    c.content as text,
    MAX(CASE WHEN r.voter_id = 0 THEN r.rating ELSE 0 END) as user_rating,
    s.total as rating,
    c.date_created_utc as timestamp,
    c.post_version as post_vers,
    u.name as author_name,
    u.user_id as author_id,
    u.role as author_role
  FROM comments c 
  LEFT JOIN commentratings r on c.comment_id = r.comment_id
  LEFT JOIN commentsumratings s on c.comment_id = s.comment_id
  LEFT JOIN users u on c.commenter_id = u.user_id
  where c.parent_comment_id = 1
  group by id
  """,
  ~S"Select * from comments",
])

Other notebooks:

Back