# Dbee

[![Gem Version](https://badge.fury.io/rb/dbee.svg)](https://badge.fury.io/rb/dbee) [![Build Status](https://travis-ci.org/bluemarblepayroll/dbee.svg?branch=master)](https://travis-ci.org/bluemarblepayroll/dbee) [![Maintainability](https://api.codeclimate.com/v1/badges/208b36a1d13751687df9/maintainability)](https://codeclimate.com/github/bluemarblepayroll/dbee/maintainability) [![Test Coverage](https://api.codeclimate.com/v1/badges/208b36a1d13751687df9/test_coverage)](https://codeclimate.com/github/bluemarblepayroll/dbee/test_coverage) [![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)

Dbee arose out of a need for an ad-hoc reporting solution that included:

* serializable queries
* serializable data modeling
* de-coupling from our main ORM (ActiveRecord)
* Rails 5.2.1 and above compatibility

Dbee provides a very simple Data Modeling and Query API's and as such it is not meant to replace a traditional ORM or your data persistence layer, but compliment them.  This library's goal is to output the SQL statement needed and **nothing** more.

Other solutions considered included:

* [Squeel](https://github.com/activerecord-hackery/squeel) - Was in production use up until Rails 5, then saw compatibility issues.
* [BabySqueel](https://github.com/rzane/baby_squeel) - Tested with some success up until Rails 5.2.1, then saw compatibility issues.

Both of these solutions ended up closely coupling our domain data layer to ad-hoc reporting layer.  One of the primary motivations for this library was to completely de-couple the data modeling from persistence modeling.

## Installation

This specific library is the core modeling component of the Dbee framework, but by itself, it not completely usable.  You will need to provide a SQL generator which understands how to convert the data and query modeling to actual SQL.  This library comes with a stub: Dbee::Providers::NullProvider, while the main reference implementation is split out into its own library: [dbee-active_record](https://github.com/bluemarblepayroll/dbee-active_record). Together these two libraries comprise a complete solution.  Refer to the other library for more information on installation.

To install through Rubygems:

````
gem install install dbee
````

You can also add this to your Gemfile:

````
bundle add dbee
````

## Examples

### The Data Model API

Consider the following simple pseudo-schema:

```
TABLE practices (
  id:integer,
  active:boolean (nullable),
  name:string
)

TABLE patients (
  id:integer,
  practice_id:integer,
  first:string,
  middle:string,
  last:string,
  chart_number:string
)

TABLE notes (
  id:integer,
  patient_id:integer,
  note_type:string,
  contents:string
)

TABLE phones (
  id:integer,
  patient_id:integer,
  phone_number_type:string,
  number:string
  )
```

*Note: Do not think too much into the merits of the above schema, it is a contrived and simplified example.*

In this example: a practice has many patients, a patient has many notes, and a patient also has many phones.  It is important to note, though, that a patient can only have one unique phone number per phone number type (such as home, cell, fax, work, etc.)

There are two ways to model this schema using Dbee:

1. code-first
2. configuration-first

#### Code-First Data Modeling

Code-first data modeling involves creating sub-classes of Dbee::Base that describes the tables, columns, and associations.  We could model the above example as:

````ruby
module ReadmeDataModels
  class PhoneNumbers < Dbee::Base
    table :phones
  end

  class Notes < Dbee::Base
  end

  class Patients < Dbee::Base
    association :notes, model: Notes, constraints: {
      type: :reference, name: :patient_id, parent: :id
    }

    association :work_phone_number, model: PhoneNumbers, constraints: [
      { type: :reference, name: :patient_id, parent: :id },
      { type: :static, name: :phone_number_type, value: 'work' }
    ]

    association :cell_phone_number, model: PhoneNumbers, constraints: [
      { type: :reference, name: :patient_id, parent: :id },
      { type: :static, name: :phone_number_type, value: 'cell' }
    ]

    association :fax_phone_number, model: PhoneNumbers, constraints: [
      { type: :reference, name: :patient_id, parent: :id },
      { type: :static, name: :phone_number_type, value: 'fax' }
    ]
  end

  class Practices < Dbee::Base
    association :patients, model: Patients, constraints: {
      type: :reference, name: :practice_id, parent: :id
    }
  end
end

````

*Note: the 'table' directive is optional, and if omitted, the classes name will be turned into snake_case and used.  In the above example you can see we wanted the class name of PhoneNumbers but the table is actually 'phones'*

#### Configuration-First Data Modeling

You can choose to alternatively describe your data model using configuration.  The YAML below is equivalent to the Ruby sub-classes above:

````yaml
name: practices
models:
  - name: patients
    constraints:
      - type: reference
        name: practice_id
        parent: id
    models:
      - name: notes
        constraints:
          - type: reference
            name: patient_id
            parent: id
      - name: work_phone_number
        table: phones
        constraints:
          - type: reference
            name: patient_id
            parent: id
          - type: static
            name: phone_number_type
            value: work
      - name: cell_phone_number
        table: phones
        constraints:
          - type: reference
            name: patient_id
            parent: id
          - type: static
            name: phone_number_type
            value: cell
      - name: fax_phone_number
        table: phones
        constraints:
          - type: reference
            name: patient_id
            parent: id
          - type: static
            name: phone_number_type
            value: fax
````

It is up to you to determine which modeling technique to use as both are equivalent.  Technically speaking, the code-first DSL is nothing more than syntactic sugar on top of Dbee::Model.

### The Query API

The Query API (Dbee::Query) is a simplified and abstract way to model an SQL query.  A Query has the following components:

* fields (SELECT)
* filters (WHERE)
* sorters (ORDER BY)
* limit (LIMIT/TAKE)

One very important concept is that all joins are `LEFT OUTER JOIN's`.  This is an intentional simplification for our key application domain: configurable custom reporting.

#### Key Paths

You use key paths in order to identify columns.  All key paths are relative to the main data model.

#### Sample Queries

Get all practices:

````ruby
query = {
  fields: [
    { key_path: 'id' },
    { key_path: 'active' },
    { key_path: 'name' }
  ]
}
````

Get all practices, limit to 10, and sort by name (descending) then id (ascending):

````ruby
query = {
  fields: [
    { key_path: 'id' },
    { key_path: 'active' },
    { key_path: 'name' }
  ],
  sorters: [
    { key_path: 'name', direction: :descending },
    { key_path: 'id' }
  ],
  limit: 10
}
````

Get top 5 active practices and patient whose name start with 'Sm':

````ruby
query = {
  fields: [
    { key_path: 'name', display: 'Practice Name' },
    { key_path: 'patients.first', display: 'Patient First Name' },
    { key_path: 'patients.middle', display: 'Patient Middle Name' },
    { key_path: 'patients.last', display: 'Patient Last Name' },
  ],
  filters: [
    { type: :equals, key_path: 'active', value: true },
    { type: :starts_with, key_path: 'patients.last', value: 'Sm' },
  ],
  limit: 5
}
````

Get practice IDs, patient IDs, names, and cell phone numbers that starts with '555':

````ruby
query = {
  fields: [
    { key_path: 'id', display: 'Practice ID #' },
    { key_path: 'patients.id', display: 'Patient ID #' },
    { key_path: 'patients.first', display: 'Patient First Name' },
    { key_path: 'patients.middle', display: 'Patient Middle Name' },
    { key_path: 'patients.last', display: 'Patient Last Name' },
    { key_path: 'patients.cell_phone_numbers.phone_number', display: 'Patient Cell #' },
  ],
  filters: [
    { type: :equals, key_path: 'active', value: true },
    {
      type: :starts_with,
      key_path: 'patients.cell_phone_numbers.phone_number',
      value: '555'
    },
  ]
}
````

#### Executing a Query

You execute a Query against a Data Model, using a Provider.  The sample provider: Dbee::Providers::NullProvider is just meant as a stand-in.  You will need to plug in a custom provider for real-world use. [See the reference ActiveRecord plugin implementation here.](https://github.com/bluemarblepayroll/dbee-active_record)

Here are some sample executions based off the preceding examples:

##### Code-First Execution

````ruby
require 'dbee'
require 'dbee/providers/active_record_provider'

class Practices < Dbee::Base; end

provider = Dbee::Providers::ActiveRecordProvider.new

query = {
  fields: [
    { key_path: 'id' },
    { key_path: 'active' },
    { key_path: 'name' }
  ]
}

sql = Dbee.sql(Practices, query, provider)
````

##### Configuration-First Execution

````ruby
require 'dbee'
require 'dbee/providers/active_record_provider'

provider = Dbee::Providers::ActiveRecordProvider.new

model = {
  name: :practices
}

query = {
  fields: [
    { key_path: 'id' },
    { key_path: 'active' },
    { key_path: 'name' }
  ]
}

sql = Dbee.sql(model, query, provider)
````

The above examples showed how to use a plugin provider, see the plugin provider's documentation for more information about its options and use.


## Contributing

### Development Environment Configuration

Basic steps to take to get this repository compiling:

1. Install [Ruby](https://www.ruby-lang.org/en/documentation/installation/) (check dbee.gemspec for versions supported)
2. Install bundler (gem install bundler)
3. Clone the repository (git clone git@github.com:bluemarblepayroll/dbee.git)
4. Navigate to the root folder (cd dbee)
5. Install dependencies (bundle)

### Running Tests

To execute the test suite run:

````bash
bundle exec rspec spec --format documentation
````

Alternatively, you can have Guard watch for changes:

````bash
bundle exec guard
````

Also, do not forget to run Rubocop:

````bash
bundle exec rubocop
````

### Publishing

Note: ensure you have proper authorization before trying to publish new versions.

After code changes have successfully gone through the Pull Request review process then the following steps should be followed for publishing new versions:

1. Merge Pull Request into master
2. Update `lib/dbee/version.rb` using [semantic versioning](https://semver.org/)
3. Install dependencies: `bundle`
4. Update `CHANGELOG.md` with release notes
5. Commit & push master to remote and ensure CI builds master successfully
6. Run `bundle exec rake release`, which will create a git tag for the version, push git commits and tags, and push the `.gem` file to [rubygems.org](https://rubygems.org).

## Code of Conduct

Everyone interacting in this codebase, issue trackers, chat rooms and mailing lists is expected to follow the [code of conduct](https://github.com/bluemarblepayroll/dbee/blob/master/CODE_OF_CONDUCT.md).

## License

This project is MIT Licensed.