The year is ending and We have a lot to celebrate! This year we saw the new release (6.d) of our favorite language.
...
So Red is an ORM still under development.
...
So lets create our first table. A table that will store the people participating on our secret santa. To the code:
use Red;
model Person {
has UInt $.id is serial;
has Str $.name is column;
has Str $.email is column;
}
my $*RED-DB = database "SQLite";
Person.^create-table;
Person.^create: :name<Fernando>, :email<fco@aco.com>;
Person.^create: :name<Aline>, :email<aja@aco.com>;
Person.^create: :name<Fernanda>;
Person.^create: :name<Sophia>;
.say for Person.^all.grep(*.email.defined).map: *.name; # Fernando
# AlineFirst things first. use Red; we are using Red after that we can freely use Red.
Red maps relational database to OOP. Each Red class is mapped to a table, each object represents a row.
So, the way we create a model (Red class) is using the model special word.
A model is just a normal class Red does not add any method you didn't explicit create.
So to interact with the database you should use the metaclass.
But lets continue.
It's creating a new model named Person. The name of the table this model represents will be
the same name as the model: "Person". If necessary, you can change
the name of the table with the is table<...> trait.
This model has 3 attributes. 2 of them has a is column trait and one has a is serial
that means the same as is column{ :id, :!nullable, :auto-increment }.
(for a pk without auto increment, is id means is column{ :id, :!nullable })
So every attribute on Person are columns.
The is serial (is column{ :id }) means that its the table's primary key.
After that it's setting a dynamic var ($*RED-DB) for the result of database "SQLite".
Database receive the driver's name and the parameters expected by it.
In this case it's using the SQLite and if you don't pass any argument it will use SQLite in memory
(if you want to use a file named secret-santa.db you can do database "SQLite", :database<secret-santa.db>).
Red uses the var $*RED-DB to know what database to use.
OK, now lets create the table! As I sad before, Red do not add any method you didn't explicitly asked for.
So, to create the table the metaclass' method is used. Person.^create-table is how you create the table.
This will run:
CREATE TABLE person(
id integer NOT NULL primary key AUTOINCREMENT,
name varchar(255) NOT NULL,
email varchar(255) NOT NULL
)That created the table.
Now We should insert some data. We are doing that with another meta method (.^create).
.^create expect the same arguments .new would expect. Each named argument will set a attribute with the same name.
.^create will create a new Person object, save it on database (with .^save: :insert) and return it.
It runs:
INSERT INTO person(
email,
name
)
VALUES(
'fco@aco.com',
'Fernando'
)Every model has a ResultSeq thats a sequence that represents every row on the table. We can get it's ResultSeq
with .^all (or .^rs). ResultSeq has some methods to help you to get information from the table, for example:
.grep will filter the rows (as the Seq.grep do in a normal Seq) but it doesnt do that in memory, it returns a new
ResultSeq with that filter setted. When its iterator is gotten, it runs a SQL query using every thing setted on the
ResultSeq.
On our example, Person.^all.grep(*.email.defined).map: *.name will run a query like:
SELECT
person.name
FROM
person
WHERE
email IS NOT NULLAnd it'll print:
Fernando
Aline
...
Lets modify the code to make it save the wishlist for each one participating of the secret santa:
use Red;
model Person { ... }
model Wishlist {
has UInt $!id is serial;
has Str:D $.name is column;
has Str $.link is column;
has UInt $!wisher-id is referencing{ Person.id };
has Person $.wisher is relationship{ .wisher-id };
}
model Person is rw {
has UInt $.id is serial;
has Str:D $.name is column;
has Str:D $.email is column;
has Wishlist @.wishes is relationship{ .wisher-id }
}
my $*RED-DB = database "SQLite";
Wishlist.^create-table;
Person.^create-table;
my \fernando = Person.^create: :name<Fernando>, :email<fco@aco.com>;
fernando.wishes.create: :name<Comma>, :link<https://commaide.com>;
fernando.wishes.create: :name("perl6 books"), :link<https://perl6book.com>;
fernando.wishes.create: :name("mac book pro"), :link<https://www.apple.com/shop/buy-mac/macbook-pro/15-inch-space-gray-2.6ghz-6-core-512gb#>;
my \aline = Person.^create: :name<Aline>, :email<aja@aco.com>;
aline.wishes.create: :name("a new closet"), :link<https://i.pinimg.com/474x/02/05/93/020593b34c205792a6a7fd7191333fc6--wardrobe-behind-bed-false-wall-wardrobe.jpg>;
my \fernanda = Person.^create: :name<Fernanda>, :email<faco@aco.com>;
fernanda.wishes.create: :name("mimikyu plush"), :link<https://www.pokemoncenter.com/mimikyu-poké-plush-%28standard-size%29---10-701-02831>;
fernanda.wishes.create: :name("camelia plush"), :link<https://farm9.static.flickr.com/8432/28947786492_80056225f3_b.jpg>;
my \sophia = Person.^create: :name<Sophia>, :email<saco@aco.com>;
sophia.wishes.create: :name("baby alive"), :link<https://www.target.com/p/baby-alive-face-paint-fairy-brunette/-/A-51304817>;
say "\n{ .name }\n{ .wishes.map({" { .name } => { .link }" }).join("\n").indent: 3 }" for Person.^allThat prints:
Fernando
Comma => https://commaide.com
perl6 books => https://perl6book.com
mac book pro => https://www.apple.com/shop/buy-mac/macbook-pro/15-inch-space-gray-2.6ghz-6-core-512gb#
Aline
a new closet => https://i.pinimg.com/474x/02/05/93/020593b34c205792a6a7fd7191333fc6--wardrobe-behind-bed-false-wall-wardrobe.jpg
Fernanda
mimikyu plush => https://www.pokemoncenter.com/mimikyu-poké-plush-%28standard-size%29---10-701-02831
camelia plush => https://farm9.static.flickr.com/8432/28947786492_80056225f3_b.jpg
Sophia
baby alive => https://www.target.com/p/baby-alive-face-paint-fairy-brunette/-/A-51304817
Now we have a new model Wishlist that refers to a table named withlist.
It has $!id as id, $!name and $!link are columns and there are something new!
has UInt $!wisher-id is referencing{ Person.id }; is the same as
has UInt $!wisher-id is column{ :references{ Person.id } }; that means is a column
that's a foreign key that references the id Person's column. It also has a
has Person $.wisher is relationship{ .wisher-id }; it's not a column, it's a "virtual" field.
the $ sigil means that there are only 1 wisher for a wish. And is relationship expects
a Callable that will receive a model. If it's Scalar it will receive the model. So, in this case
it will Wishlist. The return of the relationsip's Callable must be a column that references some other column.
Lets see how this table is created:
CREATE TABLE wishlist(
id integer NOT NULL primary key,
name varchar(255) NOT NULL,
link varchar(255) NULL,
wisher_id integer NULL references person(id)
)As you can see, no wisher column is created.
The Person model has changed too. Now it has a @.wishes relationship
(has Wishlist @.wishes is relationship{ .wisher-id }). It uses a @ sigil so each Person can have more than on wish.
The Callable passed will receive the type of the Positional attribute (Wishlist on this case) and must return a column
that references some other column.
The table created is the same as before.
We created a new Person as we did before: my \fernando = Person.^create: :name<Fernando>, :email<fco@aco.com>;
but now we use the relationship (wishes) to create a new wish
(fernando.wishes.create: :name<Comma>, :link<https://commaide.com>;).
That runs the following SQL:
INSERT INTO wishlist(
name,
link,
wisher_id
)
VALUES(
'Comma',
'https://commaide.com',
1
)Did you see? wisher_id is 1... 1 is Fernando's id. Once you created the wish from the Fernando's .wishes(),
it already knows that it belongs to Fernando.
And then we define wishes for every person we create.
Then we loop over every Person on database (Person.^all) and print its name and loop over that person's wishes and
print its name and link.
...
Change:
To: