Data Access Layer with Perl 6 and DBIish

Question

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})

Help

Answer 1

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).

Inadequate

So, then, it seems the way I have it designed is just fine, since the finished application will probably be small, under 1,000 lines. I can just remove the class attributes and have the methods which operate on the database. – Help

Answer 2

You could try Quicky, an Object Relational Manager that's still kind of raw, but at least saves you the trouble of inserting SQL statements into your code. Recently the same author has posted another ORM, koos, which seems much more polished. It will certainly speed up development with databases.

Dowser