Thank you very much. Your answer helped me a lot. On Apr 9, 3:25 am, Jeremy Evans <[email protected]> wrote: > 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.
