rom-rb / rom-sql Goto Github PK

View Code? Open in Web Editor NEW

218.0 28.0 91.0 2.69 MB

SQL support for rom-rb

Home Page: https://rom-rb.org

License: MIT License

Ruby 99.69% HTML 0.29% Dockerfile 0.03%

ruby sql rom-rb rubygem postgresql mysql sqlite sqlite3 mariadb mssql

rom-sql's People

Contributors

Stargazers

Watchers

Forkers

kwando chastell aflatter gotar mcls mjtko cflipse larribas pdswan vrish88 draxxxeus jamesmoriarty c0 spscream endash pointlessone colszowka davydovanton tehprofessor muescha jodosha apohllo vladra parndt maximderbin smaximov aarek v-kolesnikov vf19cmi alsemyonov panthomakos peterdavidhamilton romatr krule atsjj janko rykov nwallace teriyakisan rgranadino cutalion joelvh carvid doriantaylor piktur yuszuv abinoam atm-com broadlume brinkschulte deepj waiting-for-dev masanorionishi knazary serhiitroinin ethnt ianks psparrow dnnx rickenharp peterberkenbosch oreklate abrthel olleolleolle j-a-m-l 3d-dev nickpellant renatolond adam12 kokizzu mgiagante arempe93 radar pat jchapron inthememory rewritten katafrakt alex-lairan cygnetise wuarmin isabella232 kseniya82 swilgosz rmehner luismiramirez sbezugliy dgollahon pboling ali-hassan mlibrary

rom-sql's Issues

Fix build for jruby-9000

https://travis-ci.org/rom-rb/rom-sql/jobs/92706354

Problem inferring a primary key when using aggregate

I have a primary key defined in my database schema for two tables. However, using either schema inference, or an explicit id with Types::Int - then I get the error below when using aggregate.

/.rbenv/versions/2.2.5/lib/ruby/gems/2.2.0/gems/sqlite3-1.3.13/lib/sqlite3/database.rb:91:in `initialize': SQLite3::SQLException: near "NULL": syntax error (Sequel::DatabaseError)

The code below demonstrates the problem. If the inferred schema is not used and the explicit version is used instead (the commented section) it also fails.

The only way to get this to work is to use, attribute :id, Types::Int.meta(primary_key: true) for both schemas.

This may be pilot error, but it was very confusing - and appears to be a breaking change with rom-sql 0.9.

Breaking example:

#!/usr/bin/env ruby
require "rom-sql"
require "rom-repository"
require "dry-types"

module Relations
  class Users < ROM::Relation[:sql]
    schema(:users) do
      attribute :id, Types::Int
      attribute :country, Types::String
    end
  end

  class Participants < ROM::Relation[:sql]
    # schema(:participants) do
    #   attribute :id, Types::Int
    #   attribute :name, Types::String
    #   attribute :user_id, Types::Int.meta(foreign_key: true, relation: :users)

    #   associations do
    #     belongs_to :user
    #   end
    # end
    dataset(:participants)
    schema(infer: true) do
      associations do
        belongs_to :user
      end
    end
  end
end

module Repositories
  class Participant < ROM::Repository[:participants]
    relations :users

    def all
      aggregate(:user).to_a
    end
  end
end

config = ROM::Configuration.new(:sql, "sqlite::memory")
config.register_relation Relations::Users
config.register_relation Relations::Participants
container = ROM.container(config)

container.gateways[:default].tap do |gateway|
  migration = gateway.migration do
    change do
      create_table :users do
        primary_key :id
        string :country, null: true
      end

      create_table :participants do
        primary_key :id
        string :name, null: true
        foreign_key :user_id, :users, null: false
      end
    end
  end
  migration.apply gateway.connection, :up
end

connection = container.gateways[:default].connection
connection.execute "INSERT INTO users (country) VALUES ('UK')"
connection.execute "INSERT INTO participants (name, user_id) VALUES ('Fred', 1)"

repo = Repositories::Participant.new(container)

repo.all

Disable relation inference by default

Right now Gateway#schema returns all table names, which is used to automatically set up relations when no explicit relation classes were defined (or only some were defined). This has been biting people all the time when their relation classes were not loaded (due to some misconfiguration issue) and yet relations were available since they were inferred automatically.

We should make this behavior optional, and disabled by default. It's a simple change, ie something like this:

module ROM
  module SQL
    class Gateway < ROM::Gateway
      option :infer_relations, reader: true, default: false, accept: [true, false]

      # ...

       def schema
         @schema ||=  infer_relations ? connection.tables : []
       end
    end
  end
end

Relation's schema doesn't work with custom coercion rules

I have a relation class:

class Relation < ROM::Relation[:sql]                                                       
  dataset :metrics
  register_as :metrics_relation

  schema do                                                        
    attribute :category, Types::String.default { 'default category' }
  end
end

When I try to insert new record into database I get an error:

=> metrics_relation.insert(params_hash.except(:category))
=> Sequel::DatabaseError: Mysql2::Error: Field 'category' doesn't have a default value
from .../gems/sequel-4.37.0/lib/sequel/adapters/mysql2.rb:137:in `query'

infer: true works as expected

I use rom-sql 0.8.0, rom-repository 0.3.1, dry-types 0.8.1

Document custom datasets in rom-sql

There's no information about overridding dataset using a block, ie:

require 'rom-sql'

rom = ROM.container(:sql, 'sqlite::memory') do |config|
  config.gateways[:default].create_table :users do
    primary_key :id
    column :name, String
    column :email, String
  end

  config.relation(:users) do
    schema do
      attribute :id, ROM::SQL::Types::Serial
      attribute :name, ROM::SQL::Types::String
    end

    dataset do |rel|
      select(*rel.schema.attributes.keys).order(:id)
    end
  end
end

rom.relations[:users].insert(name: 'Jane')

puts rom.relations[:users].first.inspect

