main.cr

# SPDX-FileCopyrightText: Yoran Heling <projects@yorhel.nl>
# SPDX-License-Identifier: AGPL-3.0-only
require "option_parser"
require "ini"
require "db"
require "pg"
require "csv"
require "json"
require "sqlite3"
require "http/server"
require "compress/gzip"
require "digest/sha256"


CSS_BODY = {{ `gzip -nc style.css`.stringify }}
CSS_PATH = {{ "/" + `sha1sum style.css | head -c 16`.stringify + ".css" }}
FAVICON = {{ read_file "favicon.ico" }}

JS_BODY = {{ `gzip -nc lib/script.js`.stringify }}
JS_PATH = {{ "/" + `sha1sum lib/script.js | head -c 16`.stringify + ".js" }}


config_path = "config.ini"

OptionParser.parse do |parser|
  parser.banner = "Usage: sqlbin [arguments]"
  parser.on("-c PATH", "--config=PATH", "Path to the configation file") {|p| config_path = p}
  parser.on("-h", "--help", "Show this help") do
    puts parser
    exit
  end
end

class PG::Connection
  def conn
    connection
  end
end

class Conf
  getter db : String, storage : String, bind, gnuplot_path : String?, broken_check_interval : UInt32
  getter id_anon, name_anon, role_anon
  getter id_header : String?, name_header : String?, role_header : String?
  getter min_query_time : Float32, check_interval : UInt32, max_age : UInt32
  getter max_table_rows : UInt32, max_plot_rows : UInt32, max_export_rows : UInt32, queries_per_page : UInt32, about : String?

  def initialize(path)
    conf = INI.parse File.open path
    server = conf["server"]? || raise "Missing [server] configuration block"
    @db = server["db"]? || raise "Missing 'db' setting"
    @storage = server["storage"]? || raise "Missing 'storage' setting"
    @bind = server["bind"]? || "tcp://127.0.0.1:8000/"
    @gnuplot_path = server["gnuplot_path"]?
    @broken_check_interval = (server["broken_check_interval"]? || "86400").to_u32? || raise "Invalid value for broken_check_interval"

    user = conf["user"]? || {} of String => String
    @id_anon = user["id_anon"]? || "anonymous"
    @name_anon = user["name_anon"]? || "Anonymous"
    @role_anon = user["role_anon"]? || "editor"
    @id_header = user["id_header"]?
    @name_header = user["name_header"]?
    @role_header = user["role_header"]?

    cache = conf["cache"]? || {} of String => String
    @min_query_time = (cache["min_query_time"]? || "0.05").to_f32? || raise "Invalid value for cache.min_query_time"
    @check_interval = (cache["check_interval"]? || "1800").to_u32? || raise "Invalid value for cache.check_interval"
    @max_age = (cache["max_age"]? || "3600").to_u32? || raise "Invalid value for cache.max_age"

    ui = conf["ui"]? || {} of String => String
    @max_table_rows = (ui["max_table_rows"]? || "10000").to_u32? || raise "Invalid value for ui.max_table_rows"
    @max_plot_rows = (ui["max_plot_rows"]? || "10000").to_u32? || raise "Invalid value for ui.max_plot_rows"
    @max_export_rows = (ui["max_export_rows"]? || "100000").to_u32? || raise "Invalid value for ui.max_export_rows"
    @queries_per_page = (ui["queries_per_page"]? || "100").to_u32? || raise "Invalid value for ui.queries_per_page"
    @about = ui["about"]?
  end
end

