Perl6 DBIish wrapper for SQLite queries

README.md

Requirements

• DBIish

As an Ubuntu 18.04 user, I also needed to install some C libraries in order to get DBIish to install using zef. If you encounter this problem too, run the following command and try again:

sudo apt -y install libffi-dev libtommath-dev libatomic-ops-dev libuv-dev

Files

I'm using my MessageRepository here as an example. In my schema, there is a table called "message", so in order to connect to it, I have a repository class for that table with a reference to the $table variable from the abstract, so it knows where the queries should be applied. You may also notice a Service file that simply provides the database connection information. Also that if an exception is caught it is just returned. In my project, I use an entity system to manage errors and data travelling between classes so if you want to see how I did that you can find a working example in my Telegram-Relay-Bot-Perl6 project.

MessageRepository.pm6

#usr/bin/perl6

use v6;
need Repository::AbstractRepository;

class MessageRepository does AbstractRepository
{
    method new ()
    {
        self.bless(table => "message");
    }
}

MessageService.pm6

...
need Module::Message::MessageRepository;

class MessageService
{
    has $!messageRepository = MessageRepository.new;
}

Usage

INSERT

This will return the ID of the added row in an array, even if there's only one

• INSERT INTO message (column) VALUES (0)

  $!messageRepository.insert('column', 0);
  $!messageRepository.insert(%(column => 0));
  $!messageRepository.insert(['column'], [0]);

• INSERT INTO message (columnA, columnB) VALUES (1, 'string')

  $!messageRepository.insert(%(columnA => 1, columnB => 'string'));
  $!messageRepository.insert(['columnA', 'columnB'], [1, 'string']);

Multiple INSERTs can be done by sending an array of hashes

SELECT

This will also add the FROM <table name> ('message' in this case)

• SELECT column

  $!messageRepository.select('column');
  $!messageRepository.select(['column']);

• SELECT columnA, columnB

  $!messageRepository.select('columnA', 'columnB');
  $!messageRepository.select(['columnA', 'columnB']);

WHERE

• WHERE column = 0

  $!messageRepository.where('column', 0);
  $!messageRepository.where('column', '=', 0);
  $!messageRepository.where(%(column => 0));
  $!messageRepository.where(['column' => 0]);

• WHERE columnA = 1 AND columnB = 2

  $!messageRepository.where(%(columnA => 1, columnB => 2));
  $!messageRepository.where(['columnA', 'columnB'], [1, 2]);
  $!messageRepository.where(['columnA', 'columnB'], ['=', '='], [1, 2]);

• WHERE (columnA = 1 AND columnB = 2) OR columnC = 3

  $!messageRepository.where([%(columnA => 1, columnB => 2), %(columnC => 3)]);
  $!messageRepository.where(['columnA', 'columnB'], [1, 2]);
      $!messageRepository.orWhere(['columnC'], [3]);
  $!messageRepository.where(['columnA', 'columnB'], ['=', '='], [1, 2]);
      $!messageRepository.orWhere(['columnC'], ['='], [3]);

orWhere performs the same operation where but prefixes the string with 'OR'

AbstractRepository.pm6

#!/usr/bin/perl6

use v6;
use DBIish;

need Service::Service;

role AbstractRepository
{
    has $.table is rw;
    has $!dbc;
    has Str $!dbs;
    has $!dbe;
    has Str $!whereString;

    submethod BUILD (:$!table)
    {
        my %config = Service.getConfig();
        my Str $database = %config<database>.gist;

        $!dbc = DBIish.connect(
            'SQLite',
            database => $database
        ) // die "Database '$database' not found.";
    }

    submethod END ()
    {
        $!dbc.dispose();
    }

    multi method insert (@rows where { @rows.first.WHAT === any(Pair, Hash) })
    {
        my @ids;
        my @id;

        for @rows -> %row {
            @id = self.insert(%row);

            last if !@id;

            @ids.push: @id[0];
        }

        return @ids;
    }

    multi method insert (%row)
    {
        my @cols;
        my @vals;

        for %row.kv -> $col, $val {
            next if $val === 'NULL';
            @cols.push: "'$col'";

            given $val {
                when Int {
                    @vals.push: $val;
                }
                default {
                    @vals.push: "'$val'";
                }
            }
        }

        my Str $colString = @cols.join(', ');
        my Str $valString = @vals.join(', ');

        $!dbs = qq:to/STATEMENT/;
            INSERT INTO $!table ($colString)
            VALUES ($valString)
        STATEMENT

        try {
            $!dbe = $!dbc.prepare($!dbs);
            $!dbe.execute();
            $!dbe.finish();

            CATCH {
                return "$_";
            }
        }

        # Get last ID
        my $dbs = $!dbc.prepare("SELECT last_insert_rowid()");
        $dbs.execute();
        my @lastId = $dbs.row();

        $dbs.finish();

        return @lastId;
    }

    multi method insert (@columns, @values where { @columns.elems === @values.elems })
    {
        my %row = @columns Z=> @values;
        return self.insert(%row);
    }

    multi method select (*@cols where { $_.all ~~ Str })
    {
        my Str $colString = @cols.join(', ');

        $!dbs = qq:to/STATEMENT/;
        	SELECT $colString
        	FROM $!table
        STATEMENT
    }

    multi method select (@cols)
    {
        self.select(|@cols);
    }

    multi method select ()
    {
        self.select(['*']);
    }

    multi method where (@whereCols, @operators, @matches where { @whereCols.elems === @matches.elems })
    {
        my Int $index = 0;
        my Str $operator;
        $!whereString ~= '(';

        for @whereCols Z @matches -> [$col, $match] {
            $!whereString ~= " AND " if $index = $++;

            $operator = @operators[$index] // '=';
            $!whereString ~= "$col $operator ";

            given $match {
                when Int    { $!whereString ~= "$match"; }
                default     { $!whereString ~= "'$match'"; }
            }
        }
        $!whereString ~= ')';
    }

    multi method where (%matches)
    {
        my @keys = %matches.keys;
        my @vals = %matches.values;
        self.where(@keys, [], @vals);
    }

    multi method where (@matches where { @matches.first.WHAT === any(Pair, Hash) })
    {
        for @matches -> %matches {
            $!whereString ~= " OR " if $++;
            self.where(%matches);
        }
    }

    multi method where (@whereCols, @matches where { @whereCols.elems === @matches.elems })
    {
        self.where(@whereCols, [], @matches);
    }

    multi method where ($whereCol, $operator, $match)
    {
        self.where([$whereCol], [$operator], [$match]);
    }

    multi method where ($whereCol, $match)
    {
        self.where([$whereCol], [], [$match]);
    }

    multi method orWhere (|sig)
    {
        $!whereString ~= ' OR ';
        self.where(|sig);
    }

    method get ()
    {
        $!dbs ~= qq:to/STATEMENT/ if ?$!whereString;
        WHERE $!whereString
        STATEMENT

        try {
            $!dbe = $!dbc.prepare($!dbs);
            $!dbe.execute();

            CATCH {
                return "$_";
            }
        }
    }

    method getAll ()
    {
        my $error = self.get();
        return $error if ?$error;

        my @data = $!dbe.allrows(:array-of-hash);
        $!dbe.finish();
        
        return @data;
    }

    method getFirst ()
    {
        my $error = self.get();
        return $error if ?$error;

        my %data = $!dbe.row(:hash);
        $!dbe.finish();
        
        return %data;
    }
}