NoMethodError: undefined method `associate' for ROM::SQL::Association::OneToMany

Setup

The User Repo file.

require 'script/user'

module Script
  class UserRepo < ROM::Repository[:users]
    relations :departments, :roles

    commands :create, update: :by_pk

    def query(conditions)
      users.where(conditions).as(User)
    end

    def by_id(id)
      users.fetch(id)
    end

    def create_with_department(user)
      command(:create, aggregate(:department), aggregate(:role)).call(user)
    end
  end
end

Container-file

require 'rom'
require 'rom/repository'

module Script
  CONTAINER = ROM.container(:sql, 'sqlite::memory') do |conf|
    conf.default.connection.create_table(:users) do
      primary_key :id
      column :name, String, null: false
      foreign_key :department_id, :departments
      foreign_key :role_id, :roles
    end

    conf.default.connection.create_table(:departments) do
      primary_key :id
      column :name, String, null: false
    end

    conf.default.connection.create_table(:roles) do
      primary_key :id
      column :name, String, null: false
    end

    conf.relation(:users) do
      schema(infer: true) do
        associations do
          belongs_to :department
          belongs_to :role
        end
      end
    end

    conf.relation(:departments) do
      schema(infer: true) do
        associations do
          has_many :users
        end
      end
    end

    conf.relation(:roles) do
      schema(infer: true) do
        associations do
          has_many :users
        end
      end
    end
  end
end

Gemfile.lock

GEM
  remote: https://rubygems.org/
  specs:
    byebug (9.0.5)
    coderay (1.1.1)
    concurrent-ruby (1.0.2)
    dry-configurable (0.1.7)
      concurrent-ruby (~> 1.0)
    dry-container (0.4.0)
      concurrent-ruby (~> 1.0)
      dry-configurable (~> 0.1, >= 0.1.3)
    dry-equalizer (0.2.0)
    dry-logic (0.3.0)
      dry-container (~> 0.2, >= 0.2.6)
      dry-equalizer (~> 0.2)
    dry-monads (0.1.1)
      dry-equalizer
    dry-types (0.8.1)
      concurrent-ruby (~> 1.0)
      dry-configurable (~> 0.1)
      dry-container (~> 0.3)
      dry-equalizer (~> 0.2)
      dry-logic (~> 0.3, >= 0.3.0)
      dry-monads (>= 0.0.1)
      ice_nine (~> 0.11)
      inflecto (~> 0.0.0, >= 0.0.2)
    dry-validation (0.9.5)
      concurrent-ruby (~> 1.0)
      dry-configurable (~> 0.1, >= 0.1.3)
      dry-container (~> 0.2, >= 0.2.8)
      dry-equalizer (~> 0.2)
      dry-logic (~> 0.3, >= 0.3.0)
      dry-types (~> 0.8, >= 0.8.1)
    ice_nine (0.11.2)
    inflecto (0.0.2)
    method_source (0.8.2)
    pry (0.10.4)
      coderay (~> 1.1.0)
      method_source (~> 0.8.1)
      slop (~> 3.4)
    pry-byebug (3.4.0)
      byebug (~> 9.0)
      pry (~> 0.10)
    rom (2.0.0)
      concurrent-ruby (~> 1.0)
      dry-equalizer (~> 0.2)
      dry-types (~> 0.8)
      rom-mapper (~> 0.4.0)
      rom-support (~> 2.0)
    rom-http (0.5.0)
      dry-equalizer
      dry-types
      rom (~> 2.0)
      thread_safe
    rom-mapper (0.4.0)
      dry-equalizer (~> 0.2)
      rom-support (~> 2.0.0)
      transproc (~> 0.4.0)
    rom-repository (0.3.1)
      rom (~> 2.0)
      rom-mapper (~> 0.4)
      rom-support (~> 2.0)
    rom-sql (0.8.0)
      dry-equalizer (~> 0.2)
      dry-types (~> 0.8)
      rom (~> 2.0)
      rom-support (~> 2.0)
      sequel (~> 4.25)
    rom-support (2.0.0)
      concurrent-ruby (~> 1.0)
      dry-equalizer (~> 0.2)
      transproc (~> 0.4.0)
      wisper (~> 1.6, >= 1.6.0)
    sequel (4.37.0)
    slop (3.6.0)
    sqlite3 (1.3.11)
    thor (0.19.1)
    thread_safe (0.3.5)
    transproc (0.4.0)
    wisper (1.6.1)

PLATFORMS
  ruby

DEPENDENCIES
  dry-equalizer
  dry-types
  dry-validation
  pry
  pry-byebug
  rom-http
  rom-repository
  rom-sql
  sqlite3
  thor

BUNDLED WITH

Current behaviour

When I run my script I get the following error:

NoMethodError: undefined method `associate' for #<ROM::SQL::Association::OneToMany:0x000000011ed0b0>
/home/user1/.rvm/gems/ruby-2.3.0/gems/rom-sql-0.8.0/lib/rom/sql/plugin/associates.rb:69:in `block in execute'
/home/user1/.rvm/gems/ruby-2.3.0/gems/rom-sql-0.8.0/lib/rom/sql/plugin/associates.rb:68:in `map'
/home/user1/.rvm/gems/ruby-2.3.0/gems/rom-sql-0.8.0/lib/rom/sql/plugin/associates.rb:68:in `each'
/home/user1/.rvm/gems/ruby-2.3.0/gems/rom-sql-0.8.0/lib/rom/sql/plugin/associates.rb:68:in `map'
/home/user1/.rvm/gems/ruby-2.3.0/gems/rom-sql-0.8.0/lib/rom/sql/plugin/associates.rb:68:in `execute'
/home/user1/.rvm/gems/ruby-2.3.0/gems/rom-2.0.0/lib/rom/command.rb:110:in `call'
/home/user1/.rvm/gems/ruby-2.3.0/gems/rom-sql-0.8.0/lib/rom/sql/commands/error_wrapper.rb:12:in `call'
/home/user1/.rvm/gems/ruby-2.3.0/gems/rom-2.0.0/lib/rom/commands/lazy/create.rb:17:in `call'
/home/user1/.rvm/gems/ruby-2.3.0/gems/rom-2.0.0/lib/rom/commands/graph.rb:64:in `block in call'
/home/user1/.rvm/gems/ruby-2.3.0/gems/rom-2.0.0/lib/rom/commands/graph.rb:61:in `map'
/home/user1/.rvm/gems/ruby-2.3.0/gems/rom-2.0.0/lib/rom/commands/graph.rb:61:in `call'
/home/user1/.rvm/gems/ruby-2.3.0/gems/rom-2.0.0/lib/rom/commands/composite.rb:18:in `call'
/home/user1/.rvm/gems/ruby-2.3.0/gems/rom-repository-0.3.1/lib/rom/repository/command_proxy.rb:14:in `call'
/home/user1/project/lib/script/repos/user_repo.rb:18:in `create_with_department'
/home/user1/project/lib/script/cli_runner.rb:25:in `parse_data'
/home/user1/.rvm/gems/ruby-2.3.0/gems/thor-0.19.1/lib/thor/command.rb:27:in `run'
/home/user1/.rvm/gems/ruby-2.3.0/gems/thor-0.19.1/lib/thor/invocation.rb:126:in `invoke_command'
/home/user1/.rvm/gems/ruby-2.3.0/gems/thor-0.19.1/lib/thor.rb:359:in `dispatch'
/home/user1/.rvm/gems/ruby-2.3.0/gems/thor-0.19.1/lib/thor/base.rb:440:in `start'

Expected behaviour

No exception or better error message explaining what I did wrong or documentation how to prevent that error.

schema breaks on code reload

Working in a rails console, I added a new associations entry to a schema block, and reloaded. Exception thrown:

irb(main):003:0> reload!
Reloading...
  ROM[postgres] (0.1ms)  SET standard_conforming_strings = ON  
# ... lots of logging things elided
RuntimeError: can't modify frozen ROM::SQL::Schema
        from /home/flip/.gem/ruby/2.3.0/gems/rom-2.0.0/lib/rom/schema.rb:81:in `finalize!'
        from /home/flip/.gem/ruby/2.3.0/gems/rom-sql-0.8.0/lib/rom/sql/schema.rb:24:in `finalize!'
        from /home/flip/.gem/ruby/2.3.0/gems/rom-2.0.0/lib/rom/setup/finalize/finalize_relations.rb:53:in `build_relation'
        from /home/flip/.gem/ruby/2.3.0/gems/rom-2.0.0/lib/rom/setup/finalize/finalize_relations.rb:25:in `block (2 levels) in run!'
        from /home/flip/.gem/ruby/2.3.0/gems/rom-2.0.0/lib/rom/setup/finalize/finalize_relations.rb:24:in `each'
        from /home/flip/.gem/ruby/2.3.0/gems/rom-2.0.0/lib/rom/setup/finalize/finalize_relations.rb:24:in `block in run!'
        from /home/flip/.gem/ruby/2.3.0/gems/rom-2.0.0/lib/rom/relation_registry.rb:6:in `initialize'
        from /home/flip/.gem/ruby/2.3.0/gems/rom-2.0.0/lib/rom/setup/finalize/finalize_relations.rb:23:in `new'
        from /home/flip/.gem/ruby/2.3.0/gems/rom-2.0.0/lib/rom/setup/finalize/finalize_relations.rb:23:in `run!'
        from /home/flip/.gem/ruby/2.3.0/gems/rom-2.0.0/lib/rom/setup/finalize.rb:93:in `load_relations'
        from /home/flip/.gem/ruby/2.3.0/gems/rom-2.0.0/lib/rom/setup/finalize.rb:62:in `run!'
        from /home/flip/.gem/ruby/2.3.0/gems/rom-2.0.0/lib/rom/create_container.rb:35:in `finalize'
        from /home/flip/.gem/ruby/2.3.0/gems/rom-2.0.0/lib/rom/create_container.rb:13:in `initialize'
        from /home/flip/.gem/ruby/2.3.0/gems/rom-2.0.0/lib/rom/create_container.rb:54:in `initialize'
        from /home/flip/.gem/ruby/2.3.0/gems/rom-2.0.0/lib/rom/create_container.rb:59:in `new'
        from /home/flip/.gem/ruby/2.3.0/gems/rom-2.0.0/lib/rom/create_container.rb:59:in `container'
... 28 levels...
        from /home/flip/.gem/ruby/2.3.0/gems/activesupport-5.0.0.1/lib/active_support/execution_wrapper.rb:99:in `run!'
        from /home/flip/.gem/ruby/2.3.0/gems/activesupport-5.0.0.1/lib/active_support/reloader.rb:113:in `run!'
        from /home/flip/.gem/ruby/2.3.0/gems/activesupport-5.0.0.1/lib/active_support/reloader.rb:48:in `block (2 levels) in reload!'
        from /home/flip/.gem/ruby/2.3.0/gems/activesupport-5.0.0.1/lib/active_support/reloader.rb:46:in `tap'
        from /home/flip/.gem/ruby/2.3.0/gems/activesupport-5.0.0.1/lib/active_support/reloader.rb:46:in `block in reload!'
        from /home/flip/.gem/ruby/2.3.0/gems/activesupport-5.0.0.1/lib/active_support/execution_wrapper.rb:76:in `wrap'
        from /home/flip/.gem/ruby/2.3.0/gems/activesupport-5.0.0.1/lib/active_support/reloader.rb:45:in `reload!'
        from /home/flip/.gem/ruby/2.3.0/gems/railties-5.0.0.1/lib/rails/console/app.rb:32:in `reload!'
        from (irb):3
        from /home/flip/.gem/ruby/2.3.0/gems/railties-5.0.0.1/lib/rails/commands/console.rb:65:in `start'
        from /home/flip/.gem/ruby/2.3.0/gems/railties-5.0.0.1/lib/rails/commands/console_helper.rb:9:in `start'
        from /home/flip/.gem/ruby/2.3.0/gems/railties-5.0.0.1/lib/rails/commands/commands_tasks.rb:78:in `console'
        from /home/flip/.gem/ruby/2.3.0/gems/railties-5.0.0.1/lib/rails/commands/commands_tasks.rb:49:in `run_command!'
        from /home/flip/.gem/ruby/2.3.0/gems/railties-5.0.0.1/lib/rails/commands.rb:18:in `<top (required)>'
        from bin/rails:4:in `require'
        from bin/rails:4:in `<main>'irb(main):004:0>

