Another working relationship sample

use lib "lib";
use Red;

model Person {...}

model Post is rw {
    has Int     $.id        is serial;
    has Int     $!author-id is referencing{ Person.id };
    has Str     $.title     is column{ :unique };
    has Str     $.body      is column;
    has Person  $.author    is relationship{ .author-id };
    has Bool    $.deleted   is column = False;
    method delete { $!deleted = True; self.^save }
}

model Person is rw {
    has Int  $.id            is serial;
    has Str  $.name          is column;
    has Post @.posts         is relationship{ .author-id };
    method active-posts { @!posts.grep: not *.deleted }
}

my $*RED-DEBUG = True;
my $*RED-DB = database "SQLite";

Person.^create-table;
Post.^create-table;

my $p  = Person.^create: :name<Fernando>;

my $post = $p.posts.create: :title<Bla>, :body<BlaBle1>;

$p.posts.create: :title<Ble>, :body<BlaBle2>;

say $p.posts.map: *.title;

$post.delete;

say $p.active-posts.map: *.id;

Output:

SQL : CREATE TABLE person(
   id integer NOT NULL primary key autoincrement,
   name varchar(255) NULL
)
SQL : CREATE TABLE post(
   body varchar(255) NULL,
   id integer NOT NULL primary key autoincrement,
   author_id integer NULL references person(id),
   deleted integer NULL,
   title varchar(255) NULL
)
SQL : INSERT INTO person(
   name
)
VALUES(
   'Fernando'
)
SQL : SELECT
   id , name 
FROM
   person
WHERE
   id = last_insert_rowid()
LIMIT 1
SQL : INSERT INTO post(
   author_id,
   title,
   body,
   deleted
)
VALUES(
   1,
   'Bla',
   'BlaBle1',
   0
)
SQL : SELECT
   body , id , author_id as "author-id", deleted , title 
FROM
   post
WHERE
   id = last_insert_rowid()
LIMIT 1
SQL : INSERT INTO post(
   title,
   deleted,
   body,
   author_id
)
VALUES(
   'Ble',
   0,
   'BlaBle2',
   1
)
SQL : SELECT
   body , id , author_id as "author-id", deleted , title 
FROM
   post
WHERE
   id = last_insert_rowid()
LIMIT 1
SQL : SELECT
   title as "data"
FROM
   post
WHERE
   author_id = 1
(Bla Ble)
SQL : UPDATE post SET
   deleted = 1
WHERE id = 1
SQL : SELECT
   id as "data"
FROM
   post
WHERE
   author_id = 1 AND (deleted == 0 OR deleted IS NULL)
(2)