On Apr 8, 3:16 pm, paddor <[email protected]> wrote:
> Hi Jeremy
>
> Thank you for your answers.
>
> > To take an example from [1]:
>
> > ds = DB[:items].filter(:name=>:$n)
> > ds.call(:select, :n=>'Jim')
>
> > Here :$n is used as the placeholder, and :n is used to provide the
> > value of the placeholder. I guess I'm not clear on what is confusing
> > you.
>
> Does :n come from :$n? OK, I think so. But what is :name for? The
> column name?
Yes. :name is the column name, :$n is the placeholder, :n is the key
in the placeholder hash where the corresponding value in that hash is
used in place of :$n.
> > Actually, it wouldn't be able to do so in an unambiguous manner except
> > in very simple cases, and even though it would a major effort to try
> > to support. In a Model, Sequel knows exactly what the columns are for
> > the model's backing table. There is no such knowledge with datasets.
>
> Perfect! I implemented it using models! Isn't it already able to guess
> the column types? Or does this feature need to be implemented yet?
Prepared statements operate at the dataset level, not the model level,
and datasets don't know the types of their columns, or even which
table an identifier corresponds to. Here's a simple example that
shows what I mean:
DB.create_table(:a){Integer :c}
DB.create_table(:b){String :c}
DB[:a].join(:b, [:c]).filter(:c=>:$v).prepare(:select, :s)
Here :c could refer to either the Integer column in :a or the String
column in :b, so you can't possibly guess the column type. Also, even
if you could guess the type, it would quite a bit of work to parse the
internal object tree and try to figure out that :$v is actually even
related to :c. Since there isn't a good way of guessing types, Sequel
makes you specify them explicitly if they are required by PostgreSQL.
> > Note that you should only be attempting to use bound variables and
> > prepared statements if you have identified a bottleneck in your
> > application that you think preparing will speed up. Generally, only
> > queries that are complex for the database to plan stand much benefit
> > from preparing, and those queries tend to be the most complex ones.
> > Using bound variables and prepared statements for simple queries in
> > Sequel is usually slower than just embedding the value in the SQL
> > string. I've done benchmarking on this on PostgreSQL, if you are
> > interested [2].
>
> The application which I use the prepared statement is a log parser
> which saves the DHCP packets into a database table (live). And as I
> said in the mail, it's a huge amount of logs constantly arriving. The
> prepared statement already *greatly* improved the performance!
> (Everything it does is an INSERT for each parsed DHCP packet with all
> columns set into one single table, which is always the same. The
> associated objects of the other models are saved without prepared
> statement, because they're much fewer.)
I finally think I understand what your issue is. You are using
PostgreSQL which requires type specifiers on bound variable arguments
in most cases, and you want Sequel to guess the type specifiers for
you. It would have been easier if you posted your code with your
question, so I could see what you are actually trying to do.
Here's a helper method for preparing insert statements for Models on
PostgreSQL to make it easier:
class Sequel::Model
def self.prepare_insert(name, *cols)
h = {}
cols.each{|k| h[k] = :"$#{k}__#{db_schema[k][:db_type]}"}
dataset.prepare(:insert, name, h)
end
end
Here's some example code that uses it:
DB.create_table!(:as){primary_key :id; Integer :b; String :c; Date :d}
class A < Sequel::Model
IPS = prepare_insert(:aips, :b, :c, :d)
end
A::IPS.inspect
# <Sequel::Postgres::Dataset/PreparedStatement "INSERT INTO \"as\" (\"c
\", \"b\", \"d\") VALUES ($1::text, $2::integer, $3::date) RETURNING
\"id\"">
A::IPS.call(:b=>42, :c=>'RF', :d=>Date.today)
A::IPS.call(:b=>24, :c=>'FBTS', :d=>Date.today - 1)
Hopefully that's what your issue was and the code above will help. If
not, please post your code and a more detailed explanation of what you
are attempting to do.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