Composing relations through many-to-many join

We have a setup where we have a many-to-many relationship via a join table, e.g.

  module Relations
    class Questions < ROM::Relation[:sql]
      dataset :questions
      schema(infer: true) do
        associations do
          has_many :charts, through: :questions_charts
        end
      end
    end
  end

  module Relations
    class QuestionsCharts < ROM::Relation[:sql]
      dataset :questions_charts
      schema(infer: true) do
        associations do
          belongs_to :questions
          belongs_to :charts
        end
      end
    end
  end

I can query this in my questions repository using a combine_children call to pull out all the charts and that works well, e.g.

        questions
          .combine_children(
            many: {
              charts: charts,
            }
          )

However, what I'd like to do is add an order attribute to the chart which is actually a column on the questions_tables join table, not on the chart table, alongside the attributes that come from the chart table itself, e.g.

[{ id: 1, name: 'my chart', order: 1 }, { id: 2, name: 'my other chart', order: 2 }]

...where id and name are attributes of the chart table.

At the moment the associations definition is happily pulling the charts back through that has_many through call, so I'm expecting to have to write a custom view in the questions_charts relation that does the same thing, but also includes the order attribute from the questions_tables table.

However, I can't work out how to construct this view so that the join still works as it currently does automagically with the association setup. Could you please offer some advice on how to achieve that?

Thanks in advance for your help.

Relation#last doesn't respect `as`

I'm using rom-sql via rom-repository as integration with hanami-model.

From my repository, if I use #first with as it works fine:

def first
  users.as(:entity).first
end

###

repository.first.class # => User

But this isn't respected with #last:

def last
  users.as(:entity).last
end

###

repository.last.class # => Hash

To overcome to this problem, I implemented #last as follow:

  def last
    users.order(Sequel.desc(users.primary_key)).as(:entity).first
  end

The complete setup can be found here: https://github.com/hanami/model/blob/6ea119ebc308cd745fa03d5e7729678bc16d6dd2/test/support/fixtures.rb#L28

.qualified does not work with Sequel expressions

Hey folks, stumbled on following situation. If I want to have qualified columns and have Sequel expression in them, it fails to parse it and just tries to do .to_s on Sequel expression:

metas.select(
  :id,
  Sequel.expr(:meta).pg_jsonb.get_text("name").as(:name)
).qualified
.left_join(
  :accounts, accounts__name:   Sequel.expr(:meta).pg_jsonb.get_text("name")
).to_a

It returns following error

Sequel::DatabaseError: PG::UndefinedColumn: ERROR:  column metas.#<Sequel::SQL::AliasedExpression:0x007fa0558303b8> does not exist
LINE 1: SELECT "metas"."id", "metas"."#<Sequ...

from /Users/janjiss/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/sequel-4.37.0/lib/sequel/adapters/postgres.rb:187:in `async_exec'

Problem with a polymorphic table query

We have a table (snapshots) that is reused across several entities. The relationship to the other entities is specified by a combination of parent_id and parent_type in that table.

I have set up two relations (below) so that I can get as Snapshot for a Question.

This is all working well, if I use the method directly on the Snapshot relation to get the snapshot. However, if I use that method using combine_children in a QuestionRepo then it does not correctly setup the snapshot attribute in the result set that ROM returns.

Here's the code:

class Questions < ROM::Relation[:sql]
  dataset :questions
end

class Snapshots < ROM::Relation[:sql]
  dataset :snapshots

  def for_question(questions)
    where(parent_id: questions.map { |q| q[:id] }, parent_type: "question")
  end
end

[1] db = ROM.env.gateways[:default].connection
=> #<Sequel::SQLite::Database: "sqlite::memory/store">
[2] db[:snapshots].to_a
=> [{:id=>1,
  :parent_id=>1,
  :parent_type=>"question",
  :figure_id=>1,
  :figure_type=>"charts",
  :illustration=>"{}",
  :created_at=>2016-12-14 10:55:00 +0000,
  :updated_at=>2016-12-14 10:55:00 +0000}]
[3] snapshots.for_question(questions).one
=> #<ROM::Struct[Snapshot] id=1 parent_id=1 parent_type="question" figure_id=1 figure_type="charts" illustration="{}" created_at=2016-12-14 10:55:00 +0000 updated_at=2016-12-14 10:55:00 +0000>
[4] questions.combine_children(one: snapshots.for_question).to_a
=> [#<ROM::Struct[Question] id=1 topic_id=nil title="How are my employees performing?" short_title="Employee performance" clarification="Rejected opportunities are excluded from the achievement figures." methodology=nil sage_klass="Lighthouse::Sages::Questions::Simpleton" order=nil created_at=2016-12-14 10:54:58 +0000 updated_at=2016-12-14 10:54:58 +0000 snapshot=nil>]

As you can (hopefully) see, I have the correct data in the database [#1] and [#2], the method on the Snapshots relation works correctly [#3], but the combine_children call does not.

Any thoughts? Am I missing some setup step? Or is this not going to work as I expect?

Move association macros to a plugin and disable by default

Association macros are not as helpful as they could be, mostly because Sequel's graph API automatically renames column names but only for conflicting ones, which is confusing and hard to deal with. I actually stopped using those macros and I'm perfectly happy with manual joins, they are not as hard to learn as people may think, in fact I find it valuable when I can build queries myself simply because it's way more predictable than ORMish magic, like the macros.

I think not having those macros at all would be more...ROM-idiomatic :) I also think we should focus more on query interface that allows us to build even very complex queries with as little effort as possible.

That's why I think association macros should be a plugin, and it should be an opt-in feature that you enable yourself.

lack of quickstart

I wanted to try rom to access an existing sqlite database but so far I haven't done much execpt banging my head on walls which is not rally what I expected...

Can someone provide a simple and working example of how to connect ton sqlite database and query data ? For me so far it seems there are multiple ways (relation, repository), not all work with existing adapaters and each adapaters can have slight differences, in short I did not understand much :(

Here what I managed to write (and does not work):

require 'rom'

rom = ROM.container(:sql, 'sqlite:test.db')

class LibraryCollectionRepo < ROM::Relation[:sql]
  # schema(infer: true)
  # dataset 'LibraryCollection'
  
end

repo = LibraryCollectionRepo.new(rom)

p repo.to_a

(the commented lines is what I think I will need but my problem is not currently there)

I have other walls before that but I am stuck on this, apparently I am not instancing the relation object correctly and I am not even sure if I amsupposed to instantiante it, any help would be greatly appreciated, I have been watching this library for a while and it seems like it is now in a good state to try it.

Configure dataset name at runtime

Hello, I have a usecase where I need to use a relation but the table target might be different, based on a configuration on application load. Is using Class. new { dataset myvar } my only option to set this variable? I tried using Myclass.dataset(myvariable) but didn't change anything.

Any recommendation? My library is a logger to write stuff on the database and would like to give user the possibility to pick the table every time a new logger is instantiated. Obviously is quite challenging since I need to be able to use a repository which can have different relations as targets.

wrap_parent failing with by_pk

I'm looking to use wrap_parent instead of aggregate, per the new docs.

When I use wrap_parent to retrieve all records, it works well. When I try it for a single record, I get an error about ambiguous keys.

/Users/shipmana/.rbenv/versions/2.2.5/lib/ruby/gems/2.2.0/gems/sqlite3-1.3.13/lib/sqlite3/database.rb:91:in `initialize': SQLite3::SQLException: ambiguous column name: id (Sequel::DatabaseError)

