/jagsql

A toy command line tool allowing to write sql queries where you would normally use a combination of join, awk, grep, sed, potentially adding no feature at all with respect to this set of tools smile A project to have fun with: Scala 3 cats & fs2 Advanced functional concepts like recursion schemes.

Primary LanguageScala

Description

A command-line, local SQL engine.

Inspiration:

  • Powershell that can pipe structured objects
  • The amazing LogParser that allows querying log files with sql

If you are looking something more serious, look into nushell (with relies on polars) and duckdb, they are amazing.

This is only a coding experiment to pursue several goals:

  • Play with Scala 3
  • Play with Shapeless 3
  • Play with Recursion schemes
  • Build a rudimentary SQL engine, from scratch, because why not

Example

It is intended to run queries like:

SELECT
    regex_struct(
        /(?<user>[^ ]+)\s+(?<pid>[^ ]+)\s+(?<command>[^ ]+)/,
        col_0) as user
FROM RUN('ps axo user:20,pid,comm') as ps

and

SELECT
    regex_struct(
        /(?:[^ ]+\s+){2}(?<user>[^ ]+)\s+(?:[^ ]+\s+)(?<size>[^ ]+)\s+(?:[^ ]+\s+){3}(?<filename>[^ ]+)/,
        col_0) as user
FROM RUN('ls -la') as ls

but also really useful queries like

SELECT
  1 AS one
  , 1 * 42 AS three
  ,a.b + 1 AS four
  ,a.d -1 AS five
  ,a.d -1 + 1 AS six
  ,max(a.c - 1) + 1 AS seven
FROM
  (SELECT 1 AS b, 1 AS d, 1 AS c) AS a
GROUP BY a.b, a.d - 1

Structure

ERROR: Expressions in GROUP BY clause should not contain aggregation functions

SELECT 1 FROM (SELECT 1 AS bar, 2 AS baz) AS foo GROUP BY foo.bar, array(max(foo.bar))
                                                                         ^^^^^^^^^^^^

Interesting

Esoteric