Help language development. Donate to The Perl Foundation

DB::ORM::Quicky zef:tony-o last updated on 2021-02-15
# Quicky ORM

What is it?  It's a lazy ORM that I threw together because I end up prototyping a lot of things before building them out with proper schemas.  So, it's meant for lazy column typing and minimal code from the user side.

## How it works

The DB::ORM::Quicky::Model (you'll never have to instantiate this object) degrades column types in the order of Int, Num, Str to whatever an equivalent type is in the selected DB.  Essentially to `integer`, `float`, `varchar` columns.  `varchar` type columns auto resize if the width isn't great enough to hold the requested value.

The model also tracks what columns were changed and *only* updates those fields.

## Example - CRUD (Create Read Update Delete)

For the examples, I'll use SQLite and keep comments to a minimum.

### Depends

### [C]rud

use DB::ORM::Quicky;

my $orm =;

  driver  => 'SQLite', 
  options => %( 
    database => 'local.sqlite3',

# the `users` table does NOT exist yet.
my $newuser = $orm.create('users'); #this is creating a new 'row', not necessarily a new table 

# the `users` table exists with no columns or just a `DBORMID` 
#   column (only in SQLite) yet.

  username => 'tony-o',
  password => 'top secret',
  age      => 6,
  rating   => 'lame',
  decimal  => 6.66,

$;  #at this point all required columns are checked or created

### c[R]ud

my $usersearch = $'users', { rating => 'lame' });

my @users = $usersearch; #array of all users with 'lame' rating

for $ -> $user { ... }

"User count: {$usersearch.count}".say;

### cr[U]d

for $ -> $user { 
    joindate => time, #decided we want to track when a user signed up

### cru[D]

$'users', { }).delete; #delete all of our users

## More "Advanced" Querying

The ORM can take a lot of different types of values.  The usual example by code follows:

$'table', {
  '-or' => [ #-and is also valid
      username => ['user1', 'user2']
      joindate => ('-gt' => time - 5000), # -gt and -lt both work
    '-and' => [
      rating  => 'lame',
      decimal => ('-lt' => 50),
#       (username = 'user1' or username = 'user2') 
#    OR (joindate > ?)
#    OR (rating = 'lame' and decimal < 50);
#  with ? = (time - 5000) 

$'table', {
  -raw => ' strftime(\'%Y%m%d\', joindate) = strftime(\'%Y%m%d\', \'now\') ' 
#  SELECT * FROM table WHERE strftime('%Y%m%d', joindate) = strftime('%Y%m%d', 'now'); 

### Joining Tables

my $orm = qw<initialize your orm as above>;

# initialize some data
my $user = $orm.create('user');
$user.set('username', 'user1');
$user.set('password', 'user1-pass!');
$user.set('source', 'facebook');
$; #$ is now an actual value, yay

my $profile = $orm.create('profile');
$profile.set('name', 'tim tow dee');
$profile.set('uid', $;
$profile.set('source', 'facebook');

# here we'll query them as one unit
my @users = $'user', { #user table will be our main table
  '-join' => {
    '-table' => 'profile', #join the profile table to user
    '-type'  => 'inner',   #user an inner join, the default is 'left outer'
    '-on'    => {
      '-and' => {
        'uid' => 'DBORMID', #these are autoquoted where the key from the pair is quoted for the joining table and the value is quoted for the main table
                            #you can also use things like a pair here, ie: ('-lt' => 'some other column in user table')
        'source' => 'source', #becomes "profile"."source" = "user"."source" in the SQL 
  '"profile"."name"' => ('-ne' => ''), #normal 'where' parameters, notice that quoting the table and field name for the joined table *may* be necessary

for my $user (@users) {
  $user.get(qw<any field from either the profile or user table here>);

The way that the internals work on the `-on` key value pairs is that the `.key` is from the table to be joined, and the `.value` for the parent table. So, the pair of `'uid' => 'DBORMID'` translates to `profile.uid` and `user.DBORMID`, respectively.  You can avoid this behavior by providing the table names as part of the field, IE `'profile.uid' => 'user.DBORMID'`


* There isn't a mechanism to use a raw value in the `'-on'` section of the join.  
* There is also only one join possible right now.  

Both of those features are being worked on.

## Bugs, comments, feature requests? 

Yes, there are probably bugs.  Put 'em in the github bugs area or catch me in #perl6 on freenode.

## License

Whatever, it's free.  Do what you want with it.

###### Other crap