For example, with the code below, both all and all_wrapped return correctly, by_id works too with aggregate but by_id_wrapped throws the error.

Perhaps this is, once more, pilot error, but I'm not sure how to proceed.

#!/usr/bin/env ruby
require "rom"
require "rom-sql"
require "rom-repository"

class Participants < ROM::Relation[:sql]
  schema(:participants, infer: true) do
    associations do
      belongs_to :user
    end
  end
end

class Users < ROM::Relation[:sql]
  schema(:users) do
    attribute :id, Types::Serial
    attribute :country, Types::String
  end
end

config = ROM::Configuration.new(:sql, "sqlite::memory") do |container|
  connection = container.default.connection
  connection.create_table(:users) do
    primary_key :id
    string :country, null: true
  end

  connection.create_table :participants do
    primary_key :id
    string :name, null: true
    foreign_key :user_id, :users, null: false
  end

  connection.execute "INSERT INTO users (country) VALUES ('UK')"
  connection.execute "INSERT INTO participants (name, user_id) VALUES ('Fred', 1)"

  container.register_relation Users
  container.register_relation Participants
end

module Repositories
  class Participant < ROM::Repository[:participants]
    relations :users

    def all
      aggregate(:user)
        .to_a
    end

    def all_wrapped
      participants
        .wrap_parent(user: users)
        .to_a
    end

    def by_id(id)
      aggregate(:user)
        .by_pk(id)
        .one
    end

    def by_id_wrapped(id)
      participants
        .wrap_parent(user: users)
        .by_pk(id)
        .one
    end
  end
end

container = ROM.container(config)
repo = Repositories::Participant.new(container)
puts repo.all.inspect
puts repo.all_wrapped.inspect
puts repo.by_id(1).inspect
puts repo.by_id_wrapped(1).inspect

FYI, I'm using:

  * rom (3.0.0.rc2)
  * rom-mapper (0.5.0.rc1)
  * rom-repository (1.0.0.rc2)
  * rom-sql (1.0.0.rc2)

Caching the Sequel::Model on the Class instance breaks for multiple environments

Things break when you finalize the second time:

/Users/chris/.rvm/gems/ruby-2.2.3/gems/sequel-4.26.0/lib/sequel/model/base.rb:1097:in `simple_table='
    /Users/chris/.rvm/gems/ruby-2.2.3/gems/sequel-4.26.0/lib/sequel/model/base.rb:815:in `convert_input_dataset'
    /Users/chris/.rvm/gems/ruby-2.2.3/gems/sequel-4.26.0/lib/sequel/model/base.rb:686:in `set_dataset'
    /Users/chris/.rvm/gems/ruby-2.2.3/bundler/gems/rom-sql-47dcd189f797/lib/rom/sql/relation/class_methods.rb:100:in `finalize'
    /Users/chris/.rvm/gems/ruby-2.2.3/gems/rom-0.9.1/lib/rom/relation/class_interface.rb:181:in `block in registry'
    /Users/chris/.rvm/gems/ruby-2.2.3/gems/rom-0.9.1/lib/rom/relation/class_interface.rb:180:in `each_value'
    /Users/chris/.rvm/gems/ruby-2.2.3/gems/rom-0.9.1/lib/rom/relation/class_interface.rb:180:in `registry'
    /Users/chris/.rvm/gems/ruby-2.2.3/gems/rom-0.9.1/lib/rom/setup/finalize.rb:86:in `load_relations'
    /Users/chris/.rvm/gems/ruby-2.2.3/gems/rom-0.9.1/lib/rom/setup/finalize.rb:58:in `run!'
    /Users/chris/.rvm/gems/ruby-2.2.3/gems/rom-0.9.1/lib/rom/setup.rb:95:in `finalize'

Frozen schema prevents registering relation class in multiple rom-instances

After upgrading to rom 2.0.0 from a prerelease git rev my specs started to fail with RuntimeError: can't modify frozen ROM::SQL::Schema. Each spec pass if run individually, but when run in sequence only the first pass.

I follow the same pattern as used in rom-sql spec with shared context that setup a new config and rom instance for each example, but register my predefined relation classes with conf.register_relation.

It seems that using a schema prevents relation classes from being registered in multiple rom-environments:

require 'rom/sql'

conn = Sequel.sqlite

conn.create_table :users do
  primary_key :id
  String :name
end

class Users < ROM::Relation[:sql]
  schema(:users, infer: true)
end

conf1 = ROM::Configuration.new(:sql, conn)
conf1.register_relation Users
rom1 = ROM.container(conf1)

conf2 = ROM::Configuration.new(:sql, conn)
conf2.register_relation Users
rom2 = ROM.container(conf2) # ~> RuntimeError: can't modify frozen ROM::SQL::Schema

How can we not infer relation attributes?

I'm working on an existing database, on a 6 years old project, with a continuous deployment workflow.

Something I sometimes come across is removing columns from a table. We should be able to make 1 commit to remove all usage of the column, and then 1 commit to actually drop the database column, all without any errors or downtime.

If the attributes are inferred and the select queries specify all inferred columns, there will be (as there is with active record) database query errors when dropping the column.
Moreover, it requests data from de db that won't in any case be used.

ATM, I'm just playing around trying to get a grasp on ROM, and tried something like this:

class Users < ROM::Relation[:sql]
  schema :users, infer: false do
    attribute :id, Types::Int.meta(primary_key: true)
    attribute :email, Types::String
  end
end

