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 collect

See here and here.

db returns a single object, whereas collect returns a list of objects. collect is probably the closest in function to select in SQL, but performance suffers for large returned datasets. When dealing with those, it's best to use procedural searches with scan and it's bretheren, all linked in the docs.

The difference here is the point at which external symbols get pulled into memory. collect pulls its results into memory when evaluated. scan and iter iterate over their result set with the suppolied function.

Update => Nothing!

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

Delete => zap

see here

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

01oct23

pablo_escoberg