#magpiedb

#Current status

• SELECT ... FROM read_csv('...')
• SELECT ... FROM read_jsonl('...')
• WHERE
• aliases and scalar expressions
• aggregates: COUNT, SUM, AVG, MIN, MAX
• GROUP BY
• HAVING
• INNER JOIN
• aggregate queries over joined inputs
• ORDER BY
• LIMIT

• LEFT JOIN
• multiple joins in one query
• persistent tables / on-disk storage
• Parquet / Arrow

#Quick start

moon run cmd/main

moon run cmd/main "SELECT name, age FROM read_csv('fixtures/csv/people.csv') WHERE age > 18"

moon test --target native

#Example queries

SELECT name, age
FROM read_csv('fixtures/csv/people.csv')
WHERE age > 18

SELECT name, age
FROM read_csv('fixtures/csv/people.csv')
ORDER BY age DESC
LIMIT 2

SELECT active, COUNT(*) AS total
FROM read_jsonl('fixtures/jsonl/events.jsonl')
GROUP BY active
HAVING COUNT(*) > 1

SELECT people.name, events.active
FROM read_csv('fixtures/csv/join_people.csv') AS people
JOIN read_jsonl('fixtures/jsonl/join_events.jsonl') AS events
ON people.id = events.id
WHERE events.active = true

SELECT people.name, COUNT(*) AS total
FROM read_csv('fixtures/csv/join_people.csv') AS people
JOIN read_csv('fixtures/csv/join_flags.csv') AS flags
ON people.id = flags.id
GROUP BY people.name
HAVING COUNT(*) > 1

SELECT people.name, COUNT(*) AS total
FROM read_csv('fixtures/csv/join_people.csv') AS people
JOIN read_csv('fixtures/csv/join_flags.csv') AS flags
ON people.id = flags.id
GROUP BY people.name
ORDER BY total DESC, people.name
LIMIT 2

#Library usage

import {
  "codeworm96/magpiedb" @db
}

async fn run_query() -> Result[Unit, @db.DbError] {
  let conn = @db.Database::new().connect()
  let result = conn.query(
    "SELECT COUNT(*) AS total FROM read_csv('fixtures/csv/people.csv')",
  )?

  println(result.schema().columns.length().to_string())
  while true {
    match result.next() {
      Some(row) => println(row.to_string())
      None => break
    }
  }
  Ok(())
}

• Database::new()
• Database::connect()
• Connection::query(sql) (async)
• ResultSet::schema()
• ResultSet::next()

#Data model

• NULL
• BOOLEAN
• BIGINT
• DOUBLE
• VARCHAR

#Development notes

• Preferred target is native.
• The project currently uses moonlex and moonyacc for SQL front-end generation.
• CSV input currently uses xunyoyo/NyaCSV.
• The roadmap lives in PLAN.md.

#License