Re: Problems using lazy_attributes with class_table_inheritance
Thanks for getting back to me!
Assuming you meant to use lazy attributes in both the parent class and the
subclass, it semi-worked in that I no longer see the missing column errors
on the subclass. However, we still don't have typecasting and it seems I
can't update the lazy loaded column from the subclass.
Here is my setup using the same DB schema as before
class Employee < Sequel::Model
plugin :lazy_attributes, :bio
plugin :class_table_inheritance, key: :kind
end
class Manager < Employee
old_from = dataset.opts[:from][0]
set_dataset
dataset.from(old_from.expression.select_append{employees[:bio]}.as(old_from.alias))
lazy_attributes :bio
end
*Employee works as expected with typecasting and setting the lazy loaded
attribute*
employee = Employee.create active: 0, bio: "Superclass bio"
puts "employee's biography: #{employee.bio}"
SQL
INSERT INTO "employees" ("active", "bio", "kind") VALUES (false,
'Superclass bio', 'Employee') RETURNING "id"
SELECT "employees"."id", "employees"."kind", "employees"."active" FROM
"employees" WHERE ("id" = 1) LIMIT 1
SELECT "employees"."bio" FROM "employees" WHERE ("id" = 1) LIMIT 1
*Manager still doesn't work quite right. The lazy loading works but bio is
never inserted*
manager = Manager.create active: false, bio: "Manager bio"
manager.reload
puts %Q{manager's biography is "Manager bio": #{manager.bio == "Manager
bio"}}
SQL
INSERT INTO "employees" ("kind", "active") VALUES ('Manager', false)
RETURNING *
INSERT INTO "managers" ("id") VALUES (2) RETURNING *
SELECT "employees"."id", "employees"."kind", "employees"."active" FROM
(SELECT "employees"."id", "employees"."kind", "employees"."active",
"employees"."bio" FROM "employees" INNER JOIN "managers" ON
("managers"."id" = "employees"."id")) AS "employees" WHERE ("id" = 2) LIMIT
1
SELECT "employees"."bio" FROM (SELECT "employees"."id", "employees"."kind",
"employees"."active", "employees"."bio" FROM "employees" INNER JOIN
"managers" ON ("managers"."id" = "employees"."id")) AS "employees" WHERE
("id" = 2) LIMIT 1
*Typecasting is also not working for the subclass*
Manager.create active: 0
ERROR -- : PG::DatatypeMismatch: ERROR: column "active" is of type boolean
but expression is of type integer
LINE 1: ..."employees" ("kind", "active") VALUES ('Manager', 0) RETURNI...
^
HINT: You will need to rewrite or cast the expression.: INSERT INTO
"employees" ("kind", "active") VALUES ('Manager', 0) RETURNING *
manager = Manager.create active: false, bio: "Manager bio"
I suspect it's because the schema and columns for Manager are missing the
bio column.
irb(main):001:0> Manager.db_schema
=> {:id=>{}, :kind=>{}, :active=>{}}
irb(main):002:0> Manager.columns
=> [:id, :kind, :active]
Thanks for your help!
On Monday, October 18, 2021 at 8:38:44 PM UTC-7 Jeremy Evans wrote:
> On Mon, Oct 18, 2021 at 6:05 PM Sterling Brim-DeForest <
> s.brimd...@gmail.com> wrote:
>
>> I'm running into issues using the lazy_attributes plugin with the
>> class_table_inheritance plugin and am hoping to get some help with it.
>>
>> The parent table has a column that I want to lazily load. If I add the
>> lazy_attributes plugin to the parent class I run into errors related to the
>> lazy loaded column being missing. When I add it to the subclass then I lose
>> typecasting for all columns when setting them through the subclass.
>>
>> I used postgres because sqlite is not very strict with typing and I
>> wanted to use the same setup for both examples.
>>
>> setup:
>>
>> DB = Sequel.connect "postgres://user:password@host:port/database_name"
>>
>> DB.create_table :employees do
>> primary_key :id
>> column :kind, :text
>> column :active, :boolean
>> column :bio, :text
>> end
>>
>> DB.create_table :managers do
>> foreign_key :id, :employees, null: false
>> primary_key [:id]
>> end
>>
>> The base case without adding lazy_attributes manually works as expected
>>
>> class Employee < Sequel::Model
>> plugin :class_table_inheritance, key: :kind
>> end
>>
>> class Manager < Employee
>> end
>>
>> employee = Employee.create active: 0, bio: "Superclass bio"
>> puts "employee's biography: #{employee.bio}"
>> puts "Employee 'active' was typecast -> #{employee.active}"
>>
>> manager = Manager.create active: 0, bio: "Subclass bio"
>> puts "manager's biography: #{manager.bio}"
>> puts "Manager 'active' was typecast -> #{manager.active}"
>>
>>
>> Adding lazy_attributes on bio to the parent class causes reading bio to
>> raise an error: PG::UndefinedColumn: ERROR: column employees.bio does
>> not exist.
>>
>> class Employee < Sequel::Model
>> plugin :lazy_attributes, :bio
>> plugin :class_table_inheritance, key: :kind
>> end
>>
>> class Manager < Employee
>> end
>>
>> employee = Employee.create active: 0, bio: "Superclass bio"
>> # superclass works
>> puts "Employee 'active' was typecast -> #{employee.active}"
>> puts "employee's biography:
Re: Synthetic fields
On Tue, Oct 19, 2021 at 2:15 AM David Espada wrote:
> El miércoles, 13 de octubre de 2021 a las 15:43:39 UTC+2, Jeremy Evans
> escribió:
>
>> On Wed, Oct 13, 2021 at 1:49 AM David Espada wrote:
>>
>>> Hi.
>>>
>>> I need to define a synthtic field for a Sequel::Model source dataset.
>>> I'd like getting a sum field with any Model query. How can it be done?
>>>
>>
>> It sounds like you want to add a SELECTed expression to the model's
>> dataset:
>>
>> class ModelName < Sequel::Model(DB[:table].select_append(some_expression))
>> end
>>
>
> Thank you very much :D
>
> I have found a problem with my "cooked" dataset. Here is the code for
> composing it:
>
>
> class Gasto < Sequel::Model
> set_dataset(dataset.select(*dataset.columns.map {|c|
> Sequel.qualify(:gastos, c)}).
> left_join(dataset.exclude(id: :gastos__id).as('g'),
> prevision_id: :gastos__prevision_id).
> select_append {sum(:g__importe_divisa).as(:otros_gastos)}.
> group_by(:gastos__id))
> end
>
>
> When I try to save data on this model here is the result of "puts
> dataset.sql":
>
> -
> SELECT * FROM (SELECT "gastos"."id", "gastos"."factura_proveedor_id",
> "gastos"."prevision_id", "gastos"."divisa_id", "gastos"."cambio_gasto",
> "gastos"."importe_divisa_base", "gastos"."importe_divisa",
> "gastos"."cuenta_gasto", "gastos"."dato_facturacion_compra_id",
> "gastos"."concepto_id", "gastos"."contraccion_id", "gastos"."_type",
> "gastos"."codigo_analitica", "gastos"."parent_id", "gastos"."repartido",
> "gastos"."entidad_reparto", "gastos"."criterio_reparto_id",
> "gastos"."tipo_entidad", "gastos"."clave_busqueda_reparto_id",
> "gastos"."valor_reparto_id", sum("g"."importe_divisa") AS "otros_gastos"
> FROM "gastos" LEFT JOIN (SELECT * FROM "gastos" WHERE (("gastos"."_type" IN
> ('factura_proveedor_gasto')) AND ("id" != "gastos"."id"))) AS "g" ON
> ("g"."prevision_id" = "gastos"."prevision_id") WHERE ("gastos"."_type" IN
> ('factura_proveedor_gasto')) GROUP BY "gastos"."id") AS "gastos" LIMIT 1;
> --
>
> It works good when executed directly in PostgreSQL. But when doing the
> save in Sequel it raises the error:
>
>
> Sequel::DatabaseError: Java::OrgPostgresqlUtil::PSQLException: ERROR:
> syntax error at or near "("
> Position: 13
> from
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(org/postgresql/core/v3/QueryExecutorImpl.java:2532)
> Caused by Java::OrgPostgresqlUtil::PSQLException: ERROR: syntax error at
> or near "("
> Position: 13
> from
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(org/postgresql/core/v3/QueryExecutorImpl.java:2532)
> Caused by Java::OrgPostgresqlUtil::PSQLException: ERROR: syntax error at
> or near "("
> Position: 13
> from
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(org/postgresql/core/v3/QueryExecutorImpl.java:2532)
> -
>
> Is there a bug? Anything I do wrong?
>
Not a bug. You cannot insert into a subquery, and Sequel::Model
automatically wraps a dataset in a subquery in cases where it uses a JOIN,
to prevent other issues.
You may be able to do what you want by creating a view, and having the
model use that view as the dataset (PostgreSQL supports updatable views).
Alternatively, you could try:
class Gasto < Sequel::Model
plugin :split_values
orig_columns = columns
orig_instance_dataset = @instance_dataset
set_dataset ...
@instance_dataset = orig_instance_dataset
@columns = orig_columns
end
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sequel-talk+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/CADGZSSf_WV85565zsbsTaQcO6rhcbjcXp5zMzhjHO6nDvS0cvQ%40mail.gmail.com.
Re: Synthetic fields
El miércoles, 13 de octubre de 2021 a las 15:43:39 UTC+2, Jeremy Evans
escribió:
> On Wed, Oct 13, 2021 at 1:49 AM David Espada wrote:
>
>> Hi.
>>
>> I need to define a synthtic field for a Sequel::Model source dataset. I'd
>> like getting a sum field with any Model query. How can it be done?
>>
>
> It sounds like you want to add a SELECTed expression to the model's
> dataset:
>
> class ModelName < Sequel::Model(DB[:table].select_append(some_expression))
> end
>
Thank you very much :D
I have found a problem with my "cooked" dataset. Here is the code for
composing it:
class Gasto < Sequel::Model
set_dataset(dataset.select(*dataset.columns.map {|c|
Sequel.qualify(:gastos, c)}).
left_join(dataset.exclude(id: :gastos__id).as('g'),
prevision_id: :gastos__prevision_id).
select_append {sum(:g__importe_divisa).as(:otros_gastos)}.
group_by(:gastos__id))
end
When I try to save data on this model here is the result of "puts
dataset.sql":
-
SELECT * FROM (SELECT "gastos"."id", "gastos"."factura_proveedor_id",
"gastos"."prevision_id", "gastos"."divisa_id", "gastos"."cambio_gasto",
"gastos"."importe_divisa_base", "gastos"."importe_divisa",
"gastos"."cuenta_gasto", "gastos"."dato_facturacion_compra_id",
"gastos"."concepto_id", "gastos"."contraccion_id", "gastos"."_type",
"gastos"."codigo_analitica", "gastos"."parent_id", "gastos"."repartido",
"gastos"."entidad_reparto", "gastos"."criterio_reparto_id",
"gastos"."tipo_entidad", "gastos"."clave_busqueda_reparto_id",
"gastos"."valor_reparto_id", sum("g"."importe_divisa") AS "otros_gastos"
FROM "gastos" LEFT JOIN (SELECT * FROM "gastos" WHERE (("gastos"."_type" IN
('factura_proveedor_gasto')) AND ("id" != "gastos"."id"))) AS "g" ON
("g"."prevision_id" = "gastos"."prevision_id") WHERE ("gastos"."_type" IN
('factura_proveedor_gasto')) GROUP BY "gastos"."id") AS "gastos" LIMIT 1;
--
It works good when executed directly in PostgreSQL. But when doing the save
in Sequel it raises the error:
Sequel::DatabaseError: Java::OrgPostgresqlUtil::PSQLException: ERROR:
syntax error at or near "("
Position: 13
from
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(org/postgresql/core/v3/QueryExecutorImpl.java:2532)
Caused by Java::OrgPostgresqlUtil::PSQLException: ERROR: syntax error at or
near "("
Position: 13
from
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(org/postgresql/core/v3/QueryExecutorImpl.java:2532)
Caused by Java::OrgPostgresqlUtil::PSQLException: ERROR: syntax error at or
near "("
Position: 13
from
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(org/postgresql/core/v3/QueryExecutorImpl.java:2532)
-
Is there a bug? Anything I do wrong?
Thank you again for your work and tricks :)
--
David
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sequel-talk+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/bbfc518c-369f-4dbc-afe6-969daa275e63n%40googlegroups.com.
Re: Configure extension on preconnect
Just to conclude the thread, the pull request was created and merged
(thanks!), so after it is released, this is how you would apply the
expiration timeout before *:preconnect* kicks in:
*DB = Sequel.connect(*
* adapter: 'mysql2', host: '127.0.0.1', port: '49153', database: 'my_db',*
* user: 'root', password: 'my_pass',*
* ssl_mode: :disabled,*
*preconnect_extensions: :connection_expiration,*
* preconnect: true,*
* before_preconnect: proc { |db| db.pool.connection_expiration_timeout
= 100 }*
*)*
Note the new option, *:before_preconnect*, which accepts a proc that will
be passed the DB instance, after *:preconnect_extensions* are loaded, but
before the connections are made.
Best,
Marcelo
On Sunday, October 17, 2021 at 5:24:33 PM UTC+2 Jeremy Evans wrote:
> On Sun, Oct 17, 2021 at 4:49 AM Marcelo wrote:
>
>> Hello all,
>>
>> I'm adding the :connection_expiration extension to a DB instance, but I
>> need to change its default expiration time with, for example:
>> DB.pool.connection_expiration_timeout = 100
>>
>> However, this new expiration time is not applied to the initial
>> connection, but rather the default value of 14400, and I'm having to resort
>> to manually disconnecting and reconnecting:
>>
>> *DB = Sequel.connect(*
>> * adapter: 'mysql2', host: '127.0.0.1', port: '49153',
>> database: 'my_db',*
>> * user: 'root', password: 'my_pass',*
>>
>> * ssl_mode: :disabled,*
>> * extensions: :connection_expiration,*
>>
>> *)*
>>
>> *def expiration_times(db)*
>> *
>> db.pool.instance_variable_get(:@connection_expiration_timestamps).values.map{|v|
>>
>> v[1]}.join(', ')*
>> *end*
>>
>> *DB.pool.connection_expiration_timeout = 100*
>> *print "Initial values: "*
>> *puts expiration_times(DB)*
>>
>> *DB.disconnect*
>> *print "After disconnect: "*
>> *puts expiration_times(DB)*
>>
>> *DB[:users].first*
>> *print "After reconnect: "*
>> *puts expiration_times(DB)*
>>
>> *# => Initial values: 14400.0*
>> *# => After disconnect: *
>> *# => After reconnect: 100.0*
>>
>> That's still passable, but if I additionally want to enable :preconnect,
>> this doesn't work at all, since after DB.disconnect, I'd have to call the
>> private method `preconnect` directly.
>>
>> *DB = Sequel.connect(*
>> * adapter: 'mysql2', host: '127.0.0.1', port: '49153',
>> database: 'my_db',*
>> * user: 'root', password: 'my_pass',*
>>
>> * ssl_mode: :disabled,*
>> * preconnect_extensions: :connection_expiration,*
>> * preconnect: true*
>> *)*
>>
>>
>> My specific use case is with the connection_expiration extension, but I'm
>> interested if there's in general a way to configure the extension prior to
>> a connection being established, and especially for preconnections.
>>
>
> It doesn't look like there is a way to do what you want currently. We
> would probably need to add an option like :before_preconnect to do so. I'm
> definitely open to that. If you want to submit a pull request for it,
> please do so. Otherwise, I can probably take care of it before the next
> release.
>
> Thanks,
> Jeremy
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sequel-talk+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/f6cf5dc9-a8b1-45f9-9bc8-d596e7dec576n%40googlegroups.com.