class Storage
  @db : DB::Database
  getter xsrf_secret : Bytes

  def initialize(@path : String)
    Dir.mkdir_p @path, 0o700
    Dir.mkdir "#{@path}/db" if !Dir.exists? "#{@path}/db"
    Dir.mkdir "#{@path}/cache" if !Dir.exists? "#{@path}/cache"
    File.write "#{@path}/cache/CACHEDIR.TAG", "Signature: 8a477f597d28d172789f06886806bc55\n" if !File.exists? "#{@path}/cache/CACHEDIR.TAG"
    @db = DB.open "sqlite3://#{@path}/db/db.sqlite3?foreign_keys=ON"
    init_schema
    @xsrf_secret = @db.scalar("SELECT value FROM globals WHERE key = 'xsrf_secret'").as(Bytes)
  end

  private def init_schema
    ver = @db.scalar("PRAGMA user_version").as(Int64)
    if ver == 0
      puts "[storage] Initializing new database at #{@path}"
      @db.exec <<-SQL
      CREATE TABLE users (
        id     text PRIMARY KEY,
        name   text
      ) STRICT
      SQL
      @db.exec <<-SQL
      CREATE TABLE queries (
        id         int PRIMARY KEY,
        visibility int NOT NULL,
        created    int NOT NULL DEFAULT (unixepoch('now')),
        updated    int NOT NULL DEFAULT (unixepoch('now')),
        user       text NOT NULL REFERENCES users(id),
        title      text NOT NULL,
        sql        text NOT NULL,
        params     text NOT NULL
      ) STRICT
      SQL
      ver += 1
    end
    if ver == 1
      @db.exec <<-SQL
      CREATE TABLE globals (
        key    text NOT NULL PRIMARY KEY,
        value  text
      )
      SQL
      @db.exec "INSERT INTO globals VALUES (?, ?)", "xsrf_secret", Random::Secure.random_bytes
      ver += 1
    end
    if ver == 2
      @db.exec "ALTER TABLE queries ADD COLUMN graph int NOT NULL DEFAULT 0"
      ver += 1
    end
    if ver == 3
      @db.exec "ALTER TABLE queries ADD COLUMN copy int"
      ver += 1
    end
    if ver == 4
      @db.exec <<-SQL
      CREATE TABLE queries_tags (
        query  int NOT NULL REFERENCES queries (id) ON DELETE CASCADE ON UPDATE CASCADE,
        tag    text NOT NULL,
        PRIMARY KEY(query, tag)
      ) STRICT
      SQL
      @db.exec %{CREATE INDEX queries__user ON queries (user)}
      @db.exec %{CREATE INDEX queries_tags__tag ON queries_tags (tag)}
      ver += 1
    end
    if ver == 5
      @db.exec "ALTER TABLE queries ADD COLUMN broken_check int"
      @db.exec "ALTER TABLE queries ADD COLUMN broken_since int"
      @db.exec "CREATE INDEX queres__broken_check ON queries (broken_check)"
      ver += 1
    end
    @db.exec "PRAGMA user_version = #{ver}"
  end

  def update_user(id, name)
    @db.exec "UPDATE users SET name = ?2 WHERE id = ?1 AND name <> ?2", id, name
  end

  def get_user(id : String)
    @db.scalar("SELECT name FROM users WHERE id = ?", id).as(String)
  end

  def save_query(query, broken, user_id, user_name, is_admin)
    @db.transaction do |trans|
      db = trans.connection
      db.exec "INSERT INTO users (id, name) VALUES (?, ?) ON CONFLICT (id) DO NOTHING", user_id, user_name

      id = query.save.try(&.to_i64) || Random::Secure.rand(Int64)
      params = query.stored_params
      copy = query.copy.try &.to_i64
      # XXX: While the WHERE clause below will prevent users from overwriting
      # each other's queries, this code still allows someone to save a query
      # with a chosen, non-random, ID. Not really a problem, I guess?
      id = db.scalar(%{\
        INSERT INTO queries (id, visibility, user, title,                                     sql, graph, copy, params, broken_check, broken_since) \
                     VALUES (?1, ?2,         ?3,   coalesce(nullif(?4, ''), 'Unnamed query'), ?5,  ?6,    ?7,   ?8,     unixepoch('now'), #{ broken ? "unixepoch('now')" : "NULL" }) \
        ON CONFLICT (id) DO UPDATE SET \
          updated = unixepoch('now'), \
          visibility = ?2, \
          title = coalesce(nullif(?4, ''), title), \
          sql = ?5, \
          graph = ?6, \
          params = ?8, \
          broken_check = unixepoch('now'), \
          broken_since = #{ broken ? "COALESCE(broken_since, unixepoch('now'))" : "NULL" } \
        #{ is_admin ? "" : "WHERE user = ?3" } \
        RETURNING id\
      }, id, query.vis.value, user_id, query.title, query.input, params["plot"]? ? 1 : 0, copy == id ? nil : copy, params.to_s).as(Int64)

      db.exec "DELETE FROM queries_tags WHERE query = ?1", id
      query.tags.each {|t| db.exec "INSERT INTO queries_tags (query, tag) VALUES (?1, ?2)", id, t}
      id
    end.not_nil!
  end

  def load_query(id)
    @db.query_one %{\
      SELECT q.title, q.sql, q.params, q.visibility, q.user, q.created, q.updated, u.name \
           , q.copy, cq.visibility, cq.title, cq.user, cu.name \
        FROM queries q \
        JOIN users u ON u.id = q.user \
        LEFT JOIN queries cq ON cq.id = q.copy \
        LEFT JOIN users cu ON cu.id = cq.user \
       WHERE q.id = ?}, id,
      as: {
        title: String, sql: String, params: String, visibility: Int32, user_id: String, created: Int64, updated: Int64, user_name: String,
        copy: Int64?, copy_visibility: Int32?, copy_title: String?, copy_user_id: String?, copy_user_name: String?
      }
  end

  def delete_query(id)
    @db.exec "DELETE FROM queries WHERE id = ?1", id
  end

  private def internal_queries(sel : String, order : String? = nil, visibility : Int32? = nil, graph : Bool? = nil, tag : String? = nil, user : String? = nil, search : String? = nil, limit : Int64? = nil, offset : Int64? = nil)
    @db.query "\
      SELECT #{sel} \
        FROM queries q \
        JOIN users u ON u.id = q.user \
       WHERE (1 IN(1,?1,?2,?3,?4,?5,?6))
             #{user ? "AND q.user = ?1" : ""} \
             #{visibility ? "AND q.visibility = ?2" : ""} \
             #{graph == nil ? "" : graph ? "AND graph <> 0" : "AND graph = 0"} \
             #{tag && tag != "" ? "AND EXISTS(SELECT 1 FROM queries_tags t WHERE t.query = q.id AND t.tag = ?3)" : ""} \
             #{search ? "AND (q.title REGEXP ?4 OR q.sql REGEXP ?4)" : ""} \
             #{order ? "ORDER BY #{order}" : ""} \
             #{limit ? "LIMIT ?5" : ""} \
             #{offset ? "OFFSET ?6" : ""}",
       user, visibility, tag, search, limit, offset
  end

  def queries(**args)
    q = internal_queries "COUNT(*)", **args
    q.move_next
    res = q.read(Int64)
    q.close
    res
  end

  def queries(**args, &)
    q = internal_queries "q.id, q.visibility, q.title, q.graph, q.created, q.updated, q.broken_since, q.user, u.name", **args
    q.each do
      yield q.read id: Int64, visibility: Int32, title: String, graph: Int64, created: Int64, updated: Int64, broken_since: Int64?, user_id: String, user_name: String
    end
    q.close
  end

  def query_tags(id : Int64)
    @db.query_all "SELECT tag FROM queries_tags WHERE query = ?1 ORDER BY tag", id, as: String
  end

  def tags(user : String? = nil)
    @db.query_all "\
      SELECT DISTINCT t.tag \
        FROM queries_tags t \
       WHERE #{ user ? "EXISTS(SELECT 1 FROM queries q WHERE q.id = t.query AND (q.visibility == 2 OR q.user = ?1))" : "?1 IS NULL" } \
       ORDER BY tag", user, as: String
  end

  class Cache < IO
    # We're including a buffer here because the Gzip writer is pretty slow on small writes.
    # The Gzip reader is already buffered, but we're just bypassing that one.
    include IO::Buffered

    @wr : Compress::Gzip::Writer?
    @rd : Compress::Gzip::Reader?

    def initialize(writer : Bool, @fd : File)
      if writer
        @wr = Compress::Gzip::Writer.new fd, 3
      else
        @rd = Compress::Gzip::Reader.new fd
      end
    end

    def closed?
      @fd.closed?
    end

    def writer
      @wr != nil
    end

    def unbuffered_write(slice : Bytes) : Nil
      @wr.not_nil!.write slice
    end

    def unbuffered_read(slice : Bytes) : Int32
      @rd.not_nil!.unbuffered_read slice
    end

    def unbuffered_flush
      @wr.not_nil!.flush
      @fd.flush
    end

    def unbuffered_rewind
      raise "Unable to rewind Cache"
    end

    def unbuffered_close
      (@wr || @rd).not_nil!.close
      @fd.close
    end

    def cancel
      return if closed?
      (@wr || @rd).not_nil!.close
      @fd.truncate 0 if writer
      @fd.close
    end
  end

  # Open a cache file and return an IO object.
  # If .writer is false, then the cache already exists and the IO can be read
  # from, otherwise the cache has just been created and the IO should be
  # written to.  This approach attempts to avoid the thundering herd problem
  # with locks, letting any concurrent readers wait until the write is done.
  # Not quite perfect, though:
  # - If the writer doesn't complete the write for some reason, without
  #   cancelling or raising an error, readers will end up with an incomplete
  #   cache file. This persists until the cache expires.
  # - There's a race condition on checking whether this instance is the writer,
  #   there is a tiny chance that multiple processes will write the same cache.
  #   Not a problem in terms of correctness, just not great for performance.
  # Cache files are transparently compressed. The writer can call .cancel at
  # any time to drop this cache file.
  def cache(id)
    fn = "#{@path}/cache/#{Digest::SHA256.new.update(id).hexfinal[0..31]}"
    # Properly fixing the race conditions requires O_EXCL, https://github.com/crystal-lang/crystal/issues/7857
    10.times do
      # Read attempt.
      begin
        rd = File.new fn, "r"
      rescue
      else
        rd.flock_shared
        # It's possible that we obtain this read lock before the writer got
        # their exclusive lock, or that the writer has crashed and didn't write
        # anything.  In both cases we're reading an empty file, which is not
        # useful, so close and continue.
        # (Note: a properly written empty cache will still have a gzip header)
        if rd.size == 0
          rd.close
        else
          return Cache.new false, rd
        end
      end

      wr = File.open(fn, "w")
      # If we can't get an exclusive lock without blocking, something happened:
      # - Another writer is active, in which case we can just go back and read the file.
      # - Or a reader opened the file and locked for reading before we could write anything.
      begin
        wr.flock_exclusive blocking: false
      rescue
        next
      end
      return Cache.new true, wr
    end
    raise "Unable to open '#{fn}' after 10 attempts"
  end

  def cache_cleanup(max_age)
    t1 = Time.instant
    oldest = Time.utc - max_age
    num, size = 0, 0
    Dir.glob("#{@path}/cache/????????????????????????????????") do |fn|
      begin
        nfo = File.info(fn)
        if nfo.modification_time < oldest
          size += nfo.size
          num += 1
          File.delete fn
        end
      rescue ex
        puts "Error deleting '#{fn}': #{ex.message}"
      end
    end
    puts "[cache] Cleaned up #{num} files and #{size} bytes in #{(Time.instant-t1).total_seconds} seconds"
  end

  # Returns an {Int,String} for the next query to check (id,sql).
  # Sleeps if there's nothing (yet) to check.
  def next_broken(check_interval)
    loop do
      begin
        id, sql, last_check = @db.query_one "SELECT id, sql, broken_check FROM queries ORDER BY broken_check LIMIT 1", as: {Int64, String, Int64?}
      rescue
        # No rows -> empty database. Newly saved queries are already checked,
        # so we won't have to check again for at least check_interval.
        sleep check_interval.second
        next
      end
      return {id,sql} unless last_check
      now = Time.utc.to_unix
      return {id,sql} if last_check + check_interval < now
      sleep ((last_check + check_interval) - now).clamp(1, check_interval).second
    end
  end

  def save_broken(id, broken)
    @db.exec "\
      UPDATE queries \
         SET broken_check = unixepoch('now') \
           , broken_since = #{ broken ? "COALESCE(broken_since, unixepoch('now'))" : "NULL"} \
       WHERE id = ?1", id
  end
end

class PGTypes
  @type_names = Hash(Int32, String).new

  def initialize(@db : DB::Database)
  end

  def [](oid : Int32) String
    @type_names.put_if_absent(oid) do
      @db.scalar("SELECT typname FROM pg_catalog.pg_type WHERE oid = $1", oid).as(String)
    end
  end
end

class Query
  getter input, title, vis, tags : Array(String), save : Id?
  getter plot, gw, gh

  @plot : String = ""
  @gw : Int32 = 800
  @gh : Int32 = 600

  # string when viewing, nil when editing
  getter id : Id?, created : Time?, updated : Time?
  getter user_id : String?, user_name : String?

  # 'copy' can be set from a param, used when saving.
  # The other fields are only set when viewing.
  getter copy : Id?, copy_vis : Visibility?, copy_title : String?, copy_user_id : String?, copy_user_name : String?

  enum Visibility
    Private
    Unlisted
    Public
  end

  struct Id
    def initialize(@id : Int64); end
    def initialize(s : String); @id = s.to_u64(16).to_i64!; end
    def self.from_s?(s : String?); s =~ /^[0-9a-f]{16}$/ ? new s : nil; end
    def to_s(io); io.printf "%016x", @id.to_u64!; end
    def to_s; String.build {|b| to_s b}; end
    def to_i64; @id; end
  end

  def initialize(params : URI::Params)
    @input = params["sql"]? || ""
    @title = params["title"]? || ""
    @vis = (params["vis"]? || "").to_i?.try {|v| v >= 0 && v <= 2 ? Visibility.new v : Visibility::Public} || Visibility::Public
    @save = params["save"]?.try {|v| Id.from_s? v}
    @copy = params["copy"]?.try {|v| Id.from_s? v}
    @tags = (params["tags"]?||"").split(/[\s,]+/).map(&.downcase).reject!(/[^a-z0-9\/_-]/).reject ""
    load_params params
  end

  def initialize(storage : Storage, id : String)
    id = Id.new id
    q = storage.load_query id.to_i64
    @id = id
    @title = q[:title]
    @input = q[:sql]
    @vis = Visibility.new q[:visibility]
    @tags = storage.query_tags id.to_i64
    @user_id = q[:user_id]
    @user_name = q[:user_name]
    @created = Time.unix q[:created]
    @updated = Time.unix q[:updated]
    @copy = q[:copy].try {|v| Id.new v}
    @copy_vis = q[:copy_visibility].try {|v| Visibility.new v}
    @copy_title = q[:copy_title]
    @copy_user_id = q[:copy_user_id]
    @copy_user_name = q[:copy_user_name]
    load_params URI::Params.parse(q[:params])
  end

  # For query params shared between read-only and editable queries
  private def load_params(params)
    @plot = params["plot"]? || ""
    @gw = ((params["gw"]? || "800").to_i32? || 800).clamp 50, 3000
    @gh = ((params["gh"]? || "600").to_i32? || 600).clamp 50, 3000
  end

  def sql
    @input.strip.gsub(/;$/, "") # Not very reliable, won't catch ';-- comment here'
  end

  # Fields stored in the "params" column
  def stored_params
    p = URI::Params.new
    if self.plot != ""
      p["plot"] = self.plot
      p["gw"] = self.gw.to_s
      p["gh"] = self.gh.to_s
    end
    p
  end

  # Fields passed around in our form (excluding "sql")
  def form_params
    p = stored_params
    p["vis"] = vis.value.to_s if vis != Visibility::Public
    p["title"] = title if title != ""
    save.try {|s| p["save"] = s.to_s}
    p["tags"] = tags.join ", " unless tags.empty?
    p
  end

  def save(ctx)
    Id.new ctx.storage.save_query self, broken?(ctx.db), ctx.user_id, ctx.user_name, ctx.admin?
  end

  class Results
    getter columns : Array(Column)
    getter row : UInt32

    record Column, name : String, oid : Int32, typname : String

    # Cache format:
    #   i32  number of columns
    #   for each column:
    #     i32    type oid
    #     i32    name size
    #     bytes  name
    #     i32    typname size
    #     bytes  typname
    #   for each row:
    #     for each column:
    #       i32    value size (-1 for NULL)
    #       bytes  value

    ENDIAN = IO::ByteFormat::LittleEndian

    def initialize(sql : String, @max_rows : UInt32, @min_query_time : Float32, @conn : PG::Connection, @cache : Storage::Cache, pgtypes : PGTypes)
      @columns = cached? ? cache_query : db_query pgtypes, sql
      @row = 0
      @numcols = 0_i16
    end

    def cached?
      !@cache.writer
    end

    # Rest of the code assumes that a PQError is a user error, i.e. bad query.
    # These are caught and displayed to the user, whereas other error types
    # result in a 500.
    private def pqerr(msg)
      PQ::PQError.new [
        PQ::Frame::ErrorResponse::Field.new :message, msg, 77
      ]
    end

    # Based on PG::Statement.perform_query
    private def db_query(pgtypes, sql)
      @t1 = Time.instant
      conn = @conn.conn
      conn.send_parse_message sql
      conn.send_bind_message [] of PQ::Param, 0 # 0 for text format
      conn.send_describe_portal_message
      conn.send_execute_message
      conn.send_sync_message
      conn.expect_frame PQ::Frame::ParseComplete
      conn.expect_frame PQ::Frame::BindComplete
      frame = conn.read
      case frame
      when PQ::Frame::RowDescription
        fields = frame.fields
      when PQ::Frame::NoData
        cause = conn.read
        case cause
        when PQ::Frame::EmptyQueryResponse
          conn.expect_frame PQ::Frame::ReadyForQuery
          raise pqerr "Empty query"
        when PQ::Frame::CommandComplete
          @conn.close # We can recover from this, but it could've been a SET and we don't want users to fiddle with connection parameters.
          raise pqerr "Not a SELECT, EXPLAIN or SHOW"
        when PQ::Frame::Unknown
          @conn.close # Can't recover
          case cause.type
          when 'G', 'H', 'F' # CopyInResponse, CopyOutResponse, CopyFail
            raise pqerr "Can't use COPY queries here"
          else
            raise "unexpected response, got #{cause}"
          end
        else
          raise "unexpected response, got #{cause}"
        end
      else
        raise "expected RowDescription or NoData, got #{frame}"
      end
      @cache.write_bytes fields.size, ENDIAN
      fields.map do |c|
        @cache.write_bytes c.type_oid, ENDIAN
        @cache.write_bytes c.name.size, ENDIAN
        @cache.write c.name.to_slice
        typname = pgtypes[c.type_oid]
        @cache.write_bytes typname.size, ENDIAN
        @cache.write typname.to_slice
        Column.new c.name, c.type_oid, typname
      end
    rescue ex
      @cache.cancel
      @conn.close unless ex.class == PQ::PQError # Connection is now in an invalid state, don't keep around
      raise ex
    end

    private def cache_query()
      size = @cache.read_bytes Int32, ENDIAN
      Array.new(size) do
        oid = @cache.read_bytes Int32, ENDIAN
        nsize = @cache.read_bytes Int32, ENDIAN
        name = @cache.read_string nsize
        typsize = @cache.read_bytes Int32, ENDIAN
        typname = @cache.read_string typsize
        Column.new name, oid, typname
      end
    end

    private def db_next_row
      t1 = @t1
      if t1
        @cache.cancel if (Time.instant-t1).total_seconds < @min_query_time
        @t1 = nil
      end

      conn = @conn.conn
      if conn.read_next_row_start
        conn.read_i32 # frame size
        @numcols = conn.read_i16
      else
        conn.expect_frame PQ::Frame::ReadyForQuery
        @numcols = -1
      end
    rescue ex
      @cache.cancel
      raise ex
    end

    def next
      return false if @numcols < 0
      raise "Not all values have been read" if @numcols > 0
      if cached?
        @numcols = @cache.peek.size > 0 ? columns.size : -1
      else
        db_next_row
      end
      @row += 1 if @numcols >= 0
      @numcols >= 0
    end

    def db_read
      size = @conn.conn.read_i32
      @cache.write_bytes size, ENDIAN unless @cache.closed?
      if size == -1
        nil
      else
        bytes = @conn.conn.read_bytes size
        @cache.write bytes unless @cache.closed?
        String.new bytes
      end
    rescue ex
      @cache.cancel
      raise ex
    end

    def cache_read
      size = @cache.read_bytes Int32, ENDIAN
      if size == -1
        nil
      else
        @cache.read_string size
      end
    end

    def read : String?
      raise "Nothing to read" if @numcols < 1
      @numcols -= 1
      if cached?
        cache_read
      else
        db_read
      end
    end

    def close
      # Fill the cache till we've reached max_rows
      if !cached? && !@cache.closed?
        while @numcols != -1 && @row <= @max_rows
          while @numcols > 0
            read
          end
          db_next_row
          @row += 1
        end
      end
      @cache.close unless @cache.closed?

      # If we still haven't read all rows yet, there's a good chance someone
      # forgot a LIMIT and we'll be receiving far too many rows. In that case
      # it's likely faster to disconnect than to consume all rows.
      @conn.close if !cached? && @numcols != -1
    end
  end

  # Execute the query, transparently using a cache. The returned object is
  # similar to a DB::ResultSet and must be .close'd after use.
  #
  # Why all this custom code? The cystal-pg ResultSet API uses the binary
  # protocol to transfer query results, which is efficient, but means that we
  # are responsible for decoding and formatting every type, which we can't
  # really be expected to do. I've also experimented with using COPY, which
  # additionally provides a convenient format for caching, but TSV is not as
  # efficient or convenient to parse as a simple binary protocol.
  def execute(ctx)
    cache_rows = {
      ctx.config.max_table_rows+1,
      ctx.config.max_plot_rows,
      ctx.config.max_export_rows
    }.reduce {|acc, v| acc > v ? acc : v}
    Results.new input, cache_rows, ctx.config.min_query_time, ctx.db, ctx.storage.cache("sql2:#{input}"), ctx.pgtypes
  end

  def internal_explain(db)
    # ANALYZE can time-out, hence the fallback to plain EXPLAIN.
    begin
      db.query_all("EXPLAIN ANALYZE #{sql}", as: {String}).join("\n")
    rescue ex
      %{# EXPLAIN ANALYZE failed: #{ex.message}\n\
        # Output below is from EXPLAIN without ANALYZE\n\n\
      } + db.query_all("EXPLAIN #{sql}", as: {String}).join("\n")
    end
  end

  def explain(ctx)
    cache = ctx.storage.cache "explain:#{sql}"
    begin
      if cache.writer
        res = internal_explain ctx.db
        cache << res
        cache.close
        res
      else
        cache.gets_to_end
      end
    ensure
      cache.cancel
    end
  end

  def self.check_broken(db, sql)
    begin
      db.exec("EXPLAIN #{sql}")
    rescue
      return true
    end
    false
  end

  def broken?(db)
    Query.check_broken db, sql
  end

  private def plot_val(val, io)
    if val == nil
      io << %{""}
    elsif val && val.index /[\b\v\f\r\n\t "#]/
      io << '"'
      val.each_byte do |b|
        case b
        when 34; io << '\'' # ", unclear how to escape
        when 8,9,10,11,12,13; io << ' ' # various forms of whitespace and newlines
        else io.write_byte b
        end
      end
      io << '"'
    else
      io << val
    end
  end

  # Write gnuplot commands to io
  def plot(ctx, results, io)
    io << "set term svg size " << self.gw << "," << self.gh << "\n"
    io << "set datafile columnheaders\n"
    eod = Random::Secure.hex
    io << "$data << EOD" << eod << "\n"
    results.columns.each_with_index do |c, i|
      io << ' ' if i > 0
      plot_val c.name, io
    end
    io << '\n'
    while results.row < ctx.config.max_plot_rows && results.next
      results.columns.size.times do |i|
        io << ' ' if i > 0
        plot_val results.read, io
      end
      io << '\n'
    end
    io << "EOD" << eod << "\n"
    io << self.plot
  end
end

# Embedding HTML as strings is garbage, but at least we get to create useful abstractions this way.
# Might want to look into using a HTML builder shard to clean this up, but I'm never enthusiastic about adding dependencies.
module HTML

  # More convenient and efficient alternative to HTML.escape()
  struct Escape
    def initialize(@s : String, @br : Bool); end
    def to_s(io)
      @s.each_byte do |b|
        case b
        when 10; io << (@br ? "<br>" : '\n')
        when 34; io << "&quot;"
        when 38; io << "&amp;"
        when 60; io << "&lt;"
        when 62; io << "&gt;"
        else io.write_byte b
        end
      end
    end
  end

  def self.esc(s, br : Bool = false); Escape.new(s, br); end

  # Icons from lucide.dev
  enum Icon
    ArrowRightCircle
    CircleEqual
    CircleOff
    KeyRound
    LineChart
    Search
    SearchSlash

    def to_s(io)
      io << %{<svg xmlns="http://www.w3.org/2000/svg" class="icon" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2" stroke-linecap="round" stroke-linejoin="round">}
      io << case self
      in ArrowRightCircle then %{<circle cx="12" cy="12" r="10"/><path d="M8 12h8"/><path d="m12 16 4-4-4-4"/>}
      in CircleOff then %{<path d="m2 2 20 20"/><path d="M8.35 2.69A10 10 0 0 1 21.3 15.65"/><path d="M19.08 19.08A10 10 0 1 1 4.92 4.92"/>}
      in CircleEqual then %{<path d="M7 10h10"/><path d="M7 14h10"/><circle cx="12" cy="12" r="10"/>}
      in KeyRound then %{<path d="M2 18v3c0 .6.4 1 1 1h4v-3h3v-3h2l1.4-1.4a6.5 6.5 0 1 0-4-4Z"/><circle cx="16.5" cy="7.5" r=".5"/>}
      in LineChart then %{<path d="M3 3v18h18"/><path d="m19 9-5 5-4-4-3 3"/>}
      in Search then %{<circle cx="11" cy="11" r="8"/><path d="m21 21-4.3-4.3"/>}
      in SearchSlash then %{<path d="m13.5 8.5-5 5"/><circle cx="11" cy="11" r="8"/><path d="m21 21-4.3-4.3"/>}
      end
      io << "</svg>"
    end
  end

  def self.sql_error(io, ex)
    io << "<h2>SQL error</h2>"
    io << "<p>" << esc(ex.message.as(String)) << "</p>"
  end

  def self.gnuplot(path, io, embed = true)
    proc = Process.new command: path, args: {"-d"},
      input: Process::Redirect::Pipe,
      output: Process::Redirect::Pipe,
      error: Process::Redirect::Pipe

    errch = Channel(String).new
    spawn do
      begin
        errch.send proc.error.gets_to_end
      rescue ex
        errch.send ex.message.as(String)
      end
    end

    outch = Channel(Exception?).new
    spawn do
      begin
        IO.copy proc.output, io
        outch.send nil
      rescue ex
        proc.output.skip_to_end
        outch.send ex
      end
    end

    yield proc.input
    proc.input.close

    err = outch.receive
    raise err if err
    err = errch.receive
    raise err if err != ""
    status = proc.wait
    raise "gnuplot exited with error: #{status}" if (!err || err == "") && !status.success?
  rescue ex
    io << "<pre>" << HTML.esc(ex.message.as(String)) << "</pre>" if embed
  ensure
    if proc && !status
      proc.terminate graceful: false
      proc.wait
    end
  end

  struct QueryInfo
    def initialize(@ctx : Context, @query : Query); end
    def self.id; 'q'; end
    def self.label; "Info"; end
    def to_s(io)
      io << %{<h3>SQL</h3><pre><code class="language-sql">} << HTML.esc(@query.input) << %{</code></pre>}
      unless @query.plot.empty?
        io << %{<h3>Gnuplot commands</h3><pre><code class="language-gnuplot">} << HTML.esc(@query.plot) << %{</code></pre>}
      end
    end
  end

  struct QueryTable
    def initialize(@ctx : Context, @query : Query); end
    def self.id; 't'; end
    def self.label; "Table"; end

    def column_attr(c, io)
      case c.typname
      when "int2", "int4", "int8", "oid", "numeric"
        # Excluding floats, as their unpredictable precision often renders right-alignment useless.
        io << %{ class="int"}
      end
    end

    def write_val(c, v, io)
      io << "<td"
      column_attr c, io
      io << '>'

      if !v
        io << "<em>null</em>"
      elsif c.typname == "vndbid"
        io << %{<a href="https://vndb.org/} << HTML.esc(v) << %{">} << HTML.esc(v) << %{</a></td>}
      elsif v =~ /^https?:\/\/[^\s]+$/
        io << %{<a href="} << HTML.esc(v) << %{">} << HTML.esc(v) << %{</a>}
      else
        io << HTML.esc v, true
      end

      io << %{</td>}
    end

    def to_s(io)
      return if @query.sql == ""
      t1 = Time.instant
      results = @query.execute @ctx

      io << %{<table class="stripe results"><thead><tr>}
      results.columns.each do |c|
        io << "<th"
        column_attr c, io
        io << '>' << HTML.esc(c.name) << "</th>"
      end
      io << "</tr></thead><tbody>"

      while results.row < @ctx.config.max_table_rows && results.next
        io << "\n<tr>"
        col = 0
        esc = false
        results.columns.each {|c| write_val c, results.read, io }
        io << "</tr>"
      end
      num = results.row
      io << "\n</tbody></table><p>"
      io << "Limited to " if results.next
      io << num << " result" << (num == 1 ? "" : 's')
      if results.cached?
        io << " (cached)"
      else
        io << " in " << (Time.instant - t1).total_seconds << " seconds."
      end
      io << "</p>"
    rescue ex : PQ::PQError
      return HTML.sql_error io, ex
    ensure
      results.close if results
    end
  end

  struct QueryExplain
    def initialize(@ctx : Context, @query : Query); end
    def self.id; 'e'; end
    def self.label; "Explain"; end
    def to_s(io)
      return if @query.sql == ""
      begin
        res = @query.explain @ctx
      rescue ex
        HTML.sql_error io, ex
      end
      io << "<pre>" << res << "</pre>"
    end
  end

  struct QueryGraph
    def initialize(@ctx : Context, @query : Query); end
    def self.id; 'g'; end
    def self.label; "Graph"; end
    def self.params; ["plot", "gw", "gh"]; end
    def pre_header(io)
      io << %{<button class="hidden" type="submit" name="tab" value="g"></button>}
    end
    def to_s(io)
      path = @ctx.config.gnuplot_path
      unless path
        io << "<p><em>No gnuplot_path configured, graph functionality disabled.</em></p>"
        return
      end
      io << %{\
        <fieldset class="graph-form">\
          <div>\
            <a tabindex="4" href="/about-graph" target="_blank">Help</a>\
            <div>\
              <label>Width<br>\
              <input tabindex="2" type="text" name="gw" value="} << @query.gw << %{">\
              </label>\
              <label>Height<br>\
              <input tabindex="2" type="text" name="gh" value="} << @query.gh << %{">\
              </label>\
              <button tabindex="2" type="submit" name="tab" value="g">Update</button>\
            </div>\
          </div>\
          <div id="plotedit">\
            <textarea tabindex="1" spellcheck="false" placeholder="Gnuplot script goes here" rows="10" name="plot">} << HTML.esc(@query.plot) << %{</textarea>\
          </div>\
        </fieldset>\
      } unless @query.id
      HTML.gnuplot(path, io) do |wr|
        begin
          results = @query.execute @ctx
          @query.plot @ctx, results, wr
        rescue ex : PQ::PQError
          return HTML.sql_error io, ex
        ensure
          results.close if results
        end
      end
    end
  end

  struct QueryExport
    def initialize(@ctx : Context, @query : Query); end
    def self.id; 'x'; end
    def self.label; "Export"; end
    def but(ext, label)
      id = @query.id
      id ? %{<a href="/#{id}.#{ext}">#{label}</a>} : %{<button tabindex="1" type="submit" name="export" value="#{ext}">#{label}</button>}
    end
    def to_s(io)
      has_dup = false
      begin
        results = @query.execute @ctx
        set = Set(String).new
        results.columns.each do |c|
          has_dup = true if set === c.name
          set << c.name
        end
      rescue ex
        return HTML.sql_error io, ex
      ensure
        results.close if results
      end

      io << "\
        <p>Select data export format:</p>\
        <ul>\
          <li>" << but("tsv", "TSV") << " - Tab-separated values, suitable for use with a PostgreSQL COPY command.</li>"
      if has_dup
        io << %{<li><span class="strike">SQL</span> - <small>Not available, your query has duplicate result column names. Rename or alias some columns to enable SQL export.</small></li>}
      else
        io << "<li>" << but("sql", "SQL") << " - TSV with some wrapper SQL statements for direct importing, creates a table named 'sqlbin_import'.</li>"
      end
      io << "<li>" << but("csv", "CSV") << " - Comma-separated values, the shitty format everyone still uses.</li>"
      if has_dup
        io << %{<li><span class="strike">JSON</span> - <small>Not available, your query has duplicate result column names. Rename or alias some columns to enable JSON export.</small></li>}
      else
        io << "<li>" << but("json", "JSON") << " - Convenient for scripting, not very efficient with large results.</li>"
      end
      io << "\
        </ul>\
        <p><small>\
          (Exported data is truncated to the first " << @ctx.config.max_export_rows.format << " rows.)\
        </small></p>"

      if @query.plot != ""
        io << "<p>Or export the graph:</p>\
          <ul>\
            <li>" << but("svg", "SVG") << " - The graph as displayed in your browser.</li>\
            <li>" << but("plot", "Gnuplot script") << " - Full set of gnuplot commands to generate the above SVG.</li>\
          </ul>"
      end
    end
  end

  struct QuerySave
    def initialize(@ctx : Context, @query : Query); end
    def self.id; 's'; end
    def self.label; "Save"; end
    def self.params; ["title","vis","tags","save"]; end
    def pre_header(io)
      # Need to ensure that the first submit button in the form is our save button,
      # otherwise hitting the enter key in an input won't save.
      io << %{<button class="hidden" type="submit" formmethod="post" name="xsrf" value="} << @ctx.xsrf_token << %{"></button>}
    end
    def to_s(io)
      io << %{<fieldset class="form">}
      has_q, has_save = false, false
      @ctx.storage.queries(user: @ctx.user_id, order: "q.updated DESC") do |q|
        unless has_q
          io << %{\
            <fieldset>\
              <label for="save">Destination</label>\
              <select tabindex="1" id="save" name="save">\
                <option value="">New query</option>\
                <optgroup label="Overwrite existing query">}
          has_q = true
        end
        io << %{<option value="} << Query::Id.new(q[:id]) << %{"}
        if Query::Id.new(q[:id]) == @query.save
          io << " selected"
          has_save = true
        end
        io << ">" << HTML.esc(q[:title]) << "</option>"
      end
      if has_q
        io << "</optgroup>"
        if @ctx.admin? && !has_save && @query.save
          io << %{<option value="} << @query.save.not_nil! << %{" selected>Overwrite query id } << @query.save.not_nil! << %{</option>}
        end
        io << "</select></fieldset>"
      end
      io << %{\
        <fieldset>\
          <label for="title">Title</label>\
          <input tabindex="1" type="text" id="title" name="title" value="} << HTML.esc(@query.title) << %{">\
        </fieldset>\
        <fieldset>\
          <label for="tags">Tags</label>\
          <input tabindex="1" type="text" id="tags" name="tags" pattern="^[a-z0-9, /_-]+$" value="} << HTML.esc(@query.tags.join ", ") << %{">\
          <br><small>Optional, comma separated list of tags. Tags must match <code>[a-z0-9/_-]+</code></small>
        </fieldset>\
        <fieldset>\
          <label for="vis">Visibility</label>\
          <select tabindex="1" id="vis" name="vis">\
            <option value="0"} << (@query.vis == Query::Visibility::Private  ? " selected" : "") << %{>Private</option>\
            <option value="1"} << (@query.vis == Query::Visibility::Unlisted ? " selected" : "") << %{>Unlisted</option>\
            <option value="2"} << (@query.vis == Query::Visibility::Public   ? " selected" : "") << %{>Public</option>\
          </select>\
        </fieldset>\
        <fieldset>\
          <button tabindex="1" type="submit" formmethod="post" name="xsrf" value="} << @ctx.xsrf_token << %{">Save</button>\
        </fieldset>\
        </fieldset>}
    end
  end

  struct QueryDelete
    def initialize(@ctx : Context, @query : Query); end
    def self.id; 'd'; end
    def to_s(io)
      return unless @query.user_id == @ctx.user_id || @ctx.admin?
      io << %{\
        <h2>Delete query</h2>\
        <form method="POST" action="/delete">\
        <input type="hidden" name="xsrf" value="} << @ctx.xsrf_token << %{">\
        <p>Are you sure?</p>\
        <p><button type="submit" name="delete" value="} << @query.id << %{">Yes, please delete this query.</button></p>\
        </form>}
    end
  end

  struct QueryPage
    def initialize(@ctx : Context, @query : Query); end
    def html_class; "query" end
    def title
      qt = @query.title.empty? ? "query" : @query.title
      @query.id ? qt : @query.save ? "Edit #{qt}" : @query.copy ? "Copy #{qt}" : "New query"
    end
    def to_s(io)
      tabs = @query.id ?
        [QueryTable, QueryGraph, QueryInfo, QueryExplain, QueryExport, QueryDelete] :
        [QueryTable, QueryGraph, QueryExplain, QueryExport, QuerySave]
      tab = tabs.find(QueryTable) {|t| t.id == @ctx.query_params["tab"]?.try(&.[0]?) }
      tab_obj = tab.new @ctx, @query
      params = @query.form_params
      qid = @query.id
      if qid
        io << %{\
          <h2>} << HTML.esc(@query.title) << %{</h2>\
          <p class="sub">by <a href="/~} << HTML.esc(@query.user_id||"") << %{">} << HTML.esc(@query.user_name||"") << %{</a> \
            on } << @query.created.not_nil!.to_s("%F")
        io << ", last modified " << @query.updated.not_nil!.to_s("%F") if @query.created.not_nil!.date != @query.updated.not_nil!.date
        @query.tags.each do |t|
          io << %{<a class="tag" href="/browse?t=} << HTML.esc(t) << %{">} << HTML.esc(t) << %{</a>}
        end
        if @query.copy && (@query.copy_user_id == @ctx.user_id || @query.copy_vis == Query::Visibility::Public)
          io << %{<br>based on <a href="/} << @query.copy.not_nil! << %{">} << HTML.esc(@query.copy_title.not_nil!) << %{</a>}
          io << %{ by <a href="/~} << HTML.esc(@query.copy_user_id.not_nil!) << %{">} << HTML.esc(@query.copy_user_name.not_nil!) << %{</a>} if @query.copy_user_id != @query.user_id
        end
        io << "</p><nav>"
        tabs.each do |t|
          next if !t.responds_to? :label
          next if t == QueryGraph && @query.plot == ""
          io << %{<a href="/} << qid << (t == QueryTable ? "" : "/"+t.id) << %{" \
            class="tab} << (t == tab ? " sel" : "") << %{">} << t.label << %{</a>}
        end
        io << "<div>"
        if @ctx.user_id == @query.user_id || @ctx.admin?
          io << %{<form action="/} << @query.id << %{/d"><button type="submit">Delete</button></form>}
        end
        io << %{<form action="/">\
          <input type="hidden" name="sql" value="} << HTML.esc(@query.input) << %{">\
          <input type="hidden" name="tab" value="} << tab.id << %{">}
        params.each do |k,v|
          io << %{<input type="hidden" name="} << k << %{" value="} << HTML.esc(v) << %{">}
        end
        io << %{<button type="submit" name="copy" value="} << @query.id << %{">Copy</button>}
        io << %{<button type="submit" name="save" value="} << @query.id << %{">Edit</button>} if @ctx.user_id == @query.user_id || @ctx.admin?
        io << "</form></div></nav>"
      else
        orig_id = @query.title.empty? ? nil : @query.copy || @query.save
        io << %{<p>} << (@query.copy ? "Copying" : "Editing") << %{: <a href="/} << orig_id << %{">} << HTML.esc(@query.title) << %{</a></p>} if orig_id
        io << %{\
        <form>\
          <div id="sqledit">\
          <textarea tabindex="1" spellcheck="false" placeholder="SQL goes here..." name="sql" \
              rows=} << (@query.input.count('\n')+1).clamp(10,20) <<
              ">" << HTML.esc(@query.input) << %{</textarea>\
          </div>\
          <nav>}
        tab_obj.pre_header io if tab_obj.responds_to? :pre_header
        tabs.each do |t|
          next if !t.responds_to? :label
          io << %{<button tabindex="5" type="submit" class="tab} << (t == tab ? " sel" : "") << %{" name="tab" value="} << t.id << %{">}
          io << t.label << %{</button>}
          t.params.each do |p|
            io << %{<input type="hidden" name="} << p << %{" value="} << HTML.esc(params[p]) << %{">} if params[p]?
          end if t != tab && t.responds_to? :params
        end
        if tab.in?(QueryTable, QueryExplain, QueryGraph)
          io << %{<button tabindex="1" type="submit" name="tab" value="} << tab.id << %{">Run</button>}
        end
        io << "</nav>"
      end
      if @query.sql == "" && !tab.in?(QueryInfo, QueryDelete)
        io << "<p><em>Empty query.<em></p>"
      else
        io << tab_obj
      end
      io << %{<input type="hidden" name="copy" value="} << @query.copy.not_nil! << %{">} if @query.copy
      io << "</form>" if qid == nil
      io << %{<script defer src="} << JS_PATH << %{"></script>} if qid == nil || tab == QueryInfo
    end
  end

  class BrowsePage
    def initialize(@ctx : Context); end
    def initialize(@ctx : Context, @user_id : String?, @user_name : String?); end
    def html_class; "browse" end
    def own
      @user_id == @ctx.user_id
    end
    def title
      own ? "My Queries" : (@user_name.try {|v| "Queries by #{v}"}) || "Browse queries"
    end
    def to_s(io)
      params = @ctx.query_params
      begin
        search = params["q"]
        Regex.new search
      rescue
        search = params["q"]?.try {|p| Regex.escape p}
      end
      args = {
        user: @user_id,
        visibility: own || @ctx.admin? ? (params["v"]? || (own ? "" : "2")).to_i?.try(&.clamp 0, 2) : 2,
        graph: params["g"]?.try {|v| v == "" ? nil : v != "0"},
        tag: params["t"]?,
        search: !search || search == "" ? nil : %{(?i:#{search})},
      }
      total = @ctx.storage.queries **args
      per_page = @ctx.config.queries_per_page
      max_page = ((total + per_page - 1) // per_page).clamp(1, 100_000)
      page = ((params["p"]?||"1").to_i?||1).clamp(1, max_page)
      params.delete_all "p"
      io << "<h2>" << HTML.esc(title) << "</h2>"
      if total == 0 && page == 1 && args[:visibility] == (own || @ctx.admin? ? nil : 2) && !args[:search]
        io << "<p>"
        io << (own ? "You have no saved queries." :
               @user_name.try {|v| "#{v} has no saved queries."} || "No queries found.")
        io << "</p>"
        return
      end
      io << %{\
        <form><fieldset class="tableopts">\
          <select name="s">\
            <option value="cd"} << (params["s"]? == "cd" ? " selected" : "") << %{>Newest</option>\
            <option value="ca"} << (params["s"]? == "ca" ? " selected" : "") << %{>Oldest</option>\
            <option value="ud"} << (params["s"]? == "ud" ? " selected" : "") << %{>Recently updated</option>\
            <option value="ta"} << (params["s"]? == "ta" ? " selected" : "") << %{>Title</option>\
          </select>\
          <select name="g">\
            <option value="">- graph -</option>\
            <option value="0"} << (params["g"]? == "0" ? " selected" : "") << %{>No graph</option>\
            <option value="1"} << (params["g"]? == "1" ? " selected" : "") << %{>Has graph</option>\
          </select>}
      tags = @ctx.storage.tags @ctx.admin? ? nil : @ctx.user_name
      unless tags.empty?
        io << %{<select name="t"><option value="">- tag -</option>}
        tags.each do |t|
          io << %{<option value="} << HTML.esc(t) << %{"} << (t == args[:tag] ? " selected" : "") << ">" << HTML.esc(t) << "</option>"
        end
        io << "</select>"
      end
      if own || @ctx.admin?
        io << %{\
          <select name="v">\
            <option value="a">- visibility -</option>\
            <option value="0"} << (args[:visibility] == 0 ? " selected" : "") << %{>Private</option>\
            <option value="1"} << (args[:visibility] == 1 ? " selected" : "") << %{>Unlisted</option>\
            <option value="2"} << (args[:visibility] == 2 ? " selected" : "") << %{>Public</option>\
          </select>}
      end
      io << %{
          <input type="text" name="q" placeholder="Regexp search..." value="} << HTML.esc(params["q"]?||"") << %{">\
          <button type="submit">Update</button>\
        </fieldset></form>}
      if total == 0
        io << "<p>No queries found.</p>"
        return
      end

      if max_page > 1
        io << %{<p class="pagination">Page: }
        max_page.times do |i|
          n = i+1
          params["p"] = n.to_s
          io << %{ <strong>} << n << %{</strong>} if n == page
          io << %{ <a href="?} << params << %{">} << n << %{</a>} if n != page
        end
        io << %{</p>}
      end

      show_vis = own || (@ctx.admin? && args[:visibility] != 2)
      io << %{\
        <table class="stripe">\
          <thead><tr>\
            <th>Created</th>\
            <th>Updated</th>}
      io << "<th>Visibility</th>" if show_vis
      io << "<th>User</th>" if !@user_id
      io << "<th></th>\
            <th>Title</th>\
          </tr></thead>\
          <tbody>"

      order = case params["s"]?
              when "ca" then "q.created"
              when "ta" then "q.title, q.updated DESC"
              when "ud" then "q.updated DESC"
              else "q.created DESC"
              end
      @ctx.storage.queries(**args, order: order, limit: per_page.to_i64, offset: ((page-1)*per_page).to_i64) do |row|
        io << "\n<tr>\
          <td>" << Time.unix(row[:created]).to_s("%F") << "</td>\
          <td>" << Time.unix(row[:updated]).to_s("%F") << "</td>"
        io << "<td>" << Query::Visibility.new(row[:visibility]) << "</td>" if show_vis
        io << %{<td><a href="/~} << row[:user_id] << %{">} << row[:user_name] << %{</a></td>} if !@user_id
        io << %{<td class="icons">}
        if row[:broken_since]
          io << %{<span class="broken" title="Broken since } <<
            Time.unix(row[:broken_since].not_nil!).to_s("%Y-%m-%d") << %{">} <<
            HTML::Icon::CircleOff << "</span>"
        end
        if row[:graph] != 0
          io << %{<a title="Graph" href="/} << Query::Id.new(row[:id]) << %{/g">} << HTML::Icon::LineChart << "</a>"
        end
        io << %{</td><td>\
            <a href="/} << Query::Id.new(row[:id]) << %{">} << HTML.esc(row[:title]) << "</a>"
        @ctx.storage.query_tags(row[:id]).each do |t|
          params["t"] = t;
          io << %{<a class="tag" href="?} << params << %{">} << HTML.esc(t) << "</a>"
        end
        io << "</td></tr>"
      end
      io << "\
          </tbody>\
        </table>"
    end
  end

  class AboutPage
    def initialize(@ctx : Context); end
    def html_class; "about" end
    def title; "About" end
    def to_s(io)
      cfg = @ctx.config.about
      if cfg
        File.open(cfg) {|f| IO.copy f, io}
      else
        io << <<-HTML
        <h2>About</h2>
        <p>
         This website provides a simple and minimalistic interface to query a database with SQL.
         Queries can be saved and shared with others.
         See the <a href="https://dev.yorhel.nl/sqlbin">SQLBin homepage</a> for more information.
        </p>
        <p><small>(This about page can be customized by setting ui.about in the configuration file)</small></p>
        HTML
      end
    end
  end

  class AboutGraphPage
    def initialize(@ctx : Context); end
    def html_class; "about-graph" end
    def title; "Graphing help" end
    def to_s(io)
      path = @ctx.config.gnuplot_path
      unless path
        io << "<p><em>No gnuplot_path configured, graph functionality disabled.</em></p>"
        return
      end
      io << <<-HTML
      <h2>Graphing help</h2>
      <h3>Quick summary</h3>
      <p>
       The graphing feature is based on <a href="http://www.gnuplot.info/">gnuplot</a>.
      </p>
      <p>
       You can enter gnuplot commands in the textarea, output from
       <code>plot</code> commands is automatically rendered to SVG and embedded
       onto the page.  Error messages and other output (such as from the
       <code>stats</code> or <code>print</code> commands) is also rendered on
       the page.
      </p>
      <p>
       Data from your SQL query is available through a <a
       href="http://gnuplot.info/docs_6.0/loc3482.html">datablock</a> named
       <code>$data</code>. Column headers are included, data is truncated
       to the first
      HTML
      @ctx.config.max_plot_rows.format io
      io << <<-HTML
       rows.
      </p>
      <p>
       Gnuplot commands normally allow arbitrary filesystem access and shell
       execution, but these features are not available through this web
       interface and attempting to use them will result in an error.
      </p>
      <h3>Tips &amp; tricks</h3>
      <p>
       Gnuplot is a very flexible piece of software and it may take some time
       getting used to its commands. The <a
       href="https://gnuplot.sourceforge.net/demo_svg_6.0/">online demos</a>
       and <a href="http://gnuplot.info/docs_6.0/Overview.html">reference
       documentation</a> are valuable resources.
      </p>
      <p>
       If your query outputs two numeric columns, the easiest way to get
       started is with the following command:
      </p>
      <pre>
       plot $data
      </pre>
      <p>
       From there on its a mattter of trying out different plot styles,
       configuring the axes and adjusting everything else suit your needs. The
       online demos have plenty of recipes.
      </p>
      <p>
       For a visual reference to the various line types, dash types and
       patterns, the output of the <code>test</code> command is useful:
       <br>
      HTML
      HTML.gnuplot(path, io) { |wr| wr << "set terminal svg size 800,500\ntest\n" }
      io << <<-HTML
      </p>
      <h3>Dates &amp; times</h3>
      <p>
       When plotting the PostgreSQL <code>date</code> type, use the following
       commands:
      </p>
      <pre>
       set xdata time
       set timefmt "%Y-%m-%d"
       # Optional, but more useful than the default
       set format x "%Y-%m-%d"
      </pre>
      <p>
       Gnuplot can't handle the <code>timestamptz</code> format, but you can
       cast values to <code>timestamp</code> instead and then parse with:
      </p>
      <pre>
       set timefmt '"%Y-%m-%d %H:%M:%S"'
      </pre>
      <p>(Yes, that's double quotes inside singly quotes)</p>
      HTML
    end
  end

  class SchemaPage
    def initialize(@ctx : Context); end
    def html_class; "schema" end
    def title; "Database Schema" end

    enum IndexType
      Primary
      Unique
      Partial
      Index

      def self.new(ind)
        case
        when ind[:primary] then Primary
        when ind[:unique] then Unique
        when ind[:partial] then Partial
        else Index
        end
      end

      def icon
        case self
        in Primary then HTML::Icon::KeyRound
        in Unique then HTML::Icon::CircleEqual
        in Partial then HTML::Icon::SearchSlash
        in Index then HTML::Icon::Search
        end
      end

      def label
        case self
        in Primary then "Primary key"
        in Unique then "Unique index"
        in Partial then "Partial index"
        in Index then "Index"
        end
      end
    end

    def table_info(io, oid)
      indices = @ctx.db.query_all %{\
        SELECT c.relname, i.indkey, indnkeyatts, i.indisunique, i.indisprimary, i.indpred IS NOT NULL \
          FROM pg_catalog.pg_index i \
          JOIN pg_catalog.pg_class c ON c.oid = i.indexrelid
         WHERE i.indislive AND i.indrelid = $1 \
         ORDER BY i.indisprimary DESC, i.indisunique DESC, i.indexrelid \
      }, oid, as: {name: String, cols: Array(Int16), nkeys: Int16, unique: Bool, primary: Bool, partial: Bool}

      ind_nums = Hash(IndexType,Int32).new 0, 4
      indices.each {|ind| ind_nums.update(IndexType.new ind) {|v| v+1}}
      ind_names = {} of String => Char

      refs = @ctx.db.query_all %{\
        SELECT c.conname, c.conkey, n.nspname, t.relname, \
               (SELECT array_agg(a.attname ORDER BY k.n) \
                 FROM unnest(c.confkey) WITH ORDINALITY k(num,n) \
                 JOIN pg_catalog.pg_attribute a ON a.attrelid = c.confrelid AND a.attnum = k.num) \
          FROM pg_catalog.pg_constraint c \
          JOIN pg_catalog.pg_class t ON t.oid = c.confrelid \
          JOIN pg_catalog.pg_namespace n ON n.oid = t.relnamespace \
         WHERE c.contype = 'f' AND c.conrelid = $1 \
      }, oid, as: {name: String, cols: Array(Int16), fschema: String, fname: String, fcols: Array(String)}

      even = false;

      @ctx.db.query_each(%{\
        SELECT a.attnum, a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull, col_description($1, a.attnum) \
          FROM pg_catalog.pg_attribute a \
         WHERE NOT a.attisdropped \
           AND a.attnum > 0 \
           AND a.attrelid = $1 \
         ORDER BY a.attnum \
      }, oid) do |rs|
        num, col, type, notnull, comment = rs.read(Int16, String, String, Bool, String?)
        io << %{\n  <tr class="attr} << (even ? " even" : "") << %{">\
          <td></td>\
          <td>} << col << %{</td>\
          <td>} << type << (notnull ? "" : %{<strong title="can be null">?</strong>}) << %{</td>\
          <td>}
        even = !even

        indices.each do |ind|
          off = ind[:cols].index(num) || next
          itype = IndexType.new ind
          io << %{ <span title="} << itype.label << " " << HTML.esc(ind[:name]) << %{">} << itype.icon << "</span>"
          # index names are only interesting for disambiguation, don't output when there's no ambiguity.
          need_name = ind[:cols].size > 1 && ind_nums[itype] > 1
          if need_name || ind[:cols].size > 1
            io << "<sup>"
            io << ind_names.put_if_absent(ind[:name], 'A' + ind_names.size) if need_name
            io << " " if need_name && ind[:cols].size > 1
            io << (off+1) << "/" << ind[:nkeys] if ind[:cols].size > 1
            io << "</sup>"
          end
        end

        io << "</td><td>"

        refs.each do |ref|
          off = ref[:cols].index(num) || next
          io << %{ <span title="Foreign key } << HTML.esc(ref[:name]) << %{">} << HTML::Icon::ArrowRightCircle << "</span>"
          io << "<sup>" << (off+1) << '/' << ref[:cols].size << "</sup>" if ref[:cols].size > 1
          io << %{<a href="#} << HTML.esc(ref[:fschema]) << "." << HTML.esc(ref[:fname]) << %{">} << HTML.esc(ref[:fname]) << "</a>"
          io << '.' << HTML.esc(ref[:fcols][off])
        end

        io << "</td><td>" << HTML.esc(comment||"", true) << "</td></tr>"
      end
    end

    def to_s(io)
      relations = @ctx.db.query_all %{\
        SELECT c.oid, n.nspname, c.relname, c.relkind, c.reltuples, obj_description(c.oid, 'pg_class') \
          FROM pg_catalog.pg_class c \
          JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace \
         WHERE n.nspname !~ '^(pg_|information_schema)' \
           AND relkind IN('r', 'v', 'f', 'p', 'm') \
         ORDER BY n.nspname, c.relname
      }, as: {UInt32, String, String, Char, Float32, String?}
      last_schema = ""
      relations.each do |(oid, schema, name, kind, tuples, comment)|
        if last_schema != schema
          io << "</table>" if last_schema != ""
          last_schema = schema
          io << "<h2>Schema " << HTML.esc(schema) << "</h2>"
          io << "<table>"
        end
        id = HTML.esc "#{schema}.#{name}"
        io << %{\n\
          <tr class="relation" id="} << id << %{">\
            <td colspan=6>} << (
                case kind
                when 'm'; "MATERIALIZED VIEW"
                when 'v'; "VIEW"
                when 'f'; "FOREIGN TABLE"
                when 'p'; "PARTITIONED TABLE"
                else "TABLE"
                end
              ) << " <strong>" << HTML.esc(name) << "</strong> \
              " << (tuples >= 0 ? "(~#{tuples.humanize} rows) " : "") << %{\
              <a href="#} << id << %{">¶</a>\
            </td>\
          </tr>}
        if comment
          io << %{\n <tr class="relation-comment"><td colspan=6>} << HTML.esc(comment, true) << "</td></tr>"
        end
        table_info io, oid
        io << %{\n<tr class="spacer"></tr>}
      end
      io << "</table>" if last_schema != ""
    end
  end

  class Render
    def initialize(@ctx : Context, @page : (AboutPage | AboutGraphPage | BrowsePage | QueryPage | SchemaPage)); end

    def header(io)
      io << %{\
      <header>\
        <h1>SQLBin</h1>\
        <menu>\
          <li><a href="/">New</a></li>\
          <li><a href="/schema">Schema</a></li>\
          <li><a href="/browse">Browse</a></li>\
          <li><a href="/about">About</a></li>\
        </menu>\
        <menu>\
          <li>} << @ctx.user_name << %{</li>\
          <li><a href="/~} << @ctx.user_id << %{">My queries</a></li>\
        </menu>\
      </header>}
    end

    def write(res)
      res.content_type = "text/html; charset=utf8"
      res.headers["Content-Security-Policy"] = "default-src 'none'; img-src 'self'; style-src 'self'; script-src 'self'; form-action 'self'; frame-ancestors 'none'"
      io = @ctx.compress_out
      io << %{\
        <!DOCTYPE html>\
        <html>\
        <head>\
          <link href="} << CSS_PATH << %{" rel="stylesheet">\
          <title>} << @page.title << %{</title>\
        </head>\
        <body>}
      header io
      io << %{<main class="} << @page.html_class << %{">} << @page << "</main></body></html>"
      io.close
    end
  end
end


class Context
  getter config, db, pgtypes, storage, req, res
  getter user_id : String, user_name : String, user_role : Role

  enum Role
    Editor
    Admin

    def self.new(s)
      case s
      when "editor" then Editor
      when "admin" then Admin
      else raise "Invalid role: '#{s}'"
      end
    end
  end

  def initialize(
      @config : Conf,
      @db : PG::Connection,
      @pgtypes : PGTypes,
      @storage : Storage,
      @req : HTTP::Request,
      @res : HTTP::Server::Response)
    @user_id   = config.id_header  .try {|h| @req.headers[h]? || config.id_anon  } || config.id_anon
    @user_name = config.name_header.try {|h| @req.headers[h]? || config.name_anon} || config.name_anon
    @user_role = Role.new config.role_header.try {|h| @req.headers[h]? || config.role_anon} || config.role_anon
    storage.update_user user_id, user_name
  end

  def query_params
    req.query_params
  end

  def admin?
    user_role == Role::Admin
  end

  # Normally I truncate hashes because their default security level is overkill.
  # This time it's the opposite: the truncation is to protect against length extension attacks.
  # (Best practices would obviously suggest I'd use HMAC instead, but when did I ever follow advise?)
  def xsrf_token(offset : Time::Span = 0.days)
    Digest::SHA256.new.update(storage.xsrf_secret).update((Time.utc - offset).to_s("%F")).update(@user_id).hexfinal[0..31]
  end

  def forbidden(message)
    res.status = HTTP::Status::FORBIDDEN
    res.content_type = "text/plain"
    res << message
  end

  def not_found
    res.status = HTTP::Status::NOT_FOUND
    res.content_type = "text/plain"
    res << "404 Page Not Found"
  end

  # Dumb IO::Buffered wrapper around another IO.
  class Output < IO
    include IO::Buffered
    def initialize(@low : IO); end
    def unbuffered_write(slice : Bytes) : Nil; @low.write slice; end
    def unbuffered_read(slice : Bytes) : NoReturn; raise "No reading from an Output"; end
    def unbuffered_flush : Nil; @low.flush; end
    def unbuffered_rewind : NoReturn; raise "No rewinding an Output" end
    def unbuffered_close : Nil; @low.close; end
  end

  def compress_out
    if req.headers["Accept-Encoding"]?.try {|v| v =~ /gzip/}
      res.headers["Content-Encoding"] = "gzip"
      Output.new Compress::Gzip::Writer.new res, 3
    else
      Output.new res
    end
  end

  def export_helper(query, mime, ext)
    io = compress_out
    res.headers["Content-Type"] = mime
    res.headers["Content-Disposition"] = %{attachment; filename="#{query.id || "query"}-#{Time.utc.to_s("%FT%H%M%S")}.#{ext}"}
    begin
      results = query.execute self
      yield io, results
    rescue ex
      res.status = HTTP::Status::INTERNAL_SERVER_ERROR
      res.headers["Content-Type"] = "text/plain"
      res.headers.delete "Content-Disposition"
      io << "Error during query execution: #{ex.message}\n"
    ensure
      results.close if results
    end
    io.close
  end

  def write_tsv(io, results)
    while results.row < self.config.max_export_rows && results.next
      results.columns.size.times do |i|
        io << '\t' if i > 0
        v = results.read
        if v
          v.each_byte do |b|
            case b
            when 8 ; io << "\\b"
            when 9 ; io << "\\t"
            when 10; io << "\\n"
            when 11; io << "\\v"
            when 12; io << "\\f"
            when 13; io << "\\r"
            else io.write_byte b
            end
          end
        else
          io << "\\N"
        end
      end
      io << '\n'
    end
  end

  def export_tsv(query)
    # We *could* just send a COPY command to Postgres directly, but then we
    # need to reimplement the whole caching layer. Seems easier to emulate the
    # way that Postgres writes TSV.
    export_helper(query, "text/tab-separated-values", "tsv") {|io, results| write_tsv io, results}
  end

  def export_sql(query)
    export_helper(query, "application/sql", "sql") do |io, results|
      io << "CREATE TABLE sqlbin_import (\n"
      results.columns.each_with_index do |c, i|
        io << "  " << PG::EscapeHelper.escape_identifier(c.name) << " " << c.typname
        io << ',' if i + 1 < results.columns.size
        io << '\n'
      end
      io << ");\n"
      io << "COPY sqlbin_import FROM STDIN;\n"
      write_tsv io, results
      io << "\\.\n"
    end
  end

  def export_csv(query)
    export_helper(query, "text/csv", "csv") do |io, results|
      csv = CSV::Builder.new io
      csv.row do |r|
        results.columns.each {|c| r << c.name}
      end
      while results.row < self.config.max_export_rows && results.next
        csv.row do |r|
          results.columns.each { r << results.read }
        end
      end
    end
  end

  def export_json(query)
    export_helper(query, "application/json", "json") do |io, results|
      io << "["
      while results.row < self.config.max_export_rows && results.next
        io << ',' if results.row > 1
        io << "\n{"
        results.columns.each_with_index do |c, i|
          io << ',' if i > 0
          c.name.to_json io
          io << ':'
          val = results.read
          unless val
            io << "null"
            next
          end
          # TODO: Arrays? Not super easy to implement, people can always convert to a json type instead.
          case c.typname
          when "json", "jsonb", "int2", "int4", "int8", "oid"
            io << val
          when "float4", "float8", "numeric"
            # Postgres support these special values but JSON doesn't, format these as string instead.
            # I *think* all other values are already formatted correctly.
            case val
            when "NaN", "Infinity", "-Infinity" then val.to_json io
            else io << val
            end
          when "bool"
            io << (val == "t" ? "true" : "false")
          else
            val.to_json io
          end
        end
        io << '}'
      end
      io << "\n]"
    end
  end

  def export_svg(query)
    export_helper(query, "image/svg+xml", "svg") do |io, results|
      path = self.config.gnuplot_path
      raise "Gnuplot path not configured" unless path
      # Our custom sqlbin-gnuplot build strips the XML decl for embedding, let's add it back.
      io << "<?xml version='1.0' standalone='yes'?>\n"
      HTML.gnuplot(path, io, false) do |wr|
        query.plot self, results, wr
      end
    end
  end

  def export_plot(query)
    export_helper(query, "text/plain", "plot") do |io, results|
      query.plot self, results, io
    end
  end

  def export(query, format)
    case format
    when "tsv"; export_tsv query
    when "sql"; export_sql query
    when "csv"; export_csv query
    when "json"; export_json query
    when "svg"; export_svg query
    when "plot"; export_plot query
    else not_found
    end
  end

  def handle_post
    # All POST requests must have an "xsrf" form parameter
    token = req.form_params["xsrf"]? || ""
    unless token == xsrf_token || token == xsrf_token(1.days) || token == xsrf_token(2.days)
      return forbidden "Session expired, please reload the previous page and try again"
    end

    case req.path
    when "/"
      query = Query.new req.form_params
      id = query.save self
      res.status = HTTP::Status::SEE_OTHER
      res.headers["Location"] = "/#{id}"

    when "/delete"
      begin
        query = Query.new storage, req.form_params["delete"]
      rescue ex
        return not_found
      end
      if query.user_id == user_id || admin?
        storage.delete_query query.id.not_nil!.to_i64
        res.status = HTTP::Status::SEE_OTHER
        res.headers["Location"] = "/~#{query.user_id}"
      else
        forbidden "This is not your query"
      end
    end
  end

  def handle_get
    case req.path
    when "/"
      query = Query.new query_params
      if query_params["export"]?
        export query, query_params["export"]
      else
        HTML::Render.new(self, HTML::QueryPage.new self, query).write res
      end

    when "/browse"
      HTML::Render.new(self, HTML::BrowsePage.new self).write res

    when "/about"
      HTML::Render.new(self, HTML::AboutPage.new self).write res

    when "/about-graph"
      HTML::Render.new(self, HTML::AboutGraphPage.new self).write res

    when "/schema"
      HTML::Render.new(self, HTML::SchemaPage.new self).write res

    when /^\/([0-9a-f]{16})(?:\/([qtegxd])|\.(tsv|sql|csv|json|svg|plot))?$/
      begin
        query = Query.new storage, $1
      rescue ex
        return not_found
      end
      if query.vis == Query::Visibility::Private && query.user_id != user_id && !admin?
        forbidden "This query is marked as private"
      elsif $3?
        export query, $3
      else
        req.query_params["tab"] = $2 if $2?
        HTML::Render.new(self, HTML::QueryPage.new self, query).write res
      end

    when /^\/~([^\/]+)$/
      begin
        uname = $1 == user_id ? user_name : storage.get_user $1
      rescue
        return not_found
      end
      HTML::Render.new(self, HTML::BrowsePage.new self, $1, uname).write res

    else
      not_found
    end
  end
end

begin
  config = Conf.new config_path
rescue ex
  puts "Error reading configuration from '#{config_path}': #{ex.message}"
  exit 1
end

db = DB.open config.db

pgtypes = PGTypes.new db
storage = Storage.new config.storage

server = HTTP::Server.new do |context|
  req, res = context.request, context.response
  STDERR.puts "[#{Time.local}] #{req.remote_address} #{req.method} #{req.path}"

  case req.path
  when "/robots.txt"
    res.content_type = "text/plain"
    res << "User-agent: *\nDisallow: /\n"
    next
  when "/favicon.ico"
    res.content_type = "image/vnd.microsoft.icon"
    res.headers["Cache-Control"] = "max-age=31536000"
    res << FAVICON
    next
  when CSS_PATH
    res.content_type = "text/css"
    res.headers["Cache-Control"] = "max-age=31536000"
    res.headers["Content-Encoding"] = "gzip"
    res.print CSS_BODY
    next
  when JS_PATH
    res.content_type = "application/javascript"
    res.headers["Cache-Control"] = "max-age=31536000"
    res.headers["Content-Encoding"] = "gzip"
    res.print JS_BODY
    next
  end

  db.using_connection do |conn|
    ctx = Context.new config, conn.as(PG::Connection), pgtypes, storage, req, res
    case req.method
    when "POST"
      ctx.handle_post
    when "GET", "HEAD"
      ctx.handle_get
    else
      res.status = HTTP::Status::METHOD_NOT_ALLOWED
      res.content_type = "text/plain"
      res << "405 Method Not Allowed"
    end
  end
end

spawn do
  sleep 1.minute
  loop do
    storage.cache_cleanup config.max_age.seconds
    sleep config.check_interval.second
  end
end

spawn do
  loop do
    sleep 1.second
    id, sql = storage.next_broken config.broken_check_interval
    broken = Query.check_broken db, sql
    storage.save_broken id, broken
  end
end if config.broken_check_interval > 0

server.max_headers_size = 262_144 # 256k
server.max_request_line_size = 262_144
server.bind config.bind
server.each_address {|a| puts "Listening on #{a}"}
server.listen
db.close