This library officially supports the following Ruby versions:
- MRI >=
2.5
- jruby >=
9.2
See LICENSE
file.
SQL support for rom-rb
Home Page: https://rom-rb.org
License: MIT License
This library officially supports the following Ruby versions:
2.5
9.2
See LICENSE
file.
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.
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
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
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?
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.
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.
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'
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?
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
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'
No exception or better error message explaining what I did wrong or documentation how to prevent that error.
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]
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
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?
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.
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)
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
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
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.
It would be really great to have an upsert command that uses native features of PG
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?
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.)
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
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.
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'
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
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
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
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']
)
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
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.
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!
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}]
Refs hanami/model#370
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.
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>
Refs #137
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`
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.
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 |
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'
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
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)
I have a legacy table with no id
column, and keep getting errors because it was attempting to order by tablename.id
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.
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.
Types::PG::IPAddress
(this can be a Types::String
for a good start)Types::PG::GeoPoint
(I'm not exactly sure what this should be :))Inferrer[:mysql]
Inferrer[:sqlite]
"inet" => Types::PG::IPAddress
"point" => Types::PG::GeoPoint
"numeric(precision)" => Types::Float.meta(precision: precision)
(I think?)"decimal(precision)" => Types::Decimal.meta(precision: precision
"decimal(precision, scale)" => Types::Decimal.meta(precision: precision, scale: scale)
numeric(precision)
is used by Sequel in all 3 dbsI'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!
It was reported on SO that when inserting non-ascii utf-8 chars the encoding gets messed up. More info here
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:
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
????? TBD
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
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.
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!
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'))
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?
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.