SQL => PilDB

This document is intended to be a guide for those who, like me, come from a SQL background and are transitioning to using PilDB. I'll try to keep it as short and practical as possible, but I'm building it as I learn (with lots of help from the community) so as of now, it's probably kind of a mess.

The first thing to remember is that this is not a relational database. In many ways it's easier, but it takes some getting used to.

DDL => E/R

PilDB has no DDL. Indeed, there are no tables, no rows and no columns. Instead, there are Entities and Relations. You can think of these as classes in an ORM, but without the SQL under the hood.

It's tempting to think of Entities as tables and Relations as columns, but there are some gotchas with that approach.

What I'll do here instead is take a task (CRUD) oriented approach, staring with defining Entities. Thankfully, this is already pretty well documented here.

Create: Insert => new

This is best done by instantiating an +Entity class with the new keyword. Use put> to set values for individual relations (it's ok to think "columns" here), and commit to save the new object.

Validation

This is done manually, ideally before creating the external symbol with new. Symbols created and rolled back are allocated space which is not reclaimed immediately.

The various +relation classes all have a mis> method that will validate supplied values. Note: the value being validated must be supplied to the mis> method. Unlike in typical ORM's, objects do not self validate (though I'm working on possibly changing that).

Read: Select => db and search

See here and here respectively.

db returns a single object, whereas search returns a query structure which can then be iterated on (also with search). search is probably the closest in function to select in SQL.

For aggregate functions, see group and the various links in its "see also."

Update => Nothing!

You can just grab objects, modify them, and commit. No special syntax needed.

Delete => zap

see here

https://picolisp.com/wiki/?sql2pildb

23dec23

pablo_escoberg