irb(main):003:0> rom.relation(:users)
=> #<ROM::Relation[Users] dataset=#<Sequel::Postgres::Dataset: "SELECT \"id\", \"login\", \"email\", [all the other existing fields] FROM \"users\" ORDER BY \"users\".\"id\"">>

Am I missing something?

Create#insert doesn't materialize returned relation

We gotta to_a on returned relation, otherwise weird stuff happens when a command with :many results is used as it returns a relation, in some cases it may crash. We probably didn't notice because sequel's dataset API has a lot of common interfaces with Array.

Duck-typing FTW and everything but in this case we do need to materialize the relation, because that's the contract.

Inferred types are over-strict

Adding schema(:db, infer:true) to an existing relation, and I get the following errors:

     Dry::Types::ConstraintError:
       Sun, 11 Sep 2016 08:50:45 -0400 violates constraints (type?(Time) failed)

the column itself is a standard postgres timestamp:

 created_at   | timestamp without time zone |

Ability to use with ActiveRecord's connection?

I'd like to look at using ROM in our codebase, but we would need to use the same database connection that ActiveRecord uses. Is that (or will that) be possible?

Issue with declaring composite key in ROM

I was trying to follow the documentation to create a composite key for a relation.

The current setup is primary_key :job_id, :user_id

However, calling the primary_key only yields the job_id and does not return both values. I believe this is preventing me from updating and deleting by_pk

Two current workaround we used was to add an extra customized view :by_cpk which will handle the two primary keys or by declaring a new method in the specific relation with the composite key to handle the primary keys that we need it to.

1st)

view(:by_cpk, attributes[:base]) do |pk1, pk2|
    where(:job_opening_id => pk1, :user_id => pk2)
end

2nd)

def by_cpk(user_id, job_opening_id)
    where(user_id: user_id, job_opening_id: job_opening_id)
end

"Cannot find corresponding type for inet"

I'm using the bleeding edge version of rom-sql to get access to some new features, and this required pulling in the bleeding edge versions of some other rom gems to make the dependencies resolve:

rom-sql-c32f2e91e533
rom-5b338e873884
rom-repository-1bf9da460277
rom-support-673256b41457

But now when I try to start my server I get an error:

/Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-sql-c32f2e91e533/lib/rom/sql/schema/inferrer.rb:71:in `block in map_type': Cannot find corresponding type for inet (ROM::SQL::UnknownDBTypeError)
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-sql-c32f2e91e533/lib/rom/sql/schema/inferrer.rb:70:in `fetch'
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-sql-c32f2e91e533/lib/rom/sql/schema/inferrer.rb:70:in `map_type'
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-sql-c32f2e91e533/lib/rom/sql/extensions/postgres/inferrer.rb:45:in `map_type'
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-sql-c32f2e91e533/lib/rom/sql/schema/inferrer.rb:58:in `build_type'
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-sql-c32f2e91e533/lib/rom/sql/schema/inferrer.rb:47:in `block in call'
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-sql-c32f2e91e533/lib/rom/sql/schema/inferrer.rb:46:in `map'
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-sql-c32f2e91e533/lib/rom/sql/schema/inferrer.rb:46:in `call'
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-sql-c32f2e91e533/lib/rom/sql/relation.rb:42:in `block (2 levels) in inherited'
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-5b338e873884/lib/rom/schema.rb:219:in `finalize!'
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-sql-c32f2e91e533/lib/rom/sql/schema.rb:83:in `finalize!'
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-5b338e873884/lib/rom/setup/finalize/finalize_relations.rb:54:in `build_relation'
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-5b338e873884/lib/rom/setup/finalize/finalize_relations.rb:25:in `block (2 levels) in run!'
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-5b338e873884/lib/rom/setup/finalize/finalize_relations.rb:24:in `each'
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-5b338e873884/lib/rom/setup/finalize/finalize_relations.rb:24:in `block in run!'
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-5b338e873884/lib/rom/relation_registry.rb:6:in `initialize'
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-5b338e873884/lib/rom/setup/finalize/finalize_relations.rb:23:in `new'
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-5b338e873884/lib/rom/setup/finalize/finalize_relations.rb:23:in `run!'
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-5b338e873884/lib/rom/setup/finalize.rb:93:in `load_relations'
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-5b338e873884/lib/rom/setup/finalize.rb:62:in `run!'
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-5b338e873884/lib/rom/create_container.rb:33:in `finalize'
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-5b338e873884/lib/rom/create_container.rb:11:in `initialize'
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-5b338e873884/lib/rom/create_container.rb:52:in `initialize'
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-5b338e873884/lib/rom/create_container.rb:57:in `new'
    from /Users/george/.rvm/gems/ruby-2.4.0/bundler/gems/rom-5b338e873884/lib/rom/create_container.rb:57:in `container'

I have some inet columns in my PostgreSQL DB; looks like the new version of rom-sql doesn't know how to handle them (although it was working before).

(I'm using version 0.19.0 of the pg gem.)

Composite primary key error

I'm facing this error when calling a custom command:

*** ROM::SQL::DatabaseError Exception: Mysql2::Error: Operand should contain 2 column(s)

The entity, repository, command, relation and sql command to create table can be found on this gits.

My database table has two columns, both primary key: user_id and company_id.

I've the container registered as following:

container.register('create_user_company') do
    container['persistence.rom'].command(:user_company)[:create]
end

So, when calling:

container['persistence.create_user_company'].({user_id: 1, company_id: 1})

I got the SQL error described above.

Any hint on how solve this or at least see which query is actually being called to create the records?

Thanks in advance!

Add support for missing types in inferrers

There are some PG-specific types that we should add to pg inferrer. This is especially important for hanami-model where it's not possible to define attributes manually (at least not yet), so this is a blocker for hanami-model 1.0.0.

TODO

Specs should go to spec/integration/schema/inferrer/#{db_type}_spec, we have a bunch of specs already for common stuff, if there's anything db-specific in existing specs, it should be moved to a new spec that's dedicated to a given db engine.

/cc @flash-gordon @jodosha

Infer Postgres column types in relation schemas

http://rom-rb.org/learn/sql/schemas/ talks about how you can use the special postgres column types in schemas, declaring the attributes like this:

class Users < ROM::Relation[:sql]
  schema do
    attribute :meta, Types::PG::JSON
    attribute :tags, Types::PG::Array
    attribute :info, Types::PG::Hash
  end
end

But it'd be great if schema(:foo, infer: true) would detect and use these types. Right now, it fails with an error like this:

KeyError: key not found: :json
/Users/tim/.rbenv/versions/2.3.0/lib/ruby/gems/2.3.0/gems/rom-sql-0.8.0/lib/rom/sql/schema/inferrer.rb:52:in `fetch'
/Users/tim/.rbenv/versions/2.3.0/lib/ruby/gems/2.3.0/gems/rom-sql-0.8.0/lib/rom/sql/schema/inferrer.rb:52:in `build_type'

Looking at the source file, this'd be because there's no type_mapping entry for a json column.

The benefit of being able to infer these would be that you don't have to manually declare your entire table's schema as soon as you go ahead and add just one special postgres column.

Default dataset should use schema attributes

How it currently works:

Dataset selects all existing columns in the db table

How it should work:

The dataset selection should be restricted to the schema columns.

See #98 for a specific example

ROM crashes if table contains hstore column

It crashes on very early stage, even when there is no relation for given column.

begin
  require 'bundler/inline'
rescue LoadError => e
  $stderr.puts 'Bundler version 1.10 or later is required.'
  raise e
end

gemfile(true) do
  source 'https://rubygems.org'
  gem 'rom', git: 'https://github.com/rom-rb/rom.git'
  gem 'rom-sql', git: 'https://github.com/rom-rb/rom-sql.git'
  gem 'pg'
  gem 'minitest'
end

require 'minitest/autorun'

DB = Sequel.connect('postgres://localhost/rom_sql_bug')
DB.run 'CREATE EXTENSION IF NOT EXISTS hstore'
DB.run 'CREATE TABLE IF NOT EXISTS users (id serial primary key, attr hstore);'

class BugTest < Minitest::Test
  def test_aggregated_create
    ROM.container(:sql, 'postgres://localhost/rom_sql_bug')
  end
end

 1) Error:
BugTest#test_aggregated_create:
ROM::Schema::MissingAttributesError: missing attributes in ROM::Relation::Name(users) schema: :attr
    /Users/kukunin/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/bundler/gems/rom-80002e5dd6ca/lib/rom/schema.rb:324:in `finalize!'
    /Users/kukunin/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/bundler/gems/rom-sql-b2621d53ce5f/lib/rom/sql/schema.rb:100:in `finalize!'
    /Users/kukunin/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/bundler/gems/rom-80002e5dd6ca/lib/rom/setup/finalize/finalize_relations.rb:54:in `build_relation'
    /Users/kukunin/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/bundler/gems/rom-80002e5dd6ca/lib/rom/setup/finalize/finalize_relations.rb:25:in `block (2 levels) in run!'
    /Users/kukunin/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/bundler/gems/rom-80002e5dd6ca/lib/rom/setup/finalize/finalize_relations.rb:24:in `each'
    /Users/kukunin/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/bundler/gems/rom-80002e5dd6ca/lib/rom/setup/finalize/finalize_relations.rb:24:in `block in run!'
    /Users/kukunin/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/bundler/gems/rom-80002e5dd6ca/lib/rom/relation_registry.rb:6:in `initialize'
    /Users/kukunin/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/bundler/gems/rom-80002e5dd6ca/lib/rom/setup/finalize/finalize_relations.rb:23:in `new'
    /Users/kukunin/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/bundler/gems/rom-80002e5dd6ca/lib/rom/setup/finalize/finalize_relations.rb:23:in `run!'
    /Users/kukunin/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/bundler/gems/rom-80002e5dd6ca/lib/rom/setup/finalize.rb:93:in `load_relations'
    /Users/kukunin/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/bundler/gems/rom-80002e5dd6ca/lib/rom/setup/finalize.rb:62:in `run!'
    /Users/kukunin/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/bundler/gems/rom-80002e5dd6ca/lib/rom/create_container.rb:33:in `finalize'
    /Users/kukunin/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/bundler/gems/rom-80002e5dd6ca/lib/rom/create_container.rb:11:in `initialize'
    /Users/kukunin/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/bundler/gems/rom-80002e5dd6ca/lib/rom/create_container.rb:52:in `initialize'
    /Users/kukunin/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/bundler/gems/rom-80002e5dd6ca/lib/rom/create_container.rb:57:in `new'
    /Users/kukunin/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/bundler/gems/rom-80002e5dd6ca/lib/rom/create_container.rb:57:in `container'
    rom_sql_hstore.rb:24:in `test_aggregated_create'

