Jeremy,
Thanks for the speedy response and apologies for my slow reply due to the
intervening Australian Easter long weekend.
Your change works with respect to the auto-validations induced error I was
having, however it seems with such a dataset, any attempt to insert or
update a model results in some bad SQL:
Sequel::DatabaseError: PG::SyntaxError: ERROR: syntax error at or near "("
LINE 1: INSERT INTO (SELECT *, row_number() OVER (PARTITION BY "owne...
It seems I need a plain dataset when doing updates.
I noticed there are private instance methods
_insert_dataset/_update_dataset/_delete_dataset but they don't currently
seem like they are intended to be overridden by user code. However
overriding those methods in my model as follows does seem to work:
> private
def _insert_dataset
super.first_source_table
end
def _update_dataset
super.first_source_table
end
def _delete_dataset
super.first_source_table
end
Is there a reason the above could not be the default approach?
I will be re-factoring a working solution into a `scoped_sequence' plugin
as I have the need for scoped monotonically increasing columns in a number
of models due to my application being multi-tenant.
Can you think of a better/more appropriate way to implement scoped
monotonically increasing sequences in Postgres with Sequel?
On Thursday, 17 April 2014 15:26:39 UTC+10, Jeremy Evans wrote:
>
> On Wednesday, April 16, 2014 9:24:05 PM UTC-7, Andrew Hacking wrote:
>>
>> Wondering how I can specify a computed model attribute which is always
>> returned for my model.
>>
>> I would like to have an automatically computed sequence scoped on a
>> column as follows:
>>
>> DB[:table].select_append{row_number(:over, partition: owner_id, order:
>>> created_at){}.as(:item_number)}.from_self
>>
>>
>> This returns the row_number as the item_number for all items scoped to
>> owner_id, such that those numbers are monotonically increasing. It works
>> perfectly.
>>
>> I have tried to add the following in my model class:
>>
>> set_dataset DB[:table].select_append{row_number(:over, partition:
>>> owner_id, order: created_at){}.as(:item_number)}.from_self
>>
>>
>> But I get the following error:
>>
>> sequel-4.6.0/lib/sequel/dataset/sql.rb:640:in `schema_and_table':
>>> table_name should be a Symbol, SQL::QualifiedIdentifier, SQL::Identifier,
>>> or String (Sequel::Error)
>>
>> sequel-4.6.0/lib/sequel/database/schema_methods.rb:790:in
>>> `schema_and_table'
>>> sequel-4.6.0/lib/sequel/adapters/shared/postgres.rb:944:in `regclass_oid'
>>> sequel-4.6.0/lib/sequel/adapters/shared/postgres.rb:340:in `indexes'
>>> sequel-4.6.0/lib/sequel/plugins/auto_validations.rb:111:in
>>> `setup_auto_validations'
>>> sequel-4.6.0/lib/sequel/model/plugins.rb:44:in `block in
>>> after_set_dataset'
>>
>>
>> Any ideas how I can have my models include a (read only) attribute
>> computed from a window function?
>>
>
> Well, from the backtrace, a quick fix would be to not use the
> auto_validations plugin.
>
> The patch below should fix things (assuming Google Groups doesn't mangle
> it). I'll have to add a spec before I can commit it, though.
>
> Thanks,
> Jeremy
>
> diff --git a/lib/sequel/plugins/auto_validations.rb
> b/lib/sequel/plugins/auto_validations.rb
> index e51b0f8..d40dc9d 100644
> --- a/lib/sequel/plugins/auto_validations.rb
> +++ b/lib/sequel/plugins/auto_validations.rb
> @@ -107,8 +107,9 @@ module Sequel
> @auto_validate_not_null_columns = not_null_cols -
> Array(primary_key)
> explicit_not_null_cols += Array(primary_key)
> @auto_validate_explicit_not_null_columns =
> explicit_not_null_cols.uniq
> - @auto_validate_unique_columns = if db.supports_index_parsing?
> - db.indexes(dataset.first_source_table).select{|name, idx|
> idx[:unique] == true}.map{|name, idx| idx[:columns]}
> + table = dataset.first_source_table
> + @auto_validate_unique_columns = if db.supports_index_parsing?
> && [Symbol, SQL::QualifiedIdentifier, SQL::Identifier, String].any?{|c|
> table.is_a?(c)}
> + db.indexes(table).select{|name, idx| idx[:unique] ==
> true}.map{|name, idx| idx[:columns]}
> else
> []
> end
>
>
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
