I am trying to design a "data access layer" for my Perl 6 budgeting application. The goal is to have a user store various purchases in a SQLite database, and my application will generate various reports, informing the user of spending habits.
However, I am having trouble doing a "proper" data access layer. In fact, I am wondering if it's even worth it for this application. In any case, I would like to learn how to design it properly "object oriented".
I understand that I want my class to be the table, and the class's attribute to correspond to rows in the table. As it stands, my code doesn't use the class attributes at all, but still works fine.
Is there any reason to use the class attributes at all? I have looked up a few resources, most of which are in Java, and difficult for me to translate over to Perl 6. It seems unnecessarily complicated, but I suspect this is because I don't understand the reasons for this design pattern.
1 #!/usr/bin/env perl6
2
3 use v6;
4 use DBIish;
5
6 constant DB = 'budgetpro.sqlite3';
7 my $dbh = DBIish.connect('SQLite', database => DB);
8
9 $dbh.do('drop table if exists Essential');
10
11 sub create-schema {
12 $dbh.do(qq:to/SCHEMA/);
13 create table if not exists Essential(
14 id integer primary key not null,
15 name varchar not null,
16 price numeric(5,2) not null,
17 quant integer not null,
18 desc varchar not null,
19 date timestamp default (datetime('now'))
20 );
21 SCHEMA
22 }
23
24 create-schema;
25
26 class Item {
27 has $!table = 'Essential';
28 has $.name is rw;
29 has $.price is rw;
30 has $.quant is rw;
31 has Str $.desc;
32
33 method insert($name, $price, $quant, $desc) {
34 my $sth = $dbh.prepare(qq:to/INSERT/);
35 insert into $!table (name, price, quant, desc) values (?,?,?,?)
36 INSERT
37 $sth.execute($name, $price, $quant, $desc);
38 }
39
40 multi method select-all {
41 my $sth = $dbh.prepare(qq:to/SELECT/);
42 select * from $!table
43 SELECT
44 $sth.execute;
45 $sth.allrows(:array-of-hash);
46 }
47
48 multi method select-all($begin, $end) {
49 my $sth = $dbh.prepare(qq:to/SELECT/);
50 select * from $!table where date >= ? and date <= ?
51 SELECT
52 $sth.execute($begin, $end);
53 $sth.allrows(:array-of-hash);
54 }
55
56
57 # Needs accurate implementation
58 multi method total-cost($table, $begin?, $end?) {
59 sub total-price {
60 my $sth = $dbh.prepare(qq:to/SUM/);
61 select sum(price) from $table
62 SUM
63 $sth.execute;
64 $sth.allrows[0];
65 }
66 sub total-quant {
67 my $sth = $dbh.prepare(qq:to/SUM/);
68 select sum(quant) from $table
69 SUM
70 $sth.execute;
71 $sth.allrows[0];
72 }
73 return (total-quant[0] * total-price[0]);
74 }
75
76 multi method total-cost($table, $begin, $end) {
77 my $sth = $dbh.prepare(qq:to/SUM/);
78 select sum(price) from $table where date >= ? and date <= ?
79 SUM
80 $sth.execute($begin, $end);
81 $sth.allrows;
82 }
83 }
84
85 class Essential is Item {}
86
87 class Savings is Item {}
88
89 class Personal is Item {}
EDIT: Example of use-
my ($name, $price, $quant, $desc) = 'Apple', 0.99, 2, 'Delicious apple';
my $item = Essential.new;
$item.insert($name, $price, $quant, $desc);
say $item.select-all;
Output:
({date => 2018-04-02 18:59:46, desc => A delicious apple, id => 1, name => Apple, price => 5.99, quant => 2})
However, I am having trouble doing a "proper" data access layer. In fact, I am wondering if it's even worth it for this application.
This greatly depends on your application, and how many places it has that touches the database.
I understand that I want my class to be the table, and the class's attribute to correspond to rows in the table.
That would be a very unusual mapping. You want the class to represent the table, and each instance (object) of the class would be a row. The attributes correspond to columns in the table.
Then you have some kind of session object that encapsulates the database handle, and then you could write something like
my $item = Item.new(id => 42, name => 'something', price => 240.01, quant => 1, ...);
# to add it to the database, do something like:
$session.add($item);
But, a proper object-oriented data layer abstraction doesn't end there. How do you query the table? How would you write a query over multiple tables? SQLAlchemy (a Python ORM) implements an API that, translated to Perl 6, would look like this:
my $query = $session.query(Item).join(OtherClass).filter(Item.quantity > 10).filter(OtherClass.id <= 25);
for $query.all -> $item {
# do something with $item here
}
I'm not aware of a Perl 6 ORM that would support such things nicely, so it would probably be a lot of work to implement that, and maybe not worth the effort for a small project.
But if you want to get an impression on how a well-designed object-relational mapper (ORM) for a dynamic language looks like, I can highly recommend SQLAlchemy (Python), or DBIx::Class (Perl 5).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With