Association DSL should raise arg error when target relation is missing a PK

Refs #137

AutoWrap plugin search relation under wrong key

My relation and dataset has different names. And I get NoMethodError for for_wrap method, because ROM search in __registry__ (https://github.com/rom-rb/rom-sql/blob/master/lib/rom/plugins/relation/sql/auto_wrap.rb#L31) relation's base_name (https://github.com/rom-rb/rom-repository/blob/f463f192c44cfd9c27a3671df534dc86acdcbc98/lib/rom/repository/loading_proxy/wrap.rb#L24), while it contains needed relation under a register_as key (https://github.com/rom-rb/rom/blob/df7e0e0bdefc2abfaf6964171aec99e2251fd6db/lib/rom/relation/class_interface.rb#L206)

Don't automatically discover the database tables

ATM, the database tables are automatically discovered. Any table without relation being auto-discovered too.

Is there a way to disable this and only rely on relations?

Improved query DSL

Currently we're using Sequel DSL to build queries (aka relation views), it's good-enough for now but I don't consider it to be the final solution and would be great to come up with a simpler DSL for 1.0.0.

Using rom relations means leveraging features of your database, we want to focus on building optimized queries that return results that match exactly what we want to have from the application domain point of view. This means using joins and projecting data in all kinds of ways is a typical usage of rom-sql, using mappers to further transform data should be a rare use-case.

This comes with a couple of challenges:

Renaming columns
Qualifying columns
Extending header (aka columns)
Selecting columns automatically based on the view definition
Extracting values from a loaded relation that is passed into curried-for-combine-views

Here's a typical scenario that uses sequel naming conventions to qualify/rename columns and join relations:

class Tasks < ROM::Relation[:sql]
  # we define the header explicitly
  view(:for_users, [:id, :title, :user_id]) do |users|
    select(
      # even though we have the header defined, we still need to manually select
      # the columns, it's tricky because you can select columns and rename them using
      # various approaches
      :tasks__id, :tasks__title, :tasks__user_id
    ).inner_join(
      # qualifying column names is a good habit, it's easy to use wrong column name
      # accidentally and in some scenarios it will go unnoticed (and obviously cause major bugs)
      # the issue here is that underscore-based approach from sequel is ugly when names are long
      :users, users__id: :tasks__user_id
    ).where(
      # writing that `.map` on input relation is a super-common thing to do, this is a loaded relation
      # so we could introduce a plugin that extracts specific information from it
      #
      # ie we could have `users.foreign_keys` or `users.primary_keys`
      tasks__user_id: users.map { |u| u[:id] }
    )
  end
end

Ideas for an improved DSL

????? TBD

Command#update breaks under compound keys

Use case: table with a compound key relation (:user_id & :event_id)

Table Descriptions

/* user */
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(50) | YES  |     | NULL    |                |
| last_name  | varchar(50) | YES  |     | NULL    |                |
| email      | varchar(50) | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+


/* event */
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | 0       | auto_increment |
| name       | varchar(255) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

/* event_invitation */
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| user_id    | int(11)      | NO   | PRI | 0       |       |
| event_id   | int(11)      | NO   | PRI | 0       |       |
| note       | varchar(255) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

   # ... within ROM setup
   rom.relation(:event_invitation) do
      primary_key [:user_id, :event_id]
   end

Bug
Running update:

rom_container.command(:event_invitation).update.call({
    user_id: 1, 
    event_id: 1, 
    note: 'Note update'
})

shows that primary key pair doesn't get handled properly in ROM::SQL::Commands::Update#update. Variable pks (as defined on https://github.com/rom-rb/rom-sql/blob/master/lib/rom/sql/commands/update.rb#L67) is assigned a value of [nil]

Problem with recursive inner joins

The minimal code to reproduce the error is as follows:

require 'rom'
require 'rom-repository'

rom = ROM.container(:sql, 'postgres://localhost/test_rom') do |conf|
  conf.default.create_table(:nodes) do
    primary_key :id
    column :name, String, null: false
    column :parent_id, Integer
  end
end

class NodesRepo < ROM::Repository[:nodes]
  commands :create

  def by_id(id)
    nodes.where(id: id)
  end

  def recursive(names)
    query = nodes
    names.each.with_index do |name, index|
      if index == 0
        query = query.prefix("nodes_0").qualified.where("nodes_0__name" => name)
      else
        query = query.prefix("nodes_#{index}").qualified.inner_join(:nodes, "nodes_#{index}_parent_id" => "nodes_#{index-1}_id").
          where("nodes_#{index}__name" => name)
      end
    end
    query
  end
end

nodes_repo = NodesRepo.new(rom)
parent = nodes_repo.create(name: "parent")
child1 = nodes_repo.create(name: "child1", parent_id: parent.id)
child2 = nodes_repo.create(name: "child2", parent_id: parent.id)
grandchild1 = nodes_repo.create(name: "grandchild1", parent_id: child1.id)
grandchild2 = nodes_repo.create(name: "grandchild2", parent_id: child2.id)

# works fine
p nodes_repo.by_id(parent.id).to_a.first
# raises PG::DuplicateAlias: ERROR:  table name "nodes" specified more than once (Sequel::DatabaseError)
p nodes_repo.recursive(%w{parent child1 grandchild1}).to_a

Plugin for generating common relations

How about adding a plugin that would generate relation views restricted by PK + any indexed column?

So ie:

class Users < ROM::Relation[:sql]
  schema do
    attribute :id, Types::Serial
    attribute :name, Types::Index(:int) # we don't have that yet btw
  end
end

# this would be generated automatically:
users.by_id
users.by_name

It's a common pattern so a bit of Convention-Over-Configuration here would be helpful.

Support inferring custom-defined types

In PG you can define custom column types, ie like that:

CREATE TYPE inventory_item AS (
  name            text,
  supplier_id     integer,
  price           numeric
);

then specify a column like that:

column :composite1, 'inventory_item', default: Sequel.literal("ROW('fuzzy dice', 42, 1.99)")

This is what Sequel gives us about that column:

[:composite1, {:oid=>6663255, :db_type=>"inventory_item", :default=>"ROW('fuzzy dice'::text, 42, 1.99)", :allow_null=>true, :primary_key=>false, :type=>nil, :ruby_default=>nil}]

Refs hanami/model#370

Support for native upserts in PG

It would be really great to have an upsert command that uses native features of PG

How to use with dry-data?

I'm really lost on this because all documentation have been quite confusing as it explains individual parts but not how everything works together.

Basically I'm looking for some full example from table creation till data insertion.

So for example I've this

class User < Dry::Data::Struct
  attribute :id, Types::Int
  attribute :name, Types::String
  attribute :email, Types::String
end

class Post < Dry::Data::Struct
  attribute :id, Types::Int
  attribute :user, User
  attribute :text, Types::String
end

and since I'm using ROM as abstraction it should be possible to easily reuse this for persistence (not recreate attributes again for ROM) and automatically create tables and easily insert data by passing instances of these.

Undefined method `schema'

I'm receiving a undefined method 'schema' in my relation classes.
Example:

class Element < ROM::Relation[:sql]
   register_as :element

   schema(:element) do
      associations do
         has_one :unit
      end
   end
end

Replacing schema by dataset works well:

class Element < ROM::Relation[:sql]
   register_as :element
   dataset :element
end

However, with dataset, I can't (or at least don't know) how to setup the relation.

Would appreciate any support.
Thanks!

Unable to set path for migrations on a gateway

Currently, when running multiple SQL gateways, if all migrations are in the same path (db/migrate), they will all be run when calling ROM::SQL::Gateway#run_migrations. The solution to this is to separate the migrations into separate directories (which also makes sense from a code perspective)

Unfortunately, due to the ROM::SQL::Migration::Migrator option being reader only, there is no way to override the default. See https://github.com/rom-rb/rom-sql/blob/master/lib/rom/sql/migration/migrator.rb#L10

My preference would be passing in an option when defining the gateway. Something like the following might work?

ROM::Configuration.new(
  default: [:sql, 'postgres://localhost/task_master', migration_path: 'db/migrate/default'], 
  legacy: [:sql, 'mysql2://localhost/tasks', migration_path: 'db/migrate/legacy']
)

Custom relations fails using the real tablename on queries

I have a table in database called 'users' and I have defined and registered two relations over it, 'users' and 'drivers'. I have done this because I have other tables, 'journeys' for instance that hold two relations (belongs_to) with the 'users' table.

The problem is that when I want to aggregate the entity driver to the query for journeys, the sql sentence that is generated is using 'driver' as the table name, which not exists, it should use the name of the table, not the name of the relation.
Maybe I'm forgetting something, isn't it?

This is some code that shows the error

#!/usr/bin/env ruby

require "bundler/setup"

require "rom-sql"
require "rom-repository"

module Relations
  class Users < ROM::Relation[:sql]
    schema(:users) do
      attribute :id, Types::Serial
      attribute :name, Types::String
      attribute :type, Types::String

      associations do
        has_many :journeys
      end
    end
  end

  class Drivers < ROM::Relation[:sql]
    register_as :drivers

    schema(:users) do
      attribute :id, Types::Serial
      attribute :name, Types::String
      attribute :type, Types::String

      associations do
        has_many :journeys
      end
    end
  end

  class Journeys < ROM::Relation[:sql]
    schema(:journeys) do
      attribute :id, Types::Serial
      attribute :name, Types::String
      attribute :user_id, Types::ForeignKey(:users)
      attribute :driver_id, Types::ForeignKey(:drivers)

      associations do
        belongs_to :user
        belongs_to :driver
      end
    end
  end
end

module Repositories

  class Users < ROM::Repository[:users]
    commands :create
  end

  class Drivers < ROM::Repository[:drivers]
    commands :create

    def by_id(id)
      drivers.fetch(id)
    end
  end

  class Journeys < ROM::Repository[:journeys]
    commands :create

    relations :journeys, :users, :drivers

    def get_all
      journeys.to_a
    end

    def get_all_with_users
      aggregate(:user).to_a
    end

    def get_all_with_drivers
      aggregate(:driver).to_a
    end
  end

end

config = ROM::Configuration.new(:sql, "sqlite::memory")
config.register_relation Relations::Users
config.register_relation Relations::Drivers
config.register_relation Relations::Journeys

container = ROM.container(config)

container.gateways[:default].tap do |gateway|
  migration = gateway.migration do
    change do
      create_table :users do
        primary_key :id
        string :name, null: false
        string :type, null: false
      end
      create_table :journeys do
        primary_key :id
        string :name, null: false
        integer :user_id
        integer :driver_id
      end
    end
  end
  migration.apply gateway.connection, :up
end

users_repo = Repositories::Users.new(container)
john = users_repo.create(name: 'John', type: 'user')

drivers_repo = Repositories::Drivers.new(container)
drivers_repo.create(name: 'Jerry', type: 'driver')
jerry = drivers_repo.by_id(2)
puts jerry.inspect

journeys_repo = Repositories::Journeys.new(container)
journeys_repo.create(name: 'Madrid-Barcelona', user_id: john.id, driver_id: jerry.id)

journeys = journeys_repo.get_all_with_users
puts journeys.inspect

journeys = journeys_repo.get_all_with_drivers
puts journeys.inspect

Cannot have colon (':') in password for mysql

Original issue: rom-rb/rom#355
@alderion commented an hour ago
the below does not work. results in access denied.

ROM.container(:sql, "mysql://usr:pass:word@machine/database") do |rom| ...
ROM.container(:sql, "mysql://usr:pass:word@machine/database") do |rom| ...
ROM.container(:sql, "mysql://usr:'pass\:word'@machine/database") do |rom| ...

am i doing it wrong or does rom not support a password that contains a colon?

Problems with UTF-8 encoding

It was reported on SO that when inserting non-ascii utf-8 chars the encoding gets messed up. More info here

Clarify schema syntax for defining associations

If a relation is registered under a different name than the underlying dataset, it is not obvious what name goes where when defining associations.

config.relation(:users) do
  dataset :user

  schema(:user) do                                         # <= dataset name
    attribute :user_id, ROM::SQL::Types::Serial
    attribute :name, ROM::SQL::Types::String

    associate do
      many :tasks                                          # <= relation name
    end
  end
end

config.relation(:tasks) do
  dataset :task

  schema(:task) do                                         # <= dataset name
    attribute :task_id, ROM::SQL::Types::Serial
    attribute :user_id, ROM::SQL::Types::ForeignKey(:user) # <= dataset name
    attribute :name, ROM::SQL::Types::String

    associate do
      one :users                                           # <= relation name
    end
  end

  def for_users(users)
    where(user_id: users.map { |user| user[:user_id] })
  end
end

When mixing these up and using them (through ROM::Repository#aggregate) you get NoMethodError on nil:NilClass, where the missing method is foreign_key if you used relation name in ROM::SQL::Types::ForeignKey() and primary_key if you used dataset name in the associate block.

I'm not sure what the best solution would be here – changing the behavior or just emphasis the current behavior in the documentation. Or maybe it would help if the error was raised when the relation/dataset couldn't be found rather than when calling a method on the missing object.

`schema(infer: true)` overrides command's input

In my application, I have different attributes format between schema (legacy tables) and my domain models. So I need to use mappers to both save and restore an entity.

While it's easy to use mappers for Relation, I haven't found a way to use for Command. So, I use command's input for this purpose.

It works great, until I want to set association for my relation. schema(infer: true) breaks everything.

After some investigation, I've found that inferred schema overrides my custom input for any commands.

So, without schema(infer: true), I get custom mapper as input

From: /Users/kukunin/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rom-sql-0.8.0/lib/rom/sql/commands/create.rb @ line 24 ROM::SQL::Commands::Create#execute:

    22: def execute(tuples)
    23:   insert_tuples = with_input_tuples(tuples) do |tuple|
 => 24:     attributes = input[tuple]
    25:     validator.call(attributes)
    26:     attributes.to_h
    27:   end
    28:
    29:   if insert_tuples.length > 1
    30:     multi_insert(insert_tuples)
    31:   else
    32:     insert(insert_tuples)
    33:   end
    34: end

[6] pry(#<Persistence::Commands::Hoi::CreateCaseFromModel>)> input
=> #<Method: Persistence::Mappers::Hoi::CasesTuple#to_tuple>

But with schema(infer: true), it is:

[5] pry(#<Persistence::Commands::Hoi::CreateCaseFromModel>)> input
=> #<Dry::Types::Constructor type=#<Dry::Types::Hash::Schema primitive=Hash options={:member_types=>{:id=>#<Dry::Types::Constrained type=#<Dry::Types::Definition primitive=Integer options={}> options={:rule=>#<Dry::Logic::Operations::And rules=[#<Dry::Logic::Rule::Predicate predicate=#<Method: Module(Dry::Logic::Predicates::Methods)#type?> options={:args=>[Integer]}>, #<Dry::Logic::Rule::Predicate predicate=#<Method: Module(Dry::Logic::Predicates::Methods)#gt?> options={:args=>[0]}>] options={}>, :meta=>{:primary_key=>true, :name=>:id}}.....A LOT OF TEXT

So, because It doesn't map my domain entity, it crashes.

My relation:

      class Relations::Cases < ROM::Relation[:sql]
        register_as :hoi_cases
        dataset :cases
      end

and command with custom mapper:

      class Commands::CreateCaseFromModel < ROM::Command::Create[:sql]
        relation :hoi_cases
        register_as :create_from_model

        input Mappers::CasesTuple.build.method(:to_tuple)
      end

and just for clarity, my mapper code:

      class CasesTuple < ROM::Mapper
        relation :hoi_quotes
        register_as :tuple

        # mapping rules here

        def to_tuple(params)
          call([params]).first
        end
      end

P.S. If you know a better way to map entity to underlying schema in 2-directions more easily - tell me, please.

Thanks!

Should allow String substitutes

I have a value object that stands in for a string, with some stricter formatting rules. This class defines to_str and sql_literal(ds) and can stand in for a string when writing to the database. I can use String(value) to cast to a pure string.

With a schema, both inferred, and when defined as Types::Coerced::String, I get an error when using an attribute of this class.

Types, strict or not, should probably make a reasonable effort to attempt to use the implicit conversion methods

Possible to specify default sort key?

I have a legacy table with no id column, and keep getting errors because it was attempting to order by tablename.id

Too many nested queries when using union

From @blelump on July 13, 2016 11:26

Hi!

I am not sure this is related either to ROM or Sequel, but consider such query:

        users
        .inner_join(:students, id: :verifable_id)
        .where(verifable_type: "student")
        .select(:users__id,:email,:name,:surname)
        .union(
          users.inner_join(:employees, id: :verifable_id)
          .where(verifable_type: "employee")
          .select(:users__id,:email,:name,:surname)
        )

The corresponding SQL is given as:

SELECT * FROM 
    (SELECT * FROM (
        SELECT users.id, "email", "name", "surname" FROM "users" 
        INNER JOIN "students"  ON ("students"."id" = "users"."verifable_id") 
        WHERE ( ("verifable_type" = 'ForeignStudent')) ORDER BY "users"."id"
    ) AS "t1" 
    UNION 
    (SELECT * FROM (
        SELECT "users"."id", "email", "name", "surname" FROM "users" 
        INNER JOIN "employees" ON ("employees"."id" = "users"."verifable_id") 
        WHERE (("users"."id" IN ('1', '2')) AND ("verifable_type" = 'Employee')) ORDER BY "users"."id") 
    AS "t1")
) AS "t1";

which looks quite complex. Are we missing something?

I'd expect smth like:

SELECT users.id, "email", "name", "surname" FROM "users" 
INNER JOIN "students"  ON ("students"."id" = "users"."verifable_id") 
WHERE ( ("verifable_type" = 'ForeignStudent'))

UNION 

SELECT "users"."id", "email", "name", "surname" FROM "users" 
INNER JOIN "employees" ON ("employees"."id" = "users"."verifable_id") 
WHERE (("users"."id" IN ('1', '2')) AND ("verifable_type" = 'Employee'))

Insert command does not work when primary key is not AUTO_INCREMENT

I have a table where the primary key is not generated by the database, I'm using MySQL.

When using a Create command to insert data it will return the wrong tuple.

describe 'rom-bug' do
  let(:config) { ROM::Configuration.new(:sql, '<mysql url>') }
  let(:rom) {
    config.default_gateway.connection.create_table!(:tenants) {
      String :id, size: 36, null: false, primary_key: true
      String :name, null: false
    }

    config.relation(:tenants)

    config.commands(:tenants) {
      define(:create) {
        result(:one)
      }
    }

    ROM.container(config)
  }


  it 'creates the correct tupple' do
    create_tenant = rom.command(:tenants).create

    input         = {id: 'AAAA', name: 'Company A'}
    tenant_a      = create_tenant.call(input)

    expect(tenant_a).to eq(input)

    input         = {id: 'BBBB', name: 'Company B'}
    tenant_b      = create_tenant.call(input)

    expect(tenant_b).to eq(input)
  end
end

I, [2016-11-24T17:42:18.990413 #36340]  INFO -- : (0.000443s) INSERT INTO `tenants` (`id`, `name`) VALUES ('AAAA', 'Company A')
I, [2016-11-24T17:42:18.990921 #36340]  INFO -- : (0.000241s) SELECT `id`, `name` FROM `tenants` WHERE (`id` IN (0)) ORDER BY `tenants`.`id`
I, [2016-11-24T17:42:18.992827 #36340]  INFO -- : (0.000483s) INSERT INTO `tenants` (`id`, `name`) VALUES ('BBBB', 'Company B')
I, [2016-11-24T17:42:18.993585 #36340]  INFO -- : (0.000360s) SELECT `id`, `name` FROM `tenants` WHERE (`id` IN (0)) ORDER BY `tenants`.`id`

[inferrer] Add support for "line" and "circle" PG types

Hanami model expects that line and circle PG types can be automatically inferred, so it would be nice if we could have that supported by PG inferrer.

Here's what Sequel gives us about these types:

# "line"
[:geometric1, {:oid=>600, :db_type=>"point", :default=>nil, :allow_null=>true, :primary_key=>false, :type=>nil, :ruby_default=>nil}]

# circle
[:geometric3, {:oid=>718, :db_type=>"circle", :default=>"'<(15,15),1>'::circle", :allow_null=>true, :primary_key=>false, :type=>nil, :ruby_default=>nil}]